《MySQL系列-InnoDB引擎35》索引与算法-B+树索引的使用

B+树索引的使用

1 不同应用中B+树索引的使用

在OLTP中，B+树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B+树索引才是有意义的，否则即使建立了，优化器也可能不选择使用索引。

在OLAP中，如果是复杂的查询，要涉及多张表之间的连接操作，因此添加索引是有意义的。但是如果连接使用的hash join，那么索引可能又变的不那么重要了。不过在OLAP中，通常会需要对时间字段进行索引，这是因为大多数统计需要根据时间维度来进行数据的筛选。

2 联合索引

联合索引是指对表上的多个列进行索引。联合索引的创建方法和单个索引的创建方法一样，不同之处仅在于有多个索引列。

比如：对创建索引index_a_b (a,b),那么在查询的时候select * from table where a=xx and b=xx是可以使用(a,b)这个索引的。对于单列的查询select * from table where a=xxx也可以使用这个(a,b)索引。但是select * from table where b=xxx是用不到联合索引的。

联合索引的另一个好处就是已经对第二个键值进行了排序处理。例如：使用(userid,sys_date)联合索引，在查询userid并且要求有序的时候，就会默认使用(userid,sys_date)索引。

1）创建索引

mysql> create table index_test(-> userid int unsigned not null,-> sys_date date,-> key key_u (userid),-> key key_u_s (userid,sys_date)-> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)

2）插入数据

mysql> insert into index_test values(1,'2020-01-01');
mysql> insert into index_test values(2,'2022-01-01');
mysql> insert into index_test values(3,'2022-05-01');
mysql> insert into index_test values(4,'2021-05-01');

3）查询userid，不要求排序

只查询userid的时候，可以看到在possible_keys中可以有两个索引提供使用，分别是单个userid索引的ley_u和(user_id,sys_date)的联合索引key_u_s。但是最终优化器选择是userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。

mysql> explain select * from index_test where userid = 2\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: index_testpartitions: NULLtype: ref
possible_keys: key_u,key_u_skey: key_ukey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)

4）查询userid，要求排序

当查询userid，并要求排序的时候，possible_keys既可以使用key_u索引，也可以使用key_u_s索引。但是优化器选择了ley_u_s索引，因为这个联合索引中sys_date字段已经排序好了。只需要根据联合索引取出数据，无须再对sys_date做一次额外的排序操作。

mysql> explain select * from index_test where userid = 2 order by sys_date desc limit 3\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: index_testpartitions: NULLtype: ref
possible_keys: key_u,key_u_skey: key_u_skey_len: 4ref: constrows: 1filtered: 100.00Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

5）查询userid，要求排序，并强制使用key_u索引

如果是强制使用key_u索引，可以在Extra中看到有using filesort，即需要额外一次排序才能完成查询，而这次显然需要对列sys_date排序。

mysql> explain select * from index_test force index(key_u) where userid = 2 order by sys_date desc limit 3\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: index_testpartitions: NULLtype: ref
possible_keys: key_ukey: key_ukey_len: 4ref: constrows: 1filtered: 100.00Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

3 覆盖索引

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

例如：当一个表里有聚集索引和辅助索引时，在count数据的时候，innodb会自动优化使用辅助索引，而不是使用聚集索引。

4 优化器选择不适应索引的情况

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

5 索引提示

MySQL数据库支持索引提示(index hint)，显式的告诉优化器使用哪个索引。大概两种情况下需要用到索引提示：

• MySQL数据库的优化器错误的选择了某个索引，导致SQL语句运行很慢。对于目前的SQL版本，可能很少会遇见这种问题。如果存在的话，可以强制优化器使用某个索引，以此来提高运行的速度。
• 某SQL语句可以选择的索引非常多，这时优化器选择执行时间的开销可能会大于SQL语句本身。例如，优化器分析range查询本身就是比较耗时的操作。这时可以通过Index Hint来强制优化器不进行各个执行路径的成本分析，直接指定索引完成查询。

使用force index来指定索引是可行的。

6 Multi-Range Read优化

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

MRR优化的好处：

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

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

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

此外，若InooDB存储引擎或者MyISAM存储引擎的缓冲池不是足够大，即不能存放下一张表中所有的数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断的被读入缓冲池。若是按照主键顺序进行访问，则可以将此重复行为降为最低。

是否启用Multi-Range Read优化可以通过参数optimizer_switch中的标记来控制。当mrr=on时，表示启用multi-range read优化。mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。

若mrr设为on，mrr_cost_based设为off，则总是启用multi-range read优化。例如可通过如下指令设置multi-range read优化总是处于开启状态：

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

参数read_rnd_buffer_size用来控制键值的缓冲区大小，当大于该值时，则执行器对已经缓冲的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256K。

mysql> select @@read_rnd_buffer_size\G;
*************************** 1. row ***************************
@@read_rnd_buffer_size: 262144
1 row in set (0.00 sec)

7 Index Condition Pushdown (ICP)优化

和Multi-Range Read一样，Index COndition Pushdown也是MySQL5.6开始支持的查询优化的方式。之前在进行索引查询的时候，首先根据索引来查找记录，然后根据where条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在去除索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在存储引擎层。在某些查询下，可以大大减少上层SQL对记录的索取(fetch)，从而提高数据库的整体性能。

Index Condition Pushdown优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。当优化器选择Index Condition Pushdow优化时，可在执行记录的列Extra看到Using index condition的提示。