在谈论mysql的索引之前，我们先来了解一下MySQL的索引结构。
-
MySQL的索引数据结构选型是B+Tree，5.5版本以前使用的是B-Tree。那么二者有何区别呢？
一、MySQL的索引数据结构
1.1.1 什么是AVL树？
首先平衡二叉树我们比较了解，即在二叉查找树的基础上，控制其左右两边的子树高度。降低树的高度，这样有助于提升查询速率。
那么B树就在平衡二叉树的基础上再进行改进。由于AVL树尽管平衡了左右子树的高度尽可能小，但是一个结点只能保存一个数据，所以AVL树的做法在这个大数据量的时代仍然是杯水车薪。
1.1.2 B树的出世
既然一个结点只能保存一个数据行不通，那就将多个数据保存到一个结点上。于是就有了B树的产生，B树的每个叶子结点会保存多个数据。但是一个结点中的数据并不是随意排列的，而是也根据的是左结点小于根结点，根结点小于右结点。
如下图：
B树是为磁盘等外部存储设备设计的一种平衡查找树。由图中可以了解到，每个结点都是以磁盘块为单位读入内存的，位于同一个磁盘块中的数据会被一并读出来。
在操作系统的存储管理中，一类管理方式为分页存储管理。而InnoDB中也是按页将数据调入内存，页是磁盘管理的最小单位。在InnoDB中默认的页大小为16KB。但是由于磁盘块的存储空间没有页那么大，所以每次申请磁盘空间都是调入连续的磁盘块。
1.1.3 结点结构
-
指针 + 键值 + 数据
1.1.4 B树的优缺点
优点：
（1）一个结点可以储存多条数据
（2）树的高度比较小，有利于提高查询速度。
（3）每个结点可以根据实际情况包含大量的关键字信息和分支
缺点：
（1）若数据是文本，或者占用储存大小比较大的时候，每个结点能储存的数据条数将会减少，因为页的大小是固定的，如果一条数据就占用了页的空间，则一个结点只能保存一条数据，又回到了平衡二叉树的问题上。
（2）I/O操作次数仍然比较高，不利于提高查询性能。如果像上述的问题发生，一条数据一个结点的情况发生，那将很大可能导致查询速度慢。
-
由上述可见，B树仍然存在查询速率不高的情况，所以引入B+树。
1.2.1B+树
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
1.2.2 引入B+树能解决什么问题？
针对B树存在的缺点——不确定每条数据中的数据占用大小，若占用过大，则会导致I/O操作次数没有改善的情况。B+树去掉了每条数据的数据域，保留指针和键值。因为键值大多是int或者bigint这样的整形数据类型，要么占个四个字节，要么占用八个字节。去掉了数据域就相当于去掉了B树的不确定因素。而B+树是将所有键值和数据保存在叶子结点中。
如下图：
1.2.3 B+树的优点
非叶子结点将非键值的数据都去除掉，除了叶子结点外只存键值和指针。这样做带来的好处是可以让每个页都存储尽可能多的键值，缩小树的高度，减少对磁盘的I/O操作，提升查询的速率。
-
就通过上面的叙述还不是很直观的感受到其查询速率的提升，通过下面的例子就能明白！
首先，按页的大小为16KB来算，一般建表定义的主键类型为int，占用四个字节；指针一般为4个或者8个字节。按8个字节算的话：
一页中（即一个非叶子结点）大概能存储16KB/（8B+8B）=1K条数据，1K大约为1000条数据
则一个树的深度为3的话，B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条数据记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作，由此可见，使用这种方式的索引可以带来极大提升的查询效率。
1.2.4 B+树和B树的不同之处
（1）非叶子结点只储存键值信息（即主键）
（2）所有叶子结点都有一个链指针
（3）数据记录都存放在叶子结点中
二、MySQL的索引
MySQL的索引主要可以分为聚集索引和辅助索引，上述的B+树结构就是聚集索引的结构。
-
辅助索引和聚集索引的区别？
① 辅助索引的叶子结点不是包含键+数据；而是包含当前索引键+当前行对应的主键。
因此我们可以知道，使用辅助索引的话就会引发回表。在使用辅助索引构建的B+树中，找到对应的主键值，再根据主键值去聚集索引的B+树中寻找对应的那条数据记录。显然这样方式的查询速率比直接使用聚集索引更慢。
2.1 索引的具体分类：
① 主键索引（关键词：PK_）
在一张表创建的时候需要指定其中的列作为主键，方便构建出表的索引树，每张表必须有一个聚集索引树，如上述所说的，辅助索引也要依赖于聚集索引才能实现。
若用户没有具体指定主键的话，InnoDB会自定帮你创建一个不可见的、长度为6字节的row_id，并且row_id是由InnoDB维护全局的disctsys.row_id，每次插入一条数据时，都会让全局row_id+1（row_id将作为未定义主键的表的主键id）
② 唯一索引（UK_）
唯一索引和主键索引唯一的区别就是唯一索引运允许字段为空值，而主键索引不能存空值。
③ 普通索引（idx_）
普通索引常常是用来进行条件快速查找的，除此之外对字段本身没有要求。
④ 组合索引
组合索引可以看做是多个列的普通索引。组合索引通过是遵循最左匹配原则，但是在特殊情况下，不遵循。
⑤ 全文索引（倒排索引）
背景：全文索引是搜索引擎的关键技术，在mysql5.6版本以前，InnoDB是不支持的，5.6版本之后才支持全文索引。
实现原理：与like和正则表达式不同，全文索引是类似于将文章中的每个词进行建立索引，记录其存在的位置id。
优点：相较于like和正则表达式，其性能更胜一筹。前者是需要对所有行进行匹配的；而全文索引就像上面所说，是建立了一个辅助表，表中的信息为词和其存在的位置id。因此，在查询性能方面会更好。
缺点：可能存在精度问题，例如在百度时，我们输入的信息可能只是部分匹配。得到的匹配结果，可能会将我们输入的关键字拆分开来。如下图，我们输入的是我爱java，但是查询的结果关键字不是连贯的。这就是全文索引里的分词机制，从而导致的精度问题。
三、提速神器——索引
-
上述我们了解了mysql的索引结构，mysql的索引，那现在就来谈谈索引给查询速率带来的收益吧！
*面试中的常见问题：怎么提高sql的查询速度？
我：闭着眼睛先把建立索引说了。（当然还有其他方法，这里就不细说）
-
造成sql查询速度慢的根本原因：对磁盘的I/O操作过于频繁。而造成这一局面的原因有很多，可能是索引建立的不合适，也可能是sql的写法有问题或者是数据量大却不走索引等等。接下来我们就先来说一说索引的命中问题。
3.1 索引命中
3.1.1 回表和索引下推
-
回表
回表的发生：在使用普通索引（辅助索引）查到数据时，如果所查询的数据不包含在索引字段或者主键时，就会产生回表（即拿着查询到的主键，再去聚集索引树查找出行数据）
显而易见，当发生回表时，查询的速度肯定是不如没有回表的。
举个例子：当我们创建一张表，字段包含no（主键）、age、name（普通索引字段）。
select * from 表名 where name = ‘张三’
像上面这条sql语句，查询的age字段不在普通索引的索引树中，就会先找到主键值，然后再去聚集索引树中根据主键值找到相应的行数据。这就是回表，为了避免回表造成的时间浪费以及资源消耗，如果我们就需要查询其中的no的话，则最好像如下方式书写：
select no from 表名 where name = ‘张三’
这样就不会造成回表，因为要查询的no为主键，所以只需要在普通索引树中找一边即可，也称作索引覆盖。
总结：
针对上面对于回表的介绍，我们应该在写sql语句时注意，避免对select后面加 * ，若只需要其中的部分字段就写具体字段名。
同时平时我们在使用mybatisplus这种框架时，其内部使用的单表查询自带的sql工具使用的就是select * 这样的sql，所以如果不是要快速开发完成，最好手写sql语句。
减少回表即减少了对磁盘的IO操作，提升查询速率。
-
索引下推（ICP）
mysql5.6之前没有索引下推，若需要使用到两个或两个以上的索引字段，通过匹配完一个索引字段后回表找到该条记录，并将此纪录返回给mysql的server层。在server层再对第二个索引字段进行判断，若不符合条件，则继续下一个条记录。重复以上步骤。
按联合索引来说，比如A、B字段都是索引字段，在构建的B+树时，结点信息中有保存两个索引字段的信息，但是mysql的存储引擎却只判断了索引字段A，通过回表找到数据记录，然后将数据给server层，由server层对B进行判断。这显然是很浪费时间的做法，在存储引擎中就可以对两个字段进行判断，然后再回表找出数据记录返回给server层这种做法才是比较明智之举。
mysql5.6之后有索引下推后，存储引擎可以在对联合索引(二级索引 || 非主键索引)遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，正如上一段的最后阐述。
创建一张表，字段为：no、name、age、sex。设联合索引的索引字段为name、age。
select * from 表名 where name > "张三" and age = 3
话外知识点：范围判断查询到的数据量占表低于30%，否则MySQL优化器将不选择走索引。
MySQL5.6之前：
1、当匹配了第一个name，返回数据对应的主键
2、根据主键回表查出这条数据，返回给server层，由server层对查询到的数据进行判断 age = 3 。
3、若不等于3，则回到第一步，继续匹配下一个name相等的数据，如此重复。
存储引擎只会判断一个索引字段，然后交给server层做其余判断，如下代码：
[mysql> explain select * from user where name>'张三' and age=3
***************************1.row ***************************
id:1
select_type:SIMPLE
table: user
type: ref
possible_keys: name
key: name
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.01 sec)
根据Extra是using where可以看出，表示数据在 server 层还进行了过滤操作。这个过滤操作就是上述第二步，判断age是否等于3。
MySQL5.6之后：
1、由存储引擎直接判断name和age两个字段，找到符合条件的数据
2、定位到索引树的叶子结点，查到该数据的主键值
3、根据主键值进行回表查找
4、将数据返回给server层
运行结果如下：
[mysql> explain select * from user where name>'张三' and age=3
***************************1.row ***************************
id:1
select_type:SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: name
key: name
ref: const,const
rows: 1
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
原本遇到范围判断，age是不会走索引的，但是可以看到同样一个sql运行的结果不一致，这个结果的Extra显示为Using index condition，这就代表使用了索引下推，就是因为在存储引擎就将name和age进行判断过滤（所以age实际上还是走了索引），然后再将过滤后的数据进行回表。
3.2 索引失效
-
索引列参与计算
-
索引列发生类型转换
-
索引列使用函数处理
-
索引列使用模糊方式判断
-
like除了 %a、%a%，其余可以走索引
-
遇到>、<、>=、<=这类的范围判断，若查询得到的数据占总数据量30%以下，才可以走索引，否则将不能走索引
-
若遇到 != 非精确查询也无法走索引。
-
sql语句中出现or、not null 、not in、not exist等关键词
-
若为联合索引，则需要遵守最左匹配原则（但是有时候也会不满足最左匹配原则依旧可以走索引）
参考文章：
掘金：避免回表，引入索引下推|提高索引命中率 | 提前下班啦 - 掘金
掘金：探索MySQL是否走索引(一)——范围查询一定走索引吗？ - 掘金
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
