高性能MySQL【筆記】超詳細

一、MySQL架構與歷史A.併發控制

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

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

B.事務

1.事務ACID

* 原子性（atomicity）一個事務必須被視為一個不可分割的最小工作單元，整個事務中所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作

* 一致性（consistency）數據庫總是從一個一致性的狀態轉換到另外一個一致性的狀態

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

* 持久性（durability）一旦事務提交，則其所做的修改就會永久保存到數據庫中

2.四種隔離級別

* READ UNCOMMITTED（未提交讀），事務中的修改，即使沒有提交，對其他事務也都是可見的，事務可以讀取未提交的數據，也被稱為髒讀（Dirty Read），這個級別會導致很多問題

* READ COMMITTED（提交讀），大多數數據庫系統的默認隔離級別，一個事務開始時，只能“看見”已經提交的事務所做的修改，一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的，也叫不可重複讀（nonrepeatable read），有可能出現幻讀（Phantom Read），指的是當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄，當之前的事務再次讀取該範圍的記錄時，會產生幻行（Phantom Row）

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

* SERIALIZABLE（可串行化）最高級別，通過強制事務串行執行，避免了幻讀問題，會在讀取的每一行數據上都加鎖，可能導致大量的超時和鎖爭用的問題

3.死鎖：指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性循環的現象

4.事務日誌：存儲引擎在修改表的數據時只需要修改其內存拷貝，再把該修改行為記錄到持久在硬盤上的事務日誌中，而不用每次都將修改的數據本身持久到磁盤。事務日誌持久以後，內存中被修改的數據在後臺可以慢慢地刷回到磁盤，稱為預寫式日誌（Write-Ahead Logging）

C.多版本併發控制

1.多版本併發控制（MVCC）是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只鎖定必要的行

2.MVCC的實現，是通過保存數據在某個時間點的快照來實現的，有樂觀和悲觀兩種，只在REPEATABLE READ和READ COMMITTED兩個隔離級別下工作

D.MySQL的存儲引擎

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

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

3.不要輕易相信MyISAM比InnoDB快之類的經驗之談，這個結論並不是絕對的

二、MySQL基準測試

A.為什麼需要基準測試

1.基準測試可以觀察系統在不同壓力下的行為，評估系統的容量，掌握哪些是重要的變化，或者觀察系統如何處理不同的數據

B.基準測試的策略

1.兩種主要的策略：

* 針對整個系統的整體測試（集成式full-stack）

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

2.測試何種指標：

* 吞吐量，指單位時間內的事務處理數，常用的測試單位是每秒事務數（TPS），或每分鐘事務數（TPM）

* 響應時間或者延遲，用於測試任務所需的整體時間，根據具體的應用，測試的時間單位可能是微秒、毫秒、秒或者分鐘。通常使用百分比響應時間（percentile response time）來替代最大響應時間

* 併發性，需要關注的是正在工作中的併發操作，或者是同時工作中的線程數或者連接數，在測試期間記錄MySQL數據庫的Threads_running狀態值

* 可擴展性，給系統增加一倍的工作，在理想情況下就能獲得兩倍的效果（即吞吐量增加一倍），對於容量規範非常有用，可以提供其他測試無法提供的信息，來幫助發現應用的瓶頸

C.基準測試方法

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

* 使用真實數據的子集而不是全集

* 使用錯誤的數據分佈

* 使用不真實的分佈參數

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

* 在單服務器上測試分佈式應用

* 與真實用戶行為不匹配

* 反覆執行同一個查詢

* 沒有檢查錯誤

* 忽略了系統預熱（warm up）的過程

* 使用默認的服務器配置

* 測試時間太短

2.應該建立將參數和結果文檔化的規範，每一輪測試都必須進行詳細記錄

3.基準測試應該運行足夠長的時間，需要在穩定狀態下測試並觀察

4.在執行基準測試時，需要儘可能多地收集被測試系統的信息

5.自動化基準測試可以防止測試人員偶爾遺漏某些步驟，或者誤操作，另外也有助於歸檔整個測試過程，可以選擇shell、php、perl等，要儘可能使所有測試過程都自動化，包括裝載數據、系統預熱、執行測試、記錄結果等

D.基準測試工具

1.集成式測試工具：

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

* http_load，和ab類似，但更加靈活

* jMeter，可以加載其他應用並測試其性能

2.單組件式測試工具

* mysqlslap，可以模擬服務器的負載，並輸出計時信息

* MySQL Benchmark Suite(sql-bench)，單線程的，主要用於測試服務器執行查詢的速度

* Super Smack，提供壓力測試和負載生成，是一個複雜而強大的工具，可以模擬多用戶訪問，可以加載測試數據到數據庫，並支持使用隨機數據填充測試表

* Database Test Suite，類似某些工業標準測試的測試工具集

* Percona's TPCC-MySQWL Tool

* sysbench，多線程系統壓測工具，可以根據影響數據庫服務器性能的各種因素來評估系統的性能

三、服務器性能剖析

A.性能優化簡介

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

2.如果目標是降低響應時間，就需要理解為什麼服務器執行查詢需要這麼多時間，然後去減少或者消除那些對獲得查詢結果來說不必要的工作。無法測量就無法有效地優化

3.性能剖析（profiling）是測量和分析時間花費在哪裡的主要方法，一般有兩個步驟：測量任務所花費的時間，對結果進行統計和排序

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

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

* 外部資源

* 應用需要處理大量的數據

* 在循環中執行昂貴的操作

* 使用了低效的算法

2.PHP性能剖析工具：New Relic、xhprof、Ifp

C.剖析MySQL查詢

1.剖析服務器負載

* 慢查詢日誌：5.1後long_query_time為0可以捕獲所有的查詢，查詢的響應時間單位可以做到微秒級

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

2.剖析單條查詢：

* SHOW PROFILES;

* SHOW [GLOBAL] STATUS;，返回一些計數器

D.診斷間歇性問題

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

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

* 使用SHOW GLOBAL STATUS

* 使用SHOW PROCESSLIST

* 使用查詢日誌

* 理解發現的問題：使得gnuplot或R，或其他繪圖工具將結果繪製成圖形

3.捕獲診斷數據

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

* 收集數據：儘可能收集所有能收集的數據，但只在需要的時間段內收集，oprofile、strace、tcpdump、GDB堆棧跟蹤、pt-collect、pt-stalk

* 解釋結果數據：pt-mysql-summary、pt-summary輸出結果打包，pt-sift得到樣本彙總信息，pt-pmp

E.其他剖析工具

1.使用USER_STATISTICS表

2.使用strace，可以調查系統調用的情況

四、Schema與數據類型優化

A.選擇優化的數據類型

1.數據類型的選擇原則：

* 更小的通常更好

* 簡單就好

* 儘量避免NULL

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

