全局内存参数
innodb_buffer_pool_size
#innodb buffer大小。8.0默认128M
#设置建议：
可以设置为pool_size=mem*(60%)innodb_buffer_pool_instances
#innodb buffer的个数。8.0默认1
#该值分割buffer_pool_size。pool_size/instance=per pool size。可以有效减少hash table lru的latch互斥
#设置建议：
对于32位系统可设置为1
对于64位系统可以将该参数设置为CPU核数，一般建议设置为16innodb_log_buffer_size
#redo buffer的大小innodb_log_file_size
#redo log group的大小。默认48M。
#设置建议：show status engine innodb/G
innodb_log_file_size = ((new sequence - checkpoint sequence)/1024/1024)/innodb_log_files_in_group*0.75innodb_log_files_in_group
#每组redo log group member数量。默认2innodb_sort_buffer_size
#创建索引时专属的sort_buffer大小，该区域只用于创建索引，创建完索引释放。默认1Mtable_definition_cache
#用于缓存基于frm的表结构的数量。默认2000个
#设置建议：4096table_open_cache
#表数据高速缓存，每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，默认4000个
#需要尽量小于limit -n值，防止系统不稳定
#show global status like '%Open%_table%'监控优化该参数，如果open_table = table_open_cache，并且opened_table在不断增加，需要考虑设置该参数table_open_cache_instances
#table_open_cache的实例数，减少table_open_cache_handle争用。
#默认16个，可以设置为1-64线程内存参数
read_buffer_size
#MySQL顺序读缓冲池大小。默认128K
#如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
#只适用select into outfile/部分table full scan(filesort merge)两种场景，一般情况下，用到的情况很少。
#设置建议：1M-4Mread_rnd_buffer_size
#MySQL随机读缓冲池大小。默认256K，MMR及BKA特性都会使用该buffer
#Mysql随机读，但同时当order by的计划成本超出了sort_buffer_size后，mysql会产用随机读取并消耗额外的空间。MySQL会为每个session连接分发rnd_buffer。
#设置建议：8M-16Msort_buffer_size
#MySQL执行排序缓冲大小，默认256K。默认也是每个session分发。推荐值为1M-4M。如果有大量的order by / group by排序，极力推荐在session进行设置
#参考SHOW GLOBAL STATUS like '%sort%'; 的Sort_merge_passes参数，进行调优join_buffer_size
#联合查询操作所能使用的缓冲区大小，默认256K，BNL特性会会用该buffer
#每两张表full join查询使用一个join_buffer，如果涉及到多张表，不使用index查询，会使用多个join_buffer
#每个涉及到Join的SQL，最好不要超过3张表tmp_table_size
#临时表的内存缓存大小，默认16M。
#用于缓存高级group语句中的临时表，如果系统内存为128G，参考该值为64M
#show global status like 'created_tmp%';进行调优，(Created_tmp_disk_tables / Created_tmp_tables) * 100% 如果<=25%为最佳值binlog_cache_size
#每个session能够缓存bin-log的缓冲区大小，默认32K
#设置建议：根据具体的业务进行设置，
业务量比较低 : 1M
业务量比较多 : 2M-4Mthread_cache_size
#线程关闭后，可以放入堆栈缓存的个数，避免重新开启session对系统的影响。默认9
#可以查看show global status like 'thread%';Threads_created(已创建的thread)，Threads_running(运行中的thread)，Threads_cached(存放在thread_cache_size的thread)如果比较大，可以适当调节该参数
#java池长连接建议设置为100-200，短连接建议设置为500+
#设置建议：根据服务器内存进行调整 1G：8 / 2G：16 / 3G：32 / >3G：64 / >8G:512thread_stack
#每个连接线程被创建时，MySQL给它分配的内存大小，8G-16G服务器
#设置建议：64K参数查询
select
round(@@innodb_buffer_pool_size/1024/1024/1024) as innodb_buffer_gb,
round(@@innodb_buffer_pool_instances) as innodb_buffer_instance,
round(@@innodb_log_buffer_size/1024/1024) as innodb_log_buffer_mb,
round(@@innodb_sort_buffer_size/1024/1024) as innodb_sort_buffer_mb,
round(@@read_buffer_size/1024/1024) as thread_read_buffer_mb,
round(@@read_rnd_buffer_size/1024/1024) as thread_read_rnd_buffer_mb,
round(@@sort_buffer_size/1024/1024) as thread_sort_buffer_mb,
round(@@join_buffer_size/1024/1024) as thread_join_buffer_mb,
round(@@tmp_table_size/1024/1024) as thread_tmp_buffer_mb,
round(@@binlog_cache_size/1024/1024) as thread_binlog_buffer_mb,
round(@@thread_cache_size/1024/1024) as thread_cache_buffer_mb,
round(@@thread_stack/1024) as thread_stack_kb,
round(@@max_connections) as max_connects,
(SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep');数据库参数优化
内存的分配
innodb_buffer_pool_size
查询当前 MySQL 实例使用内存总和为607M，该库的innodb_buffer_pool_size为32G。
当前主机的内存大小是376G
该主机上有5个实例，因此需要考虑将 innodb_buffer_pool_size 的大小 就要考虑更多因素，一个重要的因素是 InnoDB 的总数据量(包括表和索引)。
查询InnoDB 的总数据量为162G
缓存池的命中率达到了99%以上
MySQL每秒从磁盘读的次数
通过vmstat中的cpu的wa或iostat中的%iowait查看发现并没有到IO瓶颈
通过以上综合分析，数据文件有162G，数据库内存设置为32G,主机内存足够大，但考虑到有80G为未删除已经迁走遗留数据文件，和非活跃数据，结合缓存池命中率来看，以及每秒读磁盘频率来看，该库设置为32G为合理参数设置不需要调整，调整该参数对性能提升不大。
innodb_buffer_pool_instances
一个和相关innodb_buffer_pool_size的参数是innodb_buffer_pool_instances，它设定把 InnoDB缓存池分成几个区，当innodb_buffer_pool_size大于1GB时，这个参数才会起作用，对于 大的InnoDB缓存池，建议把它设置得大一些，这样可以减少获取访问InnoDB缓存池时需要上锁的粒度，以提高并发度。
当前为8，不需要调整。
日志的分配
innodb_log_file_size
一个合理大小的日志文件应该可以容纳数据库在高峰时1到2个小时的数据变化。
当前redo的大小为2G
综合以上innodb_log_file_size不需要调整
硬盘读写参数
innodb_flush_log_at_trx_commit
当前库设置该参数为1。
（1）默认值为1，每次事务提交的时候都会将日志缓存中的数据写入到日志文件，同时还会触发文件系统到磁盘的同步，如果发生系统崩溃，数据是零丢失，这种方式对数据是最安全的，但性能是最慢的，因为把数据从缓存同步到磁盘的成本很高。这种方式适用于对数据安全性要求高的行业，如银行业。但很多互联网的应用，对数据的安全性要求不太高，而对性能的要求很高，设置成0或2会更合适。
（2）设置成0时，事务提交的时候不会触发写日志文件的操作，日志缓存中的数据以每秒一次的频率写入到日志文件中，同时还会进行文件系统到磁盘的同步操作。
（3）设置成2时，事务提交的时候会写日志文件，但文件系统到磁盘的同步是每秒进行一次。
0和2都是每秒进行一次文件系统到磁盘的同步，因此这两种方式的性能都差不多，当系统崩溃时，最多丢失1秒的数据。但0和2还有细微的不同，当设置成2时，每次事务提交都写日志文件，因此数据已经从MySQL的日志缓存刷新到了操作系统的文件缓存，如果只是MySQL崩溃，而操作系统没有崩溃，将不会丢失数据。因此0和2比较起来，通常设置为2比较好。
因此该参数可调整为2。
sync_binlog
sync_binlog参数控制事务提交时写二进制日志的行为方式，它有三个值：0、1和N。
（1）默认值为1，每次事务提交的时候都会把二进制日志刷新到磁盘，这种方式对数据是最安全的，
但性能是最慢的。
（2）设置成0时，事务提交的时候不会把二进制日志刷新到磁盘，刷磁盘的动作由操作系统控制。
（3）设置成N（N不等于0或1）时，每进行N事务提交后会进行一次把二进制日志刷新到磁盘的动作。
没有备库和使用二进制日志进行时间点恢复的需求时，可以把sync_binlog参数设置为0或N，设置为0是把刷新二进制日志文件的操作交给操作系统决定，但操作系统可能会在二进制日志文件写满进行切换时才刷新磁盘文件，这样会造成数秒的延迟，在这期间事务无法提交，因此把这个参数设置成100或1000之类的一个合理数值比设置成0好。
因此建议将此参数设置为1000
innodb_flush_method
fsync的特性：
buffer pool 的数据写磁盘的时候，需要先经历OS cache 然后在写磁盘
redo buffer 的数据写磁盘的时候，需要先经历OS cache 然后在写磁盘
O_DSYNC:
buffer pool 的数据写磁盘的时候，需要先经历OS cache 然后在写磁盘
redo buffer 的数据写磁盘的时候，穿过OS cache 直接写到磁盘
O_DIRECT：
buffer pool 的数据写磁盘的时候，跨过OS cache 然后在写磁盘
redo buffer 的数据写磁盘的时候，需要先经历OS cache 然后在写磁盘
通常对于硬盘性能好的服务器，可以设置成O_DIRECT，这样避免在InnoDB缓存和操作系统缓存中存有两份数据，而且InnoDB缓存比操作系统缓存效率要高，因为InnoDB缓存是专门针为InnoDB的数据设计的，而操作系统缓存是为通用的数据设计的。
对于读操作大大多于写操作的应用，设置成fsync会比设置成O_DIRECT性能略好。
从以上信息得出该库写大约读，可以不调整该参数值
innodb_io_capacity和innodb_io_capacity_max
这两个参数的设定是基于系统的每秒能处理的I/O数量（IOPS），可以把innodb_io_capacity_max设置成极限的IOPS，innodb_io_capacity设置成它的一半左右。
该库使用的磁盘是NVMe，高达500,000 IOPS，因此可以将innodb_io_capacity调整为25000，
innodb_io_capacity_max调整为50000
其他参数
max_connections
最大连接数合理，不用调整
binlog_order_commits
系统参数binlog_order_commits默认为on，如果把这个参数设置为off将不能保证事务的提交顺序和写入二进制日志的顺序一致，这不会影响到数据一致性，在高并发场景下还能提升一定的吞吐量。
该数据库并发性不高，因此可以不用调整
skip_name_resolve
系统参数skip_name_resolve默认为off，这时MySQL每收到一个连接请求，都会进行正向和反向 DNS解析，建议设置成on，禁止域名解析，这样会加快客户端连接到MySQL服务器的速度。
因此该参数不用调整
innodb_thread_concurrency
innodb_thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。
innodb_thread_concurrency应设为CPU核数的2倍
因此可以将innodb_thread_concurrency调整为96或者更大128
back_log
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log
该参数不用调整
innodb_write_io_threads和innodb_read_io_threads
主机逻辑cpu是48个
可以调整为innodb_read_io_threads = 24 上innodb_write_io_threads = 24
但是该库数据库的写操作比读操作多，那么可以设置：
innodb_read_io_threads = 16 innodb_write_io_threads = 32
tmp_table_size和max_heap_table_size
该参数是内存中临时表的最大大小。临时磁盘表比例超过20%，
实际限制是tmp_table_size和max_heap_table_size的最小值；可以将max_heap_table_size调整为和tmp_table_size一样。或者都调整为128M
table_open_cache和table_open_cache_instances
建议
Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95
实际操作过程中，可以把table_open_cache值设置得比Open_tables大一些，然后慢慢增加，逐步调试。如果 table_open_cache_instances 设置过小，在高并发场景下，可能导致 MySQL 内部线程严重的mutex 竞争。
以上信息可以看出Open_tables 等于 table_open_cache，并且 Opened_tables 在快速增加，说明需要打开的表已经超过限制了。
可以将table_open_cache调大至8192，table_open_cache_instances调大128
interactive_timeout和wait_timeout
参数设置合理，无需调整
join_buffer_size和sort_buffer_size
可根据慢sql中join查询和需要排序的sql进行判断是否需要扩大
