1 MySQL總體架構介紹

1.1 MySQL總體架構介紹

引言
MySQL是一個關係型資料庫
應用十分廣泛
在學習任何一門知識之前
對其架構有一個概括性的瞭解是非常重要的
比如索引、sql是在哪個地方執行的
流程是什麼樣的
今天我們就先來學習一下MySQL的總體架構

總的來說：MySQL架構是一個客戶端-伺服器系統。

MySQL主要包括以下幾部分:

Server 層：主要包括連接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日誌模塊 binglog 日誌模塊。

存儲引擎： 主要負責數據的存儲和讀取，採用可以替換的插件式架構，支持 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有自己的日誌模塊 redolog 模塊。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當做預設存儲引擎了

連接器： 身份認證和許可權相關(登錄 MySQL 的時候)。

查詢緩存: 執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本後移除，因為這個功能不太實用）mysql的server層增加一層緩存模塊，類似一個記憶體的kv層，k是sql，value是結果

分析器: 沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要幹嘛，再檢查你的 SQL 語句語法是否正確。

優化器： 按照 MySQL 認為最優的方案去執行。

執行器: 執行語句，然後從存儲引擎返回數據。

1.2 MySQL存儲引擎介紹

引言
和大多數的資料庫不同, MySQL中有一個存儲引擎的概念

針對不同的存儲需求可以選擇最優的存儲引擎。

存儲引擎就是存儲數據，建立索引，更新查詢數據等等技術的實現方式 。

存儲引擎是基於表的，而不是基於庫的
所以存儲引擎也可被稱為表類型。

MySQL提供了插件式的存儲引擎架構。所以MySQL存在多種存儲引擎，可以根據需要使用相應引擎，或者編寫存儲引擎。

​ MySQL5.0支持的存儲引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等

可以通過指定 show engines ， 來查詢當前資料庫支持的存儲引擎 ：

SHOW ENGINES;

表含義:
  - support : 指伺服器是否支持該存儲引擎
  - transactions : 指存儲引擎是否支持事務
  - XA : 指存儲引擎是否支持分散式事務處理
  - Savepoints : 指存儲引擎是否支持保存點（實現回滾到指定保存點）

• 查看MySQL資料庫存儲引擎配置

  SHOW VARIABLES LIKE '%storage_engine%';
  

1.2.1 如何更改資料庫表引擎

• 建表語句後面加入引擎賦值即可 ,命令舉例如下 ,

CREATE TABLE t1(
	id INT ,
    name VARCHAR(20)
) ENGINE = MyISAM;

• 修改已有的表引擎 , 命令舉例如下 ,

ALTER TABLE t1 ENGINE = InnoDB;

1.2.2 常用引擎及其特性對比

• 常見的存儲引擎 :

  MyISAM存儲引擎 : 訪問快,不支持事務和外鍵。表結構保存在.frm文件中，表數據保存在.MYD文件中，索引保存在.MYI文件中。

  [root@linux-141 itcast]# ll
  -rw-r-----. 1 mysql mysql       8630 9月  10 16:01 t_account_myisam.frm
  -rw-r-----. 1 mysql mysql         52 9月  10 16:06 t_account_myisam.MYD
  -rw-r-----. 1 mysql mysql       2048 9月  10 17:56 t_account_myisam.MYI
  [root@linux-141 itcast]#
  
  

innoDB存儲引擎(5.5版本開始預設) : 支持事務 ,占用磁碟空間大 ,支持併發控制。表結構保存在.frm文件中，如果是共用表空間，數據和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個文件。如果是多表空間存儲，每個表的數據和索引單獨保存在 .ibd 中。

[root@linux-141 itcast]# ll
-rw-r-----. 1 mysql mysql       8630 9月  10 16:02 t_account_innodb.frm
-rw-r-----. 1 mysql mysql      98304 9月  14 15:50 t_account_innodb.ibd
[root@linux-141 itcast]#

