Mysql-索引

一、算法介绍

Mysql索引为什么采用的B+数？我们通过比较哈希表、二叉树、红黑树、自平衡二叉树、B树、B+树的优缺点来推算出原因。

1.1、哈希表

定义：也叫散列算法，即把任意值（key）通过哈希函数变换为固定长度的key值，根据这个固定长度的值找到数据的物理地址，最后通过物理地址进行具体数据查询的数据结构。

哈希碰撞：当不同的key经过哈希函数计算后得到一样的结果，发生哈希冲突。采用链地址法解决，即用链表把碰撞的数据连接起来。

优点：哈希索引检索速度非常快。从算法时间复杂度看，哈希算法时间复杂度是O(1)。

缺点：无法友好的实现范围查找。

总结：范围查找是数据检索常用的场景。如：select * from user where id >3。如果使用hash算法，思路是：先一次性把所有数据查到并加载到内存，然后在筛选目标范围内的数据。这样的操作根本没有效率可言。

所以，hash算法实现的索引，可以快速检索精确值查询的数据，但无法实现数据高效的范围查询。不适用于Mysql索引结构。

以下是通过哈希函数： n % 13后得到的hash链表。在线演示地址：Open Hashing Visualization (usfca.edu)

1.2、二叉树（BST binary search tree）

特点：节点按从左往右的顺序排列，时间复杂度是O(lgn)，比如下图，我查找51，只需3次，相当于一半。并且因按从左往右的顺序排列，所以对范围查找也很友好。

缺点：如果数据是递增的，则会导致极端的情况--成为线性链表，时间复杂度也成为了O(n)，如下图二，而Mysql主键递增是很常见的，就会出现这问题。

因二叉树存在的问题是不平衡，所以通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，那就能保持二叉树的最佳查询性能。基于这种自动调整二叉树的平衡状态，诞生了AVL树和红黑树。

推荐的文章：一文带你搞定【二叉树】 - 知乎 (zhihu.com)

1.3、红黑树（Red-Black Tree）

红黑树是一颗会自动调整树形态的树结构。当二叉树处于不平衡状态时，红黑树会自动左右旋转节点及节点变色，调整树的形态，使其保持基本的平衡状态。从而保证查询效率不降低。

时间复杂度：O(logN)。

问题：针对主键自增的情况，会出现极其右倾的趋势，如果节点更多，右倾就会更加明显，本质上依旧没有完全解决二叉树不平衡的问题。而表的主键一般是数百万数千万。红黑树的查询效率依然不能接受。

解决方法：红黑树存在右倾趋势，那么考虑一种更严格的自平衡二叉树AVL树。但由于AVL树是个绝对的平衡二叉树，因此调整二叉树形态时，消耗的性能会更多。

1.4、自平衡二叉树（AVL树）

优点：无论节点怎么增加，AVL树不存在红黑树的右倾问题。即，大量的顺序插入不会导致查询性能的降低，从根本上解决了红黑树的问题。

时间复杂度：O(logN)，不存在极端的低效查询情况，支持范围查找、数据排序。

那之所以不采用AVL树的原因：

1）如果使用AVL树，每个节点只存储一个数据，每次磁盘IO只能取出一个节点上的数据，IO次数将直接导致性能下降。

2）没有很好利用操作系统和磁盘数据交换特性，也没有利用好磁盘IO的预读能力。

3）操作系统和磁盘之间一次数据交换是以页为单位，一页大小是4K，即每次IO操作系统会将4K的数据加载进内存。

4）设计数据库索引的时候首要考虑因素是减少磁盘IO次数。

磁盘IO有个特点，从磁盘读取1B数据和1KB数据锁消耗的时间基本一样。那么我们就根据这个思路，考虑在一个节点上尽可能多的存储数据，一次磁盘IO就多加载点数据到内存。这就是B树、B+树的设计原理。

1.5、B树、B+树（多路平衡二叉树 Balance Tree）

