全書的重點(diǎn)在四五六章：如何建表费封、如何建索引拳氢、如何查詢缩搅。第一章講解了一些基本概念：鎖與事物隔離

重中之重：4.1數(shù)據(jù)類型5.3高性能索引特性6.4查詢過程6.7優(yōu)化查詢

在10，11猖腕，12章拆祈，涉及了一些分布式的知識，也頗為精彩倘感！包括集群的拓?fù)浞呕担?fù)載均衡、主從復(fù)制等

一老玛、MySQL架構(gòu)與歷史

A.并發(fā)控制

1.讀寫鎖

共享鎖（shared lock淤年，讀鎖）：共享的，相互不阻塞的

排他鎖（exclusive lock蜡豹，寫鎖）：排他的麸粮，一個寫鎖會阻塞其他的寫鎖和讀鎖

2.鎖粒度：表鎖、行鎖

B.事務(wù)（重點(diǎn)）

1.事務(wù)ACID

* 原子性（atomicity）一個事務(wù)必須被視為一個不可分割的最小工作單元镜廉，整個事務(wù)中所有操作要么全部提交成功弄诲，要么全部失敗回滾，對于一個事務(wù)來說娇唯，不可能只執(zhí)行其中的一部分操作

* 一致性（consistency）數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另外一個一致性的狀態(tài)

* 隔離性（isolation）一個事務(wù)所做的修改在最終提交以前齐遵，對其他事務(wù)是不可見的

* 持久性（durability）一旦事務(wù)提交寂玲，則其所做的修改就會永久保存到數(shù)據(jù)庫中

2.四種隔離級別

* READ UNCOMMITTED（未提交讀），事務(wù)中的修改梗摇，即使沒有提交拓哟，對其他事務(wù)也都是可見的，事務(wù)可以讀取未提交的數(shù)據(jù)留美，也被稱為臟讀（Dirty Read）彰檬，這個級別會導(dǎo)致很多問題

事務(wù)在讀數(shù)據(jù)的時(shí)候并未對數(shù)據(jù)加鎖。

事務(wù)在修改數(shù)據(jù)的時(shí)候只對數(shù)據(jù)增加行級共享鎖谎砾。

* READ COMMITTED（提交讀）逢倍，大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級別，一個事務(wù)開始時(shí)景图，只能“看見”已經(jīng)提交的事務(wù)所做的修改较雕，一個事務(wù)從開始直到提交之前，所做的任何修改對其他事務(wù)都是不可見的挚币，也叫不可重復(fù)讀（nonrepeatable read）亮蒋，有可能出現(xiàn)幻讀（Phantom Read），指的是當(dāng)某個事務(wù)在讀取某個范圍內(nèi)的記錄時(shí)妆毕，另外一個事務(wù)又在該范圍內(nèi)插入了新的記錄慎玖，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí)净蚤，會產(chǎn)生幻行（Phantom Row）

事務(wù)對當(dāng)前被讀取的數(shù)據(jù)加 行級共享鎖（當(dāng)讀到時(shí)才加鎖）阳惹，一旦讀完該行土全，立即釋放該行級共享鎖拆融；

事務(wù)在更新某數(shù)據(jù)的瞬間（就是發(fā)生更新的瞬間），必須先對其加 行級排他鎖伯襟，直到事務(wù)結(jié)束才釋放情连。

* REPEATABLE READ（可重復(fù)讀）迫淹，通過InnoDB和XtraDB存儲引擎示括，是MySQL的默認(rèn)事務(wù)隔離級別

事務(wù)在讀取某數(shù)據(jù)的瞬間（就是開始讀取的瞬間）铺浇，必須先對其加 行級共享鎖，直到事務(wù)結(jié)束才釋放垛膝；

事務(wù)在更新某數(shù)據(jù)的瞬間（就是發(fā)生更新的瞬間）鳍侣，必須先對其加 行級排他鎖，直到事務(wù)結(jié)束才釋放吼拥。

* SERIALIZABLE（可串行化）最高級別倚聚，通過強(qiáng)制事務(wù)串行執(zhí)行，避免了幻讀問題扔罪，會在每次操作都加表級共享鎖，可能導(dǎo)致大量的超時(shí)和鎖爭用的問題

事務(wù)在讀取數(shù)據(jù)時(shí)桶雀，必須先對其加 表級共享鎖 矿酵，直到事務(wù)結(jié)束才釋放唬复；

事務(wù)在更新數(shù)據(jù)時(shí)，必須先對其加 表級排他鎖 全肮，直到事務(wù)結(jié)束才釋放敞咧。

不可重復(fù)讀和幻讀的區(qū)別

不可重復(fù)讀重點(diǎn)在于update和delete，而幻讀的重點(diǎn)在于insert辜腺。

3.死鎖：指兩個或多個事務(wù)在同一資源上相互占用休建，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象

4.事務(wù)日志：存儲引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝评疗，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中测砂，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志持久以后百匆，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢地刷回到磁盤砌些，稱為預(yù)寫式日志（Write-Ahead Logging）

C.多版本并發(fā)控制

1.多版本并發(fā)控制（MVCC）是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作加匈，因此開銷更低存璃。雖然實(shí)現(xiàn)機(jī)制有所不同，但大都實(shí)現(xiàn)了非阻塞的讀操作雕拼，寫操作也只鎖定必要的行

2.MVCC的實(shí)現(xiàn)纵东，是通過保存數(shù)據(jù)在某個時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的，有樂觀和悲觀兩種啥寇，只在REPEATABLE READ和READ COMMITTED兩個隔離級別下工作

D.MySQL的存儲引擎

1.MySQL的.frm文件保存表的定義偎球，SHOW TABLE STATUS顯示表的相關(guān)信息

2.除非有非常特別的原因需要使用其他的存儲引擎，否則應(yīng)該優(yōu)先考慮InnoDB引擎

3.不要輕易相信MyISAM比InnoDB快之類的經(jīng)驗(yàn)之談示姿，這個結(jié)論并不是絕對的

二甜橱、MySQL基準(zhǔn)測試

三、服務(wù)器性能剖析

四栈戳、Schema與數(shù)據(jù)類型優(yōu)化（重點(diǎn)）

A.選擇優(yōu)化的數(shù)據(jù)類型

1.整數(shù)類型的選擇原則：

* 更小的通常更好岂傲。如幾個int類型的選擇

* 簡單就好。如int比string成本更低子檀，ip地址用int存而不是string

* 盡量避免NULL

2.應(yīng)該盡量只在對小數(shù)進(jìn)行精確計(jì)算時(shí)才使用DECIMAL镊掖，一般盡量用浮點(diǎn)數(shù)float，計(jì)算快存儲空間小褂痰。此外亩进，將小數(shù)轉(zhuǎn)化成int類型，通過程序控制單位（將小數(shù)乘以對應(yīng)的位數(shù)變成整數(shù)）

3.VARCHAR額外使用一到兩個字節(jié)記錄字符串長度缩歪。使用VARCHAR合適的情況：字符串列的最大長度比平均長度大很多归薛；列的更新很少，所以碎片不是問題；使用了像UTF-8這樣復(fù)雜的字符集主籍，每個字符都使用不同的字節(jié)數(shù)進(jìn)行存儲

4.CHAR適合存儲很短的字符串习贫，或者所有值都接近同一個長度，或經(jīng)常更新的字符串千元；不容易產(chǎn)生碎片苫昌，在存儲空間上更有效率

5.通常應(yīng)該盡量使用TIMESTAMP，它比DATETIME空間效率更高

6.BLOB和TEXT存儲文本信息幸海，B是二進(jìn)制存儲祟身，T是字符串。只對前n個字節(jié)做排序物独、索引

7.ENUM枚舉類型袜硫。ENUM其實(shí)是字符串-數(shù)字映射關(guān)系，用戶定義字符串议纯，實(shí)際存儲的整數(shù)而不是字符串父款，而且數(shù)據(jù)庫中排序也是按整數(shù)排序。ENUM類型的字符串列表是固定的瞻凤，任何增加或刪除字符串的必然導(dǎo)致ALTER TABLE.

將ENUM類型和string類型做關(guān)聯(lián)查詢的條件(WHERE...JOIN...ON...)：

ENUM關(guān)聯(lián)ENUM最快憨攒；VARCHAR關(guān)聯(lián)VARCHAR次之；VARCHAR關(guān)聯(lián)ENUM最慢阀参，因此要避免這種關(guān)聯(lián)方式

8.id標(biāo)識符適合用整數(shù)類型肝集；AUTO_INCREMENT

ENUM類型不適合，因?yàn)閰^(qū)分度不夠蛛壳；

STRING類型(VARCHAR,CHAR)占用空間太大杏瞻，盡量避免

B.MySQL schema設(shè)計(jì)中的陷阱

1.不好的設(shè)計(jì)：

* 太多的列

* 太多的關(guān)聯(lián)

* 全能的枚舉

* 變相的枚舉

* 非此發(fā)明（Not Invent Here）的NULL

C.范式和反范式

1.范式的優(yōu)點(diǎn)：

* 范式化的更新操作通常比反范式化要快

* 當(dāng)數(shù)據(jù)較好地范式化時(shí)，就只有很少或者沒有重復(fù)數(shù)據(jù)衙荐，所以只需要修改更少的數(shù)據(jù)

* 范式化的表通常更小捞挥，可以更好地放在內(nèi)存里，所以執(zhí)行操作會更快

* 很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時(shí)更少需要DISTINCT或者GROUP BY語句

2.范式化設(shè)計(jì)的缺點(diǎn)是通常需要關(guān)聯(lián)

3.反范式的優(yōu)點(diǎn)：避免關(guān)聯(lián)忧吟，避免了隨機(jī)I/O砌函，能使用更有效的索引策略

4.常用的是兩者混用。常見的反范式方法是復(fù)制（冗余）或緩存溜族，在兩個表里都存同一個字段讹俊，不過更新代價(jià)會變高

D.緩存表和匯總表

1.有時(shí)提升性能最好的方法是同一張表中保存衍生的冗余數(shù)據(jù)，有時(shí)也需要創(chuàng)建一張完全獨(dú)立的匯總表或緩存表

2.物化視圖煌抒，MySQL并不原生支持仍劈，F(xiàn)lexviews

3.如果應(yīng)用在表中保存計(jì)數(shù)器，則在更新計(jì)數(shù)器時(shí)可能踫到并發(fā)問題寡壮，創(chuàng)建一張獨(dú)立的表存儲計(jì)數(shù)器贩疙，可以幫助避免緩存失效

* 解決獨(dú)立表并發(fā)問題可以建多行讹弯，根據(jù)id隨機(jī)更新，然后統(tǒng)計(jì)時(shí)sum()

* 按天或小時(shí)可以單獨(dú)建行这溅，舊時(shí)間可定時(shí)任務(wù)合并到統(tǒng)一的一行

4.更新表中的計(jì)數(shù)器字段可能遇到并發(fā)問題闸婴，通常可以單獨(dú)見一個計(jì)數(shù)器表芍躏。要想加強(qiáng)并發(fā)，還可以將計(jì)數(shù)器存在多行中降狠，每次隨機(jī)更新一行对竣；統(tǒng)計(jì)結(jié)果，只要進(jìn)行聚合查詢即可

E.加快ALTER TABLE操作的速度

ALTER TABLE通常很慢榜配，是因?yàn)橛眯陆Y(jié)構(gòu)創(chuàng)建一個空表否纬，從舊表插入數(shù)據(jù)，在刪除舊表

兩種方式：

* 一是在一臺不提供服務(wù)的機(jī)器上執(zhí)行ALTER TABLE操作蛋褥，然后和提供服務(wù)的主庫進(jìn)行切換

* 二是通過“影子拷貝”临燃，創(chuàng)建一張新表，然后通過重命名和刪表操作交換兩張表及里面的數(shù)據(jù)

五烙心、創(chuàng)建高性能的索引

A.索引基礎(chǔ)

1.索引可以包含一個或多個列的值膜廊，如果索引包含多個列，那么列的順序也十分重要淫茵，因?yàn)镸ySQL只能高效地使用索引的最左前綴列

2.ORM工具能夠產(chǎn)生符合邏輯的爪瓜、合法的查詢，除非只是生成非吵妆瘢基本的查詢铆铆，否則它很難生成適合索引的查詢

3.在MySQL中，索引是在存儲引擎層而不是服務(wù)器層實(shí)現(xiàn)的丹喻，所以薄货，并沒有統(tǒng)一的索引標(biāo)準(zhǔn)：不同存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支持所有類型的索引

4.B-Tree意味著所有的值都是按順序存儲的碍论，并且每一個葉子頁到根的距離相同谅猾，能夠加快訪問數(shù)據(jù)的速度，從索引的根節(jié)點(diǎn)開始進(jìn)行搜索骑冗，適用于全鍵值赊瞬、鍵值范圍或鍵前綴查找（InnoDB采用B+樹）

葉子節(jié)點(diǎn)存放指向數(shù)據(jù)的指針，和指向下一個葉子頁的指針贼涩；非葉子節(jié)點(diǎn)存放指向下一級節(jié)點(diǎn)的指針巧涧，起到約束范圍的作用，但不存指向數(shù)據(jù)的指針遥倦；

頁的大小取決于操作系統(tǒng)谤绳，這也是為什么索引列的數(shù)據(jù)類型越小越好的原因占锯，因?yàn)樗饕翟叫。豁摯娣诺乃饕岛椭羔樉驮蕉嗨跎福淮蜪O（讀一頁）的索引過濾效果越好

5.B-Tree索引的限制：

* 如果不是按照索引的最左列開始查找消略，則無法使用索引

* 不能跳過索引中的列

* 如果查詢中有某個列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找

因此瞎抛，設(shè)計(jì)索引列的順序尤其重要

6.哈希索引（hash index）基于哈希表實(shí)現(xiàn)艺演，只有精確匹配索引所有列的查詢才有效，只有Memory引擎顯式支持哈希索引桐臊。不能用范圍查詢

7.哈希索引的限制：

* 哈希索引只包含哈希值和行指針胎撤，而不存儲字段值，所以不能使用索引中的值來避免讀取行

* 哈希索引數(shù)據(jù)并不是按照索引值順序存儲的断凶，所以也就無法用于排序

* 哈希索引也不支持部分索引列匹配查找伤提，因?yàn)楣Ｋ饕冀K是使用索引列的全部內(nèi)容來計(jì)算哈希值的

* 只支持等值比較查詢，不支持任何范圍查詢

* 訪問哈希索引的數(shù)據(jù)非橙纤福快肿男，除非有很多哈希沖突

* 如果哈希沖突很多的話，一些索引維護(hù)操作的代價(jià)也會很高

8.空間數(shù)據(jù)索引（R-Tree）却嗡，MyISAM表支持空間索引舶沛，可以用作地理數(shù)據(jù)存儲，開源數(shù)據(jù)庫系統(tǒng)中對GIS的解決方案做得比較好的是PostgreSQL的PostGIS

9.全文索引窗价，適用于MATCH AGAINST操作冠王，而不是普通的WHERE條件操作

B.索引的優(yōu)點(diǎn)

1.三個優(yōu)點(diǎn)：

* 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量

* 索引可以幫助服務(wù)器避免排序和臨時(shí)表

* 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O

2.索引三星系統(tǒng)：

* 索引將相關(guān)的記錄放到一起則獲得一星

* 如果索引中的數(shù)據(jù)順序和查找中的排序一致則獲得二星

