mysql中的索引原理與表設計

標籤：

索引是有效使用資料庫的基礎，但你的資料量很小的時候，或許通過掃描整表來存取資料的效能還能接受，但當資料量極大時，當訪問量極大時，就一定需要通過索引的輔助才能有效地存取資料。一般索引建立的好壞是效能好壞的成功關鍵。

1.InnoDb資料與索引儲存細節

使用InnoDb作為資料引擎的Mysql和有叢集索引的SqlServer的資料存放區結構有點類似，雖然在物理層面，他們都儲存在Page上，但在邏輯上面，我們可以把資料分為三塊：資料區域，索引地區，主鍵地區，他們通過主鍵的值作為關聯，配合工作。預設配置下，一個Page的大小為16K。

一個表資料空間中的索引資料區域中有很多索引，每一個索引都是一顆B+Tree，在索引的B+Tree中索引的值作為B+Tree的節點的Key，資料主鍵作為節點的Value。

在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的分葉節點資料域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主鍵索引。這種索引也叫做叢集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表產生一個隱含欄位作為主鍵，這個欄位長度為6個位元組，類型為長整形。

 

表資料都以Row的形式放在一個一個大小為16K的Page中，在每個資料Page中有都頁頭資訊和一行一行的資料。其中頁頭資訊中主要放置的是這一頁資料中的所有主索引值和其對應的OFFSET，便於通過主鍵能迅速找到其對應的資料位元置。

               

2.索引最佳化檢索的原理

索引是資料庫的靈魂，如果沒有索引，資料庫也就是一堆文字檔，存在的意義並不大。索引能讓資料庫成幾何倍數地提高檢索效率。使用Innodb作為資料引擎的Mysql資料庫的索引分為叢集索引（也就是主鍵）和普通索引。上節我們已經講解了這兩種索引的儲存結構，現在我們仔細講解下索引是如何工作的。

叢集索引和普通索引都有可能由多個欄位組成，我們又稱這種索引為複合索引，1.2.3將為大家解析這種索引的效能情況.

 

2.1叢集索引

 

從上節我們知曉，Innodb的所有資料是按照叢集索引排序的，叢集索引這種儲存方式使得按主鍵的搜尋十分高效，如果我們SQL語句的選擇條件中有叢集索引，資料庫會優先使用叢集索引來進行檢索工作。

 

資料庫引擎根據條件中的主鍵的值，迅速在B+Tree中找到主鍵對應的分葉節點，然後把分葉節點所在的Page資料庫讀取到記憶體，返回給使用者，如綠色線條的流向。下面我們來運行一條SQL，從資料庫的執行情況分析一下：

 

select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;

 

 

我們可以看到，資料庫讀從磁碟取了兩個Page就把809Bytes的資料提取出來返回給用戶端。

 

下面我們試一下，如果試一下選取條件沒有包含主鍵和索引的情況：

 

 

select * from `UP_User` where bigPortrait = ‘5F29E883BFA8903B‘;

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25

  

 

可以看到如果使用主鍵作為檢索條件，檢索時間花了0.3ms，唯讀取了兩個Page，而不使用主鍵作為檢索條件，檢索時間花了2.8ms，讀取了25個Page，全域掃描才把這條記錄給找出來。這還是一個只有1000多行的表，如果更大的資料量，對比更加強烈。

 

對於這兩個Page，一個是主鍵B+Tree的資料，一個是10000094這條資料所在的資料頁。

 

2.2普通索引

 

我們用普通索引作為檢索條件來搜尋資料需要檢索兩遍索引：首先檢索普通索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。如紅色線條的流向。

 

下面我用一個例子來看看資料庫的表現：

 

select * from UP_User where userName = ‘fred‘;

# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4

 我們可以看到資料庫用了0.4ms來檢索這條資料，讀取了4個page，比用主鍵作為檢索條件多用了0.1ms，多讀取了兩個Page，而這兩個Page就是userName這個普通索引的B+Tree所在的資料頁。

 

2.3複合索引

 

叢集索引和普通索引都有可能由多個欄位組成，多個欄位組成的索引的分葉節點的Key由多個欄位的值按照順序拼接而成。這種索引的儲存結構是這樣的，首先按照第一個欄位建立一棵B+Tree，分葉節點的Key是第一個欄位的值，分葉節點的value又是一棵小的B+Tree，逐級遞減。對於這樣的索引，用排在第一的欄位作為檢索條件才有效提高檢索效率。排在後面的欄位只能在排在他前面的欄位都在檢索條件中的時候才能起輔助效果。下面我們用例子來說明這種情況。

 

我們在UP_User表上建立一個用來測試的複合索引，建立在 (`nickname`,`regTime`)兩個欄位上，下面我們測試下檢索效能：

 

 

