• MySQL性能优化(三)：深入理解索引的这点事

索引经常被人忽略，有时候甚至被误解、误用，糟糕的索引更会导致非常严重的性能问题，清楚理解索引，能够正确的使用它，便于利用它来进行数据库的优化，这才是索引的正确操作。

blog-thumb

索引，对于良好的数据库性能非常关键。只要提及到数据库性能优化，都会首先想到“索引”，看看表中是否添加索引。尤其是当表中的数据量越来越大时，索引对性能的影响尤为突出。在数据量较小且负载较低时，没有索引或者不恰当索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

不过，索引却经常被忽略，有时候甚至被误解、误用，在实际使用中经常会遇到糟糕索引而导致的性能问题。本文就索引的概念、类型、优点等方面聊聊，一起深入理解索引的这点事，更有助于你清楚的理解索引，能够正确的使用它，便于利用它来进行数据库的优化。

一、什么是索引

索引(Index)，是帮助MySQL高效获取数据的数据结构，是存储引擎用于快速找到记录的一种数据结构。

要理解 MySQL 中索引是如何工作的，最简单的例子就是去看看一本书的目录“索引”部分。如果想在一本书中找到某个章节，一般我们会先看书的目录“索引”，就会立即找到对应的页码。

在 MySQL 中，存储引擎也是用类似的方法使用索引，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。

查询是数据库中最常用的操作，我们都希望查询的速度尽可能快，因此数据库系统的设计者会从查询算法角度去进行优化。最基本的查询算法当然就是顺序查找，但是这种算法的复杂度为 O(n)，在数据量很大时就会显得非常糟糕。例如，在一张用户表 t_user 中有如下数据，想要查询到年龄为 89 岁的人，如果按照顺序查找，则得逐行扫描，可见查询效率有多低下(数量量越大，数据分布越不均匀，则花费的时间就更长)。

mysql> select * from t_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | xcbeyond |  22 |
|  2 | jack     |  34 |
|  3 | tom      |  77 |
|  4 | kitty    |   5 |
|  5 | make     |  91 |
|  6 | Mickey   |  23 |
|  7 | Andy     |  89 |
+----+----------+-----+
7 rows in set

好在，数据库系统的设计者早都意识到了这一点，参考了更优秀的查找算法，如二分查找、二叉树查找等等，但是分析之后发现，每种查找算法都只能应用于特定数据结构之上，如二分查找要求被查询的数据有序，而二叉树查找只能应用于二叉查找树。鉴于此，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，即：这就是数据库中的索引。

为了更好的理解索引，下图就以表 t_user 中的数据，展示了一种可能的索引方式。

索引举例示例图

左边是表中的数据，一共7条记录，为加快 age 列的查找，维护了一个右边所示的二叉查找树，每个节点包含索引键值及一个指向对应数据记录的指针，这样运用二叉查找就能很快的查找对应的数据了，时间复杂度为 O(log2 N)。

然而，在实际数据库中，几乎没有使用这样的二叉查找树来实现（因为二叉查找树对数据是有要求的），但其原理和这类似。

二、索引操作

在正式介绍索引之前，先一起来看看MySQL是如何创建索引、重建索引、查询索引、删除索引等操作的，以备后续使用。(建议单独保存收藏)

1. 创建索引

索引的创建可以在 CREATE TABLE 语句中进行，也可以单独用 CREATE INDEX 或 ALTER TABLE 来给表增加索引。

语法：

CREATE [UNIQUE/FULLTEXT] INDEX <索引名> ON <表名>(<列名>)

ALTER TABLE <表名> ADD INDEX|UNIQUE|PRIMARY KEY|FULLTEXT <索引名>(<列名>)

其中，创建索引时，可以指定索引类型：主键索引（PRIMARY KEY）、 唯一索引（UNIQUE）、 全文索引（FULLTEXT）、 普通索引（INDEX）。

例如：

1）以表 index_test 为例说明，先创建一个普通的表 index_test：

(创建表时，也可以直接创建索引，此处为了说明索引的创建，则单独创建索引)

mysql> create table index_test(id int,ch varchar(32));
Query OK, 0 rows affected

2）为表 index_test 单独创建索引：

