编程宝库 - 技术改变世界

编程宝库

Mysql知识

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个连接，则实际分配的排序缓冲区的总大小为100sort_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';