MySQL5.7数据库参数优化
连接相关参数
max_connections：允许客户端并发连接的最⼤数量，默认值是151，⼀般将该参数设置为500-2000
max_connect_errors：如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻⽌新连接的状态，通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。这个参数的默认值是100，⼀般将该参数设置为100000。
interactive_timeout：Mysql关闭交互连接前的等待时间，单位是秒，默认是8⼩时，建议不要将该参数设置超过24⼩时，即86400
wait_timeout：Mysql关闭⾮交互连接前的等待时间，单位是秒，默认是8⼩时，建议不要将该参数设置超过24⼩时，即86400
skip_name_resolve：如果这个参数设为OFF，则MySQL服务在检查客户端连接的时候会解析主机名；如果这个参数设为ON，则MySQL服务只会使⽤IP，在这种情况下，授权表中的Host字段必须是IP地址或localhost。
这个参数默认是关闭的
back_log：MySQL服务器连接请求队列所能处理的最⼤连接请求数，如果队列放满了，后续的连接才会拒绝。当主要的MySQL线程在很短时间内获取⼤量连接请求时，这个参数会⽣效。接下来，MySQL主线程会花费很短的时间去检查连接，然后开启新的线程。这个参数指定了MySQL的TCP/IP监听队列的⼤⼩。如果MySQL服务器在短时间内有⼤量的连接，可以增加这个参数。
⽂件相关参数sync_binlog：控制⼆进制⽇志被同步到磁盘前⼆进制⽇志提交组的数量。当这个参数为0的时候，⼆进制⽇志不会被同步到磁盘；当这个参数设为0以上的数值时，就会有设置该数值的⼆进制提交组定期同步⽇志到磁盘。当这个参数设为1的时候，所有事务在提交前会被同步到⼆进制⽇志中，因⽽即使MySQL服务器发⽣意外重启，任何⼆进制⽇志中没有的事务只会处于准备状态，这会导致MySQL服务器⾃动恢复以回滚这些事务。这样就会保证⼆进制⽇志不会丢失事务，是最安全的选项；同时由于增加了磁盘写，这对性能有⼀定降低。将这个参数设为1以上的数值会提⾼数据库的性能，但同时会伴随数据丢失的风险。建议将该参数设为2、4、6、8、16。
expire_logs_days：⼆进制⽇志⾃动删掉的时间间隔。默认值为0，代表不会⾃动删除⼆进制⽇志。想⼿动删除⼆进制⽇志，可以执⾏PURGE BINARY LOGS。
max_binlog_size：⼆进制⽇志⽂件的最⼤容量，当写⼊的⼆进制⽇志超过这个值的时候，会完成当前⼆进制的写⼊，向新的⼆进制⽇志写⼊⽇志。这个参数最⼩值时4096字节；最⼤值和默认值时1GB。相同
事务中的语句都会写⼊同⼀个⼆进制⽇志，当⼀个事务很⼤时，⼆进制⽇志实际的⼤⼩会超过max_binlog_size参数设置的值。如果max_relay_log_size参数设为0，则max_relay_log_size参数会使⽤和
max_binlog_size参数同样的⼤⼩。建议将此参数设为512M。
local_infile：是否允许客户端使⽤LOAD DATA INFILE语句。如果这个参数没有开启，客户端不能在LOAD DATA语句中使⽤LOCAL参数。open_files_limit：操作系统允许MySQL服务打开的⽂件数量。这个参数实际的值以系统启动时设定的值、max_connections和
table_open_cache为基础，使⽤下列的规则：
1. 10 + max_connections + (table_open_cache * 2)
2.  max_connections * 5
3. MySQL启动时指定open_files_limit的值
缓存控制参数binlog_cache_size：在事务中⼆进制⽇志使⽤的缓存⼤⼩。如果MySQL服务器⽀持所有的存储引擎且启⽤⼆进制⽇志，每个客户端都会被分配⼀个⼆进制⽇志缓存。如果数据库中有很多⼤的事务，增⼤这个缓存可以获得更好的性能。
Binlog_cache_use和Binlog_cache_disk_use这两个参数对于binlog_cache_size参数的优化很有⽤。binlog_cache_size参数只设置事务所使⽤的缓存，⾮事务SQL语句所使⽤的缓存由binlog_stmt_cache_size系统参数控制。建议不要将这个参数设为超过64MB，以防⽌客户端连接多⽽影响MySQL服务的性能。
max_binlog_cache_size：如果⼀个事务需要的内存超过这个参数，服务器会报错"Multi-statement transaction required more than
'max_binlog_cache_size' bytes"。这个参数最⼤的推荐值是4GB，这是因为MySQL不能在⼆进制⽇志设为超过4GB的情况下正常的⼯作。建议将该参数设为binlog_cache_size*2。
mysql group by order bybinlog_stmt_cache_size：这个参数决定⼆进制⽇志处理⾮事务性语句的缓存。如果MySQL服务⽀持任何事务性的存储引擎且开启了⼆进制⽇志，每个客户端连接都会被分配⼆进制⽇志事务和语句缓存。如果数据库中经常运⾏⼤的事务，增加这个缓存可以获得更好的性能。table_open_cache：所有线程能打开的表的数量。
thread_cache_size：MySQL服务缓存以重⽤的线程数。当客户端断开连接的时候，如果线程缓存没有使⽤满，则客户端的线程被放⼊缓存中。如果有客户端断开连接后再次连接到MySQL服务且线程在缓存中，则MySQL服务会优先使⽤缓存中的线程；如果线程缓存没有这些线程，则MySQL服务器会创建新
的线程。如果数据库有很多的新连接，可以增加这个参数来提升性能。如果MySQL服务器每秒有上百个连接，可以增⼤thread_cache_size参数来使MySQL服务器使⽤缓存的线程。通过检查Connections和Threads_created状态参数，可以判断线程缓存是否⾜够。这个参数默认的值是由下⾯的公式来决定的：8 + (max_connections / 100)
建议将此参数设置为300~500。线程缓存的命中率计算公式为(1-thread_created/connections)*100%，可以通过这个公式来优化和调整
thread_cache_size参数。
query_cache_size：为查询结果所分配的缓存。默认这个参数是没有开启的。这个参数的值应设为整数的1024倍，如果设为其他值则会被⾃动调整为接近此数值的1024倍。这个参数最⼩需要40KB。建议不要将此参数设为⼤于256MB，以免占⽤太多的系统内存。
query_cache_min_res_unit：查询缓存所分配的最⼩块的⼤⼩。默认值是4096(4KB)。
query_cache_type：设置查询缓存的类型。当这个参数为0或OFF时，则MySQL服务器不会启⽤查询缓存；当这个参数为1或ON时，则MySQL服务器会缓存所有查询结果（除了带有SELECT SQL_NO_CACHE的语句）；当这个参数为2或DEMAND时，则MySQL服务器只会缓存带有SELECT SQL_CACHE的语句。
sort_buffer_size：每个会话执⾏排序操作所分配的内存⼤⼩。想要增⼤max_sort_length参数，需要增⼤sort_buffer_size参数。如果在SHOW GLOBAL STATUS输出结果中看到每秒输出的Sort_merge_passes状态参数很⼤，可以考虑增⼤sort_buffer_size这个值来提⾼ORDER BY 和 GROUP BY的处理速度。建议设置为1~4MB。当个别会话需要执⾏⼤的排序操作时，在会话级别增⼤这个参数。
read_buffer_size：为每个线程对MyISAm表执⾏顺序读所分配的内存。如果数据库有很多顺序读，可以增加这个参数，默认值是131072字节。这个参数的值需要是4KB的整数倍。这个参数也⽤在下⾯场景中：
当执⾏ORDER BY操作时，缓存索引到临时⽂件（不是临时表）中；
执⾏批量插⼊到分区表中；
缓存嵌套查询的执⾏结果。
read_rnd_buffer_size：这个参数⽤在MyISAM表和任何存储引擎表随机读所使⽤的内存。当从MyISAM表中以键排序读取数据的时候，扫描的⾏将使⽤这个缓存以避免磁盘的扫描。将这个值设到⼀个较⼤的值可以显著提升ORDER BY的性能。然后，这个参数会应⽤到所有的客户端连接，所有不应该将这个参数在全局级别设为⼀个较⼤的值；在运⾏⼤查询的会话中，在会话级别增⼤这个参数即可。
join_buffer_size：MySQL服务器⽤来作普通索引扫描、范围索引扫描和不使⽤索引⽽执⾏全表扫描这些操作所⽤的缓存⼤⼩。通常，获取最快连接的⽅法是增加索引。当不能增加索引的时候，使全连接变快的⽅法是增⼤join_buffer_size参数。对于执⾏全连接的两张表，每张表都被分配⼀块连接内存。对于没有使⽤索引的多表复杂连接，需要多块连接内存。通常来说，可以将此参数在全局上设置⼀个较⼩的值，⽽在需要执⾏⼤连接的会话中在会话级别进⾏设置。默认值是256KB。
net_buffer_length：每个客户端线程和连接缓存和结果缓存交互，每个缓存最初都被分配⼤⼩为net_buffer_length的容量，并动态增长，直⾄达到max_allowed_packet参数的⼤⼩。当每条SQL语句执⾏完毕后，结果缓存会缩⼩到net_buffer_length⼤⼩。不建议更改这个参数，除⾮你的系统有很少的内存，可以调整这个参数。如果语句需要的内存超过了这个参数的⼤⼩，则连接缓存会⾃动增⼤。net_buffer_length参数最⼤可以设置到1MB。不能在会话级别设置这个参数。
max_allowed_packet：⽹络传输时单个数据包的⼤⼩。默认值是4MB。包信息缓存的初始值是由net_buffer_length指定的，但是包可能会增长到max_allowed_packet参数设置的值。如果要使⽤BLOB字段或长字符串，需要增加这个参数的值。这个参数的值需要设置成和最⼤的BLOB字段⼀样的⼤⼩。max_allowed_packet参数的协议限制是1GB。这个参数应该是1024整数倍。
bulk_insert_buffer_size：MyISAM表使⽤⼀种特殊的树状缓存来提⾼批量插⼊的速度，如INSERT ... SE
LECT,INSERT ... VALUES (...),(...), ...，对空表执⾏执⾏LOAD DATA INFILE。这个参数每个线程的树状缓存⼤⼩。将这个参数设为0会关闭这个参数。这个参数的默认值是
8MB。
max_heap_table_size：这个参数设置⽤户创建的MEMORY表允许增长的最⼤容量，这个参数⽤来MEMORY表的MAX_ROWS值。设置这个参数对已有的MEMORY表没有影响，除⾮表重建或执⾏ALTER TABLE、TRUNCATE TABLE语句。
这个参数也和tmp_table_size参数⼀起来现在内部in-memory表的⼤⼩。如果内存表使⽤频繁，可以增⼤这个参数的值。
tmp_table_size：内部内存临时表的最⼤内存。这个参数不会应⽤到⽤户创建的MEMORY表。如果内存临时表的⼤⼩超过了这个参数的值，则MySQL会⾃动将超出的部分转化为磁盘上的临时表。在MySQL 5.7.5版本，internal_tmp_disk_storage_engine存储引擎将作为磁盘临时表的默认引擎。在MySQL 5.7.5之前的版本，会使⽤MyISAM存储引擎。如果有很多的GROUP BY查询且系统内存充裕，可以考虑增⼤这个参数。
innodb_buffer_pool_dump_at_shutdown：指定在MySQL服务关闭时，是否记录InnoDB缓存池中的缓
存页，以缩短下次重启时的预热过程。通常和innodb_buffer_pool_load_at_startup参数搭配使⽤。innodb_buffer_pool_dump_pct参数定义了保留的最近使⽤缓存页的百分⽐。
innodb_buffer_pool_dump_now：⽴刻记录InnoDB缓冲池中的缓存页。通常和innodb_buffer_pool_load_now搭配使⽤。
innodb_buffer_pool_load_at_startup：指定MySQL服务在启动时，InnoDB缓冲池通过加载之前的缓存页数据来⾃动预热。通常和
innodb_buffer_pool_dump_at_shutdown参数搭配使⽤。
innodb_buffer_pool_load_now：⽴刻通过加载数据页来预热InnoDB缓冲池，⽆需重启数据库服务。可以⽤来在性能测试时，将缓存改成到⼀个已知的状态；或在数据库运⾏报表查询或维护后，将数据库改成到⼀个正常的状态。
MyISAM参数key_buffer_size：所有线程所共有的MyISAM表索引缓存，这块缓存被索引块使⽤。增⼤这个参数可以增加索引的读写性能，在主要使⽤MyISAM存储引擎的系统中，可设置这个参数为机器总内存的25%。如果将这个参数设置很⼤，⽐如设为机器总内存的50%以上，机器会开始page且变得异常缓慢。可以通过SHOW STATUS 语句查看 Key_read_requests,Key_reads,Key_write_requests, and
Key_writes这些状态值。正常情况下Key_reads/Key_read_requests ⽐率应该⼩于0.01。数据库更新和删除操作频繁的时
候，Key_writes/Key_write_requests ⽐率应该接近1。
key_cache_block_size：key缓存的块⼤⼩，默认值是1024字节。
myisam_sort_buffer_size：在REPAIR TABLE、CREATE INDEX 或 ALTER TABLE操作中，MyISAM索引排序使⽤的缓存⼤⼩。myisam_max_sort_file_size：当重建MyISAM索引的时候，例如执⾏REPAIR TABLE、 ALTER TABLE、或 LOAD DATA INFILE命
令，MySQL允许使⽤的临时⽂件的最⼤容量。如果MyISAM索引⽂件超过了这个值且磁盘还有充裕的空间，增⼤这个参数有助于提⾼性能。myisam_repair_threads：如果这个参数的值⼤于1，则MyISAM表在执⾏Repair操作的排序过程中，在创建索引的时候会启⽤并⾏，默认值为1。
InnoDB参数innodb_buffer_pool_size：InnDB存储引擎缓存表和索引数据所使⽤的内存⼤⼩。默认值是128MB。在以InnDB存储引擎为主的系统中，可以将这个参数设为机器物理内存的80%。同时需要注意：
设置较⼤物理内存时是否会引擎页的交换⽽导致性能下降；
InnoDB存储引擎会为缓存和控制表结构信息使⽤部分内存，因⽽实际花费的内存会⽐设置的值⼤于10%；
这个参数设置的越⼤，初始化内存池的时间越长。在MySQL 5.7.5版本，可以以chunk为单位增加或减少内存池的⼤⼩。chunk的⼤⼩可以通过innodb_buffer_pool_chunk_size参数设定，默认值是128MB。内存池的⼤⼩可以等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍。
innodb_buffer_pool_instances：InnoDB缓存池被分成的区域数。对于1GB以上⼤的InnoDB缓存，将缓存分成多个部分可以提⾼MySQL服务的并发性，减少不同线程读缓存页的读写竞争。每个缓存池有它单独的空闲列表、刷新列表、LRU列表和其他连接到内存池的数据结构，它们被mutex锁保护。这个参数只有将innodb_buffer_pool_size参数设为1GB或以上时才⽣效。建议将每个分成的内存区域设为1GB⼤⼩。innodb_max_dirty_pages_pct：当Innodb缓存池中脏页所占的百分⽐达到这个参数的值时，InnoDB会从缓存中向磁盘写⼊数据。默认值是75。
innodb_thread_concurrency：InnoDB存储引擎可以并发使⽤的最⼤线程数。当InnoDB使⽤的线程超过这参数的值时，后⾯的线程会进⼊等待状态，以先进先出的算法来处理。等待锁的线程不计⼊这个参数的值。这个参数的范围是0~1000。默认值是0。当这个参数为0时，代表InnoDB线程的并发数没有限制，这样会导致MySQL创建它所需要的尽可能多的线程。设置这个参数可以参考下⾯规则：如果⽤户线程的并发数⼩于64，可以将这个参数设为0；
如果系统并发很严重，可以先将这个参数设为128，然后再逐渐将这个参数减⼩到96, 80, 64或其他数值，直到到性能较好的⼀个数值。
innodb_flush_method：指定刷新数据到InnoDB数据⽂件和⽇志⽂件的⽅法，刷新⽅法会对I/O有影响。如果这个参数的值为空，在类Unix 系统上，这个参数的默认值为fsync；在Windows系统上，这个参数的默认值为async_unbuffered。在类Unix系统上，这个参数可设置的值如下：
fsync:InnoDB使⽤fsync()系统函数来刷新数据和⽇志⽂件，fsync是默认参数。
O_DSYNC:InnoDB使⽤O_SYNC函数来打开和刷新⽇志⽂件，使⽤fsync()函数刷新数据⽂件
littlesync：这个选项⽤在内部性能的测试，⽬前MySQL尚不⽀持，使⽤这个参数⼜⼀定的风险
nosync：这个选项⽤在内部性能的测试，⽬前MySQL尚不⽀持，使⽤这个参数⼜⼀定的风险
O_DIRECT:InnoDB使⽤O_DIRECT（或者directio()在Solaris）函数打开数据⽂件，使⽤fsync()刷新数据⽂件和⽇志⽂件
O_DIRECT_NO_FSYNC：在刷新I/O时，InnoDB使⽤O_DIRECT⽅式。
在有RAID卡和写缓存的系统中，O_DIRECT有助于避免InnoDB缓存池和操作系统缓存之间的双重缓存。在InnoDB数据和⽇志⽂件放在SAN存储上⾯的系统，默认值或O_DSYNC⽅法会对以读为主的数据库起到加速作⽤。
innodb_data_home_dir：InnoDB系统表空间所使⽤的数据⽂件的物理路径，默认路径是MySQL数据⽂件路径。如果这个参数的值为空，可以在innodb_data_file_path参数⾥使⽤绝对路径
innodb_data_file_path：InnoDB数据⽂件的路径和⼤⼩。
innodb_file_per_table：当这个参数启⽤的时候，InnoDB会将新建表的数据和索引单独存放在.ibd格式的⽂件中，⽽不是存放在系统表空间中。当这张表被删除或TRUNCATE时，InnoDB表所占⽤的存储会被释放。这个设定会开启InnoDB的⼀些其他特性，⽐如表的压缩。当这个参数关闭的时候，InnoDB会将表和索引的数据存放到系统表空间的ibdata⽂件中，这会有⼀个问题，因为系统表空间不会缩⼩，这样设置会导致空间⽆法回放。
innodb_undo_directory：InnoDB undo⽇志所在表空间的物理路径。和innodb_undo_logs、innodb_undo_tablespaces参数配合，来设置undo⽇志的路径，默认路径是数据⽂件路径。
innodb_undo_logs：指定InnoDB使⽤的undo⽇志的个数。在MySQL 5.7.2版本，32个undo⽇志被临时
表预留使⽤，并且这些⽇志存放在临时表表空间(ibtmp1)中。如果undo⽇志只存放在系统表空间中，想要额外分配供数据修改事务⽤的undo⽇志，innodb_undo_logs参数必须设置为32以上的整数。如果你配置了单独的undo表空间，要将innodb_undo_logs参数设为33以上来分配额外供数据修改事务使⽤的undo⽇志。每个undo⽇志最多可以⽀持1024个事务。如果这个参数没有设置，则它会设为默认值128。
innodb_undo_tablespaces：undo⽇志的表空间⽂件数量。默认，所有的undo⽇志都是系统表空间的⼀部分。因为在运⾏⼤的事务
时，undo⽇志会增⼤，将undo⽇志设置在多个表空间中可以减少⼀个表空间的⼤⼩。undo表空间⽂件创建在innodb_undo_directory参数指定的路径下，以undoN格式命名，N是以0开头的⼀系列整数。undo表空间的默认⼤⼩为10M。需要在初始化InnoDB前设置
innodb_undo_tablespaces这个参数。在MySQL 5.7.2版本，在128个undo⽇志中，有32个undo⽇志是为临时表所预留的，有95个undo⽇志供undo表空间使⽤。
innodb_log_files_in_group：InnoDB⽇志组包含的⽇志个数。InnoDB以循环的⽅式写⼊⽇志。这个参数的默认值和推荐值均是2。⽇志的路径由innodb_log_group_home_dir参数设定。
innodb_log_group_home_dir：InnoDB重做⽇志⽂件的物理路径，重做⽇志的数量由innodb_log_files_i
n_group参数指定。如果不指定任何InnoDB⽇志参数，MySQL默认会在MySQL数据⽂件路径下⾯创建两个名为ib_logfile0、ib_logfile1的两个重做⽇志⽂件，它们的⼤⼩由innodb_log_file_size参数设定。
innodb_log_file_size：⽇志组中每个⽇志⽂件的字节⼤⼩。所有⽇志⽂件的⼤⼩(innodb_log_file_size * innodb_log_files_in_group)不能超过512GB。
innodb_log_buffer_size：InnoDB写⼊磁盘⽇志⽂件所使⽤的缓存字节⼤⼩。如果innodb_page_size参数为32K，则默认值是8MB；如果innodb_page_size参数为64K，则默认值是16MB。如果⽇志的缓存设置较⼤，则MySQL在处理⼤事务时，在提交事务前⽆需向磁盘写⼊⽇志⽂件。建议设置此参数为4~8MB。
innodb_flush_log_at_trx_commit：当提交相关的I/O操作被批量重新排列时，这个参数控制提交操作的ACID⼀致性和⾼性能之间的平衡。可以改变这个参数的默认值来提升数据库的性能，但是在数据库宕机的时候会丢失少量的事务。这个参数的默认值为1，代表数据库遵照完整的ACID模型，每当事务提交时，InnoDB⽇志缓存中的内容均会被刷新到⽇志⽂件，并写⼊到磁盘。当这个参数为0时，InnDB⽇志缓存⼤概每秒刷新⼀次⽇志⽂件到磁盘。当事务提交时，⽇志缓存不会⽴刻写⼊⽇志⽂件，这样的机制不会100%保证每秒都向⽇志⽂件刷新⽇志，当mysqld进程宕掉的时候可能会丢失持续时间为1秒左右的事务数据。当这个参数为2时，当事务提交后，InnoDB⽇志缓存中的内容会写⼊到⽇志⽂件且⽇志⽂件，⽇志⽂件以⼤概每秒⼀次的频率刷新到磁盘。在MySQL 5.6.6版本，InnoDB⽇志刷新频率由
innodb_flush_log_at_timeout参数决定。通常将个参数设为1。
innodb_flush_log_at_timeout：写⼊或刷新⽇志的时间间隔。这个参数是在MySQL 5.6.6版本引⼊的。在MySQL 5.6.6版本之前，刷新的频率是每秒刷新⼀次。innodb_flush_log_at_timeout参数的默认值也是1秒刷新⼀次。
innodb_lock_wait_timeout：InnDB事务等待⾏锁的时间长度。默认值是50秒。当⼀个事务锁定了⼀⾏，这时另外⼀个事务想访问并修改这⼀⾏，当等待时间达到innodb_lock_wait_timeout参数设置的值时，MySQL会报错"ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"，同时会回滚语句（不是回滚整个事务）。如果想回滚整个事务，需要使⽤--innodb_rollback_on_timeout参数启动MySQL。在⾼交互性的应⽤系统或OLTP系统上，可以减⼩这个参数来快速显⽰⽤户的反馈或把更新放⼊队列稍后处理。在数据仓库中，为了更好的处理运⾏时间长的操作，可以增⼤这个参数。这个参数只应⽤在InnoDB⾏锁上，这个参数对表级锁⽆效。这个参数不适⽤于死锁，因为发⽣死锁时，InnoDB会⽴刻检测到死锁并将发⽣死锁的⼀个事务回退。
innodb_fast_shutdown：InnoDB关库模式。如果这个参数为0，InnoDB会做⼀个缓慢关机，在关机前会做完整的刷新操作，这个级别的关库操作会持续数分钟，当缓存中的数据量很⼤时，甚⾄会持续⼏个⼩时；如果数据库要执⾏版本升级或降级，需要执⾏这个级别的关库操作，以保证所有的数据变更都写⼊
到数据⽂件。如果这个参数的值是1（默认值），为了节省关库时间，InnoDB会跳过新操作，⽽是在下⼀次开机的时候通过crash recovery⽅式执⾏刷新操作。如果这个参数的值是2，InnoDB会刷新⽇志并以冷⽅式关库，就像MySQL宕机⼀样，没有提交的事务会丢失，在下⼀次开启数据库时，crash recovery所需要的时间更长；在紧急或排错情形下，需要⽴刻关闭数据库时，会使⽤这种⽅式停库。