首页 > 数据库 >Mysql因为字段字符集编码的问题导致索引没生效的解决方案

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

来源：互联网 2026-04-13 21:58:31

原始SQL查询语句 SELECT s.department_name AS departmentName, cps.purchase_type AS purchaseType FROM settlement_records s LEFT JOIN common_products_specificat

原始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结果，查询使用了为连接字段创建的索引，但并未完全发挥其效力。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

一个关键的指标是key_len=8。这个数值表明，建立的三列复合索引在本次查询中仅使用了第一列org_id。该列的数据类型为bigint，占用8个字节。

Extra列出现的“Using where”进一步验证了这一点。这意味着MySQL只能先通过索引筛选出满足org_id条件的行，然后在服务器层面对剩余的两个条件（specification_system_sn和delete_flag）进行逐行过滤。

由此产生的性能问题是：驱动表返回的约31780行结果，每一行都需要与被驱动表中约6469行数据进行匹配，总计需要进行近两亿次比对，导致查询速度显著下降。

复合索引未完全生效的原因

索引设计看似合理，但在连接操作中却未能完全匹配，其根本原因通常在于细节差异。

通过执行以下查询可以发现，连接两端的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迁移至8.0时，为保持兼容而未进行统一转换。

统一数据库字符集为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;

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

-- 生成修改字符集的语句
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`;

-- 接着批量修改所有相关表的字符集
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`);
-- ...（此处省略其他约束的重建语句）