MySQL之性能优化

MySQL性能的指标是指响应时间。对性能的剖析包括对应用程序的剖析和对MySQL查询的剖析。

剖析应用程序

一种比较好的对应用程序进行性能优化工具New Relic，在收藏中有安装步骤。该工具不仅对数据库，还能对整个系统进行监控，即使不能给出优化方案，也能定位问题所在。

剖析MySQL

对MySQL查询的剖析应自上而下进行，即剖析整个数据库服务器，定位到具体压力来源的查询，再单独剖析具体查询，分析哪些子任务是响应时间的主要消耗者。
对于数据库服务器，一般采用慢查询日志进行剖析，但由于某些原因如权限不足等，无法在服务器上记录查询，因此可以采用如下方法：

1. 使用pt-query-digest的–processlist选项不断查看SHOW FULL PROCESSLIST的输出，问题是有可能无法捕捉所有的查询。
2. 使用tcpdump抓去网络包数据，再用pt-query-digest的–type=tcpdump选项解析并分析查询网络包数据，解决了上一问题，并且精度较高。

对于单独查询进行剖析，首先使用SHOW PROFILE定位耗时多的操作，步骤如下：

1. 设置会话级的查询剖析：set profiling = 1;
2. 使用查询语句进行查询，该语句即是需要被剖析的对象。
3. 查看剖析结果：show profiles;
4. 查看详细的剖析结果：show profile for query 5; 5代表查询ID，来源于步骤3。
5. 由于步骤4得到表的内容按照执行顺序排序，为了解那一步骤出现问题，需要编写SQL脚本格式化输出。如下例所示：

set @query_id = 1;

select state, sum(duration) as total_r,
		round(
				100 * sum(duration) / 
						select  sum(duration) 
						from information_schema.profiling
						where query_id = @query_id
		), 2) as pct_r,
		count(*) as calls,
		sum(duration) / count(*) as "r/call"
from information_profiling
where query_id = @query_id
group by state
order by total_r desc;

然后，使用SHOW STATUS分析原因。注意show status和show global status的区别，前者在某些计数器上包含了后者。通过对show status的where选择获取所需的计数器，对其进行分析，但此分析大多数是推测性质的。在Percona Server中可以进一步通过show profile获取更加详细的信息。

诊断间歇性问题

间歇性问题如系统偶尔停顿或慢查询，很难诊断，也很难重现。一般步骤包括：

1. 使用工具准确描述问题：show global status，show processlist，查询日志等。
2. 使用工具捕获诊断数据：通过pt-stalk建立触发器的监控变量、阈值、检查频率等，然后调用pt-collect收集数据。系统中应提前安装好gdb（堆栈跟踪工具）、oprofile（服务器诊断工具）和MySQL的debuginfo包（符号信息）。
3. 正确地解释结果数据：通过对上述各个工具收集到的数据，进行两种关注性地条理查找。两个重要的关注包括，主观上假设和猜测的关注，客观上基于合理性和基于可度量的的方式的关注。

坚持信念，工作系统是某种状态机，只要有足够的耐心和细心，逻辑清晰并坚持下去，通常就能得到想要的结果。