一次Hash索引失效问题

在工作过程中遇到一个创建的Hash索引失效问题,表结构如下

CREATE TABLE `auth_priority_interface_mapping` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
  `uri` varchar(200) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '接口url',
  `uri_crc32` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '接口的crc32',
  ...
  PRIMARY KEY (`id`),
  KEY `idx_crc_32` (`uri_crc32`(20)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2231 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='权限接口关联表';

uri_crc32为索引列,我在使用如下查询语句时

SELECT * FROM auth_priority_interface_mapping WHERE uri_crc32 = CRC32('/account-center/auth/ajax-prepare-data') and uri ='/account-center/auth/ajax-prepare-data';

explain结果如下:
隐式类型转换explain结果.png

这个很好理解,CRC32函数返回值为整型,表结构中uri_crc32为字符串类型,这里有一个隐式类型转换,导致索引失效

解决方案:

使用如下SQL

EXPLAIN SELECT * from auth_priority_interface_mapping WHERE uri_crc32 = CONCAT(CRC32('/account-center/auth/ajax-prepare-data'),'') and uri = '/account-center/auth/ajax-prepare-data';

explain结果:

去除隐式类型转换explain结果.png

将CRC32函数返回值转换为字符串就可以了

注意:可以在=右边进行类型转换,但不可以在左边对索引列进行类型转换。

Q.E.D.


Read The Fucking Source Code!