索引设计的原则

索引的设计原则

适合建索引的场景

• 字段本身有唯一性的要求
• 经常作为where过滤条件的字段
• 经常group by 或order by的字段
• distinct字段
• 散列性高的字段

建索引应注意

• 使用类型小的建索引

• 字符前缀建索引选取长度
  针对字符串类型的前缀索引，前缀截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字段的散列度(选择性)会降低。因此要根据散列度的变化来确定前缀索引的长度。控制索引长度增大，当散列度变化率稳定时截取稳定曲线的最小长度即可。

  # 散列度公式
  count(distinct left(列名, 索引长度))/count(*)
  

• 多表 JOIN 连接操作时
  连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。
  对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。
  对用于连接的字段创建索引，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

• 使用最频繁的列放到联合索引的左侧，原因是最左匹配原则
  联合联合索引从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停 止匹配

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

限制索引的数目

要注意平衡，每张表上的索引数量限制在不超过6个，索引用b+树存放本身就会占据空间，而增删改的执行重建索引要消耗大量的时间成本。有很多个索引可以作用在同一个列上时，MySQL优化器生成执行计划时间会比较长。

不建索引的情形

• 索引作用的列不经过where、order by、group by过滤
• 表数据量小（小于一千条数据）不适合建索引，查索引的时间比直接找结果的时间还要长
• 散列度低的数据不建索引，如性别，只有男的和女的，走索引去过滤性别属性纯属多余
• 经常更新的字段不应建索引
• 使用频率低的字段
• 不建议无序值做索引，比如UUID、MD5等
• 冗余重复定义索引，如联合索引能覆盖列又同时定义单列索引，又如主键列指定唯一索引

联合索引与最左前缀匹配原则探究

联合索引本质上来说也是一个B+树，但是不同的是，单列索引的B+树的键值数量是1（即一个键值+一个页指针），但是联合索引的键值数量是大于等于2，如下图三行绿色是联合索引，粉色是记录的主键。最左前缀匹配原则就是说按次序连接where子句中的的过滤条件与联合索引进行匹配，直至出现范围匹配停止匹配。接下来根据基于B+树索引数据结构，实验一下具体的索引执行情况。

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stuno` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `classId` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3246739 DEFAULT CHARSET=utf8mb3

# 添加联合索引
mysql> alter table student add index idx_stuno_age_classId(stuno, age, classId);

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

# 测试数据
mysql> select * from student where stuno  = 1246740;
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
| 1246739 | 1246740 | jVbvXC |   86 |     598 |
+---------+---------+--------+------+---------+

# 测试使用联合索引查找单列
mysql> explain select * from student where stuno  = 1246740;# 匹配联合索引
mysql> explain select * from student where age  = 86; # 不匹配，符合最左前缀匹配原则
mysql> explain select * from student where classId = 598; # 不匹配，符合最左前缀匹配原则

# 测试部分列命中的情况
explain  select * from student where stuno = 1246740 and age = 86; 
explain  select * from student where stuno = 1246740 and classId = 598;
explain  select * from student where age = 86 and classId = 598;

# 添加单列索引
mysql> alter table student add index idx_stuno(stuno);
mysql> alter table student add index idx_age(age);
mysql> alter table student add index idx_classId(classID);

# 查找单列
explain select * from student where stuno  = 1246740; # 匹配单列索引和联合索引，优化器选择了联合索引
explain select * from student where age  = 86; # 只匹配单列索引，符合最左前缀匹配原则
explain select * from student where classId = 598; # 只匹配单列索引，符合最左前缀匹配原则

这里查stuno 可以走单列索引也可以走联合索引，但实际走了联合索引，验证了联合索引优于单列索引的原则

# 打乱WHERE条件的and顺序查联合索引
explain select * from student where stuno=1246740 and age = 86 and classId = 598; #使用联合索引
explain select * from student where age = 86 and classId = 598 and stuno=1246740; #使用联合索引

验证了匹配查询条件匹配时，优化器自动调整and顺序

# 验证下最左匹配原则
explain select * from student where stuno=1246740 and age = 86; #匹配
explain select * from student where age=86 and stuno=1246740; #匹配，优化器自动调整and顺序
explain select * from student where age = 86 and classId = 598; #不匹配
explain select * from student where stuno=1246740; #匹配

范围查找停止的情形
mysql学习（9）：联合索引和多个单列索引的使用区别详解_加联合索引和分开加索引有啥区别-CSDN博客

# 建立了a、b、c的联合索引
select * from table_name where a = 1 and b < 3 and c < 1 

这个查询中只有a列和b列使用到了索引，而c列没有使用索引，因为根据最左匹配查询原则，遇到范围查询会停止。