排查mysql性能几个途径
概览
1. 整体sql执行频率查询
总体排查的三个命令都是通过status变量中查询整体select,insert,update,delete等语句的执行频率,执行一次指数(value值)加1,得到如下的结果(这里要注意的是不能使用Navicat等不是纯mysql纯客户端的应用来查询,会影响数据的准确性):
1.1 查询当前连接的执行频率
show status like 'Com_______'
1.2 查询整个数据库sql的执行频率
show global status like 'Com_______'
1.3 查询innodb存储引擎的执行频率
show global status like 'Innodb_rows_%'
2. 单条sql语句排查
2. 慢日志排查
mysql服务设置开启慢查询日志后,设定相应的慢查询时间,不使用索引的查询等,重启后sql语句执行后就能通过找到日志上记录的慢查询,从而定位需要优化的sql语句。具体怎么做,比较简单可以直接百度哦。
2.1 实时查询
show processlist
2.2 预查询
2.2.1 explain分析
explain的参数,箭头所指方向是性能越来越差
(需要注意的是explain会有坑,因为它是静态分析的并没有像优化器那样做代价分析,所以显示的数据有可能不准确,这需要trace来更准确地分析,本文末尾会有举例)
参数 | 意义 |
---|---|
id | 1.如果id一样,则从上到下执行2.id不一样,则id越大越先执行 |
select type | simple>primary>subquery>derived>union>union result |
type | null>system>const>eq_ref>ref>range>index>ALL |
extra | using index表示select的是索引里的字段,Using filesort在order by非索引字段时会出现;Using temporary; Using filesort在group by非索引字段时会出现 |
2.2.2 show profile分析
先开启profile,然后再查询,深夜了不多说了,自行百度
2.2.3 trace优化器执行计划分析
同样的先开启trace,然后再查询(需要补充的是:要用mysql原生客户端查询!!Navicat等客户端会自己默默的执行一些sql,会把你执行的结果给刷没的)
以下sql原文出自:MySQL 的 OPTIMIZER_TRACE
# 建议在会话级别开启,影响最小
SET optimizer_trace="enabled=on";
# 随着优化过程,trace会被不断append,当超过这个设定值,会停止append,并将未append的数据大小以bytes为单位写入字段MISSING_BYTES_BEYOND_MAX_MEM_SIZE
SET optimizer_trace_max_mem_size=102400;
# 执行你的sql
SELECT ...;
# 查看trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 导出trace
SELECT TRACE INTO DUMPFILE <filename> FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 关闭trace
SET optimizer_trace="enabled=off";
3.explain的坑例:
例如对team表的单个列name做单列普通索引,然后在对name,code列做组合索引,当执行一个where条件中包含组合索引的查询语句时,explain分析出来的key是用的单列索引,但按道理应该是组合索引,这就是explain给出的信息有问题。
explain分析结果:
trace正确的分析结果: