一种优化mysql select like %%的方法

最新推荐文章于 2025-09-07 10:00:00 发布
转载 最新推荐文章于 2025-09-07 10:00:00 发布 · 1.1w 阅读 · 4

本文介绍了一种通过修改MySQL配置并结合自定义分词模块来提高LIKE查询效率的方法，利用全文索引显著提升了搜索速度。

问题：mysql like %XXX%使得数据库引擎不能使用索引，而导致select查询缓慢。200M的表select like需要1s左右，这是实时性要求高的服务器承受不了的。

解决方法：首先google一通发现，都在说like很难优化，一般都是采用开源插件分词+全文索引。然后再搜索，又搜索到了clucene，研究了下clucene，发现用它的人都是重新实现的分词模块，这样如果不实现中文分词模块，clucene的作用也不大。如果搜索的字符串长度不大的话，比如是搜索文章标题，那么自己来实现分词模块也不是很难。下面的方法就是针对这类需求而实现的。

基本原理是：把mysql配置文件中的ft_min_word_len=3改为1。（没有这项就直接添加），然后新建一个字段来保持分词结果，给这个字段建立全文索引。然后实现一个分词模块，把词语“大家好”拆分为“大 大家 大家好 家 家好 好”。然后用match .. against 来代替like %%，查询出来的结果跟like的结果基本相同（如果分词合理的话），但是效率比like高至少10倍以上。

具体实现见http://www.xiaoyatou.net/gitweb/?p=libyt.git;a=blob;f=luaTest/SplitWords.h;h=8d10daf10484b48d0534547ec15c295d82de2a79;hb=HEAD

http://www.xiaoyatou.net/gitweb/?p=libyt.git;a=blob;f=luaTest/SplitWords.cpp;h=b3cfe13b8952eda7819bf012779e10e9ff45a12c

