MySQL MyISAM/InnoDB高並發最佳化經驗

標籤：http   使用   io   strong   檔案   ar   資料   問題   cti   

最近做的一個應用，功能要求非常簡單，就是 key/value 形式的儲存，簡單的 INSERT/SELECT，沒有任何複雜查詢，唯一的問題是量非常大，如果目前投入使用，初期的單表 insert 頻率約 20Hz（次/秒，我喜歡這個單位，讓我想起國內交流電是 50Hz），但我估計以後會有 500Hz+ 的峰值。目前的工作成果，額定功率 200Hz（CPU 佔用 10 – 20，load avg = 2），最大功率 500Hz（這時 load avg > 20，很明顯，只能暫時挺挺，應該在出現這種負載前提前拆表了）

INSERT DELAYED INTO

從 資料的插入開始說起。如果可以容忍結果幾秒以後再生效的，可以用 INSERT DELAYED INTO，因為在我的這個結構中不需要對同一個 key 頻繁的 INSERT/SELECT，因為 SELECT 我是用 Memcached 擋住了，除非 Memcached 掛了，或者資料實在老到到期了，才會去 SELECT。而且要注意，如果 PHP 不需要關心 MySQL 操作的返回結果，應該使用 unbuffered query，簡單的說，在你提交 query 後，不用等待 MySQL 有返回資訊就繼續執行之後的 PHP 指令，具體用法是用 mysql_unbuffered_query 代替 mysql_query，如果用的 MySQLi 類，應該使用 mysqli->query($sQuery, MYSQLI_USE_RESULT);

如果 SHOW PROCESSLIST，可以看到使用者名稱為 DELAYED 的進程，進程數量等於 INSERT DELAYED 的表的數量，因為表級鎖的存在，每個表一條以上的 DELAYED 進程是沒有意義的

關於這個功能的 my.cnf 配置有三條，我定為如下值

delayed_insert_limit = 1000
delayed_insert_timeout = 300
delayed_queue_size = 5000

串連

有 人說，如果報錯串連數過大，你把 max_connections 調大就 OK，如果只這麼說而不講原因，完全是句廢話，你調成 1M 肯定不會再報 Too many connections（但應該會報記憶體溢出之類的），但如果是這樣 MySQL 又何必給這個參數？

我看到的一個很有用的公式

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

以前只有很模糊的概念，應該設的很大，但又不能太大，具體多大合適，知道這個就明確了。innoDB 的公式比這個複雜點，一併給出

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * ( sort_buffer_size + read_buffer_size + binlog_cache_size )
+ max_connections * 2MB

還有一個看起來很有用的參數 back_log，給我一種串連池的感覺，而且它確實在起作用，我不知道如果設大了會佔用多少記憶體，但估計不會很多。

 

key_buffer_size

很 多文章都告訴你越大越好，要為此分配一半左右的實體記憶體，這麼幹通常不會出問題，但肯定不是最優的，甚至可以說很無理頭——分多少記憶體應該是根據需求決 定，而不是不管什麼機器，都砍掉一半記憶體用作 key_buffer_size ——有的時候可能是不夠，還有的時候可能是浪費。

其實最關 鍵的指標，還是看 SHOW GLOBAL STATUS 時的 Key_blocks_unused，只要還有剩餘，就說明 key_buffer_size 沒用滿。有人說看 Key_reads 跟 Key_read_requests 的比值，至少要達到 1:100。這可以作為一個結果來衡量，但不夠準確，因為在伺服器剛啟動的時候，大多數請求都要建立緩衝，快取命中比高不起來，需要運行穩定（幾小時後） 再觀察。我個人建議還是看 Key_blocks_unused

如果不花很長時間在運行中調試，給出一個簡單的計算方法，把資料庫填滿，達到設計時的最大值，看看這時候索引佔了多大空間，然後把所有表的索引大小加起來，就是 key_buffer_size 可能達到的最大值，當然，還要留些餘地，乘個 2 或 3 之類的。

