MySQL性能优化——数据库配置优化

一、问题由来

这些日子在做一个监控系统，第一步工作就是用python解析每天产生的log日志，将log日志中有用的信息提取并存储在MySQL数据库中。好的，解析日志的脚本写完了，兴高采烈的一测试：解析1个小时产生的650MB log日志总共需要4.5个小时！！

（╯’ - ')╯︵ ┻━┻

这样的性能显然是不满足于实际业务需求的，我们需要对系统进行性能优化。

二、分析瓶颈所在

进行性能优化的第一步，是分析系统性能的瓶颈所在。

前面提到的650MB日志文件包括7篇log日志，处理过程中引入了多线程，使用了并发而非串行的处理方式，所以我们的问题缩小到：如何提高一篇log日志的处理速度。

解析一篇log日志，性能损耗无非两个方面：一是文件读取，二是数据库读写。由于log日志的解析方式是每读取一行文件，在对应表中插入或者更新一条数据，经过简单测试，解析一篇100MB的log日志，数据库插入或者更新的次数超过5万次，性能损耗几乎全部由这几万次数据插入和更新造成。所以我们的系统性能优化问题就进一步缩小为MySQL数据库的优化问题。

为了进一步确认性能瓶颈的确出在MySQL数据库的读写上，我打印了每次插入或者更新其中一张表耗费的时长，单位是微秒：

update controller一次时长 117707
update controller一次时长 50865
update controller一次时长 51861
update controller一次时长 50864
update controller一次时长 51861
update controller一次时长 51861
update controller一次时长 50865
update controller一次时长 46875
update controller一次时长 51862
update controller一次时长 51862
update controller一次时长 84774

正常情况下，由于表中数据量不大，更新一条数据的耗时应当在10ms以内，但现在更新一条controller表中的记录平均耗时超过50ms，由此可以认为性能瓶颈的确出在MySQL数据库的读写上。

三、MySQL性能优化

数据库优化有4个层次：

1. sql语句优化以及索引优化
2. 数据库设计优化
3. 配置优化
4. 硬件升级

其中，sql语句的优化最为简单的同时对性能影响巨大，主要包括：正确创建并使用索引，优化sql执行计划，用具体字段代替*，避免全表扫描等等多个方面，是性价比最高的优化方案；数据库设计优化主要指优化表结构的设计，比如将过长的字段分离成外键，表的分区，数据库的规范化（第一范式、第二范式、第三范式、BCNF）等；配置的优化包括系统配置的优化、网络配置的优化以及MySQL本身配置的优化，比如增加tcp支持的队列数、提高打开文件数的限制、关闭防火墙等都会对数据库的性能产生影响；最后就是升级硬件了。

我的sql语句非常简单，表结构也比较简单，且插入与更新的SQL数量远远超过查询，感觉1,2优化对性能提升不大。果然，经过1,2的优化后，测试发现性能无明显提升，(╯°Д°)╯︵ ┻━┻，开始考虑数据库配置的优化。在my.ini中加入：

innodb_flush_log_at_trx_commit=2
sync_binlog=1000

保存后重启数据库，再测试：

update controller一次时长 1995
update controller一次时长 1995
update controller一次时长 1995
update controller一次时长 1995
update controller一次时长 1995
update controller一次时长 1995
update controller一次时长 1997
update controller一次时长 1995
update controller一次时长 1995
update controller一次时长 998

可以看到更新一条记录的时长缩减到2ms以内，再对650M日志文件进行处理，得到的结果是处理用时447秒。
大功告成＼(＞０＜)／　
接下来介绍my.ini中增加的这神奇的两行。

四、innodb引擎的两个重要参数

innodb_flush_log_at_trx_commit：控制事务日志从innodb log buffer写入到redo log file中以及flush的频率。

innodb_flush_log_at_trx_commit = N，N=0,1,2，解析如下：
N=0 每隔一秒把事务日志缓冲区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上。这种情况下MySQL性能最好，但是如果mysqld进程崩溃，通常会导致最后1秒的日志丢失，所以也最不安全。
N=1 每个事务提交时，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上，这是innodb引擎的默认值。由于每次提交事务的日志都会被刷新到磁盘上，毫无疑问这是最安全的配置方式，但是每次提交事务都要进行磁盘I/O，所以也是效率最低的方式。
N=2 每次事务提交的时候，把事务日志数据从缓冲区写到日志文件中，并每隔1秒刷新一次日志文件到磁盘上。这是一种“比较安全”的配置，当mysqld进程崩溃时，由于日志已经写入系统缓存，所以并不会丢失数据，只有当操作系统崩溃时会导致最后1秒的日志丢失。

sync_binlog：控制二进制日志文件刷新到磁盘上的频率。

sync_binlog=N，N>=0，解析如下：
N=0 不主动刷新日志文件的数据到磁盘上，而是由操作系统决定。
N=m（m>0） 每向二进制日志文件写入m条SQL或m个事务后，将二进制文件的数据刷新到磁盘上。

总结：innodb_flush_log_at_trx_commit与sync_binlog是控制MySQL磁盘写入策略以及数据安全性的重要参数。当：

innodb_flush_log_at_trx_commit=1
sync_binlog=1

时，该模式是最安全的，但同时也是最慢的，写入性能最差，适合数据安全性非常高的情况，比如支付业务；当：

innodb_flush_log_at_trx_commit=0
sync_binlog=0

时，该模式是最快的，但安全性较差；而我所设置的：

innodb_flush_log_at_trx_commit=2
sync_binlog=1000

在兼顾安全性的同时，大大提高了写入效率，使模块达到实际上线的性能标准，撒花~ (ღ˘⌣˘ღ)