Huangh's blog

重新认识 MySQL（一）

Posted on Mar 15, 2022

概述

MySQL 无疑是“八股文”的重灾区之一，默认存储引擎是 InnoDB 的 MySQL 实际上是什么样子？

MySQL 架构

mysql-architecture

这是 MySQL 架构图，结合应用程序开发经验，不难看出：

  • 客户端/服务端模型。
  • MySQL 的守护进程是 mysqld。
  • MySQL 服务端向客户端提供了 SQL 接口（包括 DDL、DML、DQL、DCL）。
  • MySQL 可插拔存储引擎，MySQL 8.0 默认存储引擎是 InnoDB（除非创建表时指定其它引擎）。
  • MySQL 服务端从上到下可分为三层次：服务层（SQL、解析、优化、缓存）、存储引擎、文件。
  • 存储引擎隐藏了数据库文件与内存缓冲池的复杂性，向服务层提供了统一的文件读写接口？

InnoDB 架构

InnoDB 至少有以下优势：

  • 遵循 ACID 模型，事务具有提交、回滚、崩溃恢复的功能以保护用户数据。
  • 行级锁与一致性读提高了多用户并发访问的性能。
  • 每张 InnoDB 表都有一个聚簇索引，能够最小化主键查询的 I/O 次数。
  • 支持外键约束来保持数据完整性。

innodb-architecture

这是 InnoDB 架构图，二分为在内存中的结构与在磁盘上的结构，

SQL 执行路径

一条普通的 SELECT 语句的旅程如下所示：

Figure_4-1._Execution_path_of_a_query

  1. 客户端输入语句。

  2. 消息通过 TCP/IP 线路。

  3. 服务端判定是否命中缓存，未命中则解析语句与生成执行计划。

  4. 服务端调用下层函数。

  5. 存储引擎读写文件。

索引篇

数据结构

索引是以额外的写入与空间为代价来加速数据查找的一类数据结构的统称。索引就像图书开头名为“目录”的书页或结尾名为“索引”的书页，读者想要查看某一内容，与其从第一页开始翻阅直到遇见目标书页为止，不如先在“索引”或“目录”查找关键词得到页码，有了页码就能快速定位关键词所在的书页。虽然隐喻便于理解索引的加速作用，但说服力有限，至少定性描述解决查找问题的数据结构与算法性能：

符号表性能

上图是著名的红皮书对所讲述的数据结构与算法时间复杂度的总结，顺序查找最慢，二叉树查找最坏的情况下退化成顺序查询，而有序数组的插入操作很慢，对于查找与插入较好的权衡是 2-3 树查找（红黑树）。散列表或哈希表表现如何？取决于哈希函数的实现，一般情况下，Hash Table 时间复杂度是 O(1)，几乎与 N 无关。

数据库系统（DBMS）不仅管理内存缓冲池（Buffer Pool），而且管理数据库文件（Database File）。由于计算机系统主存（Memory）与磁盘（Disk）的速度高度不对等，开发者选择数据结构不仅考虑发生在内存的查找与插入，而且高度注意磁盘 I/O 次数或页（Page）访问次数。

disk-oriented_dbms

页是一块连续、固定大小的数据。从关系数据库系统的角度来看，某一张表的某一行数据存在于某一页或某一些页中。关系数据库既然声称支持 SQL，那么至少满足以下查询需求：

FROM & JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

在 WHERE 或 HAVING 处，可以是等值条件，如 =，也可以范围条件，如 <、>、BETWEEN … AND …；GROUP BY 隐含了是否相等的比较；ORDER BY 要求有序或排序；SELECT 列表可以包含聚合函数，如 COUNT、SUM、MIN、AVG、MAX 等。

不止于支持等值查询、范围查询、有序操作的高性能的磁盘友好型数据结构是什么？MySQL InnoDB 开发者选择 B+ tree。

b_tree

B+ tree 从 B-tree 演化而来，两者都属于平衡查找树，不会出现过长的枝，结点更胖且树更矮有助于缩短搜索路径或减少搜索范围。B+ tree 叶子结点冗余存储键，叶子结点包含的键的范围如下所示：

[最小键或父母的较小键, 最大键或父母的较大键)

