詳解sqlite中的查詢規(guī)劃器
來源:易賢網(wǎng) 閱讀:95355 次 日期:2016-11-15 09:38:18
溫馨提示:易賢網(wǎng)小編為您整理了“詳解sqlite中的查詢規(guī)劃器”,方便廣大網(wǎng)友查閱!

這個查詢不是特別復(fù)雜,不過,即便這樣,它仍然可以替代上百行,也許是上千行處理過程代碼。這個查詢的要點是:向下掃描event表,查找滿足下列三個條件中任何一個的最新的200條提交記錄:

  1.     此提交含有trunk標(biāo)簽。
  2.     此提交有個子提交含有“trunk標(biāo)簽。
  3.     此提交有個父提交含有“trunk標(biāo)簽。

第一個條件將顯示所有主干分支上的提交,第二個和第三個條件包含合并到主干分支,或者由主干分支產(chǎn)生的提交。這三個條件是通過在此查詢的where子句中用or連接三個exists語句實現(xiàn)的。使用下一代查詢規(guī)劃器引起的性能下降是由第二個和第三個條件產(chǎn)生的。兩個條件里存在的問題是相同的,因此我們只看第二個條件。第二個條件的子查詢可以重寫為如下語句(把次要的和不重要的進行了簡化):

plink表保存著各個提交之間的父子關(guān)系。tagxref表把標(biāo)簽映射到提交上。作為參考,對這兩個表進行查詢的模式的相關(guān)部分顯示如下:
實現(xiàn)這樣的查詢只有兩個方法值得考慮。(當(dāng)然可能還有許多其他算法,不過它們中的任何一個都不是“最佳”算法的競爭者。

  •     查找提交$ckid的所有子提交,然后對每一個進行測試,看看是否有子提交包含$trunk標(biāo)簽
  •     查找所有包含$trunk標(biāo)簽的提交,然后對每個這樣的提交進行測試,看看是否有$ckid提交的子提交。

僅憑直覺,我們?nèi)祟愓J(rèn)為第一個算法是最佳選擇。每個提交可能有幾個子提交(其中有一個提交是我們最常用到的。),然后對每個子提交進行測試,用對數(shù)運算計算出查找到$trunk標(biāo)簽的時間。實際上,算法1確實較快。然而下一代查詢規(guī)劃器卻沒有使用人們直覺上的最佳選擇。下一代查詢規(guī)劃器一定是選擇了很難得算法,算法2在數(shù)學(xué)上相對稍微難些。這是因為:在沒有其他信息的情況下下一代查詢規(guī)劃器一定假設(shè)plink_i1和tagxref_i1索引具有同等的質(zhì)量和同等的可選擇性。算法2使用了tagxref_i1索引的一個字段,plink_i1索引的兩個字段,而算法1只是使用了這兩個索引的第一個字段。正是由于算法2使用了多個字段的索引,所以下一代查詢規(guī)劃器才會以自己的標(biāo)準(zhǔn)正確地確定它作為兩種算法中性能較好的算法。兩個算法所花費的時間非常接近,算法2 只是勉強稍稍領(lǐng)先算法1。不過,這種情況下,選擇算法2確實是正確的。
很不幸,在實際的應(yīng)用中算法2比算法1要慢些。

出現(xiàn)這樣的問題是因為索引并不是具有同等質(zhì)量。一個提交有可能只有一個子提交。這樣plink_i1索引的第一個字段通常縮減值對一行進行搜索。不過由于成千上萬的提交都包含有trunk標(biāo)簽,所以tagxref_i1的第一個字段對縮減搜索不會有多大幫助。

下一代查詢規(guī)劃器是沒有辦法知道tagxref_i1在這樣的查詢中幾乎沒有什么用處,除非在數(shù)據(jù)庫上運行analyze。analyze命令 收集了各個索引的質(zhì)量統(tǒng)計信息,并把 這些統(tǒng)計信息存儲到sqlite_stat1表里。如果下一代查詢規(guī)劃器能夠訪問這些統(tǒng)計信息 ,那么在很大程度上它就會非常容易地選擇算法1作為最佳算法。
難道舊查詢規(guī)劃器沒有選擇算法2?很簡單:因為nn算法甚至從來都沒有考慮到算法2。這類規(guī)劃問題的圖示如下:

