记一次索引失效经历

数据库：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;

         再检查执行计划：

 就会发现索引生效了