MySQL實(shí)戰(zhàn)——基礎(chǔ)架構(gòu)

本文屬于個(gè)人備忘錄，主要是極客時(shí)間《MySQL實(shí)戰(zhàn)45講》學(xué)習(xí)筆記。

MySQL架構(gòu)



MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分。不同的存儲(chǔ)引擎共用一個(gè)Server層。

<>

Server層

<>

概述

Server層包括

* 連接器
* 查詢緩存
* 分析器
* 優(yōu)化器
* 執(zhí)行器
* ...
Server層功能

* MySQL大多數(shù)核心服務(wù)功能
* 所有的內(nèi)置函數(shù)，如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等
* 跨存儲(chǔ)引擎的功能，如存儲(chǔ)過程、觸發(fā)器、視圖等
<>

連接器

<>

連接器做什么？

* 連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。
連接命令示例
mysql -h$ip -P$port -u$user -p
其中，mysql是客戶端工具，用來跟服務(wù)端建立連接。在完成經(jīng)典的 TCP 握手后，連接器就要開始認(rèn)證你的身份，這個(gè)時(shí)候用的就是你輸入的用戶名和密碼。

* 如果用戶名或密碼不對(duì)，會(huì)收到"Access denied for user"錯(cuò)誤，客戶端結(jié)束執(zhí)行
*
如果用戶名密碼認(rèn)證通過，連接器會(huì)到權(quán)限表里面查出你擁有的權(quán)限。之后，這個(gè)連接里面的權(quán)限判斷邏輯，都將依賴于此時(shí)讀到的權(quán)限。

* 這就意味著，一個(gè)用戶成功建立連接后，即使你用管理員賬號(hào)對(duì)這個(gè)用戶的權(quán)限做了修改，也不會(huì)影響已經(jīng)存在連接的權(quán)限。
* 權(quán)限修改完成后，只有再新建的連接才會(huì)使用新的權(quán)限設(shè)置。
*
連接完成后，如果沒有后續(xù)的動(dòng)作，這個(gè)連接就處于空閑狀態(tài)，可以用show processlist命令看該鏈接狀態(tài)。如下。

* 其中Command列顯示為“Sleep”的這一行，就表示現(xiàn)在系統(tǒng)里面有一個(gè)空閑連接。 mysql> show processlist;
+----+----------+-----------------+---------+---------+--------+-----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------------+---------+---------+--------+-----------+------------------+
| 28 | testuser | localhost:60453 | db_test | Sleep | 8 | | NULL | | 29 |
testuser | localhost:53231 | db_test | Query | 0 | starting | show processlist
|
+----+----------+-----------------+---------+---------+--------+-----------+------------------+
25 rows in set (0.00 sec)
客戶端如果太長時(shí)間沒動(dòng)靜，連接器會(huì)自動(dòng)斷開。這個(gè)時(shí)間是由參數(shù)wait_timeout控制的，默認(rèn)值是8小時(shí)。
mysql> show variables like 'wait_timeout'; +---------------+-------+ |
Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 |
+---------------+-------+ 1 row in set (0.01 sec) mysql>
如果在連接被斷開之后，客戶端再次發(fā)送請求的話，會(huì)收到一個(gè)錯(cuò)誤：Lost connection to MySQL server during
query。這時(shí)候如果要繼續(xù)，就需要重連后再執(zhí)行請求。

<>

什么是長連接？

長連接：指連接成功后，如果客戶端持續(xù)有請求，則一直使用同一個(gè)連接。

建立連接的過程通常比較復(fù)雜，建議使用中要盡量減少建立連接的動(dòng)作，即盡量使用長連接。

<>

什么是短連接？

短連接：指每次執(zhí)行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個(gè)。

<>

長連接的影響

影響

* 全部使用長連接后，可能會(huì)發(fā)現(xiàn)，有些時(shí)候MySQL占用內(nèi)存漲得特別快。
* 如果長連接累積下來，可能導(dǎo)致內(nèi)存占用太大，被系統(tǒng)強(qiáng)行殺掉（OOM），看起來是MySQL異常重啟。
原因

* 是因?yàn)镸ySQL在執(zhí)行過程中臨時(shí)使用的內(nèi)存是管理在連接對(duì)象里面的。這些資源會(huì)在連接斷開的時(shí)候才釋放。
解決辦法

*
定期斷開長連接。

* 使用一段時(shí)間，或者程序里面判斷執(zhí)行過一個(gè)占用內(nèi)存的大查詢后斷開連接，之后查詢時(shí)再重連。
*
執(zhí)行mysql_reset_connection重新初始化連接資源