B Tree是一个绝对平衡二叉树，所有的叶子节点在同一高度，如下图的B树，每个节点限制最多存储n个key。一个节点超过n个key则自动分裂。n是自定义的树的度。也就是每个节点关键词的个数。

B树作数据库索引有以下优点：

1）优秀检索速度，时间复杂度：O(h*logN)，其中h为树高，N为每个节点关键词的个数；

2）三层高度能够支撑的数据量非常大，远远超过AVL树；

3）尽可能少的磁盘IO，加快了检索速度；

4）可以支持范围查找；

MySQL为了利用好磁盘的预读能力，将页大小设置为16K。一个节点可以存16K的内容；假设关键字类型为int，即4个字节，每个关键字对应的数据区也是4个字节，则每个节点大约能存储（16*1000）/8=2000个关键字，共2001个路数。如果高度是三，二叉树最多可以存储7个关键字，如果是B树或者B+树，最多可以存储2001的三次方减1个关键字，将是10亿级以上的数据。

注意：由于B树是绝对平衡二叉树，它时刻要保持自己的绝对平衡，所以关键字的变化，insert/delete/update都会导致B树的结构发生很大变化，所以创建索引要谨慎，具体原因和解释请往下看。

B树和B+树有什么不同呢？

1）B树一个节点存的是数据，B+树存储的是索引（地址）；所以B树一个节点的存储容量有限，而B+树能存储很多个地址，B+树叶子节点存所有的数据。

2）B+树的叶子节点是数据阶段，用了一个链表串联起来，便于范围查找。

 综上，MySQL最终选择B+树的原因：

1）在单个节点存储容量有限的情况下，单节点能够存储大量索引，使得整个B+树高度降

低，减少了磁盘IO；

2）B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身是

有序的，在数据范围查询时，更具备效率。

3）B+树的磁盘读写能力强。它的根节点和支节点不保存数据区，保存的关键字比B树多。叶

子节点不保存子节点引用，能保存的关键字和数据区更多。

4）B+树天然的排序能力；

5）B+树查询性能稳定；

因此，MySQL的索引使用的就是B+树。

B+树在查找效率、范围查找中有着非常不错的性能。

二、索引结构

Mysql引擎主要有InnoDB和MyISAM，两者对数据与索引的数据处理不同。

Innodb 创建表后生成的文件有：

• frm:创建表的语句
• idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有

• frm:创建表的语句
• MYD:表里面的数据文件（myisam data）
• MYI:表里面的索引文件（myisam index）

MyISAM引擎把数据和索引分开两个文件存储；InnoDB引擎把数据和索引放在同一个文件里。

下面从底层原理分析两个引擎是如何依靠B+树这种数据结构实现引擎的：

2.1、MyISAM引擎的底层实现

MyISAM的数据和索引落在不同的两个文件上。MyISAM在建表时以主键为Key来建立主索引B+树（即主键索引或主索引），属于非聚集索引，树的叶子节点存储的是对应数据的物理地址。我们拿到这个物理地址，就可以到MyISAM数据文件中直接定位具体的数据记录。如下图所示：

当为某个字段添加索引时，在myi索引文件中同样会生成一个对应字段的索引B+树，这就是辅助索引。该字段的索引树的叶子节点同样是记录了对应数据的物理地址。我们拿着这个物理地址到myd数据文件中找到具体的数据记录。

所以在MyISAM存储引擎中，主键索引和辅助索引是同级别的，无主次之分，实现方式一样。这就是非聚集索引。

非聚集索引的索引项是顺序存储，但索引项对应的数据记录是随机存储的。

2.2、InnoDB引擎的底层实现

InnoDB的数据和索引落在同一个文件里。首先InnoDB会根据主键ID作为key建立主键索引B+树，这就是聚集索引，如下图所示：

