mysql5.7性能提升一百倍调优宝典

栏目: IT技术 · 发布时间: 4年前

内容简介：全文中一共有常用的（事实上你如果花1-2周阅读、理解、自己动手设一下后是需要这么多参数的）76个参数，笔者把近10年里3个亿万级项目的数据库调优用此篇浓缩到了可能读者只需要2周时间就可以掌握，同时我是按照：这种style来写的，相信此篇会对一些使用mysql的尤其是正在或者将要面临万级并发的项目、网站有所帮助。具体请看文档！一千个DBA就有一千种配置方式!

全文中一共有常用的（事实上你如果花1-2周阅读、理解、自己动手设一下后是需要这么多参数的）76个参数，笔者把近10年里3个亿万级项目的数据库调优用此篇浓缩到了可能读者只需要2周时间就可以掌握，同时我是按照：

每一个参数干吗？
在某些典型硬件配置下的db上参数该设多少？
设会怎么样？
不设会怎么样？
有什么坑如何填坑？
有些参数怎么算、算法又如何

这种style来写的，相信此篇会对一些使用 mysql 的尤其是正在或者将要面临万级并发的项目、网站有所帮助。具体请看文档！

一千个DBA就有一千种配置方式!

大家一定记得不要轻易去看网上，要看只看官网！网上很多博客都是错的，连参数都列错了，5.7很多参数和5.6是完全不一样的。

可能你从未看到过这样的一篇集中火力式的把mysql参数列了这么全的文章，很有兴曾参与过超3万并发的18～19年的数轮520、618、双11、双12保卫战。因此这一篇是汇集了最精华和实战的内容把mysql所有的参数列在这边供大家参考。并且以（64c cpu，128gb内存）的mysql cpu和内存来进行了一轮配置。而此文的内存相关参数部分可以延展至256gb～512gb。

另外有一点， 建议在mysql的服务器上使用ssd 。除非并发数永远控制在500-1000内那就没必要使用ssd，普通高速磁盘就可以了。

你会发觉这篇文章是一篇宝藏，这些参数都能够自己动手试验一篇基本在外面是可以吊打mysql面试官了。

client域：

1）character_set_client

作用：

字符集设定，如果前台有连social mobile application一类包括wechat，并且允许有使用emoji表情的，请开启成utf8mb4

如果不配的后果：

mysql不支持前端app存表情等字符

配置实例：

character_set_client=utf8mb4

mysqld域：

1)server-id

作用：

遇有主从复制，必设该值，每个参与主从复制的mysql实例的server-id不能重复，必须为阿拉伯数字。

如果不配的后果：

如果你用的是主从复制，这个id不设那么整个mysql的主从复制会失几。

配置实例：

server-id=1

2)port

作用：

mysql实例端口

如果不配的后果：

默认为3306

配置实例：

port=3306

3)bind_address

作用：

除非有特殊需要，我们会限制只允许mysql实例被某一个ip方问，不支持多个，生产上都为：0.0.0.0然后使用防火墙策略来控制。

如果不配的后果：

默认不允许远程登录

配置实例：

bind_address=0.0.0.0

4)autocommit

作用：

生产上开启成1，如果你开启的是0会有一个这样的情况：

a运行一条insert语句，并未作commit;b去做查询此时b是查询不到的。这种操作一般用于在写store procedure时用到。

如果不配的后果：

如果在系统的my.cnf层面把它设成了0，如果在使用时（99%情况是用的1）时，你想要用root在生产运行时把它设成set autocommit = 1都开启不了。而如果你在一开始就没它设置成1，那么当碰到某些特殊场景特别是写store procedure时需要把它设成0时，你是可以手动临时把某一个session给开在0的。

配置实例：

autocommit = 1

5)character_set_server

作用：

字符集设定，如果前台有连social mobile application一类包括wechat，并且允许有使用emoji表情的，请开启成utf8mb4

如果不配的后果：

mysql不支持前端app存表情等字符

配置实例：

character_set_server=utf8mb4

6)skip_name_resolve

作用：

生产上建议开启成1，这样mysql server不会对客户端连接使用反向dns解析，否则客户端连上后有时在遇有生产高速运行时直接timeout，如果设成了1带来的问题就是你不能在mysql中使用主机名来对客户端权限进行划分，而是需要使用ip。

