MySQL参数调优实例探究讲解

一、MySQL 参数调优

1、一些生产常用查询命令

-- 查看数据库里正在执行的sql语句
show processlist;
-- 查看正在执行的完整sql语句，完整显示
show full processlist;
-- 查看数据库的配置参数信息，例如：my.cnf里参数的生效情况
show variables;
-- MySQL服务器运行各种状态值，查询MySQL服务器配置信息语句
show variables like '%log_bin%';
-- 查看当前会话的数据库状态信息
show session status;
-- 查看整个数据库运行状态信息，分析并做好监控
show global status;
-- 显示innodb 引擎的性能状态
show engine innodb status;
-- 登陆数据库现场抓(显示完整的进程列表)
show full processlist;
-- explain语句检查索引执行情况，将上边抓到的慢语句，进行一个索引检查
explain select * from test_table where ***

2、分析慢查询

生产者中，一般设置如果执行时间超过0.2、0.5、1秒，就是慢查询。一般可以分析缓慢的查询日志，找出有问题的SQL语句。慢速查询时间不应设置得太长，否则就没有意义。最好在2秒内。

-- 查看数据库的配置参数信息
show variables like '%slow%';
-- 查看整个数据库运行状态信息
show global status like '%slow%'; 

打开慢速查询日志可能会对系统性能产生轻微影响，如果您的MySQL是主从结构，您可以考虑打开其中一个从属服务器的慢速查询日志，这样您就可以监视慢速查询，对系统性能几乎没有影响。

3、分析连接数

-- 查询MySQL服务器最大连接数(可以理解为设置的最大连接数)
show variables like 'max_connections';
-- 查询服务器响应的最大连接数(可以理解为应用的最大连接数)
show global status like 'Max_used_connections';

过去，MySQL服务器的最大连接数为 Max_used_connections，未达到与服务器的最大连接数 max_connections ，没有问题。

理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%

最大连接数约占最大连接数的85%。如果该比例小于10%，则MySQL服务器的最大连接数设置得太高。

4、缓冲区调优 Key_buffer_size

Key_buffer_size 是对MyISAM表性能影响最大的参数。缓存 MyISAM 表索引以提高 MyISAM 表索引的读写效率。

用于索引块的缓冲区大小可以更好地处理的索引。

对MyISAM表的影响不大。MyISAM将使用系统缓存来存储数据，因此大量使用MyISAM表的计算机的内存将很快耗尽。但是，如果您将此值设置得太高（例如，大于总内存的50%），系统将转换为页面并变得非常缓慢。MySQL在读取数据时依赖于操作系统来执行文件系统缓存，因此必须为文件系统缓存留出一些空间。

建议将其设置为25%的内存，并观察性能变化。

-- 查看分配了多少内存给key_buffer_size
show variables like 'key_buffer_size';
-- 查看索引读取请求、查看请求在内存中没有找到直接从硬盘读取索引
show global status like 'key_read%'; 

计算索引未命中缓存的概率：key_cache_miss_rate = Key_reads / Key_read_requests * 100%

-- Key_blocks_unused表示未使用的缓存簇blocks数
-- Key_blocks_used表示曾经用到的最大的blocks数
show global status like 'key_blocks_u%'; 

比如服务器的所有缓存都用到了，要么增加 key_buffer_size 。比较理想的设置：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5、临时表调优 Created_tmp_tables

-- 查看在磁盘上创建临时表
show global status like 'created_tmp'
-- 查看MySQL服务器对临时表的配置
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 

每次创建临时表时，都会添加 Created_tmp_tables。如果要在磁盘上创建临时表，Created_tmp_disk_Tables也会增加，Created_tmp_files 表示MySQL服务创建的临时文件的数量。

理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

6、分析 Open Table 使用情况

-- Open_tables表示打开表的数量，如果Opened_tables数量过大，说明配置中 table_open_cache 值可能太小
show global status like 'open%tables%';
-- 查询一下服务器table_cache值
show variables like 'table_cache';

7、查看 MySQL进程使用情况

thread_cache_size 缓存可重用的线程数，此参数设置线程缓存。线程创建和销毁的成本可能很高，因为每个线程都需要连接/断开连接。如果应用程序具有大量跳转并发连接和更多线程，请增加其值。它旨在避免在正常操作中创建新线程。建议将其设置为至少16。

show global status like 'Thread%'; 
-- 查询服务器thread_cache_size配置
show variables like 'thread_cache_size'; 

如果我们在MySQL服务器配置文件中设置 thread_cache_size。当客户端断开连接时，处理此客户端的服务器线程将被缓存以响应下一个客户端，而不是销毁它（前提是缓存数量未达到上限）。

Threads_created 表示创建的线程数。如果发现创建的值太大，则表明MySQL服务器一直在创建线程，这也会消耗资源。您可以在配置文件中适当地添加 thread_cache_size 值。

8、查询缓存 query cache

