《高性能mysql》笔记-剖析数据库语句

剖析数据库语句

1.慢查询日志

MySQL慢查询日志是一种轻量而且功能全面的性能剖析工具、CPU开销小； https://www.cnblogs.com/luyucheng/p/6265594.html

2.分析慢查询日志
pt-query-digest 分析mysql慢查询的一个工具 http://www.cnblogs.com/luyucheng/p/6265873.html

常见用法：

1.剖析最差的查询语句

1.使用pt-query-digest,剖析日志，定位 Query1

# Query 1: 0 QPS, 0x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 0
# This item is included in the report because it matches --limit.

2.字节偏移数跳转日志详情

tail -c +0 保存慢查询日志路径 | head -n100 ;

剖析单句查询

1.show profile

mysql> set profiling = 1; #一般为默认关闭，可动态修改，每个查询信息都会保存到一张临时表；

mysql> select * from users;
mysql> show profiles;
mysql> show profile for query 临时表id; # 提高精度，确定单条语句花费的时间在哪个步骤

*缺点：无法对花费的时间order by 排序；

    +--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000022 |
| Waiting for query cache lock   |  0.000023 |
| init                           |  0.000003 |
| checking query cache for query |  0.001966 |
| checking permissions           |  0.000102 |
| Opening tables                 |  0.000660 |
| After opening tables           |  0.000017 |
| System lock                    |  0.000051 |
| Table lock                     |  0.000021 |
| Waiting for query cache lock   |  0.000101 |
| init                           |  0.000193 |
| optimizing                     |  0.000174 |
| statistics                     |  0.000235 |
| preparing                      |  0.000414 |
| executing                      |  0.000112 |
| Sending data                   | 19.693843 |
| end                            |  0.000042 |

解决order by 精确时间排序