MySQL-索引结构

查询

页内查询

为了方便理解，我们简化一下行结构，只保存记录头部信息的record_type和next_record。

现在假设我们有一个数据页，如下：

可以看到，页内的数据行是形成单列表的结构，同时，页有一个Page Dirctury区域，会把页中所有行数据进行分槽处理，这样就可以通过二分法快速的定位到数据。

页分裂

页与页之间会形成双向链表，并且上一页的数据行主键一定小于该页的最小主键，因为有着这样的特性，所以每次插入的时候，需要通过必要的移动来保证这个特性，现在我们假设一个页只能存三个数据行（实际上可以储存很多，16KB）。

当我们在插入一个id为4的行数据时，由于规定一页三行，所以会生成一个新的页，并进行必要的移动，具体步骤如下：

1. 把id为5的数据行移动到新的页中。
   
2. 在页10插入id为4的新数据行。
   

以上步骤就是页的分裂，看过我之前博客的朋友可能知道，数据行被删除的时候其实只是把记录头部信息的delete_flag设置为1，而不会真的去删除数据，因为删除为涉及移动，之后插入同样的行也会涉及移动，所以干脆就不删除了，只做标记。

索引

随着我们的记录越来越多，我们的页也会越来越多，如何定位数据就成了一个问题：

这里可以看到页的序号是不规则的，这是为了突出页在储存上不是连续的
具体来说，从页的双向链表中查找一条记录可以分为一下的步骤：

1. 定位数据所在的页。
2. 在页中通过二分法等操作找到数据行。

第二步我们已经讨论过了，现在问题是在第一步，如何定位所在的页。
MySQL设计者利用了类似书本目录的思想，把每一页最小主键提取出来，用来作为该页的一个标志（目录项）：

这样当所有目录项在空间上是连续的时候，我们同样可以用二分法快速的定位到所在的页。
然而这些目录项的储存也是问题，如果用连续空间，那么增加删除的时候，同样要移动大量的目录项，并且数据量的时候，目录项的数量也会非常多，要申请一大块的连续空间明显不切实际。因此MySQL设计者，把目录项也有页来储存，在页中目录项和数据行基本一模一样，就是record_type为1，列值只有主键和下一层页的页号，如图所示：

随着数据的增多，最底层的页数量增加，目录项页的数量也会增加，并且同层的页会形成双向链表，最终形成B+树，如图所示：

我们举例的时候，是从底部往上说的，实际上，数建立的时候第一个确定的是根节点，根节点的地址在之后都不会改变。

二级索引

我们之前分析的B+数都是用主键来形成的，因此如果where条件的列不是主键，就不会走索引，要一个一个的遍历所有的数据页。解决这种问题我们可以建立二级索引，二级索引的原理十分简单，我们之前以主键来形成数，二级索引则是用指定列来形成B+树，通过二级索引的B+数可以得到主键，如果要获得的数据有一部分在主键B+数上，那么就拿这个主键去再查询一次主键数，这就是回表。

MyIsam索引

MyIsam的索引同样是B+数，像InnoDB的二级索引，可以拿到数据的编号（列表下标），之后通过编号再磁盘拿到具体数据（数据在文件中以列表的形式储存）。

索引的使用

索引的代价

• 空间上：建立索引会生成一颗B+树，会占用空间。
• 时间上：对数据的增删改同样要在B+树上进行相关操作，可能还伴随着页分裂，所以消耗性能。

检索区间

对于一个查询来说，最简单的方法就是遍历页与页的双向链表以及页内的单链表，筛选出我们要查询的数据（全表查询），可以说这样查询的扫描区间为(-∞,+∞)，我们要做的就是通过索引树来缩小查询的扫描空间，实现只要扫描部分数据就可以得出结构。比如通过where条件，可以缩小范围：

SELECT * FROM table WHERE id >= 2 AND id <= 100;

该语句的扫描区间为：[2, 100]

SELECT * FROM table WHERE id IN (7, 13) OR (id >= 20 AND id < 100);

该语句的扫描区间为：[7, 7]、[13, 13]、[20, 100)

联合索引对应的区间

假设我们有联合索引INDEX(a, b, c)，那么在底层B+树的行会先排序a，a相等排序b，b相等排序c。因此在a确定值的情况下，b有序；a，b都确定值的情况下，c有序，所以才有了最左前缀法则。是否使用了联合索引中的全部字段，就得看能不能用这些字段来进一步缩小扫描区间，例如sql：

SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 'a';

