Msql修改like左匹配方案-翻转索引

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|

---