MySQL性能相关参数有哪些

共计 5139 个字符，预计需要花费 13 分钟才能阅读完成。

这篇文章给大家介绍 MySQL 性能相关参数有哪些，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

整理 MySQL 常用性能相关参数如下

general_log

记录所有执行的语句，在需要分析问题打开即可，正常服务时不需要开启，以免带来 io 性能影响

query_cache_size

缓存 sql 文本和查询结果的，如果对应的表没有变化，下次碰到一样的 SQL，跳过解析和查询，直接返回结果。

但是表变化非常频繁，SQL 也是动态生产的，由于需要不断更新 cache 内容，这时锁力度很大，反而照成瓶颈。这时最好关掉这个功能，设置参数为 0

sort_buffer_size

针对单个 session 的参数，

排序时，如果用不到 index，session 就会申请一块这么大的内存空间进行排序。如果这个参数值过小会把排序结果写入硬盘中，会影响效率。

如果太大，又可能导致物理内存耗尽，导致 OOM。

join_buffer_size

在 join 无法使用到 index 时候用到的 buffer，和 sort_buffer_size 类似

tmp_table_size

在 group by 和 distinct 时如果 SQL 用不到索引，就会使用系统内部临时表记录中间状态。如果该值不够大，就使用物理硬盘

Innodb_buffer_pool_size

InnoDB 最重要的缓存，用来缓存 innodb 索引页面、undo 页面及其他辅助数据。一般设定物理内存 50%~75%

Innodb_buffer_pool_instances

通过这个参数可以把整块 buffer pool 分割为多块 instance 内存空间，每个空间独立管理自己的内存和链表，来提升 MySQL 请求处理的并发能力。

因为 buffer pool 是通过链表来管理的，同时为了保护页面，需要在存取的时候对链表加锁，在多线程情况下，并发读写 buffer pool 缓存会有锁竞争和等待。

官方说超过 1G 的 Innodb_buffer_pool_size 考虑设定 instances 去切分内存

Innodb_log_file_size,innodb_log_files_in_group

两个参数决定 redo 空间的大小，设置存储更新 redo 越大，有效降低 buffer pool 脏页被淘汰的速度，减少了 checkpoint 此书，降低磁盘 I /O

不过设置过大，在数据库异常宕机时，恢复时间越长

Innodb_old_blocks_pct,innodb_old_blocks_time

innodb_old_blocks_pct：

全局、动态变量，默认值 37，取值范围为 5~95. 用来确定 LRU 链表中 old sublist 所占比例

innodb_old_blocks_time：

全局、动态变量，默认值 1000，取值范围为 0~2**32-1，单位 ms。

用来控制 old sublist 中 page 的转移策略，新的 page 页在进入 LRU 链表中时，会先插入到 old sublist 的头部，然后 page 需要在 old sublist 中停留 innodb_old_blocks_time 这么久后，下一次对该 page 的访问才会使其移动到 new sublist 的头部，

该参数的设置可以保护 new sublist，尽可能的防止其 being filled by page that is referenced only for a brief period。

 

默认的缓冲中的页在第一次被读取时（也就是命中缓存）会被移动到新页子表头部，意味着其会长期待在缓冲池中不会被淘汰。这样就会存在一个问题，一次表扫描（比如使用 select 查询）可能会将大量数据放入缓存中，并淘汰相应数量的旧数据，但是可能这些数据只使用一次，后面不再使用；同样地，因为 read-ahead 也会在下一次访问该页时被放入新页子表头部。这些情形会将本应会被频繁使用的页移动到旧页子表中。

所以 3 / 8 位置处。在后面的第一次命中（被访问时）的页会被移动到列表的头部。因此，那些读入缓存但是后面从来不会被访问的页也从不会被放入列表的头部，也就会在后面被从缓冲池淘汰。

MySQL 提供了配置参数,milliseconds）读取不会被标识为年轻，也就是不会被移动到列表头部。参数 1000，增大这个参数将会造成更多的页会更快的从缓冲池中被淘汰。

Innodb_flush_method

Innodb 刷数据和日志到磁盘的方式，这个值默认为空，其实：

Linux 默认 fsync

Windows 默认 async_unbuffered

SSD 和 PCIE 存储时可以使用 o_direct 提升性能

Innodb_doublewrite

MySQL 默认每个 page size 是 16k，而 OS 通常最小 I / O 单元是 4k，所以如果写 page 时可能需要调用 4 次 OS I/ O 才能完成。假定在执行两次时 DB crash 了，这时 page 只写了一部分，就产生了 partial write(不完整写)。

MySQL double write 的设定就是为了在发生 partial write 时任然保证已经 commit 的数据不丢失，以及数据文件不损坏。

但如果底层存储支持原子性可以关闭两次写，主要看 OS page size 和 DB page size 的关系。

Innodb_io_capacity

控制后台不断将内存 (dirty data) 数据 flush 硬盘的操作，遇到周期性 IO QPS 下降时可以考虑提高参数的设定，以加速 flush 的频率

参考实验提高 Innodb_io_capacity 的设置，已提升 QPS

Innodb_thread_concurrency

在并发量大的时，增加这个值，儿科降低 innodb 在并发线程之间切换开销，以增加系统的并发吞吐量

innodb_flush_log_at_trx_commit

控制 redo log 刷盘机制

innodb_flush_log_at_trx_commit=0

事务提交时，不会处理 log buffer 的内容，也不会处理 log file 在 OS cache 的刷盘操作，由 MySQL 后台 master 线程每隔 1 秒将 log buffer 刷新到磁盘的 log file 中。

在 MySQL 服务宕掉，服务器正常或宕机时：

