sql优化之使用explain与profile来分析mysql查询性能
2014-04-02 09:17:06;  来源:;  作者:;  评论:0 点击:

在mysql查询性能分析中最常用的就是explain了,profile查看一些具体的性能也是不错的1 profile我们可以先使用SELECT @@profiling;来查看
在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
在做性能测试中经常遇到一些数据库的问题,通常使用慢查询日志可以找到执行效果比较差的sql,但是仅仅找到这些sql是不行的,我们需要协助开发人员分析问题所在,这就经常用到explain
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 
使用方法,在select语句前加上explain就可以了: 
 
如:explain select surname,first_name form a,b where a.id=b.id 
 
分析结果形式如下: 
table |  type | possible_keys | key | key_len  | ref | rows | Extra 
EXPLAIN列的解释: 
table 
显示这一行的数据是关于哪张表的 
 
type 
这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 
 
possible_keys 
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 
 
key 
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 
 
key_len 
使用的索引的长度。在不损失精确性的情况下,长度越短越好 
 
ref 
显示索引的哪一列被使用了,如果可能的话,是一个常数 
 
rows 
MYSQL认为必须检查的用来返回请求数据的行数 
 
Extra 
关于MYSQL如何解析查询的额外信息。将在表中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 
 
extra列返回的描述的意义 
Distinct 
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 
 
Not exists 
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 
 
就不再搜索了 
 
Range checked for each 
 
Record(index map:#) 
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 
 
Using filesort 
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 
 
Using index 
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 
 
Using temporary 
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
 
Where used 
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题 
 
不同连接类型的解释(按照效率高低的顺序排序) 
 
system 
表只有一行:system表。这是const连接类型的特殊情况 
 
const 
表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待 
 
eq_ref 
在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用 
 
ref 
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
 
range 
这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况 
 
index 
这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据) 
 
ALL 
这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

更多关于mysql优化技术你可以参考自习室mysql教程中的优化章节
本文属转载文章,并不能保证完全正确,只供学习交流参考,版权归原作者所有。如果您认为有侵犯权利等不和法行为,请联系我们及时改正。http://www.zhuitaiyang.com/html/mysql/648.html

相关热词搜索:explain profile mysql 查询分析

上一篇:各种平台环境下的mysql重启命令总结
下一篇:随机获取Mysql数据表的一条或多条记录(及速度分析总结 rand join)

收藏
回到顶部