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 ， 可以根據需要進行動態設定，對于需要快速反饋的業務系統來說，可以將行鎖的等待時間調小，以避免事務長時間掛起； 對于后臺運行的批量處理程式來說， 可以將行鎖的等待時間調大， 以避免發生大的回滾操作，

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

標籤：其他

上一篇：零基礎學Java（9）在mac上運行命令列提示"找不到或無法加載主類"

下一篇：【Python3】推導式