【图书推荐】《MySQL 9从入门到性能优化(视频教学版)》-CSDN博客
《MySQL 9从入门到性能优化(视频教学版)(数据库技术丛书)》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)
MySQL9数据库技术_夏天又到了的博客-CSDN博客
通过对查询语句进行分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句。MySQL中提供了EXPLAIN语句和DESCRIBE语句来分析查询语句。
1. EXPLAIN语句
EXPLAIN语句的基本语法如下:
EXPLAIN [EXTENDED] SELECT select_options
使用EXTENED关键字,EXPLAIN语句将产生附加信息。select_options是SELECT语句的查询选项,包括FROM WHERE子句等。
执行该语句,可以分析EXPLAIN后面SELECT语句的执行情况,并且能够分析出所查询表的一些特征。
【例14.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:表示表的连接类型。
下面按照从最佳类型到最差类型的顺序给出各种连接类型。
(1)system
该表是仅有一行的系统表。这是const连接类型的一个特例。
(2)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;
(3)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;
(4)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;
(5)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;
(6)index_merge
该连接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了所使用索引的最长关键元素。
(7)unique_subquery
该类型替换了下面形式的IN子查询的ref:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
(8)index_subquery
该连接类型类似于unique_subquery,可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:
value IN (SELECT key_column FROM single_table WHERE some_expr)
(9)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);
(10)index
该连接类型与ALL相同,除了只扫描索引树。它通常比ALL快,因为索引文件通常比数据文件小。
(11)ALL
对于前面的表的任意行组合,进行完整的表扫描。如果表是第一个没标记const的表,使用ALL连接类型可能不是最佳选择,因为在其他情况下,它可能导致较差的查询性能。为了避免使用ALL连接类型,可以考虑增加更多的索引来优化查询性能。
5)possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果该列的值是NULL,则没有相关的索引。在这种情况下,可以检查WHERE子句,看是否可以创建适合的索引来提高查询性能。
6)key:表示查询实际使用到的索引,如果没有选择索引,则该列的值是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,可在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见SELECT语法。
7)key_len:表示MySQL选择的索引字段按字节计算的长度,如果键是NULL,则长度为NULL。注意,通过key_len值可以确定MySQL将实际使用一个多列索引中的几个字段。
8)ref:表示使用哪个列或常数与索引一起来查询记录。
9)rows:显示MySQL在表中进行查询时必须检查的行数。
10)Extra:表示MySQL在处理查询时的详细信息。
2. DESCRIBE语句
DESCRIBE语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的。DESCRIBE语句的语法形式如下:
DESCRIBE SELECT select_options
DESCRIBE可以简写成DESC。