MySQL EXPLAIN和DESCRIBE分析查询语句的用法(附带实例)
通过对查询语句进行分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句。
MySQL 中提供了 EXPLAIN 语句和 DESCRIBE 语句来分析查询语句。
执行该语句,可以分析 EXPLAIN 后面 SELECT 语句的执行情况,并且能够分析出所查询表的一些特征。
【实例 1】使用 EXPLAIN 语句来分析一个查询语句,SQL 语句如下:
1) id:SELECT 识别符。这是 SELECT 的查询序列号。
2) select_type:表示 SELECT 语句的类型。它有以下几种取值:
3) table:表示查询的表。
4) type:表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型。
① system:该表是仅有一行的系统表。这是 const 连接类型的一个特例。
② const:数据表最多只有一个匹配行,将在查询开始时被读取,并在余下的查询优化中作为常量对待。const 表查询速度很快,因为它们只读取一次。const 用于使用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分的场合。
在下面的查询中,tbl_name 可用于 const 表:
③ eq_ref:eq-ref 表示使用唯一性索引进行连接操作,使用索引查找来匹配连接条件,这种方式适用于连接条件中的列是主键或唯一性索引的情况。
eq_ref 可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。
在下面的例子中,MySQL 可以使用 eq_ref 连接来处理 ref_tables:
④ ref:对于来自前面的表的任意行组合,将从该表中读取所有匹配的行。这种类型用于索引既不是 UNIQUE 也不是 PRIMARY KEY 的情况,或者查询中使用了索引列的左子集(即索引中左边的部分列组合)。ref 可以用于使用“=”或“<=>”操作符比较带索引的列。
在下面的例子中,MySQL 可以使用 ref 连接来处理 ref_tables:
⑤ ref_or_null:该连接类型如同 ref,但是添加了 MySQL 可以专门搜索包含NULL值的行。在解决子查询中经常使用该连接类型的优化。
在下面的例子中,MySQL 可以使用 ref_or_null 连接来处理 ref_tables:
⑥ index_merge:该连接类型表示使用了索引合并优化方法。在这种情况下,key 列包含了使用的索引的清单,key_len 包含了所使用索引的最长关键元素。
⑦ unique_subquery:该类型替换了下面形式的 IN 子查询的 ref:
⑧ index_subquery:该连接类型类似于 unique_subquery,可以替换 IN 子查询,但只适合下列形式的子查询中的非唯一索引:
⑨ range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,key_len 包含所使用索引的最长关键元素。
当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符用常量比较关键字列时,类型为 range。
下面介绍几种检索指定行的情况:
⑩ index:该连接类型与 ALL 相同,除了只扫描索引树。它通常比 ALL 快,因为索引文件通常比数据文件小。
⑪ ALL:对于前面的表的任意行组合,进行完整的表扫描。如果表是第一个没标记 const 的表,使用 ALL 连接类型可能不是最佳选择,因为在其他情况下,它可能导致较差的查询性能。为了避免使用 ALL 连接类型,可以考虑增加更多的索引来优化查询性能。
5) possible_keys:指出 MySQL 能使用哪个索引在该表中找到行。如果该列的值是 NULL,则没有相关的索引。在这种情况下,可以检查 WHERE 子句,看是否可以创建适合的索引来提高查询性能。
6) key:表示查询实际使用到的索引,如果没有选择索引,则该列的值是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引,可在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
7) key_len:表示 MySQL 选择的索引字段按字节计算的长度,如果键是 NULL,则长度为 NULL。注意,通过 key_len 值可以确定 MySQL 将实际使用一个多列索引中的几个字段。
8) ref:表示使用哪个列或常数与索引一起来查询记录。
9) rows:显示 MySQL 在表中进行查询时必须检查的行数。
10) Extra:表示 MySQL 在处理查询时的详细信息。
DESCRIBE 语句的语法形式如下:
MySQL 中提供了 EXPLAIN 语句和 DESCRIBE 语句来分析查询语句。
MySQL EXPLAIN语句
EXPLAIN 语句的基本语法如下:EXPLAIN [EXTENDED] SELECT select_options使用 EXTENED 关键字,EXPLAIN 语句将产生附加信息;select_options 是 SELECT 语句的查询选项,包括 FROM WHERE 子句等。
执行该语句,可以分析 EXPLAIN 后面 SELECT 语句的执行情况,并且能够分析出所查询表的一些特征。
【实例 1】使用 EXPLAIN 语句来分析一个查询语句,SQL 语句如下:
mysql> EXPLAIN SELECT * FROM fruits; +----+-------------+--------+------+-----------------+--------+---------+--------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------+--------+---------+--------+-------+-------+ | 1 | SIMPLE | fruits | ALL | NULL | NULL | NULL | NULL | 16 | | +----+-------------+--------+------+-----------------+--------+---------+--------+-------+-------+下面对查询结果进行解释:
1) id:SELECT 识别符。这是 SELECT 的查询序列号。
2) select_type:表示 SELECT 语句的类型。它有以下几种取值:
- SIMPLE:表示简单查询,其中不包括连接查询和子查询;
- PRIMARY:表示主查询或者最外层的查询语句;
- UNION:表示连接查询的第二个或后面的查询语句;
- DEPENDENT UNION:连接查询中的第二个或后面的 SELECT 语句,取决于外面的查询;
- UNION RESULT:连接查询的结果;
- SUBQUERY,子查询中的第一个 SELECT 语句;
- DEPENDENT SUBQUERY,子查询中的第一个 SELECT 语句,取决于外面的查询;
- DERIVED,导出表的 SELECT(FROM 子句的子查询)。
3) table:表示查询的表。
4) type:表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型。
① system:该表是仅有一行的系统表。这是 const 连接类型的一个特例。
② const:数据表最多只有一个匹配行,将在查询开始时被读取,并在余下的查询优化中作为常量对待。const 表查询速度很快,因为它们只读取一次。const 用于使用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分的场合。
在下面的查询中,tbl_name 可用于 const 表:
SELECT * from tbl_name WHERE primary_key=1; SELECT * from tbl_name WHERE primary_key_part1=1AND primary_key_part2=2;
③ eq_ref:eq-ref 表示使用唯一性索引进行连接操作,使用索引查找来匹配连接条件,这种方式适用于连接条件中的列是主键或唯一性索引的情况。
eq_ref 可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。
在下面的例子中,MySQL 可以使用 eq_ref 连接来处理 ref_tables:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
④ ref:对于来自前面的表的任意行组合,将从该表中读取所有匹配的行。这种类型用于索引既不是 UNIQUE 也不是 PRIMARY KEY 的情况,或者查询中使用了索引列的左子集(即索引中左边的部分列组合)。ref 可以用于使用“=”或“<=>”操作符比较带索引的列。
在下面的例子中,MySQL 可以使用 ref 连接来处理 ref_tables:
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
⑤ ref_or_null:该连接类型如同 ref,但是添加了 MySQL 可以专门搜索包含NULL值的行。在解决子查询中经常使用该连接类型的优化。
在下面的例子中,MySQL 可以使用 ref_or_null 连接来处理 ref_tables:
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
⑥ index_merge:该连接类型表示使用了索引合并优化方法。在这种情况下,key 列包含了使用的索引的清单,key_len 包含了所使用索引的最长关键元素。
⑦ unique_subquery:该类型替换了下面形式的 IN 子查询的 ref:
value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
⑧ index_subquery:该连接类型类似于 unique_subquery,可以替换 IN 子查询,但只适合下列形式的子查询中的非唯一索引:
value IN (SELECT key_column FROM single_table WHERE some_expr)
⑨ range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,key_len 包含所使用索引的最长关键元素。
当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符用常量比较关键字列时,类型为 range。
下面介绍几种检索指定行的情况:
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
⑩ index:该连接类型与 ALL 相同,除了只扫描索引树。它通常比 ALL 快,因为索引文件通常比数据文件小。
⑪ ALL:对于前面的表的任意行组合,进行完整的表扫描。如果表是第一个没标记 const 的表,使用 ALL 连接类型可能不是最佳选择,因为在其他情况下,它可能导致较差的查询性能。为了避免使用 ALL 连接类型,可以考虑增加更多的索引来优化查询性能。
5) possible_keys:指出 MySQL 能使用哪个索引在该表中找到行。如果该列的值是 NULL,则没有相关的索引。在这种情况下,可以检查 WHERE 子句,看是否可以创建适合的索引来提高查询性能。
6) key:表示查询实际使用到的索引,如果没有选择索引,则该列的值是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引,可在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
7) key_len:表示 MySQL 选择的索引字段按字节计算的长度,如果键是 NULL,则长度为 NULL。注意,通过 key_len 值可以确定 MySQL 将实际使用一个多列索引中的几个字段。
8) ref:表示使用哪个列或常数与索引一起来查询记录。
9) rows:显示 MySQL 在表中进行查询时必须检查的行数。
10) Extra:表示 MySQL 在处理查询时的详细信息。
MySQL DESCRIBE语句
DESCRIBE 语句的使用方法与 EXPLAIN 语句是一样的,并且分析结果也是一样的。DESCRIBE 语句的语法形式如下:
DESCRIBE SELECT select_optionsDESCRIBE 可以简写成 DESC。