select * from UP_User where nickName=‘fredlong‘; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4

 

 

 

我們看到索引起作用了，和普通索引的效果一樣都用了0.43ms，讀取了四個Page就完成了任務。

 

 

select * from UP_User where regTime = ‘2015-04-27 09:53:02‘; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No  Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26

 

 

從這次選擇的執行情況來看，雖然regTime在剛才建立的複合索引中，還是做了全域掃描。因為這個複合索引排在regTime欄位前面的nickname欄位沒有出現在選擇條件中，所以這個索引我們沒用用到。

那麼我們什麼情況下會用到複合索引呢。我一般在兩種情況下會用到：

1.  需要複合索引來排重的時候。
2.  用索引的第一個欄位選取出來的結果不夠精準，需要第二個欄位做進一步的效能最佳化。

 

我基本上沒有建立過三個以上的欄位做複合索引，如果出現這種情況，我覺的你的表設計可能出現了大而全的問題，需要在表設計層面調優，而不是通過增加複雜的索引調優。所有複雜的東西都是大機率有問題的。

3.批量選擇的效率

 

我們業務經常這樣的訴求，選擇某個使用者發送的所有訊息，這個文章所有回複內容，所有昨天註冊的使用者的UserId等。這樣的訴求需要從資料庫中取出一批資料，而不是一條資料，資料庫對這種請求的處理邏輯稍微複雜一點，下面我們來分析一下各種情況。

 

3.1根據主鍵批量檢索資料

 

我們有一張表PW_Like，專門儲存Feed的所有贊（Like），該表使用feedId和userId做聯合主鍵，其中feedId為排在第一位的欄位。該表一共有19個Page的資料。下面我們選取feedId為11593所有的贊：

 

 

select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084  Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2

 

 

我們花了0.47ms取出了58條資料，但一共唯讀取了2個Page的資料，其中一個Page還是這個表的主鍵。說明這58條資料都儲存在同一個Page中。這種情況，對於資料庫來說，是效率最高的情況。

 

3.2根據普通索引批量檢索資料

 

 

還是剛才那個表，我們除了主鍵以外，還在userId上建立了索引，因為有時候需要查詢某個使用者點過的贊。那麼我們來看看只通過索引，不通過主鍵來檢索批量資料時候，資料庫的效率情況。

 

 

select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062  Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No  Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15

 

 

我們可以看到的結果是，雖然我們只取出了27條資料，但是我們讀取了15個資料Page，花了2.8毫秒，雖然沒有進行全域掃描，但基本上也把一般的資料區塊讀取出來了。因為PW_Like中的資料由於是按照feedId物理排序的，所以這27條資料分別分布在13個Page中（有兩個Page是索引和主鍵），所以資料庫需要把這13個Page全部從磁碟中讀取出來，哪怕某一個Page（16K）上只有一條資料（15Bytes），也需要把這個資料Page讀出來才能取出所有的目標Row。

 

通過普通索引來檢索批量資料，效率明顯比通過主鍵來檢索要低得多。因為資料是分散的，所以需要分散地讀取資料Page進行拼接才能完成任務。但是索引對主鍵而言還是非常有必要的補充，比如上面這個例子，當使用者量達到100萬的時候，檢索某一個使用者點的所有的贊的成本也只是大概讀取15個Page，花2ms左右。

 

3.2檢索一段時間範圍內的資料

 

選取一定範圍內的資料是我們經常要遇到的問題。在海量資料的表中檢索一定範圍內的資料，很容易引起效能問題。我們遇到的最常見的需求有以下幾種：

1. 選取一段時間內註冊的使用者資訊
   這種時候，時間肯定不會是使用者表的主鍵，如果直接用時間作為選擇條件來檢索，效率會非常差，如何解決這種問題呢？我採取的辦法是，把註冊時間作為使用者表的索引，每次先把需要檢索的時間的兩端的userId都差出來，然後用這兩個userId做選擇條件做第三次查詢就是我們想要的資料了。這三次檢索我們只需要讀取大約10個Page就能解決問題。這種方法看起來很麻煩，但是是表的資料量達到億級的時候的唯一解決方案。
2. 選取一段時間內的日誌
   日誌表是我們最常見的表，如何設計好是經常聊到的話題。日誌表之所以不好設計是因為大家都希望用時間作為主鍵，這樣檢索一段時間內的日誌將非常方便。但是用時間作為主鍵有個非常大的弊端，當日誌插入速度很快的時候，就會出現因為主鍵重複而引起衝突。
   對於這種情況，我一般把日誌產生時間和一個自增的Id作為日誌表的聯合主鍵，把時間作為第一個欄位，這樣即避免了日誌插入過快引起的主鍵唯一性衝突，又能便捷地根據時間做檢索工作，非常方便。下面是這種日誌表的檢索的例子，大家可以看到效能非常好。還有就是日誌表最好是每天一個表，這樣能更便利地管理和檢索。
   
   select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05"; 
   # Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
   # Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
   # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
   # InnoDB_pages_distinct: 3 

 