這是我做測試的時候的 phpMyAdmin ，可以看到 key_buffer_size 被浪費了太多

OPTIMIZE TABLE

優 化一下有好處，但會鎖住表，是否值得做要權衡一下。拿我現在這個表做例子，有 text 欄位，700萬條記錄，1.5G 大小，最佳化時間約兩分鐘，最佳化後效能提升了 50%，同時表的大小變為 1.4G，但隨著表的頻繁改寫，約一天后又恢複到以前的速度，因此在我看來並不值得。

Query Cache

因為每有寫操作 Query Cache 都會被清空，除了極特殊的情況（大量讀，少量寫，但即使這樣也應該是多用 memcached 才對）完全沒有必要使用這個，把 query_cache_size 設為 0 關閉這個功能吧。

InnoDB和MyISAM是在使用MySQL最常用的兩個表類型，各有優缺點，視具體應用而定。基本 的差別為：MyISAM類型不支援交易處理等進階處理，而InnoDB類型支援。MyISAM類型的表強調的是效能，其執行數度比InnoDB類型更快， 但是不提供事務支援，而InnoDB提供事務支援已經外部鍵等進階資料庫功能。

MyISAM:這個是預設類型,它是基於傳統的ISAM類型,ISAM是Indexed Sequential Access Method (有索引的順序存取方法) 的縮寫,它是儲存記錄和檔案的標準方法.與其他儲存引擎比較,MyISAM具有檢查和修複表格的大多數工具. MyISAM表格可以被壓縮,而且它們支援全文檢索搜尋.它們不是事務安全的,而且也不支援外鍵。如果事物復原將造成不完全復原，不具有原子性。如果執行大量 的SELECT，MyISAM是更好的選擇。

InnoDB:這種類型是事務安全的.它與BDB類型具有相同的特性,它們還支援外鍵.InnoDB表格速度很快.具有比BDB還豐富的特性,因此如果需 要一個事務安全的儲存引擎,建議使用它.如果你的資料執行大量的INSERT或UPDATE,出於效能方面的考慮，應該使用InnoDB表,

對於支援事物的InnoDB類型的標，影響速度的主要原因是AUTOCOMMIT預設設定是開啟的，而且程式沒有顯式調用BEGIN 開始事務，導致每插入一條都自動Commit，嚴重影響了速度。可以在執行sql前調用begin，多條sql形成一個事物（即使autocommit打 開也可以），將大大提高效能。

MyIASM是IASM表的新版本，有如下擴充：

1、二進位層次的可移植性。
2、NULL列索引。
3、對變長行比ISAM表有更少的片段。
4、支援大檔案。
5、更好的索引壓縮。
6、更好的鍵碼統計分布。
7、更好和更快的auto_increment處理。

InnoDB 是 MySQL 上第一個提供外鍵約束的引擎，除了提供交易處理外，InnoDB 還支援行鎖，提供和 Oracle 一樣的一致性的不加鎖讀取，能增加並發讀的使用者數量並提高效能，不會增加鎖的數量。

InnoDB 的設計目標是處理大容量資料時最大化效能，它的 CPU 利用率是其他所有基於磁碟的關聯式資料庫引擎中最有效率的。

InnoDB 是一套放在 MySQL 背景完整資料庫系統，InnoDB 有它自己的緩衝池，能緩衝資料和索引，InnoDB 還把資料和索引存放在資料表空間裡面，可能包含好幾個檔案，這和 MyISAM 表完全不同，在 MyISAM 中，表被存放在單獨的檔案中，InnoDB 表的大小隻受限於作業系統檔案的大小，一般為 2GB。

InnoDB所有的表都儲存在同一個資料檔案 ibdata1 中(也可能是多個檔案，或者是獨立的資料表空間檔案),相對來說比較不好備份，免費的方案可以是拷貝資料檔案、備份 binlog，或者用 mysqldump。

MyISAM 是MySQL預設存貯引擎 .

每張MyISAM 表被存放在三個檔案 。frm 檔案存放表格定義。 資料檔案是MYD (MYData) 。 索引檔案是MYI (MYIndex) 引伸。

