高效能mysql學習筆記總結

（架構及曆史）

Mysql邏輯架構

如果能在腦中有個Mysql各組件如何協同工作的架構圖，這會很有助於我們瞭解Mysql伺服器。

Mysql邏輯架構圖

最上層串連/線程處理這裡，處理串連、授權和安全。

第二層是解析器、查詢快取、最佳化器這三部分。處理查詢解析、分析、最佳化、緩衝以及所有內建函數、所有跨儲存引擎的功能（預存程序、觸發器、視圖）。

第三層就是儲存引擎了。伺服器通過API與各種儲存引擎互動，這些介面屏蔽了不同儲存引擎的差異。

另外Mysql還有各方面的特性，包括：執行與最佳化、並發控制、讀寫鎖、鎖粒度（行級鎖、表級鎖）、顯式鎖（select… for update）、事務等。

曆史

這部分也介紹了寫Mysql的曆史，我就不發出來了。

現在Mysql和Innodb引擎都是oracle的了。

Mysql的儲存引擎

主要的兩個是InnoDB、MyISAM。另外還有一些內建引擎：Archive、Blackhole、CSV、Federated、Memory等，還有一些第三方引擎就不列舉了。

擷取表資訊

使用命令show table status like ‘tableName’ \G擷取表的資訊。

會出現如下介紹的幾列資訊。

1.Name
表名稱

2.Engine:
表的儲存引擎

3.Version:
版本

4.Row_format
行格式。對於MyISAM引擎，這可能是Dynamic，Fixed或Compressed。動態行的行長度可變，例如Varchar或Blob類型欄位。固定行是指行長度不變，例如Char和Integer類型欄位。

5. Rows
表中的行數。對於非事務性表，這個值是精確的，對於事務性引擎，這個值通常是估算的。
6.Avg_row_length
平均每行包括的位元組數

7.Data_length
整個表的資料量(單位：位元組)

8.Max_data_length
表可以容納的最大資料量

9.Index_length
索引佔用磁碟的空間大小

10.Data_free
對於MyISAM引擎，標識已指派，但現在未使用的空間，並且包含了已被刪除行的空間。
11.Auto_increment
下一個Auto_increment的值

12.Create_time
表的建立時間

13.Update_time
表的最新動向時間

14.Check_time
使用 check table 或myisamchk工具檢查表的最近時間

15.Collation
表的預設字元集和字元定序

16.Checksum
如果啟用，則對整個表的內容計算時的校正和

17.Create_options
指表建立時的其他所有選項

18.Comment
包含了其他額外資訊，對於MyISAM引擎，包含了注釋徐標新，如果表使用的是innodb引擎 ，將現實表的剩餘空間。如果是一個視圖，注釋裡麵包含了VIEW字樣。

（索引1）

簡介

索引（Mysql裡面成為鍵（KEY））是儲存引擎用於快速找到記錄的資料結構。

我們都知道，索引對於大資料量的查詢來說非常有用；當然，不恰當的索引在資料量很大的時候對效能的影響也會很大。

索引類型

B-Tree（實際上是B+Tree）

正常情況下，我們說的Mysql的索引就是B+Tree實現的，其每個葉子節點有子葉子節點的指標，這些指標同時還儲存這其子葉子節點的值的上下限。B+Tree對索引列是順序組織儲存的，所以很適合尋找範圍資料。

雜湊索引

雜湊索引基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。

其他

空間資料索引（R-Tree）、全文索引、其他第三方索引

B+Tree索引使用舉例

索引的優點

除了可以快速定位到要查的資料行外，以B+Tree為例，其是順序儲存的，所以還可以用來做ORDER BY或GROUP BY操作；另外索引中儲存了實際的值，如果查詢的值只存在與索引中的話可以直接從索引中得到。

索引大大的減少了資料庫需要檢索的資料量。
索引可以協助伺服器避免排序和暫存資料表。
索引可以將隨機I/O變為順序I/O。
索引最佳化

獨立的列

