数据库:mysql
出现场景描述:
索引字段的字符集不一致导致索引失效!!!
样例sql如下,理论上这条 sql 执行计划关联 B 表操作的类型应该是 ref,但是出现了 type=ALL。
通过 show full columns from A和B,其中,A 表字段字符集 utf-8mb4,排序规则是 utf8mb4_gen-eral_ci,B 表字符集 utf-8,排序规则是utf8_general_ci。
解决方案:统一字符集
-- A_NO 和 B_NO 都有索引
SELECT A.*,B.* FROM A LEFT JOIN B ON A.B_NO = B.B_NO WHERE A.A_NO='202112080541100001';
情景再现:
CREATE TABLE `T_ITEM_UTF8` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`ITEM_NO` varchar(32) DEFAULT NULL COMMENT 'item编号',
`ITEM_NAME` varchar(255) DEFAULT NULL COMMENT 'item名称',
PRIMARY KEY (`id`),
KEY `IDX_ITEM_NO` (`ITEM_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `T_ORDER_UTF8MB4` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`MOBILE_NO` varchar(32) DEFAULT NULL COMMENT '手机号',
`ITEM_NO` varchar(32) DEFAULT NULL COMMENT 'ITEM编号',
PRIMARY KEY (`id`),
KEY `IDX_MOBILE_NO` (`MOBILE_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
INSERT INTO T_ITEM_UTF8 (ITEM_NO,ITEM_NAME) VALUES ('aaaa','A类产品');
INSERT INTO T_ITEM_UTF8 (ITEM_NO,ITEM_NAME) VALUES ('bbbb','B类产品');
INSERT INTO T_ITEM_UTF8 (ITEM_NO,ITEM_NAME) VALUES ('cccc','C类产品');
INSERT INTO T_ORDER_UTF8MB4 (MOBILE_NO,ITEM_NO) VALUES ('121手机号','aaaa');
INSERT INTO T_ORDER_UTF8MB4 (MOBILE_NO,ITEM_NO) VALUES ('123手机号','aaaa');
INSERT INTO T_ORDER_UTF8MB4 (MOBILE_NO,ITEM_NO) VALUES ('124手机号','bbbb');
INSERT INTO T_ORDER_UTF8MB4 (MOBILE_NO,ITEM_NO) VALUES ('125手机号','cccc');
执行计划:
EXPLAIN SELECT
T1.MOBILE_NO,
T2.ITEM_NAME
FROM
T_ORDER_UTF8MB4 T1
LEFT JOIN T_ITEM_UTF8 T2 ON T1.ITEM_NO = T2.ITEM_NO
WHERE
T1.MOBILE_NO = '121手机号';
查看字段字符集:
SHOW FULL COLUMNS FROM T_ITEM_UTF8;
SHOW FULL COLUMNS FROM T_ORDER_UTF8MB4;
统一字符集 utf8mb4:
ALTER TABLE T_ITEM_UTF8 CHANGE ITEM_NO ITEM_NO VARCHAR(32) CHARACTER SET utf8mb4;
再检查执行计划:
就会发现索引生效了