* 如果索引中的列包含了查詢中需要的全部列則獲得三星

C.高性能的索引策略

1.獨(dú)立的列：如果查詢中的列不是獨(dú)立的，則MySQL不會使用索引舌镶。“獨(dú)立的列”是指索引列不能是表達(dá)式的一部分柱彻，也不能是函數(shù)的參數(shù)，不要參與計(jì)算

2.前綴索引和索引選擇性

* 對于字符串類型的索引餐胀，通秤纯可以索引開始的部分字符，可以大大節(jié)約索引空間否灾，但也會降低索引的選擇性

* 索引的選擇性是指卖擅，索引列的不同取值個數(shù)（也稱為基數(shù)，cardinality）和數(shù)據(jù)表的記錄總數(shù)（#T）的比值墨技，范圍從1/#T到1之間惩阶，選擇性越高則查詢效率越高，因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過濾掉更多的行扣汪。例如断楷，性別就不適合做索引

*前綴索引是指：較長的VARCHAR等烈性必須使用前若干個字符做索引。這個長度的選擇也要基于選擇性崭别，選擇合適的長度冬筒，使得前綴的選擇性接近完整列索引的選擇性

* MySQL無法使用前綴索引做ORDERY BY和GROUP BY恐锣，也無法做覆蓋掃描

3.適當(dāng)使用多列索引，而不是對每一列建立索引舞痰。當(dāng)where中經(jīng)常出現(xiàn)and查詢時(shí)土榴，時(shí)候多列索引；盡量避免or查詢响牛，or查詢myisam表能用到索引玷禽，且所有的or條件都必須是獨(dú)立索引，而 innodb不走索引呀打；盡量用in操作代替范圍查詢论衍，因?yàn)閺姆秶樵兞泻竺娴牧芯蜔o法使用索引

4.選擇合適的索引列順序

* 正確的索引列順序依賴于使用該索引的查詢，并且同時(shí)需要考慮如何更好地滿足排序和分組的需要

* 在一個多列B-Tree索引中聚磺，索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列

* 將選擇性最高的列放到索引最前列炬丸，但也要結(jié)合where中的組合條件瘫寝、范圍查詢等因素

5.聚簇索引（innodb）：并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲方式：將數(shù)據(jù)行和索引鍵值一起存在（B+樹的）葉子結(jié)點(diǎn)稠炬，而不是在葉子結(jié)點(diǎn)存放指向數(shù)據(jù)行的指針焕阿。innodb中聚簇索引就是主鍵，而且因?yàn)閿?shù)據(jù)只存一份首启，所以一個表只有一個聚簇索引暮屡；二級索引則需要兩次索引查找，因?yàn)槎壦饕~子結(jié)點(diǎn)中存放的是行的主鍵毅桃，而不是指向數(shù)據(jù)行的指針褒纲，根據(jù)主鍵再去走聚簇索引

* 缺點(diǎn)：寫入速度嚴(yán)重依賴于插入順序是否有序；更新慢钥飞，因?yàn)槊看胃乱苿訑?shù)據(jù)行數(shù)據(jù)莺掠。最好避免隨機(jī)的（不連續(xù)且值的分布范圍非常大）聚簇索引，特別是對于I/O密集型的應(yīng)用读宙。因此推薦用自增整數(shù)ID做主鍵彻秆，而類似于UUID等隨機(jī)數(shù)做主鍵則會十分糟糕

*優(yōu)點(diǎn)：讀數(shù)據(jù)更快，減少一次IO

6.覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值结闸，就稱為覆蓋索引唇兑。這樣可以避免一次回表查詢。innodb的主鍵必然在二級索引中桦锄，所以可以用來延遲關(guān)聯(lián)

應(yīng)用：延遲關(guān)聯(lián)扎附。在使用limit查詢的時(shí)候，如果要獲取的列又特別多结耀，會有大量IO的浪費(fèi)（limit舍棄）帕棉≌敕剩可以自關(guān)聯(lián)，把limit條件放在通過覆蓋索引獲取主鍵的過程中香伴，然后根據(jù)主鍵直接獲取需要的數(shù)據(jù)行

原sql： select * from t where a...b...c... limit 500,5

新sql：select * from t join(

? ? ? ? ? ? ? ? select id from t where a...b...c... limit 500,5) using id

7.如果EXPLAIN出來的type列的值為“index”慰枕，則說明MySQL使用了索引掃描來做排序

8.壓縮（前綴）索引，默認(rèn)只壓縮字符串即纲，減少索引大小具帮，對于CPU密集型應(yīng)用，因?yàn)閽呙栊枰S機(jī)查找低斋，壓縮索引在MyISAM上要慢好幾倍

9.重復(fù)索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引蜂厅，應(yīng)該避免這樣創(chuàng)建重復(fù)索引

10.索引可以讓查詢鎖定更少的行

D.維護(hù)索引和表

1.CHECK TABLE檢查表是否損壞，ALTER TABLE innodb_tb1 ENGINE=INNODB;修復(fù)表

2.records_in_range()通過向存儲引擎?zhèn)魅雰蓚€邊界值獲取在這個范圍大概有多少條記錄膊畴，對于innodb不精確

3.info()返回各種類型的數(shù)據(jù)掘猿，包括索引的基數(shù)

4.可以使用SHOW INDEX FROM命令來查看索引的基數(shù)

5.B-Tree索引可能會碎片化，這會降低查詢的效率

六唇跨、查詢性能優(yōu)化

A.為什么查詢速度會慢

B.慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問

1.是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

* 查詢不需要的記錄稠通，沒有使用limit

* 多表關(guān)聯(lián)并返回select*，全部列

* 總是取出全部列select*

* 重復(fù)查詢相同的數(shù)據(jù)买猖，應(yīng)該使用緩存

2.MySQL是否在掃描額外的記錄

* 查詢開銷三個指標(biāo)：響應(yīng)時(shí)間改橘、掃描的行數(shù)、返回的行數(shù)

* 響應(yīng)時(shí)間：服務(wù)時(shí)間和排隊(duì)時(shí)間之和玉控，“快速上限估計(jì)”法

* 掃描的行數(shù)：較短的行的訪問速度更快飞主，內(nèi)存中的行也比磁盤中的行的訪問 速度要快得多

* 訪問類型：EXPLAIN中的type列反應(yīng)了訪問類型；通過增加合適的索引高诺；

* 三種方式應(yīng)用WHERE條件：在索引中使用WHERE條件來過濾不匹配的記錄碌识；使用索引覆蓋掃描（Extra中出現(xiàn)Using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中結(jié)果虱而；從數(shù)據(jù)表中返回?cái)?shù)據(jù)丸冕，然后過濾不滿足條件的記錄（Extra中出現(xiàn)Using Where）

* 需要掃描大量數(shù)據(jù)但只返回少數(shù)的行的優(yōu)化技巧：使用索引覆蓋掃描，改變庫表結(jié)構(gòu)薛窥，重寫復(fù)雜的查詢

C.重構(gòu)查詢的方式

1.MySQL從設(shè)計(jì)上讓連接和斷開連接都很輕量級胖烛，損耗小草雕，可以考慮把多個復(fù)雜查詢改造成多個簡單查詢

2.切分查詢陋率，將大查詢切分成小查詢，將大的delete分成多次小的操作踱侣。可以避免鎖住很多數(shù)據(jù)罢杉、占滿事務(wù)日志趟畏、耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢

3.避免關(guān)聯(lián)查詢滩租，而在應(yīng)用程序中關(guān)聯(lián)（也就是關(guān)聯(lián)查詢的缺點(diǎn)）：

* 讓緩存的效率更高

* 將查詢分解后赋秀，執(zhí)行單個查詢可以減少鎖的競爭

* 在應(yīng)用層做關(guān)聯(lián)利朵，可以更容易對數(shù)據(jù)庫進(jìn)行拆分，更容易做到高性能和可擴(kuò)展

4.分解關(guān)聯(lián)查詢的場景：

* 當(dāng)應(yīng)用能夠方便地緩存單個查詢的結(jié)果的時(shí)候

* 當(dāng)可以將數(shù)據(jù)分布到不同的MySQL服務(wù)器上的時(shí)候

* 當(dāng)能夠使用IN()的方式代替關(guān)聯(lián)查詢的時(shí)候

* 當(dāng)查詢中使用同一個數(shù)據(jù)表的時(shí)候

D.查詢執(zhí)行的基礎(chǔ)（重點(diǎn)）

1.查詢過程：

* 客戶端發(fā)送一條查詢給服務(wù)器

* 服務(wù)器先檢查查詢緩存猎莲，如果命中則立刻返回绍弟，否則進(jìn)入下一階段

* 服務(wù)器端進(jìn)行SQL解析、預(yù)處理著洼，再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃

* MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃樟遣，調(diào)用存儲引擎的API來執(zhí)行查詢

* 將結(jié)果返回給客戶端

2.通信：

MySQL客戶端和服務(wù)器之間的通信協(xié)議是“半雙工”的。沒法進(jìn)行流量控制身笤，一旦一端開始發(fā)生消息豹悬，另一端要接收完整個消息才能響應(yīng)它。所以在執(zhí)行很大的搜索時(shí)液荸，需要參數(shù)限制瞻佛，如max_allowed_packet

3.服務(wù)器狀態(tài)（SHOW FULL PROCESSLIST命令查看）：

* Sleep，線程正在等待客戶端發(fā)送新的請求

* Query娇钱，線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端

* Locked伤柄，在MySQL服務(wù)器層，該線程正在等待表鎖

* Analyzing and statistics忍弛，線程正在收集存儲引擎的統(tǒng)計(jì)信息，并生成查詢的執(zhí)行計(jì)劃

* Copying to tmp table [on disk]考抄，線程正在執(zhí)行查詢细疚，并且將其結(jié)果集都復(fù)制到一個臨時(shí)表中，要么是在做GROUP BY操作川梅，要么是文件排序操作疯兼，或者是UNION操作

* Sorting result，線程正在對結(jié)果集進(jìn)行排序

* Sending data贫途，線程可能在多個狀態(tài)之間傳送數(shù)據(jù)吧彪，或者在生成結(jié)果集，或者在向客戶端返回?cái)?shù)據(jù)

4.語法解析器和預(yù)處理：

通過關(guān)鍵字將SQL語句進(jìn)行解析丢早，并生成一棵對應(yīng)的“解析樹”姨裸，解析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢，預(yù)處理器則根據(jù)一些MySQL規(guī)則進(jìn)一步檢查解析樹是否合法

5.查詢優(yōu)化器：

找到最好的執(zhí)行計(jì)劃怨酝，使用基本成本的優(yōu)化器傀缩，將嘗試預(yù)測一個查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個农猬，使用SHOW STATUS LIKE 'Last_query_cost';查看需要多少個數(shù)據(jù)頁的隨機(jī)查找赡艰。查詢優(yōu)化器并不一定每次都能正向優(yōu)化，當(dāng)特定數(shù)據(jù)的情況下

MySQL能處理的優(yōu)化類型：

* 重新定義關(guān)聯(lián)表的順序（最為關(guān)鍵）

* 將外鏈接轉(zhuǎn)化成內(nèi)鏈接

* 使用等價(jià)變換規(guī)則（如where中的條件）

* 優(yōu)化COUNT()斤葱、MIN()和MAX()慷垮，在EXPLAIN中可以看到“Select tables optimized away”揖闸，如把MIN轉(zhuǎn)化為查詢b樹索引最左端記錄，沒有where條件的select *會有引擎維護(hù)這個值

* 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式料身，當(dāng)檢測到一個表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候汤纸，就會一直把該表達(dá)式作為常數(shù)進(jìn)行優(yōu)化處理

* 覆蓋索引掃描，當(dāng)索引中的列包含所有查詢中需要使用的列的時(shí)候惯驼，就可以使用索引返回需要的數(shù)據(jù)蹲嚣，而無須查詢對應(yīng)的數(shù)據(jù)行

* 子查詢優(yōu)化

* 提前終止查詢，在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候祟牲，MySQL總是能夠立刻終止查詢

* 等值傳播隙畜，如果兩個列的值通過等式關(guān)聯(lián)，那么MySQL能夠把其中一個列的WHERE條件傳遞到另一列上

* 列表IN()的比較说贝，MySQL將IN()列表中的數(shù)據(jù)先進(jìn)行排序议惰，然后通過二分查找的方式來確定列表中的值是否滿足條件

9.對于UNION查詢，MySQL先將一系列的單個查詢結(jié)果放到一個臨時(shí)表中乡恕，然后再重新讀出臨時(shí)表數(shù)據(jù)來完成UNION查詢

10.關(guān)聯(lián)查詢和關(guān)聯(lián)查詢優(yōu)化:

MySQL對任何關(guān)聯(lián)都執(zhí)行“嵌套循環(huán)關(guān)聯(lián)”操作言询，即MySQL先在一個表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套到下一個表中尋找匹配的行傲宜，依次下去运杭，直到找到所有表中匹配的行為止。所以表關(guān)聯(lián)的順序很關(guān)鍵

全外連接就無法通過嵌套循環(huán)和回溯的方式完成函卒，當(dāng)發(fā)現(xiàn)關(guān)聯(lián)表中沒有找到任何匹配行的時(shí)候辆憔，則可能是因?yàn)殛P(guān)聯(lián)恰好從一個沒有任何匹配的表開始，MySQL不支持全外連接

.關(guān)聯(lián)查詢優(yōu)化器：主要是優(yōu)化關(guān)聯(lián)順序报嵌。如表一和表二關(guān)聯(lián)虱咧，先執(zhí)行表一，表一有100條匹配記錄锚国，再嵌套查表二腕巡；和先執(zhí)行表二，表二有10條匹配記錄血筑，再嵌套查表一绘沉，顯然方式二更好一些。會嘗試在所有的關(guān)聯(lián)順序中選擇一個成本最小的來生成執(zhí)行計(jì)劃樹豺总，如果可能梆砸，優(yōu)化器會遍歷每一個表然后逐個做嵌套循環(huán)計(jì)算每一棵可能的執(zhí)行樹的成本，最后返回一個最優(yōu)的執(zhí)行計(jì)劃

11.無論如何排序都是一個成本很高的操作园欣，所以從性能角度考慮帖世，應(yīng)盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進(jìn)行排序

E.MySQL查詢優(yōu)化器的局限性

1.子查詢：MySQL的子查詢實(shí)現(xiàn)得非常糟糕，最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句，使用inner join關(guān)聯(lián)日矫，或者使用EXISTS()來改寫赂弓。原因：mysql會把外層表壓到子查詢中去。例如

SELECT gid,COUNT(id)?

FROM shop_goods g1

WHERE status =0 and gid IN?

????(SELECT gid?

????FROM shop_goods g2?

????WHERE sid IN? (1519066,1466114))

GROUP BY gid;

用explain看了一下哪轿，出現(xiàn)關(guān)鍵字“DEPENDENT SUBQUERY”盈魁，意味著子查詢的第一個select依賴外部的查詢;換句話說，就是 子查詢對 g2 的查詢方式依賴于外層 g1 的查詢窃诉。它意味著兩步：

第一步杨耙，MySQL 根據(jù) select gid,count(id) from shop_goods where status=0 group by gid; 得到一個大結(jié)果集 t1，其數(shù)據(jù)量為rows=850672 了飘痛；

