MySQL索引补充

说明

笔者声明：索引这部分理解的内容其实也挺广泛，但是有些部分认知不够深刻，也不够清楚，所以在这里补充

其实写到现在，有时候感觉也没有刚开始整理八股文的时候那么困惑，相反，可能越来越想认识原理性的内容，应该是好事😄

索引的分类

说明：确实挺吃亏，直接说你知道有哪些索引可以说一摊东西，但是如果让你说分类，就比较模糊了

• 按「数据结构」分类：B+tree系引、Hash系引、Full-text系引
• 按「物理存储」分类：聚簇索引(主键索引)、二级索引(辅助索引)
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引
• 按「字段个数」分类：单列索引、联合系引

关于联合索引的范围查询（联合索引的存储结构决定 了最左前缀原则）

这里主要根据小林那里提供的理解方式，感觉理解非常深刻

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。

这种特殊情况就发生在范围查询。联合索引的最左前缀原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

范围查询有很多种，那到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢?

接下来，举例几个范围查例子。

Q1: select * from t_table where a > 1 and b = 2;

由于联合索引(二级索引)是先按照a字段的值排序的，所以符合a > 1条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合a > 1条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合a > 1条件位置。所以a字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合a > 1条件的二级索引记录的范围里，b字段的值是无序的。比如下面这三条记录的a字段的值都符合a > 1查询条件，而b字段的值是无序的:

• a字段值为 5 的记录，该记录的b字段值为 8
• a字段值为 6 的记录，该记录的b字段值为 10
• a字段值为 7 的记录，该记录的b字段值为 5

因此，我们不能根据査询条件b = 2来进一步减少需要扫描的记录数量(b字段无法利用联合索引进行索引查询)。所以在执行 Q1这条查询语句的时候，对应的扫描区间是(2,+∞)，形成该扫描区间的边界条件是a > 1，与b = 2无关。（这就是索引给本次查询找到的「开头」）

因此，Q1这条查询语句只有a字段用到了联合索引进行索引查询，而b字段并没有使用到联合索引。

Q2: select * from t_table where a >= 1 and b = 2;

Q1和Q2的区别在于a >= 1

虽然在符合 a >= 1条件的二级索引记录的范围里，b字段的值是「无序」的，但是对于符合a = 1的二级索引记录的范围里，b字段的值是「有序」的。于是，在确定需要扫描的二级索引的范围时，当二级索引记录的a字段值为1时，可以通过b = 2条件减少需要扫描的二级索引记录范围(b字段可以利用联合索引进行索引查询)。即从符合a = 1 and b = 2条件的第一条记录开始扫描，而不需要从第一个a字段值为1的记录开始扫描。（这就是索引给本次查询找到的「开头」）

所以，Q2 这条查询语句a和b字段都用到了联合系引进行索引查询。

Q3: select * from t_table where a between 2 and 8 and b = 2;

Q3这样要根据数据库对between and的设计不同去分别讨论，MySQL对between and的设计是>= A && <= B的，所以其实相当于看到了=因此跟Q2一个道理。也就是说「边界值」是=所以两个字段都用到了联合系引进行索引查询。

Q4：select * from t_user where name like `j%` and age = 22;（联合索引是name和age）

其实看了上面Q2Q3有点类似，只要有了「边界」就可以利用边界条件用接下来的字段接着筛选

同样还是根据前缀为‘j’的name字段的二级索引记录都是相邻的，于是可以定位到符合前缀为‘j’的name字段的第一条记录，接着扫描直到某条记录的name前缀不为‘j’为止

还是和前面一样，看下图秒懂（小林真牛🐮）

所以，Q4 这条查询语句a和b字段都用到了联合系引进行索引查询。

综上所示，联合系引的最左匹配原则，在遇到范围查询(如>、<)的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于>=、<=、BETWEEN、 like前缀匹配的范围查询，并不会停止匹配

详解索引失效

索引失效🤣的常见六种情况：

这里有个前置知识，虽然MyISAM和InnoDB都支持B+Tree索引，但是存储结构略有不同

• InnoDB ：索引的叶子节点保存数据本身
• MyISAM：索引的叶子节点保存数据的物理地址

01对索引使用左或者左右模糊匹配

