Mysql模糊查询的其他优化方法测试

上文讲到了解决mysql 模糊查询的主要方法，还是使用全文索引，本文讲到其他相关的模糊插叙优化；同样进行耗时对比

结论：除了使用索引相关的方法，本文测试了网上其他的一些sql写法对模糊查询进行优化，其他的写法没有什么效果

无优化左前缀like

select * from yd_alarminfo_all_20220825 where alarmTitle like "网卡端口%"
--耗时：0.493

1
2
3

普通索引

-- 创建普通索引
ALTER table yd_alarminfo_all_20220825 add index idx_title(alarmTitle);
-- 查询耗时：0.003
1
2
3

效果：164倍效率提升

左前缀索引

左前缀索引就是在普通索引的基础上限定索引的长度，随着索引长度限制的越小，查询效率越差；左前缀索引的使用场景主要是：

1. 数据特征表现为数据左端就能够表现出较大的差异性，依据左侧的部分前缀，就能够有效进行数据过滤
2. 数据很长，为了节省索引的空间消耗

确定左前缀索引前缀长度的方法

1. 字段截取一定位数，计算于总数的去重后占比
2. 依据上一步结果制图，选择字段长度尽量少且索引选择性高的，性价比高的长度

select COUNT(DISTINCT LEFT(alarmTitle,3))/COUNT(*) as sel3,
	COUNT(DISTINCT LEFT(alarmTitle,5))/COUNT(*) as sel5,
	COUNT(DISTINCT LEFT(alarmTitle,7))/COUNT(*) as sel7,
	COUNT(DISTINCT LEFT(alarmTitle,9))/COUNT(*) as sel9,
	COUNT(DISTINCT LEFT(alarmTitle,11))/COUNT(*) as sel11,
	COUNT(DISTINCT LEFT(alarmTitle,13))/COUNT(*) as sel13,
	COUNT(DISTINCT LEFT(alarmTitle,15))/COUNT(*) as sel15,
	COUNT(DISTINCT LEFT(alarmTitle,17))/COUNT(*) as sel17,
	COUNT(DISTINCT LEFT(alarmTitle,19))/COUNT(*) as sel19,
	COUNT(DISTINCT LEFT(alarmTitle,21))/COUNT(*) as sel21
 from yd_alarminfo_all_20220825;
1
2
3
4
5
6
7
8
9
10
11

如图，选择度0.080之后，字段的增加，选择性增幅趋缓，选择0.080对应的长度

# 删除旧索引
DROP INDEX idx_title ON yd_alarminfo_all_20220825;
# 创建左前缀索引（索引创建语句只比普通索引多了‘(15)’）
ALTER table yd_alarminfo_all_20220825 add index idx_title(alarmTitle(15));
-- 查询耗时：0.01
1
2
3
4
5

效果：493倍效率提升

当查询结果的字符数小于左前缀长度时，左前缀可能甚者会有效率提升（理论上虽然索引选择性没有增加，但索引数少了）；字符数大于前缀长度时，因索引选择性降低的部分，性能有所降低；整体上是降低的

右后缀索引

通常我们的数据一般是左侧相似度高，右侧相似度低，但是右后缀索引可能并没有很高的适用性；右后缀索引的方法是：

1. 通过触发器或生成列语法对过滤字段反转另存
2. 对反转后的新字段创建左前缀索引

右后缀索引创建了新的反转字段，这是需要空间消耗的，而前缀索引是为了降低空间消耗的；那么右后缀索引和普通索引的性价比就不好说了

此处测试略过…

非索引优化

LOCATE

-- 删除旧索引
DROP INDEX idx_title ON yd_alarminfo_all_20220825;
-- LOCATE
SELECT * FROM yd_alarminfo_all_20220825 WHERE LOCATE('网卡端口', alarmTitle)>0
-- 查询耗时：0.499
1
2
3
4
5

效果：没太大效果

POSITION

position能够看作是locate的别名，功能跟locate同样

SELECT * FROM yd_alarminfo_all_20220825 WHERE POSITION('网卡端口' IN alarmTitle)
--查询耗时：0.544
1
2

效果：没太大效果

INSTR

SELECT * FROM yd_alarminfo_all_20220825 WHERE INSTR(alarmTitle, '网卡端口' )>0
--查询耗时：0.566
1
2

效果：没太大效果

先查询字段值，再范围查询

效果：没什么效果

结论：除了使用索引相关的方法，本文测试了网上其他的一些sql写法对模糊查询进行优化，其他的写法没有什么效果