《High Performance MySQL》真是本經(jīng)典好書，從應(yīng)用層到數(shù)據(jù)庫到硬件平臺(tái)，各種調(diào)優(yōu)技巧、常見問題全都有所提及。數(shù)據(jù)庫的各種概念技巧平時(shí)都有接觸，像索引、分區(qū)、Sharding等等，但要想真正提高還是得如此系統(tǒng)學(xué)習(xí)一下。
Chapter 1: MySQL Architecture and History

---

1.1 Transaction Isolation Level
事務(wù)隔離級別真是個(gè)老生常談的問題的，但大多材料一提到臟讀、幻讀、重復(fù)讀就講得云里霧里，所以還是自己動(dòng)手實(shí)踐能體會(huì)最深。
1.2 Implicit and Explicit Locking
InnoDB默認(rèn)自動(dòng)根據(jù)事務(wù)隔離級別管理鎖，同時(shí)支持兩種標(biāo)準(zhǔn)SQL未提及的顯示鎖(Explicit Locking)：

• SELECT ... LOCK IN SHARE MODE
• SELECT ... FOR UPDATE
• LOCK/UNLOCK TABLES

InnoDB采用兩階段鎖協(xié)議(Two-phase Locking Protocol)。在事務(wù)內(nèi)任意時(shí)刻加鎖，最后提交或回滾時(shí)一起釋放所有鎖。兩階段鎖協(xié)議(跟分布式事務(wù)XA的兩階段提交)具體如下： “一次性鎖協(xié)議：事務(wù)開始時(shí)，一次性申請所有的鎖，之后不會(huì)再申請任何鎖。如果其中某個(gè)鎖不可用，則整個(gè)申請就不成功，事務(wù)就不會(huì)執(zhí)行，在事務(wù)尾端，一次性釋放所有的鎖。一次性鎖協(xié)議不會(huì)產(chǎn)生死鎖的問題，但事務(wù)的并發(fā)度不高。 “兩階段鎖協(xié)議：整個(gè)事務(wù)分為兩個(gè)階段，前一個(gè)階段為加鎖，后一個(gè)階段為解鎖。在加鎖階段，事務(wù)只能加鎖，也可以操作數(shù)據(jù)，但不能解鎖。直到事務(wù)釋放第一個(gè)鎖，就進(jìn)入解鎖階段，此過程中事務(wù)只能解鎖，也可以操作數(shù)據(jù)，不能再加鎖。 兩階段鎖協(xié)議使得事務(wù)具有較高的并發(fā)度，因?yàn)榻怄i不必發(fā)生在事務(wù)結(jié)尾。它的不足是沒有解決死鎖的問題，因?yàn)樗诩渔i階段沒有順序要求。如兩個(gè)事務(wù)分別申請了A, B鎖，接著又申請對方的鎖，此時(shí)進(jìn)入死鎖狀態(tài)。
1.3 Multiversion Concurrency Control
類似于樂觀鎖機(jī)制，但一些文章介紹到InnoDB實(shí)現(xiàn)不是純粹的MVCC。先標(biāo)注一下，回頭進(jìn)行深入源碼研究。 Innodb的實(shí)現(xiàn)真算不上MVCC，因?yàn)椴]有實(shí)現(xiàn)核心的多版本共存，undo log中的內(nèi)容只是串行化的結(jié)果，記錄了多個(gè)事務(wù)的過程，不屬于多版本共存。但理想的MVCC是難以實(shí)現(xiàn)的，當(dāng)事務(wù)僅修改一行記錄使用理想的MVCC模式是沒有問題的，可以通過比較版本號進(jìn)行回滾；但當(dāng)事務(wù)影響到多行數(shù)據(jù)時(shí)，理想的MVCC據(jù)無能為力了。
理想MVCC難以實(shí)現(xiàn)的根本原因在于企圖通過樂觀鎖代替二段提交。修改兩行數(shù)據(jù)，但為了保證其一致性，與修改兩個(gè)分布式系統(tǒng)中的數(shù)據(jù)并無區(qū)別，而二提交是目前這種場景保證一致性的唯一手段。二段提交的本質(zhì)是鎖定，樂觀鎖的本質(zhì)是消除鎖定，二者矛盾，故理想的MVCC難以真正在實(shí)際中被應(yīng)用，Innodb只是借了MVCC這個(gè)名字，提供了讀的非阻塞而已。