如果使用 name like'%林方式来査询，因为査询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

02对索引使用函数

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了

不过，从MySQL8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。举个例子，我通过下面这条语句，对length(name)的计算结果建立一个名为 idx_name_length 的索引。

alter table t_user add key idx_name_length((length(name)));

03对索引进行表达式计算

和02一个意思。因为索引保存的是索引字段的原始值，而不是表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

04对索引隐式类型转换

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

首先了解MySQL的「数据类型转换规则」

如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是1

如果规则是 MVSQL会将自动「数字」转换成「字符串}，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较(按ascii码)，那么"10"字符串相当于"1”和”0”字符的组合，所以先是拿“1“ 字符和“9”字符比较，因为“1”字符比“9”字符小，所以结果应该是 0。

事实证明，MySQL在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较！！！

因此：select * from t_user where phone = 130000001;这条语句中，phone是字符串字段（普通索引），因此会把字符串转为数字，相当于select * from t_user where CAST(phone AS signed int) = 130000001;，可以看到CAST函数作用到了phone（索引字段），因此就是对索引进行函数计算，因此失效！！！

而select * from t_user where id = "1";这条语句id是整形，因此会把字符串“1”转换为数字，语句相当于select * from t_user where id = CAST("1" AS signed int);，因此函数作用于了输入参数，这是没有影响的，因此可以走索引

05对索引非最左匹配

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

06WHERE字句中的OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR后的条件列不是索引列，那么索引会失效。举个例子，比如下面的查询语句，id 是主键，age 是普通列，从执行计划的结果看，是走了全表扫描

select * from t_user where id = 1 or age = 18;

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。要解决办法很简单，将 age 字段设置为索引即可。

可以看到此时的执行计划就变成了type = index_merge， index_merge 的意思就是对id和age分别进行了扫描，然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描

count(*)和count(1)有什么区别，count(字段)呢

可能会好奇为什么这个问题也要放在「索引」这个部分，但是count这个函数也是和索引相关（执行过程），仍然基于InnoDB引擎阐述。

结论：按照性能来看：count(*) = count(1) > count(主键字段) > count(字段)

count()是什么

count是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。假设count函数的参数是字段名，如下:

select count(name) from torder;

这条语句是统计「t_order 表中，name 字段不为 NULL 的记录」有多少个。也就是说，如果某一条记录中的name 字段的值为NULL，则就不会被统计进去。

再来假设count() 函数的参数是数字1这个表达式，如下:

select count(1) from t_order;

这条语句是统计「t_order 表中，1 这个表达式不为 NULL 的记录」有多少个。1 这个表达式就是单纯数字，它永远都不是 NULL，所以上面这条语句，其实是在统计 t_order 表中有多少个记录。

count(主键字段)和count(字段)执行过程

在通过count函数统计有多少个记录时，MySQL 的server层会维护一个名叫count的变量。server层会循环向 InnoDB 读取一条记录（这里就是和count(*)或者count(1）不同的部分)，如果count函数指定的参数不为NULL，那么就会将变量count加1，直到符合查询的全部记录被读完，就退出循环。最后将count变量的值发送给客户端。

InnoDB 是通过 B+树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的I/O成本比遍历聚簇索引的小，因此「优化器」优先选择的是二级索引。

懂了count(主键字段)那就自然明白count(字段)的区别，普通字段没有索引的时候会用「全表扫描」的方式计数，因此它的效率最低。

count(1)和count(*)执行过程

InnoDB 循环遍历聚簇索引(主键索引)，将读取到的记录返回给server层，但是不会读取记录中的任何字段的值（所以通常会说count(1)执行效率会比 count(主键字段) 高一点），因为 count 函数的参数是 1，不是字段所以不需要读取记录中的字段值。参数1很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将count 变量加 1。

对于count(*)，很多时候下意识会认为他会像select *一样会变成读取所有字段，但是count(*)和count(1)执行过程基本一样，不会有性能差异（这是官方的解释）。

对于count(1)和count(*)的一个优化：如果表里有二级索引时，InnoDB循环遍历的对象就二级索引而不是主键索引（就是因为走二级索引扫描的时候磁盘的I/O操作成本更少，优化器决定）

小结

count(1)，count(*)， count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些；

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。