1.分析步骤 默认该功能是关闭的，使用前需提前开启Show Profile功能。

  2.查询语句 select *from tb_emp_bigdata group by id%10 limit 150000; select *from tb_emp_bigdata group by id%20 order by 5; 3.通过 Show Profiles查看结果

  4.使用 Show Profile对sql语句进行诊断 show profile cpu,block io for query Query_ID;/*Query_ID为#3步骤中show profiles列表中的Query_ID*/ 比如执行：show profile cpu,block io for query 15;

5.Show Profile的常用查询参数 ①ALL：显示所有的开销信息。

②BLOCK IO：显示块IO开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示CPU开销信息。

⑤IPC：显示发送和接收开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨SWAPS：显示交换次数开销信息。

6.日常开发需注意的结论 ①Converting  HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。

②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！

④locked。

如果在Show Profile诊断结果中出现了以上4条结果中的任何一条，则SQL语句需要优化。

7.注意 1.Show Profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。也可修改MYSQL配置文件来修改。

2.通过Show Profiles查看sql语句的耗时时间，然后通过Show Profile命令对耗时时间长的sql语句进行诊断。

3.注意Show Profile诊断结果中出现相关字段的含义，判断是否需要优化SQL语句。  

#Max_connections 1）参数优化 Max_connections ***** Mysql的最大连接数，如果服务器并发请求量比较大，可以调高这个值（要建立在机器可以支撑的情况下，因为连接数越来越多，mysql会为每个连接提供缓冲区，就会消耗更多内存，所以不能随便的调高该值

最大连接数 show variables like ‘max_connections’; 响应的连接数 show status like ‘max_used_connections’;

查看响应的连接数： max_used_connections / max_connections * 100% （理想值≈ 85%） 如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。 max_used_connections数量就是当前连接数量。 MySQL的max_connections参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接，max_connections的默认值为151。 与max_connections有关的特性 MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1； 这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准； 增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；

Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数

mysql> show variables like ‘max_connections’; mysql> show status like ‘Max_used_connections’;

Vim /etc/my.cnf max_connections=1024

back_log 查看mysql 当前系统默认back_log值，命令：show variables like ‘back_log’; 判断依据：show full processlist Vim /etc/my.cnf Back_log=xxxx back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报： unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时. back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令： cat /proc/sys/net/ipv4/tcp_max_syn_backlog，目前系统为1024。对于Linux系统推荐设置为大于512的整数。

修改系统内核参数，可以编辑/etc/sysctl.conf去调整它。如：net.ipv4.tcp_max_syn_backlog = 2048，改完后执行sysctl -p 让修改立即生效。

wait_timeout和interactive_timeout wait_timeout – 指的是mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_time – 指的是mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysql gui tool中的连接)

wait_timeout：对性能的影响

（1）如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用

（2）如果设置太大，容易造成连接打开时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误

（3）一般希望wait_timeout尽可能地低,(wait_timeout=120,interactive_timeout=1200) 说明：长连接的应用，为了不反复的分配和回收资源，降低额外开销，一般我们会将wait_timeout设定的比较小，interactive_timeout要和应用开发人员沟通长连接的应用是否很多，如果他们需要长连接，那么这个值不用修改 interactive_timeout的设置将要对你的web application没有多大的影响

key_buffer_size mysql> show variables like “key_buffer_size%”; 1)myisam表的索引缓冲区 2）临时表缓冲区 mysql> show status like “created_tmp%”; created_tmp_tables/(created_tmp_disk_tables + created_tmp_tables) 越高越好 create_tmp_disk_tables/(created_tmp_disk_tables + created_tmp_tables) 控制在5% ~ 10%之间 计算基于内存的临时表的利用率，我们要关注creted_tmp_disk_tables是否过多，从而认定当前服务器运行的状态

Mysqldump可以产生大量临时表，导致利用率上升，但是可以忽略

sort_buffer_size (session独享) 1 Sort_Buffer_Size 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。 2 Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。 3 文档说“On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation” 据说Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。

max_allowed_packet mysql> show VARIABLES like ‘%max_allowed_packet%’; mysql根据配置文件会限制server接受的数据包大小。 有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败,必须设置为1024的倍数

join_buffer_size(session独享) Join Buffer 可被用于联接是ALL、index、和range的类型;每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer；Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放；Join Buffer只存储要进行查询操作的相关列数据，而不是整行的记录。

innodb_log_file_size MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间（一个环形的数据结构）。Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节。 为InnoDB引擎设置合适的Redo log空间对于写敏感的工作负载来说是非常重要的。然而，这项工作是要做出权衡的。你配置的Redo空间越大，InnoDB就能更好的优化写操作；然而，增大Redo空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。 关于恢复时间，并不好预测对于一个指定的 innodb_log_file_size 值出现崩溃是需要多长的恢复时间–他取决于硬件能力、MySQL版本以及工作负载等因素。然而，一般情况下我们可以按照每1GB的Redo log的恢复时间大约在5分钟左右来估算。如果恢复时间对于你的使用环境来说很重要，我建议你做一些模拟测试，在正常工作负载下（预热完毕后）模拟系统崩溃，来评估更准确的恢复时间。 虽然恢复时间可以作为一个限制innodb_log_file_size的参考因素，也还有一些别的方式可以观察该参数设置是否“合理”（尤其是如果你安装了PMM： Percona Monitoring and Management）

read_buffer_size，read_rnd_buffer_size，bulk_insert_buffer_size bulk_insert_buffer_size = n 为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)。

key_buffer_size = n 用来存放索引区块的RMA值(默认设置是8M)。

join_buffer_size = n 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。

max_heap_table_size = n HEAP数据表的最大长度(默认设置是16M); 超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里。

max_connections = n MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。

query_cache_limit = n 允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是1M)。

query_cache_size = n 查询缓存区的最大长度(默认设置是0，不开辟查询缓存区)。

query_cache_type = 0/1/2

查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，”按需分配”模式，只响应SELECT SQL_CACHE命令。

read_buffer_size = n

为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。

read_rnd_buffer_size = n 类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。

sort_buffer = n 为排序操作分配的缓存区的长度(默认设置是2M); 如果这个缓存区太小，则必须创建一个临时文件来进行排序。

table_cache = n 同时打开的数据表的数量(默认设置是64)。

tmp_table_size = n 临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。

thread_cache_size show global status like ‘Thread%’;

threads_cache:代表当前线程缓存中有多少空闲线程 Threads_connected：当前被使用的线程数 Threads_created：代表子服务器启动，创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值 Threads_running：代表非休眠线程的数量，而非正在使用的线程数量

thread_cache_size thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

thread_cache_size大小的设置：

如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。 对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

查询thread_cache_size设置

show global status like’thread_cache_size’;

优化方法： 1、mysql> set global thread_cache_size=16 2、编辑/etc/my.cnf 更改/添加 thread_concurrency = 16  

InnoDB存储引擎与PostgreSQL非常不同 InnoDB的缓冲池用来管理所有数据库对象 写文件操作通过O_DIRECT选项来避免两次缓存 InnoDB缓冲池越大性能越好 通常是系统内存60%~80% PostgreSQL缓冲池仅用来管理最热的数据 强烈的依赖操作系统的缓存来处理数据 PostgreSQL缓存越大性能越差 通常是系统内存通常25%~30%  #配置文件 innodb_buffer_pool_size = 100G innodb_buffer_pool_instances = 16 innodb_page_size = 4096 innodb_flush_method = O_DIRECT #在线设置buffer pool大小 # MySQL 5.7 online resize buffer pool mysql> set global innodb_disable_resize_buffer_pool_debug=off; Query OK, 0 rows affected (0.00 sec) mysql> set global innodb_buffer_pool_size=256*1024*1024; Query OK, 0 rows affected (0.00 sec) FUZZY CHECKPOINT 刷新部分脏页，对系统影响较小 5.6：独立的刷新线程 5.7：并行刷新线程 innodb_io_capacity SHARP CHECKPOINT 刷新全部的脏页，系统hang住 innodb_fast_shutdown Neighbor Page Flush innodb_flush_neighbors innodb_io_capacity = 1000/4000/8000 innodb_page_cleaners = 1 / 4 innodb_fast_shutdown = 0/1 innodb_flush_neighbors = 0/1/2 重做日志 记录页操作的日志 与二进制日志完全不同 循环覆盖写 默认没有类似PG或者Oracle的归档 重做日志大小限制 before 5.6: max 4G start from 5.6: max 512G innodb_log_file_size = 1900M / 4G innodb_log_buffer_size = 8M / 32M innodb_log_files_in_group = 2/3 innodb_log_group_home_dir = /redolog/

undo段 实现回滚 实现MVCC功能 PostgreSQL没有undo段！！！ undo段数量 before MySQL 5.5: 1024 start from MySQL 5.5: 128*1024 undo回收 purge innodb_undo_directory = /undolog/ innodb_undo_logs = 128 innodb_undo_tablespaces = 3

innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 1G innodb_purge_rseg_truncate_frequency = 128

innodb_purge_batch_size = 300 innodb_purge_threads = 4/8  

线程池 保障高并发下的性能平稳 MariaDB线程池没有优先级队列 推荐MySQL/InnoSQL/Percona线程池 推荐默认开启用线程池 thread_handling = pool‐of‐threads  thread_pool_size = 32 # CPU thread_pool_oversubscribe = 3 extra_port = 3333 #额外的端口

