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安裝說明：//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：//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存儲引擎體系架構圖
