【八股】Mysql中小廠八股

MySQL

基礎

數據庫三大范式（中）

第一范式: 要求數據庫表的每一列都是不可分割的原子數據項
- 如詳細地址可以分割為省市區等.
第二范式: 非主鍵屬性必須完全依賴于主鍵, 不能部分依賴
- 第二范式要確保數據庫表中的每一列都和主鍵相關, 而不能只與主鍵的某一部分相關
第三范式: 任何非主鍵屬性不依賴于其它非主鍵屬性
- 三范式需要確保數據表中的每一列數據都和主鍵直接相關, 而不能間接相關, 避免傳遞依賴
- 一張表, 有學生 id, 學號, 姓名, 年齡, 班主任姓名, 班主任年齡
- 此時班主任年齡依賴于班主任姓名或者班主任 id, 不應該依賴于學生 id, 所以這就是間接相關.

數據庫三范式也并不是必須遵守的，適當添加余信息，可以減少多表查詢，提高效率

CHAR 和 VARCHAR 有什么區別？ (中)

CHAR 是固定長度的字符串類型, 定義時需要指定固定長度, 存儲時會在末尾補足空格。CHAR 適合存儲長度固定的數據, 如固定長度的代碼、狀態等, 存儲空間固定, 對于短字符串效率較高。
VARCHAR 是可變長度的字符串類型，定義時需要指定最大長度，實際存儲時根據實際長度占用存儲空間。VARCHAR 適合存儲長度可變的數據，如用戶輸入的文本、備注等，節約存儲空間。

SQL 語句的執行順序(中)

當一個查詢語句同時出現了 where,group by,having,orderby 的時候

編寫順序

1 select 字段 2 from 表名 3 where 條件列表 4 group by 分組條件 5 having 分組后篩選 6 order by 排序條件 7 limit 條數

實際執行順序：

執行 from 查看表
執行 where xx 對全表數據做篩選，返回第 1 個結果集。
針對第 1 個結果集使用 group by 分組，返回第 2 個結果集。
針對第 2 個結果集執行 having xx 進行篩選，返回第 3 個結果集。
針對第 3 個結果集執行 select xx，返回第 4 個結果集。
針對第 4 個結果集 orderby 排序，返回第 5 個結果集
針對第 5 個結果集使用 limit 進行條數限制，返回第 6 個結果集

架構/引擎

SQL 語句的執行過程/MySQL 架構是什么(中)

取得鏈接，使用到 MySQL 中的連接器

連接的過程需要先經過 TCP 三次握手，因為 MySQL 是基于 TCP 協議進行傳輸的。校驗客戶端的用戶名和密碼。校驗用戶權限

·查詢緩存，key 為 SQL 語句，value 為查詢結果，如果查到就直接返回。不建議使用此緩存，在 MySQL8.0 版本已經將查詢緩存刪除，也就是說 MySQL8.0 版本后不存在此功能

更新比較頻繁的表，查詢緩存的命中率很低的，因為只要一個表有更新操作，那么這個表的查詢緩存就會被清空

·分析器，分為詞法分析和語法分析。詞法分析就是提取 sql 語句關鍵字，語法分析就是語法校驗，構建 SQL 語法樹，方便后續模塊讀取表名、字段、語句類型、

執行階段

預處理：

檢查 SQL 查詢語句中的表或者字段是否存在；
將 select _ 中的 _ 符號，擴展為表上的所有列；

優化階段(優化器)：

在表里有多個索引的時候，決定使用哪個索引；
或者一個語句中存在多表關聯的時候（join），決定各個表的連接順序

執行階段（執行器）：據表的引擎定義(Innodb 或者 MyISAM)，去使用這個引擎提供的接口

數據庫存儲引擎有哪些（高）

innodb

事務, 外鍵, 行級鎖- 適合事務要求高, 數據完整性高的場景

MyISAM

全表鎖, 不支持事務, 不支持外鍵, 并發性低- 適合對事務要求不高, 數據完整性要求不高, 并發性要求不高的場景

memory

全表鎖，數據存儲在內存中, 默認使用 hash 索引, 檢索速度非常高.- 適合做緩存 (被 redis 代替)

innodb 和 MyISAM 的區別（高）

Innodb 有事務外鍵行級鎖- InnoDB 支持數據庫異常崩潰后的安全恢復, 依賴于 redo log, 而 MyISAM 不支持- innodb 支持 MVCC, MyISAM 不支持

索引（常考）

MySQL 中的索引類型（高）

邏輯維度

