共計 4323 個字符，預(yù)計需要花費 11 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 參數(shù)調(diào)整方法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL 參數(shù)調(diào)整方法”吧！

1  MySQL 參數(shù)調(diào)整 1.1  tx_isolation 事務(wù)參數(shù) 1.1.1  事務(wù)的特征

事務(wù)具有四個特性：原子性（Atomiocity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這四個特性簡稱 ACID 特性。

原子性：事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事務(wù)中包括的所有操作要么都做，要么都不做。

一致性：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性的狀態(tài)變到另外一個一致性狀態(tài)。

隔離性：一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間互相不干擾。

持久性：一個事務(wù)一旦成功提交，對數(shù)據(jù)庫中數(shù)據(jù)的修改就是持久性的。接下來其他的其他。

1.1.2  四種事務(wù)隔離級別

READ UNCOMMITED：SELECT 的時候允許臟讀，即 SELECT 會讀取其他事務(wù)修改而還沒有提交的數(shù)據(jù)。

READ COMMITED：SELECT 的時候不能重復(fù)讀，即同一個事務(wù)中兩次執(zhí)行同樣的查詢語句，若在第一次與第二次查詢之間時間段，其他事務(wù)又剛好修改了其查詢的數(shù)據(jù)且提交了，則兩次讀到的數(shù)據(jù)不一致。

REPEATABLE READ：SELECT 的時候可以重復(fù)讀，即同一個事務(wù)中兩次執(zhí)行同樣的查詢語句，得到的數(shù)據(jù)始終都是一致的。

SERIALIZABLE：與可重復(fù)讀的唯一區(qū)別是，默認把普通的 SELECT 語句改成 SELECT ….
LOCK IN SHARE MODE。即為查詢語句涉及到的數(shù)據(jù)加上共享瑣，阻塞其他事務(wù)修改真實數(shù)據(jù)。

1.1.3  隔離級別選擇

綜合考慮系統(tǒng)的應(yīng)用和性能，建議使用默認的 REPEATABLE READ 級別，或者 READ COMMITED 級別。

1.2  innodb_buffer_pool_size

這是 InnoDB 最重要的設(shè)置，對 InnoDB 性能有決定性的影響。默認的設(shè)置只有 8M，所以默認的數(shù)據(jù)庫設(shè)置下面 InnoDB 性能很差。在只有 InnoDB 存儲引擎的數(shù)據(jù)庫服務(wù)器上面，可以設(shè)置 60-80% 的內(nèi)存。更精確一點，在內(nèi)存容量允許的情況下面設(shè)置比 InnoDB
tablespaces 大 10% 的內(nèi)存大小。它用來設(shè)置用于緩存 InnoDB 索引及數(shù)據(jù)塊的內(nèi)存區(qū)域大小，類似于 MyISAM 存儲引擎的 key_buffer_size 參數(shù)，當(dāng)然，可能更像是 Oracle 的 db_cache_size。簡單來說，當(dāng)我們操作一個 InnoDB 表的時候，返回的所有數(shù)據(jù)或者去數(shù)據(jù)過程中用到的任何一個索引塊，都會在這個內(nèi)存區(qū)域中走一遭。

和 key_buffer_size 對于 MyISAM 引擎一樣，innodb_buffer_pool_size 設(shè)置了 InnoDB 存儲引擎需求最大的一塊內(nèi)存區(qū)域的大小，直接關(guān)系到 InnoDB 存儲引擎的性能，所以如果我們有足夠的內(nèi)存，盡可將該參數(shù)設(shè)置到足夠打，將盡可能多的 InnoDB 的索引及數(shù)據(jù)都放入到該緩存區(qū)域中，直至全部。

我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中率，并根據(jù)命中率來調(diào)整 innodb_buffer_pool_size 參數(shù)大小進行優(yōu)化。

1.3  innodb_flush_log_at_trx_commit

該參數(shù)設(shè)定了事務(wù)提交時內(nèi)存中 log 信息的刷新頻率，有 3 個值, 默認設(shè)置的是 1，也就是同步刷新：

= 0 時，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新。任何 mysqld 進程的崩潰會刪除崩潰前最后一秒的事務(wù)；

= 1 時，在每個事務(wù)提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢；

= 2 時，在每個事務(wù)提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務(wù)，不然不會丟失事務(wù)。

1.4  innodb_log_buffer_size

INNODB 日志緩存的大小，磁盤速度是很慢的，直接將 log 寫道磁盤會影響 InnoDB 的性能，該參數(shù)設(shè)定了 log buffer 的大小，一般 4M。如果有大的 blob 操作，可以適當(dāng)增大。

1.5  innodb_log_file_size

INNODB 日志文件的大小，該參數(shù)決定了 recovery speed。太大的話 recovery 就會比較慢，太小了影響查詢性能，一般取 1GB 可以兼顧性能和 recovery 的速度。

1.6  innodb_data_file_path

該參數(shù)指定表數(shù)據(jù)和索引存儲的空間，可以是一個或者
多個文件。最后一個數(shù)據(jù)文件必須是自動擴充的，也只有最后一個文件允許自動擴充。這樣，當(dāng)空間用完后，自動擴充數(shù)據(jù)文件就會自動增長（以 8MB 為單位）以 容納額外的數(shù)據(jù)。例如：innodb_data_file_path=/disk1
/ibdata1:900M;/disk2/ibdata2:50M:autoextend 兩個數(shù)據(jù)文件放在不同的磁盤上（）。數(shù)據(jù)首先放在 ibdata1 中，當(dāng)達到 900M 以后，數(shù)據(jù)就放在 ibdata2 中。一旦達到 50MB，ibdata2 將以 8MB 為單位自動增長。如果磁盤滿了，需要在另外的磁盤上面 增加一個數(shù)據(jù)文件。

