mysql:索引使用优化

一、建立索引的依据

1. 高选择性 低选择性

• 低选择性: 如果一个字段，它可取值的范围很小，称为低选择性.
• 如果某个字段的取值范围很广，几乎没有重复，即属于高选择性

2. Cardinality

表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加B+树索引是非常有必要的。

mysql> show index from buy_log;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buy_log |          1 | userid   |            1 | userid      | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            1 | nameid      | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            2 | buy_date    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3. Cardinality统计原理

(1) 统计方式

对Cardinality的统计是放在存储引擎层进行的,通过采样（Sample）的方法来完成的。
采样的过程如下：

• 取得B+树索引中叶子节点的数量，记为A。
• 随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2，…，P8。
• 根据采样信息给出Cardinality的预估值：Cardinality=（P1+P2+…+P8）*A/8。

(2) 更新策略

发生INSERT和UPDATE时就去更新Cardinality信息,需要满足下面条件：

• 表中1/16的数据已发生过变化。
• stat_modified_counter＞2 000 000 000
  如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这这种情况。故在InnoDB存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数，当stat_modified_counter大于2 000 000 000时，则同样需要更新Cardinality信息。

(3) 更新Cardinality值

当执行SQL语句ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时会导致InnoDB存储引擎去重新计算索引的Cardinality值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新Cardinality值。

mysql> show index from buy_log;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buy_log |          1 | userid   |            1 | userid      | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            1 | nameid      | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            2 | buy_date    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> ANALYZE TABLE buy_log;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| learn.buy_log | analyze | status   | OK       |
+---------------+---------+----------+----------+

mysql> show index from buy_log;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buy_log |          1 | userid   |            1 | userid      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            1 | nameid      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| buy_log |          1 | idx_n_b  |            2 | buy_date    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4. 建立索引的依据

并不是在所有的查询条件中出现的列都需要添加索引。建立索引的一般性经验是:

• 在访问表中很少一部分时使用B+树索引才有意义
• 该字段具有高选择性
• 从表中取出较少一部分数据

二、优化器不使用索引的情况

在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。例如：

SELECT*FROM orderdetails WHERE orderid＞10000 and orderid＜102000;

上述这句SQL语句查找订单号大于10000的订单详情，通过命令SHOW INDEX FROM orderdetails，可观察到的索引如图5-28所示。

可以看到表orderdetails有（OrderID，ProductID）的联合主键，此外还有对于列OrderID的单个索引。上述这句SQL显然是可以通过扫描OrderID上的索引进行数据的查找。然而通过EXPLAIN命令，用户会发现优化器并没有按照OrderID上的索引来查找数据，如图5-29所示。

在possible_keys一列可以看到查询可以使用PRIMARY、OrderID、OrdersOrder_Details三个索引，但是在最后的索引使用中，优化器选择了PRIMARY聚集索引，也就是表扫描（table scan），而非OrderID辅助索引扫描（index scan）。

这是为什么呢？原因在于用户要选取的数据是整行信息，而OrderID索引不能覆盖到我们要查询的信息，因此在对OrderID索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然OrderID索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。
因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。

若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引，如：

SELECT*FROM orderdetails FORCE INDEX(OrderID) WHERE orderid＞10000 and orderid＜102000;

三、索引提示

1. 定义

MySQL数据库支持索引提示（INDEX HINT），显式地告诉优化器使用哪个索引。注意:这里仅仅是建议,优化器可以不采取

2. 使用索引提示条件

• MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢。这种情况在最新的MySQL数据库版本中非常非常的少见。优化器在绝大部分情况下工作得都非常有效和正确。这时有经验的DBA或开发人员可以强制优化器使用某个索引，以此来提高SQL运行的速度。
• 某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。例如，优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过Index Hint来强制使优化器不进行各个执行路径的成本分析，直接选择指定的索引来完成查询。

3. 使用索引提示例子

//表结构
mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//插入测试数据
INSERT INTO t SELECT 1,1;
INSERT INTO t SELECT 1,2;
INSERT INTO t SELECT 2,3;
INSERT INTO t SELECT 2,4;
INSERT INTO t SELECT 1,2;

mysql> explain SELECT*FROM t WHERE a=1 AND b=2;
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index_merge | a,b           | b,a  | 5,5     | NULL |    1 |   100.00 | Using intersect(b,a); Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+------------------------------------------------+

列possible_keys显示了上述SQL语句可使用的索引为a，b，而实际使用的索引为列key所示，同样为a，b。也就是MySQL数据库使用a，b两个索引来完成这一个查询。列Extra提示的Using intersect（b，a）表示根据两个索引得到的结果进行求交的数学运算，最后得到结果。

mysql> explain SELECT * FROM t USE INDEX(a) WHERE a=1 AND b=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | a             | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到，虽然我们指定使用a索引，但是优化器实际选择的是通过表扫描的方式。因此，USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。

mysql> explain SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b=2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ref  | a             | a    | 5       | const |    3 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

可以看到，这时优化器的最终选择和用户指定的索引是一致的。因此，如果用户确定指定某个索引来完成查询，那么最可靠的是使用FORCE INDEX，而不是USE INDEX。

四、索引失效情况

请牢牢记住以下口诀:
模型数空运最快

