优点

  • 索引大大减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变成顺序IO
  • 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
    • 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
  • 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  • 对于非常小的表，大部分情况下简单的全表扫描更高效；

 

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

因此应该只为最经常查询和最经常排序的数据列建立索引。

MySQL里同一个数据表里的索引总数限制为16个。

 

索引的分类

      在一个表中，主键索引只能有一个，唯一索引可以有多个。

  • 主键索引（PRIMARY KEY）

    • 唯一的标识，主键不可重复，只能有一个列作为主键

  • 唯一索引（UNIQUE KEY）

    • 避免重读的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

  • 常规索引（KEY / INDEX）

    • 默认的，通过index或key关键字来设置

  • 全文索引（FULLTEXT）

    • 在特定的数据库引擎下采用，之前只有MYISAM有

    • 快速定位数据

基础语法

 -- 索引的使用
 -- 方式1：在创建表的时候给字段增加索引
 -- 方式2：表创建完毕后，增加索引
 ​
 -- 显示所有的索引信息
 SHOW INDEX FROM `student`
 ​
 -- 增加一个全文索引：格式：索引类型 索引名（列名），主键索引只要有索引名就行了
 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`)

 -- 删除索引
 DROP INDEX 索引名 ON 表名
 ​
 -- EXPLAIN分析sql执行的情况
 EXPLAIN SELECT * FROM `student`; -- 非全文索引
 EXPLAIN SELECT * FROM `student` WHERE MATCH(`StudentName`) AGAINST('郭') -- 通过全文索引进行查找

 

创建表结构

CREATE TABLE `demo`.`users`(  
  `u_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` VARCHAR(50) NOT NULL DEFAULT '用户昵称' COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) NOT NULL COMMENT '电话号码',
  `sex` TINYINT(4) DEFAULT 0 COMMENT '0男1女',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT 0 COMMENT '年龄',
  `create_time` DATETIME COMMENT '创建时间',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`u_id`)
);

创建函数添加模拟数据

DELIMITER $$

CREATE
    FUNCTION `demo`.`mock_data`()
    RETURNS INT
    BEGIN
	-- 创建变量为i
	DECLARE i INT(11) DEFAULT 1;
	WHILE i < 1000000 DO
		-- 获取当前系统时间
		SELECT CURRENT_TIMESTAMP INTO @time;
		-- 插入语句
		-- CONCAT(a,b) 把a和b两个字符串拼接
		-- RAND() 随机数
		-- FLOOR() 向下取整
		-- UUID() 唯一ID
		INSERT INTO `users`(`name`,`email`,`phone`,`sex`,`password`,`age`,`create_time`,`update_time`)
		VALUE(
				CONCAT('用户',i),
				CONCAT(FLOOR((RAND()*9999)),i,'@qq.com'),
				CONCAT('13',FLOOR(RAND()*999999999)),
				FLOOR(RAND()*2),
				UUID(),
				FLOOR(RAND()*100),
				@time,
				@time
		);
		SET i = i + 1;
	END WHILE;
	RETURN i;
		
    END$$

DELIMITER ;

执行函数

SELECT `mock_data`();

执行语句查看耗时

SELECT * FROM `users` WHERE NAME = "用户99999";

创建索引

CREATE INDEX 索引名 ON 表(字段)                索引名起名规范 id_表名_字段名

CREATE INDEX id_users_name ON `users`(`name`);

再次执行语句查看耗时

SELECT * FROM `users` WHERE `phone` = "1330039060";

最后修改于 2021-09-30 09:12:47
如果觉得我的文章对你有用，请随意赞赏
扫一扫支付
上一篇