Explain详解与索引最佳实战

dev.mysql.com/doc/refman/…

使用explain关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈

Explain

type

• 一般来说，得保证查询达到range级别，最好达到ref

最优到最差：system > const > eq_ref > ref > range > index > all

• system/const

  • 查询的某部分进行优化并将其转化成一个常量(这两种类型基本不需要优化)

    • 注: system可以理解为const的特例，只会有一条记录匹配

• eq_ref

  • primary key或unique_key索引的所有部分被连接起来，最多只会返回一条符合条件的记录

• ref

  • 相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀

• range

  • 范围查询，如in、between、>、<

• index

  • 一般是扫描某个二级索引，直接对二级索引的叶子节点遍历和扫描，一般为使用覆盖索引(Using index)

• all

  • 全表扫描(需要优化)

extra

• 常见场景

  • Using index: 使用了普通索引或覆盖索引

  • Using where: 没走索引，直接使用where语句去处理结果(需要优化)

  • Using index condition: 查询的列表不完全被索引覆盖，where条件是一个前导列的范围

  • Using temporary: 使用临时表(distinct)

  • Using filesort: 使用文件排序。如果结果集不大，可能用内存排序，如果结果集很大，可能用磁盘排序

    • 比如 order by age，如果显示Using index，表示用到了索引

  • Select tables optimized away: 使用某些聚合函数(比如max、min来访问存在索引的某个字段)

  • Using join buffer: 表示使用NLJ算法

  • Using join buffer(Blocked Nested Loop join): 表示使用BNL算法

• 优化原则: 尽量让查询、排序的结果集使用索引

key_len

• 这一列显示了MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列

  • key_len计算规则如下

    • 字符串: char(n)和varchar(n)，5.0.3(以后版本中，n均代表字符数,而不是字节数，如果是utf8一个数字或字母占1个字节，一个汉字占3个字节

    • char(n): 如果存汉字长度就是3n宇节

    • varchar(n): 如果存汉宇则长度是3n + 2字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

    • 数值类型

      • tinyint： 1字节
      • smallint： 2字节
      • int: 4字节
      • bigint：8字节

    • 时间类型

      • date: 3字节
      • timestamp: 4字节
      • datetime: 8字节

    • 如果字段允许为NULL，需要1字节记录是否为NULL

    • 索引的最大长度是768字节，当字符串过长时，MySQL会做一个类似左前缀索引的处理，将前半部分的字符串提取出来做索引

索引最佳实战

• 不在索引列上做任何操作(计算、函数、or、类型转换(left(name,3)))，会导致索引失效而转向全表扫描

• 存储引擎不能使用索引中范围条件右边的列(如 name = 'xxx' and age > 22 and position = 'manage')，最终查询不会用到position索引列

• 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)，减少select * 语句)

  • 覆盖索引就是我们可以从二级索引树查找到我们想要的结果了，就不用去回表查询，因为此时的索引树比聚集索引的小.
  • 覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index;覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引找到主键，再通过主键索引树里获取其它字段值.

• mysql使用<>、!=、not in、not exists的时候无法使用索引回导致全表扫描，<、>、<=、>=这些，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引

  • in和exists的区别

    • in适合子表比主表数据小的情况
    • exists适合子表比主表数据大的情况
    • 当主表数据与子表数据一样大时，in和exists效率差不多，可以任选一个

• is null、is not null一般情况下也无法使用索引

• like以通配符开头('%abc..')，mysql索引失效会变成全表扫描操作

• 字符串不加单引号索引失效

• 少用or、in，用它查询时mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

假设index(a,b,c)

where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y，使用到a,b
where a = 3 and b = 5 and c = 4	Y，使用到a,b,c
where b= 3 或者 where b = 3 and c = 4 或者 where c= 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b，c不能用在范围之后，b断了
where a = 3 and b like 'kk%' and c = 4	Y，使用到a,b,c
where a = 3 and b like '%kk' and c = 4	Y，只用到a
where a = 3 and b like '%kk%' and c = 4	Y，只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y，使用到a,b,c

like 'kk%' 相当于 = 常量，%kk和%kk%相当于范围