由于事务提交不刷新 logbuffer，即使事务提交了，logbuffer 也会全部丢失，但只丢失最近 1 秒的事务

innodb_flush_log_at_trx_commit=1

事务提交时，会将 log buffer 的内容写入 OS cache 文件中，同时会将 OS cache 刷新到磁盘 log file 中。

在 MySQL 服务宕掉，服务器正常或宕机时：

由于事务提交会刷新到磁盘 log file 中，所以数据都不会丢失

innodb_flush_log_at_trx_commit=2

事务提交时，会将 log buffer 的内容写到 OS cache 文件中，由 MySQL 后台 master 线程每隔 1 秒将 OS cache 的 log file 刷新到磁盘。

在 MySQL 服务宕掉，服务器正常：

由于事务已经刷新到 OS cache 中，然而服务器没宕机，这样日志还是会被刷新到磁盘中，那么数据就不会丢失

在 MySQL 服务宕掉，服务器宕机：

由于事务只刷新到 OS cache 中，服务器宕机话，日志没用被刷新到磁盘中，会丢失 1 秒的事务

sync_binlog

控制 binlog 同步到磁盘的方式

sync_binlog=0, 事务提交时将 MySQL Binlog 信息写入 OS cache Binlog 中，由 OS 自己空间其缓存的刷新。如果是服务器宕机 binlog cache 中所有 binlog 都会丢失

sync_binlog=1, 每个事务提交时，MySQL 都会把 Binlog 刷新到物理磁盘中。这样安全性最高，性能损耗是最大。特别是在多事务同行提交，会对 I / O 性能带来很大影响。

但 group commit 可以缓解压力：

binlog_group_commit_sync_delay=N, 默认是 0, 定时执行，在 commit 后等待 N 微秒后，进行 binlog 刷盘操作

binlog_group_commit_sync_no_delay_count=N，在 commit 后等待达到最大事务等待数量 N，就忽视 binlog_group_commit_sync_delay 的设置，直接开始刷盘, 注意如果 binlog_group_commit_sync_delay 设置为 0，则此选项无效

不过 group commit 的设置，可能会影响 commit 执行执行速度，可参考：
https://www.cnblogs.com/ziroro/p/9600359.html

sync_binlog=N, 表示每 N 次事务提交，MySQL 会做刷盘。如果 DB 服务或者服务器宕机会丢失一些事务

注：开启 Binlog 后，MySQL 内部会自动将事务当作一个 XA 事务处理，在提交事务过程中，会自动分配一个唯一的 XID，XID 会记录到 Binlog 和 redo log 中。事务在提交过程会自动份为 Prepare 和 Commit 两个阶段。

Prepare 阶段：告诉 InnoDB 做 prepare，InnoDB 更改事务状态，并将 redo log 刷入磁盘

Commit 阶段：先记录 Binlog，然后告诉 InnoDB commit

binlog_format

binlog_format=STATEMENT

写入执行的 SQL 语句到 binlog，从库读取这些 SQL 并执行

优势：

技术成熟

减少 binlog 的写入量

binlog 包含所有修改语句没便于审计

缺点：

有些函数不能再 slave 上复杂，如 sleep(),last_insert_id(),udf 等会除问题

与基于 row 的复制比，insert…select 需要更多的锁

隔离级别必须是 repeatable-read, 而这是发生死锁的元凶之一

binlog_format=MIXED

默认使用 STATEMENT 记录日志，特定情况下转换成 ROW 记录

binlog_format=ROW

MySQL5.7.7 之后的默认值

优点：

复制是最安全的

slave 需要的锁也最少

缺点：

binlog 会记录更多的数据

无法在 slave 上看到 master 上获取的语句，因为都是 event。但可以开启 binlog_rows_query_log_events 参数，让 binlog 记录 events 同时也记录原始 SQL 语句。

(复制建议使用 row 模式，其它模式有可能出现主从数据不一致)

tx_isolation

MySQL 隔离级别，默认是 repeatable-read

Read Uncommitted

Read Committed

Repeatable Read

Serializable

这四种级别越来越严格，但性能越来越差。

推荐使用 Read Committed，同时 binlog_format=ROW，确认 binlog 同步数据主从库一致性，兼顾安全，满足绝大多数业务。

slave_parallel_workers

MySQL 5.6 中，设置参数 slave_parallel_workers = 4，即可有 4 个 SQL Thread（coordinator 线程）来进行并行复制，其状态为：Waiting for an evant from Coordinator。但是其并行只是基于 database 的。如果数据库实例中存在多个 database，这样设置对于 Slave 复制的速度可以有比较大的提升。

其核心思想是：不同 database 下的表并发提交时的数据不会相互影响，即 slave 节点可以用对 relay log 中不同的 schema 各分配一个类似 SQL 功能的线程，来重放 relay log 中主库已经提交的事务，保持数据与主库一致。

在 MySQL 5.7 中，引入了基于组提交的并行复制（Enhanced Multi-threaded Slaves），

设置 slave_parallel_workers 0 并且 global.slave_parallel_type=‘LOGICAL_CLOCK’，即可支持一个 database 下，slave_parallel_workers 个的 worker 线程并发执行 relay log 中主库提交的事务。

其核心思想：一个组提交的事务都是可以并行回放(配合 binary log group commit);

slave 机器的 relay log 中  last_committed 相同的事务（sequence_num 不同）可以并发执行。

参数 slave_parallel_type 可以有两个值：

DATABASE 默认值，基于库的并行复制方式

LOGICAL_CLOCK：基于组提交的并行复制方式

关于 MySQL 性能相关参数有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。