就是所用的索引不能是運算式或者函數的一部分。

比如where id + 1 = 5

首碼索引和索引選擇性

首碼索引

首碼索引是指如果是varchar類型的列，只用前面的一部分作為索引，從而節省索引空間，但是這樣就不能直接從索引中得到資料了（因為索引只存了一部分）。

索引選擇性

索引選擇性是指不重複的索引值（也叫基數）與所有資料（#T）的比。範圍是1/#T到1之間。當然選擇性為1的時候是最好的，一般我們的主鍵索引就是1。

文本類型的列mysql是不允許把全部內容作為索引的。這時候我們就要選擇足夠長的內容保證選擇性，而又不能太長導致儲存空間過大。

書中給出的方案就是先以前十為樣本，看總量（COUNT(*)），然後截取量（LEFT(xxx, n)）一點點增加，如果和總量相似，則可以把這個長度作為首碼。

另外一個方案就是COUNT(DISTINCT xxx)/COUNT(*)與COUNT(DISTINCTLEFT(xxx, n))/COUNT(*)比較，n一點點增大，當比例比較相似的時候則可以使用此長度。

多列索引

多列索引需要注意的一個問題就是索引的順序。有的人以為為每列都做索引比較好，其實這是個錯誤的認識。雖然mysql後來有了索引合并的功能，但是效能並不是很好，同時也說明這個索引建立的不是最優。

索引列順序

B+Tree是按照順序儲存的索引，當有多列索引存在時，mysql是首先按照第一列的順序儲存，之後按照第二列順序儲存。

有個三星索引的概念：索引將相關的記錄放在一起獲得第一星；如果索引中的資料順序和尋找中的排列順序一致，獲得第二星；如果索引中的列包含了查詢中包含的全部列則獲得第三星。

通常情況下，索引的排序應該是，排序列、分組列、選擇性列排序。

聚簇索引

聚簇就是聚集在一起的意思嘛。

其實一般情況下，我們定義的主鍵就會是聚簇索引，而如果我們不定義主鍵的話，mysql也會選擇一個非空的唯一索引來作為聚簇索引，如果沒有這樣的索引，mysql也會隱式的給我們定義一個主鍵作為聚簇索引。

優點

相關的資料儲存在一起。
訪問資料快，叢集索引和資料行同時儲存在BTree中。
使用覆蓋索引的時候可以直接使用頁節點上的主索引值。

缺點

如果資料都在記憶體，則聚簇索引就沒什麼用了。
不是順序插入的話速度會比較慢。（其實用自增逐漸就可以了，UUID這種就會超級慢）
更新聚簇索引的列的代價很高。
如果行比較稀疏的話全表掃描會比較慢。
二級索引包含主鍵、二級索引需要兩次查詢（先找到主鍵、再通過主鍵查詢資料行）。

非聚簇索引（二級索引）

二級索引就是除了主鍵外的其他索引。

由於主鍵及資料行儲存在了B+Tree中，通過主鍵索引的查詢會非常快，但是二級索引至儲存了主鍵，所以需要造成二次查詢。

覆蓋索引

索引查詢資料確實會很快，但是Mysql也可以直接從索引中擷取資料。如果要查詢的所有資料都在索引中，Mysql就不需要再去資料行中讀取資料了。

如果一個索引包含所有要查詢的欄位，那麼這個索引稱為覆蓋索引。

而二級索引都會額外包含主鍵索引，所以二級索引列及主鍵也可以成為覆蓋索引，完成查詢。

使用索引掃描做排序

Mysql有兩種方式排序，一種是對結果排序，一種是直接通過索引掃描排序。

要用索引排序，除非索引列的順序和ORDER BY的順序一致並且順序（要麼DESC，要麼ASC）也一致，才能使用索引掃描排序（如果不一致，可以把其中之一改為相反數或反串）。

而這個排序的限制是ORDER BY子句的第一個欄位必須為索引的最左首碼，否則就不能用索引排序。

這裡有個例外，就是如果排序索引的前面列被指定為常數，則可以使用。

