从Mysql EXPLAIN探寻数据库查询优化

cynial 2010-09-25

展开全文

从Mysql EXPLAIN探寻数据库查询优化

作者: 蘑菇日期: 2010-07-20 16:37:20 阅读: http://www./index.php?load=read&id=35

一. EXPLAIN 语法

EXPLAIN tbl_name|SELECT select_options

EXPLAIN tbl_name和DESCRIBE tbl_name的作用是一样的，用于显示表结构等信息。

当我们在select语句前加上EXPLAIN后，Mysql将告诉我们它是如何处理select语句的，提供表之间的联结方式、使用索引等有关信息。

二. 测试环境简单介绍
为了节省创建表的时间，我用了joomla的文章表做测试，因为要演示优化过程，所以我事先删除了表里除主键之外的所有索引。
这里用到了三个表：

mysql> explain jos_content;
+------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------------------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | | |
| alias | varchar(255) | NO | | | |
| title_alias | varchar(255) | NO | | | |
| introtext | mediumtext | NO | | NULL | |
| fulltext | mediumtext | NO | | NULL | |
| state | tinyint(3) | NO | | 0 | |
| sectionid | int(11) unsigned | NO | | 0 | |
| mask | int(11) unsigned | NO | | 0 | |
| catid | int(11) unsigned | NO | | 0 | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
| created_by | int(11) unsigned | NO | | 0 | |
| created_by_alias | varchar(255) | NO | | | |
| modified | datetime | NO | | 0000-00-00 00:00:00 | |
| modified_by | int(11) unsigned | NO | | 0 | |
| checked_out | int(11) unsigned | NO | | 0 | |
| checked_out_time | datetime | NO | | 0000-00-00 00:00:00 | |
| publish_up | datetime | NO | | 0000-00-00 00:00:00 | |
| publish_down | datetime | NO | | 0000-00-00 00:00:00 | |
| images | text | NO | | NULL | |
| urls | text | NO | | NULL | |
| attribs | text | NO | | NULL | |
| version | int(11) unsigned | NO | | 1 | |
| parentid | int(11) unsigned | NO | | 0 | |
| ordering | int(11) | NO | | 0 | |
| metakey | text | NO | | NULL | |
| metadesc | text | NO | | NULL | |
| access | int(11) unsigned | NO | | 0 | |
| hits | int(11) unsigned | NO | | 0 | |
| metadata | text | NO | | NULL | |
+------------------+------------------+------+-----+---------------------+----------------+
30 rows in set (0.00 sec)

mysql> select count(*) from jos_content;
+----------+
| count(*) |
+----------+
| 46585 |
+----------+
1 row in set (0.00 sec)

mysql> desc jos_categories;
+------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parent_id | int(11) | NO | | 0 | |
| title | varchar(255) | NO | | | |
| name | varchar(255) | NO | | | |
| alias | varchar(255) | NO | | | |
| image | varchar(255) | NO | | | |
| section | varchar(50) | NO | | | |
| image_position | varchar(30) | NO | | | |
| description | text | NO | | NULL | |
| published | tinyint(1) | NO | | 0 | |
| checked_out | int(11) unsigned | NO | | 0 | |
| checked_out_time | datetime | NO | | 0000-00-00 00:00:00 | |
| editor | varchar(50) | YES | | NULL | |
| ordering | int(11) | NO | | 0 | |
| access | tinyint(3) unsigned | NO | | 0 | |
| count | int(11) | NO | | 0 | |
| params | text | NO | | NULL | |
+------------------+---------------------+------+-----+---------------------+----------------+
17 rows in set (0.00 sec)

mysql> select count(*) from jos_categories;
+----------+
| count(*) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)

mysql> desc jos_sections;
+------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | | |
| name | varchar(255) | NO | | | |
| alias | varchar(255) | NO | | | |
| image | text | NO | | NULL | |
| scope | varchar(50) | NO | | | |
| image_position | varchar(30) | NO | | | |
| description | text | NO | | NULL | |
| published | tinyint(1) | NO | | 0 | |
| checked_out | int(11) unsigned | NO | | 0 | |
| checked_out_time | datetime | NO | | 0000-00-00 00:00:00 | |
| ordering | int(11) | NO | | 0 | |
| access | tinyint(3) unsigned | NO | | 0 | |
| count | int(11) | NO | | 0 | |
| params | text | NO | | NULL | |
+------------------+---------------------+------+-----+---------------------+----------------+
15 rows in set (0.00 sec)

mysql> select count(*) from jos_sections;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

简单说明：
jos_sections我们可以称它为大类表，jos_categories为小类表，jos_content为文章表，三个表的记录数分别为2、18、46585。

重要的字段基本上可以见名知义，只对几个联结字段说明一下：
jos_categories.secion:　这个字段是jos_section的主键id，它表明了大类和小类的父子关系，至于jos_categories.parent_id，我们可以先无视它的存在。
jos_content.sectionid: 这个字段也是jos_section的主键id，它表明了文章所属的大类。
jos_content.catid:　这个字段是jos_categories的主键id，它表明了文章所属的小类。

