正文

優(yōu)化查詢的方法（查詢優(yōu)化的途徑）

發(fā)布時間：2023-04-22 09:08:32 稿源：創(chuàng)意嶺閱讀： 83

大家好！今天讓創(chuàng)意嶺的小編來大家介紹下關于優(yōu)化查詢的方法的問題，以下是小編對此問題的歸納整理，讓我們一起來看看吧。

開始之前先推薦一個非常厲害的Ai人工智能工具，一鍵生成原創(chuàng)文章、方案、文案、工作計劃、工作報告、論文、代碼、作文、做題和對話答疑等等

只需要輸入關鍵詞，就能返回你想要的內容，越精準，寫出的就越詳細，有微信小程序端、在線網(wǎng)頁版、PC客戶端

官網(wǎng)：https://ai.de1919.com。

創(chuàng)意嶺作為行業(yè)內優(yōu)秀的企業(yè)，服務客戶遍布全球各地，如需了解SEO相關業(yè)務請撥打電話175-8598-2043，或添加微信：1454722008

本文目錄:

1、mysql數(shù)據(jù)庫表太大查詢慢優(yōu)化的幾種方法
2、數(shù)據(jù)庫查詢有哪些優(yōu)化方面
3、如何優(yōu)化sqlite的查詢速度
4、ms sql server查詢優(yōu)化方法查詢速度慢的原因很多，常見如下幾種 1，沒有索引或者沒

優(yōu)化查詢的方法（查詢優(yōu)化的途徑）

一、mysql數(shù)據(jù)庫表太大查詢慢優(yōu)化的幾種方法

優(yōu)化方案：

主從同步+讀寫分離：

這個表在有設備條件的情況下，讀寫分離，這樣能減少很多壓力，而且數(shù)據(jù)穩(wěn)定性也能提高

縱向分表：

根據(jù)原則，每個表最多不要超過5個索引，縱向拆分字段，將部分字段拆到一個新表

通常我們按以下原則進行垂直拆分:（先區(qū)分這個表中的冷熱數(shù)據(jù)字段）

把不常用的字段單獨放在一張表;

把text，blob等大字段拆分出來放在附表中;

經(jīng)常組合查詢的列放在一張表中;

缺點是：很多邏輯需要重寫，帶來很大的工作量。

利用表分區(qū)：

這個是推薦的一個解決方案，不會帶來重寫邏輯等，可以根據(jù)時間來進行表分區(qū)，相當于在同一個磁盤上，表的數(shù)據(jù)存在不同的文件夾內，能夠極大的提高查詢速度。

橫向分表：

1000W條數(shù)據(jù)不少的，會帶來一些運維壓力，備份的時候，單表備份所需時間會很長，所以可以根據(jù)服務器硬件條件進行水平分表，每個表有多少數(shù)據(jù)為準。

二、數(shù)據(jù)庫查詢有哪些優(yōu)化方面

1 SQL查詢語句的重寫，對于一個查詢可以用多種查詢語句實現(xiàn)，但不同查詢語句的數(shù)據(jù)庫執(zhí)行計劃是不同的，一旦不能夠使用索引或造成較大的內存占用會導致性能下降，因此需要對查詢語句進行重寫優(yōu)化，最典型的例子就是not in語句使用外連接方式實現(xiàn)來進行優(yōu)化

2 創(chuàng)建合理的索引結構，根據(jù)查詢語句的中查詢條件，在關系表上建立相應的索引，如B+樹索引和hash索引

3 修改程序業(yè)務邏輯，有些功能如果使用SQL語句實現(xiàn)，不但SQL語句復雜，還將導致數(shù)據(jù)庫的負擔增加，因此可以將有些數(shù)據(jù)操作的業(yè)務邏輯放到應用層進行實現(xiàn)，就是通過java編程實現(xiàn)

4 修改數(shù)據(jù)庫服務器相關參數(shù)，優(yōu)化服務器性能

三、如何優(yōu)化sqlite的查詢速度

