MySQL效能最佳化

1. 簡介

在Web應用程式體系架構中，資料持久層（通常是一個關聯式資料庫）是關鍵的核心部分，它對系統的效能有非常重要的影響。MySQL是目前使用最多的開來源資料庫，但是MySQL資料庫的預設設定效能非常的差，僅僅是一個玩具資料庫。因此在產品中使用MySQL資料庫必須進行必要的最佳化。
最佳化是一個複雜的任務，本文描述MySQL相關的資料庫設計和查詢最佳化，伺服器端最佳化，儲存引擎最佳化。

2. 資料庫設計和查詢最佳化
在MySQL Server效能調優中，首先要考慮的就是Database Schema設計，這一點是非常重要的。一個糟糕的Schema設計即使在效能調優的MySQL Server上運行，也會表現出很差的效能；和Schema相似，查詢語句的設計也會影響MySQL的效能，應該避免寫出低效的SQL查詢。這一節將詳細討論這兩方面的最佳化。

2.1 Schema Design
Schema的最佳化取決於將要運行什麼樣的query，不同的query會有不同的Schema最佳化方案。2.2節將介紹Query Design的最佳化。Schema設計同樣受到預期資料集大小的影響。Schema設計時主要考慮：標準化，資料類型，索引。

2.1.1 標準化

標準化是在資料庫中組織資料的過程。其中包括，根據設計規則建立表並在這些表間建立關係；通過取消冗餘度與不一致相關性，該設計規則可以同時保護資料並提高資料的靈活性。通常資料庫標準化是讓資料庫設計符合某一層級的範式，通常滿足第三範式即可。也有第四範式（也稱為 Boyce Codd範式，BCNF)）與第五範式存在，但是在實際設計中很少考慮。忽視這些規則可能使得資料庫的設計不太完美，但這不應影響功能。
標準化的特點：

1) 所有的“對象”都在它自己的table中，沒有冗餘。
2) 資料庫通常由E-R圖產生。
3) 簡潔，更新屬性通常只需要更新很少的記錄。
4) Join操作比較耗時。
5) Select，sort最佳化措施比較少。
6) 適用於OLTP應用。

非標準化的特點：

1) 在一張表中儲存很多資料，資料冗餘。
2) 更新資料開銷很大，更新一個屬性可能會更新很多表，很多記錄。
3) 在刪除資料是有可能遺失資料。
4) Select，order有很多最佳化的選擇。
5) 適用於DSS應用。

標準化和非標準化都有各自的優缺點，通常在一個資料庫設計中可以混合使用，一部分表格標準化，一部分表格保留一些冗餘資料：

1) 對OLTP使用標準化，對DSS使用非標準化
2) 使用物化視圖。MySQL不直接支援該資料庫特性，但是可以用MyISAM表代替。
3) 冗餘一些資料在表格中，例如將ref_id和name存在同一張表中。但是要注意更新問題。
4) 對於一些簡單的對象，直接使用value作為建。例如IP address等
5) Reference by PRIMARY/UNIQUE KEY。MySQL可以最佳化這種操作，例如：

java 代碼
select city_name
from city,state
where state_id=state.id and state.code=‘CA'” converted to “select city_name from city where state_id=12

2.1.2 資料類型
最基本的最佳化之一就是使表在磁碟上佔據的空間儘可能小。這能帶來效能非常大的提升，因為資料小，磁碟讀入較快，並且在查詢過程中表內容被處理所佔用的記憶體更少。同時，在更小的列上建索引，索引也會佔用更少的資源。
可以使用下面的技術可以使表的效能更好並且使儲存空間最小：

1) 使用正確合適的類型，不要將數字儲存為字串。
2) 儘可能地使用最有效(最小)的資料類型。MySQL有很多節省磁碟空間和記憶體的專業化類型。
3) 儘可能使用較小的整數類型使表更小。例如，MEDIUMINT經常比INT好一些，因為MEDIUMINT列使用的空間要少25%。
4) 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程式中確實需要NULL，應該毫無疑問使用它，只是避免 預設地在所有列上有它。
5) 對於MyISAM表，如果沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。即使你已經用CREATE選項讓VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定長度的行。
6) 使用sample character set，例如latin1。盡量少使用utf-8，因為utf-8佔用的空間是latin1的3倍。可以在不需要使用utf-8的欄位上面使用latin1，例如mail，url等。

