[MySQL效能最佳化系列] 彙總索引

標籤：mys   height   user   HERE   複雜   項目   mysql效能最佳化   使用者表   tom   

1. 普通青年的索引使用方式

假設我們有一個使用者表 tb_user，內容如下：

name	age	sex
jack	22	男
rose	21	女
tom	20	男
...	...	...

執行SQL語句：

SELECT name FROM tb_user WHERE age = 20;

預設情況下，MySQL需要遍曆整張表，才能找到合格記錄。如果在age欄位上建立索引，那麼MySQL可以很快找到所有合格記錄（索引本身通過B+樹實現，查起來很快。簡單起見，想象一下二分尋找和遍曆尋找的區別。）

2. 文藝青年的玩法2.1 用冗餘的聯合索引加速查詢

接著上面的例子，我們假設，tb_user表有一百萬行，通常情況下，"WHERE age = 20"這樣的語句，會返回幾萬行資料，實際測試下發現，速度不夠快。

原因是，MySQL根據索引查詢到合格記錄後，還需要到資料表空間裡一一尋找這些記錄（實際上，索引裡同時記錄了age欄位和關聯記錄的物理行號），這意味著，MySQL必須讀取資料表空間多達幾萬次，才能返回最終結果。

聰明的你可能已經想到了，如果age欄位的索引上有name欄位的值話，MySQL就不用再費事地去訪問資料表空間了。

最終解決方案：建立聯合索引，讓MySQL直接從索引中取出name欄位的值

KEY `age_with_name` (`age`,`name`)

注意這裡的順序，必須是先age後name，反之不行（除非你是根據name查age）。

2.2 用冗餘的聯合索引加速排序

依然是之前的表，假設要做這樣的查詢：

SELECT * FROM tb_user ORDER BY age;

因為我們在age上有索引，所以排序是很快的（索引的本質就是將表記錄的物理行號按照特定規則排序）

實際項目中，SQL可能比這個複雜些，比如：

SELECT * FROM tb_user WHERE sex=‘男‘ ORDER BY age;

這個時候，age欄位上的索引就派不上用場了。因為，age索引是面向整個表的，篩選後的表和age索引是對不上的。

解決方案：依然是聯合索引！

KEY `age_with_name` (`sex`,`age`)

這個聯合索引，同時記錄了sex和age，並且排序的規則是，先按sex排，sex相同時按age排。那麼，通過"WHERE sex=‘男‘"，MySQL先對索引進行篩選，然後剩下的索引正好就是按照age排序的了。因此，整個SQL的排序速度依然很快。

[MySQL效能最佳化系列] 彙總索引