Msql修改like左匹配方案-翻转索引
背景
当前存在表:
CREATE TABLE `user` (
`uid` bigint NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '真实姓名',
`mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机号, 唯一',
... 省略其他字段
PRIMARY KEY (`uid`) USING BTREE,
UNIQUE KEY `USER_MOBILE` (`mobile`) USING BTREE COMMENT '手机号唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=20014 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
要求对mobile实现前缀匹配
功能实现方案
SELECT * FROM `user` WHERE mobile like '%xxxx'
该SQL在user
表数据量小的时候,性能问题较小,随着数据量增大的时候,由于左模糊匹配MYSQL会走全表扫描,性能会急剧下降。
改造方案 - 翻转函数索引
考虑like 'xxxx%'
可以走索引,将左模糊改成右模糊即可。
针对mobile字段建立虚拟列[2][3]r_mobile
:
ALTER table `user` add column r_mobile varchar(50) GENERATED ALWAYS AS (reverse(mobile)) virtual
mobile
本身作为唯一索引列,使用函数reverse
[4]翻转后同样是唯一的,同样给翻转后的列添加唯一索引:
CREATE UNIQUE INDEX user_r_mobile_uk_idx USING BTREE ON `user` (r_mobile)
查询SQL修改为:
SELECT * FROM `user` u WHERE r_mobile LIKE REVERSE('%xxxx')
SQL分析结果索引覆盖,解决like
左匹配问题
Name |Value |
-------------+---------------------------------------------------------------+
id |1 |
select_type |SIMPLE |
table |u |
partitions | |
type |index_merge |
possible_keys|USER_MOBILE,user_r_mobile_uk_idx |
key |USER_MOBILE,user_r_mobile_uk_idx |
key_len |203,203 |
ref | |
rows |5 |
filtered |100.0 |
Extra |Using sort_union(USER_MOBILE,user_r_mobile_uk_idx); Using where|