Mysql模糊查询like效率,以及更高效的写法

本文介绍了在MySQL中如何优化模糊查询的效率,特别是针对大数据量的情况。通过比较不同LIKE语句的使用方式及其对索引的影响,同时提供了几种替代方案如LOCATE、POSITION和INSTR函数等来提高查询性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

原文来自:https://www.cnblogs.com/chaobest/p/6737901.html


在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很重要!

 

一般情况下like模糊查询的写法为(field已建立索引):

SELECT `column` FROM `table` WHERE `field` like '%keyword%';

上面的语句用explain解释来看,SQL语句并未用到索引,而且是全表搜索,如果在数据量超大的时候,可想而知最后的效率会是这样

对比下面的写法:

SELECT `column` FROM `table` WHERE `field` like 'keyword%';

这样的写法用explain解释看到,SQL语句使用了索引,搜索的效率大大的提高了!

 

但是有的时候,我们在做模糊查询的时候,并非要想查询的关键词都在开头,所以如果不是特别的要求,"keywork%"并不合适所有的模糊查询

 

这个时候,我们可以考虑用其他的方法

1.LOCATE('substr',str,pos)方法

复制代码
SELECT LOCATE('xbar',`foobar`); 
###返回0 

SELECT LOCATE('bar',`foobarbar`); 
###返回4

SELECT LOCATE('bar',`foobarbar`,5);
###返回7
复制代码

备注:返回 substr 在 str 中第一次出现的位置,如果 substr 在 str 中不存在,返回值为 0 。如果pos存在,返回 substr 在 str 第pos个位置后第一次出现的位置,如果 substr 在 str 中不存在,返回值为0。

SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0

备注:keyword是要搜索的内容,field为被匹配的字段,查询出所有存在keyword的数据

 

2.POSITION('substr' IN `field`)方法

position可以看做是locate的别名,功能跟locate一样

SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)

3.INSTR(`str`,'substr')方法

SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0 

 

除了上述的方法外,还有一个函数FIND_IN_SET

FIND_IN_SET(str1,str2):

返回str2中str1所在的位置索引,其中str2必须以","分割开。

SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);
MySQL 中使用 `LIKE` 进行模糊查询时,百分号 `%` 用于匹配任意数量(包括零个)的字符。它通常用于字符串模式匹配,例如查找包含特定子字符串的数据。 ### 百分号 `%` 的基本用法 - `%` 匹配任意长度的任意字符(包括空字符)。 - `_`(下划线)匹配单个字符。 #### 示例: 1. 查询所有以 "iPhone" 开头的商品名称: ```sql SELECT goods_name FROM goods WHERE goods_name LIKE 'iPhone%'; ``` 2. 查询所有以 "iPod" 结尾的商品名称: ```sql SELECT goods_name FROM goods WHERE goods_name LIKE '%iPod'; ``` 3. 查询所有包含 "iPhone" 和 "iPod" 的商品名称: ```sql SELECT goods_name FROM goods WHERE goods_name LIKE '%iPhone%iPod%'; ``` ### 多个百分号的影响 虽然多个连续的 `%` 在语义上与单个 `%` 相同,但它们并不会显著影响查询性能。根据实际测试结果,无论使用一个 `%` 还是多个 `%`,查询时间几乎相同 [^2]。因此,可以按照可读性和代码风格选择写法。 ### 前置百分号对性能的影响 如果 `LIKE` 表达式以 `%` 开头(例如 `'%iPhone'`),MySQL 将无法使用索引进行高效查询。这种情况下,数据库必须执行全表扫描来匹配数据,从而可能导致性能下降 [^3]。建议尽量避免前置 `%`,除非确实需要进行左模糊或全模糊匹配。 ### 查询优化建议 1. **避免前置 `%`**:如果可能,尽量避免使用 `LIKE '%value'`,因为这会禁用索引,导致性能下降。 2. **拆分复杂条件**:对于多个关键词的匹配,可以将复杂的 `LIKE` 条件拆分为多个独立的 `LIKE` 子句,这样有助于提高查询可读性和潜在的优化机会 [^2]。 ```sql SELECT goods_name FROM goods WHERE goods_name LIKE '%iPhone%' AND goods_name LIKE '%iPod%'; ``` 3. **使用全文搜索**:如果频繁进行复杂的模糊查询,可以考虑使用 MySQL 的全文搜索功能(`FULLTEXT` 索引),它可以提供高效的文本检索能力。 ### 总结 `LIKE` 语句中的 `%` 是强大的模式匹配工具,但在使用时需要注意其对性能的影响。尽管多个 `%` 不会影响执行速度,但前置 `%` 可能会导致全表扫描,降低查询效率。根据实际需求选择合适的写法,并在必要时考虑使用全文索引优化模糊查询 [^2]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值