MySQL Glossary

index 索引

一种为 表的行 提供快速查找能力的数据结构 ，通常通过形成 表示特定 列或列集的 所有值的树结构 ( B-tree) 。

InnoDB表总是有一个 代表主键的聚集索引。它们还可以在一个或多个列上定义一个或多个二级索引。根据其结构，二级索引可以分为 部分索引、 列索引或 复合索引。

索引是 查询性能的一个关键方面。数据库架构师设计表、查询和索引以允许快速查找应用程序所需的数据。理想的数据库设计在可行的情况下使用覆盖索引 ；查询结果完全根据索引计算，无需读取实际的表数据。每个 外键约束还需要一个索引，以有效地检查父表和 子表中是否存在值。

虽然 B 树索引是最常见的，但哈希索引使用了不同类型的数据结构，如MEMORY存储引擎和InnoDB 自适应哈希索引。 R树索引用于多维信息的空间索引。

clustered index 聚集索引

主键索引的 InnoDB 术语 。InnoDB 表存储是根据主键列的值组织的，以加快涉及主键列的查询和排序。为获得最佳性能，请根据对性能最关键的查询仔细选择主键列。因为修改聚集索引的列是一项昂贵的操作，所以选择很少或从不更新的主列。

secondary index 二级索引

一种表示表列子集的InnoDB 索引。一个InnoDB表可以有零个、一个或多个二级索引。（与聚集索引相反 ，每个InnoDB表都需要它存储所有表列的数据。）

二级索引可用于满足只需要索引列中的值的查询。对于更复杂的查询，它可用于识别表中的相关行，然后使用聚集索引通过查找来检索这些行。

传统上，创建和删除二级索引会因复制 InnoDB表中的所有数据而产生大量开销。快速索引创建功能使InnoDB的二级索引的CREATE INDEX和DROP INDEX语句更快。

unique index 唯一索引

具有唯一约束的一列或一组列的索引。因为已知索引不包含任何重复值，某些类型的查找和计数操作比普通类型的索引更有效。大多数针对此类索引的查找只是为了确定某个值是否存在。索引中的值的数量与表中的行数相同，或者至少与关联列的非空值的行数相同。

更改缓冲优化不适用于唯一索引。作为一种解决方法，您可以unique_checks=0 在将批量数据加载到InnoDB表中时临时设置。

FULLTEXT index 全文索引

MySQL 全文搜索 机制中保存搜索索引的一种特殊索引。表示列值中的单词，省略任何指定为 stopwords 的单词。最初，仅适用于 MyISAM 表。从 MySQL 5.6.4 开始，它也可用于 InnoDB 表。

column index 列索引

单个列上的索引。

composite index 复合索引

包含多个列的索引。

hash index 哈希索引

一种索引类型，用于使用相等运算符而不是范围运算符（例如大于或BETWEEN）的查询。它可用于 MEMORY 引擎的表。尽管由于历史原因哈希索引是 MEMORY表的默认值，但该存储引擎还支持 B 树索引，这通常是通用查询的更好选择。

MySQL 包括这种索引类型的一个变体， 自适应哈希索引，如果需要，它会根据运行时条件为 InnoDB 表自动构建。

Fast Index Creation 快速创建索引

InnoDB Plugin 中首次引入的一项功能，现在是 MySQL 5.5 及更高版本的一部分，通过避免完全重写关联表的需要来 加快 InnoDB 二级索引的创建。加速也适用于删除二级索引。

因为索引维护会增加许多数据传输操作的性能开销，所以请考虑执行操作，例如 ALTER TABLE … ENGINE=INNODB或 INSERT INTO … SELECT * FROM …不使用任何二级索引，然后再创建索引。

在 MySQL 5.6 中，这个特性变得更加通用。ALTER TABLE 您可以在创建索引时读取和写入表，并且可以在不复制表、不阻塞DML操作或两者兼有的情况下执行更多种类的操作。因此在 MySQL 5.6 及更高版本中，这组功能被称为在线 DDL ，而不是快速索引创建。

adaptive hash index 自适应哈希索引

通过在内存中构建 哈希索引，可以使用和 运算符InnoDB加速查找 的表的优化。MySQL 监视表的索引搜索，如果查询可以从哈希索引中受益，它会自动为 经常访问的索引页面构建一个。从某种意义上说，自适应哈希索引在运行时配置 MySQL 以利用充足的主存，更接近主存数据库的架构。

