你做了一個明智的選擇
理解索引對開發和dba來說都是極其重要
差勁的索引對產品問題負相當大的一部分責任
索引不是多麼高深的問題
MySQL 索引一覽表
理解索引
為你的應用創建最佳索引
擁抱MySQL的限制
簡述索引
索引有什麼用
為從資料庫讀取資料加速
索引有什麼用
為從資料庫讀取資料加速
強制約束（唯一索引FOREIGN KEY)
沒有任何索引的情況下查詢頁能正常運行
但是那可能需要執行很長的時間
你可能聽說過的索引類型
BTREE索引– mysql中主要的索引類型
RTREE索引– 只有MyISAM支援, 用於GIS
HASH 索引– MEMORY, NDB 支援
BITMAP 索引– MySQL 不支援
FULLTEXT 索引– MyISAM, Innodb（MySQL 5.6以上。很多不同的實現
在可加速的操作中共享相同的屬性
內存相比硬盤使生活變得美好
B+樹通常用於硬盤存儲
數據存儲於葉子節點
B+Tree 示例
MyISAM、Innodb索引對比
MyISAM
資料指標指向資料檔案中的實體位置
所有索引都是一樣的（指向實體位置））
或Inj ) - 直接將資料儲存於索引的葉子節點，而不是指標二級索引– 保存主鍵索引的值作為資料指標BTREE索引能用於什麼操作?查詢所有KEY=5 的記錄(點查詢)查詢所有KEY>5 的記錄(開合間)查詢所有5避免讀取資料（只讀取索引）
其他專門的最佳化
使用索引進行查詢
SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith”
這是典型的索引KEY(LASTELAST_NAME=“Smith”
這是典型的索引KEY(LASTN
SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith” AND DEPT=“Accounting”
將會使用索引KEY(DEPT,LAST_NAME)
複合索引比較複雜
Index (A,B,C) - 欄位順序問題
下列情形將會使用索引進行查詢（全條件）
A>5
A=5 AND B>6
A=5 AND B=6 AND C=7
A=5 AND B IN (2,3) AND C>5
下列條件將不會使用索引
B>5 – 條件沒有B字段前的A
B=6 AND C=7 - 條件沒有B、C字段前的A
以下情形使用索引的一部分
A>5 AND B=2 - 第一個字段A的範圍查詢，導致只用上了索引中A字段的部分
A=5 AND B>6 AND C=2 - B字段的範圍範圍查詢，導致只使用了索引中A和B兩個欄位的部分
MySQL優化器的第一法則
在複合索引中，MySQL在遇到返回查詢(,BETWEEN)時，將停止中止剩餘部分（索引）的使用；但使用IN(… )的"範圍查詢"則可以繼續往右使用索引（的更多部分）
所用索引進行排序
SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10
將使用索引KEY(SCORE)
不使用索引將進行非常昂貴的“filesort”操作(externalsort)
常常使用組合索引進行查詢
SELECT * FROM PLAYERS WHERE COUNTRY=“US”ORDER BY SCORE DESC LIMIT 10
最佳選擇是KSCORECOUNT
高效排序的聯合索引
變得更加受限!
KEY(A,B)
以下情形將會使用索引進行排序
ORDER BY A - 對索引首字段進行排序
ORDER BY A - 對索引首字段進行排序 ORDER BY B - 對第一個字段進行點查詢，對第二個字段進行排序ORDER BY A DESC, B DESC - 對兩個字段進行相同的順序進行排序A>5 ORDER BY A - 對首字段進行範圍查詢，並對首字段進行排序以下情況將不使用索引進行排序ORDER BY B - 對第二個字段進行排序（未使用首字段）A>5 ORDER BY B –對首字段進行範圍查詢，對第二個字段進行排序A IN(1,2) ORDER BY B - 對首字段進行IN查詢，對第二個字段進行排序ORDER BY A ASC, B DESC - 對兩個欄位進行不同順序的排序MySQL使用索引排序的規則不能對兩個欄位進行不同順序的排序對非ORDER BY部分的欄位只能使用點查詢（=）– 在這種情形下，IN()也不行避免讀取資料（只讀取索引）「覆蓋索引」– 這裡指適用於特定查詢的索引，而不是一種索引的類型只讀取索引，而不去讀取資料SELECT STATUS FROM ORDERS WHERECUSTOMER_ID=123KEY(CUSTOMER_ID,STATUS)索引通常比資料本身要小（索引）是隨機的Min/Max的最佳化索引可以幫助最佳化MIN()/MAX() 這類的統計函數– 但只包含以下內容：SELECT MAX(ID) FROM TBL;SELECT MAX( SALARY) FROM EMPLOYEEGROUP BY DEPT_ID將受益於KEY(DEPT_ID,SALARY)“Using index for group-by”聯表查詢中索引的使用3聯表查詢SELECT * FROM POSTS,COMMENTS WHEREAUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID掃描表POSTS查詢所有複合條件的posts循環posts 在表每個COMMENTS post 使每個關聯的表（關聯字段）都使用上索引顯得非常的重要索引只有在被查詢的字段上是必要的– POSTS.ID字段的索引再本次查詢中是用不上的重新設計不能很好的所有索引的聯合查詢吧使用多索引MySQL可以使用超過1個索引「索引合併」SELECT * FROM TBL WHERE A=5 AND B=6– 可以分別使用索引KEY(A)和KEY(B)索引KEY(A,B) 是較好的選擇SELECT * FROM TBL WHERE A=5 OR B=6– 兩個索引同時分別被使用索引KEY(A,B) 在這個查詢中無法使用前綴索引你可以在欄位最左前綴建立索引ALTER TABLE TITLE ADD KEY(TITLE(20));需要對BLOB/XT類型的欄位建立索引能顯著的減少空間使用不能用於覆蓋索引選擇前綴長度成為一個問題選擇前綴長度字首應該有足夠的區分詞選擇前綴長度
前綴應該有足夠的區分詞
3 rows in set (27.49 sec)
使用最多的Title 前綴 mysql> select count(*) cnt, left(title,20) tl from title select count(*) cnt, left(title,20) tl from title by by tl order byc3;
3 rows in set (33.23 sec)
關鍵效能查詢集建立索引– 整體取審視他們，而不是一個個看
最好所有的查詢條件和聯表條件都使用索引– 起碼區分度最高的部分是
一般來說，可以的話，擴展索引，而不是創建新的索引
修改時記得驗證對性能的影響
索引策略示例
按能支持更多查詢的順序建立索引
SELECT * FROM TBL WHERE A=5 AND B=6
SELECT * FROM TBL WHERE A>5 AND B=6– 對兩個查詢來說KEY(B,A) 是更好的選擇
把所有都是點查詢的字段放到索引的首位
不要添加非效能關鍵查詢的索引– 太多的索引會讓MYSQL慢下來
Trick #1: 枚舉範圍
KEY (A,B)
SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5
將只使用索引的第一個欄位部分SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5索引的兩個欄位部分都使用Trick #2: 新增一個假的條件KEY (GENDER,CITY)SELECT * FROM PEOPLE WHERE CITY=“NEWYORK”完全用不上索引SELECT * FROM PEOPLE WHERE GENDER IN(M, GENDER（WE GENDER） YORK”將用上索引這個Trick在低區別度的字段上可以很好的使用Gender, Status, Boolean Types etcTrick #3: 虛實FilesortSELECT * FROM TBL WHERE A IN (1,2) ORDER BYB LIMIT 5;無法使用索引進行排序(SELECT FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALLSBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALLSELECT 2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;將會用上索引，而「filesort」只用於對不超過10行記錄
