【MySQL】索引

一、认识索引
索引是一种数据结构，用于提高数据库的查询效率。但索引是通过其他数据结构实现的，比如哈希表、B-Tree和B+Tree。
当没有索引的时候，我们查找数据需要遍历整个表，即常说的全表扫描。而有了索引之后，我们会先到索引中去查找相应的数据，然后能得知该数据在数据表中的位置，再直接去读取数据即可。
如下图所示 (这里为便于理解，用搜索二叉树表示索引，实际中的索引并不一定是搜索二叉树实现的) :

索引的优点 :

• 提高查询效率，减少IO时间。
• 利用索引进行排序的效率高，能提高CPU效率。

索引的缺点 :

• 需要额外的空间来存储索引。
• 需要维护索引，因此当改变数据库表时需要花更多时间。

此外，索引是在存储引擎中实现的，因此不同的存储引擎会使用不同的索引。


二、索引结构
1) B-Tree (搜索二叉树 → 红黑树 → B-Tree) :
我们都认识搜索二叉树这种数据结构，但搜索二叉树有个缺点，那就是当数据是按序插入时，搜索二叉树会变成一条链的形状，这时候查找的时间复杂度仍然是O(n)。为解决这个问题，红黑树就被发明出来了，红黑树能够在插入删除的时候自我调节，从而保证整个树一直是平衡的 (而不会退化成链状)，因此保证了搜索效率。
然而，红黑树也有个缺点，那就是当数据太多的时候，二叉树这种形式的层数太深，导致搜索效率不高。因此，就出现了B-Tree，B-Tree即多路搜索树，可以理解为多分支版的红黑树。对于索引来说，了解到这里即可，至于B-Tree具体的实现原理，这里就不展开了。

2) B+Tree :
B+Tree与B-Tree类似，也是多分支版的红黑树，不同的是，B+Tree只有叶子结点会存储数据 (非叶子结点只是用来构造这棵树)，而且B+Tree会将所有的叶子结点都用指针连起来，形成一个单链表。

在MySQL中，对B+Tree进行了一点改进 : 叶子结点不只是一个单链表，而是一个双向链表。
同样的，这里不对具体实现原理进行讲解。

3) 哈希表 :
哈希表应该都比较熟悉了。对于每个数据，会先计算他们在哈希表中的位置，然后插入到相应的位置；如果发生了哈希表，就以链表或红黑树的形式存进去。
哈希表作为索引的优点 : 查询效率高，通常只需要一次查找。
缺点 : 只能查找到一个值，不能按范围查找。

补充 :



三、索引分类
MySQL数据库中主要有4类索引 (数据库层面) : 主键索引、唯一索引、常规索引、全文索引。

当设置了唯一约束之后，会自动创建唯一索引。

在InnoDB存储引擎中，还有两种 : 聚集索引、二级索引。


当执行SQL语句时，会先在二级索引中查找，找到对应的叶节点之后可以得到该数据对应的主键；再根据这个主键，到聚集索引中去查找，得到数据。这个过程也叫做回表查询。

索引是可以关联多个字段的。如果一个索引只关联了一个字段，那这种索引称为单列索引；如果关联了多个字段，那称为联合索引。
需要注意的是联合索引的字段顺序 :


四、索引语法
1) 查看索引、创建索引、删除索引。
2) SQL性能分析 : ① 查询数据库中插入、更新、删除、查找等命令各自的访问频次。
② 慢查询日志 : 在MySQL配置文件中设置了慢查询日志的开关即慢查询日志需要用到的时间 (暂时称它为long_query_time)。慢查询日志是一个日志文件，当开启了慢查询日志的开关之后，所有执行时间超过long_query_time的命令都会被记录到慢查询日志中。通过查看慢查询日志，我们就能明白应该针对那些命令进行性能的优化。
③ profile : 有些指令的运行时间没有超过long_query_time，因此不会被记录到慢查询日志中。这时候就需要profile出场了，我感觉profile就像是更加精细的慢查询日志。
profile同样有一个开关，当开启了profile之后，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。

此外，还能通过profile查询某一条指令的执行时间都花在哪些环节上。

④ explain :

补充 :
子查询的写法 : 参考https://www.bilibili.com/video/BV1Kr4y1i7ru?p=78 (第8分钟左右)。
① 先挨个写出指令，一条一条查询，根据前面指令查询到的结果写出后面指令。② 整合，把后面指令中的、写死的前面指令查询到的结果、替换成前面指令。即可。虽然说起来有点绕，但实际用起来很简单。


五、索引使用规则
1) 最左前缀法则 : 针对联合索引而言。前面提到联合索引的顺序很重要，根据创建联合索引时 (代码中) 字段的顺序，我们把这些字段从左到右进行一个标号，比如第左第一个字段、第二个字段等等。
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
有两点需要注意 : ① 这里的跳过不是指查询指令中的顺序，而是指查询指令中不包含这个字段。至于这个字段放在查询指令中的哪个位置，并不影响。比如下面两条指令对索引的使用是一样的 :

select * from tb_user where profession = '软件工程' and age = 31;
select * from tb_user where age = 31 and  profession = '软件工程';

② 如果查询中没有最左的字段，那么就不会用到索引。

2) 索引失效的情况 :
① 范围查询 : 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。但如果使用的是>=或<=，那就不会有这种情况。


② 如果在索引列上进行运算操作，对应列的索引将失效。
③ 如果在使用字符串类型字段时，忘记加引号，索引将失效。
④ 模糊查询 : 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。如下面三条指令，只有第一条指令的索引生效。
⑤ or连接条件 : 当使用or连接的条件时，只有or左右两侧的字段都有索引时，索引才会生效。
⑥ 数据分布影响 : 如果MySQL觉得使用索引比全表更慢，则不会使用索引。

3) SQL提示 : 当一个字段存在多个索引时，比如同时存在单例索引和联合索引，MySQL会自动选择使用哪一个索引。如果要指定使用哪个索引，需要借助MySQL的SQL提示来完成。
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。主要有 :
① use index : 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
② ignore index ：忽略指定的索引。
③ force index : 强制使用索引。

4) 覆盖索引 : 覆盖索引指，我们要查询返回的字段就在索引中，仅需要通过查询索引就能得到我们想要的数据信息、不需要再进行回表查询。
补充 :
① 回表查询 : 查询完索引得到对应的主键之后，还需要到主键索引中进行查询以获得我们需要的信息。

② explain信息最后一列的解释 :

5) 前缀索引 :
① 如何选择前缀长度 :
6) 单列索引与联合索引 :
通常来说，联合索引能减少回表查询的几率，因此更建议使用联合查询。

六、索引设计原则



参考资料 :

• 黑马程序员教程 : https://www.bilibili.com/video/BV1Kr4y1i7ru?p=71