MySQL 配置优化 | 初意的草莓地

工作原理

类 UNIX 系统中，配置文件位置目录：

/etc/my.cnf
/etc/mysql/my.cnf
# 如果不知道当前的配置文件位置，可以执行以下指令
which mysqld # 查找 mysqld 位置
/usr/bin/mysqld --verbose --help | grep -A 1 'Default options'

最终，服务器会读取配置文件内容，去除空行和注释行，如果手动启动 mysql，会将命令行选项和配置文件内容一起处理。所以，对于长期使用的设置应该写入到全局配置文件，而不是命令行指定。

配置项建议 小写+下划线 的固定格式，即使下划线和中横线是等价的。

作用域

全局作用域：服务器级别。
会话作用域：每个数据库连接。

1
2
3

# 可以使用 show variables 查看
set autocommit = off; # 设置 session 变量，退出当前 session，该配置失效。
set global autocommit = on; # 设置全局变量，退出当前 session，该配置仍然有效。但是对于该配置修改前的连接请求无效。

SET 命令不支持后缀单位，如 1M ；但是支持表达式，如 1024 * 1024。生产环境中，应该 谨慎使用动态设置变量，因为这可能会指导数据库做大量工作。

对于一些连接级别的设置，不要轻易增加全局级别的值。有一些缓存会一次性分配指定大小的全部内存，而不管实际上是否需要这么大。更好的方法是，给这个连接指定连接级别的配置。

对于服务器的配置文件，最好添加版本管理，这样会很方便的返回之前的版本。

具体配置

通用

datadir 数据存放目录
socket
pid_file
MySQL 启动，进程 id 写入的文件位置。
user
port
端口号
default_storage_engine
默认存储引擎，默认 InnoDB。

InnoDB

innodb_buffer_pool_size = [134217728/128M]
InnoDB 缓冲池大小。InnoDB 严重依赖于缓冲池，通常设置的在服务器的最大内存 75% 以内越大越好。但是，缓冲池越大，在关闭或打开的时候，需要将赃页写入数据文件，越耗时。
innodb_buffer_pool_instances = [1]
该参数为 innodb_buffer_pool_size 的辅助参数。当 ibps 设置较大时，开启多个内存缓冲池实例，可以提高并行的内存读写。
innodb_log_buffer_size = [16777216/16M]
事务提交前，不会直接将改变的日志写出磁盘，而是先记入缓存。适当增加这个值，以节省磁盘 I/O 。
innodb_log_file_size = [50331648/48M]
InnoDB 事物日志的大小，方便用户对 MySQL 的恢复。
innodb_flush_log_at_trx_commit
存储引擎事务提交时刷新日志的方式。这个参数涉及到事务从缓存写入到磁盘的方式。0：定时每秒一次 buffer 写入 file , 并且 flush 到磁盘。1：每次事务提交时 buffer 写入 file , 并且 flush 到磁盘。2：每次提交事务 buffer 写入 file，MySQL 定时每秒一次 flush 磁盘。如果项目中，应该没有频率较快的事务提交，因此默认值 1 种方式是非常适合。
innodb_file_per_table
在重建或修改表时，innodb 的表数据会从共享表空间移动到独立表空间(.ibd)。这样可能会损失少许的查询效率，增加文件数量，但是优势是明显的。可以避免产生巨大的表空间，表删除可以释放表空间，同时在备份的时候也更有效率。因此采用默认值就可以。

MyISAM

key_buffer_size = [8388608/8M]
默认情况下，MyISAM 将所有索引都缓存在默认键缓存中。该配置指定索引缓冲的大小，它决定索引处理的速度，尤其是索引读的速度。可以通过检查状态值 key_read_request 和 key_reads 查看其设置是否合理。该配置只对 MyISAM 有效

Logging

log_error = [/var/log/mysqld.log]
错误日志目录位置，可以设置为单独错误文件：/var/lib/mysql/mysql-error.log

Other