如果要做成即允许mysql里允许使用主机名来分配客户端连接权限，又要做到不要让mysql去做dns解析，可以在mysql所在主机端的/etc/hosts文件中写上客户端的主机名，因为当客户端连接连上来时，mysql反向查找客户端连接时的域名解析的步骤是：首先查找 /etc/hosts 文件，搜索域名和IP的对应关系。但是这样做也有一个问题，那就是如果你有多个客户端多个mysql主从关系，哪到你要把mysql做成一个dns解析器吗？因此推荐设成1

如果不配的后果：

mysql server每一次会对客户端连接使用反向dns解析，经常会出现客户端连上后有timeout现象。

配置实例：

skip_name_resolve=1

7)max_connections

作用：

最大连接数，以微品会：前端3万的tps并发，假设 redis 命中失效50%（这是灾难），那么后端mysql单个主或从开启连接数为：20,000，我们公司在前端并发曾达到过6万，80%被waf、vanish、缓存挡掉，落在db上的qps最高一次为20,000连接，再按照mysql官方，max_connections值受系统os最大打开连接数限制，因此我们需要做以下2步操作：

1）在 /etc/security/limits.conf 底部增加2行

mysql hard nofile 65535

mysql soft nofile 65535

2）在/usr/lib/systemd/system/mysqld.service（视如何安装mysql所决定，用编译安装和yum安装会产生path路径不同。）文件最后添加：

LimitNOFILE=65535

LimitNPROC=65535

$ systemctl daemon-reload

$ systemctl restart mysqld.service

如不生效重服务器。

如果不配的后果：

默认只有150

配置实例：

max_connections = 20,000

8)max_connect_errors

作用：

生产上开启成10次，开发测试上使用默认即不设。

max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。如果需要设置此数值，手动添加。当此值设置为10时，意味着如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）10次，则MySQL会无条件强制阻止此客户端连接。相关的登录错误信息会记录到performance_schema.host_cache表中。如果希望重置此计数器的值，则必须重启MySQL服务器或者执行

Mysql> FLUSH HOSTS;

1 Mysql> FLUSH HOSTS;

当这一客户端成功连接一次MySQL服务器后，针对此客户端的max_connect_errors会清零。可以在防火墙上做策略限制某些ip的远程连接。

如果不配的后果：

默认为100

配置实例：

max_connect_errors =10

9)innodb_flush_log_at_trx_commit

作用：

(核心交易系统设置为1，默认为1，其他2或者0)，

0代表：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1代表：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认（因此会保留每一份redo日志）

2代表：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

除非你用的是小型机或者是超大规模mysql集群一类如：游戏行业，那么需要保留每一秒的事务，否则请设成2，要不然会严重影响系统性能。这个参数是5.6所没有的。

如果不配的后果：

默认为1，影响系统写性能。

配置实例：

innodb_flush_log_at_trx_commit=2

10)transaction_isolation

如果不配的后果：

默认就是repeatable-read

配置实例：

transaction_isolation = READ-COMMITTED

11)explicit_defaults_for_timestamp

如果不配的后果：

默认为0

配置实例：

explicit_defaults_for_timestamp = 1

12)join_buffer_size

作用：

系统默认大小为：512k，mac下默认大小为：256k，针对128GB，1万并发的mysql我推荐给到的值为：8~16M

对于JOIN KEY 有索引和二级索引，JOIN KEY 无索引mysql会使用到join_buffer_size，一般建议设置一个很小的 GLOBAL 值，完了在 SESSION 或者 QUERY 的基础上来做一个合适的调整。如果你拍脑袋给也个4g，我们有1000个并发，就是用掉了4T的内存。。。4T啊。。。你以为你是小型机。适当的去改变它确实可以带来一定的提速，但并不是说很多值越大越好，为什么我们设置成4m呢？我们假设我们的mysql所在的vm是128gb，一根这样的join（如果被用到）是4M，1万个也不过用掉40G,而根据官方说法，total加在一起产生的join_buffer_size不要超过你所在系统的50%.默认512k肯定是小了点，我们可以适当放宽，比如说：2M，在实际使用场景时我们发觉有这样的高频操作（要看高频出现的有意义的sql的执行计划，并确认该计划的：执行cost如： "query_cost": "1003179606.87"，它产生的cost为：0.93个G,如果它真的很高频出现在调优sql到无法调优的程度，我们会去做set session join_buffer_size = 1024 * 1024 * 1024;这样的操作。而不是在一开始的my.cnf中去分配一个暴大的值，我们这边基于128gb，1万connection的并发来说，你给个16M不算小也不算多，我推荐给到8~16M间（这是指在一开始）。

