mysql優(yōu)化文章（推薦）

ref：http://www.javaeye.com/topic/144033

作者：andyao
原文link: http://andyao.javaeye.com/admin/show/144033
轉(zhuǎn)載請(qǐng)留名

1. 簡(jiǎn)介
在Web應(yīng)用程序體系架構(gòu)中，數(shù)據(jù)持久層（通常是一個(gè)關(guān)系數(shù)據(jù)庫(kù)）是關(guān)鍵的核心部分，它對(duì)系統(tǒng)的性能有非常重要的影響。MySQL是目前使用最多的開(kāi)源數(shù)據(jù)庫(kù)，但是MySQL數(shù)據(jù)庫(kù)的默認(rèn)設(shè)置性能非常的差，僅僅是一個(gè)玩具數(shù)據(jù)庫(kù)。因此在產(chǎn)品中使用MySQL數(shù)據(jù)庫(kù)必須進(jìn)行必要的優(yōu)化。
優(yōu)化是一個(gè)復(fù)雜的任務(wù)，本文描述MySQL相關(guān)的數(shù)據(jù)庫(kù)設(shè)計(jì)和查詢(xún)優(yōu)化，服務(wù)器端優(yōu)化，存儲(chǔ)引擎優(yōu)化。

2. 數(shù)據(jù)庫(kù)設(shè)計(jì)和查詢(xún)優(yōu)化
在MySQL Server性能調(diào)優(yōu)中，首先要考慮的就是Database Schema設(shè)計(jì)，這一點(diǎn)是非常重要的。一個(gè)糟糕的Schema設(shè)計(jì)即使在性能調(diào)優(yōu)的MySQL Server上運(yùn)行，也會(huì)表現(xiàn)出很差的性能；和Schema相似，查詢(xún)語(yǔ)句的設(shè)計(jì)也會(huì)影響MySQL的性能，應(yīng)該避免寫(xiě)出低效的SQL查詢(xún)。這一節(jié)將詳細(xì)討論這兩方面的優(yōu)化。

2.1 Schema Design
Schema的優(yōu)化取決于將要運(yùn)行什么樣的query，不同的query會(huì)有不同的Schema優(yōu)化方案。2.2節(jié)將介紹Query Design的優(yōu)化。Schema設(shè)計(jì)同樣受到預(yù)期數(shù)據(jù)集大小的影響。Schema設(shè)計(jì)時(shí)主要考慮：標(biāo)準(zhǔn)化，數(shù)據(jù)類(lèi)型，索引。

2.1.1 標(biāo)準(zhǔn)化

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

1) 所有的“對(duì)象”都在它自己的table中，沒(méi)有冗余。
2) 數(shù)據(jù)庫(kù)通常由E-R圖生成。
3) 簡(jiǎn)潔，更新屬性通常只需要更新很少的記錄。
4) Join操作比較耗時(shí)。
5) Select，sort優(yōu)化措施比較少。
6) 適用于OLTP應(yīng)用。

非標(biāo)準(zhǔn)化的特點(diǎn)：

1) 在一張表中存儲(chǔ)很多數(shù)據(jù)，數(shù)據(jù)冗余。
2) 更新數(shù)據(jù)開(kāi)銷(xiāo)很大，更新一個(gè)屬性可能會(huì)更新很多表，很多記錄。
3) 在刪除數(shù)據(jù)是有可能丟失數(shù)據(jù)。
4) Select，order有很多優(yōu)化的選擇。
5) 適用于DSS應(yīng)用。

標(biāo)準(zhǔn)化和非標(biāo)準(zhǔn)化都有各自的優(yōu)缺點(diǎn)，通常在一個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)中可以混合使用，一部分表格標(biāo)準(zhǔn)化，一部分表格保留一些冗余數(shù)據(jù)：

1) 對(duì)OLTP使用標(biāo)準(zhǔn)化，對(duì)DSS使用非標(biāo)準(zhǔn)化
2) 使用物化視圖。MySQL不直接支持該數(shù)據(jù)庫(kù)特性，但是可以用MyISAM表代替。
3) 冗余一些數(shù)據(jù)在表格中，例如將ref_id和name存在同一張表中。但是要注意更新問(wèn)題。
4) 對(duì)于一些簡(jiǎn)單的對(duì)象，直接使用value作為建。例如IP address等
5) Reference by PRIMARY/UNIQUE KEY。MySQL可以?xún)?yōu)化這種操作，例如：

