5個概念拯救龜速的SQL查詢(Query)

優化查詢速度，相信是每個身為後端工程師的開發者，在職涯中必定會遇到的課題，而索引想必是直覺會被選擇的解決方案，而最近跟同事有碰到這樣「最佳化索引」的挑戰，趁著解決後打鐵趁熱，把自己從他們身上學會加上自己查詢的知識記錄下來，希望能對需要使用索引的開發人員有幫助。

CraftsmanHenry

10 min readDec 24, 2022

Index我想是目前大部分的開發人員聽到要最佳化（Optimize）關聯式資料庫（RDBMS）查詢（Query）第一個想到的好朋友，但不知道大家有沒有遇到過

明明就對要查詢的欄位建立過Index，怎麼查起來還是這麼慢

的窘境，過去的我就遇到不少次，前陣子剛剛好與同事合作時又再碰到一次，因此趁這個機會一次把索引再好好複習一次，並趁著剛改善完記憶還鮮明，趕快把對Index的理解記錄下來，並分享給有需要的人。

在開始文章前，先聲明這篇文章使用的是

資料庫：MySQL
資料庫引擎：InnoDB
索引類型：B-Tree Index

其中詳述InnoDB和B-Tree Index並不在討論的範圍，讀完此篇文章可以得知

為什麼索引包含查詢欄位，卻無法加速查詢的原因？
如何知道送一段查詢進去資料庫後，會被如何解讀？
設計Index時要注意的觀念

那接下來就直接開始介紹一下這次到底是遇到什麼狀況需要優化索引

情境說明

在這個情境中，核心概念是事件，事件有兩個來源，新聞與社群網站（Facebook, PTT），問題出在要查詢「一段連續較長的時間」中，特定類別（ex: 體育、財經、政治…等）的「熱門新聞事件」和「熱門社群網站事件」，大致上各表如下

透過Postman GET呈現畫面用的API發現若查詢的時間長於一個月，response的時間（5–30 secs）就會變得令人無法接受，上述兩個需求的SQL查詢大致如下

從查詢的條件可以知道目標是針對特定語言、類別和一段latest_news_time（發布時間）之間，分別根據與event相關的「新聞」和「社群網站貼文」數量（amount）排序後，找出相關數量前N（25）名的事件。

那該怎麼辦呢？當然我和同事一開始馬上就想到索引，因此我們馬上尋找有沒有索引有包含上述查詢條件中用到的欄位，而我們找到以下的索引

不過以上索引就像不存在一般，毫無發揮作用。因此我們用了SQL的 EXPLAIN關鍵字，分析看看到底SELECT語法是如何在MySQL中被執行的。而下面是我們的結果

看起來有用到索引，不過用錯索引！，因此接下來我們有兩項目標

有很多類似的索引，SELECT是否能指定使用的索引
研究索引機制是如何在SELECT中被使用的

了解問題後，下個小節來嘗試回答上面的這兩個問題

索引最佳化處方

指定使用特定的索引

Index Hints（索引提示）

查過文件後，可以利用Index Hints在table name後，加上此張表要使用的指定索引，而指定方式又分為USE INDEX和FORCE INDEX，如下

FROM `event_category` ec USE INDEX(idx_event_category_v3)

USE INDEX 會建議Query Optimizer使用指定的index，若Query Optimizer認為Table Scan比較有效率，會有機會不使用index

FORCE INDEX 顧名思義強制使用指定的INDEX，除非指定的index都不適用才會scan table，建議只在非常肯定scan table很昂貴（久）的狀況下才使用

備註：除了FROM之後的主要Table外，包括JOIN的table也能夠加上Index Hint

最佳化SELECT前必須要知道的索引概念

Covering Index（全覆式索引）

從上一小節知道可以用FORCE INDEX直接指定要用什麼索引JOIN後，我們還是發現Query的速度很慢，眼尖的人可能會發現，還有一個amount的欄位來不在索引中，甚至是在不同表內。

此時就需要Covering Index概念，Covering Index我自己翻譯成「全覆式索引」，意思是索引中包含要查詢的所有欄位。當查詢被執行的時候，若需要的欄位全都在索引當中，則會直接將索引查到的內容回傳，減少回主表查詢缺少欄位所需要的I/O時間。

備註：InnoDB與MyISAM的Covering Index運作方法有些差異

Multiple Column Index（複合索引）

MySQL索引允許多個欄位，假設大家都已經知道這個概念，在此要討論的是複合索引Leftmost Prefix的概念，希望大家還記得我們已經建好的索引idx_event_category_v3，它的建立方法大致上如下

這個索引在這裡用(`language`, `category`, `latest_news_time`)這種方式表示。Leftmost Prefix概念是指「索引能支援的查詢條件（WHERE）必須是該索引包含欄位的連續子集合」，用以上例子來說，以下都是可以支援的查詢條件

(`language`)
(`language`, `category`)
(`language`, `category`, `latest_news_time`)

其他子集合則是不行的，隨意舉兩個

(`category`, `latest_news_time`)
(`language`, `latest_news_time`)