主鍵索引: 針對表主鍵的索引, 默認創建, 只能有一個- 唯一索引: 避免一個表中的索引重復, 可以多個- 常規索引: 快速定位數據, 可以多個- 前綴索引: 在文本類型如 CHAR, VARCHAR, TEXT 類列上創建索引時, 可以指定索引列的長度. 但是數值類型不能指定長度- 聯合索引: 多個列組合的索引 (最左前綴匹配原則, 索引下推, 避免回表, select *)- 全文索引: 查找文本中的關鍵詞. 像 es 一樣. 可以多個

innodb 中根據索引的物理存儲形式，又可以分為兩種

聚集索引: 一般主鍵索引就是聚集索引, 且只有一個. 索引的葉子節點是 id, id 下推了行數據.- 二級索引: 索引的葉子節點是該列的值, 下面掛了 id
如果走二級索引, 那么就先從二級索引中拿到 id, 再根據 id 從聚集索引中查行數據. 這個過程叫回表, 一般要避免回表(不要使用 select *).

為什么 InnoDB 存儲引擎選擇使用 B+樹索引結構？(高)

相對于二叉樹, 層級更少, 搜索效率高;- B 樹無論是葉子節點還是非葉子節點, 都會保存數據, 這樣導致一頁中存儲的鍵值減少, 指針跟著減少, 要同樣保存大量數據, 只能增加樹的高度, 導致性能降低;
相對 Hash 索引， $B+\mathsf{B}+$ 樹支持范圍匹配及排序操作；

什么是覆蓋索引/什么是回表（高）

如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為覆蓋索引（Covering Index）

在 InnoDB 存儲引擎中，非主鍵索引的葉子節點包含的是主鍵的值- 這意味著，當使用非主鍵索引進行查詢時，數據庫會先找到對應的主鍵值，然后再通過主鍵索引來定位和檢索完整的行數據。這個過程被稱為“回表”

覆蓋索引即需要查詢的字段正好是索引的字段，那么直接根據該索引，就可以查到數據了，而無需回表查詢。

為了盡可能避免回表，所以往往會使用聯合索引

索引的使用原則/索引失效場景（高）

（有非常多的場景和原則，這里給出最常見的）

最左前綴匹配原則
如果索引了多列（聯合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。如果跳躍某一列，索引將部分失效（后面的字段索引失效）

聯合索引（a，b，c） 3 where a=1 and b=2 # 走聯合索引 4 where b=2 and a=1 # 走聯合索引，不會因為 where 后的字段順序就失效 5 where b=2 and c=1 # 不會走聯合索引，因為 a 字段在索引最左側，where 中沒有 a 6 where c=1 and a=2 # 會走聯合索引關于 a 的部分

索引列運算/函數會使索引失效；

where substring(phone, 10, 2) = ‘12’
模糊查詢
where name like ‘王%’ 尾部模糊，索引生效- where name like ‘王%王’ 尾部模糊，索引生效- where name like ‘%三’ 頭部模糊，索引失效
覆蓋索引
盡量使用覆蓋索引（查詢使用了索引，并且需要返回的列，在該索引中已經全部能夠找到），避免回表
在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會失效。

創建聯合索引時需要注意什么？（高）

最左前綴匹配法則
把區分度大的字段排在前面性能會更高，把性別這種區分度小的字段應該放在后面。

什么情況不走聯合索引？（高）

設置聯合索引(a,b,c)，查詢條件如下

where $a = 1$ and $b = 2$
會走聯合索引，符合最左前綴匹配法則
where $b = 2$ and $a = 1$
會走聯合索引，符合最左前綴匹配法則.注意，最左前綴匹配法則與 sql 書寫順序無關
where $b = 2$ and $c = 1$
不會走聯合索引，不符合最左前綴匹配法則，最左邊的 a 不在
where $a = 1$ and $c = 2$
會走聯合索引，符合最左前綴匹配法則.但是只會走 a 這部分的索引，無法走 c 部分的索引，因為沒有 b.

索引的優缺點？（高）

索引最大的好處是提高查詢速度，但是索引也是有缺點的

需要占用物理空間，數量越大，占用空間越大；- 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增大；- 會降低表的增刪改的效率，因為每次增刪改索引，B+樹為了維護索引有序性，都需要進行動態維護。

索引不是萬能的，它也是根據場景來使用的

索引設計原則（高）

數據量大的, 查詢頻繁的列建立索引
對于經常 where, order by, group by 的列建立索引
選擇區分度高的列做索引. 身份證號適合索引, 性別和狀態不適合索引
字符串類型且比較長的, 可以使用前綴索引
盡量使用聯合索引, 而不是單列索引, 聯合索引很多時候可以覆蓋索引, 避免回表
控制索引數量, 索引不是多多益善, 太多了占空間, 維護索引需要的代價也越多, 增刪改反而會比較慢.

常見 sql 優化手段（高）

