mysql explain 及 profile，优化SQL

http://hi.baidu.com/gguoyu/blog/item/f811d7f4cfd9b0e27709d752.html

mysql explain 及 profile

2010-07-09 14:22在mysql查询性能分析中最常用的就是explain了，profile查看一些具体的性能也是不错的
1. profile
我们可以先使用
SELECT @@profiling;
来查看是否已经启用profile，如果profilng值为0，可以通过
SET profiling = 1;
来启用。启用profiling之后，我们执行一条查询语句，比如：
select count(*) from roi_summary;
然后show profiles查看如下：
+----------+------------+----------------------------------+
| Query_ID | Duration       | Query                            |
+----------+------------+----------------------------------+
|        1       | 0.00021500 | select @@profiling               |
|        2       | 0.05522700 | select count(*) from roi_summary |
+----------+------------+----------------------------------+
2 rows in set (0.00 sec)

其中ID为5的语句是刚执行的查询语句，这时候我们执行show profile for query 2来查看这条语句的执行过程如下;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000021 |
| checking query cache for query | 0.000045 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000011 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000040 |
| init                           | 0.000012 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000010 |
| preparing                      | 0.000010 |
| executing                      | 0.000005 |
| Sending data                   | 0.055021 |
| end                            | 0.000007 |
| end                            | 0.000004 |
| query end                      | 0.000003 |
| storing result in query cache | 0.000004 |
| freeing items                  | 0.000008 |
| closing tables                 | 0.000005 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
20 rows in set (0.00 sec)
可以看出此条查询语句的执行过程及执行时间，总的时间约为0.05s。
这时候我们再执行一次
select count(*) from roi_summary;
show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration   | Query                            |
+----------+------------+----------------------------------+
|        1 | 0.00021500 | select @@profiling               |
|        2 | 0.05522700 | select count(*) from roi_summary |
|        3 | 0.00006000 | select count(*) from roi_summary |
+----------+------------+----------------------------------+

然后执行show profile for query 3来查看本条语句的执行过程
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000016 |
| checking query cache for query | 0.000007 |
| checking privileges on cached | 0.000004 |
| checking permissions           | 0.000005 |
| sending cached result to clien | 0.000022 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

可以看出此次第二次查询因为前一次的查询生成了cache，所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取，结果查询时间比第一次快了N倍。

2. explain
至于EXPLAIN 官方文档讲解的相当详细了，
地址：http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain

=============================================================================

首先，影响所有语句的一个因素是：你的许可设置得越复杂，所需要的开销越多。

执行GRANT语句时使用简单的许可，当客户执行语句时，可以使MySQL降低许可检查开销。例如，如果未授予任何表级或列级权限，服务器不需要检查tables_priv和columns_priv表的内容。同样地，如果不对任何 账户进行限制，服务器不需要对资源进行统计。如果查询量很高，可以花一些时间使用简化的授权结构来降低许可检查开销。

如果你的问题是与具体MySQL表达式或函数有关，可以使用mysql客户程序所带的BENCHMARK()函数执行定时测试。其语法为BENCHMARK(loop_count,expression)。例如：

mysql> SELECT BENCHMARK(1000000,1+1)；+------------------------+| BENCHMARK(1000000,1+1) |+------------------------+|                      0 |+------------------------+1 row in set (0.32 sec)上面结果在PentiumII 400MHz系统上获得。它显示MySQL在该系统上在0.32秒内可以执行1,000,000个简单的+表达式运算。

所有MySQL函数应该被高度优化，但是总有可能有一些例外。BENCHMARK()是一个找出是否查询有问题的优秀的工具。

7.2.1. EXPLAIN语法（获取SELECT相关信息）
EXPLAIN tbl_name或：

EXPLAIN [EXTENDED] SELECT select_optionsEXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息：

·         EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

·         如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。

该节解释EXPLAIN的第2个用法。

