InnoDB调优-索引优化策略

1、覆盖索引

覆盖索引（Covering Index）也叫索引覆盖，最早是在 InnoDB Plugin 中完成的，MySQL5.0 版本之后InnoDB存储引擎开始提供支持。

所谓覆盖索引，即从辅助索引中就可以得到查询数据，而不需要查询聚集索引中的记录。简单的说，就是辅助索引包含了（或者说覆盖了）所有需要查询的字段的值。

InnoDB存储引擎 在使用辅助索引进行查询操作时，会先根据辅助索引查询到键值和聚集索引键（ 辅助索引的叶子节点包含键值和 bookmark ，而对于 InnoDB存储引擎来说 bookmark 就是相应行数据的聚集索引键），然后再通过聚集索引键去访问实际的数据文件。也就是说，这是进行了 2 次索引查找的。

辅助索引不包含整行记录的所有信息，所以其大小是要远比聚集索引小的；如果能够在第一次根据辅助索引查找的时候得到所要的数据，就能减少大量的IO操作。

在可以进行索引覆盖的情况下，优化器通常是不会选择通过聚集索引来进行查询的。例如表 t_test 同时存在聚集索引键 id 和 辅助索引键 index_n

create table t_test(
id bigint primary key not null auto_increment,
name varchar(50) not null,
age int,
phone varchar(50),
index index_n(name)
);

此时在进行查询时，如果能够满足覆盖索引，InnoDB存储引擎并不会使用聚集索引，而是会仅仅执行一次辅助索引来完成查询。例如这里对主键 id 、索引列 name 进行查询，因为这 2 列的值都可以在辅助索引上找到，所以这里只会执行一次索引查找

select id, name from t_test;
select id, name from t_test where name = '123456';

而如果在需要查询的列里面加入 phone ，因为 phone 的值无法在辅助索引中找到，此时便无法满足索引覆盖

select id, name， phone from t_test;

需要注意的是，在使用 count(*) 进行统计操作时，因为辅助索引远小于聚集索引，所以优化器同样也将会选择辅助索引

select count(*) from t_test;

另一方面，我们知道 InnoDB存储引擎 在根据多列索引（联合索引）进行查询时，索引的顺序十分重要；必须按照索引的最左列进行查找，而不能跳过某列。例如在下面这个表中，在列 name、phone 上建立了多列索引 index_np

-- 创建含有联合索引的表
create table t_test(
id bigint primary key not null auto_increment,
name varchar(50) not null,
age int,
phone varchar(50),
index index_np( name, phone)
);

此时要若使用索引，必须从最左列（这里即为列 name）开始，否则将无法使用

-- 有效的使用了索引
select * from t_test where name = '张三';

-- 不是最左列（跳过了多列索引的最左列name）无法使用索引
select * from t_test where phone = '123456';

但是在对其进行统计操作的时候，由于能够满足覆盖索引，此时的优化器将会使用索引

select count(*) from t_test where phone = '123456';

2、索引提示

MySQL数据库支持索引提示（Index Hint），显式地告诉优化器使用哪个索引。

在新版本的MySQL数据库中，优化器有了很大的进步，在绝大部分情况下工作得都非常有效和正确。但这并不意味着就不会出现因为错误地选择了某个索引，而导致SQL语句运行的很慢的情况；也无法避免在可以选择的索引非常多的时候，优化器选择执行计划时间的开销大于SQL语句本身的情况发生。

如果在认为使用辅助索引能够提升查询性能时，可以尝试使用 USE INDEX 或者 FORCE INDEX 提示优化器使用某个索引。例如表中有索引 i_n 和 i_p ， 默认情况下同时使用这2各索引进行查询，优化器会选择2者之一进行使用

create table t_test(
id bigint primary key not null auto_increment,
name varchar(50) not null,
age int,
phone varchar(50),
index i_n ( name),
index i_p ( phone)
);

select * from t_test where name = '张三' and phone = '123456';

使用 FORCE INDEX 指定优化器使用 i_p 索引后，优化器将会使用指定的索引进行查询

select * from t_test force index( i_p) where name = '张三' and phone = '123456';

需要注意的是， USE INDEX 只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。如果用户确定指定某个索引来完成查询，那么最可靠的是使用 FORCE INDEX ，而不是 USE INDEX 。

当然，MySQL数据库除了允许强制使用索引之外，还允许允许强制不使用索引，即忽略索引。例如对主键进行忽略，然后再根据主键去查询，此时优化器对数据进行查找便不会使用主键

select * from t_test ignore index ( PRIMARY) where id = 1;

3、Multi-Range Read（MRR）优化

Multi-Range Read（MRR）优化是 MySQL 5.6 版本时引入的新特性，其作用就是将随机访问转化为较为顺序的访问，以达到减少磁盘的随机访问的目的。

对于 InnoDB和MyISAM存储引擎 的范围查询和 JOIN 查询操作，MRR 会先将查询到的辅助索引值存放在一个缓存中，接着将缓存中的键值根据 RolwId 进行排序，最后根据 RolwId 的排序顺序来访问实际的数据文件。

启用 MRR 优化后，在查询执行计划中的 Extra 上能够看到有 Using MRR 标识

-- 表包含辅助索引键
create table t_test(
id bigint primary key not null auto_increment,
name varchar(50),
age int not null,
index ia( age)
);

-- 根据辅助索引进行范围查询
select * from t_test where age > 30 and age <= 100;

顺序访问数据除了能减少磁盘随机访问之外，还带来了其他好处。例如，在 InnoDB和MyISAM存储引擎 的缓冲池不足以放下一张表中的所有数据时，频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池；若是按照主键顺序进行访问，则可以将这一重复行为降为最低。

另一方面，MMR 还可以将某些范围查询拆分为键值对，以此来进行批量数据查询。在拆分的过程中过滤一些不符合查询条件的数据，使查询的性能得到提升。

Multi-Range Read优化 可以通过 optimizer_switch 变量中的 mrr 和 mrr_cost_based 标记（flag）来控制

set @@optimizer_switch='mrr=on,mrr_cost_based=off';

其中 mrr 标记表示是否启用 Multi-Range Read优化，on 为启用。而 mrr_cost_based 标记则表示是否通过 cost based 的方式来选择是否启用；若将 mrr 设为 on，mrr_cost_based 设为 off，则总是启用 Multi-Range Read优化。

Multi-Range Read优化可适用于range、ref、eq_ref类型的查询。

4、Index Condition Pushdown（ICP）优化

Index Condition Pushdown（ICP）优化同样是MySQL 5.6 版本引入的新特性，通过减少上层SQL层对记录的FETCH（索取），从而提高数据库的整体性能。

MySQL数据库根据索引进行查询操作时，首先是通过索引找到数据所在的页，然后把页读入到内存，再交由SQL层进行过滤，最后得到要查找的数据。

而在支持 Index Condition Pushdown 优化后，MySQL数据库会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 条件部分过滤操作放在了存储引擎层。

启用 Index Condition Pushdown 优化后，在根据辅助索引进行查询时会同时过滤 WHERE 条件，在 Extra 上能够看到有 Using index condition 标识

 select * from t_test where age between 20 and 40 and name = '张三';

Index Condition Pushdown优化默认是开启的，可以通过 optimizer_switch 变量的 index_condition_pushdown 标志（flag）对其进行控制

 set @@optimizer_switch='index_condition_pushdown=on';

Index Condition Pushdown优化支持 range、ref、eq_ref、ref_or_null 类型的查询，当前支持 InnoDB 和 MyISAM存储引。需要注意，Index Condition Pushdown优化在 MySQL5.6 版本不支持分区表。