SQLite是個典型的嵌入式DBMS，它有很多優(yōu)點，它是輕量級的，在編譯之后很小，其中一個原因就是在查詢優(yōu)化方面比較簡單，它只是運用索引機制來進行優(yōu)化的，經(jīng)過對SQLite的查詢優(yōu)化的分析以及對源代碼的研究，我將SQLite的查詢優(yōu)總結如下：

一、影響查詢性能的因素：

1．對表中行的檢索數(shù)目，越小越好

2．排序與否。

3．是否要對一個索引。

4．查詢語句的形式

二、幾個查詢優(yōu)化的轉換

1．對于單個表的單個列而言，如果都有形如T.C=expr這樣的子句，并且都是用OR操作符連接起來，形如： x = expr1 OR expr2 = x OR x = expr3 此時由于對于OR，在SQLite中不能利用索引來優(yōu)化，所以可以將它轉換成帶有IN操作符的子句：x IN(expr1,expr2,expr3)這樣就可以用索引進行優(yōu)化，效果很明顯，但是如果在都沒有索引的情況下OR語句執(zhí)行效率會稍優(yōu)于IN語句的效率。

2．如果一個子句的操作符是BETWEEN，在SQLite中同樣不能用索引進行優(yōu)化，所以也要進行相應的等價轉換：如：a BETWEEN b AND c可以轉換成：(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。在上面這個子句中， (a>=b) AND (a<=c)將被設為dynamic且是(a BETWEEN b AND c)的子句，那么如果BETWEEN語句已經(jīng)編碼，那么子句就忽略不計，如果存在可利用的index使得子句已經(jīng)滿足條件，那么父句則被忽略。

3．如果一個單元的操作符是LIKE，那么將做下面的轉換：x LIKE ‘a(chǎn)bc%’，轉換成：x>=‘a(chǎn)bc’ AND x<‘a(chǎn)bd’。因為在SQLite中的LIKE是不能用索引進行優(yōu)化的，所以如果存在索引的話，則轉換后和不轉換相差很遠，因為對LIKE不起作用，但如果不存在索引，那么LIKE在效率方面也還是比不上轉換后的效率的。

三、幾種查詢語句的處理（復合查詢）

1．查詢語句為：<SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY

執(zhí)行方法： is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 這個語句的執(zhí)行過程是先將selectA和selectB執(zhí)行并且排序，再對兩個結果掃描處理，對上面四種操作是不同的，將執(zhí)行過程分成七個子過程：

outA: 將selectA的結果的一行放到最終結果集中

outB: 將selectA的結果的一行放到最終結果集中(只有UNION操作和UNION ALL操作，其它操作都不放入最終結果集中)

AltB: 當selectA的當前記錄小于selectB的當前記錄

AeqB: 當selectA的當前記錄等于selectB的當前記錄

AgtB: 當selectA的當前記錄大于selectB的當前記錄

EofA: 當selectA的結果遍歷完

EofB: 當selectB的結果遍歷完

下面就是四種操作的執(zhí)行過程：

　執(zhí)行順序

UNION ALL

UNION

EXCEPT

INTERSECT

AltB:

outA, nextA

outA,nextA

nextA

AeqB:

outA, nextA

nextA

outA, nextA

AgtB:

outB, nextB

nextB

EofA:

outB, nextB

halt

EofB:

outA, nextA

outA,nextA

halt

2．如果可能的話，可以把一個用到GROUP BY查詢的語句轉換成DISTINCT語句來查詢，因為GROUP BY有時候可能會用到index，而對于DISTINCT都不會用到索引的。

四、子查詢扁平化

例子：SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

對這個SQL語句的執(zhí)行一般默認的方法就是先執(zhí)行內查詢，把結果放到一個臨時表中，再對這個表進行外部查詢，這就要對數(shù)據(jù)處理兩次，另外這個臨時表沒有索引，所以對外部查詢就不能進行優(yōu)化了，如果對上面的SQL進行處理后可以得到如下SQL語句：SELECT x+y AS a FROM t1 WHERE z<100 AND a>5，這個結果顯然和上面的一樣，但此時只需要對

數(shù)據(jù)進行查詢一次就夠了，另外如果在表t1上有索引的話就避免了遍歷整個表。

運用flatten方法優(yōu)化SQL的條件：

1.子查詢和外查詢沒有都用集函數(shù)

2.子查詢沒有用集函數(shù)或者外查詢不是個表的連接

3.子查詢不是一個左外連接的右操作數(shù)

4.子查詢沒有用DISTINCT或者外查詢不是個表的連接

5.子查詢沒有用DISTINCT或者外查詢沒有用集函數(shù)

6.子查詢沒有用集函數(shù)或者外查詢沒有用關鍵字DISTINCT

7.子查詢有一個FROM語句

8.子查詢沒有用LIMIT或者外查詢不是表的連接

9.子查詢沒有用LIMIT或者外查詢沒有用集函數(shù)

10.子查詢沒有用集函數(shù)或者外查詢沒用LIMIT

11.子查詢和外查詢不是同時是ORDER BY子句

12.子查詢和外查詢沒有都用LIMIT

13.子查詢沒有用OFFSET

14.外查詢不是一個復合查詢的一部分或者子查詢沒有同時用關鍵字ORDER BY和LIMIT

15.外查詢沒有用集函數(shù)子查詢不包含ORDER BY

16.復合子查詢的扁平化：子查詢不是一個復合查詢，或者他是一個UNION ALL復合查詢，但他是都由若干個非集函數(shù)的查詢構成，他的父查詢不是一個復合查詢的子查詢，也沒有用集函數(shù)或者是DISTINCT查詢，并且在FROM語句中沒有其它的表或者子查詢，父查詢和子查詢可能會包含WHERE語句，這些都會受到上面11、12、13條件的限制。

例： SELECT a+1 FROM (

SELECT x FROM tab

UNION ALL

SELECT y FROM tab

UNION ALL

SELECT abs(z*2) FROM tab2

) WHERE a!=5 ORDER BY 1

轉換為：

SELECT x+1 FROM tab WHERE x+1!=5

UNION ALL

SELECT y+1 FROM tab WHERE y+1!=5

UNION ALL

SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5

ORDER BY 1

17.如果子查詢是一個復合查詢，那么父查詢的所有的ORDER BY語句必須是對子查詢的列的簡單引用

18.子查詢沒有用LIMIT或者外查詢不具有WHERE語句

子查詢扁平化是由專門一個函數(shù)實現(xiàn)的，函數(shù)為：

static int flattenSubquery(

Parse *pParse, /* Parsing context */

Select *p, /* The parent or outer SELECT statement */

int iFrom, /* Index in p->pSrc->a[] of the inner subquery */

int isAgg, /* True if outer SELECT uses aggregate functions */

int subqueryIsAgg /* True if the subquery uses aggregate functions */

)

它是在Select.c文件中實現(xiàn)的。顯然對于一個比較復雜的查詢，如果滿足上面的條件時對這個查詢語句進行扁平化處理后就可以實現(xiàn)對查詢的優(yōu)化。如果正好存在索引的話效果會更好！

五、連接查詢

在返回查詢結果之前，相關表的每行必須都已經(jīng)連接起來，在SQLite中，這是用嵌套循環(huán)實現(xiàn)的，在早期版本中，最左邊的是最外層循環(huán)，最右邊的是最內層循環(huán)，連接兩個或者更多的表時，如果有索引則放到內層循環(huán)中，也就是放到FROM最后面，因為對于前面選中的每行，找后面與之對應的行時，如果有索引則會很快，如果沒有則要遍歷整個表，這樣效率就很低，但在新版本中，這個優(yōu)化已經(jīng)實現(xiàn)。

優(yōu)化的方法如下：

對要查詢的每個表，統(tǒng)計這個表上的索引信息，首先將代價賦值為SQLITE_BIG_DBL（一個系統(tǒng)已經(jīng)定義的常量）：

1) 如果沒有索引，則找有沒有在這個表上對rowid的查詢條件：