MEMORY存儲引擎 : 記憶體存儲 , 速度快 ,不安全 ,適合小量快速訪問的數據。表結構保存在.frm中。

!

特性對比 :

1.2.3 如何選擇不同類型的引擎

在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對於複雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組合。

以下是幾種常用的存儲引擎的使用環境。

• InnoDB : 是Mysql的預設存儲引擎，用於事務處理應用程式，支持外鍵。如果應用對事務的完整性有比較高的要求，在併發條件下要求數據的一致性，數據操作除了插入和查詢以外，還包含更新、刪除操作，那麼InnoDB存儲引擎是比較合適的選擇。InnoDB存儲引擎除了有效的降低由於刪除和更新導致的鎖定， 還可以確保事務的完整提交和回滾，對於類似於計費系統或者財務系統等對數據準確性要求比較高的系統，InnoDB是最合適的選擇。
• MyISAM ： 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、併發性要求不是很高，那麼選擇這個存儲引擎是非常合適的。
• MEMORY：將所有數據保存在RAM中，在需要快速定位記錄和其他類似數據環境下，可以提供極快的訪問。MEMORY的缺陷就是對錶的大小有限制，太大的表無法緩存在記憶體中，其次是要確保表的數據可以恢復，資料庫異常終止後表中的數據是可以恢復的。MEMORY表通常用於更新不太頻繁的小表，用以快速得到訪問結果。

1.3 SQL的執行流程是什麼樣的

• 客戶端發送一條查詢給伺服器。
• 伺服器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。否則進入下一階段。
• 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。
• MySQL根據優化器生成的執行計劃，再調用存儲引擎的API來執行查詢。
• 將結果返回給客戶端。

2 MySQL存儲引擎調優

2.1 MySQL伺服器硬體優化

tips

硬體（cpu、記憶體等）相關

瞭解即可

關於提升硬體設備性能：

例如選擇儘量高頻率的記憶體（頻率不能高於主板的支持）、提升網路帶寬、使用SSD高速磁碟、提升CPU性能等。

CPU的選擇:

• 對於資料庫併發比較高的場景，CPU的數量比頻率重要。
• 對於CPU密集型場景和頻繁執行複雜SQL的場景，CPU的頻率越高越好

磁碟的選擇

影響資料庫最大的性能問題就是磁碟I/O
為提高資料庫的IOPS性能，可使用SSD或PCIE-SSD高速磁碟設備

磁碟IO的優化

可以用RAID來進行優化

常用RAID（磁碟陣列）級別：

RAID0：也稱為條帶，就是把多個磁碟鏈接成一個硬碟使用，這個級別IO最好
RAID1：也稱為鏡像，要求至少有兩個磁碟，每組磁碟存儲的數據相同
RAID5：也是把多個（最少3個）硬碟合併成一個邏輯盤使用，數據讀寫時會建立奇偶校驗信息，並且奇偶校驗信息和相對應的數據分別存儲在不同的磁碟上。當RAID5的一個磁碟數據發生損壞後，利用剩下的數據和響應的奇偶校驗信息去恢復被損壞的數據

RAID1+0（建議使用）：就是RAID0和RAID1的組合。同時具備兩個級別的優缺點，一般建議資料庫使用這個級別。

2.2 MySQL資料庫配置優化

tips：

以下為生產環境中最常用的DB參數配置

• 表示緩衝池位元組大小，大的緩衝池可以減少磁碟IO次數。
  innodb_buffer_pool_size = 推薦值為物理記憶體的50%~80%。

