一战得、MySQL架構與歷史

A.并發(fā)控制

1.共享鎖（shared lock，讀鎖）：共享的庸推，相互不阻塞的常侦。

2.排他鎖（exclusive lock，寫鎖）：排他的予弧，一個寫鎖會阻塞其他的寫鎖和讀鎖刮吧。

B.事務

1.事務ACID

* 原子性（atomicity）一個事務必須被視為一個不可分割的最小工作單元，整個事務中所有操作要么全部提交成功掖蛤，要么全部失敗回滾杀捻，對于一個事務來說，不可能只執(zhí)行其中的一部分操作蚓庭；

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

* 隔離性（isolation）一個事務所做的修改在最終提交以前，對其他事務是不可見的器赞；

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

2.四種隔離級別

* READ UNCOMMITTED（未提交讀）港柜，事務中的修改请契，即使沒有提交咳榜，對其他事務也都是可見的，事務可以讀取未提交的數(shù)據(jù)爽锥，也被稱為臟讀（Dirty Read）涌韩，這個級別會導致很多問題；

* READ COMMITTED（提交讀）氯夷，大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別臣樱，一個事務開始時，只能“看見”已經(jīng)提交的事務所做的修改腮考，一個事務從開始直到提交之前雇毫，所做的任何修改對其他事務都是不可見的，也叫不可重復讀（nonrepeatable read）踩蔚，有可能出現(xiàn)幻讀（Phantom Read）棚放，指的是當某個事務在讀取某個范圍內(nèi)的記錄時，另外一個事務又在該范圍內(nèi)插入了新的記錄馅闽，當之前的事務再次讀取該范圍的記錄時席吴，會產(chǎn)生幻行（Phantom Row）；

* REPEATABLE READ（可重復讀）捞蛋，通過InnoDB和XtraDB存儲引擎孝冒，是MySQL的默認事務隔離級別；

* SERIALIZABLE（可串行化）最高級別拟杉，通過強制事務串行執(zhí)行庄涡，避免了幻讀問題，會在讀取的每一行數(shù)據(jù)上都加鎖搬设，可能導致大量的超時和鎖爭用的問題穴店。

3.死鎖

指兩個或多個事務在同一資源上相互占用，并請求鎖定對方占用的資源拿穴，從而導致惡性循環(huán)的現(xiàn)象泣洞。

4.事務日志

存儲引擎在修改表的數(shù)據(jù)時只需要修改其內(nèi)存拷貝，再把該修改行為記錄到持久在硬盤上的事務日志中默色，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤球凰。事務日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢地刷回到磁盤腿宰，稱為預寫式日志（Write-Ahead Logging）呕诉。

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

1.多版本并發(fā)控制（MVCC）

行級鎖的一個變種，但是它在很多情況下避免了加鎖操作吃度，因此開銷更低甩挫。雖然實現(xiàn)機制有所不同，但大都實現(xiàn)了非阻塞的讀操作椿每，寫操作也只鎖定必要的行伊者。

2.MVCC的實現(xiàn)

通過保存數(shù)據(jù)在某個時間點的快照來實現(xiàn)的英遭，有樂觀和悲觀兩種，只在REPEATABLE READ和READ COMMITTED兩個隔離級別下工作亦渗。

D.MySQL的存儲引擎

1.MySQL的.frm文件保存表的定義贪绘，SHOW TABLE STATUS顯示表的相關信息。

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

3.不要輕易相信MyISAM比InnoDB快之類的經(jīng)驗之談均函，這個結論并不是絕對的亿虽。

二、MySQL基準測試

A.為什么需要基準測試

基準測試可以觀察系統(tǒng)在不同壓力下的行為苞也，評估系統(tǒng)的容量洛勉，掌握哪些是重要的變化，或者觀察系統(tǒng)如何處理不同的數(shù)據(jù)如迟。

B.基準測試的策略

1.兩種主要的策略

* 針對整個系統(tǒng)的整體測試（集成式full-stack）收毫；

* 單獨測試MySQL（單組件式single-component）。

2.測試何種指標

* 吞吐量殷勘，指單位時間內(nèi)的事務處理數(shù)此再，常用的測試單位是每秒事務數(shù)（TPS），或每分鐘事務數(shù)（TPM）玲销；

* 響應時間或者延遲输拇，用于測試任務所需的整體時間，根據(jù)具體的應用贤斜，測試的時間單位可能是微秒策吠、毫秒、秒或者分鐘瘩绒。通常使用百分比響應時間（percentile response time）來替代最大響應時間猴抹；

* 并發(fā)性，需要關注的是正在工作中的并發(fā)操作锁荔，或者是同時工作中的線程數(shù)或者連接數(shù)蟀给，在測試期間記錄MySQL數(shù)據(jù)庫的Threads_running狀態(tài)值；

* 可擴展性阳堕，給系統(tǒng)增加一倍的工作坤溃，在理想情況下就能獲得兩倍的效果（即吞吐量增加一倍），對于容量規(guī)范非常有用嘱丢，可以提供其他測試無法提供的信息薪介，來幫助發(fā)現(xiàn)應用的瓶頸。

C.基準測試方法

1.需要避免的一些常見錯誤：

* 使用真實數(shù)據(jù)的子集而不是全集越驻；

* 使用錯誤的數(shù)據(jù)分布汁政；

* 使用不真實的分布參數(shù)道偷；

* 在多用戶場景中，只做單用戶測試记劈；

* 在單服務器上測試分布式應用勺鸦；

* 與真實用戶行為不匹配；

* 反復執(zhí)行同一個查詢目木；

* 沒有檢查錯誤换途；

* 忽略了系統(tǒng)預熱（warm up）的過程；

* 使用默認的服務器配置刽射；

* 測試時間太短军拟。

2.應該建立將參數(shù)和結果文檔化的規(guī)范，每一輪測試都必須進行詳細記錄誓禁。

3.基準測試應該運行足夠長的時間懈息，需要在穩(wěn)定狀態(tài)下測試并觀察。

4.在執(zhí)行基準測試時摹恰，需要盡可能多地收集被測試系統(tǒng)的信息辫继。

5.自動化基準測試可以防止測試人員偶爾遺漏某些步驟，或者誤操作俗慈，另外也有助于歸檔整個測試過程姑宽，可以選擇shell、php闺阱、perl等低千，要盡可能使所有測試過程都自動化，包括裝載數(shù)據(jù)馏颂、系統(tǒng)預熱示血、執(zhí)行測試、記錄結果等救拉。

D.基準測試工具

1.集成式測試工具：

* ab难审，測試HTTP服務器每秒最多可以處理多少請求；

* http_load亿絮，和ab類似告喊，但更加靈活；

* jMeter派昧，可以加載其他應用并測試其性能黔姜。

2.單組件式測試工具

* mysqlslap，可以模擬服務器的負載蒂萎，并輸出計時信息秆吵；

* MySQL Benchmark Suite(sql-bench)，單線程的五慈，主要用于測試服務器執(zhí)行查詢的速度纳寂；

* Super Smack主穗，提供壓力測試和負載生成，是一個復雜而強大的工具毙芜，可以模擬多用戶訪問忽媒，可以加載測試數(shù)據(jù)到數(shù)據(jù)庫，并支持使用隨機數(shù)據(jù)填充測試表腋粥；

* Database Test Suite晦雨，類似某些工業(yè)標準測試的測試工具集；

* Percona's TPCC-MySQWL Tool隘冲；

* sysbench闹瞧，多線程系統(tǒng)壓測工具，可以根據(jù)影響數(shù)據(jù)庫服務器性能的各種因素來評估系統(tǒng)的性能对嚼。

三、服務器性能剖析

A.性能優(yōu)化簡介

1.性能绳慎，為完成某件任務所需要的時間度量纵竖，性能即響應時間，這是非常重要的原則杏愤。

2.如果目標是降低響應時間靡砌，就需要理解為什么服務器執(zhí)行查詢需要這么多時間，然后去減少或者消除那些對獲得查詢結果來說不必要的工作珊楼。無法測量就無法有效地優(yōu)化通殃。

3.性能剖析（profiling）是測量和分析時間花費在哪里的主要方法，一般有兩個步驟：測量任務所花費的時間厕宗，對結果進行統(tǒng)計和排序画舌。

B.對應用程序進行性能剖析

1.性能瓶頸可能的影響因素：

* 外部資源吨些；

* 應用需要處理大量的數(shù)據(jù)妻顶；

* 在循環(huán)中執(zhí)行昂貴的操作营曼；

* 使用了低效的算法搔弄。

2.PHP性能剖析工具：New Relic颊郎、xhprof径筏、Ifp腻扇。

C.剖析MySQL查詢

1.剖析服務器負載

* 慢查詢?nèi)罩荆?.1后long_query_time為0可以捕獲所有的查詢睛廊，查詢的響應時間單位可以做到微秒級膜楷；

