Mysql索引

1.索引介绍

1.1 什么是索引

你面前有一本书，当你想查找这本书的内容时，我想大部分人都会选择先去查看这本书的目录，然后根据目录到指定页去查找所需的内容。在这个过程中，书本的目录就充当了索引的角色。

在MySQL中，索引就可以说是一种帮助存储引擎提高获取数据效率的数据结构，与前面的例子类比一下就是，索引就是数据的目录。

1.2 索引的数据结构

前面介绍索引本身是一种数据结构，但数据结构也分很多种，下面一一介绍

1.2.1 哈希表

哈希表的底层结构是 Key-value模式，以键值对的方式存储数据，使用哈希表存储数据的情况下，Key可以存放索引列，value可以存放行记录或者磁盘地址，在等值查询的情况下效率很高，时间复杂度为O(1)。但在范围查询下，则会走全表扫描，效率极低。

1.2.2 二叉查找树

二叉查找树的特点是一个节点的左子节点全部小于这个节点，右子节点全部大于这个节点，这样独特的结构，让我们在查询数据时和插入数据时，就只要将数据和对应节点做比较就行。而且底层是通过二分查找去实现的，所以时间复杂度只需要O(logn），效率也不低。

但二叉查找树有个致命弱点：如果每次插入的都是当前最大值，它会退化成一条链表，查询退化为 O(n)。

1.2.3 平衡二叉树

上面介绍二叉查找树时，引出一个问题，在极端情况下，二分查找树会退化为链表，时间复杂度也相应退化成O(n)，所以这就引入了平衡二叉树。

平衡二叉树主要是在二叉查找树上增加了约束，保证左子树和右子树的高度差不会超过1，保证时间复杂度一直是O(logn），也不会出现极端情况退化成链表的情况，因为他会维持自平衡。

但是它也存在缺陷，随着插入元素变多，而导致树的高度变高，导致数据库磁盘I/O操作次数变多，会影响整体数据查询效率。

1.2.4 B树

虽然上面的平衡二叉树能保证时间复杂度不变，但它本质上仍然是个二叉树，就导致每个节点最多只有2个子节点，从而导致树的高度不断堆加，增加磁盘I/O次数，影响数据查询效率。

基于此，B树就解决了二叉树的节点问题，它不限制一个节点只能有2个子节点，而是允许有多个子节点，从而降低了树本身的高度，进而降低了数据库磁盘I/O的操作次数，相比二叉树提高了数据查询的效率。

虽然B树解决了二叉树带来的高度问题，但是B树的每个节点都会存储数据，这就会导致在进行数据库查询的时候会读取到无用的索引数据，进而导致需要更多的磁盘I/O操作读取到有用的索引数据。这样不仅降低了数据查询的性能，而且还对内存不友好，因为那些无效数据也会被加载进内存。

1.2.5 B+树

基于上述情况，B+树出现了，B+树就是对B树的一个升级。

相比B树，B+树的非叶子节点只会存放索引数据，只有叶子结点才会存放实际数据（也就是索引+记录），并且所有的索引记录都会出现在叶子结点，构成一个有序链表。这就解决了原本B树存在的无效数据记录会过多占用内存资源的问题。

而MySQL中索引的数据结构就是采用了B+树。在B+树中，非叶子节点只会存放索引数据，这就意味着在数据扫描过程中即便扫描到无效数据，也只是把索引数据加载进内存，大大降低了对内存资源的占用。并且B+树的叶子结点是通过链表来连接的，这也就意味着在数据查询中，不需要每一次都从根节点重新查找，大大提高了查询的效率。

总而言之，，B+树通过减少磁盘I/O中无效数据的载入+更少的磁盘I/O操作的优势获得了MySQL的青睐，并将其作为索引的数据结构。

2. 索引的使用

2.1 索引的使用场景

索引的存在提高了数据库的查询速度，基于这个要点，接下来分析一下索引的使用场景。

什么时候需要创建索引

1、字段具有唯一性限制的，比如商品编码。

2、经常需要作为where查询条件的字段，这样可以提高表的查询效率，如果查询字段不止一个，可以考虑建立联合索引。

3、经常用于group by 和 order by的字段，这样就省去了排序的操作，因为建立索引之后，在底层是已经排序好了。

什么时候不需要创建索引

1、表中数据太少的情况下，不需要创建索引。比如10条、50条这种情况，这时候走全表扫描效率还比走索引匹配要高。

2、经常需要做更新的字段不需要创建索引。因为一个字段经常更改，数据库去维护索引结构也需要成本，所以这种情况不考虑建立索引。

3、存放大量重复数据的字段不需要创建索引。比如性别字段，基本就是男和女，数据量非常平均，那这时候如果走索引查询，这个过程除了在性别字段这个索引树上查一遍，还要回表去主键索引在查一遍。这个性能开销甚至还不如直接走全表扫描。

4、where条件里用不到的字段也不加。因为索引本身也是占磁盘空间的，既然都用不到，那这个索引也没必要创建出来。

2.2 索引优化

上面介绍了索引的常见使用场景，接下来介绍常见的索引优化方案。

前缀索引优化

这种方案通过截取某个字段中字符串的前几位建立索引，从而降低索引字段大小，增加索引查询效率。比如把电话号码这种大字符串字段作为索引，如果按照原串长度作为索引，那索引字段就太大了，所以通常截取前几位作为索引字段存进去。

不过这样也存在缺陷，因为索引字段不全，所以就必须走一次回表去获取完整数据。由于这个原因，所以前缀索引也不适用于order by 和 group by，因为排序的依旧并不完整。

覆盖索引优化

这种方案是指查询后返回的字段全是索引字段，不需要通过回表操作去获取所有完整信息，降低了回表带来的I/O操作。

比如把age字段作为索引，然后查询也只查询age，这种时候不需要回表，直接能在索引树上查询到记录； 如果要查询多个字段，通常可以把这多个字段建立为联合索引，然后直接查询。

主键索引采用自增模式

这种方案也好理解，因为主键索引在B+tree的叶子结点直接存放的就是索引数据+行数据，把主键索引设为自增的，数据的存放也就是按顺序添加，对内存空间的使用非常友好，查询效率也快，维护成本也低。

索引列设置非空约束

索引的目的是为了提高数据的查询效率，基于此，在创建索引的时候需要去对索引字段设置非空约束。因为null这个字段本身无意义，如果把null作为索引数据存放，有点浪费内存空间了，所以为了更好的使用索引，最好给索引列加非空约束。

2.3 索引失效

上面介绍了索引的使用场景，但是索引创建成功了就一定会被成功使用吗 ？索引它也存在失效的情况的，在索引失效的情况下，数据库会放弃使用索引，走全表扫描，这样就大幅降低了查询效率。所以我们要尽可能避免索引失效的情况。

常见的索引失效的情况

1、查询条件中索引列参与运算、进行函数操作、类型转换都会导致索引失效。

2、索引字段使用like进行模糊匹配时，在like %… ，和 like %…% 这种左模糊匹配和左右模糊匹配的方式下，索引会失效。但是进行右模糊匹配的情况下，索引会正常使用。

3、where语句中，如果or条件的左右中有一个不是索引字段，索引也会失效、

4、如果索引字段是字符串类型，在查询时未加单引号也会导致索引失效。举个例子：

select * from user where phone = 13333556

phone字段为字符串类型，但此时索引失效。因为 MySQL 会进行隐式的数据类型转换，在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。对于上述的案例，phone字段因为未加单引号，数据库会将这个字符串字段隐式转化为整数类型，也就是说在这个过程中，phone这个索引字段参与了函数操作，而前面也介绍过，如果索引字段参与函数操作，会导致索引失效。

5、在使用联合索引时，未遵循最左匹配原则时，索引也会失效。比如创建一个（a , b, c)的联合索引，这时候索引在B+树中的结构就是按a、b、c顺序排列 ，也就是说在一个节点里按顺序存放了a、b、c三组字段数据，后续进行查询时也是按照字段顺序分别进行匹配（where顺序不重要，主要和创建索引时的顺序有关）。也就是说在联合索引的情况下，数据是按照索引第一列排序（在这个例子里就是先匹配a），第一列相同才会走第二列，如果跳过第一列数据，会导致数据库查找不到指定索引数据从而放弃使用索引，于是索引失效。

2.4 索引的优缺点

索引也不是万能的，它有长处，那它也存在弊端，下面介绍一下索引的优缺点。

优点：

1、提高了数据查询的效率，大幅降低了磁盘操作I/O的成本。

2、索引本身是有序的，所以也降低了数据排序的成本。

缺点：

1、索引本质上是拿空间换时间，所以它也会占据磁盘空间，如果索引数量多起来，占据的磁盘空间也越大。

2、索引的创建和维护会耗费时间，索引越大时间越长。

3、索引提高了查询效率，但相对的，对数据的更新就没那么友好了，因为每次更新数据，都要重新维护B+树的结构，很耗费性能。