mysql如何减少回表提高查询效率(覆盖索引和延时关联)

mysql的索引类型

    mysql支持的索引类型为：主键索引(key)、全文索引(FULLTEXT)、普通索引(NORMAL)、空间索引(SPATIAL)、唯一索引(UNIQUE)。无论使用什么方式的索引，都是通过hash或者b-tree来实现索引的存储结构。

• 主键索引：主键索引又称为聚簇索引通过B+tree的方式实现，并把索引和数据保存在一起。

• 全文索引：主要是匹配文本中的关键字，建议谨慎使用，数据量比较大的时候可以使用elasticsearch或者其他的效率更高的搜索引擎。

• 普通索引：又称为二级索引，没有任何限制，主要是加速查询速度。

• 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

• 唯一索引：具有唯一的特点，无论使用任何方式实现索引，都需要保持全局唯一，如果是复合索引，则要保证复合索引的每一列都是唯一值

无论使用哪一种索引类型，都避免不了使用hash或者b-tree作为索引的存储结构。都需要按照以此为作为目标合理的使用索引：

1. 减少服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机IO变成为顺序IO

innndb索引

   innodb中的索引中b-tree存储结构是使用B+TREE的方式实现的：都知道innodb是支持聚簇索引，主键索引和数据保存在一起按照b+tree的方式存储，并且是有序的，无论是查询，还是排序在主键上面速度都比较快，则其他的索引类型在innodb中只存储了被索引的字段和主键。

例如：在一个表(table1)中有四个字段，id、A、B 、C id为主键索引自增，建立复合索引(A-B)

1：查询id=1的所有值 这：select * from table1 where id = 1(只需要直接筛选主键索引就可以了，就可以拿到说需要的数据)

2：查询A=2的，A、B的值 select A,B from tables where A = 2(只需过滤(A-B)索引，获取A、B的值，这样查询就使用了复合索引的最左前缀原则，也使用了覆盖索引的原则，不需要回表即可查询出所需的数据)。

3：查询A=2的所有值 select  id、A、B 、C tables where A=2这个时候需要怎么查询的呢，如果 A=2有多条,mysql🈶️是怎么查询的呢，这就需要了解mysql的回表机制，以此来优化sql语句了。

• 在(A-B)索引树上找到A=2的记录，取得 id=x；
• 再到ID索引树查到id=x对应的所有字段的值(此过程叫回表查询)；
• 在(A-B)索引树上找到A=2的记录，取得 id=y
• 再到ID索引树查到id=y对应的所有字段的值(此过程叫回表查询)；；
• 一直找到没有可满足条件，循环结束。

这样的一次查询需要回表查询n次，这n次回表的需要浪费时间，这个时候就想，既然必须要回表，我可不可以只回表一次，提高查询效率呢？答案是有的就是:延时关联

延时关联：使用普通索引的复合索引（多列索引）延时关联实现，具体的优化写法如下：

 select * from tables inner join(select id from tables where A=2) as fuhe using(id)

先根据复合索引查询主键id,拿到主键id一次性会叫查询id对应的值，这就是使用覆盖索引和延时关联，减少回表次数，提高查询效率。

备注：如果数据量比较少不建议这样，有时候数据量比较少的时候，全表扫描比索引快很多。