Mysql因为字段字符集编码的问题导致索引没生效的解决方案

首页

数据库

热心网友

转载

2026-04-30

深入解析SQL查询性能问题：字符集不一致导致的索引失效

SELECT s.department_name                                 AS departmentName,
       cps.purchase_type                                 AS purchaseType
FROM settlement_records s
         LEFT JOIN common_products_specification cps
                   ON cps.org_id = s.purchase_org_id AND cps.specification_system_sn = s.specification_system_sn AND
                      cps.delete_flag = 0
WHERE s.delete_flag = 0
  AND s.purchase_org_id = 1540
  AND s.purchase_org_type = 1;

两张表在关联字段上都建立了索引，这是数据库性能优化的良好实践，为查询效率奠定了基础。

create index idx_settlement_join on settlement_records (purchase_org_id, purchase_org_type, delete_flag, product_id, vendor_id);
create index idx_settlement_org_del on settlement_records (purchase_org_id, purchase_org_type, delete_flag);

create index idx_cps_org_spec_del on common_products_specification (org_id, specification_system_sn, delete_flag);

EXPLAIN执行计划深度分析

[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "s",
    "partitions": null,
    "type": "ref",
    "possible_keys": "idx_settlement_org_del,idx_settlement_join",
    "key": "idx_settlement_org_del",
    "key_len": "13",
    "ref": "const,const,const",
    "rows": 31780,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "cps",
    "partitions": null,
    "type": "ref",
    "possible_keys": "idx_cps_org_spec_del",
    "key": "idx_cps_org_spec_del",
    "key_len": "8",
    "ref": "const",
    "rows": 6469,
    "filtered": 100,
    "Extra": "Using where"
  }
]

从EXPLAIN执行计划分析，查询虽然使用了为连接条件创建的idx_cps_org_spec_del索引，但并未充分发挥其效能。

关键线索在于key_len=8这个数值。 这个长度表明，这个包含三列的复合索引，在实际查询中仅使用了第一列org_id。该列的数据类型为bigint，在MySQL中恰好占用8字节。

Extra列出现的“Using where”进一步证实了这一点。这意味着MySQL只能先利用索引定位满足org_id条件的所有数据行，然后返回服务器层，逐行比对specification_system_sn和delete_flag这两个条件。

性能瓶颈由此产生： 驱动表settlement_records返回的31780行结果，每一行都需要在被驱动表中匹配约6469行数据，总计进行超过两亿次的逐行比较。这种大规模的回表操作，是导致SQL查询缓慢的根本原因。

复合索引为何无法完全匹配？

这引出了核心疑问：索引结构设计看似合理，为何在表连接时无法完全生效？

答案通常隐藏在细节之中。通过执行以下查询，我们发现了问题的根源——连接双方在specification_system_sn字段的字符集编码上存在差异：

SHOW FULL COLUMNS FROM settlement_records LIKE 'specification_system_sn';
-- 结果：utf8mb4_0900_ai_ci
SHOW FULL COLUMNS FROM common_products_specification LIKE 'specification_system_sn';
-- 结果：utf8mb3_general_ci

问题真相大白。MySQL字符集和排序规则不一致，是导致索引失效的罪魁祸首。 MySQL在执行JOIN操作时，发现两列虽然字段名相同，但底层编码规则不同，无法直接进行高效的索引匹配。因此，数据库优化器只能退而求其次，仅使用索引的第一列进行过滤，将更精确的匹配工作留给代价更高的内存过滤。

解决方案直接明了： 统一字符集编码。

ALTER TABLE common_products_specification
  MODIFY specification_system_sn VARCHAR(50)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

完成字符集统一后，查询性能立即得到显著提升，从“长时间等待”转变为“秒级响应”，优化效果立竿见影。

字符集不一致的成因分析

这背后，往往是MySQL版本演进和数据库迁移历史留下的“技术债务”。

简要回顾MySQL字符集的发展历程：

utf8mb3：这是MySQL历史上“标准的UTF-8”实现，每个字符最多使用3个字节表示。在MySQL 5.5版本之前是默认选择，许多遗留系统的表结构都基于此字符集创建。
utf8mb4：从MySQL 5.5版本开始被广泛推荐，使用4个字节完整支持所有Unicode字符（包括Emoji表情、特殊符号等），逐渐成为新项目的默认字符集标准。
utf8mb4_0900_ai_ci：MySQL 8.0时代的官方默认排序规则，基于Unicode 9.0标准，相比旧的排序规则更加精确和标准化。

因此，字段字符集不一致通常源于两个原因：一是建表时人为指定了不同的编码规则；二是更常见的情况——数据库从旧的MySQL 5.x版本迁移到8.0时，为保持兼容性，MySQL默认保留了原有的字符集设置，未进行全局统一转换。

如何将整个数据库统一为utf8mb4_0900_ai_ci字符集

既然找到了问题根源，对于运行在MySQL 8.0环境中的数据库，完全可以考虑将整个数据库的字符集统一为utf8mb4_0900_ai_ci，从根本上避免类似问题。以下是标准的操作流程。

第一步：检查并修改数据库默认字符集

-- 查询数据库当前字符集设置
SELECT
    schema_name AS database_name,
    default_character_set_name AS character_set,
    default_collation_name AS collation
FROM information_schema.schemata
WHERE schema_name = 'datebase_name';

-- 若字符集不符合目标，则执行修改
ALTER DATABASE your_database_name
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

