MySQL的性能优化及自动化运维实践与Mysql高并发优化

首先，我们来看看DBA的具体工作，我觉得 DBA 真的很忙：备份和恢复、监控状态、集群搭建与扩容、数据迁移和高可用，这是我们 DBA 的功能。

了解这些功能以后要对体系结构有更加深入的了解，你不知道怎么处理这些故障和投诉的事情。

所以我们要去了解缓存/线程、SQL优化、存储引擎以及SQL审计以及锁与实务、体系结构更深一点，就去研究内核原理和源码定制，DBA有这么多工作，他们就像一个小怪兽一样等着我们去解决。

今天我站在更加全面的角度跟大家分享一下我觉得我在这一年多DBA工作当中的经验，希望可以给大家带来启发和帮助。

1. MySQL的性能优化

性能优化就是我想让我的MySQL跑的更快、更顺畅。在我们开始MySQL性能优化之前，我想提出MySQL性能优化的三个关键点。Why？What？How？为什么我们要做性能优化？

我们的运维来反映我们的数据库，正常情况下是1秒，后来变成10秒，我们就要启动优化的动作。原本他的访问时间是1秒，我们想优化成0.01秒就要开启优化。

第二就是What？哪里是导致我们数据库性能变差的原因一。需要找到这个关键点。当我们找到这个问题以后，我们就需要有的放矢地进行优化。MySQL优化之前我们要明确的3W关键点。

1.1 MySQL优化基本流程

其实对于开展MySQL的优化有这样的一个基本流程。

第一步我们首先要登陆到操作系统，通过操作系统的命令，比如说操作系统的基本命令，去看我们操作系统有什么资源的占用率比较高，就是出现了资源短板，短板的意思就是这个资源的占用率或者是使用率特别高，我们要密切关注。

比如说像CPU的负载特别高，已经超过了我们的核数，或者是使用率特别高，已经达到了80%以上，这就引起我们的关注了。

确定这个短板之后，我们就要确认哪个进程使用我们这个资源，使得它的使用率或者是占用率特别高。

一般情况下跟我们相关就是MySQL这一层，比方说使用CPU的70%以上，我们就要去检查一下这个 MySQL 出现什么问题。

再进一步往里推进，如果我们发现MySQL里面是执行某一条大MySQL的时候，发现整个服务器或者是整个数据库就在那里，可能就是语句问题。

我们就要进一步通过 MySQL 的监控或者是日志信息去排查MySQL的问题。这个是很重要的发现哪个资源出现问题然后进行排查。

我们登陆系统就不会发现有CPU、IO、网络等等都很正常。在这种情况下怎么办？在这种情况下可以分三种判断。

可能我们登陆MySQL的时候整个系统就在那里了，这个情况还是操作系统的问题，我们需要通过操作系统去查是哪个资源的问题。

第二就是数据库实例问题，数据库实例问题跟数据库配置参数相关，也就是说我们配置参数可能存在一些不合理的设置需要我们去优化。

第三就是会话，我们登陆MySQL里面，一开始很正常，后来我们发现这个实例慢下来了，可能就是MySQL语句有问题，我们需要看MySQL的执行计划到具体哪一步比较慢，拖慢了整个流程。

我们发现数据库性能出现问题，都可以沿着这个流程走下去，从而定位出问题。

1.2 优化的几个关键点

我们通过刚才的基本流程，可以确定出 MySQL 需要优化的几个关键点。

第一是应用访问的优化，因为有应用需要访问我们的数据库，有请求的发送、数据的存储和网络的交互等等，会导致数据库性能会发生比较慢的地方。

二是服务器硬件选型，不知道大家DBA对服务器有没有自主权，如果有自主权的情况下，我是觉得我们应该按照 MySQL 的特性来选择服务器的硬件。

比方说我们可能要考虑到数据和日志的存储机理不同，要选择不同的类型去优化它。

第三个就是操作系统的优化。就是我们部署配置数据库之前，要对操作系统有什么优化？能够让我们的数据库有优化。

最后一个是数据库优化。数据库优化过程其实是一个全局角度优化的过程，不仅仅是是针对数据库本身优化的流程。

1.2.1 应用访问优化

我们根据每个关键点稍微开展一下。比方说应用访问的优化：

首先第一步就是减少数据的访问。因为减少数据的访问其实就是减少磁盘的访问。我们知道数据访问磁盘获得数据的速度很慢，如果我们是器械磁盘，因为器械磁盘是通过器械旋转来获得数据。

我们应该把活跃数据和内存数据放在内存里面，这样可以使我们的数据库性能提升1-1000倍。它的优化成本很低。

第二步是减少返回更多的数据，其实减少返回更多的数据终结就是减少了网络的传输，有很多大的系统，网络传输是一个很重要的瓶颈。

假设我们的数据库服务器跟我们应用服务器的距离是20公里的话，因为光线数据库是20公里，一个光的请求是0.2毫秒。如果我们减少更少的数据请求的话，那这个时间就会变短很多。所以说如果我们发现数据库的性能有问题，我们可以去看是否网络上存在问题或者是通过P命令看时间是否会变得长。

第三是减少交互次数，每个交互假设还是按照20公里来说，一个交互的时间就是0.2毫秒，2个交互就是0.4毫秒。如果有1万个操作的话，就是1万乘0.4毫秒，那就变得整个交互时间变短了很多。

但是也有它的复杂性或者是不宜扩展的局面。从应用层就降低了优化。这个成本也是很低的。

服务器硬件选型。我们公司的DBA对于服务器的应用选型没有太多的话语权，移动公司都是集团公司通过集采来选择的。在采集的时候我们不可能规定这几台服务器是用在数据库，这几个数据库用在服务系统。

所以我们在选择服务器选型时候DBA是没有办法参与进去的。这个大家可以看一下，我们采用的服务器是惠普的DL360G9，CPU：是2核×e5-2650V4，内存是8×32G，硬盘是6×1.2TSAS，网卡是4×10GE+4×1GE+1IPMI。这是我们移动云的一些服务器的选型。

这里特别说一下，如果我们DBA对于服务器有自主权的话，我们可以把数据放到SSD盘，把日志放到SAS上，这就是服务器硬件选型需要主要的地方。

1.2.2 操作系统层面的优化

第一就是毋庸置疑，我们推荐使用Linux操作系统，一些开源主流的是我们做的。像一些商业版Linux这些就是我们在用的。

要使用这个SWAP值，如果要去做的话，我们应该最大程度去使用物理，我们尽量不去使用虚拟内存，而使用物理内存。

因为物理内存的访问速度肯定比去访问磁盘要快得多。所以我们就把这个值设成了10。有的同学可能就会说为什么不把这个值设成0，就直接全部访问物理内存就好了。

如果把它设为0的话，可能就会出现内存溢出的现象，就是OOM。这不是我们DBA想看到的情况。所以我们一般把这个值设成10。

第三就是关闭NUMA特性，我们公司一般是单实例的情况，所以这个时候NUMA的特性要关注，NUMA特性就是假设我们一个服务器上有两个CPU，分布在服务器左右两边，同时有四块内存，把同一侧CPU作为一个NUMA节点，就是在物理位置分布同一侧CPU访问同一侧内存，距离比较近，速度更快。

我们尽量同一侧CPU访问同一侧内存。这跟我们数据库的特性是相违背的。因为我们数据库希望它一般部署了数据库的服务器就不会布其他的应用系统资源了。

所以我们希望数据库是独占数据库资源。所以在这