第二步珊膜，上面的大結(jié)果集 t1 中的每一條記錄，都將與子查詢 SQL 組成新的查詢語句：select gid from shop_goods where sid in (1519066,1466114) and gid=%t1.gid%宣脉。等于說车柠，子查詢要執(zhí)行85萬次。

但出于數(shù)據(jù)特殊性子查詢也不一定性能低于關(guān)聯(lián)查詢

2.UNION的限制：有時(shí)塑猖，MySQL無法將限制條件從外層“下推”到內(nèi)層竹祷，這使得原本能夠限制部分返回結(jié)果的條件無法應(yīng)用到內(nèi)層查詢的優(yōu)化上

3.MySQL無法利用多核特性來并行執(zhí)行查詢

4.對于MIN()和MAX()查詢，MySQL的優(yōu)化做得并不好

F.查詢優(yōu)化器的提示（hint）

1.HIGH_PRIORITY和LOW_PRIORITY羊苟，當(dāng)多個語句同時(shí)訪問某一個表的時(shí)候塑陵，哪些語句的優(yōu)先級相對高些、哪些語句的優(yōu)先級相對低些

2.DELAYED蜡励，對INSERT和REPLACE有效令花，會將使用該提示的語句立即返回給客戶端，并將插入的行數(shù)據(jù)放入到緩沖區(qū)巍虫，然后在表空閑時(shí)批量將數(shù)據(jù)寫入彭则，并不是所有的存儲引擎都支持鳍刷，并且該提示會導(dǎo)致函數(shù)LAST_INSERT_ID()無法正常工作

3.STRAIGHT_JOIN占遥，可以放置在SELECT語句的SELECT關(guān)鍵字之后，也可以放置在任何兩個關(guān)聯(lián)表的名字之間输瓜。第一個用法是讓查詢中所有的表按照在語句中出現(xiàn)的順序進(jìn)行關(guān)聯(lián)瓦胎，第二個用法則是固定其前后兩個表的關(guān)聯(lián)順序

4.SQL_SMALL_RESULT和SQL_BIG_RESULT，只對SELECT語句有效尤揣，它們告訴優(yōu)化器對GROUP BY或者DISTINCT查詢?nèi)绾问褂门R時(shí)表及排序

5.SQL_BUFFER_RESULT搔啊，告訴優(yōu)化器將查詢結(jié)果放入到一個臨時(shí)表，然后盡可能快地釋放表鎖

6.SQL_CACHE和SQL_NO_CACHE北戏，告訴MySQL這個結(jié)果集是否應(yīng)該緩存在查詢緩存中

7.SQL_CALC_FOUND_ROWS负芋，會計(jì)算除去LIMIT子句后這個查詢要返回的結(jié)果集的總數(shù)，而實(shí)際上只返回LIMIT要求的結(jié)果集嗜愈，可以通過函數(shù)FOUND_ROW()獲得這個值

8.FOR UPDATE和LOCK IN SHARE MODE旧蛾，主要控制SELECT語句的鎖機(jī)制莽龟，但只對實(shí)現(xiàn)了行級鎖的存儲引擎有效，僅InnoDB支持

9.USE INDEX锨天、IGNORE INDEX和FORCE INDEX毯盈，告訴優(yōu)化器使用或者不使用哪些索引來查詢記錄

10.MySQL5.0后新增的用來控制優(yōu)化器行為的參數(shù)：

* optimizer_search_depth，控制優(yōu)化器在窮舉執(zhí)行時(shí)的限度

* optimizer_prune_level病袄，讓優(yōu)化器會根據(jù)需要掃描的行數(shù)來決定是否跳過某些執(zhí)行計(jì)劃

* optimizer_switch搂赋，包含了一些開啟/關(guān)閉優(yōu)化器特性的標(biāo)志位

G.優(yōu)化特定類型的查詢（重點(diǎn)！Ｒ娌Ｄ缘臁）

1.優(yōu)化COUNT()查詢

* COUNT()是一個特殊的函數(shù)，有兩種非常不同的作用：可以統(tǒng)計(jì)某個列值的數(shù)量左刽，也可以統(tǒng)計(jì)行數(shù)捺信，在統(tǒng)計(jì)列值時(shí)要求列值是非空的（不統(tǒng)計(jì)NULL）

* COUNT(*)并不是會像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上欠痴，它會忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)迄靠，當(dāng)MySQL確認(rèn)括號內(nèi)的表達(dá)值不可能為空時(shí)，實(shí)際上就是在統(tǒng)計(jì)行數(shù)

* 使用匯總表喇辽、外部緩存系統(tǒng)來實(shí)現(xiàn)count的作用

2.優(yōu)化關(guān)聯(lián)查詢

* 確保ON或者USING子句中的列上有索引掌挚，具體來說，一定要再關(guān)聯(lián)的后一張表上的關(guān)聯(lián)列加索引菩咨。因?yàn)?.4節(jié)mysql的關(guān)聯(lián)是嵌套循環(huán)吠式，后一張表在內(nèi)層，每次都會用到關(guān)聯(lián)列作為過濾條件

* 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個表中的列

* 當(dāng)升級MySQL的時(shí)候需要注意：關(guān)聯(lián)語法抽米、運(yùn)算符優(yōu)先級等其他可能會發(fā)生變化的地方

3.優(yōu)化子查詢：盡可能使用關(guān)聯(lián)查詢代替特占，如果使用MySQL5.6以上或MariaDB則可以忽略這個建議

4.優(yōu)化GROUP BY和DISTINCT

* 使用索引優(yōu)化

* 當(dāng)無法使用索引時(shí)，GROUP BY使用兩種策略來完成：使用臨時(shí)表或者文件排序來做分組

* 盡可能的將WITH ROLLUP（超級聚合）功能移動應(yīng)用程序中處理

5.優(yōu)化LIMIT分頁

* 最簡單的辦法是盡可能地使用索引覆蓋掃描云茸，而不是查詢所有的列是目，然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列（延遲關(guān)聯(lián)）

select id,name,…… from table inner join (select id from table order by xxx limit 5000,5) as table1 USING(id);

* offset會導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉，如果可以記錄上次取數(shù)據(jù)的位置标捺，下次就可以直接從該記錄的位置開始掃描懊纳，可以避免使用offset

* 使用預(yù)先計(jì)算的匯總表，或者關(guān)聯(lián)到一個冗余表

6.優(yōu)化UNION查詢

* 通過創(chuàng)建并填充臨時(shí)表的方式來執(zhí)行UNION查詢亡容，因此很多優(yōu)化策略在UNION查詢中都沒法很好地使用嗤疯，經(jīng)常需要手工地將WHERE、LIMIT闺兢、ORDER BY等子句下推到UNION的各個子查詢中

* 除非確實(shí)需要服務(wù)器消除重復(fù)的行茂缚，否則就一定要使用UNION ALL

七、MySQL高級特性

A.分區(qū)表

1.對用戶來說，分區(qū)表是一個獨(dú)立的邏輯表脚囊，但是底層由多個物理子表組成帖汞，實(shí)際上是對一組底層表的句柄對象（Handler Object）的封裝

2.適用場景：

* 表非常大以至于無法全部都放在內(nèi)存中，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)凑术，其他均是歷史數(shù)據(jù)

* 分區(qū)表的數(shù)據(jù)更容易維護(hù)

* 分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上翩蘸，從而高效地利用多個硬件設(shè)備

* 可以使用分區(qū)表來避免某些特殊的瓶頸

* 如果需要，還可以備份和恢復(fù)獨(dú)立的分區(qū)

3.使用限制：

* 一個表最多只能有1024個分區(qū)

* 在MySQL5.1中淮逊，分區(qū)表達(dá)式必須是整數(shù)催首，或者是返回整數(shù)的表達(dá)式。在MySQL5.5中泄鹏，某些場景中可以直接使用列來進(jìn)行分區(qū)

* 如果分區(qū)字段中有主鍵或者唯一索引的列郎任，那么所有主鍵列和唯一索引列都必須包含進(jìn)來

* 分區(qū)表中無法使用外鍵約束

4.使用分區(qū)表

* 在數(shù)據(jù)量超大的時(shí)候，B-Tree索引就無法起作用了备籽，除非是索引覆蓋查詢舶治，否則數(shù)據(jù)庫服務(wù)器需要根據(jù)索引掃描的結(jié)果回表，查詢所有符合條件的記錄车猬，如果數(shù)據(jù)量巨大霉猛，將產(chǎn)生大量隨機(jī)I/O

5.保證大數(shù)據(jù)量的可擴(kuò)展性兩個策略：

* 命題掃描數(shù)據(jù)，不要任何索引

* 索引數(shù)據(jù)珠闰，并分離熱點(diǎn)

6.分區(qū)策略的問題：

* NULL值會使分區(qū)過濾無效

* 分區(qū)列和索引列不匹配

* 選擇分區(qū)的成本可能很高

* 打開并鎖住所有底層表的成本可能很高

* 維護(hù)分區(qū)的成本可能很高

* 所有分區(qū)都必須使用相同的存儲引擎

* 分區(qū)函數(shù)中可以使用的函數(shù)和表達(dá)式也有一些限制

* 某些存儲引擎不支持分區(qū)

* 對于MyISAM的分區(qū)表惜浅，不能再使用LOAD INDEX INTO CACHE操作

* 對于MyISAM表，使用分區(qū)表時(shí)需要打開更多多的文件描述符

7.查詢優(yōu)化

* 很重要的一點(diǎn)是要在WHERE條件中帶入分區(qū)列

* 只能在使用分區(qū)函數(shù)的列本身進(jìn)行比較時(shí)才能過濾分區(qū)伏嗜，而不能根據(jù)表達(dá)式的值去過濾分區(qū)坛悉，即使這個表達(dá)式是分區(qū)函數(shù)也不行

B.視圖

1.視圖本身是一個虛擬表，不存放任何數(shù)據(jù)承绸，返回的數(shù)據(jù)是MySQL從其他表中生成的

2.MySQL使用兩種算法：合并算法（MERGE）和臨時(shí)表算法（TEMPTABLE）裸影，會盡可能地使用合并算法

3.如果視圖中包含GROUP BY、DISTINCT军熏、任何聚合函數(shù)轩猩、UNION、子查詢等羞迷，只要無法在原表記錄和視圖記錄中建立一一映射的場景中界轩，MySQL都將使用臨時(shí)表算法來實(shí)現(xiàn)視圖

4.可更新視圖（updatable view）是指可以通過更新這個視圖來更新視圖涉及的相關(guān)表画饥，CHECK OPTION表示任何通過視圖更新的行衔瓮，都必須符合視圖本身的WHERE條件定義

5.在重構(gòu)schema的時(shí)候可以使用視圖，使得在修改視圖底層表結(jié)構(gòu)的時(shí)候抖甘，應(yīng)用代碼還可能繼續(xù)不報(bào)錯運(yùn)行

6.MySQL中不支持物化視圖（指將視圖結(jié)果數(shù)據(jù)存放在一個可以查看的表中热鞍，并定期從原始表中刷新數(shù)據(jù)到這個表中）

7.不會保存視圖定義的原始SQL語句

C.外鍵約束

1.使用外鍵是有成本的，通常要求每次在修改數(shù)據(jù)時(shí)都要在另外一張表中多執(zhí)行一次查找操作

2.如果想確保兩個相關(guān)表始終有一致的數(shù)據(jù)，那么使用外鍵比在應(yīng)用程序中檢查一致性的性能要高得多薇宠，在相關(guān)數(shù)據(jù)的刪除和更新上偷办，也比在應(yīng)用中維護(hù)要更高效

3.外鍵會帶來很大的額外消耗

D.在MySQL內(nèi)部存儲代碼

1.MySQL允許通過觸發(fā)器、存儲過程澄港、函數(shù)的形式來存儲代碼椒涯，從5.1開始還可以在定時(shí)任務(wù)中存放代碼，這個定時(shí)任務(wù)也被稱為“事件”回梧。存儲過程和存儲函數(shù)都被統(tǒng)稱為“存儲程序”

2.存儲代碼的優(yōu)點(diǎn)：

* 它在服務(wù)器內(nèi)部執(zhí)行废岂，離數(shù)據(jù)最近，另外在服務(wù)器上執(zhí)行還可以節(jié)省帶寬和網(wǎng)絡(luò)延遲

* 這是一種代碼重用狱意，可以方便地統(tǒng)一業(yè)務(wù)規(guī)則湖苞，保證某些行為總是一致，所以也可以為應(yīng)用提供一定的安全性

* 它可以簡化代碼的維護(hù)和版本更新

* 可以幫助提升安全详囤，比如提供更細(xì)粒度的權(quán)限控制

* 服務(wù)器端可以緩存存儲過程的執(zhí)行計(jì)劃财骨，這對于需要反復(fù)調(diào)用的過程，會大大降低消耗

* 因?yàn)槭窃诜?wù)器端部署的藏姐，所以備份隆箩、維護(hù)都可以在服務(wù)器端完成

* 可以在應(yīng)用開發(fā)和數(shù)據(jù)庫開發(fā)人員之間更好地分工

3.存儲代碼的缺點(diǎn)：

* MySQL本身沒有提供好用的開發(fā)和調(diào)試工具

* 較之應(yīng)用程序的代碼，存儲代碼效率要稍微差些

* 存儲代碼可能會給應(yīng)用程序代碼的部署帶來額外的復(fù)雜性

* 因?yàn)榇鎯Τ绦蚨疾渴鹪诜?wù)器內(nèi)羔杨，所以可能有安全隱患

* 存儲過程會給數(shù)據(jù)庫服務(wù)器增加額外的壓力摘仅，而數(shù)據(jù)庫服務(wù)器的擴(kuò)展性相比應(yīng)用服務(wù)器要差很多

* MySQL并沒有什么選項(xiàng)可以控制存儲程序的資源消耗，所以在存儲過程中的一個小錯誤问畅，可能直接把服務(wù)器拖死

* 存儲代碼在MySQL中的實(shí)現(xiàn)也有很多限制——執(zhí)行計(jì)劃緩存是連接級別的娃属，游標(biāo)的物化和臨時(shí)表相同，異常處理也非常困難

* 調(diào)試MySQL的存儲過程是一件很困難的事情

* 它和基于語句的二進(jìn)投影日志復(fù)制合作得并不好

4.存儲過程和函數(shù)的限制：

* 優(yōu)化器無法使用關(guān)鍵字DETERMINISTIC來優(yōu)化單個查詢中多次調(diào)用存儲函數(shù)的情況

* 優(yōu)化器無法評估存儲函數(shù)的執(zhí)行成本

* 每個連接都有獨(dú)立的存儲過程的執(zhí)行計(jì)劃緩存

* 存儲程序和復(fù)制是一組詭異組合

5.觸發(fā)器：可以讓你在執(zhí)行INSERT护姆、UPDATE或者DELETE的時(shí)候矾端，執(zhí)行一些特定的操作，可以在MySQL中指定是在SQL語句執(zhí)行前觸發(fā)還是在執(zhí)行后觸發(fā)卵皂，可以使用觸發(fā)器實(shí)現(xiàn)一些強(qiáng)制限制秩铆，或者某些業(yè)務(wù)邏輯，否則灯变，就需要在應(yīng)用程序中實(shí)現(xiàn)這些邏輯

6.觸發(fā)器的注意和限制：

* 對每一個表的每一個事件殴玛，最多只能定義一個觸發(fā)器