• 用來控制redo log buffer刷新到磁碟的策略。
  innodb_flush_log_at_trx_commit=1

  select @@innodb_flush_log_at_trx_commit;
  

  0 : 提交事務的時候，不立即把 redo log buffer 里的數據刷入磁碟文件中，而是依靠 InnoDB 的主線程每秒執行一次刷新到磁碟。此時可能你提交事務了，結果 mysql 宕機了，然後此時記憶體里的數據全部丟失。
  1 : 提交事務的時候，立即把 redo log buffer 里的數據刷入磁碟文件中，只要事務提交成功，那麼數據就必然在磁碟里了。
  2 : 提交事務的時候，把 redo log buffer日誌寫入磁碟文件對應的系統緩存，而不是直接進入磁碟文件，這時可能1秒後才會把系統緩存里的數據寫入到磁碟文件。
  

• 每提交1次事務就同步寫到磁碟中，可以設置為1。
  sync_binlog=1

  0：預設值。事務提交後，將二進位日誌從緩衝寫入操作系統緩衝，但是不進行刷新操作（fsync()），此時只是寫入了操作系統緩衝而沒有刷新到磁碟，若操作系統宕機則會丟失部分二進位日誌。
  1：事務提交後，將二進位文件寫入磁碟並立即執行刷新操作，相當於是同步寫入磁碟，不經過操作系統的緩存。
  N：每寫N次操作系統緩衝就執行一次刷新操作。
  

• 臟頁占innodb_buffer_pool_size的比例，觸發刷臟頁到磁碟。 推薦值為25%~50%。
  innodb_max_dirty_pages_pct=30

  臟頁：記憶體數據頁和磁碟數據頁上的內容不一致
  

• 後臺進程最大IO性能指標。
  預設200，如果SSD，調整為5000~20000

  PCIE-SSD可調整為5w左右

  預設：innodb_io_capacity=200

• 指定innodb共用表空間文件的大小。
  innodb_data_file_path ＝ ibdata:1G:autoextend：預設10M，

  一般設置為1GB

• 慢查詢日誌的閾值設置，單位秒。
  long_query_time=0.3

  合理設置區間0.1s~0.5s，

• mysql複製的形式，row為MySQL8.0的預設形式。
  binlog_format=row

  建議binlog的記錄格式為row模式

  STATEMENT模式：每一條會修改數據的sql語句都會記錄到binlog中。
  ROW模式：不記錄每條sql語句的上下文信息，僅需記錄哪條數據被修改了，修改成什麼樣了。
  MIXED模式：以上兩種模式的混合使用。
  

• 降低interactive_timeout、wait_timeout的值。

  交互等待時間和非交互等待時間，值一致，建議300~500s，預設8小時

  在用mysql客戶端對資料庫進行操作時，打開終端視窗，如果一段時間（8小時）沒有操作，再次操作時，會報錯：當前的連接已經斷開，需要重新建立連接
  

• 資料庫最大連接數max_connections=200

• 過大，實例恢復時間長；過小，造成日誌切換頻繁。
  innodb_log_file_size=預設

  redo log空間大小

• 全量日誌建議關閉。
  預設關閉general_log=0

  開啟 general log 將所有到達MySQL Server的SQL語句記錄下來，general_Log文件就會產生很大的文件，建議關閉
  

2.3 Mysql中查詢緩存優化

tips：

在MySQL 8.0之後廢棄這個功能

原理：複雜、實用性不高

作為瞭解即可

1） 查詢緩存概述

開啟Mysql的查詢緩存，當執行完全相同的SQL語句的時候，伺服器就會直接從緩存中讀取結果，當數據被修改，之前的緩存會失效，修改比較頻繁的表不適合做查詢緩存。

2） 操作流程

回顧

1. 客戶端發送一條查詢給伺服器；
2. 伺服器先會檢查查詢緩存，如果命中了緩存，則立即返回存儲在緩存中的結果。否則進入下一階段；
3. 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃；
4. MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢；
5. 將結果返回給客戶端。

3） 查詢緩存配置

1. 查看當前的MySQL資料庫是否支持查詢緩存：

   SHOW VARIABLES LIKE 'have_query_cache';	
   

2. 查看當前MySQL是否開啟了查詢緩存 ：

   SHOW VARIABLES LIKE 'query_cache_type';
   

