優化SQL系統變量分析

這篇文章主要介紹“優化SQL系統變量分析”，在日常操作中，相信很多人在優化SQL系統變量分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”優化SQL系統變量分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

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查詢結果集的最大值。增大這個值會運行查詢返回更大的結果集。

到此，關于“優化SQL系統變量分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！