第二步：批量生成并执行表字符集修改语句

-- 生成修改表字符集的SQL语句
SELECT CONCAT(
    'ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS alter_sql
FROM information_schema.tables
WHERE table_schema = 'datebase_name'
  AND table_type = 'BASE TABLE'
  AND (table_collation != 'utf8mb4_0900_ai_ci' OR table_collation IS NULL);

-- 执行后验证修改结果
SELECT
    table_name,
    table_collation
FROM information_schema.tables
WHERE table_schema = 'datebase_name'
  AND table_type = 'BASE TABLE';

执行上述生成的ALTER TABLE语句即可完成表级字符集转换。

特殊处理：Quartz调度框架相关表

需要特别注意，如果数据库中使用了Quartz等任务调度框架的表，这些表通常包含外键约束，直接修改字符集会导致错误。处理这类表需要遵循“先解除约束，后修改字符集，最后重建约束”的流程：

-- 第一步：删除外键约束
ALTER TABLE `qrtz_triggers` DROP FOREIGN KEY `qrtz_triggers_ibfk_1`;
ALTER TABLE `qrtz_simple_triggers` DROP FOREIGN KEY `qrtz_simple_triggers_ibfk_1`;
ALTER TABLE `qrtz_cron_triggers` DROP FOREIGN KEY `qrtz_cron_triggers_ibfk_1`;
ALTER TABLE `qrtz_simprop_triggers` DROP FOREIGN KEY `qrtz_simprop_triggers_ibfk_1`;
ALTER TABLE `qrtz_blob_triggers` DROP FOREIGN KEY `qrtz_blob_triggers_ibfk_1`;

-- 第二步：批量修改所有Quartz相关表的字符集
ALTER TABLE `qrtz_blob_triggers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- ...（此处省略其他表的修改语句，参考原文完整列表）

-- 第三步：按原结构重新建立外键约束
ALTER TABLE `qrtz_triggers`
ADD CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`sched_name`) REFERENCES `qrtz_job_details`(`sched_name`);
-- ...（此处省略其他约束的重建语句，参考原文完整列表）

总结与最佳实践

统一数据库字符集是解决此类隐式索引失效问题的根本方法，但操作本身存在一定风险。对于数据量较大的表，ALTER TABLE操作会锁定表结构，可能影响在线业务运行。因此，务必在业务低峰期执行此类操作，并且执行前必须做好完整的数据备份。数据安全永远是第一要务，谨慎操作至关重要。

本文完整复盘了一次由字符集不一致引发的SQL性能问题的排查与解决过程，为数据库开发者和DBA在排查类似“疑难杂症”时提供了清晰的思路和实用的解决方案。

来源:https://www.jb51.net/database/355166wpl.htm

免责声明：游乐网为非赢利性网站，所展示的游戏/软件/文章内容均来自于互联网或第三方用户上传分享，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系youleyoucom@outlook.com。

上一篇：Window10系统下命令行查询Oracle安装目录和数据库实例名称下一篇：MySQL8中的保留关键字陷阱之当表名“lead”引发SQL语法错误的解决方案

热门推荐

web3.0

比特币转错地址如何找回？实用解决方案与预防指南

比特币转错地址后，交易确认即难以撤回，资金可能永久损失。若地址无效转账会被拦截；若转入陌生地址，资产由对方控制，追回困难。补救措施包括：交易未确认时可尝试RBF撤销；转入主流交易所可联系客服；转入个人地址则只能尝试联系持有人。法律追索困难，且需警惕诈骗。预防是关键，应养成小。

热心网友

05.27

AI教程

AI一键生成PPT：智能Word转PPT工具提升办公效率

智能化内容创作：AI一键将Word转为PPT，办公效率革命在快节奏的现代职场中，如何高效处理文档、将复杂信息转化为专业演示，是提升个人与团队生产力的关键。本文将深入解析智能化内容创作如何革新工作流，并重点介绍如何利用先进的AI工具，实现从Word文档到精美PPT的智能、快速转换，助您轻松应对各类汇

热心网友

05.27

AI资讯

QoderWake手机App下载安装与申请入口指南

QoderWake移动端已上线，提供APK下载及核心功能。界面针对触控优化，采用卡片布局与手势操作，适配主流安卓设备。内置轻量级Agent运行时，可独立执行原子任务。通信经平台网关加密中转，确保安全。支持多账号切换与工作空间隔离，安装包小巧、绑定简便，可同步近期任务。具备跨端协同、远程调试、任务接管等功。

热心网友

05.27

游戏攻略

麦格纳汽车零部件供应商深度解析

PowerBI与Tableau是主流数据可视化工具。PowerBI依托微软生态，侧重与Office集成及标准化报表，适合企业协作与稳定分发。Tableau擅长交互探索与视觉表达，适合深度数据分析和制作动态故事板。两者在定位、学习曲线、数据处理和可视化方面各有侧重，选择需结合团队需求、数据环境及使用场景。

热心网友

05.27

游戏资讯

无尽噩梦7幻梦怎么下载最新版预约安装教程

《无尽噩梦7幻梦》开放预约，游戏以东方玄幻为背景，玩家扮演捉鬼师探索梦境与现实。玩法融合探索解谜与多流派技能搭配，强调策略性。虚幻引擎提升画面沉浸感，并加入团队副本与社交功能，提供高清国风恐怖体验。

热心网友

05.27