MySQL日志配置 binary log error log slow log general log（通常不推荐） events_statements_current events_statements_history(_long) # 二进制文件 log‐bin = /binlog/mysqld‐bin log‐expire‐day = 7 syslog syslog_tag = stock #mysqld_stock log‐slow‐queries long_query_time = 2 log‐queries‐not‐using‐indexes log‐slow‐admin‐statements min‐examined‐row‐limit log_throttle_queries_not_using_indexes log_slow_slave_statements [mysqld] performance_schema mysql> select * from setup_consumers\G ************************ 1. row *********************** NAME: events_stages_current ENABLED: NO …………

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

1

select id from `dynamic` order by rand() limit 1000;

上面的SQL语句，可优化为：

1

select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

1

select * from 表A where id in (select id from 表B)

上面SQL语句相当于

1

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原SQL语句：

1

select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：

1

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

取出的结果集如下图表示，A表不在B表中的数据：

1

select id,name from product limit 866613, 20

使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：

1

select id,name from product where id> 866612 limit 20

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

如下图这个SQL语句，扫描的行数成百万级以上的时候就可以使用分段查询：

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

那如何查询%name%？

如下图所示，虽然给secret字段添加了索引，但在explain结果并没有使用：

那么如何解决这个问题呢，答案：使用全文索引。

在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的SQL语法是：

1

ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的SQL语句是：

1

select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan' in boolean mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

比如：

1

select user_id,user_project from user_base where age*2=36;

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：

1

select user_id,user_project from user_base where age=36/2;

where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

对于联合索引来说，如果存在范围查询，比如between、>、 show global status like 'open%tables%';

Opened_tables数值非常大，说明cache太小，导致要频繁地open table，可以查看下当前的table_open_cache设置

> show variables like '%table_open_cache%';

默认是64，一般设置为max_connections就没问题了（如果还不够，可以继续加大，但不能设置大得离谱，可能会引发其他问题）。4G内存的机器，建议设置为2048

> set global table_open_cache=2048;

Query OK, 0 rows affected (0.00 sec)

设置后可以观察一下，如果opening table不再怎么出现，说明此修改是有效的，将其添加到mysql的配置文件，这样数据库重启后仍可保留此设置。

> show variables like '%table_open_cache%';

比较适合的值：

Open_tables/Opened_tables>=0.85

Open_tables/table_open_cache8/2G—>16/3G—>32/大于3G—>64

interactive-timeout 全局会话级动态参数，默认值28800s为8小时。服务器在关闭交互式连接之前等待其活动的秒数。如果前端程序采用短连接，建议缩短wait_timeout和interactive_timeout值, 如果前端程序采用长连接，可直接注释掉这两个参数，默认配置(8小时)

wait_timeout=600 全局会话级动态参数，默认值28800s为8小时。服务器在关闭非交互式连接之前等待其活动的秒数。在线程启动时，会话wait_timeout初始值从全局interactive_timeout值获取。

lock_wait_timeout=3600 全局会话级动态参数，默认值为31536000s（1 年）。此变量指定尝试获取元数据锁的超时时间（以秒为单位）,允许值范围为1到31536000。此超时适用于所有使用元数据锁的语句。这些包括DML和DDL操作对表、视图、存储过程和函数，以及LOCK TABLES，FLUSH TABLES WITH READ LOCK和HANDLER语句。

connect_timeout=10 全局动态参数，默认值是10s。连接超时之前的最大秒数,在 Linux 平台上，该超时也用作等待服务器首次回应的时间。如果客户经常遇到Lost connection to MySQL server at 'XXX', system error: errno，可以增加connect_timeout的值。连接超时之前的最大秒数,在 Linux 平台上，该超时也用作等待服务器首次回应的时间。

#*** tmp && heap settings相关选项 ***#

tmp_table_size 全局会话级动态参数，默认值16M。内部内存临时表的最大大小，如果超过该值，则结果放到磁盘中,此限制是针对单个表的,而不是总和。实际由tmp_table_size和max_heap_table_size中较小的值确定。如果内存中的临时表超出限制，MySQL会自动将其转换为磁盘上的临时表。如果执行许多高级GROUP BY查询，并且有大量内存，增加tmp_table_size和max_heap_table_size的值。通过比较Created_tmp_disk_tables和Created_tmp_tables变量的值，确定tmp_table_size设置的是否合理。

max_heap_table_size 全局会话级动态参数，默认值16M。独立的内存表所允许的最大容量，此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源。

#*** log settings 相关选项 ***#

log_timestamps=SYSTEM 全局动态参数，默认值UTC。控制写入错误日志的消息中时间戳的时区，以及写入文件的一般查询日志和慢速查询日志消息。不影响一般查询日志和慢查询日志消息写入表的时区（mysql.general_log, mysql.slow_log）。

log-bin=/path/logs/mysql-binlog 全局静态参数。打开二进制日志功能，在复制(replication)配置中,作为MASTER主服务器必须打开此项。如果需要从最后的备份中做基于时间点的恢复，也同样需要二进制日志。默认在datadir下。这样设置后相当于，制定了log_bin_basename和log_bin_index的配置。

log_slave_updates=1 全局静态参数，默认false。表示slave将从主服务器接收到的复制事件写进自己的二进制日志，必须在从属设备上启用二进制日志记录才能使此变量生效。

relay-log 全局静态参数，文件名。定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn

relay_log_index 全局静态参数，文件名。如果为空，则默认位置在数据文件的目录。

log_error_verbosity 全局动态参数，默认值为2。1在错误日志中记录ERROR，2记录ERROR,WARNING，3记录ERROR,WARNING,INFORMATION。从MySQL5.7.2开始，log_error_verbosity系统变量优先于并且应该代替--log-warnings选项或log_warnings系统变量使用。

log-error=/path/data/error.log 全局静态参数，文件名。记录错误日志的路径。

slow_query_log=1 全局动态参数，默认值OFF。是否开启慢查询日志。慢查询是指执行时间超过long_query_time定义时间的查询。如果log_long_format被打开,那些没有使用索引的查询也会被记录。

long-query-time=0.1 全局会话级动态参数，默认值为10s。设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，同时会增加Slow_queries状态变量的值。不要设置为1, 否则会导致所有的查询,甚至非常快的查询也被记录下来。该值可以指定为微秒。

log_output=FILE 全局动态参数，默认值为FILE，可以为FILE和TABLE。指定了慢查询输出的格式，可以设置为TABLE，然后就可以查询mysql.slow_log表了。

slow_query_log_file=/path/slow.log 全局动态参数，文件名。指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log

log-queries-not-using-indexes=1 全局动态参数，默认值OFF。启用慢查询日志的情况下启用此变量，会记录SQL语句没有使用索引的查询。此选项不一定意味着不使用索引，例如，使用全索引扫描的查询使用索引但会被记录，因为索引不会限制行数。

log_throttle_queries_not_using_indexes=60 全局动态参数，如果log_queries_not_using_indexes启用，该log_throttle_queries_not_using_indexes变量会限制每分钟可以写入慢查询日志的此类查询的数量。值0（默认值）表示“无限制”。

min_examined_row_limit=100 全局会话级动态参数，默认值为0。记录那些由于查找了多余1000次而引发的慢查询。

log_slow_admin_statements=on 全局动态参数，默认关闭。默认写入慢查询日志的语句中不包含管理语句，也不会记录不使用索引进行查找的查询。打开后会记录执行慢的管理语句，包括ALTER TABLE、 ANALYZE TABLE、 CHECK TABLE、 CREATE INDEX、 DROP INDEX、 OPTIMIZE TABLE和 REPAIR TABLE。

log_slow_slave_statements=on 全局动态参数，默认关闭。此变量会启用日志记录在从属服务器上执行时间超过主库定义long_query_time几秒的查询，变量的状态适用于所有后续启动START SLAVE之后的语句。需要注意，所有以行格式记录在master中的语句都不会记录在slave的慢日志中，即使log_slow_slave_statements已启用。

log_queries_not_using_indexes=on 全局动态参数，默认关闭。开启后会把不使用索引的查询记录到慢查询日志中。

sync_binlog=1 全局动态参数，默认值为1。控制MySQL服务器将二进制日志同步到磁盘的频率。sync_binlog=0：禁用MySQL服务器将二进制日志同步到磁盘。相反，MySQL服务器依赖操作系统不时将二进制日志刷新到磁盘，就像它对任何其他文件所做的那样。此设置提供了最佳性能，但如果发生电源故障或操作系统崩溃，服务器可能已提交尚未同步到二进制日志的事务。sync_binlog=1：在提交事务之前启用二进制日志到磁盘的同步。这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响。在电源故障或操作系统崩溃的情况下，二进制日志中丢失的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证不会从二进制日志中丢失事务。sync_binlog=N, 其中是 0 或 1 以外的值：在收集到二进制日志提交组N后，将二进制日志同步到磁盘。N在电源故障或操作系统崩溃的情况下，服务器可能已经提交了尚未刷新到二进制日志的事务。由于磁盘写入次数增加，此设置可能会对性能产生负面影响。较高的值会提高性能，但会增加数据丢失的风险。

