1、全域性記憶體緩衝區
1）key_buffer_size
該變數是隻儲存MyISAM索引資訊的全域性記憶體緩衝區。在對應的.MYI檔案中的索引資料從磁碟上被讀取出來然後存入這個緩衝區。想要調整key_buffer_size的大小，只需要簡單統計所有MyISAM表中總索引的大小，然後隨著資料隨時間增長而調整。
當這個索引碼緩衝區中沒有足夠的空間來儲存新的索引資料時，將會用最近最少使用的的方法覆蓋掉舊的頁面。
2）innodb_buffer_pool_size
innodb_buffer_pool_size是用來儲存所有InnoDB資料和索引的全域性記憶體緩衝區。對完全使用InnoDB的資料庫來說，這是個很重要的緩衝區，一定要正確分配，不正確的分配這個緩衝區可能導致額外的磁碟IO開銷並降低查詢效能。
常見的方法是把innodb_buffer_pool_size設定為RAM的80%，但是很多情況下這樣設定不合理，如RAM大小50G，而資料庫總量只有2G。
可以使用SHOW GLOBAL STATUS或者SHOW ENGINE INNODB STATUS命令來監控InnoDB緩衝池的使用情況。

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer%';

3）innodb_additional_mem_pool_size
innodb_additional_mem_pool_size變數為InnoDB特定資料字典資訊定義了記憶體池。對於這個變數，沒有什麼好的方法來確定它的最優值，一般將其設定為10M。
4）query_cache_size
query_cache_size變數是一個用來儲存經常快取過的查詢全域性記憶體緩衝區。使用query_cache_type變數可以總體啟用和禁用查詢快取。啟用時query_cache_size的值可能為0，這表示沒有查詢需要被快取。而MySQL例項可以透過動態的改變query_cache_size的值在某個時間仍然可以支援快取。
2、全域性/會話記憶體緩衝區
1）max_heap_table_size
這個變數定義了MySQL MEMORY儲存引擎表的最大容量。當某個表容量超過最大值時，應用程式會收到下面的資訊。
mysql> set session max_heap_table_size=1024*1024;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test (id int(10),user_type varchar(20),code int(10)) engine=memory;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test select * from t_user_info_tmp_bak;
ERROR 1114 (HY000): The table 'test' is full

這個變數有一個全域性預設值，而且在上例的每個執行緒上也可以指定這個變數的值。MySQL並沒有為所有MEMORY表的總容量做任何限制。這個變數僅用於單個表。
MEMORY儲存引擎表的總大小可以透過SHOW TABLE STATUS命令和INFORMATION_SCHEMA.TABLES表來確定。
2）tmp_table_size
max_heap_table_size和tmp_table_size變數中的最小值定義了內部表的最大容量，內部臨時表用於儲存在記憶體中的查詢執行過程。如果在explain select的結果中的extra列中出現了using temporary，那麼可以判斷在查詢執行過程中用到了內部臨時表。
MySQL使用memory儲存引擎來支援這些內部臨時表，但是內部臨時表的容量超過max_heap_table_size和tmp_table_size中的最小值是，MySQL會在臨時位置建立一個基於MyISAM磁碟的表。
3、會話緩衝區
1）join_buffer_size
join_buffer_size定義了每個執行緒的記憶體緩衝區，當查詢必須連線兩個表的資料集並且不能使用索引時，這個緩衝區會被用到。這個緩衝區是專門為每個執行緒的索引連線操作準備的。可以透過查詢計劃中Extra列的值為Using join buffer來證明使用了這個緩衝區。建議這個緩衝區設定為預設大小。增加這個緩衝區的大小也不會加快連線操作的速度。
2）sort_buffer_size
這個變數定義了每個執行緒用於對結果集排序的每執行緒緩衝區。可以透過查詢計劃中extra列的值為Using file-sort來確定使用了這個緩衝區。不推薦增加這個緩衝區的大小，因為這個緩衝區是完全分配給每個請求的，而且當預設值太大時可能會降低查詢的執行速度。
3）read_buffer_size
當SQL查詢執行連續的表資料掃描時會用到這個緩衝區。只有在大量連續表資料掃描時才推薦增加這個緩衝區的大小。
4）read_rnd_buffer_size
這個緩衝區用來儲存那些作為排序操作的結果被讀取的資料。這個緩衝區和read_buffer_size的不同之處在於，他讀取的連續資料是和資料在磁碟上的儲存方式相關的。只有在執行大型ORDER BY語句時才推薦增加這個緩衝區的大小。
4、有關基礎工具的變數
1）slow_query_log
這個布林型別的變數可以啟用執行緩慢的查詢的日誌功能，日誌將會報告索引執行時間超過long_query_time變數值的查詢。
2）slow_query_log_file
這個變數定義了當慢查詢日誌功能開啟時儲存所有被記錄的查詢檔案的檔名。這個是全域性變數，可以動態改變它的值。
3）general_log
這個變數用來啟用記錄每條查詢執行情況的全面查詢日誌。這個變數只能在每個伺服器例項值上啟用或者禁用。這是個全域性變數，可以動態改變它的值。
4）general_log_file
這個變數定義了記錄了當全面日誌啟用時所有SQL查詢的檔名，這是個全域性變數，可以動態改變它的值。
5）long_query_time
這個變數指定了一個查詢執行時間的限制，當慢查詢日誌功能啟用時，執行時間超過這個限制的查詢都會被記錄在慢查詢日誌中。
6）log_output
這個變數定義了慢查詢日誌和全面查詢日誌的輸出位置，有效的選項有file,table,none。當定義輸出位置為file是，日誌的輸出檔案分別由slow_query_log_file和general_log_file系統變數來定義。如果這個變數為table，日誌輸出將會分別記錄在mysql.slow_log和mysql.general_log表中。這兩個表是在內部以CSV儲存引擎定義的，所以不支援任何索引。這是個全域性變數，可被動態定義。
5、其他最佳化變數
1）optimizer_switch
這個變數定義了一系列MySQL查詢最佳化器特性的高階開關，可以用來關閉（預設是啟用狀態）三種不同的索引合併條件以及引擎下推條件。
2）default_storage_engine
當未指定ENGINE值時，這個變數用來為create table命令指定儲存引擎。
3）max_allowed_packet
可以用max_allowed_packet變數來定義SQL查詢結果集的最大值。增大這個值會執行查詢返回更大的結果集。

最佳化ySQL系統變數詳細介紹

相關文章