java 代碼

1. select city_name
2. from city,state
3. where state_id=state.id and state.code=‘CA’” converted to “select city_name from city where state_id=12

2.1.2 數(shù)據(jù)類(lèi)型
最基本的優(yōu)化之一就是使表在磁盤(pán)上占據(jù)的空間盡可能小。這能帶來(lái)性能非常大的提升，因?yàn)閿?shù)據(jù)小，磁盤(pán)讀入較快，并且在查詢(xún)過(guò)程中表內(nèi)容被處理所占用的內(nèi)存更少。同時(shí)，在更小的列上建索引，索引也會(huì)占用更少的資源。
可以使用下面的技術(shù)可以使表的性能更好并且使存儲(chǔ)空間最小：

1) 使用正確合適的類(lèi)型，不要將數(shù)字存儲(chǔ)為字符串。
2) 盡可能地使用最有效(最小)的數(shù)據(jù)類(lèi)型。MySQL有很多節(jié)省磁盤(pán)空間和內(nèi)存的專(zhuān)業(yè)化類(lèi)型。
3) 盡可能使用較小的整數(shù)類(lèi)型使表更小。例如，MEDIUMINT經(jīng)常比INT好一些，因?yàn)镸EDIUMINT列使用的空間要少25%。
4) 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節(jié)省一位。注意如果在應(yīng)用程序中確實(shí)需要NULL，應(yīng)該毫無(wú)疑問(wèn)使用它，只是避免 默認(rèn)地在所有列上有它。
5) 對(duì)于MyISAM表，如果沒(méi)有任何變長(zhǎng)列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會(huì)浪費(fèi)一些空間。即使你已經(jīng)用CREATE選項(xiàng)讓VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定長(zhǎng)度的行。
6) 使用sample character set，例如latin1。盡量少使用utf-8，因?yàn)閡tf-8占用的空間是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。

2.1.3 索引
所有MySQL列類(lèi)型可以被索引。對(duì)相關(guān)列使用索引是提高SELECT操作性能的最佳途徑。使用索引應(yīng)該注意以下幾點(diǎn)：

1) MySQL只會(huì)使用前綴，例如key(a, b) …where b=5 將使用不到索引。
2) 要選擇性的使用索引。在變化很少的列上使用索引并不是很好，例如性別列。
3) 在Unique列上定義Unique index。
4) 避免建立使用不到的索引。
5) 在Btree index中（InnoDB使用Btree），可以在需要排序的列上建立索引。
6) 避免重復(fù)的索引。
7) 避免在已有索引的前綴上建立索引。例如：如果存在index（a，b）則去掉index（a）。
8) 控制單個(gè)索引的長(zhǎng)度。使用key（name（8））在數(shù)據(jù)的前面幾個(gè)字符建立索引。
9) 越是短的鍵值越好，最好使用integer。
10) 在查詢(xún)中要使用到索引（使用explain查看），可以減少讀磁盤(pán)的次數(shù)，加速讀取數(shù)據(jù)。
11) 相近的鍵值比隨機(jī)好。Auto_increment就比uuid好。
12) Optimize table可以壓縮和排序index，注意不要頻繁運(yùn)行。
13) Analyze table可以更新數(shù)據(jù)。

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

1) 在有index的情況下，盡量保證查詢(xún)使用了正確的index?？梢允褂肊XPLAIN select …查看結(jié)果，分析查詢(xún)。
2) 查詢(xún)時(shí)使用匹配的類(lèi)型。例如select * from a where id=5， 如果這里id是字符類(lèi)型，同時(shí)有index，這條查詢(xún)則使用不到index，會(huì)做全表掃描，速度會(huì)很慢。正確的應(yīng)該是 … where id=”5” ，加上引號(hào)表明類(lèi)型是字符。
3) 使用--log-slow-queries –long-query-time=2查看查詢(xún)比較慢的語(yǔ)句。然后使用explain分析查詢(xún)，做出優(yōu)化。