Chapter?4: Optimizing Schema and Data Types

---

4.1 Choosing Optimal Data Types
本章一上來就精辟的提出了關(guān)于模式和數(shù)據(jù)類型的總設(shè)計(jì)原則，那就是：

Smaller is usually better(越小通常越好)：因?yàn)檎加酶俅疟P空間，內(nèi)存以及CPU緩存，所以越小通常代表越快。

Simple is good(簡單的就是好的)：因?yàn)樽址团判蛞?guī)則(Collation)使得字符串的比較很復(fù)雜，所以我們應(yīng)當(dāng)用Integer等內(nèi)建類型而非字符串來保存日期時(shí)間或IP地址。

Avoid NULL if possible(盡可能避免NULL)：MySQL對NULL有特殊的處理邏輯，所以NULL會(huì)使索引、索引統(tǒng)計(jì)、值比較都變得更加復(fù)雜。

4.2 Using ENUM Instead Of A String Type
MySQL內(nèi)部將枚舉保存為整數(shù)，通過一張Lookup Table保存枚舉與整數(shù)的對應(yīng)關(guān)系。所以使用枚舉非常節(jié)省空間(原則1越小越好越快)，根據(jù)枚舉總個(gè)數(shù)而定，只會(huì)占用1或2個(gè)字節(jié)。 但是隨之而來的問題是：添加刪除枚舉值都要ALTER TABLE。并且使用Lookup Table進(jìn)行轉(zhuǎn)換時(shí)也會(huì)有開銷，尤其是與CHAR或VARCHAR類型的列做聯(lián)接時(shí)，但有時(shí)這種開銷可以被枚舉節(jié)省空間的優(yōu)勢所抵消。
4.3 Cons of A Normalized Schema
規(guī)范化范式(Normalized Schema)不僅增加JOIN數(shù)，并且會(huì)使本可以屬于一個(gè)索引的列分隔到不同的表中。 例如：SELECT ... FROM message INNER JOIN user USING(user_id) ? ? ?WHERE user.account_type = 'premium' ? ? ?ORDER BY message.published DESC LIMIT 10 則有兩種執(zhí)行計(jì)劃：

倒序走published索引掃描message表，每行都去user表檢查是否type為'premium'，直到找到10行。

走account_type索引掃描user表找到所有type為'premium'的行，進(jìn)行filesort后返回10行。

上面的問題本質(zhì)在于：JOIN使我們無法通過一個(gè)索引就同時(shí)完成排序和過濾。 改為非規(guī)范化 =>?SELECT .. FROM user_message? ? ? ?WHERE account_type = 'premium' ? ? ?ORDER BY published DESC LIMIT 10 則(account_type, published)上的索引能高效地完成任務(wù)！
4.4 Cache and Summary Tables
這一部分緊接上面關(guān)于Normalized和Denormalized Schema的Pros and Cons的討論，從4.4到4.6提出了幾種冗余數(shù)據(jù)的常用且實(shí)用的方法。這幾種技術(shù)本質(zhì)上都是為了加速查詢操作，但代價(jià)是拖慢了寫操作，并且會(huì)增加開發(fā)的復(fù)雜度。
緩存表(Cache Table)指那些包含能夠輕松從Schema中獲得的數(shù)據(jù)的表，即表中的數(shù)據(jù)是邏輯冗余(Logically Redundant)。匯總表(Summary/Roll-up Table)是說包含通過聚合函數(shù)得到的數(shù)據(jù)的表，例如表中數(shù)據(jù)是通過GROUP BY得到的。
為什么需要它們呢？最常見的場景就是報(bào)表等統(tǒng)計(jì)工作。生成這些統(tǒng)計(jì)數(shù)據(jù)要掃描大量數(shù)據(jù)，實(shí)時(shí)計(jì)算成本很高且很多時(shí)候沒有必要。而且查詢這些數(shù)據(jù)還要加大量組合索引(各種維度的)才能提高性能，然而這些索引又會(huì)對平時(shí)的更新和插入等操作造成影響。于是常用技術(shù)就是添加中間表到其他引擎(利用MyISAM更小的索引和全文檢索能力)，甚至其他系統(tǒng)(Lucene或Sphinx)。
有了中間表作為緩存，我們需要定期的更新或者重建它。影子表(Shadow Table)是一種不錯(cuò)的技術(shù)！ 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
物化視圖即預(yù)先計(jì)算并真正存儲(chǔ)在磁盤上的視圖(一般視圖是不會(huì)實(shí)際存儲(chǔ)，在訪問視圖時(shí)執(zhí)行對應(yīng)的SQL獲得數(shù)據(jù))。MySQL沒有物化視圖，但有一個(gè)很棒的開源實(shí)現(xiàn)Flexviews Tools。它有一些很有用的功能，例如：

