登录
首页 >  数据库 >  MySQL

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

来源：SegmentFault

时间：2023-02-25 09:16:19 227浏览 收藏

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《InnoDB引擎B+树索引使用和新特性》，介绍一下MySQL、Mysql索引、MySQL优化、B+树、InnoDB，希望对大家的知识积累有所帮助，助力实战开发！

我们已经讲过了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

数据库优化器大部分时候都工作的很有效和正确，但是专业的

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 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章：索引与算法

今天关于《InnoDB引擎B+树索引使用和新特性》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！

mysql MySQL Mysql索引 MySQL优化 B+树 InnoDB
声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表