MySQL性能優(yōu)化文檔

1、.主題：MySQL性能優(yōu)化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ì)和查詢優(yōu)化，服務(wù)器端優(yōu)化，存儲(chǔ)引擎優(yōu)化。2. 數(shù)據(jù)庫(kù)設(shè)計(jì)和查詢優(yōu)化 在MySQL Server性能調(diào)優(yōu)中，首先要考慮的就是Database Schema設(shè)計(jì)，這一點(diǎn)是非常重要的。一個(gè)糟糕的Schema設(shè)計(jì)即使在性能調(diào)優(yōu)的MySQL Ser

2、ver上運(yùn)行，也會(huì)表現(xiàn)出很差的性能；和Schema相似，查詢語(yǔ)句的設(shè)計(jì)也會(huì)影響MySQL的性能，應(yīng)該避免寫出低效的SQL查詢。這一節(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ù)類型，索引。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ù)并

3、提高數(shù)據(jù)的靈活性。通常數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)化是讓數(shù)據(jù)庫(kù)設(shè)計(jì)符合某一級(jí)別的范式，通常滿足第三范式即可。也有第四范式（也稱為 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)銷很大，更新一個(gè)屬性可能會(huì)更新

4、很多表，很多記錄。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/UNIQU

5、E KEY。MySQL可以優(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ù)類型 最基本的優(yōu)化之一就是使表在磁盤上占據(jù)的空間盡可能小。這能帶來(lái)性能非常大的提升，因?yàn)閿?shù)據(jù)小，磁盤讀入較快，并且在查詢過(guò)程中表內(nèi)容被處理所占用的內(nèi)存更少。同時(shí)，在更小的列上建索引，索引也會(huì)占用更少的資源?？梢允褂孟旅娴募夹g(shù)可以使表的性

6、能更好并且使存儲(chǔ)空間最?。?) 使用正確合適的類型，不要將數(shù)字存儲(chǔ)為字符串。2) 盡可能地使用最有效(最小)的數(shù)據(jù)類型。MySQL有很多節(jié)省磁盤空間和內(nèi)存的專業(yè)化類型。3) 盡可能使用較小的整數(shù)類型使表更小。例如，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列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能

7、會(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倍?？梢栽诓恍枰褂胾tf-8的字段上面使用latin1，例如mail，url等。2.1.3 索引 所有MySQL列類型可以被索引。對(duì)相關(guān)列使用索引是提高SELECT操作性能的最佳途徑。使用索引應(yīng)該注意以下幾點(diǎn)：1) MySQL只會(huì)使用前綴，例如key(a, b) where b=5 將使用不到索引。2) 要選擇性的使用索引。在變化很少的

8、列上使用索引并不是很好，例如性別列。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) 在查詢中要使用到索引（使用explain查看），可以減少讀磁盤的次數(shù)，加速讀取數(shù)據(jù)。11) 相近的鍵值比隨機(jī)好。Auto_incr

9、ement就比uuid好。12) Optimize table可以壓縮和排序index，注意不要頻繁運(yùn)行。13) Analyze table可以更新數(shù)據(jù)。2.2 Designing queries 查詢語(yǔ)句的優(yōu)化是一個(gè)Case by case的問(wèn)題，不同的sql有不同的優(yōu)化方案，在這里我只列出一些通用的技巧。1) 在有index的情況下，盡量保證查詢使用了正確的index?？梢允褂肊XPLAIN select 查看結(jié)果，分析查詢。2) 查詢時(shí)使用匹配的類型。例如select * from a where id=5， 如果這里id是字符類型，同時(shí)有index，這條查詢則使用不到index，會(huì)做全

10、表掃描，速度會(huì)很慢。正確的應(yīng)該是 where id=”5” ，加上引號(hào)表明類型是字符。3) 使用-log-slow-queries long-query-time=2查看查詢比較慢的語(yǔ)句。然后使用explain分析查詢，做出優(yōu)化。3. 服務(wù)器端優(yōu)化3.1 MySQL安裝 MySQL有很多發(fā)行版本，最好使用MySQL AB發(fā)布的二進(jìn)制版本。也可以下載源代碼進(jìn)行編譯安裝，但是編譯器和類庫(kù)的一些bug可能會(huì)使編譯完成的MySQL存在潛在的問(wèn)題。如果安裝MySQL的服務(wù)器使用的是Intel公司的處理器，可以使用intel c+編譯的版本，在Linux World2005的一篇PPT中提到，使用inte

11、l C+編譯器編譯的MySQL查詢速度比正常版本快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：要足夠大，