如果不配的后果：

默认的为256k

配置实例：

join_buffer_size = 16M

13)tmp_table_size

作用：

如果是128gb内存的服务器，我建议是在my.cnf中设成64M

通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。默认系统为32M，如果当你的临时表越来越多加在一起超过了这个值，那么mysql会在系统磁盘上创建，这个值不是越多越好，也没有一个合适的值。一开始的建议为>64M，然后在运行时我们通过以下公式来做临时调优，

show global status like 'created_tmp%';

把得到的结果中的：(Created_tmp_disk_tables / Created_tmp_tables) * 100% 如果<=25%为最佳值。注意了，在生产时热设定时一定要用类似以下算法：

set global tmp_table_size=64*1024*1024而不是set global tmp_table_size=64M。

如果不配的后果：

默认为32M

配置实例：

tmp_table_size = 67108864

14)tmpdir

这块参数可以让运维给到，放到大空间里就行了，没什么太敏感的。

15)max_allowed_packet

作用：

如果你经常在应用层碰到了：Got a packet bigger than'max_allowed_packet' bytes，这时你可以使用

show variables like '%max_allowed_packet%';来查看这个值，这个值没有合适，一般如：用客户端导入数据的时候，遇到错误代码: 1153 - Got a packet bigger than 'max_allowed_packet' bytes 终止了数据导入。这样的场景下，当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时，将发出“信息包过大”错误，并关闭连接。对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与MySQL服务器的连接”错误。

客户端和服务器均有自己的max_allowed_packet变量，因此，如你打算处理大的信息包，必须增加客户端和服务器上的该变量。一般情况下，服务器默认max-allowed-packet为1MB,可以通过在交换机上抓包或者是图形化分析来抓返回结果判断。一般推荐在128gb内存下设置的置为128M.也可以在运行时动态调整：set global max_allowed_packet = 128*1024*1024

如果不配的后果：

配置实例：

max_allowed_packet = 134217728

16)sql_mode

不需要去设置，使用默认的，这块和性能无关。我们的中台中的sql如果碰到有sql报错，因该是在测试环境上就已经报了，它的作用是用来约束你sql的写法的，如果是一个从头开始开发的应用，我们比如说约束好都是ansi sql写法， 对于一个产品，不要去做这种画蛇添足的做法 。

17)interactive_timeout

作用：

单位为s，系统默认为：28800s即8小时。如果这2个值太大，你会发觉在mysql中有大量sleep的连接，这些连接又被称为：僵尸连接，僵尸连接一多你真正要用的时候就会抛：too many connection这样的错，因此对于长久不用的连接，我们一般要使用“踢出机制”，多久对于一个活动累的sql进行踢呢？我们说如果有一个长事务，它要执行1小时，我不知道这是不是属于正常？当然如果你设了太短，说1分钟就把它踢了，还真不一定踢的对，按照我们在oracle中设置的best practice我们都会把它放到10分钟。你有一条sql连着，10分钟不用，我就把它踢了，这也算正常。但是在高并发的场景下这个timeout会缩短至3-5分钟，这就是为什么我提倡我们的非报表即时类查询需要优化到sql的运行时间不超过300ms的原因，因为在高并发场景下，超过500ms的sql都已经很夸张了。保守点我觉得可以设成10分钏，在应用端由其通过jdbc连接数据库的，做的好的应用都会在jdbc里有一个autoconnect参数，这个autoconnect参数就要和mysql中的wait_timeout来做匹配了。同时在应用端要有相应的validate sql一类的操作来keep alived。不过我更推荐使用”连接池内连接的生存周期（idleConnectionTestPeriod）”来做设置，把这个置设成<mysql内的这两个值将会是最好，同时，idleConnectionTestPeriod会使用到异步的方式去做超时check。如c3p0中的：idleConnectionTestPeriod和testConnectionOnCheckin相当可靠

