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)。