回表/索引覆盖/最左匹配/索引下推

回表

 

回表需要从innodb储存引擎说起，因为只在innodb储存引擎中会存在回表的情况，确切的说只有聚簇索引会出现回表问题，然mysql只有InnoDB支持聚簇索引

InnoDB储存引擎两种类型的索引

1. 聚簇索引

2. 普通索引

 

什么是聚簇索引：将数据和索引储存在一起，节点储存索引，叶子节点储存数据

普通索引：节点储存普通索引，叶子节点储存聚簇索引

 

聚簇索引的选取：

用主键作为聚簇索引

如果没有主键，那么选取第一个具有唯一约束的列（唯一索引）作为聚簇索引

如果上面两者都没有，那么Innode会隐式生成一个主键索引（row_id），作为聚簇索引

 

 

InnoDB储存索引的数据结构是 B+tree

B+tree结构如下

造成回表的原因是，普通索引只是存了，聚簇索引的索引值，因此进行搜索时是先

找到普通索引的值——》获取存储的聚簇索引的值——》再到维护的聚簇索引中查找聚簇索引的值——》拿到数据，这经历了一个重复查找的过程，因此成为回表

 

 

索引覆盖

利用索引覆盖来优化查询速度，索引覆盖的目标就是避免回表，因此索引覆盖也只是针对InnoDB储存引擎。

 

索引覆盖是指通过查询条件获取到需要返回字段的过程不需要进行回表查询，（查询条件的字段是索引，返回的字段是主键或联合索引）

 

例如

 select id,name from t_user where name="lili";

id是主键，name是普通索引

此时这里就不需要回表，因为普通索引中存放了聚簇索引的值(主键id)，不需要进行回表就可以拿到所需要的数据

 

例如

 select id,name,age from t_user where name="lili";

在这个查询中，age不是与name相关的联合索引，会产生回表，因此没有利用到索引覆盖的特性。

 

如何才能利用索引覆盖？使用联合索引

 select id,name,age from t_user where name="lili";

这个查询，只需要将name与age在建立表时进行联合索引，即可利用到索引覆盖的特性来提高查询速度。

 

 

最左匹配

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配

 

例如

 create table test_test(id int,A char(5),B char(6), C char(7),index(A,B,C))

联合索引的第一列为前缀

 

以最左匹配为原则，进行查询时使用 （A）（AB）(AC)，（ABC）会使用到索引，其它情况不会使用索引

A使用索引

 

B不使用索引

 

C不使用索引

 

AB使用索引

 

AC使用索引

 

BC不使用索引

这里的图忘截取了

ABC使用索引

 

 

索引下推（ ICP可用于InnoDB 和MyISAM表 ）

参考官网： https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

index condition pushdown

索引条件下推优化的适用性取决于以下条件：

• ICP用于 range， ref， eq_ref，和 ref_or_null访问方法时，有一个需要访问的全部表行。

• ICP可用于InnoDB 和MyISAM表。（例外：MySQL 5.6中的分区表不支持ICP； MySQL 5.7中已解决此问题。）

• 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I / O操作。对于 InnoDB聚集索引，完整的记录已被读入InnoDB 缓冲区。在这种情况下使用ICP不会减少I / O。

• 不能将引用子查询的条件下推。

• 涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。

• 触发条件不能下推

•  

 

 

假设一个表包含有关人员及其地址的信息，并且该表的索引定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的zipcode价值，但不确定姓氏，可以这样搜索：

 SELECT * FROM people
   WHERE zipcode='95054'
   AND lastname LIKE '%etrunia%'
   AND address LIKE '%Main Street%';

MySQL可以使用索引来扫描使用的人 zipcode='95054'。第二部分（lastname LIKE '%etrunia%'）不能用于限制必须扫描的行数，因此，如果没有“索引条件下推”，此查询必须为所有具有条件的人检索完整的表行 zipcode='95054'。

通过“索引条件下推”，MySQLlastname LIKE '%etrunia%'在读取整个表行之前会检查该 部分。这样可以避免读取与zipcode条件而不是 lastname条件匹配的索引元组对应的完整行 。

 

通过设置可以关闭索引下推

 SET optimizer_switch = 'index_condition_pushdown=off';
 SET optimizer_switch = 'index_condition_pushdown=on';