查詢語句中不要使用 select, 避免回表查詢
數據庫主鍵要保證自增(UUID 不適合做主鍵), 且插入的數據主鍵也要交保證自增插入, 否則會引起頁分裂
盡量避免在 where 子句中使用!=或<>操作符, 否則將引擎放棄使用索引而進行全表掃描
盡量避免在 where 子句中對字段進行 null 值判斷, 否則將導致引擎放棄使用索引而進行全表掃描
使用 update 時, where 條件盡量用帶索引的字段, 上行鎖. InnoDB 的行鎖是針對索引加的鎖, 不是針對記錄加的鎖.
count(_)效率最高, 因為 innodb 做了優化.
表關聯查詢的效率高于子查詢, 所以盡量少用子查詢, 用關聯查詢替代.
關聯查詢時, on 的條件列最好加上索引, 否則非常慢

SQL 優化詳解(中)

插入優化insert 優化

批量插入

Insert into tb_test values(1,'Tom'),(2,'cat'),(3,'Jerry');

批量插入每次插入500~1000比較好關于1000就分幾次批量插入

手動提交事務

start transaction; insert into tb_test values(1,‘Tom’),(2,‘cat’),(3,‘Jerry’); insert into tb_test values(4,‘Tom’),(3,‘cat’),(6,‘Jerry’); insert into tb_test values(7,‘Tom’),(8,‘cat’),(9,‘Jerry’); commit;

主鍵順序插入

主鍵順序插入主鍵亂序插入：8 1 9 21 88 2 4 15 89 5 7 3 主鍵順序插入：1 2 3 4 5 7 8 9 15 21 88 89

主鍵順序插入的效率高于亂序插入

大批量插入數據

如果一次性需要插入大批量數據，使用 insert 語句插入性能較低，此時可以使用 MySQL 數據庫提供的 load 指令進行插入。操作如下：

客戶端連接服務端時，加上參數- local- infilemysql- local- infile- - u root- p#設置全局參數 local infile 為 1，開啟本地加載文件導入數據的開關 set global local infile $= 1$ #執行 load 指令將準備好的數據，加載到該結構中 load data local infile /root/sq1. log into table tb_user fields terminated by $↑,\uparrow ,$ lines terminated by $↑,↑,\uparrow ,\uparrow ,$

主鍵順序插入性能高于亂序插入

主鍵優化

主鍵亂序會導致頁分裂問題，性能較差

頁分裂

頁可以為空，也可以填充一半，也可以填充 $100%100\%$ 。每個頁包含了 2- N 行數據（如果一行數據多大，會行溢出），根據主鍵排列。

主鍵設計原則

滿足業務需求的情況下，盡量降低主鍵的長度。二級索引有很多個，二級索引的葉子節點存儲了主鍵當主鍵太長時，很浪費存儲空間插入數據時，盡量選擇順序插入，選擇使用 AUTO_INCREMENT 自增主鍵。主鍵順序插入效率高盡量不要使用 UUID 做主鍵或者是其他自然主鍵，如身份證號。UUID 和身份證都不是自增的，都是無序的，所以插入效率低業務操作時，避免對主鍵的修改。

order by 優化

$①$ .Using filesort：通過表的索引或全表掃描，讀取滿足條件的數據行，然后在排序緩沖區 sort buffer 中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。 $②$ .Using index：通過有序索引順序掃描直接返回有序數據，這種情況即為 using index，不需要額外排序，操作效率高。

根據排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則。

盡量使用覆蓋索引。

多字段排序，一個升序一個降序，此時需要注意聯合索引在創建時的規則（ASC/DESC）。

如果不可避免的出現 filesort，大數據量排序時，可以適當增大排序緩沖區大小 sort_buffer_size（默認 256k）。

group by 優化

在分組操作時，可以通過索引來提高效率。

分組操作時，索引的使用也是滿足最左前綴法則的。

limit 優化

有張 blog 表, 字段 id, content, create_time, …, 需要分頁按創建時間倒序顯示 blog

常規分頁查詢 sql: select * from blog order by create_time desc limit 2000000, 10

這里 mysql 需要排序前面的 2000010 條記錄, 然后丟棄其他記錄, 只返回 2000000~2000010 條記錄. 所以存在嚴重的性能問題.

優化: 建立排序列索引 + id 子查詢避免回表

建立 create_time 的索引
使用子查詢分頁 select * from blog inner join (select id from blog order by create_time desc limit 2000000, 10)

一個常見又非常頭疼的問題就是 limit 2000000, 10, 此時需要 MySQL 排序前 2000010 記錄, 僅僅返回 2000000 - 2000010 的記錄, 其他記錄丟棄, 查詢排序的代價非常大。

優化思路: 一般分頁查詢時, 通過創建覆蓋索引能夠比較好地提高性能, 可以通過覆蓋索引加子查詢形式進行優化。