* 只支持“基于行的觸發(fā)”，也就是說添祸，觸發(fā)器是針對一條記錄的滚粟，而不是針對整個SQL語句的，如果變更的數(shù)據(jù)集非常大的話刃泌，效率會很低

* 觸發(fā)器可以掩蓋服務(wù)器背后的工作

* 觸發(fā)器可以掩蓋服務(wù)器背后的工作凡壤，一個簡單的SQL語句背后可能包含了很多看不見的工作

* 觸發(fā)器的問題也很難排查署尤，如果某個性能問題和觸發(fā)器相關(guān)，會很難分析和定位

* 觸發(fā)器可能導(dǎo)致死鎖和鎖等待

* 觸發(fā)器并不能一定保證更新的原子性

7.觸發(fā)器的用處：

* 實(shí)現(xiàn)一些約束亚侠、系統(tǒng)維護(hù)任務(wù)曹体，以及更新反范式化數(shù)據(jù)的時(shí)候

* 記錄數(shù)據(jù)變更日志

8.事件：類似于Linux的定時(shí)任務(wù)，指定MySQL在某個時(shí)候執(zhí)行一段SQL代碼硝烂，或者每隔一個時(shí)間間隔執(zhí)行一段SQL代碼

E.游標(biāo)

1.MySQL在服務(wù)器端提供提供只讀的箕别、單向的游標(biāo)，而且只能在存儲過程或者更底層的客戶端API中使用滞谢，指向的對象都是存儲在臨時(shí)表中而不是實(shí)際查詢到的數(shù)據(jù)究孕，所以總是只讀的

2.會帶來額外的性能開銷

3.不支持客戶端的游標(biāo)

F.綁定變量

1.當(dāng)創(chuàng)建一個綁定變量SQL時(shí)，客戶端向服務(wù)器發(fā)送了一個SQL語句的原型爹凹。服務(wù)器端收到這個SQL語句框架后厨诸，解析并存儲這個SQL語句的部分執(zhí)行計(jì)劃，返回給客戶端一個SQL語句處理句柄禾酱。以后每次執(zhí)行這類查詢微酬，客戶端都指定使用這個句柄

2.可以更高效地執(zhí)行大量的重復(fù)語句：

* 在服務(wù)器端只需要解析一次SQL語句

* 在服務(wù)器端某些優(yōu)化項(xiàng)的工作只需要執(zhí)行一次，因?yàn)樗鼤彺嬉徊糠值膱?zhí)行計(jì)劃

* 以二進(jìn)制的方式只發(fā)送參數(shù)和句柄颤陶，比起每次都發(fā)送ASC2碼文本效率更高

* 僅僅是參數(shù)——而不是整個查詢語句——需要發(fā)送到服務(wù)器端颗管，所以網(wǎng)絡(luò)開銷會更小

* MySQL在存儲參數(shù)的時(shí)候，直接將其存放到緩存中滓走，不再需要在內(nèi)存中多次復(fù)制

3.綁定變量相對也更安全垦江。無須在應(yīng)用程序中處理轉(zhuǎn)義，一則更簡單了搅方，二則也大大減少了SQL注入和攻擊的風(fēng)險(xiǎn)

4.最主要的用途就是在存儲過程中使用比吭，構(gòu)建并執(zhí)行“動態(tài)”的SQL語句

5.綁定變量的限制：

* 綁定變量是會話級別的枕扫，所以連接之間不能共用綁定變量句柄

* 在5.1版本之前碘橘，綁定變量的SQL是不能使用查詢緩存的

* 并不是所有的時(shí)候使用綁定變量都能獲得更好的性能

* 如果總是忘記釋放綁定變量資源，則在服務(wù)器端很容易發(fā)生資源“泄漏”

* 有些操作驳糯，比如BEGIN涛漂，無法在綁定變量中完成

G.用戶自定義函數(shù)

1.用戶自定義函數(shù)（UDF）必須事先編譯好并動態(tài)鏈接到服務(wù)器上赏表，這種平臺相關(guān)性使得UDF在很多方面都很強(qiáng)大，但一個錯誤也很可能讓服務(wù)器直接崩潰匈仗，甚至擾亂服務(wù)器的內(nèi)存或者數(shù)據(jù)

H.插件

1.插件可以在MySQL中新增啟動選項(xiàng)和狀態(tài)值瓢剿，還可以新增INFORMATION_SCHEMA表，或者在MySQL的后臺執(zhí)行任務(wù)等等

2.在5.1后支持的插件接口：

* 存儲過程插件

* 后臺插件悠轩，可以讓程序在MySQL中運(yùn)行间狂，可以實(shí)現(xiàn)自己的網(wǎng)絡(luò)監(jiān)聽、執(zhí)行自己的定期任務(wù)

* INFORMATION_SCHEMA插件哗蜈，提供一個新的內(nèi)存INFORMATION_SCHEMA表

* 全文解析插件前标，提供一種處理文本的功能，可以根據(jù)自己的需求來對一個文檔進(jìn)行分詞

* 審計(jì)插件距潘，在查詢執(zhí)行的過程中的某些固定點(diǎn)被調(diào)用炼列，可以記錄MySQL的事件日志

* 認(rèn)證插件，既可可以在MySQL客戶端也可在它的服務(wù)器端音比，可以使用這類插件來擴(kuò)展MySQL的認(rèn)證功能

I.字符集和校對

1.字符集是一種從二進(jìn)制編碼到某類字符符號的映射俭尖，可以參考如何使用一個字節(jié)來表示英文字母《呆妫“校對”是指一組用于某個字符集的排序規(guī)則

2.每種字符集都可能有多種校對規(guī)則稽犁，并且都有一個默認(rèn)的校對規(guī)則，每個校對規(guī)則都是針對某個特定的字符集的骚亿，和其他的字符集沒有關(guān)系

3.MySQL有很多的選項(xiàng)用于控制字符集已亥，這些選項(xiàng)和字符集很容易混淆，只有基于字符的值才真正的“有”字符集的概念

4.MySQL的兩類設(shè)置：創(chuàng)建對象時(shí)的默認(rèn)設(shè)置来屠、服務(wù)器和客戶端通信時(shí)的設(shè)置

5.如果比較的兩個字符串的字符集不同虑椎，MySQL會先將其轉(zhuǎn)成同一個字符集再進(jìn)行比較

6.一些需要注意的地方：

* 詭異的character_set_database設(shè)置，當(dāng)改變默認(rèn)數(shù)據(jù)庫的時(shí)候俱笛，這個變量也會跟著變捆姜，所以當(dāng)連接到MySQL實(shí)例上又沒有指定要使用的數(shù)據(jù)庫時(shí)，默認(rèn)值會和character_set_server相同

* LOAD DATA INFILE迎膜，當(dāng)使用時(shí)泥技，數(shù)據(jù)庫總是將文件中的字符按照字符集character_set_database來解析

* SELECT INTO OUTFILE，MySQL會將結(jié)果不做任何轉(zhuǎn)碼地寫入文件

* 嵌入式轉(zhuǎn)義序列磕仅，MySQL會根據(jù)character_set_client的設(shè)置來解析轉(zhuǎn)義序列

7.某些字符集和校對規(guī)則可能會需要更多的CPU操作珊豹，可能會消耗更多的內(nèi)存和存儲空間，甚至還會影響索引的正常使用

* 不同的字符集和校對規(guī)則之間的轉(zhuǎn)換可能會帶來額外的系統(tǒng)開銷

* 只有排序查詢要求的字符集與服務(wù)器數(shù)據(jù)的字符集相同的時(shí)候榕订，才能使用索引進(jìn)行排序

* 為了能夠適應(yīng)各種字符集平夜，包括客戶端字符集、在查詢中顯式指定的字符集卸亮，MySQL會在需要的時(shí)候進(jìn)行字符集轉(zhuǎn)換

J.全文索引

1.MyISAM的全文索引作用對象是一個“全文集合”忽妒，這可能是某個數(shù)據(jù)表的一列，也可能是多個列

2.可以根據(jù)WHERE子句中的MATCH AGAINST來區(qū)分查詢是否使用全文索引

3.在使用全文索引進(jìn)行排序的時(shí)候兼贸，MySQL無法再使用索引排序段直，如果不想使用文件排序的話，就不要在查詢中使用ORDER BY子句

4.在布爾搜索中溶诞，用戶可以在查詢中自定義某個被搜索的詞語的相關(guān)性鸯檬，可能通過一些前綴修飾符來定制搜索

5.全文索引在INSERT、UPDATE螺垢、DELETE中的操作代價(jià)很大

6.全文索引會影響索引選擇喧务、WHERE子句赖歌、ORDER BY等：

* 如果查詢中使用了MATCH AGAINST子句，而對應(yīng)列上又有可用的全文索引功茴，那么MySQL就一定會使用這個全文索引

* 全文索引只能用作全文搜索匹配

* 全文索引不存儲索引列的實(shí)際值庐冯，也就不可能用作索引覆蓋掃描

* 除了相關(guān)性排序，全文索引不能用作其他的排序

7.全文索引的配置和優(yōu)化：

* 經(jīng)常使用OPTIMIZE TABLE來減少碎片坎穿，如果是I/O密集型的定期進(jìn)行全文索引重建

* 保證索引緩存足夠大

* 提供一個好的停用詞表

* 忽略一些太短的單詞

* 導(dǎo)入大量數(shù)據(jù)時(shí)展父，最好通過命令DISABLE KEYS來禁用全文索引，然后導(dǎo)入結(jié)束后使用ENABLE KEYS來建立全文索引

* 如果數(shù)據(jù)集特別大玲昧，則需要對數(shù)據(jù)進(jìn)行手動分區(qū)栖茉，然后將數(shù)據(jù)分布到不同的節(jié)點(diǎn)，再做并行的搜索

K.分布式（XA）事務(wù)

1.XA事務(wù)中需要有一個事務(wù)協(xié)調(diào)器來保證所有的事務(wù)參與者都完成了準(zhǔn)備工作孵延。如果協(xié)調(diào)器收到所有的參與者都準(zhǔn)備好的消息吕漂，就會告訴所有的事務(wù)可以提交了，MySQL在這個XA事務(wù)過程中扮演一個參與者的角色尘应，而不是協(xié)調(diào)者

2.因?yàn)橥ㄐ叛舆t和參與者本身可能失敗痰娱，所以外部XA事務(wù)比內(nèi)部消耗會更大

L.查詢緩存

1.MySQL查詢緩存保存查詢返回的完整結(jié)果，當(dāng)查詢命中該緩存菩收，MySQL會立刻返回結(jié)果梨睁，跳過了解析、優(yōu)化和執(zhí)行階段

2.MySQL判斷緩存命中的方法很簡單：緩存放在一個引用表中娜饵，通過一個哈希值引用坡贺，這個哈希值包括了如下因素，即查詢本身箱舞、當(dāng)前要查詢的數(shù)據(jù)庫遍坟、客戶端協(xié)議的版本等一些其他可能會影響返回結(jié)果的信息

3.當(dāng)判斷緩存是否命中時(shí)，MySQL不會解析晴股、“正規(guī)化”或者參數(shù)化查詢語句愿伴，而是直接使用SQL語句和客戶端發(fā)送過來的其他原始信息。任何字符上的不同电湘，例如空格隔节、注釋——都會導(dǎo)致緩存的不命中

4.當(dāng)查詢語句中有一些不確定的數(shù)據(jù)時(shí)，則不會被緩存寂呛，例如包含函數(shù)NOW()或者CURRENT_DATE()的查詢不會被緩存怎诫，只要包含任何用戶自定義函數(shù)、存儲函數(shù)贷痪、用戶變量幻妓、臨時(shí)表、mysql庫中的系統(tǒng)表劫拢，或者任何包含列級別權(quán)限的表肉津，都不會被緩存

5.打開查詢緩存對讀和寫操作都會帶來額外的消耗：

* 讀查詢在開始之前必須先檢查是否命中緩存

* 如果這個讀查詢可以被緩存强胰，那么當(dāng)完成執(zhí)行后，MySQL若發(fā)現(xiàn)查詢緩存中沒有這個查詢妹沙，會將其結(jié)果存入查詢緩存偶洋，這會帶來額外的系統(tǒng)消耗

* 當(dāng)向某個表寫入數(shù)據(jù)的時(shí)候，MySQL必須將對應(yīng)表的所有緩存都設(shè)置失效初烘，如果查詢緩存非常大或者碎片很多涡真，這個操作就可能會帶來很大系統(tǒng)消耗

6.對于需要消耗大量資源的查詢通常都是非常適合緩存的

7.緩存未命中：

* 查詢語句無法被緩存

* MySQL從未處理這個查詢

* 查詢緩存的內(nèi)存用完了

* 查詢緩存還沒有完成預(yù)熱

* 查詢語句之前從未執(zhí)行過

* 緩存失效操作太多了

8.緩存參數(shù)配置：

* query_cache_type分俯，是否打開查詢緩存

* query_cache_size肾筐，查詢緩存使用的總內(nèi)存空間

* query_cache_min_res_unit，在查詢緩存中分配內(nèi)存塊時(shí)的最小單位缸剪，可以幫助減少由碎片導(dǎo)致的內(nèi)存空間浪費(fèi)

* query_cache_limit吗铐，MySQL能夠緩存的最大查詢結(jié)果

* query_cache_wlock_invalidate，如果某個數(shù)據(jù)表被其他的連接鎖住杏节，是否仍然從查詢緩存中返回結(jié)果

9.InnoDB和查詢緩存

* 事務(wù)是否可以訪問查詢緩存取決于當(dāng)前事務(wù)ID唬渗，以及對應(yīng)的數(shù)據(jù)表上是否有鎖

* 如果表上有任何的鎖，那么對這個表的任何查詢語句都是無法被緩存的

10.通用查詢緩存優(yōu)化：

* 用多個小表代替一個大表對查詢緩存有好處

* 批量寫入時(shí)只需要做一次緩存失效奋渔，所以相比單條寫入效率更好

* 因?yàn)榫彺婵臻g太大镊逝，在過期操作的時(shí)候可能會導(dǎo)致服務(wù)器僵死，控制緩存空間的大小

* 無法在數(shù)據(jù)庫或者表級別控制查詢緩存嫉鲸，但是可以通過SQL_CACHE和SQL_NO_CACHE來控制某個SELECT語句是否需要進(jìn)行緩存

* 對于 寫密集型的應(yīng)用來說撑蒜，直接禁用查詢緩存可能會提高系統(tǒng)的性能

* 因?yàn)閷コ庑盘柫康母偁帲袝r(shí)直接關(guān)閉查詢緩存對讀密集型的應(yīng)用也會有好處

八玄渗、優(yōu)化服務(wù)器設(shè)置

A.MySQL配置的工作原理

1.任何打算長期使用的設(shè)置都應(yīng)該寫到全局配置文件座菠，而不是在命令行特別指定

2.常用變量和動態(tài)修改它們的效果：

* key_buffer_size，可以一次性為鍵緩沖區(qū)（key buffer藤树，也叫鍵緩存key cache）分配所有指定的空間

* table_cache_size浴滴，不會立即生效——會延遲到下次有線程打開表才有效果，如果值大于緩存中表的數(shù)量岁钓，線程可以把最新打開的表放入緩存升略，如果比緩存中的表數(shù)小，將從緩存中刪除不常使用的表