3.使用VARCHAR合適的情況：字符串列的最大長度比平均長度大很多；列的更新很少，所以碎片不是問題；使用了像UTF-8這樣複雜的字符集，每個字符都使用不同的字節數進行存儲

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

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

B.MySQL schema設計中的陷阱

1.不好的設計：

* 太多的列

* 太多的關聯

* 全能的枚舉

* 變相的枚舉

* 非此發明（Not Invent Here）的NULL

C.範式和反範式

1.範式的優點：

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

* 當數據較好地範式化時，就只有很少或者沒有重複數據，所以只需要修改更少的數據

* 範式化的表通常更小，可以更好地放在內存裡，所以執行操作會更快

* 很少有多餘的數據意味著檢索列表數據時更少需要DISTINCT或者GROUP BY語句

2.範式化設計的缺點是通常需要關聯

3.反範式的優點：避免關聯，避免了隨機I/O，能使用更有效的索引策略

D.緩存表和彙總表

1.有時提升性能最好的方法是同一張表中保存衍生的冗餘數據，有時也需要創建一張完全獨立的彙總表或緩存表

2.物化視圖，MySQL並不原生支持，Flexviews

3.如果應用在表中保存計數器，則在更新計數器時可能踫到併發問題，創建一張獨立的表存儲計數器，可以幫助避免緩存失效

* 解決獨立表併發問題可以建多行，根據id隨機更新，然後統計時sum()

* 按天或小時可以單獨建行，舊時間可定時任務合併到統一的一行

E.加快ALTER TABLE操作的速度

1.兩種方式：

* 一是在一臺不提供服務的機器上執行ALTER TABLE操作，然後和提供服務的主庫進行切換

* 二是通過“影子拷貝”，創建一張新表，然後通過重命名和刪表操作交換兩張表及裡面的數據

2.快速創建MyISAM索引，先禁用索引，導入數據，然後重新啟用索引

五、創建高性能的索引

A.索引基礎

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

2.ORM工具能夠產生符合邏輯的、合法的查詢，除非只是生成非常基本的查詢，否則它很難生成適合索引的查詢

3.在MySQL中，索引是在存儲引擎層而不是服務器層實現的，所以，並沒有統一的索引標準：不同存儲引擎的索引的工作方式並不一樣，也不是所有的存儲引擎都支持所有類型的索引

4.B-Tree意味著所有的值都是按順序存儲的，並且每一個葉子頁到根的距離相同，能夠加快訪問數據的速度，從索引的根節點開始進行搜索，適用於全鍵值、鍵值範圍或鍵前綴查找

5.B-Tree索引的限制：

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

* 不能跳過索引中的列

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

6.哈希索引（hash index）基於哈希表實現，只有精確匹配索引所有列的查詢才有效，只有Memory引擎顯式支持哈希索引

7.哈希索引的限制：

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

* 哈希索引數據並不是按照索引值順序存儲的，所以也就無法用於排序

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

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

* 訪問哈希索引的數據非常快，除非有很多哈希衝突

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

8.空間數據索引（R-Tree），MyISAM表支持空間索引，可以用作地理數據存儲，開源數據庫系統中對GIS的解決方案做得比較好的是PostgreSQL的PostGIS

9.全文索引，適用於MATCH AGAINST操作，而不是普通的WHERE條件操作

B.索引的優點

1.三個優點：

* 索引大大減少了服務器需要掃描的數據量

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

* 索引可以將隨機I/O變為順序I/O

2.索引三星系統：

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

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

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

C.高性能的索引策略

1.獨立的列：如果查詢中的列不是獨立的，則MySQL不會使用索引。“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數

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

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

* 索引的選擇性是指，不重複的索引值（也稱為基數，cardinality）和數據表的記錄總數（#T）的比值，範圍從1/#T到1之間，選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行

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

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

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

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

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

4.聚簇索引：並不是一種單獨的索引類型，而是一種數據存儲方式

* 最好避免隨機的（不連續且值的分佈範圍非常大）聚簇索引，特別是對於I/O密集型的應用

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

* 覆蓋索引必須要存儲索引列的值，

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

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

8.重複索引是指在相同的列上按照相同的順序創建的相同類型的索引，應該避免這樣創建重複索引

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

D.維護索引和表

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

2.records_in_range()通過向存儲引擎傳入兩個邊界值獲取在這個範圍大概有多少條記錄，對於innodb不精確

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

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

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

六、查詢性能優化

A.為什麼查詢速度會慢

1.如果要優化查詢，實際上要優化其子任務，要麼消除其中一些子任務，要麼減少子任務的執行次數，要麼讓子任務運行得更快

2.查詢的生命週期大致可以按照順序來看：從客戶端，到服務器，然後在服務器上進行解析，生成執行計劃，執行，並返回結果給客戶端

B.慢查詢基礎：優化數據訪問

1.兩個分析步驟：

* 確認應用程序是否在檢索大量超過需要的數據

* 確認MySQL服務器層是否在分析大量超過需要的數據行

2.是否向數據庫請求了不需要的數據

* 查詢不需要的記錄

* 多表關聯並返回全部列

* 總是取出全部列

* 重複查詢相同的數據

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

* 查詢開銷三個指標：響應時間、掃描的行數、返回的行數

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

* 掃描的行數：較短的行的訪問速度更快，內存中的行也比磁盤中的行的訪問 速度要快得多

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

* 三種方式應用WHERE條件：在索引中使用WHERE條件來過濾不匹配的記錄；使用索引覆蓋掃描（Extra中出現Using index）來返回記錄，直接從索引中過濾不需要的記錄並返回命中結果；從數據表中返回數據，然後過濾不滿足條件的記錄（Extra中出現Using Where）

* 需要掃描大量數據但只返回少數的行的優化技巧：使用索引覆蓋掃描，改變庫表結構，重寫複雜的查詢

C.重構查詢的方式

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

2.切分查詢，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果，可以避免鎖住很多數據、佔滿事務日誌、耗盡系統資源、阻塞很多小的但重要的查詢

3.分解關聯查詢優勢：

* 讓緩存的效率更高

* 將查詢分解後，執行單個查詢可以減少鎖的競爭

* 在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展

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

* 可以減少冗餘記錄的查詢

* 相當於在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯

4.分解關聯查詢的場景：

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

* 當可以將數據分佈到不同的MySQL服務器上的時候

* 當能夠使用IN()的方式代替關聯查詢的時候

* 當查詢中使用同一個數據表的時候

D.查詢執行的基礎

1.查詢執行路徑

* 客戶端發送一條查詢給服務器

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

* 服務器端進行SQL解析、預處理，再由優化器生成對應的執行計劃

* MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢

* 將結果返回給客戶端

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

3.MySQL通常需要等所有的數據都已經發送給客戶端才能釋放這條查詢所佔用的資源，所以接收全部結果並緩存通常可以減少服務器的壓力