query_cache_size 指定MySQL查询结果缓冲区的大小，如果应用程序具有大量读取且没有应用程序级缓存，则此选项非常有用。但是，不要将其设置得太大，因为维护它也需要大量开销，这会导致MySQL速度减慢。

-- 查询 query_cache
show global status like 'qcache%'; 
-- 查询服务器 query_cache 配置
show variables like 'query_cache%';

query_cache_limit：缓存上限，超过此大小的查询将不会被缓存，缓存单条SQL的结果集上限。默认4KB。当一条SQL返回的结果集大于这个限制的时候，将不被MySQL缓存。

query_cache_min_res_unit：缓存块的最小大小，缓存是每个数据集的最小内存大小。默认大小为4KB。如果它太小，MySQL将经常访问内存块以获取信息。如果设置得太大，内存将被浪费。如果SQL返回的结果集非常小，还可以减少参数以避免内存浪费。如果大多数结果集大于4KB，请考虑增加参数。

query_cache_size：查询缓存大小 query_cache_type：缓存类型，它决定要缓存的查询。在本例中，这意味着不缓存 select sql_no_cache 查询 query_cache_wlock_invalidate：当其他客户端正在写入MyISAM表时，如果查询在 query cache 中，则在读取表以获取结果之前，是返回缓存结果还是等待写入操作完成。

计算缓存碎片率：Qcache_free_blocks / Qcache_total_blocks * 100%

计算缓存利用率：(query_cache_size - Qcache_free_memory) / query_cache_size * 100%

计算缓存命中率：(Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

query_cache_min_res_unit，默认值为4KB。大的设置对于大数据查询很好，但如果您的查询都是小数据查询，则很容易导致内存碎片和浪费。如果查询缓存碎片率超过20%，则可以使用 FLUSH QUERY CACHE 对缓存碎片进行碎片整理，或者如果查询都是小数据，则可以尝试减少 query_cache_min_res_unit 。如果查询缓存利用率低于25%，query_cache_size 如果大小设置得太大，可以适当减小。

9、分析SQL排序与锁及文件使用与表扫描情况

-- 分析SQL排序使用情况
show global status like 'sort%'; 

sort_buffer_size，为每个需要排序的线程分配此大小的缓冲区，并增加此值以加快ORDER BY或GROUP BY操作。

但是，与此参数相对应的分配内存对于每个连接是互斥的。如果有100个连接，则实际分配的排序缓冲区的总大小为100×sort_buffer_size，通常设置为2M以观察更改，然后进行调整。

-- 分析SQL表扫描情况
show global status like 'handler_read%'; 

表扫描率 = Handler_read_rnd_next / Com_select，如果表扫描速率超过4000，则表明执行了太多的表扫描。很可能索引尚未构建，并且已添加读取缓冲区（read_buffer_size）大小值有一些优势，但最好不要超过8MB。

-- 查看文件打开数
show global status like 'open_files';
-- 查看配置 Open_files / open_files_limit * 100% <= 75%
show variables like 'open_files_limit';
-- 查看表锁
show global status like 'table_locks%';
-- 查询MySQL服务器完成的查询请求次数：
show global status like 'com_select';

二、MySQL优化参数实战

互联网上有很多文章介绍如何配置 MySQL 服务器。然而，考虑到服务器硬件配置和特定应用程序的差异，首先使用MySQL默认值，我们需要根据自己的情况优化配置。

好的做法是，首先使用MySQL默认值，在 MySQL 服务器稳定运行一段时间后运行，并根据服务器的状态，以及上述的调优参数的知识对其进行优化。

每个连接都需要申请相应的内存，根据默认参数值，每个连接线程使用的最大内存大小为25MB。线程级别参数不应设置得太大。

read_buffer_size：用于顺序读的缓冲区大小，提高顺序读效率，默认131072

read_rnd_buffer_size：用于随机读的缓冲大小，提高随机读效率，默认262144

sort_buffer_size：排序缓冲大小，提高排序效率，默认262144

join_buffer_size：用于普通索引扫描，范围索引扫描和不使用索引并因此执行全表扫描连接的缓冲区的最小值，提高表连接效率，默认262144

binlog_cache_size：二进制日志缓冲大小，用于缓存事务执行期间binlog，提高binlog写入效率，默认32768

tmp_table_size：临时表内存大小，提高临时表存储效率，默认16777216

thread_stack：线程堆栈大小，暂存复杂SQL语句、控制存储过程递归深度等，默认262144

thread_cache_size：线程缓存大小，减少多次打开线程开销，默认8+(max_connections/100)，上限100

net_buffer_length：线程池连接缓冲以及读取结果缓冲大小，默认16384

bulk_insert_buffer_size：MyISAM表批量写入数据缓存大小，默认8388608

总结

本文主要讲解MySQL参数调优，包括SQL调优的参数解析，如何在生产环境调优，文中根据不同的数据库参数给出调优建议与应用场景。