tmp_table_size = [16777216/16M]
max_heap_table_size = [16777216/16M]
这两个设置用于控制 Memory 引擎的内存临时表能使用的最大内存。如果隐身内存临时表(in-memory)的大小超过了这两个值，就会转换为磁盘 MyISAM 表(on-disk)，这会导致严重的性能瓶颈。但不能设置过大，防止内存溢出。建议两个设置的大小相同。

临时表 可以通过 CREATE TEMPORARY TABLE 创建。
隐式临时表 MySQL 某些 SQL 在执行时会在内存（磁盘）中创建临时表，以优化 SQL 的执行。以下情况会生产隐式内存临时表：

使用了不同的 ORDER BY 和 GROUP BY 条件

同时使用了DISTINCT和 ORDER BY

下面这些情况不会创建内存临时表，而使用磁盘临时表。

数据表中含有BLOB类型或TEXT类型字段列；

在GROUP BY或DISTINCT任何条件中含有超过512字节的列；

如果使用了UNION或UNION ALL，而且SELECT列中含有任何超过512字节的列

如果in-memory临时表变得太大，超过tmp_table_size或max_heap_table_size数值时；

优化 Blob 和 Text 的场景
服务器不能在内存临时表中存储 Blob 值。所以查询涉及 Blob 值，又需要使用临时表，不管 Blob 有多小，都会立即在磁盘上创建临> 时表，效率很低。有两种方法可以对此进行优化：

使用 substring() 函数把值转换为 varchar。

让临时表更快一些。

query_cache_size = [1048576/1M]
MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。可以通过 show status like '%Qcache%'; 查看查询缓存的命中情况。
query_cache_type = [OFF]
查询缓存默认关闭，设置为 ON 打开。
max-connections = [151]
服务器最大连接数，通常设置为现有连接数的 2 倍，以便应对短暂的连接高峰。可以通过观察 Max_used_connections 状态变量随着时间的变化来检测连接状态。
thread_cache_size = [9]
观察 Threads_connected 状态变量并且找出它的最大最小值，设置超过最大值 2 倍。也可以查看 thread_cache_size 状态变量，如果这个变量一直增长，则可能要调大 thread_cache_size。对于大多数项目，这个默认值是偏小的。
open_files_limit = [5000]
MySQL 可以打开文件描述符的最大数量。太小 MySQL 会报不能打开文件的错误，过大则应注意避免内存溢出的问题。
expire_logs_days = [0/不删除]
如果设置了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用，二进制日志会占用大量的磁盘空间。
max_allowed_packet = [4194304/4M]
运行服务器能发送最大包的大小，默认 4M，如果设置得太小，复制时可能会出问题，表现为备库不能接受主库发送过来的复制数据。建议增加到 16M 或更大。
max_connect_errors = [100]
短暂时间内不断尝试连接服务器，客户端会被列入黑名单，直到再次刷新主机缓存。这个默认值太小，可以增加这个值。
skip_name_resolve = [OFF]
是否允许登录的时候使用 DNS 查找。建议设置为 ON，禁用 DNS 查找，改用 IP 地址访问。

复制相关

read_only = [OFF]
这个设置禁止没有特权的用户对数据做变更。这非常使用于主从时对备库的设置，规范备库把主库主库作为数据的唯一来源。从库建议设置为 ON，但是也要提防从库提升后导致数据不能写入的问题。
skip_slave_start
这个设置会阻止 MySQL 试图自动启动复制，这在服务器崩溃或某些情况下，启动复制是不安全的，所以要禁止，等到服务器排查完毕，手动复制。
slave_net_timeout = [60/分钟]
备库连接主库失败，重新连接的等待时间。默认 60 分钟太长，建议改成 1 分钟。
sync_binlog = [1]
sync_master_info= [1]
sync_relay_log = [10000]
sync_relay_log_info= [10000]
这几个配置表示每隔多少事件将复制状态同步到磁盘，这些配置使得主从复制崩溃的时候，更容易回复。但是频繁的磁盘操作 fsync() 来了严重的性能问题，会导致复制延迟。建议如果开启，适当增加间隔数。