目录

一、参数读取顺序

二、参数类型

三、参数详解

四、参考资料

一、参数读取顺序

查询出来具有顺序性。以下查询可以看出，MySQL是按/etc/my.cnf → /etc/mysql/my.cnf → /usr/etc/my.cnf → ~/.my.cnf 顺序读取。

[root@micro-service-b-1 ~]$ which mysql
/bin/mysql
[root@micro-service-b-1 ~]$ /bin/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/etc/my.cnf ~/.my.cnf 
[root@micro-service-b-1 ~]$

多个配置文件配置相同参数，以最后的读取为准。同时，MySQL数据库可以没有配置文件的情况下，则按照MySQL编译时默认参数和源代码指定参数的默认值来启动MySQL实例。

二、参数类型

        MySQL数据库的参数以键值对（key/value）的形式配置。通过show variables命令查询所有的参数配置，也可以通过like来过滤参数名。如下所示，查询Innodb存储引擎处理io的线程配置。

mysql> show variables like 'innodb_%io_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
        Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
        Value: 4
2 rows in set (0.01 sec)

        MySQL的参数类型分为两类：

  • 动态(dynamic)参数：MySQL实例运行中，可以修改的参数
  • 静态(static)参数：整个MySQL实例生命周期不能修改（只读），启动实例前可以修改
mysql> set global datadir='/db/mysql';
ERROR 1238 (HY000): Variable 'datadir' is a read only variable

实例本身不会对参数文件中的值进行修改。

三、参数详解

        以下参数，通过show variables或show variables like查询。

类型

名称

描述

错误日志

log_error

1. 不仅记录所有的错误信息，也记录警告和正常信息；

2. 默认路径数据目录下.err结尾的文件

查询日志

general_log

1. 是否开启查询日志文件（记录所有对MySQL请求信息）；

2. 默认关闭OFF，原因是日志量非常庞大

general_log_file

1. 查询日志文件路径；

2. 默认./（数据库数据目录，即：data目录）

慢查询日志

slow_query_log

1. 是否开启慢查询日志；

2. 默认关闭OFF，开启ON

long_query_time

1. 大于时间阈值时则记录；

2. 默认10s（>10s时记录）

slow_query_log_file

1. 慢查询日志文件路径；

2. 默认./（数据库数据目录，即：data目录）

log_output

1. 慢查询/通用查询日志输出格式；

2. 默认FILE；若TABLE时，则mysql.slow_log表

log_queries_not_using

_indexes

1. SQL没有使用索引时，是否记录到慢查询日志；

2. 默认关闭OFF，ON开启

log_throttle_queries_not

_using_indexes

1. SQL没有使用索引时，每分钟记录到慢查询日志中的个数；

2. 默认0，即：没有限制

二进制日志

log-bin

1. 是否开启二进制日志文件，log-bin=[name]；

2. 默认路径是datadir指定，文件名称name指定

max_binlog_size

1. 单个二进制日志文件的最大值，默认1073741824= 1G；

2. 超过该值后，则生成一个新文件，后缀名+1，并记录到index

binlog_cache_size

1. 事务开始时，所有未提交的二进制日志记录到一个缓存中，等待事务提交时，直接将缓存写入二进制日志文件；

2. 当线程开启一个事务时，会自动创建一个缓存；

3. 默认缓存大小32768 = 32KB，超出时，则写入临时文件

sync_binlog

1. sync_binlog=[N]，表示每写缓冲N次就触发同步到磁盘；

2. sync_binlog=1表示同步写磁盘，即：不使用缓存直接写入磁盘

innodb_support_xa

1. 是否支持XA事务，默认开启ON；

2. sync_binlog=1时，事务已提交，而写磁盘时宕机，通过该参数完成二进制日志与数据文件的同步

binlog_format

1. 二进制日志文件输出格式，默认ROW；

2. 格式类型：Statement（内容SQL语句，但是Mysql函数不会写入），Row（更新的记录），Mixed（SQL+Row）

表空间

innodb_data_file_path

1. 共享表空间的路径，默认数据目录下的ibdata1；

2. 多文件组成一个表空间；

3. 共享表空间存放所有表数据

innodb_file_per_table

1. 是否开启为每个表建立独立表空间；

2. 默认开启ON，表空间为：表名.ibd；

3. 独立表空间存储：表数据、索引、插入缓冲的BITMAP信息，其他存储在默认共享表空间。

重做日志

innodb_log_file_size

1. 每个重做日志文件的大小；

2. 不能大于512G

innodb_log_files_in_group

1. 重做日志文件组中的文件数量；

2. 默认2，数据目录下的：ib_logfile0、ib_logfile1

innodb_log_group_home

_dir

1. 重做日志文件所在的路径；

2. 默认./，即：数据目录下

innodb_log_buffer_size

