MySQL性能优化

• 优化的基本原则
  • 优化简介
  • 基本原则
• 硬件优化
• 参数优化
  • 操作系统参数
  • MySQL 参数
• MySQL 库表结构优化
• MySQL 语句及索引优化

优化的基本原则

优化简介

所谓MySQL优化，一方面是指通过调整系统参数、合理安排资源使得 MySQL 的运行速度更快、更加节省资源，另一方面，也指优化我们通常使用的 SQL 语句，尤其是查询语句，来提高 MySQL 的性能。

总的来说，优化可以在不同的层次上进行，主要分为以下层次，如下图1所示：

• 硬件层面
• 配置参数层面
• 数据库表结构层面
• SQL 及索引层面

从底层往上层(硬件层面->索引层面)，优化的成本越来越高，但是效果越来越差。总结一下：

• 成本：硬件优化 > 配置优化 > 表结构优化 > SQL语句优化 > 索引优化
• 效果：索引优化 > SQL语句优化 > 表结构优化 > 配置优化 > 硬件优化

基本原则

MySQL性能优化的基本原则如下：

• 减少系统瓶颈
• 减少资源占用
• 提高系统反应速度

MySQL性能优化通常从下面几个方面入手：

• 找出系统瓶颈，提高 MySQL数据库的整体性能
• 合理的结构设计和参数调整，提高数据库操作的响应速度
• 最大限度节省系统资源，以便系统可以提供更大负荷的服务

比如：

• 通过优化文件系统，来提高磁盘 I/O 的读写速度
• 通过优化操作系统的调度策略，提高 MySQL 在高负荷情况下的负载能力
• 通过优化表结构、索引、查询语句等使得查询响应更快

硬件优化

MySQL 最终是运行在硬件上，对硬件优化无疑可以提高 MySQL 的性能，比如在云托管型数据库场景下，通过纵向扩展来提高 MySQL 性能，以便能承受更高的 QPS。

硬件优化就是对 MySQL 所在的服务器 CPU、内存、磁盘进行优化。大内存，高 I/O，是现代基于 Web 的数据库的必备。

参数优化

参数主要包含两方面的参数：操作系统层面的参数以及MySQL层面的参数。

操作系统参数

数据库是基于操作系统的，目前大多数 MySQL 都是安装在 Linux 系统之上，所以对于操作系统的一些参数配置也会影响到 MySQL 的性能，下面列举出一些常见的配置:

• 增加 tcp 支持的队列数

  • net.ipv4.tcp_max_syn_backlog = 65535

• 减少断开连接时，资源回收

  • net.ipv4.tcp_max_tw_buckets = 8000
  • net.ipv4.tcp_tw_reuse = 1
  • net.ipv4.tcp_tw_recycle = 1
  • net.ipv4.tcp_fin_timeout = 10

• 打开文件的限制

  • 可以使用 ulimit -a 显示各种限制，我们可以通过修改/etc/security/limits.conf 文件修改其限制，比如 *soft nofile 65535、*hard nofile 65535 等。

注意：一般情况下，尽量不要去修改系统参数，因为系统参数是对整个操作系统生效的，可能这个服务器上并不仅仅只启动了一个 MySQL 服务，特别是在云数据库场景下。

MySQL 参数

MySQL 库表结构优化

MySQL 语句及索引优化