MySQL服务器 - 性能优化

MySQL对于很多Linux从业者而言，是一个非常棘手的问题，多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程，很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

首先我们说一下，关于优化，优化的点又在哪里？其实就是两个方面：

1、安全（数据可持续性）

2、性能（数据的高性能访问）

优化范围：

1、存储、主机和操作系统方面:

2、主机架构稳定性

3、I/O规划及配置

4、Swap交换分区

5、OS内核参数和网络问题

应用程序方面:

1、应用程序稳定性

2、SQL语句性能

3、串行访问资源

4、性能欠佳会话管理

5、这个应用适不适合用MySQL

数据库优化方面:

1、内存

2、数据库结构(物理&逻辑)

3、实例配置（不管是在，设计系统，定位问题还是优化，都可以按照这个顺序执行）

优化维度：

1、硬件、系统配置、数据库表结构、SQL及索引

1、优化成本:硬件>系统配置>数据库表结构>SQL及索引

2、优化效果:硬件<系统配置<数据库表结构<SQL及索引

检查问题常用工具：

1、msyqladmin#mysql客户端，可进行管理操作

2、mysqlshow#功能强大的查看shell命令

3、show[SESSION|GLOBAL]variables#查看数据库参数信息

4、SHOW[SESSION|GLOBAL]STATUS#查看数据库的状态信息

5、information_schema#获取元数据的方法S

6、SHOWENGINEINNODBSTATUSInnodb#引擎的所有状态

7、SHOWPROCESSLIST#查看当前所有连接session状态

8、explain#获取查询语句的执行计划s

9、howindex#查看表的索引信息

10、slow-log#记录慢查询语句

11、mysqldumpslow#分析slowlog文件的

不常用但好用的工具：

1、zabbix#监控主机、系统、数据库（部署zabbix监控平台）

2、pt-query-digest#分析慢日志

3、mysqlslap#分析慢日志

4、sysbench#压力测试工具

5、mysql profiling#统计数据库整体状态工具 

6、Performance Schema mysql#性能状态统计的数据

7、workbench#管理、备份、监控、分析、优化工具（比较费资源）

 