4.查詢狀態，SHOW FULL PROCESSLIST命令查看：

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

* Query，線程正在執行查詢或者正在將結果發送給客戶端

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

* Analyzing and statistics，線程正在收集存儲引擎的統計信息，並生成查詢的執行計劃

* Copying to tmp table [on disk]，線程正在執行查詢，並且將其結果集都複製到一個臨時表中，要麼是在做GROUP BY操作，要麼是文件排序操作，或者是UNION操作

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

* Sending data，線程可能在多個狀態之間傳送數據，或者在生成結果集，或者在向客戶端返回數據

5.語法解析器和預處理，通過關鍵字將SQL語句進行解析，並生成一棵對應的“解析樹”，解析器將使用MySQL語法規則驗證和解析查詢，預處理器則根據一些MySQL規則進一步檢查解析樹是否合法

6.查詢優化器，找到最好的執行計劃，使用基本成本的優化器，將嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的一個，使用SHOW STATUS LIKE 'Last_query_cost';查看需要多少個數據頁的隨機查找

7.導致MySQL查詢優化器選擇錯誤的原因：

* 統計信息不準確，Innodb不能維護一個數據表的行數的精確統計信息

* 執行計劃中的成本估算不等同於實際執行的成本

* MySQL的最優可能和你想的最優不一樣

* MySQL從不考慮其他併發執行的查詢

* MySQL也並不是任何時候都是基於成本的優化

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

* 優化器有時候無法去估算所有可能的執行計劃

8.MySQL能處理的優化類型：

* 重新定義關聯表的順序

* 將外鏈接轉化成內鏈接

* 使用等價變換規則

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

* 預估並轉化為常數表達式，當檢測到一個表達式可以轉化為常數的時候，就會一直把該表達式作為常數進行優化處理

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

* 子查詢優化

* 提前終止查詢，在發現已經滿足查詢需求的時候，MySQL總是能夠立刻終止查詢

* 等值傳播，如果兩個列的值通過等式關聯，那麼MySQL能夠把其中一個列的WHERE條件傳遞到另一列上

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

9.在服務器層有查詢優化器，卻沒有保存數據和索引的統計信息，統計信息由存儲引擎實現，不同的存儲引擎可能會存儲不同的統計信息

10.在MySQL中，每一個查詢，每一個片段（包括子查詢，甚至基於單表的SELECT）都可能是關聯

11.對於UNION查詢，MySQL先將一系列的單個查詢結果放到一個臨時表中，然後再重新讀出臨時表數據來完成UNION查詢

12.MySQL對任何關聯都執行“嵌套循環關聯”操作，即MySQL先在一個表中循環取出單條數據，然後再嵌套到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止

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

14.關聯查詢優化器，會嘗試在所有的關聯順序中選擇一個成本最小的來生成執行計劃樹，如果可能，優化器會遍歷每一個表然後逐個做嵌套循環計算每一棵可能的執行樹的成本，最後返回一個最優的執行計劃

15.如果有超過n個表的關聯，那麼需要檢查n的階乘關聯順序，稱為“搜索空間”，搜索空間的增長速度非常快

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

17.當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果數據量小則在內存中進行，如果數據量大則需要使用磁盤，MySQL將這個過程稱為文件排序（filesort），即使完全是內存排序不需要任何磁盤文件時也是如此

E.MySQL查詢優化器的侷限性

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

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

3.MySQL無法利用多核特性來並行執行查詢

4.MySQL不支持哈希關聯，MariaDB已經實現了哈希關聯

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

6.對於MIN()和MAX()查詢，MySQL的優化做得並不好

F.查詢優化器的提示（hint）

1.HIGH_PRIORITY和LOW_PRIORITY，當多個語句同時訪問某一個表的時候，哪些語句的優先級相對高些、哪些語句的優先級相對低些

2.DELAYED，對INSERT和REPLACE有效，會將使用該提示的語句立即返回給客戶端，並將插入的行數據放入到緩衝區，然後在表空閒時批量將數據寫入，並不是所有的存儲引擎都支持，並且該提示會導致函數LAST_INSERT_ID()無法正常工作

3.STRAIGHT_JOIN，可以放置在SELECT語句的SELECT關鍵字之後，也可以放置在任何兩個關聯表的名字之間。第一個用法是讓查詢中所有的表按照在語句中出現的順序進行關聯，第二個用法則是固定其前後兩個表的關聯順序

4.SQL_SMALL_RESULT和SQL_BIG_RESULT，只對SELECT語句有效，它們告訴優化器對GROUP BY或者DISTINCT查詢如何使用臨時表及排序

5.SQL_BUFFER_RESULT，告訴優化器將查詢結果放入到一個臨時表，然後儘可能快地釋放表鎖

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

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

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

9.USE INDEX、IGNORE INDEX和FORCE INDEX，告訴優化器使用或者不使用哪些索引來查詢記錄

10.MySQL5.0後新增的用來控制優化器行為的參數：

* optimizer_search_depth，控制優化器在窮舉執行時的限度

* optimizer_prune_level，讓優化器會根據需要掃描的行數來決定是否跳過某些執行計劃

* optimizer_switch，包含了一些開啟/關閉優化器特性的標誌位

G.優化特定類型的查詢

1.優化COUNT()查詢

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

* COUNT(*)並不是會像我們猜想的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數，當MySQL確認括號內的表達值不可能為空時，實際上就是在統計行數

* MyISAM的COUNT()函數只有沒有任何WHERE條件下的COUNT(*)才非常快

* 使用近似值，如EXPLAIN出來的優化器估算行數

* 使用索引覆蓋

* 使用匯總表

* 使用外部緩存系統

2.優化關聯查詢

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

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

* 當升級MySQL的時候需要注意：關聯語法、運算符優先級等其他可能會發生變化的地方

3.優化子查詢：儘可能使用關聯查詢代替，如果使用MySQL5.6以上或MariaDB則可以忽略這個建議

4.優化GROUP BY和DISTINCT

* 使用索引優化

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

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

5.優化LIMIT分頁

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

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

* 使用預先計算的彙總表，或者關聯到一個冗餘表

6.優化UNION查詢

* 通過創建並填充臨時表的方式來執行UNION查詢，因此很多優化策略在UNION查詢中都沒法很好地使用，經常需要手工地將WHERE、LIMIT、ORDER BY等子句下推到UNION的各個子查詢中

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

七、MySQL高級特性

A.分區表

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

2.適用場景：

* 表非常大以至於無法全部都放在內存中，或者只在表的最後部分有熱點數據，其他均是歷史數據

* 分區表的數據更容易維護

* 分區表的數據可以分佈在不同的物理設備上，從而高效地利用多個硬件設備

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

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

3.使用限制：

* 一個表最多隻能有1024個分區

* 在MySQL5.1中，分區表達式必須是整數，或者是返回整數的表達式。在MySQL5.5中，某些場景中可以直接使用列來進行分區

