配置参数

back_log=1024

  • 可以在堆栈中的连接数量
  • MySQL每处理一个连接请求时都会创建一个新线程与之对应。在主线程创建新线程期间，如果前端应用有大量的短连接请求到达数据库，MySQL会限制这些新的连接进入请求队列，由参数back_log控制。如果等待的连接数量超过back_log的值，则将不会接受新的连接请求，所以如果需要MySQL能够处理大量的短连接，需要提高此参数的大小。
  • 如果参数过小，应用可能出现如下错误：
    • SQLSTATE[HY000] [2002] Connection timed out;

binlog_cache_size

  • 事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中
  • 系统给 binlog cache 分配了一片内存，每个线程一个，大小由 binlog_cache_size 控制，超过就会暂存到磁盘

eq_range_index_dive_limit

  • 当条件数N小于 eq_range_index_dive_limit 时，优化器认为此时条件个数尚可，可以采用成本较高但更为精确的 index dive 方式来计算执行成本；当N大于或等于 eq_range_index_dive_limit 时，优化器会认为此时使用 index dive 的方式计算成本带来的开销过大，此时MySQL优化器会根据 index statistics 直接估算成本。
  • 大部分情况下，where 条件中使用的索引列的选择性都还是不错的，使用 index statistic 直接估算返回行数并不会有太大偏差，并且能够避免 index dive 带来的开销，在 IN 条件较多的情况下，能快速找到正确的执行计划，提升系统性能。然而，不均匀分布的索引也不罕见，这种情况下，eq_range_index_dive_limit 可能会显着影响查询执行计划。
  • mysql5.7 默认为 200

join_buffer_size

  • 联合查询操作所能使用的缓冲区大小。
  • join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100

innodb_sort_buffer_size

  • 创建索引时，由于聚合排序的内存区域的大小

innodb_flush_log_at_trx_commit=1

  • 提交事务的时候将 redo 日志写入磁盘中
    • innodb_flush_log_at_trx_commit=0：提交事务的时候，只是将日志写入 redo log buffer中(MySQL 异常重启时就会丢失数据)
      • 此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
    • innodb_flush_log_at_trx_commit=1：提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。
    • innodb_flush_log_at_trx_commit=2：提交事务的时候，日志写入磁盘os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

innodb_lock_wait_timeout=10

  • Innodb 锁等待超时时间，如果出现事务锁，超说超时时间后会自动回复

innodb_deadlock_detect=on(默认开启)

  • 主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的

innodb_file_per_table = 1

  • 5.6.6后，默认为 ON
  • 每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中

innodb_io_capacity/innodb_io_capacity_max

  • innodb_io_capacity_max 设置为磁盘最大 IOPS 数
  • innodb_io_capacit 设置为最大 IOPS 的50%~70%
  • 说明：
    • 限制写入脏页的速度
    • 如果过小，写入脏页数据过慢，会导致刷新脏页 io 占用时间过长，影响其他 io 使用
    • 如果过大，会导致磁盘 io 负荷过大，影响其他进程的写操作

innodb_flush_neighbors

  • 5.7 默认为 1 ；8.0 默认为 0
  • 在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
  • innodb_flush_neighbors 为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
  • 使用机械磁盘时，可以减少随机 IO，SSD 这类IOPS较高的设备，建议设为 0 ，减少 sql 语句执行时间

innodb_thread_concurrency=0

  • 限制并发线程的执行数

innodb_use_native_aio

  • 是否开启异步IO(AIO)