1．如果有Rowid=EXPR，如果有的話則返回對這個表代價估計，代價計為零，查詢得到的記錄數(shù)為1，并完成對這個表的代價估計，

2．如果沒有Rowid=EXPR 但有rowid IN (...)，而IN是一個列表，那么記錄返回記錄數(shù)為IN列表中元素的個數(shù)，估計代價為NlogN,

3．如果IN不是一個列表而是一個子查詢結果，那么由于具體這個子查詢不能確定，所以只能估計一個值，返回記錄數(shù)為100，代價為200。

4．如果對rowid是范圍的查詢，那么就估計所有符合條件的記錄是總記錄的三分之一，總記錄估計為1000000，并且估計代價也為記錄數(shù)。

5．如果這個查詢還要求排序，則再另外加上排序的代價NlogN

6．如果此時得到的代價小于總代價，那么就更新總代價，否則不更新。

2) 如果WHERE子句中存在OR操作符，那么要把這些OR連接的所有子句分開再進行分析。

1．如果有子句是由AND連接符構成，那么再把由AND連接的子句再分別分析。

2．如果連接的子句的形式是X<op><expr>，那么就再分析這個子句。

3．接下來就是把整個對OR操作的總代價計算出來。

4．如果這個查詢要求排序，則再在上面總代價上再乘上排序代價NlogN

