本文主要基于MySQL 5.7版本的数据库环境，总结my.cnf文件中核心参数的配置使用，让更多的人对MySQL技术体系有更全面、更专业的深度了解。

一、客户端核心参数

1、port

    端口号，默认3306

2、socket

    Socket文件地址，默认以.sock为文件名称后缀，用于UNIX套接字通信。

二、服务端全局区(SGA)缓冲区参数

1、innodb_buffer_pool

• InnoDB缓冲池，位于主内存

• 缓存被访问过的表数据、索引文件、插入缓冲、数据字典等

• 推荐大小设置为物理内存的50%-80%

2、innodb_buffer_pool_instance

• 将缓冲池进一步划分为互相隔离的内存区域，更好的支持并发读写，减少内存争用现象

• 默认值为1

• 仅当innodb_buffer_pool参数配置大于1024MB有效

• 通过命令show engine innodb status查看每个内存区域的内存使用情况

3、innodb_old_blocks_time和innodb_old_blocks_pct

• InnoDB缓冲池innodb_buffer_pool，内部由LRU链表管理

• LRU链表进一步分为old pages list和young pages list：

  old pages list：存放长时间未被访问的数据页

  young pages list：存放最新、最近被访问的数据页

• 当超过innodb_old_blocks_time参数设置的时间时，就会移动到old pages list，默认是1000ms。

• innodb_old_blocks_pct参数决定old pages list在LRU链表及innodb_buffer_pool中占比：

  存在大表扫描或者执行mysqldump备份操作时，可能会剔除热数据，增加I/O压力，因此要适当减少innodb_old_blocks_pct参数值

4、innodb_log_buffer_size和innodb_log_waits

• innodb_log_buffer_size：日志缓冲区的大小，取值范围16MB-64MB

• innodb_log_waits：等待日志缓冲刷出的次数

• 通过命令show global status like “%innodb_log_waits%”查看：

  如果发现innodb_log_waits值大于0，并且持续增长，可以考虑增大innodb_log_buffer_size

5、innodb_log_buffer

• 事务在内存中的缓冲区的大小，即redo log buffer

6、query_cache_size和query_cache_type

• query_cache_siz静态数据的缓存区大小

• 默认关闭，建议关闭，保证数据库的TPS

• 数据仓库场景可能会选择开启

• 实例启动前，设置query_cache_size=0和query_cache_type=off才算真正关闭

三、服务端程序缓冲区(PGA)参数

1、sort_buffer_size

• 用于SQL语句在内存中的临时排序

2、join_buffer_size

• 表连接使用，用于BKA

3、read_buffer_size

• MyISAM存储引擎的表顺序扫描的缓存

4、read_rnd_buffer_size

• 随机读缓冲区大小

• 用于mrr特性

5、tmp_table_size和max_heap_table_size

• tmp_table_size：SQL语句在排序或者分组时没有用到索引，会使用临时表空间

• max_heap_table_size：管理heap、memory存储引擎表

• 建议tmp_table_size和max_heap_table_siz大小配置一致

三、服务端刷盘参数

        内存中的数据需要根据刷新机制决定在某一时刻如何刷新到磁盘，刷新机制涉及redo log buffer、data buffer、binlog cache等内存缓冲区，read thread、write thread、redo log thread、change buffer thread四大I/O线程，及master thread后台主线程。

1、innodb_flush_log_at_trx_commit

• 控制由内存redo log buffer写入磁盘relo log、和执行刷盘操作的频率

• 可选参数值(实践根据业务场景选择)：

    0:redo log thread每隔1s会执行一次刷盘策略；

               此种情况，每次事务提交不会触发redo log thread将日志缓冲中的数据写入redo log；

               性能最好，但是安全性最低，MySQL进程崩溃将会导致丢失1s的数据；

            1:每次事务提交时，都会触发redo log thread将日志缓冲中的数据写入redo log，并且执行刷盘操作，以便保证数据确实已经写入磁盘；

               安全性最高，但是性能最差；

            2:每次事务提交时，都会触发redo log thread将日志缓冲中的数据写入redo log，但是不会同时执行刷盘操作；

• 另外两个决定redo log buffer刷到磁盘的条件：

            master thread每秒进行刷新；

            redo log buffer使用超过一半时触发刷新；

2、sync_binlog

• 控制由内存binlog cache刷新到磁盘binglog的频率

• 参数值为正整数：

           0:每次事务提交后，不主动做fsync之类的磁盘同步指令刷新binlog cache，让FileSystem自行决定同步时机，或者binlog cache满才同步到磁盘；

           1:每次事务提交后，主动做刷新binlog cache到磁盘；

           n:每进行n次事务提交后，主动做fsync之类的磁盘同步指令刷新binlog cache到磁盘；

3、数据库的双一模式：

• innodb_flush_log_at_trx_commit = 1

• sync_binlog = 1

4、innodb_max_dirty_pages_pct

• 控制脏页的刷新频率，在buffer pool中dirty page所占的百分比，达到阈值会触发page cleaner thrad执行脏页刷新

• 默认值是75%

• 建议设置为25%-50%之间，保证数据库整体的TPS

5、innodb_flush_method

• 控制InnoDB数据文件、redo log文件的打开刷写模式；

• 可选参数值：

        O_SYNC:

        O_DSYNC:

        O_DIRECT:数据文件直接从MySQL InnoDB Buffer写入到磁盘，不用通过OS buffer；

四、服务端并发量参数