interactive_timeout：交互式连接超时时间(mysql工具、mysqldump等)

wait_timeout：非交互式连接超时时间，默认的连接mysql api程序,jdbc连接数据库等

interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。

show global variables like 'wait_timeout';

1 timeout 只是针对空闲会话有影响。

2 session级别的wait_timeout继承global级别的interactive_timeout的值。而global级别的session则不受interactive_timeout的影响。

3 交互式会话的timeout时间受global级别的interactive_timeout影响。因此要修改非交互模式下的timeout，必须同时修改interactive_timeout的值。

4 非交互模式下，wait_timeout参数继承global级别的wait_timeout。

如果不配的后果：

系统默认为28800

配置实例：

interactive_timeout = 600

18)wait_timeout

同interactive_timeout， 两个值都设成一样 。

19)read_buffer_size

作用：

这个值其实轻易是用不到的，因为，它只对2种场景的full table scan产生影响而不是所有的full table scan，同时从mysql5.6以后开始没有数据块多块读的功能,与是否设置 read_buffer_size参数无关。应用场景：

1）SELECT INTO … OUTFILE ‘fileName‘

2）When filesort is used, during merge buffers and when merged results are written to a temporary file, then writes are buffered

一般保留默认:64k，保守作法是设置在1～4M，不过它的应用场景很有限，对于互联网场景真的不太用，我推荐设成4M

如果不配的后果：

默认为64k

配置实例：

read_buffer_size = 4194304

20)read_rnd_buffer_size

作用：

就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到 read_rnd_buffer_size 参数所设置的内存缓冲区。它的默认为256k，最大可以设到2G，它会对order by关键字起作用，当order by的计划成本超出了sort_buffer_size后，mysql会产用随机读取并消耗额外的内容，很多外面的博客说它是只对myisam引擎起作用，但其实不是，该参数还真的覆盖到所有引擎，一般它的推荐设置在8-16M，我推荐8M，根据sql分析计划如果碰到高频的查询且order by的返回包体都很大，那么再在session级别去放。

如果不配的后果：

默认为256k

配置实例：

read_rnd_buffer_size = 8388608

21)sort_buffer_size

作用：

每个会话执行排序操作所分配的内存大小。想要增大max_sort_length参数，需要增大sort_buffer_size参数。如果在SHOW GLOBAL STATUS输出结果中看到每秒输出的Sort_merge_passes状态参数很大，可以考虑增大sort_buffer_size这个值来提高ORDER BY 和 GROUP BY的处理速度。建议设置为1~4MB。当个别会话需要执行大的排序操作时，在会话级别增大这个参数。所谓会话级别，我举个例子，你拍脑袋一下，说我设个32M,你所它乘10,000请求，这得多大内存。另外，千万要注意，在mysql内存，当你的sort_buffer_size在超过2K时在底层使用的是mmap()的c函数去做内存分配的，而不是malloc()，做过c的都知道mmap()是一个矢量单位，因此它会付出性能的影响，能影响多少呢？单条sql影响值在30%。

如果不配的后果：

默认值为1M

配置实例：

sort_buffer_size =4194304

22)innodb_page_size

作用：

这个值可要小心，一般它在设置后就不能轻易改了，一般来说我们都认为，值越大越好，不是的，这个值它的原理是这样的：size越小，内存划分粒度越大，使用率越高，但是会有其他问题，就是限制了索引字段还有整行的大小。innodb引擎读取内存还有更新都是一页一页更新的，这个innodb_page_size决定了，一个基本页的大小。常用B+Tree索引，B+树是为磁盘及其他存储辅助设备而设计一种平衡查找树（不是二叉树）。B+树中，所有记录的节点按大小顺序存放在同一层的叶子节点中，各叶子节点用指针进行连接。MySQL将每个叶子节点的大小设置为一个页的整数倍，利用磁盘的预读机制，能有效减少磁盘I/O次数，提高查询效率。如果一个行数据，超过了一页的一半，那么一个页只能容纳一条记录，这样B+Tree在不理想的情况下就变成了双向链表。我们拿白话来说就是：你越大，空间利用率更高，但是越小呢越有助于性能但是这边一定一定有一个“但是”，但是小到一定的量反而性能不好，为什么呢？太大上面我已经举例了，太小。。。mysql的页间的check point太频繁。怎么样做才能达到一个合理值呢？

