商業女性用馬克筆書寫的複合形象
數據庫優化可以說是後臺開發中永恆的話題，數據庫的性能通常是整個服務吞吐量的瓶頸之所在。
1. 索引概述
InnoDB中的表都是按照主鍵順序組織存放的，這種組織方式稱之為索引組織表，對比於MyISAM的表組織方式。在InnoDB中每張表都必須有一個主鍵，如果在創建表的時候沒有顯式定義主鍵，則InnoDB首先會判斷表中是否有非空的唯一索引，如果有則將該列作為主鍵；否則InnoDB會自動創建一個6字節大小的指針作為主鍵。除主鍵之外，InnoDB還可以有輔助索引，而輔助索引頁中僅僅存放鍵值和指向數據頁的偏移量，而不像主鍵數據頁存儲的是一個完整的行記錄。
InnoDB存儲引擎中，所有的數據都被邏輯地存放在一個表空間中，表空間又被分為段(Segment)、區(Extent)、頁(Page)組成，其中段由存儲引擎自動管理，區的大小固定為1M，然後默認情況下頁的大小為16KB，也就是一個區總共有64個連續的頁組成。不過在MySQL5.6開始，頁的大小可以設置為4K、8K了，設置成4K除了可以提高磁盤的利用率之外，對於現代SSD硬盤將更加合適，不過這中更新比較的麻煩，需要將輸入導出後再重新導入，一般的備份恢復工具都是原樣複製數據，沒有辦法支持變更頁大小。
默認的B+樹索引其查找次數(效率)取決於B+樹的高度，生產環境下一般樹高為3~4層，即查詢一條記錄需要經過3~4個索引頁，而且B+樹索引並不能找到一個給定鍵值的具體行，其只能根據鍵和索引找到數據行所在的頁，然後數據庫把對應的頁讀取到內存，再在內存中執行查找，並最後得到需要查詢的數據。InnoDB還會監控對錶上各索引頁的查詢操作，如果觀察到通過建立hash索引可以帶來速度提升，則會根據訪問頻率和訪問模式自動為部分熱點頁建立hash索引，這個過程稱之為自適應哈希索引，而且該過程是人為無法干預、存儲引擎自動實現的。
使用索引的一大禁忌是不要在引用索引列的時候使用函數，比如max(id)、id+3>5等，或者隱式的數據類型轉換操作，這樣會導致索引失效導致全掃描。
2. 在線修改數據表
在MySQL 5.5之前修改表結構、或者創建新索引的時候，需要經過：先鎖定原始表，創建一張新的臨時表(臨時使用tmpdir路徑，確保有足夠空間可用)，然後把原表中的數據導入到新的臨時表中，接著刪除原表，最後再把臨時表重新命名為原來的表名。所以修改表結構需要注意，將對同一個表的ALTER TABLE多個操作合併到一條語句中，減少上述重複的步驟。同時，針對修改列名、修改數值類型的表示長度INT(3)->INT(10)、修改數據表註釋、向ENUM增加新的類型、修改數據表名這些操作不需要將數據表中的所有記錄都複製到臨時表。
新版MySQL支持Fast Index Creation，具體說來就是對於新輔助索引的創建，InnoDB會對要創建索引的表上一個S鎖，使該表以只讀的可用性提供服務，由於不需要重新創建表、拷貝數據，因而輔助索引的創建速度也快很多；刪除索引的時候InnoDB只需更新內部試圖標記輔助索引的空間為可用，同時刪除MySQL數據庫內部試圖上對應表的索引定義即可。
MySQL 5.6的版本支持Online DDL，允許在輔助索引創建的同時，還允許對錶同時執行諸如INSERT、UPDATE、DELETE等DML操作而不會被阻塞，其原理是在執行索引創建或者刪除操作的時候，將INSERT、UPDATE、DELETE這類的操作日誌先記錄到一個叫做“在線修改日誌”的內存空間中，當索引完成後再重新應用這些更新到表上，以此達到數據的一致性。不過“在線修改日誌“只存留在內存中，默認大小是128MB，如果修改表結構時候DML操作太多，會導致該空間不夠用而撤銷修改。
3. 創建索引
創建索引的時候講求一個Cardinality指標，該值表示索引中唯一值的估計數目，理想情況下該值除以錶行數應該儘可能接近1，否則表示該列選擇性太低而應該考慮刪除該索引。 對Cardinality的統計是使用採樣方式進行估算的，當表的修改數目超過總記錄的1/16、或者修改總次數超過20億次，則會隨機選擇8個數據頁重新統計該值，不過通過ANALYZE TABLE命令可以強制讓數據庫重新收集相關的統計信息。
實踐中OLTP和OLAP對索引的要求是有差異的，在OLTP應用中查詢操作通常只從數據庫返回很小部分數據集，此時根據查詢條件選擇高區分度的列來創建索引是很有意義的；對於OLAP應用通常都需要返回大批量的數據，很多情況下建立索引意義不是很大，因為大量數據返回的話往往全表順序掃描效率更高，不過OLAP中對時間創建索引是很常見的操作。
4. 覆蓋索引
表示直接從輔助索引中就可以得到需要的查詢記錄，而不需要再從聚簇索引中查詢行記錄。使用覆蓋索引的好處是輔助索引不包含整行記錄，所以索引大小會遠遠小於聚簇索引，單個索引頁就可以存儲更多的索引項，那麼訪問索引本身的操作就可以減少順序IO操作了。有些情況，比如在MySQL中SELECT COUNT(*) FROM t;優化器是可以選擇使用輔助索引來優化查詢速度的，因為可以訪問更少的索引頁就可以統計到查詢結果了。
如果SELECT列不能使用覆蓋索引完成，那麼除了在輔助索引上查到指定記錄後，還需要進行一次書籤訪問才能查找到整行中其他列的數據，並且此時的查找將是成本很高的隨機離散讀操作(相對於傳統機械磁盤)。
所以如果優化器覺得需要返回的數據量很少，則優化器還是可能會選擇使用輔助索引外加訪問聚簇索引的方式來返回記錄的；但是當訪問數據量佔整個表記錄中挺大一部分的時候(比如20%)，則優化器可能會選擇全表掃描的方式來查找數據，因為全表順序讀的代價可能比大量隨機讀的效率要高。大部分時候優化器都能做的不錯，不過當用戶有對索引的使用有足夠信心的時候還是可以影響優化器執行計劃的生成的，比如：可以使用USE INDEX的方式來提示優化器使用某個索引，不過實際上優化器還是會根據自己的判斷確定是否需要使用該索引；而通過FORCE INDEX則會強制選擇使用該索引；使用IGNORE INDEX會使優化器不能使用指定的索引，這通常可以誘導觸發執行全表掃描。
5. Multi-Range Read(MRR)優化
為了防止非覆蓋索引取數據的時候造成的大量隨機I/O，MyISAM和InnoDB會將查詢到的輔助索引存放在一個緩存中，然後將他們通過主鍵進行排序，並按排序後的主鍵進行順序書籤查找。通過這種方式可以將低效隨機訪問轉化為高效順序數據訪問，而且同一數據塊確保只需要被訪問一次，同時也減少緩衝池中頁被替換的次數，所以可以帶來查詢性能的極大提升。
MySQL5.6開始支持該項優化，使用的時候需要SET optimizer_switch=’mrr=on|off’的方式打開。MRR特性可以用於range、ref、eq_ref類型的查詢操作，當查詢使用到該特性的時候就可以在Extra看到Using MRR提示了，當在有表連接的情況下，如果連接鍵是被驅動表的主鍵的時候，也會先基於驅動表的連接鍵進行排序，按照這個順序就可以MRR按照被驅動表的主鍵訪問數據了。
從上面的介紹看到MRR是一個思路簡單但是卻很重要的優化，但是在某些情況下使用也可能會有負面效應。當表的數據量很小，大部分數據也都被緩存的時候，使用MRR不會帶來隨機訪問的收益，反而會因為額外的排序操作增加資源消耗；當限制只需要返回LIMIT n的時候，這種優化會讀取排序很多不需要的索引，性能反而會降低；排序使用的內存空間大小由mrr_buffer_size設定的，如果該內存較小但是待排序的索引數量大的時候，就需要使用磁盤輔助進行多塊排序歸併，這也會降低性能。
6. Index Condition Pushdown(ICP)優化
老舊數據庫版本只有索引可用的限制條件才會被傳輸到存儲引擎層，在新版本開啟ICP優化的時候，針對選用索引涉及到的數據列條件就都會被傳輸到存儲引擎層，所以在支持ICP特性後，存儲引擎在處理索引的同時就可以判斷是否可以通過下推的選擇條件對部分記錄直接進行過濾操作了。所以在老版本的數據庫，都是存儲引擎對索引可以直接使用的條件進行操作，然後再將這些數據傳遞給MySQL引擎，這樣就會涉及到大量數據條目的讀取、傳遞和篩選工作，這時候在Extra中肯定會看到Using where的提示，因為MySQL引擎對存儲引擎傳遞來的數據進行了篩選加工；現在將索引涉及到的篩選條件下推放到了存儲引擎層，就大大減少了上面的操作任務。
該功能可以使用SET optimizer_switch=’index_condition_pushdown=on|off’的方式打開或者關閉。ICP優化可以用於range、ref、req_ref、ref_or_null類型的查詢，當查詢使用到該特性的時候可以在Extra看到Using index condition。
7. 索引合併
當查詢WHERE中羅列有多個條件，他們都可以使用不同的索引進行優化查詢的時候，如果優化器發現某一個索引返回的記錄相比其他索引顯著的要少，那麼執行計劃就會選用這個索引；而如果優化器發現多個索引都不高效的時候，優化器會將這些查詢條件分離，用各自的索引分別獨立執行檢索，最後再將多個結果集合進行合併後返回。當然，這種情況優化器也可能使用全表掃面的方式處理。
本文完！
閱讀更多 程序猿猩球 的文章