innodb_stats_persistent

  • 统计信息是否开启持久化统计
  • 启用持久化统计信息，修改超过 10% 数据就要更新
  • 动态自动统计，修改 1/16 数据就要更新
    • 统计信息不持久化，每次动态采集，存储在内存中，重启失效（需重新统计），不推荐
    • innodb_stats_transient_sample_pages：动态采集page，默认8个
    • 每个表设定统计模式
      • CREATE/ALTER TABLE …
      • TATS_PERSISTENT=1,STATS_AOTU_RECALC=1,STATS_SAMPLE_PAGES=200;
    • mysql -auto-rehash
  • innodb_stats_method 控制统计信息针对索引中 NULL 值的算法当设置为 nulls_equal 所有的 NULL 值都视为一个 value group；当设置为 nulls_unequal 每一个 NULL 值被视为一个 value group ；设置为nulls_ignore 时，NULL 值被忽略
  • 执行 show table status、show index，访问 I_S.TABLES/STATISTICS 视图时更新统计信息

log_bin_trust_function_creators

  • 当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制
  • 创建存储过程时，如果出现 “This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de”，可通过更改参数为 TRUE，配置存储过程

log_slave_updates = on

  • 备库执行 relay log 后生成 binlog

long_query_time=0.5

  • 记录慢sql的时间阈值

log_slow_admin_statements=0

  • 启用该参数后后会将 ALTER TABLE， ANALYZE TABLE， CHECK TABLE， CREATE INDEX，DROP INDEX， OPTIMIZE TABLE，和 REPAIR TABLE语句也记录至慢查询日志

log_slow_slave_statements=0

  • 主从复制时候不会将复制查询写入慢查询日志

max_heap_table_size

  • max_heap_table_size MEMORY内存引擎的表大小
  • 除非使用诸如CREATE TABLE之类的语句重新创建该表或使用ALTER TABLE或TRUNCATE TABLE更改该表，否则设置此变量对任何现有的MEMORY表均无效。
  • 服务器重新启动还会将现有MEMORY表的最大大小设置为全局max_heap_table_size值。
  • 可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数(Created_tmp_disk_tables和Created_tmp_tables),来判断参数是否合理,一般的比例关系是: Created_tmp_disk_tables/Created_tmp_tables<5%
  • 与 tmp_table_size 一起使用，可限制临时表大小

max_length_for_sort_data=1024

  • 无法避免 filesort 时，MySQL 会有两种排序算法
    • 两次扫描算法:两次访问数据，第一步获取排序字段的行指针信息，在内存中排序，第二步根据行指针获取记录
    • 一次扫描算法:一次性取出满足条件的所有记录，在排序中排序后输出结果集。是采用空间换时间的方法。
  • 当此排序行小于此参数时，会选择一次扫描算法

query_cache_type = 0

  • 是否开启查询缓存(MySQL 8.0 已删除此功能)
  • 对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定
1

mysql> select SQL_CACHE * from T where ID=10；
  • 大多数情况下不建议用查询缓存，因为查询缓存往往弊大于利.数据更新时，会导致缓存重新刷新。

read_only = 1

  • 限制普通用户只读
  • 从库防止意外执行 sql 影响主从状态时使用

super_read_only = 1

  • 限制超级用户只读
  • 从库防止意外执行 sql 影响主从状态时使用

sync_binlog=1

  • 控制 binlog 日志写入磁盘过程
    • sync_binlog=0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。
    • sync_binlog=1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作
    • sync_binlog=N：每写N次操作系统缓冲就执行一次刷新操作

slow_query_log=1

  • 是否开启慢sql日志

slow_query_log_file

  • 慢sql日志路径

sort_buffer_size

  • MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序
  • On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation
  • 此参数为线程参数，过大会会有 OOM 风险

slave_parallel_workers

  • 默认为 0
  • 非 0 参数开启多线程复制，在 32 核机器上 推荐设为 8-16

table_open_cache=2048

  • 表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
  • 阿里推荐配置:{LEAST(DBInstanceClassMemory/1073741824*256, 2048)}
  • 部分推荐配置为 长连接数 * 3

thread_cache_size=2000

  • 缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。

tmp_table_size

  • 内存临时表的大小
  • 当 GROUP BY 过多时，可考虑调大这个参数
  • 实际上由 tmp_table_size 和 max_heap_table_size 中较小的值决定

expire_logs_days

  • MySQL binlog 保存的天数，指定天数前的日志会被自动删除