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

2020-03-23 11:11:09 浏览数 (1)

前期回顾：

MySQL性能优化(一)：MySQL架构与核心问题

MySQL性能优化(二)：选择优化的数据类型

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

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

一、什么是索引

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

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

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

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

代码语言：txt复制
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：

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

代码语言：txt复制
mysql> create table index_test(id int,ch varchar(32));
Query OK, 0 rows affected

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

代码语言：txt复制
mysql> create index idx on index_test(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

或者

代码语言：txt复制
mysql> alter table index_test add index idx(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

2.重建索引

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

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

例如:

代码语言：txt复制
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 <表名>

例如：

代码语言：txt复制
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 <索引名>

例如：

代码语言：txt复制
mysql> drop index idx on index_test;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

或者

代码语言：txt复制
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中可通过如下命令查看页的大小：

代码语言：txt复制
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索引结构：

每个节点占用一个磁盘块空间，一个节点上有两个升序排序的关键字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进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有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)，不同键值的行计算出来的哈希值也不一样。<u>哈希索引将所有的哈希值存储在索引中，同时在哈希表中保存指向每个数据行的指针。</u>

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

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

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

代码语言：txt复制
f('xcbeyond')=2390

f('jack')=4010

f('tom')=5178

f('kitty')=1067

f('make')=7901

f('Mickey')=3079

f('Andy')=8301

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

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

代码语言：txt复制
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，以确保查找到数据的准确性。

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

  • 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序。
  • 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(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
编程算法 存储 云数据库SQLServer 数据结构 全文检索

0 人点赞