Mysql模糊查詢優(yōu)化方法及測(cè)試詳細(xì)講解
上文講到了解決mysql 模糊查詢的主要方法，還是使用全文索引，本文講到其他相關(guān)的模糊插敘優(yōu)化；同樣進(jìn)行耗時(shí)對(duì)比
結(jié)論：除了使用索引相關(guān)的方法，本文測(cè)試了網(wǎng)上其他的一些sql寫法對(duì)模糊查詢進(jìn)行優(yōu)化，其他的寫法沒有什么效果
無優(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ǔ)上限定索引的長度，隨著索引長度限制的越小，查詢效率越差；左前綴索引的使用場(chǎng)景主要是：
- 數(shù)據(jù)特征表現(xiàn)為數(shù)據(jù)左端就能夠表現(xiàn)出較大的差異性，依據(jù)左側(cè)的部分前綴，就能夠有效進(jìn)行數(shù)據(jù)過濾
- 數(shù)據(jù)很長，為了節(jié)省索引的空間消耗
確定左前綴索引前綴長度的方法
- 字段截取一定位數(shù)，計(jì)算于總數(shù)的去重后占比
- 依據(jù)上一步結(jié)果制圖，選擇字段長度盡量少且索引選擇性高的，性價(jià)比高的長度
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)的長度
# 刪除舊索引 DROP INDEX idx_title ON yd_alarminfo_all_20220825; # 創(chuàng)建左前綴索引（索引創(chuàng)建語句只比普通索引多了‘(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ù)小于左前綴長度時(shí)，左前綴可能甚者會(huì)有效率提升（理論上雖然索引選擇性沒有增加，但索引數(shù)少了）；字符數(shù)大于前綴長度時(shí)，因索引選擇性降低的部分，性能有所降低；整體上是降低的
右后綴索引
通常我們的數(shù)據(jù)一般是左側(cè)相似度高，右側(cè)相似度低，但是右后綴索引可能并沒有很高的適用性；右后綴索引的方法是：
- 通過觸發(fā)器或生成列語法對(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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
文章講述了對(duì) MySQL 存儲(chǔ)過程未執(zhí)行的問題排查過程,先后排查了數(shù)據(jù)庫版本升級(jí)、用戶權(quán)限不足,最終發(fā)現(xiàn)是存儲(chǔ)過程創(chuàng)建時(shí)的 SQL 模式導(dǎo)致,即使之后去掉該模式,存儲(chǔ)過程內(nèi)部仍沿用舊模式,重新創(chuàng)建存儲(chǔ)過程后問題解決,需要的朋友可以參考下2025-02-02
淺談為什么Mysql數(shù)據(jù)庫盡量避免NULL
MySQL內(nèi)連接和外連接及七種SQL?JOINS的實(shí)現(xiàn)
MySql獲取當(dāng)前時(shí)間并轉(zhuǎn)換成字符串的實(shí)現(xiàn)
MySQL8.0設(shè)置遠(yuǎn)程訪問權(quán)限的方法