一、記憶體相關參數優化

1. Buffffer Pool參數優化

1.1 緩沖池記憶體大小配置

一個大的日志緩沖區允許大量的事務在送出之前不寫日志到磁盤。是以，如果你有很多事務的更新，插入或删除操作，通過設定這個參數會大量的減少磁盤I/O的次數數。

建議: 在專用資料庫伺服器上，可以将緩沖池大小設定為伺服器實體記憶體的60% - 80%。

檢視緩沖池大小

mysql> show variables like '%innodb_buffer_pool_size%'; 
+-------------------------+-----------+ 
| Variable_name | Value | 
+-------------------------+-----------+ 
| innodb_buffer_pool_size | 134217728 | 
+-------------------------+-----------+ 
  
mysql> select 134217728 / 1024 / 1024; 
+-------------------------+ 
| 134217728 / 1024 / 1024 | 
+-------------------------+ 
| 128.00000000 | 
+-------------------------+

線上調整InnoDB緩沖池大小

innodb_buffer_pool_size可以動态設定，允許在不重新啟動伺服器的情況下調整緩沖池的大小。

mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 512 
Query OK, 0 rows affected (0.10 sec) 

mysql> show variables like '%innodb_buffer_pool_size%'; 
+-------------------------+-----------+ 
| Variable_name 					| Value 		| 
+-------------------------+-----------+ 
| innodb_buffer_pool_size | 268435456 | 
+-------------------------+-----------+

監控線上調整緩沖池的進度

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'; 
+----------------------------------+----------------------------------------------------------------------+ 
| Variable_name 									 | Value 																																| 
+----------------------------------+----------------------------------------------------------------------+ 
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. | 
+----------------------------------+----------------------------------------------------------------------+

1.2 配置多個buffffer pool執行個體

當buffffer pool的大小是GB級别時，将一個buffffer pool分割成幾個獨立的執行個體能降低多個線程同時讀寫緩存頁的競争性而提高并發性。

通過innodb_buffffer_pool_instances參數可以調整執行個體個數。如果有多個執行個體，則緩存的資料頁會随機放置到任意的執行個體中，且每個執行個體都有獨立的buffffer pool所有的特性。

buffffer pool 可以存放多個 instance，每個instance由多個chunk組成。instance的數量範圍和chunk的總數量範圍分别為1-64，1-1000。

Innodb_buffer_pool_instances 的預設值是1，最大可以調整成64

mysql> show variables like 'innodb_buffer_pool_instances'; 
+------------------------------+-------+ 
| Variable_name 							 | Value | 
+------------------------------+-------+ 
| innodb_buffer_pool_instances | 1 		 | 
+------------------------------+-------+

1.3 chunk(塊)大小配置

增大或減小緩沖池大小時，将以chunk的形式執行操作.chunk 大小由innodb_buffer_pool_chunk_size 決定引入chunk 是為了友善線上修改緩沖池大小，修改時以chunk 為機關拷貝 buffffer pool。

mysql> show variables like 'innodb_buffer_pool_chunk_size'; 
+-------------------------------+-----------+ 
| Variable_name 								| Value 		| 
+-------------------------------+-----------+ 
| innodb_buffer_pool_chunk_size | 134217728 | 
+-------------------------------+-----------+

緩沖池大小 innodb_buffer_pool_size 必須始終等于或者是

chunk_size * instances 的倍數(不等于則MySQL會自動調整)。

假設
	innodb_buffer_pool_chunk_size=128MB 
  innodb_buffer_pool_instances=16 
那麼
	innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances=2GB 
  
如果我們設定innodb_buffer_pool_size=9GB，則會被自動調整為10GB

1.4 InnoDB 緩存性能評估

目前配置的innodb_buffffer_pool_size是否合适，可以通過分析InnoDB緩沖池的緩存命中率來驗證。

以下公式計算InnoDB buffffer pool 命中率:

命中率 = innodb_buffer_pool_read_requests / 
  (innodb_buffer_pool_read_requests+innodb_buffer_pool_reads)* 100 