由于 B+ tree 叶子结点更胖且叶子结点之间相互链接组成有序链表，范围查询时定位到一个叶子结点后无需返回到父母结点，而是遍历有序链表，因此范围查找性能高于 B-tree。

受到 B+Tree index structures in InnoDB 的启发，确信 InnoDB 基于 B+ tree 实现索引，其结点被称之为页（Page），页包含多条记录（Record），记录由键（Key）与值（Value）组成，值的类型是元组（tuple）或指针（pointer）。除了用户插入的记录，Infimun 和 Supremun 都属于系统记录（system record），前者包含最大下界，后者包含最小上界。叶子页的记录包含行数据，非叶子页的记录包含指向其它页的指针，叶子页之间和非叶子页之间都存在链接，非叶子页为快速定位叶子页提供了导航。

叶子页逻辑结构：

B_Tree_Simplified_Leaf_Page

非叶子页逻辑结构：

B_Tree_Simplified_Non_Leaf_Page

假设创建一张表并插入一些记录：

CREATE TABLE t_btree (
  i INT NOT NULL,
  s CHAR(10) NOT NULL,
  PRIMARY KEY(i)
) ENGINE=InnoDB;

INSERT INTO t_btree (i, s) VALUES (0, "A"), (1, "B"), (2, "C");

这张表的索引如下所示（逻辑结构）：

B_Tree_Structure

不局限于二维表格，转移到索引的数据结构是解决问题的全新视角。

索引分类

按数据结构：

大多数 MySQL 索引（PRIMARY KEY、UNIQUE、KEY/INDEX）都存储在 B-Trees 中。例外：空间数据类型的索引使用 R-trees；MEMORY 表也支持 Hash 索引；InnoDB 对 FULLTEXT 索引使用倒排列表。

按存储器：

Adaptive Hash Index 在内存中，Clustered and Secondary Indexes 和 InnoDB Full-Text Indexes 在磁盘上。

按索引类（Index Class）：

Table_13.2_InnoDB_Storage_Engine_Index_Characteristics

按列的个数：

分成单列索引（column index）和复合索引（composite index），复合索引即多列索引。

如何使用

创建

用户要么在创建表时创建索引，要么在创建表后创建索引。一般情况下，MySQL 优化器足以通过统计信息从用户创建的索引当中选择最优的索引。

优化

每张 InnoDB 表都有一个聚簇索引（clustered index），却不一定有二级索引（secondary Index），前者有时被称为主键索引，后者有时被称为辅助索引。聚簇索引叶子页的记录包含整行数据，二级索引叶子页的记录包含主键值。

假设有一张名为 tab 的表，主键（PRIMARY KEY）是 pk，唯一键（UNIQUE）是 uk，普通索引（KEY/INDEX）的列是 k，无索引的列是 col：

pk uk k col
1 uk1 k1 col1
2 uk2 k2 col2
3 uk3 k3 col3

表 tab 的聚簇索引叶子页们表示为:

(1, uk1, k1, col1) ⇆ (2, uk2, k2, col2) ⇆ (3, uk3, k3, col13)

表 tab 的一个二级索引叶子页们表示为：

(uk1, 1) ⇆ (uk2, 2) ⇆ (uk3, 3)

表 tab 的另一个二级索引叶子页们表示为：

(k1, 1) ⇆ (k2, 2) ⇆ (k3, 3)

下面的语句执行只在一个聚簇索引上搜索：

SELECT * FROM tab WHERE pk = 3;

下面的语句执行依次在一个二级索引和一个聚簇索引上搜索：

SELECT * FROM tab WHERE uk = "uk3";

先在 uk 的索引找到 (uk3, 3) ，得到主键值为 3，再通过该主键值在 pk 的索引找到 (3, uk3, col13)。由此看来，索引访问次数的差异决定了通过主键查询可以比通过唯一键查询或普通索引列查询更快。应用程序不总是只通过主键查询数据，假设有一张学员表：