2.1.3 索引
所有MySQL列類型可以被索引。對相關列使用索引是提高SELECT操作效能的最佳途徑。使用索引應該注意以下幾點：

1) MySQL只會使用首碼，例如key(a, b) …where b=5 將使用不到索引。
2) 要選擇性的使用索引。在變化很少的列上使用索引並不是很好，例如性別列。
3) 在Unique列上定義Unique index。
4) 避免建立使用不到的索引。
5) 在Btree index中（InnoDB使用Btree），可以在需要排序的列上建立索引。
6) 避免重複的索引。
7) 避免在已有索引的首碼上建立索引。例如：如果存在index（a，b）則去掉index（a）。
8) 控制單個索引的長度。使用key（name（8））在資料的前面幾個字元建立索引。
9) 越是短的索引值越好，最好使用integer。
10) 在查詢中要使用到索引（使用explain查看），可以減少讀磁碟的次數，加速讀取資料。
11) 相近的索引值比隨機好。Auto_increment就比uuid好。
12) Optimize table可以壓縮和排序index，注意不要頻繁運行。
13) Analyze table可以更新資料。

2.2 Designing queries
查詢語句的最佳化是一個Case by case的問題，不同的sql有不同的最佳化方案，在這裡我只列出一些通用的技巧。

1) 在有index的情況下，盡量保證查詢使用了正確的index。可以使用EXPLAIN select …查看結果，分析查詢。
2) 查詢時使用匹配的類型。例如select * from a where id=5， 如果這裡id是字元類型，同時有index，這條查詢則使用不到index，會做全表掃描，速度會很慢。正確的應該是 … where id=”5” ，加上引號表明類型是字元。
3) 使用--log-slow-queries –long-query-time=2查看查詢比較慢的語句。然後使用explain分析查詢，做出最佳化。

3. 伺服器端最佳化

3.1 MySQL安裝
MySQL有很多發行版本，最好使用MySQL AB發布的二進位版本。也可以下載原始碼進行編譯安裝，但是編譯器和類庫的一些bug可能會使編譯完成的MySQL存在潛在的問題。
如果安裝MySQL的伺服器使用的是Intel公司的處理器，可以使用intel c++編譯的版本，在Linux World2005的一篇PPT中提到，使用intel C++編譯器編譯的MySQL查詢速度比正常版本快30%左右。Intel c++編譯版本可以在MySQL官方網站下載。

3.2 伺服器設定最佳化
MySQL預設的設定效能很差，所以要做一些參數的調整。這一節介紹一些通用的參數調整，不涉及具體的儲存引擎（主要指MyISAM，InnoDB，相關最佳化在4中介紹）。

--character-set：如果是單一語言使用簡單的character set例如latin1。盡量少用Utf-8，utf-8佔用空間較多。
--memlock：鎖定MySQL只能運行在記憶體中，避免swapping，但是如果記憶體不夠時有可能出現錯誤。
--max_allowed_packet：要足夠大，以適應比較大的SQL查詢，對效能沒有太大影響，主要是避免出現packet錯誤。
--max_connections：server允許的最大串連。太大的話會出現out of memory。
--table_cache：MySQL在同一時間保持開啟的table的數量。開啟table開銷比較大。一般設定為512。
--query_cache_size： 用於緩衝查詢的記憶體大小。
--datadir：mysql存放資料的根目錄，和安裝檔案分開在不同的磁碟可以提高一點效能。

4. 儲存引擎最佳化

MySQL支援不同的儲存引擎，主要使用的有MyISAM和InnoDB。

