MySQL通用优化技巧

内容提纲

1. MySQL的特点；

2. 硬件、系统优化；

3. MySQL 配置优化；

4. SCHEMA设计优化；

5. SQL 优化；

6. 其他优化。

MySQL 的特点

首先，需要明确的是。想要做好MySQL优化，需要先了解MySQL都有哪些特点：

简言之，MySQL一般用于互联网业务的数据持久化存储，并且用于保证数据的一致性、可靠性，而不是用于：

• 复杂查询；

• 复杂运算；

• 大二进制存储。

等奇葩用途。

CPU的利用特点

看看MySQL不同版本对CPU多核的支持、利用情况：

建议：

• 采用最新MySQL版本，以提升其CPU利用率；

• 每个SQL足够简单，不要太过复杂；

• 每个连接足够快速完成，不要“恋战”。

内存利用特点

内存利用、管理方面有什么特点呢？

建议：

• 关闭query cache；

• 采用InnoDB；

• 采用Percona\MariaDB分支版本；

• 简单KV数据用NOSQL存储，不使用MySQL。

磁盘的利用特点

最后看下磁盘I/O方面的特点：

建议：

• 使用多盘提升整体I/O性能；

• 多使用高速I/O设备；

• 尽量加大内存，缓解I/O负载。

MySQL 优化

了解完MySQL各方面的特点后，我们可以开始进行优化工作了。

在开始之前，我们需要先明确几点：

1. 为何而优化？领导指派\用户投诉\监控预警\没事找事？当前跑得好好的话，就没必要折腾神马优化没事找抽，即便想练手，也要悠着点，防止误操作；

2. 优化的目标是什么，说白了，就是要解决什么瓶颈，切忌在过程中偏离初心；

3. 计算投入产出比，比如为了让性能提升1%而投入1人月，基本上是非常不划算了，还不如去干点别的；

4. 优化前做好现场信息采集，优化后再次采集做对比，确认优化成果（用来邀功啊，让老板看到你的成绩，年底加加薪什么的，最起码也能锻炼总结归纳文档能力吧）。

通常，我们进行MySQL优化工作的套路是这样的：

确认需求，先明确当前的运行状态，是否真的需要进行优化，别没事找事；

常见瓶颈：

• 绝大多数瓶颈在于I/O子系统；

• 若CPU很高，90%以上是因为索引不当；

• 发生swap时，可能因为内存分配太小或过大；

• iowait太高时，想办法从索引角度入手优化，以及提高I/O设备性能，增加内存，减少排序，减少SELECT一次性读取数据量。

常用优化策略

1. 瞬间并发很高，采用thread pool；

2. 频繁order by\group by，索引入手；

3. 适当调整内存，不要太大或太小。一般ibp设置为50% ~ 70%为宜；

4. iowait高，加内存，提高iops，减少数据读写。

制定方案时，重点解决发生频率高的问题（量变更容易引起质变）；回顾反馈，整理文档，回顾总结，从零散资料中总结出规律，预防风险再次出现。

一般采用下面几个瓶颈分析工具：

绝大多数情况下，有经验的工程师靠sysstat工具集中的就足够了，很多问题一看现象大概就能知道瓶颈何在。

在MySQL层面，有哪些确认瓶颈的手段呢？

硬件、系统优化

我们继续MySQL优化之旅。先来看看从硬件以及OS层面，都有哪些可以优化的。首先主要是BIOS中关于CPU和内存的参数调整，其次是RAID方面的优化。

再来看看几个参考配置图：

1、CPU选择最大性能模式，避免节能模式导致性能不足。

2、关闭NUMA，降低swap概率。

3、采用RAID-10，并且选择FORCE WB。

在OS层面，可以有几个优化手段：

• 调整IO Scheduler

• 使用XFS

• 调整其他内核选项备

备注：

1. vm.swappiness，降低发生swap的几率；

2. vm.dirty_background_ratio & vm.dirty_ratio，避免瞬间大量I/O请求导致系统卡死。

从这个压测结果可以看到noop/deadline有明显优势。

这个io scheduler还可以在线修改的哦，还等神马？

echo deadline > /sys/block/sdc/queue/scheduler

在用PCIe SSD设备做测试时，XFS的IOPS能跑到ext4的4倍，表现非常好。

还有什么理由不用XFS呢？

xfs挂载参数：

/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

格式化参数不用特别指定，默认的即可。

MySQL配置优化

前面讲到，给MySQL分配的内存不要太大或太小，那么多少合适呢。

首先，要搞清楚MySQL的内存都由哪些部分组成：

1. global buffers和oracle的SGA一个意思，就是全局一次分配，多个线程间共享。

2. thread buffers和oracle的PGA一个意思，每个线程单独分配，线程间不能相互共享，因此不要分配过大，避免内存不够使用，发生OOM。

原则： 对这些选项调整时，不要照猫画虎随便调整，要先做到心里有数，了解其具体作用才动手。

看看innodb_flush_log_at_trx_commit分别为0、1、2的性能对比如：

