本文來自于拜讀《高性能MySQL（第三版）》時(shí)的讀書筆記
作者：安明哲
轉(zhuǎn)載時(shí)請(qǐng)注明部分內(nèi)容來自《高性能MySQL(第三版)》

MySQL的邏輯構(gòu)架

MySQL服務(wù)器邏輯架構(gòu)

• 最上層：鏈接處理，授權(quán)認(rèn)證，安全等處理

• 第二層：查詢解析、分析、優(yōu)化、緩存以及內(nèi)置函數(shù)（如：日期，時(shí)間，數(shù)學(xué)和加密函數(shù)）

• 第三層：包含了存儲(chǔ)引擎，存儲(chǔ)引擎負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。

鏈接管理和安全性

每一個(gè)客戶端鏈接都在MySQL服務(wù)器進(jìn)程中擁有一個(gè)多線程，在CPU中輪詢運(yùn)行，服務(wù)器會(huì)負(fù)責(zé)緩存線程，因此不需要為每一個(gè)新建的鏈接創(chuàng)建或者銷毀線程。

MySQL5.5以后支持線程池插件，可以使用池中少量的線程來服務(wù)大量的連接。

客戶端連接到MySQL之后，服務(wù)器會(huì)對(duì)其進(jìn)行認(rèn)證，認(rèn)證基于用戶名，密碼和原始主機(jī)信息，如何使用了SSL連接，還可以使用證書認(rèn)證。一旦連接成功，服務(wù)器會(huì)繼續(xù)驗(yàn)證客戶端的權(quán)限。

通常我們通過一個(gè)用戶名和密碼外加一個(gè)數(shù)據(jù)庫的地址連接數(shù)據(jù)庫，連接數(shù)據(jù)庫的指令可能如下：

mysql -u username -h 192.168.1.100 -p *****

當(dāng)我們鏈接MySQL-Server時(shí)，他可能會(huì)檢查我們的用戶名密碼，除此之外還會(huì)檢查我們的主機(jī)地址，通過主機(jī)地址判斷是否允許我們登錄，比如大部分小型站點(diǎn)會(huì)把MySQL和站點(diǎn)文檔放置在一個(gè)服務(wù)器，而且MySQL拒絕了localhost之外的所有連接（不允許遠(yuǎn)程連接），其次我們還可以在MySQL內(nèi)設(shè)置用戶的權(quán)限，比如僅允許select（只讀）。

優(yōu)化與執(zhí)行

MySQL會(huì)解析查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)，然后進(jìn)行各種優(yōu)化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。

對(duì)于SELECT語句，MySQL內(nèi)部會(huì)有一個(gè)Query Cache，如果這個(gè)緩存內(nèi)能夠找到對(duì)應(yīng)的查詢，MySQL則會(huì)省去解析和優(yōu)化工作，直接返回結(jié)果。

并發(fā)控制

MySQL共有兩個(gè)層面的并發(fā)控制，服務(wù)器層與存儲(chǔ)引擎層。

讀寫鎖

當(dāng)有多個(gè)連接同時(shí)修改一個(gè)數(shù)據(jù)庫的某一個(gè)表的某一行的時(shí)候，會(huì)發(fā)生什么結(jié)果是不確定的。

這有點(diǎn)類似于多線程編程下的線程同步和死鎖，事實(shí)上MySQL問題的本質(zhì)似乎也就在這里。

解決這類問題的方法就是并發(fā)控制，MySQL在處理這類并發(fā)操作的時(shí)候可以實(shí)現(xiàn)一個(gè)由兩種類型的鎖組成的索系統(tǒng)來解決問題。這兩種類型的鎖一般被稱為共享鎖和排它鎖。

共享鎖是互不阻塞的，也就是多個(gè)用戶可以同時(shí)讀取一個(gè)資源，所以也叫做讀鎖。

排它鎖是一個(gè)寫鎖會(huì)阻塞其他寫入和讀取操作，只有這樣才能保證同一時(shí)間只會(huì)存在呢一個(gè)連接在想數(shù)據(jù)庫內(nèi)寫入，所以也被稱作寫鎖。

粒度鎖

一種提高共享資源并發(fā)性的方式就是讓鎖定得對(duì)象更具有選擇性。盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)，而不是所有資源。更理想的方式是，只會(huì)對(duì)要修改的數(shù)據(jù)片進(jìn)行精確的鎖定。

那么，理論上鎖定的資源越小，鎖定范圍越精確，那么并發(fā)性能就會(huì)越高。但是事實(shí)上，創(chuàng)建一個(gè)數(shù)據(jù)鎖也會(huì)造成系統(tǒng)的開銷，如果系統(tǒng)通過大量的時(shí)間來管理鎖，而不是存取數(shù)據(jù)，系統(tǒng)的性能反而會(huì)降低。

表鎖

粒度鎖的精確度是根據(jù)需求來決定的，很多時(shí)候我們都是在尋找精度和鎖開銷之間的一個(gè)平衡點(diǎn)。

表鎖是MySQL中基本的鎖策略，并且是開銷最小的策略。當(dāng)用戶在對(duì)一張表進(jìn)行寫操作之前，首先獲得寫鎖，于是，它阻塞了其他鏈接的讀取和寫入操作，只有寫鎖接觸的時(shí)候，其他鏈接才能獲得讀鎖。
不僅如此，MySQL還設(shè)置了鎖的優(yōu)先級(jí)，在操作列隊(duì)中MySQL可能會(huì)把寫入操作插入到讀取操作之前。

