阿里顶级程序员轻松带你掌握MySQL调优之查询优化

一、查询慢的原因

1、网络

(1)网络丢包，重传

这个比较容易理解。当SQL 从客户端发送到数据库，执行完毕，数据库将结果返回给客户端，这个将数据返回给客户端的过程本质是网络包传输。因为链路的不稳定性，如果在传输过程中发送丢包会导致数据包重传，进而增加数据传输时间。从客户端来看，就会觉得SQL 执行慢。

(2)网卡满 比如大字段

如果公司业务体量很大，比如平时每天300w订单的电商平台，平台大促(双十一，618)的时候极有可能出现网卡被打满。网卡带宽被占满类似各种节假日高速公路收费站(网卡)拥堵导致车流(数据包传输的速度)行动缓慢。

(3)网络链路变长

该场景会影响应用纬度的一个事务比如交易下单整体耗时。

我们知道每个节点之间的数据传输是需要时间的，比如同城跨机房(15KM)之间的访问一般网络耗时1.5ms左右。

链路1 [client1]--调用--[client2]---[proxy]---[db] 相比 链路2[client1] -- [proxy] --[db]

执行一条sql请求会增加 [client1]--[client2]之间的网络传输耗时大约3ms。如果一个业务事件包含30个sql ，那么链路1要比链路2 多花至少90ms的时间成本。导致业务整体变慢。

2、CPU

(1)cpu 电源策略

cpu 电源策略是控制cpu运行在哪种模式下的耗电策略的，对于数据库服务器推荐最大性能模式 以下内容摘自 《Red Hat Enterprise Linux7 电源管理指南》

ASPM（Active-State Power Management，活动状态电源管理）能节省 PCI Express（PCIe，Peripheral Component Interconnect Express）子系统的电量，其原理为当 PCIe 连接没有处于使用状态时将其设定为低功率状态。ASPM 可以同时控制连接两端的电源状态，并且在连接终端的设备处于满电状态的情况下，仍然可以节电。

启用 ASPM 时，在不同电源状态间转换连接时需要时间，因此会增加设备延迟。ASPM 有三种决定电源状态的策略：

• 默认（default）
• 根据系统固件（例如：BIOS）指定的默认设置设定 PCIe 连接的电源状态。这是 ASPM 的默认状态。
• 节电（powersave）
• 将 ASPM 设定为尽可能节电，不考虑性能损失。
• 性能（performance）
• 禁用 ASPM 使 PCIe 链接以最佳性能操作。

使用 pcie_aspm kernel 参数可以启用或者禁用 ASPM，其中 pcie_aspm=off 会禁用 ASPM，而 pcie_aspm=force 会启用 ASPM，即使在不支持 ASPM 的设备中也可以。

ASPM 策略在 /sys/module/pcie_aspm/parameters/policy 中设置，但还可以使用 pcie_aspm.policy kernel 参数在启动时指定，其中 pcie_aspm.policy=performance 将设定 ASPM 性能策略。

(2)CPU被消耗

CPU的消耗如图:

用户

用户空间CPU消耗，各种逻辑运算

正在进行大量tps

函数/排序/类型转化/逻辑IO访问…

用户空间消耗大量cpu，产生的系统调用是什么？那些函数使用了cpu周期？

IO等待

等待IO请求的完成

此时CPU实际上空闲

如vmstat中的wa 很高。但IO等待增加，wa也不一定会上升（请求I/O后等待响应，但进程从核上移开了）

3、IO

(1)磁盘IO被其他任务占用

有些备份策略为了减少备份空间的使用，基于xtrabckup备份的时候 使用了compress选项将备份集压缩。当我们需要在数据库服务器上恢复一个比较大的实例，而解压缩的过程需要耗费cpu和占用大量io导致数据库实例所在的磁盘io使用率100%，会影响MySQL 从磁盘获取数据的速度，导致大量慢查询。

(2)raid卡 充放电，raid 卡重置

RAID卡都有写cache(Battery Backed Write Cache),写cache对IO性能的提升非常明显,因为掉电会丢失数据,所以必须由电池提供支持。电池会定期充放电,一般为90天左右,当发现电量低于某个阀值时,会将写cache策略从writeback置为writethrough,相当于写cache会失效,这时如果系统有大量的IO操作,可能会明显感觉到IO响应速度变慢,cpu 队列堆积系统load飙高。

(3)IO调度算法

noop（电梯式调度策略）：

NOOP实现了一个FIFO队列，它像电梯的工作方式一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一个介质。NOOP倾向于饿死读而利于写，因此NOOP对于闪存设备，RAM以及嵌入式是最好的选择。

deadline（介质时间调度策略）：

Deadline确保了在一个截至时间内服务请求，这个截至时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象。Deadline对数据库类应用是最好的选择。

anticipatory（预料I/O调度策略）：

本质上与Deadline一样，但在最后一次读操作后，要等待6ms，才能继续进行对其他I/O请求进行调度。它会在每个6ms中插入新的I/O操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。AS适合于写入较多的环境，比如文件服务器，AS对数据库环境表现很差。

4、上下文切换

(1)什么叫上下文切换?

假设有三个CPU,每个CPU核心数是4,那么物理核心数:3*4 = 12(个)

这个核是指CPU里面的Core,可以理解为是将四个CPU封装在一起,但是有的CPU具有超线程的技术,一个核可以模拟出两个两个虚拟核,上面的12个物理核可以让操作系统认为有24个核,同是并行执行24个任务,注意:实际上一个核(Core)只能同时执行一个任务,多个任务同时执行的话,会在Core上高速切换。

为什么要超线程?

超线程这个概念是Intel提出的，简单来说是在一个CPU上真正的并发两