如果再启用binlog后的对比：

最后，再加上sync_binlog选项不同设置的对比：

备注： 这3个测试结果图均来自Percona。

结论&建议：

1. 想要保证数据安全，就设置 trx_commit =1 & sync_binlog = 1

2. 在slave上或非关键场景，可以都改成0

SCHEMA设计优化

接下来看看MySQL的模式（SCHEMA）设计优化要点：

要点：

1. 默认地，使用InnoDB引擎，别上MyISAM给自己找事；

2. InnoDB必须要有自增（或类似自增）属性的主键；

3. 不使用或少使用TEXT/BLOB列；

4. NOT NULL主要是为了优化索引效率；

5. 若无特殊需求，均可使用latin1字符集，否则用utf8\utf8mb4等大字符集保证通用性。

其他要点：

SQL优化

SQL优化层面有几个要点：

以及 COUNT(*)、大分页 的优化要点：

接下来，我们来看看EXPLAIN的结果中，有哪些关键信息要注意的。首先看下EXPLAIN结果的type列，都可以给我们什么信息：

再看看Extra列有哪些状态要引起重视：

MySQL的慢日志可用下面的工具来进行解析和管理：

pt-query-digest + Box Anemometer的案例，可以对slow log进行便捷管理。

关于JOIN优化有下面的几个关键点：

接下来看看哪些情况下，无法有效使用索引的：

再看看几个杀手级SQL的案例及其优化建议：

在平时，我们登入MySQL服务器后，如果觉得有问题，可以重点关注下面的一些线程状态：

其他优化

关于DBA的利器，常用percona-toolkit工具简介：

附：关于MariaDB及Percona分支版本的简介

Q&A

Q1: 多实例，进程会不会抢占？每个事例都是单独起的。

A：除了OS层面的资源会相互影响外，其他的不会。比如某个实例消耗特别多cpu资源的话，那么其他实例也会跟着受影响，这是必然的，除非用虚拟化等方式做隔离。

Q2: SSD建议单盘还是Raid？

A：如果不担心丢数据，单盘呗。如果怕丢的话，那显然不能单盘了。随机io很高的话，Raid5就不合适了。不过除非采用SSD，用Raid5也不怕了。事实上，Raid卡反而会影响（降低）SSD性能的发挥，但为了数据可靠性，没办法，还好影响不算特别大。

Q3: 能介绍一下哪些业务场景适合哪种RAID吗？

A：1、高随机IO，用Raid10；2、需要大容量，用Raid5。基本就这两种方案，事实上，因为SSD的IOPS性能已经很不错了，很多企业会选择直接用3块盘构建Raid5。毋庸置疑，上了PCIE SSD，可以避免很多问题，或者DBA可以少干很多活，至少可以缓解。

Q4: nnodb_buffer_pool_instances应该如何设置？

A：ibp的instance一般不超过8为宜，超过8的话，可能有反作用，不过多个instance的前提是，平均到每个instance的ibp不能小于2G，否则也没啥意义。

Q5: No text,or in compressed是指如果使用text的话，建议压缩吗？在压缩数据方面，叶老师有什么经验吗？

A：对的，建议不要在InnoDB中存储大量文本。需要的话，事先压缩好再存进去。不需要检索的文本，可以统统压缩后存进去，不是用InnoDB的压缩格式哦，是事先外部压缩后存储，文本内容在存储进去前先压缩好，不是用InnoDB的compressed这种row format，那会被坑惨的，性能损失9层，只有一半压缩比，还不如用TokuDB算了。

Q6: MariaDB和MySQL的优缺点，以及大神怎么看Maria有否取代MySQL的趋势？

A：想要取代还早呢，没那么容易，而且也没必要取代，作为补充就ok。除非哪天MySQL官方版本闭源了，或者支持很差。

Q7: 新的业务系统，是建议继续用MySQL5.5或以上，还是用mariaDB？

A：建议优先Percona 5.6，其次是MySQL 5.6，最末才是MariaDB。

Q8: 你们的数据库备份是用Percona的工具进行吗？每周一全备，每天一增量？用这些工具备份，会不会出现恢复不了的情况？这个有没有办法验证备份是否“正常” ？

A：工具则以xtrabackup为主，mysqldump为辅，数量不是巨大的话，每天一全备，大多有slave做热备，所以就没定期增备了。Mydumper也有些不太爽的，也比较小众就是，备份文件一定要做恢复性测试，千万别只备份不恢复测试，关键时刻会死人的。

Q9:恢复性测试怎么做 有流程方案指导一下吗？

A：简单的：数据恢复，简单查询验证数量，关键数据什么的；复杂的：搭测试环境呗。

Q10: 有没有什么效率较高的验证备份有效性的工具或者方法？还是只好把库恢复出来核对？

A：mysqldump或mydumper备份的文件，可以用grep简单快速验证；xtrabackup的话，只能看文件大小，或者做全量恢复了。

MySQL通用优化技巧