MySQL服务器配置优化

MySQL 配置的工作原理

配置文件：Windows：my.ini，Linux：my.cnf。

对于 Linux，配置文件一般是在/etc/my.cnf 或/etc/mysql/my.cnf。

可以通过命令查看：

mysql --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

配置文件被分成多个部分，开头是一个用中括号括起来的分段名称。相应的配置要放在对应的分段中才会生效。

语法、作用域和动态性

配置项使用小写，单词之间用下划线或横线分隔,一般使用下划线。

配置项有多个作用域，有些配置是服务器级别，有些是会话级别，还有对象级别的。

除了在配置文件中配置外，很多变量可以在服务器运行时动态修改，而无需重启服务。

SHOW VARIABLES LIKE 'sort_buffer_size';-- 262144
SET sort_buffer_size = 2621440;
SET GLOBAL sort_buffer_size = 2621440;
SET @@sort_buffer_size = 2621440;
SET @@SESSION.sort_buffer_size = 2621440;
SET @@GLOBAL.sort_buffer_size= 2621440;

动态设置参数会在服务重启后失效，如果希望永久使用这些配置，需要写入配置文件。

如果修改了全局配置，这个值对当前会话和其他已存在的会话都不会生效，只会对新的会话生效。会话级别的修改会对当前会话立刻生效。

有些变量使用了不同的单位，配置前要先确认单位。例如：table_cache 单位是表的数量，而不是缓存的字节数。

设置变量的副作用

动态设置变量可能会有副作用，例如：从缓存中刷新脏块。务必小心动态配置，可能会导致服务器做大量的工作而影响性能。

常用的变量：

key_buffer_size 给键缓冲区分配空间。
table_cache_size 缓存的表的数量。
thread_cache_size 缓存的线程的数量。
query_cache_size 查询缓存分配的内存大小。重新设置 MySQL 会清空所有查询缓存。
read_buffer_size MySQL 读入缓冲区的大小。对表进行顺序扫描的请求会被分配到这个缓冲区，如果对表的顺序扫描特别频繁，可以适当增大该值。
read_rnd_buffer_size MySQL 的随机读缓冲区大小。需要随机读取数据行时，会被分配到这个缓冲区。
sort_buffer_size MySQL 排序时会分配的内存大小，只要涉及排序就会立马分配，而不管是否真正用得到。

sort_buffer_size 由于只要涉及排序就会立马分配指定的大小，所以不建议设置的过大。特别是对于服务器级别，应该设置的小一些，否则会浪费很多内存，增加系统的开销。如果确实需要大的排序内存，可以设置会话级别的，为单独的连接分配即可，最好是针对某些查询来分配。

SET @@SESSION.sort_buffer_size = value;
-- SELECT ...
SET @@SESSION.sort_buffer_size = DEFAULT;

配置变量的值并不是越大越好，错误的配置可能会影响性能甚至服务崩溃。

修改配置过后应该进行一系列的测试，以证明新的配置确实可以提升性能。

创建配置文件

不存在适合任何场景的最好的通用配置文件，开发自己的配置文件尤为重要。

这里列出一份基础的配置：

[mysqld]
# GENERAL
# 数据存放目录
datadir = /var/lib/mysql
# sock文件位置
socket = /var/lib/mysql/mysql.sock
# 进程id文件
pid_file = /var/lib/mysql/mysql.pid
# MySQL运行的用户
user = mysql
# 服务端口
port = 3306
# 默认存储引擎
default_storage_engine = InnoDB
# INNODB
# innodb每个数据页大小
innodb_buffer_pool_size = <value>
# 日志文件大小，如果对 Innodb 数据表有大量的写入操作，适当调大些。
innodb_log_file_size = <value>
# 表结构和表数据分开存放
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
# MyISAM
key_buffer_Size = <value>
# LOGGING
log_error = /var/lib/mysql/mysql-error. log
# 慢查询日志
slow_query_log = /var/lib/mysql/mysql-slow.log
# OTHER
# 内部内存临时表的最大值
tmp_table_size = 32M
max_heap_table_size = 32M
# 是否开启查询缓存
query_cache_type = 0
# 查询缓存分配内存大小
query_cache_size = 0
# 最大连接数
max_connections = <value>
# 线程缓存数
thread_cache = <value>
# 表缓存数
table_cache = <value>
#打开文件数，对于Linux应该设置的足够大
open_files_limit = 65535
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

