LIKE比较查询优化

在执行检索时，有两种方式可以使用到索引：
1.单值查询，比如where id=2
2.范围查询，比如where id between 2 and 20、where id<20

当使用like来检索文本的开始位置，实际上是一种范围查找。事实上，下面的两个查询是等价的：

SELECT ... FROM ... WHERE name LIKE 'B%';
SELECT ... FROM ... WHERE name >= 'B' AND name < 'C';

使用like匹配文本开始位置通常是可以使用到索引的。

有时候，like处理匹配文本的开始，还包含其它匹配，比如：name like 'A%B%C'，也会使用到索引。

在文本的中间使用like检索，是无法用到索引的。但我们可以继续想想是否做点什么从而优化查询。

 

1.是否可以从文本的开始进行匹配
在真是世界中，有很多这样的查询：like '%middle of text%'。而实际上，他们是可以改成like 'beginning%of%text%'的。
也许是开发人员没有深入的分析，也是他们压根就是错的。无论是哪种原因，都应该仔细考虑一下，是否做这种转换。

 

2.借助神奇的单字符_和%
_表示任意一个字符
%表示0个或任意多个字符
如果可能，使用一个_或任意个_来代替%，这会减少检索的字符的数量。比如：

SELECT ... FROM ... WHERE code LIKE '%x%';
SELECT ... FROM ... WHERE code LIKE '__x%';

假设code有20个字符。第一个SQL会检索所有的字符；第二个SQL只会检索三个字符。即使这两个SQL都不能使用上索引，第二个也会快一点，因为做的工作少。

 

3.合并检索
有时候，你需要在文本的中间做多种字符串检索，但是你是知道他们的出现顺序的。可以考虑看看是否可以合并成一个检索，加快查询：
来看下面的例子：

SELECT ... FROM ... WHERE code LIKE '%XX%' AND code LIKE '%YY%';

如果你知道，在文本种"XX"是不可能出现在"YY"后面的，你可以用这种方式优化查询：

SELECT ... FROM ... WHERE code LIKE '%XX%YY%';

这样，只有在XX出现后，才会匹配YY。

如果你知道在两个字符串之间有多少个字符，还可以更好的优化:

-- XX和YY之间只有3个字符:
SELECT ... FROM ... WHERE code LIKE '%XX__YY%';
-- XX和YY之间至少有3个字符:
SELECT ... FROM ... WHERE code LIKE '%XX__%YY%';

 

4.similar to语法
有些数据库，比如postgresql，支持similar to语法。比like更灵活，但没有regexp灵活。
有人可能会争论说similar to没用，但是我们会在这里使用它。当然你喜欢用regexp，也没人阻止你。
similar to有时候可用于结合两个like条件。看下面的例子：

SELECT ... FROM ... WHERE code LIKE '%XX%' OR code LIKE '%YY%';
SELECT ... FROM ... WHERE code LIKE '%(XX|YY)%';

 

5.标志位
有时我们使用LIKE来搜索包含某个标志的文本(可能是代码)。在这种情况下，我们通常需要查找少量可能的标志。这与基于用户输入任何内容的搜索非常不同。

如果重要标志的数量很少(为了给你一个数字，我说3个)，你可以考虑创建布尔列，如果标志存在，则为TRUE，否则为FALSE。你可以使用生成列(generated columns)来确保布尔值的正确性:

ALTER TABLE table_name
    ADD COLUMN is_tkk BOOLEAN
    AS (product_code LIKE '%tkk%')
    STORED;

这个生成列的值在插入或更新的时候会被计算出来。

 

6.使用gin索引

CREATE INDEX idx_gin_code ON code USING gin (code);

 

7.使用逆序

从文本的尾部匹配
从文本的尾部匹配无法使用索引。但是有一种替换方式。
将文中的内容和like匹配的关键字都变得逆序。就变成了从文本头部匹配了。

SELECT ... FROM ... WHERE code LIKE '%abc';
-- will become:
SELECT ... FROM ... WHERE code_rev LIKE REVERSE('%abc');

可以在应用中将列的内容逆序，也可以借助生成列(generated column)

ALTER TABLE table_name
    ADD COLUMN code_rev VARCHAR(200)
        AS (REVERSE(code))
        STORED,
    ADD INDEX idx_code_rev (code_rev);

如果code列的值很长，我们需要考虑是否需要将所有值都逆序。也可以截取一部分，比如 REVERSE(LEFT(code, 10))