MySQL系统学习（03）：深入浅出索引
image.png
前言
数据库的索引在日常工作中经常会接触到，重要性我们都很清楚。但是关于索引的实现，不同存储引擎下索引的实现原理又是怎样的，使我们今天需要去深入的。
什么是索引
一句话说，索引的出现是为了提高数据库的查询效率，就像书的目录一样 一本几百页的数，如果我们想要快速的找到某篇文章，在不借助目录的情况下，那一定需要费不少力气。索引就像数据库表的目录一样。
索引的常见模型
索引的出现是为了提高查询效率，但是实现索引的方式有很多种，所以这里也就引入的索引模型的概念。可以用于提高读写效率的数据结构有很多，这里简单的介绍3几种常见也比较简单的，分别是哈希表、有序数组、搜索树。
下面主要从使用的角度，分析一下这三种模型的区别。
哈希索引模型
哈希表是一种键值（KV）存储的数据结构，我们只要输入待查询的值key，即可以找到对应的值即value。哈希的思路很简单，即把值放进数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免的多个key值经过hash函数的换算会出现同一个值得情况。处理这种情况的办法就是 拉出一个链表。
假设，我们现在维护了一张身份证和姓名的数据表，需要根据身份证号查询姓名，这是对应的hash索引的示意图如下：
image.png
图中，id_number2、id_number4计算出来的值都是N，但是没关系，后面还跟了一个链表。假设这时我们要查询id_number2对应的名字是什么,首先通过id_number2通过hash函数算出N，然后拿到链表，便利获取user2.
需要注意的是，图中id_number的4个值不是连续的，这样做的好处是插入新的user时非常快，只需要往后面追加。但缺点是，因为不是有序的，hash索引在做区间查询的时候效率很低。
我们可以想象一下，假设我们需要查询[id_numberX, id_numberY]这个区间的所有用户姓名，那就必须要全部扫描一遍。
所以，Hash索引更适合做等值得这种检索，比如Memcached及其他一些NoSQL库的引擎。
有序数据索引模型
而有序数组在等值查询和范围查询下的性能都非常优秀，还是上面这个根据id_numer查询姓名的事例，如果使用有序数组作为索引结构的话，示意图如下：
image.png
这里我们假设身份证号码没有重复，那么它就是按照身份证号码依次递增保存的。这时候如果我们需要查询id_number2对应的姓名，按照二分法很快就可以查找到。时间复杂度T（n）= O（log（n））
同时很显然这数据结构支持范围检索，如果我们要查询[id_numberX, id_numberY]这个区间内的姓名，首先通过二分法快速查找到id_numberX的身份证，如果id_numberX不存在，那就先找到第一个大于id_numberX的身份证，然后向右遍历，直到找到大于id_numberY的身份证，结束循环。
如果仅看效率，那有序数组是最好的数据结构了，比如我们要保存2018年某城市人口或经济等数据，像这种不需要再修改的数据是再合适不过的。因为有序数组的插入性能非常的差，因为向有序数组的中间插入一个User，那需要将其后面的所有数据都向后移动一个位置。
二叉搜索数索引模型
还是上面的例子，我们用二叉搜索数实现的示意图如下：
image.png
二叉搜索数的特点：每个节点的左儿子小于父节点，而父节点又小于右儿子。这样如果我们要查询User2的话，就需要按照这个顺序：
image.png
这个时间复杂度T（n） = O（log（n））
N叉数索引模型
树可以二叉，也可以有多叉（N叉数），N叉数多个儿子的大小从左到右依次递增，二叉树是搜索效率最高的，但是实际上大部分的数据库都不使用二叉树，因为索引不仅存在内存中，还需要写入磁盘。
我们可以想象一下一颗100万节点的二叉树，树高20。依次查询可能访问20 个数据块。在机械硬盘时代，从磁盘随机读取一个数据块需要20ms左右的寻址时间。也就是说一个100万行的数据，使用二叉树索引模型的话，查询一行，需要的时间大概在20 * 20ms的时间，那是不能忍受的。
为了让一个查询尽量少的读取磁盘，就需要尽量让查询过程让问尽量少的数据块，那么我们就不应该使用二叉树，应该使用“N叉”数，这里的N取决与数据块的大小。
以InnoDB的整数字段索引为例，如果这个N为1200，树高为4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总在内存中的，所以一个一个10行数据的表上整形字段的索引，在查询一个值得时候最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，所以访问磁盘的次数就更少了。
N叉数由于在读写上的性能优先，以及适配磁盘的访问模式，已经被广泛的应用在数据库引擎中。
不管是是hash表、有序数据还是N叉数，他们都是不断迭代的、不断优化的产物，或者解决方案。数据库技术发展到今天跳表、LSM树等也被用于数据库引擎中，这里就不一一展开了（其实这里我也展开不了，也没具体了解过，感兴趣的话可以了解下【尴尬】）。
我们心里要有个概念，数据库底层存储的核心就是基于这些数据模型的。每碰到一个数据库我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库适用于什么样的场景。
上面我主要记录的是关于常用的几种索引数据模型的数据结构及适用场景。下面我们来具体看下在MySQL中，是怎么应用的。首先MySQL中，索引是在存储引擎层实现的，所以并没有统一的标准，即不同存储引擎的索引工作方式都是不一样的。而即使多个存储引擎支持同一个索引模型，那其底层的具体实现可能也是有差别的。
由于InnoDB是使用最为广泛的且是MySQL现在的默认存储引擎，所以接下来我们就以InnoDB为例，来分析下其索引模型是如何工作的。
InnoDB的索引模型
在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存放方式的表被称为索引组织表。在这之前我们都知道 InnoDB的索引结构是采用B+数结构存储的，所以数据也都是存储在B+数中的，但是可能对其细节不是很了解，下面我们来一块分析下。
首先，我们要知道每一个索引在InnoDB里面都分别对应一棵B+数。
假设，我们有一个主键ID的表，其中有一个字段K，在K字段上也有一个其他索引（非主键索引）。
我的建表语句是这样的：
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
表中R1~R5的（ID，k）值分别是这样的：（100， 1）、（200，2）、（300，3）、（500， 5）和（600， 6）。ID索引树与k索引树对应的示意图分别是这样子的：
image.png
从图中不难看出，根据叶子节点的内容，数据看索引可以分为主键索引和非主键索引。
主键索引的叶子节点存放的是整行数据（图中的Rx），在InnoDB中，主键索引也被称为聚簇索引。
非主键索引的叶子节点对应的是主键的值，在InnoDB中非主键索引也被称为二级索引。
根据上的索引说明，我们来看一个问题：基于主键索引和普通索引的查询有什么区别？
- 如果查询语句是select * from T where ID = 500,即主键查询方式，只需要查询ID这棵B+树。
- 如果查询语句是select * from T where k = 5, 即非主键索引查询，则需要先搜索k索引树，获取到主键ID的值 = 500，再到ID索引树查询一次，这个回到ID索引树查询的过程称为 回表
也就是说基于普通索引/二级索引的查询需要多扫描一棵索引树。所以我们应用中尽量使用主键索引。
索引维护
B+树为了维护索引有序性，在插入新值得时候必须做必要的维护。以上面图为例，如果我们需要插入新行的ID值为700，则只需要在R5的行后面查询新行即可。但是如果需要插入的新行ID=400，那就相对比较麻烦了，需要逻辑上移动后面行的位置，为新行腾出空位。
而更糟糕的是，如果R5的数据页已经满了，则根据B+树的算法，需要申请一个新的数据页，然后挪动部分数据过去，这个过程叫做页分裂。这种情况下，性能自然会受到影响。
除了性能外页分裂还将影响数据页的利用率，原本在一个页的数据，现在拆分到两个数据页，整体空间利用率下降大约50%。
有页分裂自然也就有页合并。当相邻两页由于删除了数据，利用率很低之后，会将数据页合并。
基于上面的索引维护过程说明，我们来看一个案例：
我们会在一些建表规范里看到过一个数据表一定要有一个自增主键，事无绝对，我们来看下什么情况下建议一定要有？什么情况下不应该有？
主键自增是指在自增列上定义的主键， 在建表语句中一般是这么定义的：
mysql> ID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
在插入数据的时候，可以不指定ID的具体值，系统会获取当前ID最大值加1（默认情况下是+1，除非我们在建表的时候指定了自增间隔N, 那么则是+N）作为下一条记录的ID。
也就是说，主键自增的插入方式正好符合我们前面提到的递增插入场景。每次插入一条新记录都是追加插入，不需要挪动任何节点，也不涉及叶子节点的分裂，所以写数据的成本很低。
但是有些场景下我们需要将业务字段作为主键，比如：报告编号、不重复的身份证号等字段，这样我们往往没法保证有序插入（自增插入），所以写数据的成本是相对高很多。
除了性能的考量，我们还可以从占用空间的角度来分析。比如我们有一个字符串类型的身份证号，那么我们是使用它来做主键还是自增ID呢？
由于每个二级索引的叶子节点上都是主键的值，所以如果使用身份证，那每个二级索引的叶子节点都将存一个占用20个字节的字符串。而如果使用整形作为主键，则占用4个字节；使用长整型，占用8个字节。
显然主键值越小，二级索引的叶子节点就越小，普通索引占用的空间也就越小
有没有什么场合使用业务字段作为主键的呢？还是有的，比如某些业务场景的索引要求是这样的：
1.只有一个索引
2.该索引必须是唯一索引
可能你已经看出来了，这是一个经典的KV场景。由于没有其他索引，也就不用考虑其他索引叶子节点的大小问题了。
这个时候我们就需要考虑上面提到的“尽量使用主键查询”原则，直接将这个业务字段设置为主键，避免回表引入的性能损耗。
最左前缀原则
关于索引还有一个非常非常重要的索引生效原则：最左前缀原则
刚开始接触索引的时候，我们会发现很多业务字段都会作为等值、范围、模糊等检索条件，那我们常常会由于这样没每个字段都创建一个索引是不是很麻烦，索引占用的空间应该非常大吧，是的！我们说过每个索引都会对应一个B+树。
比如我需要根据身份证查询用户的家庭地址，需要这不是一个频繁的查询需要，但是数据量很大的情况下我们总不能让它走全表吧。那应该怎么做的？
这里，我们先说结论：B+树这种结构可以利用索引的最左前缀来定位数据
为了更直观的说明这个问题，我画了下面这张图，我们利用（name，age）这个联合索引（也有叫复合索引的）来解释：
image.png
可以看到索引项是按照索引定义里面的顺序出现的 , 即（name, age）-> ("曹操"， 30)
当我们的逻辑需要时查询一个姓名为“曹操”的人，可以快速定位到ID4，然后向后遍历所有需要得到的结果。
如果我们要查姓名第一个字为“曹”的人，会使用模糊匹配， “ where name like '曹%' ”， 这时候我们也可以利用到这个索引，查询到第一个ID4。
可以看出不只是索引的全部定义，只要满足索引的最左前缀，就能利用索引实现快速检索。这个最左前缀可以是联合索引的前N个字段，也可以是字符串索引的前N个字符。
基于上面的最左前缀原则，我们来看下：在建立索引的时候，如何安排索引内的字段顺序
其实就是将最高频检索条件对应的字段放到最左边即可。
补充：如果有一个联合索引（a，b），同时又存在a,b各自的索引（a）和（b），那这种情况下如果只有一个基于b的查询条件，这种是不能利用到（a, b）索引的。这种情况我们需要同时维护一个（a,b）（b）两个索引就可以了。
索引下推
这个是MySQL 5.6引入的，上面的最左前缀原则可以用于在索引中定位问题。但是那些不满足最左前缀的怎办呢？
我们还是以上面的（name，age）这个联合索引为例。如果现在有一个需求检索出用户表中姓名第一个字为“曹”，并且年龄为45岁的所有男人。SQL我们会这样写
SQL> select * from T where name like '曹%' and age = 45 and ismale=1；
你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用“曹”找到第一个满足条件的ID4。当然，这还不错了，总比全表扫描好。然后判断其他条件是否满足。
在MySQL 5.6之前，只能从ID4开始一个个的进行回表，在主键B+树中找到记录，在判断其他字段值。
而在MySQL 5.6之后，MySQL可以在（name， age）所以上直接判断age是否等于45.满足条件的在进行回表，减少了回表的次数。这个引入的新的逻辑叫索引下推
总结
MySQL的索引模型，已经后面提到的最左前缀等是非常非常重要的，搞懂这些，对我们的数据查询性能会有很大的帮助。但是关于索引的内容绝不止我今天记录的这一点点，还有很多的东西需要我们去探索。
个人博客网站：RelaxHeart网 - TEC博客