binlog_cache_size=4M 全局动态参数，默认值32KB。在一个事务中binlog为了记录SQL状态所持有的cache大小，如果经常使用大事务，可以增加此值来获取更大的性能。事务中的状态都将被缓冲在binlog缓冲中，然后在提交后一次性写入到binlog中，如果事务比此值大, 会使用磁盘上的临时文件来替代。此缓冲在每个连接的事务第一次更新状态时在session级别被创建。可以结合Binlog_cache_use和Binlog_cache_disk_use参数的值，调整此变量的大小。binlog_cache_size仅设置事务缓存的大小；语句缓存的大小由binlog_stmt_cache_size系统变量控制。

max_binlog_cache_size=2G 全局动态参数。如果一个事务需要超过这么多字节的内存，服务器会生成一个多语句事务需要超过'max_binlog_cache_size'字节的存储错误。最小值为 4096。可能的最大值为 16EB。最大推荐值为4GB，这是因为MySQL目前无法处理大于4GB的二进制日志。max_binlog_cache_size仅设置事务缓存的大小，语句缓存的上限由max_binlog_stmt_cache_size系统变量控制。在MySQL 5.7中，max_binlog_cache_size对会话的可见性与binlog_cache_size系统变量的可见性相匹配，换句话说，更改其值只会影响值更改后启动的新会话。

max_binlog_size=1G 全局动态参数，默认值1073741824为1GB。如果二进制日志写入的内容超出给定值，日志就会发生滚动。该值不能大于1GB或小于4096字节。如果正使用大的事务，二进制日志还会超过max_binlog_size。如果写入二进制日志导致当前日志文件大小超过此变量的值，则服务器轮换二进制日志（关闭当前文件并打开下一个文件）。最小值为 4096 字节。最大值和默认值为 1GB。加密的二进制日志文件有一个额外的512字节标头，包含在max_binlog_size。如果max_relay_log_size为0，则该值max_binlog_size也适用于中继日志。开启GTID，当max_binlog_size被用完，如果无法访问系统表mysql.gtid_executed，把当前的GTID信息写入二进制日志文件，则无法切换二进制日志。在这种情况下，服务器会根据其binlog_error_action设置做出响应。如果设置为IGNORE_ERROR，则在服务器上记录错误并停止二进制日志记录，如果设置为ABORT_SERVER，则服务器会关闭。

secure-file-priv=/path/tmp 全局静态参数。此变量用于限制数据导入和导出操作的影响，例如由 LOAD DATA and SELECT ... INTO OUTFILE语句和LOAD_FILE()函数执行的操作。这些操作只允许有FILE权限的用户使用。secure_file_priv可以设置如下：如果为空，则变量无效。这不是一个安全的设置。如果设置为目录的名称，则服务器将导入和导出操作限制为仅处理该目录中的文件。目录必须存在；服务器不会创建它。如果设置为NULL，服务器将禁用导入和导出操作。服务器 secure_file_priv在启动时检查 的值，如果该值不安全，则将警告写入错误日志。如果非NULL值是空的，或者值是数据目录或其子目录，或者所有用户都可以访问的目录，则认为非值是不安全的。如果secure_file_priv设置为不存在的路径，则服务器将错误消息写入错误日志并退出。

expire_logs_days=30 全局动态参数。自动删除binlog的天数，8.0已弃用，由binlog_expire_logs_seconds代替。MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项

binlog_expire_logs_seconds 全局静态参数，默认值2592000秒，即30天。如果expire_logs_days和binlog_expire_logs_seconds在启动时有一个参数设置为非0，则该值决定二进制日志有效期，如果同时出现则binlog_expire_logs_seconds决定二进制日志有效期，如果未设置则用binlog_expire_logs_seconds的默认值为二进制日志有效期。

master_info_repository=TABLE 全局动态参数，5.7默认为FILE，8.0默认值为TABLE。该变量的设置决定了从服务器是将主状态和连接信息记录到系统数据库中mysql.slave_master_info表中，还是记录到数据目录中的文件中。在配置多个复制通道之前，必须设置此变量，只有在没有执行复制线程时才能更改此变量的值。

relay_log_info_repository=TABLE 全局动态参数，5.7默认为FILE，8.0默认值为TABLE。该变量的设置决定了从服务器是将其在中继日志中的位置记录到系统数据库中的mysql.slave_relay_log_info表中，还是记录到数据目录中的文件中。配置多个复制通道时需要设置。还需要中继日志信息日志的TABLE设置以使复制对意外停止具有弹性，为此还必须启用--relay-log-recovery选项。

relay_log_recovery=1 全局静态参数，默认5.7FALSE/8.0OFF关闭。在服务器启动后立即自动启用中继日志恢复。恢复进程创建一个新的中继日志文件，将SQL线程位置初始化为这个新的中继日志，并将I/O线程初始化为SQL线程位置，然后继续从主服务器读取中继日志。这个全局变量是只读的，可以通过使用该选项启动从属服务器来更改其值，该--relay-log-recovery选项应在复制从属服务器意外停止后使用，以确保不会处理可能损坏的中继日志。此变量还与relay-log-purge交互，后者控制在不再需要日志时清除日志。在禁用--relay-log-recovery选项时启用relay-log-purge可能会从未清除的文件中读取中继日志，从而导致数据不一致。当relay_log_recovery启用并且从属服务器由于在多线程模式下运行时遇到错误而停止时，可以使用START SLAVE UNTIL SQL_AFTER_MTS_GAPS确保在切换回单线程模式或执行CHANGE MASTER TO 语句之前处理所有间隙。

relay-log-purge=1 全局动态参数，默认启用。不再需要时立即禁用或者启用中继日志的自动清除。

gtid_mode=on 全局动态参数，默认关闭。此选项指定是否使用全局事务标识符(GTID) 来标识事务。将此选项设置为--gtid-mode=ON要求将enforce-gtid-consistency其设置为ON。记录的事务可以是匿名的或使用GTID。匿名事务依赖二进制日志文件和位置来识别特定事务。GTID事务具有用于引用事务的唯一标识符。不同的模式是：OFF: 新的和复制的事务都必须是匿名的。OFF_PERMISSIVE: 新交易是匿名的。复制的交易可以是匿名交易或 GTID 交易。ON_PERMISSIVE: 新交易是 GTID 交易。复制的交易可以是匿名交易或 GTID 交易。ON：新事务和复制事务都必须是 GTID 事务。从一个值到另一个值的更改一次只能是一个步骤。例如，如果 gtid_mode当前设置为 OFF_PERMISSIVE，则可以更改为 OFF或ON_PERMISSIVE但不能更改为ON。

enforce_gtid_consistency=1 全局动态参数，默认关闭。启用后，服务器通过只允许执行可以使用GTID安全记录的语句来强制执行GTID一致性。在启用基于GTID的复制之前，必须将--enforce-gtid-consistency选项设置为ON。可以配置的值为：OFF：允许所有事务违反 GTID 一致性。ON: 不允许任何事务违反 GTID 一致性。WARN：允许所有事务违反 GTID 一致性，但在这种情况下会生成警告。enforce_gtid_consistency开启时，只能记录可以使用GTID安全语句记录的语句，因此以下操作不能使用：CREATE TABLE ... SELECT statements。CREATE TEMPORARY TABLE 或 DROP TEMPORARY TABLEstatements 在事务内部。更新事务和非事务表的事务或语句。如果所有非事务性表都是临时的，则在与事务性DML相同的事务或同一语句中允许非事务性DML是一个例外。

slave-rows-search-algorithms='INDEX_SCAN,HASH_SCAN' 全局动态参数，在8.0.18弃用。在为基于行的日志记录和复制准备成批的行时，此选项控制如何搜索行以查找匹配项，散列是否用于使用主键或唯一键、其他键或不使用键的搜索全部。默认值为 TABLE_SCAN,INDEX_SCAN，这意味着所有可以使用索引的搜索都使用它们，而没有任何索引的搜索使用表扫描。

binlog_format=ROW 全局会话级动态参数，默认值为ROW，其他值ROW/STATEMENT/MIXED。MySQL复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。STATEMENT模式（SBR）每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数，last_insert_id()，以及user-defined functions(udf)等会出现问题)ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

binlog_checksum=1 全局动态参数。启用后，会校验主服务器写入二进制日志中的每个事件。binlog_checksum支持值NONE（禁用）和CRC32，默认值为CRC32。不能在事务中更改binlog_checksum的值。当binlog_checksum禁用（值 NONE）时，服务器通过写入和检查每个事件的事件长度（而不是校验和）来验证它是否仅将完整事件写入二进制日志。更改此变量的值会切换二进制日志，导致校验总是写入整个二进制日志文件，而不是只写入其中的一部分。在主服务器上将此变量设置为从服务器无法识别的值会导致从服务器将自己的binlog_checksum值设置为NONE，并停止复制并出现错误。（Bug #13553750，Bug #61096）如果与旧从站的向后兼容性是一个问题，可能需要显式设置该值为NONE.

slow_launch_time 默认动态参数，默认值为2.如果创建线程花费的时间超过了这么多秒，服务器会增加show global status like 'Slow_launch_threads'状态变量。

general_log=0 全局动态参数，默认关闭。将所有到达MySQL Server的SQL语句记录下来。

general_log_file=/path/mysql.log 全局动态参数，文件名。general_log开启才能生效。

max_relay_log_size=1G 全局静态参数。服务器自动切换中继日志文件的大小。如果此值非零，则中继日志在其大小超过此值时自动切换。如果此值为零（默认值），则中继日志在达到max_binlog_size时发生切换。