CDC(Change Data Capture)工具能夠讀取日志(Binary Logs)，并提取對應(yīng)的行變化。

一組幫助定義和管理視圖的存儲(chǔ)過程

將改變反應(yīng)到物化視圖數(shù)據(jù)上的工具

具體來說，它利用基于行的日志(Row-based Binary Log)包含了變化行的前后數(shù)據(jù)，所以Flextviews能夠在無需訪問源表的情況下，知道變化前和變化后的數(shù)據(jù)，并重新計(jì)算物化視圖。這是它相比我們自己維護(hù)的Cache表或Summary表的優(yōu)勢。
4.6 Counter Tables
Web應(yīng)用一個(gè)常見問題就是并發(fā)訪問計(jì)數(shù)表，此書中提出方案來提高并發(fā)量。總體設(shè)計(jì)思路是：添加更多的槽來分散并發(fā)的訪問。與Java的Concurrent并發(fā)包中的ConcurrentHashMap的設(shè)計(jì)理念有些像。
計(jì)數(shù)表和對應(yīng)訪問SQL可以簡化如下： mysql>?CREATE TABLE hit_counter(cnt int unsigned not null) ENGINE=InnoDB; mysql>?UPDATE hit_counter SET cnt = cnt + 1;
可以看出，表中的一行計(jì)數(shù)器數(shù)據(jù)其實(shí)相當(dāng)于全局鎖，對它的更新將會(huì)被串行化。所以，首先建表時(shí)加入Slot一列。并初始化100條數(shù)據(jù)。 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：如果需要每天刷新計(jì)數(shù)器的話，那么建表時(shí)就加入時(shí)間列： 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：如果不想每天都插入初始數(shù)據(jù)的話，可以用下面的SQL： mysql>?INSERT INTO daily_hit_counter(day, slot, cnt)? ? ? ?VALUES(CURRENT_DATE, RAND() * 100, 1) ? ? ?ON DUPLICATE KEY UPDATE cnt = cnt + 1;
psss：如果想減少計(jì)數(shù)器的行數(shù)來節(jié)約空間，那么可以執(zhí)行一個(gè)定期任務(wù)來合并所有記錄到Slot 0：

Chapter 5:?Indexing for High Performance

---