原 sql: select * from user limit 200000, 10

優化后的 sql: select * from user inner join (select id from user limit 2000000, 10) uid on user: id = uid: id

幾種 count 對比

count 的幾種用法

count（主鍵）

InnoDB 引擎會遍歷整張表，把每一行的主鍵 id 值都取出來，返回給服務層。服務層拿到主鍵后，直接按行進行累加(主鍵不可能為 null)。

count（字段）

沒有 not null 約束：InnoDB 引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，服務層判斷是否為 null，不為 null，計數累加。有 not null 約束：InnoDB 引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，直接按行進行累加。

count（1）

InnoDB 引擎遍歷整張表，但不取值。服務層對于返回的每一行，放一個數字“1”進去，直接按行進行累加。

count（*）

InnoDB 引擎并不會把全部字段取出來，而是專門做了優化，不取值，服務層直接按行進行累加。

按照效率排序的話，count（字段）<count（主鍵 id）<count（1）≈count（*），所以盡量使用 count（*）。

update 優化

InnoDB 的行鎖是針對索引加的鎖，不是針對記錄加的鎖。如果 where 條件是不帶索引的字段，那么就會是表鎖。如果 where 條件是帶索引的字段，那么是行鎖。并且該索引不能失效，否則會從行鎖升級為表鎖。表鎖的并發性能低在有事務的情況下，update 進行更新的時候，

在有事務的情況下，update 進行更新的時候

如果 where 條件是 id 等帶索引的字段，則 update 會對該上行鎖。那么其他事務不能對該行進行操作，但是可以對該表的其他行進行操作。2. 如果 where 條件是不帶索引的字段，則 update 會上表鎖，其他事務對整張表都不能進行操作（會阻塞）。因此，使用 update 時，where 條件盡量用帶索引的字段，上行鎖。

因此，使用 update 時，where 條件盡量用帶索引的字段，上行鎖

where 優化

應盡量避免在 where 子句中使用 $! =$ 或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null 可以在 num 上設置默認值 0，確保表中 num 列沒有 null 值，然后這樣查詢：select id from t where num = 0

什么時候不要使用索引（高）

經常增刪改的列不要建立索引
有大量重復的列不建立索引
表記錄太少不要建立索引

索引下推 (中)

可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，然后再去做回表，從而減少了回表次數，提升了性能。

組合索引滿足最左匹配，但是遇到非等值判斷時匹配停止。name like ‘陳%’ 不是等值匹配，所以 age = 20 這里就用不上 (name, age) 組合索引了。如果沒有索引下推，組合索引只能用到 name，age 的判定就需要回表才能做了。5.6 之后有了索引下推，age = 20 可以直接在組合索引里判定
舉例說明

假設存在表 user，其索引為（name，age），查詢語句如下：

1 SELECT * 2 FROM user 3 WHERE name LIKE ‘王%’ 4 AND age = 30;

聯合索引先按 name 排序，name 一樣再按 age 排序，如果是 name=“張三” and age > 18，這個就能使用聯合索引的所有列. 不需要索引下推.

無索引下推：

這里走聯合索引先篩選出姓名以王開頭的用戶- 由于這里是模糊匹配，不是等值匹配，故獲取所有以王開頭的用戶后，他們的 age 不一定有序的.

如[張一，20]，[張二，18]，[張三，30]。所以無法繼續使用聯合索引的特性來篩選 age，只能拿到以王開頭的用戶的 id，去回表，然后再篩選出 age $= 30$ 的人.

有索引下推：

走聯合索引先篩選出姓名以王開頭且同時 age $= 30$ 的用戶
結果為[張三，30]
回表只需要根據張三的 id 查即可可以看到使用了索引下推后，大大減少了回表操作。新手可能不是很理解，建議配合視頻或者網上博客理解。

怎么找到慢 sql？可以從哪些角度優化？(高)

尋找慢 sql：

打開慢查詢日志- 使用 explain 執行計劃來對慢 SQL 進行分析，查詢是否使用了索引(sql 語句前加上 explain 即可)

sql 優化：

避免使用 select ，避免查詢不需要的列- 嘗試給 where, order by, limit 后面的列添加索引- 如果有索引，避免給重復值很多的列添加索引- 添加索引盡量使用聯合索引，盡量覆蓋索引，避免回表- 對于 update 和 delete 慢，應該 where 后面跟索引列，使用行鎖，避免使用表鎖- 對于 insert 慢，應該使用遞增的主鍵，避免頁分裂- 如果有 limit，應該先查 id，再根據 id 查詢（覆蓋索引+子查詢優化）- 盡量使用 count()
避免索引失效
數據量大使用分庫分表
可以加緩存, 加 es

什么是慢查詢日志（slowquerylog）（高）

