本文主要基于MySQL 5.7版本的數據庫環境，總結my.cnf文件中核心參數的配置使用，讓更多的人對MySQL技術體系有更全面、更專業的深度了解。

一、客戶端核心參數

1、port

端口號，默認3306

2、socket

Socket文件地址，默認以.sock為文件名稱后綴，用于UNIX套接字通信。

二、服務端全局區(SGA)緩沖區參數

1、innodb_buffer_pool

InnoDB緩沖池，位于主內存

緩存被訪問過的表數據、索引文件、插入緩沖、數據字典等

推薦大小設置為物理內存的50%-80%

2、innodb_buffer_pool_instance

將緩沖池進一步劃分為互相隔離的內存區域，更好的支持并發讀寫，減少內存爭用現象

默認值為1

僅當innodb_buffer_pool參數配置大于1024MB有效

通過命令show engine innodb status查看每個內存區域的內存使用情況

3、innodb_old_blocks_time和innodb_old_blocks_pct

InnoDB緩沖池innodb_buffer_pool，內部由LRU鏈表管理

LRU鏈表進一步分為old pages list和young pages list：

old pages list：存放長時間未被訪問的數據頁

young pages list：存放最新、最近被訪問的數據頁

當超過innodb_old_blocks_time參數設置的時間時，就會移動到old pages list，默認是1000ms。

innodb_old_blocks_pct參數決定old pages list在LRU鏈表及innodb_buffer_pool中占比：

存在大表掃描或者執行mysqldump備份操作時，可能會剔除熱數據，增加I/O壓力，因此要適當減少innodb_old_blocks_pct參數值

4、innodb_log_buffer_size和innodb_log_waits

innodb_log_buffer_size：日志緩沖區的大小，取值范圍16MB-64MB

innodb_log_waits：等待日志緩沖刷出的次數

通過命令show global status like "%innodb_log_waits%"查看：

如果發現innodb_log_waits值大于0，并且持續增長，可以考慮增大innodb_log_buffer_size

5、innodb_log_buffer

事務在內存中的緩沖區的大小，即redo log buffer

6、query_cache_size和query_cache_type

query_cache_siz靜態數據的緩存區大小

默認關閉，建議關閉，保證數據庫的TPS

數據倉庫場景可能會選擇開啟

實例啟動前，設置query_cache_size=0和query_cache_type=off才算真正關閉

三、服務端程序緩沖區(PGA)參數

1、sort_buffer_size

用于SQL語句在內存中的臨時排序

2、join_buffer_size

表連接使用，用于BKA

3、read_buffer_size

MyISAM存儲引擎的表順序掃描的緩存

4、read_rnd_buffer_size

隨機讀緩沖區大小

用于mrr特性

5、tmp_table_size和max_heap_table_size

tmp_table_size：SQL語句在排序或者分組時沒有用到索引，會使用臨時表空間

max_heap_table_size：管理heap、memory存儲引擎表

建議tmp_table_size和max_heap_table_siz大小配置一致

三、服務端刷盤參數

內存中的數據需要根據刷新機制決定在某一時刻如何刷新到磁盤，刷新機制涉及redo log buffer、data buffer、binlog cache等內存緩沖區，read thread、write thread、redo log thread、change buffer thread四大I/O線程，及master?thread后臺主線程。

1、innodb_flush_log_at_trx_commit

控制由內存redo log buffer寫入磁盤relo log、和執行刷盤操作的頻率

可選參數值(實踐根據業務場景選擇)：

0:redo log thread每隔1s會執行一次刷盤策略；

此種情況，每次事務提交不會觸發redo log thread將日志緩沖中的數據寫入redo log；

性能最好，但是安全性最低，MySQL進程崩潰將會導致丟失1s的數據；

1:每次事務提交時，都會觸發redo log thread將日志緩沖中的數據寫入redo log，并且執行刷盤操作，以便保證數據確實已經寫入磁盤；

安全性最高，但是性能最差；

2:每次事務提交時，都會觸發redo log thread將日志緩沖中的數據寫入redo log，但是不會同時執行刷盤操作；

另外兩個決定redo log buffer刷到磁盤的條件：

master thread每秒進行刷新；

redo log buffer使用超過一半時觸發刷新；

2、sync_binlog

控制由內存binlog cache刷新到磁盤binglog的頻率

參數值為正整數：

0:每次事務提交后，不主動做fsync之類的磁盤同步指令刷新binlog cache，讓FileSystem自行決定同步時機，或者binlog cache滿才同步到磁盤；

1:每次事務提交后，主動做刷新binlog cache到磁盤；

n:每進行n次事務提交后，主動做fsync之類的磁盤同步指令刷新binlog cache到磁盤；

3、數據庫的雙一模式：

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

4、innodb_max_dirty_pages_pct

控制臟頁的刷新頻率，在buffer pool中dirty page所占的百分比，達到閾值會觸發page cleaner thrad執行臟頁刷新

默認值是75%

建議設置為25%-50%之間，保證數據庫整體的TPS

5、innodb_flush_method

控制InnoDB數據文件、redo log文件的打開刷寫模式；

可選參數值：

O_SYNC:

O_DSYNC:

O_DIRECT:數據文件直接從MySQL InnoDB Buffer寫入到磁盤，不用通過OS buffer；

四、服務端并發量參數

