mysql语句性能剖析

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 |
+----------------------+----------+

17 rows in set, 1 warning (0.01 sec)

从结果中可以看到Creating sort index 占用了过长的时间，使用explain查看了执行计划，有使用到索引。

最后查看表结果时，发现虽然只返加了一条记录，但是每一个deviceID得到的数据值，其实结果很可观，大概返回１万多记录，需要先对这一万多条记录排序，然后再取最前面一条，所以排序是一个相当长的过程

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        |
+----------------------------+----------+