* 生成剖析報告：pt-query-digest旭咽。

2.剖析單條查詢：

* SHOW PROFILES；

* SHOW [GLOBAL] STATUS;赌厅，返回一些計數(shù)器穷绵。

D.診斷間歇性問題

1.盡量不要用試錯的方式來解決問題，如果一時無法定位特愿，可能是測量的方式不正確请垛，或者測量的點選擇有誤催训，或者使用的工具不合適。

2.確定單條查詢問題還是服務器問題：

* 使用SHOW GLOBAL STATUS宗收；

* 使用SHOW PROCESSLIST漫拭；

* 使用查詢?nèi)罩荆?/p>

* 理解發(fā)現(xiàn)的問題：使得gnuplot或R，或其他繪圖工具將結果繪制成圖形混稽。

3.捕獲診斷數(shù)據(jù)

* 診斷觸發(fā)器：在問題出現(xiàn)時能夠捕獲數(shù)據(jù)的基礎采驻，有兩個常見問題可能導致無法達到預期的結果：誤報（false positive）或者漏檢（false negative）,pt-stalk工具；

* 收集數(shù)據(jù)：盡可能收集所有能收集的數(shù)據(jù)匈勋，但只在需要的時間段內(nèi)收集礼旅，oprofile、strace洽洁、tcpdump痘系、GDB堆棧跟蹤、pt-collect饿自、pt-stalk汰翠；

* 解釋結果數(shù)據(jù)：pt-mysql-summary、pt-summary輸出結果打包昭雌，pt-sift得到樣本匯總信息复唤，pt-pmp。

E.其他剖析工具

1.使用USER_STATISTICS表烛卧。

2.使用strace佛纫，可以調(diào)查系統(tǒng)調(diào)用的情況。

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

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

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

* 更小的通常更好呈宇；

* 簡單就好；

* 盡量避免NULL局雄。

2.應該盡量只在對小數(shù)進行精確計算時才使用DECIMAL攒盈，使用int類型通過程序控制單位效果更好。

3.使用VARCHAR合適的情況：字符串列的最大長度比平均長度大很多哎榴；列的更新很少型豁，所以碎片不是問題；使用了像UTF-8這樣復雜的字符集尚蝌，每個字符都使用不同的字節(jié)數(shù)進行存儲迎变。

4.CHAR適合存儲很短的字符串，或者所有值都接近同一個長度飘言；不容易產(chǎn)生碎片衣形，在存儲空間上更有效率。

5.通常應該盡量使用TIMESTAMP，它比DATETIME空間效率更高谆吴。

B.MySQL schema設計中的陷阱

1.不好的設計：

* 太多的列倒源；

* 太多的關聯(lián)；

* 全能的枚舉句狼；

* 變相的枚舉笋熬；

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

C.范式和反范式

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

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

* 當數(shù)據(jù)較好地范式化時胳螟，就只有很少或者沒有重復數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)筹吐；

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

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

2.范式化設計的缺點是通常需要關聯(lián)。

3.反范式的優(yōu)點：避免關聯(lián)洋侨，避免了隨機I/O舍扰，能使用更有效的索引策略。

D.緩存表和匯總表

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

2.物化視圖审丘，MySQL并不原生支持吏够，F(xiàn)lexviews。

3.如果應用在表中保存計數(shù)器滩报，則在更新計數(shù)器時可能踫到并發(fā)問題锅知，創(chuàng)建一張獨立的表存儲計數(shù)器，可以幫助避免緩存失效脓钾。

* 解決獨立表并發(fā)問題可以建多行售睹，根據(jù)id隨機更新，然后統(tǒng)計時sum()可训；

* 按天或小時可以單獨建行昌妹，舊時間可定時任務合并到統(tǒng)一的一行。

E.加快ALTER TABLE操作的速度

1.兩種方式：

* 一是在一臺不提供服務的機器上執(zhí)行ALTER TABLE操作握截，然后和提供服務的主庫進行切換飞崖；

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

2.快速創(chuàng)建MyISAM索引，先禁用索引胯努，導入數(shù)據(jù)牢裳，然后重新啟用索引逢防。

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

A.索引基礎

1.索引可以包含一個或多個列的值蒲讯，如果索引包含多個列辜伟，那么列的順序也十分重要，因為MySQL只能高效地使用索引的最左前綴列导狡。

2.ORM工具能夠產(chǎn)生符合邏輯的偎痛、合法的查詢踩麦，除非只是生成非澄角基本的查詢卢肃，否則它很難生成適合索引的查詢。

3.在MySQL中幅垮，索引是在存儲引擎層而不是服務器層實現(xiàn)的讳侨，所以，并沒有統(tǒng)一的索引標準：不同存儲引擎的索引的工作方式并不一樣甘桑，也不是所有的存儲引擎都支持所有類型的索引铆帽。

4.B-Tree意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同，能夠加快訪問數(shù)據(jù)的速度，從索引的根節(jié)點開始進行搜索糟红，適用于全鍵值准浴、鍵值范圍或鍵前綴查找求橄。

5.B-Tree索引的限制：

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

* 不能跳過索引中的列豹爹；

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

6.哈希索引（hash index）基于哈希表實現(xiàn)权她，只有精確匹配索引所有列的查詢才有效蝴罪，只有Memory引擎顯式支持哈希索引。

7.哈希索引的限制：

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

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

* 哈希索引也不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內(nèi)容來計算哈希值的省骂；

* 只支持等值比較查詢温学，不支持任何范圍查詢逃延；

* 訪問哈希索引的數(shù)據(jù)非常快，除非有很多哈希沖突；

* 如果哈希沖突很多的話，一些索引維護操作的代價也會很高。

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

9.全文索引昼弟，適用于MATCH AGAINST操作变骡，而不是普通的WHERE條件操作旬盯。

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

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

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

* 索引可以幫助服務器避免排序和臨時表某弦；

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

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

* 索引將相關的記錄放到一起則獲得一星拣度；

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

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

C.高性能的索引策略

1.獨立的列：如果查詢中的列不是獨立的杜漠，則MySQL不會使用索引戴陡」埃“獨立的列”是指索引列不能是表達式的一部分，也不能是函數(shù)的參數(shù)晰房。

2.前綴索引和索引選擇性验夯。

* 通常可以索引開始的部分字符，可以大大節(jié)約索引空間，但也會降低索引的選擇性怔软；

* 索引的選擇性是指腻豌，不重復的索引值（也稱為基數(shù)惹骂，cardinality）和數(shù)據(jù)表的記錄總數(shù)（#T）的比值，范圍從1/#T到1之間，選擇性越高則查詢效率越高乳蛾，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行十嘿；

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

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

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

* 在一個多列B-Tree索引中久脯，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列；

* 將選擇性最高的列放到索引最前列。

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

* 最好避免隨機的（不連續(xù)且值的分布范圍非常大）聚簇索引，特別是對于I/O密集型的應用做粤。

5.覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，就稱為覆蓋索引灼舍。

* 覆蓋索引必須要存儲索引列的值尤辱。

6.如果EXPLAIN出來的type列的值為“index”塔粒，則說明MySQL使用了索引掃描來做排序圃酵。

7.壓縮（前綴）索引，默認只壓縮字符串捌锭，減少索引大小俘陷，對于CPU密集型應用，因為掃描需要隨機查找观谦，壓縮索引在MyISAM上要慢好幾倍。

8.重復索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引豁状，應該避免這樣創(chuàng)建重復索引捉偏。

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

D.維護索引和表

1.CHECK TABLE檢查表是否損壞泻红，ALTER TABLE innodb_tb1 ENGINE=INNODB;修復表夭禽。

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

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

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

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

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

A.為什么查詢速度會慢

1.如果要優(yōu)化查詢酷麦，實際上要優(yōu)化其子任務矿卑，要么消除其中一些子任務，要么減少子任務的執(zhí)行次數(shù)沃饶，要么讓子任務運行得更快母廷。

2.查詢的生命周期大致可以按照順序來看：從客戶端，到服務器糊肤，然后在服務器上進行解析琴昆，生成執(zhí)行計劃，執(zhí)行馆揉，并返回結果給客戶端业舍。

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

1.兩個分析步驟：

* 確認應用程序是否在檢索大量超過需要的數(shù)據(jù)；

* 確認MySQL服務器層是否在分析大量超過需要的數(shù)據(jù)行升酣。

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

* 查詢不需要的記錄舷暮；

* 多表關聯(lián)并返回全部列；

* 總是取出全部列噩茄；

* 重復查詢相同的數(shù)據(jù)下面。

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

* 查詢開銷三個指標：響應時間、掃描的行數(shù)绩聘、返回的行數(shù)沥割；

* 響應時間：服務時間和排隊時間之和，“快速上限估計”法君纫；

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