relay-log-purge=1 全局动态参数，默认值为on。是否自动清空不再需要中继日志时。在启用该选项时禁用清除中继日志会导致数据一致性风险，因此不具备崩溃安全性。

binlog_stmt_cache_size 全局动态参数，默认32KB。二进制日志用于保存事务期间发出的非事务语句的内存缓冲区的大小。在服务器上启用二进制日志记录时（使用log_bin系统变量设置为ON），如果服务器支持任何事务存储引擎，则为每个客户端分配单独的二进制日志事务和语句缓存。如果事务中使用的非事务性语句的数据超出内存缓冲区中的空间，则超出的数据将存储在临时文件中。当服务器上的二进制日志加密处于活动状态时，内存缓冲区未加密，但（从 MySQL 8.0.17 开始）用于保存二进制日志缓存的任何临时文件都被加密。提交每个事务后，通过清除内存缓冲区并截断临时文件（如果使用）来重置二进制日志语句缓存。如果经常在事务期间使用大型非事务性语句，则可以通过减少或消除写入临时文件的需要来增加此缓存大小以获得更好的性能。查看Binlog_stmt_cache_use和Binlog_stmt_cache_disk_use状态，调整此变量的大小。

slave-net-timeout=60 全局动态参数，默认值60s为1分钟。在从服务器认为连接断开、中止读取并尝试重新连接之前等待来自主服务器更多数据的秒数。第一次重试在超时后立即发生。但是，只有从服务器超过slave_net_timeout秒没有从主服务器收到数据才通知网络中断。

net_read_timeout=30 全局会话级动态参数，默认30s。从服务器从主服务器读取信息的超时时间。

net_write_timeout=60 全局动态参数，默认值60s。从服务器写入信息的超时时间。

net_retry_count=10 全局会话级动态参数，默认值为10次。如果通信端口上的读取或写入中断，在放弃之前重试的次数。

net_buffer_length 全局会话级动态参数，默认值16KB，最大为1MB。每个客户端线程都与一个连接缓冲区和结果缓冲区相关联。两者都以给出的大小开始，每个SQL语句的net_buffer_length根据需要动态放大到max_allowed_packet的值，结果缓冲区缩小到net_buffer_length的值。此参数值不用配置，连接缓冲区会自动扩大，在会话级别是只读的。

#*** MyISAM 相关选项 ***#

key_buffer_size=32M 全局动态参数。MyISAM引擎表索引块缓冲区大小，增加它可得到更好的索引处理性能。如果是以InnoDB引擎为主的DB，key_buffer_size参数值可以设置较小，8MB已足够。如果是以MyISAM引擎为主，可设置较大，但不能超过4G。如果对表的顺序扫描请求非常频繁，并且认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。强烈建议不使用MyISAM引擎，默认使用InnoDB引擎，该参数值设置的过大反而会是服务器整体效率降低！

read_buffer_size=8M 全局会话级动态参数。MyISAM引擎表进行全表顺序扫描时分配的缓冲区大小。此变量的值应设置为4KB的倍数。

read_rnd_buffer_size=4M 全局会话级动态参数。MyISAM引擎表以索引扫描(Random Scan)方式扫描数据的buffer大小，多用于范围读取优化。当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道。如果增高此值,可以提高很多ORDER BY操作的性能。

bulk_insert_buffer_size=64M 全局会话级动态参数。MyISAM用在块插入优化中的树缓冲区的大小。MyISAM引擎使用特殊的树状缓存来使得插入(INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATAINFILE) 更快地进行批量插入。此变量限制每个进程中缓冲树的字节数，设置为0会关闭此优化，为了最优化不要将此值设置大于key_buffer_size。

myisam_sort_buffer_size=128M 全局会话级动态参数。MyISAM 设置恢复表之时使用的缓冲区的大小，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序MyISAM索引分配的缓冲区。

myisam_max_sort_file_size=10G 全局动态参数。MySQL在重建MyISAM索引时允许使用的临时文件的最大大小（在REPAIR TABLE、 ALTER TABLE或 期间LOAD DATA INFILE）。如果文件大小大于此值，则使用键缓存创建索引，这会更慢。该值以字节为单位。如果MyISAM索引文件超过此大小并且磁盘空间可用，则增加该值可能有助于提高性能。该空间必须在包含原始索引文件所在目录的文件系统中可用。

myisam_repair_threads=1 全局会话级动态参数，默认值为1。如果此值大于1，MyISAM则在此过程中并行创建表索引（每个索引在其自己的线程中）。如果一个表拥有超过一个索引,MyISAM可以通过并行排序使用超过一个线程去修复他们。这对于拥有多个CPU以及大量内存情况的用户，是一个很好的选择。

#*** INNODB 相关选项 ***#

innodb_file_per_table=1 全局动态参数，默认开启。InnoDB为独立表空间模式。InnoDB将每个新创建的表的数据和索引存储在单独的.ibd文件中，而不是系统表空间中。当这些表被删除或截断时，这些表的存储空间会被回收。此设置启用 InnoDB表 压缩等功能。通过ALTER TABLE操作会将InnoDB表从系统表空间移动到单个文件。

独立表空间优点：1.每个表都有自已独立的表空间。2.每个表的数据和索引都会存在自已的表空间中。

3.可以实现单表在不同的数据库中移动。4.空间可以回收（除drop table操作处，表空不能自已回收）

缺点：1.单表增加过大，如超过100G

结论：共享表空间在Insert操作上稍有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files

innodb_open_files=65535 全局静态参数，默认值是300。限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个参数值。

innodb_buffer_pool_size=2G 全局静态参数，默认值128MB。InnoDB缓存表和索引数据的内存区域（包括数据页、索引页、插入缓存、锁信息、自适应哈希、数据字典信息）。当缓冲池的大小大于1GB时，设置innodb_buffer_pool_instances为大于1的值可以提高繁忙服务器上的可伸缩性。InnoDB使用一个缓冲池来保存索引和原始数据，设置越大，在存取表里面数据时所需要的磁盘I/O越少。在数据库专用服务器上，可以设置这个变量到服务器物理内存大小的70%-80%。配置缓冲池大小时请注意以下潜在问题，并准备好在必要时缩减缓冲池的大小。物理内存的竞争可能导致操作系统中的分页。InnoDB为缓冲区和控制结构保留额外的内存，因此分配的总空间大约比指定的缓冲池大小大 10%。缓冲池的地址空间必须是连续的，这在具有在特定地址加载的DLL的Windows系统上可能是一个问题。初始化缓冲池的时间大致与其大小成正比。在具有大型缓冲池的实例上，初始化时间可能很长。为了减少初始化时间，可以在服务器关闭时保存缓冲池状态并在服务器启动时恢复它。当增加或减少缓冲池大小时，操作是以块的形式执行的。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认为 128 MB。缓冲池大小必须始终等于或倍数innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances。如果将缓冲池大小更改为不等于或倍数innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值，则缓冲池大小会自动调整为等于或倍数的值， 且不小于指定的缓冲池大小 innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances。innodb_buffer_pool_size可以动态设置，可以在不重新启动服务器的情况下调整缓冲池的大小。可通过查看Innodb_buffer_pool_resize_status变量的状态，调整缓冲池大小。

innodb_buffer_pool_instances=4 全局静态参数，默认值为8。Innodb缓冲池划分 的区域数。对于具有数 GB 范围内缓冲池的系统，将缓冲池划分为单独的实例可以通过减少不同线程读取和写入缓存页面时的争用来提高并发性。使用散列函数将存储在缓冲池中或从缓冲池中读取的每一页随机分配给缓冲池实例之一。每个缓冲池管理自己的空闲列表、刷新列表、 LRU和所有其他连接到缓冲池的数据结构，并受到自己的缓冲池互斥体的保护。innodb_buffer_pool_size此选项仅在设置为1GB或更大时生效，总缓冲池大小在所有缓冲池中分配。为获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1GB。

innodb_buffer_pool_load_at_startup=1 全局静态参数，默认开启。指定在MySQL服务器启动时，InnoDB缓冲池通过加载它在较早时间持有的相同页面来自动预热。通常与innodb_buffer_pool_dump_at_shutdown结合使用。

innodb_buffer_pool_dump_at_shutdown=1 全局动态参数，默认开启。指定是否在MySQL服务器关闭时记录缓存在InnoDB缓冲池中的页面，以缩短下次重启时的预热过程。innodb_buffer_pool_dump_pct选项定义要转储的最近使用的缓冲池页面的百分比。

innodb_buffer_pool_dump_pct=25 全局动态参数，默认25。定义要转储的最近使用的缓冲池页面的百分比。指定要读取和转储的每个缓冲池最近使用的页面的百分比。范围为1到100。例如，如果有4个缓冲池，每个缓冲池有100个页面，并且innodb_buffer_pool_dump_pct设置为25，则转储每个缓冲池中最近使用的25个页面。

innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend 全局静态参数，默认ibdata1:12M:autoextend。InnoDB定义系统表空间数据文件的名称、大小和属性。InnoDB将数据保存在一个或者多个数据文件中成为表空间，如果只有单个盘保存的数据，一个自增文件就足够了。其他情况下，每个设备一个文件一般就可以了，也可以配置InnoDB来使用裸盘分区。对第一个系统表空间数据文件强制执行最小文件大小，以确保有足够的空间用于双写缓冲区页面：对于innodb_page_size=16KB或更小的值，最小文件大小为 3MB。对于innodb_page_size=32KB的值，最小文件大小为 6MB。对于innodb_page_size=64KB的值，最小文件大小为 12MB。InnoDB定义系统表空间数据文件的名称、大小和属性。autoextend默认增量为64MB，要修改增量，请更改innodb_autoextend_increment系统变量。

