1、.主題：MySQL性能優化1. 簡介 在Web應用程序體系架構中，數據持久層（通常是一個關系數據庫）是關鍵的核心部分，它對系統的性能有非常重要的影響。MySQL是目前使用最多的開源數據庫，但是MySQL數據庫的默認設置性能非常的差，僅僅是一個玩具數據庫。因此在產品中使用MySQL數據庫必須進行必要的優化。優化是一個復雜的任務，本文描述MySQL相關的數據庫設計和查詢優化，服務器端優化，存儲引擎優化。2. 數據庫設計和查詢優化 在MySQL Server性能調優中，首先要考慮的就是Database Schema設計，這一點是非常重要的。一個糟糕的Schema設計即使在性能調優的MySQL Ser

2、ver上運行，也會表現出很差的性能；和Schema相似，查詢語句的設計也會影響MySQL的性能，應該避免寫出低效的SQL查詢。這一節將詳細討論這兩方面的優化。2.1 Schema Design Schema的優化取決于將要運行什么樣的query，不同的query會有不同的Schema優化方案。2.2節將介紹Query Design的優化。Schema設計同樣受到預期數據集大小的影響。Schema設計時主要考慮：標準化，數據類型，索引。2.1.1 標準化 標準化是在數據庫中組織數據的過程。其中包括，根據設計規則創建表并在這些表間建立關系；通過取消冗余度與不一致相關性，該設計規則可以同時保護數據并

3、提高數據的靈活性。通常數據庫標準化是讓數據庫設計符合某一級別的范式，通常滿足第三范式即可。也有第四范式（也稱為 Boyce Codd范式，BCNF)）與第五范式存在，但是在實際設計中很少考慮。忽視這些規則可能使得數據庫的設計不太完美，但這不應影響功能。標準化的特點：1) 所有的“對象”都在它自己的table中，沒有冗余。2) 數據庫通常由E-R圖生成。3) 簡潔，更新屬性通常只需要更新很少的記錄。4) Join操作比較耗時。5) Select，sort優化措施比較少。6) 適用于OLTP應用。非標準化的特點：1) 在一張表中存儲很多數據，數據冗余。2) 更新數據開銷很大，更新一個屬性可能會更新

4、很多表，很多記錄。3) 在刪除數據是有可能丟失數據。4) Select，order有很多優化的選擇。5) 適用于DSS應用。標準化和非標準化都有各自的優缺點，通常在一個數據庫設計中可以混合使用，一部分表格標準化，一部分表格保留一些冗余數據：1) 對OLTP使用標準化，對DSS使用非標準化2) 使用物化視圖。MySQL不直接支持該數據庫特性，但是可以用MyISAM表代替。3) 冗余一些數據在表格中，例如將ref_id和name存在同一張表中。但是要注意更新問題。4) 對于一些簡單的對象，直接使用value作為建。例如IP address等5) Reference by PRIMARY/UNIQU

5、E KEY。MySQL可以優化這種操作，例如：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 數據類型 最基本的優化之一就是使表在磁盤上占據的空間盡可能小。這能帶來性能非常大的提升，因為數據小，磁盤讀入較快，并且在查詢過程中表內容被處理所占用的內存更少。同時，在更小的列上建索引，索引也會占用更少的資源?？梢允褂孟旅娴募夹g可以使表的性

6、能更好并且使存儲空間最?。?) 使用正確合適的類型，不要將數字存儲為字符串。2) 盡可能地使用最有效(最小)的數據類型。MySQL有很多節省磁盤空間和內存的專業化類型。3) 盡可能使用較小的整數類型使表更小。例如，MEDIUMINT經常比INT好一些，因為MEDIUMINT列使用的空間要少25%。4) 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程序中確實需要NULL，應該毫無疑問使用它，只是避免 默認地在所有列上有它。5) 對于MyISAM表，如果沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能

7、會浪費一些空間。即使你已經用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) 要選擇性的使用索引。在變化很少的

8、列上使用索引并不是很好，例如性別列。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_incr

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

10、表掃描，速度會很慢。正確的應該是 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中提到，使用inte

11、l 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：要足夠大，

12、以適應比較大的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管理非事務表。它提供高速

13、存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默認的存儲引擎，除非配置MySQL默認使用另外一個引擎。4.1.1 MyISAM特性 MyISAM Properties1) 不支持事務，宕機會破壞表2) 使用較小的內存和磁盤空間3) 基于表的鎖，并發更新數據會出現嚴重性能問題4) MySQL只緩存Index，數據由OS緩存 Typical MyISAM usages1) 日志系統2) 只讀或者絕大部分是讀操作的應用3) 全表掃描4) 批量導入數據5) 沒有事務的低并發讀/寫4.1.2 MyISAM優化要點1) 聲明列為NOT NULL，

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

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

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

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

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

19、操作。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，所以默認的數據庫設置下面

20、InnoDB性能很差。在只有InnoDB存儲引擎的數據庫服務器上面，可以設置60-80%的內存。更精確一點，在內存容量允許的情況下面設置比InnoDB tablespaces大10%的內存大小。innodb_data_file_path：指定表數據和索引存儲的空間，可以是一個或者多個文件。最后一個數據文件必須是自動擴充的，也只有最后一個文件允許自動擴充。這樣，當空間用完后，自動擴充數據文件就會自動增長（以8MB為單位）以容納額外的數據。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個數據文

21、件放在不同的磁盤上。數據首先放在ibdata1中，當達到900M以后，數據就放在ibdata2中。一旦達到50MB，ibdata2將以8MB為單位自動增長。如果磁盤滿了，需要在另外的磁盤上面增加一個數據文件。innodb_autoextend_increment: 默認是8M, 如果一次insert數據量比較多的話, 可以適當增加.innodb_data_home_dir：放置表空間數據的目錄，默認在mysql的數據目錄，設置到和MySQL安裝文件不同的分區可以提高性能。innodb_log_file_size：該參數決定了recovery speed。太大的話recovery就會比較慢，太小

22、了影響查詢性能，一般取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