* 訪問類型：EXPLAIN中的type列反應了訪問類型蓄髓；通過增加合適的索引叉庐；

* 三種方式應用WHERE條件：在索引中使用WHERE條件來過濾不匹配的記錄；使用索引覆蓋掃描（Extra中出現(xiàn)Using index）來返回記錄会喝，直接從索引中過濾不需要的記錄并返回命中結果陡叠；從數(shù)據(jù)表中返回數(shù)據(jù)，然后過濾不滿足條件的記錄（Extra中出現(xiàn)Using Where）肢执；

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

C.重構查詢的方式

1.MySQL從設計上讓連接和斷開連接都很輕量級兴溜，在返回一個小的查詢結果方面很高效侦厚。

2.切分查詢，將大查詢切分成小查詢拙徽，每個查詢功能完全一樣刨沦，只完成一小部分，每次只返回一小部分查詢結果膘怕，可以避免鎖住很多數(shù)據(jù)想诅、占滿事務日志、耗盡系統(tǒng)資源岛心、阻塞很多小的但重要的查詢来破。

3.分解關聯(lián)查詢優(yōu)勢：

* 讓緩存的效率更高；

* 將查詢分解后忘古，執(zhí)行單個查詢可以減少鎖的競爭徘禁；

* 在應用層做關聯(lián)，可以更容易對數(shù)據(jù)庫進行拆分存皂，更容易做到高性能和可擴展晌坤；

* 查詢本身效率也可能會有所提升匠抗；

* 可以減少冗余記錄的查詢宁昭；

* 相當于在應用中實現(xiàn)了哈希關聯(lián)霸饲，而不是使用MySQL的嵌套循環(huán)關聯(lián)。

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

* 當應用能夠方便地緩存單個查詢的結果的時候疤孕；

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

* 當能夠使用IN()的方式代替關聯(lián)查詢的時候央拖；

* 當查詢中使用同一個數(shù)據(jù)表的時候祭阀。

D.查詢執(zhí)行的基礎

1.查詢執(zhí)行路徑

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

* 服務器先檢查查詢緩存鲜戒，如果命中則立刻返回专控，否則進入下一階段；

* 服務器端進行SQL解析遏餐、預處理伦腐，再由優(yōu)化器生成對應的執(zhí)行計劃；

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

* 將結果返回給客戶端。

2.MySQL客戶端和服務器之間的通信協(xié)議是“半雙工”的粹庞，無法將一個消息切成小塊獨立來發(fā)送咳焚，沒法進行流量控制，一旦一端開始發(fā)生消息庞溜，另一端要接收完整個消息才能響應它

3.MySQL通常需要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶端才能釋放這條查詢所占用的資源革半，所以接收全部結果并緩存通常可以減少服務器的壓力

4.查詢狀態(tài)，SHOW FULL PROCESSLIST命令查看：

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

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

* Locked，在MySQL服務器層赏胚，該線程正在等待表鎖

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

* Copying to tmp table [on disk]觉阅，線程正在執(zhí)行查詢崖疤，并且將其結果集都復制到一個臨時表中，要么是在做GROUP BY操作典勇，要么是文件排序操作劫哼，或者是UNION操作

* Sorting result，線程正在對結果集進行排序

* Sending data割笙，線程可能在多個狀態(tài)之間傳送數(shù)據(jù)权烧，或者在生成結果集，或者在向客戶端返回數(shù)據(jù)

5.語法解析器和預處理伤溉，通過關鍵字將SQL語句進行解析般码，并生成一棵對應的“解析樹”，解析器將使用MySQL語法規(guī)則驗證和解析查詢乱顾，預處理器則根據(jù)一些MySQL規(guī)則進一步檢查解析樹是否合法

6.查詢優(yōu)化器板祝，找到最好的執(zhí)行計劃，使用基本成本的優(yōu)化器走净，將嘗試預測一個查詢使用某種執(zhí)行計劃時的成本券时，并選擇其中成本最小的一個，使用SHOW STATUS LIKE 'Last_query_cost';查看需要多少個數(shù)據(jù)頁的隨機查找

7.導致MySQL查詢優(yōu)化器選擇錯誤的原因：

* 統(tǒng)計信息不準確伏伯，Innodb不能維護一個數(shù)據(jù)表的行數(shù)的精確統(tǒng)計信息

* 執(zhí)行計劃中的成本估算不等同于實際執(zhí)行的成本

* MySQL的最優(yōu)可能和你想的最優(yōu)不一樣

* MySQL從不考慮其他并發(fā)執(zhí)行的查詢

* MySQL也并不是任何時候都是基于成本的優(yōu)化

* MySQL不會考慮不受其控制的操作的成本

* 優(yōu)化器有時候無法去估算所有可能的執(zhí)行計劃

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

* 重新定義關聯(lián)表的順序

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

* 使用等價變換規(guī)則

* 優(yōu)化COUNT()橘洞、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”

* 預估并轉化為常數(shù)表達式说搅，當檢測到一個表達式可以轉化為常數(shù)的時候炸枣，就會一直把該表達式作為常數(shù)進行優(yōu)化處理

* 覆蓋索引掃描，當索引中的列包含所有查詢中需要使用的列的時候蜓堕，就可以使用索引返回需要的數(shù)據(jù)抛虏，而無須查詢對應的數(shù)據(jù)行

* 子查詢優(yōu)化

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

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

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

9.在服務器層有查詢優(yōu)化器峰髓，卻沒有保存數(shù)據(jù)和索引的統(tǒng)計信息，統(tǒng)計信息由存儲引擎實現(xiàn)息尺，不同的存儲引擎可能會存儲不同的統(tǒng)計信息

10.在MySQL中携兵，每一個查詢，每一個片段（包括子查詢搂誉，甚至基于單表的SELECT）都可能是關聯(lián)

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

12.MySQL對任何關聯(lián)都執(zhí)行“嵌套循環(huán)關聯(lián)”操作炭懊，即MySQL先在一個表中循環(huán)取出單條數(shù)據(jù)并级，然后再嵌套到下一個表中尋找匹配的行，依次下去侮腹，直到找到所有表中匹配的行為止

13.全外連接就無法通過嵌套循環(huán)和回溯的方式完成嘲碧，當發(fā)現(xiàn)關聯(lián)表中沒有找到任何匹配行的時候，則可能是因為關聯(lián)恰好從一個沒有任何匹配的表開始父阻，MySQL不支持全外連接

14.關聯(lián)查詢優(yōu)化器愈涩，會嘗試在所有的關聯(lián)順序中選擇一個成本最小的來生成執(zhí)行計劃樹，如果可能加矛，優(yōu)化器會遍歷每一個表然后逐個做嵌套循環(huán)計算每一棵可能的執(zhí)行樹的成本履婉，最后返回一個最優(yōu)的執(zhí)行計劃

15.如果有超過n個表的關聯(lián)，那么需要檢查n的階乘關聯(lián)順序荒椭，稱為“搜索空間”谐鼎，搜索空間的增長速度非常快

16.無論如何排序都是一個成本很高的操作趣惠，所以從性能角度考慮，應盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進行排序

17.當不能使用索引生成排序結果的時候身害，MySQL需要自己進行排序味悄，如果數(shù)據(jù)量小則在內(nèi)存中進行，如果數(shù)據(jù)量大則需要使用磁盤塌鸯，MySQL將這個過程稱為文件排序（filesort）侍瑟，即使完全是內(nèi)存排序不需要任何磁盤文件時也是如此

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

1.關聯(lián)子查詢：MySQL的子查詢實現(xiàn)得非常糟糕，最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句丙猬，使用GROUP_CONCAT()在IN()中構造一個由逗號分隔的列表涨颜，或者使用EXISTS()來改寫

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

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

4.MySQL不支持哈希關聯(lián)庭瑰，MariaDB已經(jīng)實現(xiàn)了哈希關聯(lián)

5.MySQL不支持松散索引掃描，5.0后版本在分組查詢中需要找到分組的最大值和最小值時可以使用松散索引掃描

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

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

1.HIGH_PRIORITY和LOW_PRIORITY弹灭，當多個語句同時訪問某一個表的時候督暂，哪些語句的優(yōu)先級相對高些、哪些語句的優(yōu)先級相對低些

2.DELAYED穷吮，對INSERT和REPLACE有效逻翁，會將使用該提示的語句立即返回給客戶端，并將插入的行數(shù)據(jù)放入到緩沖區(qū)捡鱼，然后在表空閑時批量將數(shù)據(jù)寫入八回，并不是所有的存儲引擎都支持，并且該提示會導致函數(shù)LAST_INSERT_ID()無法正常工作