这个我们是在全真生产环境、全数据量下用测试工具去对4k,8k,16k三种场景压测得到的吞吐量即tps来做观察的，我这边可以给出一个推荐值，以单表超1000w条数据基于中台1.1的数据库结果（每个表都超1000w），我们在设置该值为：8K时，它的吞吐达到最优。

如果不配的后果：

32位下默认为8192

64位下默认为16384

配置实例：

innodb_page_size = 8192

23)innodb_buffer_pool_size

作用：

这个值和innodb_buffer_pool_instances相辅相成。在32位机器下，innodb_buffer_pool_instances一般为1，在64位机器上，这个值为8-64.

pool_instances其实为cpu核数，它的作用是：

1）对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。

2）使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表，刷新列表， LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

innodb_buffer_pool_size的设置需要为pool_instance的整数倍。

网上很多说innodb_buffer_pool_size为系统的70%，这是错的！因为你真的设了70%你的swap空间会被挤压，你不要忘了你还有os，上面还可能有监控agent端。一旦swap空间被挤压后你的mysql反面严重拖慢读写。

此处强烈建议设成内存的20%-65%间（独立的mysql服务器），为什么有一个20%呢？对于<4gb的mysql用服务器来说按照20%系统内存来设置。由于我们是128gb的内存，此处我建议使用72G,如果内存超过128gb，一般我们会把pool instance设成16个，每个开启10g左右的buffer_pool_size，对于256gb内存的服务器来说我们可以这样设。

如果不配的后果：

默认为64

配置实例：

innodb_buffer_pool_size = 72G

24)innodb_buffer_pool_instances = 8

这个参数同innodb_buffer_pool_size一起讲解了。

25)innodb_buffer_pool_load_at_startup

作用：

这两个参数几乎没人用一般dba也不曾听说过，它是什么意思呢？Mysql在第一次（重启）时，它的buffer_pool_size中是空的，随着mysql运行时间1-2小时后，它的buffer_pool_size里开始被塞入东西，它分为old block与new block，而此时mysql性能开始一点点读写效率上去了，那是因为在buffer_pool_size没有放入东西时，mysql很多读写发生在硬盘上，从硬盘到内存的加载过程是一个比较漫长和耗时的过程，因此我们往往会设一个startup=1以加快这个“预热”过程，它与参数shutdown配合使用，即相当于把上次使用的innot_db_buffer_pool里的东西在启动时先做一次加载，以加快mysql的性能。它会在innodb的数据目录中生成一个文件：ib_buffer_pool。高度注意：加入了startup和shutdown=1时，mysql的启动过程会比较慢，如果你上次的dump出的buffer_pool里的东西有50多g那么mysql启动时的加载过程会变得比较慢。这个值很多人使用默认的0（不开启），它的影响就是你在mysql重启后，一开始你的系统读写性能不如在你系统运行了2-4小时（视db读写而定）反而它的读写性能变好了。不设使用默认值（0）。

如果不配的后果：

不配的话系统默认为0

配置实例：

innodb_buffer_pool_load_at_startup = 0

26)innodb_buffer_pool_dump_at_shutdown

同上面的startup参数以及解说

27)innodb_lru_scan_depth

作用：

innodb_io_capactiy 在sas 15000转的下配置800就可以了，在ssd下面配置2000以上。

可使用默认配置。即不设。

如果不配的后果：

默认为200，db吞吐量上不去。

配置实例：

innodb_lru_scan_depth = 2000

28)innodb_lock_wait_timeout

作用：

我们一般会碰到，mysql innodb_lock_wait_timeout这个错，这个错是慢sql导致，它代表的是慢sql的事务锁超过了mysql锁超时的设置了。默认这个值为：50s，这个值是可以动态改变的，我不建议去改这个值，因为一个sql能达50s这得多夸张？

动态改变命令如下：

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

SET GLOBAL innodb_lock_wait_timeout=500;

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

把它设成60s足够了。

如果不配的后果：

默认为50s

配置实例：

innodb_lock_wait_timeout = 60

29)innodb_io_capacity_max

作用：

