mysql之优化-大纲
前言：DBA的日常工作
- 日常操作：监控状态，备份与恢复，高可用，数据迁移，集群搭建与扩容，高可用；
- 体系结构：sql审计，存储引擎，sql优化，缓存/线程，建表与索引，锁与事务；
- 进阶操作：源码定制，内核原理；
首先，我们来看看DBA的具体工作，我觉得 DBA 真的很忙：备份和恢复、监控状态、集群搭建与扩容、数据迁移和高可用，这是我们 DBA 的功能。
了解这些功能以后要对体系结构有更加深入的了解，你不知道怎么处理这些故障和投诉的事情。
所以我们要去了解缓存/线程、SQL优化、存储引擎以及SQL审计以及锁与实务、体系结构更深一点，就去研究内核原理和源码定制，DBA有这么多工作，他们就像一个小怪兽一样等着我们去解决。
一、MySQL的性能优化
性能优化就是我想让我的MySQL跑的更快、更顺畅。在我们开始MySQL性能优化之前，我想提出MySQL性能优化的三个关键点。Why？What？How？为什么我们要做性能优化？
我们的运维来反映我们的数据库，正常情况下是1秒，后来变成10秒，我们就要启动优化的动作。原本他的访问时间是1秒，我们想优化成0.01秒就要开启优化。
第二就是What？哪里是导致我们数据库性能变差的原因一。需要找到这个关键点。当我们找到这个问题以后，我们就需要有的放矢地进行优化。MySQL优化之前我们要明确的3W关键点。
1.1 MySQL优化基本流程
其实对于开展MySQL的优化有这样的一个基本流程。
1.1.1 第一步：OS诊断
我们首先要登陆到操作系统，通过操作系统的命令，比如说操作系统的基本命令，去看我们操作系统有什么资源的占用率比较高，就是出现了资源短板，短板的意思就是这个资源的占用率或者是使用率特别高，我们要密切关注。
1.1.2 第二步：OS资源短板
比如说像CPU的负载特别高，已经超过了我们的核数，或者是使用率特别高，已经达到了80%以上，这就引起我们的关注了。确定这个短板之后，我们就要确认哪个进程使用我们这个资源，使得它的使用率或者是占用率特别高。
1.1.3 第三步：mysql最耗资源
一般情况下跟我们相关就是MySQL这一层，比方说使用CPU的70%以上，我们就要去检查一下这个 MySQL 出现什么问题。
1.1.4 第四步：sql语句优化
再进一步往里推进，如果我们发现MySQL里面是执行某一条大MySQL的时候，发现整个服务器或者是整个数据库就在那里，可能就是语句问题。
1.1.5 第五步：mysql监控
我们就要进一步通过 MySQL 的监控或者是日志信息去排查MySQL的问题。这个是很重要的发现哪个资源出现问题然后进行排查。
我们登陆系统就不会发现有CPU、IO、网络等等都很正常。在这种情况下怎么办？在这种情况下可以分三种判断。
第一、 可能我们登陆MySQL的时候整个系统就在那里了，这个情况还是操作系统的问题，我们需要通过操作系统去查是哪个资源的问题。
第二、可能是数据库实例问题，数据库实例问题跟数据库配置参数相关，也就是说我们配置参数可能存在一些不合理的设置需要我们去优化。
第三、可能就是会话，我们登陆MySQL里面，一开始很正常，后来我们发现这个实例慢下来了，可能就是MySQL语句有问题，我们需要看MySQL的执行计划到具体哪一步比较慢，拖慢了整个流程。
我们发现数据库性能出现问题，都可以沿着这个流程走下去，从而定位出问题。
二、优化的几个关键点
我们通过刚才的基本流程，可以确定出 MySQL 需要优化的几个关键点：
2.1 应用层优化
因为有应用需要访问我们的数据库，有请求的发送、数据的存储和网络的交互等等，会导致数据库性能会发生比较慢的地方。
我们根据每个关键点稍微开展一下。比方说应用访问的优化：
2.1.1 第一步就是减少数据的访问。
因为减少数据的访问其实就是减少磁盘的访问。我们知道数据访问磁盘获得数据的速度很慢，如果我们是器械磁盘，因为器械磁盘是通过器械旋转来获得数据。
我们应该把活跃数据和内存数据放在内存里面，这样可以使我们的数据库性能提升1-1000倍。它的优化成本很低。
2.1.2 第二步是减少返回更多的数据。
其实减少返回更多的数据终结就是减少了网络的传输，有很多大的系统，网络传输是一个很重要的瓶颈。
假设我们的数据库服务器跟我们应用服务器的距离是20公里的话，因为光线数据库是20公里，一个光的请求是0.2毫秒。如果我们减少更少的数据请求的话，那这个时间就会变短很多。所以说如果我们发现数据库的性能有问题，我们可以去看是否网络上存在问题或者是通过P命令看时间是否会变得长。
2.1.3 第三是减少交互次数。
每个交互假设还是按照20公里来说，一个交互的时间就是0.2毫秒，2个交互就是0.4毫秒。如果有1万个操作的话，就是1万乘0.4毫秒，那就变得整个交互时间变短了很多。
但是也有它的复杂性或者是不宜扩展的局面。从应用层就降低了优化。这个成本也是很低的。
2.2 操作系统层面的优化
就是我们部署配置数据库之前，要对操作系统有什么优化？能够让我们的数据库有优化。
2.2.1 推荐使用Linux操作系统
2.2.2 要使用这个SWAP值
如果要去做的话，我们应该最大程度去使用物理，我们尽量不去使用虚拟内存，而使用物理内存。
因为物理内存的访问速度肯定比去访问磁盘要快得多。所以我们就把这个值设成了10。有的同学可能就会说为什么不把这个值设成0，就直接全部访问物理内存就好了。
如果把它设为0的话，可能就会出现内存溢出的现象，就是OOM。这不是我们DBA想看到的情况。所以我们一般把这个值设成10。
2.2.3 第三就是关闭NUMA特性
公司是单实例的情况下，这个时候NUMA的特性要关注，NUMA特性就是假设我们一个服务器上有两个CPU，分布在服务器左右两边，同时有四块内存，把同一侧CPU作为一个NUMA节点，就是在物理位置分布同一侧CPU访问同一侧内存，距离比较近，速度更快。
我们尽量同一侧CPU访问同一侧内存。这跟我们数据库的特性是相违背的。因为我们数据库希望它一般部署了数据库的服务器就不会布其他的应用系统资源了。
所以我们希望数据库是独占数据库资源。所以在这种情况下我们要尽量关闭这个NUMA特性。
2.2.4 第四就是网卡优化
我们采用多个物理网卡通过做bond绑定成虚拟网卡，就是一些双网卡做成Bond或者调整网络参数。
2.2.5 第五就是磁盘调度设置
一般会有几个算法，比如说NOOP算法、CFQ或者是Deadline算法，比如说这NOOP算法用在我们数据库上有什么问题？就会有饿死读操作的方式存在，如果两个写操作，第一个写操作进来不需要等这个结束以后第二个写操作就可以开展了。
如果是读操作的话，第二个读操作就一定要在在前一个完成。如果有几毫秒的时间里面，进来一堆写操作，后面的独操作就会饿死的，这个不符合我们数据库算法调动的方式。
- 另外就是CFQ算法，CFQ算法不适合我们的数据库服务器，MySQL是单操作服务器。所以我们这个算法也不适合我们使用。一般情况下数据服务器会使用Deadline的算法，程序会调用这个时候的IO请求去解决这个请求。这种Deadline算法更加适合数据库，因为这个Deadline的算法更加适合。
2.2.6 最后一个是文件系统的推荐
移动云的数据库系统是Xfs或者是Ext4或者是Noatime或者是nobarrier，这些都会有影响。这是数据库系统的优化。
2.3 数据库实例的优化
数据库优化过程其实是一个全局角度优化的过程，不仅仅是是针对数据库本身优化的流程。
我列了几个参数我们在标准化的时候需要规范和配置的。这里不一一揭示了。这些参数大家都可以找到，重点看一下。其实这些参数很重要。因为它决定了我们实例的性能。某一些参数配置不合理，我们实例的性能就会受到很大的影响。
2.4 SQL语句的优化
这里有编写高效SQL语句的原则，这个原则我们DBA要知道，DBA要通知业务方的研发，让他们也知道。有很多业务侧进来都是业务写的，他没有经验的话，就会写出一些有问题的语句，所以最后就变成我们DBA要去严查。所以最开始要把这些思想贯彻给业务研发，让他们按照这个流程去编写SQL的设计。
2.5 索引的设计
这里说的是覆盖索引，比如说有了这个覆盖索引我们的查询，查询的字段都是在这个索引内，还有我们查询的后面的字段也是索引，还有我们一些排序位置也是覆盖索引，就是这一系列全部都是中了索引的情况所以就叫覆盖索引。也列举了一些不能使用索引的情况。比如说不要给选择率低的字段选择索引，如果通过索引扫描记录数超过30%就变成全表扫描了。还有Like额查询条件列最左以通配符%开始，两个独立索引，其中一个用于索引一个用于排序。以上就是对于MySQL性能优化的步骤。
2.6 服务器硬件选型
大多公司的DBA对于服务器的应用选型没有太多的话语权，移动公司都是集团公司通过集采来选择的。在采集的时候不可能规定这几台服务器是用在数据库，这几个数据库用在服务系统。
如果DBA对于服务器有自主权的话，可能要考虑到数据和日志的存储机理不同，要选择不同的类型去优化它。可以把数据放到SSD盘，把日志放到SAS上，这就是服务器硬件选型需要主要的地方。
