MySQL中EXPLAIN语句及用法实例

下面是“MySQL中EXPLAIN语句及用法实例”攻略。

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使用了索引，可以直接找到符合查询条件的记录，查询效率得到了显著提高。

总结

总之，使用EXPLAIN语句可以帮助我们分析查询语句的性能瓶颈所在，从而找到最优的优化方案。在实际的应用中，我们需要根据实际情况来分析查询语句的执行效率，逐步完善索引等优化手段，不断提高查询效率，提升系统性能。