4. 存儲引擎優化
MySQL支持不同的存儲引擎，主要使用的有MyISAM和InnoDB。
4.1 MyISAM
MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置裡被支持，它是默認的存儲引擎，除非配置MySQL默認使用另外一個引擎。
4.1.1 MyISAM特性
4.1.1.1 MyISAM PropertIEs
1) 不支持事務，宕機會破壞表
2) 使用較小的內存和磁盤空間
3) 基於表的鎖，並發更新數據會出現嚴重性能問題
4) MySQL只緩存Index，數據由OS緩存
4.1.1.2 Typical MyISAM usages
1) 日志系統
2) 只讀或者絕大部分是讀操作的應用
3) 全表掃描
4) 批量導入數據
5) 沒有事務的低並發讀/寫
4.1.2 MyISAM優化要點
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) 避免並發insert，update。
2) 可以使用insert delayed，但是有可能丟失數據。
3) 優化查詢語句。
4) 水平分區。
5) 垂直分區。
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存儲它的表＆索引在一個表空間中，表空間可以包含數個文件（或原始磁盤分區）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何大小，即使在文件尺寸被限制為2GB的操作系統上。
許多需要高性能的大型數據庫站點上使用了 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) 高並發的應用。
3) 自動恢復。
4) 較快速的基於主鍵的操作。
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，所以默認的數據庫設置下面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安裝文件不同的分區可以提高性能。
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： 該參數設定了事務提交時內存中log信息的處理。
1) =1時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢。
2) =2時，在每個事務提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。
3) =0時， 日志緩沖每秒一次地被寫到日志文件，並且對日志文件做到磁盤操作的刷新。任何MySQLd進程的崩潰會刪除崩潰前最後一秒的事務
innodb_file_per_table：可以存儲每個InnoDB表和它的索引在它自己的文件中。
transaction-isolation=READ-COMITTED: 如果應用程序可以運行在READ-COMMITED隔離級別，做此設定會有一定的性能提升。
innodb_flush_method： 設置InnoDB同步IO的方式：
1) Default – 使用fsync（）。
2) O_SYNC 以sync模式打開文件，通常比較慢。
3) O_DIRECT，在Linux上使用Direct IO。可以顯著提高速度，特別是在RAID系統上。避免額外的數據復制和double buffering（MySQL buffering 和OS buffering）。
innodb_thread_concurrency： InnoDB kernel最大的線程數。
1) 最少設置為(num_disks+num_cpus)*2。
2) 可以通過設置成1000來禁止這個限制
5. 緩存
緩存有很多種，為應用程序加上適當的緩存策略會顯著提高應用程序的性能。由於應用緩存是一個比較大的話題，所以這一部分還需要進一步調研。