此功能由 innodb_adaptive_hash_index 配置选项控制。由于此功能有利于某些工作负载而不是其他工作负载，并且用于哈希索引的内存保留在缓冲池中，因此通常您应该启用和禁用此功能进行基准测试。

column prefix 列前缀

当创建索引的时候带上长度，例如 CREATE INDEX idx ON t1 (c1(N))，只有前面的 N 个字符存储到索引中。保持索引前缀小可以使索引更紧凑，而内存和磁盘 I/O 的节省有助于提高性能。（尽管使索引前缀太小会阻碍查询优化，因为会使具有不同值的行在查询优化器看来是重复的。）

对于包含二进制值或长文本字符串的列，排序不是主要考虑因素，将整个值存储在索引中会浪费空间，索引会自动使用值的前 N（通常为 768）个字符进行查找和排序。

回表

二级索引未覆盖查询字段，需要通过主键索引查询记录，这个过程叫回表。索引覆盖会避免回表。

索引覆盖 covering index

索引覆盖就不用回表

包含查询检索到的所有列 的索引。查询不是使用索引值作为指针来查找完整的表行，而是从索引结构中返回值，从而节省磁盘 I/O。 InnoDB可以将这种优化技术应用于比 MyISAM 更多的索引，因为 InnoDB 二级索引也包括 主键列。 InnoDB在事务结束之前，不能将此技术应用于针对事务修改的表的查询。

给定正确的查询，任何列索引或 复合索引都可以充当覆盖索引。设计您的索引和查询以尽可能利用这种优化技术。

EXPLAIN 输出中的列 Extra 显示 Using index

最左匹配

下面

1
2
3
4
5
6
7

CREATE INDEX idx_id_name ON user (name, age);
EXPLAIN SELECT * FROM t_user WHERE name = '' and age = 1;
EXPLAIN SELECT * FROM t_user WHERE name = '';
-- 不会用到索引
EXPLAIN SELECT * FROM t_user WHERE age = 1;
-- 查询优化器会
EXPLAIN SELECT * FROM t_user WHERE age = 1 AND name = '';

索引条件下推 Index Condition Pushdown Optimization

索引条件下推 (ICP) 是针对 MySQL 使用索引从表中检索行的情况的优化。如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估WHERE行的条件。启用 ICP 后，如果部分 WHERE条件可以仅使用索引中的列进行评估，则 MySQL 服务器会推送这部分WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足这一条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

Multi-Range Read Optimization MRR

MRR 当表很大并且未存储在存储引擎的缓存中时，使用二级索引上的范围扫描读取行可能会导致对基表的许多随机磁盘访问。通过磁盘扫描多范围读取 (MRR) 优化，MySQL 尝试通过首先仅扫描索引并收集相关行的键来减少范围扫描的随机磁盘访问次数。然后对键进行排序，最后使用主键的顺序从基表中检索行。Disk-sweep MRR 的动机是减少随机磁盘访问的次数，而是实现对基表数据的更顺序扫描。

多范围读取优化提供了以下好处：

• MRR 允许基于索引元组按顺序访问数据行，而不是按随机顺序访问。服务器获取一组满足查询条件的索引元组，按照数据行ID顺序进行排序，并使用排序后的元组按顺序检索数据行。这使得数据访问更高效且成本更低。

• MRR 支持对需要通过索引元组访问数据行的操作的键访问请求进行批处理，例如范围索引扫描和使用索引作为连接属性的等连接。MRR 遍历一系列索引范围以获得合格的索引元组。随着这些结果的累积，它们用于访问相应的数据行。在开始读取数据行之前不必获取所有索引元组。

EXPLAIN 输出中的列 Extra 显示 Using MRR。

Example Databases

Example Databases

下载 sakila 导入测试用

查询优化器

从原来MySQL的只采用基于规则的优化器到目前的基于代价的优化器，实践证明对于大数据量复杂的查询，效果还是比较明显的。同时可也看到随着MySQL8.0的推出，MySQL也加入了一些新指标，比如直方图等，使得代价优化越来越准确，越来越好了。

• https://dev.mysql.com/doc/refman/8.0/en/glossary.html
• https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html