EXPLAIN语句在MySQL中的作用

一个查询语句,无论多么精细地编写,都可能会有性能瓶颈。常见的瓶颈有数据量太大、表太多、查询的JOIN语句过于复杂或者索引不当等。当遇到性能瓶颈问题时,我们通常需要使用MySQL的EXPLAIN语句来分析查询语句的性能瓶颈所在,从而找到最优的优化方案。

EXPLAIN语句可以帮助我们查看MySQL如何处理查询语句以及使用哪些索引。

EXPLAIN语句的语法结构

EXPLAIN SELECT 字段 FROM 表名 WHERE 条件语句 [GROUP BY 字段] [HAVING 条件] [ORDER BY 字段] [LIMIT 数量];

EXPLAIN语句的结果解释

当我们执行一个查询语句后, EXPLAIN会返回一张表格,列出了表在执行该查询时加锁的情况、表扫描的顺序以及使用的索引等信息。下面是表格字段和其解释:

  • id:查询的序号,从1开始递增,一般如果查询语句较复杂或者涉及到多个表的关系, id值越大的查询,执行代价就越大;
  • select_type:查询的类型,比如简单查询、联合查询、子查询等;
  • table:显示该行的数据是哪个表的查询结果;
  • partitions:区分不同的分区;
  • type:显示 join 类型。该列越是显示“ALL”,就说明查询优化的效果越差。值得注意的是,一般情况下必须使用覆盖索引才能达到最优查询效果;
  • possible_keys:这列显示了哪些索引可以应用到这个查询中,一个或多个列名说明可以使用索引,null则没有可使用的索引;
  • key:这列显示MySQL实际采用的索引,如果是 null,则没有使用索引。在某些情况下,MySQL会选用优化器来自行选择一个比可能使用的索引更好的索引;
  • key_len:表示索引中使用的字节数,越短越好,如果使用的是复合索引(多个列组成的索引),则该值表示索引中使用的前缀字节数;
  • ref:表示索引中使用的哪个字段作为参考,如果使用的是常数索引(如常量才用到的索引,非数据字段,例如当前时间函数),则不显示;
  • rows:表示MySQL认为它执行查询时必须扫描的行数;
  • filtered:返回的结果占总结果的百分比;
  • Extra:包含MySQL解决查询的详细信息,如是否使用了索引,是否需要排序,是否是内存临时表等。

EXPLAIN语句的实例

实例1:最简单的示例

假设我们有一个user表,里面有id、username、age三个字段。

我们想查找年龄(age)大于等于18岁的用户的ID和姓名,可以这样编写查询语句:

SELECT id, username FROM user WHERE age >= 18;

运行该查询语句时,使用EXPLAIN语句来查看分析结果:

EXPLAIN SELECT id, username FROM user WHERE age >= 18;

查询结果如下:

+----+-------------+-------+------------+------+---------------+------+---------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len |
+----+-------------+-------+------------+------+---------------+------+---------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    |
+----+-------------+-------+------------+------+---------------+------+---------+

可以看到,type列值是ALL。这表示MySQL在查询user表时必须进行完整的扫描以获取符合查询条件的结果。

 

为了优化查询语句,我们可以给age字段添加索引,如下:

ALTER TABLE user ADD KEY index_age (age);

然后使用 EXPLAIN 语句再查看查询结果:

EXPLAIN SELECT id, username FROM user WHERE age >= 18;

查询结果如下:

+----+-------------+-------+------------+------+---------------+----------------+---------+
| id | select_type | table | partitions | type | possible_keys | key            | key_len |
+----+-------------+-------+------------+------+---------------+----------------+---------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_age     | index_age      | 4       |
+----+-------------+-------+------------+------+---------------+----------------+---------+

可以看到,type列值变为了ref。这表示MySQL使用了索引,可以直接找到符合查询条件的记录。

实例2:使用JOIN语句示例

假设我们有两个表user和orders,user表的结构为id、username、age三个字段,orders表的结构为id、user_id、order_number三个字段,其中user_id为外键。

我们想查找所有年龄大于等于18岁的用户及其对应的订单号,可以使用关联查询语句:

SELECT u.id, u.username, o.order_number FROM user AS u JOIN orders AS o ON u.id = o.user_id WHERE u.age >= 18;

运行该查询语句时,使用EXPLAIN语句来查看分析结果:

EXPLAIN SELECT u.id, u.username, o.order_number FROM user AS u JOIN orders AS o ON u.id = o.user_id WHERE u.age >= 18;

查询结果如下:

+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref            | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL           |    3 |   100.00 | Using where                        |
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL           |    4 |    25.00 | Using where; Using join buffer    |
+----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+------------------------------------+

可以看到,两张表都使用了ALL类型,这表示MySQL在查询时必须进行完整的扫描以获取符合联接条件的结果。

为了优化查询语句,我们可以给user表的age字段和orders表的user_id字段添加索引,如下:

ALTER TABLE user ADD KEY index_age (age);

ALTER TABLE orders ADD KEY index_user_id (user_id);

然后使用EXPLAIN语句再查看查询结果:

EXPLAIN SELECT u.id, u.username, o.order_number FROM user AS u JOIN orders AS o ON u.id = o.user_id WHERE u.age >= 18;

查询结果如下:

+----+-------------+-------+------------+--------+----------------+---------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys  | key           | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------+---------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ref    | index_age      | index_age     | 4       | const          |    2 |   100.00 | Using where |
|  1 | SIMPLE      | o     | NULL       | eq_ref | index_user_id  | index_user_id | 5       | test.u.id |    1 |   100.00 |             |
+----+-------------+-------+------------+--------+----------------+---------------+---------+----------------+------+----------+-------------+

可以看到,type列值分别变为了ref和eq_ref。这表示MySQL使用了索引,可以直接找到符合查询条件的记录,查询效率得到了显著提高。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注