MySQL系統變量優化的方法是什么

138次閱讀

共計 8130 個字符，預計需要花費 21 分鐘才能閱讀完成。

本篇內容介紹了“MySQL 系統變量優化的方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

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%
+—————————————+————————————————–+
| Variable_name | Value |
+—————————————+————————————————–+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 180330 16:27:30 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 51679 |
| Innodb_buffer_pool_bytes_data | 846708736 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 116888 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 4641 |
| Innodb_buffer_pool_pages_total | 57344 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 91375799 |
| Innodb_buffer_pool_reads | 380 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 41378543 |
+—————————————+————————————————–+
mysql SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-04-10 14:29:36 0x7f6777c37700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 327 srv_active, 0 srv_shutdown, 942463 srv_idle
srv_master_thread log flush and writes: 942790
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 2095
OS WAIT ARRAY INFO: signal count 2039
RW-shared spins 0, rounds 469, OS waits 237
RW-excl spins 0, rounds 9654, OS waits 322
RW-sx spins 148, rounds 4422, OS waits 145
Spin rounds per wait: 469.00 RW-shared, 9654.00 RW-excl, 29.88 RW-sx
————
TRANSACTIONS
————
Trx id counter 10166
Purge done for trx s n:o 10166 undo n:o 0 state: running but idle
History list length 56
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421558371456848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o s:, sync i/o s:
Pending flushes (fsync) log: 0; buffer pool: 0
894 OS file reads, 125124 OS file writes, 6256 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 232523, node heap has 0 buffer(s)
Hash table size 232523, node heap has 0 buffer(s)
Hash table size 232523, node heap has 0 buffer(s)
Hash table size 232523, node heap has 3093 buffer(s)
Hash table size 232523, node heap has 1 buffer(s)
Hash table size 232523, node heap has 1545 buffer(s)
Hash table size 232523, node heap has 1 buffer(s)
Hash table size 232523, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
—
LOG
—
Log sequence number 1258938797
Log flushed up to 1258938797
Pages flushed up to 1258938797
Last checkpoint at 1258938788
0 pending log flushes, 0 pending chkp writes
1286 log i/o s done, 0.00 log i/o s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total large memory allocated 962002944
Dictionary memory allocated 320881
Buffer pool size 57344
Free buffers 1024
Database pages 51679
Old database pages 19056
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 335, not young 124
0.00 youngs/s, 0.00 non-youngs/s
Pages read 379, created 60479, written 118447
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 51679, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
————–
ROW OPERATIONS
————–
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10779, Main thread ID=140081746663168, state: sleeping
Number of rows inserted 10501644, updated 2100327, deleted 0, read 21003388
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
—————————-
END OF INNODB MONITOR OUTPUT
============================
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 查詢結果集的最大值。增大這個值會運行查詢返回更大的結果集。