慢查詢日志記錄了執行時間超過 long_query_time（默認是 10s，通常設置為 1s）的所有查詢語句，在解決 SQL 慢查詢（SQL 執行時間過長）問題的時候經常會用到

找到慢 SQL 是優化 SQL 語句性能的第一步，然后再用 EXPLAIN 命令可以對慢 SQL 進行分析，獲取執行計劃的相關信息

explain 執行計劃（高）

1 #先執行一條 sql 2 select _ from user; 3 4 #在該 sql 前加上 explain 關鍵字 5 explain select _ from user;

EXPLAIN 執行計劃各字段含義：

Id

select 查詢的序列號，表示查詢中執行 select 子句或者是操作表的順序(id 相同，執行順序從上到下；id 不同，值越大，越先執行)。

select_type

表示 SELECT 的類型，常見的取值有 SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、

Key 實際使用的索引，如果為 NULL，則沒有使用索引。> Key_len 表示索引中使用的字節數，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好。> rowsMySQL 認為必須要執行查詢的行數，在 innodb 引擎的表中，是一個估計值，可能并不總是準確的。> filtered 表示返回結果的行數占需讀取行數的百分比，filtered 的值越大越好。最后一個字段 Extra，代表額外信息，額外信息會展示沒有查到的信息

重要點關注:type,prossible_key,key,key_len,extra.

發現查詢速度很慢，怎么解決 (高)

分析查詢語句：使用 EXPLAIN 命令分析 SQL 執行計劃，找出慢查詢的原因，比如是否使用了全表掃描，是否存在索引未被利用的情況等，并根據相應情況對索引進行適當修改。
創建或優化索引：根據查詢條件創建合適的索引，特別是經常用于 WHERE 子句的字段、Orderby 排序的字段、Join 連表查詢的字典、groupby 的字段，并且如果查詢中經常涉及多個字段，考慮創建聯合索引
避免索引失效：比如不要用左模糊匹配、函數計算、表達式計算等等。
查詢優化：避免使用 SELECT *, 只查詢真正需要的列；使用覆蓋索引，即索引包含所有查詢的字段；聯表查詢最好要以小表驅動大表，并且被驅動表的字段要有索引，當然最好通過穴余字段的設計，避免聯表查詢。
分頁優化：針對深分頁的查詢優化
優化數據庫表：如果單表的數據超過了千萬級別，考慮是否需要將大表拆分為小表，減輕單個表的查詢壓力。也可以將字段多的表分解成多個表，有些字段使用頻率高，有些低，數據量大時，會由于使用頻率低的存在而變慢，可以考慮分開
使用緩存技術：引入緩存，存儲熱點數據和頻繁查詢的結果

Explain 發現執行的索引不正確的話，怎么辦？(高)

可以使用 forceindex，強制走索引

事務（常考）

什么是數據庫事務/事務四大特性 (高)

事務：一系列 sql 語句，要么全成功，要么全失敗

原子性(Atomicity)：事務是不可分割的最小單元，n 個連續操作失敗了一個，前面的操作回滾(要么都成功，要么都失敗)

原子性通過 undolog 回滾來實現

一致性(Consistency)：執行事務前后，數據總量保持一致.例如轉賬業務中，無論事務是否成功，轉賬者和收款人的總額應該是不變的；

保證了其他三個特性，一致性就自然實現了，

持久性(Durability)：持久性是指一個事務一旦被提交，它對數據庫中數據的改變就是永久性的，無法撤銷

redolog 來實現

隔離性(Isolation): 多個用戶并發訪問數據庫時，數據庫為每一個用戶開啟的事務，不能被其他事務的操作數據所干擾，多個并發事務之間要相互隔離，保證每個事務不受并發影響，獨立執行。

mvcc+鎖配合 undolog 來實現

隔離性產生的問題（高）

臟讀指讀取到其他事務正在處理的未提交數據
不可重復讀指并發更新時，另一個事務前后查詢相同數據時的數據不符合預期
幻讀指并發新增、刪除這種會產生數量變化的操作時，另一個事務前后查詢相同數據時的不符合預期

事務的隔離級別 (高)

為了解決以上的問題，主流的關系型數據庫都會提供四種事務的隔離級別。事務隔離級別從低到高分別是：讀未提交、讀已提交、可重復讀、串行化。

事務隔離級別等級越高，越能保證數據的一致性和完整性，但是執行效率也越低。

所以在設置數據庫的事務隔離級別時需要做一下權衡，MySQL 默認是可重復讀的級別。