參數1: innodb_buffer_pool_reads：表示InnoDB緩沖池無法滿足的請求數。需要從磁盤中讀取。 
參數2: innodb_buffer_pool_read_requests：表示從記憶體中讀取頁的請求數。

mysql> show status like 'innodb_buffer_pool_read%'; 
+---------------------------------------+-------+ 
| Variable_name 												| Value | 
+---------------------------------------+-------+ 
| 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 			| 12701 | 
| Innodb_buffer_pool_reads 							| 455 	| 
+---------------------------------------+-------+ 

  -- 此值低于90%，則可以考慮增加innodb_buffer_pool_size。 
mysql> select 12701 / (455 + 12701) * 100 ; 
+-----------------------------+ 
| 12701 / (455 + 12701) * 100 | 
+-----------------------------+ 
| 96.5415 										| 
+-----------------------------+

1.5 Page管理相關參數

檢視Page頁的大小(預設16KB), innodb_page_size 隻能在初始化MySQL執行個體之前配置，不能在之後修改。如果沒有指定值，則使用預設頁面大小初始化執行個體。

mysql> show variables like '%innodb_page_size%'; 
+------------------+-------+ 
| Variable_name 	 | Value | 
+------------------+-------+ 
| innodb_page_size | 16384 | 
+------------------+-------+

Page頁管理狀态相關參數

mysql> show global status like '%innodb_buffer_pool_pages%'; 
+----------------------------------+-------+ 
| Variable_name 									 | Value | 
+----------------------------------+-------+ 
| Innodb_buffer_pool_pages_data 	 | 515 	 | 
| Innodb_buffer_pool_pages_dirty 	 | 0 		 | 
| Innodb_buffer_pool_pages_flushed | 334 	 | 
| Innodb_buffer_pool_pages_free 	 | 15868 | 
| Innodb_buffer_pool_pages_misc 	 | 0 		 | 
| Innodb_buffer_pool_pages_total 	 | 16383 | 
+----------------------------------+-------+

pages_data: InnoDB緩沖池中包含資料的頁數。該數字包括髒頁面和幹淨頁面。
pages_dirty: 顯示在記憶體中修改但尚未寫入資料檔案的InnoDB緩沖池資料頁的數量（髒頁重新整理）。
pages_flushed: 表示從InnoDB緩沖池中重新整理髒頁的請求數。
pages_free: 顯示InnoDB緩沖池中的空閑頁面。
pages_misc: 緩存池中目前已經被用作管理用途或hash index而不能用作為普通資料頁的數目。
pages_total: 緩存池的頁總數目。機關是page。

優化建議

innodb_page_size的官方描述:

MySQL 5.7增加了對32KB和64KB頁面大小的支援。預設的16KB或更大的頁面大小适用于各種工作負載，特别是涉及表掃描的查詢和涉及批量更新的DML操作。對于涉及許多小寫操作的OLTP工作負載，較小的頁面大小可能更有效。

Page大小對于行存儲的影響

對于4KB、8KB、16KB和32KB的頁大小，最大行大小(不包括存儲在頁外的任何可變長度的列)略小于頁大小的一半。

Page大小對于索引的影響

如果在建立MySQL執行個體時通過指定innodb_page_size選項将InnoDB頁面大小減少到8KB或4KB，索引鍵的最大長度将按比例降低，這是基于16KB頁面大小的3072位元組限制。也就是說，當頁面大小為8KB時，最大索引鍵長度為1536位元組，而當頁面大小為4KB時，最大索引鍵長度為768位元組。

1.6 Change Buffffer相關參數優化

change buffffering是MySQL5.5加入的新特性，change buffffering是insert buffffer的加強，insert buffffer隻針對insert有效，change buffffering對insert、delete、update(delete+insert)、purge都有效。

配置change buffffer使用模式

innodb_change_buffffering 配置參數說明

mysql> show variables like '%innodb_change_buffering%'; 
+-------------------------+-------+ 
| Variable_name 				  | Value | 
+-------------------------+-------+ 
| innodb_change_buffering | all 	| 
+-------------------------+-------+

