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`);
-- ...（此处省略其他约束的重建语句，参考原文完整列表）