Mysql因為字段字符集編碼的問題導(dǎo)致索引沒生效的解決方案

更新時間：2025年12月18日 10:50:20 作者：諾淺

通過分析MySQL的EXPLAIN結(jié)果,發(fā)現(xiàn)復(fù)合索引只使用了部分字段,原因是字符集不一致導(dǎo)致的,修改字符集為utf8mb4_0900_ai_ci后,查詢性能顯著提升,文章還介紹了MySQL字符集的演進以及如何統(tǒng)一數(shù)據(jù)庫字符集

我的原始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;

兩張表在關(guān)鍵字段上都有索引

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結(jié)果分析

[
  {
    "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，這個很關(guān)鍵，說明索引只用到了org_id列，這一列的數(shù)據(jù)類型是bigint，長度剛好是8。

Extra: Using where 表示索引沒覆蓋 JOIN 的所有條件，還需要額外過濾 specification_system_sn 和 delete_flag。

慢的原因：

MySQL 拿著 31780 行 s 的結(jié)果，去 cps 里掃 6469 行，做 N × M 的匹配，代價就非常大了。

idx_cps_org_spec_del (org_id, specification_system_sn, delete_flag) 索引沒用完整，EXPLAIN 里只用到了 org_id，說明 specification_system_sn 和 delete_flag 沒被成功利用。

為什么復(fù)合索引只匹配到了org_id

那這就很奇怪了，我的索引明明是復(fù)合索引，為什么只會用到前面的org_id？

最終通過如下語句發(fā)現(xiàn)原來是specification_system_sn字段的字符集不一致的原因

SHOW FULL COLUMNS FROM settlement_records LIKE 'specification_system_sn';
-- 結(jié)果：utf8mb4_0900_ai_ci

SHOW FULL COLUMNS FROM common_products_specification LIKE 'specification_system_sn';
-- 結(jié)果：utf8mb3_general_ci

MySQL 的字符集和排序規(guī)則不一致是導(dǎo)致索引只用到 org_id 的直接原因。

MySQL 在 JOIN 時也認為兩邊的列類型不完全匹配，因此無法在索引上做完整匹配，只能先用 org_id 掃一遍，再在內(nèi)存里過濾 specification_system_sn。

修改字符集

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

修改了之后就很快了，一秒不要就出結(jié)果。

為什么兩個字符集不一致呢？

這其實是 MySQL 的歷史產(chǎn)物和版本差異在作怪。

MySQL 字符集演進：

utf8mb3：原來的“utf8”，每個字符最多 3 個字節(jié)。MySQL 5.5 以前是主流，很多老表默認就是 utf8mb3_general_ci。
utf8mb4：從 MySQL 5.5 開始推薦，用來完整支持 Unicode（比如 Emoji、少數(shù)民族字符），每個字符最多 4 個字節(jié)。
utf8mb4_0900_ai_ci：MySQL 8.0 默認的新 collation，基于 Unicode 9.0，比 utf8mb4_general_ci 排序更標準，支持更多 Unicode 特性。

所以原因就只有2個：

有人建表時指定了字符集和排序規(guī)則
進行過數(shù)據(jù)庫遷移，原來用的5，后面遷移到8了，mysql遷移時會默認保留原字符集和排序規(guī)則

如何把整個庫的所有表及字段的字符集都統(tǒng)一為utf8mb4_0900_ai_ci

因為我用的是mysql8，所以可以統(tǒng)一字符集規(guī)則為utf8mb4_0900_ai_ci，怎么做呢？

查詢并修改現(xiàn)在數(shù)據(jù)庫默認的字符集

-- 查詢數(shù)據(jù)庫默認字符集
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;

查詢現(xiàn)有的不是這個字符集的表并生成修改語句

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

-- 改完之后可以查詢一下現(xiàn)在表的字符集
-- 查詢所有表字符集
SELECT
    table_name,
    table_collation
FROM information_schema.tables
WHERE table_schema = 'datebase_name'
  AND table_type = 'BASE TABLE';

執(zhí)行上面的語句

關(guān)于qrtz框架的特殊處理

因為這個框架的表有外鍵約束，無法直接改，需要先刪除約束，改完了再創(chuàng)建約束，完整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`);