B+树的叶子节点存储的是主键ID对应的数据。如：执行select * from user_info where id=15时，InnoDB会查询主键ID索引B+树，找到对应的user_name=Bob。

这是建表时InnoDB自动为主键ID建好的索引树，这就解释了为什么mysql建表时必须指定主键的原因。如果没有显示指定主键，则会选择第一个不允许为NULL的唯一索引，如果还是没有，则采用InnoDB存储引擎为每行数据内置的6字节ROWID设为主键，作为聚集索引。

每张表只有一个主键，故而每张表只有一个聚集索引。

聚集索引的数据物理存放数据和索引顺序是一致的。即只要索引是相邻的，对应的数据一定也是相邻地存放在磁盘上的。

当为某个字段添加索引时，InnoDB就会建立该字段的索引B+树，这就是辅助索引。辅助索引树的叶子节点存储的是主键索引的关键字的值。我们通过辅助索引树搜索到的是对应主键索引的关键字，在通过主键索引树找到对应的叶子节点，从而获取叶子节点数据区的数据记录。

2.3、InnoDB与MyISAM索引对比

为什么InnoDB只在主键索引树的叶子节点存储具体数据记录，在其他索引树却不存数据呢？为何要两次索引树遍历，先找到主键，再在主键索引树中找到对应数据呢？

1）InnoDB需要节省空间，因为一个表可能有很多辅助索引，每个字段加了索引后，索引树中都存储数据，idb文件会非常大；

2）InnoDB和MyISAM索引的设计放在一张图对比，如下图：

3）、更新数据（update/delete/insert）操作会导致索引树结构发生变化；

因为B+树是一个绝对平衡的二叉树，为了维持绝对平衡，B+树的节点会通过合并和分裂进行调整，如此每个节点存储数据的地址会发生变化；如果辅助索引B+树中存储的是地址而不是主键Key，那就需要同步更新每个节点的地址。好比编程中定义了一个常量，用常量代替固定的值在代码各个地方使用，当常量值需要变更时，非常简单，只要修改常量对应的具体值即可，其他所有使用常量的地方都无须变更。

如果InnoDB和MyISAM一样在主键索引和辅助索引的叶子节点存放数据行指针，一旦数据发生变化，则需要去重新维护所有的索引。

MyISAM由于数据文件和索引文件各自分开，所以数据发生变化时，数据行记录的头指针地址不会变化，同时索引顺序的变化不会导致数据文件中数据地址的改变。而InnoDB的数据和索引在同一个文件，且索引顺序的变化会导致数据地址的变化，如此，就需要重新维护所有的索引。

小结：

MyISAM的查询性能更好，很明显，MyISAM通过索引文件中的索引树找到地址后，直接进入数据文件定位具体的数据记录，返回给客户端；

而InnoDB通过辅助索引树找到主键key后，还需要再查询一次主键索引树，才能定位到具体的数据记录。MyISAM一步到位，InnoDB需要两步查询。

三、如何建立索引

3.1、列的离散型

离散型计算公式：count(distinct colum_name):count(*)，去重后的列个数：个数。值在(0,1]范围内。离散型的比值越接近1，说明重复的越少，表明离散型越高，越适合选择添加为索引。

为什么说离散型越高，选择型越好？

因为离散度越高，通过索引最终确定的范围就越小，最终扫描的行数也越少，效率越高。

3.2、添加索引的依据

1）较频繁的作为查询条件的字段应该创建索引；

2）唯一性太差的字段不适合单独创建索引；（离散型越高，越适合做索引列）

3）更新频繁的字段不适合创建索引；（因为针对数据的增删改，B+树要维护绝对的平衡二叉树，所以会通过节点的合并和分裂进行调整，如此每个节点的地址会发生变化。当主键索引数据地址发生变化，用主键值代替地址，就无须通知辅助索引进行修改；）

4）最少空间原则：关键字越小，每个节点保存的关键字数越多，检索效率就越高；