比如有索引(date, id1, id2)，語句如下：

select … from xxx where date = ‘2015-04-30’ ORDER BY id1, id2

這時候第一個索引date為常量，可以使用第二、三列索引完成索引排序。

冗餘索引和重複索引

有的人可能對一個欄位做了多種類型的索引，這其實是多餘的（重複索引）。

但是冗餘索引和重複索引還是不一樣的。如果建立了索引(A, B)又建立了索引(A)則是冗餘索引，其實(A)就是(A, B)的首碼，完全可以用(A, B)取代（對於B+Tree來說）。但如果建立索引(B)就不一樣了。

未使用的索引

有些索引我們當初建表的時候想當然的就建立了，但可能實際上我們根本就沒有使用。大家都知道索引會導致表資料更新的時候的效率變低，這樣的索引實際上就是累贅，我們應該刪除他們。

我們可以使用Percona Toolkit的pt-index-usage來讀取查詢日誌，並對日誌中的每條查詢進行EXPLAIN，然後列印出關於索引和查詢的報告。這樣我們就會發現哪些索引幾乎沒有使用過，可以果斷刪掉了。

（索引2）

索引最佳化案例

我就以書中的案例給大家介紹一下。

假設要設計一個線上約會網站，使用者資訊表包含這些列：國家、地區、城市、性別、眼睛顏色等。其需要設計根據使用者各種資訊進行搜尋，還需要根據使用者的最後上線時間、其他會員對使用者的評分排序。

盡量用到索引排序

使用索引排序會嚴格限制到索引的設計與查詢。如果希望根據使用者評分排序，就沒法使用索引查詢年齡範圍；如果使用範圍查詢，就沒法使用其他索引（其後面的索引）進行排序了。如果這個where條件很常用，那還是資料查出來在排序吧。

支援多種過濾條件

country、sex選擇性其實並不高，國家多數就那麼幾個，sex一般情況也沒幾個，但是這種查詢條件基本上都會用到，甚至有的時候sex都是單選的，所以這些選項加入到索引中也是可以的，索引為(sex, country)。如果需要查多個情況的時候，我們可以使用in查詢，這時候可以查詢到需要的資料，也可以用到首碼索引。

但是in的條件還是不宜過長。雖然年齡也可以用in完成，使之不用變為範圍索引，但是年齡的範圍多數不是很短。

所以我們應該盡量把age放到最後，因為一般會用age >= xx and age =< xx，這樣age索引用後，再往後的索引就不能用了，很少會出現age=xx的使用方式。

同樣我們可以把眼睛顏色、頭髮顏色使用in來處理，但是這種方式實際上會出現指數增長現象。等於實際上的組合形式是各種in的數量相乘。

避免多個範圍條件

比如我們想查詢近一周（7天）登入過的使用者，以及年齡範圍的使用者，這樣無論如何都只能使用一個索引了。因為範圍索引後的索引列都不能使用了。當然我們可以把年齡用in了，但是年齡的範圍數量真的有點多。

這裡同樣提供了一種方式，我們可以設定不讓使用者輸入時間段，而採用近1天登入過、近3天登入過、近7天登入過、近半月登入過、近1月登入過等選項讓使用者選擇。

然後增加一個欄位，每次使用者登入把值設定為0，然後由定時任務處理，把符合上述要求的使用者分別賦值為1、2、3等，這樣只需使用=條件即可完成，而非範圍索引。

最佳化排序

當我們執行排序的時候，我們使用select * from xxx order by xxx limit xx, xx的時候，就算有索引，翻頁到很後面效能會很低，由於每次都會去關聯到指定行去判斷資料，所以IO很高。

這時候可以使用延遲關聯的方式來更高效的使用索引。

select * from xxx inner join (

select pk from xxx order by xxx limit xx, xx

) as x using(pk)

這樣可以利用二級索引內建主鍵索引，進行二次查詢完成最佳化。

索引與表的維護

