MySQL中like关键字与索引的使用

严格来说，like '%abc'本身是不能使用索引的，但是一条SQL语句有没有使用索引，要看这条SQL语句整体写的是什么，like不是疫情期间的新冠病毒，沾上一点儿就会把索引隔离开。只要你明白自己在干什么，完全可以使用like。

like的简单说明

like这个查询条件其实用代码很好说明。比如一个查询是select id, name from user，我们把查询的返回的结果放在一个数组里，like 的作用就相当于做了下面的事情：

ret = []
for user in user_list:
    if user['name'].find(sub_str) != -1:
        ret.append(user)

like的作用就相当于对前面语句的查询结果遍历了一遍，对结果中的每一项都做了子串匹配。而like 本身没有引入任何索引的机制，就是一个纯粹的字串匹配，一般来说索引（哈希索引）只能使用在严格匹配的场景上。而上面的例子，在数据库实际的处理中，就相当于把这个遍历的过程放到了数据之内而已。

这个时候，我们比较容易就可以想到，如果使用了like，那么查询的效率的主要取决于user_list的大小，也就是在SQL中like 之外的部分结果集合的大小。所以根据这个，我们可以看一下哪些情况可以使用like。

对like的合理使用

首先，如果表的规模很小，比如一共100来个用户，查个名字什么的like随便用。

第二是，配合其他被索引覆盖的查询条件一起用。这个逻辑也特别的简单，既然直接使用like工作量太大，那么先用别的索引把范围缩小，再用like从已经缩小的中间结果里面筛选出最后的结果就可以了。如何缩小范围就和具体的数据有关了，比如可以先用用户的最后登录时间先筛选出最近登陆过的用户，这个登录时间是可以做一个B+树索引的，每次只在这个范围里做like，需要遍历的空间就小多了。当

然这个方法需要通过额外的信息来压缩使用like遍历的范围，如果从需求的层面无法得到这个额外的信息就没办法这么干了。这时我们我们可以开始考虑使用MySQL的全文索引。

全文索引

MySQL全文索引是在比较大规模的数据集合上解决like查询条件性能的最适合的方法。

MySQL建立全文索引的方式是对字段的内容进行分词，然后对每一个词都进行索引，直接的效果就是分词的结果列表中的每一个词都可以被检索到。所以全文索引的效果非常接近于通过like进行查询的效果。使用全文索引的方法很简单，首先对查询的字段建立全文索引

CREATE FULLTEXT INDEX idx_name ON user(name);

然后通过match against来进行查询

SELECT id, name FROM user WHERE MATCH(name) AGAINST('Json');

不过对于中文来说，这还是不够的，因为MySQL默认的分词机制是不支持中文的。这时就可以粗暴的使用2-gram的分词方式，也就是把“这是一句话”分成`"这是"，"是一"，"一句"，"句话" 几个词的分词方式。这么做的话，需要首先把修改MySQL的一个设置，把ngram的token的长度为2

SET GLOBAL ngram_token_size = 2;

然后需要在创建全文索引时指定parser为ngram

CREATE FULLTEXT INDEX idx_name ON user(name) WITH PARSER ngram;

这样就可以开心的用中文的全文索引来查询了。不过因为对查询也会进行分词的原因，所以通过全文索引得到的结果数量会远远比通过like查询得到的结果多。比如查询“我爱我家”，那么name字段中包含了我爱、爱我、我家的内容都会被返回回来，这是如果想要更为精准的返回匹配“我爱我家”的结果，我们就可以继续使用like了：

SELECT id, name FROM user WHERE MATCH(name) AGAINST('我爱我家') and name like '%我爱我家%';

不要担心，因为这里查询条件中，还有使用全文索引的查询条件来确保索引的使用，而like则只是在使用索引得到的结果中再筛选一次。 既可以得到和like几乎相同的查询效果，又通过索引来提高了查询效率。

总结

使用like进行查询的逻辑其实非常符合我们的直接的认知。就像在bash中使用grep来筛选命令行的结果和like其实是同样的逻辑。只是如果数据库中的数据很多时，我们需要通过其他的方法来缩减直接应用like的范围而已。