sql优化之:mysql的explain与profile
程序员文章站
2022-05-24 12:48:12
...
在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 官方文档讲解的相当详细了,
地址:dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain
来自:nonb.cn
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 官方文档讲解的相当详细了,
地址:dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain
来自:nonb.cn
上一篇: springMVC对异常处理的支持
推荐阅读
-
神奇的 SQL 之 MySQL 性能分析神器 → EXPLAIN,SQL 起飞的基石!
-
荐 Mysql2020最新教程(高级部分)之存储引擎与简单的sql优化
-
查询优化之EXPLAIN的使用分析_MySQL
-
MySQL查询优化之explain的深入解析【转载】
-
sql优化之:mysql的explain与profile
-
读《Effectove MySQL之SQL语句最优化》的有感!
-
Mysql研究之SQL语言的设计与编写完全解析_MySQL
-
Mysql研究之SQL语言的设计与编写完全解析_MySQL
-
mysql:21个性能优化最佳实践之2[EXPLAIN你的SELECT查询]_MySQL
-
MySQL架构与Explain优化SQL语句