1、索引原理與表設計1. 索引原理索引是有效使用數據庫的基礎，但你的數據量很小的時候，或許通過掃描整表來存取數據的性能還能接受，但當數據量極大時，當訪問量極大時，就一定需要通過索引的輔助才能有效地存取數據。一般索引建立的好壞是性能好壞的成功關鍵。1.1 InnoDb數據與索引存儲細節使用InnoDb作為數據引擎的Mysql和有聚集索引的SqlServer的數據存儲結構有點類似，雖然在物理層面，他們都存儲在Page上，但在邏輯上面，我們可以把數據分為三塊：數據區域，索引區域，主鍵區域，他們通過主鍵的值作為關聯，配合工作。默認配置下，一個Page的大小為16K。一個表數據空間中的索引數據區域中有很多索

2、引，每一個索引都是一顆B+Tree，在索引的B+Tree中索引的值作為B+Tree的節點的Key，數據主鍵作為節點的Value。在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點數據域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引?？梢钥吹饺~節點包含了完整的數據記錄。這種索引叫做聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為Inno

3、DB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。表數據都以Row的形式放在一個一個大小為16K的Page中，在每個數據Page中有都頁頭信息和一行一行的數據。其中頁頭信息中主要放置的是這一頁數據中的所有主鍵值和其對應的OFFSET，便于通過主鍵能迅速找到其對應的數據位置。1.2 索引優化檢索的原理索引是數據庫的靈魂，如果沒有索引，數據庫也就是一堆文本文件，存在的意義并不大。索引能讓數據庫成幾何倍數地提高檢索效率。使用Innodb作為數據引擎的Mysql數據庫的索引分為聚集索引（也就是主鍵）和普通索引。上節我們已經講解了這兩種索引的存儲結構，現在我們仔細講解下索引是如何工作

4、的。聚集索引和普通索引都有可能由多個字段組成，我們又稱這種索引為復合索引，1.2.3將為大家解析這種索引的性能情況。1.2.1 聚集索引從上節我們知曉，Innodb的所有數據是按照聚集索引排序的，聚集索引這種存儲方式使得按主鍵的搜索十分高效，如果我們SQL語句的選擇條件中有聚集索引，數據庫會優先使用聚集索引來進行檢索工作。數據庫引擎根據條件中的主鍵的值，迅速在B+Tree中找到主鍵對應的葉節點，然后把葉節點所在的Page數據庫讀取到內存，返回給用戶，如上圖綠色線條的流向。下面我們來運行一條SQL，從數據庫的執行情況分析一下：select * from UP_User where userId

5、= 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:

6、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: 2SET timestamp=1451104535;select * from UP_User where userId = 10000094;我們可以看到，數據庫讀從磁盤取了兩個Page就把809Bytes的數據提取出來返回給客戶端。下面我們試一下，如果試

7、一下選取條件沒有包含主鍵和索引的情況：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_t

8、able_on_disk: No# InnoDB_pages_distinct: 25可以看到如果使用主鍵作為檢索條件，檢索時間花了0.3ms，只讀取了兩個Page，而不使用主鍵作為檢索條件，檢索時間花了2.8ms，讀取了25個Page，全局掃描才把這條記錄給找出來。這還是一個只有1000多行的表，如果更大的數據量，對比更加強烈。對于這兩個Page，一個是主鍵B+Tree的數據，一個是10000094這條數據所在的數據頁。1.2.2 普通索引我們用普通索引作為檢索條件來搜索數據需要檢索兩遍索引：首先檢索普通索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。如上圖紅色線條的流向。下面我用一個例子來

9、看看數據庫的表現：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_page

10、s_distinct: 4我們可以看到數據庫用了0.4ms來檢索這條數據，讀取了4個page，比用主鍵作為檢索條件多用了0.1ms，多讀取了兩個Page，而這兩個Page就是userName這個普通索引的B+Tree所在的數據頁。1.2.3 復合索引聚集索引和普通索引都有可能由多個字段組成，多個字段組成的索引的葉節點的Key由多個字段的值按照順序拼接而成。這種索引的存儲結構是這樣的，首先按照第一個字段建立一棵B+Tree，葉節點的Key是第一個字段的值，葉節點的value又是一棵小的B+Tree，逐級遞減。對于這樣的索引，用排在第一的字段作為檢索條件才有效提高檢索效率。排在后面的字段只能在排在

11、他前面的字段都在檢索條件中的時候才能起輔助效果。下面我們用例子來說明這種情況。我們在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

12、_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_aff