配置change buffffer 大小

ChangeBuffffer占用BufffferPool空間，預設占25%，最大允許占50%，可以根據讀寫業務量來進行調整。參數 innodb_change_buffer_max_size ;

mysql> show variables like 'innodb_change_buffer_max_size'; 
+-------------------------------+-------+ 
| Variable_name 								| Value | 
+-------------------------------+-------+ 
| innodb_change_buffer_max_size | 25 		| 
+-------------------------------+-------+ 
1 row in set (0.00 sec)

檢視change buffffer的工作狀态

-- 檢視change buffer的工作狀态 
------------------------------------- 
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

size: 表示已經合并到輔助索引頁的數量；
free list len: 表示空閑清單長度；
seg size：表示目前Change Buffffer的大小，2*16KB；
merges：表示合并的次數；
merged operations：表示每個具體操作合并的次數；

insert：表示插入操作；
delete mark：表示删除标記操作；
delete：表示實體删除操作；

二、日志相關參數優化

1.日志緩沖區相關參數配置

日志緩沖區的大小。一般預設值16MB是夠用的，但如果事務之中含有blog/text等大字段，這個緩沖區會被很快填滿會引起額外的IO負載。配置更大的日志緩沖區,可以有效的提高MySQL的效率。

innodb_log_buffffer_size 緩沖區大小

mysql> show variables like 'innodb_log_buffer_size'; 
+------------------------+----------+ 
| Variable_name 				 | Value 		| 
+------------------------+----------+ 
| innodb_log_buffer_size | 16777216 | 
+------------------------+----------+

innodb_log_files_in_group 日志組檔案個數

日志組根據需要來建立。而日志組的成員則需要至少2個，實作循環寫入并作為備援政策。

mysql> show variables like 'innodb_log_files_in_group'; 
+---------------------------+-------+ 
| Variable_name 						| Value | 
+---------------------------+-------+ 
| innodb_log_files_in_group | 2 		| 
+---------------------------+-------+

innodb_log_file_size 日志檔案大小

參數innodb_log_file_size用于設定MySQL日志組中每個日志檔案的大小(預設48M)。此參數是一個全局的靜态參數，不能動态修改。

參數innodb_log_fifile_size的最大值，二進制日志檔案大小（innodb_log_fifile_size * innodb_log_fifiles_in_group）不能超過512GB.是以單個日志檔案的大小不能超過256G。

mysql> show variables like 'innodb_log_file_size'; 
+----------------------+----------+ 
| Variable_name 			 | Value 		| 
+----------------------+----------+ 
| innodb_log_file_size | 50331648 | 
+----------------------+----------+

2.日志檔案參數優化

首先我們先來看一下日志檔案大小設定對性能的影響

設定過小