5．如果此時得到的代價小于總代價，那么就更新總代價，否則不更新。

3) 如果有索引，則統(tǒng)計每個表的索引信息，對于每個索引：

1．先找到這個索引對應的列號，再找到對應的能用到（操作符必須為=或者是IN（…））這個索引的WHERE子句，如果沒有找到，則退出對每個索引的循環(huán)，如果找到，則判斷這個子句的操作符是什么，如果是=，那么沒有附加的代價，如果是IN（sub-select），那么估計它附加代價inMultiplier為25，如果是IN（list），那么附加代價就是N（N為list的列數(shù)）。

2．再計算總的代價和總的查詢結果記錄數(shù)和代價。

3． nRow = pProbe->aiRowEst[i] * inMultiplier;/*計算行數(shù)*/

4． cost = nRow * estLog(inMultiplier);/*統(tǒng)計代價*/

5．如果找不到操作符為=或者是IN（…）的子句，而是范圍的查詢，那么同樣只好估計查詢結果記錄數(shù)為nRow/3，估計代價為cost/3。

6．同樣，如果此查詢要求排序的話，再在上面的總代價上加上NlogN

7．如果此時得到的代價小于總代價，那么就更新總代價，否則不更新。

4) 通過上面的優(yōu)化過程，可以得到對一個表查詢的總代價（就是上面各個代價的總和），再對第二個表進行同樣的操作，這樣如此直到把FROM子句中所有的表都計算出各自的代價，最后取最小的，這將作為嵌套循環(huán)的最內層，依次可以得到整個嵌套循環(huán)的嵌套順序，此時正是最優(yōu)的，達到了優(yōu)化的目的。

5) 所以循環(huán)的嵌套順序不一定是與FROM子句中的順序一致，因為在執(zhí)行過程中會用索引優(yōu)化來重新排列順序。

六、索引

在SQLite中，有以下幾種索引：

1) 單列索引

2) 多列索引

3) 唯一性索引

4) 對于聲明為：INTEGER PRIMARY KEY的主鍵來說，這列會按默認方式排序，所以雖然在數(shù)據(jù)字典中沒有對它生成索引，但它的功能就像個索引。所以如果在這個主鍵上在單獨建立索引的話，這樣既浪費空間也沒有任何好處。

運用索引的注意事項：

1) 對于一個很小的表來說沒必要建立索引

2) 在一個表上如果經(jīng)常做的是插入更新操作，那么就要節(jié)制使用索引

3) 也不要在一個表上建立太多的索引，如果建立太多的話那么在查詢的時候SQLite可能不會選擇最好的來執(zhí)行查詢，一個解決辦法就是建立聚蔟索引

索引的運用時機：