13、ected: 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字段沒有出現在選擇條件中，所以這個索引我們沒用用到。那么我們什么情況下會用到復合索引呢。我一

14、般在兩種情況下會用到：1 需要復合索引來排重的時候。2 用索引的第一個字段選取出來的結果不夠精準，需要第二個字段做進一步的性能優化。我基本上沒有建立過三個以上的字段做復合索引，如果出現這種情況，我覺的你的表設計可能出現了大而全的問題，需要在表設計層面調優，而不是通過增加復雜的索引調優。所有復雜的東西都是大概率有問題的。1.3 批量選擇的效率我們業務經常這樣的訴求，選擇某個用戶發送的所有消息，這個帖子所有回復內容，所有昨天注冊的用戶的UserId等。這樣的訴求需要從數據庫中取出一批數據，而不是一條數據，數據庫對這種請求的處理邏輯稍微復雜一點，下面我們來分析一下各種情況。1.3.1 根據主鍵批量檢

15、索數據我們有一張表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

16、 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中。這種情況，對于數據庫來說，是效率最高的情況。1.3.2 根據普通索引批量檢索數據還是剛才那個表，我們除了主鍵以外，還在userId上建立了索引，因為有時候需要查詢某個用戶點過的贊。那么我們來看看只通過索引，不通

17、過主鍵來檢索批量數據時候，數據庫的效率情況。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

18、: No# InnoDB_pages_distinct: 15我們可以看到的結果是，雖然我們只取出了27條數據，但是我們讀取了15個數據Page，花了2.8毫秒，雖然沒有進行全局掃描，但基本上也把一般的數據塊讀取出來了。因為PW_Like中的數據由于是按照feedId物理排序的，所以這27條數據分別分布在13個Page中（有兩個Page是索引和主鍵），所以數據庫需要把這13個Page全部從磁盤中讀取出來，哪怕某一個Page（16K）上只有一條數據（15Bytes），也需要把這個數據Page讀出來才能取出所有的目標Row。通過普通索引來檢索批量數據，效率明顯比通過主鍵來檢索要低得多。因為數據是分

19、散的，所以需要分散地讀取數據Page進行拼接才能完成任務。但是索引對主鍵而言還是非常有必要的補充，比如上面這個例子，當用戶量達到100萬的時候，檢索某一個用戶點的所有的贊的成本也只是大概讀取15個Page，花2ms左右。1.3.3 選取一段時間范圍內的數據選取一定范圍內的數據是我們經常要遇到的問題。在海量數據的表中檢索一定范圍內的數據，很容易引起性能問題。我們遇到的最常見的需求有以下幾種：1 選取一段時間內注冊的用戶信息這種時候，時間肯定不會是用戶表的主鍵，如果直接用時間作為選擇條件來檢索，效率會非常差，如何解決這種問題呢？我采取的辦法是，把注冊時間作為用戶表的索引，每次先把需要檢索的時間的兩

20、端的userId都差出來，然后用這兩個userId做選擇條件做第三次查詢就是我們想要的數據了。這三次檢索我們只需要讀取大約10個Page就能解決問題。這種方法看起來很麻煩，但是是表的數據量達到億級的時候的唯一解決方案。2 選取一段時間內的日志日志表是我們最常見的表，如何設計好是經常聊到的話題。日志表之所以不好設計是因為大家都希望用時間作為主鍵，這樣檢索一段時間內的日志將非常方便。但是用時間作為主鍵有個非常大的弊端，當日志插入速度很快的時候，就會出現因為主鍵重復而引起沖突。對于這種情況，我一般把日志生成時間和一個自增的Id作為日志表的聯合主鍵，把時間作為第一個字段，這樣即避免了日志插入過快引起的

21、主鍵唯一性沖突，又能便捷地根據時間做檢索工作，非常方便。下面是這種日志表的檢索的例子，大家可以看到性能非常好。還有就是日志表最好是每天一個表，這樣能更便利地管理和檢索。select * from log_test where logTime 2015-12-27 11:53:05 and logTime 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

22、: 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_s

23、ent: 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_s

24、ent: 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 針對在選擇條件中的索引字