重做日志缓冲大小，默认8MB

innodb_flush_log_at_trx

_commit

1. 控制事务提交时处理重做日志方式；

2. 默认1：事务提交时，redo log buffer写入磁盘、

           0：等待master thread每秒刷新到磁盘、

           2：异步写入磁盘，注意事务提交时不能确保写入重做日志中

   注意：0、2导致事务丢失

PID文件

pid_file

MySQL实例的进程ID文件，默认路径数据目录

临时目录

tmpdir

1. 临时文件存放路径，如：存放临时表、socket文件等；

2. 默认/tmp

套接字文件

socket

1. 连接MySQL实例使用套接字方式；

2. 默认路径：/tmp目录

InnoDB版本

innodb_version

查看InnoDB版本，也可以使用select version()语句

读写线程

innodb_read_io_threads

配置InnoDB从磁盘读取数据的线程个数，默认4

innodb_write_io_threads

配置InnoDB从缓冲写入磁盘的线程个数，默认4

Purge线程

innodb_purge_threads

1. 配置线程个数回收事务提交后的undolog页；

2. 默认线程数4

Buffer Pool

innodb_buffer_pool_size

1. 设置缓冲池大小，影响着MySQL整体性能；

2. 默认值128MB

innodb_buffer_pool_instances

缓冲池实例个数，默认1

innodb_buffer_pool_filename

1. 设置缓冲池名；

2. 默认ib_buffer_pool，在数据目录下

LRU算法

innodb_old_blocks_pct

1. 新读取的页插入LRU列表中的位置（midpoint）；

2. 默认37%，该位置的前部是new部分，后部是old部分

innodb_old_blocks_time

1. 在midpoint等待多久移到到LRU列表的前端；

2. 默认1000ms=1s；

3. page made young：该时间过后，移动到LRU首端；

    page not made young：没有移动到LRU首端

Checkpoint


innodb_fast_shutdown

1. MySQL关闭时，所有脏页刷新到磁盘；

2. 默认1，表示开启；

3. 属于Sharp Checkpoint

innodb_lru_scan_depth

1. LRU列表可用页的数量，默认1024页；

2. 少于该值时，触发Checkpoint；

3. 属于Fuzzy Checkpoint

innodb_max_dirty_pages

_pct

1. 脏页占缓冲池的百分比，默认75%；

2. 占比超出该值后，触发Checkpoint；

3. 属于Fuzzy Checkpoint

Change Buffer

innodb_change_buffering

1. 开启INSERT/DELETE/UPDATE的各种缓冲；

2. 默认all，开启以上三种缓冲

innodb_change_buffer

_max_size

1. Change Buffer占缓冲池的百分比，默认25%；

2. 该参数最大值50%，即：占缓冲池的一半

Double Write

Innodb_dblwr_pages_written

写的页数，使用show global status查询

Innodb_dblwr_writes

1. 写入磁盘的次数，使用show global status查询；

2. 页数 : 次数远小于64:1，说明系统写入压力不高

Native AIO

innodb_use_native_aio

1. 是否开启Native AIO，即：使用内核的AIO；

2. 默认ON启用

刷新邻接页

innodb_flush_neighbors

1. 是否开启刷新邻接页，即：刷新页时，该页所在的区（extent）的所有脏页是否刷新到磁盘；

2. 默认开启1，关闭0

MySQL关闭

innodb_fast_shutdown

1. MySQL关闭时的操作，默认1；

2. 值：1：无需完成所有的purge和merge insert buffer，缓冲池的脏页刷新即可

            0：完成所有的purge和merge insert buffer

            2：没有完成1和2，需要日志写入日志文件

MySQL启动

innodb_force_recovery

MySQL启动时的操作，默认0（进行所有的恢复）

数据页

完整性检查

innodb_checksums

1. 是否开启页的完整性检查；

2. 默认开启ON（即：每次从磁盘读取页的时候，检查页的完整性）

innodb_checksum

_algorithm

页的完整性检查的checksum函数算法，默认crc32

行格式

innodb_file_format

1. 查询文件格式；

2. 当前值为Barracuda，包括：Antelope（老版本行记录格式：Compact/Redudant） + Dynamic + Compressed

innodb_file_format

_check

1. 是否开启检查InnoDB对file_format的支持；

2. 默认开启ON（检查是否支持）

SQL入参审查

sql_mode

1. 审查sql的输入参数，可多值；

2. 默认：ONLY_FULL_GROUP_BY,

               STRICT_TRANS_TABLES,

               NO_ZERO_IN_DATE,

               NO_ZERO_DATE,

               ERROR_FOR_DIVISION_BY_ZERO,

               NO_AUTO_CREATE_USER,

               NO_ENGINE_SUBSTITUTION

分区支持

show plugins

1. 查询MySQL是否支持分区；

