explain字段释疑

  • id：标识本次执行select的语句顺序，在子查询中有意义。
  • select_type：
    • SIMPLLE：简单查询，该查询不包含 UNION 或子查询
    • PRIMARY：如果查询包含UNION 或子查询，则最外层的查询被标识为PRIMARY
    • UNION：表示此查询是 UNION 中的第二个或者随后的查询
    • SUBQUERY：子查询中的第一个select语句(该子查询不在from子句中)
    • DERIVED：包含在from子句中子查询(也称为派生表)
  • table：查询的表
  • type：查询方式，性能从好到差：
    • system/const：主键/唯一索引查询
    • eq_ref：使用唯一索引扫描，或确定返回单条记录。
    • ref：使用非唯一索引扫描或唯一索引前缀扫描。
    • range：索引范围扫描，存在in、between、>、<等对索引的范围查询场景下。
    • index：索引全扫描，按某个非聚集索引扫其所有叶子节点，需要回表，所有有时候不如all。
    • all：全表扫描，也就是扫聚集索引的所有叶子节点。
  • possible_key：可能使用的索引
  • key：实际使用的索引
  • key_len：索引长度
  • ref：标识where条件中筛选的值，一般是const、func、null、字段名
  • rows：扫描行数

  • Extra：额外信息描述

    • Using where：普通过滤
    • Using Index Condition：使用了索引下推过滤

      索引提示

  • USE INDEX：推荐一个索引列表给mysql，但也不一定就会用。

  • FORCE INDEX：强制使用某个索引，如果给定的索引是合理的，那么确实可以减少mysql在其他索引的上评估成本，如果给定的不对，可能并不如mysql选择出来的索引。

  • IGNORE INDEX：强制不使用某个索引。

    索引下推

  • 在5.6版本之后的优化，还有MRR（multi-range read）。

  • 先简单说一下MRR，通过将查出来的非聚集索引按主键索引进行重排序，再去回表查询，降低离散读和情况并减少缓存页换出的操作，可以提高效率。
  • 我们都mysql结构可以简单划分为应用层+存储引擎层，我们用的sql语句里的where后面的字段并不会每一个字段都会命中索引，比如有idx（username, age），sql语句是where username like ‘larry%’ and age=10
  • 5.6版本之前的sql执行逻辑是，先从存储引擎层根据索引由先过滤出符合条件的主键ID列表（也就是会根据idx的username进行过滤，age由于username的模糊查询不会被过滤），然后在应用层进行回表查出剩余where字段的值（也就是age），然后进行过滤（age=10）。
  • 而索引下推（Index Condition Pushdown）的逻辑则是第一步一样，第二步则会在存储引擎继续根据索引进行过滤，也就是进行age=10的过滤，极大减少了回表数量。

  • 所以要触发ICP，其实有两点要求：

    • 需要被过滤的字段一定要建立了联合索引。
    • 需要被过滤的字段无法使用完整的联合索引，比如比如联合索引的字段使用范围查询，如>、<、between、like等。

      索引行数统计方案

  • mysql优化器在选择索引的时候，一个很最重要的参考标准就是扫描行数，当然是越少越好，所以mysql有两种方式来获取扫描行数。

  • 索引下潜（Index Dive）：调用存储引擎的接口，获取准确的估算数据，成本高。
  • 索引统计（Index Statistics）：存储引擎会以一定的机制更新每个索引的cardinality值，但这个值并不是实时的，所以存在一定的误差，但该值是可以直接获取到的，成本低。