一、mysql有關權限的表

MySQL服務器通過權限表來控制用戶對數據庫的訪問，權限表存放在mysql數據庫里，由mysql_install_db腳本初始化。這些權限表分別user，db，table_priv，columns_priv和host。下面分別介紹一下這些表的結構和內容：

user權限表：記錄允許連接到服務器的用戶帳號信息，里面的權限是全局級的。
db權限表：記錄各個帳號在各個數據庫上的操作權限。
table_priv權限表：記錄數據表級的操作權限。
columns_priv權限表：記錄數據列級的操作權限。
host權限表：配合db權限表對給定主機上數據庫級操作權限作更細致的控制。這個權限表不受GRANT和REVOKE語句的影響。

二、鎖

根據加鎖的范圍，MySQL 里面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。

1、全局鎖：

顧名思義，全局鎖就是對整個數據庫實例加鎖。
MySQL 提供了一個加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處于只讀狀態(tài)的時候，可以使用這個命令，之后其他線程的以下語句會被阻塞：數據更新語句（數據的增刪改）、數據定義語句（包括建表、修改表結構等）和更新類事務的提交語句。

全局鎖的典型使用場景是，做全庫邏輯備份。//也就是把整庫每個表都 select 出來存成文本。

官方自帶的邏輯備份工具是mysqldump。當 mysqldump 使用參數–single-transaction 的時候，導數據之前就會在可重復讀隔離級別下開啟一個事務來確保拿到一致性視圖。而由于 MVCC 的支持，這個過程中數據是可以正常更新的。所以，single-transaction 方法只適用于所有的表使用事務引擎的庫。如果有的表使用了不支持事務的引擎，那么備份就只能通過 FTWRL 方法。這往往是 DBA 要求業(yè)務開發(fā)人員使用 InnoDB 替代 MyISAM 的原因之一。

既然要全庫只讀，為什么不使用 set global readonly=true 的方式呢？確實 readonly 方式也可以讓全庫進入只讀狀態(tài)，但我還是會建議你用 FTWRL 方式，主要有兩個原因：一是，在有些系統(tǒng)中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變量的方式影響面更大，我不建議你使用。二是，在異常處理機制上有差異。如果執(zhí)行 FTWRL 命令之后由于客戶端發(fā)生異常斷開，那么 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態(tài)。而將整個庫設置為 readonly 之后，如果客戶端發(fā)生異常，則數據庫就會一直保持 readonly 狀態(tài)，這樣會導致整個庫長時間處于不可寫狀態(tài)，風險較高。

2、表級鎖：

MySQL 里面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL)。表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

LOCK TABLES 和 UNLOCK TABLES
Mysql也支持lock tables和unlock tables，這都是在服務器層（MySQL Server層）實現的，和存儲引擎無關，它們有自己的用途，并不能替代事務處理。（除了禁用了autocommint后可以使用，其他情況不建議使用）：

LOCK TABLES 可以鎖定用于當前線程的表。如果表被其他線程鎖定，則當前線程會等待，直到可以獲取所有鎖定為止。
UNLOCK TABLES 可以釋放當前線程獲得的任何鎖定。當前線程執(zhí)行另一個 LOCK TABLES 時，
或當與服務器的連接被關閉時，所有由當前線程鎖定的表被隱含地解鎖
另一類表級的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對表做結構變更操作的時候，加 MDL 寫鎖。讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。

共享鎖（讀鎖）：其他事務可以讀，但不能寫。
排他鎖（寫鎖）：其他事務不能讀取，也不能寫。

image.png

我們可以看到 session A 先啟動，這時候會對表 t 加一個 MDL 讀鎖。由于 session B 需要的也是 MDL 讀鎖，因此可以正常執(zhí)行。之后 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。如果只有 session C 自己被阻塞還沒什么關系，但是之后所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞。前面我們說了，所有對表的增刪改查操作都需要先申請 MDL 讀鎖，就都被鎖住，等于這個表現在完全不可讀寫了。如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時后會再起一個新 session 再請求的話，這個庫的線程很快就會爆滿。你現在應該知道了，事務中的 MDL 鎖，在語句執(zhí)行開始時申請，但是語句結束后并不會馬上釋放，而會等到整個事務提交后再釋放。