* 如果分區字段中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來

* 分區表中無法使用外鍵約束

4.使用分區表

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

5.保證大數據量的可擴展性兩個策略：

* 命題掃描數據，不要任何索引

* 索引數據，並分離熱點

6.分區策略的問題：

* NULL值會使分區過濾無效

* 分區列和索引列不匹配

* 選擇分區的成本可能很高

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

* 維護分區的成本可能很高

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

* 分區函數中可以使用的函數和表達式也有一些限制

* 某些存儲引擎不支持分區

* 對於MyISAM的分區表，不能再使用LOAD INDEX INTO CACHE操作

* 對於MyISAM表，使用分區表時需要打開更多多的文件描述符

7.查詢優化

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

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

B.視圖

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

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

3.如果視圖中包含GROUP BY、DISTINCT、任何聚合函數、UNION、子查詢等，只要無法在原表記錄和視圖記錄中建立一一映射的場景中，MySQL都將使用臨時表算法來實現視圖

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

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

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

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

C.外鍵約束

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

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

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

D.在MySQL內部存儲代碼

1.MySQL允許通過觸發器、存儲過程、函數的形式來存儲代碼，從5.1開始還可以在定時任務中存放代碼，這個定時任務也被稱為“事件”。存儲過程和存儲函數都被統稱為“存儲程序”

2.存儲代碼的優點：

* 它在服務器內部執行，離數據最近，另外在服務器上執行還可以節省帶寬和網絡延遲

* 這是一種代碼重用，可以方便地統一業務規則，保證某些行為總是一致，所以也可以為應用提供一定的安全性

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

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

* 服務器端可以緩存存儲過程的執行計劃，這對於需要反覆調用的過程，會大大降低消耗

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

* 可以在應用開發和數據庫開發人員之間更好地分工

3.存儲代碼的缺點：

* MySQL本身沒有提供好用的開發和調試工具

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

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

* 因為存儲程序都部署在服務器內，所以可能有安全隱患

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

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

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

* 調試MySQL的存儲過程是一件很困難的事情

* 它和基於語句的二進投影日誌複製合作得並不好

4.存儲過程和函數的限制：

* 優化器無法使用關鍵字DETERMINISTIC來優化單個查詢中多次調用存儲函數的情況

* 優化器無法評估存儲函數的執行成本

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

* 存儲程序和複製是一組詭異組合

5.觸發器：可以讓你在執行INSERT、UPDATE或者DELETE的時候，執行一些特定的操作，可以在MySQL中指定是在SQL語句執行前觸發還是在執行後觸發，可以使用觸發器實現一些強制限制，或者某些業務邏輯，否則，就需要在應用程序中實現這些邏輯

6.觸發器的注意和限制：

* 對每一個表的每一個事件，最多隻能定義一個觸發器

* 只支持“基於行的觸發”，也就是說，觸發器是針對一條記錄的，而不是針對整個SQL語句的，如果變更的數據集非常大的話，效率會很低

* 觸發器可以掩蓋服務器背後的工作

* 觸發器可以掩蓋服務器背後的工作，一個簡單的SQL語句背後可能包含了很多看不見的工作

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

* 觸發器可能導致死鎖和鎖等待

* 觸發器並不能一定保證更新的原子性

7.觸發器的用處：

* 實現一些約束、系統維護任務，以及更新反範式化數據的時候

* 記錄數據變更日誌

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

E.遊標

1.MySQL在服務器端提供提供只讀的、單向的遊標，而且只能在存儲過程或者更底層的客戶端API中使用，指向的對象都是存儲在臨時表中而不是實際查詢到的數據，所以總是隻讀的

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

3.不支持客戶端的遊標

F.綁定變量

1.當創建一個綁定變量SQL時，客戶端向服務器發送了一個SQL語句的原型。服務器端收到這個SQL語句框架後，解析並存儲這個SQL語句的部分執行計劃，返回給客戶端一個SQL語句處理句柄。以後每次執行這類查詢，客戶端都指定使用這個句柄

2.可以更高效地執行大量的重複語句：

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

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

* 以二進制的方式只發送參數和句柄，比起每次都發送ASC2碼文本效率更高

* 僅僅是參數——而不是整個查詢語句——需要發送到服務器端，所以網絡開銷會更小

* MySQL在存儲參數的時候，直接將其存放到緩存中，不再需要在內存中多次複製

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

4.最主要的用途就是在存儲過程中使用，構建並執行“動態”的SQL語句

5.綁定變量的限制：

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

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

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

* 如果總是忘記釋放綁定變量資源，則在服務器端很容易發生資源“洩漏”

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

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

H.插件

1.插件可以在MySQL中新增啟動選項和狀態值，還可以新增INFORMATION_SCHEMA表，或者在MySQL的後臺執行任務等等

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

* 存儲過程插件

* 後臺插件，可以讓程序在MySQL中運行，可以實現自己的網絡監聽、執行自己的定期任務

* INFORMATION_SCHEMA插件，提供一個新的內存INFORMATION_SCHEMA表

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

* 審計插件，在查詢執行的過程中的某些固定點被調用，可以記錄MySQL的事件日誌

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

I.字符集和校對

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

2.每種字符集都可能有多種校對規則，並且都有一個默認的校對規則，每個校對規則都是針對某個特定的字符集的，和其他的字符集沒有關係

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

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

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

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

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

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

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

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

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

* 不同的字符集和校對規則之間的轉換可能會帶來額外的系統開銷

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

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

J.全文索引

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

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

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

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

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

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

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

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

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

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

7.全文索引的配置和優化：

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

* 保證索引緩存足夠大

* 提供一個好的停用詞表

* 忽略一些太短的單詞

* 導入大量數據時，最好通過命令DISABLE KEYS來禁用全文索引，然後導入結束後使用ENABLE KEYS來建立全文索引

* 如果數據集特別大，則需要對數據進行手動分區，然後將數據分佈到不同的節點，再做並行的搜索

K.分佈式（XA）事務

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

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

L.查詢緩存

1.MySQL查詢緩存保存查詢返回的完整結果，當查詢命中該緩存，MySQL會立刻返回結果，跳過了解析、優化和執行階段

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

3.當判斷緩存是否命中時，MySQL不會解析、“正規化”或者參數化查詢語句，而是直接使用SQL語句和客戶端發送過來的其他原始信息。任何字符上的不同，例如空格、註釋——都會導致緩存的不命中

4.當查詢語句中有一些不確定的數據時，則不會被緩存，例如包含函數NOW()或者CURRENT_DATE()的查詢不會被緩存，只要包含任何用戶自定義函數、存儲函數、用戶變量、臨時表、mysql庫中的系統表，或者任何包含列級別權限的表，都不會被緩存

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

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