这个值很重要，它对读无效，对写很有决定意义。

它会直接决定mysql的tps（吞吐性能），这边给出参考：sata/sas硬盘这个值在200. sas raid10: 2000，ssd硬盘：8000， fusion-io（闪存卡）：25,000-50,000

本调优基于的是ssd，此值设置为8000，笔者上一家公司互联网金融是把一整个mysql扔到了闪存卡里的，因此设置的值为：50,000.

需要根据paas或者是ias的vm的硬盘性号来定

如果不配的后果：

默认为200，系统吞吐上不去。

配置实例：

innodb_io_capacity_max = 8000

30)innodb_io_capacity

它是io_capacity_max的一半，同样，它对读无效对写有决定意义。

配置实例：

innodb_io_capacity_max = 4000

31)innodb_flush_method

作用：

推荐使用O_DIRECT。让我们一起来理解一下，它有3种模式：

1）fdatasync，上面最常提到的fsync(int fd)函数，该函数作用是flush时将与fd文件描述符所指文件有关的buffer刷写到磁盘，并且flush完元数据信息(比如修改日期、创建日期等)才算flush成功。它对磁盘的io读写会很频繁

2）O_DIRECT则表示我们的write操作是从mysql innodb buffer里直接向磁盘上写，它会充分利用缓存

3）_DIRECT模式的free内存下降比较慢，因为它是据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲，O_DIRECT在SQL吞吐能力上较好。

如果不配的后果：

它的默认值为fdatasync。

配置实例：

innodb_flush_method = O_DIRECT

32)innodb_file_format

作用：

推荐使用Barracuda模式

它是启用表压缩用的，如：

CREATE TABLE `test_1` (

`x` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

建完后可以通过：show table status like 'test_1';来查看是否已经启用了表压缩了。

innodb_file_format有这么几种模式：

Antelope-羚羊模式，支持Redundant（冗余）、Compact（紧凑）模式
Barracuda-梭子鱼,是InnoDB Plugin支持的文件格式，在原来的基础上新增了两种数据表格式的支持：Dynamic 和 Compressed

因此我推荐使用：Barracude模式，因为它可以兼容其它数据模式。

它也可以在运行时动态改变：SET GLOBAL innodb_file_format_max = barracuda;

如果不配的后果：

它默认使用的是叫“联合模式”，即不是棱子鱼也不是羚羊。

配置实例：

innodb_file_format = Barracuda

33)innodb_file_format_max

这个参数必须和innodb_file_format参数一致，一定记住，要不然不生效。

34)innodb_log_group_home_dir = /redolog/

这个就不用解释了，太傻瓜了。这种路径的都可由运维决定，记得挂在大磁盘下。

35)innodb_undo_directory = /undolog/

这个就不用解释了，太傻瓜了。这种路径的都可由运维决定，记得挂在大磁盘下。

36)innodb_undo_logs = 128

作用：

指定回滚段的个数（早期版本该参数名字是innodb_rollback_segments），默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。现在SSD非常普及。innodb_undo_logs可以默认为128不变。

如果不配的后果：

默认就是128

配置实例：

innodb_undo_logs = 128

37)innodb_undo_tablespaces

作用：

如果不配的后果：

默认为：3

配置实例：

innodb_undo_tablespaces = 3

38)innodb_flush_neighbors

作用：

这个参数很要紧，目前在ssd盛行的情况下我们都把它设为0（不开启），如果你设置成了1即开启（默认状态）InnoDB就会刷新一个extent中的所有页面，因为SSD在随机IO上没有额外负载，所以不需要启用该特性， 开启了反而多此一句 。下面给出一段mysql5.7源码编译前程序员看的readme里的一句话：

This new default changes MySQL to cater for SSDs and fast storage devices by default. We expect that for the majority of users, this will result in a small performance gain. Users who are using slower hard drives may see a performance loss, and are encouraged to revert to the previous defaults by setting innodb_flush_neighbors=1.

如果不配的后果：

它的默认是1，不是0.这个参数对机性硬盘来说很有效，可以减少随机io，增加性能。如果是ssd类磁盘，建议设置为0，可以更快的刷新脏页。如果你把它设为1同时又是ssd那就显得没必要了。这边普及一下小知识，如果你装过8.0，你可以去看一下，8.0已经把这个默认值设为0了。

