mysql like 优化
假设给表中字段A 建了索引
在做 like 查询的时候 有如下四种情况:
1. SELECT * FROM TABLE LIKE 'XXX'
走索引
2. SELECT * FROM TABLE LIKE 'XXX%'
走索引
3. SELECT * FROM TABLE LIKE '%XXX'
全表扫描,不走索引
4. SELECT * FROM TABLE LIKE '%XXX%'
全表扫描,不走索引
sql 1 2 都会走索引,速度很快。 3和4 因为 ‘%’ 在前面,不符合前缀规则,所以,只能全表扫描了。
本文主要针对sql 3 的优化.
SELECT * FROM TABLE LIKE ‘%XXX’
查询 以 xxx 结尾的数据。这个的优化,我们可以通过反向思考来解决,既 我可不可以针对这列在建一个 反过来的索引。
ver 8.0+
create index reverse_name on table_name (name) reverse;
others ver:
alter table table_name add reverse_name varchar(10) as (reverse(name));
create index on reverse_name;
老版本要先创建一个叫 reverse_name 的字段,能后给这个字段创建索引。要繁琐写些。
sql 4 优化:
这个还在搜集当中,目前还没有发现比较好的方案
参考:
https://www.v2ex.com/t/133996
https://stackoverflow.com/questions/51987771/mysql-reverse-index-and-query
其他:
网上有说用 LOCATE、FIND_IN_SET、INSTR、POSITION 函数替代like 进行优化,但是我测试了几次,觉得效果都不太理想,和lik的速度相差不大。所有这个就不在本文的讨论范围。具体的可以看 https://www.cnblogs.com/tommy-huang/p/4483583.html