上文講到了解決mysql 模糊查詢的主要方法，還是使用全文索引，本文講到其他相關(guān)的模糊插敘優(yōu)化；同樣進(jìn)行耗時(shí)對(duì)比

結(jié)論：除了使用索引相關(guān)的方法，本文測(cè)試了網(wǎng)上其他的一些sql寫法對(duì)模糊查詢進(jìn)行優(yōu)化，其他的寫法沒有什么效果

無(wú)優(yōu)化左前綴like

select * from yd_alarminfo_all_20220825 where alarmTitle like "網(wǎng)卡端口%"
--耗時(shí)：0.493

普通索引

-- 創(chuàng)建普通索引
ALTER table yd_alarminfo_all_20220825 add index idx_title(alarmTitle);
-- 查詢耗時(shí)：0.003

效果：164倍效率提升

左前綴索引

左前綴索引就是在普通索引的基礎(chǔ)上限定索引的長(zhǎng)度，隨著索引長(zhǎng)度限制的越小，查詢效率越差；左前綴索引的使用場(chǎng)景主要是：

• 數(shù)據(jù)特征表現(xiàn)為數(shù)據(jù)左端就能夠表現(xiàn)出較大的差異性，依據(jù)左側(cè)的部分前綴，就能夠有效進(jìn)行數(shù)據(jù)過濾
• 數(shù)據(jù)很長(zhǎng)，為了節(jié)省索引的空間消耗

確定左前綴索引前綴長(zhǎng)度的方法

• 字段截取一定位數(shù)，計(jì)算于總數(shù)的去重后占比
• 依據(jù)上一步結(jié)果制圖，選擇字段長(zhǎng)度盡量少且索引選擇性高的，性價(jià)比高的長(zhǎng)度

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;

如圖，選擇度0.080之后，字段的增加，選擇性增幅趨緩，選擇0.080對(duì)應(yīng)的長(zhǎng)度

# 刪除舊索引
DROP INDEX idx_title ON yd_alarminfo_all_20220825;
# 創(chuàng)建左前綴索引（索引創(chuàng)建語(yǔ)句只比普通索引多了‘(15)'）
ALTER table yd_alarminfo_all_20220825 add index idx_title(alarmTitle(15));
-- 查詢耗時(shí)：0.01

select * from yd_alarminfo_all_20220825 where alarmTitle like "網(wǎng)卡端口%'

效果：493倍效率提升

當(dāng)查詢結(jié)果的字符數(shù)小于左前綴長(zhǎng)度時(shí)，左前綴可能甚者會(huì)有效率提升（理論上雖然索引選擇性沒有增加，但索引數(shù)少了）；字符數(shù)大于前綴長(zhǎng)度時(shí)，因索引選擇性降低的部分，性能有所降低；整體上是降低的

右后綴索引

通常我們的數(shù)據(jù)一般是左側(cè)相似度高，右側(cè)相似度低，但是右后綴索引可能并沒有很高的適用性；右后綴索引的方法是：

• 通過觸發(fā)器或生成列語(yǔ)法對(duì)過濾字段反轉(zhuǎn)另存
• 對(duì)反轉(zhuǎn)后的新字段創(chuàng)建左前綴索引

右后綴索引創(chuàng)建了新的反轉(zhuǎn)字段，這是需要空間消耗的，而前綴索引是為了降低空間消耗的；那么右后綴索引和普通索引的性價(jià)比就不好說了

此處測(cè)試略過…

非索引優(yōu)化

LOCATE

-- 刪除舊索引
DROP INDEX idx_title ON yd_alarminfo_all_20220825;
-- LOCATE
SELECT * FROM yd_alarminfo_all_20220825 WHERE LOCATE('網(wǎng)卡端口', alarmTitle)>0
-- 查詢耗時(shí)：0.499

效果：沒太大效果

POSITION

position能夠看作是locate的別名，功能跟locate同樣

SELECT * FROM yd_alarminfo_all_20220825 WHERE POSITION('網(wǎng)卡端口' IN alarmTitle)
--查詢耗時(shí)：0.544

效果：沒太大效果

INSTR

SELECT * FROM yd_alarminfo_all_20220825 WHERE INSTR(alarmTitle, '網(wǎng)卡端口' )>0
--查詢耗時(shí)：0.566

效果：沒太大效果

先查詢字段值，再范圍查詢

效果：沒什么效果

結(jié)論：除了使用索引相關(guān)的方法，本文測(cè)試了網(wǎng)上其他的一些sql寫法對(duì)模糊查詢進(jìn)行優(yōu)化，其他的寫法沒有什么效果

到此這篇關(guān)于Mysql模糊查詢優(yōu)化方法及測(cè)試詳細(xì)講解的文章就介紹到這了,更多相關(guān)Mysql模糊查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論