配置实例：

innodb_flush_neighbors = 0

39)innodb_log_file_size

作用：

这个值的默认为5M，是远远不够的，在安装完mysql时需要尽快的修改这个值。

如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了，就会增加恢复的时间，因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复。

而这个值是没有一个绝对的概念的，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空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。我们是通过“测试”得到，怎么测试下面给出方法论：一般情况下我们可以按照每1GB的Redo log的恢复时间大约在5分钟左右来估算。如果恢复时间对于你的使用环境来说很重要，我建议你做一些模拟测试，在正常工作负载下（预热完毕后）模拟系统崩溃，来评估更准确的恢复时间。你可以安装 Percona Monitoring and Management，在该pmm的percona monitoring and management图表中，主要看：

1）Uncheckpointed Bytes ，如果它已经非常接近 Max Checkpoint Age，那么你几乎可以确定当前的 innodb_log_file_size 值因为太小已经某种程度上限制了系统性能。增加该值可以较为显著的提升系统性能。

2）Uncheckpointed Bytes 远小于 Max Checkpoint Age，这种情况下再增加 innodb_log_file_size 就不会有明显性能提升。

在调整完log_file_size后我们再到pmm中去看：Redo Log空间指标，比如说我们看到了1小时内有60g数据被写入日志文件，差不多就是每10分钟会有10g数据在进行“写日志“，我们需要牢牢记得，这个”写日日土已“的时间拖得越久、出现的频次越少就越有助于mysql的innodb的性能。因此这个值没有绝对推荐。如果你没有pmm，那么我们来人肉算，在上面我已经给出了人肉算的详细例子！

如果不配的后果：

默认是5M，这是肯定不够的。

配置实例：

innodb_log_file_size = 140M

40)innodb_log_buffer_size

作用：

对于较小的innodb_buffer_pool_size，我们会把它设成和innodb_buffer_pool_size一样。

而当超过4gb的innodb_buffer_pool_size时，我们的建议是把它切的够碎，这是mysql5.7里新带的特性，它的默认在8m，但是对于大量有事务操作的mysql我们推荐在写操作库上设置：16m

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据.官方的方档建议设置为1－8M之间！

如果不配的后果：

默认是8M

配置实例：

innodb_log_buffer_size = 16777216

41)innodb_purge_threads

作用：

这个参数轻易不用的，我推荐它设为：0，为什么呢？这个参数是和innodb_force_recovery关联起来的，只有当数据库崩溃后重启时才会临时去设的。它的使用场景如下：

mysql断电，重启后无效，起不来。所以我们根据innodb_force_recovery的参数：

1. (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。

2. (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。

3. (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。

4. (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。

5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。

6. (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

我们在my.cnf中如下设置：

innodb_force_recovery = 6

innodb_purge_threads = 0

记住，一旦当innodb_force_recovery>2时，要把innodb_purge_threads设成0 .

如果不配的后果：

默认不要去设，可以不配，出现了问题在recover需要时再去改。

配置实例：

innodb_purge_threads = 0

42)innodb_large_prefix

作用：

如果你的客户端和服务端的字符集设成了utf8mb4，那么我们需要把这个开关开启，为什么呢？mysql在5.6之前一直都是单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.6以后，开始支持4个字节的uutf8。255×4>767, 于是增加了这个参数。这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072.

在mysql5.6中这个开关叫on, off。而在5.7中叫0和1，由于我们前面设置了utf8mb4，因此这边我们必须把这个参数开启。

如果不配的后果：

不配会有问题，特别是索引会无效、或者不是走最优计划，如果你的字符集是utf8mb4，那么这个值必开启。

配置实例：

innodb_large_prefix = 1

43)innodb_thread_concurrency

作用：

如：64核cpu，那么推荐：64（<=cpu核数）

如果一个工作负载中，并发用户线程的数量小于等于64，建议设置innodb_thread_concurrency=0;而事实上我们的系统是处于大并发大事务的情况下的，怎么来算这个值？建议是先设置为128，然后我们不断的降这个值，直到发现能够提供最佳性能的线程数。为了安全起间我们会把它设成和cpu一样大小。

如果不配的后果：

默认在64位下会是8

配置实例：

innodb_thread_concurrency = 64

44)innodb_print_all_deadlocks

作用：

如果不配的后果：

不会记录该信息。

配置实例：

innodb_print_all_deadlocks = 1

45)innodb_strict_mode

如果不配的后果：

如果不配碰到开发或者非专业的dba会把旧ddl语句生效在5.7内，另外一个问题就是ddl语句出错时报错不明显，这会影响到“主从复制”，至于dll为什么会影响到主从复制，我们后面会在“slave_skip_errors = ddl_exist_errors”中详细解说。

配置实例：

innodb_strict_mode = 1

46)log_error

