MySQL服务器性能剖析

一、性能优化原则

第一：性能即相应时间；

第二：无法测量就无法有效的优化；

二、剖析MySQL查询

对查询性能剖析有两种方式：剖析整个数据库服务器，对这些查询进行单独剖析；

2.1剖析服务器

2.1.1慢查询日志

#默认慢查询日志为关闭状态（图已开启）
show variables like "%slow_query%";
#默认慢查询日志记录时间为10秒（图已修改为4s）
show variables like "%long_query_time%";

#设置开启
set global slow_query_log = 1;
#设置时间4秒，支持微妙级别设置
set global long_query_time = 4;

#慢查询相关参数
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。

slow_query_log_file：MySQL慢查询日志存储路径。

long_query_time ：慢查询阈值(s)，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中。

log_throttle_queries_not_using_indexes：表示每分钟允许记录到slow_log的且未使用索引的sql语句次数(0为无限制，如果为固定值，可能会记录不到sql)。

log_output：日志存储方式。’FILE’表示存入文件，‘TABLE’表示存入系统表。因为FILE模式开销比较低，所以默认为FILE。

log_slow_admin_statements = 1： 记录ALTER TABLE等语句引发的慢查询

log_slow_slave_statements = 1：记录从服务器产生的慢查询

min_examined_row_limit = 100 ：SQL扫描行数大于等于100行才会被记录

2.1.2剖析单条查询

show profile

#第一步，开启profile
set profiling=1;
#第二步，执行相关sql语句
#第三步，查看profile
show profiles;
#第四步，查看特定的sql查询
show profile for query #{id};

show status

MySQL的show status命令返回了一些计数器，既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器。

#清空计数器
flush status;
#执行相关sql语句
#查看相关的操作
show status where Variables_name like "Handler%" or Variables_name like "Created%";

三、诊断间歇性问题

首先要确定是单挑查询的问题，还是服务器的问题；

通过show global status查看（Threads_running、Threads_connected）的尖刺或者凹陷来发现；

通过show processlist查看线程连接和线程状态；

四、总结

总体而言，解决性能问题的方法，首先要澄清问题，然后选择合适的技术来解决问题。如果是提升整体服务器，一个比较好的起点是所有查询记录到日志。如果追查某些性能低下的查询，可以使用show profile或者explain这样的工具。