《高性能Mysql》读书笔记（三）：索引类型和高性能索引策略

本文链接：https://blog.csdn.net/AmbHowie/article/details/105575476

博客链接：Cs XJH’s Blog

索引类型

B-Tree索引基于B-Tree结构实现，B-Tree是平衡多路查找树，该索引支持全键值、范围键值、键前缀匹配查找。为了清楚地了解数据库如何在磁盘中通过B-Tree索引精确地查找数据，接下来从磁盘层面了解下原理：

系统从磁盘读取数据到内存是以磁盘块为最小基本单位的，所以，初步查询是从磁盘块开始的。

InnoDB存储引擎有页的概念，页是其存储引擎的最小存储单元，默认页的存储大小为16k。而磁盘块的大小没有页那么大，所以，InnoDB引擎可以一次申请若干连续的磁盘块。

磁盘块中包含若干条记录和指针，指针指向其他磁盘块，而记录由键值和data两部分组成。该键为主键，data则是除主键外的其他列数据。

如上图所示，如果需要查询某条行记录，首先将磁盘块1读入内存，根据需要查询的主键值和B-Tree的性质，可以高效地查询到对应的记录数据或者指向目标磁盘块的指针。

在InnoDB存储引擎中使用的是B+Tree的存储结构，B+Tree是B-Tree的优化结构，可以更高效地帮助查询记录。

由上面可知，B-Tree的高度受data数据大小的影响。如果data数据较大，那么一个磁盘块可以存储的记录数将会很少，那么对应的B-Tree的高度将增加。又由于将磁盘块数据读入内存的IO操作是影响查询效率的关键，而B-Tree越高则进行IO操作的次数将越多，对效率影响越大。

B+Tree相对于B-Tree特点：

非叶子结点只存储键值
叶子结点存储所有的键值和对应的记录
叶子结点构成循环链表

由于B+Tree非叶子结点只存储键值，那么极大地限制了树的高度，减少了IO操作。并且，叶子结点的循环链表结构也有利于进行范围查询操作。

B+Tree.jpg

哈希索引是基于哈希表结构实现，只支持精确匹配。对于每一行数据，存储引擎都会对索引列计算得到一个哈希码。哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果哈希码重复，将以链表的方式存储多个记录到同一哈希码下。

如果需要根据哈希索引查询记录，首先会计算该行记录索引列值的哈希码，然后根据哈希码匹配索引中的哈希码，匹配成功后，拿到指向记录的指针，遍历指针指向的记录，如果键值相同则是目标记录。

哈希索引自身只存储对应的哈希值，所以索引结构十分紧凑，这也让哈希查找的速度十分快。但哈希索引也有一些限制：

有时候需要对于很长的字符列建立索引，但直接对这列数据建立索引不但耗费存储空间，而且是很低效的。一个好的方案是索引该列数据开始的部分字符，但一个缺点是这会降低索引的选择性。

索引的选择性是不重复值数量与记录总数的比值，说白了就是数据的不重复率，不重复率越高，那么索引的选择性越高，索引也就越高效。
常用的计算索引选择性的SQL语句：SELECT COUNT(DISTINCT column) / COUNT(*) FROM table

建立索引的依据在于SQL语句的查询和排序条件，并且，我们常常查询时会与多个列相关。如果你直接对相关的所有列建立单列索引，这样的索引性能是很低的，因为这样一来最好的情况下也只能是“一星”索引，在其之前，还有“二星”，“三星”等索引，和它们间的差距是几个数量级的。

于是，就到了我们的多列索引出场了。根据SQL的条件只创建一个索引，却索引多个列。但这里还有一个问题，就是索引列的顺序。

因为多列索引需要服从最左列匹配原则。说白了就是索引列排序的优先顺序，并且，只有左边的索引列都使用了，才会使用该索引。此外，为了索引的高性能考虑，索引列中索引选择性越高的列应该排在前面。