MySQL的每-一个新版本中都增加了更多的可测量点。如果当前的趋势可靠的话，那么在性能方面比较重要的测量需求很快能够在全球范围内得到支持。但如果只是需要剖析并找出代价高的查询，就不需要如此复杂。有一个工具很早之前就能帮到我们了，这就是慢查询日志。

        捕获MySQL的查询到日志文件中

        在MySQL中，慢查询日志最初只是捕获比较“慢”的查询，而性能剖析却需要针对所有的查询。而且在MySQL 5.0及之前的版本中，慢查询日志的响应时间的单位是秒,粒度太粗了。幸运的是，这些限制都已经成为历史了。在MySQL 5.1及更新的版本中，慢日志的功能已经被加强，可以通过设置long_ query_ time 为0来捕获所有的查询，而且查询的响应时间单位已经可以做到微秒级。如果使用的是Percona Server, 那么5.0版本就具备了这些特性，而且PerconaServer提供了对日志内容和查询捕获的更多控制能力。

        在MySQL的当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具。如果还在担心开启慢查询日志会带来额外的I/O开销，那大可以放心。我们在I/O密集型场景做过基准测试，慢查询日志带来的开销可以忽略不计( 实际上在CPU密集型场景的影响还稍微大一些)。更需要担心的是日志可能消耗大量的磁盘空间。如果长期开启慢查询日志，注意要部署日志轮转(log rotation)工具。或者不要长期启用慢查询日志，只在需要收集负载样本的期间开启即可。

        MySQL还有另外一种查询日志，被称之为“通用日志”，但很少用于分析和剖析服务器性能。通用日志在查询请求到服务器时进行记录，所以不包含响应时间和执行计划等重要信息。MySQL 5.1之后支持将日志记录到数据库的表中，但多数情况下这样做没什么必要。这不但对性能有较大影响，而且MySQL 5.1在将慢查询记录到文件中时已经支持微秒级别的信息，然而将慢查询记录到表中会导致时间粒度退化为只能到秒级。而秒级别的慢查询日志没有太大的意义。

      Percona Server的慢查询日志比MySQL官方版本记录了更多细节且有价值的信息，如查询执行计划、锁、I/O活动等。这些特性都是随着处理各种不同的优化场景的需求而慢慢加进来的。另外在可管理性上也进行了增强。比如全局修改针对每个连接的long_query_ time 的阈值，这样当应用使用连接池或者持久连接的时候，可以不用重置会话级别的变量而启动或者停止连接的查询日志。总的来说，慢查询日志是-种轻量而且功能全面的性能剖析工具，是优化服务器查询的利器。

      有时因为某些原因如权限不足等,无法在服务器_上记录查询。这样的限制我们也常常碰到,所以我们开发了两种替代的技术,都集成到了Percona Toolkit中的pt-query-digest中。第一种是通过--processlist选项不断查看SHOW FULL PROCESSLIST 的输出，记录查询第一次出现的时间和消失的时间。某些情况下这样的精度也足够发现问题，但却无法捕获所有的查询。一些执行较快的查询可能在两次执行的间隙就执行完成了，从而无法捕获到。

      第二种技术是通过抓取TCP网络包，然后根据MySQL的客户端/服务端通信协议进行解析。可以先通过tcpdump将网络包数据保存到磁盘，然后使用pt-query-digest的--type=tcpdump选项来解析并分析查询。此方法的精度比较高，并且可以捕获所有查询。还可以解析更高级的协议特性，比如可以解析二进制协议，从而创建并执行服务端预解析的语句(prepared statement)及压缩协议。另外还有一种方法，就是通过MySQLProxy代理层的脚本来记录所有查询，但在实践中我们很少这样做。

 

      分析查询日志

            强烈建议大家从现在起就利用慢查询日志捕获服务器上的所有查询，并且进行分析。可以在一些典型的时间窗口如业务高峰期的一个小时内记录查询。如果业务趋势比较均衡,那么一分钟甚至更短的时间内捕获需要优化的低效查询也是可行的。

           不要直接打开整个慢查询日志进行分析，这样做只会浪费时间和金钱。首先应该生成--个剖析报告，如果需要，则可以再查看日志中需要特别关注的部分。自顶向下是比较好的方式，否则有可能像前面提到的，反而导致业务的逆优化。

           从慢查询日志中生成剖析报告需要有-款好工具，这里我们建议使用pt-query-digest,这毫无疑问是分析MySQL查询日志最有力的工具。该工具功能强大，包括可以将查询报告保存到数据库中，以及追踪工作负载随时间的变化。

一般情况下，只需要将慢查询日志文件作为参数传递给pt-query-digest,就可以正确地工作了。它会将查询的剖析报告打印出来，并且能够选择将“重要”的查询逐条打印出更详细的信息。输出的报告细节详尽,绝对可以让生活更美好。该工具还在持续的开发中,因此要了解最新的功能请阅读最新版本的文档。

这里给出一份pt-query-digest输出的报告的例子，作为进行性能剖析的开始。这是前面提到过的一个未修改过的剖析报告:

 

可以看到这个比之前的版本多了一些细节。首先，每个查询都有一个ID,这是对查询语句计算出的哈希值指纹,计算时去掉了查询条件中的文本值和所有空格，并且全部转化为小写字母(请注意第三条和第四条语句的摘要看起来- -样，但哈希指纹是不-样的)。该工具对表名也有类似的规范做法。表名InvitesNew后面的问号意味着这是-一个分片(shard)的表,表名后面的分片标识被问号替代，这样就可以将同- -组分片表作为一个整体做汇总统计。这个例子实际上是来自一个压力很大的分片过的Facebook应用。

报告中的V/M列提供了方差均值比(variance-to-mean ratio)的详细数据，方差均值比也就是常说的离差指数(index of dispersion)。离差指数高的查询对应的执行时间的变化较大，而这类查询通常都值得去优化。如果pt-query-digest指定了--explain选项,输出结果中会增加-一列简要描述查询的执行计划，执行计划是查询背后的“极客代码”。通,过联合观察执行计划列和V/M列，可以更容易识别出性能低下需要优化的查询。

最后，在尾部也增加了一行输出，显示了其他17个占比较低而不值得单独显示的查询的统计数据。可以通过--limnit和--outliers选项指定工具显示更多查询的详细信息，而不是将一些不重要的查询汇总在最后一行。默认只会打印时间消耗前10位的查询，或者执行时间超过1秒阈值很多倍的查询，这两个限制都是可配置的。
 

 

 

参考资料：《高性能MySQL》、Java架构师