3. 服務(wù)器端優(yōu)化3.1 MySQL安裝
MySQL有很多發(fā)行版本，最好使用MySQL AB發(fā)布的二進(jìn)制版本。也可以下載源代碼進(jìn)行編譯安裝，但是編譯器和類(lèi)庫(kù)的一些bug可能會(huì)使編譯完成的MySQL存在潛在的問(wèn)題。
如果安裝MySQL的服務(wù)器使用的是Intel公司的處理器，可以使用intel c++編譯的版本，在Linux World2005的一篇PPT中提到，使用intel C++編譯器編譯的MySQL查詢(xún)速度比正常版本快30%左右。Intel c++編譯版本可以在MySQL官方網(wǎng)站下載。

3.2 服務(wù)器設(shè)置優(yōu)化
MySQL默認(rèn)的設(shè)置性能很差，所以要做一些參數(shù)的調(diào)整。這一節(jié)介紹一些通用的參數(shù)調(diào)整，不涉及具體的存儲(chǔ)引擎（主要指MyISAM，InnoDB，相關(guān)優(yōu)化在4中介紹）。

--character-set：如果是單一語(yǔ)言使用簡(jiǎn)單的character set例如latin1。盡量少用Utf-8，utf-8占用空間較多。
--memlock：鎖定MySQL只能運(yùn)行在內(nèi)存中，避免swapping，但是如果內(nèi)存不夠時(shí)有可能出現(xiàn)錯(cuò)誤。
--max_allowed_packet：要足夠大，以適應(yīng)比較大的SQL查詢(xún)，對(duì)性能沒(méi)有太大影響，主要是避免出現(xiàn)packet錯(cuò)誤。
--max_connections：server允許的最大連接。太大的話會(huì)出現(xiàn)out of memory。
--table_cache：MySQL在同一時(shí)間保持打開(kāi)的table的數(shù)量。打開(kāi)table開(kāi)銷(xiāo)比較大。一般設(shè)置為512。
--query_cache_size： 用于緩存查詢(xún)的內(nèi)存大小。
--datadir：mysql存放數(shù)據(jù)的根目錄，和安裝文件分開(kāi)在不同的磁盤(pán)可以提高一點(diǎn)性能。

4. 存儲(chǔ)引擎優(yōu)化
MySQL支持不同的存儲(chǔ)引擎，主要使用的有MyISAM和InnoDB。

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

4.1.1 MyISAM特性
4.1.1.1 MyISAM Properties

1) 不支持事務(wù)，宕機(jī)會(huì)破壞表
2) 使用較小的內(nèi)存和磁盤(pán)空間
3) 基于表的鎖，并發(fā)更新數(shù)據(jù)會(huì)出現(xiàn)嚴(yán)重性能問(wèn)題
4) MySQL只緩存Index，數(shù)據(jù)由OS緩存

4.1.1.2 Typical MyISAM usages

1) 日志系統(tǒng)
2) 只讀或者絕大部分是讀操作的應(yīng)用
3) 全表掃描
4) 批量導(dǎo)入數(shù)據(jù)
5) 沒(méi)有事務(wù)的低并發(fā)讀/寫(xiě)

4.1.2 MyISAM優(yōu)化要點(diǎn)

1) 聲明列為NOT NULL，可以減少磁盤(pán)存儲(chǔ)。
2) 使用optimize table做碎片整理，回收空閑空間。注意僅僅在非常大的數(shù)據(jù)變化后運(yùn)行。
3) Deleting/updating/adding大量數(shù)據(jù)的時(shí)候禁止使用index。使用ALTER TABLE t DISABLE KEYS。
4) 設(shè)置myisam_max_[extra]_sort_file_size足夠大，可以顯著提高repair table的速度。

4.1.3 MyISAM Table Locks

1) 避免并發(fā)insert，update。
2) 可以使用insert delayed，但是有可能丟失數(shù)據(jù)。
3) 優(yōu)化查詢(xún)語(yǔ)句。
4) 水平分區(qū)。
5) 垂直分區(qū)。
6) 如果都不起作用，使用InnoDB。

