Msql修改like全模糊匹配方案-全文索引

最新推荐文章于 2023-12-06 10:26:56 发布

zaink2022

最新推荐文章于 2023-12-06 10:26:56 发布

阅读量395

点赞数

分类专栏： java 学习笔记文章标签： mysql 数据库 sql java

本文链接：https://blog.csdn.net/z313410164/article/details/118970633

版权

学习笔记同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

java

7 篇文章 0 订阅

订阅专栏

本文介绍了如何使用MySQL的全文索引和ngram分词器优化大表的模糊查找性能。当数据量增大时，普通的LIKE查询会导致全表扫描，性能下降。通过创建全文索引并使用ngram分词器，可以实现对手机号码这类纯数字字段的高效模糊匹配。添加全文索引后，查询语句改为使用MATCH() AGAINST()，从而提高查询效率。

摘要由CSDN通过智能技术生成

Msql修改like全模糊匹配方案-全文索引

作者	版本	时间
313410164	1.0	2021-7-21

背景

当前存在表：

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会走全表扫描，性能会急剧下降。

explain 查看SQL分析结果：

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra      |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-----------+
 1|SIMPLE     |u    |          |ALL |             |   |       |   |12617|   11.11|Using where|

改造方案 - 全文检索

Mysql对于大表的模糊查找，自身提供了一种全文检索^[1]的方案。其中全文检索普通模式是按照英语的习惯做自然词拆分，以空格来界限词的开始和结束，最小分词大小由参数(最小ft_min_word_len默认4,最长词ft_max_word_len默认84)控制，在本例中，字段mobile记录的为手机号码，是11位纯数字，按照自然词拆分无法实现。

Mysql内置了ngram分词器^[2]，本身是用于非英语环境的分词。该分词器的分词大小不受默认分词的最小/最大分词大小控制，使用ngram_token_size，默认值2，同时该分词器会忽略空格。

这个参数意味着，默认情况下：

“ab cd"被解析为"ab”， “cd”
“a bc"被解析为"bc”

该分词器符合满足需求。

添加全文索引

ALTER TABLE `pumpkin-user-0801`.`user` ADD FULLTEXT INDEX user_mobile_IDX (mobile) WITH PARSER ngram;

查询SQL修改为：

SELECT * from `user` u where MATCH (mobile) against ('xxxx')

explain 分析SQL，走全文索引

id|select_type|table|partitions|type    |possible_keys        |key                  |key_len|ref  |rows|filtered|Extra                        |
--+-----------+-----+----------+--------+---------------------+---------------------+-------+-----+----+--------+-----------------------------+
 1|SIMPLE     |u    |          |fulltext|user_mobile_ftext_idx|user_mobile_ftext_idx|0      |const|   1|   100.0|Using where; Ft_hints: sorted|