首先说一下extal中参数:
Using index表示查询的列被索引覆盖,因而无需再回表查询,因而效率较高。例如:select id from test where id = 5;其中id为主键
Using where;Using index表示查询的列被索引覆盖,且where筛选条件是索引列前导列的一个范围,或者是索引列的非前导列,例如:select id from test where id > 5;。很明显,效率也很高。
Using where表示查询的列未被索引覆盖,且where筛选条件是索引列前导列的一个范围,或者是索引列的非前导列,或者是非索引列,例如:select * from test where id > 30; 。因为未被索引覆盖,所以需要回表,因而性能比前两者差。
Extra为null表示查询的列未被索引覆盖,且where筛选条件是索引的前导列,这意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现,因而性能也比前两者差。
Using index condition是MySQL 5.6中引入的一种新特性,叫做Index Condition Pushdown(ICP),是一种在存储引擎层使用索引过滤数据的一种优化方式。这里的“下推” 是指将原来在server层进行的table filter中可以进行index filter的部分,在引擎层面使用index filter进行处理,不再需要回表进行table filter。使用ICP可以减少存储引擎层返回需要被index filter过滤掉的行记录,省去了存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。Using index condition仅适用于二级索引,原因是ICP的目的是减少全行读取的次数,从而减少IO操作。而对于innodb聚集索引,完整的记录已被读入到innodb缓冲区,在这种情况下,ICP不会减少io,所以ICP只适用于二级索引,一般发生在查询字段无法被二级索引覆盖的场景,该场景下往往需要回表。通过ICP,可以减少存储引擎返回的行记录,从而减少了IO操作
数据库中有一张表read_user_collection_record
1. 我为其建立的索引为:
2. 我执行的sql为:
SELECT count(*) from read_user_collection_record where app_id=28 and state=1;
3. 执行的结果为:
4. 我执行explain的结果是:
5. 结果:根据索引的最左侧搜索,以及key和extra来观察,确实走了索引
将sql变为:
SELECT count(*) from read_user_collection_record where app_id=28 and state=1 and created_at > "2021-05-01";
6. 执行explain的结果是:
7. 根据索引最左侧搜索来分析,当索引走到create_at时,虽然不是using index, 虽然create_at是范围查询,但是create_at被索引覆盖,所以效率还是可以的
8. 使用explain来执行:
EXPLAIN SELECT count(*) from read_user_collection_record where app_id=28 and state=1 and created_at = "2021-05-01";
9. 执行结果
10. 看到extra为using index,说明用了索引并且没有回表
参考文章:地址