* 如果這個讀查詢可以被緩存，那麼當完成執行後，MySQL若發現查詢緩存中沒有這個查詢，會將其結果存入查詢緩存，這會帶來額外的系統消耗

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

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

7.緩存未命中：

* 查詢語句無法被緩存

* MySQL從未處理這個查詢

* 查詢緩存的內存用完了

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

* 查詢語句之前從未執行過

* 緩存失效操作太多了

8.緩存參數配置：

* query_cache_type，是否打開查詢緩存

* query_cache_size，查詢緩存使用的總內存空間

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

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

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

9.InnoDB和查詢緩存

* 事務是否可以訪問查詢緩存取決於當前事務ID，以及對應的數據表上是否有鎖

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

10.通用查詢緩存優化：

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

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

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

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

* 對於 寫密集型的應用來說，直接禁用查詢緩存可能會提高系統的性能

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

八、優化服務器設置

A.MySQL配置的工作原理

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

2.常用變量和動態修改它們的效果：

* key_buffer_size，可以一次性為鍵緩衝區（key buffer，也叫鍵緩存key cache）分配所有指定的空間

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

* thread_cache_size，不會立即生效——將在下次有連接被關閉時產生效果，檢查緩存中是否還有空間來緩存線程，如果有空間，則緩存該線程以備下次連接徵用，如果沒空間，將銷燬該線程而不再緩存

* query_cache_size，一次性分配並初始化這塊內存

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

* read_rnd_buffer_size，只在有查詢需要使用時才會為該緩存分配內存，並且只會分配需要的內存大小而不是全部指定的大小

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

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

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

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

B.什麼不該做

1.不要根據一些“比率”來調優：例如緩存命中率跟緩存是否過大或過小沒有關係2.不要使用調優腳本

3.不要相信很流行的內存消耗公式

C.創建MySQL配置文件

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

2.從一個比默認值大一點但不是大得很離譜的安全值開始是比較好的，MySQL的內存利用率並不總是可以預測的：它可能依賴很多的因素，例如查詢的複雜性和併發性

3.配置服務器的首選途徑：瞭解它內部做了什麼，以及參數之間如何相互影響，然後再決定

4.open_files_limit，在Linux系統上設置得儘可能大，如果參數不夠大，將會踫到24號錯誤“打開的文件太多（too many open files）”

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

D.配置內存使用

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

2.配置內存：

* 確定可以使用的內存上限

* 確定每個連接MySQL需要使用多少內存

* 確定操作系統需要多少內存才夠用

* 把剩下的內存全部給MySQL的緩存

3.MySQL保持一個連接（線程）只需要少量的內存，它還需要一個基本量的內存來執行任何給定查詢，需要為高峰時期執行的大量查詢預留好足夠的內存，否則，查詢執行可能因為缺乏內存而導致執行效率不佳或執行失敗

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

5.如果服務器只運行MySQL，所有不需要為操作系統以及查詢處理保留的內存都可以用作MySQL緩存

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

* InnoDB緩衝池

* InnoDB日誌文件和MyISAM數據的操作系統緩存

* MyISAM鍵緩存

* 查詢緩存

* 無法手工配置的緩存，例如二進制日誌和表定義文件的操作系統緩存

7.InnoDB緩衝池並不僅僅緩存索引：它還會緩存行的數據、自適應哈希索引、插入緩衝（Insert Buffer）、鎖，以及其他內部數據結構，還使用緩衝池來幫助延遲寫入，InnoDB嚴重依賴緩衝池

8.如果事先知道什麼時候需要關閉InnoDB，可以在運行時修改innodb_max_dirty_pages_pct變量，將值改小，等待刷新純種清理緩衝池，然後在髒頁數量較少時關閉，可以監控the Innodb_buffer_pool_pages_dirty狀態變量或者使用innotop來監控SHOW INNODB STATUS來觀察髒頁的刷新量

7.MyISAM的鍵緩存也被稱為鍵緩衝，默認只有一個鍵緩存，但也可以創建多個，MyISAM自身只緩存索引，不緩存數據，最重要的配置項是key_buffer_size，不要超過索引的總大小，或者不超過操作系統緩存保留總內存的25%-50%，以更小的為準

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

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

10.線程緩存保存那些當前沒有與連接關聯但是準備為後面新的連接服務的線程，當一個新的連接創建時，如果緩存中有線程存在，MySQL從緩存中刪除一個線程，並且把它分配給這個新的連接，當連接關閉時，如果線程緩存還有空間的話，MySQL又會把線程放回緩存，如果沒有空間的話，MySQL會銷燬這個線程

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

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

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

13.InnoDB數據字典（Data Dictionary），InnoDB自己的表緩存，當InnoDB打開一張表，就增加了一個對應的對象到數據字典

14.InnoDB沒有將統計信息持久化，而是在每次打開表時重新計算，5.6以後可以打開innodb_use_sys_stats_table選項來持久化存儲統計信息到磁盤

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

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

E.配置MySQL的I/O行為

1.InnoDB I/O配置

* InnoDB不僅允許控制怎麼恢復，還允許控制怎麼打開和刷新數據（文件），這會對恢復和整體性能產生巨大的影響

* 對於常見的應用，最重要的一小部分內容是InnoDB日誌文件大小、InnoDB怎樣刷新它的日誌緩衝，以及InnoDB怎樣執行I/O

* 整體的日誌文件大小受控於innodb_log_file_size和innodb_log_files_in_group兩個參數，對寫性能非常重要

* 通常不需要把日誌緩衝區設置得非常大，推薦的範圍是1MB-8MB，除非要寫很多相當大的BLOB記錄

* 可以通過檢查SHOW INNODB STATUS的輸出中LOG部分來監控InnoDB的日誌和日誌緩衝區的I/O性能，通過觀察Innodb_os_log_written狀態變量來查看InnodDB對日誌文件寫出了多少數據。日誌文件的全部大小，應該足夠容納服務器一個小時的活動內容

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

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

* InnoDB用表空間並不只是存儲表和索引，還保存了回滾日誌、插入緩衝（Insert Buffer）、雙寫緩衝（Doublerite Buffer）及其他內部數據結構

* 為了控制寫入速度，可以設置innodb_max_purge_lag變量為一個大於0的值，這個值表示InnoDB開始延遲後面的語句更新數據之前，可以等待被清除的最大的事務數量

* 雙寫緩衝是表空間的一個特殊的保留區域，在一些連續的塊中足夠保存100個頁，本質上是一個最近寫回的頁面的備份拷貝，當InnoDB從緩衝池刷新頁面到磁盤時，首先把它們寫（或者刷新）到雙寫緩衝，然後再把它們寫到其所屬的數據區域中，這可以保證每個頁面的寫入都是原子並且持久化的

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

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

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

2.MyISAM的I/O配置

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

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

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