25、段做排序操作，索引會起優化排序的作用。1.5 索引維護在前面我們可以看到所有的主鍵和索引都是排好序的，那么排序這件事情就需要銷號資源，每次有新的數據插入，或者老的數據的數值發生變更，排序就需要調整，這里面是需要損耗性能的，下面我們分析一下。自增型字段作為主鍵時，數據庫對主鍵的維護成本非常低：1 每次新增加的值都是一個最大的值，追加到最后即可，其他數據不需要挪動；2 這種數據一般不做修改。使用業務型字段作為主鍵時，主鍵維護成本會比較高。每次生成的新數據都有可能需要挪動其他數據的位置。因為innodb主鍵和數據是在放在一塊的，每次挪動主鍵，也需要挪動數據，維護的成本會比較搞，對于需要頻繁寫入的表，

26、不建議使用業務字段作為主鍵的。由于主鍵是所有索引的葉節點的值，也是數據排序的依據，如果主鍵的值被修改，那么需要修改所有相關索引，并且需要修改整個主鍵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_dis

27、k_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個就不一定低了，這種事需要均衡地對待。1.6 設計索引原則索引其實是一把雙刃劍，用好了事半功倍，沒用好，事倍功半。主鍵

28、的字段無特殊情況，一定要使用數值類型的，排序時占用計算資源少，存儲時占用空間也少。若主鍵的字段值很大，則整個數據表的各種索引也會變得沒有效率，因為所有的索引的葉節點的值都是主鍵。并不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有字段 sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。2. 表設計原則從經歷過的大大小小的項目中提取出自己的數據表設計原則與大家分享，具有一定的個人風格，供大家參考。22.1 數據庫盡量作為數據倉庫數據庫盡量作為數據倉庫，業務邏輯在應用中實現，而

29、不是通過復雜的SQL語句或存儲過程實現業務邏輯。在數據庫中實現業務邏輯會增加業務應用與數據庫之間的耦合性，也會增加數據的壓力。數據庫應該只是個落地的倉庫，用的時候取出來，用完了放回去，而不是在倉庫里面對數據進行加工。對于事務控制這塊，數據庫中的邏輯處理有一定優勢，但我一般選擇利用redis的全局性來鎖定某條數據來完成事物控制。2.2 一張表對應且只對應一個邏輯主體一張表只負責落地一個業務邏輯，干凈利落。2.3 盡量避免字段中允許NULL值在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值。2.4 預留

30、一個flag字段作為擴展字段我習慣給一些有可能出現特定邏輯需求的表加上一個int32型flag字段，這樣很多bool邏輯判斷都可以放到這個這個flag字段的不同的位內來實現。比如我在CU_Sticker（表情）表內冗余了字段flag，之后在這個flag上增加了以下的零散邏輯判斷：RESENDENT(0x1),/是不是作為本地表情RECOMMAND(0x2),/是不是推薦的表情IWATCHAWARD(0x4);/用戶安裝iwatch版本之后，是不是贈送此表情2.5 盡量使用數字作為字段類型越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和CPU緩存中都需要更少的空間，處理起來更快。數據量多

31、的表，枚舉類型只允許存儲值，不允許存儲枚舉字符串。理論上，除了日志表，其他表的內容除了用戶產生的內容，表內數據應該都是一些數字，日期。3. SQL語句撰寫原則數據庫設計好了，如何合理地寫SQL語句去訪問數據庫也有許多需要注意的地方。33.1 不要對where后的字段做運算對Where后面字段做運算會讓索引失效，引發全表掃描。select * from UP_User where userId+1=10000095# Query_time: 0.002880 Lock_time: 0.000125 Rows_sent: 1 Rows_examined: 1876 Rows_affected: 0

32、# Bytes_sent: 818 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: 263.2 不要對where后的字段使用函數對Where后面字段使用函數會讓索引失效，引發全表掃描。select * from UP_User where SUBSTRING(userName , 1 , 1) = F# Query_time: 0.0071

33、03 Lock_time: 0.003494 Rows_sent: 73 Rows_examined: 1876 Rows_affected: 0# Bytes_sent: 6160 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這種情況我們可以用下面這種寫法替代：select * from UP_User where userNam

34、e like F%;# Query_time: 0.001333 Lock_time: 0.000149 Rows_sent: 73 Rows_examined: 73 Rows_affected: 0# Bytes_sent: 6284 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0# InnoDB_trx_id: F0AB# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 183.3 不要寫負向查詢盡量不要用not,not exists ， not in ， not like這些語句查詢數據。3.4 使用or運算一定要小心使用or運算一定要小心，如果有一個條件中沒有索引，那么可能前面索引一個都用不著，全表掃描。select * from UP_User where userName=fred or userId = 10000088 or nickname=viking# Query_time: 0.004272 Lock_time: 0.000127 Rows_sent: 3 Rows_examined: 1876 Rows