讀未提交(Read Uncommitted)，是最低的隔離級別，所有的事務都可以看到其他未提交的事務的執行結果。不能解決臟讀，可重復讀，幻讀，所以很少應用于實際項目。
讀已提交(Read Committed)，在該隔離級別下，一個事務的更新操作結果只有在該事務提交之后，另一個事務才可能讀取到同一筆數據更新后的結果。- 可以防止臟讀，但是不能解決可重復讀和幻讀的問題。
可重復讀(Repeatable Read)，MySQL 默認的隔離級別。
- 在該隔離級別下, 一個事務多次讀同一個數據, 實際上讀的是數據快照, 其他事務修改數據在當前事務是不可見的, 這樣就可以保證在同一個事務內兩次讀到的數據是一樣的
- 可以防止臟讀、不可重復讀、第一類更新丟失、第二類更新丟失的問題, 不過還是會出現幻讀。
串行化(Serializable), 這是最高的隔離級別。
- 它要求事務序列化執行, 事務只能一個接著一個地執行, 不能并發執行(會阻塞)。
- 在這個級別, 可以解決上面提到的所有并發問題, 但可能導致大量的超時現象和鎖競爭, 通常不會用這個隔離級別

注意: 事務的隔離級別越高, 數據安全性就越高, 但是執行效率越低。事務的隔離級別越低, 執行效率就越高, 但是數據安全性就越低。

MySQL 的隔離級別怎么實現的 (中)

MySQL 的隔離級別基于鎖和 MVCC 機制共同實現的。

SERIALIZABLE 隔離級別, 是通過鎖來實現的
除了 SERIALIZABLE 隔離級別, 其他的隔離級別都是基于 MVCC 實現
不過, SERIALIZABLE 之外的其他隔離級別可能也需要用到鎖機制, 就比如 REPEATABLE-READ 在當前讀情況下需要使用加鎖讀來保證不會出現幻讀

單條 update 語句是原子性的嗎？（中）

是原子性的

主要通過鎖+undolog 日志保證原子性的

執行 update 的時候，會加行級別鎖，保證了一個事務更新一條記錄的時候，不會被其他事務干擾。- 事務執行過程中，會生成 undolog,如果事務執行失敗，就可以通過 undolog 日志進行回滾。

MVCC

什么是 MVCC（中）

MVCC,多版本并發控制
指維護一個數據的多個版本，使得讀寫操作沒有沖突
MVCC 的具體實現依賴于數據庫記錄中的隱式字段、undolog 日志、ReadView

MVCC 可以為數據庫解決什么問題（中）

在并發讀寫數據庫時，可以做到在讀(select)操作時不用阻塞寫操作，寫操作也不用阻塞讀操作，提高了數據庫并發讀寫的性能。同時還可以解決臟讀、幻讀、不可重復讀等事務隔離問題

MVCC 的實現原理（中）

MVCC 的具體實現，依賴于數據庫記錄中的隱式字段（最近更新的事務 id 和回滾指針）、undo log 日志、readView。

在內部實現中，InnoDB 通過數據行的 DB_TRX_ID（最近更新的事務 id）和 ReadView 來判斷數據的可見性，如不可見，則通過數據行的 DB_ROLL_PTR（回滾指針）找到 undo log 版本鏈中的歷史版本(找到ReadView創建之前已經commit的數據)。這就是快照讀

每個事務讀到的數據版本可能是不一樣的，在同一個事務中，用戶只能看到該事務創建 ReadView 之前已經提交的修改和該事務本身做的修改

ReadView 是什么（中）

ReadView 是 MVCC 中用來判斷數據的可見性的。里面記錄了活躍事務 id 列表，全局事務中最大的事務 id 值，創建該 ReadView 的事務的事務 id 等。

通過比較當前事務 id 和 ReadView 中記錄的事務 id，就能知道該版本的記錄對當前事務是否可見。如不可見，則通過數據行的 DB_ROLL_PTR（回滾指針）找到 undo log 版本鏈中的歷史版本。這就是快照讀

如果想詳細了解 readView 中具體有什么，怎么對比的，建議網上查對應視頻。文字很難講解清楚，一般面試也不會問這么詳細。

當前讀與快照讀（中）

當前讀：讀取的是記錄的最新版本，讀取時還要保證其他并發事務不能修改當前記錄，會對讀取的記錄進行加鎖

對于我們日常的操作，如：select…lock in share mode(共享鎖)，select…for update、update、insert、delete(排他鎖)都是一種當前讀
當前讀：使用臨鍵鎖進行加鎖來保證不出現幻讀

快照讀：不加鎖的 select 就是快照讀，快照讀讀取的是記錄數據的可見版本有可能是歷史數據，由 MVCC 機制來保證不出現幻讀。

讀已提交RC：每次 select 都會生成一個快照讀
可重復讀RR：事務開始后的第一個 select 才是快照讀的地方
串行化：快照度會退化為當前讀

MVCC 是怎么實現不可重復讀的（中）