索引项的比较规则：对于索引项中关键字的对比，一定是从左到右依次进行

3.3、联合索引

1）单列索引是一种特殊的联合索引；

2）联合索引列的顺序要遵循最左前置原则。

• 最左前置原则（最常用列> 离散度高>最少空间）

3）联合索引是一个包含了多个功效的索引，列的选择原则：

• 经常用的列优先（最左匹配原则）
• 离散度高的列优先（离散度高原则）
• 宽度小的列优先（最少空间原则）

3.4、回表查询和索引覆盖

在讲解回表查询前，先回顾一下InnoDB的索引实现。InnoDB有两大类索引：聚集索引（聚簇索引）和普通索引（辅助索引）。

【InnoDB的聚集索引】

InnoDB聚集索引的叶子节点存储的是行记录数据，因此InnoDB必须有且只有一个聚集索引。

1）如果表定义了Primary key主键，那么PK就是聚集索引；

2）如果表没有定义PK，则第一个Not Null Unique的列就是聚集索引；

3）否则InnoDB会另外建一个隐蔽的ROWID（6个字节）作为聚集索引；

【InnoDB的普通索引】

InnoDB普通索引的叶子节点存储主键值（MyISAM则是存储行记录的物理地址指针）

那么什么是回表查询呢？

当通过普通索引查询记录时，扫描普通索引树，得到主键PK的值；然后在通过主键PK的值扫描主键索引树，最后得到数据行记录。如此两边扫描索引树的过程，称为回表查询。

覆盖索引

覆盖索引是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列，建立普通索引（可以是单列索引，也可以是一个索引语句定义所有要查询的列，即联合索引）。如此，就可以直接返回索引中的数据，不需要通过聚集索引去定位行记录，避免回表的发生。

举例：select id from users where name=?

因为只需要id的值，所以通过name查询，扫描name索引后，直接返回id，不需要扫描id索引。这就是覆盖索引；

select id,age from users where name=?

因为同时需要id和age的值，所以无法使用到覆盖索引了。通过name查询，扫描name索引得到id值后，还需要扫描id索引，从而得到id值对应的数据行记录，最后返回id和age，这就通过回表查询了。

了解覆盖索引后，就明白，查询sql中尽量不使用select *，要写明具体的字段名。当使用到覆盖索引时，不需要进入数据区就直接返回结果，提升查询效率；就算没使用到覆盖索引，也可以减少数据的网络传输量，减少磁盘IO和网络IO，提升效率。

覆盖索引的定义与注意事项

如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。

要注意的是，不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，MySQL只能使用B-Tree索引做覆盖索引。

另外，当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的Extra列可以看到【Using Index】的信息。

覆盖索引的优点

1. 索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。
2. 索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
3. 一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。
4. 由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。

3.5、基于索引的sql优化思路

1、索引列的数据长度在满足业务需求的前提下，越少越好；
2、表中的索引不是越多越好。冗余或者无效索引会占用磁盘空间，同时影响增删改的效率；
3、where条件中，like 9% 、like %9%、like%9，三种方式都用不到索引；后两种对索引是无效的，第一种9%不确定，取决于列的离散度。如果离散度很低，查询优化器发现走索引查询更差，就会直接忽略索引，进行全表扫描；
4、where条件中in属于范围查找，可以使用索引；not in无法使用索引；
5、select 查询时，指定需要的列，尽量不用select *；
6、where条件中有函数，索引失效。函数具有不确定性；
7、联合索引中，如果不是按照索引最左列开始查找，则无法使用索引；
8、联合索引中，如果where有某个列的范围查询，其右边所有的列都无法使用索引；
9、联合索引中，如果where中精确匹配最左列并范围匹配另一列，可以使用到索引。

尾序

1、随数据增加，各算法形态的演变过程，有助于算法理解：Data Structure Visualization (usfca.edu)

2、参考文章：MySQL索引原理（1） - 知乎 (zhihu.com)