推荐一种 MySQL like '%业余草'后缀索引高效搜索的用法！

你知道的越多，不知道的就越多，业余的像一棵小草！

你来，我们一起精进！你不来，我和你的竞争对手一起精进！

编辑：业余草

推荐：https://www.xttblog.com/?p=5336

经常阅读一些国外的技术文章，会让你大开眼界，比如我之前的文章《超赞，老外的一种避免递归查询所有子部门的树数据表设计与实现！》。一种新的设计，让我们更高效的实现子部门等功能的查询。

我最近两天还看到了一个倒序搜索设计，巧妙的利用空间换时间的思路实现了部分业务的查询效率。“让like后缀搜索走索引，让索引不失效”。

实现过程很简单，当然这样的业务场景可能也不多，以至于众多群友“不服”。

参考我的历史文章：从根上理解SQL的like查询%在前为什么不走索引?

我们知道 like 匹配列的前缀(比如like "a%")才可能利用到索引（如果有索引的话）。

like 操作符比较特殊，只有在匹配完整的字符串或者字符前缀时才产生合适的扫描区间（至于原因，我历史文章中有详细的原理讲解，本文我再简单阐述一下）。

比较字符串的大小其实就相当于依次比较每个字符的大小。字符串的比较过程如下：

先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。如果两个字符串的第一个字符相同，再比较第二个字符，第二个字符比较小那个字符串就比较小，以此类推。

如果这个列是索引列，那么字符串前缀相同的记录在单链表中肯定是相邻的。比如搜索条件为 key1 LIKE "a%"，对于非聚集索引来说，所有字符串前缀为a的记录肯定是相邻的。所以我们只要沿着单链表往后扫描即可，直到字符串前缀不为a为止。

所以，key1 LIKE "a%"的扫描区间相当于["a", "b")。

匹配列的中间字符或者后缀

匹配列的中间字符或者后缀(比如like "%a%"，like "%com")。

如果查询中间包含的某个字符串，比如：

SELECT*FROMdemo_infoWHEREnameLIKE"%a%";

MySQL 就无法快速定位记录位置了，因为字符串中间有a的字符串并没有排好序，所以只能全表扫描了。

如果查询后缀包含某个字符串，你会怎么做呢？

假设有个索引列 url，想查询以 com 为后缀的网址的话可以这样写查询条件，WHERE url LIKE "%com"，但是这样的话无法使用该 url 列的索引。

这里，我看到一个老外，新加了一个倒序 url 列。

这样可以把后缀查询改写成前缀查询，不过我们就得把表中的数据全部逆序存储一下，这样再查找以 com 为后缀的网址时搜索条件便可以这么写：WHERE url LIKE "moc%"，这样就可以用到索引了。这样即使是千万上亿级别的数据量，也可以快速查找而不是全表扫描。如果要查看正确的 url，只需要将此逆序一下就可以了。

本文讲的虽然不是一个重大算法实现，但是通过倒序 url 存储，巧妙的实现了 like 的后缀模糊搜索能力。一种思路的转变，带来更多可能性。

关键词： 你会怎么做 一种新的 竞争对手