1、innodb_max_thread_concurrency

• InnoDB内核最大并发线程数，默认值为0(含义不受限制)

2、innodb_open_files

• InnoDB可同时打开的.ibd文件个数

• 参数值规则>=10，默认值300

• 建议调整为65535，提高并发数

3、max_connections和innodb_thread_concurrency

• max_connections控制MySQL数据库最大的连接数，默认值151，高并发场景中经常遇到的”too many connections”异常报错，即为此参数配置过低或者集群需要水平扩容

• innodb_thread_concurrency控制InnoDB内部并发数，参数值为正整数(默认值为0)：

           0:不受限制；

           n:并发数为n，建议设置为服务器逻辑CPU核数的2倍，根据实际情况由小及大逐步调整验证；

五、服务端事务参数

1、transaction_isolation

• 事务隔离级别

• 可选参数值(默认值REPEATABLE-READ)：

        READ-UNCOMMITTED：读未提交

        READ-COMMITTED：读已提交

        REPEATABLE-READ：可重复读

        SERIALIZABLE：序列化

六、服务端影响I/O性能的参数

1、innodb_log_file_size

• redo log日志的大小，需要合理设置：

            如果设置偏小，将会导致redo log切换频繁，产生无用的I/O消耗，影响数据库性能；

            如果设置偏大，将会导致实例宕机恢复需要较长时间；

2、innodb_log_files_in_group

• redo log文件组中的日志文件的数量，默认情况下至少需要2个。

3、innodb_stats_on_metadata

• information_schema中的表，某些监控程序读取innodb_stats_on_metadata表

• 可通过命令”set global innodb_stats_on_metadata=0″关闭

七、服务端慢查询参数

1、show_query_log

• 慢查询日志开关

• 可选参数值：

            1:开启状态，推荐配置；

2、long_query_time

• 慢查询的时间阈值，单位为秒；

• 超过阈值就会记录到慢查询日志文件；

3、log_queries_not_using_indexs

• 运行的SQL没有使用索引，是否记录到慢查询日志文件

• 可选参数值：

        on：记录，推荐配置，set global set log_queries_not_using_indexs=on；

        off：不记录；

八、服务端二进制文件参数

1、expire_logs_days

• binlog文件的过期时间，单位为天

2、binlog_format

• 二进制日志文件的内容格式

• 可选参数值：

        statement：记录的是一条SQL语句

        row：此模式较为安全，不会出现跨库复制丢失数据的情况；

                    一般中间件采集数据解析也是这种模式，记录行变更前的样子及变更后的内容

        mixed：混合模式

九、服务端其它参数

1、innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown

• 快速加载实例宕机前内存中的热数据，减少磁盘I/O压力

• 可选参数值(默认0)：

  0：关闭状态

  1：启用状态

• 在实例关闭时，将热数据的元数据信息保存到innodb_buffer_pool_filename参数指定的文件(默认ib_buffer_pool)

• 在实例启动时，从innodb_buffer_pool_filenam参数指定的文件(默认ib_buffer_pool)快速加载回内存

2、innodb_data_file_path

• 指定系统表空间文件的路径和ibdata1文件的大小

• 默认大小是10MB

3、interactive_timeout和wait_timeout

• interactive_timeout是服务器关闭交互式连接前等待活动的时间，默认值28800s，即8小时

• iwait_timeout是服务器关闭非交互式连接前等待活动的时间，默认值28800s，即8小时

• 两个参数需要一起调整，并且保持一致，默认值时间过长，建议调整为300s-600s之间

4、server-id

• 主从环境中，当前MySQL在主从结构中的唯一标识

5、lower_case_table_names

• 表名称是否区分大小写

• 可选参数值：

        0:区分大小写

        1:不区分大小写，实际存储使用小写；

6、innodb_fast_shutdown和innodb_force_recoverty

• innodb_fast_shutdown控制表的InnoDB存储引擎在关闭时的行为

• 可选参数值：

        0：默认值；

                需要执行purge all、merge change buffer、flush dirty pages操作；

                此方式是最慢的关闭方式，但是restart时最快；

        1：不需要执行purge all、merge change buffer等操作，但是需要执行flush dirty pages操作；

        2：不完成purge all、merge change buffer等操作，也不刷新脏页到磁盘；

                只是将日志写入日志文件，因此不会丢数据，但是重新启动会进行recovery操作；

7、innodb_force_recoverty

• 控制表的InnoDB存储引擎在恢复时的行为

• 可选参数值：

        0:默认值，当需要恢复时执行所有操作；

        1:忽略检查到的corrupt页；

        2:阻止主线程的运行，

        3:不执行事务回滚操作；

        4:不执行插入缓冲的合并操作；

        5:不查看撤销日志，InnoDB存储引擎会将未提交的事务视为已提交；

        6:不执行前滚操作；

7、innodb_status_output和innodb_status_output_locks

• 数据库监控信息记录到error log

• 建议关闭，防止错误日志增长过快，造成磁盘空间使用紧张

8、innodb_io_capacity

• InnoDB后台进程最大的I/O性能指标，影响刷新脏页和插入缓冲的数量

• 默认值为200，可根据实际使用的磁盘类型选择调整

9、auto_increment_increment和auto_increment_offset

• auto_increment_increment表示自增长字段每次递增的量，默认值为1

• auto_increment_offset表示自增长字段从哪个值开始，默认值为1

欢迎订阅个人公众号，浏览更多MySQL技术体系知识。