Mysql 参数调优

benzhou

数据库优化（影响力从上到下减小）(1)SQL语句优化(2)数据库设计优化(3)参数优化(4)硬件资源和文件系统　　从上面的列表可以看出参数对于mysql 优化来说实际上起到的作用并不是很大，如果想更好的发挥mysql性能，还是从前面2点入手较好，但并不代表对于参数的优化一点作用都没有，不代表参数优化没必要做。要优化数据库，第一步需要优化的就是IO，即尽可能将磁盘IO 转化成内存IO，众所周知，内存IO和磁盘IO 速度上并不是同一个等级的。
　　下面就介绍一下一些比较常见的参数的优化：
　　query_cache_size/query_cache_type （global）
　　
　　这两个参数是针对Query cache 的优化，Query cache用于缓存SQL语句执行的结果集(仅针对select)语句。若Mysql已打开Query cache，那么当Mysql 接受到select语句请求后，如果命中缓存，则直接通过Query cache 返回结果，从而忽略后面所有步骤(如SQL语句的解析，优化器优化，向存储引擎请求数据等)，进而极大的提高性能。
　　对于Query cache来说最重要的参数便是query_cache_size 和 query_cache_type，前者用于设置缓存resultset的内存大小，后者设置什么场景下使用Query cache。
　　一般来说query_cache_size 设置为256M是一个比较合适的大小，不过具体还得根据Query cache 的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）来进行相应调整。
　　query_cache_type [0|1|2]
　　0(OFF) 表示完全不使用query_cache
　　1(ON) 表示除显示要求不使用query_cache(使用了sql_no_cache)之外的所有select语句都是用query cache
　　2(DEMOND) 表示显示要求才使用query cache(使用了sql_cache)
　　key_buffer_size （global）  
　　key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果有足够的内存，这个缓存区域最好是能够存放下所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。
　　此外，当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。
　　innodb_buffer_pool_size（global）
　　
　　对应于MyISAM 的 key_buffer_size，InnoDB存储引擎也有自己的缓存。这个常数用于设置用于缓存 InnoDB 索引及数据块的内存区域大小。简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。
和key_buffer_size 对于 MyISAM 引擎一样，innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够大，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。    我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。skip-locking 　　避免Mysql 的外部锁定，减少出错几率，增强稳定性。
skip-name-resolve　　默认情况下，Mysql会自动的进行DNS解析，使用该参数可以禁止Mysql对外部连接进行DNS解析，使用这一选项可以消除Mysql 进行DNS解析时间。但需要需要的是，如果开启该选项，则所有远程主机连接授权都要使用IP地址的方式了，否则Mysql将无法正常处理连接请求。
max_allowed_packet = 4M　　设定在网络传输中一次消息传输量的最大值，系统默认为1MB，最大为1GB，必须设定为1024的倍数，单位为字节。
max_connections = 5000     指定mysql 允许的最大连接进程数。如果在访问论坛时经常出现Too many connections的错误，则需要增大该值。具体数值需根据服务器性能来决定。max_connect_errors = 6000    设置每个主机的连接请求异常中断的最大次数，当超过该次数，Mysql服务器将禁止host的连接请求，知道Mysql服务器重启或者通过flush hosts 命令清空此host的相关信息。wait_timeout = 120　　指定一个请求的最大连接时间，对于4GB左右内存的服务器来说，可以设置为5-10。（具体数值需根据服务器自身性能来设置）
tmp_table_size = 64M    设置内存临时表最大值，如果超过该值，则会将临时表写入磁盘，设置范围为1KB-4GB　　例如order by 、group by等操作可能会用到临时表，此时如果需要的空间小于该参数tmp_table_size的值，那么mysql会将临时表建在内存中，否则自动转换到磁盘上（仅针对MyISAM 的情况）。
　　binlog_cache_size
　　设置binlog缓存大小，一般2MB～4MB是一个比较合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB。
　　暂时想到的就这些参数，以后接触到其他的再慢慢补充上来。