12、以適應(yīng)比較大的SQL查詢，對(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)銷比較大。一般設(shè)置為512。-query_cache_size： 用于緩存查詢的內(nèi)存大小。-datadir：mysql存放數(shù)據(jù)的根目錄，和安裝文件分開(kāi)在不同的磁盤可以提高一點(diǎn)性能。4. 存儲(chǔ)引擎優(yōu)化 MySQL支持不同的存儲(chǔ)引擎，主要使用的有MyISAM和InnoDB。4.1 MyISAM MyISAM管理非事務(wù)表。它提供高速

13、存儲(chǔ)和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默認(rèn)的存儲(chǔ)引擎，除非配置MySQL默認(rèn)使用另外一個(gè)引擎。4.1.1 MyISAM特性 MyISAM Properties1) 不支持事務(wù)，宕機(jī)會(huì)破壞表2) 使用較小的內(nèi)存和磁盤空間3) 基于表的鎖，并發(fā)更新數(shù)據(jù)會(huì)出現(xiàn)嚴(yán)重性能問(wèn)題4) MySQL只緩存Index，數(shù)據(jù)由OS緩存 Typical MyISAM usages1) 日志系統(tǒng)2) 只讀或者絕大部分是讀操作的應(yīng)用3) 全表掃描4) 批量導(dǎo)入數(shù)據(jù)5) 沒(méi)有事務(wù)的低并發(fā)讀/寫4.1.2 MyISAM優(yōu)化要點(diǎn)1) 聲明列為NOT NULL，

14、可以減少磁盤存儲(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 Locks1) 避免并發(fā)insert，update。2) 可以使用insert delayed，但是有可能丟失數(shù)據(jù)。3) 優(yōu)化查詢語(yǔ)句。4) 水平分區(qū)。5) 垂直分區(qū)。6) 如

15、果都不起作用，使用InnoDB。4.1.4 MyISAM Key Cache1) 設(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中可

16、以提高查詢速度。因?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查詢中，你可以自由地將

17、InnoDB類型的表與其它MySQL的表的類型混合起來(lái)，甚至在同一個(gè)查詢中也可以混合。InnoDB是為在處理巨大數(shù)據(jù)量時(shí)獲得最大性能而設(shè)計(jì)的。它的CPU使用效率非常高。InnoDB存儲(chǔ)引擎已經(jīng)完全與MySQL服務(wù)器整合，InnoDB存儲(chǔ)引擎為在內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。 InnoDB存儲(chǔ)它的表索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個(gè)表被存在分離的文件中。InnoDB 表可以是任何大小，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。許多需要高性能的大型數(shù)據(jù)庫(kù)站點(diǎn)上使用了InnoDB引擎。著名的Internet新聞

18、站點(diǎn)S運(yùn)行在InnoDB上。 Mytrix, Inc.在InnoDB上存儲(chǔ)超過(guò)1TB的數(shù)據(jù)，還有一些其它站點(diǎn)在InnoDB上處理平均每秒800次插入/更新的負(fù)荷。4.2.1 InnoDB特性 InnoDB Properties1) 支持事務(wù)，ACID，外鍵。2) Row level locks。 3) 支持不同的隔離級(jí)別。4) 和MyISAM相比需要較多的內(nèi)存和磁盤空間。5) 沒(méi)有鍵壓縮。6) 數(shù)據(jù)和索引都緩存在內(nèi)存hash表中。 InnoDB Good For1) 需要事務(wù)的應(yīng)用。2) 高并發(fā)的應(yīng)用。3) 自動(dòng)恢復(fù)。4) 較快速的基于主鍵的

19、操作。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)銷。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è)置下面

20、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ù)文

21、件放在不同的磁盤上。數(shù)據(jù)首先放在ibdata1中，當(dāng)達(dá)到900M以后，數(shù)據(jù)就放在ibdata2中。一旦達(dá)到50MB，ibdata2將以8MB為單位自動(dòng)增長(zhǎng)。如果磁盤滿了，需要在另外的磁盤上面增加一個(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ì)比較慢，太小

22、了影響查詢性能，一般取256M可以兼顧性能和recovery的速度。innodb_log_buffer_size：磁盤速度是很慢的，直接將log寫道磁盤會(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í)，日志緩沖被寫到日志文件，對(duì)日志文件做到磁盤操作的刷新。Truly ACID。速度慢。2) =2時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫到文件，但不對(duì)日志文件做到磁盤操作的刷新。只有操作系統(tǒng)崩潰或掉電才會(huì)刪除最后一秒的事務(wù)，不然不會(huì)丟失事務(wù)。3) =0時(shí)， 日志緩沖每秒一次地被寫到日志文件，并且對(duì)日志文件做到磁盤操作的刷新。任何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