- 用户反馈:获取存在性能问题的sql
- 慢查询日志:获取存在性能问题的sql
- 实时获取存在性能的sql
使用慢查询日志获取有性能问题的sql
- slow_query_log 启动停止记录慢查询日志
- slow_query_log_file 指定慢查询日志的存储路径以及文件
- long_query_time 指定记录慢查询日志sql执行时间的阈值
- log_queries_not_using_indexes 是否记录未使用的索引的sql
- 1:用户信息以及线程id
- 2:执行查询时间
- 3:锁的时间
- 4、返回数据行数
- 5、扫描数据的行数
- 6、执行sql时间
- 7、执行的sql
常用慢查询日志分析工具 mysqldumpslow
常用慢查询分析工具 pt-query-digest
pt-query-digest \
--explain h=127.0.0.1, u=root,p=p@ssW0rd \ slow-mysql.log
实时查询
查询处理各个阶段所消耗的时间
使用profile
1、set profiling = 1 ;
执行查询
- 启动profile
- 这是一个session级的配置
2、show profiles;
查看每一个查询所消耗的总时间的信息
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00012525 | sel
select count(*) from temp_shop_user |
| 2 | 0.09744925 | select count(*) from temp_shop_user
3、show profiles for query N;
查询的每个阶段所消耗的时间
mysql> show profile for query 1 ;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 2.5E-5 |
| freeing items | 9.6E-5 |
| logging slow query | 3E-6 |
| cleaning up | 2E-6 |
+--------------------+----------+
4 rows in set
4、查询cpu信息
mysql> show profile cpu for query 3;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 3.5E-5 | 0 | 0 |
| checking permissions | 6E-6 | 0 | 0 |
| Opening tables | 1.3E-5 | 0 | 0 |
| System lock | 6E-6 | 0 | 0 |
| init | 8E-6 | 0 | 0 |
| optimizing | 3E-6 | 0 | 0 |
| statistics | 7E-6 | 0 | 0 |
| preparing | 4E-6 | 0 | 0 |
| executing | 2E-6 | 0 | 0 |
| Sending data | 0.0296 | 0.03125 | 0 |
| end | 1E-5 | 0 | 0 |
| query end | 3E-6 | 0 | 0 |
| closing tables | 8E-6 | 0 | 0 |
| freeing items | 0.000125 | 0 | 0 |
| logging slow query | 2E-6 | 0 | 0 |
| cleaning up | 3E-6 | 0 | 0 |
+----------------------+----------+----------+------------+
16 rows in set
5、performance_schema
5.6之后版本,建议启用
1>进入performance_schema 库
mysql> use performance_schema ;
Database changed
2>mysql> update setup_instruments set enabled ='YES' where name like 'stage%';
Query OK, 0 rows affected
Rows matched: 0 Changed: 0 Warnings: 0
3>启动历史记录表
mysql> update setup_consumers set enabled ='YES' where name like 'events%';
Query OK, 0 rows affected
Rows matched: 6 Changed: 0 Warnings: 0