4.1.4 MyISAM Key Cache

1) 設(shè)置key_buffer_size variable。MyISAN最主要的cache設(shè)置，用于緩存MyISAM表格的index數(shù)據(jù)，該參數(shù)只對(duì)MyISAM有影響。通常在只使用MyISAM的Server中設(shè)置25-33%的內(nèi)存大小。
2) 可以使用幾個(gè)不同的Key Caches（對(duì)一些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中可以提高查詢(xún)速度。因?yàn)閜reloading index是順序的，所以非常快。

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

4.2 InnoDBInnoDB給MySQL提供了具有提交，回滾和崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）存儲(chǔ)引擎。InnoDB提供row level lock，并且也在SELECT語(yǔ)句提供一個(gè)Oracle風(fēng)格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒(méi)有在InnoDB中擴(kuò)大鎖定的需要，因?yàn)樵贗nnoDB中row level lock適合非常小的空間。InnoDB也支持FOREIGN KEY約束。在SQL查詢(xún)中，你可以自由地將InnoDB類(lèi)型的表與其它MySQL的表的類(lèi)型混合起來(lái)，甚至在同一個(gè)查詢(xún)中也可以混合。
InnoDB是為在處理巨大數(shù)據(jù)量時(shí)獲得最大性能而設(shè)計(jì)的。它的CPU使用效率非常高。
InnoDB存儲(chǔ)引擎已經(jīng)完全與MySQL服務(wù)器整合，InnoDB存儲(chǔ)引擎為在內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。 InnoDB存儲(chǔ)它的表＆索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤(pán)分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個(gè)表被存在分離的文件中。InnoDB 表可以是任何大小，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。
許多需要高性能的大型數(shù)據(jù)庫(kù)站點(diǎn)上使用了InnoDB引擎。著名的Internet新聞?wù)军c(diǎn)Slashdot.org運(yùn)行在InnoDB上。 Mytrix, Inc.在InnoDB上存儲(chǔ)超過(guò)1TB的數(shù)據(jù)，還有一些其它站點(diǎn)在InnoDB上處理平均每秒800次插入/更新的負(fù)荷。
4.2.1 InnoDB特性
4.2.1.1 InnoDB Properties

1) 支持事務(wù)，ACID，外鍵。
2) Row level locks。
3) 支持不同的隔離級(jí)別。
4) 和MyISAM相比需要較多的內(nèi)存和磁盤(pán)空間。
5) 沒(méi)有鍵壓縮。
6) 數(shù)據(jù)和索引都緩存在內(nèi)存hash表中。

4.2.1.2 InnoDB Good For

1) 需要事務(wù)的應(yīng)用。
2) 高并發(fā)的應(yīng)用。
3) 自動(dòng)恢復(fù)。
4) 較快速的基于主鍵的操作。

4.2.2 InnoDB優(yōu)化要點(diǎn)

1) 盡量使用short，integer的主鍵。
2) Load/Insert數(shù)據(jù)時(shí)按主鍵順序。如果數(shù)據(jù)沒(méi)有按主鍵排序，先排序然后再進(jìn)行數(shù)據(jù)庫(kù)操作。
3) 在Load數(shù)據(jù)是為設(shè)置SET UNIQUE_CHECKS=0，SET FOREIGN_KEY_CHECKS=0，可以避免外鍵和唯一性約束檢查的開(kāi)銷(xiāo)。
4) 使用prefix keys。因?yàn)镮nnoDB沒(méi)有key壓縮功能。

4.2.3 InnoDB服務(wù)器端設(shè)定

innodb_buffer_pool_size：這是InnoDB最重要的設(shè)置，對(duì)InnoDB性能有決定性的影響。默認(rèn)的設(shè)置只有8M，所以默認(rèn)的數(shù)據(jù)庫(kù)設(shè)置下面InnoDB性能很差。在只有InnoDB存儲(chǔ)引擎的數(shù)據(jù)庫(kù)服務(wù)器上面，可以設(shè)置60-80%的內(nèi)存。更精確一點(diǎn)，在內(nèi)存容量允許的情況下面設(shè)置比InnoDB tablespaces大10%的內(nèi)存大小。

