一份超详细的MySQL高性能优化实战总结

鸣涧

一份超详细的MySQL高性能优化实战总结！

MySQL 对于很多 Linux 从业者而言，是一个非常棘手的问题，多数情况都是因为对数据库出现问题的情况和处理思路不清晰。

作者：惨绿少年来源：博客园|2019-01-15 09:34收藏
分享

MySQL 对于很多 Linux 从业者而言，是一个非常棘手的问题，多数情况都是因为对数据库出现问题的情况和处理思路不清晰。

在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程，很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。

MySQL 查询过程

优化的哲学

注：优化有风险，修改需谨慎。

• 优化可能带来的问题：
• 优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。
• 优化手段本来就有很大的风险，只不过你没能力意识到和预见到。
• 任何的技术可以解决一个问题，但必然存在带来一个问题的风险。
• 对于优化来说解决问题而带来的问题，控制在可接受的范围内才是有成果。
• 保持现状或出现更差的情况都是失败。
  

优化的需求：

• 稳定性和业务可持续性，通常比性能更重要。
• 优化不可避免涉及到变更，变更就有风险。
• 优化使性能变好，维持和变差是等概率事件。
• 切记优化，应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化。
  

所以优化工作，是由业务需求驱使的!

优化由谁参与?在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。

优化思路

优化什么

在数据库优化上有两个主要方面：

• 安全：数据可持续性。
• 性能：数据的高性能访问。
  

优化的范围有哪些

存储、主机和操作系统方面：

• 主机架构稳定性
• I/O 规划及配置
• Swap 交换分区
• OS 内核参数和网络问题
  

应用程序方面：

• 应用程序稳定性
• SQL 语句性能
• 串行访问资源
• 性能欠佳会话管理
• 这个应用适不适合用 MySQL
  

数据库优化方面：

• 内存
• 数据库结构(物理&逻辑)
• 实例配置
  

说明：不管是设计系统、定位问题还是优化，都可以按照这个顺序执行。

优化维度

数据库优化维度有如下四个：

• 硬件
• 系统配置
• 数据库表结构
• SQL 及索引
  

优化选择：

• 优化成本：硬件>系统配置>数据库表结构>SQL 及索引。
• 优化效果：硬件<系统配置<数据库表结构
  

鸣涧

基础优化

优化思路

定位问题点吮吸：硬件>系统>应用>数据库>架构(高可用、读写分离、分库分表)。

处理方向：明确优化目标、性能和安全的折中、防患未然。

硬件优化

①主机方面

根据数据库类型，主机 CPU 选择、内存容量选择、磁盘选择：

• 平衡内存和磁盘资源
• 随机的 I/O 和顺序的 I/O
• 主机 RAID 卡的 BBU(Battery Backup Unit)关闭
  

②CPU 的选择

CPU 的两个关键因素：核数、主频。根据不同的业务类型进行选择：

• CPU 密集型：计算比较多，OLTP 主频很高的 CPU、核数还要多。
• IO 密集型：查询比较，OLAP 核数要多，主频不一定高的。
  

③内存的选择

OLAP 类型数据库，需要更多内存，和数据获取量级有关。OLTP 类型数据一般内存是 CPU 核心数量的 2 倍到 4 倍，没有最佳实践。

④存储方面

根据存储数据种类的不同，选择不同的存储设备，配置合理的 RAID 级别(raid5、raid10、热备盘)。

对于操作系统来讲，不需要太特殊的选择，最好做好冗余(raid1)(ssd、sas、sata)。

主机 raid 卡选择：

• 实现操作系统磁盘的冗余(raid1)
• 平衡内存和磁盘资源
• 随机的 I/O 和顺序的 I/O
• 主机 raid 卡的 BBU(Battery Backup Unit)要关闭
  

⑤网络设备方面

使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA 卡)。注意：以上这些规划应该在初始设计系统时就应该考虑好。

服务器硬件优化

服务器硬件优化关键点：

• 物理状态灯
• 自带管理设备：远程控制卡(FENCE设备：ipmi ilo idarc)、开关机、硬件监控。
• 第三方的监控软件、设备(snmp、agent)对物理设施进行监控。
• 存储设备：自带的监控平台。EMC2(HP 收购了)、 日立(HDS)、IBM 低端 OEM HDS、高端存储是自己技术，华为存储。
  

系统优化

CPU：基本不需要调整，在硬件选择方面下功夫即可。

内存：基本不需要调整，在硬件选择方面下功夫即可。

SWAP：MySQL 尽量避免使用 Swap。阿里云的服务器中默认 swap 为 0。

IO ：raid、no lvm、ext4 或 xfs、ssd、IO 调度策略。

Swap 调整(不使用 swap 分区)：

• /proc/sys/vm/swappiness的内容改成0(临时)，/etc/sysctl. conf上添加vm.swappiness=0(永久)
  

这个参数决定了 Linux 是倾向于使用 Swap，还是倾向于释放文件系统 Cache。在内存紧张的情况下，数值越低越倾向于释放文件系统 Cache。

当然，这个参数只能减少使用 Swap 的概率，并不能避免 Linux 使用 Swap。

修改 MySQL 的配置参数 innodb_flush_ method，开启 O_DIRECT 模式。

这种情况下，InnoDB 的 buffer pool 会直接绕过文件系统 Cache 来访问磁盘，但是 redo log 依旧会使用文件系统 Cache。

值得注意的是，Redo log 是覆写模式的，即使使用了文件系统的 Cache，也不会占用太多。

IO 调度策略：

• #echo deadline>/sys/block/sda/queue/scheduler 临时修改为deadline
  

永久修改：

• vi /boot/grub/grub.conf
• 更改到如下内容:
• kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet