记一次因为字段字符集编码的问题导致索引没生效的优化记录

原创

诺__浅 2025-09-30 14:50:31 ©著作权

文章标签 mysql sql ci MySQL 排序规则 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者诺__浅的原创作品，请联系作者获取转载授权，否则将追究法律责任

我的原始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"
  }
]

可以看到走了 idx_cps_org_spec_del 索引，
但 key_len=8，这个很关键，说明索引只用到了org_id列，这一列的数据类型是bigint，长度刚好是8。
Extra: Using where 表示索引没覆盖 JOIN 的所有条件，还需要额外过滤 specification_system_sn 和 delete_flag。

慢的原因： MySQL 拿着 31780 行 s 的结果，去 cps 里扫 6469 行，做 N × M 的匹配，代价就非常大了。idx_cps_org_spec_del (org_id, specification_system_sn, delete_flag) 索引没用完整，EXPLAIN 里只用到了 org_id，说明 specification_system_sn 和 delete_flag 没被成功利用。

为什么复合索引只匹配到了org_id

那这就很奇怪了，我的索引明明是复合索引，为什么只会用到前面的org_id？最终通过如下语句发现原来是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 的字符集和排序规则不一致是导致索引只用到 org_id 的直接原因。MySQL 在 JOIN 时也认为两边的列类型不完全匹配，因此无法在索引上做完整匹配，只能先用 org_id 扫一遍，再在内存里过滤 specification_system_sn。

修改字符集

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

修改了之后就很快了，一秒不要就出结果。

为什么两个字符集不一致呢？

这其实是 MySQL 的历史产物和版本差异在作怪。🤓

MySQL 字符集演进
utf8mb3：原来的“utf8”，每个字符最多 3 个字节。MySQL 5.5 以前是主流，很多老表默认就是 utf8mb3_general_ci。
utf8mb4：从 MySQL 5.5 开始推荐，用来完整支持 Unicode（比如 Emoji、少数民族字符），每个字符最多 4 个字节。
utf8mb4_0900_ai_ci：MySQL 8.0 默认的新 collation，基于 Unicode 9.0，比 utf8mb4_general_ci 排序更标准，支持更多 Unicode 特性。

所以原因就只有2个

有人建表时指定了字符集和排序规则
进行过数据库迁移，原来用的5，后面迁移到8了，mysql迁移时会默认保留原字符集和排序规则

如何把整个库的所有表及字段的字符集都统一为utf8mb4_0900_ai_ci

因为我用的是mysql8，所以可以统一字符集规则为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';

执行上面的语句
关于qrtz框架的特殊处理
因为这个框架的表有外键约束，无法直接改，需要先删除约束，改完了再创建约束，完整sql如下

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_calendars` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_cron_triggers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_fired_triggers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_job_details` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_locks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_paused_trigger_grps` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_scheduler_state` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_simple_triggers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_simprop_triggers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `qrtz_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 `qrtz_simple_triggers`
ADD CONSTRAINT `qrtz_simple_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`)
REFERENCES `qrtz_triggers`(`sched_name`, `trigger_name`, `trigger_group`);

ALTER TABLE `qrtz_cron_triggers`
ADD CONSTRAINT `qrtz_cron_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`)
REFERENCES `qrtz_triggers`(`sched_name`, `trigger_name`, `trigger_group`);

ALTER TABLE `qrtz_simprop_triggers`
ADD CONSTRAINT `qrtz_simprop_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`)
REFERENCES `qrtz_triggers`(`sched_name`, `trigger_name`, `trigger_group`);

ALTER TABLE `qrtz_blob_triggers`
ADD CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`)
REFERENCES `qrtz_triggers`(`sched_name`, `trigger_name`, `trigger_group`);