* 內存映射使得MyISAM直接通過操作系統的頁面緩存訪問.MYD文件，避免系統調用的開銷，5.1後可以通過myisam_use_mmap選項打開內存映射

F.配置MySQL併發

1.InnoDB併發配置

* InnoDB有自己的“線程調度器”控制線程怎麼進入內核訪問數據，以及它們在內核中一次可以做哪些事，最基本的限制併發的方式是使用innodb_thread_concurrency變量，它會限制一次性可以有多少線程進入內核

* 併發值 = CPU數量 * 磁盤數量 * 2，在實踐中使用更小的值會更好一點

2.MyISAM併發配置

* 儘管MyISAM是表級鎖，它依然可以一邊讀取，一邊併發追加新行，這種情況下只能讀取到查詢開始時的所有數據，新插入的數據是不可見的，這樣可以避免不一致讀

* 通過設置concurrent_insert這個變量，可以配置MyISAM打開併發插入

* 讓INSERT、REPLACE、DELETE、UPDATE語句的優先級比SELECT語句更低，設置low_priority_updates選項就可以

G.基於工作負載的配置

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

2.優化BLOB和TEXT場景

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

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

* 讓臨時表更快一些：放在基於內存的文件系統

* 如果使用的是InnoDB，也可以調大InnoDB日誌緩衝大小

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

* 擴展存儲禁用了自適應哈希，因為需要完整地比較列的整個長度，才能發現是不是正確的數據

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

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

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

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

H.完成基本配置

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

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

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

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

I.安全和穩定的設置

1.expire_logs_days，如果啟用了二進制日誌，應該打開這個選項，可以讓服務器在指定的天數之後清理舊的二進制日誌

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

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

4.skip_name_resolve，禁用了另一個網絡相關和鑑權誰相關的陷阱：DNS查找

5.sql_mode，不建議修改

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

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

8.skip_slave_start，阻止MySQL試圖自動啟動複製

9.slave_net_timeout，控制備庫發現跟主庫的連接已經失敗並且需要重連之前等待的時間，設置為一分鐘或更短

10.sync_master_info、sync_relay_log、sync_relay_log_info，5.5以後版本可用，解決了複製中備庫長期存在的問題：不把它們的狀態文件同步到磁盤，所以服務器崩潰後可能需要人來猜測複製的位置實際上在主庫是哪個位置，並且可能在中繼日誌（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，有時需要把這個設置得相當高，才能穩定地刷新髒頁

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

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

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

九、操作系統和硬件優化

A.什麼限制了MySQL的性能

1.當數據可以放在內存中或者可以從磁盤中以足夠快的速度讀取時，CPU可能出現瓶頸，把大量的數據集完全放到大容量的內存中，以現在的硬件條件完全是可行的

2.I/O瓶頸，一般發生在工作所需的數據遠遠超過有效內存容量的時候，如果應用程序是分佈在網絡上的，或者如果有大量的查詢和低延遲的要求，瓶頸可能轉移到網絡上

B.如何為MySQL選擇CPU

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

2.當遇到CPU密集型的工作時，MySQL通常可以從更快的CPU中獲益，但還依賴於負載情況和CPU數量

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

4.多CPU在聯機事務處理（OLTP）系統的場景中非常有用，在這樣的環境中，併發可能成為瓶頸

C.平衡內存和磁盤資源

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

2.設計良好的數據庫緩存（如InnoDB緩衝池），其效率通常超過操作系統的緩存，因為操作系統緩存是為通用任務設計的

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

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

5.工作集包括數據和索引，所以應該採用緩存單位來計數，一個緩存單位是存儲引擎工作的數據最小單位

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

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

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

D.固態存儲

1.高質量閃存設備具備：

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

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

* 相比硬盤能更好地支持併發

* 提升隨機I/O和併發性

2.閃存的最重要特徵是可以迅速完成多次小單位讀取，但是寫入更有挑戰性。閃存不能在沒有做擦除操作前改寫一個單元（Cell），並且一次必須擦除一個大塊。擦除週期是緩慢的，並且最終會磨損整個塊

3.垃圾收集對理解閃存很重要。為了保持一些塊是乾淨的並且可以被寫入，設備需要回收髒塊。這需要設備上有一些空閒空間

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

5.固態存儲最適合使用在任何有著大量隨機I/O工作負載的場景下，隨機I/O通常是由於數據大於服務器的內存導致的，閃存設備可能大大緩解這種問題

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

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

8.Flashcache，磁盤和內存技術的結合，適合以讀為主的I/O密集型負載，並且工作集太大，用內存優化並不經濟的情況

9.優化固態存儲上的MySQL

* 增加InnoDB的I/O容量

* 讓InnoDB日誌文件更大

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

* 禁用預讀

* 配置InnoDB刷新算法

* 禁用雙寫緩衝的可能

* 限制插入緩衝大小，插入緩衝設計來用於減少當更新行時不在內存中的非唯一索引引起的隨機I/O

* InnoDB的頁大小

* 優化InnoDB頁面校驗（Checksum）的替代算法

E.為備庫選擇硬件

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

F.RAID性能優化

1.RAID可以幫助做冗餘、擴展存儲容量、緩存，以及加速

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

3.RAID 1：在很多情況下提供很好的讀性能，並且在不同的磁盤間冗餘數據，所以有很好的冗餘性，非常適合用來存放日誌或者類似的工作

4.RAID 5：通過分佈奇偶校驗把數據分散到多個磁盤，如果任何一個盤的數據失效，都可以從奇偶校驗塊中重建，但如果有兩個磁盤失效了，則整個卷的數據無法恢復，最經濟的冗餘配置。隨機寫是昂貴的，存放數據或者日誌是一種可接受的選擇，或者是以讀為主的業務

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

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

G.SAN和NAS

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

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

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

4.SAN的應用：

* 備份，可以只備份SAN

* 簡化容量規劃

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

* 高可用

* 服務器之間的交互

* 成本

H.使用多磁盤卷

1.二進制日誌和數據文件分離的真正的優勢，是減少事故中同時丟失數據和日誌文件的可能性

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

I.網絡配置

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

2.可以通過MySQL的back_log選項控制MySQL的傳入TCP連接隊列的大小，在每秒有很多連接創建和銷燬的環境中，默認值50是不夠的

3.網絡物理隔離也是很重要的因素，儘可能避免實時的跨數據中心的操作是明智的

J.選擇操作系統

1.一般企業級的MySQL部署在Windows上，但一般的企業級MySQL更多的還是部署在類UNIX操作系統上

K.選擇文件系統

1.如果可能，最好使用日誌文件系統，如ext3、ext4、XFS、ZFS或者JFS

2.可以調整文件系統的預讀行為，因為這可能也是多餘的

L.選擇磁盤隊列調度策略

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

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

M.線程

1.MySQL每個連接使用一個線程，另外還有內部處理線程、特殊用途的線程，以及所有存儲引擎創建的線程