error log所在位置，这个不用多讲，可以和mysql log放在同一路径下，文件名能够和其它log区分开来。

47)slow_query_log

建议开启

48)slow_query_log_file

慢sql所在位置，这个不用多讲，可以和mysql log放在同一路径下，文件名能够和其它log区分开来。

49)log_queries_not_using_indexes=1

强烈建议开启成1.

50)log_slow_admin_statements = 1

强烈建议开启成1.

51)log_slow_slave_statements = 1

强烈建议开启成1.

52)log_throttle_queries_not_using_indexes

配置实例：

log_throttle_queries_not_using_indexes = 50

53)expire_logs_days

54)long_query_time

55)min_examined_row_limit

56)master_info_repository

57)relay_log_info_repository

如果不配的后果：

这个参数和上面的master_info_repository必须保持一致，要不然mysql实例启不起来

配置实例：

relay_log_info_repository = TABLE

58)log_bin = bin.log

主从复制时用，主从复制下的bin.log日志所在文件夹。

59)sync_binlog

如果不配的后果：

默认为0

配置实例：

sync_binlog = 1

60)gtid_mode

61)enforce_gtid_consistency

如果不配的后果：

必须跟着gtid_mode一起开启，要不然mysql实例起不来。

配置实例：

enforce_gtid_consistency = 1

62)log_slave_updates

63)binlog_format

64)relay_log

主从复制用，定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录（datadir），文件名为host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）

65)relay_log_recovery

推荐设置：

作用：

主从复制用， 推荐值为1 ，建议打开。

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能， 建议开启 。

如果不配的后果：

默认情况下是关闭的。

配置实例：

relay_log_recovery = 1

66)slave_skip_errors

如果不配的后果：

如果因为建表语句和mysql5.7有冲突时在单实例模式下mysql运行时不会发现，在主从复制时如果没有设跳过值，一旦发生，会影响主从复制，表现就是：主从复制失败。

配置实例：

slave_skip_errors = ddl_exist_errors

67)innodb_buffer_pool_dump_pct

如果不配的后果：

不配的话不生效。

配置实例：

innodb_buffer_pool_dump_pct=25

68)innodb_page_cleaners=8

这值一般会在主从延迟的情况下会去设，它的值最好是=innodb_buffer_pool_instance的值，它就是cpu的核数。

69)innodb_undo_log_truncate

如果不配的后果：

不配的话是不生效的。

配置实例：

innodb_undo_log_truncate=1

70)innodb_max_undo_log_size

如果不配的后果：

系统按照1GB来计算。

配置实例：

innodb_max_undo_log_size=2G

71)innodb_purge_rseg_truncate_frequency

72)binlog_gtid_simple_recovery

如果不配的后果：

默认为0

配置实例：

binlog_gtid_simple_recovery=1

73)log_timestamps

如果不配的后果：

默认值为:UTC

配置实例：

log_timestamps=system

74)transaction_write_set_extraction

75)show_compatibility_56

算法精解

Kyle Loudon / 肖翔、陈舸 / 机械工业出版社 / 2012-8 / 79.00元

本书是数据结构和算法领域的经典之作，十余年来，畅销不衰！全书共分为三部分：第一部分首先介绍了数据结构和算法的概念，以及使用它们的原因和意义，然后讲解了数据结构和算法中最常用的技术——指针和递归，最后还介绍了算法的分析方法，旨在为读者学习这本书打下坚实的基础；第二部分对链表、栈、队列、集合、哈希表、堆、图等常用数据结构进行了深入阐述；第三部分对排序、搜索数值计算、数据压缩、数据加密、图算法、几何算法......一起来看看《算法精解》这本书的介绍吧!

码农工具