* thread_cache_size屡限，不會立即生效——將在下次有連接被關(guān)閉時(shí)產(chǎn)生效果降宅，檢查緩存中是否還有空間來緩存線程，如果有空間囚霸，則緩存該線程以備下次連接征用腰根，如果沒空間，將銷毀該線程而不再緩存

* query_cache_size拓型，一次性分配并初始化這塊內(nèi)存

* read_buffer_size额嘿，只在有查詢需要使用時(shí)才會為該緩存分配內(nèi)存

* read_rnd_buffer_size瘸恼，只在有查詢需要使用時(shí)才會為該緩存分配內(nèi)存，并且只會分配需要的內(nèi)存大小而不是全部指定的大小

* sort_buffer_size册养，只會在有查詢需要做排序時(shí)才會為該緩存分配內(nèi)存

3.對于連接級別的設(shè)置东帅，不要輕易地在全局級別增加它們的值，除非確認(rèn)這樣做是對的

4.設(shè)置變量時(shí)請小心球拦，并不是值越大就越好靠闭，而且如果設(shè)置的值太高，可能更容易導(dǎo)致問題：可能會由于內(nèi)存不足導(dǎo)致服務(wù)器內(nèi)存交換坎炼，或者超過地址空間

5.不要期望通過建立一套基準(zhǔn)測試方案愧膀，然后不斷迭代地驗(yàn)證對配置項(xiàng)的修改來找到最佳配置方案，而要把時(shí)間花在檢查備份谣光、監(jiān)控執(zhí)行計(jì)劃的變動之類的事情上檩淋，可能會更有意義

B.什么不該做

1.不要根據(jù)一些“比率”來調(diào)優(yōu)：例如緩存命中率跟緩存是否過大或過小沒有關(guān)系2.不要使用調(diào)優(yōu)腳本

3.不要相信很流行的內(nèi)存消耗公式

C.創(chuàng)建MySQL配置文件

1.MySQL編譯的默認(rèn)設(shè)置并不都是靠譜的，雖然其中大部分都比較合適

2.從一個比默認(rèn)值大一點(diǎn)但不是大得很離譜的安全值開始是比較好的萄金，MySQL的內(nèi)存利用率并不總是可以預(yù)測的：它可能依賴很多的因素蟀悦，例如查詢的復(fù)雜性和并發(fā)性

3.配置服務(wù)器的首選途徑：了解它內(nèi)部做了什么，以及參數(shù)之間如何相互影響氧敢，然后再決定

4.open_files_limit日戈，在Linux系統(tǒng)上設(shè)置得盡可能大，如果參數(shù)不夠大孙乖，將會踫到24號錯誤“打開的文件太多（too many open files）”

5.每隔60秒查看狀態(tài)變量的增量變化：mysqladmin extended-status ri60

D.配置內(nèi)存使用

1.配置MySQL正確地使用內(nèi)存量對高性能是至關(guān)重要的浙炼，內(nèi)存消耗分為兩類：可以控制的內(nèi)存和不可以控制的內(nèi)存

2.配置內(nèi)存：

* 確定可以使用的內(nèi)存上限

* 確定每個連接MySQL需要使用多少內(nèi)存

* 確定操作系統(tǒng)需要多少內(nèi)存才夠用

* 把剩下的內(nèi)存全部給MySQL的緩存

3.MySQL保持一個連接（線程）只需要少量的內(nèi)存，它還需要一個基本量的內(nèi)存來執(zhí)行任何給定查詢的圆，需要為高峰時(shí)期執(zhí)行的大量查詢預(yù)留好足夠的內(nèi)存鼓拧，否則，查詢執(zhí)行可能因?yàn)槿狈?nèi)存而導(dǎo)致執(zhí)行效率不佳或執(zhí)行失敗

4.跟查詢一樣越妈，操作系統(tǒng)也需要保留足夠的內(nèi)存給它工作季俩，如果沒有虛擬內(nèi)存正在交換（Paging）到磁盤，就是表明操作系統(tǒng)內(nèi)存足夠的最佳跡象

5.如果服務(wù)器只運(yùn)行MySQL梅掠，所有不需要為操作系統(tǒng)以及查詢處理保留的內(nèi)存都可以用作MySQL緩存

6.大部分情況下最重要的緩存：

* InnoDB緩沖池

* InnoDB日志文件和MyISAM數(shù)據(jù)的操作系統(tǒng)緩存

* MyISAM鍵緩存

* 查詢緩存

* 無法手工配置的緩存酌住，例如二進(jìn)制日志和表定義文件的操作系統(tǒng)緩存

7.InnoDB緩沖池并不僅僅緩存索引：它還會緩存行的數(shù)據(jù)、自適應(yīng)哈希索引阎抒、插入緩沖（Insert Buffer）酪我、鎖，以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)且叁，還使用緩沖池來幫助延遲寫入都哭，InnoDB嚴(yán)重依賴緩沖池

8.如果事先知道什么時(shí)候需要關(guān)閉InnoDB，可以在運(yùn)行時(shí)修改innodb_max_dirty_pages_pct變量，將值改小欺矫，等待刷新純種清理緩沖池纱新，然后在臟頁數(shù)量較少時(shí)關(guān)閉，可以監(jiān)控the Innodb_buffer_pool_pages_dirty狀態(tài)變量或者使用innotop來監(jiān)控SHOW INNODB STATUS來觀察臟頁的刷新量

7.MyISAM的鍵緩存也被稱為鍵緩沖穆趴，默認(rèn)只有一個鍵緩存脸爱，但也可以創(chuàng)建多個，MyISAM自身只緩存索引未妹，不緩存數(shù)據(jù)簿废，最重要的配置項(xiàng)是key_buffer_size，不要超過索引的總大小络它，或者不超過操作系統(tǒng)緩存保留總內(nèi)存的25%-50%族檬，以更小的為準(zhǔn)

8.了解MyISAM索引實(shí)際上占用多少磁盤空間，查詢INFORMATION_SCHEMA表的INDEX_LENGTH字段酪耕，把它們的值相加导梆，就可以得到索引存儲占用空間

9.塊大小也是很重要的（特別是寫密集型負(fù)載）轨淌，因?yàn)樗绊懥薓yISAM迂烁、操作系統(tǒng)緩存，以及文件系統(tǒng)之間的交互递鹉，如果緩存塊太小盟步，可能會踫到寫時(shí)讀取

10.線程緩存保存那些當(dāng)前沒有與連接關(guān)聯(lián)但是準(zhǔn)備為后面新的連接服務(wù)的線程，當(dāng)一個新的連接創(chuàng)建時(shí)躏结，如果緩存中有線程存在却盘，MySQL從緩存中刪除一個線程，并且把它分配給這個新的連接媳拴，當(dāng)連接關(guān)閉時(shí)黄橘，如果線程緩存還有空間的話，MySQL又會把線程放回緩存屈溉，如果沒有空間的話塞关，MySQL會銷毀這個線程

11.thread_cache_size變量指定了MySQL可以保持在緩存中的線程數(shù)，一般不需要配置這個值子巾，除非服務(wù)器會有很多連接請求

12.表緩存（Table Cache）和線程緩存的概念是相似的帆赢，但存儲的對象代表的是表，緩存對象包含相關(guān)表.frm文件的解析結(jié)果线梗，加上其他數(shù)據(jù)椰于。表緩存可以重用資源，讓服務(wù)器避免修改MyISAM文件頭來標(biāo)記表“正在使用中”仪搔，對InnoDB的重要性要小得多

12.表緩存的缺點(diǎn)是瘾婿，當(dāng)服務(wù)器有很多MyISAM表時(shí)，可能會導(dǎo)致關(guān)機(jī)時(shí)間較長，因?yàn)殛P(guān)機(jī)前索引塊必須完成刷新偏陪，表都必須標(biāo)記為不再打開

13.InnoDB數(shù)據(jù)字典（Data Dictionary）孩饼，InnoDB自己的表緩存，當(dāng)InnoDB打開一張表竹挡，就增加了一個對應(yīng)的對象到數(shù)據(jù)字典

14.InnoDB沒有將統(tǒng)計(jì)信息持久化镀娶，而是在每次打開表時(shí)重新計(jì)算，5.6以后可以打開innodb_use_sys_stats_table選項(xiàng)來持久化存儲統(tǒng)計(jì)信息到磁盤

15.可以關(guān)閉InnoDB的innodb_stats_on_metadata選項(xiàng)來避免耗時(shí)的表統(tǒng)計(jì)信息刷新

16.如果可以揪罕，最好把innodb_open_files的值設(shè)置得足夠大以使服務(wù)器可以保持所有的.ibd文件同時(shí)打開

E.配置MySQL的I/O行為

1.InnoDB I/O配置

* InnoDB不僅允許控制怎么恢復(fù)梯码，還允許控制怎么打開和刷新數(shù)據(jù)（文件），這會對恢復(fù)和整體性能產(chǎn)生巨大的影響

* 對于常見的應(yīng)用好啰，最重要的一小部分內(nèi)容是InnoDB日志文件大小轩娶、InnoDB怎樣刷新它的日志緩沖，以及InnoDB怎樣執(zhí)行I/O

* 整體的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group兩個參數(shù)框往，對寫性能非常重要

* 通常不需要把日志緩沖區(qū)設(shè)置得非常大鳄抒，推薦的范圍是1MB-8MB，除非要寫很多相當(dāng)大的BLOB記錄

* 可以通過檢查SHOW INNODB STATUS的輸出中LOG部分來監(jiān)控InnoDB的日志和日志緩沖區(qū)的I/O性能椰弊，通過觀察Innodb_os_log_written狀態(tài)變量來查看InnodDB對日志文件寫出了多少數(shù)據(jù)许溅。日志文件的全部大小，應(yīng)該足夠容納服務(wù)器一個小時(shí)的活動內(nèi)容

* 如果和持久相比更在乎性能秉版，可以修改innodb_flush_log_at_trx_commit變量來控制日志緩沖刷新的頻繁程度

* 使用innodb_flush_method選項(xiàng)可以配置InnoDB如何跟文件系統(tǒng)相互作用

* InnoDB用表空間并不只是存儲表和索引贤重，還保存了回滾日志、插入緩沖（Insert Buffer）清焕、雙寫緩沖（Doublerite Buffer）及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)

* 為了控制寫入速度并蝗，可以設(shè)置innodb_max_purge_lag變量為一個大于0的值，這個值表示InnoDB開始延遲后面的語句更新數(shù)據(jù)之前秸妥，可以等待被清除的最大的事務(wù)數(shù)量

* 雙寫緩沖是表空間的一個特殊的保留區(qū)域滚停，在一些連續(xù)的塊中足夠保存100個頁，本質(zhì)上是一個最近寫回的頁面的備份拷貝粥惧，當(dāng)InnoDB從緩沖池刷新頁面到磁盤時(shí)键畴，首先把它們寫（或者刷新）到雙寫緩沖，然后再把它們寫到其所屬的數(shù)據(jù)區(qū)域中影晓，這可以保證每個頁面的寫入都是原子并且持久化的

* 設(shè)置innodb_doublewrite為0來關(guān)閉雙寫緩沖

* sync_binlog選項(xiàng)控制MySQL怎么刷新二進(jìn)制日志到磁盤

* 二進(jìn)制日志镰吵，如果希望使用expire_logs_days選項(xiàng)來自動清理舊的二進(jìn)制日志，就不要用rm命令去刪

2.MyISAM的I/O配置

* MyISAM通常每次寫操作之后就把索引變更刷新磁盤挂签，批量操作會更快一些

* 通過設(shè)置delay_key_write變量疤祭，可以延遲索引的寫入，修改的鍵緩沖塊直到表被關(guān)閉才會刷新

* myisam_recover選項(xiàng)控制MyISAM怎樣尋找和修復(fù)錯誤

* 內(nèi)存映射使得MyISAM直接通過操作系統(tǒng)的頁面緩存訪問.MYD文件饵婆，避免系統(tǒng)調(diào)用的開銷勺馆，5.1后可以通過myisam_use_mmap選項(xiàng)打開內(nèi)存映射

F.配置MySQL并發(fā)

1.InnoDB并發(fā)配置

* InnoDB有自己的“線程調(diào)度器”控制線程怎么進(jìn)入內(nèi)核訪問數(shù)據(jù)，以及它們在內(nèi)核中一次可以做哪些事，最基本的限制并發(fā)的方式是使用innodb_thread_concurrency變量草穆，它會限制一次性可以有多少線程進(jìn)入內(nèi)核

* 并發(fā)值 = CPU數(shù)量 * 磁盤數(shù)量 * 2灌灾，在實(shí)踐中使用更小的值會更好一點(diǎn)

2.MyISAM并發(fā)配置

* 盡管MyISAM是表級鎖，它依然可以一邊讀取悲柱，一邊并發(fā)追加新行锋喜，這種情況下只能讀取到查詢開始時(shí)的所有數(shù)據(jù)，新插入的數(shù)據(jù)是不可見的豌鸡，這樣可以避免不一致讀

* 通過設(shè)置concurrent_insert這個變量嘿般，可以配置MyISAM打開并發(fā)插入

* 讓INSERT、REPLACE涯冠、DELETE炉奴、UPDATE語句的優(yōu)先級比SELECT語句更低，設(shè)置low_priority_updates選項(xiàng)就可以

G.基于工作負(fù)載的配置

1.當(dāng)服務(wù)器滿載情況下運(yùn)行時(shí)蛇更，請嘗試記錄所有的查詢語句瞻赶，因?yàn)檫@是最好的方式來查看哪種類型的查詢語句占用資源最多，同時(shí)創(chuàng)建processlist快照派任，通過state或者command字段來聚合它們

2.優(yōu)化BLOB和TEXT場景

* BLOB有幾個限制使得服務(wù)器對它的處理跟其他類型不一樣砸逊，不能在內(nèi)存臨時(shí)表中存儲BLOB值，效率很低

* 通過SUBSTRING()函數(shù)把值轉(zhuǎn)換為VARCHAR

* 讓臨時(shí)表更快一些：放在基于內(nèi)存的文件系統(tǒng)

* 如果使用的是InnoDB吨瞎，也可以調(diào)大InnoDB日志緩沖大小

* 大字段在InnoDB里可能浪費(fèi)大量空間

* 擴(kuò)展存儲禁用了自適應(yīng)哈希痹兜，因?yàn)樾枰暾乇容^列的整個長度穆咐，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)

* 太長的值可能使得查詢中作為WHERE條件不能使用索引

* 如果一張表里有很多大字段颤诀，最好是把它們組合起來單獨(dú)存到一個列里面

* 有時(shí)候可以把大字段用COMPRESS()壓縮后再存為BLOB，或者發(fā)送到MySQL前在應(yīng)用程序中進(jìn)行壓縮

3.優(yōu)化排序（Filesorts）：當(dāng)MySQL必須排序BLOG或TEXT字段時(shí)对湃，它只會使用前綴崖叫，然后忽略剩下部分的值

H.完成基本配置

1.tmp_table_size和max_heap_table_size，這兩個設(shè)置控制使得Memory引擎的內(nèi)存臨時(shí)表能使用多大的內(nèi)存

2.max_connections拍柒，這個設(shè)置的作用就像一個緊急剎車心傀，以保證服務(wù)器不會因應(yīng)用程序激增的連接而不堪重負(fù)，設(shè)置得以容納正巢鹧叮可能達(dá)到的負(fù)載脂男，并且要足夠安全，能保證允許你登錄和管理服務(wù)器