維護表有三個主要目的：找到並修複損壞的表、維護準確的索引統計資訊、減少片段。

找到並修複損壞的表

表損壞通常都是系統或者硬體問題，如果你發現了什麼莫名其妙的問題，試著使用check table命令來檢查是否發生了表損壞。一般innodb引擎的表，我們使用如下命令可以修複表：

ALTER TABLE xxx ENGINE=INNODB;

如果innodb的表發生了損壞，一般是很嚴重的問題，因為innodb一般不會損壞。如果損壞了，要麼是硬體問題、要麼是DBA的一些錯誤操作，比如在Mysql外部操作的檔案。

更新統計資料

可以通過命令ANALYZE TABLE來重建統計資訊（比如行數，INNODB通常是不準的）。

可以使用SHOW INDEX FROM xx來查看索引的基數（Cardinality）。這個會有很多資訊，需要注意的是，Cardinality給出了（估算）這個索引列有多少不同的值。

減少索引和資料片段

BTree所以可能會片段化，這會嚴重影響查詢的效率。片段化的索引可能會以很差或無序的方式儲存在磁碟上。

有三種類型的資料片段：

行片段：這種片段指的是資料行被儲存為多個地方的多個片段中。即使查詢只從索引中訪問一行記錄，行片段也會導致效能降低；
行間片段：行片段是指邏輯上順序的頁，或者行在磁碟上不是順序儲存的；
剩餘空間片段：剩餘空間片段是指資料頁中有大量的空餘空間，這會導致伺服器讀取大量不需要的資料，從而造成浪費。

對於MySIAM儲存引擎，這三種片段都會出現。

對於InnoDB，行片段不會出現，InnoDB會移動短小的行並重寫到片段中。

可以使用Optimize table 表名來整理資料。如果不支援這個命令，可以使用ALTER TABLE xxx ENGINE=INNODB;這個命令來重建表。

索引小結

在建立索引或者利用這些索引編寫查詢語句的時候，應注意以下三點：

單行訪問是很慢的。如果伺服器從儲存中讀取一個資料區塊只為一行資料，那麼就浪費了很多的工作。最好讀取的塊能夠儘可能多的包含需要的行資料。
按順序存取範圍資料是很快的，並且GROUP BY操作也無須為排序和按組彙總消耗太多效能了。
索引覆蓋查詢是很快的。如果查詢的結果可以直接從索引中得到，可以省去二次查詢擷取行資料。

其實多數情況我們都希望能夠設計一個完美的三星索引適合所有查詢，但是這真的很難，我們需要取捨，對於常用查詢必須有很好的索引，對於一些比較少用的查詢，我們可以容許對查詢出的資料區塊做排序、篩選

（查詢效能最佳化）

最佳化mysql，除了需要最優的庫表設計、很好的索引設計以外，還需要編寫合理的查詢。如果查詢寫的比較爛，索引有可能根本就用不上。那樣不管庫表、索引設計的再好也沒用。

下面會介紹如何寫出高效的查詢語句，以及明白高效與低效的原因。

為什麼查詢速度會慢

查詢需要快速，主要是要回應時間快。

查詢需要消耗的時間如下：網路、CPU計算、產生統計資訊和執行計畫、鎖等待（互斥等待）等，尤其是向底層儲存引擎檢索資料的時候的這些調用。這些調用需要在記憶體操作、CPU操作和記憶體不足時導致的IO操作上消耗時間。

有些操作我們做了一些不必要的額外操作、甚至重複調用。所以我們需要最佳化這些查詢，去最佳化和消除這些操作花費的時間。

慢查詢基礎：最佳化資料訪問

查詢效能低的最主要原因就是訪問的資料過多。有時候我們可能並不總是需要那麼多的資料，但是我們仍然去訪問那麼多的資料。

通常我們從兩方面去分析會比較有效：

1、確認應用程式是否在檢索大量超過需要的資料。通常是訪問了太多的行，也有可能是訪問了太多的列。
2、確認Mysql是否在分析大量超過需要分析的資料。