至于为什么会用两个表来存储类别，而不用parent_id的方式实现无限级分类，我们也不必深究，因为这个就是joomla的结构，我们只是用来测试EXPLAIN而已。

三. 测试过程
下面我们说明如何使用EXPLAIN查看执行过程并对表结构和sql语句进行优化。
在下面的测试过程中，我会用一些看来根本就没有意义的SQL语句，只是为了演示SQL语句结构和输出效果，其可用价值我们大可不必过于追究，在实际生产中还要以实际应用为准。

因为使用了EXPLAIN关键字，所以Mysql为我们列出了一些查询信息，它包含以下列：

id: SELECT的识别符，这是SELECT的查询序列号。

select_type: SELECT类型，有以下几种不同的类型
    (1).SIMPLE: 简单的SELECT（不使用UNION或子查询）
    (2).PRIMARY: 最外面的SELECT，如果我们使用UNION或子查询，第一个查询将会是这个类型
    (3).UNION: 使用UNION查询时，除第一个语句外的所有语句会返回这个类型
    (4).DEPENDENT UNION:　UNION中的第二个或后面的SELECT语句，取决于外面的查询。
    (5).UNION RESULT: UNION的结果。
    (6).SUBQUERY: 子查询中的第一个SELECT。
    (7).DEPENDENT SUBQUERY: 子查询中的第一个SELECT，取决于外面的查询。
    (8).DERIVED: 衍生表会返回这个类型。如：select * from (select * from jos_content) as A;。

table: 输出引用的表。

type: 联接类型，从这个选项我们可以初步判断查询效率，有以下几种不同的类型（按从最佳到最坏排序）：
    (1).system: 表中仅有一行记录，这是const的一个特例。
    (2).const: 表中最多有一行符合查询条件，它在查询开始时被读取。因为只有一行，这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只被读取一次！（如上面的查询）
    (3).eq_ref: 对于每个来自于前面的表的行组合，从该表中读取一行。例如：select * from A,B where A.id=B.id，如果id在B表中是unique或primary key，会返回这个类型。它是说对于A表中的每一行，在B表中读取符合记录的一行。除了const之外，这是最好的联接类型。
    (4).ref: 这个类型跟eq_ref类似，不同的是eq_ref能根据unique或主键在后面的表中选择出唯一的行，而不能确定唯一行，则使用这个类型。
    (5).ref_or_null: 该联接类型如同ref，但是添加了MySQL 可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    (6).index_merge: 索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。
    (7).unique_subquery: unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。explain select * from jos_content where id in (select id from jos_categories);会使用这个类型。
    (8).index_subquery: 该联接类型类似于unique_subquery。可以替换IN子查询，但只适合子查询中的非唯一索引。
    (9).range: 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用这个类型。
    (10).index: 这与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
    (11).all: 对于每个来自于先前的表的行组合，将要做一个完整的表扫描。如果表格是第一个没标记const的表，效果不是很好，并且在所有的其他情况下很差。你可以通过增加更多的索引来避免ALL，使得行能从早先的表中基于常数值或列值被检索出来。

possible_keys: possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在 possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

key: key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。对于MyISAM 和BDB 表，运行ANALYZE TABLE 可以帮助优化器选择更好的索引。对于MyISAM 表，可以使用myisamchk --analyze。

key_len: 此列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。在不损失精确性的情况下，长度越短越好。

ref: 此列显示使用哪个列或常数与key一起从表中选择行。

rows: 此列显示了MySQL认为它执行查询时必须检查的行数。

Extra:　该列包含MySQL解决查询的详细信息。
    (1).Distinct: 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。
    (2).Not exists: MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
    (3).Range checked for each: Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
    (4).Using filesort: MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
    (5).Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
    (6).Using temporary: 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
    (7).Using where: 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

上面的解释，不必全部看懂，有些可以一带而过，因为确实有些抽象，有些我也只是摘抄，但是下面我们用几个具体的实例来更加深入地理解一下。

这个是我们经常使用的一种查询方式，对B表的联接类型使用了eq_ref，索引使用了PRIMARY，但是对于A表，却没有使用任何索引，这可能不是我们想要的。
查看以上SQL语句，我们可能会想到，有必要给A.catid加个索引了。

这样表A便使用了idx_catid索引。

下面我们做一次三个表的联合查询

这里显示了Mysql先将C表读入查询，并使用PRIMARY索引，然后联合A表进行查询，这时候type显示的是ALL，可以用的索引有idx_catid，但是实际没有用。
原因非常明显，因为使用的连接条件是A.sectionid=C.id，所以我们给A.sectionid加个索引先。

这时候显示结果告诉我们，效果很明显，在连接A表时type变成了ref，索引使用了idx_section，如果我们注意看后两列，对A表的查询结果后一次明显少了一半左右，而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。

