MySQL系列-InnoDB索引

B+树索引

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用的和最为有效的索引。

B+树的结构大概就是如下图所示的亚子：

B+树.png

它有一些关键特征：

• 所有的叶子结点都在同一高度上
• 每个非叶子结点的值都是它的子结点的最小(或最大)值，上图是最小值
• 每个结点内的数据是有序的
• 所有的叶子结点都通过链表按顺序连接在一起，即整个叶子结点是一个有序的链表
• 只有叶子结点才记录卫星数据。

InnoDB中，这些结点的值就是对应数据表索引列的列值，简单理解下，如果上图是学生信息表里学号的索引内容，那每个结点的值就是学号，查询学号为30的同学的记录时，第一次和第一行比，介于15和56之间，那下一次就比较15这个结点的子结点里的值，介于20和49之间，那就定位到20这个结点的子结点，一共有2条数据，再一次经过比较找到30的结点，读取这个结点上的卫星数据，查询结束。

上一段只是简单理解一下MySQL InnoDB的索引查询过程，InnoDB的B+树索引有两类，聚集索引和辅助索引，这两种索引实际使用起来是有一些细节上的不同的。

聚集索引

InnoDB会在建表的时候为该表的主键添加索引，这个索引就是表的聚集索引，因为一张表只能有一个主键，所以自然地，一张表也只能拥有一个聚集索引，聚集索引的叶子结点的数据里是表里一条完整的记录。

上述例子中，假如学号是表的主键，那这就是一个聚集索引，叶子结点可以查询到某个学号的学生的完整的记录。

这里呢，有一个常常被DBA忽视的问题，B+树索引并不能直接找到一个给定键值的具体行。B+树能找到的只是被查找数据行所在的页，然后数据库通过把页读到内存，再在内存中进行查找，最后得到要查找的数据。真实的结构是下图这样的：

image.png

假设图中的数字仍然是学号，那如果我们要找到9号学生的纪录，通过索引，我们找到的其实是9号学生记录所在的数据页，里面包含了7、8、9三条记录，这是需要通过学号的比较才能最终找到记录。

辅助索引

数据表里非主键创建的索引就是辅助索引。它和聚集索引的区别就在于叶子结点的数据的内容。辅助索引存放的只是索引列的键值和聚集索引的书签（bookmark），其实就是该记录的主键，方便从聚集索引中找到完整的记录。也就是说，通过辅助索引查找数据是先从辅助索引找到聚集索引，也就是主键，在通过聚集索引找到完整数据。

覆盖索引（也叫索引覆盖）

聪明的读者可能会问，如果我本来就只要查询辅助索引对应的列值，那还需要通过聚集索引去取数据吗？答案是不需要，这就是MySQL的覆盖索引机制，从辅助索引可以得到查询的记录，就不需要查询聚集索引里的记录。或者对于一些记录总数的查询，我也不需要关心数据具体是什么内容，只需要知道表里有多少行数据的情况也可以索引覆盖。那如果有查询条件可以走辅助索引，但不能进行索引覆盖的情况，优化器还会不会选择辅助索引呢？如果通过辅助索引查找的数据是少量的，那还是可以选择辅助索引，为什么要强调少量呢？因为如果数据比较多，通过辅助索引拿到聚集索引去查询数据，对于聚集索引来说就相当是随机读，而随机读对于机械硬盘的存储设备是非常消耗性能的，此时可能还不如选择扫描全表或者直接使用聚集索引。

Cardinality

这个概念是索引里非常重要的一个知识点。在学习这个内容之前我总是觉得索引就是为了加快查找速度而生的，所以有了索引，肯定就要会选择使用。然而事实并非如此，MySQL的优化器有时会觉得不用索引反而更好，左右优化器判断的就是这个叫Cardinality的变量。

Cardinality表示的当前索引列的值在表中不重复的记录的数量。因为数据库每查一次索引就会产生一次I/O操作，I/O操作是非常消耗性能的，想象一下如果一个学校有1000名学生，把这一1000名学生的内容录入数据库，这时我搜索一下学生里叫小明的学生，结果999个学生都叫小明，那走索引就可能要查999次，每次查询还需要多次I/O，这时索引就失去了意义，本身数据在数据表里都是有序的，直接一次I/O顺序读出来远比走索引快，所以一般经验是，在访问表中很少一部分时使用B+树索引才有意义，但具体这个值怎么影响优化器的选择，查了一些资料，似乎没有明确的答案，说是这个值会影响优化器在执行计划里预估的检索行数，如果估计行数超过一定比例就不走辅助索引，直接扫全表，这个比例一般是20%。

聪明的读者可能又想到问，数据库的数据会频繁发生变更，MySQL是不是会实时刷新这个值呢？MySQL当然不会实时刷新这个值，只有达到以下条件的时候的MySQL才会更新：

1.当表中1/16的数据发生变化时
2.表中更新频次达到2 000 000 000次时

同时设置这两个条件是为了防止某些热区的数据被频繁更新，而这些数据的量又达不到1/16，那没有次数的限制就永远不会触发更新。

Cardinality的值也是可以通过命令手动刷新的，但是这个操作是非常消耗性能的，建议在数据库访问频率较低的时段进行。经常手动刷新这个值对于数据库的性能有着非常重要的意义，虽然一次耗时较长，但仍然非常建议定期去手动刷新一下。

为什么在MySQL会自动更新这个值的情况下还建议去手动刷新呢？其实MySQL计算这个值并不是精确的，它默认是随机取8个数据页的数据，计算这8个页里不重复记录的平均值，然后乘以页数来得到一个估计值，其偶然性是很大的，极端情况它甚至会取样取到全是重复的记录，导致它认为表里的数据全是重复的。

最后，Cardinality不光是指导MySQL是否使用索引的关键指标，也是对DBA决定是否要加索引的重要参考，需要加索引的列应该尽可能地满足：

Cardinality/总数据量≈1

的条件。