mysql> create index idx on index_test(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

或者

mysql> alter table index_test add index idx(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

2.重建索引

重建索引，在常规的数据库维护操作中经常使用。在数据库运行了较长时间后，索引都有损坏的可能，这时就需要重建。对数据重建索引可以起到提高检索效率。

重建索引，实质上的对表的修复。

例如:

mysql> repair table index_test quick;
+-----------------+--------+----------+---------------------------------------------------------+
| Table           | Op     | Msg_type | Msg_text                                                |
+-----------------+--------+----------+---------------------------------------------------------+
| test.index_test | repair | note     | The storage engine for the table doesn't support repair |
+-----------------+--------+----------+---------------------------------------------------------+
1 row in set

3. 查询索引

有时，为了查看某张表是否有索引，索引情况如何，就需要通过命令 show index from|in table_name 来查看索引。

语法:

SHOW INDEX FROM|IN <表名>

例如：

mysql> show index from index_test;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test |          1 | idx      |            1 | id          | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

细心的你，或许看到查询结果中的字段 index_type 的值 BTREE，这也就是接下来会讲到的 B Tree 索引，从另一方面也可以知道 InnoDB 默认的索引类型为 B Tree。

4. 删除索引

删除索引可以使用 DROP INDEX 或 ALTER TABLE 语句来实现。

语法：

DROP INDEX <索引名> ON <表名>

ALTER TABLE <表名> DROP INDEX <索引名>

例如：

mysql> drop index idx on index_test;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

或者

mysql> alter table index_test drop index idx;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

三、索引类型

索引有很多种类型，可以为不同的场景提供更好的性能。在 MySQL 中，索引是在存储引擎层实现的，所以，并没有统一的索引标准：不同存储引擎的索引的工作方式也是不一样的，也不是所有的存储引擎都支持所有类型的索引。

从存储结构上来划分：

  • BTree 索引（B-Tree 或 B+Tree 索引）
  • 哈希索引
  • 全文索引（full-index）

从应用层次来分：

  • 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但允许有空值。
  • 复合索引：即一个索引包含多个列。

下面我们从索引的存储结构上，来看看 MySQL 支持的索引类型，底层是如何实现的，以及它们的优缺点。

MySQL 默认存储引擎是 Innodb，只显式支持 B-Tree 索引，对于频繁访问的表，Innodb 会透明建立自适应哈希索引，即在B树索引基础上建立哈希索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

1. B-Tree索引

当大家在谈论索引的时候，如果没有特别指明类型，多半说的是 B-Tree 索引，它使用B-Tree数据结构来存储数据，可以让系统高效的找到数据所在的磁盘块。

B 代表平衡(balance)，而不是二叉(binary)，因为 B-Tree 是从最早的平衡二叉树演化而来的。

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set

而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree 定义数据记录为一个二元组[key、data]：

  • key 为记录的主键，即表中的主键值，用于记录唯一的数据行，key 值是唯一且互不相同的。

  • data 为一行记录中除主键外的数据。

一棵 m 阶的 B-Tree 有如下特性：

  • 每个节点最多有 m 个孩子。
  • 除了根节点和叶子节点外，其它每个节点至少有 ceil(m/2) 个孩子。
  • 若根节点不是叶子节点，则至少有 2 个孩子。
  • 所有叶子节点都在同一层，且不包含其它关键字信息。
  • 每个非终端节点包含 n 个关键字信息（p0,p1,…pn,k1,…kn）
  • 关键字 key 的个数 n 满足：ceil(m/2)-1 <= n <= m-1
  • ki(i=1,…n) 为关键字，且关键字升序排序。
  • pi(i=1,…n) 为指向子节点的指针。p(i-1) 指向的子树的所有节点关键字均小于 ki，但都大于 k(i-1)。

注：ceil() 为取整函数。

B-Tree 中的每个节点根据实际情况，可以包含大量的键值 key、数据 data、和指针 p。如下图所示为一个 3 阶的 B-Tree 索引结构：

B-Tree索引举例示意图

每个节点占用一个磁盘块空间，一个节点上有两个升序排序的关键字 key 和三个指向子节点的指针 p，指针存储的是子节点所在磁盘块的地址。两个关键词 key 划分成为三个范围域对应的三个指针 p，并指向的子节点的数据的范围域。以根节点为例，关键字为 17 和 35，p1 指针指向的子节点的数据范围为小于 17，p2 指针指向的子节点的数据范围为 17~35，p3 指针指向的子节点的数据范围为大于 35。

模拟查找关键字为 29 数据行的过程：

  1. 根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】

  2. 比较关键字 29 在区间 （17,35），找到磁盘块 1 的指针 p2。

  3. 根据 p2 指针找到磁盘块3，读入内存。【磁盘 I/O 操作第 2 次】

  4. 比较关键字 29 在区间 （26,30），找到磁盘块 3 的指针 p2。

  5. 根据 p2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】

  6. 在磁盘块 8 中的关键字列表中找到关键字 29。

分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的关键字 key 是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。 B-Tree 相对于 AVLTree(高度平衡的二叉树)缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

2. B+Tree索引

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

从上一节中的 B-Tree 结构图中可以看到每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

B+Tree 相对于 B-Tree 有几点不同：

  • 非叶子节点只存储键值信息。

  • 所有叶子节点之间都有一个链指针。

  • 数据记录都存放在叶子节点中。

将上一小节中的 B-Tree 进行优化，由于 B+Tree 的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+Tree 后其结构如下图所示：

B+Tree索引举例示意图

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出 B+Tree 的优点，下面做一个推算：

InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占用 8 个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的K取值为 10^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护 10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

3. 哈希索引

哈希索引(hash index)，是基于哈希表实现的。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值(hash value)，不同键值的行计算出来的哈希值也不一样。哈希索引将所有的哈希值存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有 Memory 引擎显示支持哈希索引，同时哈希索引也是 Memory 存储引擎的默认索引类型，并且 Memory 存储引擎也是支持 B-Tree 索引。

如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希值。

继续以表 t_user 中的数据举例说明，并对字段 name 设置哈希索引。假设索引使用的哈希函数是 f()，则计算出来的哈希值(都是举例数据，并非真实数据)为：

f('xcbeyond')=2390

f('jack')=4010

f('tom')=5178

f('kitty')=1067

f('make')=7901

f('Mickey')=3079

f('Andy')=8301

计算出来的哈希值，会指向对应数据行的数据，指向关系如下图:

哈希索引举例示意图

执行如下查询，并能够查询到对应的数据。

mysql> select * from t_user where name = 'xcbeyond';
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | xcbeyond |  22 |
+----+----------+-----+
1 row in set

先计算出 xcbeyond 的哈希值，根据该哈希值寻找到对应指向的数据行。f('xcbeyond')=2390，所以MySQL在索引中查找 2390，并找到指向第 1 行的数据行，然后比较第 1 行的值是否等于 xcbeyond，以确保查找到数据的准确性。

**因为索引自身只需存储对应的哈希值，所有索引的结构十分紧凑，这也让哈希索引查找的速度非常快。**然而，哈希索引也有它的限制，即：索引失效。

  • 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序。
  • 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列 (A,B) 上都建立哈希索引，如果查询时只有数据列 A，则无法使用该索引。
  • 哈希索引只支持等比较查询，包括 =、in()，不支持任何范围、模糊查找，例如，where age > 20、where name like '%xc%'。
  • 如果哈希冲突很多的话，存储引擎必须进行链表来维护，维护这些链表的操作代价会很大，则查询的性能会很低。

4. 全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是比较索引中的值。

全文索引和其他类型索引的匹配方式完全不一样，它有许多需要注意的细节。更类似于搜索引擎做的事情，而不是简单的 where 条件匹配。

在相同的列上同时创建全文索引和基于值的B-Tree索引是不会有冲突的，全文索引适用于全文模糊搜索(MATCH AGAINST)操作，而不是普通的 where 条件操作。

四、索引优点

索引可以让 MySQL 服务器快速地定位到表的指定位置，但这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用。

最常见的 B-Tree 索引，按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的，所以 B-Tree 也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下优点：

  • 索引大大减少了 MySQL 服务器需要扫描的数据量。(全表扫描)
  • 索引可以帮助 MySQL 服务器避免排序和临时表。
  • 索引可以将随机 I/O 变为顺序 I/O。

索引是最好的解决方案吗？

索引并不总是最好的方案。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长，这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配，如可以采用表分区的方式。

如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录“哪个用户的信息存放在哪个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。对于大型系统，这是一个常用的技巧。

参考文章：

  1. https://www.jianshu.com/p/d67c637776d6
  2. https://cloud.tencent.com/developer/article/1125452