2.MySQL確實需要內核級線程的支持，而不只是用戶級線程，這樣才能更有效地使用多個CPU，另外也需要有效的同步原子

N.內存交換區

1.內存交換對MySQL性能影響是很糟糕的，它破壞了緩存在內存的目的，並且相對於使用很小的內存做緩存，使用交換區的性能更差

2.在GNU/Linux上，可以用vmstat來監控內存交換，最好查看si和so列報告的內存交換I/O活動，這比看swpd列報告的交換區利用率更重要，最佳為0

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

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

5.使用MySQL的memlock配置項，可以把MySQL鎖定在內存

O.操作系統狀態

1.vmstat

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

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

* memory，swpd列顯示多少塊被換出到了磁盤，剩下的三個列顯示了多少塊是空閒的、多少塊正在被用作緩衝，以及多少正在被用作操作系統的緩存

* swap，顯示頁面交換活動

* io，顯示有多少塊從塊設備讀取（ bi）和寫出（bo）

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

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

2.iostat

* iostats -dx 5，每5秒刷新

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

* r/s和w/s，每秒發送到設備的讀和寫請求

* rsec/s和wsec/s，每秒讀和寫的扇區數

* avgrq-sz，請求的扇區數

* avgqu-sz，在設備隊列中等待的請求數

* await，磁盤排除上花費的毫秒數

* svctm，服務請求花費的毫秒數，不包括排除時間

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

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

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

5.發生內存交換的機器可能在swpd列有一個很高的值

十、複製

A.複製概述

1.MySQL支持兩種複製方式：基於行的複製和基於語句的複製，都是通過在主庫上記錄二進制日誌、在備庫重放日誌的方式來實現異步的數據複製

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

3.通過複製可以將讀操作指向備庫來獲得更好的讀擴展，但對於寫操作，除非設計得當，否則並不適合通過寫複製來擴展寫操作

4.複製解決的問題：

* 數據分佈

* 負載均衡

* 備份

* 高可用性和故障切換

* MySQL升級測試

5.複製如何工作

* 在主庫上把數據更新記錄到二進制日誌（Binary Log）中

* 備庫將主庫上的日誌複製到自己的中繼日誌（Relay Log）中

* 備庫讀取中繼日誌中的事件，將其重放到備庫數據之上

B.配置複製

1.在每臺服務器上創建複製帳號

* 用來監控和管理複製的帳號需要REPLICATION CLIENT權限，並且針對這兩種目的使用同一個帳號更加容易

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

2.配置主庫和備庫

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

* 有時候只開啟了二進制日誌，但卻沒有開啟log_slave_updates，可能會踫到一些奇怪的現象

* 如果可能的話，最好使用read_only配置選項，會阻止任何沒有特權權限的線程修改數據

3.通知備庫連接到主庫並從主庫複製數據

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之前只支持基於語句的複製（也稱為邏輯複製），主庫會記錄那些造成數據更改的查詢，當備庫讀取並重放這些事件時，實際上只是把主庫上執行過的SQL再執行一遍

* 好處是實現相當簡單，日誌更加緊湊，不會佔用太多帶寬

* 問題是基於語句的方式可能並不如其看起來那麼便利，還存在一些無法被正確複製的SQL，更新必須是串行的這需要更多的鎖

2.基於行的複製

* 5.1開始支持，會將實際數據記錄在二進制日誌中，跟其他數據庫的實現比較想像

* 好處是可以正確地複製每一行，一些語句可以被更加有效地複製

* 如果使用全表更新，則開銷會很大，因為每一行的數據都會被記錄到二進制日誌中，這使得二進制日誌事件非常龐大，並且會給主庫上記錄日誌和複製增加額外的負載，更慢的日誌記錄則會降低併發度

3.基於行或基於語句：哪種更優

* 基於語句的複製模式的優點：當主備的模式不同時，邏輯複製能夠在多種情況下工作；基於語句的方式執行復制的過程基本上就是執行SQL語句

* 基於語句的複製模式的缺點：很多情況下通過基於語句的模式無法正確複製，如果正在使用觸發器或者存儲過程，就不要使用基於語句的複製模式，除非能夠清楚地確定不會踫到複製的問題

* 基於行的複製模式的優點：幾乎沒有基於行的複製模式無法處理的場景；可能減少鎖的使用，並不要求這種強串行化是可重複的；會記錄數據變更；佔用更少的CPU；能夠幫助更快地找到並解決數據不致的情況

* 基於行的複製模式的缺點：無法判斷執行了哪些SQL；無法知道服務器在做什麼；在某些情況下，例如找不到要修改的行時，基於行的複製可能會導致複製停止

4.複製文件

* mysql-bin.index，二進制日誌文件

* mysql-relay-bin-index，中繼日誌的索引文件

* master.info，保存備庫連接到主庫所需要的信息

* relay-log.info，包含了當前備庫複製的二進制日誌和中繼日誌座標

5.發送複製事件到其他備庫：log_slave_updates，可以讓備庫變成其他服務器的主庫

6.複製過濾選項

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

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

D.複製拓撲

1.基本原則：

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

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

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

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

2.一主庫多備庫

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

4.主動-被動模式下的主主複製：其中一臺服務器是隻讀的被動服務器

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

6.環形複製：每個服務器都是在它之前的服務器的備庫，是在它之後的服務器的主庫

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

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

9.定製的複製方案

* 選擇性複製：配置replicate_wild_do_table

* 分離功能：OLTP、OLAP

* 數據歸檔：在備庫上保留主庫上刪除過的數據

* 將備庫用作全文檢索

* 只讀備庫：read_only選項

* 模擬多主庫複製

* 創建日誌服務器：創建沒有數據的日誌服務器，更加容易重放並且/或者過濾二進制日誌事件

E.複製和容量規劃

1.寫操作通常是複製的瓶頸，並且很難使用複製來擴展寫操作

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

F.複製管理和維護

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

2.從庫上，使用SHOW SLAVE STATUS

十一、可擴展的MySQL

A.什麼是可擴展性

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

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

B.擴展MySQL

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

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

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

4.向外擴展（橫向擴展、水平擴展）：複製、拆分、數據分片

* 按功能拆分（按職責拆分），不同的節點執行不同的任務

* 數據分片，把數據分割成一小片，或者一小塊，然後存儲到不同的節點中

* 選擇分區鍵（partitioning key）

* 多個分區鍵

* 跨分片查詢，使用C或Java編寫一個輔助應用來執行查詢並聚合結果集，也可以藉助彙總表來執行

* 分配數據、分片和節點

5.通過多實例擴展

6.通過集群擴展

* MySQL Cluster（NDB Cluster）

* Clustrix

* ScaleBase

* GenieDB

* Akiban

7.向內擴展，對不再需要的數據進行歸檔和清理

8.保持活躍數據獨立

