《高效能MySQL》讀書筆記(上)，高效能mysql
《High Performance MySQL》真是本經典好書，從應用程式層到資料庫到硬體平台，各種調優技巧、常見問題全都有所提及。資料庫的各種概念技巧平時都有接觸，像索引、分區、Sharding等等，但要想真正提高還是得如此系統學習一下。
Chapter 1: MySQL Architecture and History
1.1 Transaction Isolation Level
交易隔離等級真是個老生常談的問題的，但大多材料一提到髒讀、幻讀、重複讀就講得雲裡霧裡，所以還是自己動手實踐能體會最深。推薦文章：MySQL資料庫交易隔離等級。
1.2 Implicit and Explicit Locking
InnoDB預設自動根據交易隔離等級管理鎖，同時支援兩種標準SQL未提及的顯示鎖(Explicit Locking)：
- SELECT ... LOCK IN SHARE MODE
- SELECT ... FOR UPDATE
- LOCK/UNLOCK TABLES
InnoDB採用兩階段鎖協議(Two-phase Locking Protocol)。在事務內任意時刻加鎖，最後提交或復原時一起釋放所有鎖。兩階段鎖協議(跟分散式交易XA的兩階段交易認可)具體如下：“一次性鎖協議：事務開始時，一次性申請所有的鎖，之後不會再申請任何鎖。如果其中某個鎖不可用，則整個申請就不成功，事務就不會執行，在事務尾端，一次性釋放所有的鎖。一次性鎖協議不會產生死結的問題，但事務的並發度不高。“兩階段鎖協議：整個事務分為兩個階段，前一個階段為加鎖，後一個階段為解鎖。在加鎖階段，事務只能加鎖，也可以操作資料，但不能解鎖。直到事務釋放第一個鎖，就進入解鎖階段，此過程中事務只能解鎖，也可以操作資料，不能再加鎖。 兩階段鎖協議
使得事務具有較高的並發度，因為解鎖不必發生在事務結尾。它的
不足是沒有解決死結的問題，因為它在加鎖階段沒有順序要求。如兩個事務分別申請了A, B鎖，接著又申請對方的鎖，此時進入死結狀態。
1.3 Multiversion Concurrency Control
類似於樂觀鎖機制，但一些文章介紹到InnoDB實現不是純粹的MVCC。先標註一下，回頭進行深入源碼研究。收藏文章：何登成的InnoDB多版本(MVCC)實現簡要分析，老碼農的MySQL中的MVCC。“Innodb的實現真算不上MVCC，因為並沒有實現核心的多版本共存，undo log中的內容只是序列化的結果，記錄了多個事務的過程，不屬於多版本共存。但理想的MVCC是難以實現的，當事務僅修改一行記錄使用理想的MVCC模式是沒有問題的，可以通過比較版本號碼進行復原；但當事務影響到多行資料時，理想的MVCC據無能為力了。
“理想MVCC難以實現的根本原因在於企圖通過樂觀鎖代替二段提交。修改兩行資料，但為了保證其一致性，與修改兩個分布式系統中的資料並無區別，而二提交是目前這種情境保證一致性的唯一手段。二段提交的本質是鎖定，樂觀鎖的本質是消除鎖定，二者矛盾，故理想的MVCC難以真正在實際中被應用，
Innodb只是借了MVCC這個名字，提供了讀的非阻塞而已。
Chapter 4: Optimizing Schema and Data Types
4.1 Choosing Optimal Data Types
本章一上來就精闢的提出了關於模式和資料類型的總設計原則，那就是：
4.2 Using ENUM Instead Of A String Type
MySQL內部將枚舉儲存為整數，通過一張Lookup Table儲存枚舉與整數的對應關係。所以使用枚舉非常節省空間的(原則1越小越好越快)，根據枚舉總個數而定，只會佔用1或2個位元組。但是隨之而來的問題是：添加刪除枚舉值都要ALTER TABLE。並且使用Lookup Table進行轉換時也會有開銷，尤其是與CHAR或VARCHAR類型的列做聯結時，但有時這種開銷可以被枚舉節省空間的優勢所抵消。
4.3 Cons of A Normalized Schema
正常化範式(Normalized Schema)不僅增加JOIN數，並且會使本可以屬於一個索引的列分隔到不同的表中。例如：SELECT ... FROM message INNER JOIN user USING(user_id) WHERE user.account_type = 'premium' ORDER BY message.published DESC LIMIT 10則有兩種執行計畫：
上面的問題本質在於：
JOIN使我們無法通過一個索引就同時完成排序和過濾。改為非正常化 => SELECT .. FROM user_message WHERE account_type = 'premium' ORDER BY published DESC LIMIT 10則(account_type, published)上的索引能高效地完成任務！
4.4 Cache and Summary Tables
這一部分緊接上面關於Normalized和Denormalized Schema的Pros and Cons的討論，從4.4到4.6提出了幾種冗餘資料的常用且實用的方法。
這幾種技術本質上都是為了加速查詢操作，但代價是拖慢了寫操作，並且會增加開發的複雜度。
緩衝表(Cache Table)指那些包含能夠輕鬆從Schema中獲得的資料的表，即表中的資料是邏輯冗餘(Logically Redundant)。匯總表(Summary/Roll-up Table)是說包含通過彙總函式得到的資料的表，例如表中資料是通過GROUP BY得到的。
為什麼需要它們呢？最常見的情境就是報表等統計工作。產生這些統計資料要掃描大量資料，Realtime Compute成本很高且很多時候沒有必要。而且查詢這些資料還要加大量複合式索引(各種維度)才能提高效能，然而這些索引又會對平時的更新和插入等操作造成影響。於是常用技術就是添加中間表到其他引擎(利用MyISAM更小的索引和全文檢索索引能力)，甚至其他系統(Lucene或Sphinx)。
有了中間表作為緩衝，我們需要週期性更新或者重建它。影子表(Shadow Table)是一種不錯的技術！mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;mysql> CREATE TABLE my_summary_new LIKE my_summary;mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary只需一條rename操作，我們就可以原子地將影子表替換上去(swap with an atomic rename)，並且之前的表也保留下來以防需要復原。
4.5 Materialized Views
物化視圖即預先計算並真正儲存在磁碟上的視圖(一般視圖是不會實際儲存，在訪問視圖時執行對應的SQL獲得資料)。MySQL沒有物化視圖，但有一個很棒的開源實現Flexviews Tools。它有一些很有用的功能，例如：具體來說，它利用基於行的日誌(Row-based Binary Log)包含了變化行的前後資料，所以Flextviews能夠在無需訪問源表的情況下，知道變化前和變化後的資料，並重新計算物化視圖。這是它相比我們自己維護的Cache表或Summary表的優勢。
4.6 Counter Tables
Web應用一個常見問題就是並發訪問計數表，此書中提出方案來提高並發量。總體設計思路是：添加更多的槽來分散並發的訪問。與Java的Concurrent並發包中的ConcurrentHashMap的設計理念有些像。
計數表和對應訪問SQL可以簡化如下：mysql> CREATE TABLE hit_counter(cnt int unsigned not null) ENGINE=InnoDB;mysql> UPDATE hit_counter SET cnt = cnt + 1;
可以看出，表中的一行計數器資料其實相當於全域鎖，對它的更新將會被序列化。所以，首先建表時加入Slot一列。並初始化100條資料。CREATE TABLE hit_counter( slot tinyint unsigned not null primary key, cnt int unsigned not null) ENGINE=InnoDB;
之後將更新和查詢SQL改為：mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;mysql> SELECT SUM(cnt) FROM hit_counter;
ps：如果需要每天重新整理計數器的話，那麼建表時就加入時間列：CREATE TABLE daily_hit_counter( day date not null, slot tinyint unsigned not null primary key, cnt int unsigned not null, primary key(day, slot)) ENGINE=InnoDB;
pss：如果不想每天都插入初始資料的話，可以用下面的SQL：mysql> INSERT INTO daily_hit_counter(day, slot, cnt) VALUES(CURRENT_DATE, RAND() * 100, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;
psss：如果想減少計數器的行數來節約空間，那麼可以執行一個定期任務來合并所有記錄到Slot 0：
Chapter 5: Indexing for High Performance
5.1 B-Tree Family
一般我們討論資料庫索引時，其實指的都是B樹索引，MySQL的CREATE TABLE及其他語句中也的確使用這種說法。然而實際上，儲存引擎內部可能會使用不同的儲存結構。例如NDB使用T樹(關於不同的索引類型，在我的另一篇介紹記憶體資料庫中也有所提及。T樹就非常適合記憶體儲存)，而InnoDB使用B+樹。
所以準確地說我們使用的是B樹大家族裡B樹的各種變形。
各種變形的核心是圍繞著內結點出度(例如基於記憶體的T樹和基於磁碟的B樹)、儲存使用率(B樹和B+樹)等方面進行的。
首先B樹與其他資料結構如紅/黑樹狀結構、普通AVL樹的最大區別就是：B樹的結點有很多個子結點。而這一點正是為減少磁碟I/O讀取開銷而設計。因為子結點很多，所以樹的總體高度很低，這樣就只需載入少量的磁碟頁就能尋找到目標資料。那關於B樹和B+樹的區別呢：B+樹的內結點不存data(即指向key所在資料行的指標)，只存key。B+樹的優勢：
- 因為內部結點不存data了，所以在一個磁碟頁上能存更多的key了，樹的高度進一步降低，從而加快key的尋找命中。
- 需要全樹遍曆時(如某欄位的範圍查詢甚至full scan，這都是很常見而頻繁的查詢操作)，只需要對B+樹的葉子結點進行線性遍曆即可，而B樹則需要樹遍曆。而線性遍曆比樹遍曆命中率更高(因為相鄰資料都很近，不會分散在結點的左右子樹中，跨頁的機率能低一些吧)
B樹的優勢：
- 在B樹中尋找可能在內部結點結束，而B+樹則必須在葉子結點結束。
首先引用一個B樹尋找的例子：
“下面，咱們來類比下尋找檔案29的過程：
分析上面的過程，發現需要
3次磁碟IO操作和3次記憶體尋找操作。關於記憶體中的檔案名稱尋找，由於是一個有序表結構，可以利用折半尋找提高效率。至於IO操作是影響整個B樹尋找效率的決定因素。當然，如果我們使用平衡二叉樹的磁碟儲存結構來進行尋找，磁碟4次，最多5次，而且檔案越多，B樹比平衡二叉樹所用的磁碟IO操作次數將越少，效率也越高。
而B+樹就是這個樣子：