innodb_data_file_path：指定表數(shù)據(jù)和索引存儲(chǔ)的空間，可以是一個(gè)或者多個(gè)文件。最后一個(gè)數(shù)據(jù)文件必須是自動(dòng)擴(kuò)充的，也只有最后一個(gè)文件允許自動(dòng)擴(kuò)充。這樣，當(dāng)空間用完后，自動(dòng)擴(kuò)充數(shù)據(jù)文件就會(huì)自動(dòng)增長(zhǎng)（以8MB為單位）以容納額外的數(shù)據(jù)。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個(gè)數(shù)據(jù)文件放在不同的磁盤(pán)上。數(shù)據(jù)首先放在ibdata1中，當(dāng)達(dá)到900M以后，數(shù)據(jù)就放在ibdata2中。一旦達(dá)到50MB，ibdata2將以8MB為單位自動(dòng)增長(zhǎng)。如果磁盤(pán)滿了，需要在另外的磁盤(pán)上面增加一個(gè)數(shù)據(jù)文件。

innodb_autoextend_increment: 默認(rèn)是8M, 如果一次insert數(shù)據(jù)量比較多的話, 可以適當(dāng)增加.

innodb_data_home_dir：放置表空間數(shù)據(jù)的目錄，默認(rèn)在mysql的數(shù)據(jù)目錄，設(shè)置到和MySQL安裝文件不同的分區(qū)可以提高性能。

innodb_log_file_size：該參數(shù)決定了recovery speed。太大的話recovery就會(huì)比較慢，太小了影響查詢(xún)性能，一般取256M可以兼顧性能和recovery的速度
。
innodb_log_buffer_size：磁盤(pán)速度是很慢的，直接將log寫(xiě)道磁盤(pán)會(huì)影響InnoDB的性能，該參數(shù)設(shè)定了log buffer的大小，一般4M。如果有大的blob操作，可以適當(dāng)增大。

innodb_flush_logs_at_trx_commit=2： 該參數(shù)設(shè)定了事務(wù)提交時(shí)內(nèi)存中l(wèi)og信息的處理。

1) =1時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到日志文件，對(duì)日志文件做到磁盤(pán)操作的刷新。Truly ACID。速度慢。
2) =2時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到文件，但不對(duì)日志文件做到磁盤(pán)操作的刷新。只有操作系統(tǒng)崩潰或掉電才會(huì)刪除最后一秒的事務(wù)，不然不會(huì)丟失事務(wù)。
3) =0時(shí)， 日志緩沖每秒一次地被寫(xiě)到日志文件，并且對(duì)日志文件做到磁盤(pán)操作的刷新。任何mysqld進(jìn)程的崩潰會(huì)刪除崩潰前最后一秒的事務(wù)

innodb_file_per_table：可以存儲(chǔ)每個(gè)InnoDB表和它的索引在它自己的文件中。

transaction-isolation=READ-COMITTED: 如果應(yīng)用程序可以運(yùn)行在READ-COMMITED隔離級(jí)別，做此設(shè)定會(huì)有一定的性能提升。

innodb_flush_method： 設(shè)置InnoDB同步IO的方式：

1) Default – 使用fsync（）。
2) O_SYNC 以sync模式打開(kāi)文件，通常比較慢。
3) O_DIRECT，在Linux上使用Direct IO?？梢燥@著提高速度，特別是在RAID系統(tǒng)上。避免額外的數(shù)據(jù)復(fù)制和double buffering（mysql buffering 和OS buffering）。

innodb_thread_concurrency： InnoDB kernel最大的線程數(shù)。

1) 最少設(shè)置為(num_disks+num_cpus)*2。
2) 可以通過(guò)設(shè)置成1000來(lái)禁止這個(gè)限制

5. 緩存
緩存有很多種，為應(yīng)用程序加上適當(dāng)?shù)木彺娌呗詴?huì)顯著提高應(yīng)用程序的性能。由于應(yīng)用緩存是一個(gè)比較大的話題，所以這一部分還需要進(jìn)一步調(diào)研。

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