C.負載均衡

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

十二、高可用性

A.什麼是高可用性

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

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

B.導致宕機的原因

1.運行環境問題，最普遍的問題是磁盤空間耗盡

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

3.糟糕的Schema和索引設計

4.複製問題通常由於主備數據不一致導致

5.數據丟失通常由於DROP TABLE的誤操作導致，並總是伴隨著缺少可用備份的問題

C.如何實現高可用性

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

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

* 儘量保證在發生宕機時能夠快速恢復

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

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

* 缺少嚴格的評估

* 沒有正確地監控MySQL的相關信息

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

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

D.避免單點失效

1.系統中任何不冗餘的部分都是一個可能失效的單點

2.可以採用兩種方法來為系統增加冗餘：增加空餘容量和重複組件

3.共享存儲或磁盤複製

* 能夠為數據庫服務器和存儲解耦合，通常使用的是SAN

* 兩個優點：可以避免除存儲外的其他任何組件失效所引起的數據丟失，併為非存儲組件建立冗餘提供可能

4.MySQL同步複製

* 當使用同步複製時，主庫上的事務只有在至少一個備庫上提交後才能認為其執行完成

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

* MySQL Cluster

* Percona XtraDB Cluster

5.基於複製的冗餘

* 複製管理器是使用標準MySQL複製來創建冗餘的工具

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

2.提升備庫或切換角色

3.虛擬IP地址或IP接管

4.中間件解決方案，可以使用代理、端口轉發、網絡地址轉換或者硬件負載均衡來實現故障轉移和故障恢復

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

十三、雲端的MySQL

A.雲的優點、缺點和相關誤解

1.優點：

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

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

* 隨著供應商發佈新的服務和成本降低，雲提供的價值越來越大

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

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

2.缺點：

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

* 無法保證容量和可用性

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

B.MySQL在雲端的經濟價值

1.雲託管比較適合尚處於初級階段的企業，或者那些持續接觸新概念並且本質上是以適用為主的企業

2.大量使用的策略是儘可能又快又便宜地開發和發佈應用

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

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

1.數據庫通常是一個應用系統中主要或唯一的有狀態並且持久化的組件

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

D.四種基礎資源

1.CPU通常少且慢

2.內在大小受限制

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

4.網絡性能還比較好

E.MySQL在雲主機上的性能

1.需要高併發的工作負載並不是非常適合雲計算

2.那些需要大量I/O的工作負載在雲中並不總是表現很好

F.MySQL數據庫即服務（DBaaS）

1.將數據庫本身作為雲資源

十四、應用層優化

A.常見問題

1.什麼東西在消耗系統中每臺主機的CPU、磁盤、網絡，以及內存資源？這些值是否合理？如果不合理，對應用程序做基本的檢查，看什麼佔用了資源

2.應用真是需要所有獲取到的數據嗎？

3.應用在處理本應由數據庫處理的事情嗎，或者反過來？

4.應用執行了太多的查詢？

5.應用執行的查詢太少了？

6.應用創建了沒必要的MySQL連接嗎？

7.應用對一個MySQL實例創建連接的次數太多了嗎？

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

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

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

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

B.Web服務器問題

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

2.如果用一個通用目的的Apache配置直接用於Web服務，最後很可能產生很多重量級的Apache進程

3.不要使用Apache來做靜態內容服務，或者至少和動態服務使用不同的Apache實例

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

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

* 打開gzip壓縮

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

C.緩存

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

2.應用可以緩存部分計算結果，所以應用層緩存可能比更低層次的緩存更有效，可以節省兩方面的工作：獲取數據以及基於這些數據進行計算，重點是緩存命中率可能更低，並且可能使用較多的內存

3.應用層緩存：

* 本地緩存

* 本地共享內存緩存

* 分佈式內存緩存

* 磁盤上的緩存

4.緩存控制策略

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

* 顯式失效，如果不能接受髒數據，那麼進程在更新原始數據時需要同時使緩存失效

* 讀時失效，在更改舊數據時，為了避免要同時失效派生出來的髒數據，可以在緩存中保存一些信息，當從緩存中讀數據時可以利用這些信息判斷數據是否已經失效

5.可以在後臺預先請求一些頁面，並將結果存為靜態頁面，好處：

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

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

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

D.MySQL的替代品

1.搜索：Lucene和Sphinx

2.簡單的鍵值存儲：Redis

3.結構化數據：Hadoop

十五、備份與恢復

A.為什麼要備份

1.災難恢復

2.人們改變想法

3.審計

4.測試

B.定義恢復需求

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

C.設計MySQL備份方案

1.建議

* 在生產實踐中，對於大數據庫來說，物理備份是必需的：邏輯備份太慢並受到資源限制，從邏輯備份中恢復需要很長時間

* 保留多個備份集

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

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

* 完全不借助備份工具本身來監控備份和備份的過程

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

* 對安全性要仔細考慮

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

3.邏輯備份優點：

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

* 恢復非常簡單

* 可能通過網絡來備份和恢復

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

* 非常靈活

* 與存儲引擎無關

* 有助於避免數據損壞

4.邏輯備份的缺點：

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

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

* 無法保證導出後再還原出來的一定是同樣的數據

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

5.物理備份優點：

* 基於文件的備份，只需要將需要的文件複製到其他地方即可

* 恢復簡單

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

6.物理備份缺點：

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

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

7.除非經過測試，不要假定備份是正常的

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

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

* 非顯著數據

* 代碼

* 複製配置

* 服務器配置

* 選定的操作系統

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

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

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

* 備份二進制日誌，每次備份後FLUSH LOGS

* 不要備份沒有改變的表

* 不要備份沒有改變的行

* 某些數據根本不需要備份

* 備份所有的數據，然後發送到一個有去重特性的目的地

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

13.文件一致性：每個文件的內部一致性

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

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

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

E.備份數據

1.生成邏輯備份

* SQL導出：mysqldump方式

* 符號分隔文件備份：使用SELECT INTO OUTFILE以符號分隔文件格式創建數據的邏輯備份

2.文件系統快照

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

F.從備份中恢復

1.恢復步驟：

* 停止MySQL服務器

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

* 將數據從備份中移到MySQL數據目錄

* 改變配置

* 改變文件權限

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

* 載入邏輯備份文件

* 檢查和重放二進制日誌

* 檢測已經還原的數據

* 以完全權限重啟服務器

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.監測工具

1.開源的監控工具

* Nagios

* Zabbix

* Zenoss

* OpenNMS

* Groundwork Open Source

* MRTG

* Cacti

* Ganglia

* Munin

2.商業監控系統

* MySQL Enterprise Monitor

* MONyog

* New Relic

* Circonus

* Monitis

* Splunk

* Pingdom

3.Innotop的命令行監控

閱讀更多 ZyBlog 的文章

關鍵字: Linux 共享 阻塞