分析一条sql的性能的标准总结

这篇文章将给大家介绍如何使用 explain 来分析一条 sql 。

网上其实已经有非常多的文章都很详细的介绍了 explain 的使用，这篇文章将实例和原理结合起来，尽量让你有更好的理解，相信我，认真看完你应该会有特别的收获。

explain 翻译过来就是解释的意思，在 mysql 里被称作执行计划，即可以通过该命令看出 mysql 在经过优化器分析后决定要如何执行该条 sql 。

说到优化器，再多说一句，mysql 内置了一个强大的优化器，优化器的主要任务就是把你写的 sql 再给优化一下，尽可能以更低成本去执行，比如扫描更少的行数，避免排序等。执行一条sql语句都经历了什么？我在前面的文章中有介绍过优化器相关的。

你可能会问，一般在什么时候会要用 explain 呢，大多数情况下都是从 mysql 的慢查询日志中揪出来一些查询效率比较慢的 sql 来使用 explain 分析，也有的是就是在对 mysql 进行优化的时候，比如添加索引，通过 explain 来分析添加的索引能否被命中，还有的就是在业务开发的时候，在满足需求的情况下，你可能需要通过 explain 来选择一个更高效的 sql。

那么 explain 该怎么用呢，很简单，直接在 sql 前面加上 explain 就行了，如下所示。

mysql> explain select * from t;+----+-------------+-------+------+---------------+------+---------+------+--------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra |+----+-------------+-------+------+---------------+------+---------+------+--------+-------+| 1 | SIMPLE   | t   | ALL | NULL     | NULL | NULL  | NULL | 100332 | NULL |+----+-------------+-------+------+---------------+------+---------+------+--------+-------+1 row in set (0.04 sec)

其中 type、key、rows、Extra 这几个字段我认为是比较重要的，我们接下来通过具体的实例来帮你更好的理解这几个字段的含义。

首先有必要简单介绍下这几个字段的字面意思。

type 表示 mysql 访问数据的方式，常见的有全表扫描（all）、遍历索引（index）、区间查询（range）、常量或等值查询（ref、eq_ref）、主键等值查询（const）、当表中只有一条记录时（system）。下面是效率从最好到最差的一个排序。

system > const > eq_ref > ref > range > index > all

rows 表示查询过程中可能需要扫描的行数，这个数据不一定准确，是mysql 抽样统计的一个数据。

Extra 表示一些额外的信息，通常会显示是否使用了索引，是否需要排序，是否会用到临时表等。

好了，接下来正式开始实例分析。

还是沿用前面文章中创建的存储引擎创建一个测试表，我们这里插入 10 w 条测试数据，表结构如下：

CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

mysql> alter table t add index a_index(a);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add index b_index(b);Query OK, 0 rows affected (0.20 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t   |     0 | PRIMARY |      1 | id     | A     |   100332 |   NULL | NULL  |   | BTREE   |     |        || t   |     1 | a_index |      1 | a      | A     |   100332 |   NULL | NULL  | YES | BTREE   |     |        || t   |     1 | b_index |      1 | b      | A     |   100332 |   NULL | NULL  | YES | BTREE   |     |        |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)

接下来我们分别给字段 a 和 b 添加普通索引，然后再看下添加索引后的几条 sql 。

mysql> alter table t add index a_index(a);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add index b_index(b);Query OK, 0 rows affected (0.20 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t   |     0 | PRIMARY |      1 | id     | A     |   100332 |   NULL | NULL  |   | BTREE   |     |        || t   |     1 | a_index |      1 | a      | A     |   100332 |   NULL | NULL  | YES | BTREE   |     |        || t   |     1 | b_index |      1 | b      | A     |   100332 |   NULL | NULL  | YES | BTREE   |     |        |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)

这里是因为 select * 的话还需要回到主键索引上查找 b 字段，这个过程叫回表，这条语句会筛选出 9w 条满足条件的数据，也就是说这 9w 条数据都需要回表操作，全表扫描都才 10w 条数据，所以在 mysql 的优化器看来还不如直接全表扫描得了，至少还免去了回表过程了。

当然也不是说只要有回表操作就不会命中索引，用不用索引关键还在于 mysql 认为哪种查询代价更低，我们把上面的 sql 中 where 条件再稍微改造一下。

mysql> explain select * from t where a > 99000;+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra         |+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+| 1 | SIMPLE   | t   | range | a_index    | a_index | 5    | NULL | 999 | Using index condition |+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+1 row in set (0.00 sec)

我们还可以看到 Extra 字段中值为 Using index condition，这个意思是指用到了索引，但是需要回表，再看下面这个语句。

mysql> explain select a from t where a > 99000;+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra          |+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+| 1 | SIMPLE   | t   | range | a_index    | a_index | 5    | NULL | 999 | Using where; Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+1 row in set (0.00 sec)

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra                 |+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+| 1 | SIMPLE   | t   | range | a_index    | a_index | 5    | NULL | 999 | Using index condition; Using filesort |+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+1 row in set (0.00 sec)

mysql> explain select a from t where a > 99990 order by a;+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys  | key   | key_len | ref | rows | Extra          |+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+| 1 | SIMPLE   | t   | range | a_index,ab_index | a_index | 5    | NULL |  10 | Using where; Using index |+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+1 row in set (0.00 sec)

mysql> alter table t add index ab_index(a,b);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划，也提到了一些常见的索引优化，事实上还有更多的可能性，你也可以自己去写一个 sql ，然后使用 explain 分析，看看有哪些是可以被优化的。

更多相关文章

随机推荐