mysql参数优化

mysql一些优化参数：

1.innodb_write_io_threads和innodb_read_io_threads
假如CPU是2颗8核的，那么可以设置：
innodb_read_io_threads = 8
innodb_write_io_threads = 8

如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads = 10
innodb_write_io_threads = 6

也就是说，你可以根据情况加以设置。

注意

这两个参数不支持动态改变，需要把该参数加入my.cnf里，修改完后重启MySQL服务，允许值的范围是1～64

2.tmp_table_size和max_heap_table_size
内存中临时表的最大大小。这个变量不适用于用户创建的MEMORY引擎表；
实际限制是tmp_table_size和max_heap_table_size的最小值；
当内存中的临时表超过限制时，MysQL会自动将其转换为磁盘上的临时表
tmp_table_size 内存中临时表的最大大小，这个变量不适用于用户创建的MEMORY引擎表。
max_heap_table_size这个参数是用来限制MEMORY引擎表的，这个变量设置用户创建的MEMORY表允许增长的最大大小，该变量的值用于计算内存表的MAX_ROWS值。
当在内存或磁盘上创建内部临时表时，服务器会增加Created_tmp_tables值。当在磁盘上创建内部临时表时，服务器会增加Created_tmp_disk_tables值。如果在磁盘上创建了过多的内部临时表，请考虑增加tmp_table_size和max_heap_table_size设置。
内存临时表由MEMORY存储引擎管理，所以建议max_heap_table_size设置一样的，如果tmp表变得太大，就会在磁盘上创建默认的InnoDB表（5.7版本是这样）；
tmp_table_size&max_heap_table_size，内部临时表是存在内存中的，使用 MEMORY 存储引擎，如果大小超过了这两者较小的值，则会转化为磁盘临时表；
internal_tmp_disk_storage_engine：如果内部临时表转化为磁盘临时表，则这个参数指定了磁盘临时表的存储引擎，默认是 INNODB，还可以设置为 MYISAM；
innodb_temp_data_file_path：指定了临时表空间的位置和大小，默认值为 ibtmp1:12M:autoextend ，即 datadir/ibtmp1，初始大小12M可以无限扩展，建议限制一个最大值防止把磁盘撑满。

show global status like '%tmp%';
 Created_tmp_disk_tables 
| Created_tmp_files       | 393199     |
| Created_tmp_tables      | 5025057610
Created_tmp_disk_tables/Created_tmp_tables 如果大于10% 则需要加大tmp_table_size

3.sync_binlog=1，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据，但影响并发和写入的性能，根据业务场景衡量. 
innodb_flush_log_at_trx_commit当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失
innodb_io_capacity    
200      单盘SAS/SATA
2000       SAS*12 RAID 10
5000      SSD
20000    FUSION-IO 根据实际磁盘进行配置

4.sort_merge_passes表示当需要排序时，在排序缓冲中无法将结果完全存放，则将会基于磁盘创建临时文件进行排序。如果该值较高，则应提高sort_buffer_size大小。最好的办法是找到是由哪些排序SQL造成的。
innodb 缓冲池的读命中率                                                                            
innodb_buffer_read_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100
Innodb 缓冲池的利用率                                                                              
Innodb_buffer_usage =  ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100
数据库缓冲池读命中率较低，使用率正常
select_full_join   没有主键（key）联合（Join）的执行   出现 full join 的次数，该值不为0，需要检查表上是否有合适的索引    
threads_created表示创建过的线程数，如果发现threads_created值过大的话，表明mysql服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值
select_scan对 表进行完全扫描的联接的数量  应为恒定的值，不会随时间增长而有明显的增长
max_used_connections / max_connections * 100% ≈ 85% 连接数足够
Aborted_connects    查询服务器 尝试已经失败的MySQL 服务器的连接的次数 
Aborted_ clients  查询 服务器异常 客户端数（由于客户没有正确关闭连接已经死掉，已经放弃的连接数量）                                                                
这两个值应为恒定值，不随 时间增长而增长（ 或 增长幅度较小）
table_locks_immediate 表示立即释放表锁数，table_locks_waited 表示需要等待的表锁数，table_locks_waited为0表示没有等待

5.table_open_cache 
Open_tables                 
Opened_tables  
发现open_tables等于table_open_cache，都是512，说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_open_cache的值，4G内存的机器，建议设置为2048

比较适合的值：

Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95