3.thread_cache_size种呐，可以通過觀察服務(wù)器一段時(shí)間的活動宰翅，來計(jì)算一個有理有據(jù)的值，250的上限是一個不錯的估算值

4.table_cache_size爽室，應(yīng)該被設(shè)置得足夠大汁讼，以避免總是需要重新打開和重新解析表的定義，可能通過觀察Open_tables的值及其在一段時(shí)間的變化來檢查該變量

I.安全和穩(wěn)定的設(shè)置

1.expire_logs_days，如果啟用了二進(jìn)制日志嘿架，應(yīng)該打開這個選項(xiàng)瓶珊，可以讓服務(wù)器在指定的天數(shù)之后清理舊的二進(jìn)制日志

2.max_allowed_packet，防止服務(wù)器發(fā)送太大的包耸彪，也會控制多大的包可以被接收

3.max_connect_errors伞芹，如果知道服務(wù)器可以充分抵御蠻力攻擊左腔，可以把這個值設(shè)得非常大吧碾，以有效地禁用主機(jī)黑名單

4.skip_name_resolve甘晤，禁用了另一個網(wǎng)絡(luò)相關(guān)和鑒權(quán)誰相關(guān)的陷阱：DNS查找

5.sql_mode瑞妇，不建議修改

6.sysdate_is_now季惯，可能導(dǎo)致與應(yīng)用預(yù)期向后不兼容的選項(xiàng)

7.read_only吗蚌，禁止沒有特權(quán)的用戶在備庫做變更永票，只接受從主庫傳輸過來的變更奖慌，不接受從應(yīng)用來的變更扮宠，可以把備庫設(shè)置為只讀模式

8.skip_slave_start西乖，阻止MySQL試圖自動啟動復(fù)制

9.slave_net_timeout，控制備庫發(fā)現(xiàn)跟主庫的連接已經(jīng)失敗并且需要重連之前等待的時(shí)間坛增，設(shè)置為一分鐘或更短

10.sync_master_info获雕、sync_relay_log、sync_relay_log_info收捣，5.5以后版本可用届案，解決了復(fù)制中備庫長期存在的問題：不把它們的狀態(tài)文件同步到磁盤，所以服務(wù)器崩潰后可能需要人來猜測復(fù)制的位置實(shí)際上在主庫是哪個位置罢艾，并且可能在中繼日志（Relay Log）里有損壞

J.高級InnoDB設(shè)置

1.innodb楣颠，如果設(shè)置為FORCE，只有在InnoDB可以啟動時(shí)咐蚯，服務(wù)器才會啟動

2.innodb_autoinc_lock_mode童漩，控制InnoDB如何生成自增主鍵值

3.innodb_buffer_pool_instances，在5.5以后春锋，可以把緩沖池切分為多段矫膨，在高負(fù)載的多核機(jī)器上提升MySQL可擴(kuò)展性的一個重要方式

4.innodb_io_capacity，有時(shí)需要把這個設(shè)置得相當(dāng)高期奔，才能穩(wěn)定地刷新臟頁

5.innodb_read_io_threads和innodb_write_io_threads侧馅，控制有多少后臺線程可以被I/O操作使用

6.innodb_strict_mode，讓MySQL在某些條件下把警告改成拋錯呐萌，尤其是無效的或者可能有風(fēng)險(xiǎn)的CREATE TABLE選項(xiàng)

7.innodb_old_blocks_time馁痴，指定一個頁面從LRU鏈表的“年輕”部分轉(zhuǎn)移到“年老”部分之前必須經(jīng)過的毫秒數(shù)，默認(rèn)為0搁胆，設(shè)置為1000毫秒（1秒）非常有效

九弥搞、操作系統(tǒng)和硬件優(yōu)化

A.什么限制了MySQL的性能

1.當(dāng)數(shù)據(jù)可以放在內(nèi)存中或者可以從磁盤中以足夠快的速度讀取時(shí)邮绿，CPU可能出現(xiàn)瓶頸，把大量的數(shù)據(jù)集完全放到大容量的內(nèi)存中攀例，以現(xiàn)在的硬件條件完全是可行的

2.I/O瓶頸船逮，一般發(fā)生在工作所需的數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過有效內(nèi)存容量的時(shí)候，如果應(yīng)用程序是分布在網(wǎng)絡(luò)上的粤铭，或者如果有大量的查詢和低延遲的要求挖胃，瓶頸可能轉(zhuǎn)移到網(wǎng)絡(luò)上

B.如何為MySQL選擇CPU

1.可以通過檢查CPU利用率來判斷是否是CPU密集型的工作負(fù)載，還需要看看CPU使用率和大多數(shù)重要的查詢的I/O之間的平衡梆惯，并注意CPU負(fù)載是否分配均勻

2.當(dāng)遇到CPU密集型的工作時(shí)酱鸭，MySQL通常可以從更快的CPU中獲益垛吗，但還依賴于負(fù)載情況和CPU數(shù)量

3.MySQL復(fù)制也能在高速CPU下工作得非常好凹髓，而多CPU對復(fù)制的幫助卻不大

4.多CPU在聯(lián)機(jī)事務(wù)處理（OLTP）系統(tǒng)的場景中非常有用，在這樣的環(huán)境中怯屉，并發(fā)可能成為瓶頸

C.平衡內(nèi)存和磁盤資源

1.配置大量內(nèi)存最終目的是避免磁盤I/O蔚舀，最關(guān)鍵的是平衡磁盤的大小、速度锨络、成本和其他因素赌躺，以便為工作負(fù)載提供高性能的表現(xiàn)

2.設(shè)計(jì)良好的數(shù)據(jù)庫緩存（如InnoDB緩沖池），其效率通常超過操作系統(tǒng)的緩存羡儿，因?yàn)椴僮飨到y(tǒng)緩存是為通用任務(wù)設(shè)計(jì)的

3.數(shù)據(jù)庫服務(wù)器同時(shí)使用順序和隨機(jī)I/O礼患，隨機(jī)I/O從緩存從受益最多

4.每個應(yīng)用程序都有一個數(shù)據(jù)的“工作集”——就是這個工作確實(shí)需要用到的數(shù)據(jù)

5.工作集包括數(shù)據(jù)和索引，所以應(yīng)該采用緩存單位來計(jì)數(shù)掠归，一個緩存單位是存儲引擎工作的數(shù)據(jù)最小單位

6.找到一個良好的內(nèi)存/磁盤比例最好的方式是通過試驗(yàn)和基準(zhǔn)測試

7.硬盤選擇考慮因素：存儲容量缅叠、傳輸速度、訪問時(shí)間拂到、主軸轉(zhuǎn)速痪署、物理尺寸

8.MySQL如何擴(kuò)展到多個磁盤上取決于存儲引擎和工作負(fù)載，InnoDB能很好地?cái)U(kuò)展到多個硬盤驅(qū)動器兄旬，然而，MyISAM的表鎖限制其寫的可擴(kuò)展性余寥，因此寫繁重的工作加在MyISAM上领铐，可能無法從多個驅(qū)動器中收益

D.固態(tài)存儲

1.高質(zhì)量閃存設(shè)備具備：

* 相比硬盤有更好的隨機(jī)讀寫性能

* 相比硬盤有更好的順序讀寫性能

* 相比硬盤能更好地支持并發(fā)

* 提升隨機(jī)I/O和并發(fā)性

2.閃存的最重要特征是可以迅速完成多次小單位讀取，但是寫入更有挑戰(zhàn)性宋舷。閃存不能在沒有做擦除操作前改寫一個單元（Cell）绪撵，并且一次必須擦除一個大塊。擦除周期是緩慢的祝蝠，并且最終會磨損整個塊

3.垃圾收集對理解閃存很重要音诈。為了保持一些塊是干凈的并且可以被寫入幻碱，設(shè)備需要回收臟塊。這需要設(shè)備上有一些空閑空間

4.許多設(shè)備被填滿后會開始變慢细溅，速度下降是由于沒有空閑塊時(shí)必須等待擦寫完成所造成的

5.固態(tài)存儲最適合使用在任何有著大量隨機(jī)I/O工作負(fù)載的場景下褥傍，隨機(jī)I/O通常是由于數(shù)據(jù)大于服務(wù)器的內(nèi)存導(dǎo)致的，閃存設(shè)備可能大大緩解這種問題

6.單線程工作負(fù)載也是另一個閃存的潛在應(yīng)用場景

7.閃存也可以為服務(wù)器整合提供巨大的幫助

8.Flashcache喇聊，磁盤和內(nèi)存技術(shù)的結(jié)合恍风，適合以讀為主的I/O密集型負(fù)載，并且工作集太大誓篱，用內(nèi)存優(yōu)化并不經(jīng)濟(jì)的情況

9.優(yōu)化固態(tài)存儲上的MySQL

* 增加InnoDB的I/O容量

* 讓InnoDB日志文件更大

* 把一些文件從閃存轉(zhuǎn)移到RAID

* 禁用預(yù)讀

* 配置InnoDB刷新算法

* 禁用雙寫緩沖的可能

* 限制插入緩沖大小朋贬，插入緩沖設(shè)計(jì)來用于減少當(dāng)更新行時(shí)不在內(nèi)存中的非唯一索引引起的隨機(jī)I/O

* InnoDB的頁大小

* 優(yōu)化InnoDB頁面校驗(yàn)（Checksum）的替代算法

E.為備庫選擇硬件

1.通常需要跟主庫差不多的配置

F.RAID性能優(yōu)化

1.RAID可以幫助做冗余、擴(kuò)展存儲容量窜骄、緩存锦募，以及加速

2.RAID 0：如果只是簡單的評估成本和性能，是成本最低和性能最高的RAID配置

3.RAID 1：在很多情況下提供很好的讀性能邻遏，并且在不同的磁盤間冗余數(shù)據(jù)御滩，所以有很好的冗余性，非常適合用來存放日志或者類似的工作

4.RAID 5：通過分布奇偶校驗(yàn)把數(shù)據(jù)分散到多個磁盤党远，如果任何一個盤的數(shù)據(jù)失效削解，都可以從奇偶校驗(yàn)塊中重建，但如果有兩個磁盤失效了沟娱，則整個卷的數(shù)據(jù)無法恢復(fù)氛驮，最經(jīng)濟(jì)的冗余配置。隨機(jī)寫是昂貴的济似，存放數(shù)據(jù)或者日志是一種可接受的選擇矫废，或者是以讀為主的業(yè)務(wù)

5.RAID 10：對數(shù)據(jù)存儲是個非常好的選擇，由分片的鏡像組成砰蠢，對讀和寫都有良好的擴(kuò)展性

6.RAID 50：由條帶化的RAID 5組成

G.SAN和NAS

1.SAN（Storage Area Network）和NAS（Network-Attached Storage）是兩個外部文件存儲設(shè)備加載到服務(wù)器的方法蓖扑，訪問SAN設(shè)備時(shí)通過塊接口，NAS設(shè)備通過基于文件的協(xié)議來訪問

2.SAN允許服務(wù)器訪問非常大量的硬盤驅(qū)動器台舱，并且通常配置大容量智能高速緩存來緩沖寫入

3.哪些工作放在SAN上不合適：執(zhí)行大量的隨機(jī)I/O的單線程任務(wù)

4.SAN的應(yīng)用：

* 備份律杠，可以只備份SAN

* 簡化容量規(guī)劃

* 存儲整合還是服務(wù)器整合

* 高可用

* 服務(wù)器之間的交互

* 成本

H.使用多磁盤卷

1.二進(jìn)制日志和數(shù)據(jù)文件分離的真正的優(yōu)勢，是減少事故中同時(shí)丟失數(shù)據(jù)和日志文件的可能性

2.如果有很多磁盤竞惋，投入一些給事務(wù)日志可能會從中受益

I.網(wǎng)絡(luò)配置

1.在生產(chǎn)服務(wù)器上啟用skip_name_resolve是個好主意柜去，損壞或緩慢的DNS解析對許多應(yīng)用程序都是個問題，對MySQL尤嚴(yán)重拆宛，如果啟用skip_name_resolve選項(xiàng)嗓奢，MySQL將不會做任何DNS查找的工作

2.可以通過MySQL的back_log選項(xiàng)控制MySQL的傳入TCP連接隊(duì)列的大小，在每秒有很多連接創(chuàng)建和銷毀的環(huán)境中浑厚，默認(rèn)值50是不夠的

3.網(wǎng)絡(luò)物理隔離也是很重要的因素股耽，盡可能避免實(shí)時(shí)的跨數(shù)據(jù)中心的操作是明智的

J.選擇操作系統(tǒng)

1.一般企業(yè)級的MySQL部署在Windows上根盒，但一般的企業(yè)級MySQL更多的還是部署在類UNIX操作系統(tǒng)上

K.選擇文件系統(tǒng)

1.如果可能，最好使用日志文件系統(tǒng)物蝙，如ext3炎滞、ext4、XFS茬末、ZFS或者JFS

2.可以調(diào)整文件系統(tǒng)的預(yù)讀行為厂榛，因?yàn)檫@可能也是多余的

L.選擇磁盤隊(duì)列調(diào)度策略

1.在GUN/Linux上，隊(duì)列調(diào)度決定了到塊設(shè)備的請求實(shí)際上發(fā)送到底層設(shè)備的順序丽惭，默認(rèn)情況下使用cfq（Completely Fair Queueing击奶，完全公平排隊(duì)）策略，在MySQL的工作負(fù)載類型下责掏，cfq會導(dǎo)致很差的響應(yīng)時(shí)間柜砾，因?yàn)闀陉?duì)列中延遲一些不必要的請求

2.cfq之外的兩個選項(xiàng)都適合服務(wù)器級的硬件，noop調(diào)度適合沒有自己的調(diào)度算法的設(shè)備换衬，deadline則對RAID控制器和直接使用的磁盤都工作良好

M.線程

1.MySQL每個連接使用一個線程痰驱，另外還有內(nèi)部處理線程、特殊用途的線程瞳浦，以及所有存儲引擎創(chuàng)建的線程

2.MySQL確實(shí)需要內(nèi)核級線程的支持担映，而不只是用戶級線程，這樣才能更有效地使用多個CPU叫潦，另外也需要有效的同步原子

N.內(nèi)存交換區(qū)

1.內(nèi)存交換對MySQL性能影響是很糟糕的蝇完，它破壞了緩存在內(nèi)存的目的，并且相對于使用很小的內(nèi)存做緩存矗蕊，使用交換區(qū)的性能更差

2.在GNU/Linux上短蜕，可以用vmstat來監(jiān)控內(nèi)存交換，最好查看si和so列報(bào)告的內(nèi)存交換I/O活動傻咖，這比看swpd列報(bào)告的交換區(qū)利用率更重要朋魔，最佳為0

3.設(shè)置/proc/sys/vm/swappiness為一個很小的值

4.修改存儲引擎怎么讀取和寫入數(shù)據(jù)，使用innodb_flush_method=0_DIRECT減輕I/O壓力

5.使用MySQL的memlock配置項(xiàng)卿操，可以把MySQL鎖定在內(nèi)存

O.操作系統(tǒng)狀態(tài)

1.vmstat

* vmstat 5警检，每隔5秒刷新一次

* procs，r列顯示多少進(jìn)程正在等待CPU硬纤，b列顯示多少進(jìn)程正在不可中斷地休眠

* memory解滓，swpd列顯示多少塊被換出到了磁盤，剩下的三個列顯示了多少塊是空閑的筝家、多少塊正在被用作緩沖，以及多少正在被用作操作系統(tǒng)的緩存

