【享学MySQL】系列：MySQL性能调优

我是少侠露飞。博客不仅是笔记，更是一种思考，一种分享。

引言

MySQL性能调优是个很宽泛的话题，也是面试必备考察点，MySQL调优，不仅仅是分库分表，SQL语句优化等，写这篇博客，主要从各个方面总结一下MySQL调优的方向点。

硬件层面

1 选择合适的CPU

事实上数据库主要有两类：

• OLTP(Online Transaction Processing，在线事务处理)，多用于日常事务处理的应用中，如银行交易、在线商品交易等。
• OLAP（Online Analytical Processing，在线分析处理），多用在数据仓库和数据集市中，一般需要执行复杂的SQL语句来进行查询。

而MySQL属于OLTP数据库，数据量较小，并发量较大，事务处理的时间较短，查询的语句较简单，一般都走索引。
OLTP属于IO密集型的操作，而OLAP属于CPU密集型的操作。InnoDB主要的后台操作都是在一个单独的MASTER THREAD中完成的，因此并不能很好的支持多核的应用，在当前的MySQL版本下，一条SQL查询语句只能在一个CPU中工作，并不支持多CPU的处理。但是多核CPU对MySQL处理大并发量的请求还是有帮助的。

针对MySQL服务器，应该将更多的注意力放在提高IO的配置上。

2 内存的重要性

我们知道，InnoDB搜索引擎有个重要的缓冲池，即InnoDB Buffer Pool，该缓存既缓存数据，又缓存索引，该缓冲池的大小直接影响了数据库的性能。

假设数据和索引总大小为18GB，依次将缓冲池大小设置为2GB、6GB、10GB、16GB、18GB、20GB时，可以发现，随着缓冲池的增大，TPS也会增加。当缓冲池增大到20GB时，数据库的性能有了很大的提高。因为此时缓冲池的大小已经大于数据文件本身的大小，所有数据文件的操作都可以在内存中进行，因而此时的性能应该是最优的。

这里还有另外两个问题。

1.当缓冲池的大小大于数据库文件的大小，是否意味着没有磁盘操作了？

当然肯定是否定的。因为后台的master线程还负责将脏页异步的写入磁盘，每次事务提交时还需要立即写入redo log文件。

2.索引建立的是不是越多越好？

撇开其它方面不谈，我想根据这里的缓冲池角度就能回答出不是越多越好。因为索引越多，占据的内存越大，这就意味着缓冲池中的能够加载的数据量越小，由上述的实验可知TPS也会受限。

3 硬盘对数据库性能的影响

3.1 机械硬盘

目前大多数的数据库使用的都是传统的机械硬盘。机械硬盘耗时操作主要体现在两个方面：寻道时间和旋转延迟。当前服务器的寻道时间已经能达到3ms，转速15000rpm（意味着转一圈为4ms），因此每次IO平均时间在3+4/2=5ms，这个数据看上去还行，但是当出现高并发的情况，一秒数十万次的IO，再算算此时的时间消耗呢？
并且最关键的是，传统机械硬盘最大的问题在于读写磁头，什么意思呢，机械硬盘读写磁头使得硬盘不能顺序访问，而且能随机访问。随机访问时需要耗费长时间的磁头旋转和定位来查找，因而速度远远慢于顺序访问。

3.2 固态硬盘

内部由闪存（Flash Memory）组成，没有传统的读写磁头，不需要耗费大量的时间进行磁头旋转和定位。

当主机发布一个读写请求时，固态硬盘的控制器会把IO命令从逻辑地址映射成实际的物理地址，写操作还需要修改相应的映射表信息，算上所有的这些额外开销，固态硬盘的访问延时一般也都小于0.1ms。

但是需要注意的一点是，闪存中的数据是不可以更新的，只能通过扇区（sector）的覆盖重写，而在重写之前，还需要执行非常耗时的擦除（erase）操作。擦除操作不能在数据所在的扇区上进行，只能擦除整个被称为擦除块的基础上，该擦除块的尺寸大于扇区的大小，且擦除块有擦写次数的限制（当然目前已有算法来解决该问题），但对于数据库，依然必须认真考虑固态硬盘在写入方面存在的问题。

事实上由于当前MySQL数据库基本基于传统机械硬盘开发，所以并不能充分利用固态硬盘的超高速随机读取性能（一个简单的证据就是，当采用固态的话，就没必要再需要邻接页的预读取特性了）。但少侠相信，MySQL官方肯定也在为此做改进。

4 合理的设置RAID

RAID，即独立磁盘冗余数组，基本思想是把多个相对便宜的硬盘组合起来，成为一个磁盘上数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。
RAID类型可分为RAID 0、RAID 1、RAID 5、RAID 10、RAID 50等。

