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

這篇文章給大家介紹 MySQL 性能相關參數有哪些，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

整理 MySQL 常用性能相關參數如下

general_log

記錄所有執行的語句，在需要分析問題打開即可，正常服務時不需要開啟，以免帶來 io 性能影響

query_cache_size

緩存 sql 文本和查詢結果的，如果對應的表沒有變化，下次碰到一樣的 SQL，跳過解析和查詢，直接返回結果。

但是表變化非常頻繁，SQL 也是動態生產的，由于需要不斷更新 cache 內容，這時鎖力度很大，反而照成瓶頸。這時最好關掉這個功能，設置參數為 0

sort_buffer_size

針對單個 session 的參數，

排序時，如果用不到 index，session 就會申請一塊這么大的內存空間進行排序。如果這個參數值過小會把排序結果寫入硬盤中，會影響效率。

如果太大，又可能導致物理內存耗盡，導致 OOM。

join_buffer_size

在 join 無法使用到 index 時候用到的 buffer，和 sort_buffer_size 類似

tmp_table_size

在 group by 和 distinct 時如果 SQL 用不到索引，就會使用系統內部臨時表記錄中間狀態。如果該值不夠大，就使用物理硬盤

Innodb_buffer_pool_size

InnoDB 最重要的緩存，用來緩存 innodb 索引頁面、undo 頁面及其他輔助數據。一般設定物理內存 50%~75%

Innodb_buffer_pool_instances

通過這個參數可以把整塊 buffer pool 分割為多塊 instance 內存空間，每個空間獨立管理自己的內存和鏈表，來提升 MySQL 請求處理的并發能力。

因為 buffer pool 是通過鏈表來管理的，同時為了保護頁面，需要在存取的時候對鏈表加鎖，在多線程情況下，并發讀寫 buffer pool 緩存會有鎖競爭和等待。

官方說超過 1G 的 Innodb_buffer_pool_size 考慮設定 instances 去切分內存

Innodb_log_file_size,innodb_log_files_in_group

兩個參數決定 redo 空間的大小，設置存儲更新 redo 越大，有效降低 buffer pool 臟頁被淘汰的速度，減少了 checkpoint 此書，降低磁盤 I /O

不過設置過大，在數據庫異常宕機時，恢復時間越長

Innodb_old_blocks_pct,innodb_old_blocks_time

innodb_old_blocks_pct：

全局、動態變量，默認值 37，取值范圍為 5~95. 用來確定 LRU 鏈表中 old sublist 所占比例

innodb_old_blocks_time：

全局、動態變量，默認值 1000，取值范圍為 0~2**32-1，單位 ms。

用來控制 old sublist 中 page 的轉移策略，新的 page 頁在進入 LRU 鏈表中時，會先插入到 old sublist 的頭部，然后 page 需要在 old sublist 中停留 innodb_old_blocks_time 這么久后，下一次對該 page 的訪問才會使其移動到 new sublist 的頭部，

該參數的設置可以保護 new sublist，盡可能的防止其 being filled by page that is referenced only for a brief period。

 

默認的緩沖中的頁在第一次被讀取時（也就是命中緩存）會被移動到新頁子表頭部，意味著其會長期待在緩沖池中不會被淘汰。這樣就會存在一個問題，一次表掃描（比如使用 select 查詢）可能會將大量數據放入緩存中，并淘汰相應數量的舊數據，但是可能這些數據只使用一次，后面不再使用；同樣地，因為 read-ahead 也會在下一次訪問該頁時被放入新頁子表頭部。這些情形會將本應會被頻繁使用的頁移動到舊頁子表中。

所以 3 / 8 位置處。在后面的第一次命中（被訪問時）的頁會被移動到列表的頭部。因此，那些讀入緩存但是后面從來不會被訪問的頁也從不會被放入列表的頭部，也就會在后面被從緩沖池淘汰。

MySQL 提供了配置參數,milliseconds）讀取不會被標識為年輕，也就是不會被移動到列表頭部。參數 1000，增大這個參數將會造成更多的頁會更快的從緩沖池中被淘汰。

Innodb_flush_method

Innodb 刷數據和日志到磁盤的方式，這個值默認為空，其實：

Linux 默認 fsync

Windows 默認 async_unbuffered

SSD 和 PCIE 存儲時可以使用 o_direct 提升性能

Innodb_doublewrite

MySQL 默認每個 page size 是 16k，而 OS 通常最小 I / O 單元是 4k，所以如果寫 page 時可能需要調用 4 次 OS I/ O 才能完成。假定在執行兩次時 DB crash 了，這時 page 只寫了一部分，就產生了 partial write(不完整寫)。

MySQL double write 的設定就是為了在發生 partial write 時任然保證已經 commit 的數據不丟失，以及數據文件不損壞。

但如果底層存儲支持原子性可以關閉兩次寫，主要看 OS page size 和 DB page size 的關系。

Innodb_io_capacity

控制后臺不斷將內存 (dirty data) 數據 flush 硬盤的操作，遇到周期性 IO QPS 下降時可以考慮提高參數的設定，以加速 flush 的頻率

參考實驗提高 Innodb_io_capacity 的設置，已提升 QPS

Innodb_thread_concurrency

在并發量大的時，增加這個值，兒科降低 innodb 在并發線程之間切換開銷，以增加系統的并發吞吐量

innodb_flush_log_at_trx_commit

控制 redo log 刷盤機制

innodb_flush_log_at_trx_commit=0

事務提交時，不會處理 log buffer 的內容，也不會處理 log file 在 OS cache 的刷盤操作，由 MySQL 后臺 master 線程每隔 1 秒將 log buffer 刷新到磁盤的 log file 中。

在 MySQL 服務宕掉，服務器正常或宕機時：