CREATE TABLE `student` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `last_name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `gender` tinyint unsigned NOT NULL DEFAULT '0',
  `ip_address` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `level` int NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

预期数据量足够大的情况下，如何创建索引以提高种一些条件查询的效率：email、level、email and level、level and email？假设 id 的索引名为 pk，若只创建普通索引：email_k，则下面的语句在 email_k 搜索命中后，仍然会在 pk 搜索：

select email, `level` from student where email = 'sharryms@edublogs.org';

若只创建普通索引 level_k，则下面的语句在 level_k 搜索命中后，仍然会在 pk 搜索：

select email, `level` from student where `level` = 59;

除了聚簇索引，是否存在包含 SQL 检索到的所有列的索引？MySQL 支持复合多个普通索引，可以创建多列索引。如果表拥有一个多列索引，优化器可以使用索引的任何最左前缀来查找行。例如，如果在 (col1, col2, col3) 上有一个三列索引，则在 (col1)、(col1, col2) 和 (col1, col2, col3) 上有均有索引。

使用索引 (col1, col2, col3) 的语句们：

SELECT * FROM tbl WHERE col1 = val1;
SELECT * FROM tbl WHERE col1 = val1 AND col2 = val2;

无可用索引的语句们：

SELECT * FROM tbl WHERE col2 = val2;
SELECT * FROM tbl WHERE col2 = val2 AND col3 = val3;

MySQL 官方还有一个非常经典的例子，创建一个索引：INDEX name (last_name, first_name)。

使用索引 name 的语句们：

SELECT * FROM student WHERE last_name = 'Jones';
SELECT * FROM student WHERE last_name = 'Jones' AND first_name = 'John';
SELECT * FROM student WHERE last_name='Jones' AND (first_name = 'John' OR first_name = 'Jon');
SELECT * FROM student WHERE last_name = 'Jones' AND first_name >='M' AND first_name < 'N';

无法使用索引 name 的语句们：

SELECT * FROM student WHERE first_name = 'John';
SELECT * FROM student WHERE last_name = 'Jones' OR first_name = 'John';

覆盖了查询语句中所有列的索引被称为覆盖索引（covering index），在中文语境，覆盖索引可以避免回表，回表指的是在一个索引上搜索完成后返回到另一个索引上搜索。如果在索引 (col1, col2, col3) 命中了 col1，那么可直接获取 col1、col2、col3 而无需继续在其它索引上搜索 col2 或 col3。

如果用 (email)、(level) 依次表示 email_k、level_k，那么 (email, level) 表示多个索引：(email)、(email, level)，反过来说，(level, email) 表示多个索引：(level)、(level, email)。对于优化器来说，level and email 和 email and level 等价，剩下 level 和 email 需要覆盖。

一般情况下，页包含的记录越短或者列长度之和越短，又或者索引占用空间越小，从磁盘加载到内存也就越快。数据类型 VARCHAR(M) 其中的 M 表示以字符为单位声明的非二进制字符串类型的列长度，例如，上文列 email 的数据类型是 varchar(50)，它表示可存储的最大字符数为 50，然而，一个字符等于多少字节则取决于字符集。列长度单位是字节数，如何计算可以参考数据类型存储要求。

既然过长字符串有可能降低查询性能，那有哪些优化方案？MySQL 支持索引字符串前缀，但是，前缀的区分度过低的话，很有可能增加扫描行数（rows examined），在最坏的情况下（最后一个键的值才全等），不仅在二级索引上遍历许多值的前缀相等的键，而且每次都要返回到聚簇索引上进一步查找和对比才能确认是否全等；由于回表，字符串前缀索引很少成为覆盖索引。如果后缀比前缀区分度更高，可以考虑倒序排列字符串，例如先倒排存储身份证号，将来查询需要反转输入字符串：

select column_list from t where id_card = REVERSE('input_id_card');

如果用 count(distinct col) 表示 col 的基数，那么 col 的区分度计算类似于：

select count(distinct col) / count(*) from t;

另一种方案是新增相应的哈希列，例如增加整数类型的列：身份证号的 CRC32，查询需要调用哈希函数，且需要组合原列处理可能发生碰撞的场景：

select column_list from t where id_card_crc = CRC32('input_id_card') and id_card = 'input_id_card';

简单总结一下四种索引字符串方式的优缺点：

索引字符串 优点 缺点
整串 扫描行数或回表次数较少。 空间占用较大。
前缀 空间占用较小。 可能增加扫描行数或回表次数。
倒序 无需新增列。 不支持范围查询；空间占用较大。
哈希 扫描行数或回表次数较少。 不支持范围查询；需要新增列。

有时，业务系统要求数据不重复，唯一索引实现了数据库层的唯一性约束。与普通索引相比，Change Buffer 无法缓冲对唯一索引的插入，除非设置 unique_checks = 0，因为唯一性检查要求必须将页从磁盘读入内存才能判断某值是否已存在。

INSERT、UPDATE 和 DELETE 都需要更改所有索引，出于这个原因，这些变更通常会被缓冲。页在缓冲池中被更改，而不立即刷入磁盘。删除记录时，会设置一个标志，因此不会立即在磁盘上删除记录。稍后，变更将由 InnoDB 后台线程刷入磁盘。已在内存中更改但尚未刷入磁盘的页被称为脏页，数据变更缓冲区被称为 Change Buffer。

普通索引组合 Change Buffer 是一种不错的优化：

  • 一张页可以在内存中多次更改，并且只能刷入磁盘一次。
  • 因为脏页们一起刷入磁盘，所以 I/O 次数比较少。

做正确的事情

优化具体查询语句的第一步不应直接套用上文所说的方式，而应该先使用 EXPLAIN，特别是理解 EXPLAIN 输出信息，比如是否使用索引、使用哪些索引、访问类型、预计扫描行数等。

当表 student 只有聚簇索引：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | id          | A         |      204700 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql> explain select * from student where id = 169951;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select email, `level` from student where email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298708 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select email, `level` from student where `level` = 59;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298708 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where email = 'sharryms@edublogs.org' and `level` = 59;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298708 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from student where `level` = 59 and email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298708 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当表 student 的二级索引只有 (email) 和 (level)：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | id          | A         |      204700 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | email_k  |            1 | email       | A         |      204700 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | level_k  |            1 | level       | A         |         101 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

mysql> explain select email, `level` from student where email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | email_k       | email_k | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select email, `level` from student where `level` = 59;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | level_k       | level_k | 4       | const | 2936 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where email = 'sharryms@edublogs.org' and `level` = 59;
+----+-------------+---------+------------+------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | email_k,level_k | email_k | 202     | const |    1 |     5.00 | Using where |
+----+-------------+---------+------------+------+-----------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where `level` = 59 and email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | email_k,level_k | email_k | 202     | const |    1 |     5.00 | Using where |
+----+-------------+---------+------------+------+-----------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当表 student 的二级索引只有 (email, level)：

mysql> show index from student;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY       |            1 | id          | A         |      204700 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | email_level_k |            1 | email       | A         |      298708 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | email_level_k |            2 | level       | A         |      298708 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

mysql> explain select email, `level` from student where email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k | email_level_k | 202     | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select email, `level` from student where `level` = 59;
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key           | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | student | NULL       | index | email_level_k | email_level_k | 206     | NULL | 298708 |    10.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where email = 'sharryms@edublogs.org' and `level` = 59;
+----+-------------+---------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k | email_level_k | 206     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where `level` = 59 and email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k | email_level_k | 206     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

当表 student 的二级索引只有 (email, level) 和 (level)

mysql> show index from student;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY       |            1 | id          | A         |      204700 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | email_level_k |            1 | email       | A         |      298708 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | email_level_k |            2 | level       | A         |      298708 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | level_k       |            1 | level       | A         |         101 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

mysql> explain select email, `level` from student where email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k | email_level_k | 202     | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

mysql> explain select email, `level` from student where `level` = 59;
+----+-------------+---------+------------+------+-----------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k,level_k | level_k | 4       | const | 2936 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from student where email = 'sharryms@edublogs.org' and `level` = 59;
+----+-------------+---------+------------+------+-----------------------+---------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key           | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+---------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k,level_k | email_level_k | 206     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+---------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where `level` = 59 and email = 'sharryms@edublogs.org';
+----+-------------+---------+------------+------+-----------------------+---------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key           | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+---------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | email_level_k,level_k | email_level_k | 206     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+---------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

本文首发于 https://h2cone.github.io/

参考