首先我們要解決長事務，事務不提交，就會一直占著 MDL 鎖。在 MySQL 的 information_schema 庫的 innodb_trx 表中，你可以查到當前執(zhí)行中的事務。如果你要做 DDL 變更的表剛好有長事務在執(zhí)行，要考慮先暫停 DDL，或者 kill 掉這個長事務。熱點表kill 可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在 alter table 語句里面設定等待時間，如果在這個指定的等待時間里面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞后面的業(yè)務語句，先放棄。之后開發(fā)人員或者 DBA 再通過重試命令重復這個過程。MariaDB 已經合并了 AliSQL 的這個功能，所以這兩個開源分支目前都支持 DDL NOWAIT/WAIT n 這個語法。

小結：全局鎖主要用在邏輯備份過程中。對于全部是 InnoDB 引擎的庫，我建議你選擇使用–single-transaction 參數，對應用會更友好。表鎖一般是在數據庫引擎不支持行鎖的時候才會被用到的。如果你發(fā)現你的應用程序里有 lock tables 這樣的語句，你需要追查一下，比較可能的情況是：要么是你的系統(tǒng)現在還在用 MyISAM 這類不支持事務的引擎，那要安排升級換引擎；要么是你的引擎升級了，但是代碼還沒升級，把 lock tables 和 unlock tables 改成 begin 和 commit，問題就解決了。

3、行鎖：

在 InnoDB 事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協(xié)議。如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放。

另外還有間隙鎖，間隙鎖和行鎖合稱 next-key lock。下面繼續(xù)介紹。

4、死鎖和死鎖檢測

當并發(fā)系統(tǒng)中不同線程出現循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致這幾個線程都進入無限等待的狀態(tài)，稱為死鎖。

當出現死鎖以后，有兩種策略：

一種策略是，直接進入等待，直到超時。這個超時時間可以通過參數 innodb_lock_wait_timeout 來設置。
另一種策略是，發(fā)起死鎖檢測（常用），發(fā)現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續(xù)執(zhí)行。將參數 innodb_deadlock_detect 設置為 on，表示開啟這個邏輯。

在 InnoDB 中，innodb_lock_wait_timeout 的默認值是 50s，對于在線服務來說，這個等待時間往往是無法接受的。所以，正常情況下我們還是要采用第二種策略，即：主動死鎖檢測，而且 innodb_deadlock_detect 的默認值本身就是 on。主動死鎖檢測在發(fā)生死鎖的時候，是能夠快速發(fā)現并進行處理的，但是它也是有額外負擔的。

怎么解決由這種熱點行更新導致的性能問題呢？
問題的癥結在于，死鎖檢測要耗費大量的 CPU 資源。一種頭痛醫(yī)頭的方法，就是如果你能確保這個業(yè)務一定不會出現死鎖，可以臨時把死鎖檢測關掉。但是這種操作本身帶有一定的風險，可能會出現大量的超時，這是業(yè)務有損的。另一個思路是控制并發(fā)度。這個并發(fā)控制要做在數據庫服務端。如果你有中間件，可以考慮在中間件實現；如果你的團隊有能修改 MySQL 源碼的人，也可以做在 MySQL 里面。基本思路就是，對于相同行的更新，在進入引擎之前排隊。這樣在 InnoDB 內部就不會有大量的死鎖檢測工作了。

如果出現死鎖，可以用 SHOW INNODB STATUS 命令來確定最后一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發(fā)死鎖的 SQL 語句，事務已經獲得的鎖，正在等待什么鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。

MyISAM避免死鎖：
在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，所以 MyISAM 表不會出現死鎖。

三、幻讀、臟讀

1、幻讀

1）什么是幻讀

簡單理解就是事務A讀到了事務B新插入的數據，導致數據不一致。

2）產生幻讀的原因

行鎖只能鎖住行，但是新插入記錄這個動作，要更新的是記錄之間的“間隙”。因此，為了解決幻讀問題，InnoDB 只好引入新的鎖，也就是間隙鎖 (Gap Lock)。跟間隙鎖存在沖突關系的，是“往這個間隙中插入一個記錄”這個操作。間隙鎖之間都不存在沖突關系。

==間隙鎖和行鎖合稱 next-key lock，間隙鎖是開區(qū)間，每個 next-key lock 是前開后閉區(qū)間==。
間隙鎖的引入，可能會導致同樣的語句鎖住更大的范圍，這其實是影響了并發(fā)度的。

Eg:如圖

在這里插入圖片描述

在這里插入圖片描述

這里 session B 并不會被堵住。因為表 t 里并沒有 c=7 這個記錄，因此 session A 加的是間隙鎖 (5,10)。而 session B 也是在這個間隙加的間隙鎖。如果用 select * from t for update 要把整個表所有記錄鎖起來，就形成了 7 個 next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

注：間隙鎖可能導致死鎖:

在這里插入圖片描述

在這里插入圖片描述

3）解決幻讀方式

