MySQL Like查询优化大全

最新推荐文章于 2024-06-28 09:51:17 发布

-江沐风-

最新推荐文章于 2024-06-28 09:51:17 发布

阅读量4.9k

点赞数 2

分类专栏： Java学习文章标签： mysql 数据库 sql

原文链接：https://wenku.baidu.com/view/d17a4e2a1db91a37f111f18583d049649b660e2f.html

版权

Java学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

2.1 Generated Column虚拟列优化（针对`%name`场景）

针对%name无法走索引的情况，可以通过以下两种方式来优化。

（1）第一种方式：

新增一列字段，用来存储原先查询字段的倒序，也就是原来存储的是ABC，新增字段存储为CBA；

大概实现如下：

UPDATE test SET name_reverse = REVERSE(name);

-- 如果不需要完全存储，只需要存储原数据的后几位，可以：
UPDATE test SET name_reverse = REVERSE(SUBSTRING(name, -6));

-- 然后给该字段添加索引：
ALTER TABLE test ADD INDEX idx_name_desc(name_reverse);

那么最后的查询条件如下：

WHERE name LIKE REVERSE('%公共场合');

（2）第二种方式：

新建一个虚拟列（Generated Column），不过需要MySql5.7以上支持；不过原理和上述方式一致；通过使用虚拟列的方式，可以不需要改动Java代码；

备注：

MySQL 5.7之后增加了对Generated Column的支持，能够在此列中指定一些预先定义的表达式，或者是结合其他列使用一些函数计算出相应的结果做给该列的值；
Generated Column支持两种类型，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示；
后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

使用方式：

`agency_reverse_virtual` varchar(200) GENERATED ALWAYS AS (reverse(`agency`)) VIRTUAL;

-- 然后给该字段添加索引即可；
ALTER TABLE test ADD INDEX idx_name_desc(agency_reverse_virtual);

2.2 分词 + 倒排索引

我们可以考虑手动分词+倒排索引的方式来实现：

1）表user_info核心字段 : id,name（name包含索引）；

2）分词name_participles表：id, user_id, name_participles（联合索引：(name_participles,user_id)）

数据存储的话，在分词表name_participles 针对name字段进行两两分词。

比如，name为：“中国商标专利事务”，分词划分：中国,国商,商标,标专,专利,利事,事务，最终存储时共7条记录；

如果要查询字符串：“事务专利”，sql如下：

SELECT u.* FROM user_info u JOIN  (
	   SELECT user_id FROM name_participles WHERE name_participles IN ('事务','务专','专利')
	   GROUP BY user_id HAVING count(DISTINCT(name_participles) ) = 3
) n ON n.user_id = u.id

不过针对包括类似回文的字符串，还有点问题。比如针对字符串进行分词：“中国商标专利事务所有限公司务专”，然后进行搜索:

搜：“专利事务”，查询过滤为：IN (‘专利’,‘利事’,‘事务’)，能正常搜出来该条记录；
搜：“事务专利”，查询过滤为：IN (‘事务’,‘务专’,‘专利’)，也能搜出来该条记录，但正常是不应该搜到这条数据的;

针对这种情况，需要进行二次筛选，这时候我们再根据name进行筛选，由于子查询出来的数据量不多，并且可以走主键索引所以再次进行Like也还好：

SELECT u.* FROM user_info u JOIN  (
	   SELECT user_id FROM name_participles WHERE name_participles IN ('事务','务专','专利')
	   GROUP BY user_id HAVING count(DISTINCT(name_participles) ) = 3
) n ON n.user_id = u.id WHERE u.`name` LIKE '%事务专利%'

备注：

针对要分词的字符串name，长度不能太长，要不然分词表的数据会很大；
如果要搜索单个字符串，这时候会无法进行搜索，只能通过like进行模糊搜索，针对这种情况，我们可以考虑在产品端进行限制，不支持单个字符串查询；

2.3 MySQL全文索引

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文分词。

缺点：

查询速度不稳定，有时候查询比较快，有时候查询还比不上like全匹配模糊查询，当查询条件比较长时，问题更明显；
当查询条件比较长时，可能会触发全文检索的缓存限制：innodb_ft_result_cache_limit；
全文索引会对查询中间产生的数据和查询的结果在内存中进行缓存，如果达到结果缓存大小限制，则返回错误，指示查询超出了最大允许的内存：FTS query exceeds result cache limit

2.4 ICP（索引条件下推优化）

ICP（Index Condition Pushdown）是MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低存储引擎访问表的次数和MySQL服务器访问存储引擎的次数，提高查询效率。

降低存储引擎访问表的次数；
MySQL服务器访问存储引擎的次数；

条件：

只能用于range、 ref、 eq_ref、ref_or_null访问方法；

ICP可用于InnoDB和MyISAM表，对于InnoDB表，ICP 仅用于二级索引（辅助索引），MySQL 5.6 中的分区表不支持 ICP；5.7版本后可以用于分区表查询；

ICP 的目标是减少访问表的次数，从而减少 I/O 操作。对于 InnoDB聚集索引，完整的记录已经读入InnoDB 缓冲区，在这种情况下使用 ICP 不会减少 I/O次数；

如果查询走到了ICP，那么查询计划中 Extra列会输出：Using index condition；
默认情况下ICP是启用的，可以使用系统参数optimizer_switch来控制器是否开启；
select @@optimizer_switch;
引用了子查询的条件不能下推；

引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数；

触发条件也不能下推；

原理：

为了理解ICP是如何工作的，我们先了解下没有使用ICP的情况下，MySQL是如何查询的：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程如下：

读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

例子：创建联合索引INDEX (zipcode, lastname, firstname)，然后执行case：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

（1）不使用ICP的情况：

在不使用索引下推的情况下，根据联合索引“最左匹配”原则，只有zipcode列能用到索引，lastname列由于是模糊匹配，是不能用到索引的，此时的执行过程是这样的：

存储引擎根据联合索引，找到zipcode值为95054的记录；
然后根据这几条记录中的id值，逐一进行回表扫描，去主键索引中取出完整的行记录，并把这些记录返回给Server层；
Server层接收到这些记录，并按条件lastname LIKE '%etrunia%' AND address LIKE '%Main Street%'进行过滤，最终留下满足条件的记录；

（2）使用ICP的情况：

使用索引下推的情况下，执行过程是这样的：

存储引擎根据联合索引，找到zipcode='95054'的记录，共4条；
由于联合索引中包含lastname列，存储引擎直接在联合索引中按lastname LIKE '%etrunia%'进行过滤，过滤后剩下2条记录；
根据过滤后的记录的id值，逐一进行回表扫描，去聚簇索引中取出完整的行记录，并把这些记录返回给Server层；
Server层根据WHERE语句的其它条件address LIKE '%Main Street%'，再次对行记录进行筛选，最终只留下最终留下满足条件的记录；

备注：