优化MySQL服务器

本节内容需要较全面的知识，可能很难理解，一般只有专业的数据库管理员才能进行这一类的优化。下面为读者介绍优化 MySQL 服务器的方法。

优化服务器硬件

服务器的硬件直接决定着 MySQL 数据库的性能。例如，增加内存和提高硬盘的读写速度，可以提高 MySQL 数据库的查询、更新的速度。

优化服务器硬件的方法主要有以下几种：

• 配置较大的内存
• 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度
• 合理分布磁盘 I/O，把磁盘 I/O 分散在多个设备上，以减少资源竞争，提高并行操作能力
• 配置多处理器，MySQL 是多线程的数据库，多处理器可同时执行多个线程

随着硬件技术的成熟，硬件的价格也随之降低。现在普通的个人电脑都已经配置了 8GB 内存，甚至一些个人电脑配置 16GB 内存。因为内存的读写速度比硬盘的读写速度快。可以在内存中为 MySQL 设置更多的缓冲区，这样可以提高 MySQL 的访问的速度。如果将查询频率很高的记录存储在内存中，那么查询速度就会很快。

如果条件允许，可以将内存提高到 16GB。并且选择 my-innodb-heavy-4G.ini 作为 MySQL 数据库的配置文件。但是，这个配置文件主要支持 InnoDB 存储引擎的表。如果使用 8GB 内存，可以选择 my-huge.ini 作为配置文件。MySQL 所在的计算机最好是专用数据库服务器，这样数据库就可以完全利用该机器的资源。

服务器类型分为 Developer Machine、Server Machine 和 Dedicate MySQL Server Machine。其中 Developer Machine 用来做软件开发的时候使用，数据库占用的资源比较少。后面两者占用的资源比较多，尤其是 Dedicate MySQL Server Machine，其几乎要占用所有的资源。

还可以使用多块磁盘来存储数据。这样可以从多个磁盘上并行读取数据，提高数据库读取数据的速度。通过镜像机制可以将不同计算机上的 MySQL 服务器进行同步，这些 MySQL 服务器中的数据都是一样的。通过不同的 MySQL 服务器来提供数据库服务，这样可以降低单个 MySQL 服务器的压力，从而提高 MySQL 的性能。

优化MySQL参数

和大多数数据库一样，MySQL 提供了很多参数来进行服务器的优化设置。数据库服务器第一次启动时，很多参数都是默认设置的，这在实际应用中并不能完全满足需求，为此数据库管理员要进行必要的设置。

1. 查看性能参数的方法

MySQL 服务器启动之后，可以使用 SHOW VARIABLES; 命令查看系统参数，也可称为静态参数。这些参数是系统默认或者 DBA 调整优化后的参数，可以通过 SET 命令或在配置文件中修改。

使用 SHOW STATUS; 命令查询服务器运行的实时状态信息，也就是动态参数。便于 DBA 查看当前 MySQL 运行的状态，做出相应优化，不能手动修改。

例 1

下面为使用 SHOW VARIABLES 和 SHOW STATUS 命令的实例。

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW STATUS LIKE 'key_read_requests';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 149   |
+-------------------+-------+
1 row in set (0.01 sec)

2. 设置优化性能参数

在 MySQL 中，有些参数直接影响到系统的性能。我们可以通过优化 MySQL 的参数提高资源利用率，从而达到提高 MySQL 服务器性能的目的。以下配置参数都在 my.cnf 或者 my.ini 文件的 [mysqld] 组中。

下面对几个重要参数进行详细介绍。

1）key_buffer_size（针对MyISAM存储引擎）

表示索引缓存的大小，这个参数是对 MyISAM 表性能影响最大的一个参数。值越大，索引进行查询的速度越快。

通过检查状态值 key_read_requests 和 key_reads，可以知道 key_buffer_size 的值是否合理。正常情况下，key_reads / key_read_requests 的比例值需小于 0.01。

2）table_cache（针对MyISAM存储引擎）

表示数据库用户同时打开的表的个数。值越大，能够同时打开的表的个数越多。需要注意的是，这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。

在设置该参数的时候，可以通过 open_tables 和 opened_tables 变量的值来确定该参数的值。open_tables 参数表示当前打开的表缓存数，opened_tables 参数表示曾经打开的表缓存数。

