千万数据量下，掌握Mysql索引底层原理将会拯救世界

• Mysql索引的本质
• Mysql索引的底层原理
• Mysql索引的实战经验

面试

问: 数据库中最常见的查询优化方式是什么?

答: 加索引

问: 为什么加索引能优化查询?

答1∶...不知道

答2∶因为索引其实就是一种优化查询的数据结构，比如Mysql中的索引是用B+树实现的，而B+树就是一种数据结构，可以优化查询速度，可以利用索引快速查找数据，所以能优化查询。

问: 你知道哪些数据结构可以提高查询速度吗?(听到这个问题就感觉此处有坑...)

答: 哈希表、完全平衡二叉树、B树、B+树等等

问: 那这些数据结构既然都能优化查询速度，那Mysql中为何选择使用B+树?

答: ...不知道

提问

SHOWINDEX FROM employees.titles;

有一个titles表，主键由emp_no，title，from_date三个字段组成。

那么以下几个语句会用到索引吗:

1. select * from employees.titles where emp_no = 1
2. select * from employees.titles where title = '1'
3. select * from employees.titles where title = '1' and emp_no = 1;

索引(Index)

• 到底什么是索引(Index)?
• 大学老师是这么定义的:索引就像书的目录
• Mysql官网是这么定义的:Indexes are used to find rows with specific column values quickly
• 我是这么定义的:索引是一种优化查询的数据结构

为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql独独喜欢B+树?

哈希表是什么?

哈希表(Hash table，也叫散列表)，是根据键值(Key value)而直接进行访问的数据结构。也就是说，它通过把键值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。

哈希表的做法其实很简单，就是把Key通过一个固定的算法函数即所谓的哈希函数转换成一个整型数字，然后就将该数字对数组长度进行取余，取余结果就当作数组的下标，将value存储在以该数字为下标的数组空间里。而当使用哈希表进行查询的时候，就是再次使用哈希函数将key转换为对应的数组下标，并定位到该空间获取value，如此一来，就可以充分利用到数组的定位性能进行数据定位。

哈希表的特点是什么?

假如有这么一张表(表名:sanguo):

现在对name字段建立哈希索引:

注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。

那么对于这样一个索引结构，现在来执行下面的sql语句:

select * from sanguo where name ='周瑜'

可以直接对周瑜'按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据。

那么如果现在执行下面的sql语句:

select * from sanguo where name >'周瑜'

则无能为力，因为哈希表的特点就是可以快速地精确查询，但是不支持范围查询。

如果用完全平衡二叉树呢?

还是上面的数据用完全平衡二叉树表示如下图(为了简单，数据对应的地址就不画在图中了。)∶

图中的每一个节点实际上应该有四部分:

1. 左手指针，指向左子树
2. 键值
3. 键值所对应的数据的存储地址
4. 右指针，指向右子树

另外需要提醒的是，二叉树是有顺序的，简单地说就是“左边的小于右边的”

假如我们现在来查找'周瑜'，需要找2次（第一次曹操，第二次周瑜)，比哈希表要多一次。而且由于完全平衡二叉树是有序的，所以也是支持范围查找的。

如果用B树呢?

还是上面的表数据用B树表示如下图(为了简单，数据对应的地址就不画在图中了。)∶

我们可以发现同样的元素，B树的表示要比完全平衡二叉树要矮"，原因在于B树中的一个节点可以存储多个元素。

如果用B+树呢?

还是上面的数据用B+树表示如下图(为了简单，数据对应的地址就不画在图中了。)∶

我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

那么B+树到底有什么优势呢?

这里我们用“反证法”，假如我们现在就用完全平衡二叉树作为索引的数据结构，我们来看一下有什么不妥的地方。

实际上，索引也是很"大"的，因为索引也是存储元素的，我们的一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据， 或者哪个数据结构能够提高磁盘的IO效率。

回头看一下完全平衡二叉树，当我们需要查询“张飞”时，需要以下步骤

1. 从磁盘中取出曹操"到内存，CPU从内存取出数据进行笔记，“张飞"'曹操"，取左子树（(产生了一次磁盘IO)
2. 从磁盘中取出“周瑜"到内存，CPU从内存取出数据进行笔记，“张飞"“周瑜"，取右子树（产生了一次磁盘IO)
3. 从磁盘中取出“孙权"到内存，CPU从内存取出数据进行笔记，“张飞”"“孙权"，取右子树（产生了一次磁盘IO)
4. 从磁盘中取出"“黄忠"到内存，CPU从内存取出数据进行笔记，“张飞"=“张飞”，找到结果（产生了一次磁盘IO)

同理，回头看一下B树，我们发现只发送三次磁盘IO就可以找到“张飞"了，这就是B树的优点:一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了。

而，B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

所以，到这里，我们可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。