• RAID 0:即带区集，将多个磁盘合并成一个大的磁盘，没有冗余，并行I/O，速度最快。在存放数据时，将数据按照磁盘的个数分段，然后同时将这些数据写进这些盘中。但是RAID 0没有冗余功能，这就意味着当有一个物理磁盘损坏时，则所有的数据都会丢失。理论上多磁盘的效能就等于[单一磁盘效能]×[磁盘数]，但实际上受限于总线I/O瓶颈及其他因素的影响，所以RAID效能会随边际递减。也就是说假设一个磁盘的效能是50MB/S，两个磁盘的RAID 0效能约96MB/S,三个磁盘的RAID 0也许是130MB/S，而不是150MB/S。
• RAID 1:两组以上的N个磁盘相互作为镜像，在一些多线程操作系统中能有很好的读取速度，但写入速度略有降低。除非拥有相同数据的主磁盘和镜像同时损坏，否则只要一个磁盘正常，即可正常运作，因此可靠性最高,但磁盘利用率最低。
• RAID 5: 采用了Disk Striping（硬盘分区）技术。至少需要三个硬盘，把数据和奇偶校验信息存储到不同磁盘上。当一个磁盘损坏时，通过剩下的数据和相应的奇偶信息去恢复损坏的数据。
• RAID 10:先镜射，再分区数据。它将所有硬盘分为两组，视为RAID 0的最低组合，然后将这两组各自视为RAID 1运作。
• RAID 50:即镜像阵列条带，由至少6块硬盘组成，像RAID 0一样，数据被分区成条带，在同一时间内向多块磁盘写入；像RAID 5一样，RAID 50也是以数据的校验位来保证数据的安全，且校验带均匀分布在各个磁盘上（其目的在于提高读写性能）。

对于数据库应用来说，RAID 10是最好的选择，它同时兼顾了RAID 1 和RAID0的特性。

5 操作系统的影响

Linux是MySQL服务器最常见的操作系统。FreeBSD也是一种常见的操作系统，但较早版本的FreeBSD对MySQL支持得不是很好。Solaris是高性能、高可靠性的操作系统，同时其提供的ZFS文件系统非常适合MySQL的数据库引用。
此外，Windows系统在MySQL的数据库应用也很常见，也有很多公司喜欢在开发环境使用Windows版本的MySQL，在正式环境中使用Linux。这本身没什么问题，但问题通常发生在大小写敏感方面，Windows下表名不区分大小写，而Linux操作系统却是大小写敏感的，这在开发的时候就需要注意的。

软件层面

以上所述的调优多是涉及MySQL服务器硬件设备选型等硬件层面的优化方案。但说到底，我们是软件开发工程师，更加侧重的当然还是软件方面的优化。说到软件层面的优化，第一要说得当然还是分库分表了。

分库分表

分库分表，说到底还是在数据量过大时，单库单表承载不了太大的IO连接和TPS。分库分表从规模上分为如下三个层次：

1. 单库单表：一个库，一张表，这是最基本的一种方式，对于一些基本的通用配置表，数据量不是很大时，这种方案完全没问题。
2. 单库多表：这种方案通常是将数据水平拆分，一个库里有多个表，每张表只存放部分数据，所有表的数据就是全量数据。这种方案需要注意的是：当涉及分页查询时，可能有问题，因为我们没办法从任何一张表里获得全量数据。并且当一条数据过来时候，通过什么映射规则确定该记录插入到哪张表里也有讲究，防止映射不均匀，出现数据聚集的问题。
3. 多库多表：这就是将同样的数据分布在不同库中的不同表里，吞吐量更大了，但是单库多表的问题这里也都有。此外，多库还涉及数据的主从同步问题，一般主流的方案是一主多备，然后master只负责写，salve负责读，master中新写入的数据通过bin log文件同步到slave机器上进行更新。

索引优化

这里还是要说说老生常谈的索引优化。索引优化需要注意以下几点：

• 在经常需要搜索的列上建立索引，可以加快搜索的速度。
• 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
• 在经常使用表连接的列上创建索引，这些列主要是一些关联字段，可以加快表连接的速度。
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
• 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
• 在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。

注意连接查询的时候一定不要用SELECT * FROM，不要查询所有数据，并且连接表不宜过多，按阿里的规范是严禁连接查询超过三张表，那有时候想要拿到别的表的一些字段如何做呢？常见的方案是在主表上设置冗余字段（即以空间换时间），这样查询的时候就不用太多的连接查询了。

小结

欢乐而愉快的2020中（秋）国（庆）节即将结束了。特意写篇博客调整一下心态，明天返杭准备上班喽。