本文根据是MySQL中文网创始人叶金荣在DevOps华南运维圈@UCloud微信群的「运维在线」栏目的嘉宾分享整理而成。

　　目录

1. 　　MySQL的特点;
2. 　　硬件、系统优化;
3. 　　MySQL 配置优化;
4. 　　SCHEMA设计优化;
5. 　　SQL 优化;
6. 　   其他优化。
7.       问答环节

  一、MySQL 的特点

　　首先我们需要明确的是。想要做好MySQL优化，就需要先了解MySQL都有哪些特点，如图1所示的内容：

　　图1

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

　　（1）复杂查询;

　　（2）复杂运算;

　　（3）大二进制存储。等奇葩用途。

　　1.CPU的利用特点

　　看看MySQL不同版本对CPU多核的支持、利用情况，如图2所示的内容：

　　图2

　　建议：

　　（1）采用最新MySQL版本，以提升其CPU利用率;

　　（2）每个SQL足够简单，不要太过复杂;

　　（3）每个连接足够快速完成，不要“恋战”。2.内存利用特点

　　那么在内存利用、管理方面有什么特点呢?如图3所示的内容

　　图3

　　建议：

　　（1）关闭query cache;

　　（2）采用InnoDB;

　　（3）采用Percona\MariaDB分支版本;

　　简单KV数据用NOSQL存储，不使用MySQL。3.磁盘的利用特点

　　最后我们看下磁盘I/O方面的特点，如图4所示的内容：

　　图4

　　建议：

　　（1）使用多盘提升整体I/O性能;

　　（2）多使用高速I/O设备;

　　（3）尽量加大内存，缓解I/O负载。4.MySQL 优化

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

　　在这之前，我们需要先明确以下几点：

• 　　为何而优化?领导指派\用户投诉\监控预警\没事找事?当前跑得好好的话，就没必要折腾神马优化没事找抽，即便想练手，也要悠着点，防止误操作;
• 　　优化的目标是什么，说白了，就是要解决什么瓶颈，切忌在过程中偏离初心;
• 　　计算投入产出比，比如为了让性能提升1%而投入1人月，基本上是非常不划算了，还不如去干点别的;

　　优化前做好现场信息采集，优化后再次采集做对比，确认优化成果(用来邀功啊，让老板看到你的成绩，年底加加薪什么的，最起码也能锻炼总结归纳文档能力吧)。通常，我们进行MySQL优化工作的套路是这样的，如图5所示的内容：

　　图5

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

　　我们常见的瓶颈：

　　（1）绝大多数瓶颈在于I/O子系统;

　  （2）若CPU很高，90%以上是因为索引不当;

　　（3）发生swap时，可能因为内存分配太小或过大;

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

　　瞬间并发很高，采用thread pool;

　　频繁order by\group by，索引入手;

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

　　iowait高，加内存，提高iops，减少数据读写。制定方案时，重点解决发生频率高的问题(量变更容易引起质变);回顾反馈，整理文档，回顾总结，从零散资料中总结出规律，预防风险再次出现。

　　一般采用下面几个瓶颈分析工具，如图6所示的内容：

　　图6

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

　　在MySQL层面，有哪些确认瓶颈的手段呢?如图7所示的内容

　　图7

　　二、硬件、系统优化

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

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

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

　　图8

　　图9

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

　　图10

　                             图11

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

　                        图12

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

　　（1）调整IO Scheduler

　　（2）使用XFS

　　（3）调整其他内核选项备

                      图13

　　备注：

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

　　vm.dirty_background_ratio & vm.dirty_ratio，避免瞬间大量I/O请求导致系统卡死。从这个压测结果可以看到noop/deadline有明显优势。如图14所示的内容

　　图14

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

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

　　在用PCIe SSD设备做测试时，XFS的IOPS能跑到ext4的4倍，表现非常好。如图15所示的内容

　　图15

　　还有什么理由不用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。

            图16

                 　图17

                  　图18

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

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

　　图19

　　如果再启用binlog后的对比，如图20所示：

　　图20

　　最后，再加上sync_binlog选项不同设置的对比，如图21所示：

　　图21

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

　　结论与建议：

• 　　想要保证数据安全，就设置 trx_commit =1 & sync_binlog = 1
• 　　在slave上或非关键场景，可以都改成0四、SCHEMA设计优化

　　接下来看看MySQL的模式(SCHEMA)设计优化要点，如图22所示的内容：

　　图22

　　要点：

• 　　默认地，使用InnoDB引擎，别上MyISAM给自己找事;
• 　　InnoDB必须要有自增(或类似自增)属性的主键;
• 　　不使用或少使用TEXT/BLOB列;
• 　　NOT NULL主要是为了优化索引效率;
• 　　若无特殊需求，均可使用latin1字符集，否则用utf8\utf8mb4等大字符集保证通用性。其他要点，如图23所示的内容：
• 
  

　　图23

　　五、SQL优化

　　SQL优化层面有以下几个要点，如图24所示的内容：

　　图24

　　以及 COUNT(*)、大分页 的优化要点,，如图5所示的内容：

　　图25

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

　　图26

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

　　图27

　　图28

　　MySQL的慢日志可用下面的工具来进行解析与管理,，如图29所示的内容：

　　图29

　　pt-query-digest + Box Anemometer的案例，可以对slow log进行便捷管理。如图30所示的内容：

　　图30

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

　　图31

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

　　图32

　　再看看几个杀手级SQL的案例及其优化建议，如下面的图所示：

　　图33

　　图34

　　图35

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

　　图36

　　图37

　　图38

　　六、其他优化

　　图39

　　关于DBA的利器，常用percona-toolkit工具简介，如下面的图所示：

　　图40

　　图41

      图42

　　附：关于MariaDB及Percona分支版本的简介，如下面的图所示

　　图43

     图44

　　七、问答环节

　　1.多实例，进程会不会抢占?每个事例都是单独起的。

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

　　2. SSD建议单盘还是Raid?

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

　　3. 能介绍一下哪些业务场景适合哪种RAID吗?

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

　　4.nnodb_buffer_pool_instances应该如何设置?

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

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

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

　　6.MariaDB和MySQL的优缺点，以及大神怎么看Maria有否取代MySQL的趋势?

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

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

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

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

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

　　9。恢复性测试怎么做 有流程方案指导一下吗?

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

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

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