MySQL（十二）索引使用的情况分析

索引使用的情况分析

数据准备

• 创建表student_info、course

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb3;

• 创建生成随机字符串函数

  -- 函数返回随机字符串
  DELIMITER //
  
  CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
  BEGIN 
  	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  	DECLARE return_str VARCHAR(255) DEFAULT '';
  	DECLARE i INT DEFAULT 0;
  	WHILE i < n DO 
         SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
         SET i = i + 1;
      END WHILE;
      RETURN return_str;
  END //
  DELIMITER ;
  
  

  首先要确保相信函数的变量log_bin_trust_function_creators为1

  SELECT @@log_bin_trust_function_creators variable;
  
  SET GLOBAL log_bin_trust_function_creators = 1;
  

• rand_num函数

  -- rand_num
  DELIMITER //
  
  CREATE FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int
  BEGIN
  DECLARE i INT DEFAULT 0;
  SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
  RETURN i;
  END //
  DELIMITER ;
  

• 创建插入课程表的存储过程

  -- 创建插入课程表的存储过程
  DELIMITER //
  
  CREATE PROCEDURE `insert_course`( max_num INT )
  BEGIN
  DECLARE i INT DEFAULT 0;
   SET autocommit = 0;    #设置手动提交事务
   REPEAT  #循环
   SET i = i + 1;  #赋值
   INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6));
   UNTIL i = max_num
   END REPEAT;
   COMMIT;  #提交事务
  END //
  DELIMITER ;
  