參數 innodb_log_file_size 設定太小，就會導緻MySQL的日志檔案( redo log）頻繁切換，頻繁的觸發資料庫的檢查點（Checkpoint），導緻重新整理髒頁到磁盤的次數增加。進而影響IO性能。
處理大事務時，将所有的日志檔案寫滿了，事務内容還沒有寫完，這樣就會導緻日志不能切換。

設定過大

參數 innodb_log_file_size 如果設定太大，雖然可以提升IO性能，但是當MySQL由于意外當機時，二進制日志很大，那麼恢複的時間必然很長。而且這個恢複時間往往不可控，受多方面因素影響。

優化建議:

如何設定合适的日志檔案大小 ?

根據實際生産場景的優化經驗,一般是計算一段時間内生成的事務日志（redo log）的大小，而MySQL的日志檔案的大小最少應該承載一個小時的業務日志量(官網文檔中有說明)。

1. 想要估計一下InnoDB redo log的大小，需要抓取一段時間内Log Sequence Number的資料,來計算日志一小時内的日志大小.

Log sequence number

自系統修改開始，就不斷的修改頁面，也就不斷的生成redo日志。為了記錄一共生成了多少日志，于是mysql設計了全局變量log sequence number，簡稱lsn，但不是從0開始，是從8704位元組開始。

-- pager分頁工具, 隻擷取 sequence的資訊 
mysql> pager grep sequence;
PAGER set to 'grep sequence' 

  -- 查詢狀态,并倒計時一分鐘 
mysql> show engine innodb status\G select sleep(60); 
Log sequence number 5399154 
1 row in set (0.00 sec) 

1 row in set (1 min 0.00 sec)

-- 一分時間内所生成的資料量 5406150 
mysql> show engine innodb status\G select sleep(60); 
Log sequence number 5406150 

-- 關閉pager mysql> nopager; 
PAGER set to stdout

2. 有了一分鐘的日志量,據此推算一小時内的日志量

mysql> select (5406150 - 5399154) / 1024 as kb_per_min; 
+------------+ 
| kb_per_min | 
+------------+ 
| 6.8320 | 
+------------+ 
  
mysql> select (5406150 - 5399154) / 1024 * 60 as kb_per_min; 
+------------+ 
| kb_per_min | 
+------------+ 
| 409.9219 | 
+------------+

三、IO線程相關參數優化

資料庫屬于 IO 密集型的應用程式，其主要職責就是資料的管理及存儲工作。從記憶體中讀取一個資料庫資料的時間是微秒級别，而從一塊普通硬碟上讀取一個IO是在毫秒級别。要優化資料庫，IO操作是必須要優化的，盡可能将磁盤IO轉化為記憶體IO。

1) 參數: query_cache_size&have_query_cache

MySQL查詢緩存儲存查詢傳回的完整結果。當查詢命中該緩存，會立刻傳回結果，跳過了解析，優化和執行階段。

查詢緩存會跟蹤查詢中涉及的每個表，如果這寫表發生變化，那麼和這個表相關的所有緩存都将失效。

1. 檢視查詢緩存是否開啟

-- 查詢是否支援查詢緩存 
mysql> show variables like 'have_query_cache'; 
+------------------+-------+ 
| Variable_name 	 | Value | 
+------------------+-------+ 
| have_query_cache | YES 	 | 
+------------------+-------+ 

-- 查詢是否開啟查詢緩存 預設關閉 
mysql> show variables like '%query_cache_type%'; 
+------------------+-------+ 
| Variable_name 	 | Value | 
+------------------+-------+ 
| query_cache_type | OFF 	 | 
+------------------+-------+

2. 開啟緩存,在my.ini中添加下面一行參數

query_cache_size=128M 
query_cache_type=1 

query_cache_type: 
	設定為0，OFF,緩存禁用 
	設定為1，ON,緩存所有的結果 
	設定為2，DENAND,隻緩存在select語句中通過SQL_CACHE指定需要緩存的查詢

3. 測試能否緩存查詢

mysql> show status like '%Qcache%'; 
+-------------------------+---------+ 
| Variable_name 					| Value | 
+-------------------------+---------+ 
| Qcache_free_blocks 			| 1 			| 
| Qcache_free_memory 			| 1031832 | 
| Qcache_hits 						| 0 			| 
| Qcache_inserts 					| 0 			| 
| Qcache_lowmem_prunes 		| 0 			| 
| Qcache_not_cached 			| 1 			| 
| Qcache_queries_in_cache | 0 			| 
| Qcache_total_blocks 		| 1 			| 
+-------------------------+---------+

Qcache_free_blocks:緩存中目前剩餘的blocks數量（如果值較大，則查詢緩存中的記憶體碎片過多）；
Qcache_free_memory:空閑緩存的記憶體大小；
Qcache_hits:命中緩存次數；
Qcache_inserts: 未命中然後進行正常查詢；
Qcache_lowmem_prunes:查詢因為記憶體不足而被移除出查詢緩存記錄；
Qcache_not_cached: 沒有被緩存的查詢數量；
Qcache_queries_in_cache:目前緩存中緩存的查詢數量；
Qcache_total_blocks:目前緩存的block數量；