* swap邻辉，顯示頁面交換活動

* io溪王，顯示有多少塊從塊設(shè)備讀热啊（ bi）和寫出（bo）

* system，顯示了每秒中斷（in）和上下文切換（cs）的數(shù)量

* cpu莹菱，顯示所有的CPU時(shí)間花費(fèi)在各類操作的百分比

2.iostat

* iostats -dx 5移国，每5秒刷新

* rrqm/s和wrqm/s，每秒合并的讀和寫請求道伟，意味著操作系統(tǒng)從隊(duì)列中拿出多個邏輯請求合并為一個請求到實(shí)際磁盤

* r/s和w/s迹缀，每秒發(fā)送到設(shè)備的讀和寫請求

* rsec/s和wsec/s，每秒讀和寫的扇區(qū)數(shù)

* avgrq-sz蜜徽，請求的扇區(qū)數(shù)

* avgqu-sz祝懂，在設(shè)備隊(duì)列中等待的請求數(shù)

* await，磁盤排除上花費(fèi)的毫秒數(shù)

* svctm拘鞋，服務(wù)請求花費(fèi)的毫秒數(shù)砚蓬，不包括排除時(shí)間

* %util，至少有一個活躍請求所占時(shí)間的百分比

3.CPU密集型的機(jī)器盆色，vmstat輸出通常在us列會有一個很高的值灰蛙，也可能在sy列有很高的值

4.I/O密集型工作負(fù)載下，vmstat會顯示很多處理器在非中斷休眠（b列）狀態(tài)隔躲，并且wa這一列的值很高

5.發(fā)生內(nèi)存交換的機(jī)器可能在swpd列有一個很高的值

十摩梧、復(fù)制

A.復(fù)制概述（重點(diǎn)）

1.復(fù)制功能是mysql大規(guī)模，高性能的基礎(chǔ)

兩種復(fù)制方式：基于行宣旱、基于語句

原理都是主庫記錄二進(jìn)制日志仅父，備庫重放日志的方式異步復(fù)制。有復(fù)制時(shí)延

二進(jìn)制日志的開銷對主庫影響很小

復(fù)制通常為了通過備庫响鹃，拓展讀功能驾霜，寫操作不太適合擴(kuò)展

2.復(fù)制如何工作

主庫記錄二進(jìn)制日志（在事務(wù)完成前記錄）

備庫復(fù)制主庫日志到自己的中繼日志（備庫開啟io線程、主庫開啟日志轉(zhuǎn)儲線程）

備庫執(zhí)行中繼日志更新數(shù)據(jù)（備庫開啟sql線程）

瓶頸在于买置，備庫的sql線程是單線程串行執(zhí)行

B.配置復(fù)制

1.在每臺服務(wù)器上創(chuàng)建復(fù)制帳號

2.配置主庫和備庫

* 必須明確地指定一個唯一的服務(wù)器ID

* 有時(shí)候只開啟了二進(jìn)制日志粪糙，但卻沒有開啟log_slave_updates，可能會踫到一些奇怪的現(xiàn)象

* 如果可能的話忿项，最好使用read_only配置選項(xiàng)蓉冈，會阻止任何沒有特權(quán)權(quán)限的線程修改數(shù)據(jù)

3.通知備庫連接到主庫并從主庫復(fù)制數(shù)據(jù)

4.推薦的復(fù)制配置

C.復(fù)制的原理（重點(diǎn)）

1.基于語句的復(fù)制

* 5.0之前只支持基于語句的復(fù)制（也稱為邏輯復(fù)制），主庫會記錄那些造成數(shù)據(jù)更改的查詢轩触，當(dāng)備庫讀取并重放這些事件時(shí)寞酿，實(shí)際上只是把主庫上執(zhí)行過的SQL再執(zhí)行一遍

* 好處是實(shí)現(xiàn)相當(dāng)簡單，日志更加緊湊脱柱，不會占用太多帶寬

* 問題是基于語句的方式可能并不如其看起來那么便利伐弹，還存在一些無法被正確復(fù)制的SQL（如時(shí)間戳）；更新必須是串行的榨为，這需要更多的鎖

2.基于行的復(fù)制

* 5.1開始支持惨好，會將實(shí)際數(shù)據(jù)記錄在二進(jìn)制日志中

* 好處是可以正確地復(fù)制每一行煌茴，一些語句可以被更加有效地復(fù)制

* 如果使用全表更新，則開銷會很大日川，因?yàn)槊恳恍械臄?shù)據(jù)都會被記錄到二進(jìn)制日志中蔓腐，這使得二進(jìn)制日志事件非常龐大，并且會給主庫上記錄日志和復(fù)制增加額外的負(fù)載龄句，更慢的日志記錄則會降低并發(fā)度

3.基于行或基于語句：哪種更優(yōu)

* 基于語句的復(fù)制模式的優(yōu)點(diǎn)：當(dāng)主備的模式不同時(shí)回论，邏輯復(fù)制能夠在多種情況下工作；基于語句的方式執(zhí)行復(fù)制的過程基本上就是執(zhí)行SQL語句

* 基于語句的復(fù)制模式的缺點(diǎn)：很多情況下通過基于語句的模式無法正確復(fù)制分歇，如果正在使用觸發(fā)器或者存儲過程傀蓉，就不要使用基于語句的復(fù)制模式，除非能夠清楚地確定不會踫到復(fù)制的問題

* 基于行的復(fù)制模式的優(yōu)點(diǎn)：幾乎沒有基于行的復(fù)制模式無法處理的場景卿樱；可能減少鎖的使用僚害，并不要求這種強(qiáng)串行化是可重復(fù)的；會記錄數(shù)據(jù)變更繁调；占用更少的CPU萨蚕；能夠幫助更快地找到并解決數(shù)據(jù)不致的情況

* 基于行的復(fù)制模式的缺點(diǎn)：無法判斷執(zhí)行了哪些SQL；無法知道服務(wù)器在做什么蹄胰；在某些情況下岳遥，例如找不到要修改的行時(shí)，基于行的復(fù)制可能會導(dǎo)致復(fù)制停止

4.復(fù)制文件

* mysql-bin.index裕寨，二進(jìn)制日志文件

* mysql-relay-bin-index浩蓉，中繼日志的索引文件

* master.info，保存?zhèn)鋷爝B接到主庫所需要的信息

* relay-log.info宾袜，包含了當(dāng)前備庫復(fù)制的二進(jìn)制日志和中繼日志坐標(biāo)

5.發(fā)送復(fù)制事件到其他備庫：log_slave_updates捻艳，可以讓備庫變成其他服務(wù)器的主庫

6.復(fù)制過濾選項(xiàng)

* 在主庫上過濾記錄到二進(jìn)制日志中的事件

* 在備庫上過濾記錄到中繼日志的事件

D.復(fù)制拓?fù)洌ㄖ攸c(diǎn)）

1.基本原則：

* 一個MySQL備庫實(shí)例只能有一個主庫（不支持多主庫）

* 每個備庫都必須有一個唯一的服務(wù)器ID

* 一個主庫可以有多個備庫

2.一主庫多備庫

適用于少量寫，大量讀操作的場景庆猫，將讀分?jǐn)偟絺鋷?/b>

備庫既可以分?jǐn)傋x壓力认轨，也可作為災(zāi)難備份作用

3.主動-主動模式下的主主復(fù)制：一對主庫，配置相同

最大的問題在于如何接近沖突：auto_increment_increment和auto_increment_offset可以讓MySQL自動為INSERT語句選擇不互相沖突的值

總體來說不推薦

4.主動-被動模式下的主主復(fù)制：其中一臺服務(wù)器是只讀的被動服務(wù)器月培，但配置是對稱的（互為主備嘁字，只是只在一個服務(wù)器上進(jìn)行寫操作），使得故障轉(zhuǎn)移很方便杉畜。

應(yīng)用場景：優(yōu)化ALTER TABLE纪蜒。停止主動服務(wù)器a的備庫復(fù)制線程，在被動服務(wù)器b進(jìn)行alter操作此叠，然后交換角色纯续，最后在a啟動復(fù)制線程

5.擁有備庫的主主結(jié)構(gòu)：在4的基礎(chǔ)上，增加了冗余每個庫增加一個備庫，能夠消除站點(diǎn)單點(diǎn)失效的問題

6.環(huán)形復(fù)制：每個服務(wù)器都是在它之前的服務(wù)器的備庫杆烁，是在它之后的服務(wù)器的主庫牙丽，不推薦

7.分發(fā)主庫

背景：備庫過多時(shí)简卧，對主庫造成很大的負(fù)載兔魂。

分發(fā)主庫事實(shí)上也是一個備庫，提取和提供主庫的二進(jìn)制日志举娩。讓多個備庫連接分發(fā)主庫析校，分發(fā)主庫連接主庫

缺點(diǎn)：故障轉(zhuǎn)移時(shí)，無法使用備庫代替主庫铜涉。因?yàn)榉职l(fā)主庫導(dǎo)致備庫與原始二進(jìn)制日志坐標(biāo)不同

8.樹或金字塔形9.定制的復(fù)制方案

* 選擇性復(fù)制：配置replicate_wild_do_table

* 分離功能：OLTP智玻、OLAP

* 數(shù)據(jù)歸檔：在備庫上保留主庫上刪除過的數(shù)據(jù)

* 將備庫用作全文檢索

* 只讀備庫：read_only選項(xiàng)

* 模擬多主庫復(fù)制

* 創(chuàng)建日志服務(wù)器：創(chuàng)建沒有數(shù)據(jù)的日志服務(wù)器，更加容易重放并且/或者過濾二進(jìn)制日志事件

E.復(fù)制和容量規(guī)劃

1.寫操作通常是復(fù)制的瓶頸芙代，并且很難使用復(fù)制來擴(kuò)展寫操作

2.在構(gòu)建一個大型應(yīng)用時(shí)吊奢，有意讓服務(wù)器不被充分使用，這應(yīng)該是一種聰明并且蔓延的方式纹烹，尤其在使用復(fù)制的時(shí)候页滚，有多余容量的服務(wù)器可以更好地處理負(fù)載尖峰，也有更多能力處理慢速查詢和維護(hù)工作铺呵，并且能夠更好地跟上復(fù)制

3.主備延遲監(jiān)控：

粗略的方法裹驰，用show master status看主庫二進(jìn)制日志的位置和配置

F.復(fù)制管理和維護(hù)

1.在主庫上，可以使用SHOW MASTER STATUS命令來查看當(dāng)前主庫的二進(jìn)制日志位置和配置

2.從庫上片挂，使用SHOW SLAVE STATUS

十一幻林、可擴(kuò)展的MySQL

A.什么是可擴(kuò)展性

1.可擴(kuò)展性表明了當(dāng)需要增加資源以執(zhí)行更多工作時(shí)系統(tǒng)能夠獲得劃算的等同提升（equal bang for the buck）的能力，缺乏擴(kuò)展能力的系統(tǒng)在達(dá)到收益遞減的轉(zhuǎn)折點(diǎn)后音念，將無法進(jìn)一步增長

2.可擴(kuò)展性就是能夠通過增加資源來提升容量的能力

3.amdahl定律

B.擴(kuò)展MySQL

1..向上擴(kuò)展（垂直擴(kuò)展）意味著購買更多性能強(qiáng)悍的硬件

2.向外擴(kuò)展（橫向擴(kuò)展沪饺、水平擴(kuò)展）：復(fù)制、拆分闷愤、數(shù)據(jù)分片（重點(diǎn)Ｕ稀！８翁贰）

向外擴(kuò)展多個節(jié)點(diǎn)掘宪，所謂節(jié)點(diǎn)：可以是一個服務(wù)器，也可以是一個冗余系統(tǒng)（第十章）

* 按功能拆分（按職責(zé)拆分）攘烛，不同的節(jié)點(diǎn)執(zhí)行不同的任務(wù)

* 數(shù)據(jù)分片魏滚，把數(shù)據(jù)分割成一小片，或者一小塊坟漱，然后存儲到不同的節(jié)點(diǎn)中鼠次。如非必要，盡量不要分片

（復(fù)制與分片的區(qū)別：復(fù)制只能分擔(dān)讀請求，因?yàn)橐粋€主備系統(tǒng)只有主可以進(jìn)行寫操作腥寇；分片可以分擔(dān)讀寫成翩，相當(dāng)于不同的數(shù)據(jù)庫表）

* 選擇分區(qū)鍵原則（partitioning key）：盡可能讓分區(qū)足夠小，且避免過多的跨片查詢赦役。

分片小麻敌，方便管理，如轉(zhuǎn)移重新分配容量掂摔；但分片過小會產(chǎn)生太多的表术羔，跨片查詢的概率也變多

常用的部署分片方法：在不同的表面包含分片號，在查詢中使用占位符

*分片的方式：固定分片和動態(tài)分片（重點(diǎn)）

固定分片：對分區(qū)鍵的值進(jìn)行哈希計(jì)算乙漓。優(yōu)點(diǎn)：方便使用级历，效率高；缺點(diǎn)：無法負(fù)載均衡叭披，且修改分區(qū)策略困難寥殖，rehash遷移數(shù)據(jù)會導(dǎo)致大量更新

（可以參考一致性哈希算法）

動態(tài)分片：用一個表將分區(qū)鍵和分片id進(jìn)行記錄。優(yōu)點(diǎn)：靈活涩蜘，甚至可以分層分配嚼贡；缺點(diǎn)維護(hù)成本高

實(shí)際中常常將兩者結(jié)合使用，先哈希映射皱坛，然后動態(tài)分配實(shí)現(xiàn)負(fù)載均衡

*如何生產(chǎn)全局唯一ID（參考分布式唯一id编曼，雪花算法等）（重點(diǎn)）

單一數(shù)據(jù)庫使用auto_increment獲取自增ID

多臺數(shù)據(jù)庫服務(wù)器時(shí)：使用auto_increment_offset；以兩個服務(wù)器舉例剩辟，將自增幅度設(shè)為2掐场，一個offset為1，一臺offset設(shè)為2.

5.通過多實(shí)例擴(kuò)展

6.通過集群擴(kuò)展

7.向內(nèi)擴(kuò)展贩猎，對不再需要的數(shù)據(jù)進(jìn)行歸檔和清理

8.保持活躍數(shù)據(jù)獨(dú)立

C.負(fù)載均衡

1.在一個服務(wù)器集群中盡可能地平均負(fù)載量熊户，通常在服務(wù)器前端設(shè)置一個負(fù)載均衡器（硬件）

2.常見方式：

*直連法：

1）一在應(yīng)用層控制讀寫分離，讀用備庫寫用主庫吭服。缺點(diǎn)：復(fù)制時(shí)延可能帶來臟數(shù)據(jù)

可以基于版本控制嚷堡、主備時(shí)延檢測的方式來避免臟數(shù)據(jù)問題

2）dns，ip層修改

*LB中間件

缺點(diǎn)：常見的中間件都是基于http開發(fā)艇棕，mysql是基于tcp層的蝌戒，很多功能不適配

*常見負(fù)載均衡算法：隨機(jī)，輪詢沼琉，最少連接數(shù)北苟，最快響應(yīng)，哈希打瘪，權(quán)重設(shè)置友鼻。

*增加服務(wù)器傻昙，冷啟動問題

十二、高可用性

A.什么是高可用性