3. 查看查詢緩存的占用大小 ：

   SHOW VARIABLES LIKE 'query_cache_size';
   

4. 查看查詢緩存的狀態變數：

   SHOW STATUS LIKE 'Qcache%';
   

各個變數的含義如下：

4） 開啟查詢緩存

MySQL的查詢緩存預設是關閉的，需要手動配置參數 query_cache_type ， 來開啟查詢緩存。query_cache_type 該參數的可取值有三個 ：

在 my.cnf 配置中，增加以下配置 ：

#開啟查詢緩存
query_cache_type=1

配置完畢之後，重啟服務既可生效 ；

然後就可以在命令行執行SQL語句進行驗證 ，執行一條比較耗時的SQL語句，然後再多執行幾次，查看後面幾次的執行時間；獲取通過查看查詢緩存的緩存命中數，來判定是否走查詢緩存。

-- 執行SQL語句進行驗證 查詢緩存
SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';
-- 將SELECT修改為小寫，發現緩存失效
SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

5） 查詢緩存SELECT選項

可以在SELECT語句中指定兩個與查詢緩存相關的選項 ：

SQL_CACHE : 如果查詢結果是可緩存的，並且 query_cache_type 系統變數的值為ON或 DEMAND ，則緩存查詢結果 。

SQL_NO_CACHE : 伺服器不使用查詢緩存。它既不檢查查詢緩存，也不檢查結果是否已緩存，也不緩存查詢結果。

例子：

SELECT SQL_CACHE id, name FROM xxx;
SELECT SQL_NO_CACHE id, name FROM xxx;

​

6） 查詢緩存失效的情況

tips

需要註意的問題

1） SQL 語句不一致的情況， 要想命中查詢緩存，查詢的SQL語句必須一致。

SQL1 : select count(*) from xxx;
SQL2 : Select count(*) from xxx;

2） 當查詢語句中有一些不確定的值，則不會緩存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

SQL1 : select * from xxx where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();

3） 不使用任何表查詢語句。

select 'A';

4） 查詢 mysql， information_schema或 performance_schema 資料庫中的表時，不會走查詢緩存。

select * from information_schema.engines;

5） 在存儲的函數，觸發器或事件的主體內執行的查詢。

6） 如果表更改，則使用該表的所有高速緩存查詢都將變為無效並從高速緩存中刪除。這包括使用MERGE映射到已更改表的表的查詢。一個表可以被許多類型的語句，如被改變 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

將查詢緩存關閉，因為後面還需要進行索引的驗證，所以不希望走查詢緩存

[root@linux-141 itcast]# vi /etc/my.cnf
[root@linux-141 itcast]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!

2.4. Mysql記憶體管理及優化

1）記憶體優化原則

1） 將儘量多的記憶體分配給MySQL做緩存，但要給操作系統和其他程式預留足夠記憶體。

2） MyISAM 存儲引擎的數據文件讀取依賴於操作系統自身的IO緩存，因此，如果有MyISAM表，就要預留更多的記憶體給操作系統做IO緩存。

3） 排序區、連接區等緩存是分配給每個資料庫會話（session）專用的，其預設值的設置要根據最大連接數合理分配，如果設置太大，不但浪費資源，而且在併發連接較高時會導致物理記憶體耗盡。

2） MyISAM 記憶體優化

MyISAM 存儲引擎使用 key_buffer 緩存索引塊，加速myisam索引的讀寫速度。對於myisam表的數據塊，mysql沒有特別的緩存機制，完全依賴於操作系統的IO緩存。

key_buffer_size

key_buffer_size決定MyISAM索引塊緩存區的大小，直接影響到MyISAM表的存取效率。可以在MySQL參數文件中設置key_buffer_size的值，對於一般MyISAM資料庫，建議至少將1/4可用記憶體分配給key_buffer_size。

在my.cnf 中做如下配置：

key_buffer_size=512M

read_buffer_size

