mysql版本升级中的表结构迁移与优化-mysql教程-PHP中文网

MySQL 5.7 升级至 8.0 后 performance 下降主因有四：information_schema 查询变慢（需调优缓存或转 InnoDB）、mysql.func 表缺失（应强制升级而非手动建表）、utf8mb4 索引超限（须显式设 ROW_FORMAT=DYNAMIC）、JSON 查询退化（应建虚拟列索引或原生 JSON 索引）。

mysql版本升级中的表结构迁移与优化

MySQL 5.7 升级到 8.0 后 `information_schema` 查询变慢怎么办

升级后发现 SELECT * FROM information_schema.TABLES 响应明显延迟，甚至超时，这不是 bug，而是 MySQL 8.0 默认启用了更严格的元数据统计收集机制。旧版依赖缓存且不校验表状态，新版每次查询都可能触发隐式 OPEN TABLE 操作，尤其在表数量多、存储引擎混用（如大量 MyISAM 表未转 InnoDB）时更明显。

临时缓解：执行 SET SESSION information_schema_stats_expiry = 86400; 把元数据缓存有效期设为 24 小时（单位秒），避免频繁刷新
根本解决：升级前批量执行 ALTER TABLE tbl_name ENGINE=InnoDB;，确保所有业务表使用 InnoDB；8.0 对 MyISAM 元数据访问路径未优化
验证是否生效：查 SHOW VARIABLES LIKE 'information_schema_stats_expiry';，确认会话/全局值已变更

`mysql_upgrade` 执行失败提示 `Table 'mysql.func' doesn't exist`

这是典型“跳版本升级”遗留问题，比如从 5.6 直升 8.0，中间缺失 5.7 的系统表结构变更。MySQL 8.0 已彻底移除 mysql.func（自定义函数元数据表），改由 mysql.routines 和 mysql.parameters 承载，但 mysql_upgrade 仍试图兼容旧逻辑。

不要手动建 mysql.func 表——该表在 8.0 中无意义且会导致后续权限异常
改用 mysqld --upgrade=FORCE 启动实例，强制跳过兼容性检查，让升级流程走新路径
启动成功后，立即运行 mysql_upgrade --skip-sys-schema（加 --skip-sys-schema 避免对 sys 库重复操作）
完成后检查 SELECT routine_name FROM mysql.routines WHERE routine_type = 'FUNCTION'; 确认函数是否正常注册

升级后 `utf8mb4` 字符集行为突变：索引长度超限报错

5.7 默认 innodb_large_prefix=ON 且 ROW_FORMAT=DYNAMIC，但很多老表建表时没显式指定，实际用的是 COMPACT 格式。8.0 默认要求 ROW_FORMAT=DYNAMIC 或 COMPRESSED 才支持长 utf8mb4 索引（如 VARCHAR(255) + utf8mb4_0900_as_cs），否则建索引直接报 Specified key was too long。

JSON Toolkit

JSON文件多功能工具。支持格式化、验证、压缩、键排序及点记法路径查询。零依赖。

下载

检查当前表格式：SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='your_db';

批量修复命令示例：

ALTER TABLE your_table ROW_FORMAT=DYNAMIC;

后续建表必须显式声明：CREATE TABLE t (s VARCHAR(255)) ROW_FORMAT=DYNAMIC CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
注意：修改 ROW_FORMAT 会锁表并重建，生产环境务必在低峰期执行

JSON 字段迁移后查询性能断崖式下跌

5.7 引入 JSON 类型但优化器支持弱，很多查询靠 JSON_EXTRACT() + 函数索引勉强可用；8.0 虽增强 JSON 支持，但默认不自动为 JSON 字段生成虚拟列索引，原有查询若依赖 JSON_CONTAINS() 或路径表达式，很可能退化为全表扫描。

先定位慢查询：EXPLAIN FORMAT=JSON SELECT ... 查看是否出现 "rows": 100000 且 "type": "ALL"

为常用 JSON 路径建虚拟列+索引：

ALTER TABLE logs ADD COLUMN status VARCHAR(20) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.status'))) STORED;

CREATE INDEX idx_status ON logs(status);

避免在 WHERE 中直接写 JSON_EXTRACT(data, '$.status') = 'done'，改用虚拟列名 status = 'done'
注意：8.0.22+ 支持原生 JSON 索引（CREATE INDEX idx ON t (data->"$.status");），但仅限于 -> 操作符，不支持 JSON_EXTRACT 函数