a.間隙鎖是在可重復讀隔離級別下才會生效的。所以可重復讀隔離級別+間隙鎖
b.讀提交隔離級別加 binlog_format=row 的組合。

第二種方式解釋一下：
MySQL 通過 BINLOG 錄入執(zhí)行成功的 INSERT、UPDATE、DELETE 等更新數據的 SQL 語句，并由此實現 MySQL 數據庫的恢復和主從復制。MySQL 的恢復機制（復制其實就是在 Slave Mysql 不斷做基于 BINLOG 的恢復）有以下特點：

一是 MySQL 的恢復是 SQL 語句級的，也就是重新執(zhí)行 BINLOG 中的 SQL 語句。
二是 MySQL 的 Binlog 是按照事務提交的先后順序記錄的，恢復也是按這個順序進行的。

MySQL 的恢復機制要求：在一個事務未提交前，其他并發(fā)事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀。

2、臟讀

1）臟讀(Dirty Read)

臟頁：當內存數據頁跟磁盤數據頁內容不一致的時候，我們稱這個內存頁為“臟頁”。也就是緩沖池中已經被修改，但是還沒有刷新到磁盤。

臟數據：是指事務對緩沖池中行記錄的修改，并且還沒有被提交。

臟讀指的就是在不同事務下，當前事務可以讀到另外事務未提交的數據，簡單來說就是可以讀到臟數據。（臟讀現象的發(fā)生條件是需要事務的隔離級別為READ UNCOMMITTED）

2）刷臟頁

內存數據寫入到磁盤后，內存和磁盤上的數據頁的內容就一致了，稱為“干凈頁”。平時執(zhí)行很快的更新操作，其實就是在寫內存和日志，而 MySQL 偶爾“抖”一下的那個瞬間，可能就是在刷臟頁（flush）。

（1）什么時候刷呢

第一個場景對應的就是 InnoDB 的 redo log 寫滿了。這時候系統(tǒng)會停止所有更新操作，就需要將兩個點之間的日志，對應的所有臟頁都 flush 到磁盤上，把 checkpoint 往前推進，redo log 留出空間可以繼續(xù)寫。
第二個場景對應的就是系統(tǒng)內存不足。當需要新的內存頁，而內存不夠用的時候，就要淘汰一些數據頁，空出內存給別的數據頁使用。如果淘汰的是“臟頁”，就要先將臟頁寫到磁盤。
第三個場景，對應的就是 MySQL 認為系統(tǒng)“空閑”的時候。
第四個場景，對應的就是 MySQL 正常關閉的情況。這時候，MySQL 會把內存的臟頁都 flush 到磁盤上，這樣下次 MySQL 啟動的時候，就可以直接從磁盤上讀數據，啟動速度會很快。

（2）四種場景對性能的影響

第三種情況是屬于 MySQL 空閑時的操作，這時系統(tǒng)沒什么壓力。而第四種場景是數據庫本來就要關閉了。這兩種情況下，你不會太關注“性能”問題。

第一種是“redo log 寫滿了，要 flush 臟頁”，這種情況是 InnoDB 要盡量避免的。因為出現這種情況的時候，整個系統(tǒng)就不能再接受更新了，所有的更新都必須堵住。

第二種是“內存不夠用了，要先將臟頁寫到磁盤”，這種情況其實是常態(tài)。InnoDB 用緩沖池（buffer pool）管理內存，緩沖池中的內存頁有三種狀態(tài)：第一種是，還沒有使用的；第二種是，使用了并且是干凈頁；第三種是，使用了并且是臟頁。InnoDB 的策略是盡量使用內存，因此對于一個長時間運行的庫來說，未被使用的頁面很少。而當要讀入的數據頁沒有在內存的時候，就必須到緩沖池中申請一個數據頁。這時候只能把最久不使用的數據頁從內存中淘汰掉：如果要淘汰的是一個干凈頁，就直接釋放出來復用；但如果是臟頁呢，就必須將臟頁先刷到磁盤，變成干凈頁后才能復用。

（3）InnoDB 刷臟頁的控制策略

InnoDB 的刷盤速度就是要參考這兩個因素：一個是臟頁比例，一個是 redo log 寫盤速度。
==參數 innodb_max_dirty_pages_pct 是臟頁比例上限，默認值是 75%==。InnoDB 會根據當前的臟頁比例（假設為 M），算出一個范圍在 0 到 100 之間的數字。InnoDB 每次寫入的日志都有一個序號，當前寫入的序號跟 checkpoint 對應的序號之間的差值，我們假設為 N。InnoDB 會根據這個 N 算出一個范圍在 0 到 100 之間的數字，這個計算公式可以記為 F2(N)。根據上述算得的 F1(M) 和 F2(N) 兩個值，取其中較大的值記為 R，之后引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度。平時要多關注臟頁比例，不要讓它經常接近 75%。

