一、MySQL架構與歷史

MySQL邏輯架構

• 最上層的服務并不是MySQL所獨有的，大多數基于網絡的客戶端/服務器的工具或者服務都有類似的架構。比如連接處理、授權認證、安全等等
• 第二層架構是MySQL比較有意思的部分。大多數MySQL的核心服務功能都在這一層，包括查詢解析、分析、優化、緩存以及所有的內置函數(例如，日期、時間、數學和加密函數)，所有跨存儲引擎的功能都在這一層實現:存儲過程、觸發器、視圖等
• 第三層包含了存儲引擎。存儲引擎負責MySQL中數據的存儲和提取。和GNU/Linux下的各種文件系統一樣，每個存儲引擎都有它的優勢和劣勢。服務器通過API與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。存儲引擎API包含幾十個底層函數，用于執行諸如“開始一個事務”或者。根據主鍵提取一行記錄"等操作。但存儲引擎不會去解析SQL性,不同存儲引擎之間也不會相互通信，而只是簡單地響應上層服務器的請求

優化與執行

MySQL會解析查詢，并創建內部數據結構（解析樹）,然后對其進行各種優化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。用戶可以通過特殊的關鍵字提示（hint）優化器，影響它的決策過程。也可以請求優化器解釋（explain） 優化過程的各個因素，使用戶可以知道服務器是如何進行優化決策的，并提供-一個參考基準，便于用戶重構查詢和schema、修改相關配置，使應用盡可能高效運行

優化器并不關心表使用的是什么存儲引擎，但存儲引擎對于優化查詢是有影響的。優化器會請求存儲引擎提供容量或某個具體操作的開銷信息，以及表數據的統計信息等

對于SELECT語句，在解析查詢之前，服務器會先檢查查詢緩存(Query Cache),如果能夠在其中找到對應的查詢，服務器就不必再執行查詢解析、優化和執行的整個過程，而是直接返回查詢緩存中的結果集

并發控制

MySQL在兩個層面實現并發控制：服務器層與存儲引擎層

在處理并發讀或寫時，可以通過實現一個由兩種鎖組成的系統來解決問題。這兩種鎖通常被稱為共享鎖和排他鎖，或者稱為讀鎖和寫鎖。讀鎖是共享的，或者說是相互不阻塞的，多個客戶可以在同時讀取同一數據。而寫鎖是排他的，同一時刻只能有一個用戶能夠寫入，并防止其他用戶讀取正在寫入的數據

鎖粒度是指加鎖的對象的大小。顯然，鎖的粒度越小，并發控制效率越高。鎖的各種操作，包括獲得鎖、檢查鎖和釋放鎖等，都會增加系統開銷。因此，如果系統花費大量時間來管理鎖，而不是用來獲取數據，就會影響系統性能

有兩種常見的縮策略，表鎖和行級鎖。表鎖開銷較小，但是并發控制不好。行級鎖可以很好地實現并發控制，但是開銷比較大

所謂的鎖策略，就是在鎖的開銷和數據的安全性之間尋求平衡，這種平衡當然也會影響到性能。大多數商業數據庫系統沒有提供更多的選擇,一般都是在表上施加行級鎖(row-level lock)，并以各種復雜的方式來實現，以便在鎖比較多的情況下盡可能地提供更好的性能。而MySQL則提供了多種選擇。每種MySQL存儲引擎都可以實現自己的鎖策略和鎖粒度

事務

事務將幾個操作作為一個整體，要么全部執行，要么全部放棄。事務的四大特性ACID：

• 原子性（atomicity）：一個事務必須被視為一個不可分割的最小工作單元，整個事務中所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務來說，不可能只執行其中的一部分操作
• 一致性（consistency）：數據庫總是從一個一致性的狀態轉換到另外一個一致性的狀態，比如有一個事務包括四步，從零加一，從一加一，從二加一，從三加一，那么根據一致原則，最后的結果一定是0或者4，從一個狀態轉換到另一個狀態
• 隔離性（isolation）：一個事務所做的修改在最終提交以前，對其他事務是不可見的
• 持久性（durability）：一旦事務提交，則其所做的修改就會永久保存到數據庫中

隔離級別

在SQL標準中定義了四種隔離級別，每一種級別都規定了一個事務中所做的修改，哪些在事務內和事務間是可見的，哪些是不可見的。較低級別的隔離通常可以執行更高的并發，系統的開銷也更低

• READ UNCOMMITTED (未提交讀)：在READ UNCOMITTED 級別，事務中的修改，即使沒有提交，對其他事務也都是可見的。事務可以讀取未提交的數據，這也被稱為臟讀(Dirty Read)
• READ COMITTED (提交讀)：一個事務開始時，只能看見已經提交的修改，并且所做的修改對其他事務不可見。這種存在的一個問題叫不可重復讀，就是指事務A讀取某條記錄之后，事務B對其進行了修改，這時當A再次讀取該數據的時候就會發現與之前讀取的結果不一樣
• REPEATABLE READ (可重復讀)：可重復讀解決了，數據不可重復讀的問題，但仍然不能解決幻讀，所謂幻讀，指的是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄時，會產生幻行(Phantom Row)。這是mysql的默認級別，mysql還可以通過MVCC+間隙鎖解決幻讀問題
• SERIALIZABLE (可串行化)：事務按照串行的方式執行，并在每行數據上加鎖，可能產生大量的超時和鎖爭用問題

四種隔離級別與可避免操作

• Read uncommitted【級別最低，什么都避免不了】
• Read committed【可避免臟讀】
• Repeatable read【可避免臟讀，不可重復讀】
• Serializable【可避免臟讀，不可重復讀，虛讀】

臟讀、不可重復讀與幻讀(虛讀)

• 臟讀：一個事務讀取到另外一個事務未提交的數據
• 不可重復讀：一個事務讀取到另外一個事務已經提交的數據，也就是說一個事務可以看到其他事務所做的修改
• 幻讀(虛讀)：是指在一個事務內讀取到了別的事務插入的數據，導致前后讀取不一致

死鎖

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

事務日志

存儲引擎在修改表的數據時只需要修改其內存拷貝，再把該修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據本身持久到磁盤。事務日志持久以后，內存中被修改的數據在后臺可以慢慢地刷回到磁盤，稱為預寫式日志（Write-Ahead Logging），如果數據的修改已經記錄到事務日志并持久化，但數據本身還沒有寫回磁盤，此時系統崩潰，存儲引擎在重啟時能夠自動恢復這部分修改的數據。具體的恢復方式則視存儲引擎而定。MySQL提供了兩種事務型的存儲引擎: InnoDB和NDB Cluster，也有一些第三方存儲引擎

多版本并發控制

MySQL的大多數事務型存儲引擎實現的都不是簡單的行級鎖。基于提升并發性能的考慮，它們一般都同時實現了多版本并發控制(MVCC)。可以認為MVCC是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然大多引擎實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只鎖定必要的行。典型的有樂觀(optimistic) 并發控制和悲觀(pessimistic) 并發控制

MVCC的實現，是通過保存數據在某個時間點的快照來實現的，且只在REPEATABLE READ 和READ COMMITTED 兩個隔離級別下工作。其他兩個隔離級別都和MVCC不兼容,因為READ UNCONMITTED總是讀取最新的數據行，而不是符合當前事務版本的數據行。而SERIALIZABLE則會對所有讀取的行都加鎖

MySQL的存儲引擎

在文件系統中，MySQL將每個數據庫( 也可以稱之為schema)保存為數據目錄下的一個子目錄。創建表時，MySQL會在數據庫子目錄下創建一個和表同名的frm文件保存表的定義。可以使用SHOW TABLE STATUS LIKE ‘tablename’ \G 顯示表的相關信息

• Name：表名
• Engine：表使用的引擎
• Version：表版本信息
• Row_format：行的格式，一般包含可變長度的字段，如VARCHAR或BLOB
• Rows：表中的行數
• Avg_row_ length：平均每行的字數
• Data_ length：表數據的大小
• Max_ data_ length：表的最大容量，該值和存儲引擎有關
• Index_length：索引的大小
• Data_free：對于MyISAM表，表示已分配但目前沒有使用的空間
• Auto_ increment：下一個AUTO_ INCREMENT 的值,
• Create_time：表的創建時間
• Update_ time： 表的最后修改時間
• Check_ time：使用CKECK TABLE命令或者myisamchk工具最后一次檢查表的時間
• Col lation：表的默認字符集和字符列排序規則
• Checksum:：如果啟用，保存的是整個表的實時校驗和
• Create_options：創建表時指定的其他選項
• Comment ：創建表時的注釋

InnoDB 存儲引擎

InnoDB是MySQL的默認事務型引擎，也是最重要、使用最廣泛的存儲引擎。InnoDB的性能和自動崩潰恢復特性，使得它在非事務型存儲的需求中也很流行。除非有非常非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎

InnoDB采用MVCC來支持高并發，井且實現了四個標準的隔離級別。其默認級別是REPEATABLE READ (可重復讀),并且通過 間隙鎖(next-key locking) 策略防止幻讀的出現。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入

InnoDB內部做了很多優化，包括從磁盤讀取數據時采的可預測性預讀，能夠自動在內存中創建hash索引以加速讀操作的自適應哈希索引(adaptive hash index),以及能夠加速插人操作的插人緩沖區(insert buffer)等。后面將更詳細地討論這些內容

MyISAM 存儲引擎

在MySQL 5.1 及之前的版本，MyISAM是默認的存儲引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(GIS) 等，但MyISAM不支持事務和行級鎖，而且有一個毫無疑問的缺陷就是崩潰后無法安全恢復。所以MySQL 5.1以后版本的MySQL已經默認不用MyISAM引擎了，相對于InnoDB那一點點的并發量的增加(需要一些條件)，這些缺點讓我們不在默認引擎為MyISAM，百分之九十以上情況我們需要InnoDB，MyISAM所擁有的優點大多已能在InnoDB找到代替方案，其它引擎也一樣，所以，如果不是非常特殊的要求，我們大多情況使用InnoDB引擎，MyISAM已有的特性

• 加鎖與并發：MyISAM對整張表加鎖，而不是針對行
• 修復：對于MyISAM表，MySQL可以手工或者自動執行檢查和修復操作，但這里說的修復和事務恢復以及崩潰恢復是不同的概念。執行表的修復可能導致一些數據丟失，而且修復操作是非常慢的
• 索引：支持索引，且支持全文分詞索引

MyISAM 性能

MyISAM引擎設計簡單，數據以緊密格式存儲，所以在某些場景下的性能很好，比如，數據只讀，表比較小，可以忍受修復失敗，的場景下。不要輕易相信 “MyISAM比InnoDB快” 之類的話，在很多場景下，這個結論是不對的，甚至InnoDB的速度會比MyISAM望塵莫及，比如使用到聚簇索引時

其它引擎

MySQL還有一些有特殊用途的存儲引擎。在新版本中，有些可能因為一些原因已經不再支持，就不一一說明了。那么，引擎這么多，我們應該怎么選擇呢，歸納為一句話：“除非需要用到某些InnoDB不具備的特性，并且沒有其他辦法可以替代，否則都應該優先選擇InnoDB引擎。" 輕易切換引擎，轉則讓事情復雜難搞，重則翻車跑路

二、MySQL 基準測試

基準測試(benchmark) 也譯做標準檢查程序，是MySQL新手和專家都需要掌握的一項基本技能。簡單地說，基準測試是針對系統設計的一種壓力測試。通常的目標是為了掌握系統的行為。對調優而言，準確的測試相當重要，只有測試正確才知道問題出在哪里，才能加以改進

基準測試完成的工作

• 驗證基于系統的一些假設，確認這些假設是否符合實際情況
• 重現系統中的某些異常行為，以解決這些異常
• 測試系統當前的運行情況
• 模擬比當前系統更高的負載，以找出系統隨著壓力增加而可能遇到的擴展性瓶頸
• 規劃未來的業務增長
• 測試應用適應可變環境的能力
• 測試不同的硬件、軟件和操作系統配置
• 證明新采購的設備是否配置正確

