Mysql join语句索引失效-数据表使用不同字符集

背景

表P

CREATE TABLE `P` (
  `c_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `c_platform_uid` varchar(32) NOT NULL DEFAULT '' COMMENT '平台uid',
  `c_school_uid` varchar(32) NOT NULL DEFAULT '' COMMENT '学校uid'
  PRIMARY KEY (`c_id`),
  UNIQUE KEY `PLATFORM_SCHOOL_KEY` (`c_platform_uid`,`c_school_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表B

CREATE TABLE `B` (
  `c_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
  `c_school_uid` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '学校主键',
  PRIMARY KEY (`c_id`),
  UNIQUE KEY `unique_uid` (`c_school_uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

业务SQL

SELECT COUNT(1)
FROM P
	LEFT JOIN B ON P.c_school_uid = B.c_school_uid
WHERE P.c_is_deleted = 0
	AND B.c_is_deleted = 0
	AND P.c_platform_uid = '1';

现象

从上边的背景看出数据表字符集不一致，连表字段字符集也出现了一个utf-8

explain结果

这里看出主表和从表都命中了索引，但是从表也仅仅是index类型的访问

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

上线后，在数据量不过万级的情况下，还是出现了部分慢sql的报警

show warnings

拿到测试环境验证，增加了一次show warnings的检查

'Note', 
'1003', 
'/* select#1 */ 
select 
    count(1) AS `COUNT(1)` 
from 
    B join P 
where 
    ((`P`.`c_is_deleted` = 0) 
    and (`P`.`c_platform_uid` = \'1\')
    and (`P`.`c_school_uid` = convert(`B`.`c_school_uid` using utf8mb4)))'

可以看到mysql给出的优化意见是有字符集转换的建议

处理

修改字符集

修改了B表和连表字段c_school_uid字符集为 utf8mb4后再查看explain

访问类型升级到eq_ref，从表结果集大幅度降低