4.1 MyISAM
MyISAM管理非事務表。它提供高速儲存和檢索，以及全文檢索搜尋能力。MyISAM在所有MySQL配置裡被支援，它是預設的儲存引擎，除非配置MySQL預設使用另外一個引擎。

4.1.1 MyISAM特性
4.1.1.1 MyISAM Properties

1) 不支援事務，宕機會破壞表
2) 使用較小的記憶體和磁碟空間
3) 基於表的鎖，並發更新資料會出現嚴重性能問題
4) MySQL只緩衝Index，資料由OS緩衝

4.1.1.2 Typical MyISAM usages

1) 日誌系統
2) 唯讀或者絕大部分是讀操作的應用
3) 全表掃描
4) 大量匯入資料
5) 沒有事務的低並發讀/寫

4.1.2 MyISAM最佳化要點

1) 聲明列為NOT NULL，可以減少磁碟儲存。
2) 使用optimize table做磁碟重組，回收空閑空間。注意僅僅在非常大的資料變化後運行。
3) Deleting/updating/adding大量資料的時候禁止使用index。使用ALTER TABLE t DISABLE KEYS。
4) 設定myisam_max_[extra]_sort_file_size足夠大，可以顯著提高repair table的速度。

4.1.3 MyISAM Table Locks

1) 避免並發insert，update。
2) 可以使用insert delayed，但是有可能遺失資料。
3) 最佳化查詢語句。
4) 水平資料分割。
5) 垂直資料分割。
6) 如果都不起作用，使用InnoDB。

4.1.4 MyISAM Key Cache

1) 設定key_buffer_size variable。MyISAN最主要的cache設定，用於緩衝MyISAM表格的index資料，該參數只對MyISAM有影響。通常在只使用MyISAM的Server中設定25-33%的記憶體大小。
2) 可以使用幾個不同的Key Caches（對一些hot data）。

a) SET GLOBAL test.key_buffer_size=512*1024;
b) CACHE INDEX t1.i1, t2.i1, t3 IN test;

2) Preload index到Cache中可以提高查詢速度。因為preloading index是順序的，所以非常快。

a) LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES；

4.2 InnoDB
InnoDB給MySQL提供了具有提交，復原和崩潰恢複能力的事務安全（ACID相容）儲存引擎。InnoDB提供row level lock，並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多使用者部署和效能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中row level lock適合非常小的空間。InnoDB也支援FOREIGN KEY約束。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。
InnoDB是為在處理巨大資料量時獲得最大效能而設計的。它的CPU使用效率非常高。
InnoDB儲存引擎已經完全與MySQL伺服器整合，InnoDB儲存引擎為在記憶體中快取資料和索引而維持它自己的緩衝池。 InnoDB儲存它的表＆索引在一個資料表空間中，資料表空間可以包含數個檔案（或原始磁碟分割）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的檔案中。InnoDB 表可以是任何大小，即使在檔案尺寸被限制為2GB的作業系統上。
許多需要高效能的大型資料庫網站上使用了InnoDB引擎。著名的Internet新聞網站Slashdot.org運行在InnoDB上。 Mytrix, Inc.在InnoDB上儲存超過1TB的資料，還有一些其它網站在InnoDB上處理平均每秒800次插入/更新的負荷。
4.2.1 InnoDB特性
4.2.1.1 InnoDB Properties

1) 支援事務，ACID，外鍵。
2) Row level locks。
3) 支援不同的隔離等級。
4) 和MyISAM相比需要較多的記憶體和磁碟空間。
5) 沒有鍵壓縮。
6) 資料和索引都緩衝在記憶體hash表中。

4.2.1.2 InnoDB Good For

1) 需要事務的應用。
2) 高並發的應用。
3) 自動回復。
4) 較快速的基於主鍵的操作。

4.2.2 InnoDB最佳化要點

1) 盡量使用short，integer的主鍵。
2) Load/Insert資料時按主鍵順序。如果資料沒有按主鍵排序，先排序然後再進行資料庫操作。
3) 在Load資料是為設定SET UNIQUE_CHECKS=0，SET FOREIGN_KEY_CHECKS=0，可以避免外鍵和唯一性限制式檢查的開銷。
4) 使用prefix keys。因為InnoDB沒有key壓縮功能。