通过索引a可以确定检索区间：[(1, -∞, -∞)，(1, +∞, +∞) ]
在索引a确定值的情况下，b是有序，因此条件b = 2可以进一步缩小扫描区间：[(1, 2, -∞)，(1, 2, +∞) ]
在索引a，b都确定值的情况下，c也是有序，因此条件c = 'a’可以进一步缩小扫描区间：[(1, 2, ‘a’)，(1, 2, ‘a’) ]
因此该联合索引中三个字段都可以用来缩小扫描区间，三个字段都用上了。
再看以下例子：

SELECT * FROM table WHERE a = 1 AND b > 2 AND c = 'a';

通过索引a可以确定检索区间：[(1, -∞, -∞)，(1, +∞, +∞) ]
在索引a确定值的情况下，b是有序，因此条件b > 2可以进一步缩小扫描区间：((1, 2, -∞)，(1, +∞, +∞) ]
而对于符合 b > 2 的记录来说，并不是直接安装c进行排序的，也就是在这些记录里字段c是乱序的，因此我们无法进一步缩小区间
所以该联合索引中我们只用了两个字段来缩小该sql的扫描区间。
思考以下sql：

SELECT * FROM table WHERE a = 1 AND b >= 2 AND c = 'a';

通过a， b我们可以确定区间[(1, 2, -∞)，(1, +∞, +∞) ]
虽然对于满足 b >= 2 条件的索引，c是无序的，但是对于 b = 2 ，c则是有序的，因此我们可以用c来进一步缩小区间：[(1, 2, ‘a’)，(1, +∞, +∞) ]
所以该联合索引中三个字段都可以用来缩小扫描区间，三个字段都用上了。

索引用于排序

我们在查询之后，经常会使用ORDER BY子句来对记录进行排序，在一般情况下我们只能把记录加载到内存中，然后再用排序算法在内存中对记录进行排序，如果记录数量太多，不能一次性加载到内存中，还需要借助磁盘来存放中间结果，这种在内存或者磁盘里面进行的排序方式称为文件排序（filesort）。例如，我们有一个表，字段a，b为联合索引，c没有任何索引。

SELECT * FROM table ORDER BY c;       //使用文件排序

SELECT * FROM table ORDER BY a，b; 

假如我们通过a，b来排序，因为a，b在联合索引树上本来就是有序的，所以我们只要沿着链表一直向后扫描就可以了，不需要进行排序的操作。
注意：

1. 索引列顺序问题：ORDER BY子句后面的列顺序必须按照索引列的顺序给出。
2. ASC和DESC不可以混用。
3. 用来排序的列不是来自同一个索引的列，这种情况不会使用索引进行排序。
4. 用来排序的列在一个联合索引上，但是不是连续的列。
5. 用来形成扫描区间的索引列与排序列不同：比如用a生成扫描区间，却用b去排序。
6. 排序列不是以单独的列名存在子句中，比如UPPER(a)，列放在函数内。

回表的代价

假如我们现在有一个二级索引INDEX（a），主键为id，表中还有其他字段，分析以下sql：

SELECT * FROM table WHERE a = 's';

1. 确认二级索引的扫描区间[‘s’ , ‘s’]
2. 在索引a生成的B+树上，找到第一个为’s’的行
3. 向后检索（页内单链表向后检索，页与页双向链表检索），直到不等于’s’
4. 每次在索引树检索到匹配的id，都去聚簇索引中获取完整的数据
   这个去聚簇索引中获取完整的数据的操作就是回表操作。
   乍一看，似乎没有什么问题，现在思考一下，这个sql是不是一定会走索引。

SELECT * FROM table WHERE a = 's';

答案是不一定，假如表中记录很多，并且a = ‘s’的记录占了90%，那么用索引去查询，由于对于a索引树，id是无规律的，所以执行回表操作，要涉及大量磁盘IO，这样可能导致走索引的性能还不如直接在聚簇索引上进行全表扫描。

更好的创建和使用索引

1. 只为用于搜索、排序或者分组的列创建索引。
2. 考虑索引列中不重复值的个数：如果列中有大量重复值就可能像我们分析的回表情况一样还不如全文检索。
3. 索引列的类型尽量小：一个页大小默认为16KB，如果索引列的类型小就可以存放更多的记录，减少IO次数。
4. 创建前缀索引：对于太长的列，如果要创建索引，可以取该列的前缀创建索引。
5. 覆盖索引：在使用二级索引进行查询时，最好的情况就是查询列表中只包含索引列，这样就可以避免回表操作。
6. 索引列要单独以列名在条件中出现，如果加了函数或者进行计算就不会走索引了，哪怕原本可以走索引。