MySql5.6性能優(yōu)化學(xué)習(xí)筆記

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

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

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

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

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

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

1、 system
表僅有一行，這是const類型的特列，平時(shí)不會出現(xiàn)，這個(gè)也可以忽略不計(jì)。
2、 const
數(shù)據(jù)表最多只有一個(gè)匹配行，因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查詢，可理解為const是最優(yōu)化的。

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

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

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

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

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

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

可以強(qiáng)制使用索引或者忽略索引：

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

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

4.2.8. ref
顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。
4.2.9. rows
顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)。
4.2.10. Extra
該列包含MySQL解決查詢的詳細(xì)信息
? Distinct:MySQL發(fā)現(xiàn)第1個(gè)匹配行后,停止為當(dāng)前的行組合搜索更多的行。
? Not exists:MySQL能夠?qū)Σ樵冞M(jìn)行LEFT JOIN優(yōu)化,發(fā)現(xiàn)1個(gè)匹配LEFT JOIN標(biāo)準(zhǔn)的行后,不再為前面的的行組合在該表內(nèi)檢查更多的行。
? range checked for each record (index map: #):MySQL沒有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來自前面的表的列值已知,可能部分索引可以使用。
? Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。
? Using index:從只使用索引樹中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來檢索表中的列信息。
? Using temporary:為了解決查詢,MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來容納結(jié)果。
? Using where:WHERE 子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。
? Using sort_union(...), Using union(...), Using intersect(...):這些函數(shù)說明如何為index_merge聯(lián)接類型合并索引掃描。
? Using index for group-by:類似于訪問表的Using index方式,Using index for group-by表示MySQL發(fā)現(xiàn)了一個(gè)索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實(shí)際的表。
4.3. 使用索引查詢需要注意
索引可以提供查詢的速度，但并不是使用了帶有索引的字段查詢都會生效，有些情況下是不生效的，需要注意！
4.3.1. 使用LIKE關(guān)鍵字的查詢
在使用LIKE關(guān)鍵字進(jìn)行查詢的查詢語句中，如果匹配字符串的第一個(gè)字符為“%”，索引不起作用。只有“%”不在第一個(gè)位置，索引才會生效。

4.3.2. 使用聯(lián)合索引的查詢
MySQL可以為多個(gè)字段創(chuàng)建索引，一個(gè)索引可以包括16個(gè)字段。對于聯(lián)合索引，只有查詢條件中使用了這些字段中第一個(gè)字段時(shí)，索引才會生效。

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

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

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

執(zhí)行子查詢時(shí)，MYSQL需要?jiǎng)?chuàng)建臨時(shí)表，查詢完畢后再刪除這些臨時(shí)表，所以，子查詢的速度會受到一定的影響。

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

5. 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化
   一個(gè)好的數(shù)據(jù)庫設(shè)計(jì)方案對于數(shù)據(jù)庫的性能往往會起到事半功倍的效果。

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

因?yàn)楫?dāng)一個(gè)表的數(shù)據(jù)量很大時(shí)，會由于使用頻率低的字段的存在而變慢。

5.2. 增加中間表
對于需要經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率。

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

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

注意：
冗余字段的值在一個(gè)表中修改了，就要想辦法在其他表中更新，否則就會導(dǎo)致數(shù)據(jù)不一致的問題。

6. 插入數(shù)據(jù)的優(yōu)化
   插入數(shù)據(jù)時(shí)，影響插入速度的主要是索引、唯一性校驗(yàn)、一次插入的數(shù)據(jù)條數(shù)等。

插入數(shù)據(jù)的優(yōu)化，不同的存儲引擎優(yōu)化手段不一樣，在MySQL中常用的存儲引擎有，MyISAM和InnoDB，兩者的區(qū)別：

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

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

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

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

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

對于空表批量插入數(shù)據(jù)，則不需要進(jìn)行操作，因?yàn)镸yISAM引擎的表是在導(dǎo)入數(shù)據(jù)后才建立索引。
6.1.2. 禁用唯一性檢查
唯一性校驗(yàn)會降低插入記錄的速度，可以在插入記錄之前禁用唯一性檢查，插入數(shù)據(jù)完成后再開啟。

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

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

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

禁用：SET foreign_key_checks = 0;
開啟：SET foreign_key_checks = 1;
6.2.3. 禁止自動(dòng)提交
插入數(shù)據(jù)之前執(zhí)行禁止事務(wù)的自動(dòng)提交，數(shù)據(jù)插入完成后再恢復(fù)，可以提高插入速度。

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

7. 服務(wù)器優(yōu)化
   7.1. 優(yōu)化服務(wù)器硬件
   服務(wù)器的硬件性能直接決定著MySQL數(shù)據(jù)庫的性能，硬件的性能瓶頸，直接決定MySQL數(shù)據(jù)庫的運(yùn)行速度和效率。

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



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

要求：必須記憶至少3個(gè)。