innodb_flush_log_at_trx_commit=1 全局动态参数，默认值1。控制事务日志从innodb log buffer写入到redo log中的频率。>0每向二进制日志文件写入N条SQL或者N个事务后，则把二进制日志文件的数据刷新到磁盘上。可以通过更改默认值来获得更好的性能，但随后可能会在崩溃中丢失多达一秒钟的事务。值为0时，由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。事务提交时不会执行从日志缓冲区到日志文件的写入。在这种情况下，MySQL性能最好。由于进程调度问题，不能保证每秒一次的刷新。由于刷新到磁盘操作大约每秒发生一次，因此任何mysqld进程崩溃都会丢失多达一秒钟的事务。值为1时，完全符合ACID要求。每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。值为2时，每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，但并不会立即刷写到磁盘，由存储引擎的main_thread每秒将日志刷新到磁盘。由于进程调度问题，每秒一次的刷新不能保证每秒发生100%。刷新到磁盘操作大约每秒发生一次，因此可能会在操作系统崩溃或断电时丢失多达一秒钟的事务。InnoDB日志刷新频率由innodb_flush_log_at_timeout控制，允许将日志刷新频率设置为N秒（其中 N是1 … 2700，默认值为 1）。但是，任何 mysqld进程崩溃都可以抹掉多达N秒钟的事务。DDL更改和其他内部InnoDB活动会独立于设置innodb_flush_log_at_trx_commit刷新InnoDB日志。InnoDB无论innodb_flush_log_at_trx_commit设置如何，崩溃恢复都有效。事务要么完全应用，要么完全删除。InnoDB为了在与事务一起使用的复制设置中获得最大可能的持久性和一致性，请使用以下设置：sync_binlog=1 && innodb_flush_log_at_trx_commit=1，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。但是都为1会导致频繁的IO操作，因此该模式也是最慢的一种方式。双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，推荐的做法是innodb_flush_log_at_trx_commit=2 && sync_binlog=N(N为500 或1000)且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

警告：许多操作系统和一些磁盘硬件欺骗了刷新到磁盘操作。他们可能会告诉mysqld已经发生了刷新，即使它还没有发生。在这种情况下，即使使用推荐的设置也无法保证事务的持久性，在最坏的情况下，断电可能会损坏InnoDB数据。在SCSI磁盘控制器或磁盘本身中使用电池支持的磁盘缓存可加快文件刷新速度，并使操作更安全。可以尝试禁用硬件缓存中磁盘写入的缓存。

innodb_log_buffer_size=32M 全局静态参数，默认值16MB。InnoDB用于写入磁盘上日志文件的缓冲区大小（以字节为单位）。当此值快满时, InnoDB将必须刷新数据到磁盘上。由于基本上每秒都会刷新一次，所以没有必要将此值设置的太大。大型日志缓冲区使大型事务无需在事务提交之前将日志写入磁盘即可运行。因此，如果有更新、插入或删除许多行的事务，则使日志缓冲区更大可以节省磁盘 I/O。

innodb_log_file_size=48M 全局静态参数，默认值48MB。在日志组中每个日志文件的大小，应该设置日志文件总合大小到缓冲池大小的25%~100%，来避免在日志文件覆写上不必要的缓冲池刷新行为。通常，日志文件的组合大小应该足够大，以便服务器可以消除工作负载活动的高峰和低谷，这通常意味着有足够的重做日志空间来处理超过一个小时的写入活动。该值越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘 I/O。较大的日志文件也会使崩溃恢复更慢。如果innodb_dedicated_server启用，则 innodb_log_file_size如果未明确定义，则会自动配置该值。

innodb_log_files_in_group=2 全局静态参数，默认为2。日志组中的日志文件数。以循环方式写入文件。

#innodb_max_undo_log_size=1G 全局动态参数，默认值为1073741824字节(1024 MiB)。定义undo表空间的阈值大小。如果撤消表空间超过阈值，则可以在innodb_undo_log_truncate启用时将其标记为截断。

#innodb_purge_rseg_truncate_frequency 全局动态参数，默认128次。根据调用清除的次数定义清除系统释放回滚段的频率。在释放回滚段之前，无法截断undo表空间。通常，清除系统每调用128次清除就释放回滚段一次。默认值为128。减小此值会增加清除线程释放回滚段的频率。

#innodb_undo_directory=/path/data/undolog 全局动态参数。InnoDB创建undo表空间的路径。通常用于将undo日志放在不同的存储设备上。与innodb_rollback_segments和innodb_undo_tablespaces一起使用。没有默认值（它是NULL）。如果未指定路径，则会在MySQL数据目录中创建undo表空间。

#innodb_rollback_segments 全局动态参数，默认128。定义InnoDB使用undo回滚段的数。系统表空间总是分配一个回滚段，32个回滚段保留给临时表使用，并托管在临时表空间( ibtmp1)中。要为生成undo记录的数据修改事务分配额外的回滚段，必须将innodb_rollback_segments设置为大于33的值。如果配置单独的undo表空间，系统表空间中的回滚段将变为非活动状态。每个回滚段最多可以支持1023个数据修改的事务。当innodb_rollback_segments设置为32或更少时，InnoDB将一个回滚段分配给系统表空间，将32分配给临时表空间( ibtmp1)。当innodb_rollback_segments设置为大于32的值时，InnoDB将一个回滚段分配给系统表空间，将32分配给临时表空间( ibtmp1)，并为undo表空间（如果存在）分配额外的回滚段。如果不存在撤消表空间，则会将额外的回滚段分配给系统表空间。尽管可以增加或减少InnoDB使用的回滚段的数量，但系统中物理存在的回滚段的数量永远不会减少。因此，可以从该参数的低值开始并逐渐增加它，以避免分配不需要的回滚段。默认值为128，这也是innodb_rollback_segments的最大值。

#innodb_undo_tablespaces=95 全局静态参数，默认95。InnoDB使用undo表空间的数量。5.7.21弃用，8.0.14弃用。

#根据您的服务器IOPS能力适当调整，一般配普通SSD盘的话，可以调整到10000-20000。配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000-80000。

innodb_io_capacity=4000 全局动态参数，默认值200。该参数设置InnoDB后台任务每秒执行I/O操作数的上限，例如从缓冲池刷新页面和合并来自更改缓冲区的数据。该参数是限制所有缓冲池实例的总限制。刷新脏页时，限制在缓冲池实例之间平均分配。innodb_io_capacity应该设置为系统每秒大约可以执行的I/O操作数。理想情况下，将设置保持在尽可能低的水平，但不要太低以至于后台活动落后。如果该值太高，则会从缓冲池中删除数据并太快地插入缓冲区，从而无法为缓存提供显着优势。对于具有更高I/O速率的繁忙系统，可以设置更高的值以帮助服务器处理与高速率行更改相关的后台维护工作。通常，可以根据InnoDB用于I/O的驱动器数量来增加该值。例如，可以增加使用多个磁盘或固态磁盘(SSD)的系统的价值。对于低端SSD，默认设置200通常就足够了。对于更高端的总线连接SSD，请考虑更高的设置，例如 1000。对于具有单个5400RPM或7200RPM驱动器的系统，可以将值降低到100，它表示可用于执行约100IOPS的老一代磁盘驱动器的每秒I/O操作(IOPS)的估计比例。尽管可以指定一个非常高的值，但在实践中，比较大的值几乎没有任何好处。通常，不建议使用20000或更高的值，除非已证明较低的值不足以满足工作负载。调整innodb_io_capacity时考虑写入工作负载。具有大量写入工作负载的系统可能会受益于更高的设置。对于写入工作量较小的系统，较低的设置可能就足够了。可以设置innodb_io_capacity为100或更大的任何数字，最大由innodb_io_capacity_max定义。innodb_io_capacity可以在MySQL选项文件(my.cnf或)中设置或使用需要权限的语句my.ini动态更改。SET GLOBALSUPER innodb_flush_sync配置选项会导致innodb_io_capacity在检查点处发生的I/O活动突发期间忽略该设置。innodb_flush_sync默认启用。innodb_io_capacity_max=8000 全局动态参数，如果刷新活动落后，则可以以比InnoDB innodb_io_capacity变量定义的更高的每秒I/O操作(IOPS)速率更积极地刷新。innodb_io_capacity_max变量定义了在这种情况下后台任务执行的最大IOPS数。innodb_flush_sync=0 全局动态参数，默认开启。innodb_flush_sync的变量会导致在检查点发生的I/O活动突发期间忽略innodb_io_capacity设置。要遵守innodb_io_capacity设置定义的I/O速率，请禁用innodb_flush_sync。