借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计（例如关键字集的势），这样会影响优化器进行的选择。参见13.5.2.1节，“ANALYZE TABLE语法”。

还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是SELECT开头。

EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。

当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。

EXPLAIN的每个输出行提供一个表的相关信息，并且每个行包括下面的列：

·         id

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

·         select_type

SELECT类型，可以为以下任何一种：

o        SIMPLE

简单SELECT(不使用UNION或子查询)

o        PRIMARY

最外面的SELECT

o        UNION

UNION中的第二个或后面的SELECT语句

o        DEPENDENT UNION

UNION中的第二个或后面的SELECT语句，取决于外面的查询

o        UNION RESULT

UNION的结果。

o        SUBQUERY

子查询中的第一个SELECT

o        DEPENDENT SUBQUERY

子查询中的第一个SELECT，取决于外面的查询

o        DERIVED

导出表的SELECT(FROM子句的子查询)

·         table

输出的行所引用的表。

·         type

联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：

o        system

表仅有一行(=系统表)。这是const联接类型的一个特例。

o        const

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！

const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：

SELECT * from tbl_name WHERE primary_key=1； SELECT * from tbl_nameWHERE primary_key_part1=1和 primary_key_part2=2；o        eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

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;o        ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

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;o        ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

SELECT * FROM ref_tableWHERE key_column=expr OR key_column IS NULL;参见7.2.7节，“MySQL如何优化IS NULL”。

o        index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。详细信息参见7.2.6节，“索引合并优化”。

o        unique_subquery

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

o        index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)o        range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range：

SELECT * FROM tbl_nameWHERE key_column = 10; SELECT * FROM tbl_nameWHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_nameWHERE key_column IN (10,20,30); SELECT * FROM tbl_nameWHERE key_part1= 10 AND key_part2 IN (10,20,30);o        index

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

o        ALL

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

·         possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。参见13.1.2节，“ALTER TABLE语法”。

为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。

·         key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见13.2.7节，“SELECT语法”。

对于MyISAM和BDB表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，可以使用myisamchk --analyze。参见13.5.2.1节，“ANALYZE TABLE语法”和5.9.4节，“表维护和崩溃恢复”。

·         key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

·         ref

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

·         rows

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

·         Extra

该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：

o        Distinct

MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

o        Not exists

MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

下面是一个可以这样优化的查询类型的例子：

SELECT * 从t1 LEFT JOIN t2 ON t1.id=t2.id  WHERE t2.id IS NULL；假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。

