1.语句性能剖析
set profiling=1;
select *
from Pm25ControlLog
where deviceId='C89346459837' and retCode='00000'
order by submitTime desc limit 1;
+----------+--------------+------+------+---------------------+--------+---------+-----------------------------------------------+
| id | deviceId | pm25 | voc | submitTime | onoff | retCode | retInfo |
+----------+--------------+------+------+---------------------+--------+---------+-----------------------------------------------+
| 37072024 | C89346459837 | 20 | NULL | 2016-11-17 10:50:26 | 20e00F | 00000 | 当前净化器已经关闭无需再次关闭 |
+----------+--------------+------+------+---------------------+--------+---------+-----------------------------------------------+
1 row in set (3.86 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000403 |
| checking permissions | 0.000119 |
| Opening tables | 0.000131 |
| init | 0.000160 |
| System lock | 0.000138 |
| optimizing | 0.000135 |
| statistics | 0.001816 |
| preparing | 0.000159 |
| Sorting result | 0.000117 |
| executing | 0.000114 |
| Sending data | 0.000129 |
| Creating sort index | 3.856124 |
| end | 0.000188 |
| query end | 0.000049 |
| closing tables | 0.000059 |
| freeing items | 0.000062 |
| cleaning up | 0.000146 |
+----------------------+----------+
2.慢日志中的内容如下:
# Time: 161117 16:15:21
# User@Host: root[root] @ localhost [] Id: 144132438
# Query_time: 24.763819 Lock_time: 0.000100 Rows_sent: 1 Rows_examined: 17921432
use smartair;
SET timestamp=1479370521;
select *
from bak_Pm25ControlLog
where deviceId='C89346459837' and retCode='00000'
order by submitTime desc limit 1;
3.从status状态表
flush status;
select *
from Pm25ControlLog
where deviceId='C8934645A3AA' and retCode='00000'
order by submitTime desc limit 1;
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Created_tmp_disk_tables | 0 | 使用的磁盘临时表数据
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 | 使用的内存临时表数
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 44783 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 | 没有使用到索引的读操作
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+----------+
set profiling=1;
select *
from Pm25ControlLog
where deviceId='C89346459837' and retCode='00000'
order by submitTime desc limit 1;
+----------+--------------+------+------+---------------------+--------+---------+-----------------------------------------------+
| id | deviceId | pm25 | voc | submitTime | onoff | retCode | retInfo |
+----------+--------------+------+------+---------------------+--------+---------+-----------------------------------------------+
| 37072024 | C89346459837 | 20 | NULL | 2016-11-17 10:50:26 | 20e00F | 00000 | 当前净化器已经关闭无需再次关闭 |
+----------+--------------+------+------+---------------------+--------+---------+-----------------------------------------------+
1 row in set (3.86 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000403 |
| checking permissions | 0.000119 |
| Opening tables | 0.000131 |
| init | 0.000160 |
| System lock | 0.000138 |
| optimizing | 0.000135 |
| statistics | 0.001816 |
| preparing | 0.000159 |
| Sorting result | 0.000117 |
| executing | 0.000114 |
| Sending data | 0.000129 |
| Creating sort index | 3.856124 |
| end | 0.000188 |
| query end | 0.000049 |
| closing tables | 0.000059 |
| freeing items | 0.000062 |
| cleaning up | 0.000146 |
+----------------------+----------+
17 rows in set, 1 warning (0.01 sec)
从结果中可以看到Creating sort index 占用了过长的时间,使用explain查看了执行计划,有使用到索引。
最后查看表结果时,发现虽然只返加了一条记录,但是每一个deviceID得到的数据值,其实结果很可观,大概返回1万多记录,需要先对这一万多条记录排序,然后再取最前面一条,所以排序是一个相当长的过程
2.慢日志中的内容如下:
# Time: 161117 16:15:21
# User@Host: root[root] @ localhost [] Id: 144132438
# Query_time: 24.763819 Lock_time: 0.000100 Rows_sent: 1 Rows_examined: 17921432
use smartair;
SET timestamp=1479370521;
select *
from bak_Pm25ControlLog
where deviceId='C89346459837' and retCode='00000'
order by submitTime desc limit 1;
3.从status状态表
flush status;
select *
from Pm25ControlLog
where deviceId='C8934645A3AA' and retCode='00000'
order by submitTime desc limit 1;
show status where variable_name like 'Handler%' or variable_name like 'Created%';
mysql> show status where variable_name like 'Handler%' or variable_name like 'Created%';+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Created_tmp_disk_tables | 0 | 使用的磁盘临时表数据
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 | 使用的内存临时表数
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 44783 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 | 没有使用到索引的读操作
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+----------+