聚簇索引
- 如果表设置了主键,则主键就是聚簇索引
- 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引
- 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引
- 普通索引
普通索引也叫二级索引,除聚簇索引外的索引,即非聚簇索引。
InnoDB的普通索引叶子节点存储的是主键(聚簇索引)的值,而MyISAM的普通索引存储的是记录指针。
回表查询
先通过普通索引的值定位聚簇索引值,再通过聚簇索引的值定位行记录数据,需要扫描两次索引B+树,它的性能较扫一遍索引树更低。
索引覆盖
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
例如:select id,age from user where age = 10;
如何实现覆盖索引
常见的方法是:将被查询的字段,建立到联合索引里去。
1、如实现:select id,age from user where age = 10;
explain分析:因为age是普通索引,使用到了age索引,通过一次扫描B+树即可查询到相应的结果,这样就实现了覆盖索引
列查询回表优化
前文在描述索引覆盖使用的例子就是
例如:select id,age,name from user where age = 10;
使用索引覆盖:建组合索引idx_age_name(age,name)即可
分页查询
例如:select id,age,name from user order by age limit 100,2;
因为name字段不是索引,所以在分页查询需要进行回表查询,此时Extra为Using filesort文件排序,查询性能低下。
使用索引覆盖:建组合索引idx_age_name(age,name)
什么是索引下推
索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。
MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。
索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。
我们来具体看一下,在没有使用ICP的情况下,MySQL的查询:
存储引擎读取索引记录;
根据索引中的主键值,定位并读取完整的行记录;
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。
使用ICP的情况下,查询过程:
存储引擎读取索引记录(不是完整的行记录);
判断WHERE条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;
条件满足,使用索引中的主键去定位并读取完整的行记录(就是所谓的回表);
存储引擎把记录交给Server层,Server层检测该记录是否满足WHERE条件的其余部分。
使用ICP
而MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到name like ‘张%’,由于联合索引中包含age列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。
我们看一下示意图:
可以看到只回表了一次。
除此之外我们还可以看一下执行计划,看到Extra一列里 Using index condition,这就是用到了索引下推。
索引下推使用条件
只能用于range、 ref、 eq_ref、ref_or_null访问方法;
只能用于InnoDB和 MyISAM存储引擎及其分区表;
对InnoDB存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);
索引下推的目的是为了减少回表次数,也就是要减少IO操作。对于InnoDB的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。
引用了子查询的条件不能下推;
引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。
参考:
https://juejin.cn/post/7005794550862053412