怎样使MySQL模糊查询性能优化
2022/05/27 11:25:05
一、背景
我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索，LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
如果需要查找客户表中所有姓氏是“孙”的数据，可以使用下面的 SQL 语句：
SELECT * FROM Customer WHERE Name LIKE '孙%'
如果需要查找客户表中所有手机尾号是“1234”的数据，可以使用下面的 SQL 语句：
SELECT * FROM Customer WHERE Phone LIKE '%123456'
如果需要查找客户表中所有名字中包含“秀”的数据，可以使用下面的 SQL 语句：
SELECT * FROM Customer WHERE Name LIKE '%秀%'
以上三种分别对应了：左前缀匹配、右后缀匹配和模糊查询，并且对应了不同的查询优化方式。
左前缀匹配查询优化：索引就可以
我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。
简单来说，我们可以将字符串倒过来，让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例，将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时，去查找以“空悟孙”开头的数据即可。
具体做法是：在该表中增加“txt_back”列，将“txt”列的值倒置后，填入“txt_back”列中，最后为 “txt_back”列增加索引。
二、模糊查询优化
根据用户量和接口qps决定使用哪种优化方式，如果用户量级小，是面向B端的系统，可以采用mysql的feature进行查询优化，如果用户量级大，我们需要的就是一个全文检索引擎。
2.1 MySQL是如何优化模糊匹配like的SQL
2.1.1 全文索引
语法
alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);
MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。
从MySQL 5.7开始内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。
mysql 官方文档对match策略的解释：https://dev.mysql.com/doc/internals/en/full-text-search.html
2.1.2 生成列
MySQL 5.7开始支持生成列，生成列是由表达式的值计算而来，有两种模式：VIRTUAL和STORED，如果不指定默认是VIRTUAL，创建语法如下
col_name data_type [GENERATED ALWAYS] AS (expr) [**VIRTUAL** | **STORED**] [NOT NULL | NULL]
可以解决右后缀匹配的问题，但是不能解决模糊查询的问题。也是将字符串倒过来。
2.1.3 索引条件下推ICP
MySQL 5.6开始支持ICP（Index Condition Pushdown），不支持ICP之前，当进行索引查询时，首先根据索引来查找数据，然后再根据where条件来过滤，扫描了大量不必要的数据，增加了数据库IO操作。在支持ICP后，MySQL在取出索引数据的同时，判断是否可以进行where条件过滤，将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，减少了不必要数据被扫描带来的IO开销。在某些查询下，可以减少Server层对存储引擎层数据的读取，从而提供数据库的整体性能。
关闭ICP特性的SQL性能分析
开启ICP特性后，由于like条件可以通过索引筛选，存储引擎层通过索引与where条件的比较来去除不符合条件的记录，这个过程不需要读取记录，同时只返回给Server层筛选后的记录，减少不必要的IO开销。
2.2 快速入门的全文检索引擎
Sphinx
如果你和我一样，数据源存放在MySQL，可是使用：Sphinx ；
其实咱们KM早期就是使用Sphinx实现全文检索查询的，Sphinx可以非常容易的与SQL数据库和脚本语言集成。当前系统内置MySQL和PostgreSQL 数据库数据源的支持，也支持从标准输入读取特定格式 的XML数据。通过修改源代码，用户可以自行增加新的数据源。现在KM使用的是搜搜的底层实现搜索；
Elasticsearch
如果你是使用MongoDB，中文分词同样是个坑！业界通用方案是使用Elasticsearch 实现中文检索。 大致的路数是：使用 mongo-connector 将数据同步到Elasticsearch中；
RediSearch
最后还有一个好东西RediSearch是一款基于redis的搜索组件。基于redis，性能高效，.实时更新索引，支持Suggest前缀、拼音查找(AutoComplete 功能) ，支持单个或多个分词搜索 ，可根据字段进行结果排序。
我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索，LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
如果需要查找客户表中所有姓氏是“孙”的数据，可以使用下面的 SQL 语句：
SELECT * FROM Customer WHERE Name LIKE '孙%'
如果需要查找客户表中所有手机尾号是“1234”的数据，可以使用下面的 SQL 语句：
SELECT * FROM Customer WHERE Phone LIKE '%123456'
如果需要查找客户表中所有名字中包含“秀”的数据，可以使用下面的 SQL 语句：
SELECT * FROM Customer WHERE Name LIKE '%秀%'
以上三种分别对应了：左前缀匹配、右后缀匹配和模糊查询，并且对应了不同的查询优化方式。
左前缀匹配查询优化：索引就可以
我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。
简单来说，我们可以将字符串倒过来，让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例，将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时，去查找以“空悟孙”开头的数据即可。
具体做法是：在该表中增加“txt_back”列，将“txt”列的值倒置后，填入“txt_back”列中，最后为 “txt_back”列增加索引。
二、模糊查询优化
根据用户量和接口qps决定使用哪种优化方式，如果用户量级小，是面向B端的系统，可以采用mysql的feature进行查询优化，如果用户量级大，我们需要的就是一个全文检索引擎。
2.1 MySQL是如何优化模糊匹配like的SQL
2.1.1 全文索引
语法
alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);
MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。
从MySQL 5.7开始内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。
mysql 官方文档对match策略的解释：https://dev.mysql.com/doc/internals/en/full-text-search.html
2.1.2 生成列
MySQL 5.7开始支持生成列，生成列是由表达式的值计算而来，有两种模式：VIRTUAL和STORED，如果不指定默认是VIRTUAL，创建语法如下
col_name data_type [GENERATED ALWAYS] AS (expr) [**VIRTUAL** | **STORED**] [NOT NULL | NULL]
可以解决右后缀匹配的问题，但是不能解决模糊查询的问题。也是将字符串倒过来。
2.1.3 索引条件下推ICP
MySQL 5.6开始支持ICP（Index Condition Pushdown），不支持ICP之前，当进行索引查询时，首先根据索引来查找数据，然后再根据where条件来过滤，扫描了大量不必要的数据，增加了数据库IO操作。在支持ICP后，MySQL在取出索引数据的同时，判断是否可以进行where条件过滤，将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，减少了不必要数据被扫描带来的IO开销。在某些查询下，可以减少Server层对存储引擎层数据的读取，从而提供数据库的整体性能。
关闭ICP特性的SQL性能分析
开启ICP特性后，由于like条件可以通过索引筛选，存储引擎层通过索引与where条件的比较来去除不符合条件的记录，这个过程不需要读取记录，同时只返回给Server层筛选后的记录，减少不必要的IO开销。
2.2 快速入门的全文检索引擎
Sphinx
如果你和我一样，数据源存放在MySQL，可是使用：Sphinx ；
其实咱们KM早期就是使用Sphinx实现全文检索查询的，Sphinx可以非常容易的与SQL数据库和脚本语言集成。当前系统内置MySQL和PostgreSQL 数据库数据源的支持，也支持从标准输入读取特定格式 的XML数据。通过修改源代码，用户可以自行增加新的数据源。现在KM使用的是搜搜的底层实现搜索；
Elasticsearch
如果你是使用MongoDB，中文分词同样是个坑！业界通用方案是使用Elasticsearch 实现中文检索。 大致的路数是：使用 mongo-connector 将数据同步到Elasticsearch中；
RediSearch
最后还有一个好东西RediSearch是一款基于redis的搜索组件。基于redis，性能高效，.实时更新索引，支持Suggest前缀、拼音查找(AutoComplete 功能) ，支持单个或多个分词搜索 ，可根据字段进行结果排序。