臟頁比例是通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的。

一旦一個查詢請求需要在執(zhí)行過程中先 flush 掉一個臟頁時，這個查詢就可能要比平時慢了。而 MySQL 中的一個機制，可能讓你的查詢會更慢：在準備刷一個臟頁的時候，如果這個數據頁旁邊的數據頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續(xù)蔓延，也就是對于每個鄰居數據頁，如果跟它相鄰的數據頁也還是臟頁的話，也會被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 參數就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的。在 MySQL 8.0 中，innodb_flush_neighbors 參數的默認值已經是 0 了，減少 SQL 語句響應時間。

eg:我這是低版本，設置0時情況：

image.png

四、加鎖規(guī)則

這個規(guī)則說明：MySQL 后面的版本可能會改變加鎖策略，所以這個規(guī)則只限于截止到現在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。
包含了兩個“原則”、兩個“優(yōu)化”和一個“bug”。
原則 1：加鎖的基本單位是 next-key lock。希望你還記得，next-key lock 是前開后閉區(qū)間。
原則 2：查找過程中訪問到的對象才會加鎖。
優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。
優(yōu)化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。
一個 “bug”：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

五、數據庫表的空間回收

一個 InnoDB 表包含兩部分，即：表結構定義和數據。在 MySQL 8.0 版本以前，表結構是存在以.frm 為后綴的文件里。而 MySQL 8.0 版本，則已經允許把表結構定義放在系統(tǒng)數據表中了。

1、nnodb_file_per_table

參數 innodb_file_per_table表示的是表數據既可以存在共享表空間里，也可以是單獨的文件。這個行為是由參數 innodb_file_per_table 控制的：
（1）這個參數設置為 OFF 表示的是，表的數據放在系統(tǒng)共享表空間，也就是跟數據字典放在一起；
（2）這個參數設置為 ON 表示的是，每個 InnoDB 表數據存儲在一個以 .ibd 為后綴的文件中。從 MySQL 5.6.6 版本開始，它的默認值就是 ON 了。

建議你不論使用 MySQL 的哪個版本，都將這個值設置為 ON。因為，一個表單獨存儲為一個文件更容易管理，而且在你不需要這個表的時候，通過 drop table 命令，系統(tǒng)就會直接刪除這個文件。而如果是放在共享表空間中，即使表刪掉了，空間也是不會回收的。

image.png

2、數據頁的復用及空洞

如果相鄰的兩個數據頁利用率都很小，系統(tǒng)就會把這兩個頁上的數據合到其中一個頁上，另外一個數據頁就被標記為可復用。如果我們用 delete 命令把整個表的數據刪除呢？結果就是，所有的數據頁都會被標記為可復用，但磁盤文件的大小是不會變的。也就是說，通過 delete 命令是不能回收表空間的。
如果數據是按照索引遞增順序插入的，那么索引是緊湊的。但如果數據是隨機插入的，就可能造成索引的數據頁分裂。

在這里插入圖片描述

更新索引上的值，可以理解為刪除一個舊的值，再插入一個新值。不難理解，這也是會造成空洞的。也就是說，經過大量增刪改的表，都是可能是存在空洞的。而重建表，就可以達到這樣的目的。

3、重建表

這里，你可以使用 alter table A engine=InnoDB 命令來重建表。在 MySQL 5.5 版本之前，這個命令的執(zhí)行流程跟我們前面描述的差不多，區(qū)別只是這個臨時表 B 不需要你自己創(chuàng)建，MySQL 會自動完成轉存數據、交換表名、刪除舊表的操作。因此，在整個 DDL 過程中，表不能有更新，不然就會造成數據丟失。也就是說，這個 DDL 不是 Online 的。

MySQL 5.6 版本開始引入的 Online DDL，對這個操作流程做了優(yōu)化。

重建表的流程：建立一個臨時文件，掃描表 A 主鍵的所有數據頁；用數據頁中表 A 的記錄生成 B+ 樹，存儲到臨時文件中；生成臨時文件的過程中，將所有對 A 的操作記錄在一個日志文件（row log）中，對應的是圖中 state2 的狀態(tài)；臨時文件生成后，將日志文件中的操作應用到臨時文件，得到一個邏輯數據上與表 A 相同的數據文件，對應的就是圖中 state3 的狀態(tài)；用臨時文件替換表 A 的數據文件。