MySQL配置参数优化

---

一、MySQL参数配置

　　在Linux/MAC中使用的是my.cnf文件，一般在MySQL安装相关的文件夹下，也可能在etc/MySQL目录下。启动的时候，直接用MySQLd(MySQL服务器的命令)，加上-defaults -file，指定配置文件路径，这样决定用哪个配置文件来启动MySQL服务器。 　　
　　在windows上它的扩展名叫my.ini，内容与格式和my.cnf是一样的。 配置文件里面默认情况下分成两节。
　　第一节叫做[mysqld], 第二节叫做[mysql], 用中括号括起来的。mysqld是MySQL server相关配置。MySQL这个小节叫section，里面的参数是给MySQL命令行MySQL client来使用。
　　show variables; 可以查看所有的命令，大约604个配置。例如用:show variables like '%keyword%';通过关键字来查询属性值。
　　MySQL配置又分为全局属性和当前MySQL会话会话有效的两类属性。

　　查看当前参数变量 show variables like '%keyword%';

　　查看全局参数变量 show global variables like '%keyword%';

　　访问参数变量值可以通过 show的方式 或 select @@+变量名来访问 (例如:select @@last_insert_id);

　　设置全局变量 set global ~ ~；

二、常见的参数配置

1.连接请求的变量

max_connections

　　max_connections是指MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过’conn%'通配符查看当前状态的连接数量，以定夺该值的大小。MySQL服务器允许的最大连接数16384；查看系统当前最大连接数：show variables like ‘max_connections’;

back_log

　　back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

wait-timeout

　　wait-timeout：MySQL客户端的数据库连接闲置最大时间值。说得比较通俗一点，就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout 值为8个小时，可以通过命令show variables like 'wait_timeout’查看结果值;。设置这个值是非常有意义的，比如你的网站有大量的MySQL链接请求（每个MySQL连接都是要内存资源开销的 ），由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源，或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MySQL的状态（可用show full processlist)，如果经常发现MySQL中有大量的Sleep进程，则需要 修改wait-timeout值了。

interactive_timeout

　　interactive_timeout：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在MySQL_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

　　wait_timeout:　服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由MySQL_real_connect()的连接选项CLIENT_INTERACTIVE定义).这两个参数必须配合使用。否则单独设置wait_timeout无效。

2.缓冲区变量

key_buffer_size

　　key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM(MySQL表存储的一种类型，可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好。其值可以用以下命令查得：show status like ‘key_read%’;

query_cache_size

　　query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数。

max_connect_errors

　　max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。

sort_buffer_size

　　sort_buffer_size 是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。

max_allowed_packet

　　max_allowed_packet是MySQL中的一个设定参数，用于设定所接受的包的大小，根据情形不同，其缺省值可能是1M或者4M，比如是4M的情况下，这个值的大小即为：4 10241024= 4194304。

join_buffer_size

　　join_buffer_size = n 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)

thread_cache_size(服务器线程缓存)

　　默认的thread_cache_size=8，但是看到好多配置的样例里的值一般是32，64，甚至是128，感觉这个参数对优化应该有帮助，于是查了下：
　　根据调查发现以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(–>表示要调整的值) 根据物理内存设置规则如下：
1G —> 8
2G —> 16
3G —> 32 >3G —> 64

3.配置Innodb的变量

innodb_buffer_pool_size

　　这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。默认值：128M。专用mysql服务器设置的大小： 操作系统内存的70%-80%最佳。

innodb_flush_log_at_timeout

　　innodb_flush_log_at_timeout 这个参数的意思是刷新日志的时间，在mysql5.6版本中可以自定义，默认为1s。

innodb_thread_concurrency

　　innodb_thread_concurrency预先设置的并发线程数量，那么就会按照innodb_thread_sleep_delay预先设定的值休眠N秒，之后再次尝试连接，重试两次的机制是为了减少CPU上下文切换的次数，以降低CPU消耗。

innodb_log_buffer_size

　　innodb_log_buffer_size = n 事务日志文件写操作缓存区的最大长度(默认设置是1MB)。

innodb_log_file_size

　　该参数决定着mysql事务日志文件（ib_logfile0）的大小；

innodb_log_files_in_group

　　innodb_log_files_in_group = n 使用多少个日志文件(默认设置是2)。InnoDB数据表驱动程序将以轮转方式依次填写这些文件; 当所有的日志文件都写满以后，之后的日志信息将写入第一个日志文件的最大长度(默认设置是5MB)。这个长度必须以MB(兆字节)或GB(千兆字节)为单 位进行设置。

read_buffer_size

　　read_buffer_size 是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

read_rnd_buffer_size

　　read_rnd_buffer_size 是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

bulk_insert_buffer_size

　　为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)