MySQL参数级优化和系统级优化

最近学习了一点MySQL数据库的优化，于是乎就想写一写自己的心得与体会。
相对于Oracle来说，MySQL有很多值得吐槽的地方：稳定性、糟糕的优化器，难看的执行计划等等。不过综合来讲，MySQL还是蛮不错的，首先开源、免费；配置灵活，可以根据具体场景进行应用；性能方面，优化后的MySQL能够达到与Oracle相当的水平，这点几乎没有其他的数据库能做到了。
淘宝在开发出自己的OceanBase前用的就是MySQL数据库，足以说明MySQL的品质了。

—数据库优化的概念

大体来分，数据库的优化可以分为以下三种：SQL优化，参数级优化和系统级优化。

SQL优化：通过改变SQL语句，调整关键条件，索引等等，改变数据库优化器的执行策略，使优化器能够更好更快地执行SQL语句。Oracle的优化器做的是相当棒的，但是MySQL的优化器真的是很让人吐槽，比如很多条件重写不支持等等。不过从另一方面来讲，这也给DBA留下了很大的空间来优化SQL，提升数据库性能。
参数级优化：改变MySQL的配置文件，主要是my.cnf（win下是my.ini）的参数，达到让数据库作为一个软件，运行到最佳状态。
系统级优化：改变数据库以下（OS，CPU，存储等等）的优化，为数据库运行提供一个良好运转的环境。

本文主要站在运维DBA的角度来进行MySQL（5.6）数据库的优化，包括一些参数级优化和系统级优化的内容。SQL优化暂不写，网上有一些专门介绍MySQL的SQL优化的视频，比如那海蓝蓝老师的视频，需要的可以观看。

—参数级优化

MySQL的参数优化是与存储引擎相关的，在这里只介绍Innodb的参数优化。为什么是InnoDB而不是MyISAM或者是其他存储引擎，我个人的看法是如下：
●混合存储引擎可能会有好处，但是也会带来后期运维上的许多困难，不建议使用混合存储引擎。
●最常用的存储引擎就是InnoDB或者MyISAM，其他存储引擎用的都比较少的，在考虑存储引擎选择时也是首先选择这两种。
●在数据量比较小的时候，MyISAM的速度会比较快。但是MyISAM的缺点也很明显，不支持事务并且是表级锁。我们日常所处理的业务是OLTP，会有频繁的DML操作，数据量一大，表级锁的劣势很快就会显现出来。
●InnoDB现在是由ORACLE公司来开发了，有理由相信在InnoDB会越做越好。

介绍优化之前先简单来给一张InnoDB的系统结构图：

对此图我不再做过多描述，通用关系型数据库的差不多都是这个结构，逻辑结构包括缓存、日志、物理文件等等。

下面介绍几个重要的通用的调优参数：
1.innodb_buffer_pool_size
这个参数就是数据库占用系统内存的大小。理想状态当然是越多越好，数据在缓存中比在磁盘上（普通HDD盘）要快几十倍甚至百倍，我们希望尽可能多的数据在缓存中。但是要考虑到实际情况中还有其他服务，所以一般这个参数设置为系统内存的90%就可以了。

2.innodb_buffer_pool_instances
这个参数可以将将innodb_buffer_pool划分为不同的instance，每个instance独立管理，有独立的LRU、FLUSH、FREE。
大内存时可以多设几个instance，分散管理内存，分散锁的压力，提升数据库的性能。当然不能无限分散，分散也是需要耗费资源的。最优解需要我们根据实际情况摸索。

3.innodb_log_file_size
单个redo日志文件的大小，默认值为5M。单个日志文件越大，就能减少日志切换次数，减少checkpoint时间。所以一般可以设大一点。
在MySQL5.5之前，innodb_log_file_size*innodb_log_file_in_group(文件个数)是不能超过4G的，但是在MySQL5.6以后的版本中logfile最大的可以设为512GB，所以大小也就不用太过关心了。
需要注意的是，redo日志文件越大，数据库挂了恢复的时间越长。不过在实际生产中一般会使用主备结构，主库挂了切到备库，留给主库恢复的时间就比较充足了。尽管如此，我们仍需要考虑到一些其他情况，比如备库损坏，物理文件问题等等。
我个人推荐innodb_log_file_size设置为512M或者1G，可以根据实际情况调整。

4.innodb_log_buffer_size
这个参数目的是写日志时也先写innodb_log_buffer，buffer写满或事务提交，刷新数据。从而避免直接写文件，减少IO操作。如果大事务比较频繁，那么为了避免频繁的IO，可以将此参数设置大一些。

5.innodb_thread_concurrency
允许进入InnoDB核心的线程数，可以理解为限流的功能。
官方推荐innodb_thread_concurrency =0，也就是说让InnoDB自己管理允许进入InnoDB核心的线程数，但是实际生产中这个效果并不良好。在并发压力比较大时，会有大量的线程涌入InnoDB工作核心，造成CPU频繁的上下文切换，不能正常工作，造成数据库的阻塞。
推荐设置innodb_thread_concurrency为cpu的核心数。

