MySQL性能優化就是通過合理安排資源，調整系統參數使MySQL運作更快、更節省資源。MySQL性能優化包括查詢速度優化、更新速度優化、MySQL伺服器優化等。本篇部落格将從查詢優化、資料庫結構優化、MySQL伺服器優化3個方面介紹。
MySQL資料庫優化，一方面是找出系統瓶頸，提高MySQL資料庫整體性能；另一方面需要合理的結構設計和參數調整，以提高使用者操作響應速度；同時還要盡可能節省系統資源，以便系統可以提供更大負荷的服務。例如，通過優化檔案系統，提高磁盤I\O的讀寫書讀；通過優化作業系統排程政策，提高MySQL在高負荷下的負載能力；優化表結構、索引、查詢語句等使查詢響應更快。
在MySQL中使用SHOW STATUS語句查詢一些MySQL資料庫的性能，其文法為：SHOW STATUS LIKE 'value';
其中value是要查詢的參數值，一些常用的性能參數如下：
- Connections：連接配接MySQL伺服器的次數；
- Uptime：MySQL伺服器的上線時間；
- Slow_queries：慢查詢次數；
- Com_select：查詢操作的次數；
- Com_insert：插入操作的次數；
- Com_update：更新操作的次數；
- Com_delete：删除操作的次數。
| 優化查詢 |
通過對查詢語句的分析，可以了解查詢語句執行情況，找出查詢語句執行的瓶頸，進而優化查詢語句。MySQL中提供了EXPLAIN語句和DESCRIBE語句，用來分析查詢語句。
EXPLAIN語句基本文法格式為：EXPLAIN [EXTENDED] SELECT select_options 使用EXTENDED關鍵字，EXPLAIN語句将産生附加資訊。select_options是SELECT語句的查詢選項，包括FROM WHERE子句等。通過該語句可以分析EXPLAIN後面的SELECT語句執行情況，并且分析所查詢表的一些特征。
如下是使用EXPLAIN語句來分析一個簡單的查詢語句：
表1
查詢結果解釋如下：
- id：SELECT辨別符，表示SELECT的查詢序列号
- select_type：表示SELECT語句的類型。可取值有：SIMPLE表示簡單的查詢，不包括連接配接查詢和子查詢；PRIMARY表示主查詢，或者最外層的查詢語句（見表2）；UNION表示連接配接查詢的第二個或者後面的查詢語句；DEPENDENT UNIO連接配接查詢中的第2個或後面的SELECT語句，取決于外面的查詢；UNION RESULT連接配接查詢的結果；SUBQUERY子查詢中的第一個SELECT語句；DEPENDENT SUBQUERY子查詢中的第1個SELECT,取決于外面的查詢（見表2）；DERIVED表示導出表的SELECT
- table：表示查詢的表
- type：表示表的連接配接類型，如下是從最佳類型到最差類型的介紹。
- system：該表是僅有一行的系統表，是const連接配接類型的一個特例
- const：資料表最多隻有一個比對行，将在查詢開始時被讀取，并在餘下的查詢優化中作為常量對待。const用于使用常數值比較PRIMARY KEY或UNIQUE索引的所有部分的場合（見表3）
- eq_ref：對于每個來自前面表的行組合，從該表中讀取一行。當一個索引的所有部分都在查詢中使用并且索引是UNIQUE或PRIMARY KEY時，即可使用該類型
- ref：對于來自前面的表的任意行組合，将從該表中讀取所有比對行。這種類型用于索引既不是UNIQUE也不是PRIMARY KEY的情況，或者查詢中使用了索引列的子集。ref可以用于使用=或者<=>操作符的帶索引的列（見表4）
- ref_or_null：該連接配接類型如同ref，但是添加了MySQL可以專門搜尋包含NULL值得行。在解決子查詢中常使用該連接配接類型的優化。
- index_merge：該連接配接類型使用了索引合并優化方法
- unique_subquery：一個索引查找函數，可以完全替換子查詢，效率更高（見表2）
- index_subquery：該連接配接類型類似于unique_subquery，可以替換IN子查詢
- range：隻檢索給定範圍的行，使用一個索引來選擇行
- index：該連接配接類型與ALL相同除了隻掃描索引樹。由于索引檔案通常比資料檔案小，是以index比ALL快（見表2）
- ALL：對于前面表的任意行組合，進行完整的表掃描，通常可以增加索引來避免使用ALL連接配接
possible_keys：表示MySQL能使用哪個索引在該表中找到行，如果為NULL表示沒有相關索引。在這種情況下，通過檢查where子句看它是否引用某些列或适合索引的列來提高查詢性能。如果有，則可通過建立索引提高查詢性能key：表示查詢實際使用的索引，該值為NULL表示沒有選擇索引。key_len：表示MySQL選擇的索引字段按子節計算的長度ref：表示使用哪個列或常數與索引一起來查詢記錄rows：顯示MySQL在表中查詢時必須檢查的行數Extra：該列MySQL在處理查詢時的詳細資訊
表2
表3
表4
- 索引對查詢速度的影響
MySQL中提高性能的方式是對資料表設計合理的索引，加快查詢速度，首先如下圖所示，我們在tb_student表上建立索引，再次檢視rows列值得變化。發現查詢的行數由表1中的8行變為下表中的1行，進而通過對name列建立索引來提高查詢效率。
在使用索引的過程中，也需要注意一些情況。在這些情況下，有可能使用帶有索引的字段查詢時，索引并沒有起作用，下面重點介紹這幾種特殊情況。
-
- 使用LIKE關鍵字的查詢語句
在使用LIKE關鍵字進行查詢的查詢語句中，如果比對字元串的第一個字元為"%"，索引不會起作用。隻有"%"不在第一個位置時，索引才會有效，還是拿tb_student表的name字段進行查詢，如我們查詢名字末尾含有“亮”字的人。我們發現雖然我們對name字段建立了索引，但是索引并沒有減少查詢的行數。
-
- 使用多列索引的查詢語句
MySQL可以為多個字段建立索引。一個索引可以包括16個字段。對于多列索引，隻有查詢條件中使用了這些字段中第1個字段時，索引才會被使用。如下圖，我們首先對表tb_score的cID和grade兩個字段建立索引，來驗證多列索引的使用情況。
如下圖是未對表的兩個字段建立索引時，查詢某個課程成績大于88分的學生資訊，從下圖中可得該語句掃描了表中的7行資料。
我們對該兩個字段建立索引，然後同樣查詢某個課程且成績大于88分的學生的資訊，如下表所示，查詢的行數縮減到了4行。
當我們把限制條件改為僅對成績查詢時，如隻查詢成績大于88分的學生資訊，如下表所示，則該語句查詢了表中的全部15行資料。
-
- 使用OR關鍵字的查詢語句
查詢語句的查詢條件中隻有OR關鍵字，且OR前後的兩個條件中的列都是索引時，查詢中才使用索引。否則查詢将不使用索引。
還是拿tb_score表進行舉例，首先我們删除在該表上建立的索引index_cID_grade，如下圖所示：
然後查詢某門課程或成績大于88分的學生資訊，發現該語句掃描了表中全部行。
我們再次給表tb_score的cID和grade建立索引後，再次執行該語句發現查詢的行數減少了。
- 優化子查詢
MySQL使用子查詢可以進行SELECT語句的嵌套查詢，子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作。子查詢雖然可以使查詢語句很靈活，但執行效率不高。執行子查詢時，MySQL需要為内層查詢語句的查詢結果建立一個臨時表。然後外層查詢語句從臨時表中查詢記錄。查詢結束後再撤銷臨時表。是以查詢速度會受到影響。
在MySQL中，可以使用連接配接（JOIN）查詢來代替子查詢。連接配接查詢不需要建立臨時表，其速度比子查詢更快，如果查詢中使用索引的話，性能會更佳。連接配接之是以有更高的效率，是因為MySQL不需要再記憶體中建立臨時表來完成查詢工作。
| 優化插入記錄速度 |
插入記錄時，影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數等。根據這些情況，可以分别進行優化，本節将介紹優化插入記錄速度的幾種方法。
對于MyISAM引擎表常見的優化方法如下：
- 禁用索引。對于非空表插入記錄時，MySQL會根據表的索引對插入記錄建立索引。如果插入大量資料，建立索引會降低插入記錄的速度。為了解決這種情況可以在插入記錄之前禁用索引，資料插入完畢後在開啟索引。禁用索引的語句為：ALTER TABLE tb_name DISABLE KEYS; 重新開啟索引的語句為：ALTER TABLE table_name ENABLE KEYS; 對于空表批量導入資料，則不需要進行此操作，因為MyISAM引擎的表是在導入資料之後才建立索引的。
- 禁用唯一性檢查：資料插入時，MySQL會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢之後再開啟。禁用唯一性檢查的語句為：SET UNIQUE_CHECKS=0; 開啟唯一性檢查的語句為：SET UNIQUE_CHECKS=1;
- 使用批量插入。使用一條INSERT語句插入多條記錄。如INSERT INTO table_name VALUES(....),(....),(....)
- 使用LOAD DATA INFILE批量導入 當需要批量導入資料時，使用LOAD DATA INFILE語句導入資料的速度比INSERT語句快。
對于InnoDB引擎的表，常見的優化方法如下：
- 禁用唯一性檢查。同MyISAM引擎相同，通過SET UNIQUE_CHECKS=0; 導入資料之後将該值置1。
- 禁用外鍵檢查。插入資料之前執行禁止對外鍵的查詢，資料插入完成之後再恢複對外鍵的檢查。禁用外鍵檢查語句為：SET FOREIGN_KEY_CHECKS=0; 恢複對外鍵的檢查語句為：SET FOREIGN_KEY_CHECKS=1;
- 禁止自動送出。插入資料之前禁止事務的自動送出，資料導入完成之後，執行恢複自動送出操作。禁止自動送出語句為：SET AUTOCOMMIT=0; 恢複自動送出隻需将該值置1。
| 優化MySQL的參數 |
通過優化MySQL的參數可以提高資源使用率，進而達到提高MySQL伺服器性能的目的。如下是一些該方面參數的介紹。該參數的配置資訊都在my.cnf或者my.ini檔案中。
- key_buffer_size：表示索引緩沖區的大小。所有線程共享緩沖區。增加索引緩沖區可以得到更好處理的索引。當然如果這個值太大，會導緻作業系統頻換換頁，降低系統性能。
- table_cache：表示同時打開表的個數，該值越大能夠同時打開的表的個數越多。如果打開表的個數太多會影響作業系統的性能。
- query_cache_size：查詢緩沖區的大小。該值和query_cache_type配合使用。當query_cache_type=0,所有查詢都不使用緩沖區，但是MySQL并不會釋放query_cache_size所配置的緩沖區記憶體。當query_cache_type=1,所有查詢使用緩沖區，除非在查詢語句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM table_name; 當query_cache_type=2,隻有在查詢語句中使用SQL_CACHE關鍵字，查詢才會使用緩沖區。使用查詢緩沖區可以提高查詢速度，這種方式适用于修改操作少且經常執行相同的查詢操作的情況。
- sort_buffer_size：排序緩沖區的大小，該值越大排序的速度越快。
- innodb_buffer_pool_size：表示InnoDB類型的表和索引的最大緩存，該值越大查詢速度就會越快。