MySQL體系結(jié)構(gòu)與存儲引擎
點擊上方藍色字體，選擇“標(biāo)星公眾號”
優(yōu)質(zhì)文章，第一時間送達
作者 | Tse先生
來源 | urlify.cn/q2Inyi
1、MySQL體系結(jié)構(gòu)
MySQL的體系結(jié)構(gòu)可以分為兩層，MySQL Server層和存儲引擎層。
在MySQL Server層中又包括連接層和SQL層，如圖1-1所示。
應(yīng)用程序通過接口( 如ODBC、JDBC)來連接MySQL。
最先連接處理的是連接層，連接層包括通信協(xié)議、線程處理、用戶名密碼認證三個部分。
通信協(xié)議負責(zé)檢測客戶端版本是否兼容MySQL服務(wù)端。
線程處理是指每一個連接請求都會分配一個對應(yīng)的線程，
相當(dāng)于一條SQL對應(yīng)一個線程，一個線程對應(yīng)一個邏輯 CPU，并會在多個邏輯CPU之間進行切換。
用戶名密碼認證驗證創(chuàng)建的賬號和密碼，以及host主機授權(quán)是否可以連接到MySQL服務(wù)器。
SQL層包含權(quán)限判斷、查詢緩存、解析器、預(yù)處理、查詢優(yōu)化器、緩存和執(zhí)行計劃。
圖1-1 MySQL體系結(jié)構(gòu)
權(quán)限判斷可以審核用戶有沒有訪問某個庫、某個表，或者表里某行的權(quán)限。
查詢緩存通過 Query Cache 進行操作，如果數(shù)據(jù)在 Query Cache 中，則直接返回結(jié)果給客戶端。
查詢解析器針對 SQL 語句進行解析，判斷語法是否正確。預(yù)處理器對解析器無法解析的語義進行處理。
優(yōu)化器對 SQL 進行改寫和相應(yīng)的優(yōu)化，并生成最優(yōu)的執(zhí)行計劃，就可以調(diào)用程序的 API 接口，通過存儲引擎層訪問數(shù)據(jù)。
存儲引擎層也是 MySQL 數(shù)據(jù)庫區(qū)別于其他數(shù)據(jù)庫最核心的一點。
2、query cache 詳解
Query Cache在生產(chǎn)中建議關(guān)閉，因為它只能緩存靜態(tài)數(shù)據(jù)信息，一旦數(shù)據(jù)發(fā)生變化， 經(jīng)常讀寫，QueryCache就成了“雞肋”。
一般像數(shù)據(jù)倉庫之類的可能會考慮開啟QueryCache。
這里再提及一句，MySQL 5.6之前版本的Query Cache默認是開啟的，5.6 之后默認是關(guān)閉的。
如何徹底關(guān)閉Query Cache是我們需要關(guān)注的。
首先涉及query_ cache 的兩個核心參數(shù)：
#查詢緩存大小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設(shè)置為0，就算關(guān)閉查詢緩存了。
但實際上，我們最不能忽略的參數(shù)就是query_cache_type了。
要想徹底關(guān)閉Query Cache,必須一開始就把query_cache_type 關(guān)閉。
即便是啟動后把query_cache_type 設(shè)置為off,也會影響數(shù)據(jù)庫的TPS。
通過壓力測試，我們來看兩個核心參數(shù)在不同設(shè)置下TPS的對比情況。
這里介紹一款MySQL數(shù)據(jù)庫的壓力測試軟件sysbench,用它來進行基準(zhǔn)測試。
sysbench 是一個開源的、模塊化的、跨平臺的多線程性能測試工具，
可以用來進行CPU、內(nèi)存、磁盤I/O、線程、數(shù)據(jù)庫的性能測試。
目前支持的數(shù)據(jù)庫有MySQL、Oracle 和PostgreSQL。
#sysbench安裝說明：https://www.cnblogs.com/Sungeek/p/14669280.html
2.1、第一種情況
我們來看一下在不同query_cache 參數(shù)設(shè)置下，TPS的性能對比情況。
第一種情況：
修改my.cnf參數(shù)是query_cache_type=0、 query_cache_size=0。
用sysbench構(gòu)造100000數(shù)據(jù)，也就是準(zhǔn)備階段：
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#參數(shù)說明
oltp-tables-count：表總數(shù)，生產(chǎn)環(huán)境最少20張表以上
oltp-table-size：表大小，生產(chǎn)環(huán)境最少一千萬條數(shù)據(jù)
threads：線程數(shù)
time：生產(chǎn)環(huán)境測試時間最少15分鐘
report-interval：10秒生成一次報告
然后進入測試階段，由于是測試環(huán)境，這里設(shè)置的線程數(shù)量不多，為10個，即num-threads=10。
測試出每秒處理的事務(wù)數(shù)為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
#生成的報告結(jié)果
2.2、第二種情況
第二種情況：是query_cache_type=1、 query_cache_size=0
測試出每秒處理的事務(wù)數(shù)為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
#生成的報告結(jié)果
2.3、第三種情況
第三種情況：把query_ cache_ type=0，是query_ cache_ size=1024000
測試出每秒處理的事務(wù)數(shù)為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
#生成的報告結(jié)果
2.4、第四種情況
第四種情況：把query_ cache_ type=1，是query_ cache_ size=1024000
測試出每秒處理的事務(wù)數(shù)為621.55
#總結(jié)：
四種情況下：TPS的值分別為668.18、660.63、 618.43、621.55
可見設(shè)置正確的query_cache的關(guān)閉方式有多么重要。
#MySQL8.0據(jù)說是廢除了query_cache：https://blog.csdn.net/n88Lpo/article/details/108395640
3、MySQL存儲引擎
MySQL數(shù)據(jù)庫及其分支版本主要的存儲引擎有InnoDB、MyISAM、Memory、blackhole、
TokuDB和MariaDB columnstore。
用得最多應(yīng)該就是InnoDB、MyISAM、Memory
#主要的存儲引擎的特性對比：
InnoDB和MyISAM是最主流的兩個存儲引擎，現(xiàn)在數(shù)據(jù)庫版本默認的存儲引擎是InnoDB，
并且MySQL 8.0宣布InnoDB存儲數(shù)據(jù)字典，MyISAM徹底從MySQL數(shù)據(jù)庫中剝離開，被廢棄了。
但等用上新版本的時候，徹底上線前，還是有不少互聯(lián)網(wǎng)公司依然在使用MyISAM存儲引擎。
這里建議大家把線上MyISAM的存儲引擎表全部轉(zhuǎn)化成InnoDB表存儲。
#innodb和myisam兩者之間的主要區(qū)別：
可以看出InnoDB存儲引擎的優(yōu)勢很明顯。
MySQL被Oracle收購之后，也針對存儲引擎層做了相應(yīng)的改進與優(yōu)化，Server層沒有太大的變動，
主要優(yōu)化的核心就是InnoDB存儲引擎，所以我們今后的重心就放在InnoDB上面,研究它的體系結(jié)構(gòu)。
#MySQL InnoDB存儲引擎體系架構(gòu)圖
粉絲福利：Java從入門到入土學(xué)習(xí)路線圖
??????
??長按上方微信二維碼 2 秒
感謝點贊支持下哈 