3.STRAIGHT_JOIN驾诈，可以放置在SELECT語句的SELECT關鍵字之后辽社，也可以放置在任何兩個關聯(lián)表的名字之間。第一個用法是讓查詢中所有的表按照在語句中出現(xiàn)的順序進行關聯(lián)翘鸭，第二個用法則是固定其前后兩個表的關聯(lián)順序

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

5.SQL_BUFFER_RESULT就乓，告訴優(yōu)化器將查詢結果放入到一個臨時表汉匙，然后盡可能快地釋放表鎖

6.SQL_CACHE和SQL_NO_CACHE，告訴MySQL這個結果集是否應該緩存在查詢緩存中

7.SQL_CALC_FOUND_ROWS生蚁，會計算除去LIMIT子句后這個查詢要返回的結果集的總數(shù)噩翠，而實際上只返回LIMIT要求的結果集，可以通過函數(shù)FOUND_ROW()獲得這個值

8.FOR UPDATE和LOCK IN SHARE MODE邦投，主要控制SELECT語句的鎖機制伤锚，但只對實現(xiàn)了行級鎖的存儲引擎有效，僅InnoDB支持

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

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

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

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

* optimizer_switch狞洋，包含了一些開啟/關閉優(yōu)化器特性的標志位

G.優(yōu)化特定類型的查詢

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

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

* COUNT(*)并不是會像我們猜想的那樣擴展成所有的列，實際上假勿，它會忽略所有的列而直接統(tǒng)計所有的行數(shù)借嗽，當MySQL確認括號內(nèi)的表達值不可能為空時，實際上就是在統(tǒng)計行數(shù)

* MyISAM的COUNT()函數(shù)只有沒有任何WHERE條件下的COUNT(*)才非匙啵快

* 使用近似值恶导，如EXPLAIN出來的優(yōu)化器估算行數(shù)

* 使用索引覆蓋

* 使用匯總表

* 使用外部緩存系統(tǒng)

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

* 確保ON或者USING子句中的列上有索引

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

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

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

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

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

* 當無法使用索引時甲锡，GROUP BY使用兩種策略來完成：使用臨時表或者文件排序來做分組

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

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

* 最簡單的辦法是盡可能地使用索引覆蓋掃描兆蕉，而不是查詢所有的列，然后根據(jù)需要做一次關聯(lián)操作再返回所需的列缤沦，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);

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

* 使用預先計算的匯總表包蓝，或者關聯(lián)到一個冗余表

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

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

* 除非確實需要服務器消除重復的行届巩，否則就一定要使用UNION ALL

七硅瞧、MySQL高級特性

A.分區(qū)表

1.對用戶來說，分區(qū)表是一個獨立的邏輯表恕汇，但是底層由多個物理子表組成腕唧，實際上是對一組底層表的句柄對象（Handler Object）的封裝

2.適用場景：

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

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

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

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

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

3.使用限制：

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

* 在MySQL5.1中缺谴，分區(qū)表達式必須是整數(shù)但惶，或者是返回整數(shù)的表達式。在MySQL5.5中湿蛔，某些場景中可以直接使用列來進行分區(qū)

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

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

4.使用分區(qū)表

* 在數(shù)據(jù)量超大的時候，B-Tree索引就無法起作用了煌集，除非是索引覆蓋查詢妓肢，否則數(shù)據(jù)庫服務器需要根據(jù)索引掃描的結果回表，查詢所有符合條件的記錄苫纤，如果數(shù)據(jù)量巨大，將產(chǎn)生大量隨機I/O

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

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

* 索引數(shù)據(jù)卷拘，并分離熱點

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

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

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

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

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

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

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

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

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

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

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

7.查詢優(yōu)化

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

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

B.視圖

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

2.MySQL使用兩種算法：合并算法（MERGE）和臨時表算法（TEMPTABLE）瓣蛀，會盡可能地使用合并算法

3.如果視圖中包含GROUP BY、DISTINCT雷厂、任何聚合函數(shù)惋增、UNION、子查詢等改鲫，只要無法在原表記錄和視圖記錄中建立一一映射的場景中诈皿，MySQL都將使用臨時表算法來實現(xiàn)視圖

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

5.在重構schema的時候可以使用視圖稽亏，使得在修改視圖底層表結構的時候，應用代碼還可能繼續(xù)不報錯運行

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

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

C.外鍵約束

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

2.如果想確保兩個相關表始終有一致的數(shù)據(jù)，那么使用外鍵比在應用程序中檢查一致性的性能要高得多烟零，在相關數(shù)據(jù)的刪除和更新上瘪松，也比在應用中維護要更高效

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

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

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

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

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

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

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

* 可以幫助提升安全荐吉，比如提供更細粒度的權限控制

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

* 因為是在服務器端部署的样屠，所以備份穿撮、維護都可以在服務器端完成

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

3.存儲代碼的缺點：

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

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

* 存儲代碼可能會給應用程序代碼的部署帶來額外的復雜性

* 因為存儲程序都部署在服務器內(nèi)痪欲，所以可能有安全隱患

* 存儲過程會給數(shù)據(jù)庫服務器增加額外的壓力悦穿，而數(shù)據(jù)庫服務器的擴展性相比應用服務器要差很多

* MySQL并沒有什么選項可以控制存儲程序的資源消耗，所以在存儲過程中的一個小錯誤业踢，可能直接把服務器拖死

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

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

* 它和基于語句的二進投影日志復制合作得并不好

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

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

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

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

* 存儲程序和復制是一組詭異組合

5.觸發(fā)器：可以讓你在執(zhí)行INSERT知举、UPDATE或者DELETE的時候瞬沦，執(zhí)行一些特定的操作太伊，可以在MySQL中指定是在SQL語句執(zhí)行前觸發(fā)還是在執(zhí)行后觸發(fā)，可以使用觸發(fā)器實現(xiàn)一些強制限制逛钻，或者某些業(yè)務邏輯僚焦，否則，就需要在應用程序中實現(xiàn)這些邏輯

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

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

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

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

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

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

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

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

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

* 實現(xiàn)一些約束文黎、系統(tǒng)維護任務惹苗，以及更新反范式化數(shù)據(jù)的時候

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

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

E.游標

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

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

3.不支持客戶端的游標

F.綁定變量

1.當創(chuàng)建一個綁定變量SQL時，客戶端向服務器發(fā)送了一個SQL語句的原型本涕。服務器端收到這個SQL語句框架后业汰，解析并存儲這個SQL語句的部分執(zhí)行計劃，返回給客戶端一個SQL語句處理句柄菩颖。以后每次執(zhí)行這類查詢样漆，客戶端都指定使用這個句柄

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

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

* 在服務器端某些優(yōu)化項的工作只需要執(zhí)行一次，因為它會緩存一部分的執(zhí)行計劃

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

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

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

3.綁定變量相對也更安全舞竿。無須在應用程序中處理轉義，一則更簡單了窿冯，二則也大大減少了SQL注入和攻擊的風險

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

5.綁定變量的限制：

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

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

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

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

* 有些操作，比如BEGIN芜赌，無法在綁定變量中完成

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

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

H.插件

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

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

* 存儲過程插件

* 后臺插件洲愤，可以讓程序在MySQL中運行轿秧，可以實現(xiàn)自己的網(wǎng)絡監(jiān)聽荣赶、執(zhí)行自己的定期任務

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

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

* 審計插件，在查詢執(zhí)行的過程中的某些固定點被調(diào)用舰罚，可以記錄MySQL的事件日志

* 認證插件榨馁，既可可以在MySQL客戶端也可在它的服務器端，可以使用這類插件來擴展MySQL的認證功能

I.字符集和校對

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

2.每種字符集都可能有多種校對規(guī)則沉帮，并且都有一個默認的校對規(guī)則锈死，每個校對規(guī)則都是針對某個特定的字符集的，和其他的字符集沒有關系

3.MySQL有很多的選項用于控制字符集遇西，這些選項和字符集很容易混淆馅精，只有基于字符的值才真正的“有”字符集的概念

4.MySQL的兩類設置：創(chuàng)建對象時的默認設置、服務器和客戶端通信時的設置

5.如果比較的兩個字符串的字符集不同粱檀，MySQL會先將其轉成同一個字符集再進行比較

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

* 詭異的character_set_database設置洲敢，當改變默認數(shù)據(jù)庫的時候，這個變量也會跟著變茄蚯，所以當連接到MySQL實例上又沒有指定要使用的數(shù)據(jù)庫時压彭，默認值會和character_set_server相同

* LOAD DATA INFILE，當使用時渗常，數(shù)據(jù)庫總是將文件中的字符按照字符集character_set_database來解析

* SELECT INTO OUTFILE壮不，MySQL會將結果不做任何轉碼地寫入文件

* 嵌入式轉義序列，MySQL會根據(jù)character_set_client的設置來解析轉義序列

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

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

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

