高性能MySQL学习——高性能索引设计

高性能索引设计

索引设计和工作原理、索引类型、索引使用技巧、如何创建高性能索引、索引创建规范

索引设计和工作原理

什么是索引，维基百科对其定义：数据库索引是一种数据结构，它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。通俗来说，索引类似于书的目录，根据其中记录的页码可以快速找到所需的内容。

MySQL 官方对索引（Index）的定义是存储引擎用于快速查找记录的一种数据结构。

• 索引是物理数据页，数据库页大小（Page Size）决定了一个页可以存储多少个索引行，以及需要多少页来存储指定大小的索引。
• 索引可以加快检索速度，但同时也降低索引列插入、删除、更新的速度，索引维护需要代价。

索引涉及的理论知识有二分查找法、哈希表及 B+Tree。

二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。如下图所示是维基百科对二分查找法的定义。
它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。
举个例子，有序数组 [1-71] 有 17 个值, 即在有序数组 [A0-A16] 中希望找到 Target(7)所在的位置，首选确定下标 L 为 0，下标 R 为 16，下标 m 为 floor [( L+R)/2]，即向下取整数。

1. 第一次查询

下标 L=0，R=16，m= floor[(0+16)/2]=8，获得 A8 的值为 14，因为 A8(14) >Target(7) 则设置 R=m-1=7，如下图所示。
2. 第二次查询

下标 L=0，R=7，m=floor[(0+7)/2]=3，获得 A3 的值为 6，A3(6) < Target(7) 则设置下标 L=m+1=4，如下图所示。
3. 第三次查询

下标 L=4，R=7，m=floor[(4+7)/2]=5，获得 A5 的值为 8，A5(8) > Target(7) 则设置下标 R=m-1=4，如下图所示。
4. 第四次查询

下标 L=4，R=4，m=floor[(4+4)/2]=4，获得 A4 的值为 7，A4(7) = Target(7)，查询结束，如下图所示。
此次查询经过 4 次二分查找后找到目标数据 7，如果在查询过程中出现下标 L>R 的情况，则表示目标元素不在有序数组内，结束查询。

二分查找是索引实现的理论基础

索引原理

数据库查询是数据库的核心功能，索引是作为加速查询的重要技术手段。对于索引数据结构的选择其本质是贴合当前数据读写的硬件环境选择一个优秀的数据结构进行数据存储及遍历，在数据库中大部分索引都是通过 B+Tree 来实现的。当然也涉及其他数据结构，在 MySQL 中除了 B+Tree 索引外还需要关注下 Hash 索引。

接下来我们对 Hash 索引、B+Tree 索引逐一展开学习。因为后续大部分内容都是讲 B 树的，为了让 B 树的内容更连贯，这里先讲 Hash 索引。

Hash 索引

哈希表是数据库中哈希索引的基础，是根据键值 <key,value> 存储数据的结构。简单说，哈希表是使用哈希函数将索引列计算到桶或槽的数组，实际存储是根据哈希函数将 key 换算成确定的存储位置，并将 value 存放到该数组位置上。访问时，只需要输入待查找的 key，即可通过哈希函数计算得出确定的存储位置并读取数据。

如下图所示，姓名作为 key，通过哈希函数对姓名字段数据进行计算，得到哈希码并存放到桶或槽的数组中，同时存放指向真实数据行的指针作为 value，形成哈希表。
接下来从哈希索引如何实现、Hash 碰撞处理、MySQL 如何使用 Hash，三个方面学习哈希索引。

首先讲解哈希索引是如何实现的？数据库中哈希索引是基于哈希表实现的，对于哈希索引列的数据通过 Hash 算法计算，得到对应索引列的哈希码形成哈希表，由哈希码及哈希码指向的真实数据行的指针组成了哈希索引。哈希索引的应用场景是只在对哈希索引列的等值查询才有效。

如下图所示，根据表中的 name 字段构建 Hash 索引，通过 Hash 算法对每一行 name 字段的数据进行计算，得出 Hash 码。由 Hash 码及 Hash 码指向真实数据行的指针组成了哈希索引。
因为哈希索引只存储哈希值和行指针，不存储实际字段值，所以其结构紧凑，查询速度也非常快，在无哈希冲突的场景下访问哈希索引一次即可命中。但是哈希索引只适用于等值查询，包括 =、IN()、<=> （安全等于， select null <=> null 和 select null=null 是不一样的结果) ，不支持范围查询。

另外，哈希索引的性能跟哈希冲突数量成反比，哈希冲突越多其维护代价越大性能越低。

Hash 碰撞的处理方法，Hash 碰撞是指不同索引列值计算出相同的哈希码，如上图所示， 表中 name 字段为 John Smith 和 Sandra Dee 两个不同值根据 Hash 算法计算出来的哈希码都是 152，这就表示出现了 Hash 碰撞。 对于 Hash 碰撞通用的处理方法是使用链表，将 Hash 冲突碰撞的元素形成一个链表，发生冲突时在链表上进行二次遍历找到数据。

• Hash 碰撞跟选择的 Hash 算法有关系，为了减少 Hash 碰撞的概率࿰