[李景山php]mysql 配置优化

mysql 配置优化

今天我们不讨论其它优化，就就配置上，讲讲mysql的优化，看看怎么处理更好。最好是固态硬盘。

注意：此处说明的my.cnf可能根据不同的配置位置显示情况不一样。

全局配置优化–连接

1 最大连接数 max_connections

查看当前最大连接数：

show variables like ‘max_connections’;

查看最大响应连接数：

show status like ‘max_used_connections’;

设置建议：建议设置最大响应连接数是最大连接数的80%；

也就是说：max_used_connections /max_connections = 80%；

设置mysql最大连接数的方法：

1 通过命令行进行配置【缺点：重启后消失】

set global max_connections=1000;

2 通过设置my.ini或者my.cnf里面的

max_connections = 数字

注意：此处设置的最大连接数，有bug,如果在linux下默认被限制为214个。

在mysql的官方文档中这样说过：

MySQL 能够支持的最大连接数量受限于操作系统,必要时可以增大 open-files-limit。

通过下面的参数ulimit -n 查看linux默认下打开的参数是 1024个。
可以通过如下方法查看连接

2 暂存的连接数量 back_log 每个连接256kb。

当用户连接数量超过 max_connections 数量时候，更多的请求就会被放置在堆栈中，设置这个数字，也就是说设置TCP/IP的侦测队列。
建议设置在100个左右，无特殊设置。

设置方式：

通过my.cnf 添加 back_log = 数字 设置即可。

3 等待交互连接关闭秒数 interactive_timeout

一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。
默认数值是28800，可调优为7200。

设置方式：

通过my.cnf 添加 interactive_timeout = 数字 设置即可。

全局配置优化–缓存

4 索引缓冲区大小 key_buffer_size

key_buffer_size 指的是索引缓冲区大小，他决定索引处理的速度，尤其是索引读的速度，通过检测状态值 key_read_requests 跟 key_reads,可以知道key_buffer_size设置是否合理，这里比例比如 key_reads/key_read_requests 应该尽可能低，至少是1:100，或者1：1000.可以通过如下语句获取到
检测读取请求：

show global status like ‘key_read%’;

显示索引缓存大小的设置：

show variables like ‘key_buffer_size’;

注意：上面的数据需要多运行后，进行调优设置。

设置方式：

通过my.cnf 添加 key_buffer_size = 数字 设置即可。

5 查询缓冲区大小 query_cache_size

使用查询缓冲区，mysql将查询的结果存放在缓冲区中，如果后面有相同的sql查询，就会直接在缓冲区内读取。

查看是否合理：

执行：SHOW STATUS LIKE ‘Qcache%’;

获取结果：

+-------------------------+----------+| Variable_name   | Value|+-------------------------+----------+| Qcache_free_blocks  | 1|| Qcache_free_memory  | 16760152 || Qcache_hits | 0|| Qcache_inserts  | 0|| Qcache_lowmem_prunes| 0|| Qcache_not_cached   | 0|| Qcache_queries_in_cache | 0|| Qcache_total_blocks | 1|+-------------------------+----------+

参数说明：

如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，

如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；

如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。

此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。

query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。

query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。

可以使用下面查询：

SHOW VARIABLES LIKE ‘query_cache%’;

+------------------------------+----------+| Variable_name| Value|+------------------------------+----------+| query_cache_limit| 1048576  || query_cache_min_res_unit | 4096 || query_cache_size | 16777216 || query_cache_type | OFF  || query_cache_wlock_invalidate | OFF  |+------------------------------+----------+

查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服务器查询缓存碎片率＝20.46％，查询缓存利用率＝62.26％，查询缓存命中率＝1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

全局配置优化–单个连接缓存

6 单个顺序扫描缓存大小 record_buffer_size

7 随机读缓冲区大小 read_rnd_buffer_size

8 排序缓冲区大小 sort_buffer_size

9 联合查询缓冲区 join_buffer_size

10 表高速缓存区大小 table_cache

11 用户创建内存表的大小 max_heap_table_size

12 临时表大小 tmp_table_size

13 复用保存中的线程数量 thread_cache_size

14 期待提供的线程数 thread_concurrency

15 请求最大连接时间 wait_timeout

INNODB引擎配置优化

缓冲池是数据和索引缓存 innodb_buffer_pool_size

这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

redo日志的大小 innodb_log_file_size