InnoDB 配置

InnoDB 在大多数情况下需要配置大小合适的缓冲池（Buffer Pool）和日志文件（Log File），默认值太小了。其他配置倒都是可选的。

对于 Linux，应该把 open_files_limit 设置的尽可能大，否则会报错：too many open files。

配置内存使用

MySQL 服务器配置合适的内存对于实现高性能至关重要。

MySQL 内存消耗分两类：可控内存和不可控内存。

配置步骤：

确定可以使用的内存上限。
确定每个连接需要消耗多少内存，例如：排序，临时表。
减去服务器上其他应用程序需要预留的内存。
剩下的内存全部给 MySQL 缓存。

MySQL 可以使用多少内存

MySQL 是单进程多线程服务，32 位操作系统对于单进程使用的内存量很有限，这在一定程度上会限制 MySQL 可以使用的内存。

每个连接需要的内存

MySQL 保持每个连接只需要少量内存，它还要求一个基本量的内存来执行任何查询。需要为高峰期执行的大量查询预留内存，否则可能执行失败。

了解高峰期 MySQL 将消耗多少内存十分重要，建议进行压力测试，以确保内存分配充足。

为操作系统保留内存

跟查询一样，操作系统也需要预留足够的内存来工作。如果没有虚拟内存交换到磁盘，说明系统内存足够用。

建议预留 2GB 或总内存的 5%，以较大者为主。

为缓存分配内存

减去所有的应用程序内存消耗，所有的空闲内存都可以用作 MySQL 缓存。

MySQL 使用缓存来避免磁盘 I/O，磁盘 I/O 开销是很大的，特别是机械硬盘，充足的缓存对性能特别重要。

重要的缓存：

InnoDB 缓存池
InnoDB 日志文件和 MyISAM 数据的操作系统缓存
MyISAM 键缓存
查询缓存
无法手工配置的缓存例如：二进制日志、表定义文件的操作系统缓存。

InnoDB 缓冲池

如果大部分是 InnoDB 表，那么 InnoDB 缓冲池非常需要内存。

InnoDB 缓冲池缓存的数据包括：索引、数据行、自适应哈希索引、插入缓存、锁、内部数据结构。

InnoDB 严重依赖缓冲池，必须分配足够的内存。可以通过 SHOW 或 innotop 监控查看缓冲池的内存利用情况。

线程缓存

线程缓存保存那些当前没有与连接关联的线程，可以为后面的连接快速提供服务。

客户端与服务端建立连接时，MySQL 会从缓存中拿出一个线程与连接关联，断开时又将线程放回缓存中。

线程缓存加速乐客户端与服务端之间的连接和响应。

判断线程缓存是否足够大，可以通过命令查看线程情况：

SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Threads_connected';

表缓存

表缓存和线程缓存概念类似，缓存的对象是表。

缓存的对象包含表的.frm 文件的解析结果，加上一些其他数据。表缓存可以重用资源。

表缓存对于 InnoDB 影响较小。

MySQL5.1 中，表缓存被拆分成两部分：表定义缓存、打开表的缓存。通过 table_open_cache 和 table_definition_cache 来配置。通常可以把 table_definition_cache 设置的足够高来缓存所有表定义。

InnoDB 数据字典

InnoDB 有自己的表缓存，称为表定义缓存或数据字段，目前还无法对其配置。

第一次打开表时会计算统计信息，这需要很多 I/O 操作。InnoDB 没有像 MyISAM 一样对统计信息进行持久化，每次打开表都要重新计算。

如果可以，最好把 innodb_open_files 设置的足够大，使得服务器可以保持.idb 文件同时打开。

配置 MySQL 的 I/O 行为

有些配置会影响 MySQL 同步数据到磁盘以及恢复的操作，这些操作对性能影响很大。

InnoDB 的 I/O 配置

InnoDB 有很多需要配置的地方，它有一系列复杂的缓存和文件设计可以提升性能，以及保证 CAID 的特性，且每个部分都是可以配置的。

InnoDB 事务日志

InnoDB 使用事务日志来减少提交事务时的开销。

