MySql5.6性能優化學習筆記

1. 目標
   ? 了解什么是優化
   ? 掌握優化查詢的方法
   ? 掌握優化數據庫結構的方法
   ? 掌握優化MySQL服務器的方法
2. 什么是優化？
   ? 合理安排資源、調整系統參數使MySQL運行更快、更節省資源。
   ? 優化是多方面的，包括查詢、更新、服務器等。
   ? 原則：減少系統瓶頸，減少資源占用，增加系統的反應速度。
3. 數據庫性能參數
   ? 使用SHOW STATUS語句查看MySQL數據庫的性能參數
   ? SHOW STATUS LIKE 'value‘
   ? 常用的參數：
   ? Slowqueries 慢查詢次數
   ? Com(CRUD) 操作的次數
   ? Uptime 上線時間
4. 查詢優化
   4.1. EXPLAIN
   在MySQL中可以使用EXPLAIN查看SQL執行計劃，用法：EXPLAIN SELECT * FROM tb_item
   

4.2. 結果說明
4.2.1. id
SELECT識別符。這是SELECT查詢序列號。這個不重要。
4.2.2. select_type
表示SELECT語句的類型。

有以下幾種值：
1、 SIMPLE
表示簡單查詢，其中不包含連接查詢和子查詢。
2、 PRIMARY
表示主查詢，或者是最外面的查詢語句。

3、 UNION
表示連接查詢的第2個或后面的查詢語句。

4、 DEPENDENT UNION
UNION中的第二個或后面的SELECT語句，取決于外面的查詢。
5、 UNION RESULT
連接查詢的結果。
6、 SUBQUERY
子查詢中的第1個SELECT語句。

7、 DEPENDENT SUBQUERY
子查詢中的第1個SELECT語句，取決于外面的查詢。
8、 DERIVED
SELECT(FROM 子句的子查詢)。
4.2.3. table
表示查詢的表。
4.2.4. type（重要）
表示表的連接類型。
以下的連接類型的順序是從最佳類型到最差類型：

1、 system
表僅有一行，這是const類型的特列，平時不會出現，這個也可以忽略不計。
2、 const
數據表最多只有一個匹配行，因為只匹配一行數據，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查詢，可理解為const是最優化的。

3、 eq_ref
mysql手冊是這樣說的:"對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比較帶索引的列。

4、 ref
查詢條件索引既不是UNIQUE也不是PRIMARY KEY的情況。ref可用于=或<或>操作符的帶索引的列。

5、 ref_or_null
該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。

上面這五種情況都是很理想的索引使用情況。

6、 index_merge
該聯接類型表示使用了索引合并優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。
7、 unique_subquery
該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一個索引查找函數,可以完全替換子查詢,效率更高。
8、 index_subquery
該聯接類型類似于unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9、 range
只檢索給定范圍的行,使用一個索引來選擇行。

10、 index
該聯接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數據文件小。
11、 ALL
對于每個來自于先前的表的行組合,進行完整的表掃描。（性能最差）
4.2.5. possible_keys
指出MySQL能使用哪個索引在該表中找到行。
如果該列為NULL，說明沒有使用索引，可以對該列創建索引來提高性能。
4.2.6. key
顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。

可以強制使用索引或者忽略索引：

4.2.7. key_len
顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。

注意：key_len是確定了MySQL將實際使用的索引長度。

4.2.8. ref
顯示使用哪個列或常數與key一起從表中選擇行。
4.2.9. rows
顯示MySQL認為它執行查詢時必須檢查的行數。
4.2.10. 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查詢的所有列,而不要額外搜索硬盤訪問實際的表。
4.3. 使用索引查詢需要注意
索引可以提供查詢的速度，但并不是使用了帶有索引的字段查詢都會生效，有些情況下是不生效的，需要注意！
4.3.1. 使用LIKE關鍵字的查詢
在使用LIKE關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不起作用。只有“%”不在第一個位置，索引才會生效。

4.3.2. 使用聯合索引的查詢
MySQL可以為多個字段創建索引，一個索引可以包括16個字段。對于聯合索引，只有查詢條件中使用了這些字段中第一個字段時，索引才會生效。

4.3.3. 使用OR關鍵字的查詢
查詢語句的查詢條件中只有OR關鍵字，且OR前后的兩個條件中的列都是索引時，索引才會生效，否則，索引不生效。