6.innodb_io_capacity和innodb_max_dirty_pages_pct
innodb_io_capacity为innodb每秒后台进程处理IO操作的数据页上限，也就是说每秒最多能刷新多少buffer中的脏页。如果IO设备比较给力，那么这个值可以设大一些。一般设置为内存块大小的70%。
innodb_max_dirty_pages_pct为innodb从buffer中刷新脏页的比例。可能听起来和innodb_io_capacity没啥区别，但是两者是不一样的。innodb_io_capacity表示刷新脏页的能力上限，但是这个能力不一定要吃满，毕竟是IO，还是比较耗时的。可以再根据实际情况调整这个比例，innodb_max_dirty_pages_pct是这个比例的上限。
innodb_io_capacity*innodb_max_dirty_pages_pct即为每秒刷新脏页的上限。

7.innodb_flush_method
这个参数有两个选项O_DSYNC和O_DIRECT。
raid设备实际上是有缓存的，如果想避免数据被innodb_buffer和raid多次cache，那么就可以将innodb_flush_method设置为O_DIRECT。

8.innodb_file_per_table
我们知道，InnoDB 默认会将所有的数据库InnoDB引擎的表的表结构独立成文件，但是所有的数据会存储在一个共享空间中：ibdata1，这就会造成在实际生产中ibdata1急速增长，长期会造成性能的急剧下降以及严重的安全隐患。
innodb_file_per_table=1可以将每个表的数据独立出来，形成单独的表空间以及物理文件，避免共享表空间产生的IO竞争，提升数据库的性能。

9.innodb_flush_log_at_trx_commit
这个参数表示InnoDB处理log buffer时的落盘方式。
0：每秒将log buffer的内容写事务日志并且刷新到磁盘；
1：每个事务提交后，将log buffer的内容写事务日志并即时将这些数据内容写入磁盘；
2：每个事务提交后，将log buffer的内容写事务日志，但是这些数据内容并不会即时磁盘，而是要等待操作系统触发刷盘动作，数据才会最终落盘。
1是最稳妥的，但是会有不断的触发IO。平衡性策略需要我们自己把握。

10.sync_binlog
多少事务记录写一次binlog。
binlog的重要性不言而喻，主备数据的传送完全靠binlog。假如说将sync_binlog设为1000，那么假如说主库执行了500条事务挂掉了，那么此时因为还没有写binlog，备库是无法获取到这些数据的，就会出现主备不一致的现象。但是频繁IO必然造成性能下降，所以平衡性仍是需要我们把控。
一般在不严格的系统中，sync_binlog可以设大一点。但是在核心系统中，sync_binlog需要设小一些。不考虑性能，即可以设置innodb_flush_log_at_trx_commit = 1，sync_binlog = 1，这样主备的数据是一致的，不会丢失数据。但是要不要这么做还得具体分析。

—系统级优化

1.硬盘的选择
这个就不多说了，SSD取代HDD已经成为一种趋势。虽然SSD价格比较昂贵一点，也有一些缺点，但是毕竟技术的发展都要经历这个过程。
当前的话，可以考虑用SSD和HDD混搭的策略，HDD存历史数据，SSD存即时数据。
另外我知道一种PCIE总线方式的SSD部署，据说很棒。具体来说我不太了解存储知识，想了解的可以自己查一下。

2.NUMA
非统一内存访问（NUMA）是一种用于多处理器的电脑记忆体设计。在NUMA下，处理器访问它自己的本地存储器的速度比非本地存储器（存储器的地方到另一个处理器之间共享的处理器或存储器）快一些。

通俗来讲，就是让规定CPU管理的内存区域，避免CPU跨内存访问。让两个CPU单独工作，每人管理1G的效率是比两个CPU共同管理2G的效率是要高的。这和人工作的原理是一样的。
如果数据库实例个数为偶数，那么可以均分给NUMA；如果是奇数，那么则不要做NUMA。NUMA具体的实现方式我其实也不太懂，但是相关资料显示会有性能的提高。
另外需要注意的是，NUAM可以在BOIS和OS层面完成：
在os层numa关闭时，打开bios层的numa会影响性能，QPS会下降15-30%；在bios层面numa关闭是，无论os层面的numa是否打开，都不会影响性能。

3.malloc优化
MySQL在运行过程中必定会申请内存，系统的malloc毕竟是考虑到OS所有的程序的，可能对MySQL的支持并非最优。一些高手验证了用jemalloc或者tcmalloc对MySQL的支持是比较好的。
例如jemalloc应用方式如下
下载jemalloc源码包；
编译安装；
./configure;make & make install
配置MySQL
[mysqld_safe]
malloc-lib=$PATH/libjemalloc.so

4.内存插法
6根内存在4通道里的插法有两种：2/2/1/1，简称42插法；2/2/2/0，简称33插法。不同厂家的设备插法对MySQL的性能还是有一定影响的，例如HP/DELL/华为/英业达对比：
HP/DELL/华为保持42插法性能会比33插法性能高。NUMA开启，QPS提升8-20%；NUMA关闭，QPS值能提升12-38%。
英业达无论何种插法都表现良好。

暂时写这么多吧！