* MySQL >= 5.7版本
* 在每次執(zhí)行一個(gè)比較大的操作后，重新初始化鏈接資源
* 這個(gè)過程不需要重連和重新鑒權(quán)，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)
<>

查詢緩存

連接建立完成后，就可以進(jìn)行查詢了。MySQL接到一個(gè)查詢請求后，會(huì)先查詢緩存。

* 如果語句在緩存中，則其對(duì)應(yīng)的結(jié)果會(huì)被直接返回給客戶端，以提高查詢效率。

* 如果語句不在緩存中，會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果會(huì)被存入查詢緩存中。
但是大多數(shù)情況下不要使用查詢緩存。因?yàn)椴樵兙彺嫱状笥诶?br>
查詢緩存的失效非常頻繁，只要有對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。

*
不使用緩存

* 對(duì)于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會(huì)非常低。
*
可使用緩存

* 業(yè)務(wù)是一張靜態(tài)表，很長時(shí)間才會(huì)更新一次。
* 比如，一個(gè)系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。
可通過設(shè)置參數(shù)query_cache_type來設(shè)置要不要使用查詢緩存。

*
DEMAND

* 默認(rèn)都不使用查詢緩存
*
SQL_CACHE

* 顯式指定使用查詢緩存，如 mysql> select SQL_CACHE * from T where ID=10；
通常情況下，該參數(shù)設(shè)置為OFF，即關(guān)閉查詢緩存即可。
mysql> show variables like 'query_cache_type'; +------------------+-------+ |
Variable_name | Value | +------------------+-------+ | query_cache_type | OFF |
+------------------+-------+ 1 row in set (0.00 sec)
注意

* MySQL 8.0版本直接將查詢緩存的整塊功能刪掉了，即8.0開始徹底沒有這個(gè)功能了。
<>

分析器

分析器做什么？

*
詞法分析

* 識(shí)別SQL語句各個(gè)字符串是什么、代表什么
*
語法分析

* 根據(jù)語法規(guī)則，判斷SQL語句是否滿足MySQL語法
* 如果語句不對(duì)，會(huì)報(bào)“You have an error in your SQL syntax”錯(cuò)誤 mysql> elect * from t
where ID=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'elect * from t where ID=1' at line 1
一般語法錯(cuò)誤會(huì)提示第一個(gè)出現(xiàn)錯(cuò)誤的位置，需要關(guān)注緊接“use near”的內(nèi)容。

<>

優(yōu)化器

優(yōu)化器做什么？

* 在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引
* 或者在一個(gè)語句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序
比如執(zhí)行下面這樣的語句，這個(gè)語句是執(zhí)行兩個(gè)表的join：

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
兩種執(zhí)行方法

* 先從表t1里面取出c=10的記錄的ID值，再根據(jù)ID值關(guān)聯(lián)到表t2，再判斷t2里面d的值是否等于20
* 先從表t2里面取出d=20的記錄的ID值，再根據(jù)ID值關(guān)聯(lián)到t1，再判斷t1里面c的值是否等于10
這兩種執(zhí)行方法的邏輯結(jié)果是一樣的，但是執(zhí)行的效率會(huì)有不同，而優(yōu)化器的作用就是決定選擇使用哪一個(gè)方案。

<>

執(zhí)行器

分析器：要做什么
優(yōu)化器：怎么做

執(zhí)行步驟

*
鑒權(quán)

* 沒有：返回?zé)o權(quán)限錯(cuò)誤
* 有：繼續(xù)
*
打開表執(zhí)行

* 打開表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。
有索引查詢

* 第一次調(diào)用的是“取滿足條件的第一行”這個(gè)接口
* 之后循環(huán)調(diào)用“滿足條件的下一行”這個(gè)接口
無索引查詢


*
調(diào)用InnoDB引擎接口取這個(gè)表的第一行，判斷ID值是不是10

* 如果不是則跳過
* 如果是則將這行存在結(jié)果集中
* 調(diào)用引擎接口取下一行，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行
* 執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端
掃描了多少行？

* 數(shù)據(jù)庫慢查詢?nèi)罩局杏幸粋€(gè)rows_examined字段，表示這個(gè)語句執(zhí)行過程中掃描了多少行
* 這個(gè)值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時(shí)候累加的

<>

存儲(chǔ)引擎層

* 存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取
* 架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎
* 目前最常用的存儲(chǔ)引擎是InnoDB，它從MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎
比如，執(zhí)行create table建表時(shí)，

* 如果不指定引擎類型，默認(rèn)使用的就是 InnoDB
* 使用engine=memory 來指定內(nèi)存引擎創(chuàng)建表

• ? 上一篇： Linux防火墻常用命令
• ? 下一篇：強(qiáng)烈推薦 10 款珍藏的 Chrome 瀏覽器插件