基準測試的策略

基準測試有兩種主要的策略：一是針對整個系統的整體測試，另外是單獨測試MySQL。這兩種策略也被稱為集成式(ull-stack) 以及 單組件式(single-component) 基準測試。針對整個系統做集成式測試，而不是單獨測試MySQL的原因主要有以下幾點:

• 用戶關注的是整體性能
• MySQL并非總是性能瓶頸
• 做整體測試，才能發現各部分之間的緩存帶來的問題

測試何種指標

• 吞吐量：指單位時間內的事務處理數，常用的測試單位是每秒事務數（TPS），或每分鐘事務數（TPM）
• 響應時間或者延遲：用于測試任務所需的整體時間，根據具體的應用，測試的時間單位可能是微秒、毫秒、秒或者分鐘。通常使用百分比響應時間（percentile response time）來替代最大響應時間
• 并發性：需要關注的是正在工作中的并發操作，或者是同時工作中的線程數或者連接數，在測試期間記錄MySQL數據庫的Threads_running狀態值
• 可擴展性：給系統增加一倍的工作，在理想情況下就能獲得兩倍的效果（即吞吐量增加一倍），對于容量規范非常有用，可以提供其他測試無法提供的信息，來幫助發現應用的瓶頸

基準測試方法

設計測試條件時，應盡量使測試過程與真實應用的場景接近，基準測試方法的一常見錯誤

• 使用真實數據的子集而不是全集
• 使用錯誤的數據分布
• 在多用戶場景中，只做單用戶的測試
• 在單服務器上測試分布式應用
• 與真實用戶行為不匹配
• 反復執行同一個查詢，導致全部或部分緩存
• 沒有檢查錯誤
• 忽略了系統預熱(warm up)的過程
• 使用默認的服務器配置
• 測試時間太短
• 收集的測試信息不夠全

基準測試的一些建議

• 應該建立將參數和結果文檔化的規范，每一輪測試都必須進行詳細記錄
• 基準測試應該運行足夠長的時間，需要在穩定狀態下測試并觀察
• 在執行基準測試時，需要盡可能多地收集被測試系統的信息
• 自動化基準測試可以防止測試人員偶爾遺漏某些步驟，或者誤操作，另外也有助于歸檔整個測試過程，可以選擇shell、php、perl等
• 要盡可能使所有測試過程都自動化，包括裝載數據、系統預熱、執行測試、記錄結果等

三、服務器性能剖析

性能優化簡介

• 性能，為完成某件任務所需要的時間度量，性能即響應時間
• 如果目標是降低響應時間，就需要理解為什么服務器執行查詢需要這么多時間，然后去減少或者消除那些對獲得查詢結果來說不必要的工作。無法測量就無法有效地優化
• 性能剖析（profiling）是測量和分析時間花費在哪里的主要方法，一般有兩個步驟：測量任務所花費的時間，對結果進行統計和排序

對應用程序進行性能剖析

性能瓶頸可能的影響因素

• 外部資源
• 應用需要處理大量的數據
• 在循環中執行昂貴的操作
• 使用了低效的算法

診斷間歇性問題

間歇性的問題比如系統偶爾停頓或者慢查詢，很難診斷。有些幻影問題只在沒有注意到的時候才發生，而且無法確認如何重現，診斷這樣的問題往往要花費很多時間，此時應注意：

• 盡量不要用試錯的方式來解決問題，如果一時無法定位，可能是測量的方式不正確，或者測量的點選擇有誤，或者使用的工具不合適
• 確定單條查詢問題還是服務器問題
• 捕獲盡可能多的診斷數據

其它注意點

• 定義性能最有效的方法是響應時間
• 如果無法測量就無法有效地優化，所以性能優化工作需要基于高質量、全方位及完整的響應時間測量
• 測量的最佳開始點是應用程序，而不是數據庫。即使問題出在底層的數據庫，借助良好的測量也可以很容易地發現問題
• 大多數系統無法完整地測量，測量有時候也會有錯誤的結果
• 完整的測量會產生大量需要分析的數據，所以需要用到剖析器
• 有兩種消耗時間的操作：工作或者等待
• 優化和提升是兩回事。當繼續提升的成本超過收益的時候，應當停止優化

總體來說，解決性能問題的方法，首先是要澄清問題，然后選擇合適的技術來解答這些問題。

四、Schema與數據類型優化

良好的邏輯設計和物理設計是高性能的基石，應該根據系統將要執行的查詢語句來設計schema，這往往需要權衡各種因素。例如，反范式的設計可以加快某些類型的查詢，但同時可能使另一些類型的查詢變慢

選擇優化的數據類型

MySQL支持的數據類型非常多，選擇正確的數據類型對于獲得高性能至關重要。有以下幾條建議：

• 更小的通常更好：一般情況下，應該盡量使用可以正確存儲數據的最小數據類型。更小的數據類型通常更快，因為它們占用更少的磁盤、內存和CPU緩存，并且處理時需要的CPU周期也更少
• 簡單就好：簡單數據類型的操作通常需要更少的CPU周期。例如，整型比字符比較操作代價更低
• 盡量避免NULL：很多表都包含可為NULL (空值)的列，即使應用程序并不需要保存NULL也是如此，這是因為可為NULL是列的默認屬性。通常情況下最好指定列為NOT NULL,除非真的需要存儲NULL值。如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的存儲空間，在MySQL里也需要特殊處理

在為列選擇數據類型時，第一步需要確定合適的大類型:數字、字符串、時間等，第二步就是選擇類型的精度

整數類型

MySQL整數可以指定寬度，如int(11)，對大多數應用這是沒有意義的：它不會限制值的合法范圍，只是規定了MySQL的一些交互工具用來顯示字符的個數。對于存儲和計算來說，int(20)和int(1)是相同的

實數類型

因為需要額外空間和計算開銷，所以盡量只在對小數進行計算的時候才使用decimal。在數據量比較大的時候，可以考慮使用bigint代替decimal，將存儲的單位根據小數的位數乘以相應的倍數即可。例如：銀行存儲財務數據精確到萬分之一分，則可以把所有金額乘以一百萬,然后將結果存儲在BIGINT里，這樣可以同時避免浮點存儲計算不精確和DECIMAL精確計算代價高的問題

字符串類型

CHAR適合存儲短的短的字符串，或者所有的值都接近同一長度。對于經常變更的數據，CHAR也比VARCHAR好，因為定長的CHAR不容易產生碎片。對于非常短的字符串，CHAR也比VARCHAR更好，因為VARCHAR還需要1或2個額外的字節存儲字符串長度。VARCHAR適合變長范圍比較大的數據

可以使用枚舉替代常用的字符串類型，枚舉可以把一些不重復的字符串存儲成預定義的集合。枚舉字段是按照內部存儲的整數而不是字符串進行排序的。枚舉不好的地方是當向枚舉中增加字段的時候,需要使用ALTER TABLE語句來進行修改。所以，對將來可能會變的字符串，使用枚舉不是個好的主意

日期和時間類型

通常應該盡量使用TIMESTAMP，它比DATETIME空間效率更高，如果需要將時間保存到毫秒級別，可以使用BIGINT

位數據類型

MySQL有少數幾種存儲類型使用緊湊的位存儲數據。所有這些位類型，不管底層存儲格式和處理方式如何，從技術上來說都是字符串類型

可以使用BIT列在一列中存儲一個或多個true/false值。BIT(1)定義一個包含單個位的字段，bit(2)存儲兩個位。bit列最多存儲64個位MySQL將BIT當作字符串類型，而不是數字類型。當檢索bit(1)時，結果是一個包含二進制0或1值的字符串，而不是ASCII碼的"0"或"1"。然后，在數字上下文場景中檢索時，結果將是為字符串轉換成的數字

選擇標識符

如果存儲UUID值，則應該移除“-” 符號；或者更好的做法是，用UNHEX()函數轉換。UUID值為16字節的數字，并且存儲在一個BINARY(16)列中。檢索時可以通過HEX()函數來格式化為十六進制格式

關于對象關系映射(ORM)系統

寫得很爛的schema遷移程序，或者自動生成schema的程序，都會導致嚴重的性能浪費問題。有些程序存儲任何東西都會使用很大的VARCHAR列，對象關系映射(ORM) 系統（以及使用它們的“框架”）是另一種常見的性能噩夢，一些ORM系統會存儲任意類型的數據到任意類型的后端數據存儲中，這通常意味著其沒有設計使用更優的數據類型來存儲這種設計對開發者很有吸引力，因為這使得他們可以用面向對象的方式工作，不需要考慮數據是怎么存儲的。然而，“對開發者隱藏復雜性”的應用通常不能很好地擴展。我們建議在用性能交換開發人員的效率之前仔細考慮，并且總是在真實大小的數據集.上做測試，這樣就不會太晚才發現性能問題

MySQL schema設計中的陷阱

• 太多的列：列太多，所需的列不多，容易造成轉換問題
• 太多的關聯：mysql限制最多關聯61張表，單個查詢建議在12個表以內
• 全能的枚舉：注意防止過度使用枚舉
• 變相的枚舉：枚舉（ENUM）和集合（SET）不要混用
• 非此發明(Not Invent Here)的NULL：盡量避免使用NULL，且建議盡可能地考慮替代方案，但如果除了NULL值，沒有更好的代替值時，盡情使用NULL吧，因為強行使用代替值，可能會有造成一些程序問題

范式與反范式

范式的優點和缺點

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

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

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

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

  范式化設計的schema的缺點是通常需要關聯。稍微復雜一些的查詢語句在符合范式的schema上都可能需要至少一次關聯，也許更多。這不但代價昂貴，也可能使一些索引策略無效。例如，范式化可能將列存放在不同的表中，而這些列如果在-一個表中本可以屬于同一個索引

反范式的優點和缺點

• 避免了關聯

• 避免了隨機I/O

• 更有效的使用索引策略

反范式的缺點是容易造成數據冗余，并且如果表的列數過多，使用的不多，會造成查詢轉換性能浪費

事實是，完全的范式化和完全的反范式化schema都是實驗室里才有的東西：在真實世界中很少會這么極端地使用。在實際應用中經常需要混用，可能使用部分范式化的schema、緩存表，以及其他技巧

緩存表、匯總表和計數表

緩存表表示存儲那些可以簡單地從schema其他表獲取數據的表。

匯總表保存的是使用GROUP BY語句聚合數據的表，使用匯總表的原因是，實時計算和統計值是很昂貴的操作，因為要么需要掃描表中的大部分數據，要么只能在某些索引上才能有效運行

計數器表是用來統計某個操作的次數的表，我們可以在一個表中定義一個名為cnt的字段來表示操作的次數，然后每次執行了操作之后將其加1。但是，加1需要更新操作來完成，每次更新的時候要獲取記錄的鎖，因此并發效率不高。解決這個問題，我們可以再增加一些字段slot作為隨機的槽，例如增加100個slot字段，每次執行操作的時候，我們使用隨機數選擇某個slot，并對其進行+1更新（只用鎖住部分數據，因此效率比較高）。最后統計的時候將全部記錄加起來即可

更慢的寫，更快的讀

為了提升讀查詢的速度，經常會需要建一些額外索引，增加冗余列，甚至是創建緩存表和匯總表。這些方法會增加寫查詢的負擔，也需要額外的維護任務，但在設計高性能數據庫時，這些都是常見的技巧：雖然寫操作變得更慢了，但更顯著地提高了讀操作的性能

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE 操作的性能對大表來說是個大問題。MySQL執行大部分修改表結構操作的方法是，鎖住表，然后用新的結構創建一個空表，從舊表中查出所有數據插入新表，然后刪除舊表。這樣操作可能需要花費很長時間，如果內存不足而表又很大，而且還有很多索引的情況下尤其如此。一般而言，大部分ALTER TABLE操作將導致MySQL服務中斷，這里介紹兩種DDL操作技巧，避免這種情況：

• 一是在一臺不提供服務的機器上執行ALTER TABLE操作，然后和提供服務的主庫進行切換
• 二是通過“影子拷貝”，創建一張新表，然后通過重命名和刪表操作交換兩張表及里面的數據

