通过explain分析低效的SQL执行计划

之前我们讲过如何开启慢查询日志，这个日志的最大作用就是我们通过设定超时阈值，在执行SQL语句中的消耗时间大于这个阈值，将会被记录到慢查询日志里面。DBA通过这个慢查询日志定位到执行缓慢的sql语句，以便来进行优化。那我们今天就来学习一下如何分析抵消的SQL语句。

mysql> explain select* from co3 where ctime=68776 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: co3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9471195
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

我们主要对三个列说明一下：

select_type：表示SELECT 的类型，常见取值：SIMPLE、PRIMARY、UNION、SUBQUERY

table：输出的结果集的表。

KEY：表示执行语句中使用索引名。

type：表示MySQL在表中找到所需行的方式，或者叫访问类型。这个type的取值是我们重点学习的。主要有以下几个取值：

ALL   |  index  |  range   |  ref   |   eq_ref  |  const,system  |  NULL

从左到右，性能有最差到最好。

1）type=ALL，全表扫描，效率最差。MySQL会遍历全表来找到匹配的行。

mysql> explain select* from co3 where ctime=68776 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: co3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9471195
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

2）type=index，索引全扫描。MySQL遍历整个索引来查询匹配的行。

mysql> explain select count(*) from co3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: co3
   partitions: NULL
         type: index
possible_keys: NULL
          key: source_creative_id
      key_len: 10
          ref: NULL
         rows: 9471195
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

3）type=range，索引范围扫描，常见于<  <=   >  >=   between等操作符。

mysql> explain select * from co3 where id >= 309 and id <= 500 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: co3
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

4）type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> explain select * from co3 where campaign_id=45413\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: co3
   partitions: NULL
         type: ref
possible_keys: campaign_id
          key: campaign_id
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

索引campaign_id是非唯一索引，查询条件为等值查询条件，所以扫描索引的类型为ref。ref  也会出现在join操作中。

mysql> explain select * from co3 a,co2 b where a.campaign_id = b.campaign_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: campaign_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9471195
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: campaign_id
          key: campaign_id
      key_len: 8
          ref: mob_adn.a.campaign_id
         rows: 105
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

5）type=eq_ref，类似于ref，区别在于使用的索引是唯一索引，我们知道唯一索引是unique index 或者是primary key作为关联条件，对于每个索引键值，表中只有一条记录匹配。

mysql> explain select * from co3 a,co2 b where a.id=b.id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9471195
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mob_adn.a.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

6）type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如根据主键primary key或者唯一索引unique index进行的查询。

mysql> desc select* from co2 where id=68777845 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: co2
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

7）type=NULL，MySQL不用访问表或者索引，直接就能得到结果，例如：

mysql> explain select 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.01 sec)

转载于:https://www.cnblogs.com/FengGeBlog/p/10288528.html