* 為了能夠適應各種字符集，包括客戶端字符集健蕊、在查詢中顯式指定的字符集菱阵，MySQL會在需要的時候進行字符集轉換

J.全文索引

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

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

3.在使用全文索引進行排序的時候晴及，MySQL無法再使用索引排序，如果不想使用文件排序的話嫡锌，就不要在查詢中使用ORDER BY子句

4.在布爾搜索中虑稼，用戶可以在查詢中自定義某個被搜索的詞語的相關性，可能通過一些前綴修飾符來定制搜索

5.全文索引在INSERT势木、UPDATE蛛倦、DELETE中的操作代價很大

6.全文索引會影響索引選擇、WHERE子句跟压、ORDER BY等：

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

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

* 全文索引不存儲索引列的實際值震蒋，也就不可能用作索引覆蓋掃描

* 除了相關性排序茸塞，全文索引不能用作其他的排序

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

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

* 保證索引緩存足夠大

* 提供一個好的停用詞表

* 忽略一些太短的單詞

* 導入大量數(shù)據(jù)時查剖，最好通過命令DISABLE KEYS來禁用全文索引钾虐，然后導入結束后使用ENABLE KEYS來建立全文索引

* 如果數(shù)據(jù)集特別大，則需要對數(shù)據(jù)進行手動分區(qū)笋庄，然后將數(shù)據(jù)分布到不同的節(jié)點效扫，再做并行的搜索

K.分布式（XA）事務

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

2.因為通信延遲和參與者本身可能失敗静暂，所以外部XA事務比內(nèi)部消耗會更大

L.查詢緩存

1.MySQL查詢緩存保存查詢返回的完整結果济丘，當查詢命中該緩存，MySQL會立刻返回結果洽蛀，跳過了解析摹迷、優(yōu)化和執(zhí)行階段

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

3.當判斷緩存是否命中時鲫寄，MySQL不會解析吉执、“正規(guī)化”或者參數(shù)化查詢語句，而是直接使用SQL語句和客戶端發(fā)送過來的其他原始信息塔拳。任何字符上的不同鼠证，例如空格、注釋——都會導致緩存的不命中

4.當查詢語句中有一些不確定的數(shù)據(jù)時靠抑，則不會被緩存，例如包含函數(shù)NOW()或者CURRENT_DATE()的查詢不會被緩存适掰，只要包含任何用戶自定義函數(shù)颂碧、存儲函數(shù)、用戶變量类浪、臨時表载城、mysql庫中的系統(tǒng)表，或者任何包含列級別權限的表费就，都不會被緩存

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

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

* 如果這個讀查詢可以被緩存诉瓦，那么當完成執(zhí)行后，MySQL若發(fā)現(xiàn)查詢緩存中沒有這個查詢力细，會將其結果存入查詢緩存睬澡，這會帶來額外的系統(tǒng)消耗

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

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

7.緩存未命中：

* 查詢語句無法被緩存

* MySQL從未處理這個查詢

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

* 查詢緩存還沒有完成預熱

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

* 緩存失效操作太多了

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

* query_cache_type，是否打開查詢緩存

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

* query_cache_min_res_unit昔脯，在查詢緩存中分配內(nèi)存塊時的最小單位，可以幫助減少由碎片導致的內(nèi)存空間浪費

* query_cache_limit笛臣，MySQL能夠緩存的最大查詢結果

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

9.InnoDB和查詢緩存

* 事務是否可以訪問查詢緩存取決于當前事務ID沈堡，以及對應的數(shù)據(jù)表上是否有鎖

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

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

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

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

* 因為緩存空間太大踱蛀，在過期操作的時候可能會導致服務器僵死窿给，控制緩存空間的大小

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

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

* 因為對互斥信號量的競爭崩泡，有時直接關閉查詢緩存對讀密集型的應用也會有好處

八、優(yōu)化服務器設置

A.MySQL配置的工作原理

1.任何打算長期使用的設置都應該寫到全局配置文件猬膨，而不是在命令行特別指定

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

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

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

* thread_cache_size姐军，不會立即生效——將在下次有連接被關閉時產(chǎn)生效果，檢查緩存中是否還有空間來緩存線程尖淘，如果有空間奕锌，則緩存該線程以備下次連接征用，如果沒空間村生，將銷毀該線程而不再緩存

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

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

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

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

3.對于連接級別的設置卫病，不要輕易地在全局級別增加它們的值油啤，除非確認這樣做是對的

4.設置變量時請小心，并不是值越大就越好忽肛，而且如果設置的值太高村砂，可能更容易導致問題：可能會由于內(nèi)存不足導致服務器內(nèi)存交換，或者超過地址空間

5.不要期望通過建立一套基準測試方案屹逛，然后不斷迭代地驗證對配置項的修改來找到最佳配置方案础废，而要把時間花在檢查備份、監(jiān)控執(zhí)行計劃的變動之類的事情上罕模，可能會更有意義

B.什么不該做

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

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

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

1.MySQL編譯的默認設置并不都是靠譜的评腺，雖然其中大部分都比較合適

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

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

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

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

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

1.配置MySQL正確地使用內(nèi)存量對高性能是至關重要的担敌，內(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í)行任何給定查詢，需要為高峰時期執(zhí)行的大量查詢預留好足夠的內(nèi)存全封，否則马昙，查詢執(zhí)行可能因為缺乏內(nèi)存而導致執(zhí)行效率不佳或執(zhí)行失敗

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

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

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

* InnoDB緩沖池

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

* MyISAM鍵緩存

* 查詢緩存

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

7.InnoDB緩沖池并不僅僅緩存索引：它還會緩存行的數(shù)據(jù)子房、自適應哈希索引形用、插入緩沖（Insert Buffer）、鎖池颈，以及其他內(nèi)部數(shù)據(jù)結構尾序，還使用緩沖池來幫助延遲寫入，InnoDB嚴重依賴緩沖池

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

7.MyISAM的鍵緩存也被稱為鍵緩沖梦鉴，默認只有一個鍵緩存李茫，但也可以創(chuàng)建多個，MyISAM自身只緩存索引肥橙，不緩存數(shù)據(jù)魄宏，最重要的配置項是key_buffer_size，不要超過索引的總大小存筏，或者不超過操作系統(tǒng)緩存保留總內(nèi)存的25%-50%宠互，以更小的為準

8.了解MyISAM索引實際上占用多少磁盤空間，查詢INFORMATION_SCHEMA表的INDEX_LENGTH字段椭坚，把它們的值相加予跌，就可以得到索引存儲占用空間

9.塊大小也是很重要的（特別是寫密集型負載），因為它影響了MyISAM善茎、操作系統(tǒng)緩存券册，以及文件系統(tǒng)之間的交互，如果緩存塊太小垂涯，可能會踫到寫時讀取

10.線程緩存保存那些當前沒有與連接關聯(lián)但是準備為后面新的連接服務的線程烁焙，當一個新的連接創(chuàng)建時，如果緩存中有線程存在耕赘，MySQL從緩存中刪除一個線程骄蝇，并且把它分配給這個新的連接，當連接關閉時鞠苟，如果線程緩存還有空間的話乞榨，MySQL又會把線程放回緩存秽之，如果沒有空間的話，MySQL會銷毀這個線程

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

12.表緩存（Table Cache）和線程緩存的概念是相似的，但存儲的對象代表的是表鹦倚，緩存對象包含相關表.frm文件的解析結果河质，加上其他數(shù)據(jù)。表緩存可以重用資源震叙，讓服務器避免修改MyISAM文件頭來標記表“正在使用中”掀鹅，對InnoDB的重要性要小得多

12.表緩存的缺點是，當服務器有很多MyISAM表時媒楼，可能會導致關機時間較長乐尊，因為關機前索引塊必須完成刷新，表都必須標記為不再打開

13.InnoDB數(shù)據(jù)字典（Data Dictionary）划址，InnoDB自己的表緩存扔嵌，當InnoDB打開一張表，就增加了一個對應的對象到數(shù)據(jù)字典

14.InnoDB沒有將統(tǒng)計信息持久化夺颤，而是在每次打開表時重新計算痢缎，5.6以后可以打開innodb_use_sys_stats_table選項來持久化存儲統(tǒng)計信息到磁盤

15.可以關閉InnoDB的innodb_stats_on_metadata選項來避免耗時的表統(tǒng)計信息刷新

16.如果可以，最好把innodb_open_files的值設置得足夠大以使服務器可以保持所有的.ibd文件同時打開

E.配置MySQL的I/O行為

1.InnoDB I/O配置

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

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

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

* 通常不需要把日志緩沖區(qū)設置得非常大抚恒，推薦的范圍是1MB-8MB咱台，除非要寫很多相當大的BLOB記錄

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

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

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

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

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

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

* 設置innodb_doublewrite為0來關閉雙寫緩沖

* sync_binlog選項控制MySQL怎么刷新二進制日志到磁盤