因為MyISAM相對簡單所以在效率上要優於InnoDB..小型應用使用MyISAM是不錯的選擇。

MyISAM表是儲存成檔案的形式,在跨平台的資料轉移中使用MyISAM儲存會省去不少的麻煩。

以下是一些細節和具體實現的差別：

1、InnoDB不支援FULLTEXT類型的索引。
2、InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。
3、對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。
4、DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方案是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性（例如外鍵）的表不適用。
6、InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%”

綜上所述，任何一種表都不是萬能的，只有恰當的針對業務類型來瘍合適的表類型，才能最大的發揮MySQL的效能優勢。

兩種類型最主要的差別就是 InnoDB 支援交易處理與外鍵和行級鎖.而MyISAM不支援.所以Myisam往往就容易被人認為只適合在小項目中使用。

我作為使用mysql的使用者角度出發，innodb和myisam都是比較喜歡的，但是從我目前營運的資料庫平台要達到需求：99.9%的穩定性，方便的擴充性和高可用性來說的話，myisam絕對是我的首選。

原因如下：

1.首先我目前平台上承載的大部分項目是讀多寫少的項目，而myisam的讀效能是比innodb強不少的。

2.myisam的索引和資料是分開的，並且索引是有壓縮的，記憶體使用量率就對應提高了不少。能載入更多索引，而innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成innodb比myisam體積龐大不校

3.從平台角度來說，經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的範圍不對，導致這個表沒法正常用了，這個時候 myisam 的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的檔案，隨便放到一個資料庫目錄下，然後dump成sql再導回到主庫，並把對應的binlog 補上。如果是innodb，恐怕不可能有這麼快速度，別和我說讓innodb定期用匯出xxx.sql機製備份，因為我平台上最小的一個資料庫執行個體的資料 量基本都是幾十G大校

4.從我接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作innodb其實也是會鎖表的，很多人以為innodb是行級鎖，那個只是 where對它主鍵是有效，非主鍵的都會鎖全表的。

5.還有就是經常有很多應用部門需要我給他們定期某些表的資料，myisam的話很方便，只要發給他們對應那表的frm.MYD,MYI的檔案，讓他們自 己在對應版本的資料庫啟動就行，而innodb就需要匯出xxx.sql了，因為光給別人檔案，受字典資料檔案的影響，對方是無法使用的。

6.如果和myisam比insert寫操作的話，innodb還達不到myisam的寫效能，如果是針對基於索引的update操作，雖然myisam可能會遜色innodb,但是那麼高並發的寫，從庫能否追的上也是一個問題，還不如通過多執行個體分庫分表架構來解決。

7.如果是用Myisam的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些select count(*)操作，非常適合大項目總量約幾億的rows某一類型(如日誌，調查統計)的業務表。

當然innodb也不是絕對不用，用事務的項目如類比炒股項目，我就是用innodb的，活躍使用者20多萬時候，也是很輕鬆應付了，因此我個人也是很喜歡Innodb的，只是

如果從資料庫平台應用出發，我還是會首選myisam.

PS:可能有人會說你myisam無法抗太多寫操作，但是我可以通過架構來彌補，說個我現有用的資料庫平台容量：主從資料總量在幾百T以上，每天十多億 pv的動態網頁面，還有幾個大項目是通過資料介面方式調用未算進pv總數，(其中包括一個大項目因為初期memcached沒部署,導致單台資料庫每天處理 9千萬的查詢)。而我的整體資料庫伺服器平均負載都在0.5-1左右。

MyISAM和InnoDB最佳化：

key_buffer_size – 這對MyISAM表來說非常重要。如果只是使用MyISAM表，可以把它設定為可用記憶體的 30-40%。合理的值取決於索引大小、資料量以及負載 — 記住，MyISAM表會使用作業系統的緩衝來快取資料，因此需要留出部分記憶體給它們，很多情況下資料比索引大多了。儘管如此，需要總是檢查是否所有的 key_buffer 都被利用了 — .MYI 檔案只有 1GB，而 key_buffer 卻設定為 4GB 的情況是非常少的。這麼做太浪費了。如果你很少使用MyISAM表，那麼也保留低於 16-32MB 的 key_buffer_size 以適應給予磁碟的暫存資料表索引所需。

