小站跑几年之后,几乎都会遇到同一个念头:落后的大版本该换了。这台机器上的 MySQL 5.7 已经跑了四年多,官方早就停止了支持,安全补丁不会再有,而新装的一台机器上是 8.0。当时的预期很简单——新版更快、更省内存、还有窗口函数。迁移的 SQL 导完、站点也能正常打开,我们就宣布完成了。然后接下来的两周里,后台列表变慢、某些页面偶尔卡三秒、部分日志里开始出现奇怪的乱码字符串,一件一件冒出来,每件都不致命,但每件都指向同一件事:版本切换本身带进来了一批隐性的变化,而我们的验收只测了「能不能打开」。
这篇文章不复述升级步骤(那些网上一查就有),而是记录这三个改起来最费劲的点:字符集导致的索引与连接开销、连接建立成本的变化、以及执行计划在统计信息不同之后的漂移。每个点都给出我们实际用的判定命令和最终的改法。如果你的站点也要做这种跨越式的版本升级,这几条应该能省掉不少弯路。
第一个问题:字符集不是一个「设置」,它会一路影响到索引和连接
先说症状。升级之后,后台有几个按标题搜索的页面从 200 毫秒涨到 1.5 秒,其他页面正常。慢查询日志里那条语句本身不复杂,WHERE title LIKE '%关键字%',在 5.7 上一直是全表扫描,五万行的表跑 200 毫秒,我们一直默认它「就这样」。但 8.0 上同样一条语句变成 1.5 秒,执行计划却没变。
问题出在连接建立这一侧的字符集协商上。5.7 的默认 character_set_server 是 latin1(我们当初建库时没显式指定,库表是 utf8,客户端连上来是 utf8mb4),而 8.0 把服务端默认改成了 utf8mb4,这本是好事,但它同时把 collation_server 的默认值带到了 utf8mb4_0900_ai_ci。结果是:库里的表用 utf8mb4_general_ci,连接排序规则是 utf8mb4_0900_ai_ci,两边的 collation 不一致。
当 collation 不一致时,比较运算需要在两套排序规则之间做转换,对于带 LIKE 或 ORDER BY 的查询,优化器会放弃一部分下推能力,甚至在 EXPLAIN 里出现 Using temporary; Using filesort 而 5.7 上没有。更隐蔽的是,这类不一致还会让索引无法用于比较:一个在 5.7 上还能走索引的等值查询,到了 8.0 变成全表扫,而 EXPLAIN 不会告诉你「因为 collation 不匹配所以没用索引」,它只是老老实实写着 type: ALL。
判定方法很直接,三组值摆在一起看:
SELECT @@character_set_server, @@collation_server;
SELECT @@character_set_connection, @@collation_connection;
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'site' AND table_collation <> 'utf8mb4_general_ci';
SHOW CREATE TABLE site.posts\G我们跑完发现有三张表是 utf8mb4_unicode_ci(更早的一次迁移留下的),其余是 general_ci,而连接默认已经是 0900_ai_ci——三种排序规则同时存在。这就解释了为什么只有那几张表的查询变慢,而不是全站。
改法上我踩了一个坑值得说:一开始图省事,直接改了服务端默认值 collation_server = utf8mb4_general_ci,慢查询确实回来了。但几天后发现新写入的中文在某个导出功能里是乱码——原因是 0900_ai_ci 和 general_ci 对某些字符的比较结果不同,而导出脚本自己设了 SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci,两边比较时被判为「不相等」,某些看起来一样的字符串被当成不同的键。这说明改默认值只是掩盖了不一致,没有消除它。
真正的做法是把三处统一到同一个 collation,然后修正连接。我们最终选了 utf8mb4_0900_ai_ci 作为唯一标准,理由有三:8.0 的原生默认、支持更完整的 Unicode 排序、以及 _general_ci 在 8.0 上的一些历史行为已经被标记为不推荐。统一之后,所有客户端连接必须显式声明 collation,不能只写 charset:
# my.cnf
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
# 应用侧(PHP PDO 示例)
# new PDO('mysql:host=...;dbname=site;charset=utf8mb4', $u, $p,
# [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci"]);顺序上必须先改表、再改连接、最后改默认值。反过来做的话,中间会有一段时间表和连接不一致,表现就是「有时候对有时候不对」,很难查。改表用 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,对大表要评估锁——8.0 的 ALGORITHM=INPLACE 在很多情况下可用,但 CONVERT TO 往往还是要重建表,五万行无所谓,百万行就得排窗口。
第二个问题:连接建立成本变了,小站的连接池被打回原形
第二个症状是「第一个请求慢、后面快」。这在 5.7 时代也存在,但幅度很小,我们一直没管。8.0 上这个差距被放大到 2~3 秒,用户感知就是「点一下,转圈半天,然后突然全出来了」。
原因是 8.0 默认启用了 caching_sha2_password 作为认证插件。第一次连接时,如果服务端没有缓存该用户的认证信息,客户端需要走一次完整的 RSA 公钥交换(或 TLS),这在跨机房、且客户端到服务端 RTT 二三十毫秒的情况下,会被放大成几百毫秒到几秒。而且这个成本是每个用户名各一次,缓存命中之后就消失了——正好符合「第一个慢、后面快」的形态。
SELECT user, host, plugin FROM mysql.user;
# 看到 caching_sha2_password 的就是 8.0 新默认
# 临时验证:把某个应用账号换回旧插件(确认是不是这个原因)
ALTER USER 'site'@'%' IDENTIFIED WITH mysql_native_password BY '...';
FLUSH PRIVILEGES; -- 8.0.11+ 里 ALTER USER 本身就会重新加载权限,FLUSH 可省但我不建议就此长期用回 mysql_native_password:它在 8.0 里已被标记为弃用,后面的版本会移除;而且它的哈希方式更弱。我们最后采用的是两条并行措施:给应用侧换成支持 caching_sha2_password 的新驱动(PHP 的 mysqlnd 需要 7.4 以上才有完整支持),同时把「连接建立成本」当成一个必须被摊薄的固定开销——也就是把连接池做起来。
这台机器是 1G 内存的小机,做不了大池子,所以我的策略是:常驻连接数固定为 CPU 核数 + 2(这里是一核,所以 3 条),用 wait_timeout 控制空闲连接的存活时间,把 wait_timeout 设成略小于应用层的回收时间。这里有个很容易配反的关系:如果 MySQL 先断开(wait_timeout 小于应用的 idle 检测),应用拿到的是一个已经死掉的连接,报 MySQL server has gone away;反之,应用先回收就只是浪费一点内存,安全得多。所以我们把 wait_timeout = 120,应用侧 idle 检测设 60 秒。
# my.cnf(1G 小机上的保守值)
[mysqld]
max_connections = 80
wait_timeout = 120
interactive_timeout = 120
max_allowed_packet = 16M
还有一个常被忽略的细项:skip_name_resolve。默认情况下 MySQL 会对每个新连接做一次反向 DNS 查询来确认主机名,在小机上这一次查询可能耗时几十到几百毫秒,而且会串行影响连接建立。把 skip_name_resolve = ON 打开之后,权限表里的 host 必须写成 IP 或通配,这也是我一直推荐的做法——避免 DNS 故障连带数据库连接一起挂掉。
第三个问题:执行计划的漂移,比你想象得更普遍
第三类症状最不显眼:同样的 SQL、同样的数据,某些语句偶尔变慢,重跑一次又好。8.0 换了优化器和统计信息的采集方式,直方图(histogram)变成可用的东西,代价模型也有调整。对我们这种历史遗留了一堆子查询和 ORDER BY RAND() 的站来说,5.7 上「运气好走得通」的计划,在 8.0 上可能换成了另一个同样合法但慢得多的计划。
我们的排查顺序是固定的三步。第一步拿 EXPLAIN ANALYZE 而不是 EXPLAIN——8.0 的这个功能在 5.7 上没有,它会把每一行的实际耗时和预估行数打出来,一眼就能看出是「预估行数错了」还是「执行本身慢」:
EXPLAIN ANALYZE
SELECT p.id, p.title, (SELECT COUNT(*) FROM comments c WHERE c.pid = p.id) AS n
FROM posts p
WHERE p.status = 1
ORDER BY p.id DESC
LIMIT 20;这一步立刻暴露了问题:子查询里的 COUNT(*) 被对每一行执行了一次,而 comments.pid 上有索引但并不覆盖 pid 之外的信息,20 行就是 20 次索引查找加回表。5.7 上因为 LIMIT 下推的策略不同,实际只跑了 20 次;8.0 上 ORDER BY id DESC 让它先排序再取,行为一样但额外的排序成本更高。改法不是调参数,而是把这句改成一次聚合:
SELECT p.id, p.title, COALESCE(c.n, 0) AS n
FROM posts p
LEFT JOIN (SELECT pid, COUNT(*) AS n FROM comments GROUP BY pid) c
ON c.pid = p.id
WHERE p.status = 1
ORDER BY p.id DESC
LIMIT 20;第二步是看 information_schema.statistics 里的基数(Cardinality)与真实数据是否差一个量级。8.0 的统计信息默认更频繁地自动更新,但小站数据变化慢,反而容易出现「统计信息滞后」的假象。手动跑一次 ANALYZE TABLE posts, comments; 并前后对比执行计划,如果计划变回了预期的形状,就说明问题在统计信息而不是索引本身。
第三步,也是我最推荐在所有升级后立刻做的一件事:把慢查询阈值调低到一个会难受的水平。long_query_time = 0.3(而不是默认 10),配合 log_queries_not_using_indexes = OFF(这个开关打开会把日志刷爆,而且大部分是全表扫描的小表,没有价值)。跑一天,按「相同语句指纹」聚合,你会得到一份非常具体的问题名单,比在那里空想「哪里可能慢」有效得多。8.0 还有个好处是 performance_schema 里的 events_statements_summary_by_digest 可以直接按平均延迟排序:
SELECT DIGEST_TEXT, COUNT_STAR, ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms,
SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;SUM_ROWS_EXAMINED 与 SUM_ROWS_SENT 的比值特别值得看:比值大意味着查了很多行只返回很少,通常是索引不合适;差值极小说明是全表扫返回全表,那种语句一般来说本身就不该出现在线上。
回滚式的改法:为什么我们坚持一次只动一件事
整个修复过程我们前后做了将近两周,节奏是每次只改一处、改完观察至少 24 小时、并且每一处都留了可回退的操作。这个纪律不是洁癖:升级带来的问题会互相掩盖,如果一次改五处,第二天变快了,你无法知道是哪处起的作用,也无法在出问题时退回去。实际做的顺序是:
- 统一 collation(改表 → 改连接 → 改默认值),观察慢查询数量。
- 打开
skip_name_resolve,观察连接建立时间的 P95。 - 把应用驱动升级到支持
caching_sha2_password的版本,观察首请求延迟。 - 改掉那条带子查询的 SQL,观察页面耗时。
- 调低慢查询阈值到 0.3 秒,跑一天收一份清单,按清单逐条处理。
每一步的命令都写进了一个 upgrade-8.0.md,包括对应的回退语句。这份文件后来救了我们一次:第 3 步升级驱动之后,有个后台功能报 commands out of sync,我们照文档退回旧驱动、确认问题复现、再定位到具体是哪个未释放的 mysqli_result,全程不到二十分钟。
一点感觉:版本升级的验收清单应该包含什么
回头看,这次的教训不在技术上,而在验收标准上。我们当时的验收是「首页能开、后台能登录、文章能发」,这三条只覆盖了功能,完全没有覆盖性能与数据一致性。如果让我重做一次,验收清单会是四组:
- 功能:核心路径能走通,包括登录、发布、搜索、上传、导出。
- 数据正确性:随机抽 20 行中文与非中文字符串,比较新旧库的
HEX()与排序结果;重点看LIKE与ORDER BY的返回顺序是否一致。 - 性能:升级前先采一份基线(首页、列表页、搜索页的 P50/P95,以及
performance_schema的 top 10 语句),升级后同一时段同口径再采一份,差异超过 30% 的逐条解释。 - 连接:首请求延迟、连接建立时间、以及
max_connections的峰值水位——这三项在 8.0 上几乎必然会有变化,提前知道比事后发现好。
MySQL 8.0 本身是个好东西,我们最后也确实享受到了窗口函数和更好的性能诊断。真正的问题从来不是「新版不好」,而是「旧版的默认值恰好一直在容忍我们的一堆不规范」。版本一换,这些不规范就集体浮出了水面。这么想的话,这次的两周并不算白费:它把四年积累的隐性问题一次清掉了大半。