* 二進制日志，如果希望使用expire_logs_days選項來自動清理舊的二進制日志榆俺，就不要用rm命令去刪

2.MyISAM的I/O配置

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

* 通過設置delay_key_write變量，可以延遲索引的寫入茴晋，修改的鍵緩沖塊直到表被關閉才會刷新

* myisam_recover選項控制MyISAM怎樣尋找和修復錯誤

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

F.配置MySQL并發(fā)

1.InnoDB并發(fā)配置

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

* 并發(fā)值 = CPU數(shù)量 * 磁盤數(shù)量 * 2烁涌，在實踐中使用更小的值會更好一點

2.MyISAM并發(fā)配置

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

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

* 讓INSERT、REPLACE掂榔、DELETE继效、UPDATE語句的優(yōu)先級比SELECT語句更低，設置low_priority_updates選項就可以

G.基于工作負載的配置

1.當服務器滿載情況下運行時装获，請嘗試記錄所有的查詢語句瑞信，因為這是最好的方式來查看哪種類型的查詢語句占用資源最多，同時創(chuàng)建processlist快照穴豫，通過state或者command字段來聚合它們

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

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

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

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

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

* 大字段在InnoDB里可能浪費大量空間

* 擴展存儲禁用了自適應哈希秤涩，因為需要完整地比較列的整個長度，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)

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

* 如果一張表里有很多大字段司抱，最好是把它們組合起來單獨存到一個列里面

* 有時候可以把大字段用COMPRESS()壓縮后再存為BLOB筐眷，或者發(fā)送到MySQL前在應用程序中進行壓縮

3.優(yōu)化排序（Filesorts）：當MySQL必須排序BLOG或TEXT字段時，它只會使用前綴习柠，然后忽略剩下部分的值

H.完成基本配置

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

2.max_connections照棋，這個設置的作用就像一個緊急剎車，以保證服務器不會因應用程序激增的連接而不堪重負武翎，設置得以容納正沉姨浚可能達到的負載，并且要足夠安全后频，能保證允許你登錄和管理服務器

3.thread_cache_size梳庆，可以通過觀察服務器一段時間的活動，來計算一個有理有據(jù)的值卑惜，250的上限是一個不錯的估算值

4.table_cache_size膏执，應該被設置得足夠大，以避免總是需要重新打開和重新解析表的定義露久，可能通過觀察Open_tables的值及其在一段時間的變化來檢查該變量

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

1.expire_logs_days更米，如果啟用了二進制日志，應該打開這個選項毫痕，可以讓服務器在指定的天數(shù)之后清理舊的二進制日志

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

3.max_connect_errors消请，如果知道服務器可以充分抵御蠻力攻擊栏笆，可以把這個值設得非常大，以有效地禁用主機黑名單

4.skip_name_resolve臊泰，禁用了另一個網(wǎng)絡相關和鑒權誰相關的陷阱：DNS查找

5.sql_mode蛉加，不建議修改

6.sysdate_is_now，可能導致與應用預期向后不兼容的選項

7.read_only缸逃，禁止沒有特權的用戶在備庫做變更针饥，只接受從主庫傳輸過來的變更，不接受從應用來的變更需频，可以把備庫設置為只讀模式

8.skip_slave_start丁眼，阻止MySQL試圖自動啟動復制

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

10.sync_master_info搭独、sync_relay_log橡庞、sync_relay_log_info挣磨，5.5以后版本可用金蜀，解決了復制中備庫長期存在的問題：不把它們的狀態(tài)文件同步到磁盤，所以服務器崩潰后可能需要人來猜測復制的位置實際上在主庫是哪個位置吃靠，并且可能在中繼日志（Relay Log）里有損壞

J.高級InnoDB設置

1.innodb硫眨，如果設置為FORCE，只有在InnoDB可以啟動時，服務器才會啟動

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

3.innodb_buffer_pool_instances巧号，在5.5以后，可以把緩沖池切分為多段姥闭，在高負載的多核機器上提升MySQL可擴展性的一個重要方式

4.innodb_io_capacity丹鸿，有時需要把這個設置得相當高，才能穩(wěn)定地刷新臟頁

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

6.innodb_strict_mode靠欢，讓MySQL在某些條件下把警告改成拋錯，尤其是無效的或者可能有風險的CREATE TABLE選項

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

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

A.什么限制了MySQL的性能

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

2.I/O瓶頸坦弟，一般發(fā)生在工作所需的數(shù)據(jù)遠遠超過有效內(nèi)存容量的時候，如果應用程序是分布在網(wǎng)絡上的官地，或者如果有大量的查詢和低延遲的要求酿傍，瓶頸可能轉移到網(wǎng)絡上

B.如何為MySQL選擇CPU

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

2.當遇到CPU密集型的工作時拧粪，MySQL通常可以從更快的CPU中獲益沧侥，但還依賴于負載情況和CPU數(shù)量

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

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

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

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

2.設計良好的數(shù)據(jù)庫緩存（如InnoDB緩沖池）绢记，其效率通常超過操作系統(tǒng)的緩存扁达，因為操作系統(tǒng)緩存是為通用任務設計的

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

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

5.工作集包括數(shù)據(jù)和索引蠢熄，所以應該采用緩存單位來計數(shù)跪解，一個緩存單位是存儲引擎工作的數(shù)據(jù)最小單位

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

7.硬盤選擇考慮因素：存儲容量、傳輸速度签孔、訪問時間叉讥、主軸轉速窘行、物理尺寸

8.MySQL如何擴展到多個磁盤上取決于存儲引擎和工作負載，InnoDB能很好地擴展到多個硬盤驅動器图仓，然而罐盔，MyISAM的表鎖限制其寫的可擴展性，因此寫繁重的工作加在MyISAM上救崔，可能無法從多個驅動器中收益

D.固態(tài)存儲

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

* 相比硬盤有更好的隨機讀寫性能

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

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

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

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

3.垃圾收集對理解閃存很重要狸臣。為了保持一些塊是干凈的并且可以被寫入莹桅，設備需要回收臟塊。這需要設備上有一些空閑空間

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

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

6.單線程工作負載也是另一個閃存的潛在應用場景

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

8.Flashcache煤禽，磁盤和內(nèi)存技術的結合铐达，適合以讀為主的I/O密集型負載，并且工作集太大檬果，用內(nèi)存優(yōu)化并不經(jīng)濟的情況

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

* 增加InnoDB的I/O容量

* 讓InnoDB日志文件更大

* 把一些文件從閃存轉移到RAID

* 禁用預讀

* 配置InnoDB刷新算法

* 禁用雙寫緩沖的可能

* 限制插入緩沖大小瓮孙，插入緩沖設計來用于減少當更新行時不在內(nèi)存中的非唯一索引引起的隨機I/O

* InnoDB的頁大小

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

E.為備庫選擇硬件

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

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

1.RAID可以幫助做冗余、擴展存儲容量选脊、緩存杭抠，以及加速

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

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

4.RAID 5：通過分布奇偶校驗把數(shù)據(jù)分散到多個磁盤钝的，如果任何一個盤的數(shù)據(jù)失效翁垂，都可以從奇偶校驗塊中重建，但如果有兩個磁盤失效了硝桩，則整個卷的數(shù)據(jù)無法恢復沿猜，最經(jīng)濟的冗余配置。隨機寫是昂貴的碗脊，存放數(shù)據(jù)或者日志是一種可接受的選擇啼肩，或者是以讀為主的業(yè)務

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

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

G.SAN和NAS

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

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

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

4.SAN的應用：

* 備份颁虐，可以只備份SAN

* 簡化容量規(guī)劃

* 存儲整合還是服務器整合

* 高可用

* 服務器之間的交互

* 成本

H.使用多磁盤卷

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

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

I.網(wǎng)絡配置

1.在生產(chǎn)服務器上啟用skip_name_resolve是個好主意另绩，損壞或緩慢的DNS解析對許多應用程序都是個問題儒陨，對MySQL尤嚴重，如果啟用skip_name_resolve選項笋籽，MySQL將不會做任何DNS查找的工作

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

3.網(wǎng)絡物理隔離也是很重要的因素车海，盡可能避免實時的跨數(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)的預讀行為棵红，因為這可能也是多余的

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

1.在GUN/Linux上，隊列調(diào)度決定了到塊設備的請求實際上發(fā)送到底層設備的順序咧栗，默認情況下使用cfq（Completely Fair Queueing逆甜，完全公平排隊）策略，在MySQL的工作負載類型下致板，cfq會導致很差的響應時間交煞，因為會在隊列中延遲一些不必要的請求

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

M.線程

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