1.高可用性不是絕對的彩扔，只有相對更高的可用性妆档，100%的可用性是不可能達(dá)到的

2.可用性每提高一點(diǎn)，所花費(fèi)的成本都會遠(yuǎn)超之前虫碉，可用性的效果和開銷的比例并不是線性的

B.導(dǎo)致宕機(jī)的原因

1.運(yùn)行環(huán)境問題贾惦，最普遍的問題是磁盤空間耗盡

2.性能問題，最普遍的原因是運(yùn)行很糟糕的SQL蔗衡，或服務(wù)器BUG或錯誤的行為

3.糟糕的Schema和索引設(shè)計(jì)

4.復(fù)制問題通常由于主備數(shù)據(jù)不一致導(dǎo)致

5.數(shù)據(jù)丟失通常由于DROP TABLE的誤操作導(dǎo)致纤虽，并總是伴隨著缺少可用備份的問題

C.如何實(shí)現(xiàn)高可用性

1.可以通過同時(shí)進(jìn)行以下兩步來獲得高可用性

* 可以嘗試避免導(dǎo)致宕機(jī)的原因來減少宕機(jī)時(shí)間

* 盡量保證在發(fā)生宕機(jī)時(shí)能夠快速恢復(fù)

2.提升平均失效時(shí)間（MTBF）

* 對系統(tǒng)變更管理的缺失是所有導(dǎo)致宕機(jī)的事件中最普遍的原因

* 缺少嚴(yán)格的評估

* 沒有正確地監(jiān)控MySQL的相關(guān)信息

3.降低平均恢復(fù)時(shí)間（MTTR）

* 所有的宕機(jī)事件都是由多方面的失效聯(lián)合在一起導(dǎo)致的，可以通過利用合適的方法確保單點(diǎn)的安全來避免

D.避免單點(diǎn)失效

1.系統(tǒng)中任何不冗余的部分都是一個可能失效的單點(diǎn)

2.可以采用兩種方法來為系統(tǒng)增加冗余：增加空余容量和重復(fù)組件

3.共享存儲或磁盤復(fù)制

* 能夠?yàn)閿?shù)據(jù)庫服務(wù)器和存儲解耦合绞惦，通常使用的是SAN

* 兩個優(yōu)點(diǎn)：可以避免除存儲外的其他任何組件失效所引起的數(shù)據(jù)丟失，并為非存儲組件建立冗余提供可能

4.MySQL同步復(fù)制

* 當(dāng)使用同步復(fù)制時(shí)洋措，主庫上的事務(wù)只有在至少一個備庫上提交后才能認(rèn)為其執(zhí)行完成

* 完成了兩個目標(biāo)：當(dāng)服務(wù)器崩潰時(shí)沒有提交的事務(wù)會丟失济蝉，并且至少有一個備庫擁有實(shí)時(shí)的數(shù)據(jù)副本

* MySQL Cluster

* Percona XtraDB Cluster

5.基于復(fù)制的冗余

* 復(fù)制管理器是使用標(biāo)準(zhǔn)MySQL復(fù)制來創(chuàng)建冗余的工具

E.故障轉(zhuǎn)移和故障恢復(fù)

1.冗余一點(diǎn)也不會增加可用性或減少宕機(jī)，和故障轉(zhuǎn)移結(jié)合可以幫助更快地恢復(fù)菠发，故障轉(zhuǎn)移最重要的部分就是故障恢復(fù)

2.提升備庫或切換角色

3.虛擬IP地址或IP接管

4.中間件解決方案王滤，可以使用代理、端口轉(zhuǎn)發(fā)滓鸠、網(wǎng)絡(luò)地址轉(zhuǎn)換或者硬件負(fù)載均衡來實(shí)現(xiàn)故障轉(zhuǎn)移和故障恢復(fù)

5.在應(yīng)用中處理故障轉(zhuǎn)移

十三雁乡、云端的MySQL

A.云的優(yōu)點(diǎn)、缺點(diǎn)和相關(guān)誤解

1.優(yōu)點(diǎn)：

* 云是一種將基礎(chǔ)設(shè)施外包出去無須自己管理的方法

* 云一般是按照即用即付的方式支付

* 隨著供應(yīng)商發(fā)布新的服務(wù)和成本降低冬阳，云提供的價(jià)值越來越大

* 云能夠幫助你輕松地準(zhǔn)備好服務(wù)器和其他資源

* 云代表了對基礎(chǔ)設(shè)施的另一種思考方式——作為通過API來定義和控制的資源——支持更多的自動化操作

2.缺點(diǎn)：

* 資源是共享并且不可預(yù)測的

* 無法保證容量和可用性

* 虛擬的共享資源導(dǎo)致排查故障更加困難

B.MySQL在云端的經(jīng)濟(jì)價(jià)值

1.云托管比較適合尚處于初級階段的企業(yè)仗处，或者那些持續(xù)接觸新概念并且本質(zhì)上是以適用為主的企業(yè)

2.大量使用的策略是盡可能又快又便宜地開發(fā)和發(fā)布應(yīng)用

3.運(yùn)行不是很重要的基礎(chǔ)設(shè)施

C.云中的MySQL的可擴(kuò)展性和高可用性

1.數(shù)據(jù)庫通常是一個應(yīng)用系統(tǒng)中主要或唯一的有狀態(tài)并且持久化的組件

2.MySQL并不具備在一個無共享集群中的對等角色服務(wù)器之間遷移的能力

D.四種基礎(chǔ)資源

1.CPU通常少且慢

2.內(nèi)在大小受限制

3.I/O的吞吐量廊蜒、延遲以及一致性受到限制

4.網(wǎng)絡(luò)性能還比較好

E.MySQL在云主機(jī)上的性能

1.需要高并發(fā)的工作負(fù)載并不是非常適合云計(jì)算

2.那些需要大量I/O的工作負(fù)載在云中并不總是表現(xiàn)很好

F.MySQL數(shù)據(jù)庫即服務(wù)（DBaaS）

1.將數(shù)據(jù)庫本身作為云資源

十四、應(yīng)用層優(yōu)化

A.常見問題

1.什么東西在消耗系統(tǒng)中每臺主機(jī)的CPU珠月、磁盤、網(wǎng)絡(luò)楔敌，以及內(nèi)存資源啤挎？這些值是否合理？如果不合理卵凑，對應(yīng)用程序做基本的檢查庆聘，看什么占用了資源

2.應(yīng)用真是需要所有獲取到的數(shù)據(jù)嗎？

3.應(yīng)用在處理本應(yīng)由數(shù)據(jù)庫處理的事情嗎勺卢，或者反過來伙判？

4.應(yīng)用執(zhí)行了太多的查詢？

5.應(yīng)用執(zhí)行的查詢太少了值漫？

6.應(yīng)用創(chuàng)建了沒必要的MySQL連接嗎澳腹？

7.應(yīng)用對一個MySQL實(shí)例創(chuàng)建連接的次數(shù)太多了嗎织盼？

8.應(yīng)用做了太多的“垃圾”查詢？

9.應(yīng)用使用了連接池嗎酱塔？這既可能是好事沥邻，也可能是壞事

10.應(yīng)用是否使用長連接？

11.應(yīng)用是否在不使用的時(shí)候還保持連接撕開羊娃？

B.Web服務(wù)器問題

1.最常見的問題是保持它的進(jìn)程的存活（alive）時(shí)間過長唐全，或者在各種不同的用途下混合使用，而不是分別對不同類型的工作進(jìn)行優(yōu)化

2.如果用一個通用目的的Apache配置直接用于Web服務(wù)蕊玷，最后很可能產(chǎn)生很多重量級的Apache進(jìn)程

3.不要使用Apache來做靜態(tài)內(nèi)容服務(wù)邮利，或者至少和動態(tài)服務(wù)使用不同的Apache實(shí)例

4.進(jìn)程存活時(shí)間變短策略：

* 不要讓Apache填鴨式地服務(wù)客戶端

* 打開gzip壓縮

* 不要為用于長距離連接的Apache配置啟用Keep-Alive選項(xiàng)

C.緩存

1.被動緩存除了存儲和返回?cái)?shù)據(jù)外不做任何事情；主動緩存在訪問未命中時(shí)做一些額外工作

2.應(yīng)用可以緩存部分計(jì)算結(jié)果垃帅，所以應(yīng)用層緩存可能比更低層次的緩存更有效延届，可以節(jié)省兩方面的工作：獲取數(shù)據(jù)以及基于這些數(shù)據(jù)進(jìn)行計(jì)算，重點(diǎn)是緩存命中率可能更低贸诚，并且可能使用較多的內(nèi)存

3.應(yīng)用層緩存：

* 本地緩存

* 本地共享內(nèi)存緩存

* 分布式內(nèi)存緩存

* 磁盤上的緩存

4.緩存控制策略

* TTL（time to live方庭，存活時(shí)間）

* 顯式失效，如果不能接受臟數(shù)據(jù)酱固，那么進(jìn)程在更新原始數(shù)據(jù)時(shí)需要同時(shí)使緩存失效

* 讀時(shí)失效械念，在更改舊數(shù)據(jù)時(shí)，為了避免要同時(shí)失效派生出來的臟數(shù)據(jù)运悲，可以在緩存中保存一些信息龄减，當(dāng)從緩存中讀數(shù)據(jù)時(shí)可以利用這些信息判斷數(shù)據(jù)是否已經(jīng)失效

5.可以在后臺預(yù)先請求一些頁面，并將結(jié)果存為靜態(tài)頁面班眯，好處：

* 應(yīng)用代碼沒有復(fù)雜的命中和未命中處理路徑

* 當(dāng)未命中的處理路徑慢得不可接受時(shí)希停，這種方案可以很好地工作

* 預(yù)生成內(nèi)容可以避免在緩存未命中時(shí)導(dǎo)致的雪崩效應(yīng)

D.MySQL的替代品

1.搜索：Lucene和Sphinx

2.簡單的鍵值存儲：Redis

3.結(jié)構(gòu)化數(shù)據(jù)：Hadoop

十五、備份與恢復(fù)

A.為什么要備份

1.災(zāi)難恢復(fù)

2.人們改變想法

3.審計(jì)

4.測試

B.定義恢復(fù)需求

1.規(guī)劃備份和恢復(fù)策略時(shí)鳖敷，有兩個重要的需求：恢復(fù)點(diǎn)目標(biāo)（PRO）和恢復(fù)時(shí)間目標(biāo)（RTO）

C.設(shè)計(jì)MySQL備份方案

1.建議

* 在生產(chǎn)實(shí)踐中脖苏，對于大數(shù)據(jù)庫來說，物理備份是必需的：邏輯備份太慢并受到資源限制定踱，從邏輯備份中恢復(fù)需要很長時(shí)間

* 保留多個備份集

* 定期從邏輯備份（或者物理備份）中抽取數(shù)據(jù)進(jìn)行恢復(fù)測試

* 保存二進(jìn)制日志以用于基于故障時(shí)間點(diǎn)的恢復(fù)

* 完全不借助備份工具本身來監(jiān)控備份和備份的過程

* 通過演練整個恢復(fù)過程來測試備份和恢復(fù)

* 對安全性要仔細(xì)考慮

2.如果可能棍潘，關(guān)閉MySQL做備份是最簡單最安全的，需要考慮：鎖時(shí)間崖媚、備份時(shí)間亦歉、備份負(fù)載、恢復(fù)時(shí)間

3.邏輯備份優(yōu)點(diǎn)：

* 可以用編輯器或像grep和sed之類的命令查看和操作的普通文件

* 恢復(fù)非常簡單

* 可能通過網(wǎng)絡(luò)來備份和恢復(fù)

* 可以在類似Amazon RDS這樣不能訪問底層文件系統(tǒng)的系統(tǒng)中使用

* 非常靈活

* 與存儲引擎無關(guān)

* 有助于避免數(shù)據(jù)損壞

4.邏輯備份的缺點(diǎn)：

* 必須由數(shù)據(jù)庫服務(wù)器完成生成邏輯備份的工作

* 邏輯備份在某些場景下比數(shù)據(jù)庫文件本身更大

* 無法保證導(dǎo)出后再還原出來的一定是同樣的數(shù)據(jù)

* 從邏輯備份中還原需要MySQL加載和解釋語句

5.物理備份優(yōu)點(diǎn)：

* 基于文件的備份畅哑，只需要將需要的文件復(fù)制到其他地方即可

* 恢復(fù)簡單

* InnoDB和MyISAM的物理備份非常容易跨平臺

6.物理備份缺點(diǎn)：

* InnoDB的原始文件通常比相應(yīng)的邏輯備份要大得多

* 物理備份不總是可以跨平臺

7.除非經(jīng)過測試肴楷，不要假定備份是正常的

8.建議混合使用物理和邏輯兩種方式來做備份

9.MySQL備份需要考慮的幾點(diǎn)：

* 非顯著數(shù)據(jù)

* 代碼

* 復(fù)制配置

* 服務(wù)器配置

* 選定的操作系統(tǒng)

10.差異備份是對自上次全備份后所有改變的部分做備份，而增量備份則是自從任意類型的上次備份后所有修改做的備份

11.差異荠呐、增量備份的建議：

* 使用Percona XtraBackup和MySQL Enterprise Backup中的增量備份特性

* 備份二進(jìn)制日志赛蔫，每次備份后FLUSH LOGS

* 不要備份沒有改變的表

* 不要備份沒有改變的行

* 某些數(shù)據(jù)根本不需要備份

* 備份所有的數(shù)據(jù)砂客，然后發(fā)送到一個有去重特性的目的地

12.數(shù)據(jù)一致性：當(dāng)備份時(shí)，應(yīng)該考慮是否需要數(shù)據(jù)在指定時(shí)間點(diǎn)一致

13.文件一致性：每個文件的內(nèi)部一致性

14.從備庫中備份最大的好處是可以不干擾主庫呵恢，故意將一個備庫延時(shí)一段時(shí)間對于某些災(zāi)難場景非常有用

D.管理和備份二進(jìn)制日志

1.expire_log_days變量MySQL定期清理日志

E.備份數(shù)據(jù)

1.生成邏輯備份

* SQL導(dǎo)出：mysqldump方式

* 符號分隔文件備份：使用SELECT INTO OUTFILE以符號分隔文件格式創(chuàng)建數(shù)據(jù)的邏輯備份

2.文件系統(tǒng)快照

* 支持快照的文件系統(tǒng)和設(shè)備包括FreeBSD的文件系統(tǒng)鞠值、ZFS文件系統(tǒng)、GNU/Linux的邏輯卷管理（LVM）渗钉，以及許多的SAN系統(tǒng)和文件存儲解決方案

F.從備份中恢復(fù)

1.恢復(fù)步驟：

* 停止MySQL服務(wù)器

* 記錄服務(wù)器的配置和文件權(quán)限

* 將數(shù)據(jù)從備份中移到MySQL數(shù)據(jù)目錄

* 改變配置

* 改變文件權(quán)限

* 以限制訪問模式重啟服務(wù)器彤恶，等待完成啟動

* 載入邏輯備份文件

* 檢查和重放二進(jìn)制日志

* 檢測已經(jīng)還原的數(shù)據(jù)

* 以完全權(quán)限重啟服務(wù)器

G.備份和恢復(fù)工具

1.MySQL Enterprise Backup

2.Percona XtraBackup

3.mylvmbackup

4.Zmanda Recovery Manager

5.mydunper

6.mysqldump