[譯] MYSQL索引最佳實踐

xbdm3765 8年前發布 | 15K 次閱讀 MySQL 數據庫服務器

今日整理文檔時發現多年前的這個文檔還是蠻實用的，然后在網絡搜索了一下并沒有相關的譯文，所以決定把它翻譯過來，如有不當的地方請多包涵和指正。以下是內容：

你做了一個明智的選擇

  • 理解索引對開發和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<10 的記錄 (閉合間)

  • 不適用于：查詢KEY最后一個數字等于0的所有記錄

    – 因為這不能定義為范圍查詢操作

字符索引

  • 這（和數值）沒什么區別… 真的

    • collation是為字符串定義的排序規則

    • 如： “AAAA” < “AAAB”

  • 前綴LIKE 查詢是一種特殊的范圍查詢

    • LIKE “ABC%” 的意思是：

    • “ABC[最小值]”<KEY<“ABC[最大值]”

      – LIKE “%ABC” 無法使用索引查詢

聯合索引

  • 是這樣進行排序的， 比較首列，然后第二列，第三列以此類推，如：

    • KEY(col1,col2,col3)

    • (1,2,3) < (1,3,1)

  • 使用一個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 WHERE

    LAST_NAME=“Smith”

    • 這是典型的索引 KEY(LAST_NAME)

  • 可以使用復合索引

    • SELECT * FROM EMPLOYEES WHERE

      LAST_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 SCORE

    DESC LIMIT 10

    • 將使用索引 KEY(SCORE)

    • 不使用索引將進行非常昂貴的“filesort”操作(external

      sort)

  • 常常使用組合索引進行查詢

    • 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 WHERE

    CUSTOMER_ID=123

– KEY(CUSTOMER_ID,STATUS)

  • 索引通常比數據本身要小

  • （索引）讀取起來更有次序

    – 讀取數據指針通常是隨機的

Min/Max的優化

  • 索引可以幫助優化 MIN()/MAX() 這類的統計函數

    – 但只包含以下這些：

  • SELECT MAX(ID) FROM TBL;

  • SELECT MAX(SALARY) FROM EMPLOYEE

    GROUP BY DEPT_ID

    • 將受益于 KEY(DEPT_ID,SALARY)

    • “Using index for group-by”

聯表查詢中索引的使用

  • MySQL 使用 “嵌套循環（Nested Loops）”進行聯表查詢

    • SELECT * FROM POSTS,COMMENTS WHERE

      AUTHOR=“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;

total p10 p20
998335 624949 960894

1 row in set (44.19 sec)

  • 檢查異常值

    • 確保不會有很多記錄使用相同的前綴

使用最多的Title

mysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;

cnt tl
136 The Wedding
129 Lost and Found
112 Horror Marathon

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;

cnt tl
184 Wetten, dass..? aus
136 The Wedding
129 Lost and Found

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;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE title range NULL production_year 5 NULL 201 Using index for group-by

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 2

    AND 4 AND B=5

    • 將只使用索引的第一個字段部分

  • SELECT * FROM TBL WHERE A IN (2,3,4) AND

    B=5

    • 索引的兩個字段部分都使用

Trick #2: 添加一個假的條件

  • KEY (GENDER,CITY)

  • SELECT * FROM PEOPLE WHERE CITY=“NEW

    YORK”

    • 完全用不上索引

  • 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 BY

    B 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行記錄

 

 

來自：https://segmentfault.com/a/1190000007494097

 

 本文由用戶 xbdm3765 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！