mysql5.6 参数优化怎么做？关键参数有哪些

mysql5.6 参数优化的重要性

很多人觉得数据库能用就行,参数优化是“锦上添花”，其实不是，mysql5.6作为很多中小企业的“老伙计”，默认参数就像刚出厂的手机——能用，但不一定适合你的实际需求，我见过一家做本地生活服务的公司，服务器8G内存，mysql5.6跑着默认配置，用户一到饭点下单就卡，后台查日志全是“连接超时”“查询慢”，后来一调参数，性能直接提了40%，用户投诉少了一大半。参数优化就像给数据库“量身定制”衣服，合身了才能跑得又快又稳。

不优化参数会有啥问题？内存浪费算小事，严重的话查询慢到用户流失，甚至数据库崩溃，比如默认的innodb_buffer_pool_size在5.6里才128M，要是你用InnoDB引擎存了10G数据，这128M缓存根本不够用，只能频繁读磁盘，速度慢得像蜗牛爬，还有连接数，默认max_connections才151，要是你网站突然来了200个并发用户，直接就“连接数满了”，新用户进都进不来，所以说，mysql5.6参数优化不是要不要做，是必须做。

mysql5.6 核心参数如何设置

调参数不能瞎调,得抓重点，我整理了几个mysql5.6里最影响性能的“核心参数”，照着调基本不会出大错，先说innodb_buffer_pool_size，这是InnoDB引擎的“内存仓库”，放数据和索引的地方，设置多大合适？一般是服务器物理内存的50%-70%，比如你服务器16G内存，分给它10G差不多，我之前给一家公司调的时候，从默认128M提到8G，查询速度直接快了3倍，磁盘IO降了一半。

然后是max_connections，最大连接数，默认151真的不够用，尤其是电商、论坛这类并发高的场景，怎么算？看看你业务高峰时大概多少人同时访问数据库，比如监控显示高峰有300个连接，那就设400（留100备用），我朋友的小网站之前设200，结果促销时用户一多就“Too many connections”，后来提到500就再没出过问题，不过也别设太大，比如你服务器就2G内存，设1000连接反而会占满内存，服务器变卡。

还有key_buffer_size，这是给MyISAM引擎用的索引缓存（现在InnoDB是主流，但老系统可能还有MyISAM表），如果你的表是MyISAM，这个参数得调，一般设物理内存的10%-20%；要是全用InnoDB，设个64M意思下就行，我还遇到过有人把这个参数设得比内存还大，结果mysql启动直接报错，记住参数不是越大越好，合适才重要。

query_cache_size这个参数有点特殊，5.6里默认是开启的，但其实高并发下它可能“帮倒忙”，因为查询缓存会给每个查询加锁，并发一高就排队，反而变慢，我建议小网站数据量不大可以设个64M试试，大网站直接设0关掉，用应用层缓存（比如Redis）效果更好，之前帮一家电商关了query_cache后，查询延迟从500ms降到了100ms，亲测有效。

mysql5.6 参数优化步骤详解

优化参数不是拍脑袋改,得按步骤来，我总结了一套“五步法”，亲测好用，第一步先“摸底”，看看当前参数啥样，用命令show variables;能列出所有参数，想查具体的就用show variables like '参数名';，比如查连接数就输show variables like 'max_connections';，再用show status;看看运行状态，比如Connections（总连接数）、Slow_queries（慢查询数），心里有个数。

第二步分析慢查询,找出“拖后腿”的SQL，先开启慢查询日志：在my.cnf里加slow_query_log = 1，slow_query_log_file = /var/log/mysql/slow.log，long_query_time = 2（超过2秒的查询算慢查询），等一天后看日志，用mysqldumpslow工具分析，比如执行mysqldumpslow -s t /var/log/mysql/slow.log，就能按时间排序看到哪些SQL最慢，我之前发现有个查询没加索引，跑一次要5秒，加了索引后0.1秒就搞定了。

第三步就是改参数了,找到mysql的配置文件my.cnf（一般在/etc/mysql/或/etc/下），用vim打开，在[mysqld]下面加参数，比如改innodb_buffer_pool_size就写innodb_buffer_pool_size = 10G，改max_connections就写max_connections = 400，改完保存，记得注释说明为啥改，# 2024-05-20 调大连接数，解决高峰连接超时问题”，以后维护方便。

第四步重启mysql让参数生效,命令是service mysqld restart（centos）或systemctl restart mysql（ubuntu）。注意！一定要在业务低峰期重启，比如凌晨2点，避免影响用户，我有次帮客户中午重启，结果正好赶上用户下单，被老板追着问了一下午，血的教训。