2.MySQL確實需要內(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列報告的內(nèi)存交換I/O活動揭璃，這比看swpd列報告的交換區(qū)利用率更重要晚凿，最佳為0

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

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

5.使用MySQL的memlock配置項瘦馍，可以把MySQL鎖定在內(nèi)存

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

1.vmstat

* vmstat 5歼秽，每隔5秒刷新一次

* procs，r列顯示多少進程正在等待CPU情组，b列顯示多少進程正在不可中斷地休眠

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

* swap，顯示頁面交換活動

* io底瓣，顯示有多少塊從塊設備讀刃痪尽（ bi）和寫出（bo）

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

* cpu捐凭，顯示所有的CPU時間花費在各類操作的百分比

2.iostat

* iostats -dx 5拨扶，每5秒刷新

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

* r/s和w/s屈雄，每秒發(fā)送到設備的讀和寫請求

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

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

* avgqu-sz酒奶，在設備隊列中等待的請求數(shù)

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

* svctm奶赔，服務請求花費的毫秒數(shù)惋嚎，不包括排除時間

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

3.CPU密集型的機器站刑，vmstat輸出通常在us列會有一個很高的值另伍，也可能在sy列有很高的值

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

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

十摆尝、復制

A.復制概述

1.MySQL支持兩種復制方式：基于行的復制和基于語句的復制，都是通過在主庫上記錄二進制日志因悲、在備庫重放日志的方式來實現(xiàn)異步的數(shù)據(jù)復制

2.復制通常不會增加主庫的開銷堕汞，主要是啟用二進制日志帶來的開銷，但出于備份或及時從崩潰中恢復的目的晃琳，這點開銷也是必要的

3.通過復制可以將讀操作指向備庫來獲得更好的讀擴展讯检，但對于寫操作琐鲁，除非設計得當，否則并不適合通過寫復制來擴展寫操作

4.復制解決的問題：

* 數(shù)據(jù)分布

* 負載均衡

* 備份

* 高可用性和故障切換

* MySQL升級測試

5.復制如何工作

* 在主庫上把數(shù)據(jù)更新記錄到二進制日志（Binary Log）中

* 備庫將主庫上的日志復制到自己的中繼日志（Relay Log）中

* 備庫讀取中繼日志中的事件人灼，將其重放到備庫數(shù)據(jù)之上

B.配置復制

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

* 用來監(jiān)控和管理復制的帳號需要REPLICATION CLIENT權限围段，并且針對這兩種目的使用同一個帳號更加容易

* 如果在主庫上建立了帳號，然后從主庫將數(shù)據(jù)克隆到備庫時投放，備庫也就設置好了——變成主庫所需要的配置

2.配置主庫和備庫

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

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

* 如果可能的話跪呈，最好使用read_only配置選項段磨，會阻止任何沒有特權權限的線程修改數(shù)據(jù)

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

4.推薦的復制配置

* sync_binlog =1，在提交事務前會將二進制日志同步到磁盤上耗绿，保證在服務器崩潰時不會丟失事件

* 如果無法容忍服務器崩潰導致表損壞苹支，推薦使用InnoDB

* 推薦明確指定二進制日志的名字，log_bin=/var/lib/mysql/mysql-bin

* 在備庫上為中繼日志指定絕對路徑误阻，relay_log

* 如果正在使用5.5并且不介意額外的fsync()導致的性能開銷债蜜，最好設置：sync_master_info，sync_relay_log究反，sync_relay_log_info

C.復制的原理

1.基于語句的復制

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

* 好處是實現(xiàn)相當簡單狼速，日志更加緊湊，不會占用太多帶寬

* 問題是基于語句的方式可能并不如其看起來那么便利卦停，還存在一些無法被正確復制的SQL向胡，更新必須是串行的這需要更多的鎖

2.基于行的復制

* 5.1開始支持，會將實際數(shù)據(jù)記錄在二進制日志中惊完，跟其他數(shù)據(jù)庫的實現(xiàn)比較想像

* 好處是可以正確地復制每一行僵芹，一些語句可以被更加有效地復制

* 如果使用全表更新，則開銷會很大小槐，因為每一行的數(shù)據(jù)都會被記錄到二進制日志中拇派，這使得二進制日志事件非常龐大，并且會給主庫上記錄日志和復制增加額外的負載凿跳，更慢的日志記錄則會降低并發(fā)度

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

* 基于語句的復制模式的優(yōu)點：當主備的模式不同時件豌，邏輯復制能夠在多種情況下工作；基于語句的方式執(zhí)行復制的過程基本上就是執(zhí)行SQL語句

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

* 基于行的復制模式的優(yōu)點：幾乎沒有基于行的復制模式無法處理的場景躬审；可能減少鎖的使用棘街，并不要求這種強串行化是可重復的；會記錄數(shù)據(jù)變更承边；占用更少的CPU遭殉；能夠幫助更快地找到并解決數(shù)據(jù)不致的情況

* 基于行的復制模式的缺點：無法判斷執(zhí)行了哪些SQL；無法知道服務器在做什么博助；在某些情況下险污，例如找不到要修改的行時，基于行的復制可能會導致復制停止

4.復制文件

* mysql-bin.index富岳，二進制日志文件

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

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

* relay-log.info窖式，包含了當前備庫復制的二進制日志和中繼日志坐標

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

6.復制過濾選項

* 在主庫上過濾記錄到二進制日志中的事件

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

D.復制拓撲

1.基本原則：

* 一個MySQL備庫實例只能有一個主庫

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

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

* 如果打開了log_slave_updates選項，一個備庫可以把其主庫上的數(shù)據(jù)變化傳播到其他備庫

2.一主庫多備庫

3.主動-主動模式下的主主復制：auto_increment_increment和auto_increment_offset可以讓MySQL自動為INSERT語句選擇不互相沖突的值

4.主動-被動模式下的主主復制：其中一臺服務器是只讀的被動服務器

5.擁有備庫的主主結構：增加了冗余萝喘，能夠消除站點單點失效的問題

6.環(huán)形復制：每個服務器都是在它之前的服務器的備庫淮逻，是在它之后的服務器的主庫

7.分發(fā)主庫事實上也是一個備庫，提取和提供主庫的二進制日志

8.樹或金字塔形：減輕了主庫的負擔阁簸，但中間層出現(xiàn)的任何錯誤都會影響到多個服務器

9.定制的復制方案

* 選擇性復制：配置replicate_wild_do_table

* 分離功能：OLTP爬早、OLAP

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

* 將備庫用作全文檢索

* 只讀備庫：read_only選項

* 模擬多主庫復制

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

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

1.寫操作通常是復制的瓶頸启妹，并且很難使用復制來擴展寫操作

2.在構建一個大型應用時筛严，有意讓服務器不被充分使用，這應該是一種聰明并且蔓延的方式饶米，尤其在使用復制的時候桨啃，有多余容量的服務器可以更好地處理負載尖峰，也有更多能力處理慢速查詢和維護工作咙崎，并且能夠更好地跟上復制

F.復制管理和維護

1.在主庫上优幸，可以使用SHOW MASTER STATUS命令來查看當前主庫的二進制日志位置和配置

2.從庫上，使用SHOW SLAVE STATUS

十一褪猛、可擴展的MySQL

A.什么是可擴展性

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

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

B.擴展MySQL

1.規(guī)劃可擴展性最困難的部分是估算需要承擔的負載到底有多少伊滋，還需要大致正確地估計日程表碳却，需要知道底線在哪里

2.可以做的準備工作：優(yōu)化性能、購買性能更強的硬件

3.向上擴展（垂直擴展）意味著購買更多性能強悍的硬件

4.向外擴展（橫向擴展笑旺、水平擴展）：復制昼浦、拆分、數(shù)據(jù)分片

* 按功能拆分（按職責拆分）筒主，不同的節(jié)點執(zhí)行不同的任務

* 數(shù)據(jù)分片关噪，把數(shù)據(jù)分割成一小片鸟蟹，或者一小塊，然后存儲到不同的節(jié)點中

* 選擇分區(qū)鍵（partitioning key）

* 多個分區(qū)鍵

* 跨分片查詢使兔，使用C或Java編寫一個輔助應用來執(zhí)行查詢并聚合結果集建钥，也可以借助匯總表來執(zhí)行

* 分配數(shù)據(jù)、分片和節(jié)點

5.通過多實例擴展

6.通過集群擴展

* MySQL Cluster（NDB Cluster）

* Clustrix

* ScaleBase

* GenieDB

* Akiban

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

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

C.負載均衡

1.在一個服務器集群中盡可能地平均負載量熊经，通常在服務器前端設置一個負載均衡器

十二、高可用性

A.什么是高可用性

1.高可用性不是絕對的欲险，只有相對更高的可用性镐依，100%的可用性是不可能達到的

2.可用性每提高一點，所花費的成本都會遠超之前天试，可用性的效果和開銷的比例并不是線性的

B.導致宕機的原因

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

2.性能問題，最普遍的原因是運行很糟糕的SQL秋秤，或服務器BUG或錯誤的行為

3.糟糕的Schema和索引設計

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

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

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

1.可以通過同時進行以下兩步來獲得高可用性

* 可以嘗試避免導致宕機的原因來減少宕機時間

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

2.提升平均失效時間（MTBF）

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

* 缺少嚴格的評估

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

3.降低平均恢復時間（MTTR）

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

D.避免單點失效

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

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

3.共享存儲或磁盤復制

* 能夠為數(shù)據(jù)庫服務器和存儲解耦合灼卢，通常使用的是SAN

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

4.MySQL同步復制

* 當使用同步復制時，主庫上的事務只有在至少一個備庫上提交后才能認為其執(zhí)行完成

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

* MySQL Cluster

* Percona XtraDB Cluster

5.基于復制的冗余

* 復制管理器是使用標準MySQL復制來創(chuàng)建冗余的工具

E.故障轉移和故障恢復

1.冗余一點也不會增加可用性或減少宕機崇堰，和故障轉移結合可以幫助更快地恢復，故障轉移最重要的部分就是故障恢復

2.提升備庫或切換角色

3.虛擬IP地址或IP接管

4.中間件解決方案涩咖，可以使用代理海诲、端口轉發(fā)、網(wǎng)絡地址轉換或者硬件負載均衡來實現(xiàn)故障轉移和故障恢復

5.在應用中處理故障轉移

十三檩互、云端的MySQL

A.云的優(yōu)點特幔、缺點和相關誤解

1.優(yōu)點：

* 云是一種將基礎設施外包出去無須自己管理的方法

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

* 隨著供應商發(fā)布新的服務和成本降低，云提供的價值越來越大

* 云能夠幫助你輕松地準備好服務器和其他資源

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

2.缺點：

* 資源是共享并且不可預測的

* 無法保證容量和可用性

* 虛擬的共享資源導致排查故障更加困難

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

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

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

3.運行不是很重要的基礎設施

C.云中的MySQL的可擴展性和高可用性

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

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

D.四種基礎資源

1.CPU通常少且慢

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

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

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

E.MySQL在云主機上的性能

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

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

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

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

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

A.常見問題

1.什么東西在消耗系統(tǒng)中每臺主機的CPU饵较、磁盤拍嵌、網(wǎng)絡，以及內(nèi)存資源循诉？這些值是否合理横辆？如果不合理，對應用程序做基本的檢查茄猫，看什么占用了資源

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

3.應用在處理本應由數(shù)據(jù)庫處理的事情嗎困肩，或者反過來？

4.應用執(zhí)行了太多的查詢脆侮？

5.應用執(zhí)行的查詢太少了僻弹？

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

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

8.應用做了太多的“垃圾”查詢？

9.應用使用了連接池嗎芭毙？這既可能是好事筋蓖，也可能是壞事

10.應用是否使用長連接？

11.應用是否在不使用的時候還保持連接撕開退敦？

B.Web服務器問題

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

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

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

4.進程存活時間變短策略：

* 不要讓Apache填鴨式地服務客戶端

* 打開gzip壓縮

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

C.緩存

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

2.應用可以緩存部分計算結果钝域，所以應用層緩存可能比更低層次的緩存更有效讽坏，可以節(jié)省兩方面的工作：獲取數(shù)據(jù)以及基于這些數(shù)據(jù)進行計算，重點是緩存命中率可能更低例证，并且可能使用較多的內(nèi)存

3.應用層緩存：

* 本地緩存

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

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

* 磁盤上的緩存

4.緩存控制策略

* TTL（time to live路呜，存活時間）

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

* 讀時失效胀葱，在更改舊數(shù)據(jù)時，為了避免要同時失效派生出來的臟數(shù)據(jù)笙蒙，可以在緩存中保存一些信息抵屿，當從緩存中讀數(shù)據(jù)時可以利用這些信息判斷數(shù)據(jù)是否已經(jīng)失效

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

* 應用代碼沒有復雜的命中和未命中處理路徑

* 當未命中的處理路徑慢得不可接受時轧葛，這種方案可以很好地工作

* 預生成內(nèi)容可以避免在緩存未命中時導致的雪崩效應

D.MySQL的替代品

1.搜索：Lucene和Sphinx

2.簡單的鍵值存儲：Redis

3.結構化數(shù)據(jù)：Hadoop

十五、備份與恢復

A.為什么要備份

1.災難恢復

2.人們改變想法

3.審計

4.測試

B.定義恢復需求

1.規(guī)劃備份和恢復策略時绿渣，有兩個重要的需求：恢復點目標（PRO）和恢復時間目標（RTO）

C.設計MySQL備份方案

1.建議

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

* 保留多個備份集

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

* 保存二進制日志以用于基于故障時間點的恢復

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

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

* 對安全性要仔細考慮

2.如果可能姜胖，關閉MySQL做備份是最簡單最安全的，需要考慮：鎖時間淀散、備份時間右莱、備份負載蚜锨、恢復時間

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

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

* 恢復非常簡單

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

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

* 非常靈活

* 與存儲引擎無關

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

4.邏輯備份的缺點：

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

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

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

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

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

* 基于文件的備份，只需要將需要的文件復制到其他地方即可

* 恢復簡單

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

6.物理備份缺點：

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

* 物理備份不總是可以跨平臺

7.除非經(jīng)過測試慢蜓，不要假定備份是正常的

8.建議混合使用物理和邏輯兩種方式來做備份

9.MySQL備份需要考慮的幾點：

* 非顯著數(shù)據(jù)

* 代碼

* 復制配置

* 服務器配置

* 選定的操作系統(tǒng)

10.差異備份是對自上次全備份后所有改變的部分做備份亚再，而增量備份則是自從任意類型的上次備份后所有修改做的備份

11.差異、增量備份的建議：

* 使用Percona XtraBackup和MySQL Enterprise Backup中的增量備份特性

* 備份二進制日志晨抡，每次備份后FLUSH LOGS

* 不要備份沒有改變的表

* 不要備份沒有改變的行

* 某些數(shù)據(jù)根本不需要備份

* 備份所有的數(shù)據(jù)组题，然后發(fā)送到一個有去重特性的目的地

12.數(shù)據(jù)一致性：當備份時，應該考慮是否需要數(shù)據(jù)在指定時間點一致

13.文件一致性：每個文件的內(nèi)部一致性

14.從備庫中備份最大的好處是可以不干擾主庫巾兆，故意將一個備庫延時一段時間對于某些災難場景非常有用

D.管理和備份二進制日志

1.expire_log_days變量MySQL定期清理日志

E.備份數(shù)據(jù)

1.生成邏輯備份

* SQL導出：mysqldump方式

* 符號分隔文件備份：使用SELECT INTO OUTFILE以符號分隔文件格式創(chuàng)建數(shù)據(jù)的邏輯備份

2.文件系統(tǒng)快照

* 支持快照的文件系統(tǒng)和設備包括FreeBSD的文件系統(tǒng)同欠、ZFS文件系統(tǒng)、GNU/Linux的邏輯卷管理（LVM）调煎，以及許多的SAN系統(tǒng)和文件存儲解決方案

F.從備份中恢復

1.恢復步驟：

* 停止MySQL服務器

* 記錄服務器的配置和文件權限

* 將數(shù)據(jù)從備份中移到MySQL數(shù)據(jù)目錄

* 改變配置

* 改變文件權限

* 以限制訪問模式重啟服務器镜遣，等待完成啟動

* 載入邏輯備份文件

* 檢查和重放二進制日志

* 檢測已經(jīng)還原的數(shù)據(jù)

* 以完全權限重啟服務器

G.備份和恢復工具

1.MySQL Enterprise Backup

2.Percona XtraBackup

3.mylvmbackup

4.Zmanda Recovery Manager

5.mydunper

6.mysqldump

十六、MySQL用戶工具

A.接口工具

1.MySQL Workbench

2.SQLyog

3.phpMyAdmin

4.Adminer

B.命令行工具集

1.Percona Toolkit

2.Maatkit and Aspersa

3.The openark kit

4.MySql workbench

C.SQL實用集

1.common_schema

2.mysql-sr-lib

3.MySQL UDF倉庫

4.MySQL Forge

D.監(jiān)測工具

1.開源的監(jiān)控工具

* Nagios

* Zabbix

* Zenoss

* OpenNMS

* Groundwork Open Source

* MRTG

* Cacti

* Ganglia

* Munin

2.商業(yè)監(jiān)控系統(tǒng)

* MySQL Enterprise Monitor

* MONyog

* New Relic

* Circonus

* Monitis

* Splunk

* Pingdom

3.Innotop的命令行監(jiān)控

作者：ZyBlog

鏈接：http://www.reibang.com/p/47664afa249e

來源：簡書