MySQL新手入門教程:mysql最佳化入門

MySQL是目前常用的RDBMS(RelationalDatabaseManagementSystem/關聯式資料庫管理系統)，還有其他如PostgreSQL,Oracle,DB2等關聯式資料庫管理系統。而資料庫效能的重要性無需強調，在這裡簡單說一下，安裝MySQL之後的最佳化相關的話題。

計算MySQL使用記憶體

首先確認一下，計算MySQL進程佔用記憶體的方法。

MySQL佔用記憶體 = 全域緩衝  + ( 線程緩衝 x 最大串連數 )
全域緩衝的佔用記憶體，用以下方法計算。

max_heap_table_size參數不一定分配記憶體，在這裡為了安全也計算到全域緩衝。

全域緩衝 ＝ key_buffer_size
                ＋ innodb_buffer_pool_size
                ＋ innodb_additional_mem_pool_size
                ＋ innodb_log_buffer_size
                ＋ max_heap_table_size
                ＋ query_cache_size
線程緩衝的佔用記憶體，用以下方法計算。

在通常查詢中myisam_sort_buffer_size使用的可能性很小，因此沒有計算到線程緩衝。並且在這裡把max_allowed_packet計算線上程緩衝裡，如果接傳送的資料量不大的話，可使用net_buffer_length進行計算。

線程緩衝 ＝ sort_buffer_size
                ＋ read_rnd_buffer_size
                ＋ join_buffer_size
                ＋ read_buffer_size
                ＋ max_allowed_packet
                ＋ thread_stack
MySQL緩衝

如何使用緩衝對MySQL的效能表現至關重要，MySQL有以下2種緩衝。

全域緩衝(Global Cache)
線程緩衝(Thread Cache)
全域緩衝

innodb_buffer_pool_size

緩衝InnoDB的索引及資料
使用InnoDB時至關重要的參數
innodb_additional_mem_pool_size

InnoDB儲存的資料目錄資訊及內部資料結構
不足時往MySQL錯誤記錄檔檔案輸出警告(Warning)
使用預設值，查看MySQL錯誤記錄檔檔案不足時再增加
innodb_log_buffer_size

InnoDB交易記錄使用的緩衝區
事務結束或者一定間隔將緩衝區的日誌寫到檔案(同步到磁碟)
盡量給其他參數多配置記憶體
key_buffer_size

緩衝MyISAM的索引
query_cache_size

緩衝查詢(SELECT)的結果
對MySQL效能有直接影響
query_cache_type參數可改變MySQL緩衝行為
線程緩衝

sort_buffer_size

ORDER BY,GROUP BY時使用地區
根據程式的使用方式進行配置
read_rnd_buffer_size

讀取排序後資料時使用
提高ORDER BY效能
join_buffer_size

進行表結合時，如沒有使用索引的話使用該地區
表結合推薦使用索引，所以該參數無需配置的過大
read_buffer_size

讀取全表時的使用地區
不使用索引的查詢是，不應該使用的因此該參數無需配置的過大
myisam_sort_buffer_size

MyISAM的DDL(DataDefinitionLanguage)的索引排序時使用的地區
通常查詢不會使用該地區，因此預設就可以
max_allowed_packet

資料包發送緩衝區是儲存接傳送資料包的記憶體地區
被net_buffer_length參數初期化，根據需要擴張到max_allowed_packe指定的大小
記憶體以外的參數

max_connections

可串連MySQL資料庫的最大串連數
預設是151
innodb_lof_file_size

儲存InnoDB更新日誌到磁碟
innodb_log_file已滿時，innodb_buffer_pool更新日誌寫入磁碟
調整innodb_buffer_pool_size時，innodb_log_file_size也需調整
調整的越大Crash Recovery的時間也會跟著變長
table_open_cache

儲存使用表(Table)的檔案指標
至少需要「同時串連數 x Table數」
MyISAM是一個表(Table)需要2個檔案指標
注意OS限制 ※cat /proc/sys/fs/file-max
thread_cache_size

通過緩衝線程(Thread)，降低串連時的負荷
根據實際的負荷進行配置