如果 open_tables 的值已经接近 table_cache 的值，且 opened_tables 还在不断变大，则说明 MySQL 正在将缓存的表释放以容纳新的表，此时可能需要加大table_cache 的值。对于大多数情况，比较适合的值如下：

open_tables / opened_tables >= 0.85
open_tables / table_cache <= 0.95

执行 FLUSH TABLE 操作后，系统会关闭一些当前没有使用的表缓存，因此 FLUSH TABLE 后，open_tables 参数的值会变小，opened_tables 参数的值不会变。

3）query_cache_size

表示查询缓存区的大小。使用查询缓存区可以提高查询的速度。

内存中会为 MySQL 保留部分的缓存区，这些缓存区可以提高 MySQL 的处理速度。

可以从以下几个方面考虑如何设置该参数的大小：

• 查询缓存对 DDL 和 DML 语句的性能的影响
• 查询缓存的内部维护成本
• 查询缓存的命中率以及内存使用率等因素

4）query_cache_type

表示查询缓冲区的开启状态，用于控制查询结果是否放到查询缓存中。这种方式只适用于修改操作少且经常执行相同的查询操作的情况，其默认值为 0。

• 值为 0 表示关闭；
• 值为 1 表示开启；
• 值为 2 表示按要求使用查询缓存区，只有 SELECT 语句中使用了 SQL_CACHE 关键字，查询缓存区才会使用。例如，SELECT SQL_CACHE * FROM student。

5）max_connections

表示数据库的最大连接数，默认值为 100。参数最大值不能超过 16384，即使超过也以 16384 为准。该参数设置过小的最明显特征是出现“Too many connections”错误。当然连接数也不是越大越好，因为这些连接会浪费内存的资源。

6）sort_buffer_size

表示排序缓存区的大小。值越大，排序的速度越快。

7）read_buffer_size

表示为每个线程保留的缓冲区的大小。当线程需要从表中连续读取记录时需要用到这个缓冲区。

8）read_rnd_buffer_size

表示为每个线程保留的缓冲区的大小，与 read_buffer_size 相似。但主要用于存储按特定顺序读取出来的记录。

9）innodb_buffer_pool_size

表示 InnoDB 类型的表和索引的最大缓存。值越大，查询的速度越快。但是这个值太大了也会影响操作系统的性能。

调优参考计算方法：

• val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
• val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的 75%
• val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

10）innodb_log_file_size

该参数的作用是设置日志组中每个日志文件的大小。该参数在高写入负载尤其是大数据集的情况下很重要，这个值越大则性能相对较高。最好不要超过 innodb_log_files_in_group * innodb_log_file_size 的 0.75。

11）innodb_log_files_in_group

该参数用于指定数据库中有几个日志组，默认为2个，因为有可能出现跨日志的大事务，所以一般来讲，建议使用 3~4 个日志组。

12）innodb_log_buffer_size

该参数的作用是设置日志缓存的大小，一旦提交事务，则将该缓存池中的内容写到磁盘的日志文件上。该参数的设置在中等强度写入负载以及较短事务情况下，一般都可以满足服务器的性能要求。如果服务器负载较大，可以考虑加大该参数的值。一般缓存池中的内存每秒钟写到磁盘一次，所以设置较大会浪费内存空间，一般设置为 8MB~16MB 就足够了。

可以参考 Innodb_os_log_written 的值，如果该值增加过快，可以适当的增加该参数的值。

13）innodb_flush_log_at_trx_commit

表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数有 3 个值，分别为 0、1 和 2。

• 值为 0 时，表示每隔 1 秒将数据写入日志文件并将日志文件写入磁盘；
• 值为 1 时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；
• 值为 2 时，表示每次提交事务时将数据写入日志文件，每隔 1 秒将日志文件写入磁盘。

该参数的默认值为 1，是最安全最合理的值。为了保证事务的持久性和一致性，建议将该参数设置为 1。

参数设置的值要根据自己的实际情况来设置，并不是值越大越好，可能设置的数值太大体现不出优化效果，反而造成系统空间被占用，导致操作系统变慢。合理的配置参数可以提高 MySQL 服务器的性能。需要注意的是，配置完参数以后，需要重新启动 MySQL 服务配置才会生效。