• 模:指的是模糊匹配,对于%开头的模糊匹配key,例如%key,索引会失效;但是形如key%,索引不会失效。
• 型:当sql语句where后使用的key数据类型和表中数据类型不一致时,索引会失效。比如where key = 1，表中key是vchar类型，索引会失效.这种工作中比较容易犯,应当重点注意.
• 数:指的是对索引的字段进行函数计算,比如形如where … avg(key)等，索引会失效
• 空:指的是索引的字段在表中可能存在空值,索引会失效
• 运:指的是对索引的字段进行相关的计算,例如加减等等,索引会失效
• 最:大名鼎鼎的联合索引最左匹配原则
• 快;当sql优化器估测不使用索引更快时
• 其他:比如范围查询 join 等也可能会导致索引失效

五、Multi-Range Read（MRR）优化

1. 定义

MySQL5.6版本开始支持Multi-Range Read（MRR）优化。Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。Multi-Range Read优化可适用于range，ref，eq_ref类型的查询。

2. MRR优化优点

MRR优化有以下几个好处：

• MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
• 减少缓冲池中页被替换的次数。
• 批量处理对键值的查询操作。

对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：

• 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
• 将缓存中的键值根据RowID进行排序。
• 根据RowID的排序顺序来访问实际的数据文件。

(1) 使数据访问变得较为顺序

如下面sql语句:

SELECT * FROM salaries WHERE salary＞10000 AND salary＜40000;

salary上有一个辅助索引idx_s，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查询。当不启用Multi-Range Read特性时，看到的执行计划如图5-34所示。

若启用Mulit-Range Read特性，则除了会在列Extra看到Using index condition外，还会看见Using MRR选项，如图5-35所示。

而在实际的执行中会体会到两个的执行时间差别非常巨大，如表5-4所示。

(2) 将范围查询拆分为键值对

Multi-Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，例如：

SELECT * FROM t WHERE key_part1＞=1000 AND key_part1＜2000 AND key_part2=1000;

表t有（key_part1，key_part2）的联合索引，因此索引根据key_part1，key_part2的位置关系进行排序。若没有Multi-Read Range，此时查询类型为Range，SQL优化器会先将key_part1大于1000且小于2000的数据都取出，即使key_part2不等于1000。待取出行数据后再根据key_part2的条件进行过滤。这会导致无用数据被取出。如果有大量的数据且其key_part2不等于1000，则启用Mulit-Range Read优化会使性能有巨大的提升。

倘若启用了Multi-Range Read优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（1000，1000），（1001，1000），（1002，1000），…，（1999，1000），最后再根据这些拆分出的条件进行数据的查询。

3. MRR设置

//查看相关设置
mysql> show variables like 'optimizer_switch'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: 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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

//用来控制键值的缓冲区大小，当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256K
mysql> show variables like 'read_rnd_buffer_size';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)

3. MRR举例

//表结构
 CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `nameid` int(11) DEFAULT NULL,
  `buy_date` date DEFAULT NULL,
  PRIMARY KEY (`userid`),
  KEY `idx_n` (`nameid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//测试数据
mysql> select * from buy_log;
+--------+--------+------------+
| userid | nameid | buy_date   |
+--------+--------+------------+
|      1 |      1 | 2009-01-01 |
|      2 |      1 | 2009-04-01 |
|      3 |      1 | 2009-02-01 |
|      4 |      1 | 2009-03-01 |
|      5 |      2 | 2009-01-01 |
|      6 |      3 | 2009-02-01 |
|      7 |      3 | 2009-01-01 |
+--------+--------+------------+

//按消耗开启(默认设置)
mysql>  explain SELECT * FROM buy_log  WHERE nameid>2 and buy_date='2009-02-01';
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | buy_log | NULL       | range | idx_n         | idx_n | 5       | NULL |    2 |    14.29 | Using index condition; Using where |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+

//始终开启MRR优化
mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off';

mysql> show variables like 'optimizer_switch'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: 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=off,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

mysql>  explain SELECT * FROM buy_log  WHERE nameid>2 and buy_date='2009-02-01';
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | buy_log | NULL       | range | idx_n         | idx_n | 5       | NULL |    2 |    14.29 | Using index condition; Using where; Using MRR |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------------------------+

五、Index Condition Pushdown（ICP）优化

1. 定义

Index Condition Pushdown同样是MySQL 5.6开始支持的一种根据索引进行查询的优化方式。之前的MySQL数据库版本不支持Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。

2. ICP优点

在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。

3. 效果对比

表5-5对比了在MySQL 5.5和MySQL 5.6中上述SQL语句的执行时间，并且同时比较开启MRR后的执行时间。

上述的执行时间的比较同样是不对缓冲池做任何的预热操作。可见Index Condition Pushdown优化可以将查询效率在原有MySQL 5.5版本的技术上提高23%。而再同时启用Mulit-Range Read优化后，性能还能有400%的提升！

3. ICP举例

//表结构
 CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `nameid` int(11) DEFAULT NULL,
  `buy_date` date DEFAULT NULL,
  PRIMARY KEY (`userid`),
  KEY `idx_n` (`nameid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

//测试数据
mysql> select * from buy_log;
+--------+--------+------------+
| userid | nameid | buy_date   |
+--------+--------+------------+
|      1 |      1 | 2009-01-01 |
|      2 |      1 | 2009-04-01 |
|      3 |      1 | 2009-02-01 |
|      4 |      1 | 2009-03-01 |
|      5 |      2 | 2009-01-01 |
|      6 |      3 | 2009-02-01 |
|      7 |      3 | 2009-01-01 |
+--------+--------+------------+

//Using index condition 就是表示ICP优化, 因为idx_n 是指nameid的索引
mysql>  explain SELECT * FROM buy_log  WHERE nameid>2 and buy_date='2009-02-01';
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | buy_log | NULL       | range | idx_n         | idx_n | 5       | NULL |    2 |    14.29 | Using index condition; Using where |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+