那么，可以想象一下MySQL的后臺(tái)實(shí)現(xiàn)，可能表鎖的是這樣是實(shí)現(xiàn)的(僅僅是本人猜想)：

//下面的代碼類似于偽代碼，沒有參考MySQL源碼
//僅僅是本人為了為了理解MySQL表鎖的臆測(cè)：
struct table_lock
{
      /*一個(gè)表鎖的結(jié)構(gòu)體*/
     char* table_name;
     char* host;
};
//實(shí)例化一個(gè)鏈接
int* instance = getInstance();
//當(dāng)執(zhí)行讀取操作的時(shí)候，可能需要傳遞一個(gè)table_name
instance->readTable(table_name)
{
    //讀取操作時(shí)候首先要獲取表鎖的所有權(quán)
    table_lock.name = table_name;
    table_lock.host = instance;
}

行級(jí)鎖

行級(jí)鎖可以最大程度的支持并發(fā)處理（同時(shí)也帶來了最大的鎖開銷）。行級(jí)鎖只在存儲(chǔ)引擎中實(shí)現(xiàn)。

行級(jí)鎖比表鎖更加精確,他把鎖的對(duì)象精確到了對(duì)象的某一行，但也就意味著需要?jiǎng)?chuàng)建更多的鎖。

事務(wù)

事務(wù)其實(shí)就是一個(gè)獨(dú)立的工作單元。如果數(shù)據(jù)庫引擎能夠完成事務(wù)中的每一項(xiàng)操作，那么全組的SQL語句都會(huì)被執(zhí)行，如果任何一條語句因?yàn)楸罎⒒蛘咂渌驘o法執(zhí)行，那么所有語句都不執(zhí)行。

最簡(jiǎn)單的例子就是我們銀行的轉(zhuǎn)賬系統(tǒng)，一個(gè)轉(zhuǎn)賬操作實(shí)際上是從用戶賬戶表里減去對(duì)A的賬戶余額進(jìn)行修改，同時(shí)再去修改B的賬戶余額，最后再在記錄表被記錄下這一條操作。SQL語句大致如下：

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 1010001;
UPDATE checking SET balance = balance-200.00 WHERE customer_id = 1010001;
UPDATE checking SET balance = balance+200.00 WHERE customer_id = 1010002;
INSERT INTO record VALUES('1010001', '1010002', 200.00);
COMMIT;

這個(gè)時(shí)候如果執(zhí)行到第三條語句的時(shí)候發(fā)現(xiàn)1010002這個(gè)賬戶已經(jīng)被凍結(jié)了，無法接受轉(zhuǎn)賬，這個(gè)時(shí)候按照正常的邏輯，數(shù)據(jù)庫內(nèi)部10010001這個(gè)用戶平白無故的就少去了200元，但是10010002并沒有收到。
使用事務(wù)就完美的解決了這個(gè)問題，如果一條語句執(zhí)行失敗，沒關(guān)系，最終沒有commit之前，MySQL是不會(huì)進(jìn)行寫入操作的。

ACID

那么有一個(gè)專業(yè)的名詞來描繪這種需求，他叫做ACID:
A---atomicity（原子性）:一個(gè)事務(wù)必須被視為一個(gè)不可分割最小工作單元，事務(wù)中的操作只要有一個(gè)失敗則全部回滾（可以理解為，一件事情，要么全做，有一點(diǎn)調(diào)點(diǎn)不具備那么全部都不做。）

C---Consistency(一致性)：在一個(gè)轉(zhuǎn)賬事務(wù)中，A賬戶的減少必定對(duì)應(yīng)著B賬戶的增加，這個(gè)狀態(tài)轉(zhuǎn)換的過程必須保持一致，那么這就是事務(wù)的一致性。

I---Isolation（隔離性）：通常來說，一個(gè)事物所做的修改在最終提交之前，對(duì)其他事務(wù)是不可兼得。也就是說，事務(wù)內(nèi)部的操作是不會(huì)被外部所看到的，只有最終提交之后，我們才能在銀行系統(tǒng)中看到兩個(gè)賬戶金額的變化（這個(gè)時(shí)候如何還有一個(gè)事務(wù)在執(zhí)行同樣的轉(zhuǎn)賬操作，那么們是相互隔離的，這看起來并不嚴(yán)謹(jǐn)，事實(shí)這里還設(shè)計(jì)一個(gè)隔離級(jí)別的問題，這個(gè)以后再談）。

D---durability（持久性）：一旦失誤提交，所有的修改會(huì)永久的保存在數(shù)據(jù)庫中，即使系統(tǒng)崩潰，服務(wù)器被損壞，只要硬盤還在，數(shù)據(jù)就依舊存在。

事務(wù)的ACID看起來很簡(jiǎn)單，但是在應(yīng)用邏輯中要實(shí)現(xiàn)這一點(diǎn)非常難，因?yàn)檫€有相當(dāng)一部分涉及到用戶體驗(yàn)的考慮，你必須保證數(shù)據(jù)同步的問題，保證數(shù)據(jù)的一致性和持久性同時(shí)還要讓用戶覺察不到這么復(fù)雜的工作。而且，事務(wù)所做的操作正如鎖一樣，需要更多的系統(tǒng)資源，你還需要更強(qiáng)的CPU、更大的內(nèi)存和更多的磁盤空間。