SQL查詢如何最佳化？ （詳解）

為什麼要最佳化

系統的吞吐量瓶頸往往會出現在資料庫的存取速度上，也就是隨著應用程式的運行，資料庫的中的資料會越來越多，處理時間會相應變慢，且資料是存放在磁碟上的，讀寫速度無法和記憶體相比

##如何最佳化

1、設計資料庫時：資料庫表、欄位的設計，儲存引擎

#2、利用好MySQL本身提供的功能，如索引，語句寫法的調優

3、MySQL叢集、分庫分錶、讀寫分離

關於SQL語句的最佳化的方法方式，網路有很多經驗，所以本文拋開這些，設法在DAO層的最佳化和資料庫設計最佳化上建樹，並列舉兩個簡單實例

例子1：ERP查詢最佳化

現況分析：

1、缺少關聯索引

2、Mysql本身的效能所限，對多個表的關聯支援不好，目前的效能主要集中在列表查詢上面，列表查詢關聯了許多表

應對方法：

1 增加必要的索引：透過explain查看執行記錄，依照執行計畫新增索引；

2 先統計業務資料主表主鍵，取得較小結果集，然後再利用結果集關聯查詢；
1） 先根據主表和條件查詢顯示業務資料的主鍵
2） 根據主鍵作為查詢條件，再關聯其他關聯表，查詢需要的業務欄位
3 ) 在主表查詢時，針對需要關聯其他表的查詢條件，需要做只有設定這個條件，才會做表格關聯的設定

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假设未优化前的SQL是这样的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?.....

那么优化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1AND A.XX = ?AND A.VV = ?第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?

小結：
##這種最佳化適用於，列表查詢，因為一個列表查詢的條件一般都是和主表掛鉤的，所以利用這一點，建立關鍵字段索引，同時通過查詢條件的限制大大的縮小主表的數據量。這樣關聯其他表的時候就會快的多

範例2：文章搜尋優化

#假設你要做個貼吧的文章搜尋功能，最簡單直接的儲存結構，就是利用關係資料庫，創建這樣一個儲存文章的關係資料庫表TT_ARTICLES：

 

那麼，假如現在的搜尋關鍵字是“目標”，我們就可以利用字串匹配的方式來對CONTENT 列進行匹配查詢：

select * from ARTICLES where CONTENT like '% 目标 %';

這很容易就實現了搜尋功能。但是，這樣的方式有著明顯的問題，即使用 % 來進行字串匹配是非常低效的，因此這樣的查詢需要遍歷整個表（全表掃描）。幾篇、幾十篇文章的時 候，還不是什麼問題，但是如果有幾十萬、幾百萬的文章，這種方式是完全不可行的。且不說單獨的關係資料庫表就不能容納那麼大的資料了，就是能夠容納，要掃描一遍，這裡的時間代價是難以想像的

於是，我們就要引入「倒排索引」的技術了。在前面所述的場景下， 我們可以把這個概念拆分為兩個部分來解釋： 好，那上面的ARTICLES 表依然存在，但現在需要添加一個關鍵字表KEYWORDS，並且，KEYWORD 列需要添加索引，因此這條關鍵字的記錄可以快速找到：

當然，我們還需要一個關聯關係表把KEYWORDS 表和ARTICLES 表結合起來， KEYWORD_ID 和ARTICLE_ID 作為聯合主鍵 

你看，這其實是一個多對多的關係，也就是同一個關鍵字可以出現在多​​篇文章中，而一篇文章可以包含多個不同的關鍵字。這樣，我們可以先根據被索引了的關鍵字，從 KEYWARDS 表 中找到對應的 KEYWORD_ID，進而根據它在上面的關聯關係表找到 ARTICLE_ID，再根據 它去 ARTICLES 表中找到對應的文章。

小結：

這看起來是三次查找，但是因為每次都走索引，就免去了全表掃描，在資料量較小的時候速度並不慢，而且，在使用SQL 實作的時候，這個過程完全可以放到一個SQL 語句中。在數 據量較小的時候，上面的方法已經夠好用了。這樣解決了全表掃描和字串 % 匹配查詢造成的效能問題。

總結：

在技術面試的時候，如果你能舉出實際的例子，或者是直接說自己開發過程的問題和收穫會讓面試分會加很多，回答邏輯性也要強一點，不要東一點西一點，容易把自己都繞暈的。例如，問為怎麼優化SQL你不要一上來就直接回答加索引，你可以這樣回答：

面試官您好，首先我們的專案DB資料量遇到了瓶頸，導致清單查詢非常緩慢，給使用者的體驗不好，為了解決這個問題，有很多種方法，例如最基本的資料庫表設計，基本的SQL優化，MYSQL的集群，讀寫分離，分庫分錶，架構上增加緩存層等，他們的優缺點……，綜合這些然後再結合我們項目特點，最後我們在技術選型的時候選了誰。

如果你這樣有條不紊，有理有據的回答了問題而且還說出這麼多問題外的知識點，面試官會覺得你不只是一個會寫代碼的人，而是你邏輯清晰，你對科技選型，有自己的理解與思考

本文來自 SQL教學 專欄，歡迎學習！

以上是SQL查詢如何最佳化？ （詳解）的詳細內容。更多資訊請關注PHP中文網其他相關文章！