explain MySQL-蒲公英云

explain MySQL

叁歲伎倆 2021-06-24 14:36 541阅读 0赞

explain + sql语句是为了查看当前这条sql语句是否存在优化的可能,也就是说,这条sql语句是否影响性能.

mysql> explain select * from trades_order; +----+-------------+--------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | trades_order | ALL | NULL | NULL | NULL | NULL | 511 | | +----+-------------+--------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from trades_order where id=1; +----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | trades_order | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

从上面的两个例子中可以看到一些字段.下面我们就分别说说他们的意义.
id:sql语句的序号,不重要
select_type:sql语句的类型,不重要
table:所要查询的表,不重要
type:检索的类型,重要,类型值由优到差的顺序:system->const->eq_ref->ref->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL
possible_keys:可用的索引字段,不重要
key:使用的索引字段,重要
key_len:索引长度,不重要
ref:检索条件涉及的字段,不重要
rows:检索到的行数,不重要
Extra:显示解决查询的详细信息,不重要

所以看一条sql语句的性能,依次查看的指标为:type->key->ref->Extra,这样回头看一下,第一个sql语句的性能是有多糟.