乐趣区

InnoDB引擎B树索引使用和新特性

jiezi

我们已经讲过了 MySQL InnoDB 索引原理和算法，这里来说下如何管理和使用 B+ 树索引以及一些新的特性。

B+ 树索引的管理

我们在 InnoDB 引擎中常用的索引基本都是 B+ 树索引。

创建和删除索引

它的创建和删除有两种方法：

# 方式一：alter table, 此时 index 和 key 均可以，如果要求所有值均不重复，加上 unique
alter table tbl_name add [unique] index|key index_name (index_col_name,...);
alter table tbl_name drop index|key index_name;

# 方式二：create/drop index, 此时只能用 index
create index index_name on tbl_name (index_col_name,...);
drop index index_name on tbl_name;

修改索引

MySQL没有提供修改索引的命令，我们一般先删掉索引，再重建同名索引达到修改的目标。

查看索引

我们在查看数据表描述时可以看到都有哪些索引，有三种方法：

# 方法一：查看创建表的语句
mysql> show create table serviceHost;
| Table       | Create Table 
| t     | CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  `c` varchar(20) DEFAULT NULL,
  `d` varchar(20) DEFAULT NULL,
  `e` varchar(20) DEFAULT NULL,
  `f` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`),
  KEY `idx_b` (`b`),
  KEY `idex_cde` (`c`,`d`,`e`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

可以看到该表中有 4 个索引，主键集合索引(id)，唯一辅助索引(a)，单列辅助索引(b)，组合辅助索引(c,d,e)。

# 方法二：查看表的描述
mysql> describe t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| a     | varchar(20) | YES  | UNI | NULL    |                |
| b     | varchar(20) | YES  | MUL | NULL    |                |
| c     | varchar(20) | YES  | MUL | NULL    |                |
| d     | varchar(20) | YES  |     | NULL    |                |
| e     | varchar(20) | YES  |     | NULL    |                |
| f     | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

在这个表里可以更明显的看到各个字段的属性，注意 Key 这一列就代表索引，分别有 4 种值：
- PRI: 代表该列为主键
- UNI: 代表该列是唯一索引的第一列（唯一索引允许多个空值，非空值必须唯一）
- MUL: 代表非唯一索引的第一列
- 空: 代表该列没有索引

其中的 d,e 也为组合索引的一部分，为什么没有标识呢？是因为由于索引的特性，除了主键组合外，其余组合索引我们只标注第一个。

# 方法三：查看详细索引信息
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          0 | a        |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idx_b    |            1 | b           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idex_cde |            1 | c           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idex_cde |            2 | d           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idex_cde |            3 | e           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

这里的每一列的含义：

  • Table: 索引所在表名
  • Non_unique: 该列值是否唯一，但是 NULL 可以存在多个
  • Key_name: 索引的名字，drop时使用该名字
  • Seq_in_index: 索引中该列的位置
  • Column_name: 索引列的名称
  • Collation: 在索引中列存储的方式，值为 A 或者 NULL。B+ 树索引总是 A，即排序的（asc）。如果是Heap 存储引擎，并且建立了 Hash 索引，则为NULL。
  • Cardinality: 该索引中值去重后的估计数量。
  • Sub_part: 是否是列的部分被索引，如果部分索引，则为数字，否则为NULL。
  • Packed: 关键字如何被压缩。
  • Null: 索引列中是否包含 Null 值。
  • Index_type: 索引类型。InnoDB引擎中只支持 B+ 树索引，所以均为BTREE。
  • Comment: 索引注释。

联合索引

联合索引就是对多个列进行索引，它按照索引列次序进行依次有序。

我们直接查询索引中的字段显然能够直接使用这个索引：

mysql> explain select * from t where c='10' and d='10';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t     | ref  | idex_cde      | idex_cde | 126     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

我们在需要通过该索引排序时也会用到：

mysql> explain select c from t order by c desc limit 10;  
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | index | NULL          | idex_cde | 189     | NULL |   10 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

覆盖索引

InnoDB支持覆盖索引 （covering index），即从辅助索引中可以查询的记录，就不需要查询聚集索引的记录。由于辅助索引不包含整行数据，因此大小原小于聚集索引，而每页可以读取更多的数据，减少了大量页的置换，也就是减少大量的I/O 操作，即提高了效率。

但注意到，如果我们要选择的是整行数据，因为还要通过书签查询到聚集索引，所以优化器很可能不会使用我们的索引：

# 不仅仅使用了辅助索引，这个 index condition 我们后面会说，还使用了聚集索引
mysql> explain select * from t where c='10' and d='10';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t     | ref  | idex_cde      | idex_cde | 68      | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

# use index 代表使用了辅助索引
mysql> explain select c,d from t where c='10' and d='10'; 
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | idex_cde      | idex_cde | 68      | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

索引提示

如覆盖索引中的情况（Using where），可以理解为优化器没有选择使用索引而是使用了表扫描。如果使用索引扫描，后面还需要通过一次书签访问查找整行数据，通过书签查找的数据是无序的，成为离散读操作。而顺序读速度远远快于离散读，因此选择表扫描。但这也不是绝对的，当访问的数据量很小时，还是会选择辅助索引，而如果超过了20%（估值），则会通过表扫描。

# 我们改下 a 值为数字类型，可以选中范围
mysql> alter table t modify a int(11);
Query OK, 230 rows affected (0.07 sec)
Records: 230  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a>10;                          
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | a             | NULL | NULL    | NULL |  230 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t where a<10;  
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a             | a    | 5       | NULL |    8 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

数据库优化器大部分时候都工作的很有效和正确，但是专业的 DBA 可以强制使用某些索引，但有两种情况可以使用提示：

  • 数据库错误使用了索引，导致语句执行很慢，概率极低。
  • 索引非常多，优化器选择执行计划的时间会大于 SQL 执行时间，比如分析 range 查询就很耗时。此时提示可以省去成本分析的过程，直接使用指定索引来执行。

索引提示有两种程度：

# 索引提示（index hint），只是告诉优化器可以选择，但优化器还会自己判断，不一定使用
mysql> explain select * from t use index(a) order by a; 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |  230 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

# 强制使用索引（index force），保证最终和用户选择的是一致的
mysql> explain select * from t force index(a) order by a;   
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | index | NULL          | a    | 5       | NULL |  230 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

索引参数

上面我们对索引的所有参数都进行了说明，但有几个参数需要我们格外注意。

列头部分索引：Sub_part

我们一般对整列数据进行索引，但如果内容很长，我们也可以只对列的开头部分进行索引：

mysql> drop index idx_b on t;
mysql> create index idx_b on t (b(10));
mysql> show index from t where Key_name='idx_b';
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          1 | idx_b    |            1 | b           | A         |           0 |       10 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

可以看到我们只对 b 这列的前 10 个字符进行索引，Sub_part字段也变为了10。

唯一值估计数目：Cardinality

Cardinality(基数)非常关键，常常是我们判断是否使用这个索引的依据。它表示索引中不重复记录数量的预估值。也可以成为散列程度。

我们在刚才的表中随机插入一些数据到列 b，从100-1000 之间，其余列均为 NULL，id 列自增：

mysql> select count(b) from t;
+----------+
| count(b) |
+----------+
|      110 |
+----------+
1 row in set (0.00 sec)
mysql> select distinct(b) from t;
+------+
| b    |
+------+
| 100  |
| 200  |
| 300  |
| 400  |
| 500  |
| 600  |
| 700  |
| 800  |
| 900  |
| 1000 |
+------+
10 rows in set (0.00 sec)
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |         110 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          0 | a        |            1 | a           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idex_cde |            1 | c           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idex_cde |            2 | d           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idex_cde |            3 | e           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | idx_b    |            1 | b           | A         |          20 |       10 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

可以看到 id 这列的散列值为 110，b 这列的散列值为20，其余都为2。这里理论上应该为10 和1，至于为什么翻倍，我没有找到答案。如果找到后续会更新。

该值的计算方法为：

  1. InnoDB引擎取得 B+ 树索引中叶子节点的数量，即为A。
  2. 随机取得 8 个叶子节点，统计每页不同记录的个数，为P1,p2,...P8.
  3. Cardinality = (P1+P2+...+P8) / 8 * A。

由于是抽样的方法，所以可能每次取的值不同。

另外我们在上一节中说过，由于索引的更新操作非常频繁，因此我们通过一定的策略来更新该值：

  • 表中 1/16 的数据已发生过变化。
  • stat_modified_counter>2 000 000 000(M, 二十亿次 CUD 操作)

当我们想要手动更新该值时，可以执行下面的任何一种：

analyze table;（推荐）show table status;
show index;
访问 information_schema 下的 tables/statistics；

数据库索引统计时，有可能会发生问题，导致值为NULL，此时手动更新下即可。由于统计这个会影响统计时刻的性能，如果我们能在低峰期对关键表批量手动更新，可以让索引更好的为你服务：ananlyze tables;

相关参数

# 设置统计时的采样页数量，默认为 8
innodb_stats_sample_pages
# 判断如何处理 NULL 值，默认为 nulls_equal，视为相等记录。innodb_stats_method = nulls_equal|nulls_unequal|nulls_ignored

优化索引的使用

我们前面说过，由于辅助索引不存储行数据，辅助索引除非是只查询索引列，否则还是要索引到聚集索引（主键索引）上来查找。而同时主键索引的连续读（双向链表）比辅助索引的跳读效率更高，因此如果存储引擎认为该索引并不能有效提升效率的话，会直接使用全表扫描。

B+树的特性决定了只有在访问表中很少一部分数据时才有意义。比如性别有两种，我们通过该索引查询时，一般均为 50% 的数据，此时成为低选择性的字段，这时的索引是完全没有意义的。而如果是姓名，则重复的很少，此时属于高选择性，则使用该索引是非常合适的。

我们可以通过 Cardinality 字段来决定是否使用索引，取Cardinality/rows_in_table，如果该值接近1，则很适合建立索引，而如果该值接近于0，则应该考虑删掉该不必要的索引。

根据上面的实例，测试下：

mysql> describe select * from t where id=10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> describe select * from t where b=10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | idx_b         | NULL | NULL    | NULL |  230 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意 possible_keys 字段，代表可能采用的索引，即该列存在的索引。而 key 字段代表实际采用的索引。最后一个 Using where 值代表采用了全表扫描。

索引优化新功能

在 5.5、5.6 版本中加入了一些新的特性，来从引擎内部优化 SQL 执行速度。
先说下数据库的三种操作类型：

  • DDL（Data Definition Languages）: 数据定义语句，如数据库、表、列、索引的变化，命令为 create/drop/alter 等。
  • DML（Data Manipulation Languages）: 数据操纵语句，如数据库的增删改查，命令为 insert/update/delete/select 等。
  • DCL（Data Control Languages）: 数据控制语句，如设置权限，命令为 grant/revoke 等。

Fast Index Creation （快速索引创建）

5.5之前版本中索引的添加或删除之类的 DDL 操作过程为：

  1. 创建一张新表，表结构为通过 alter table 新定义的结构
  2. 然后把原表中数据导入到临时表
  3. 删除原表
  4. 最后把临时表重命名为原表名

可以看到，如果表的数据非常大，这个过程也会非常耗时，而此时的数据库服务会因为锁而不可用。而 5.5 之后支持了一种 FIC 的索引创建方式，对于辅助索引的创建，会对该表加上一个 S（shared） 共享锁，不需要重建表，因此速度提高很多，可用性也得到了提高。

但注意此时只能读操作，写操作依然不可用，同时该方法只针对辅助索引，对主键依然需要重建。删除索引操作，只需要 InnoDB 删除内部视图对该表的索引定义，并将索引空间标记为可用即可。

Online Schema change（在线架构改变）

OSC最早是 Facebook 用PHP脚本实现的，可以在有读写事务对表进行操作的时候执行其他事务，提高了数据库在 DDL 操作时的并发性。其步骤：

  1. init：初始化阶段，对表做验证工作，如果表没有主键或者存在触发器或外键，则无法使用。
  2. createCopyTable：创建和原始表结构一样的新表
  3. alterCopyTable: 对新表进行操作，如添加索引或列等
  4. createDeltasTable: 创建 deltas 临时表，之后所有的 DML 操作会被记录到该表中
  5. createTriggers: 对原表创建触发器，所有 DML 操作产生的记录会被写入到 deltas 表
  6. startSnapshotXact: 开始 OSC 操作的事务
  7. selectTableIntoOutfile: 将原表数据写入到新表。为了减少原表的锁定时间，通过分片将数据输出到多个外部文件，然后将文件数据导入到新表中。
  8. dropNCIndexs: 在导入到新表前，删除新表中所有的辅助索引。
  9. loadCopyable: 将导出的分片文件导入到新表
  10. replayChanges: 将 OSC 过程中存在 deltas 表中的 DML 操作记录应用到新表。
  11. recreateNCIndexes: 重新创建辅助索引
  12. replayChanges: 重放日志，将在创建辅助索引过程中的新日志回放。
  13. swapTables: 原表和新表交换名字，需要锁定两张表，由于改名很快，因此阻塞很短。

这个功能有些局限性，同时由于可以在执行过程中设置set sql_bin_log=0，导致主从不一致的情况发生。

Online DDL（在线数据定义）

前面两种功能，FIC还是会阻塞一些 DML 操作，OSC有很大局限性，因此 5.6 之后支持了 Online DDL 功能。它允许创建辅助索引的时候，允许 DML 的操作，极大提高了可用性。它支持的几种变更操作：

  • 辅助索引的增删
  • 自增长值的变更
  • 添加和删除外键约束
  • 重命名列

它支持 3 种算法和 4 种锁：

 alter table t add key (f), ALGORITHM=DEFAULT|INPLACE|COPY, LOCK=DEFAULT|NONE|SHARED|EXCLUSIVE;

算法：

  • COPY：老版本方法，即创建临时表
  • INPLACE：不需要创建临时表
  • DEFAULT：会根据 old_alter_table 参数来判断是否采用老方法，默认为off 
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.39    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'old_alter_table';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

4种锁：

  • NONE：不加锁，获得最大并发度。
  • SHARE：和 FIC 一样，可并发读，不可并发写。
  • EXCLUSIVE：加上一个 X 锁（排它锁），阻塞所有线程。
  • DEFAULT：会依次判断是否可用，争取最大并发度，NONE > SHARE > EXCLUSIVE。

Online DDL的原理是在 DDL 操作时，将 DML 操作日志写在缓存中，待操作完成后，在重做到表上达到数据的一次性。缓存的大小由 innodb_online_alter_log_max_size 参数来控制。

Multi-Range Read优化

MRR优化是为了减少磁盘的随机访问，并将其转换为顺序的数据访问，能够带来极大的性能提升。主要体现在减少缓存池中页被替换的次数和批量处理对键值的查询操作。对于 InnoDB 和MyISAM存储引擎的范围查询和 JOIN 查询，其原理为：

  1. 将查询得到的辅助索引键值存在缓存中。
  2. 将缓存的键值根据 RowID 来进行排序。
  3. 根据 RowID 的排序顺序来访问实际的数据文件。

该优化主要体现在使用索引的范围查询，但是是查询整行数据：

mysql>  explain select * from t where a<50 and a > 30; 
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                            |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | t     | range | a             | a    | 5       | NULL |   18 | Using index condition; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+
1 row in set (0.00 sec)

该特性效率可以提高数十倍，但有时候这个特性可能并未启动，通过 optimizer_switch 中的标记来控制。当 mrr=on 时，表示启用 MRR 优化；当 mrr_cost_based=off 时，表示总启用，=on时，根据优化器计算的代价来选择是否启用。

mysql> select @@optimizer_switch;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @@optimizer_switch='mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec)

Index Condition Pushdown（索引合并过滤）

ICP优化是提前进行索引条件的过滤，将部分过滤放在存储引擎层，减少对行数据的读取，从而提高性能。

# 在执行前需要将强制执行 MRR 优化关闭
mysql> set @@optimizer_switch='mrr_cost_based=on';       
Query OK, 0 rows affected (0.00 sec)

mysql>  explain select * from t where c='50' and d > 30; 
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | idex_cde      | idex_cde | 68      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

可以看到，我们使用了 ICP 优化，在 5.6 版本之前，会先过滤 c='50' 的条件，在读取所有符合的行记录，然后过滤 d>30。而在新特性中，会检测到 c 和 d 有联合索引，因此提前过滤，并一次性读取行记录。该特性可以提高23% 的性能，可以与 MRR 共同使用。

参考资料

  1. mysql 5.6 原生 Online DDL 解析：http://seanlook.com/2016/05/2…
  2. mysql X 锁和 S 锁：https://www.jianshu.com/p/342…
  3. 详解 MySQL—DDL 语句、DML 语句与 DCL 语句：https://www.cnblogs.com/zhang…
  4. MySQL 技术内幕 InnoDB 存储引擎 第 2 版》第 5 章：索引与算法
退出移动版