在讀已提交下，在事務中每一次執行快照讀時生成 ReadView，這也就造成了每次讀取就有不同 ReadView，那么就會讀到已提交的事務修改的內容，造成不可重復讀的問題

解決不可重復讀主要靠 readview，在隔離級別為可重復讀時，僅在事務中第一次執行快照讀時生成 ReadView，后續復用該 ReadView.

由于后續復用了 ReadView，所以數據對當前事務的可見性和第一次是一樣的，所以從 undolog 中讀到的數據快照和第一次是一樣的，即便過程中有其他事務修改也讀不到

MVCC 是怎么防止幻讀的（中）

MVCC解決的是快照讀下的幻讀問題，鎖解決當前讀下的幻讀問題，而在RC中每次查詢都會生成，在RR中是在事務開始的時候生成ReadView

InnoDB 存儲引擎在 RR 級別下通過 MVCC 和 Next- key Lock(臨鍵鎖)來解決幻讀問題

1、執行普通 select，此時會以 MVCC 快照讀的方式讀取數據

快照讀：避免加鎖，通過 MVCC 來進行控制，使其他事務所做的更新對當前事務不可見，從而防止幻讀。

在快照讀的情況下，RR 隔離級別只會在事務開啟后的第一次查詢生成 ReadView。所以在生成 ReadView 之后其它事務所做的更新、插入記錄版本對當前事務并不可見，實現了可重復讀和防止快照讀下的“幻讀”

2、執行 select…for update/lock in share mode、insert、update、delete 等為當前讀

這些語句執行前都會查詢最新版本的數據，所以是當前讀

當前讀：通過臨鍵鎖 next- key- lock 鎖住空隙，防止其他事務在查詢的范圍內插入數據，從而避免幻讀。

在當前讀下，讀取的都是最新的數據，如果其它事務有插入新的記錄，并且剛好在當前事務查詢范圍內，就會產生幻讀！InnoDB 使用 Next- key Lock 臨鍵鎖來防止這種情況。當執行當前讀時，會鎖定讀取到的記錄的同時，鎖定它們的間隙，防止其它事務在查詢范圍內插入數據。只要我不讓你插入，就不會發生幻讀

但是 MVCC 并沒有徹底防止幻讀問題，只是解決了大多數幻讀問題，在一些極端場景還是會有幻讀問題。

鎖

(鎖這一節非常復雜，鎖非常多，各種情況也非常多，往往不是面試重點，所以了解即可，這里只給出常見考點)

鎖的分類 (中)

全局鎖：鎖定數據庫中的所有表。

全局鎖就是對整個數據庫實例加鎖，加鎖后整個實例就處于只讀狀態，后續的 DML 的寫語句，DDL 語句，已經更新操作的事務提交語句都將被阻塞
表級鎖

O 表鎖

每次操作鎖住整張表- 開銷小，加鎖快- 并發度最低
元數據鎖（metadata lock, MDL）
MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。- MDL 的作用：保證讀寫的正確性。- 如果一個查詢正在遍歷一個表中的數據，而執行期間另一個線程對這個表結構做變更，刪了一列，那么查詢線程拿到的結果跟表結構對不上，肯定是不行的。- 當對一個表做增刪改查操作的時候，加 MDL 讀鎖；- 當要對表做結構變更操作的時候，加 MDL 寫鎖。- 讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。- 如果有兩個線程要同時給一個表加字段，其中一個要等另一個執行完才能開始執行。
行級鎖
顧名思義，行鎖就是針對數據表中行記錄的鎖（也有人稱為記錄鎖）。- 事務 A 更新了一行，而這時候事務 B 也要更新同一行，則必須等事務 A 的操作完成后才能進行更新。

特點：

每次操作鎖住一行數據- 開銷大，加鎖慢- 發生鎖沖突的概率是最低的，并發度是最高的

InnoDB 有哪幾類行鎖？ (中)

MySQL InnoDB 支持三種行鎖定方式：

記錄鎖（Record Lock）：也被稱為記錄鎖，屬于單個行記錄上的鎖。
間隙鎖（Gap Lock）：鎖定一個范圍，不包括記錄本身。
SELECT * FROM table WHERE id BETWEEN 1 AND 10 FOR UPDATE;
即所有在（1，10）區間內的記錄行都會被鎖住，所有 id 為 2、3、4、5、6、7、8、9 的數據行的插入會被阻塞，但是 1 和 10 兩條記錄行并不會被鎖住
臨鍵鎖（Next-key Lock）：行鎖和間隙鎖組合，同時鎖住數據，并鎖住數據前面的間隙 Gap。相當于鎖定一個范圍，包含記錄本身，左閉右開
有一個列 age，已有的記錄中 age 分別為 2，8。則潛在的臨鍵鎖為 $(? \infty, 2]$ (2,8] (8,+∞]