• 创建插入学生信息表存储过程

  -- 创建插入学生信息表存储过程
  DELIMITER //
  
  CREATE PROCEDURE `insert_stu`( max_num INT )
  BEGIN
  DECLARE i INT DEFAULT 0;
   SET autocommit = 0;    #设置手动提交事务
   REPEAT  #循环
   SET i = i + 1;  #赋值
   INSERT INTO student_info (course_id, class_id ,student_id ,name ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
   UNTIL i = max_num
   END REPEAT;
   COMMIT;  #提交事务
  END //
  DELIMITER ;
  

• 执行插入存储过程

  CALL insert_course(100);
  CALL insert_stu(1000000);
  

1 字段数值具有唯一性限制

​ 索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此如果某个字段是唯一性的，可以通过创建唯一或者主键索引来通过索引快速确定该条记录。

阿里规定具有业务上具有唯一性的字段，必须设置唯一性索引，哪怕是组合字段

唯一性索引对于insert的性能影响可以忽略不计

2 频繁作为where的查询条件

​ 某个字段如果在select语句的where条件中频繁被用到，那么就需要给这个字段添加索引了，尤其在数据量大的情况下，添加普通索引能够大幅提升数据查询的效率。

​ 如对student_id不添加索引和添加索引进行查询：

-- 1559ms
SELECT * FROM student_info WHERE student_id = 196206;

ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

-- 1225ms
SELECT * FROM student_info WHERE student_id = 196206;

使用的云服务器效果可能收到网络的影响

3 经常Group By和Order By的字段

​ 当需要使用Group By对数据进行分组查询，或者使用Order By对数据进行排序的时候，可以对分组、排序的字段添加索引，这是因为索引本身就是让数据按照某种顺序进行存储和检索的，如果待排序的列有多个，那么就可在多个列上建立联合索引。

​ 比如，按照sid进行分组查询：

SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
LIMIT 100;

-- 不对sid设置索引：2671ms
-- 设置索引：36ms

​ orderby就不演示了，下面呢看一种两个并存的情况：

SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;

Order BY的字段不依赖于Group的字段，因为使用sql_mode包含only_full_group_by所以会报错，因此需要修改sqlmode:

SELECT @@sql_mode;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

​ 此时添加两个普通索引的话，使用EXPLAIN查看执行计划：

ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

CREATE INDEX idx_cre_time ON student_info(create_time DESC);

-- 2595ms

EXPLAIN SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;

key:idx_sid
Extra:Using temporary; Using filesort

可以看到只使用了idx_sid，而没有使用idx_cre_time索引

​ 此时删除普通索引，并创建一个联合索引：

CREATE INDEX idx_sid_cre_time ON student_info(student_id, create_time DESC);

-- 267ms

EXPLAIN SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;
possible_key: idx_sid,idx_sid_cre_time, key: idx_sid_cre_time
Extra: Using index; Using temporary; Using filesort

注意这时候并没有删除两个普通索引，所以possible_key中还是出现了idx_sid，但最后还是使用了联合索引

​ 如果创建一个这样的联合索引：

DROP INDEX idx_sid_cre_time ON student_info;
CREATE INDEX idx_cre_time_sid ON student_info(create_time DESC, student_id);

-- sql执行时间：2658ms

EXPLAIN SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;

possible_key: idx_sid,idx_cre_time_sid, key: idx_sid
Extra: Using temporary; Using filesort

由于联合索引的最左前缀原则，idx_cre_time_sid在创建B+树的时候会先按照create_time的值降序排列再按照sid排序，因此查询的时候只有create_time起作用，而根据sql的执行顺序，首先执行Group By再执行Order By，因此会使用索引sid的索引idx_sid

4 update、delete的where条件列

​ 和select的where条件同理。

5 distinct字段需要创建索引

​ 当需要对某个字段去重的时候，对字段添加索引能够提升查询的效率，这是由于索引本身是对列进行排序存储和检索的，因此去重不需要再进行排序，效率较高。

SELECT DISTINCT(student_id)
FROM student_info

-- 使用索引：474ms
-- 不使用：1367ms

6 多表join连接操作时，创建索引的注意事项

• 多表连接的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常大，严重影响效率
• 应对where条件的字段创建索引
• 应对连接条件ON的字段创建索引，并且，两个字段的类型必须是相同的（否则会使用隐式转换函数导致索引失效）

7 使用列的类型小的创建索引

​ 这里的类型小，指的是该类型表示的数据范围的大小（如TINYINT < MEDIUMINT < INT < BIGINT），这是因为：

• 数据类型越小，在查询时比较越快
• 数据类型越小，意味着数据页能够存放越多的记录，那么磁盘IO的次数也就会越少，也就意味着可以把更多的数据页缓存在内存，从而加快读写效率

这一点对于主键更加使用，因为主键对应的索引不仅用于聚簇索引，非聚簇索引的节点的也需要存储一份记录的主键，使用类型小的主键同样能够提高效率

8 使用字符串前缀创建索引

​ 如果一个字符型的字段很长，那么存储就需要占用很大的空间，也就意味着创建索引的时候：

• 索引在做字符串比较的时候更加费时
• 在索引中占用的空间变大，数据页存放记录减少，IO次数增加

​ 这时候就可以通过截取字符串一部分内容创建索引，这个就叫做前缀索引，这样虽然不能够准确定位到记录的位置，但是可以根据前缀相同的记录主键进行回表操作，来查询完整的字符串。既节约了空间，又减少了字符串的比较时间，还大体能够解决排序的问题。

​ 举个栗子，如创建一张商户表，商户地址较长，就可以进行添加前缀索引：

CREATE TABLE shop(
	address VARCHAR(120) NOT NULL
);

CREATE INDEX idx_addr ON shop(address(12));

​ 问题来了：该截取多少呢？截取多了，导致空间占用并且比较时间长；少了则重复性太高，字段的散列(选择度)降低。这时候就可以通过选择度计算来判断截取的长度：

SELECT COUNT(DISTINCT address(10)) / COUNT(*) FROM SHOP;
SELECT COUNT(DISTINCT address(20)) / COUNT(*) FROM SHOP;
SELECT COUNT(DISTINCT address(30)) / COUNT(*) FROM SHOP;
SELECT COUNT(DISTINCT address(40)) / COUNT(*) FROM SHOP;

值越接近1，意味着选择度越高

索引前缀对排序的影响

​ 如果对字段address添加了长度为12的前缀索引，那么下面的排序将不会使用索引而是使用文件排序

SELECT * FROM shop
ORDER BY address
LIMIT 12;

阿里开发手册索引前缀规定

​ 规定：在varchar类型字段上建立索引，必须指定索引长度，并根据文本的实际区分度来决定索引长度

9 区分度高的列适合作为索引

​ 列的基数指的是一个列中不重复数据的个数，也就是说，在行数一定的情况下，列的基数越大，列的值越分散，越小则越集中，因此应该选择基数大的列建立索引。可以使用下面的区分度计算来判断基数的大小：

SELECT COUNT(DISTINCT a) / COUNT(*) FROM tableName;

扩展：

• 联合索引应把区分度大的列放到前面
• 区分度小的列不适合索引，是因为容易通过创建的索引检索到集中的相同列，还需要回表操作找到具体记录

10 使用最频繁的列放到联合索引的左侧

​ 这样可以少建立一些索引，由于最左前缀原则，可以增加联合索引的使用率

11 在多个字段都需要创建索引的情况下，联合索引要优于单列索引

​ 比如创建的一个联合索引：

ALTER TABLE student_info
ADD INDEX idx_sid_sname_cid(student_id, ...)

​ 则这个索引可以用于sid的查询、sid和sname的查询以及三者的查询，使用频率要高于三个单列索引

限制索引的数目

​ 在实际工作过程中，也要注意平衡，索引的数目不是越多越好。一般情况下，建议单表的索引不超过6个，这是因为：

• 每个索引都需要占据磁盘空间，索引越多，占用的磁盘空间越大
• 索引会影响增删改的性能，因为表中数据更改的同时，还需要对索引进行调整和更新，造成负担
• 优化器在选择如何优化查询的时候，会根据统一的信息，对每一个可以使用的索引进行评估，来生成一个最好的执行计划，如果索引过多则会增加优化器的负担，降低查询的性能

不适合创建索引的情况

• 在where条件下用不到的字段不要使用索引

• 数据量较小的表不要使用索引，一般数据低于1000行则不需要

• 大量重复数据的列不要创建索引，否则会严重降低更新速度，一般重复读高于10%则认为重复读高

• 频繁更新的字段不要创建索引

• 不建议使用无序的值作为索引，比如身份证和UUID，在索引比较的时候转换成ASCII，并且插入的时候会造成页的分裂

• 删除不再使用或者很少使用的索引

• 不要定义冗余或重复的索引，比如联合索引和单列索引造成冗余、主键索引和唯一性索引造成重复