最后一步监控效果,别改完就不管了，用show processlist;看实时连接，有没有堵塞；用show status like 'Innodb_buffer_pool_read_requests';和show status like 'Innodb_buffer_pool_reads';算缓存命中率（(1 - 物理读/逻辑读)*100%），理想状态95%以上，要是命中率低，说明innodb_buffer_pool_size还得调大，我一般会观察3天，没问题才算优化完成。

mysql5.6 参数优化案例分享

去年帮一个做在线教育的客户调mysql5.6，他们用的是4核8G服务器，跑着一个网课平台，学生一到晚上7点上课，系统就卡，老师直播时PPT都加载不出来，客户急得不行，说再这样学生要退费了，我远程连上去一看，首先查参数，发现innodb_buffer_pool_size才512M，max_connections 200，slow_query_log还没开。

第一步先开慢查询日志,设置long_query_time=1，收集了两小时数据，用mysqldumpslow一看，有个查询课程列表的SQL，每次都要扫全表，耗时3秒多，因为没加索引，我给course表的teacher_id字段加了索引，瞬间快到0.05秒，然后看内存参数，8G服务器，innodb_buffer_pool_size调到5G（留3G给系统和其他服务），max_connections提到500（他们高峰并发大概300）。

改完重启mysql,晚上7点学生上课，我守着监控看，连接数稳定在280左右，没再超；查询响应时间从平均800ms降到200ms；磁盘IO之前每秒200次，现在只有50次，客户老板发来消息说：“老师和学生都说今天特别流畅，太感谢了！” 后来他们又加了1000多学生，系统还是很稳。这个案例说明，参数优化加索引优化双管齐下，效果才最好。

mysql5.6 与mysql5.7参数优化对比

现在很多人问我,mysql5.6和5.7参数优化区别大不大？说实话，5.7在参数上做了不少优化，默认配置更“聪明”，但5.6也有自己的调优空间，比如innodb_buffer_pool_size，5.7默认是128M（和5.6一样），但5.7支持动态调整，改了不用重启；5.6必须重启，所以调5.6时得规划好时间，别临时改。

连接数方面,5.7默认max_connections是151（和5.6一样），但5.7新增了max_user_connections（单个用户最大连接数），可以更精细控制；5.6只能靠应用层限制，还有query_cache_size，5.7默认是0（关闭），因为官方发现它在高并发下弊大于利；5.6默认是开启的，所以5.6用户一定要检查这个参数，高并发场景建议关掉。

日志方面,5.7默认开启了binlog（二进制日志），且格式是ROW（更安全），5.6默认binlog可能没开，需要手动配置，还有innodb_stats_on_metadata，5.6默认是1（查询information_schema时更新统计信息），会拖慢查询；5.7默认是0，性能更好，所以5.6用户记得把这个参数设为0，我之前调过一家公司，设完后查询information_schema的速度快了10倍。

5.7在默认参数上更“省心”，但5.6通过手动优化，性能也能追上来，要是你还在用5.6，别觉得老版本不行，把核心参数调好，照样能扛住中小规模业务。

mysql5.6 参数优化常见问题

调参数时总会遇到各种坑,我总结了几个常见问题，帮你避坑，第一个问题：参数改了不生效，这多半是没改对配置文件，比如把参数加到了[mysqld_safe]下面，或者配置文件有多个（/etc/my.cnf和/etc/mysql/my.cnf），改了一个另一个没改，解决办法：用mysqld --help --verbose | grep -A 1 'Default options'看mysql读取配置文件的顺序，按顺序改。

第二个问题：调大内存参数后服务器变慢，比如把innodb_buffer_pool_size设得比服务器内存还大，导致系统用swap（虚拟内存），速度反而变慢，我之前有个客户，8G内存硬把这个参数设10G，结果mysql启动后服务器卡得动不了，正确做法：内存参数总和别超过物理内存的80%，给系统留20%。

第三个问题：慢查询依然存在，有人以为调了参数就万事大吉，其实参数优化只是“基础”，SQL语句和索引才是“核心”，比如一个没加索引的SQL，就算把innodb_buffer_pool_size设到100G，照样慢，解决办法：先用explain分析SQL，看看有没有全表扫描，有没有用对索引，这比调参数更重要。

第四个问题：重启mysql后参数又恢复默认，这是因为只在命令行用set global改了参数，没写到配置文件里，set global改的是内存中的参数，重启就没了；必须写到my.cnf里才是永久生效，改参数“两步走”，先set global临时生效（测试用），没问题再写配置文件永久保存。