MySQL 兩個事務的 update 語句同時更新同一條數據，會發生什么情況？(中)

兩個事務同時使用 update, 首先要明確是當前讀- 當事務 A 對 id=1 這行記錄進行更新時，會對主鍵 id 為 1 的記錄加行鎖- 事務 B 對 id=1 進行更新時，發現已經有行鎖了，就會陷入阻塞狀態

兩條 update 語句修改處理同一張表的不同范圍的數據，一個<5，一個>10，會阻塞嗎？(中)

得分情況

如果 update 的 where 條件是索引列，那么會加行鎖

第一條 update xxx where 索引列 $< 5$ ，鎖住的范圍是（-無窮,5）- 第二條 update xxx where 索引列 $> 10$ ，鎖住的范圍是（10, + 無窮）

如果兩個 update 的 where 條件不是索引列，那么由于沒有用到索引，所以會觸發全表掃描，會加表鎖。此時第二條 update 執行的時候，就會被阻塞。

日志

三大日志（高）

undo log（回滾日志）：主要用于事務回滾和 MVCC，實現了事務中的原子性
redo log（重做日志）：主要用于掉電重啟等故障恢復，實現了事務中的持久性
binlog（歸檔日志/二進制日志）：主要用于數據備份和主從復制；

binlog 主要記錄了什么？有什么用？ (高)

binlog,即二進制日志，主要記錄了對 MySQL 數據庫執行了更改的所有操作（數據庫執行的所有 DDL 和 DML 語句）

包括表結構變更（CREATE、ALTER、DROPTABLE…）
表數據修改（INSERT、UPDATE、DELETE…）
但不包括 SELECT、SHOW 這類不會對數據庫造成更改的操作。

數據庫的數據備份、主備、主從需要依靠 binlog 來同步數據，保證數據一致性。

redo log 主要記錄了什么？有什么用？ (高)

redo log 重做日志，記錄的是事務提交時數據頁的物理修改，是用來實現事務的持久性。它讓 MySQL 擁有了崩潰恢復能力。

redo log 基本過程 (高)

MySQL 中數據是以頁為單位，你查詢一條記錄，會從硬盤把一頁的數據加載出來，加載出來的數據叫數據頁，會放入到 Buffer Pool 中。

后續的查詢都是先從 Buffer Pool 中找，沒有命中再去硬盤加載，減少硬盤 IO 開銷，提升性能。

更新表數據的時候，也是如此，發現 Buffer Pool 里存在要更新的數據，就直接在 Buffer Pool 里更新。

一個事務提交之后，我們對 Buffer Pool 中對應的頁的修改可能還未持久化到磁盤。這個時候，如果 MySQL 突然宕機的話，這個事務的更改是不是直接就消失了呢？

MySQL InnoDB 引擎使用 redo log 來保證事務的持久性，redo log 主要做的事情就是記錄頁的修改，比如某個頁面某個偏移量處修改了幾個字節的值以及具體被修改的內容是什么。

在事務提交時，我們會將 redo log 按照刷盤策略刷到磁盤上去。即使 MySQL 宕機了，重啟之后也能恢復未能寫入磁盤的數據，從而保證事務的持久性。也就是說，redo log 讓 MySQL 具備了崩潰回復能力。

為什么事務提交后不直接將 BufferPool 的數據同步到磁盤 (中)

實際上，數據頁大小是 16KB，刷盤比較耗時，可能就修改了數據頁里的幾 Byte 數據，有必要把完整的數據頁刷盤嗎？

而且數據頁刷盤是隨機寫，因為一個數據頁對應的位置可能在硬盤文件的隨機位置，所以性能是很差。

如果是寫 redo log，一行記錄可能就占幾十 Byte，只包含表空間號、數據頁號、磁盤文件偏移量、更新值，內容少，再加上是順序寫，所以刷盤速度很快。

所以用 redo log 形式記錄修改內容，性能會遠遠超過刷數據頁的方式，這也讓數據庫的并發能力更強。

binlog 和 redolog 有什么區別？（中）

binlog 主要用于數據庫還原，屬于數據級別的數據恢復，主從復制是 binlog 最常見的一個應用場景。redolog 主要用于保證事務的持久性，屬于事務級別的數據恢復。
redolog 屬于 InnoDB 引擎特有的，binlog 屬于所有存儲引擎共有的，因為 binlog 是 MySQL 的 Server 層實現的。
redolog 屬于物理日志，主要記錄的是某個頁的修改。binlog 屬于邏輯日志，主要記錄的是數據庫執行的所有 DDL 和 DML 語句。
binlog 通過追加的方式進行寫入，大小沒有限制。redo log 采用循環寫的方式進行寫入，大小固定，當寫到結尾時，會回到開頭循環寫日志。