4.批量選擇的效率

 

 

我們在批量檢索資料的時候，對選擇的結果在大多數情況下，都需要資料是排好序的。排序的效能也是日常需要注意到的，下面我們分三種情況來分析下資料庫是如何排序的。

 

對於ORDER BY 主鍵這種情況，資料庫是非常樂於見到的，基本上不會有額外效能的損耗，因為資料本來就是按照主鍵順序儲存的，取出來直接返回即可。

下面的所有關於排序的例子是在UP_MessageHistory表上做的實驗，這個表一共有195個page，35417行資料，主鍵建立在欄位id上，在sendUserId和destUserId上都建立了索引。

 

首先我們先做一個沒有排序的檢索：

 

 

select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 

 

 

 

然後我們在相同的條件下對id進行排序： 

 

 

select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125

 

 

 

從上面的資料可以看出，效能和沒有加order by差不多，基本沒有額外的效能損耗。接下來我們對索引進行排序：

 

 

select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125

 

 

 

接下來我們用一般字元串欄位做排序再看看：

 

 

select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125

 

 

 

然後我們再用普通的數字類型欄位排序看看情況：

 

 

Java代碼  

1. <strong>select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime;</strong>  
2. <strong># Query_time: 0.018522</strong>  Lock_time: 0.000107  Rows_sent: 3572  Rows_examined: 7144  Rows_affected: 0  
3. # Bytes_sent: 95709  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0  
4. # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No  
5. #   InnoDB_pages_distinct: 125  

 

 

針對以上的實驗結果，我們可以得出以下結論：

 

1. 針對主鍵做排序操作不會有效能損耗；
2. 針對不在選擇條件中的索引欄位做排序操作，索引不會起最佳化排序的作用；
3. 針對數實值型別欄位排序會比針對字串類型欄位排序的效率要高很多。

 

下面我們再研究下用選擇條件中的索引欄位排序，資料庫是否會最佳化排序演算法，我們任然用UP_User表來研究。

 

 

select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17

 

 

然後我們用選擇條件中的索引欄位做排序：

 

 

select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17

 

 

然後我們用選擇條件中的非索引數值欄位做排序：

 

 

select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17

 

 

從上面三條查詢語句的執行時間來分析，使用索引欄位排序和不排序花的時間差不多，比使用普通欄位排序花的時間少一些，因此我們可以得出第四條結論：

 

4.針對在選擇條件中的索引欄位做排序操作，索引會起最佳化排序的作用。

 

5.索引維護

 

在前面我們可以看到所有的主鍵和索引都是排好序的，那麼排序這件事情就需要銷號資源，每次有新的資料插入，或者老的資料的數值發生變更，排序就需要調整，這裡面是需要損耗效能的，下面我們分析一下。

自增型欄位作為主鍵時，資料庫對主鍵的維護成本非常低：

 

1. 每次新增加的值都是一個最大的值，追加到最後即可，其他資料不需要挪動；
2. 這種資料一般不做修改。

  

 

 

使用業務型欄位作為主鍵時，主鍵維護成本會比較高。每次產生的新資料都有可能需要挪動其他資料的位置。

 

 

 

因為innodb主鍵和資料是在放在一塊的，每次挪動主鍵，也需要挪動資料，維護的成本會比較搞，對於需要頻繁寫入的表，不建議使用業務欄位作為主鍵的。

 

由於主鍵是所有索引的分葉節點的值，也是資料排序的依據，如果主鍵的值被修改，那麼需要修改所有相關索引，並且需要修改整個主鍵B+Tree的排序，損耗會非常大。避免頻繁更新主鍵可以避免以上提到的問題。

 

update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11

 從上面的資料可以看出來，索然SQL語句只修改了一條資料，卻影響了11個Page。

 

相對於主鍵而已，索引就輕很多，它的分葉節點的值是主鍵，很輕，維護起來成本比較低。但也不建議為一個表建立過多索引。維護一個索引成本低，維護8個就不一定低了，這種事需要均衡地對待。

 

6.索引設計原則

索引其實是一把雙刃劍，用好了事半功倍，沒用好，事倍功半。

主鍵的欄位無特殊情況，一定要使用數實值型別的，排序時佔用計算資源少，儲存時佔用空間也少。若主鍵的欄位值很大，則整個資料表的各種索引也會變得沒有效率，因為所有的索引的分葉節點的值都是主鍵。

 

並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢最佳化的，當索引列有大量資料重複時，SQL查詢可能不會去利用索引，如一表中有欄位 sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

 

mysql中的索引原理與表設計