innodb_flush_neighbors=0 全局动态参数，5.7默认为1，8.0默认为0。指定从InnoDB缓冲池中刷新页面是否也会刷新相同范围内的其他脏页面。设置为0禁用innodb_flush_neighbors相同范围内的脏页不会被刷新。设置1刷新相同范围内的连续脏页。设置为2会刷新相同范围内的脏页。innodb_write_io_threads=8/innodb_read_io_threads=8 全局静态参数，默认值4。innodb使用后台线程处理数据页上的读写I/O(输入输出)请求，根据服务器的CPU核数来更改,默认是4。这两个参数不支持动态改变，需要把该参数加入到my.cnf里，修改完后重启MySQL服务，允许值的范围从1-64。innodb_purge_threads=4 全局静态参数，默认为4。专门用于清除InnoDB操作的后台线程数。最小值1表示清除操作始终由后台线程执行，而不是作为主线程的一部分。在一个或多个后台线程中运行InnoDB清除操作有助于减少内部争用，提高可伸缩性。将该值增加到大于1会创建许多单独的清除线程，这可以提高对多个表执行DML操作的系统的效率。最大值为32。

innodb_page_cleaners=4 全局静态参数，默认为4。从缓冲池实例中清理刷新脏页的页面线程数。页面清理线程执行刷新列表和LRU刷新。当有多个页面清理线程时，每个缓冲池实例的缓冲池刷新任务被分派给空闲的页面清理线程。如果页面清理线程的数量超过缓冲池实例的数量，则innodb_page_cleaners自动设置为与innodb_buffer_pool_instances相同的值。如果工作负载在将脏页从缓冲池实例刷新到数据文件时受写入IO限制，并且系统硬件具有可用容量，则增加页面清理线程的数量可能有助于提高写入IO吞吐量。多线程页面清理器支持扩展到关闭和恢复阶段。innodb_max_dirty_pages_pct=75 全局动态参数，默认值75。innodb主线程刷新缓存池中的数据，使脏数据比例小于90%，这是一个软限制，不被保证绝对执行。

innodb_flush_method=O_DIRECT 全局静态参数。定义用于将数据刷新到InnoDB数据文件和日志文件的方法，这会影响I/O吞吐量。表空间总是使用双重写入刷新方法。innodb_flush_method参数的选项包括：fsync：InnoDB调用系统fsync()刷新数据和日志文件。fsync是默认设置。O_DSYNC：InnoDB用于O_SYNC打开和刷新日志文件，以及fsync()刷新数据文件。InnoDB不会直接使用O_DSYNC，因为在许多Unix上都存在问题。O_DIRECT：InnoDB使用O_DIRECT（或 directio()在 Solaris 上）打开数据文件，并用于fsync()刷新数据和日志文件。此选项在某些GNU/Linux版本、FreeBSD 和 Solaris 上可用。O_DIRECT_NO_FSYNC: InnoDB在刷新I/O期间使用O_DIRECT，但之后跳过系统对fsync()调用。此设置适用于某些类型的文件系统，但不适用于其他类型。例如，它不适合XFS。如果不确定使用的文件系统是否需要fsync()，例如保留所有文件元数据请O_DIRECT改用。如何设置innodb_flush_method取决于硬件配置和工作负载，会有不同的性能影响。对于特定配置进行基准测试，以决定使用哪个设置，或者是否保留默认设置。检查Innodb_data_fsyncs的状态变量，查看每个设置的调用总数。fsync()工作负载中读取和写入操作的混合会影响设置的执行方式。例如，在具有硬件RAID控制器和电池支持的写入缓存的系统上，O_DIRECT可以帮助避免InnoDB缓冲池和操作系统文件系统缓存之间的双重缓冲。在某些系统上InnoDB数据和日志文件位于SAN上，这是默认值，或者对于主要是语句O_DSYNC的读取繁重的工作负载可能更快。SELECT始终使用反映您的生产环境的硬件和工作负载测试此参数。

innodb_lru_scan_depth=4000 全局动态参数，默认1024。影响InnoDB缓冲池刷新操作的算法和启发式的参数。指定每个缓冲池实例，页面清理线程扫描的缓冲池LRU页面列表向下多远，以查找要刷新的脏页。这是每秒执行一次的后台操作。小于默认值的设置通常适用于大多数工作负载。远高于必要的值可能会影响性能。只有在典型工作负载下有备用I/O容量时才考虑增加该值。相反，如果写入密集型工作负载使I/O容量饱和，请减小该值，尤其是在缓冲池较大的情况下。在调整innodb_lru_scan_depth时，从一个低值开始并向上设置，目标是很少看到零空闲页面。此外，在更改缓冲池实例的数量时考虑调整innodb_lru_scan_depth，因为innodb_lru_scan_depth*innodb_buffer_pool_instances定义了页面清理线程每秒执行的工作量。

innodb_checksum_algorithm=crc32 全局动态参数，默认值为crc32。指定如何生成和验证存储InnoDB表空间在磁盘中的块。

innodb_lock_wait_timeout=10 全局会话级动态参数，默认值50。InnoDB事务在放弃之前等待行锁的时间秒数。尝试访问被另一个InnoDB事务锁定的行的事务在发出以下错误之前最多等待这么多秒以对该行进行写访问：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction对于高度交互的应用程序或OLTP系统，可能会降低此值，以快速显示用户反馈或将更新放入队列以供稍后处理。可以为长时间运行的后端操作增加此值，例如数据仓库中等待其他大型插入或更新操作完成的转换步骤。innodb_lock_wait_timeout仅适用于InnoDB行锁。MySQL表锁不会在InnoDB内部发生，并且此超时不适用于等待表锁。锁定等待超时值在启用时不适用于死锁，因为会立即检测死锁并回滚其中一个死锁事务。在禁用时，发生死锁时依赖于事务回滚。innodb_lock_wait_timeout可以在运行时使用SET GLOBALor SET SESSION语句设置。更改 GLOBAL设置需要SUPER特权并影响随后连接的所有客户端的操作。任何客户端都可以更改SESSION的innodb_lock_wait_timeout设置，仅影响该客户端。

innodb_rollback_on_timeout=1 全局静态参数，默认关闭。InnoDB默认情况下仅回滚事务超时的最后一条语句。如果--innodb_rollback_on_timeout指定，事务超时会导致InnoDB中止并回滚整个事务。

innodb_print_all_deadlocks=1 全局动态参数，默认关闭。启用此选项后，有关InnoDB用户事务中的所有死锁的信息都会记录在mysqld错误日志中。否则使用SHOW ENGINE INNODB STATUS命令只会看到最后一个死锁的信息。偶尔的 InnoDB 死锁不一定是问题，因为 InnoDB 会立即检测到情况并自动回滚其中一个事务。如果应用程序没有适当的错误处理逻辑来检测回滚并重试其操作，可以使用此选项来解决为什么会发生死锁。大量死锁可能表明需要对发出 DML 或 SELECT … FOR UPDATE 语句的多个表的事务进行重组，以便每个事务以相同的顺序访问表，从而避免死锁情况。innodb_online_alter_log_max_size=4G 全局动态参数，默认134217728字节128M。指定在InnoDB表的联机DDL操作期间使用临时日志文件大小的上限。每个正在创建的索引或正在更改的表都有一个这样的日志文件。此日志文件存储在DDL操作期间在表中插入、更新或删除的数据。临时日志文件会在需要时按innodb_sort_buffer_size的值进行扩展，直至达到innodb_online_alter_log_max_size指定的最大值。如果临时日志文件超出大小上限，则ALTER TABLE操作失败并回滚所有未提交的并发DML操作。因此，此选项的较大值允许在线DDL操作期间发生更多DML，但也会延长DDL操作结束时表被锁定以应用日志数据的时间段。innodb_stats_on_metadata=0 全局动态参数，默认关闭。此选项仅适用于将优化器统计信息配置为非持久性的情况。当禁用innodb_stats_persistent或使用STATS_PERSISTENT=0创建或更改单个表时，优化器统计信息不会持久保存到磁盘。当innodb_stats_on_metadata启用时，当元数据语句show table status或者访问INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS表时（这些更新类似于ANALYZE TABLE发生的情况）InnoDB更新非持久统计信息。当关闭时，InnoDB在这些操作期间不会更新统计信息。禁用该设置可以提高具有大量表或索引的模式的访问速度。它还可以提高涉及 InnoDB 表的查询的执行计划的稳定性。要更改设置，请执行语句 SET GLOBAL innodb_stats_on_metadata=ON1|OFF0。更改设置需要SUPER权限并立即影响所有连接的操作。

innodb_undo_log_truncate=on 全局动态参数，默认关闭。启用后，超过定义的阈值的undo表空间将innodb_max_undo_log_size标记为截断。只有撤消表空间可以被截断。不支持截断驻留在系统表空间中的撤消日志。要发生截断，必须至少有两个undo表空间和两个重做的undo日志被配置。意味着innodb_undo_tablespaces必须设置为等于或大于2的值，并且innodb_rollback_segments必须设置为等于或大于35的值。innodb_purge_rseg_truncate_frequency配置选项可用于加快撤消表空间的截断。

internal_tmp_disk_storage_engine=InnoDB 全局动态参数，默认为InnoDB，8.0.16开始删除该选项。磁盘内部临时表的存储引擎为InnoDB引擎。使用internal_tmp_disk_storage_engine=INNODB时，生成超出InnoDB行或列限制的磁盘内部临时表的查询将返回Row size too large或Too many columns错误。解决方法是设置internal_tmp_disk_storage_engine=MYISAM。

#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快