4.4. 子查詢優化
MySQL從4.1版本開始支持子查詢，使用子查詢進行SELECT語句嵌套查詢，可以一次完成很多邏輯上需要多個步驟才能完成的SQL操作。

子查詢雖然很靈活，但是執行效率并不高。

執行子查詢時，MYSQL需要創建臨時表，查詢完畢后再刪除這些臨時表，所以，子查詢的速度會受到一定的影響。

優化：
可以使用連接查詢（JOIN）代替子查詢，連接查詢時不需要建立臨時表，其速度比子查詢快。

5. 數據庫結構優化
   一個好的數據庫設計方案對于數據庫的性能往往會起到事半功倍的效果。

需要考慮數據冗余、查詢和更新的速度、字段的數據類型是否合理等多方面的內容。
5.1. 將字段很多的表分解成多個表
對于字段較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。

因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。

5.2. 增加中間表
對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。

通過建立中間表，將需要通過聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢。
5.3. 增加冗余字段
設計數據表時應盡量遵循范式理論的規約，盡可能的減少冗余字段，讓數據庫設計看起來精致、優雅。但是，合理的加入冗余字段可以提高查詢速度。

表的規范化程度越高，表和表之間的關系越多，需要連接查詢的情況也就越多，性能也就越差。

注意：
冗余字段的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導致數據不一致的問題。

6. 插入數據的優化
   插入數據時，影響插入速度的主要是索引、唯一性校驗、一次插入的數據條數等。

插入數據的優化，不同的存儲引擎優化手段不一樣，在MySQL中常用的存儲引擎有，MyISAM和InnoDB，兩者的區別：

http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html

6.1. MyISAM
6.1.1. 禁用索引
對于非空表，插入記錄時，MySQL會根據表的索引對插入的記錄建立索引。如果插入大量數據，建立索引會降低插入數據速度。

為了解決這個問題，可以在批量插入數據之前禁用索引，數據插入完成后再開啟索引。

禁用索引的語句：
ALTER TABLE table_name DISABLE KEYS

開啟索引語句：
ALTER TABLE table_name ENABLE KEYS

對于空表批量插入數據，則不需要進行操作，因為MyISAM引擎的表是在導入數據后才建立索引。
6.1.2. 禁用唯一性檢查
唯一性校驗會降低插入記錄的速度，可以在插入記錄之前禁用唯一性檢查，插入數據完成后再開啟。

禁用唯一性檢查的語句：SET UNIQUE_CHECKS = 0;

開啟唯一性檢查的語句：SET UNIQUE_CHECKS = 1;
6.1.3. 批量插入數據
插入數據時，可以使用一條INSERT語句插入一條數據，也可以插入多條數據。

第二種方式的插入速度比第一種方式快。
6.1.4. 使用LOAD DATA INFILE
當需要批量導入數據時，使用LOAD DATA INFILE語句比INSERT語句插入速度快很多。
6.2. InnoDB
6.2.1. 禁用唯一性檢查
用法和MyISAM一樣。
6.2.2. 禁用外鍵檢查
插入數據之前執行禁止對外鍵的檢查，數據插入完成后再恢復，可以提供插入速度。

禁用：SET foreign_key_checks = 0;
開啟：SET foreign_key_checks = 1;
6.2.3. 禁止自動提交
插入數據之前執行禁止事務的自動提交，數據插入完成后再恢復，可以提高插入速度。

禁用：SET autocommit = 0;
開啟：SET autocommit = 1;

7. 服務器優化
   7.1. 優化服務器硬件
   服務器的硬件性能直接決定著MySQL數據庫的性能，硬件的性能瓶頸，直接決定MySQL數據庫的運行速度和效率。

需要從以下幾個方面考慮：
1、 配置較大的內存。足夠大的內存，是提高MySQL數據庫性能的方法之一。內存的IO比硬盤快的多，可以增加系統的緩沖區容量，使數據在內存停留的時間更長，以減少磁盤的IO。
2、 配置高速磁盤，比如SSD。
3、 合理分配磁盤IO，把磁盤IO分散到多個設備上，以減少資源的競爭，提高并行操作能力。
4、 配置多核處理器，MySQL是多線程的數據庫，多處理器可以提高同時執行多個線程的能力。
7.2. 優化MySQL的參數
通過優化MySQL的參數可以提高資源利用率，從而達到提高MySQL服務器性能的目的。



MySQL的配置參數都在my.conf或者my.ini文件的[mysqld]組中，常用的參數如下：

要求：必須記憶至少3個。