mysql like %keyword%不走索引替代方法

柔光的暖阳◎ 2022-02-25 07:14 810阅读 1赞

mysql 在使用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

4、除了上述的方法外，还有一个函数FIND\_IN\_SET

FIND\_IN\_SET(str1,str2):

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

SELECT \* FROM \`person\` WHERE FIND\_IN\_SET('apply',\`name\`);

以上方法在使用 \* 时不走索引, 使用多个列时走所以.

mysql5.6中已经解决了 like %keyword% 第一个%分好的问题.但注意EXPLAIN SELECT city\_id ,city FROM city WHERE city LIKE '%xin%';

select 的列不能时\* 否则不走索引.

发表评论取消回复

表情：

评论列表（有 0 条评论，810人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL中使用IN（）查询到底走不走索引？

MySQL中使用IN（）查询到底走不走索引？看数据量 EXPLAIN SELECT from users WHERE is_doc

女爷i/ 2024年03月23日 10:44/ 0 赞/ 88 阅读

相关 MySQL中，如何让 like %abc 走索引查询？

> 我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，可以使用 `REVERSE()` 函数来创建一个函数索引，

╰半夏微凉°/ 2024年02月17日 10:28/ 0 赞/ 57 阅读

相关【MySQL】MySQL不走索引的情况分析

未建立索引当数据表没有设计相关索引时，查询会扫描全表。 create table test_temp ( test_id in

水深无声/ 2023年10月14日 19:16/ 0 赞/ 70 阅读

相关 Mysql什么情况下不走索引

背景分析今天领导在查询报表时，发现特别慢，于是引发一系列关于sql优化的工作，最终发现是分析同学在进行多表关联时进行不等值关联造成全表扫描，且使用字段无索引造成1W条数

谁践踏了优雅/ 2023年09月30日 20:01/ 0 赞/ 31 阅读

相关 MySql Delete不走索引问题

如果delete语句带有查询，写法不对会导致不走索引。简单粗暴的办法：拆两条sql，一条查询，一条delete ======================= 【不走索

我不是女神ヾ/ 2023年06月02日 05:30/ 0 赞/ 35 阅读

相关 oracle索引列like走索引吗,sql – Oracle查询在索引号列上使用“like”,性能不佳

在查询1上,正在执行全表扫描,即使id是索引列.查询2实现了相同的结果,但更快.如果查询1运行返回索引列,那么它将快速返回,但如果返回非索引列,或者整个行是查询需要更长时间.

旧城等待，/ 2023年01月19日 04:51/ 0 赞/ 27 阅读

相关以通配符（%）开始的like字符串，走索引

了解SQL的同学，都知道，like关键字可以走索引，只要字符串不是以通配符（%）开始。如果类似 like "%xxx" 的sql，如何走索引呢？基于REVERSE()函数来

灰太狼/ 2022年09月25日 03:21/ 0 赞/ 133 阅读

相关不走索引原因

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条S

清疚/ 2022年09月18日 00:59/ 0 赞/ 224 阅读

相关 mysql like %keyword%不走索引替代方法

mysql 在使用msyql进行模糊查询的时候，很自然的会用到like语句，通常情况下，在数据量小的时候，不容易看出查询的效率，但在数据量达到百万级，千万级的时候，查询的效率就

柔光的暖阳◎/ 2022年02月25日 07:14/ 1 赞/ 811 阅读

相关 oracle细节之like模糊查询不能走索引？

这里要纠正一个网上很多教程说的模糊匹配不能走索引的说法，因为在看《收获，不止SQL优化》一书，里面举例说到了，并且自己也跟着例子实践了一下，确实like一些特殊情况也是可以走索

本是古典何须时尚/ 2021年09月19日 22:40/ 0 赞/ 406 阅读