1.7  innodb_data_home_dir

該參數(shù)設(shè)置了放置表空間數(shù)據(jù)的目錄，默認在 mysql 的數(shù)據(jù)目錄，由 MySQL 參數(shù) datadir 指定。設(shè)置到和 MySQL 安裝文件不同的分區(qū)可以提高性能。可以與 innodb_data_file_path 配合使用，用 innodb_data_home_dir 來指定共同位置，然后在通過 inndo_data_file_path 來指定文件名即可。

1.8  innodb_file_io_threads

此參數(shù)指定 InnoDB 表可用的文件 I/O 線程數(shù)，建議在非 Windows 平臺中這個參數(shù)設(shè)置為 4。

1.9  innodb_flush_logs_at_trx_commit

該參數(shù)設(shè)定了事務(wù)提交時內(nèi)存中 log 信息的處理。

1) = 1 時，在每個事務(wù)提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢。默認為此值。

2) = 2 時，在每個事務(wù)提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務(wù)，不然不會丟失事務(wù)。

3) = 0 時，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新。任何 mysqld 進程的崩潰會刪除崩潰前最后一秒的事務(wù)。

1.10  innodb_flush_method

影響了服務(wù)器 flush 數(shù)據(jù)或日志文件的方法。設(shè)置 InnoDB 同步 IO 的方式

1)  Default
使用 fsync（）。

2)  O_SYNC
以 sync 模式打開文件，通常比較慢。

3)  O_DIRECT，在 Linux 上使用 Direct
IO。跳過了操作系統(tǒng)的文件系統(tǒng) Disk Cache，讓 MySQL 直接讀寫磁盤，可以顯著提高速度，特別是在 RAID 系統(tǒng)上。避免額外的數(shù)據(jù)復(fù)制和 double buffering（mysql buffering 和 OS buffering）。避免雙緩沖 (double buffering) 和降低 swap 的壓力。大多數(shù)情況下可以提高性能. 但是注意如果 RAID cache 不夠的話, 寫 IO 的操作會有麻煩。

1.11  tmp_table_size

tmp_table_size，它規(guī)定了內(nèi)存臨時表的最大值，每個線程都要分配。（實際起限制作用的是 tmp_table_size 和 max_heap_table_size 的最小值。）如果內(nèi)存臨時表超出了限制，MySQL 就會自動地把它轉(zhuǎn)化為基于磁盤的 MyISAM 表，此參數(shù)不限制 create tables 創(chuàng)建的內(nèi)存表，存儲在指定的 tmpdir 目錄下，默認:

mysql show variables like tmpdir

1.12  max_heap_table_size

這個變量定義了用戶可以創(chuàng)建的內(nèi)存表 (memory table) 的大小。這個值用來計算內(nèi)存表的最大行數(shù)值。如果內(nèi)存內(nèi)的臨時表超過該值，MySQL 自動將它轉(zhuǎn)換為硬盤上的 MyISAM 表。這個變量支持動態(tài)改變，即 set @max_heap_table_size=#，但是對于已經(jīng)存在的內(nèi)存表就沒有什么用了，除非這個表被重新創(chuàng)建 (create table) 或者修改 (alter table) 或者 truncate table。服務(wù)重啟也會設(shè)置已經(jīng)存在的內(nèi)存表為全局 max_heap_table_size 的值。這個變量和 tmp_table_size 一起限制了內(nèi)部內(nèi)存表的大小。

1.13  long_query_time

long_query_time，對 SQL 執(zhí)行設(shè)定一個時間限制，單位為秒，超出這個時間，測返回錯誤。該參數(shù)可動態(tài)設(shè)置：set
global long_query_time=1800;

1.14  join_buffer_size

如果兩個表關(guān)聯(lián)查詢，但關(guān)聯(lián)的字段又沒有索引時，使用此參數(shù)分配的內(nèi)存空間。

1.15  key_buffer_size

MyISAM 表的索引塊分配了緩沖區(qū)，由所有線程共享。key_buffer_size 是索引塊緩沖區(qū)的大小。鍵值緩沖區(qū)即為鍵值緩存. 用 key_buffer_size 結(jié)合 Key_blocks_unused 狀態(tài)變量和緩沖區(qū)塊大小，可以確定使用的鍵值緩沖區(qū)的比例。從 key_cache_block_size 服務(wù)器變量可以獲得緩沖區(qū)塊大小。使用的緩沖區(qū)的比例為：

1 – ((Key_blocks_unused * key_cache_block_size) /
key_buffer_size)

注意：MyISAM 的數(shù)據(jù)文件讀取依賴于操作系統(tǒng)自身的 IO 緩存，如果有 MyISAM 表，要預(yù)留更多的內(nèi)存給操作系統(tǒng)。

1.16  wait_timeout

wait_timeout，設(shè)置非互交連接會話的空閑超時時間，單位為 s。

1.17  interactive_timeout

interactive_timeout，設(shè)置互交連接會話的空閑超時時間，單位為 s。

感謝各位的閱讀，以上就是“MySQL 參數(shù)調(diào)整方法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對 MySQL 參數(shù)調(diào)整方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！