Mysql因为字段字符集编码的问题导致索引没生效的解决方案
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">我的原始sql</a></li><li><a href="#_label1">explain结果分析</a></li><li><a href="#_label2">为什么复合索引只匹配到了org_id</a></li><li><a href="#_label3">为什么两个字符集不一致呢？</a></li><li><a href="#_label4">如何把整个库的所有表及字段的字符集都统一为utf8mb4_0900_ai_ci</a></li><li><a href="#_label5">总结</a></li></ul></div><p class="maodian"><a name="_label0"></a></p><h2>我的原始sql</h2><div class="jb51code"><pre class="brush: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;
</pre></div>
<p>两张表在关键字段上都有索引</p>
<div class="jb51code"><pre class="brush:sql;">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);
</pre></div>
<div class="jb51code"><pre class="brush:sql;">create index idx_cps_org_spec_del on common_products_specification (org_id, specification_system_sn, delete_flag);
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>explain结果分析</h2>
<div class="jb51code"><pre class="brush:json;">[
{
"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"
}
]
</pre></div>
<p>可以看到走了 idx_cps_org_spec_del 索引，</p>
<p><strong>但 key_len=8，这个很关键，说明索引只用到了org_id列，这一列的数据类型是bigint，长度刚好是8。</strong></p>
<p>Extra: Using where 表示索引没覆盖 JOIN 的所有条件，还需要额外过滤 specification_system_sn 和 delete_flag。</p>
<p><strong>慢的原因：</strong></p>
<p>MySQL 拿着 31780 行 s 的结果，去 cps 里扫 6469 行，做 N × M 的匹配，代价就非常大了。</p>
<p>idx_cps_org_spec_del (org_id, specification_system_sn, delete_flag) 索引没用完整，EXPLAIN 里只用到了 org_id，说明 specification_system_sn 和 delete_flag 没被成功利用。</p>
<p class="maodian"><a name="_label2"></a></p><h2>为什么复合索引只匹配到了org_id</h2>
<p>那这就很奇怪了，我的索引明明是复合索引，为什么只会用到前面的org_id？</p>
<p>最终通过如下语句发现原来是<code>specification_system_sn</code>字段的字符集不一致的原因</p>
<div class="jb51code"><pre class="brush:sql;">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
</pre></div>
<p>MySQL 的 字符集和排序规则不一致 是导致索引只用到 org_id 的直接原因。</p>
<p>MySQL 在 JOIN 时也认为两边的列类型不完全匹配，因此 无法在索引上做完整匹配，只能先用 org_id 扫一遍，再在内存里过滤 specification_system_sn。</p>
<p><strong>修改字符集</strong></p>
<div class="jb51code"><pre class="brush:sql;">ALTER TABLE common_products_specification
MODIFY specification_system_sn VARCHAR(50)
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
</pre></div>
<p>修改了之后就很快了，一秒不要就出结果。</p>
<p class="maodian"><a name="_label3"></a></p><h2>为什么两个字符集不一致呢？</h2>
<p>这其实是 MySQL 的历史产物和版本差异在作怪。</p>
<p><strong>MySQL 字符集演进：</strong></p>
<ul><li><code>utf8mb3</code>：原来的“utf8”，每个字符最多 3 个字节。MySQL 5.5 以前是主流，很多老表默认就是 utf8mb3_general_ci。</li><li><code>utf8mb4</code>：从 MySQL 5.5 开始推荐，用来完整支持 Unicode（比如 Emoji、少数民族字符），每个字符最多 4 个字节。</li><li><code>utf8mb4_0900_ai_ci</code>：MySQL 8.0 默认的新 collation，基于 Unicode 9.0，比 utf8mb4_general_ci 排序更标准，支持更多 Unicode 特性。</li></ul>
<p>所以原因就只有2个：</p>
<ol><li>有人建表时指定了字符集和排序规则</li><li>进行过数据库迁移，原来用的5，后面迁移到8了，mysql迁移时会默认保留原字符集和排序规则</li></ol>
<p class="maodian"><a name="_label4"></a></p><h2>如何把整个库的所有表及字段的字符集都统一为utf8mb4_0900_ai_ci</h2>
<p>因为我用的是mysql8，所以可以统一字符集规则为utf8mb4_0900_ai_ci，怎么做呢？</p>
<ul><li><strong>查询并修改现在数据库默认的字符集</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">-- 查询数据库默认字符集
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;
</pre></div>
<ul><li><strong>查询现有的不是这个字符集的表并生成修改语句</strong></li></ul>
<div class="jb51code"><pre class="brush: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';
</pre></div>
<p>执行上面的语句</p>
<p><strong>关于qrtz框架的特殊处理</strong></p>
<p>因为这个框架的表有外键约束，无法直接改，需要先删除约束，改完了再创建约束，完整sql如下：</p>
<div class="jb51code"><pre class="brush: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`);
</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>总结</h2>
<p>改字符集有风险，如数据量大表会锁表，建议低峰期修改且先备份，<strong>数据无价，谨慎操作</strong></p>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]