dba+開(kāi)源工具:讓數(shù)據(jù)庫(kù)查詢(xún)快如閃電!自動(dòng)判斷條件字段是否增加索引工具
sql_helper工具簡(jiǎn)介
索引在數(shù)據(jù)庫(kù)中非常重要,它可以加快查詢(xún)速度并提高數(shù)據(jù)庫(kù)性能。對(duì)于經(jīng)常被用作查詢(xún)條件的字段,添加索引可以顯著改善查詢(xún)效率。然而,索引的創(chuàng)建和維護(hù)需要考慮多個(gè)因素,包括數(shù)據(jù)量、查詢(xún)頻率、更新頻率等。
sql_helper工具是一個(gè)開(kāi)源項(xiàng)目,它提供命令行模式和Web端接口兩種方式使用,其主要功能是自動(dòng)判斷條件字段是否需要增加索引,適用于MySQL5.7/8.0和MariaDB數(shù)據(jù)庫(kù),旨在幫助開(kāi)發(fā)人員優(yōu)化數(shù)據(jù)庫(kù)查詢(xún)性能。通過(guò)分析SQL語(yǔ)句,該工具可以檢測(cè)出哪些條件字段可以考慮添加索引來(lái)提高查詢(xún)效率。
工作流程
第一步:通過(guò)SQL語(yǔ)法解析器,提煉出表名,別名,關(guān)聯(lián)字段名,條件字段名,排序字段名,分組字段名。
第二步:檢查是否有where條件,如沒(méi)有則給出提示。
第三步:檢測(cè)到a join b on a.id = b.id(關(guān)聯(lián)查詢(xún)時(shí)),通過(guò)查詢(xún)表結(jié)構(gòu),檢查關(guān)聯(lián)字段是否有索引,如沒(méi)有給出創(chuàng)建索引提示。
第四步:通過(guò)調(diào)用Explain執(zhí)行計(jì)劃,如果type值是ALL,或者rows大于1000,檢查該表(如有別名,找到其對(duì)應(yīng)的原始表名)和where條件字段的數(shù)據(jù)分布,工具默認(rèn)會(huì)采樣10萬(wàn)條數(shù)據(jù)作為樣本,檢查Cardinality基數(shù),例如sex性別字段,有男女兩個(gè)值,如果占比超過(guò)半數(shù)(50%),則不建議對(duì)該字段創(chuàng)建索引。
第五步:檢查group by和order by字段(同樣的算法),之后與where條件字段合并,組合成聯(lián)合索引。
第六步:檢查這些字段之前是否創(chuàng)建過(guò)索引,如果沒(méi)有給予提示創(chuàng)建,如果之前就有索引,則不提示。
需要注意的是:sql_helper工具假定你的SQL語(yǔ)句條件表達(dá)式都為and的前提下,提示創(chuàng)建聯(lián)合索引。如果是or,SQL解析器解析起來(lái)會(huì)有些困難(SQL靈活多變,且不固定,無(wú)法用通用的算法組合字段)。
例如where c1 = 1 or c2 = 2
工具會(huì)提示(c1,c2)創(chuàng)建一個(gè)聯(lián)合索引,但實(shí)際上應(yīng)該單獨(dú)對(duì)c1和c2創(chuàng)建一個(gè)獨(dú)立索引。
即select ... from t where c1 = 1
union all
select ... from t where c2 = 2
命令行方式使用
shell> chmod 755 sql_helper
shell> ./sql_helper -f test.yaml -q 'select * from sbtest1 limit 1;'
- 注:test.yaml為MySQL配置文件。
--sample參數(shù):默認(rèn)采樣10萬(wàn)條數(shù)據(jù)(你可以在從庫(kù)上獲取樣本數(shù)據(jù)),根據(jù)你的實(shí)際情況,適當(dāng)增加采樣數(shù)據(jù),比如100-1000萬(wàn)行,這樣工具會(huì)更精準(zhǔn)的判斷是否添加索引。
僅支持SELECT查詢(xún)(主要針對(duì)慢日志里的SQL)。
圖片
請(qǐng)注意,自動(dòng)判斷是否增加索引只是一個(gè)輔助功能,最終的決策還應(yīng)該根據(jù)具體的業(yè)務(wù)需求以及對(duì)數(shù)據(jù)庫(kù)性能優(yōu)化的考慮來(lái)進(jìn)行。此外,索引的創(chuàng)建和維護(hù)需要謹(jǐn)慎操作,需要考慮數(shù)據(jù)量、查詢(xún)頻率、更新頻率等因素,避免對(duì)數(shù)據(jù)庫(kù)性能產(chǎn)生負(fù)面影響。
- 工具適用于Centos7系統(tǒng)。