innodb_status_output=0 全局动态参数，默认关闭。启用或禁用标准InnoDB监视器的定期输出。还与innodb_status_output_locks结合使用以启用或禁用InnoDB锁定监视器的定期输出。innodb_status_output_locks=1 全局动态参数，默认关闭。启用或禁用InnoDB锁定监视器。启用后，InnoDB锁定监视器会在SHOW ENGINE INNODB STATUS输出中打印有关锁定的附加信息，并在定期输出中打印到MySQL错误日志。InnoDB锁定监视器的定期输出作为标准InnoDB监视器输出的一部分打印。因此，InnoDB必须启用标准监视器才能使InnoDB Lock Monitor定期将数据打印到MySQL错误日志。

innodb_sort_buffer_size=67108864 全局静态参数，默认1048576字节为1M。指定在创建InnoDB索引期间用于对数据进行排序的排序缓冲区的大小。指定的大小定义了读入内存进行内部排序然后写出到磁盘的数据量。这个过程被称为“运行”。在合并阶段，读取和合并指定大小的缓冲区对。设置越大，运行和合并的次数就越少。该排序区域仅用于索引创建期间的合并排序，而不是在以后的索引维护操作期间。当索引创建完成时，缓冲区被释放。

innodb_autoinc_lock_mode=1 全局静态参数，默认值5.7版本为1，8.0版本为2。0：“传统”锁定模式。在这种锁定模式下，所有“ INSERT-like ”语句都会获得一个特殊的表级AUTO-INC锁定，用于插入到具有AUTO_INCREMENT列的表中。此锁通常保持到语句的末尾（而不是事务的末尾），以确保为给定的语句序列以可预测和可重复的顺序分配自动递增值INSERT ，并确保自动递增值任何给定语句分配的都是连续的。1：“连续”锁定模式。在这种模式下，“批量插入”使用特殊的AUTO-INC表级锁并持有它直到语句结束。这适用于所有INSERT … SELECT、 REPLACE … SELECT和LOAD DATA语句。一次只能执行一个持有AUTO-INC锁的语句。如果批量插入操作的源表与目标表不同，则AUTO-INC在对源表中选择的第一行进行共享锁之后，再对目标表进行锁定。如果批量插入操作的源和目标是同一个表，则AUTO-INC在所有选定行上获取共享锁后获取锁。2：“交错”锁定模式。在这种锁模式下，没有 “ INSERT-like ” 语句使用表级AUTO-INC锁，可以同时执行多条语句。这是最快且最具可扩展性的锁定模式，但 在使用基于语句的复制或恢复方案时从二进制日志重放SQL语句时，它是不安全的。在这种锁定模式下，自动递增值保证在所有并发执行 的“ INSERT-like ” 语句中是唯一的并且单调递增。但是，由于多个语句可以同时生成数字（即，数字的分配在语句之间交错），为任何给定语句插入的行生成的值可能不是连续的。如果唯一执行的语句是“简单插入”，其中要插入的行数是提前知道的，那么除了“混合模式插入”之外，为单个语句生成的数字中没有间隙。但是，当执行“批量插入”时，任何给定语句分配的自动增量值可能存在间隙。将自动增量与复制一起使用：如果使用基于语句的复制，请设置innodb_autoinc_lock_mode为0或1，并在主服务器及其从服务器上使用相同的值。innodb_autoinc_lock_mode如果使用=2（“ interleaved ”）或主从不使用相同锁定模式的配置，则不能确保从属上的自动增量值与主控上的值相同。如果使用基于行或混合格式的复制，所有自动增量锁定模式都是安全的，因为基于行的复制对 SQL 语句的执行顺序不敏感（并且混合格式使用基于行的对于基于语句的复制不安全的任何语句的复制）。批量插入的自动增量值的差距：innodb_autoinc_lock_mode设置为0（“传统”）或1（“连续”）时，任何给定语句生成的自动增量值都是连续的，没有间隙，因为表级AUTO-INC锁一直保持到语句结束，并且仅一次可以执行一个这样的语句。innodb_autoinc_lock_mode设置为2（“interleaved”）时，“bulk inserts”生成的自动增量值可能存在间隙，但前提是同时执行“INSERT-like”语句。

innodb_buffer_pool_chunk_size=128M 全局静态参数，默认值128MB。定义InnoDB缓冲池大小调整操作的块大小。允许在不重新启动服务器的情况下调整缓冲池的大小。为避免在调整大小操作期间复制所有缓冲池页面，该操作以 “块”执行。一个块中包含的页数取决于innodb_page_size的值。innodb_buffer_pool_chunk_size可以以1MB（1048576 字节）为单位增加或减少。innodb_buffer_pool_chunk_size更改值时适用以下条件：如果在缓冲池初始化时innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances大于当前缓冲池大小，innodb_buffer_pool_chunk_size则截断为innodb_buffer_pool_size/innodb_buffer_pool_instances。缓冲池大小必须始终等于或数倍innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances。如果更改innodb_buffer_pool_chunk_size, innodb_buffer_pool_size 会自动调整为等于或倍数innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances且不小于当前缓冲池大小的值。调整发生在缓冲池初始化时。更改innodb_buffer_pool_chunk_size时应小心，因为更改此值会自动增加缓冲池的大小。在更改innodb_buffer_pool_chunk_size之前，计算它将产生的影响，innodb_buffer_pool_size以确保生成的缓冲池大小是可以接受的。为避免潜在的性能问题，块 (innodb_buffer_pool_size/innodb_buffer_pool_chunk_size)的数量不应超过1000。

innodb_page_size=16384 全局静态参数，默认值16KB。指定MySQL实例中所有表空间的页面大小。可以使用值4096/4k 8192/8k 16384/16k 32768/32k 65536/64k来指定页面大小。innodb_page_size只能在初始化MySQL实例之前配置，之后不能更改。如果未指定值，则使用默认页面大小初始化实例。在ROW_FORMAT=COMPRESSED时不支持设置为32KB或64KB。默认的16KB或更大的页面大小适用于广泛的工作负载，特别是涉及表扫描的查询和涉及批量更新的DML操作。对于涉及许多小写入的OLTP工作负载，较小的页面大小可能更有效，当单个页面包含许多行时，争用可能是一个问题。对于通常使用小块大小的SSD存储设备，较小的页面可能也很有效。使InnoDB页面大小接近存储设备块大小可以最大限度地减少重写到磁盘的未更改数据量。第一个系统表空间数据文件 ( ibdata1) 的最小文件大小因innodb_page_size值而异。

innodb_thread_concurrency=16 全局动态参数，默认为0。服务器有几个CPU就设置为几。InnoDB尝试将并发的操作系统线程数保持在InnoDB小于或等于此变量给定的限制内（InnoDB使用操作系统线程来处理用户事务）。一旦线程数达到此限制，额外的线程将被置于“先进先出”（FIFO）队列中以等待执行。等待锁的线程不计入并发执行的线程数。此变量的范围是0到1000。0（默认值）被解释为无限并发（不限制并发数），可以更好去发挥CPU多核处理能力来提高并发量。如果MySQL实例与其他应用程序共享CPU资源，工作负载或并发用户数量正在增长，请考虑设置此变量。最优值依赖于应用程序，硬件以及操作系统的调度方式和运行的MySQL版本。需要测试一系列值以确定提供最佳性能的设置。

innodb_flush_log_at_timeout=1 全局动态参数，默认为1，每秒一次。设置多少秒写入和刷新日志。innodb_flush_log_at_timeout允许增加刷新之间的超时时间，以减少刷新并避免影响二进制日志组提交的性能。

innodb_log_group_home_dir 全局静态参数，目录名。InnoDB重做日志文件的目录路径，其编号由innodb_log_files_in_group指定。如果不指定任何InnoDB日志变量，则默认在MySQL数据目录中创建名为ib_logfile0和ib_logfile1的两个文件。日志文件大小由innodb_log_file_size系统变量给出。可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能。

innodb_force_recovery=1 全局静态参数，默认值为0，一般不调整。如果InnoDB表空间损坏, 设置此值为一个非零值可能导出业务表，从1开始并且增加此值直到能够成功的导出表。

innodb_fast_shutdown 全局动态参数，默认值为1。InnoDB关机模式。如果值为0，InnoDB则在关闭之前会进行慢速关闭、完全清除和更改缓冲区合并。如果值为1，则InnoDB在关机时跳过这些操作，该过程称为快速关机。如果值为2，则InnoDB刷新其日志并冷关机，就好像MySQL崩溃了：没有提交的事务丢失，但是崩溃恢复操作使下次启动需要更长的时间。在仍然缓冲大量数据的极端情况下，缓慢关闭可能需要几分钟甚至几小时。在MySQL主要版本之间升级或降级之前使用慢速关闭技术，以便在升级过程更新文件格式时做好所有数据文件的准备。在紧急情况或故障排除情况下使用innodb_fast_shutdown=2，以在数据存在损坏风险时获得绝对最快的关闭速度。

#*** performance_schema相关设置 ***#

performance_schema=1 全局静态参数，默认开启。是否启用性能模式，会在performance_schema数据库中记录相关的信息。

#*** innodb monitor相关设置 ***#

#innodb monitor 全局动态参数。启用InnoDB指标计数器。可以使用INFORMATION_SCHEMA.INNODB_METRICS表格查询计数器数据。配置在my.cnf中。

innodb_monitor_enable="module_innodb"

innodb_monitor_enable="module_server"

innodb_monitor_enable="module_dml"

innodb_monitor_enable="module_ddl"