o        range checked for each record (index map: #)

MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。关于适用性标准的描述参见7.2.5节，“范围优化”和7.2.6节，“索引合并优化”，不同的是前面表的所有列值已知并且认为是常量。

这并不很快，但比执行没有索引的联接要快得多。

o        Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。参见7.2.12节，“MySQL如何优化ORDER BY”。

o        Using index

从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。

o        Using temporary

为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

o        Using where

WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。

如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

o        Using sort_union(...), Using union(...), Using intersect(...)

这些函数说明如何为index_merge联接类型合并索引扫描。详细信息参见7.2.6节，“索引合并优化”。

o        Using index for group-by

类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。详情参见7.2.13节，“MySQL如何优化GROUP BY”。

通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。参见7.5.2节，“调节服务器参数”。

下列例子显示出一个多表JOIN如何能使用EXPLAIN提供的信息逐步被优化。

假定你有下面所示的SELECT语句，计划使用EXPLAIN来检查它：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,               tt.ProjectReference, tt.EstimatedShipDate,               tt.ActualShipDate, tt.ClientID,               tt.ServiceCodes, tt.RepetitiveID,               tt.CurrentProcess, tt.CurrentDPPerson,               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,               et_1.COUNTRY, do.CUSTNAME        FROM tt, et, et AS et_1, do        WHERE tt.SubmitTime IS NULL          AND tt.ActualPC = et.EMPLOYID          AND tt.AssignedPC = et_1.EMPLOYID          AND tt.ClientID = do.CUSTNMBR;对于这个例子，假定：

·         被比较的列声明如下：

表
 列
 列类型
 
tt
 ActualPC
 CHAR(10)
 
tt
 AssignedPC
 CHAR(10)
 
tt
 ClientID
 CHAR(10)
 
et
 EMPLOYID
 CHAR(15)
 
do
 CUSTNMBR
 CHAR(15)
 

·         表有下面的索引：

表
 索引
 
tt
 ActualPC
 
tt
 AssignedPC
 
tt
 ClientID
 
et
 EMPLOYID(主键)
 
do
 CUSTNMBR(主键)
 

·         tt.ActualPC值不是均匀分布的。

开始，在进行优化前，EXPLAIN语句产生下列信息：

table type possible_keys key  key_len ref  rows  Extraet    ALL  PRIMARY       NULL NULL    NULL 74do    ALL  PRIMARY       NULL NULL    NULL 2135et_1  ALL  PRIMARY       NULL NULL    NULL 74tt    ALL  AssignedPC,   NULL NULL    NULL 3872           ClientID,           ActualPC      range checked for each record (key map: 35) 因为type对每张表是ALL，这个输出显示MySQL正在对所有表产生一个笛卡尔乘积；即每一个行的组合！这将花相当长的时间，因为必须检查每张表的行数的乘积！对于一个实例，这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大，你只能想象它将花多长时间……

这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。

为了修正在列长度上的不同，使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再执行EXPLAIN语句产生这个结果：

table type   possible_keys key     key_len ref         rows    Extratt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using             ClientID,                                         where             ActualPCdo    ALL    PRIMARY       NULL    NULL    NULL        2135      range checked for each record (key map: 1)et_1  ALL    PRIMARY       NULL    NULL    NULL        74      range checked for each record (key map: 1)et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1 这不是完美的，但是好一些了：rows值的乘积少了一个因子74。这个版本在几秒内执行完。

第2种方法能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列的长度失配问题：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),    ->                MODIFY ClientID   VARCHAR(15);EXPLAIN产生的输出显示在下面：

table type   possible_keys key      key_len ref           rows Extraet    ALL    PRIMARY       NULL     NULL    NULL          74tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using             ClientID,                                         where             ActualPCet_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1 这几乎很好了。

剩下的问题是，默认情况，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。幸好，很容易告诉MySQL来分析关键字分布：

mysql> ANALYZE TABLE tt；现在联接是“完美”的了，而且EXPLAIN产生这个结果：

table type   possible_keys key     key_len ref           rows Extratt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using             ClientID,                                        where             ActualPCet    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1注意在从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。如果不是，可以通过在SELECT语句里面使用STRAIGHT_JOIN并且试着在FROM子句以不同的次序列出表，可能得到更好的性能。

7.2.2. 估计查询性能
在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B－树索引进行估计，将需要log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1次搜索才能找到行。

在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，这对于有一个长度为3(中等整数)的索引的500,000行的表，通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。

上面的索引需要大约500,000 * 7 * 3/2 = 5.2MB，(假设典型情况下索引缓存区填充率为2/3)，可以将大部分索引保存在内存中，仅需要1-2调用从OS读数据来找出行。

然而对于写，将需要4次搜索请求(如上)来找到在哪儿存放新索引，并且通常需要2次搜索来更新这个索引并且写入行。

注意，上述讨论并不意味着应用程序的性能将缓慢地以logN 退化！当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题，随数据增加而增加 键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制 键高速缓冲区大小。参见7.5.2节，“调节服务器参数”。

7.2.3. SELECT查询的速度
总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。参见7.4.5节，“MySQL如何使用索引”和7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

下面是一些加速对MyISAM表的查询的一般建议：

·         为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk --analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk --description --verbose可以显示索引分布信息。

·         要想根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！