MySQL参数配置优化

数据库参数配置优化：

1、连接相关参数

max_connections：指定MySQL服务端最大并发连接数，默认为151。一般将其设置为500~2000

max_connect_errors：指定允许连接不成功的最大尝试次数，一般将其设置为10万以上的量级，一定不要忽略这个参数

interactive_timeout和wait_timeout参数：这两个参数必须同时设置，设置的时长不要超过24小时，即参数值小于86400。默认值均为28800，即8小时

skip-name-resolve：禁用DNS解析，建议启用本参数，对于加快网络连接速度有一定帮助

back_log：指定MySQL连接请求队列中存放的最大连接请求数量

2、文件相关参数

sync_binlog：指定同步二进制日志文件的频率，默认值为0，建议设置为大于0，要性能就设置该参数值为0，为了安全则指定该参数值为1。表示MySQL每进行n次事务后，就触发同步其binary log到磁盘

expire_logs_days：设置二进制日志文件的生命周期。建议保留7~14天。自动清除操作一般会在启动或二进制被flushed时，手动删除二进制日志可以通过purge binary logs语句进行

max_binlog_size：指定二进制日志文件大小，默认为1G

local_infile：是否允许从客户端本地加载数据，默认为允许

open_files_limit：指定操作系统允许mysqld进程使用的文件描述符数量

3、缓存控制参数

binlog_cache_size：二进制日志事务缓存区大小，默认32K

max_binlog_cache_size：binlog能够使用的最大内存区

binlog_stmt_cache_size：非事务语句的binlog_cache_size

table_open_cache：指定MySQL同时能够打开的表对象的数量

thread_cache_size：MySQL为快速重用而缓存的线程数量。

线程缓存命中率=(1-Threads_created/Connections)*100%，个人建议在300~500之间

query_cache_size：用于缓存查询结果集的内存区大小

query_cache_limit：能够缓存的单条SQL语句生成的最大结果集，默认为1M

query_cache_min_res_unit：指定查询缓存最小分配的块大小，默认为4K

query_cache_type：设置查询缓存的类型，0(OFF),1(ON),2(DEMAND)

sort_buffer_size：单个会话使用的排序区的大小，默认256K，一般设置为1~4M，根据情况设置

read_buffer_size：顺序读取时的数据缓存区大小，全表扫描，默认128K，最大为2G

read_rnd_buffer_size：随机读取时的数据缓存区大小，索引扫描，默认256K，最大为4G

join_buffer_size：表join操作时的缓存区大小，默认为256K，次参数值很重要，对于特殊的查询，可以单独设置session级别的较大值

net_buffer_length：单个客户端与MySQL服务器交互时，相关信息的缓存区大小，默认16K

max_allowd_packet：指定网络传输时单次最大传输的数据包大小，默认为4M

bulk_insert_buffer_size：指定批量插入时的缓存区大小，默认为8M

tmp_table_size：临时表最大可用空间

4、MyISAM专用参数

key_buffer_size：MyISAM表索引的缓存区大小，默认8M

key_cache_block_size：指定索引缓存的块大小，默认1K

5、InnoDB专用参数

innodb_buffer_pool_size：指定InnoDB引擎专用缓存区大小

InnoDB缓存区命中率=(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

innodb_buffer_pool_instances：指定InnoDB缓存池分为多少个区域来使用，默认为-1，表示由InnoDB自行调整

innodb_max_dirty_pages_pct：指定InnoDB缓存池中的脏页比例，默认为75

innodb_thread_concurrency：指定InnoDB内部的最大线程数

innodb_flush_method：用来控制InnoDB刷新数据文件及日志文件的方式。默认使用fsync()系统调用刷新数据文件和日志文件，还有O_DSYNC,O_DIRECT,O_DIRECT_NO_FSYNC选项

innodb_data_home_dir：指定InnoDB数据文件保存路径，默认保存在MySQL datadir参数指定的路径下

innodb_data_file_path：指定InnoDB数据文件名及文件大小

innodb_file_per_table：指定是否将每个InnoDB表对象存储到独立的数据文件

innodb_undo_directory：指定InnoDB引擎的UNDO表空间数据文件存储路径

innodb_undo_logs：指定回滚段的数量，默认是0

innodb_unod_tablespace：指定InnoDB回滚段表空间的数量

innodb_log_files_in_group：指定InnoDB日志文件组中日志文件的数量

innodb_log_group_home_dir：指定InnoDB日志文件的保存位置

innodb_log_file_size：指定InnoDB单个日志文件的大小

innodb_log_buffer_size：InnoDB日志缓存区大小，默认8M

innodb_flush_log_at_trx_commit：指定InnoDB刷新log buffer中的数据到日志文件的方式，默认为1。当为1时，只要事务提交或回滚，就会将缓存中的数据写入日志文件，并且明确触发文件系统同步数据