4.2.3 InnoDB伺服器端設定

innodb_buffer_pool_size：這是InnoDB最重要的設定，對InnoDB效能有決定性的影響。預設的設定只有8M，所以預設的資料庫設定下面InnoDB效能很差。在只有InnoDB儲存引擎的資料庫伺服器上面，可以設定60-80%的記憶體。更精確一點，在記憶體容量允許的情況下面設定比InnoDB tablespaces大10%的記憶體大小。

innodb_data_file_path：指定表資料和索引儲存的空間，可以是一個或者多個檔案。最後一個資料檔案必須是自動擴充的，也只有最後一個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動成長（以8MB為單位）以容納額外的資料。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個資料檔案放在不同的磁碟上。資料首先放在ibdata1中，當達到900M以後，資料就放在ibdata2中。一旦達到50MB，ibdata2將以8MB為單位自動成長。如果磁碟滿了，需要在另外的磁碟上面增加一個資料檔案。
innodb_autoextend_increment: 預設是8M, 如果一次insert資料量比較多的話, 可以適當增加.

innodb_data_home_dir：放置資料表空間資料的目錄，預設在mysql的資料目錄，設定到和MySQL安裝檔案不同的分區可以提高效能。

innodb_log_file_size：該參數決定了recovery speed。太大的話recovery就會比較慢，太小了影響查詢效能，一般取256M可以兼顧效能和recovery的速度
。
innodb_log_buffer_size：磁碟速度是很慢的，直接將log寫道磁碟會影響InnoDB的效能，該參數設定了log buffer的大小，一般4M。如果有大的blob操作，可以適當增大。

innodb_flush_logs_at_trx_commit=2： 該參數設定了事務提交時記憶體中log資訊的處理。

1) =1時，在每個事務提交時，日誌緩衝被寫到記錄檔，對記錄檔做到磁碟操作的重新整理。Truly ACID。速度慢。
2) =2時，在每個事務提交時，日誌緩衝被寫到檔案，但不對記錄檔做到磁碟操作的重新整理。只有作業系統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。
3) =0時， 日誌緩衝每秒一次地被寫到記錄檔，並且對記錄檔做到磁碟操作的重新整理。任何mysqld進程的崩潰會刪除崩潰前最後一秒的事務

innodb_file_per_table：可以儲存每個InnoDB表和它的索引在它自己的檔案中。

transaction-isolation=READ-COMITTED: 如果應用程式可以運行在READ-COMMITED隔離等級，做此設定會有一定的效能提升。

innodb_flush_method： 設定InnoDB同步IO的方式：

1) Default – 使用fsync（）。
2) O_SYNC 以sync模式開啟檔案，通常比較慢。
3) O_DIRECT，在Linux上使用Direct IO。可以顯著提高速度，特別是在RAID系統上。避免額外的資料複製和double buffering（mysql buffering 和OS buffering）。

innodb_thread_concurrency： InnoDB kernel最大的線程數。

1) 最少設定為(num_disks+num_cpus)*2。
2) 可以通過設定成1000來禁止這個限制

5. 緩衝

緩衝有很多種，為應用程式加上適當的緩衝策略會顯著提高應用程式的效能。由於應用緩衝是一個比較大的話題，所以這一部分還需要進一步調研。

6. Reference
1) http://www.mysqlperformanceblog.com/
2) Advanced MySQL Performance Optimization, Peter Zaitsev, Tobias Asplund, MySQL Users Conference 2005
3) Improving MySQL Server Performance with Intel C++ Compiler，Peter Zaitsev，Linux World 2005
4) MySQL Performance Optimization, Peter Zaitsev, Percona Ltd, OPEN SOURCE DATABASE CONFERENCE 2006
5) MySQL Server Settings Tuning, Peter Zaitsev, co-founder, Percona Ltd, 2007
6) MySQL Reference Manual