讲解如何优化之前先说说 EXPLAIN 命令的使用

EXPLAIN 命令简介

MySQL 提供了一个 EXPLAIN 命令， 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。
EXPLAIN 命令用法十分简单， 在 SELECT 语句前加上 EXPLAIN 就可以了。

准备数据

create table `fh6`(
    id int primary key auto_increment, 
    order_date timestamp, 
    order_price int, 
    customer varchar(20), 
    order_channel varchar(20), 
    key d_p_c(order_date,order_price,customer), 
    key channel(order_channel), 
    key customer(customer)
);

insert into `fh6`
    (order_date,order_price,customer,order_channel)
values
    (CURRENT_TIMESTAMP,200,'Eric','below'),
    (CURRENT_TIMESTAMP,300,'Gavin','above'),
    (CURRENT_TIMESTAMP,400,'Stan','below'),
    (CURRENT_TIMESTAMP,500,'Alice','above'),
    (CURRENT_TIMESTAMP,600,'Eric','below'),
    (CURRENT_TIMESTAMP,700,'Gavin','above'),
    (CURRENT_TIMESTAMP,800,'Stan','below'),
    (CURRENT_TIMESTAMP,900,'Alice','above'),
    (CURRENT_TIMESTAMP,1000,'Eric','below'),
    (CURRENT_TIMESTAMP,100,'Gavin','above'),
    (CURRENT_TIMESTAMP,200,'Stan','below'),
    (CURRENT_TIMESTAMP,300,'Alice','above');



create table `fh5`(
    id int primary key auto_increment, 
    customer_name varchar(20)
);

insert into `fh5`
    (customer_name)
values
    ('Eric'),
    ('Gavin'),
    ('Stan'),
    ('Alice');

EXPLAIN 输出格式

mysql> explain select customer,max(order_price) from fh6 group by order_date,order_price\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fh6
   partitions: NULL
         type: index
possible_keys: d_p_c
          key: d_p_c
      key_len: 32
          ref: NULL
         rows: 100
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

 各列的含义如下:

  • id:  SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type:  SELECT 查询的类型.
  • table:  查询的是哪个表
  • partitions:  匹配的分区
  • type:  join 类型
  • possible_keys:  此次查询中可能选用的索引
  • key:  此次查询中确切使用到的索引.
  • ref:  哪个字段或常数与 key 一起被使用
  • rows:  显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered:  表示此查询条件所过滤的数据的百分比
  • extra:  额外的信息

接下来我们来重点看一下比较重要的几个字段。

select_type

select_type 表示了查询的类型, 它的常用取值有:

  • SIMPLE, 表示此查询不包含 UNION 查询或子查询
  • PRIMARY, 表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

最常见的查询类别应该是 SIMPLE 了, 比如当我们的查询没有子查询, 也没有 UNION 查询时, 那么通常就是 SIMPLE 类型。

table

表示查询涉及的表或衍生表

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.

type常用的取值有:

  • system: 表中只有一条数据. 这个类型是特殊的 const 类型.
  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
  • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
  • ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
  • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
    当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
  • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
    index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
  • ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

type 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下:

ALL < index < range ~ index_merge < ref < eq_ref < const < system

ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

possible_keys

possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

字符串

  1. char(n): n 字节长度
  2. varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值类型:

  1. TINYINT: 1字节
  2. SMALLINT: 2字节
  3. MEDIUMINT: 3字节
  4. INT: 4字节
  5. BIGINT: 8字节

时间类型

  1. DATE: 3字节
  2. TIMESTAMP: 4字节
  3. DATETIME: 8字节

字段属性:

  1. NULL 属性 占用1字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

Explain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

  • Using filesort 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
  • Using index “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.




通常的应用中，提倡 sql 简单，避免复杂度。 所以一般不会使用 join ，子查询等 sql 语句，间接 GROUP BY 和索引的使用占据大多数。

满足 GROUP BY 语句最一般的方法是扫描整个表然后创建一个新的临时表，表中每个组的所有行的所有行为应该是连续的。然后用这个临时表找到组并应用累积函数（如果有）。在一些情况下，MYSQL 可以利用索引访问来避免创建临时表。

GROUP BY 使用到索引最重要的前提条件是，GROUP BY 所有的参照列（分组依据的列）来自于同一个索引，并且索引按照顺序存储所有的keys（ 即BTREE index，HASH index没有顺序的概念 ）。然而能否使用索引访问来代替临时表也依赖于索引中的哪部分被用于查询，为这些部分指定的条件，和选中的聚合函数。

MYSQL 有两种方法可以使 GROUP BY 查询通过索引访问，就是下面说的 Loose Index Scan 和 Tight Index Scan 。在 Loose Index Scan 方式下，分组操作和范围预测（如果有的话）一起执行完成的。在 Tight Index Scan 方式下，先对索引执行范围扫描（range scan），再对结果元组进行分组。

1. Loose Index Scan

