萬盛學電腦網 >> 數據庫 >> mysql教程 >> 高性能mysql學習筆記總結
高性能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能夠更好的優化這個查詢（後面會說）