2. 使用该命令，查看：Name: partition

                                    Status: ACTIVE

外键检查

foreign_key_checks

1. 是否开启外键即时检查；

2. 默认开启ON，即：子表插入数据时检查外键；

3. 子表大批量插入数据时，可以暂时关闭外键检查

索引算法

old_alter_table

1. 创建或删除索引的算法；

2. 默认关闭OFF（采用INPLACE）；

3. INPLACE：无需创建临时表，采用Online DDL；

    COPY：创建临时表

Online DDL

缓存

innodb_online_alter

_log_max_size

1. Online DDL时，DML操作日志的缓存大小；

2. 默认128MB

Cardinality值

采样


innodb_stats_sample

_pages

Cardinality的每次采样统计的页数，默认8

innodb_stats_method

1. Cardinality采样统计对Null的处理；

2. 默认nulls_equal（NULL值作为相同记录）、   

           nulls_unequal（NULL值作为不同记录）、

           nulls_ignored（忽略NULL）

innodb_stats_persistent

1. analyze table触发Cardinality统计时，其值是否存入磁盘；

2. 默认ON（减少重新计算每个索引的Cardinality值）

innodb_stats_on

_metadata

1. show table status、show index、访问information_schema下的TABLES和STATISTICS时，是否开启Cardinality统计；

2. 默认OFF

innodb_stats_persistent

_sample_pages

1. 参数innodb_stats_persistent为ON时，使用analyze table每次采样统计的页数；

2. 默认20

innodb_stats_transient

_sample_pages

代替innodb_stats_sample_pages参数，默认8

优化器

optimizer_switch

1. 优化器开关，多值；

2. 如：可启用MRR（Multi-Range Read）：mrr为on（默认）表示开启，mrr_cost_based为on（默认）表示是否通过cost based方式来启用MRR；为off时，表示总是启用MRR

如：可启用ICP（Index Condition Pushdown）：index_condition_pushdown为on（默认）表示开启

read_rnd_buffer_size

MRR的键值对的缓冲区大小，默认256KB

自适应

Hash索引

innodb_adaptive_hash

_index

开启自适应哈希索引，默认开启ON

全文检索

索引


innodb_ft_aux_table

1. 哪些表通过INNODB_FT_INDEX_TABLE表来查询表的倒排索引的辅助表（Auxiliary Table）；

2. 默认空

innodb_ft_cache_size

全文检索索引缓存的大小设置，默认32MB

innodb_ft_server_stopword

_table

1. 用户自定义stopword（忽略的全文检索词）的表；

2. 默认空

innodb_ft_min_token_size

1. 全文检索查询字段的最小长度；

2. 默认3，若少于该值时，则忽略该查询

innodb_ft_max_token_size

1. 全文检索查询字段的最大长度；

2. 默认83，若大于该值时，则忽略该查询

自增长

innodb_autoinc_lock_mode

1. 自增长模式；

2. 默认1（simple inserts则使用互斥量对内存中的计数器累加操作；bulk inserts则为表锁的AUTO-INC locking）、

           0（表锁）、

           2（insert-like则表锁，其他为非表锁）

关闭

Gap Lock

innodb_locks_unsafe

_for_binlog

1. 是否关闭Gap Lock；

2. 默认OFF，即0（启用Gap Lock）、

                         1（关闭Gap Lock）

阻塞事务


innodb_lock_wait_timeout

阻塞事务的等待时间

innodb_rollback_on_timeout

1. 阻塞事务超时后，是否进行回滚；

2. 默认OFF不回滚（静态参数）

undo log


innodb_undo_directory

1. undo日志rollback segment所在的路径；

2. 默认./（ibdata1）

innodb_undo_logs

1. undo日志rollback segment的个数；

2. 默认128

innodb_undo_tablespaces)

1. undo日志空间数量（构成rollback segment的文件数）；

2. 默认0

purge操作


innodb_purge_batch_size

1. 每次purge操作需要清理的undo page的数量；

2. 默认300

innodb_max_purge_lag

1. 控制history list（事务提交的顺序列表）的最大长度；

2. 默认0（不对history list做任何限制）,

   若大于0时，则每次DML操作时，更新行有延迟

innodb_max_purge_lag

_delay

更新行，最大延迟的毫秒数，默认0

group commit

binlog_max_flush_queue

_time

1. 组提交（group commit）时的flush阶段中等待时间；

2. 默认0（不等待）

事务提交后的行为

completion_type

1. 控制事务提交后的行为；

2. 默认0（NO_CHAIN没有任何操作，即：commit=commit work）、

           1（commit work等同于commit and chain，链式事务：事务提交后，自动开启相同隔离级别的事务）、

           2（自动断开与服务器的连接）

四、参考资料

MySQL :: MySQL 5.7 Reference Manual :: 1.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7