日志中记录了事务，无须每个事务提交时都把缓冲池的脏块刷新到磁盘中。事务涉及到的数据和索引通常会映射到表的随机位置，意味着需要大量的随机 I/O。事务日志将随机 I/O 变成顺序 I/O。一旦日志文件写入到磁盘了，事务就持久化了，如果系统崩溃，InnoDB 可以使用事务日志来恢复事务。

事务日志有大表，采用环形写入，当写到末尾后，会从头部开始写，但不会覆盖还未应用到数据文件的记录。

InnoDB 使用一个后台线程智能的刷新这些变更到应用文件。

事务日志文件的大小受控于 innodb_log_file_size 和 innodb_log_files_in_group 两个参数，这对写入性能非常重要。

默认情况下只有两个 5MB 的文件，共 10MB。对于高性能工作来说，这太小了。至少需要几百 MB，甚至几个 G。

InnoDB 使用多个文件作为一组循环日志。通常不需要修改默认的日志数量，修改单个的文件大小即可。

修改日志代销，需要完全关闭 MySQL，将旧日志文件备份并移除，重新配置参数然后重启。成功启动后才可以删除旧的事务日志，否则数据库可能无法恢复。

如果有大事务，增加日志缓冲区大小可以帮助减少 I/O。配置 innodb_log_buffer_size 来控制日志缓冲区大小。推荐的范围是 1MB~8MB。

日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化。如果和持久化相比更在乎性能，可以修改 innodb_flush_log_at_trx_commit 来控制日志缓冲刷新的频繁程度：

0：日志缓存写到日志文件，每秒刷新一次，事务提交时不做任何事。
1：每次事务提交都刷新到持久化存储。
2：日志缓存写到日志文件，但不刷新，InnoDB 每秒做一次刷新，2 与 0 的区别是，即使服务宕机，也不会丢失事务。

把日志缓冲写入到日志文件，日志文件刷新到持久化存储。

SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

打开和刷新日志及数据文件

配置 innodb_flush_method 决定 InnoDB 如何跟文件系统相互作用。

不仅影响 InnoDB 如何写数据，还会影响如何读数据。

Windows 和非 Windows 的系统对这个选项的值是互斥的：async_unbuffered、unbuffered、nomal 只能在 Windows 下使用，且不能用其他值，默认是 unbuffered。其他操作系统是 fdatasync。

配置 MySQL 并发

InnoDB 并发配置

InnoDB 有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它在内核中一次可以做哪些事。

最基本的限制并发的方式是使用 innodb_thread_concurrency 变量，它可以控制一次性可以由多少线程进入内核，0 表示不限制。

理论上：并发值 = CPU 数量 _ 磁盘数量 _ 2

innodb_concurrency_tickets 选项控制票据的数量。

使用线程池控制并发。

基本配置

max_connections 最大连接数，预留一些连接，防止连接失败。
thread_cache_size 线程缓存数。
table_cache_size 表缓存数，应该被设置的足够大些。
expire_logs_days 启用了二进制日志时有用，可以让服务器在指定的天数清理旧的二进制日志。
max_allowed_packet 服务器可以接收的最大包大小。
max_connect_errors 连接错误次数超过这个该值，会被加入黑名单，屏蔽连接。
skip_name_resolve 禁用 DNS 解析。
sql_mode 定义了 MySQL 应该支持的 sql 语法，对数据的校验等等，限制一些所谓的‘不合法’的操作。不建议修改。
read_only 禁止没有特权的用户在备库做变更。
skip_slave_start 阻止 MySQL 试图自动启动复制。
slave_net_timeout 备库主库连接的超时时间。

高级 InnoDB 设置

innodb_autoinc_lock_mode 控制 InnoDB 如何生成自增主键值。高并发插入时，自增主键可能是性能瓶颈。
innodb_buffer_pool_instances 把缓冲池切分成多段。
innodb_io_capacity 告诉 InnoDB 服务器有多大的 I/O 能力。
innodb_read_io_threads 设置后台有多少读线程可以被 I/O 操作。
innodb_write_io_threads 设置后台有多少写线程可以被 I/O 操作。
innodb_strict_mode 开启 InnoDB 严格检查模式，会让以前的警告变为报错。
innodb_old_blocks_time InnoDB 有两段缓冲池 LRU 链表，这个变量指定一个页面从 Young 的子链表转换到 Old 的子链表最少要经历的毫秒数。