松散索引扫描相当于Oracle中的跳跃索引扫描（skip index scan），就是不需要连续的扫描索引中得每一个元组，扫描时仅考虑索引中得一部分。当查询中没有where条件的时候，松散索引扫描读取的索引元组的个数和groups的数量相同。如果where条件包含范围预测，松散索引扫描查找每个group中第一个满足范围条件，然后再读取最少可能数的keys。松散索引扫描只需要读取很少量的数据就可以完成group by操作，因而执行效率非常高。使用松散索引扫描需要满足以下条件：

  • 查询在单一表上。

  • group by指定的所有列是索引的一个最左前缀，并且没有其它的列。比如表t1（ c1,c2,c3,c4）上建立了索引（c1,c2,c3）。如果查询包含“group by c1,c2”，那么可以使用松散索引扫描。但是“group by c2,c3”(不是索引最左前缀)和“group by c1,c2,c4”(c4字段不在索引中)。

  • 如果在选择列表select list中存在聚集函数，只能使用 min()和max()两个聚集函数，并且指定的是同一列（如果min()和max()同时存在）。这一列必须在索引中，且紧跟着group by指定的列。比如，select t1,t2,min(t3),max(t3) from t1 group by c1,c2。

  • 如果查询中存在除了group by指定的列之外的索引其他部分，那么必须以常量的形式出现（除了min()和max()两个聚集函数）。
    比如，select c1,c3 from t1 group by c1,c2不能使用松散索引扫描。而select c1,c3 from t1 where c3 = 3 group by c1,c2可以使用松散索引扫描。

  • 索引中的列必须索引整个数据列的值(full column values must be indexed)，而不是一个前缀索引。比如，c1 varchar(20), INDEX (c1(10)),这个索引没发用作松散索引扫描。
    (前缀索引，与上面提到的索引的最左前缀是不同的东西)

如果查询能够使用松散索引扫描，那么执行计划中Etra中提示“ using index for group-by”。

mysql> explain select order_channel from fh6 group by order_channel\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fh6
   partitions: NULL
         type: range
possible_keys: channel
          key: channel
      key_len: 23
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index for group-by
1 row in set, 1 warning (0.01 sec)



Assume that there is an index idx(c1,c2,c3) on table t1(c1,c2,c3,c4). The loose index scan access method can be used for the following queries:

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

自从5.5开始，松散索引扫描可以作用于在select list中其它形式的聚集函数，除了min()和max()之外，还支持：

  • AVG(DISTINCT), SUM(DISTINCT)和COUNT(DISTINCT)可以使用松散索引扫描。AVG(DISTINCT), SUM(DISTINCT)只能使用单一列作为参数。而COUNT(DISTINCT)可以使用多列参数。

  • 在查询中没有group by和distinct条件。

  • 之前声明的松散扫描限制条件同样起作用。
    下面的查询可以使用松散索引扫描

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

下面的查询不能够使用松散索引扫描

SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;


2. Tight Index Scan

紧凑索引扫描可能是全索引扫描或者范围索引扫描，取决于查询条件。

当松散索引扫描条件没有满足的时候，group by 仍然有可能避免创建临时表。如果在where条件有范围扫描，那么紧凑索引扫描仅读取满足这些条件的keys（索引元组），否则执行全索引扫描。这种方式读取所有where条件定义的范围内的keys，或者扫描整个索引，因而称作紧凑索引扫描。对于紧凑索引扫描，只有在所有满足范围条件的keys被找到之后才会执行分组操作。

如果紧凑索引扫描起作用，那么必须满足：在查询中存在常量相等where条件字段（索引中的字段），且该字段在group by指定的字段的前面或者中间。来自于相等条件的常量能够填充搜索keys中的gaps，因而可以构成一个索引的完整前缀。索引前缀能够用于索引查找。如果要求对group by的结果进行排序，并且查找字段组成一个索引前缀，那么MySQL同样可以避免额外的排序操作。

Assume that there is an index idx(c1,c2,c3) on table t1(c1,c2,c3,c4). The following queries do not work with the loose index scan access method described earlier, but still work with the tight index scan access method.

  • There is a gap in the GROUP BY, but it is covered by the condition c2 = ‘a’:
    select c1,c2,c3 from t1 where c2 = ‘a’ group by c1,c3
  • The GROUP BY does not begin with the first part of the key, but there is a condition that provides a constant for that part:
    select c1,c2,c3 from t1 where c1 = ‘a’ group by c2,c3

使用紧凑索引扫描，执行计划Extra一般显示“using index”，相当于使用了覆盖索引。

mysql> explain select order_date,order_price,customer from fh6 where order_price = 100 group by order_date,customer\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fh6
   partitions: NULL
         type: index
possible_keys: d_p_c
          key: d_p_c
      key_len: 32
          ref: NULL
         rows: 100
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)



松散索引扫描和紧凑索引扫描的最大区别是是否需要扫描整个索引或者整个范围扫描。