是否向資料庫請求了不需要的資料

有些查詢會請求超過需要的資料，然後這些多餘的資料會被應用程式丟棄。這會給Mysql伺服器帶來額外的負擔，並增加網路開銷，另外也會消耗應用伺服器的CPU和記憶體資源。

下面是一些常見的錯誤：

查詢不需要的記錄

使用ResultSet返回結果集的時候，我們可能只需要前10條，一些人會天真的以為應用程式是一個迴圈，拿一條資料，我們關閉結果集，就不會繼續查詢了。實際上Mysql不會這樣，而會全部查出，然後存在記憶體裡。這種情況我們最好使用LIMIT來截取資料。

多表關聯時返回全部列

join時最好只取需要的列，減少子查詢內容（有時更可以讓子查詢使用覆蓋查詢）。

總是取出全部的列

使用select *進行查詢，當我們看到這樣的語句的時候，總要用懷疑的眼光看看是否真的需要所有的列，這樣會導致查詢無法使用索引覆蓋查詢。不過很多人覺得這樣可以簡化開發，同時這樣寫其實有時候可以使用到查詢快取。

多次查詢相同的資料

有時候一個介面需要幾處相同的資料，我們儘可能的把一樣的資料通過一次查詢擷取出來。不要一遍遍的查詢，耗費效能。

Mysql是否在掃描額外的記錄

在確定只返回了需要的記錄之後，我們需要在確定下是否只掃描了需要的記錄。衡量Mysql查詢開銷最簡單的三個準則就是：

回應時間
掃描行數
返回行數

這三個指標都會記錄到Mysql的慢日誌中，我們主要查看是否掃描了過多的行這點最為重要。

回應時間

回應時間其實只是表面上的值。

回應時間=服務時間+排隊時間。

一般比較常見的排隊等待時間是消耗在了I/O及鎖上。一般這個並不是很好分析時間是否在一個合理的範圍，多數是憑經驗去判斷這樣一個查詢，它的回應時間是否在一個合理的值。

掃描的行數和返回的行數

理想情況下，掃描的行數和返回的行數應該是相同的，但是理想的情況往往很少，尤其是在做關聯查詢的時候。但是我們也應該儘可能的減少掃描的行數。

掃描行數和訪問類型

Mysql有好幾種方式可以查詢一條資料，有些方式需要掃描好多行才能獲得一條資料，有些方式不需要掃描就能擷取資料。

在EXPLAIN語句返回的type列反映了傳回型別。訪問類型有很多種：從全表掃描到索引掃描、範圍掃描、唯一索引掃描、常數引用等。這裡列的是速度從慢到快，掃描的行數也少從多到少。

如果查詢沒有辦法找到合適的訪問類型，那麼最好的辦法就是為之建立一個合適的索引。

比如select * from xxx where id=1

這種查詢，當id是主鍵索引的時候，我們使用EXPLAIN的時候發現，type是ref，其另一個參數值rows為1也說明了這個查詢只需要訪問一條資料。如果沒有索引呢，這時候type會是ALL，同時rows一般會比較大，約等資料條數，另外這裡的Extra參數會顯示為Using where，標識Mysql是通過where條件來確定資料行的。

一般Mysql通過三種方式應用where條件：

在索引中使用where條件過濾不匹配的記錄，這是在儲存引擎層做的。
使用覆蓋索引返回記錄（Extra會提示為Using index），直接從索引過濾並返回需要的記錄。
從資料表返回資料然後過濾資料（Extra會提示為Using where）。

所以說建立好的索引可以大大最佳化我們的查詢效能，但也並不總是可以最佳化，有時候確實是只能訪問很多資料而沒有什麼好的索引能夠幫忙。

一般我們如果發現，一個查詢掃描的行比返回的行多很多，我們通常可以採用下面的方法去解決：

使用索引覆蓋掃描。
改變庫表結構，比如使用單獨的匯總表。
重寫查詢語句，讓Mysql能夠更好的最佳化這個查詢（後面會說）