innodb_buffer_pool_size – 這對Innodb表來說非常重要。Innodb相比MyISAM表對緩衝更為敏感。MyISAM可以在預設的 key_buffer_size 設定下啟動並執行可以，然而Innodb在預設的 innodb_buffer_pool_size 設定下卻跟蝸牛似的。由於Innodb把資料和索引都緩衝起來，無需留給作業系統太多的記憶體，因此如果只需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。一些應用於 key_buffer 的規則有 — 如果你的資料量不大，並且不會暴增，那麼無需把 innodb_buffer_pool_size 設定的太大了。

innodb_additional_pool_size – 這個選項對效能影響並不太多，至少在有差不多足夠記憶體可分配的作業系統上是這樣。不過如果你仍然想設定為 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的記憶體有多少。

innodb_log_file_size 在高寫入負載尤其是大資料集的情況下很重要。這個值越大則效能相對越高，但是要注意到可能會增加恢復。我經常設定為 64-512MB，跟據伺服器大小而異。

innodb_log_buffer_size 默 認的設定在中等強度寫入負載以及較短事務的情況下，伺服器效能還可 以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。如果它的值設定太高了，可能會浪費記憶體 — 它每秒都會重新整理一次，因此無需設定超過1秒所需的記憶體空間。通常 8-16MB 就足夠了。越小的系統它的值越小。

innodb_flush_logs_at_trx_commit 是否為Innodb比MyISAM慢1000倍而頭大？看來也許你忘了修改這個參數了。預設值是 1，這意味著每次提交的更新事務（或者每個事務之外的語句）都會重新整理到磁碟中，而這相當耗費資源，尤其是沒有電池備用緩衝時。很多應用程式，尤其是從 MyISAM轉變過來的那些，把它的值設定為 2 就可以了，也就是不把日誌重新整理到磁碟上，而只重新整理到作業系統的緩衝上。日誌仍然會每秒重新整理到磁碟中去，因此通常不會丟失每秒1-2次更新的消耗。如果設定 為 0 就快很多了，不過也相對不安全了 — MySQL伺服器崩潰時就會丟失一些事務。設定為 2 指揮丟失重新整理到作業系統緩衝的那部分事務。

table_cache — 開啟一個表的開銷可能很大。例如MyISAM把MYI檔案頭標誌該表正在使用中。你肯定不希望這種操作太頻繁，所以通常要加大緩衝數量，使得足以最大限度 地緩衝開啟的表。它需要用到作業系統的資源以及記憶體，對當前的硬體設定來說當然不是什麼問題了。如果你有200多個表的話，那麼設定為 1024 也許比較合適（每個線程都需要開啟表），如果串連數比較大那麼就加大它的值。我曾經見過設定為 100,000 的情況。

thread_cache — 線程的建立和銷毀的開銷可能很大，因為每個線程的串連/斷開都需要。我通常至少設定為 16。如果應用程式中有大量的跳躍並發串連並且 Threads_Created 的值也比較大，那麼我就會加大它的值。它的目的是在通常的操作中無需建立新線程。

query_cache — 如果你的應用程式有大量讀，而且沒有應用程式層級的緩衝，那麼這很有用。不要把它設定太大了，因為想要維護它也需要不少開銷，這會導致MySQL變慢。通 常設定為 32-512Mb。設定完之後最好是跟蹤一段時間，查看是否運行良好。在一定的負載壓力下，如果快取命中率太低了，就啟用它。

sort_buffer_size –如果你只有一些簡單的查詢，那麼就無需增加它的值了，儘管你有 64GB 的記憶體。搞不好也許會降低效能。

部分內容來源：https://soulogic.com/archives/347

MySQL MyISAM/InnoDB高並發最佳化經驗