mysql 数据库优化参数设置_轻松优化MySQL-之数据库参数优化

最佳实践

使用InnoDB存储引擎

InnoDB引擎已经在多方面超越了MyIsam引擎，没有特殊需求的情况下建议选择InnoDB引擎。

让InnoDB使用全部内存

innodb_buffer_pool_size参数指定了 InnoDB 可以使用的内存总量。

建议设置为物理内存的 80%，因为要给操作系统留有空间。

如果你的内存是 32GB，可以设置为大约 25GB

innodb_buffer_pool_size = 25600M

注意：

(1)如果值小于1GB，说明真的应该升级服务器了

(2)如果内存特别大，例如200gb，就不必给操作系统留 20% 了，因为OS用不了 40gb。

让InnoDB多实例

innodb_buffer_pool_size的值大于 1G时，innodb_buffer_pool_instances会把 InnoDB 的缓存池划分成多个实例。

多个缓冲池的好处：

多个线程同时访问缓冲池时可能会遇到瓶颈，而多个缓冲池则可以最小化这个冲突

官方建议的 buffer 数量：每个 buffer pool 实例至少要 1G

例如内存为 32GB，innodb_buffer_pool_size为 25GB，那么合适的方案就是 25600M / 24 = 1.06GB

innodb_buffer_pool_instances = 24

加大max_length_for_sort_data参数的设置

在MySQL中，排序算法分为两种，一是只加载排序字段到内存，排序完成后再到表中取其他字段，二是加载所有需要的字段到内存，显然第二种节省了IO操作，所以更快。决定使用哪种算法是通过参数max_length_for_sort_data来决定的，当所有返回字段的最大长度小于这个参数值时，MySQL就会选择第二种算法，反之使用第一种。所以，如果有充足的内存让MySQL存放须要返回的非排序字段，就可以加大这个参数的值来让MySQL选择第二种排序算法。

当内存不是很充裕时，不能简单地通过强行加大上面的参数来强迫MySQL去使用高效算法，否则可能会造成MySQL不得不将数据分成很多段，然后进行排序，这样可能会得不偿失，此时就须要去掉不必要的返回字段，让返回结果长度适应max_length_for_sort_data参数的限制。

增大sort_buffer_size参数设置

增大sort_buffer_size并不是为了让 MySQL选择第二种排序算法，而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段，因为分段会造成MySQL不得不使用临时表来进行交换排序。

打开查询缓存

充分利用Mysql的查询缓存机制，业务中很多SQL都会重复执行的，当然现在很多数据层框架中也有缓存功能，但数据层框架中的缓存属于应用级别的，在数据被外部更新时会导致缓存数据过期问题。

案例

发现网站页面打开非常慢，对处理过程简单记录了一下。

找问题

首先登录服务器使用 top 查看当前进程信息，发现排名第一的是 mysql，占用 cpu 达到了 100% 以上，这就明确了是 mysql 的问题。

登录 mysql，使用 show processlist 查看下当前执行状态，发现了大量 LOCK 操作，也有多个 Copying to tmp table 的操作，说明有 sql 出现了问题，操作过于复杂，对临时表使用频繁，把其他操作阻塞了。

解决思路

找到了问题后，把处理方向确定为检查和修改配置、sql优化。

修改mysql配置

临时表

既然涉及了到了临时表，就先查看下目前临时表的信息

查看临时表的使用状态

show global status like 'created_tmp%';

发现 created_tmp_disk_tables 值过高，需要增加此值。

再看一下现在临时表的大小

show variables like '%tmp_table_size%';

在现在值的基础上增加一些，重新设置临时表大小

线程缓存数

看当前线程情况

show global status like 'Thread%';

发现 threads_created 的值过大，表明MySQL服务器一直在创建线程

查看当前值

show variables like 'thread_cache_size';

此参数需要调高

打开表数量

查看打开表的情况

show global status like 'open%tables%';

发现 opened_tables 数量过大，说明 table_cache 的值可能太小。

查看当前值

show variables like 'table_cache';

此参数需要调高

最大连接数

查看当前允许的最大连接数

show variables like 'max_connections';

查看服务器连接数的峰值

show global status like 'Max_used_connections';

峰值还没到最大限制，不需要修改

join buffer 和 sort buffer

查看现有值

SELECT @@sort_buffer_size;

SELECT @@join_buffer_size;

是默认值，需要修改

修改配置

确定了要修改的参数后，修改 my.cnf ，例如

table_cache = 64

sort_buffer_size = 8M

join_buffer_size = 4M

thread_cache_size = 300

thread_concurrency = 8

tmp_table_size = 246M

sql优化

从 show processlist 结果集中找出主要的复杂语句，对其进行 explain 和 profile 分析，进行索引优化，把复杂的sql 根据业务拆分为多个小的sql。