Mysql模糊查詢優(yōu)化方法及測試詳細講解
上文講到了解決mysql 模糊查詢的主要方法，還是使用全文索引，本文講到其他相關(guān)的模糊插敘優(yōu)化；同樣進行耗時對比
結(jié)論：除了使用索引相關(guān)的方法，本文測試了網(wǎng)上其他的一些sql寫法對模糊查詢進行優(yōu)化，其他的寫法沒有什么效果
無優(yōu)化左前綴like
select * from yd_alarminfo_all_20220825 where alarmTitle like "網(wǎng)卡端口%" --耗時：0.493
普通索引
-- 創(chuàng)建普通索引 ALTER table yd_alarminfo_all_20220825 add index idx_title(alarmTitle); -- 查詢耗時：0.003
效果：164倍效率提升
左前綴索引
左前綴索引就是在普通索引的基礎(chǔ)上限定索引的長度，隨著索引長度限制的越小，查詢效率越差；左前綴索引的使用場景主要是：
- 數(shù)據(jù)特征表現(xiàn)為數(shù)據(jù)左端就能夠表現(xiàn)出較大的差異性，依據(jù)左側(cè)的部分前綴，就能夠有效進行數(shù)據(jù)過濾
- 數(shù)據(jù)很長，為了節(jié)省索引的空間消耗
確定左前綴索引前綴長度的方法
- 字段截取一定位數(shù)，計算于總數(shù)的去重后占比
- 依據(jù)上一步結(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對應的長度
# 刪除舊索引 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)); -- 查詢耗時：0.01
select * from yd_alarminfo_all_20220825 where alarmTitle like "網(wǎng)卡端口%'
效果：493倍效率提升
當查詢結(jié)果的字符數(shù)小于左前綴長度時，左前綴可能甚者會有效率提升（理論上雖然索引選擇性沒有增加，但索引數(shù)少了）；字符數(shù)大于前綴長度時，因索引選擇性降低的部分，性能有所降低；整體上是降低的
右后綴索引
通常我們的數(shù)據(jù)一般是左側(cè)相似度高，右側(cè)相似度低，但是右后綴索引可能并沒有很高的適用性；右后綴索引的方法是：
- 通過觸發(fā)器或生成列語法對過濾字段反轉(zhuǎn)另存
- 對反轉(zhuǎn)后的新字段創(chuàng)建左前綴索引
右后綴索引創(chuàng)建了新的反轉(zhuǎn)字段，這是需要空間消耗的，而前綴索引是為了降低空間消耗的；那么右后綴索引和普通索引的性價比就不好說了
此處測試略過…
非索引優(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
-- 查詢耗時：0.499
效果：沒太大效果
POSITION
position能夠看作是locate的別名，功能跟locate同樣
SELECT * FROM yd_alarminfo_all_20220825 WHERE POSITION('網(wǎng)卡端口' IN alarmTitle)
--查詢耗時：0.544
效果：沒太大效果
INSTR
SELECT * FROM yd_alarminfo_all_20220825 WHERE INSTR(alarmTitle, '網(wǎng)卡端口' )>0 --查詢耗時：0.566
效果：沒太大效果
先查詢字段值，再范圍查詢
效果：沒什么效果
結(jié)論：除了使用索引相關(guān)的方法，本文測試了網(wǎng)上其他的一些sql寫法對模糊查詢進行優(yōu)化，其他的寫法沒有什么效果
到此這篇關(guān)于Mysql模糊查詢優(yōu)化方法及測試詳細講解的文章就介紹到這了,更多相關(guān)Mysql模糊查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
文章講述了對 MySQL 存儲過程未執(zhí)行的問題排查過程,先后排查了數(shù)據(jù)庫版本升級、用戶權(quán)限不足,最終發(fā)現(xiàn)是存儲過程創(chuàng)建時的 SQL 模式導致,即使之后去掉該模式,存儲過程內(nèi)部仍沿用舊模式,重新創(chuàng)建存儲過程后問題解決,需要的朋友可以參考下2025-02-02
子查詢指一個查詢語句嵌套在另一個查詢語句內(nèi)部的查詢,這個特性從 MySQL 4.1 開始引入,下面這篇文章主要介紹了MySQL子查詢原理的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-01-01
這篇文章主要介紹了Mysql?exists用法的的相關(guān)資料，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
淺談為什么Mysql數(shù)據(jù)庫盡量避免NULL
這篇文章主要介紹了淺談為什么Mysql數(shù)據(jù)庫盡量避免NULL,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-02-02
MySQL內(nèi)連接和外連接及七種SQL?JOINS的實現(xiàn)
這篇文章主要介紹了Mysql內(nèi)連接和外連接的區(qū)別以及七種SQL?Joins的實現(xiàn),相信看完這篇文章你對SQL內(nèi)外連接的多表查詢就足夠理解了,需要的朋友可以參考下2023-03-03
MySql獲取當前時間并轉(zhuǎn)換成字符串的實現(xiàn)
本文主要介紹了MySql獲取當前時間并轉(zhuǎn)換成字符串的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-07-07
這篇文章主要介紹了MySQL select查詢之LIKE與通配符用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
這篇文章主要介紹了MySQL 的覆蓋索引與回表的使用方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-02-02
在使用 MySQL 數(shù)據(jù)庫時,有時候會發(fā)生某個表被鎖住的情況,這可能會導致其他用戶無法對該表進行讀寫操作,影響系統(tǒng)的正常運行,本文主要介紹了淺談MySQL數(shù)據(jù)庫表鎖了怎么解鎖,感興趣的可以了解一下2023-10-10
MySQL8.0設(shè)置遠程訪問權(quán)限的方法
這篇文章主要介紹了MySQL8.0設(shè)置遠程訪問權(quán)限的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11