MySQL性能優(yōu)化詳解

來源：易賢網閱讀：1208 次日期：2015-08-27 16:45:12

溫馨提示：易賢網小編為您整理了“MySQL性能優(yōu)化詳解”,方便廣大網友查閱！

MySQL數據庫性能優(yōu)化是本文的主要核心，將從數據庫的優(yōu)化設計，到具體的操作。好的優(yōu)化能使服務器性能提升較大的空間，希望本文對大家有所幫助。

1. MySQL性能優(yōu)化簡介

在Web應用程序體系架構中，數據持久層（通常是一個關系數據庫）是關鍵的核心部分，它對系統(tǒng)的性能有非常重要的影響。MySQL是目前使用最多的開源數據庫，但是MySQL數據庫的默認設置性能非常的差，僅僅是一個玩具數據庫。因此在產品中使用MySQL數據庫必須進行必要的優(yōu)化。

優(yōu)化是一個復雜的任務，本文描述MySQL相關的數據庫設計和查詢優(yōu)化，服務器端優(yōu)化，存儲引擎優(yōu)化。

2. 數據庫設計和查詢優(yōu)化

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

2.1 Schema Design

Schema的優(yōu)化取決于將要運行什么樣的query，不同的query會有不同的Schema優(yōu)化方案。2.2節(jié)將介紹Query Design的優(yōu)化。Schema設計同樣受到預期數據集大小的影響。Schema設計時主要考慮：標準化，數據類型，索引。

2.1.1 標準化

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

標準化的特點：

1) 所有的“對象”都在它自己的table中，沒有冗余。

2) 數據庫通常由E-R圖生成。

3) 簡潔，更新屬性通常只需要更新很少的記錄。

4) Join操作比較耗時。

5) Select，sort優(yōu)化措施比較少。

6) 適用于OLTP應用。

非標準化的特點：

1) 在一張表中存儲很多數據，數據冗余。

2) 更新數據開銷很大，更新一個屬性可能會更新很多表，很多記錄。

3) 在刪除數據是有可能丟失數據。

4) Select，order有很多優(yōu)化的選擇。

5) 適用于DSS應用。

標準化和非標準化都有各自的優(yōu)缺點，通常在一個數據庫設計中可以混合使用，一部分表格標準化，一部分表格保留一些冗余數據：

1) 對OLTP使用標準化，對DSS使用非標準化

2) 使用物化視圖。MySQL不直接支持該數據庫特性，但是可以用MyISAM表代替。

3) 冗余一些數據在表格中，例如將ref_id和name存在同一張表中。但是要注意更新問題。

4) 對于一些簡單的對象，直接使用value作為建。例如IP address等

5) Reference by PRIMARY/UNIQUE KEY。MySQL可以優(yōu)化這種操作，例如：

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 數據類型

最基本的優(yōu)化之一就是使表在磁盤上占據的空間盡可能小。這能帶來性能非常大的提升，因為數據小，磁盤讀入較快，并且在查詢過程中表內容被處理所占用的內存更少。同時，在更小的列上建索引，索引也會占用更少的資源。

可以使用下面的技術可以使表的性能更好并且使存儲空間最?。?/P>

1) 使用正確合適的類型，不要將數字存儲為字符串。

2) 盡可能地使用最有效(最小)的數據類型。MySQL有很多節(jié)省磁盤空間和內存的專業(yè)化類型。

3) 盡可能使用較小的整數類型使表更小。例如，MEDIUMINT經常比INT好一些，因為MEDIUMINT列使用的空間要少25%。

4) 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節(jié)省一位。注意如果在應用程序中確實需要NULL，應該毫無疑問使用它，只是避免默認地在所有列上有它。

5) 對于MyISAM表，如果沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。即使你已經用CREATE選項讓VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定長度的行。

6) 使用sample character set，例如latin1。盡量少使用utf-8，因為utf-8占用的空間是latin1的3倍?？梢栽诓恍枰褂胾tf-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

查詢語句的優(yōu)化是一個Case by case的問題，不同的sql有不同的優(yōu)化方案，在這里我只列出一些通用的技巧。

1) 在有index的情況下，盡量保證查詢使用了正確的index?？梢允褂肊XPLAIN select …查看結果，分析查詢。

2) 查詢時使用匹配的類型。例如select * from a where id=5，如果這里id是字符類型，同時有index，這條查詢則使用不到index，會做全表掃描，速度會很慢。正確的應該是 … where id=”5” ，加上引號表明類型是字符。

3) 使用--log-slow-queries –long-query-time=2查看查詢比較慢的語句。然后使用explain分析查詢，做出優(yōu)化。

3. 服務器端優(yōu)化

3.1 MySQL安裝

MySQL有很多發(fā)行版本，最好使用MySQL AB發(fā)布的二進制版本。也可以下載源代碼進行編譯安裝，但是編譯器和類庫的一些bug可能會使編譯完成的MySQL存在潛在的問題。

如果安裝 MySQL的服務器使用的是Intel公司的處理器，可以使用intel c++編譯的版本，在Linux World2005的一篇PPT中提到，使用intel C++編譯器編譯的MySQL查詢速度比正常版本快30%左右。Intel c++編譯版本可以在MySQL官方網站下載。

3.2 服務器設置優(yōu)化

MySQL默認的設置性能很差，所以要做一些參數的調整。這一節(jié)介紹一些通用的參數調整，不涉及具體的存儲引擎（主要指MyISAM，InnoDB，相關優(yōu)化在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. 存儲引擎優(yōu)化

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

4.1 MyISAM

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

4.1.1 MyISAM特性

4.1.1.1 MyISAM Properties

1) 不支持事務，宕機會破壞表

2) 使用較小的內存和磁盤空間

3) 基于表的鎖，并發(fā)更新數據會出現嚴重性能問題

4) MySQL只緩存Index，數據由OS緩存

4.1.1.2 Typical MyISAM usages

1) 日志系統(tǒng)

2) 只讀或者絕大部分是讀操作的應用

3) 全表掃描

4) 批量導入數據

5) 沒有事務的低并發(fā)讀/寫

4.1.2 MyISAM優(yōu)化要點

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) 避免并發(fā)insert，update。

2) 可以使用insert delayed，但是有可能丟失數據。

3) 優(yōu)化查詢語句。

4) 水平分區(qū)。

5) 垂直分區(qū)。

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存儲它的表＆索引在一個表空間中，表空間可以包含數個文件（或原始磁盤分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何大小，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。

許多需要高性能的大型數據庫站點上使用了 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) 高并發(fā)的應用。

3) 自動恢復。

4) 較快速的基于主鍵的操作。

4.2.2 InnoDB優(yōu)化要點

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安裝文件不同的分區(qū)可以提高性能。

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：該參數設定了事務提交時內存中l(wèi)og信息的處理。

1) =1時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢。

2) =2時，在每個事務提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務，不然不會丟失事務。

3) =0時，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新。任何mysqld進程的崩潰會刪除崩潰前最后一秒的事務

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

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

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

1) Default – 使用fsync（）。