MySQL性能优化

• 优化的基本原则
  • 优化简介
  • 基本原则
• 硬件优化
• 参数优化
  • 操作系统参数
  • MySQL 参数
• MySQL 库表结构优化
  • 数据类型的选择
  • 字符编码的选择
  • 表数据适度冗余
  • 设置主键ID
• 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 参数

对于DBA来说，可能会经常对 MySQL 参数进行调整，来满足业务的临时需求，比如：临时调大max_connections。MySQL 的配置文件在 my.conf 中，其中有一些参数会直接影响着 MySQL 实例的性能。

• innodb_buffer_pool_size
• innodb_buffer_pool_instances
• innodb_log_buffer_size
• innodb_flush_log_at_trx_commit
• innodb_read_io_threads
• innodb_write_io_threads
• innodb_file_per_table
• innodb_stats_on_metadata
• max_connections

上述只是列举了一些常用的参数，具体如何优化还需要结合实际场景，上述参数的意义和推荐值可以参考 mysql官方文档。

MySQL 库表结构优化

对于一般意义上的 RD 而言，最容易接触到的就是设计业务表结构。俗语说: 表结构的设计直接影响着整个系统的瓶颈。如果表结构设计的好，则能发挥更好的 MySQL 性能。而且表结构一旦确定下来，则后续再进行修改的代价是非常高的，所以对我们来说，再最初进行系统设计的时候，就一定要花大量时间在表结构设计上。我们一般有下面几个纬度进行思考：

数据类型的选择

虽然我们无法改变数据库中需要存储的数据，但是可以精细化管理每个字段的类型，虽然这可能会带来维护成本的提升，但用的合适则能节省存储空间并且提升读写性能。

• 数字类型
• 字符类型
• 时间类型
• enum 与 set 类型

字符编码的选择

字符集直接决定了数据在MySQL中的存储编码方式。由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

表数据适度冗余

在一些场景下，通过适度的冗余存储，会极大的降低 Join 和 多表关联查询的情况，提升 SQL 执行的效率。该种方式是 以空间换时间的方式，同时如何保证冗余字段的一致性也需要进行特殊考虑，确保更新的同时，冗余字段也被更新。

设置主键ID

我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT(自增)标志。因为后续的一些操作需要使用主键：集群，分区等操作。另外主键也应该设计的更短，以便一个索引页中可以存放更多的索引项。

MySQL 语句及索引优化

上述对 表结构 进行优化后，最后一个就是 SQL 语句对优化和索引等优化。这也是 RD 最关心的问题，并且也是成本最低的优化。

语句优化

SQL 语句优化非常庞杂，场景非常多，我们具体使用时，需要根据 执行计划 去详细分析一条 SQL 语句所花费的代价，再根据数据特征和业务特征去进行特定的优化。下面列举一些常见的情况：

• 避免全表扫描
• 应尽量避免在 where 子句中使用!=或<>操作符
• 应尽量避免在 where 子句中对字段进行 null 值判断

索引优化

索引优化也是我们常用的一种优化方式，就是尽可能让业务的每一条 SQL 都走索引，虽然走索引会降低花费的时间成本，但是会增加存储成本，特别是联合索引并且索引项特别多的时候，占用的空间也会特别大。下面是一些常见的索引优化方式：

• 建索引要有区分度
• 尽量扩展索引，不要新建索引
• 索引列不能参与计算
• 最左匹配原则
• 不用的索引及时删除