告别LIKE+%：MySQL模糊查询的高效替代方案
墨夶的博客
11-28 1013
通过全文索引、正则表达式等高级技术，我们可以显著提升MySQL数据库的模糊查询性能，为用户提供更快、更准确的搜索体验。在未来的工作中，不断学习和探索新技术，将是我们不断提升数据库性能、优化用户体验的关键。的模糊查询技术，包括全文索引和正则表达式，以及一些性能优化的小贴士。这可以让你执行更复杂的匹配操作，比如查找以特定字母开头或结尾的字符串，或者匹配特定模式的字符串。：对于不常变动的数据，可以利用查询缓存来减少重复查询的开销。在数据库查询中，模糊查询是一个常见的需求，但传统的。随着数据量的不断增长，传统的。
MySQL--Like %XXX% 优化测试
m0_62754777的博客
10-13 471
002
like‘%xxxxx%‘性能优化
riven2011的博客
09-23 824
前言： like使用方式一共三种格式 like ‘xxxx%’——这种能正常使用索引 like ‘%xxxx’——利用reverse函数索引优化 like’%xxxx%’——使用客制化函数instr和通用手段两种方法 一、构建实验条件 1.创建测试表（大概90M） create table test1 as select level id, dbms_random.string('X', 30) name, dbms_random.string('f', 30) address,
5种优化方法：MySQL的Like查询
田维常
10-22 866
好，我是田哥昨天，一个朋友在面试中被面试官问到了这么个问题：MySQL中like模糊查询如何做优化？这个问题说实话也算是烂大街了，在面试中被问到的概率还是蛮大的。你遇到过吗？你是否能回答上来呢？你知道几种呢？好了，我们进入正题。在MySQL中， 关于LIKE 模糊查询可能会导致性能问题，特别是当使用通配符%开头时，因为这通常会导致全表扫描。以下用5种方法可以帮助优化 LIKE 模糊查询。五种方法第...
mysql怎么优化like_MySQL的LIKE模糊查询优化
weixin_42119281的博客
01-18 3417
%xxx%这种方式对于数据量少的时候，我们倒可以随意用，但是数据量大的时候，我们就体验到了查询性能的问题，像老化的车子艰难趴着坡一样，并且这种方式并未使用到索引，而是全表扫描mysql高效模糊查询 代替like而对于xxx% 或者%xxx方式，explain一下可以发现查询使用到了索引，性能提升了不少，当然这种方式不适用与所有的查询场景。可以采取以下的函数进行查询。LOCATE('substr'...
eclipse的servlet默认不执行index_男朋友连模糊匹配like %%怎么优化都不知道
weixin_39947501的博客
11-28 357
三歪最近发现我一直在写MySQL的文章，然后就跟我说他有sql用到like的时候就没办法用到索引了，问我怎么办。我让他坐在我腿上，摸着他的手说道：傻瓜，这样这样，你看这不是好了？顺手刮了一下他的鼻子。三歪小脸一红，说：你真讨厌，然后娇羞的走了。玩笑归玩笑哈，其实在开发过程中，经常会碰到一些业务场景，需要以完全模糊匹配的方式查找数据，就会想到用 like %xxx% 或者 like %xx...
mysql优化之like和=性能详析
09-08
在数据库查询中，LIKE和=是两种常见的比较操作符，它们在功能上有着显著的区别，同时也涉及到性能优化的问题。LIKE主要用于模糊查询，允许在查询条件中使用通配符，而=则用于精确匹配。尽管两者在某些情况下可以互换...
MySQL Like模糊查询速度太慢如何解决
12-14
- `EXPLAIN`是MySQL提供的一种工具，用于分析SQL查询的执行计划。通过`EXPLAIN`关键字，可以查看查询是否使用了索引、是否进行全表扫描等信息，从而评估查询性能。 3. **Oracle LIKE优化**： - 在Oracle中，对于`...
MySQL模糊查询效率提升10倍！3种替代LIKE +%的革命性方法
java专栏
09-07 704
MySQL模糊查询性能优化指南 本文针对LIKE '%keyword%'查询性能低下的痛点，提出三大革命性解决方案：正则表达式REGEXP（提升400%）、全文索引FTS（提速10倍）和JSON虚拟列（支持实时处理）。通过电商平台实战案例，证实优化后响应时间从3秒降至0.3秒，用户转化率提升22%。文章还揭示了开发者常犯的5大错误，并分享索引优化、分页改进等实用技巧，最后展望AI驱动查询、向量搜索等前沿方向。适合中高级开发者突破数据库性能瓶颈。
php mysql like 实现多关键词搜索的方法
12-18
在PHP与MySQL数据库交互时，有时我们需要实现一种功能，即根据用户输入的多个关键词进行搜索。这通常涉及到SQL的LIKE语句，它允许我们在WHERE子句中使用通配符进行模糊匹配。本文将详细讲解如何使用PHP和MySQL的LIKE...
Mysql中like %xxx% 模糊查询该如何优化
weixin_40587144的博客
02-22 2457
Mysql中like %xxx% 模糊查询该如何优化
MySQL的模式匹配-like and regexp
weixin_30588729的博客
09-09 508
转载：http://hi.baidu.com/lq3158/blog/item/dc121d083cbaac36e92488a2.htmlMySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。 SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小...
mysql %%的业务优化_一种优化mysql select like %%的方法
weixin_28878185的博客
01-27 339
问题：mysql like %XXX%使得数据库引擎不能使用索引，而导致select查询缓慢。200M的表select like需要1s左右，这是实时性要求高的服务器承受不了的。解决方法：首先google一通发现，都在说like很难优化，一般都是采用开源插件分词+全文索引。然后再搜索，又搜索到了clucene，研究了下clucene，发现用它的人都是重新实现的分词模块，这样如果不实现中文分词模块，...
数据库优化之：like %xxx%该如何优化
1点25的博客
11-16 2733
再来简单分析一下索引条件下推的原理，在执行查询时先利用SQL中所提供的created_at条件在联合索引B+树中进行快速查找，匹配到所有符合created_at条件的B+树叶子节点后，再根据company_name条件进行过滤，然后再根据过滤之后的结果中的主键ID进行回表找到其他字段（回表），最终才返回结果，这样处理的好处是能够减少回表的次数，从而提高查询效率。就一定会导致索引失效，原理也可以配合其他字段一起来建联合索引，从而使用到索引条件下推机制。就会利用到索引条件下推机制，比如下图中Extra里的。
mysql like %%优化,由浅入深学优化之like‘%%’坑爹写法
weixin_31154627的博客
03-23 2737
某交易系统，监控告警有长SQL产生，脚本查询大于5分钟的长SQL。脚本内容如下：SQL Text : select from ( select tmp_page., rownum row_id from ( SELECTUUID,USR_ID,IN_MNO,MNO,xxxx,xxx,xxx,xxxxx,xxxxxFROM ABC.MEC_IFWHERE MNO LIKE :1order b...
MySQL 使用 like “%x“，索引一定会失效吗？
小林coding
01-25 8697
大家好，我是小林。 昨天发了一篇关于索引失效的文章：谁还没碰过索引失效呢 我在文末留了一个有点意思的思考题： 这个思考题其实是出自于，我之前这篇文章「一条 SQL 语句引发的思考」中留言区一位读者朋友出的问题。 很多读者都在留言区说了自己的想法，也有不少读者私聊我答案到底是什么？ 所以，我今晚就跟大家聊聊这个思考题。 题目一 题目一很简单，相信大家都能分析出答案，我昨天分享的索引失效文章里也提及过。 **「题目 1 」**的数据库表如下，id 是主键索引，name 是二级索引，其他字段都是非索引字段。
MySql检索数据select+where+like
weixin_43489515的博客
03-25 2572
一、介绍 1、本节讲解where的一些用法，包括where子句的常用操作符与逻辑操作符以及like模糊搜索 2、where常用操作符包括=、！=、<、>、<=、>=、between…and… 3、where逻辑操作符包括 and、or、in、not，可与常用操作符一起使用 4、like模糊搜索主要是通过通配符%去匹配 二、常用操作符使用 查询单个值（value为单一）...
MySql not in 优化
LoveEate的博客
08-14 425
表 A 表 B Select a.*From A a Where a.bid not in （Select b.id From B b） 优化之后 Select a.idFrom A a Left Join B b on b.id=a.bid where b.id is null
mysql 正反模糊查询_MySQL模糊查询语法_MySQL
weixin_39860583的博客
02-18 375
bitsCN.comMySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。为了找出...
mysql where like % 111 %;怎么优化查询
最新发布
10-17
<think>嗯，用户想优化 MySQL 中 where like '%111%' 这样的查询。这类查询确实很常见但性能往往不好，因为前导通配符会让索引失效。 用户引用了三条资料：第一条提到 OR 条件优化和索引失效问题，第二条解释 % 通配符的匹配规则，第三条提到用 union 优化多个 like 查询。这些引用很有价值，特别是 union 方案值得重点考虑。 用户可能正在处理模糊搜索功能，比如商品搜索或日志查询，数据量应该不小，不然不会关注性能优化。ta 的提问很精准，说明已经意识到前导通配符是痛点。 首先得明确核心瓶颈：B树索引无法匹配'%xxx'这种模式。不过还是有几种思路： 1. 如果允许的话，把前导通配符去掉变成'111%'，这样就能用上索引了。不过用户需求明确要求双百分号，这条路可能行不通。 2. 考虑全文索引方案，但需要表引擎是 InnoDB 且 MySQL 5.6+版本。这个方案最优雅但可能有版本限制。 3. 用 union 拆分查询，像引用[3]说的那样，不过要拆成'111%'和'%111'两部分。虽然后半段还是扫表，但前半段能用索引，整体性能提升明显。 4. 引入专用搜索引擎，不过对用户来说可能太重了。 另外注意到引用[1]提到 OR 条件优化，虽然不能直接解决 like 问题，但提醒了避免在 where 子句混用 or 和 like，这点要补充说明。 用户应该需要立即见效的方案，所以得优先推荐 union 技巧和全文索引。如果数据量特别大，再考虑额外提醒搜索引擎方案。优化器提示之类的进阶技巧可能用户暂时用不上，简单提一句即可。 最后要强调：所有优化都要配合 explain 验证，毕竟实际效果取决于数据分布。 </think>针对 `WHERE LIKE '%111%'` 这类**前导通配符导致索引失效**的查询优化，以下是系统性的解决方案： --- ### 一、核心优化方案 1. **强制使用全文索引（最优解）** ```sql -- 创建全文索引（需InnoDB/MyISAM表） ALTER TABLE 表名 ADD FULLTEXT INDEX idx_name (字段名); -- 使用MATCH AGAINST代替LIKE（注意保留原LIKE条件防精度丢失） SELECT * FROM 表名 WHERE MATCH(字段名) AGAINST('111' IN BOOLEAN MODE) AND 字段名 LIKE '%111%'; ``` *✅ 原理*：全文索引支持单词级检索，规避前导通配符限制[^1]。需注意： - MySQL 5.6+ 支持InnoDB全文索引 - 中文需配置ngram分词（`ngram_token_size=2`） 2. **反向索引+前缀查询（适合固定后缀）** ```sql -- 新增反转字段并建索引 ALTER TABLE 表名 ADD COLUMN reverse_name VARCHAR(255) AS (REVERSE(字段名)) STORED; CREATE INDEX idx_reverse ON 表名 (reverse_name); -- 查询转换（111结尾 → 111反转后开头） SELECT * FROM 表名 WHERE reverse_name LIKE REVERSE('111') + '%'; -- 实际传参：'111%'反转→'%111' ``` *✅ 适用场景*：已知后缀（如手机尾号、证件号末位） --- ### 二、辅助优化技巧 1. **拆分UNION查询（引用[3]方案增强）** ```sql (SELECT * FROM 表名 WHERE 字段名 LIKE '111%') -- 前缀命中索引 UNION ALL (SELECT * FROM 表名 WHERE 字段名 LIKE '%111' AND 字段名 NOT LIKE '111%') -- 避免重复 ``` *⚠️ 注意*：需确保前缀查询过滤掉大部分数据 2. **覆盖索引减少IO** ```sql CREATE INDEX idx_covering ON 表名 (字段名, id, 其他查询字段); ``` *✅ 效果*：二级索引树覆盖查询列，避免回表[^1] --- ### 三、架构级解决方案 | 方案 | 适用场景 | 优势 | |---------------------|-----------------------------------|----------------------| | Elasticsearch/Solr | 高频模糊检索（日志/商品搜索） | 毫秒级响应，分词优化 | | 生成列+表达式索引 | MySQL 8.0+ | 原生支持函数索引 | | 查询缓存 | 静态数据 | 避免重复计算 | --- ### 四、优化黄金原则 1. **严禁混合OR条件** ```sql -- 错误写法（引用[1]警示） SELECT * FROM users WHERE name LIKE '%abc%' OR email LIKE '%abc%'; -- 双字段索引失效 -- 正确改写 SELECT * FROM users WHERE name LIKE '%abc%' UNION ALL SELECT * FROM users WHERE email LIKE '%abc%' AND name NOT LIKE '%abc%'; ``` 2. **实时验证执行计划** ```sql EXPLAIN SELECT ... -- 检查type列避免"ALL"(全表扫描) ``` - 目标扫描类型：`index` > `range` > `ref` 3. **控制结果集大小** ```sql LIMIT 100 -- 强制终止大数据量扫描 ``` > **最终建议**： > 中小数据量 → 采用 **全文索引** + **覆盖索引** > 大数据量高并发 → **Elasticsearch** 专业搜索方案 --- ### 相关问题 1. 全文索引对中文分词的支持如何配置？ 2. 如何在MySQL 8.0中创建函数索引？ 3. Elasticsearch与MySQL数据实时同步有哪些方案？ 4. 联合索引中字段顺序对模糊查询有何影响？ [^1]: OR条件优化与索引失效警告 [^2]: 百分号通配符匹配规则 [^3]: UNION拆分查询策略
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值