在如左圖那樣“沒有運行analyze“的情況下,nn算法選擇循環(huán)p9plink)作為外循環(huán),因為4.9比5.2要小,結(jié)果就是選擇p-t路徑,即算法1。nn算法只是在每一步查找一個最佳選擇路徑,因此它完全忽略了這樣一個事實:5.2+4.4是比4.9+4.8性能稍稍有些好的規(guī)劃。然而n3算法對著兩個連接追蹤了5個最佳路徑,因此它最終選擇了t-p路徑,因為這條路徑的總體資源消耗要少一些。路徑t-p就是算法2。

注意: 如果運行了analyze,那么對資源消耗的評估就更加接近于現(xiàn)實,這樣nn和n3都選擇算法1。

(附注:最新的兩圖中對資源消耗的評估是下一代查詢規(guī)劃器使用以2為底的對數(shù)算法計算得出來的,而且與舊查詢規(guī)劃器相比假設(shè)的資源消耗稍微有些不同。因此,最后兩個圖中的資源消耗評估不能與tpc-h q8圖里的資源消耗評估進行比較。)

4.2 問題修正

對資源倉庫數(shù)據(jù)庫運行analyze可立即修復(fù)這類性能問題。然而,無論是否對資源倉庫是否進行分析,我們都要求fossil十分強壯,而且總是能夠快速地運行?;谶@個原因,我們修改查詢使用cross join操作符而不使用常用的join操作符。sqlite將不會對cross join連接的表重新排序。這個功能是sqlite中長期都有的一個功能,做這么特別的設(shè)計就是允許具有豐富經(jīng)驗的開發(fā)人員能夠強制sqlite執(zhí)行特定的嵌套循環(huán)順序。一旦某個連接更改為(增加了一個關(guān)鍵字的)cross join這樣的連接,下一代查詢規(guī)劃器就不管是否使用analyze收集統(tǒng)計統(tǒng)計信息都強制選擇稍稍快一點的算法1。

我們說算法1快一些“,不過,嚴(yán)格來說這么說不準(zhǔn)確。對一個常見的存儲倉庫運行算法1是快一些,不過,可能構(gòu)建這樣一種資源倉庫:對資源倉庫的每一次提交都是提交給不同的名字唯一的分支上,而且所有的提交都是根提交的子提交。這種情況下,tagxref_i1與plink_i1相比就具有更多的可選項了,此時算法2才真正快一些。然而實際中這樣的資源倉庫極不可能出現(xiàn),所以使用cross join語法硬編碼嵌套循環(huán)的順序是解決這種情形下存在問題的適合方案。

