你做了一個明智的選擇
理解索引對開發和dba來說都是極其重要
差勁的索引對產品問題負相當大的一部分責任
索引不是多么高深的問題
MySQL 索引一覽表
理解索引
為你的應用創建最佳索引
擁抱MySQL的限制
簡述索引
索引有什么用
為從數據庫讀取數據加速
強制約束 (唯一索引 UNIQUE, 外鍵 FOREIGN KEY)
沒有任何索引的情況下查詢頁能正常運行
但是那可能需要執行很長的時間
你可能聽說過的索引類型
BTREE索引 – mysql中主要的索引類型
RTREE索引 – 只有MyISAM支持, 用于GIS
HASH 索引 – MEMORY, NDB 支持
BITMAP 索引 – MySQL 不支持
FULLTEXT 索引 – MyISAM, Innodb（MySQL 5.6以上支持）
類BTREE索引家族
有很多不同的實現
在可加速的操作中共享相同的屬性
內存相比硬盤使生活變得美好
B+樹通常用于硬盤存儲
數據存儲于葉子節點
B+Tree 示例
MyISAM、Innodb索引對比
MyISAM
數據指針指向數據文件中的物理位置
所有索引都是一樣的（指向物理位置））
Innodb
主鍵索引 (顯式或隱式) – 直接將數據存儲于索引的葉子節點，而不是指針
二級索引 – 保存主鍵索引的值作為數據指針
BTREE索引能用于什么操作 ?
查詢所有 KEY=5 的記錄 (點查詢)
查詢所有 KEY>5 的記錄 (開合間)
查詢所有 5
不適用于：查詢KEY最后一個數字等于0的所有記錄
因為這不能定義為范圍查詢操作
字符索引
這（和數值）沒什么區別… 真的
collation是為字符串定義的排序規則
如： “AAAA”
前綴LIKE 查詢是一種特殊的范圍查詢
LIKE “ABC%” 的意思是：
“ABC[最小值]”
LIKE “%ABC” 無法使用索引查詢
聯合索引
是這樣進行排序的， 比較首列，然后第二列，第三列以此類推，如：
KEY(col1,col2,col3)
(1,2,3)
使用一個BTREE索引，而不是每個層級一個單獨的BTREE索引
索引的開銷
索引是昂貴的，不要添加多余的索引
多數情況下，擴展索引比添加一個新的索引要好
寫 – 更新索引常常是數據庫寫操作的主要開銷
讀 – 需要再硬盤和內存開銷空間; 查詢優化中需要額外的開銷
索引成本的影響
長主鍵索引（Innodb）?– 使所有相應的二級索引 變得更長、更慢
“隨機”主鍵索引（Innodb）?– 插入導致大量的頁面分割
越長的索引通常越慢
Index with insertion in random order?– SHA1(‘password’)
低區分度的索引是低劣的?– 在性別字段建的索引
相關索引是不太昂貴的– insert_time與自增id是相關的
Innodb表的索引
數據按主鍵聚集
選擇最佳的字段作為主鍵
比如評論表 – (POST_ID,COMMENT_ID) 是作為主鍵的不錯選擇，使得單個post的評論聚在一起
或者 “打包” 單個 BIGINT（字段）
主鍵隱式地附加到所有索引中
KEY (A) 實質上是 KEY (A,ID)
覆蓋索引，有利于排序
MySQL是如何使用索引的
查詢
排序
避免讀取數據（只讀取索引）
其他專門的優化
使用索引進行查詢
SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith”
這是典型的索引 KEY(LAST_NAME)
可以使用復合索引
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
最佳選擇是 KEY(COUNTRY,SCORE)
高效排序的聯合索引
變得更加受限!
KEY(A,B)
以下情形將會使用索引進行排序
ORDER BY A – 對索引首字段進行排序
A=5 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=123
KEY(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”
聯表查詢中索引的使用
MySQL 使用 “嵌套循環（Nested Loops）”進行聯表查詢
SELECT * FROM POSTS,COMMENTS WHEREAUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID
掃描表POSTS查詢所有復合條件的 posts
循環posts 在表COMMENTS 中查找 每個post的所有comments
使每個關聯的表（關聯字段）都使用上索引顯得非常的重要
索引只有在被查詢的字段上是必要的– 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/TEXT類型的字段建立索引
能顯著的減少空間使用
不能用于覆蓋索引
選擇前綴長度成為一個問題
選擇前綴長度
前綴應該有足夠的區分度
比較distinct前綴、distinct整個字段的值
mysql> select count(distinct(title)) total,count(distinct(left(title,10))) p10,count(distinct(left(title,20))) p20 from title;
1 row in set (44.19 sec)
檢查異常值
確保不會有很多記錄使用相同的前綴
使用最多的Titlemysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;
3 rows in set (27.49 sec)
使用最多的Title 前綴?mysql> select count(*) cnt, left(title,20) tl from title group by tl order by cnt desc limit 3;
3 rows in set (33.23 sec)
MySQL如何選擇使用哪個索引的?
每次查詢動態選擇– 查詢文本中常量很重要
評估需要查詢的行數?對給定的索引，在表中進行”dive”
如果（dive）不可行時，使用 “Cardinality” 進行統計– 這是進行 ANALYZE TABLE時 更新的
更多關于索引的選擇
并不只是最小化掃描行數
很多其他的heuristics（嘗試） and hacks– 對Innodb來說主鍵是很重要的
覆蓋索引效益
Full table scan is faster, all being equal（這句不是太明白）
我們也可以使用索引進行排序
須知
驗證MYSQL實際使用的執行計劃
注意是可以根據常量和數據動態改變的
使用EXPLAIN
EXPLAIN 是一個很好的工具，可以看到MYSQL將如何進行查詢
mysql> explain select max(season_nr) from title group by production_year;
http://dev.mysql.com/doc/refm…
記住，真實的查詢可能跟執行計劃不同
1 row in set (0.01 sec)
MySQL Explain 101
“type” 從好到差排序如下：– system,const,eq_ref,ref,range,index,ALL
注意 “rows” – 更大的數值意味著更慢的查詢
檢查 “key_len” – 顯示索引的哪些部分真實使用到了
留意”Extra”
Using Index – 好
Using Filesort, Using Temporary – 差
索引策略
為你的關鍵性能查詢集建立索引– 整體取審視他們，而不是一個個看
最好所有的查詢條件和聯表條件都使用索引– 起碼區分度最高的部分是
一般來說，可以的話，擴展索引，而不是創建新的索引
修改時記得驗證對性能的影響
索引策略示例
按能支持更多查詢的順序建立索引
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”,”F”) AND CITY=“NEW YORK”
將用上索引
這個Trick在低區別度的字段上可以很好的使用
Gender, Status, Boolean Types etc
Trick #3: 虛實Filesort
KEY(A,B)
SELECT * FROM TBL WHERE A IN (1,2) ORDER BYB LIMIT 5;
無法使用索引進行排序
(SELECT?FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT?FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;
將會用上索引，而“filesort”只用于對不超過10行記錄