如果需要經常順序掃描MyISAM 表，可以通過增大read_buffer_size的值來改善性能。但需要註意的是read_buffer_size是每個session獨占的，如果預設值設置太大，就會造成記憶體浪費。

read_rnd_buffer_size

對於需要做排序的MyISAM 表的查詢，如帶有order by子句的sql，適當增加 read_rnd_buffer_size 的值，可以改善此類的sql性能。

但需要註意的是 read_rnd_buffer_size 是每個session獨占的，如果預設值設置太大，就會造成記憶體浪費。

3） InnoDB 記憶體優化

innodb用一塊記憶體區做IO緩存池，該緩存池不僅用來緩存innodb的索引塊，而且也用來緩存innodb的數據塊。

innodb_buffer_pool_size

該變數決定了 innodb 存儲引擎表數據和索引數據的最大緩存區大小。在保證操作系統及其他程式有足夠記憶體可用的情況下，innodb_buffer_pool_size 的值越大，緩存命中率越高，訪問InnoDB表需要的磁碟I/O 就越少，性能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size

決定了innodb重做日誌緩存的大小，對於可能產生大量更新記錄的大事務，增加innodb_log_buffer_size的大小，可以避免innodb在事務提交前就執行不必要的日誌寫入磁碟操作。

innodb_log_buffer_size=10M

2.5. Mysql併發參數調整

從實現上來說，MySQL Server 是多線程結構，包括後臺線程和客戶服務線程。多線程可以有效利用伺服器資源，提高資料庫的併發性能。在Mysql中，控制併發連接和線程的主要參數包括 max_connections、back_log、thread_cache_size、table_open_cahce。

1） max_connections

最大可支持的連接數

採用max_connections 控制允許連接到MySQL資料庫的最大數量，預設值是 151。如果狀態變數 connection_errors_max_connections 不為零，並且一直增長，則說明不斷有連接請求因資料庫連接數已達到允許最大值而失敗，這時可以考慮增大max_connections 的值。

Mysql 最大可支持的連接數，取決於很多因素，包括給定操作系統平臺的線程庫的質量、記憶體大小、每個連接的負荷、CPU的處理速度，期望的響應時間等。在Linux 平臺下，性能好的伺服器，支持 500-1000 個連接不是難事，需要根據伺服器性能進行評估設定。

2） back_log

積壓請求棧大小

back_log 參數控制MySQL監聽TCP埠時設置的積壓請求棧大小。如果MySql的連接數達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源，將會報錯。5.6.6 版本之前預設值為 50 ， 之後的版本預設為 50 + （max_connections / 5）， 但最大不超過900。

如果需要資料庫在較短的時間內處理大量連接請求， 可以考慮適當增大back_log 的值。

3） table_open_cache

執行線程可打開表緩存個數

該參數用來控制所有SQL語句執行線程可打開表緩存的數量， 而在執行SQL語句時，每一個SQL執行線程至少要打開 1 個表緩存。該參數的值應該根據設置的最大連接數 max_connections 以及每個連接執行關聯查詢中涉及的表的最大數量來設定 ：

​ max_connections x N ；

4） thread_cache_size

緩存客戶服務線程的數量

為了加快連接資料庫的速度，MySQL 會緩存一定數量的客戶服務線程以備重用，通過參數 thread_cache_size 可控制 MySQL 緩存客戶服務線程的數量。

5）lock_wait_timeout

innodb_lock_wait_timeout

事務等待行鎖的時間

該參數是用來設置InnoDB 事務等待行鎖的時間，預設值是50ms ， 可以根據需要進行動態設置。對於需要快速反饋的業務系統來說，可以將行鎖的等待時間調小，以避免事務長時間掛起； 對於後臺運行的批量處理程式來說， 可以將行鎖的等待時間調大， 以避免發生大的回滾操作。

本文由傳智教育博學谷 - 狂野架構師教研團隊發佈
如果本文對您有幫助，歡迎關註和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力
轉載請註明出處！