innodb_monitor_enable="module_trx"

innodb_monitor_enable="module_os"

innodb_monitor_enable="module_purge"

innodb_monitor_enable="module_log"

innodb_monitor_enable="module_lock"

innodb_monitor_enable="module_buffer"

innodb_monitor_enable="module_index"

innodb_monitor_enable="module_ibuf_system"

innodb_monitor_enable="module_buffer_page"

innodb_monitor_enable="module_adaptive_hash"

[mysqldump]

quick 不要缓存结果，逐行打印。支持较大数据库的转储，在导出非常巨大的表时需要此项。

max_allowed_packet=32M 增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。如果使用大的BLOB值，而且未为mysqld授予为处理查询而访问足够内存的权限，也会遇到与大信息包有关的问题。如果怀疑出现了该情况，请尝试在mysqld_safe脚本开始增加ulimit -d 256000，并重启mysqld。

数据库对象是数据库的组成部分，常见的有以下几种： 表（Table ）、索引（Index）、视图（View）、图表（Diagram）、缺省值（Default）、规则（Rule）、触发器（Trigger）、存储过程（Stored Procedure）、 用户（User）等。 命名规范是指数据库对象如数据库（SCHEMA）、表（TABLE）、索引（INDEX）、约束（CONSTRAINTS）等的命名约定。

1、命名使用具有意义的英文词汇，词汇中间以下划线分隔。

2、命名只能使用英文字母、数字、下划线，以英文字母开头。

3、避免用MySQL的保留字如：backup、call、group等，参考MySQL 5.7+的关键字和保留字。

4、所有数据库对象使用小写字母，实际上MySQL中是可以设置大小写是否敏感的，为了保证统一性，规范全部用小写字母。

1、数据库命名尽量不超过30个字符。

2、数据库命名一般为项目名称+代表库含义的简写，比如IM项目的工作流数据库，可以是 im_flow。

3、数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8。

4、命名应使用小写。

1、常规表表名以t_开头，t代表table的意思，命名规则即 t + 模块（包含模块含义的简写）+ 表（包含表含义的简写），比如用户模块的教育信息表：t_user_eduinfo。

2、临时表（RD、QA或DBA同学用于数据临时处理的表），命名规则：temp前缀+模块+表+日期后缀：temp_user_eduinfo_20210719。

3、备份表（用于保存和归档历史数据或者作为灾备恢复的数据）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20210719。

4、同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。

5、多个单词以下划线 _ 分隔。

6、常规表表名不超过30个字符，temp表和bak表视情况而定，尽量简短为宜，命名应使用小写。

1、字段命名需要表示其实际含义的英文单词或简写，单词之间用下划线 _ 进行连接，如 service_ip、service_port。

2、各表之间相同意义的字段必须同名，比如a表和b表都有创建时间，应该统一为create_time，不一致会很混乱。

3、多个单词以下划线 _ 分隔。

4、字段名尽量不超过30个字符，命名应该使用小写。

1、唯一索引使用uni + 字段名 来命名

> create unique index uni_uid on t_user_basic(uid);

2、非唯一索引使用idx + 字段名 来命名

> create index idx_uname_mobile on t_user_basic(uname,mobile);

3、多个单词以下划线 _ 分隔。

4、索引名不超过50个字符，命名使用小写，组合索引的字段不宜太多，不然也不利于查询效率的提升。

5、多单词组成的列名，取尽可能代表意义的缩写，如 test_contact表member_id和friend_id上的组合索引：idx_mid_fid。

6、理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)。

1、视图名以v开头，表示view，完整结构是v+视图内容含义缩写。

2、如果视图只来源单个表，则为v+表名。如果视图由几个表关联产生就用v+下划线（_）连接几个表名，视图名不超过30个字符。

3、如无特殊需要，严禁开发人员创建视图。

4、命名应使用小写。

1、存储过程名以sp开头，表示存储过程（storage procedure）。之后多个单词以下划线（_）进行连接。存储过程命名中应体现其功能。存储过程名不能超过30个字符。

2、存储过程中的输入参数以i_开头，输出参数以o_开头。

3、命名应使用小写。

> create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100));

1、函数名以func开始，表示function。之后多个单词以下划线（_）进行连接，函数命名中应体现其功能。函数名不超过30个字符。

2、命名应使用小写。

> create function func_format_date(ctime datetime)

1、触发器以trig开头，表示trigger 触发器。

2、基本部分，描述触发器所加的表，触发器名不超过30个字符。

3、后缀（_i,_u,_d）,表示触发条件的触发方式（insert,update或delete）。

4、命名应使用小写。

> DROP TRIGGER IF EXISTS trig_attach_log_d;

> CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

1、唯一约束：uk_表名称_字段名。uk是UNIQUE KEY的缩写。比如给一个部门的部门名称加上唯一约束，来保证不重名，如下：

> ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

2、外键约束：fk_表名，后面紧跟该外键所在的表名和对应的主表名（不含t_）。子表名和父表名用下划线(_)分隔。如下：

>ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3、非空约束：如无特殊需要，建议所有字段默认非空(not null)，不同数据类型必须给出默认值(default)，如下：

Create table emp(

id int(11) NOT NULL,

name varchar(30) DEFAULT '',

deptId int(11) DEFAULT 0,

salary float DEFAULT NULL,

primary key(id));

4、出于性能考虑，如无特殊需要，建议不使用外键。参照完整性由代码控制。这个也是普遍的做法，从程序角度进行完整性控制，但是如果不注意，也会产生脏数据。

5、命名应使用小写。

1、生产使用的用户命名格式为 code_应用。

2、只读用户命名规则为 read_应用。

Windows默认情况下无法建立大写库名

Linux大小写敏感，MySQL数据文件（库名、表名、表别名严格区分大小写）

Linux查询大小写敏感

Windows查询大小写不敏感

MySQL 5.6，lower_case_table_names，0：表示区分大小写、1：表示不区分大小写

MySQL 5.7，lower_case_table_names，2：表示区分大小写、1：表示不区分大小写

统一命名使用全小写字母和下划线分割

列名与列表名在所有情况下忽略大小写

1. 在读表的时候，尽可能的避免全表扫描，合理的根据业务需求，在where及order by涉及的列上建立索引。

2. 应尽量避免在where字句中使用!= 或 操作符，否则将引擎会放弃索引而走全表扫描。

3. 尽量避免where字句中对字段进行null值判断，否则也会导致引擎放弃索引而走全表扫描。可以用0代替判断，前提是保证字段不能为null。

4. 尽量避免在where字句中用or拼接，否则也会走全表扫描。可以通过union all 拼接代替。

5. 尽量不适用Like做搜索查询，避免%xxx式查询。

6. 尽量不适用In 或 Not in查询，否则会导致全表扫描。对于连续的数字，可以用between 代替 in。比如：select id from t where num between 1 and 3

7. 如果在where字句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。比如：select id from t where num=@num ** 可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num**

8. 尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。比如：select id from t where num/2=100

9. 应尽量避免在where子句中对字段进行函数操作。

10. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

12. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

13. 应尽可能的避免更新 clustered （聚集）索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。一个表只能有一个聚集索引，比如表中的时间列。

14. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

15. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

16. 尽量不要select查询*全部信息，只读取所需要的字段。

17. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

18. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

百万级别配置参考 [mysqld] port = 3306 socket = /tmp/mysql.sock user = mysql server_id　　= 10 datadir　　= /data/mysql/ old_passwords　　= 1 lower_case_table_names　　= 1 character-set-server　　= utf8 default-storage-engine　　= INNODB log-bin　　= bin.log log-error　　= error.log pid-file　　= mysql.pid long_query_time　　= 2 slow_query_time　　= 2 slow_query_log　 slow_query_log_file　　= slow.log binlog_cache_size　　= 4MB binlog_format　　= mixed max_binlog_cache_size　　= 16MB max_binlog_size　　= 1GB expire_logs_days　　= 30 ft_min_word_len　　= 4 back_log　　= 512 max_allowed_packet　　= 64MB max_connections　　= 4096 max_connect_errors　　= 100 join_buffer_size　　= 2MB read_buffer_size　　= 2MB read_rnd_buffer_size　　= 2MB sort_buffer_size　　　　= 2MB query_cache_size　　= 2MB table_open_cache　　= 10000 thread_cache_size　　= 256 max_heap_table_size　　= 64MB tmp_table_size　　= 64MB thread_stack　　= 192KB thread_concurrency　　= 24 local-infile　　= 0 skip-show-database skip-name-resolve skip-external-locking connect_timeout　　= 600 interactive_timeout　　= 600 wait_timeout　　= 600 innodb_buffer_pool_size　　= 64G innodb_additional_mem_pool_size　　= 32MB innodb_data_file_path　　=　ibdata1:1G;ibdata2:1G:autoextend innodb_read_io_threads　　= 8 innodb_write_io_threads 　　= 8 innodb_file_per_table　　= 1 innodb_flush_log_at_thx_commit　　= 2 innodb_lock_wait_timeout　　= 120 innodb_log_buffer_size　　= 8MB innodb_log_file_size　　= 256MB innodb_log_files_in_group　　= 3 innodb_max_dirty_pages_pct　　= 90 innodb_thread_concurrency　　= 16 innodb_open_files　　= 10000 #innodb_force_recovery　　= 4

#replication slave read-only #skip-salve-start relay-log　　= relay.log log-slave-updates