五、創建高性能的索引

索引(在MySQL中也叫做“鍵(key)") 是存儲引擎用于快速找到記錄的一種數據結構。索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易將查詢性能提高幾個數量級。

索引基礎

數據庫的索引類似于書的索引，實際的查找某個值的時候，先按照值進行查找，然后返回包含該值的數據行。索引可以包含一個或多個列的值，如果索引包含多個列，那么列的順序也很重要，索引對多個列排序的依據是CREATE TABLE時定義索引的順序，所以MySQL只能高效地使用索引的最左前綴列，這里要注意的是，創建一個包含多個列的索引，和創建多個只包含一列的索引是大不相同的

ORM工具能夠產生符合邏輯的、合法的查詢，除非只是生成非常基本的查詢（例如僅是根據主鍵查詢），否則它很難生成適合索引的查詢

索引的類型

索引有很多種類型，可以為不同的場景提供更好的性能。在MySQL中，索引是在存儲引擎層而不是服務器層實現的。所以，并沒有統一的索引標準：不同存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支持所有類型的索引。即使多個存儲引擎支持同一種類型的索引，其底層的實現也可能不同

B-Tree索引

通常人們所說的索引。實際上很多存儲引擎使用的是B+Tree。這里B-Tree索引適用于全鍵值、鍵值范圍或鍵前綴查找，B-Tree通常意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同

類型，以多列索引key(last_name, first_name, dob)為例：

• 全值匹配：指定查詢的人的fitst_name, last_name和dob
• 匹配最左前綴：查找指定了last_name的記錄
• 匹配列前綴：匹配某一列的值的開頭部分，比如last_name以J開頭
• 匹配范圍值：匹配某兩個索引之間的值，例如Allen和Barrymore之間的人
• 精確匹配某一列并范圍匹配另一列：查找last_name為Allen，并且first_name以k開頭的
• 只訪問索引的查詢：B-Tree通常可以支持只訪問索引的查詢，即查詢只需要訪問索引，而無需訪問數據行

因為索引樹中的節點是有序的，所以除了按值查找之外，索引還可以用于查詢中的ORDER BY 操作( 按順序查找)。一般來說，如果B-Tree可以按照某種方式查找到值，那么也可以按照這種方式用于排序。所以，如果ORDERBY子句滿足前面列出的幾種查詢類型，則這個索引也可以滿足對應的排序需求

B-Tree的一些限制：

• 如果不是按照從最左列開始查找，則無法使用索引。例如無法查找只指定了first_name或者dob的記錄
• 不能跳過索引中的列：不能在查找的時候只指定了last_name和dob，那么dob不會使用索引
• 如果查詢的時候有某個列的查詢范圍，則其右邊的所有列都無法使用索引優化查找。比如對last_name使用了like，那么first_name和dob將不會使用索引

哈希索引

哈希索引(hash index)基于哈希表實現，只有精確匹配索引所有列的查詢才有效。對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼(hash code),哈希碼是一個較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針，因為索引自身只需存儲對應的哈希值，所以索引的結構十分緊湊，這也讓哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行。不過，訪問內存中的行的速度很快，所以大部分情況下這一點對性能的影響并不明顯
• 哈希索引數據并不是按照索引值順序存儲的，所以也就無法用于排序
• 哈希索引也不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的。例如，在數據列(A,B) 上建立哈希索引,如果查詢只有數據列A，則無法使用該索引
• 哈希索引只支持等值比較查詢，包括=、IN()、<=> (注意<>和<=>是不同的操作)。也不支持任何范圍查詢，例如WHERE price > 100
• 訪問哈希索引的數據非常快，除非有很多哈希沖突(不同的索引列值卻有相同的哈希值)。當出現哈希沖突的時候，存儲引擎會把值存到鏈表中，所以此時必須遍歷鏈表中所有的行指針，逐行進行比較，直到找到所有符合條件的行
• 如果哈希沖突很多的話，一些索引維護操作的代價也會很高。沖突越多，代價越大

因為這些限制，哈希索引只適用于某些特定的場合。而一旦適合哈希索引，則它帶來的性能提升將非常顯著。

空間數據索引(R-Tree)

MyISAM表支持空間索引，可以用作地理數據存儲

全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。它有許多需要注意的細節，如停用詞、詞干和復數、布爾搜索等。全文索引更類似于搜索引擎做的事情，而不是簡單的WHERE條件匹配。在相同的列上同時創建全文索引和基于值的B-Tree索引不會有沖突，全文索引適用于MATCH AGAINST 操作，而不是普通的WHERE條件操作

還有很多第三方的存儲引擎使用不同類型的數據結構來存儲索引，例如分型樹索引等

索引的優點

索引可以讓服務器快速地定位到表的指定位置。但是這并不是索引的唯一作用，到目前為止可以看到，根據創建索引的數據結構不同，索引也有一些其他的附加作用。總結下來索引共有三個優點：

• 索引大大減少了服務器需要掃描的數據量
• 索引可以幫助服務器避免排序和臨時表
• 索引可以將隨機I/O變為順序I/O

索引三星系統

• 索引將相關的記錄放到一起則獲得一星
• 如果索引中的數據順序和查找中的排序一致則獲得二星
• 如果索引中的列包含了查詢中需要的全部列則獲得三星

對于小型的表，使用全表掃描更高效；對中到大型的表，使用索引非常有效。對于特大型的表，建立和使用索引的代價會隨之增長。這種情況下可以使用分區來查出一組數據，而不是一條一條地匹配

高性能的索引策略

MySQL EXPLAIN

EXPLAIN命令是查詢表信息的工具，我們可以根據查詢的信息做出正確的優化行為，所以這里有必要加一下它的使用介紹

• id：執行編號，標識select所屬的行。如果在語句中沒子查詢或關聯查詢，只有唯一的select，每行都將顯示1。否則，內層的select語句一般會順序編號，對應于其在原始語句中的位置
• select_type：SELECT類型,可以為以下任何一種
  • SIMPLE:簡單SELECT(不使用UNION或子查詢)
  • PRIMARY:最外面的SELECT
  • UNION:UNION中的第二個或后面的SELECT語句
  • DEPENDENT UNION:UNION中的第二個或后面的SELECT語句,取決于外面的查詢
  • UNION RESULT:UNION 的結果
  • SUBQUERY:子查詢中的第一個SELECT
  • DEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決于外面的查詢
  • DERIVED:導出表的SELECT(FROM子句的子查詢)
• table：訪問引用哪個表（引用某個查詢，如“user”）
• type：聯接類型。下面給出各種聯接類型,按照從最佳類型到最壞類型進行排序:
  • system:表僅有一行(=系統表)。這是const聯接類型的一個特例
  • const:表最多有一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被優化器剩余部分認為是常數。const表很快,因為它們只讀取一次!
  • eq_ref:對于每個來自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯接類型,除了const類型
  • ref:對于每個來自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取
  • ref_or_null:該聯接類型如同ref,但是添加了MySQL可以專門搜索包含NULL值的行
  • index_merge:該聯接類型表示使用了索引合并優化方法
  • unique_subquery:該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數,可以完全替換子查詢,效率更高
  • index_subquery:該聯接類型類似于unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range:只檢索給定范圍的行,使用一個索引來選擇行
  • index:該聯接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數據文件小
  • ALL:對于每個來自于先前的表的行組合,進行完整的表掃描
• possible_keys：揭示哪一些索引可能有利于高效的查找
• key：顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL
• key_len：顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL
• ref：顯示使用哪個列或常數與key一起從表中選擇行
• rows：顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的數據相乘可以估算要處理的行數
• filtered：顯示了通過條件過濾出的行數的百分比估計值
• Extra：該列包含MySQL解決查詢的詳細信息
  • Distinct:MySQL發現第1個匹配行后,停止為當前的行組合搜索更多的行
  • Not exists:MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行后,不再為前面的的行組合在該表內檢查更多的行
  • range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用
  • Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行
  • Using index:從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息
  • Using temporary:為了解決查詢,MySQL需要創建一個臨時表來容納結果
  • Using where:WHERE 子句用于限制哪一個行匹配下一個表或發送到客戶
  • Using sort_union(…), Using union(…), Using intersect(…):這些函數說明如何為index_merge聯接類型合并索引掃描
  • Using index for group-by:類似于訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實際的表

獨立的列

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

前綴索引和索引的選擇性

索引很長的字符串會讓索引變得大且慢。通常可以只索引開始部分的字符，這樣可以節約索引空間，從而提高索引的效率。缺點是會降低索引的選擇性。索引的選擇性是指不重復的索引值和記錄總數的比，顯然越大越好。所以，我們需要選擇足夠長的前綴來保證選擇性，同時又不能太長以降低索引空間

我們可以使用語句

select count(distinct left(col_name, 3)) / count(*) from tbl_name;

來統計使用3個字符的前綴選擇性，同理可以計算出4個，5個等的情況。最后，選擇一個合理的前綴長度即可。選擇了長度之后可以像下面這樣設置指定長度的索引：

alter table add key(col_nane(4));

多列索引

很多人對多列索引的理解都不夠。一個常見的錯誤就是，為每個列創建獨立的索引，或者按照錯誤的順序創建多列索引

在多個列上建立獨立的單列索引大部分情況下并不能提高MySQL的查詢性能。MySQL5.0和更新版本引入了一種叫“ 索引合并”(index merge)的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。更早版本的MySQL只能使用其中某一個單列索引，然而這種情況下沒有哪一個獨立的單列索引是非常有效的

但在MySQL 5.0和更新的版本中，查詢能夠同時使用這兩個單列索引進行掃描，并將結果進行合并。這種算法有三個變種：OR條件的聯合(union) ，AND條件的相交(intersection)，組合前兩種情況的聯合及相交。

索引合并策略有時候是一種優化的結果，但實際上更多時候說明了表上的索引建得很糟糕:

• 當出現服務器對多個索引做相交操作時(通常有多個AND條件)，通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引。
• 當服務器需要對多個索引做聯合操作時(通常有多個OR條件)，通常需要耗費大量CPU和內存資源在算法的緩存、排序和合并操作上。特別是當其中有些索引的選擇性不高，需要合并掃描返回的大量數據的時候
• 更重要的是，優化器不會把這些計算到“查詢成本”(cost) 中，優化器只關心隨機頁面讀取。這會使得查詢的成本被“ 低估”,導致該執行計劃還不如直接走全表掃描。這樣做不但會消耗更多的CPU和內存資源，還可能會影響查詢的并發性，但如果是單獨運行這樣的查詢則往往會忽略對并發性的影響。通常來說，還不如像在MySQL4.1或者更早的時代一樣，將查詢改寫成UNION的方式往往更好

如果在EXPLAIN中看到有索引合并，應該好好檢查一下查詢和表的結構，看是不是已經是最優的。也可以通過參數optimizer_ switch 來關閉索引合并功能。也可以使用IGNORE INDEX 提示讓優化器忽略掉某些索引

選擇合適的索引順序

如果要對多個列建立一個索引，除了上面的問題之外，還應該考慮所建的索引中列的順序。比如，對col1, col2兩列數據建立索引，那么我們的順序應被指定為(col1, col2)還是(col2, col1)呢。我們要先知道，在一個多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，等等。所以，在沒有特殊要求的情況下，我們可以使用選擇性來解決這個問題，我們可以將選擇性比較高的列作為索引的第一列，另一列作為第二列，以次類推

聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式。具體的細節依賴于其實現方式，但InnoDB的聚簇索引實際上在同一個結構中保存了B-Tree 索引和數據行

當表有聚簇索引時，它的數據行實際上存放在索引的葉子頁(leaf page)中。術語“聚簇”表示數據行和相鄰的鍵值緊湊地存儲在一起。因為無法同時把數據行存放在兩個不同的地方，所以一個表只能有一個聚簇索引

因為是存儲引擎負責實現索引，因此不是所有的存儲引擎都支持聚簇索引。這里我們主要關注InnoDB，但是這里討論的原理對于任何支持聚簇索引的存儲引擎都是適用的。

下圖展示了聚簇索引中的記錄是如何存放的。注意到，葉子頁包含了行的全部數據，但是節點頁只包含了索引列。在這個案例中，索引列包含的是整數值，圖中的索引列即為主鍵列

如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。InnoDB只聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠

聚簇主鍵可能對性能有幫助，但也可能導致嚴重的性能問題。所以需要仔細地考慮聚簇索引，尤其是將表的存儲引擎從InnoDB改成其他引擎的時候(反過來也一樣)

聚集的數據有一些重要的優點:

• 可以把相關數據保存在一起。例如實現電子郵箱時，可以根據用戶ID來聚集數據，這樣只需要從磁盤讀取少數的數據頁就能獲取某個用戶的全部郵件。如果沒有使用聚簇索引，則每封郵件都可能導致一次磁盤I/O
• 數據訪問更快。聚簇索引將索引和數據保存在同一個B-Tree中，因此從聚簇索引中獲取數據通常比在非聚簇索引中查找要快
• 使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值

如果在設計表和查詢時能充分利用上面的優點，那就能極大地提升性能。同時，聚簇索引也有一些缺點:

• 聚簇數據最大限度地提高了I/O密集型應用的性能，但如果數據全部都放在內存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優勢了
• 插入速度嚴重依賴于插入順序。按照主鍵的順序插入是加載數據到InnoDB表中速度最快的方式。但如果不是按照主鍵順序加載數據，那么在加載完成后最好使用OPTIMIZE TABLE 命令重新組織一下表
• 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置
• 基于聚簇索引的表在插人新行，或者主鍵被更新導致需要移動行的時候，可能面臨“頁分裂(page split)” 的問題。當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導致表占用更多的磁盤空間
• 聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致數據存儲不連續的時候
• 二級索引(非聚簇索引)可能比想象的要更大，因為在二級索引的葉子節點包含了引用行的主鍵列
• 二級索引訪問需要兩次索引查找，而不是一次

最后一點可能讓人有些疑惑，為什么二級索引需要兩次索引查找？答案在于二級索引中保存的“行指針”的實質。要記住，二級索引葉子節點保存的不是指向行的物理位置的指針，而是行的主鍵值
這意味著通過二級索引查找行，存儲引擎需要找到二級索引的葉子節點獲得對應的主鍵值，然后根據這個值去聚簇索引中查找到對應的行。這里做了重復的工作:兩次B-Tree查找而不是一次。對于InnoDB，自適應哈希索引能夠減少這樣的重復工作

InnoDB和MyISAM的數據分布對比

聚簇索引和非聚簇索引的數據分布有區別，以及對應的主鍵索引和二級索引的數據分布也有區別，通常會讓人感到困擾和意外。來看看InnoDB和MyISAM是如何存儲下面這個表的:

CREATE TABLE layout_test (col1 int NOT NULL,col2 int NOT NULL,PRIMARY KEY(col1),KEY(col2) );

假設該表的主鍵取值為1 ~ 10 000,按照隨機順序插入并使用OPTIMIZE TABLE 命令做了優化。換句話說，數據在磁盤上的存儲方式已經最優，但行的順序是隨機的。列col2的值是從1 ~ 100 之間隨機賦值，所以有很多重復的值

MyISAM的數據分布

MyISAM 的數據分布非常簡單，所以先介紹它。MyISAM按照數據插入的順序存儲在磁盤上，如下圖所示

在行的旁邊顯示了行號（可理解為地址），從0開始遞增。因為行是定長的，所以MyISAM可以從表的開頭跳過所需的字節找到需要的行(MyISAM 并不總是使用圖中的“行號”，而是根據定長還是變長的行使用不同策略)

這種分布方式很容易創建索引。下面顯示的一系列圖，隱藏了頁的物理細節，只顯示索引中的“節點”，索引中的每個葉子節點包含“行號”。下圖顯示了表的主鍵

這里忽略了一些細節，例如前一個B-Tree節點有多少個內部節點，不過這并不影響對非聚簇存儲引擎的基本數據分布的理解

那col2列上的索引又會如何呢?有什么特殊的嗎?回答是沒什么區別，下圖顯示了col2列上的索引

事實上，MyISAM中主鍵索引和其他索引在結構上沒有什么不同。主鍵索引就是一個名為PRIMARY的唯一非空索引

InnoDB的數據分布

因為InnoDB支持聚簇索引，所以使用非常不同的方式存儲同樣的數據。InnoDB如下圖所示的方式存儲數據

第一眼看上去，感覺該圖和前面的MyISAM主鍵分布圖沒有什么不同，但再仔細看細節，會注意到該圖顯示了整個表，而不是只有索引。因為在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那樣需要獨立的行存儲

聚簇索引的每一個葉子節點都包含了主鍵值、事務ID、用于事務和MVCC的回滾指針以及所有的剩余列(在這個例子中是col2)。如果主鍵是一個列前綴索引，InnoDB 也會包含完整的主鍵列和剩下的其他列

還有一點和MyISAM的不同是，InnoDB的二級索引和聚簇索引很不相同。InnoDB 二級索引的葉子節點中存儲的不是“行指針”，而是主鍵值,并以此作為指向行的“指針” 這樣的策略減少了當出現行移動或者數據頁分裂時二級索引的維護工作。使用主鍵值當作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB 在移動行時無須更新二級索引中的這個“指針”

下圖顯示了示例表的col2索引。每一個葉子節點都包含了索引列(這里是col2)，緊接著是主鍵值(col1)。并展示了B-Tree的葉子節點結構，但我們故意省略了非葉子節點這樣的細節。InnoDB的非葉子節點包含了索引列和一個指向下級節點的指針(下一級節點可以是非葉子節點，也可以是葉子節點)。這對聚簇索引和二級索引都適用

下圖是描述InnoDB和MyISAM如何存放表的抽象圖。從圖中可以很容易看出InnoDB和MyISAM保存數據和索引的區別

在InnoDB表中按主鍵順序插入行
如果正在使用InnoDB表并且沒有什么數據需要聚集，那么可以定義一個代理鍵(surrogate key)作為主鍵，這種主鍵的數據應該和應用無關，最簡單的方法是使用AUTO_ INCREMENT 自增列。這樣可以保證數據行是按順序寫人，對于根據主鍵做關聯操作的性能也會更好
最好避免隨機的(不連續且值的分布范圍非常大)聚簇索引，特別是對于1/O密集型的應用。例如，從性能的角度考慮，使用UUID來作為聚簇索引則會很糟糕:它使得聚簇索引的插人變得完全隨機，這是最壞的情況，使得數據沒有任何聚集特性，下面總結了一些缺點：

• 寫入的目標頁可能已經刷到磁盤上并從緩存中移除，或者是還沒有被加載到緩存中，InnoDB在插人之前不得不先找到并從磁盤讀取目標頁到內存中。這將導致大量的隨機I/O
• 因為寫人是亂序的，InnoDB 不得不頻繁地做頁分裂操作，以便為新的行分配空間。頁分裂會導致移動大量數據，一次插入最少需要修改三個頁而不是一個頁
• 由于頻繁的頁分裂，頁會變得稀疏并被不規則地填充，所以最終數據會有碎片

在把這些隨機值載入到聚簇索引以后，也許需要做一次OPTIMIZE TABLE 來重建表并優化頁的填充。不難看出，使用InnoDB時應該盡可能地按主鍵順序插入數據，并且盡可能地使用單調增加的聚簇鍵的值來插入新行

覆蓋索引

如果一個索引包含了所有需要查詢的字段的值，就稱之為覆蓋索引。覆蓋索引就是從索引中直接獲取查詢結果，要使用覆蓋索引需要注意select查詢列中包含在索引列中，此時不用第二次查詢表，所以速度很快；where條件包含索引列或者復合索引的前導列；查詢結果的字段長度盡可能少

使用延遲關聯解決索引無法覆蓋問題：下面的解決方法對效率的提升不是絕對的

SELECT * FROM products WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%'

上面的SQL中要查詢全部的列，而我們沒有覆蓋全部列的索引，因此沒有覆蓋索引。另外，like操作無法使用索引，因為like操作只有在匹配左前綴時才能使用索引

我們可以像下面這樣解決問題：

SELECT * FROM products JOIN (SELECT prod_id FROM products WHERE actor = 'SEAB CARREY' AND title like '%APPOLO%') AS t1 ON (t1.prod_id = products.prod_id)

這里，需要先建立(actor, title, prod_id)索引。我們先在子查詢中找到匹配的prod_id，然后跟外層中數據進行匹配來獲取所有列值。當符合where條件的數據數量遠小于actor過濾出的數據數量的時候，它的效率尤其高。因為，根據子查詢的where過濾出數據之后才與外層查詢關聯，而后者使用actor讀取出數據之后，再用title進行關聯。前者需要讀取的數據量更少

按索引掃描來排序

生成有序結果的兩種方式：排序，按索引順序掃描。當explain出的type為index時，說明使用索引掃描來進行排序。MySQL可以使用一個索引既滿足排序，又滿足查找。只有當索引的列順序和ORDER BY子句順序一致，且列的排序方向都一樣時，才能用索引對結果做排序

冗余和重復索引

MySQL允許在相同列上創建多個索引，無論是有意的還是無意的。但重復索引是指在相同的列上按照相同的順序創建的相同類型的索引。應該避免這樣創建重復索引，發現后也應刪除。常見的錯誤有：

• 使用主鍵和唯一約束時與已有的所沖突，因為主鍵和唯一約束是通過索引來實現的，如果再定義索引就會冗余，例如：無意間給主鍵也加個索引
• 若創建了索引(A,B)再創建索引(A)則冗余，而索引(B,A)和(B)不是，因為(B)不是最左前綴，故應該盡量擴展已有的索引而不是創建新索引

索引和鎖

InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少訪問行的次數，所以索引能減少鎖的數量

維護索引和表

即使用正確的類型創建了表并加上了合適的索引，工作也沒有結束：還需要維護表和索引來確保它們都正常工作。維護表有三個主要的目的：找到并修復損壞的表，維護準確的索引統計信息，減少碎片

• CHECK TABLE檢查表是否損壞，ALTER TABLE innodb_tb1 ENGINE=INNODB；修復表
• records_in_range()通過向存儲引擎傳入兩個邊界值獲取在這個范圍大概有多少條記錄，對于innodb不精確
• info()返回各種類型的數據，包括索引的基數
• 可以使用SHOW INDEX FROM命令來查看索引的基數
• B-Tree索引可能會碎片化，這會降低查詢的效率

六、查詢性能優化

為什么查詢速度會慢

• 如果要優化查詢，實際上要優化其子任務，要么消除其中一些子任務，要么減少子任務的執行次數，要么讓子任務運行得更快
• 查詢的生命周期大致可以按照順序來看：從客戶端，到服務器，然后在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端

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

兩個分析步驟

• 確認應用程序是否在檢索大量超過需要的數據
• 確認MySQL服務器層是否在分析大量超過需要的數據行

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

• 查詢不需要的記錄
• 多表關聯并返回全部列
• 總是取出全部列
• 重復查詢相同的數據

MySQL是否在掃描額外的記錄

• 查詢開銷三個指標：響應時間、掃描的行數、返回的行數
• 響應時間：服務時間和排隊時間之和，“快速上限估計”法
• 掃描的行數：較短的行的訪問速度更快，內存中的行也比磁盤中的行的訪問 速度要快得多
• 訪問類型：EXPLAIN中的type列反應了訪問類型；通過增加合適的索引
• 三種方式應用WHERE條件：在索引中使用WHERE條件來過濾不匹配的記錄；使用索引覆蓋掃描（Extra中出現Using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中結果；從數據表中返回數據，然后過濾不滿足條件的記錄（Extra中出現Using Where）
• 需要掃描大量數據但只返回少數的行的優化技巧：使用索引覆蓋掃描，改變庫表結構，重寫復雜的查詢

重構查詢的方式

• MySQL從設計上讓連接和斷開連接都很輕量級，在返回一個小的查詢結果方面很高效
• 切分查詢，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果，可以避免鎖住很多數據、占滿事務日志、耗盡系統資源、阻塞很多小的但重要的查詢
• 分解關聯查詢優勢：
  • 讓緩存的效率更高
  • 將查詢分解后，執行單個查詢可以減少鎖的競爭
  • 在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展
  • 查詢本身效率也可能會有所提升
  • 可以減少冗余記錄的查詢
  • 相當于在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯
• 分解關聯查詢的場景
  • 當應用能夠方便地緩存單個查詢的結果的時候當
  • 可以將數據分布到不同的MySQL服務器上的時候
  • 當能夠使用IN()的方式代替關聯查詢的時候
  • 當查詢中使用同一個數據表的時候

查詢執行的基礎

• 查詢執行路徑
  • 客戶端發送一條查詢給服務器
  • 服務器先檢查查詢緩存，如果命中則立刻返回，否則進入下一階段
  • 服務器端進行SQL解析、預處理，再由優化器生成對應的執行計劃
  • MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢
  • 將結果返回給客戶端
• MySQL客戶端和服務器之間的通信協議是“半雙工”的，無法將一個消息切成小塊獨立來發送，沒法進行流量控制，一旦一端開始發生消息，另一端要接收完整個消息才能響應它
• MySQL通常需要等所有的數據都已經發送給客戶端才能釋放這條查詢所占用的資源，所以接收全部結果并緩存通常可以減少服務器的壓力
• 查詢狀態，SHOW FULL PROCESSLIST命令查看：
  • Sleep，線程正在等待客戶端發送新的請求
  • Query，線程正在執行查詢或者正在將結果發送給客戶端
  • Locked，在MySQL服務器層，該線程正在等待表鎖
  • Analyzing and statistics，線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃
  • Copying to tmp table [on disk]，線程正在執行查詢，并且將其結果集都復制到一個臨時表中，要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作
  • Sorting result，線程正在對結果集進行排序
  • Sending data，線程可能在多個狀態之間傳送數據，或者在生成結果集，或者在向客戶端返回數據
• 語法解析器和預處理，通過關鍵字將SQL語句進行解析，并生成一棵對應的“解析樹”，解析器將使用MySQL語法規則驗證和解析查詢，預處理器則根據一些MySQL規則進一步檢查解析樹是否合法
• 查詢優化器，找到最好的執行計劃，使用基本成本的優化器，將嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個，使用SHOW STATUS LIKE ‘Last_query_cost’;查看需要多少個數據頁的隨機查找
• 導致MySQL查詢優化器選擇錯誤的原因：
  • 統計信息不準確，Innodb不能維護一個數據表的行數的精確統計信息執行計劃中的成本估算不等同于實際執行的成本
  • MySQL的最優可能和你想的最優不一樣
  • MySQL從不考慮其他并發執行的查詢
  • MySQL也并不是任何時候都是基于成本的優化
  • MySQL不會考慮不受其控制的操作的成本
  • 優化器有時候無法去估算所有可能的執行計劃
• MySQL能處理的優化類型
  • 重新定義關聯表的順序
  • 將外鏈接轉化成內鏈接
  • 使用等價變換規則
  • 優化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”
  • 預估并轉化為常數表達式，當檢測到一個表達式可以轉化為常數的時候，就會一直把該表達式作為常數進行優化處理
  • 覆蓋索引掃描，當索引中的列包含所有查詢中需要使用的列的時候，就可以使用索引返回需要的數據，而無須查詢對應的數據行
  • 子查詢優化
  • 提前終止查詢，在發現已經滿足查詢需求的時候，MySQL總是能夠立刻終止查詢
  • 等值傳播，如果兩個列的值通過等式關聯，那么MySQL能夠把其中一個列的WHERE條件傳遞到另一列上
  • 列表IN()的比較，MySQL將IN()列表中的數據先進行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件
• 在服務器層有查詢優化器，卻沒有保存數據和索引的統計信息，統計信息由存儲引擎實現，不同的存儲引擎可能會存儲不同的統計信息
• 在MySQL中，每一個查詢，每一個片段（包括子查詢，甚至基于單表的SELECT）都可能是關聯
• 對于UNION查詢，MySQL先將一系列的單個查詢結果放到一個臨時表中，然后再重新讀出臨時表數據來完成UNION查詢
• MySQL對任何關聯都執行“嵌套循環關聯”操作，即MySQL先在一個表中循環取出單條數據，然后再嵌套到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止
• 全外連接就無法通過嵌套循環和回溯的方式完成，當發現關聯表中沒有找到任何匹配行的時候，則可能是因為關聯恰好從一個沒有任何匹配的表開始，MySQL不支持全外連接
• 關聯查詢優化器，會嘗試在所有的關聯順序中選擇一個成本最小的來生成執行計劃樹，如果可能，優化器會遍歷每一個表然后逐個做嵌套循環計算每一棵可能的執行樹的成本，最后返回一個最優的執行計劃
• 如果有超過n個表的關聯，那么需要檢查n的階乘關聯順序，稱為“搜索空間”，搜索空間的增長速度非常快
• 無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應盡可能避免排序或者盡可能避免對大量數據進行排序
• 當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果數據量小則在內存中進行，如果數據量大則需要使用磁盤，MySQL將這個過程稱為文件排序（filesort），即使完全是內存排序不需要任何磁盤文件時也是如此

MySQL查詢優化器的局限性

• 關聯子查詢：MySQL的子查詢實現得非常糟糕，最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句，使用GROUP_CONCAT()在IN()中構造一個由逗號分隔的列表，或者使用EXISTS()來改寫
• UNION的限制：有時，MySQL無法將限制條件從外層“下推”到內層，這使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上
• MySQL無法利用多核特性來并行執行查詢
• MySQL不支持哈希關聯，MariaDB已經實現了哈希關聯
• MySQL不支持松散索引掃描，5.0后版本在分組查詢中需要找到分組的最大值和最小值時可以使用松散索引掃描
• 對于MIN()和MAX()查詢，MySQL的優化做得并不好

查詢優化器的提示

• HIGH_PRIORITY和LOW_PRIORITY，當多個語句同時訪問某一個表的時候，哪些語句的優先級相對高些、哪些語句的優先級相對低些
• DELAYED，對INSERT和REPLACE有效，會將使用該提示的語句立即返回給客戶端，并將插入的行數據放入到緩沖區，然后在表空閑時批量將數據寫入，并不是所有的存儲引擎都支持，并且該提示會導致函數LAST_INSERT_ID()無法正常工作
• STRAIGHT_JOIN，可以放置在SELECT語句的SELECT關鍵字之后，也可以放置在任何兩個關聯表的名字之間。第一個用法是讓查詢中所有的表按照在語句中出現的順序進行關聯，第二個用法則是固定其前后兩個表的關聯順序
• SQL_SMALL_RESULT和SQL_BIG_RESULT，只對SELECT語句有效，它們告訴優化器對GROUP BY或者DISTINCT查詢如何使用臨時表及排序
• SQL_BUFFER_RESULT，告訴優化器將查詢結果放入到一個臨時表，然后盡可能快地釋放表鎖
• SQL_CACHE和SQL_NO_CACHE，告訴MySQL這個結果集是否應該緩存在查詢緩存中
• SQL_CALC_FOUND_ROWS，會計算除去LIMIT子句后這個查詢要返回的結果集的總數，而實際上只返回LIMIT要求的結果集，可以通過函數FOUND_ROW()獲得這個值
• FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT語句的鎖機制，但只對實現了行級鎖的存儲引擎有效，僅InnoDB支持
• USE INDEX、IGNORE INDEX和FORCE INDEX，告訴優化器使用或者不使用哪些索引來查詢記錄
• MySQL5.0后新增的用來控制優化器行為的參數：
  • optimizer_search_depth，控制優化器在窮舉執行時的限度
  • optimizer_prune_level，讓優化器會根據需要掃描的行數來決定是否跳過某些執行計劃
  • optimizer_switch，包含了一些開啟/關閉優化器特性的標志位

優化特定類型的查詢

• 優化COUNT()查詢
  • COUNT()是一個特殊的函數，有兩種非常不同的作用：可以統計某個列值的數量，也可以統計行數，在統計列值時要求列值是非空的（不統計NULL）
  • COUNT(*)并不是會像我們猜想的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數，當MySQL確認括號內的表達值不可能為空時，實際上就是在統計行數
  • MyISAM的COUNT()函數只有沒有任何WHERE條件下的COUNT(*)才非常快
  • 使用近似值，如EXPLAIN出來的優化器估算行數
  • 使用索引覆蓋
  • 使用匯總表
  • 使用外部緩存系統
• 優化關聯查詢
  • 確保ON或者USING子句中的列上有索引
  • 確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列
  • 當升級MySQL的時候需要注意：關聯語法、運算符優先級等其他可能會發生變化的地方
• 優化子查詢
  • 盡可能使用關聯查詢代替，如果使用MySQL5.6以上或MariaDB則可以忽略這個建議
• 優化GROUP BY和DISTINCT
  • 使用索引優化
  • 當無法使用索引時，GROUP BY使用兩種策略來完成：使用臨時表或者文件排序來做分組
  • 盡可能的將WITH ROLLUP（超級聚合）功能移動應用程序中處理
• 優化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
  • 使用預先計算的匯總表，或者關聯到一個冗余表
• 優化UNION查詢
  • 通過創建并填充臨時表的方式來執行UNION查詢，因此很多優化策略在UNION查詢中都沒法很好地使用，經常需要手工地將WHERE、LIMIT、ORDER BY等子句下推到UNION的各個子查詢中
  • 除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL

七、MySQL高級特性

分區表

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

• 適用場景：

  • 表非常大以至于無法全部都放在內存中，或者只在表的最后部分有熱點數據，其他均是歷史數據
  • 分區表的數據更容易維護
  • 分區表的數據可以分布在不同的物理設備上，從而高效地利用多個硬件設備
  • 可以使用分區表來避免某些特殊的瓶頸
  • 如果需要，還可以備份和恢復獨立的分區

• 使用限制：

  • 一個表最多只能有1024個分區
  • 在MySQL5.1中，分區表達式必須是整數，或者是返回整數的表達式。在MySQL5.5中，某些場景中可以直接使用列來進行分區
  • 如果分區字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來
  • 分區表中無法使用外鍵約束

• 使用分區表

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

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

  • 命題掃描數據，不要任何索引
  • 索引數據，并分離熱點

• 分區策略的問題：

  • NULL值會使分區過濾無效
  • 分區列和索引列不匹配
  • 選擇分區的成本可能很高
  • 打開并鎖住所有底層表的成本可能很高
  • 維護分區的成本可能很高
  • 所有分區都必須使用相同的存儲引擎
  • 分區函數中可以使用的函數和表達式也有一些限制
  • 某些存儲引擎不支持分區
  • 對于MyISAM的分區表，不能再使用LOAD INDEX INTO CACHE操作
  • 對于MyISAM表，使用分區表時需要打開更多多的文件描述符

• 查詢優化

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

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

視圖

• 視圖本身是一個虛擬表，不存放任何數據，返回的數據是MySQL從其他表中生成的
• MySQL使用兩種算法：合并算法（MERGE）和臨時表算法（TEMPTABLE），會盡可能地使用合并算法
• 如果視圖中包含GROUP BY、DISTINCT、任何聚合函數、UNION、子查詢等，只要無法在原表記錄和視圖記錄中建立一一映射的場景中，MySQL都將使用臨時表算法來實現視圖
• 可更新視圖（updatable view）是指可以通過更新這個視圖來更新視圖涉及的相關表，CHECK OPTION表示任何通過視圖更新的行，都必須符合視圖本身的WHERE條件定義
• 在重構schema的時候可以使用視圖，使得在修改視圖底層表結構的時候，應用代碼還可能繼續不報錯運行
• MySQL中不支持物化視圖（指將視圖結果數據存放在一個可以查看的表中，并定期從原始表中刷新數據到這個表中）
• 不會保存視圖定義的原始SQL語句

外鍵約束

• 使用外鍵是有成本的，通常要求每次在修改數據時都要在另外一張表中多執行一次查找操作
• 如果想確保兩個相關表始終有一致的數據，那么使用外鍵比在應用程序中檢查一致性的性能要高得多，在相關數據的刪除和更新上，也比在應用中維護要更高效
• 外鍵會帶來很大的額外消耗

在MySQL內部存儲代碼

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

• 存儲代碼的優點：

  • 它在服務器內部執行，離數據最近，另外在服務器上執行還可以節省帶寬和網絡延遲
  • 這是一種代碼重用，可以方便地統一業務規則，保證某些行為總是一致，所以也可以為應用提供一定的安全性
  • 它可以簡化代碼的維護和版本更新
  • 可以幫助提升安全，比如提供更細粒度的權限控制
  • 服務器端可以緩存存儲過程的執行計劃，這對于需要反復調用的過程，會大大降低消耗
  • 因為是在服務器端部署的，所以備份、維護都可以在服務器端完成
  • 可以在應用開發和數據庫開發人員之間更好地分工

• 存儲代碼的缺點：

  • MySQL本身沒有提供好用的開發和調試工具
  • 較之應用程序的代碼，存儲代碼效率要稍微差些
  • 存儲代碼可能會給應用程序代碼的部署帶來額外的復雜性
  • 因為存儲程序都部署在服務器內，所以可能有安全隱患
  • 存儲過程會給數據庫服務器增加額外的壓力，而數據庫服務器的擴展性相比應用服務器要差很多
  • MySQL并沒有什么選項可以控制存儲程序的資源消耗，所以在存儲過程中的一個小錯誤，可能直接把服務器拖死
  • 存儲代碼在MySQL中的實現也有很多限制——執行計劃緩存是連接級別的，游標的物化和臨時表相同，異常處理也非常困難
  • 調試MySQL的存儲過程是一件很困難的事情
  • 它和基于語句的二進投影日志復制合作得并不好

  基于以上原因阿里手冊里都強制禁止使用存儲過程方式操作SQL

• 存儲過程和函數的限制：

  • 優化器無法使用關鍵字DETERMINISTIC來優化單個查詢中多次調用存儲函數的情況
  • 優化器無法評估存儲函數的執行成本
  • 每個連接都有獨立的存儲過程的執行計劃緩存
  • 存儲程序和復制是一組詭異組合

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

• 觸發器的注意和限制：

  • 對每一個表的每一個事件，最多只能定義一個觸發器
  • 只支持“基于行的觸發”，也就是說，觸發器是針對一條記錄的，而不是針對整個SQL語句的，如果變更的數據集非常大的話，效率會很低
  • 觸發器可以掩蓋服務器背后的工作
  • 觸發器可以掩蓋服務器背后的工作，一個簡單的SQL語句背后可能包含了很多看不見的工作
  • 觸發器的問題也很難排查，如果某個性能問題和觸發器相關，會很難分析和定位
  • 觸發器可能導致死鎖和鎖等待
  • 觸發器并不能一定保證更新的原子性

• 觸發器的用處：

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

  • 記錄數據變更日志

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

游標

• MySQL在服務器端提供提供只讀的、單向的游標，而且只能在存儲過程或者更底層的客戶端API中使用，指向的對象都是存儲在臨時表中而不是實際查詢到的數據，所以總是只讀的
• 會帶來額外的性能開銷
• 不支持客戶端的游標

綁定變量

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

• 可以更高效地執行大量的重復語句：

  • 在服務器端只需要解析一次SQL語句
  • 在服務器端某些優化項的工作只需要執行一次，因為它會緩存一部分的執行計劃
  • 以二進制的方式只發送參數和句柄，比起每次都發送ASC2碼文本效率更高
  • 僅僅是參數——而不是整個查詢語句——需要發送到服務器端，所以網絡開銷會更小
  • MySQL在存儲參數的時候，直接將其存放到緩存中，不再需要在內存中多次復制

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

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

• 綁定變量的限制：

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

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

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

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

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

• 用戶自定義函數

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

插件

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

• 在5.1后支持的插件接口：

  • 存儲過程插件

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

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

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

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

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

字符集和校對

• 字符集是一種從二進制編碼到某類字符符號的映射，可以參考如何使用一個字節來表示英文字母。“校對”是指一組用于某個字符集的排序規則
• 每種字符集都可能有多種校對規則，并且都有一個默認的校對規則，每個校對規則都是針對某個特定的字符集的，和其他的字符集沒有關系
• MySQL有很多的選項用于控制字符集，這些選項和字符集很容易混淆，只有基于字符的值才真正的“有”字符集的概念
• MySQL的兩類設置：創建對象時的默認設置、服務器和客戶端通信時的設置
• 如果比較的兩個字符串的字符集不同，MySQL會先將其轉成同一個字符集再進行比較
• 一些需要注意的地方：
  • 詭異的character_set_database設置，當改變默認數據庫的時候，這個變量也會跟著變，所以當連接到MySQL實例上又沒有指定要使用的數據庫時，默認值會和character_set_server相同
  • LOAD DATA INFILE，當使用時，數據庫總是將文件中的字符按照字符集character_set_database來解析
  • SELECT INTO OUTFILE，MySQL會將結果不做任何轉碼地寫入文件
  • 嵌入式轉義序列，MySQL會根據character_set_client的設置來解析轉義序列
• 某些字符集和校對規則可能會需要更多的CPU操作，可能會消耗更多的內存和存儲空間，甚至還會影響索引的正常使用
  • 不同的字符集和校對規則之間的轉換可能會帶來額外的系統開銷
  • 只有排序查詢要求的字符集與服務器數據的字符集相同的時候，才能使用索引進行排序
  • 為了能夠適應各種字符集，包括客戶端字符集、在查詢中顯式指定的字符集，MySQL會在需要的時候進行字符集轉換

全文索引

• MyISAM的全文索引作用對象是一個“全文集合”，這可能是某個數據表的一列，也可能是多個列
• 可以根據WHERE子句中的MATCH AGAINST來區分查詢是否使用全文索引
• 在使用全文索引進行排序的時候，MySQL無法再使用索引排序，如果不想使用文件排序的話，就不要在查詢中使用ORDER BY子句
• 在布爾搜索中，用戶可以在查詢中自定義某個被搜索的詞語的相關性，可能通過一些前綴修飾符來定制搜索
• 全文索引在INSERT、UPDATE、DELETE中的操作代價很大
• 全文索引會影響索引選擇、WHERE子句、ORDER BY等：
  • 如果查詢中使用了MATCH AGAINST子句，而對應列上又有可用的全文索引，那么MySQL就一定會使用這個全文索引
  • 全文索引只能用作全文搜索匹配
  • 全文索引不存儲索引列的實際值，也就不可能用作索引覆蓋掃描
  • 除了相關性排序，全文索引不能用作其他的排序
• 全文索引的配置和優化：
  • 經常使用OPTIMIZE TABLE來減少碎片，如果是I/O密集型的定期進行全文索引重建
  • 保證索引緩存足夠大
  • 提供一個好的停用詞表
  • 忽略一些太短的單詞
  • 導入大量數據時，最好通過命令DISABLE KEYS來禁用全文索引，然后導入結束后使用ENABLE KEYS來建立全文索引
  • 如果數據集特別大，則需要對數據進行手動分區，然后將數據分布到不同的節點，再做并行的搜索

分布式（XA）事務

• XA事務中需要有一個事務協調器來保證所有的事務參與者都完成了準備工作。如果協調器收到所有的參與者都準備好的消息，就會告訴所有的事務可以提交了，MySQL在這個XA事務過程中扮演一個參與者的角色，而不是協調者
• 因為通信延遲和參與者本身可能失敗，所以外部XA事務比內部消耗會更大

查詢緩存

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

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

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

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

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

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

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

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

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

• 緩存未命中：

  • 查詢語句無法被緩存
  • MySQL從未處理這個查詢
  • 查詢緩存的內存用完了
  • 查詢緩存還沒有完成預熱
  • 查詢語句之前從未執行過
  • 緩存失效操作太多了

• 緩存參數配置：

  • query_cache_type，是否打開查詢緩存
  • query_cache_size，查詢緩存使用的總內存空間
  • query_cache_min_res_unit，在查詢緩存中分配內存塊時的最小單位，可以幫助減少由碎片導致的內存空間浪費
  • query_cache_limit，MySQL能夠緩存的最大查詢結果
  • query_cache_wlock_invalidate，如果某個數據表被其他的連接鎖住，是否仍然從查詢緩存中返回結果

• InnoDB和查詢緩存

  • 事務是否可以訪問查詢緩存取決于當前事務ID，以及對應的數據表上是否有鎖
  • 如果表上有任何的鎖，那么對這個表的任何查詢語句都是無法被緩存的

• 通用查詢緩存優化：

  • 用多個小表代替一個大表對查詢緩存有好處
  • 批量寫入時只需要做一次緩存失效，所以相比單條寫入效率更好
  • 因為緩存空間太大，在過期操作的時候可能會導致服務器僵死，控制緩存空間的大小
  • 無法在數據庫或者表級別控制查詢緩存，但是可以通過SQL_CACHE和SQL_NO_CACHE來控制某個SELECT語句是否需要進行緩存
  • 對于寫密集型的應用來說，直接禁用查詢緩存可能會提高系統的性能
  • 因為對互斥信號量的競爭，有時直接關閉查詢緩存對讀密集型的應用也會有好處

八、優化服務器設置

MySQL配置的工作原理

• 任何打算長期使用的設置都應該寫到全局配置文件，而不是在命令行特別指定，命令行指定重啟服務后，配置將消失
• 常用變量和動態修改它們的效果：
  • 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，只會在有查詢需要做排序時才會為該緩存分配內存
• 對于連接級別的設置，不要輕易地在全局級別增加它們的值，除非確認這樣做是對的
• 設置變量時請小心，并不是值越大就越好，而且如果設置的值太高，可能更容易導致問題：可能會由于內存不足導致服務器內存交換，或者超過地址空間
• 不要期望通過建立一套基準測試方案，然后不斷迭代地驗證對配置項的修改來找到最佳配置方案，而要把時間花在檢查備份、監控執行計劃的變動之類的事情上，可能會更有意義

什么不該做

• 不要根據一些“比率”來調優：例如緩存命中率跟緩存是否過大或過小沒有關系
• 不要使用調優腳本
• 不要相信很流行的內存消耗公式

創建MySQL配置文件

• MySQL編譯的默認設置并不都是靠譜的，雖然其中大部分都比較合適
• 從一個比默認值大一點但不是大得很離譜的安全值開始是比較好的，MySQL的內存利用率并不總是可以預測的：它可能依賴很多的因素，例如查詢的復雜性和并發性
• 配置服務器的首選途徑：了解它內部做了什么，以及參數之間如何相互影響，然后再決定怎么配置
• open_files_limit，在Linux系統上設置得盡可能大，如果參數不夠大，將會踫到24號錯誤“打開的文件太多（too many open files）”
• 每隔60秒查看狀態變量的增量變化：mysqladmin extended-status ri60

配置內存使用

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

• 配置內存：

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

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

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

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

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

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

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

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

  • InnoDB緩沖池
  • InnoDB日志文件和MyISAM數據的操作系統緩存
  • MyISAM鍵緩存
  • 查詢緩存
  • 無法手工配置的緩存，例如二進制日志和表定義文件的操作系統緩存

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

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

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

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

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

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

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

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

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

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

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

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

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

配置MySQL的I/O行為

• 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命令去刪
• MyISAM的I/O配置
  • MyISAM通常每次寫操作之后就把索引變更刷新磁盤，批量操作會更快一些
  • 通過設置delay_key_write變量，可以延遲索引的寫入，修改的鍵緩沖塊直到表被關閉才會刷新
  • myisam_recover選項控制MyISAM怎樣尋找和修復錯誤
  • 內存映射使得MyISAM直接通過操作系統的頁面緩存訪問.MYD文件，避免系統調用的開銷，5.1后可以通過myisam_use_mmap選項打開內存映射

配置MySQL并發

• InnoDB并發配置

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

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

• MyISAM并發配置

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

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

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

基于工作負載的配置

• 當服務器滿載情況下運行時，請嘗試記錄所有的查詢語句，因為這是最好的方式來查看哪種類型的查詢語句占用資源最多，同時創建processlist快照，通過state或者command字段來聚合它們
• 優化BLOB和TEXT場景
  • BLOB有幾個限制使得服務器對它的處理跟其他類型不一樣，不能在內存臨時表中存儲BLOB值，效率很低
  • 通過SUBSTRING()函數把值轉換為VARCHAR
  • 讓臨時表更快一些：放在基于內存的文件系統
  • 如果使用的是InnoDB，也可以調大InnoDB日志緩沖大小
  • 大字段在InnoDB里可能浪費大量空間
  • 擴展存儲禁用了自適應哈希，因為需要完整地比較列的整個長度，才能發現是不是正確的數據
  • 太長的值可能使得查詢中作為WHERE條件不能使用索引
  • 如果一張表里有很多大字段，最好是把它們組合起來單獨存到一個列里面
  • 有時候可以把大字段用COMPRESS()壓縮后再存為BLOB，或者發送到MySQL前在應用程序中進行壓縮
• 優化排序（Filesorts）：當MySQL必須排序BLOG或TEXT字段時，它只會使用前綴，然后忽略剩下部分的值

完成基本配置

• tmp_table_size和max_heap_table_size，這兩個設置控制使得Memory引擎的內存臨時表能使用多大的內存
• max_connections，這個設置的作用就像一個緊急剎車，以保證服務器不會因應用程序激增的連接而不堪重負，設置得以容納正常可能達到的負載，并且要足夠安全，能保證允許你登錄和管理服務器
• thread_cache_size，可以通過觀察服務器一段時間的活動，來計算一個有理有據的值，250的上限是一個不錯的估算值
• table_cache_size，應該被設置得足夠大，以避免總是需要重新打開和重新解析表的定義，可能通過觀察Open_tables的值及其在一段時間的變化來檢查該變量

安全和穩定的設置

• expire_logs_days，如果啟用了二進制日志，應該打開這個選項，可以讓服務器在指定的天數之后清理舊的二進制日志
• max_allowed_packet，防止服務器發送太大的包，也會控制多大的包可以被接收
• max_connect_errors，如果知道服務器可以充分抵御蠻力攻擊，可以把這個值設得非常大，以有效地禁用主機黑名單
• skip_name_resolve，禁用了另一個網絡相關和鑒權誰相關的陷阱：DNS查找
• sql_mode，不建議修改
• sysdate_is_now，可能導致與應用預期向后不兼容的選項
• read_only，禁止沒有特權的用戶在備庫做變更，只接受從主庫傳輸過來的變更，不接受從應用來的變更，可以把備庫設置為只讀模式
• skip_slave_start，阻止MySQL試圖自動啟動復制
• slave_net_timeout，控制備庫發現跟主庫的連接已經失敗并且需要重連之前等待的時間，設置為一分鐘或更短
• sync_master_info、sync_relay_log、sync_relay_log_info，5.5以后版本可用，解決了復制中備庫長期存在的問題：不把它們的狀態文件同步到磁盤，所以服務器崩潰后可能需要人來猜測復制的位置實際上在主庫是哪個位置，并且可能在中繼日志（Relay Log）里有損壞

高級InnoDB設置

• innodb，如果設置為FORCE，只有在InnoDB可以啟動時，服務器才會啟動
• innodb_autoinc_lock_mode，控制InnoDB如何生成自增主鍵值
• innodb_buffer_pool_instances，在5.5以后，可以把緩沖池切分為多段，在高負載的多核機器上提升MySQL可擴展性的一個重要方式
• innodb_io_capacity，有時需要把這個設置得相當高，才能穩定地刷新臟頁
• innodb_read_io_threads和innodb_write_io_threads，控制有多少后臺線程可以被I/O操作使用
• innodb_strict_mode，讓MySQL在某些條件下把警告改成拋錯，尤其是無效的或者可能有風險的CREATE TABLE選項
• innodb_old_blocks_time，指定一個頁面從LRU鏈表的“年輕”部分轉移到“年老”部分之前必須經過的毫秒數，默認為0，設置為1000毫秒（1秒）非常有效

九、操作系統和硬件優化

什么限制了MySQL的性能

• 當數據可以放在內存中或者可以從磁盤中以足夠快的速度讀取時，CPU可能出現瓶頸，把大量的數據集完全放到大容量的內存中，以現在的硬件條件完全是可行的
• I/O瓶頸，一般發生在工作所需的數據遠遠超過有效內存容量的時候，如果應用程序是分布在網絡上的，或者如果有大量的查詢和低延遲的要求，瓶頸可能轉移到網絡上

如何為MySQL選擇CPU

• 可以通過檢查CPU利用率來判斷是否是CPU密集型的工作負載，還需要看看CPU使用率和大多數重要的查詢的I/O之間的平衡，并注意CPU負載是否分配均勻
• 當遇到CPU密集型的工作時，MySQL通常可以從更快的CPU中獲益，但還依賴于負載情況和CPU數量
• MySQL復制也能在高速CPU下工作得非常好，而多CPU對復制的幫助卻不大
• 多CPU在聯機事務處理（OLTP）系統的場景中非常有用，在這樣的環境中，并發可能成為瓶頸

平衡內存和磁盤資源

• 配置大量內存最終目的是避免磁盤I/O，最關鍵的是平衡磁盤的大小、速度、成本和其他因素，以便為工作負載提供高性能的表現
• 設計良好的數據庫緩存（如InnoDB緩沖池），其效率通常超過操作系統的緩存，因為操作系統緩存是為通用任務設計的
• 數據庫服務器同時使用順序和隨機I/O，隨機I/O從緩存從受益最多
• 每個應用程序都有一個數據的“工作集”——就是這個工作確實需要用到的數據
• 工作集包括數據和索引，所以應該采用緩存單位來計數，一個緩存單位是存儲引擎工作的數據最小單位
• 找到一個良好的內存/磁盤比例最好的方式是通過試驗和基準測試
• 硬盤選擇考慮因素：存儲容量、傳輸速度、訪問時間、主軸轉速、物理尺寸
• MySQL如何擴展到多個磁盤上取決于存儲引擎和工作負載，InnoDB能很好地擴展到多個硬盤驅動器，然而，MyISAM的表鎖限制其寫的可擴展性，因此寫繁重的工作加在MyISAM上，可能無法從多個驅動器中收益

固態存儲

• 高質量閃存設備具備：

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

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

  • 相比硬盤能更好地支持并發

  • 提升隨機I/O和并發性

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

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

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

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

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

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

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

• 優化固態存儲上的MySQL

  • 增加InnoDB的I/O容量
  • 讓InnoDB日志文件更大
  • 把一些文件從閃存轉移到RAID
  • 禁用預讀
  • 配置InnoDB刷新算法
  • 禁用雙寫緩沖的可能
  • 限制插入緩沖大小，插入緩沖設計來用于減少當更新行時不在內存中的非唯一索引引起的隨機I/O
  • InnoDB的頁大小
  • 優化InnoDB頁面校驗（Checksum）的替代算法

為備庫選擇硬件

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

RAID性能優化

• RAID可以幫助做冗余、擴展存儲容量、緩存，以及加速
• RAID 0：如果只是簡單的評估成本和性能，是成本最低和性能最高的RAID配置
• RAID 1：在很多情況下提供很好的讀性能，并且在不同的磁盤間冗余數據，所以有很好的冗余性，非常適合用來存放日志或者類似的工作
• RAID 5：通過分布奇偶校驗把數據分散到多個磁盤，如果任何一個盤的數據失效，都可以從奇偶校驗塊中重建，但如果有兩個磁盤失效了，則整個卷的數據無法恢復，最經濟的冗余配置。隨機寫是昂貴的，存放數據或者日志是一種可接受的選擇，或者是以讀為主的業務
• RAID 10：對數據存儲是個非常好的選擇，由分片的鏡像組成，對讀和寫都有良好的擴展性
• RAID 50：由條帶化的RAID 5組成

SAN和NAS

• SAN（Storage Area Network）和NAS（Network-Attached Storage）是兩個外部文件存儲設備加載到服務器的方法，訪問SAN設備時通過塊接口，NAS設備通過基于文件的協議來訪問
• SAN允許服務器訪問非常大量的硬盤驅動器，并且通常配置大容量智能高速緩存來緩沖寫入
• 哪些工作放在SAN上不合適：執行大量的隨機I/O的單線程任務
• SAN的應用：
  • 備份，可以只備份SAN
  • 簡化容量規劃
  • 存儲整合還是服務器整合
  • 高可用
  • 服務器之間的交互
  • 成本

使用多磁盤卷

• 二進制日志和數據文件分離的真正的優勢，是減少事故中同時丟失數據和日志文件的可能性
• 如果有很多磁盤，投入一些給事務日志可能會從中受益

網絡配置

• 在生產服務器上啟用skip_name_resolve是個好主意，損壞或緩慢的DNS解析對許多應用程序都是個問題，對MySQL尤嚴重，如果啟用skip_name_resolve選項，MySQL將不會做任何DNS查找的工作
• 可以通過MySQL的back_log選項控制MySQL的傳入TCP連接隊列的大小，在每秒有很多連接創建和銷毀的環境中，默認值50是不夠的
• 網絡物理隔離也是很重要的因素，盡可能避免實時的跨數據中心的操作是明智的

選擇操作系統

• 一般的企業級部署MySQL大多在類UNIX操作系統上

選擇文件系統

• 如果可能，最好使用日志文件系統，如ext3、ext4、XFS、ZFS或者JFS
• 可以調整文件系統的預讀行為，因為這可能也是多余的

選擇磁盤隊列調度策略

• 在GUN/Linux上，隊列調度決定了到塊設備的請求實際上發送到底層設備的順序，默認情況下使用cfq（Completely Fair Queueing，完全公平排隊）策略，在MySQL的工作負載類型下，cfq會導致很差的響應時間，因為會在隊列中延遲一些不必要的請求
• cfq之外的兩個選項都適合服務器級的硬件，noop調度適合沒有自己的調度算法的設備，deadline則對RAID控制器和直接使用的磁盤都工作良好

線程

• MySQL每個連接使用一個線程，另外還有內部處理線程、特殊用途的線程，以及所有存儲引擎創建的線程
• MySQL確實需要內核級線程的支持，而不只是用戶級線程，這樣才能更有效地使用多個CPU，另外也需要有效的同步原子

內存交換區

• 內存交換對MySQL性能影響是很糟糕的，它破壞了緩存在內存的目的，并且相對于使用很小的內存做緩存，使用交換區的性能更差
• 在GNU/Linux上，可以用vmstat來監控內存交換，最好查看si和so列報告的內存交換I/O活動，這比看swpd列報告的交換區利用率更重要，最佳為0
• 設置/proc/sys/vm/swappiness為一個很小的值
• 修改存儲引擎怎么讀取和寫入數據，使用innodb_flush_method=0_DIRECT減輕I/O壓力
• 使用MySQL的memlock配置項，可以把MySQL鎖定在內存

操作系統狀態

• vmstat

  • vmstat 5，每隔5秒刷新一次
  • procs，r列顯示多少進程正在等待CPU，b列顯示多少進程正在不可中斷地休眠
  • memory，swpd列顯示多少塊被換出到了磁盤，剩下的三個列顯示了多少塊是空閑的、多少塊正在被用作緩沖，以及多少正在被用作操作系統的緩存
  • swap，顯示頁面交換活動
  • io，顯示有多少塊從塊設備讀取（ bi）和寫出（bo）
  • system，顯示了每秒中斷（in）和上下文切換（cs）的數量
  • cpu，顯示所有的CPU時間花費在各類操作的百分比

• iostat

  • iostats -dx 5，每5秒刷新

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

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

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

  • avgrq-sz，請求的扇區數

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

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

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

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

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

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

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

十、復制

復制概述

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

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

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

• 復制解決的問題：

  • 數據分布

  • 負載均衡

  • 備份

  • 高可用性和故障切換

  • MySQL升級測試

• 復制如何工作

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

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

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

配置復制

• 在每臺服務器上創建復制帳號
  • 用來監控和管理復制的帳號需要REPLICATION CLIENT權限，并且針對這兩種目的使用同一個帳號更加容易
  • 如果在主庫上建立了帳號，然后從主庫將數據克隆到備庫時，備庫也就設置好了——變成主庫所需要的配置
• 配置主庫和備庫
  • 必須明確地指定一個唯一的服務器ID
  • 有時候只開啟了二進制日志，但卻沒有開啟log_slave_updates，可能會踫到一些奇怪的現象
  • 如果可能的話，最好使用read_only配置選項，會阻止任何沒有特權權限的線程修改數據
• 通知備庫連接到主庫并從主庫復制數據
• 推薦的復制配置
  • 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

復制的原理

• 基于語句的復制

  • 5.0之前只支持基于語句的復制（也稱為邏輯復制），主庫會記錄那些造成數據更改的查詢，當備庫讀取并重放這些事件時，實際上只是把主庫上執行過的SQL再執行一遍
  • 好處是實現相當簡單，日志更加緊湊，不會占用太多帶寬
  • 問題是基于語句的方式可能并不如其看起來那么便利，還存在一些無法被正確復制的SQL，更新必須是串行的這需要更多的鎖

• 基于行的復制

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

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

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

• 基于行或基于語句：哪種更優

  • 基于語句的復制模式的優點：當主備的模式不同時，邏輯復制能夠在多種情況下工作；基于語句的方式執行復制的過程基本上就是執行SQL語句
  • 基于語句的復制模式的缺點：很多情況下通過基于語句的模式無法正確復制，如果正在使用觸發器或者存儲過程，就不要使用基于語句的復制模式，除非能夠清楚地確定不會踫到復制的問題
  • 基于行的復制模式的優點：幾乎沒有基于行的復制模式無法處理的場景；可能減少鎖的使用，并不要求這種強串行化是可重復的；會記錄數據變更；占用更少的CPU；能夠幫助更快地找到并解決數據不致的情況
  • 基于行的復制模式的缺點：無法判斷執行了哪些SQL；無法知道服務器在做什么；在某些情況下，例如找不到要修改的行時，基于行的復制可能會導致復制停止

• 復制文件

  • mysql-bin.index，二進制日志文件

  • mysql-relay-bin-index，中繼日志的索引文件

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

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

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

• 6.復制過濾選項

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

復制拓撲

• 基本原則：

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

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

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

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

• 一主庫多備庫

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

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

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

• 環形復制：每個服務器都是在它之前的服務器的備庫，是在它之后的服務器的主庫

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

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

• 定制的復制方案

  • 選擇性復制：配置replicate_wild_do_table

  • 分離功能：OLTP、OLAP

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

  • 將備庫用作全文檢索

  • 只讀備庫：read_only選項

  • 模擬多主庫復制

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

復制和容量規劃

• 寫操作通常是復制的瓶頸，并且很難使用復制來擴展寫操作
• 在構建一個大型應用時，有意讓服務器不被充分使用，這應該是一種聰明并且蔓延的方式，尤其在使用復制的時候，有多余容量的服務器可以更好地處理負載尖峰，也有更多能力處理慢速查詢和維護工作，并且能夠更好地跟上復制

復制管理和維護

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

十一、擴展的MySQL

什么是可擴展性

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

擴展MySQL

• 規劃可擴展性最困難的部分是估算需要承擔的負載到底有多少，還需要大致正確地估計日程表，需要知道底線在哪里
• 可以做的準備工作：優化性能、購買性能更強的硬件
• 向上擴展（垂直擴展）意味著購買更多性能強悍的硬件
• 向外擴展（橫向擴展、水平擴展）：復制、拆分、數據分片
  • 按功能拆分（按職責拆分），不同的節點執行不同的任務
  • 數據分片，把數據分割成一小片，或者一小塊，然后存儲到不同的節點中
  • 選擇分區鍵（partitioning key）
  • 多個分區鍵
  • 跨分片查詢，使用C或Java編寫一個輔助應用來執行查詢并聚合結果集，也可以借助匯總表來執行
  • 分配數據、分片和節點
• 通過多實例擴展
• 通過集群擴展
  • MySQL Cluster（NDB Cluster）
  • Clustrix
  • ScaleBase
  • GenieDB
  • Akiban
• 向內擴展，對不再需要的數據進行歸檔和清理
• 保持活躍數據獨立

負載均衡

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

十二、高可用性

什么是高可用性

• 高可用性不是絕對的，只有相對更高的可用性，100%的可用性是不可能達到的
• 可用性每提高一點，所花費的成本都會遠超之前，可用性的效果和開銷的比例并不是線性的

導致宕機的原因

• 運行環境問題，最普遍的問題是磁盤空間耗盡
• 性能問題，最普遍的原因是運行很糟糕的SQL，或服務器BUG或錯誤的行為
• 糟糕的Schema和索引設計
• 復制問題通常由于主備數據不一致導致
• 數據丟失通常由于DROP TABLE的誤操作導致，并總是伴隨著缺少可用備份的問題

如何實現高可用性

• 可以通過同時進行以下兩步來獲得高可用性
  • 可以嘗試避免導致宕機的原因來減少宕機時間
  • 盡量保證在發生宕機時能夠快速恢復
• 提升平均失效時間（MTBF）
  • 對系統變更管理的缺失是所有導致宕機的事件中最普遍的原因
  • 缺少嚴格的評估
  • 沒有正確地監控MySQL的相關信息
• 降低平均恢復時間（MTTR）
  • 所有的宕機事件都是由多方面的失效聯合在一起導致的，可以通過利用合適的方法確保單點的安全來避免

避免單點失效

• 系統中任何不冗余的部分都是一個可能失效的單點
• 可以采用兩種方法來為系統增加冗余：增加空余容量和重復組件
• 共享存儲或磁盤復制
  • 能夠為數據庫服務器和存儲解耦合，通常使用的是SAN
  • 兩個優點：可以避免除存儲外的其他任何組件失效所引起的數據丟失，并為非存儲組件建立冗余提供可能
• MySQL同步復制
  • 當使用同步復制時，主庫上的事務只有在至少一個備庫上提交后才能認為其執行完成
  • 完成了兩個目標：當服務器崩潰時沒有提交的事務會丟失，并且至少有一個備庫擁有實時的數據副本
  • MySQL Cluster
  • Percona XtraDB Cluster
• 基于復制的冗余
  • 復制管理器是使用標準MySQL復制來創建冗余的工具

故障轉移和故障恢復

• 冗余一點也不會增加可用性或減少宕機，和故障轉移結合可以幫助更快地恢復，故障轉移最重要的部分就是故障恢復
• 提升備庫或切換角色
• 虛擬IP地址或IP接管
• 中間件解決方案，可以使用代理、端口轉發、網絡地址轉換或者硬件負載均衡來實現故障轉移和故障恢復
• 在應用中處理故障轉移

十三、云端的MySQL

云的優點、缺點和相關誤解

• 優點：

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

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

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

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

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

• 2.缺點：

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

  • 無法保證容量和可用性

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

MySQL在云端的經濟價值

• 云托管比較適合尚處于初級階段的企業，或者那些持續接觸新概念并且本質上是以適用為主的企業
• 大量使用的策略是盡可能又快又便宜地開發和發布應用
• 運行不是很重要的基礎設施

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

• 數據庫通常是一個應用系統中主要或唯一的有狀態并且持久化的組件
• MySQL并不具備在一個無共享集群中的對等角色服務器之間遷移的能力

四種基礎資源

• CPU通常少且慢
• 內在大小受限制
• I/O的吞吐量、延遲以及一致性受到限制
• 網絡性能還比較好

MySQL在云主機上的性能

• 需要高并發的工作負載并不是非常適合云計算
• 那些需要大量I/O的工作負載在云中并不總是表現很好

MySQL數據庫即服務（DBaaS）

• 將數據庫本身作為云資源

十四、應用層優化

• 常見問題
  • 什么東西在消耗系統中每臺主機的CPU、磁盤、網絡，以及內存資源？這些值是否合理？如果不合理，對應用程序做基本的檢查，看什么占用了資源
  • 應用真是需要所有獲取到的數據嗎？
  • 應用在處理本應由數據庫處理的事情嗎，或者反過來？
  • 應用執行了太多的查詢？
  • 應用執行的查詢太少了？
  • 應用創建了沒必要的MySQL連接嗎？
  • 應用對一個MySQL實例創建連接的次數太多了嗎？
  • 應用做了太多的“垃圾”查詢？
  • 應用使用了連接池嗎？這既可能是好事，也可能是壞事
  • 應用是否使用長連接？
  • 應用是否在不使用的時候還保持連接撕開？

Web服務器問題

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

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

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

• 進程存活時間變短策略：

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

  • 打開gzip壓縮

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

緩存

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

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

• 應用層緩存：

  • 本地緩存

  • 本地共享內存緩存

  • 分布式內存緩存

  • 磁盤上的緩存

• 緩存控制策略

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

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

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

• 可以在后臺預先請求一些頁面，并將結果存為靜態頁面，好處：

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

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

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

MySQL的替代品

• 搜索：Lucene和Sphinx
• 簡單的鍵值存儲：Redis
• 非結構化數據：Hadoop

十五、備份與恢復

為什么要備份

• 災難恢復
• 人們改變想法
• 審計
• 測試

定義恢復需求

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

設計MySQL備份方案

• 建議

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

  • 保留多個備份集

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

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

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

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

  • 對安全性要仔細考慮

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

• 邏輯備份優點：

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

  • 恢復非常簡單

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

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

  • 非常靈活

  • 與存儲引擎無關

  • 有助于避免數據損壞

• 邏輯備份的缺點：

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

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

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

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

• 物理備份優點：

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

  • 恢復簡單

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

• 物理備份缺點：

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

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

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

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

• MySQL備份需要考慮的幾點：

  • 非顯著數據
  • 代碼
  • 復制配置
  • 服務器配置
  • 選定的操作系統

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

• 差異、增量備份的建議：

  • 使用Percona XtraBackup和MySQL Enterprise Backup中的增量備份特性
  • 備份二進制日志，每次備份后FLUSH LOGS
  • 不要備份沒有改變的表
  • 不要備份沒有改變的行
  • 某些數據根本不需要備份
  • 備份所有的數據，然后發送到一個有去重特性的目的地

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

• 文件一致性：每個文件的內部一致性

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

管理和備份二進制日志

• expire_log_days變量MySQL定期清理日志

備份數據

• 生成邏輯備份

  • SQL導出：mysqldump方式

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

• 文件系統快照

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

從備份中恢復

• 恢復步驟：

  • 停止MySQL服務器

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

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

  • 改變配置

  • 改變文件權限

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

  • 載入邏輯備份文件

  • 檢查和重放二進制日志

  • 檢測已經還原的數據

  • 以完全權限重啟服務器

• 備份和恢復工具

  • MySQL Enterprise Backup
  • Percona XtraBackup
  • mylvmbackup
  • Zmanda Recovery Manager
  • mydunper
  • mysqldump

十六、MySQL用戶工具

接口工具

• MySQL Workbench
• SQLyog
• phpMyAdmin
• Adminer

命令行工具集

• Percona Toolkit
• Maatkit and Aspersa
• The openark kit
• MySql workbench

SQL實用集

• common_schema
• mysql-sr-lib
• MySQL UDF倉庫
• MySQL Forge

監測工具

• 開源的監控工具

  • Nagios

  • Zabbix

  • Zenoss

  • OpenNMS

  • Groundwork Open Source

  • MRTG

  • Cacti

  • Ganglia

  • Munin

• 商業監控系統

  • MySQL Enterprise Monitor

  • MONyog

  • New Relic

  • Circonus

  • Monitis

  • Splunk

  • Pingdom

• Innotop的命令行監控

總結

以上是生活随笔為你收集整理的高性能Mysql（第三版）笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。