5.0 避免或者修正查詢規(guī)劃器問題的方法一覽表

    不要驚慌!查詢規(guī)劃器選擇差的規(guī)劃這種情況實際上是非常罕見的。你未必會在應(yīng)用中碰到這樣的問題。如果你沒有性能方面問題,那么你就不必為此而擔(dān)心。

    創(chuàng)建正確的索引。大多數(shù)sql性能問題不是因為查詢規(guī)劃器問題而引起的,而是因為缺少合適的索引。確保索引可以促進所有大型的查詢。大多數(shù)性能問題都可以使用一個或者兩個create index命令來解決,而不需要對應(yīng)用代碼進行修改。

    避免創(chuàng)建低質(zhì)量的索引。(用于解決查詢規(guī)劃器問題而創(chuàng)建的)低質(zhì)量索引是這樣的索引:表里的索引最左一個字段具有相同值的行超過10行或者20行。特別注意,避免使用布爾字段或或者“枚舉類型”字段作為索引的最左一字段。

    這篇文章的前一段所說的fossil性能問題是因為tagxref表的tagxref_i1索引的最左一子段(tagid字段)具有相同值得項超過1萬。

    如果你一定要使用低質(zhì)量的索引,那么請一定要運行analyze。只要查詢規(guī)劃器知道那個索引時低質(zhì)量的,那么低質(zhì)量的索引就不會讓它迷惑。查詢規(guī)劃器知曉低質(zhì)量索引的方法是通過sqlite_stat1表的內(nèi)容來實現(xiàn)的,這個表示有analyze命令計算得來的。

    當(dāng)然,analyze只有在數(shù)據(jù)庫一開始就擁有非常大量的內(nèi)容的情況下才能夠高效地運行。當(dāng)你希望創(chuàng)建一個數(shù)據(jù)庫并累積了大量數(shù)據(jù)的時候,你可以運行命令analyze sqlite_master創(chuàng)建sqlite_stat1表,然后(使用常用的insert語句)向sqlite_stat1表中填入用來說明這樣的數(shù)據(jù)庫正適合你的應(yīng)用的內(nèi)容-也許這樣的內(nèi)容是你在對實驗室的某個填寫的非常完美的模板數(shù)據(jù)庫運行analyze命令后所獲得的。

    編寫你自己的代碼。增加可以讓你快速且非常容易就能知道哪些查詢需要很多時間,這樣就只運行哪些特別不需要花太長時間的查詢。

    如果查詢可能使用沒有運行分析的數(shù)據(jù)庫上的低質(zhì)量索引,那么請使用cross join語法,強制使用特定的嵌套循環(huán)順序。sqlite對cross join操作符進行特殊的處理,它強制左表為右表的外部循環(huán)。

    如果有其他方法實現(xiàn),那么就避免這么做,因為它與任何一個sql語言理念里的強大的優(yōu)點相抵觸,特別是應(yīng)用開發(fā)人不需要了解查詢規(guī)劃。如果你使用了cross join,那么直到開發(fā)周期的后期你也要這么做,而且要在注釋里仔細(xì)地說明cross join是如何使用的,這樣以后才有可能把它去掉。在開發(fā)周期的早期就避免使用cross join,因為這么做是不成熟的優(yōu)化措施,也就是眾所周知的萬惡之源。

    使用單目運算符+,取消where子句某些限制條件。當(dāng)對某個具體的查詢有更高質(zhì)量的索引可以使用的時候,如果查詢規(guī)劃器仍然堅持選擇差質(zhì)量的索引,那么請在where子句中謹(jǐn)慎地使用單目運算符+,這樣做就可以強制查詢規(guī)劃器不使用差質(zhì)量的索引。如果可能的話,就盡量小心地添加這個這個運算符,而且尤其避免在應(yīng)用開發(fā)的周期的早期就使用。特別要注意:給一個與類型密切相關(guān)的等號表達式增加單目運算符+可能更改這個表達式的結(jié)果。

    使用indexed by語法,強制有問題的查詢選擇特定的索引。同前兩個標(biāo)題一樣,如果可能的話,盡量避免使用這個方法,尤其避免在開發(fā)的早期這么做,因為很清楚,它是一個不成熟的優(yōu)化措施。
6.0 結(jié)論

sqlite的查詢規(guī)劃器做這樣的工作做得非常好:為正在運行的sql語句選擇快速算法。對舊查詢規(guī)劃器來說,這是事實,對新的下一代查詢規(guī)劃器來說更是這樣。也許偶然會出現(xiàn)這樣的情況:由于信息不完整,查詢規(guī)劃器選擇了稍差的查詢規(guī)劃。 與使用舊查詢規(guī)劃器相比,使用下一代查詢規(guī)劃器這種情形就會更少出現(xiàn)了,不過仍然有可能出現(xiàn)。即便出現(xiàn)了這種極少出現(xiàn)的情況,應(yīng)用開發(fā)人員需要做的是了解和幫助查詢規(guī)劃器做正確的事情。通常情況下,下一代查詢規(guī)劃器只是對sqlite做了一個新的增強,這種增強可以讓應(yīng)用運行的更快些,而且不需要開發(fā)人員做更多的思考或者動作。

更多信息請查看技術(shù)文章
易賢網(wǎng)手機網(wǎng)站地址:詳解sqlite中的查詢規(guī)劃器
由于各方面情況的不斷調(diào)整與變化,易賢網(wǎng)提供的所有考試信息和咨詢回復(fù)僅供參考,敬請考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇?zhǔn)!

2025國考·省考課程試聽報名

  • 報班類型
  • 姓名
  • 手機號
  • 驗證碼
關(guān)于我們 | 聯(lián)系我們 | 人才招聘 | 網(wǎng)站聲明 | 網(wǎng)站幫助 | 非正式的簡要咨詢 | 簡要咨詢須知 | 加入群交流 | 手機站點 | 投訴建議
工業(yè)和信息化部備案號:滇ICP備2023014141號-1 云南省教育廳備案號:云教ICP備0901021 滇公網(wǎng)安備53010202001879號 人力資源服務(wù)許可證:(云)人服證字(2023)第0102001523號
云南網(wǎng)警備案專用圖標(biāo)
聯(lián)系電話:0871-65099533/13759567129 獲取招聘考試信息及咨詢關(guān)注公眾號:hfpxwx
咨詢QQ:526150442(9:00—18:00)版權(quán)所有:易賢網(wǎng)
云南網(wǎng)警報警專用圖標(biāo)