1、innodb_max_thread_concurrency

InnoDB內核最大并發線程數，默認值為0(含義不受限制)

2、innodb_open_files

InnoDB可同時打開的.ibd文件個數

參數值規則>=10，默認值300

建議調整為65535，提高并發數

3、max_connections和innodb_thread_concurrency

max_connections控制MySQL數據庫最大的連接數，默認值151，高并發場景中經常遇到的"too many connections"異常報錯，即為此參數配置過低或者集群需要水平擴容

innodb_thread_concurrency控制InnoDB內部并發數，參數值為正整數(默認值為0)：

0:不受限制；

n:并發數為n，建議設置為服務器邏輯CPU核數的2倍，根據實際情況由小及大逐步調整驗證；

五、服務端事務參數

1、transaction_isolation

事務隔離級別

可選參數值(默認值REPEATABLE-READ)：

READ-UNCOMMITTED：讀未提交

READ-COMMITTED：讀已提交

REPEATABLE-READ：可重復讀

SERIALIZABLE：序列化

六、服務端影響I/O性能的參數

1、innodb_log_file_size

redo log日志的大小，需要合理設置：

如果設置偏小，將會導致redo log切換頻繁，產生無用的I/O消耗，影響數據庫性能；

如果設置偏大，將會導致實例宕機恢復需要較長時間；

2、innodb_log_files_in_group

redo log文件組中的日志文件的數量，默認情況下至少需要2個。

3、innodb_stats_on_metadata

information_schema中的表，某些監控程序讀取innodb_stats_on_metadata表

可通過命令"set global innodb_stats_on_metadata=0"關閉

七、服務端慢查詢參數

1、show_query_log

慢查詢日志開關

可選參數值：

1:開啟狀態，推薦配置；

2、long_query_time

慢查詢的時間閾值，單位為秒；

超過閾值就會記錄到慢查詢日志文件；

3、log_queries_not_using_indexs

運行的SQL沒有使用索引，是否記錄到慢查詢日志文件

可選參數值：

on：記錄，推薦配置，set global set log_queries_not_using_indexs=on；

off：不記錄；

八、服務端二進制文件參數

1、expire_logs_days

binlog文件的過期時間，單位為天

2、binlog_format

二進制日志文件的內容格式

可選參數值：

statement：記錄的是一條SQL語句

row：此模式較為安全，不會出現跨庫復制丟失數據的情況；

一般中間件采集數據解析也是這種模式，記錄行變更前的樣子及變更后的內容

mixed：混合模式

九、服務端其它參數

1、innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown

快速加載實例宕機前內存中的熱數據，減少磁盤I/O壓力

可選參數值(默認0)：

0：關閉狀態

1：啟用狀態

在實例關閉時，將熱數據的元數據信息保存到innodb_buffer_pool_filename參數指定的文件(默認ib_buffer_pool)

在實例啟動時，從innodb_buffer_pool_filenam參數指定的文件(默認ib_buffer_pool)快速加載回內存

2、innodb_data_file_path

指定系統表空間文件的路徑和ibdata1文件的大小

默認大小是10MB

3、interactive_timeout和wait_timeout

interactive_timeout是服務器關閉交互式連接前等待活動的時間，默認值28800s，即8小時

iwait_timeout是服務器關閉非交互式連接前等待活動的時間，默認值28800s，即8小時

兩個參數需要一起調整，并且保持一致，默認值時間過長，建議調整為300s-600s之間

4、server-id

主從環境中，當前MySQL在主從結構中的唯一標識

5、lower_case_table_names

表名稱是否區分大小寫

可選參數值：

0:區分大小寫

1:不區分大小寫，實際存儲使用小寫；

6、innodb_fast_shutdown和innodb_force_recoverty

innodb_fast_shutdown控制表的InnoDB存儲引擎在關閉時的行為

可選參數值：

0：默認值；

需要執行purge all、merge change buffer、flush dirty pages操作；

此方式是最慢的關閉方式，但是restart時最快；

1：不需要執行purge all、merge change buffer等操作，但是需要執行flush dirty pages操作；

2：不完成purge all、merge change buffer等操作，也不刷新臟頁到磁盤；

只是將日志寫入日志文件，因此不會丟數據，但是重新啟動會進行recovery操作；

7、innodb_force_recoverty

控制表的InnoDB存儲引擎在恢復時的行為

可選參數值：

0:默認值，當需要恢復時執行所有操作；

1:忽略檢查到的corrupt頁；

2:阻止主線程的運行，

3:不執行事務回滾操作；

4:不執行插入緩沖的合并操作；

5:不查看撤銷日志，InnoDB存儲引擎會將未提交的事務視為已提交；

6:不執行前滾操作；

7、innodb_status_output和innodb_status_output_locks

數據庫監控信息記錄到error log

建議關閉，防止錯誤日志增長過快，造成磁盤空間使用緊張

8、innodb_io_capacity

InnoDB后臺進程最大的I/O性能指標，影響刷新臟頁和插入緩沖的數量

默認值為200，可根據實際使用的磁盤類型選擇調整

9、auto_increment_increment和auto_increment_offset

auto_increment_increment表示自增長字段每次遞增的量，默認值為1

auto_increment_offset表示自增長字段從哪個值開始，默認值為1

歡迎訂閱個人公眾號，瀏覽更多MySQL技術體系知識。