由于事務提交不刷新 logbuffer，即使事務提交了，logbuffer 也會全部丟失，但只丟失最近 1 秒的事務

innodb_flush_log_at_trx_commit=1

事務提交時，會將 log buffer 的內容寫入 OS cache 文件中，同時會將 OS cache 刷新到磁盤 log file 中。

在 MySQL 服務宕掉，服務器正常或宕機時：

由于事務提交會刷新到磁盤 log file 中，所以數據都不會丟失

innodb_flush_log_at_trx_commit=2

事務提交時，會將 log buffer 的內容寫到 OS cache 文件中，由 MySQL 后臺 master 線程每隔 1 秒將 OS cache 的 log file 刷新到磁盤。

在 MySQL 服務宕掉，服務器正常：

由于事務已經刷新到 OS cache 中，然而服務器沒宕機，這樣日志還是會被刷新到磁盤中，那么數據就不會丟失

在 MySQL 服務宕掉，服務器宕機：

由于事務只刷新到 OS cache 中，服務器宕機話，日志沒用被刷新到磁盤中，會丟失 1 秒的事務

sync_binlog

控制 binlog 同步到磁盤的方式

sync_binlog=0, 事務提交時將 MySQL Binlog 信息寫入 OS cache Binlog 中，由 OS 自己空間其緩存的刷新。如果是服務器宕機 binlog cache 中所有 binlog 都會丟失

sync_binlog=1, 每個事務提交時，MySQL 都會把 Binlog 刷新到物理磁盤中。這樣安全性最高，性能損耗是最大。特別是在多事務同行提交，會對 I / O 性能帶來很大影響。

但 group commit 可以緩解壓力：

binlog_group_commit_sync_delay=N, 默認是 0, 定時執行，在 commit 后等待 N 微秒后，進行 binlog 刷盤操作

binlog_group_commit_sync_no_delay_count=N，在 commit 后等待達到最大事務等待數量 N，就忽視 binlog_group_commit_sync_delay 的設置，直接開始刷盤, 注意如果 binlog_group_commit_sync_delay 設置為 0，則此選項無效

不過 group commit 的設置，可能會影響 commit 執行執行速度，可參考：
https://www.cnblogs.com/ziroro/p/9600359.html

sync_binlog=N, 表示每 N 次事務提交，MySQL 會做刷盤。如果 DB 服務或者服務器宕機會丟失一些事務

注：開啟 Binlog 后，MySQL 內部會自動將事務當作一個 XA 事務處理，在提交事務過程中，會自動分配一個唯一的 XID，XID 會記錄到 Binlog 和 redo log 中。事務在提交過程會自動份為 Prepare 和 Commit 兩個階段。

Prepare 階段：告訴 InnoDB 做 prepare，InnoDB 更改事務狀態，并將 redo log 刷入磁盤

Commit 階段：先記錄 Binlog，然后告訴 InnoDB commit

binlog_format

binlog_format=STATEMENT

寫入執行的 SQL 語句到 binlog，從庫讀取這些 SQL 并執行

優勢：

技術成熟

減少 binlog 的寫入量

binlog 包含所有修改語句沒便于審計

缺點：

有些函數不能再 slave 上復雜，如 sleep(),last_insert_id(),udf 等會除問題

與基于 row 的復制比，insert…select 需要更多的鎖

隔離級別必須是 repeatable-read, 而這是發生死鎖的元兇之一

binlog_format=MIXED

默認使用 STATEMENT 記錄日志，特定情況下轉換成 ROW 記錄

binlog_format=ROW

MySQL5.7.7 之后的默認值

優點：

復制是最安全的

slave 需要的鎖也最少

缺點：

binlog 會記錄更多的數據

無法在 slave 上看到 master 上獲取的語句，因為都是 event。但可以開啟 binlog_rows_query_log_events 參數，讓 binlog 記錄 events 同時也記錄原始 SQL 語句。

(復制建議使用 row 模式，其它模式有可能出現主從數據不一致)

tx_isolation

MySQL 隔離級別，默認是 repeatable-read

Read Uncommitted

Read Committed

Repeatable Read

Serializable

這四種級別越來越嚴格，但性能越來越差。

推薦使用 Read Committed，同時 binlog_format=ROW，確認 binlog 同步數據主從庫一致性，兼顧安全，滿足絕大多數業務。

slave_parallel_workers

MySQL 5.6 中，設置參數 slave_parallel_workers = 4，即可有 4 個 SQL Thread（coordinator 線程）來進行并行復制，其狀態為：Waiting for an evant from Coordinator。但是其并行只是基于 database 的。如果數據庫實例中存在多個 database，這樣設置對于 Slave 復制的速度可以有比較大的提升。

其核心思想是：不同 database 下的表并發提交時的數據不會相互影響，即 slave 節點可以用對 relay log 中不同的 schema 各分配一個類似 SQL 功能的線程，來重放 relay log 中主庫已經提交的事務，保持數據與主庫一致。

在 MySQL 5.7 中，引入了基于組提交的并行復制（Enhanced Multi-threaded Slaves），

設置 slave_parallel_workers 0 并且 global.slave_parallel_type=‘LOGICAL_CLOCK’，即可支持一個 database 下，slave_parallel_workers 個的 worker 線程并發執行 relay log 中主庫提交的事務。

其核心思想：一個組提交的事務都是可以并行回放(配合 binary log group commit);

slave 機器的 relay log 中  last_committed 相同的事務（sequence_num 不同）可以并發執行。

參數 slave_parallel_type 可以有兩個值：

DATABASE 默認值，基于庫的并行復制方式

LOGICAL_CLOCK：基于組提交的并行復制方式

關于 MySQL 性能相關參數有哪些就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。