Schema與數(shù)據(jù)類型優(yōu)化

1. 選擇數(shù)據(jù)類型原則

   • 更小的通常更好

     更小的數(shù)據(jù)類型通常更快，因為它們占用更少的磁盤、內(nèi)存和CPU緩存，并且處理時需要的CPU周期也更少。
     - 簡單就好
     > 簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。
     - 盡量避免NULL
     > 如果查詢中包含可為NULL的列，對MySQL來說更難優(yōu)化，因為可為NULL的列使得索引、索引統(tǒng)計和值比較都更復雜。
     > 如果已有字段為NULL，在不做問題的情況下，也沒有必要一定要將其轉(zhuǎn)為NOT NULL。
     > 如果計劃在列上建索引，應該盡量避免設計成可為NULL的列。

2. Schema設計的壞味道

   • 太多的列

     MySQL的存儲引擎API工作時需要在服務器層和存儲引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務器層將緩沖內(nèi)容解碼成各個列。從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)結構的操作代價非常高。
     - 太多的關聯(lián)
     > MySQL限制了每個關聯(lián)操作最多只能有61張表
     > 如果希望查詢執(zhí)行得快速且并發(fā)性好，單個查詢最好在12個表以內(nèi)做關聯(lián)。

3. 冗余、緩存與匯總
   冗余：反范式設計，添加冗余字段，防止關聯(lián)產(chǎn)生的性能消耗
   緩存：存儲可以比較簡單地從其他表中的schema獲取數(shù)據(jù)的表
   匯總：存儲使用GROUP BY等聚合語句聚合數(shù)據(jù)的表

索引優(yōu)化

索引（在MySQL中也叫做“鍵（key）”）是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結構。

只有當索引幫助存儲引擎快速查找到記錄帶來的好處大于其帶來的額外工作時，索引才是有效的。

1. 索引的類型

   • B-Tree（B+Tree）索引

   B-Tree索引意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同。
   * 哈希索引
   > 哈希所有基于哈希表實現(xiàn)，只有精確匹配索引所有列的查詢才有效。哈希索引只能用于等值比較。
   * 空間索引
   > MyISAM表支持空間索引，可以用作地理數(shù)據(jù)存儲。
   MySQL5.7，添加了InnoDB的GIS支持。
   * 全文索引
   > 在相同的列上同時創(chuàng)建全文索引和基于值的B-Tree索引不會有沖突，全文索引適用于MATCH AGAINST操作，而不是普通的WHERE條件操作。

2. 索引的優(yōu)點

   • 大大減少了服務器需要掃描的數(shù)據(jù)量
   • 幫助服務器避免排序和臨時表
   • 將隨機I/O變?yōu)轫樞騃/O

3. 高性能的索引策略

   • 獨立的列

   索引列不能是表達式的一部分，也不能是函數(shù)的參數(shù)
   * 前綴索引和索引選擇性
   > 索引的選擇性是指，不重復的索引值（也稱為基數(shù)，cardinality）和數(shù)據(jù)表的記錄總數(shù)（#T）的比值，范圍從1/#T到1之間。
   * 選擇合適的索引列順序
   > 正確的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要。

   當不需要考慮排序和分組時，將選擇性最高的列放在前面通常有效，但是這只使用于優(yōu)化WHERE條件的場景；不同場景對應不同的選擇，沒有一個通用的解決方案。

   • 聚簇索引

   聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。

   術語“聚簇”表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起。因為無法同時把數(shù)據(jù)行存放在兩個不同的地方，所以一個表只能有一個聚簇索引（一般就是主鍵索引）。

   • 覆蓋索引

   如果一個索引包含所有需要查詢的字段的值，那么就稱之為“覆蓋索引”

   當發(fā)起一個被索引覆蓋的查詢時，使用EXPLAIN語句可以看到Extra列對應的值為“Using index”。

   • 使用索引掃描來排序

   MySQL有兩種方式可以生成有序的結果：通過排序條件；或者按索引順序掃描。

   當MySQL使用索引順序掃描來排序，使用EXPLAIN語句可以看到type列對應的值為“index”

   • 索引和鎖

   InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的行數(shù)，從而減少鎖的數(shù)量。

   即使使用了索引，InnoDB也可能鎖住一些不需要的數(shù)據(jù)。如果不能使用索引查找和鎖定行的話可能會更糟，MySQL會做全表掃描并鎖住所有的行，而不管是不是需要。

   InnoDB在二級索引上使用共享（讀）鎖，但訪問主鍵索引使用排他（寫）鎖。這消除了使用覆蓋索引的可能性，并使得SELECT FOR UPDATE操作比LOCK IN SHARE MODE或非鎖定查詢要慢很多。