優化建議: Query Cache的使用需要多個參數配合，其中最為關鍵的是 query_cache_size 和query_cache_type ，前者設定用于緩存 ResultSet 的記憶體大小，後者設定在何場景下使用 Query Cache。

MySQL資料庫資料變化相對不多，query_cache_size 一般設定為256MB比較合适 ,也可以通過計算Query Cache的命中率來進行調整

( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )

2) 參數: innodb_max_dirty_pages_pct

該參數是InnoDB 存儲引擎用來控制buffffer pool中髒頁的百分比，當髒頁數量占比超過這個參數設定的值時，InnoDB會啟動刷髒頁的操作。

-- innodb_max_dirty_pages_pct 參數可以動态調整，最小值為0， 最大值為99.99，預設值為 75。 
mysql> show variables like 'innodb_max_dirty_pages_pct'; 
+----------------------------+-----------+ 
| Variable_name 						 | Value 		 | 
+----------------------------+-----------+ 
| innodb_max_dirty_pages_pct | 75.000000 | 
+----------------------------+-----------+

優化建議: 該參數比例值越大，從記憶體到磁盤的寫入操作就會相對減少，是以能夠一定程度下減少寫入操作的磁盤IO。但是，如果這個比例值過大，當資料庫 Crash 之後重新開機的時間可能就會很長，因為會有大量的事務資料需要從日志檔案恢複出來寫入資料檔案中.最大不建議超過90,一般重新開機恢複的資料在超過1GB的話,啟動速度就會變慢。

3) 參數: innodb_old_blocks_pct&innodb_old_blocks_time

innodb_old_blocks_pct 用來确定LRU連結清單中old sublist所占比例,預設占用37%

mysql> show variables like '%innodb_old_blocks_pct%'; 
+-----------------------+-------+ 
| Variable_name 				| Value | 
+-----------------------+-------+ 
| innodb_old_blocks_pct | 37 		| 
+-----------------------+-------+

innodb_old_blocks_time 用來控制old sublist中page的轉移政策，新的page頁在進入LRU連結清單中時，會先插入到old sublist的頭部，然後page需要在old sublist中停留innodb_old_blocks_time這麼久後，下一次對該page的通路才會使其移動到new sublist的頭部，預設值1秒。

mysql> show variables like '%innodb_old_blocks_time%'; 
+------------------------+-------+ 
| Variable_name 				 | Value | 
+------------------------+-------+ 
| innodb_old_blocks_time | 1000  | 
+------------------------+-------+

優化建議: 在沒有大表掃描的情況下，并且資料多為頻繁使用的資料時，我們可以增加innodb_old_blocks_pct的值，并且減小innodb_old_blocks_time的值。讓資料頁能夠更快和更多的進入的熱點資料區。

4) 參數: innodb_io_capacity&innodb_io_capacity_max

innodb_io_capacity : InnoDB1.0.x版本開始提供該參數 ,它的作用在兩個方面:

合并插入緩沖時,每秒合并插入緩沖的數量為 innodb_io_capacity值的5%，預設就是200*5%=10
在從緩沖區重新整理髒頁時（checkpoint）,每秒重新整理髒頁的數量就等于innodb_io_capacity的值，預設200

innodb_io_capacity_max : 若使用者使用了SSD類的磁盤,或者将幾塊磁盤做了RAID,當儲存設備擁有更高的 IO速度時,可以将 innodbio_capacity_max的值調高,直到符合磁盤IO的吞吐量為止。

優化建議: 在有頻繁寫入的操作時,對該參數進行調整.并且該參數設定的大小取決于硬碟的IOPS，即每秒的輸入輸出量（或讀寫次數）。

什麼樣的磁盤配置應該設定innodb_io_capacity參數的值是多少,下面是一些參考

僅供參考,建議通過sysbench或者其他基準工具對磁盤吞吐量進行測試

MySQL架構原理之InnoDB存儲引擎參數優化

一、記憶體相關參數優化

1. Buffffer Pool參數優化

二、日志相關參數優化

三、IO線程相關參數優化

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案