Leftmost Prefix使我們了解建立索引時需要注意「索引的順序性」，也因此衍生出順序該怎麼決定比較好的議題，首先不用懷疑的是「頻繁被作為查詢條件的欄位越左方越好」，接著又可以問如果有多個時常被查詢的欄位，順序怎麼擺呢？

小弟我根據在網路上的文章並搭配MySQL文件了解後得到兩種選擇方式，彼此沒有好壞

資料差異性「低」的欄位優先：此選擇似乎有違背上面提到的Cardinality的概念，不過這是基於壓縮率的考量，多個重複的值開頭形成的複合索引，壓縮後比較有利於減少儲存所需空間
資料差異性「高」的欄位優先：高差異性的欄位優先就是完全基於Performance的考量，讓Query能夠比較快找到

因此可以總結2項建立索引時，須考慮的重點

欄位作為查詢條件有多頻繁
壓縮率（儲存）

下面附上，參考的兩個討論，討論之中有提到一些相關的MySQL文件，有興趣的人可以了解一下

Composite indexes: Most selective column first?

I've been reading about composite indexes and I'm slightly confused about ordering. This documentation (little less…

dba.stackexchange.com

Oracle: does the column order matter in an index?

If a and b both have 1000 distinct values and they are always queried together then the order of columns in the index…

stackoverflow.com

Index Cardinality（索引基數？）

Cardinality翻譯叫做基數，總之我是看不太懂，不過其實就是指「索引的獨特性」，索引的獨特性受到組成索引欄位的獨特性影響。

欄位的獨特性用官方文件上的範例解釋一下，先看下面這個Query

SELECT col1 FROM table1 WHERE col1 = 50;

若table1共有1000比資料，以上Query若col1是有unique constraint獨特限制的欄位，結果只會回傳1比（col1有1000種值）。另一方面，假設col1總共只有10種值且值均勻分布，那以上的查詢則會回傳20比。

根據上面索引欄位排序的概念，越左邊的欄位獨特性越高，可以在每次B-Tree索引的分支上過濾掉越多的值，使得搜尋的速度越快。

獨特性外，另外還要考慮欄位的分散性，分散性是指欄位中每種值的數量之間的差異性有多高，舉例來說 col1 中有10種值，不過val1有991比，其他9種值比都各只有1比，以上就是分散性極差的範例，分散性太差也無法在Tree的分支中過濾掉夠多的值。

總而言之，要有高Cardinality的索引，組成的欄位的內部資料最好能

獨特性高
分散性高

ORDER BY Fields（欄位排序）

不知道大家是否還記得此篇文章面對的問題有個條件是

找出「前N熱門」的新聞與社群事件

因此會需要ORDER BY這個關鍵字，儘管我們最後有LIMIT N的條件，不過為了針對特定時間區段的數量做排序，就需要把時間內所有的值通通抓出來後，再進行排序；不是單單收集N比指定時間區段的資料就可以，可以見以下示意圖

圖3. 有ORDER BY會需要三個月內的全數資料排序再回傳，沒有則只需要前N比回傳

另外觀察上面的Query會發現，排序用的amount並不在索引之中，如此一來Query Optimizer會認為Scan Table的效率可能會比較好，因而棄用index，到此針對上述的查詢要讓ORDER BY善用索引，可以總結出兩項原則

WHERE的條件必須要以Leftmost Prefix的方式存放在索引當中
ORDER BY和SELECT的欄位必須全數在索引（是的Covering Index）當中

因此除了原有的idx_event_category_v3外，還需要一個包含amount欄位的索引，大致上如下

最後我們的 Query 變成這樣

備註：InnoDB的索引會自動將Primary Key包含在其中

在結束之前

這篇文章中從同事跟我在工作上遭遇的Query瓶頸開始，介紹查詢的情境並且發現我們「以為有用索引就會變快」的錯覺 😂，從而啟發我們去更深入了解MySQL的索引機制是如何在Query Optimizer中被執行的，在了解以上說明的5個概念有這樣的結論

在Query中，FROM跟JOIN關鍵字後的表都可以使用索引提示（Index Hints）指定要用哪些索引加快速度
原本的event和event_category表中有些（json）欄位的值非常巨大導致Scan Table很慢，因此會需要全覆式索引（Covering Index）把所有需要的欄位通通塞進索引中，以加速ORDER BY的速度
複合索引（Mutiple Column Index）需要考慮Leftmost Prefix和大多數的Query是如何下搜尋條件的，如此在提升Performance的同時還可以節省儲存空間和提升索引的可利用率
知道可以用Cardinality（索引基數）判斷索引欄位的優劣

希望到此有耐心讀到這的人也跟我一樣也對索引有大躍進😂的認識，這次對於MySQL Performance的提升實在花足心思；當然改善完畢後，還有針對效能的測試，不過為了不把這篇文章變得累牘連篇，還是下次有機會再提效能測試的部分吧！（又挖了個坑！ 😮）

最後這篇文章不是我個人的研究項目，而是與其他兩位同事一起研究的，這次改善Query Performance的任務中，學會相當多之前對於索引不一樣的概念，讓我對MySQL又更了解一些，真的非常感謝他們兩位。