5.1 B-Tree Family
一般我們討論數(shù)據(jù)庫索引時(shí)，其實(shí)指的都是B樹索引，MySQL的CREATE TABLE及其他語句中也的確使用這種說法。然而實(shí)際上，存儲(chǔ)引擎內(nèi)部可能會(huì)使用不同的存儲(chǔ)結(jié)構(gòu)。例如NDB使用T樹(關(guān)于不同的索引類型，T樹就非常適合內(nèi)存存儲(chǔ))，而InnoDB使用B+樹。
所以準(zhǔn)確地說我們使用的是B樹大家族里B樹的各種變形。各種變形的核心是圍繞著內(nèi)結(jié)點(diǎn)出度(例如基于內(nèi)存的T樹和基于磁盤的B樹)、存儲(chǔ)使用率(B樹和B+樹)等方面進(jìn)行的。
首先B樹與其他數(shù)據(jù)結(jié)構(gòu)如紅黑樹、普通AVL樹的最大區(qū)別就是：B樹的結(jié)點(diǎn)有很多個(gè)子結(jié)點(diǎn)。而這一點(diǎn)正是為減少磁盤I/O讀取開銷而設(shè)計(jì)。因?yàn)樽咏Y(jié)點(diǎn)很多，所以樹的總體高度很低，這樣就只需加載少量的磁盤頁就能查找到目標(biāo)數(shù)據(jù)。那關(guān)于B樹和B+樹的區(qū)別呢：B+樹的內(nèi)結(jié)點(diǎn)不存data(即指向key所在數(shù)據(jù)行的指針)，只存key。 B+樹的優(yōu)勢：

• 因?yàn)閮?nèi)部結(jié)點(diǎn)不存data了，所以在一個(gè)磁盤頁上能存更多的key了，樹的高度進(jìn)一步降低，從而加快key的查找命中。
• 需要全樹遍歷時(shí)(如某字段的范圍查詢甚至full scan，這都是很常見而頻繁的查詢操作)，只需要對B+樹的葉子結(jié)點(diǎn)進(jìn)行線性遍歷即可，而B樹則需要樹遍歷。而線性遍歷比樹遍歷命中率更高(因?yàn)橄噜彅?shù)據(jù)都很近，不會(huì)分散在結(jié)點(diǎn)的左右子樹中，跨頁的概率能低一些吧)

B樹的優(yōu)勢：

• 在B樹中查找可能在內(nèi)部結(jié)點(diǎn)結(jié)束，而B+樹則必須在葉子結(jié)點(diǎn)結(jié)束。

首先引用一個(gè)B樹查找的例子：
“下面，咱們來模擬下查找文件29的過程：

根據(jù)根結(jié)點(diǎn)指針找到文件目錄的根磁盤塊1，將其中的信息導(dǎo)入內(nèi)存。【磁盤IO操作 1次】 ? ?

此時(shí)內(nèi)存中有兩個(gè)文件名17、35和三個(gè)存儲(chǔ)其他磁盤頁面地址的數(shù)據(jù)。根據(jù)算法我們發(fā)現(xiàn)：17<29<35，因此我們找到指針p2。

根據(jù)p2指針，我們定位到磁盤塊3，并將其中的信息導(dǎo)入內(nèi)存。【磁盤IO操作 2次】 ? ?

此時(shí)內(nèi)存中有兩個(gè)文件名26，30和三個(gè)存儲(chǔ)其他磁盤頁面地址的數(shù)據(jù)。根據(jù)算法我們發(fā)現(xiàn)：26<29<30，因此我們找到指針p2。

根據(jù)p2指針，我們定位到磁盤塊8，并將其中的信息導(dǎo)入內(nèi)存。【磁盤IO操作 3次】 ? ?

此時(shí)內(nèi)存中有兩個(gè)文件名28，29。根據(jù)算法我們查找到文件名29，并定位了該文件內(nèi)存的磁盤地址。

分析上面的過程，發(fā)現(xiàn)需要3次磁盤IO操作和3次內(nèi)存查找操作。關(guān)于內(nèi)存中的文件名查找，由于是一個(gè)有序表結(jié)構(gòu)，可以利用折半查找提高效率。至于IO操作是影響整個(gè)B樹查找效率的決定因素。當(dāng)然，如果我們使用平衡二叉樹的磁盤存儲(chǔ)結(jié)構(gòu)來進(jìn)行查找，磁盤4次，最多5次，而且文件越多，B樹比平衡二叉樹所用的磁盤IO操作次數(shù)將越少，效率也越高。
而B+樹就是這個(gè)樣子： ?

總結(jié)

以上是生活随笔為你收集整理的《高性能MySQL》读书笔记的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。