阿里二面：MySQL索引是怎么支撑千万级表的快速查找？

前言

在 MySQL 官方提到，改善操作性能的最佳方法 SELECT 在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针，从而使查询可以快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。

尽管可能会为查询中使用的每个可能的列创建索引，但不必要的索引会浪费空间和时间，使MySQL难以确定要使用的索引。索引还会增加插入，更新和删除的成本，因为必须更新每个索引。您必须找到适当的平衡，才能使用最佳索引集来实现快速查询。

那么，索引到底是什么？透过现象看本质：

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。

另外，阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。对此，有阿里的黄金铁律支撑，所以，很多人设计大数据存储时，多会以此为标准，进行分表操作。

以及，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

为了更深入理解索引的本质，这里我们先了解一下磁盘相关知识。

外存储器-磁盘

计算机一般有两种存储的方式：内存储器(main memory)和外存储器(external memory)

• 内存:读写速度非常快，但是容量很小，而且造价非常贵，在不通电的情况下会数据会丢失，不能长期存储数据；
• 外存：磁盘是相对常见的外存储设备，它是以存取时间变化不大为特征的。可以直接存取任何字符组，且容量大、速度较其它外存设备更快。

磁盘的构造

磁盘是一个扁平的圆盘(与电唱机的唱片类似)。盘面上有许多称为磁道的圆圈，数据就记录在这些磁道上。磁盘可以是单片的，也可以是由若干盘片组成的盘组，每一盘片上有两个面。

当磁盘驱动器执行读/写功能时。盘片装在一个主轴上，并绕主轴高速旋转，当磁道在读/写头(又叫磁头) 下通过时，就可以进行数据的读/写了。

一般磁盘分为固定头盘(磁头固定)和活动头盘。

固定头盘的每一个磁道上都有独立的磁头，它是固定不动的，专门负责这一磁道上数据的读/写。

活动头盘的磁头是可移动的。每一个盘面上只有一个磁头(磁头是双向的，因此正反盘面都能读写)。它可以从该面的一个磁道移动到另一个磁道，所有磁头都装在同一个动臂上，因此不同盘面上的所有磁头都是同时移动的(行动整齐划一)，当盘片绕主轴旋转的时候，磁头与旋转的盘片形成一个圆柱体，各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面。因此，柱面的个数也就是盘面上的磁道数。

磁盘的读/写原理和效率

磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、块号(磁道上的盘块)。

读/写磁盘上某一指定数据需要下面3个步骤：

1. 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。
2. 如上图6盘组示意图中，所有磁头都定位到了10个盘面的10条磁道上(磁头都是双向的)，这时根据盘面号来确定指定盘面上的磁道。
3. 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。

经过上面三个步骤，指定数据的存储位置就被找到，这时就可以开始读/写操作了。

访问某一具体信息，由3部分时间组成：

• 查找时间(seek time) Ts: 完成上述步骤(1)所需要的时间。这部分时间代价最高，最大可达到0.1s左右；
• 等待时间(latency time) Tl: 完成上述步骤(3)所需要的时间。由于盘片绕主轴旋转速度很快，一般为7200转/分(电脑硬盘的性能指标之一, 家用的普通硬盘的转速一般有5400rpm(笔记本)、7200rpm几种)，因此一般旋转一圈大约0.0083s；
• 传输时间(transmission time) Tt: 数据通过系统总线传送到内存的时间，一般传输一个字节(byte)大概0.02us=2*10^(-8)s。

寻道时间Ts : Ts=m∗n+s

n : 跨越n条磁道的时间； s： 启动磁臂的时间，约为2ms ； m：与磁盘驱动器速度有关的常数，约为0.2ms。

延迟时间Tr : Tr=1/（2∗r）

r ： 磁盘的旋转速度

传输时间Tt : Tt=b/（r∗N）

r ： 磁盘的旋转速度； N：为一个磁道上的字节数；b：每次所读/写的字节数b

总平均存取时间 : Ta=Ts+Tr+Tt

磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在查找时间Ts上，因此我们应该尽量将相关信息存放在同一盘块，同一磁道中，或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的查找时间Ts。

所以，在大规模数据存储方面，大量数据存储在外存磁盘中，而在外存磁盘中读取/写入块(block)中某数据时，首先需要定位到磁盘中的某块，如何有效地查找磁盘中的数据，需要一种合理高效的外存数据结构。

索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构

​

索引数据结构，主要包含以下几类，我们来对比下

• 二叉树
• 平衡二叉树
• 红黑树
• Hash表
• B-Tree

二叉树

定义：每个结点最多有两个子树，左子树比父节点小，右子树比父节点大。

缺点：会出现极端情况导致整棵树只有左子树或只有右子树。

​

平衡二叉树（AVL Tree）

定义：平衡二叉树又称AVL树，是一种特殊的二叉查找树，其左右子数都是平衡二叉树，且左右子树高度差的绝对值不超过1。

缺点：AVL树是高度平衡的，频繁的插入和删除，会引起频繁的rebalance，导致效率下降。