ref：

作者：andyao
原文link:
轉載請留名

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 InnoDBInnoDB給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

[@more@]

mysql最佳化文章（推薦）

相關文章