学习mysql原理专题总结(2)索引底层原理分析

本篇带来索引底层原理分析的总结，接下来会带来Explain关键字详解、查询优化原理分析与实战。

页结构存储数据过程

如果一个数据少于16kb，那么页就会存储多组数据，并以主键排序的方式插入数据。为便于查找数据，会用page_directory以组存放主键值。如图

若数据多了，自然是多页，那么如何查找哪一页呢，同理，建立一个页来存放页数和那一页的最小主键值。如图（B+树）

这里注意的是，查数据的时候总的入口应该保持不变。那么在插入第二页数据的时候，第一页会先复制一份，原本的第一页变成目录页。第一页Copy和第二页构成叶子节点。照这样，总的入口就会保持不变了。

聚簇索引

我们上面以主键建立的B+树，也称聚簇索引，特点：
按主键值的大小进行记录和页的排序：

• 数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表

• 数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。

• B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引（复制索引）

以别的列作为搜索条件时我们可以多建几棵B+树(就是说的建索引)，不同的B+树中的数据可以采用不同的排序规则。
二级索引与聚簇索引有几处不同：

1. 按指定的索引列的值来进行排序
2. 叶子节点存储的不是完整的用户记录，而只是索引列+主键。
3. 目录项记录中不是主键+页号，变成了索引列+页号。
4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表。

联合索引

以多个列的大小为排序规则建立的B+树称为联合索引（建立多个索引），本质上也是一个二级索引

我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的
目录项记录的内容实际上是由三个部分构成的：

• 索引列的值
• 页号(找叶子页)
• 主键值(进行回表操作)
  
  用到索引，看是否能够缩小查找的范围，如果不能，则不建议使用索引。

索引的代价

空间的代价

一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个
索引也是会占用磁盘空间的。

时间上的代价

索引是对数据的排序，那么当对表中的数据进行增、删、改操作时，都需要去维护修改内容涉及到的B+树索引。
所以在进行增、删、改操作时可能需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护好排序。

B+树索引实战

假设我们建有3个字段bcd，对其使用联合索引

全局匹配

select * from t1 where b = 1 and c = 1 and d = 1;

查询优化器会分析这些查询条件并且按照可以使用的索引中列的顺序来决定先使用哪个查询条件

匹配左边的列

select * from t1 where b = 1;
select * from t1 where b = 1 and c = 1;

下面这个sql是用不到索引的

select * from t1 where c = 1;

因为B+树先是按照b列的值排序的，在b列的值相同的情况下才使用c列进行排序，也就是说b列的值不同的记录中c的值可能是无序的。而现在你跳过b列直接根据c的值去查找，这是做不到的。

匹配列前缀

如果只给出后缀或者中间的某个字符串，比如：

select * from t1 where b like '%101%';

字符串中间有’101’的字符串并没有排好序，只能全表扫描

匹配范围值

select * from t1 where b > 1 and b < 20000;

注意的是只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引。

精确匹配某一列并范围匹配另一列

select * from t1 where b = 1 and c > 1;

排序

select * from t1 order by b, c, d;

先按照b值排序，b值相同，则按照c来排序，c的值相同，则按照d排序。由于这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

注意:

• ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出order by c, b, d
  的顺序，那也是用不了B+树索引的。
• 使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。
  不可混用。

分组

select b, c, d, count(*) from t1 group by b, c, d;

先按b在按c在按d，正好这个分组顺序又和B+树中的索引列的顺序是一致的，所以可以直接使用B+树索引进行分组

如何建立索引

考虑索引选择性

索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数的比值：

选择性=基数除以记录数

选择性越高的索引价值越大。

考虑前缀索引

用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

缺点：不能用索引来排序或分组，也不能用于覆盖索引(只通过索引，就取到了所需要的数据)。

总结

• 索引列的类型尽量小
• 利用索引字符串值的前缀
• 主键自增
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。