MySQL體系結構與存盤引擎

 1、MySQL體系結構

MySQL的體系結構可以分為兩層，MySQL Server層和存盤引擎層，

在MySQL Server層中又包括連接層和SQL層，如圖1-1所示，

應用程式通過介面( 如ODBC、JDBC)來連接MySQL，

最先連接處理的是連接層，連接層包括通信協議、執行緒處理、用戶名密碼認證三個部分，

通信協議負責檢測客戶端版本是否兼容MySQL服務端，

執行緒處理是指每一個連接請求都會分配一個對應的執行緒，

相當于一條SQL對應一個執行緒，一個執行緒對應一個邏輯 CPU，并會在多個邏輯CPU之間進行切換，

用戶名密碼認證驗證創建的賬號和密碼，以及host主機授權是否可以連接到MySQL服務器，

SQL層包含權限判斷、查詢快取、決議器、預處理、查詢優化器、快取和執行計劃，

　　　　　　　　　　圖1-1 MySQL體系結構

權限判斷可以審核用戶有沒有訪問某個庫、某個表，或者表里某行的權限，

查詢快取通過 Query Cache 進行操作，如果資料在 Query Cache 中，則直接回傳結果給客戶端，

查詢決議器針對 SQL 陳述句進行決議，判斷語法是否正確，前處理器對決議器無法決議的語意進行處理，

優化器對 SQL 進行改寫和相應的優化，并生成最優的執行計劃，就可以呼叫程式的 API 介面，通過存盤引擎層訪問資料，

存盤引擎層也是 MySQL 資料庫區別于其他資料庫最核心的一點，

2、query cache 詳解

Query Cache在生產中建議關閉，因為它只能快取靜態資料資訊，一旦資料發生變化， 經常讀寫，QueryCache就成了“雞肋”，

一般像資料倉庫之類的可能會考慮開啟QueryCache，

這里再提及一句，MySQL 5.6之前版本的Query Cache默認是開啟的，5.6 之后默認是關閉的，

如何徹底關閉Query Cache是我們需要關注的，

首先涉及query_ cache 的兩個核心引數：

#查詢快取大小
mysql> show variables like '%query_cache_size%';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 0       |
+------------------+---------+
1 row in set (0.00 sec)

#查詢快取型別
mysql> show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

有些人一直認為只需要把query_cache_size設定為0，就算關閉查詢快取了，

但實際上，我們最不能忽略的引數就是query_cache_type了，

要想徹底關閉Query Cache,必須一開始就把query_cache_type 關閉，

即便是啟動后把query_cache_type 設定為off,也會影響資料庫的TPS，

通過壓力測驗，我們來看兩個核心引數在不同設定下TPS的對比情況，

這里介紹一款MySQL資料庫的壓力測驗軟體sysbench,用它來進行基準測驗，

sysbench 是一個開源的、模塊化的、跨平臺的多執行緒性能測驗工具，

可以用來進行CPU、記憶體、磁盤I/O、執行緒、資料庫的性能測驗，

目前支持的資料庫有MySQL、Oracle 和PostgreSQL，

 #sysbench安裝說明：https://www.cnblogs.com/Sungeek/p/14669280.html

2.1、第一種情況

我們來看一下在不同query_cache 引數設定下，TPS的性能對比情況，
第一種情況：

修改my.cnf引數是query_cache_type=0、 query_cache_size=0，

用sysbench構造100000資料，也就是準備階段：

create database sbtest;
cd /mysql/app/sysbench
sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 prepare

然后進入測驗階段，由于是測驗環境，這里設定的執行緒數量不多，為10個，即num-threads=10，

測驗出每秒處理的事務數為668.18

sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-test-mode=complex \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 --time=60 \
--report-interval=10 \
run

 #生成的報告結果

2.2、第二種情況

第二種情況：是query_cache_type=1、 query_cache_size=0

測驗出每秒處理的事務數為660.63

sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-test-mode=complex \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 --time=60 \
--report-interval=10 \
run

 #生成的報告結果

2.3、第三種情況

第三種情況：把query_ cache_ type=0，是query_ cache_ size=1024000

測驗出每秒處理的事務數為618.43

sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.51 \
--mysql-port=3306 --mysql-user=root --mysql-password=rootroot \
--oltp-test-mode=complex \
--oltp-tables-count=1 --oltp-table-size=100000 --threads=10 --time=60 \
--report-interval=10 \
run

 #生成的報告結果

2.4、第四種情況

第四種情況：把query_ cache_ type=1，是query_ cache_ size=1024000

測驗出每秒處理的事務數為621.55

#總結：

四種情況下：TPS的值分別為668.18、660.63、 618.43、621.55

可見設定正確的query_cache的關閉方式有多么重要，

#MySQL8.0據說是廢除了query_cache：https://blog.csdn.net/n88Lpo/article/details/108395640

3、MySQL存盤引擎

MySQL資料庫及其分支版本主要的存盤引擎有InnoDB、MyISAM、Memory、blackhole、
TokuDB和MariaDB columnstore，

用得最多應該就是InnoDB、MyISAM、Memory

#主要的存盤引擎的特性對比：

InnoDB和MyISAM是最主流的兩個存盤引擎，現在資料庫版本默認的存盤引擎是InnoDB，
并且MySQL 8.0宣布InnoDB存盤資料字典，MyISAM徹底從MySQL資料庫中剝離開，被廢棄了，
但等用上新版本的時候，徹底上線前，還是有不少互聯網公司依然在使用MyISAM存盤引擎，
這里建議大家把線上MyISAM的存盤引擎表全部轉化成InnoDB表存盤，

#innodb和myisam兩者之間的主要區別：

可以看出InnoDB存盤引擎的優勢很明顯，

MySQL被Oracle收購之后，也針對存盤引擎層做了相應的改進與優化，Server層沒有太大的變動，

主要優化的核心就是InnoDB存盤引擎，所以我們今后的重心就放在InnoDB上面,研究它的體系結構，

#MySQL InnoDB存盤引擎體系架構圖

標籤：其他

上一篇：MySQL體系結構與存盤引擎

下一篇：mysql 資料庫學習筆記