对于上面的语句，我们换一种写法

Mysql 读入表的顺序被改变了，这意味着，如果我们用left join来做连接查询，Mysql会按SQL语句中表出现的顺序读入，还有一个有变化的地方是联接B和C的type都变成了eq_ref，前边我们说过，这样说明Mysql可以找到唯一的行，这个效率是比ref要高的。

再来看一个排序的例子：

对于上面两条语句，只是修改了一下排序字段，而第一个使用了Using temporary，而第二个却没有。在日常的网站维护中，如果有Using temporary出现，说明需要做一些优化措施了。
而为什么第一个用了临时表，而第二个没有用呢？
因为如果有ORDER BY子句和一个不同的GROUP BY子句，或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。
那么，对于上面例子中的第一条语句，我们需要对jos_categories的id进行排序，可以将SQL做如下改动：

mysql> explain select B.id,B.title,A.title from jos_categories A left join jos_content B on A.id=B.catid left join jos_sections C on B.sectionid=C.id order by A.id;
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 18 | Using filesort |
| 1 | SIMPLE | B | ref | idx_catid | idx_catid | 4 | joomla_test.A.id | 3328 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test.B.sectionid | 1 | Using index |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
3 rows in set (0.00 sec)

这样我们发现，不会再有Using temporary了，而且在查询jos_content时，查询的记录明显有了数量级的降低，这是因为jos_content的idx_catid起了作用。
所以结论是：尽量对第一个表的索引键进行排序，这样效率是高的。

我们还会发现，在排序的语句中都出现了Using filesort，字面意思可能会被理解为：使用文件进行排序或中文件中进行排序。实际上这是不正确的，这是一个让人产生误解的词语。
当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。

然而，当我们回过头来再看上面运行过的一个SQL的时候会有以下发现：

这是我们刚才运行过的一条语句，只是加了一个排序，而这条语句中C表的主键对排序起了作用，我们会发现Using filesort没有了。
而尽管在上面的语句中也是对第一个表的主键进行排序，却没有得到想要的效果（第一个表的主键没有用到），这是为什么呢？实际上以上运行过的所有left join的语句中，第一个表的索引都没有用到，尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪！

于是我们继续测试了下一条SQL：

然后，当再次进行排序操作的时候，Using filesoft也没有再出现

这个结果表明：对where条件里涉及到的字段，Mysql会使用索引进行搜索，而这个索引的使用也对排序的效率有很好的提升。

写了段程序测试了一下，分别让以下两个SQL语句执行200次：

select A.id,A.title,B.title from jos_content A left join jos_categories B on A.catid=B.id left join jos_sections C on A.sectionid=C.id
select A.id,A.title,B.title from jos_content A,jos_categories B,jos_sections C where A.catid=B.id and A.sectionid=C.id
select A.id,A.title,B.title from jos_content A left join jos_categories B on A.catid=B.id left join jos_sections C on A.sectionid=C.id　order by rand() limit 10
select A.id from jos_content A left join jos_categories B on B.id=A.catid left join jos_sections C on A.sectionid=C.id order by A.id

结果是第(1)条平均用时20s，第(2)条平均用时44s，第(3)条平均用时70s，第(4)条平均用时2s。而且假如我们用explain观察第(3)条语句的执行情况，会发现它创建了temporary表来进行排序。

综上所述，可以得出如下结论：
1. 对需要查询和排序的字段要加索引。
2. 在一定环境下，left join还是比普通连接查询效率要高，但是要尽量少地连接表，并且在做连接查询时注意观察索引是否起了作用。
3. 排序尽量对第一个表的索引字段进行，可以避免mysql创建临时表，这是非常耗资源的。
4. 对where条件里涉及到的字段，应适当地添加索引，这样会对排序操作有优化的作用。
5. 在做随机抽取数据的需求时，避免使用order by rand()，从上面的例子可以看出，这种是很浪费数据库资源的，在执行过程中用show processlist查看，会发现第(3)条有Copying to tmp table on disk。而对(3)和(4)的对比得知，如果要实现这个功能，最好另辟奚径，来减轻Mysql的压力。
6. 从第4点可以看出，如果说在分页时我们能先得到主键，再根据主键查询相关内容，也能得到查询的优化效果。通过国外《High Performance MySQL》专家组的测试可以看出，根据主键进行查询的类似“SELECT ... FROM... WHERE id = ...”的SQL语句（其中id为PRIMARYKEY），每秒钟能够处理10000次以上的查询，而普通的SELECT查询每秒只能处理几十次到几百次。涉及到分页的查询效率问题，网上的可用资源越来越多，查询功能也体现出了它的重要性。也便是sphinx、lucene这些第三方搜索引擎的用武之地了。
7. 在平时的作业中，可以打开Mysql的Slow queries功能，经常检查一下是哪些语句降低的Mysql的执行效率，并进行定期优化。