大家好气破，我是大彬~

今天給大家分享MySQL沉那常考的面試題，看看你們能答對多少现使。

本期MySQL面試題的目錄如下：

• 事務的四大特性低匙？
• 事務隔離級別有哪些？
• 索引
  • 什么是索引碳锈？
  • 索引的優(yōu)缺點顽冶？
  • 索引的作用？
  • 什么情況下需要建索引售碳？
  • 什么情況下不建索引渗稍？
  • 索引的數據結構
  • Hash索引和B+樹索引的區(qū)別佩迟？
  • 為什么B+樹比B樹更適合實現數據庫索引？
  • 索引有什么分類竿屹？
  • 什么是最左匹配原則报强？
  • 什么是聚集索引？
  • 什么是覆蓋索引拱燃？
  • 索引的設計原則秉溉？
  • 索引什么時候會失效？
  • 什么是前綴索引碗誉？
• 常見的存儲引擎有哪些召嘶？
• MyISAM和InnoDB的區(qū)別？
• MVCC 實現原理哮缺？
• 快照讀和當前讀
• 共享鎖和排他鎖
• 大表怎么優(yōu)化弄跌？
• bin log/redo log/undo log
• bin log和redo log有什么區(qū)別？
• 講一下MySQL架構尝苇？
• 分庫分表
• 什么是分區(qū)表铛只？
• 分區(qū)表類型
• 查詢語句執(zhí)行流程？
• 更新語句執(zhí)行過程糠溜？
• exist和in的區(qū)別淳玩？
• truncate、delete與drop區(qū)別非竿？
• having和where的區(qū)別蜕着？
• 什么是MySQL主從同步？
• 為什么要做主從同步红柱？
• 樂觀鎖和悲觀鎖是什么承匣？
• 用過processlist嗎？

事務的四大特性锤悄？

事務特性ACID：原子性（Atomicity）韧骗、一致性（Consistency）、隔離性（Isolation）铁蹈、持久性（Durability）宽闲。

• 原子性是指事務包含的所有操作要么全部成功众眨，要么全部失敗回滾握牧。
• 一致性是指一個事務執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)。比如a與b賬戶共有1000塊娩梨，兩人之間轉賬之后無論成功還是失敗沿腰，它們的賬戶總和還是1000。
• 隔離性狈定。跟隔離級別相關颂龙，如read committed习蓬，一個事務只能讀到已經提交的修改。
• 持久性是指一個事務一旦被提交了措嵌，那么對數據庫中的數據的改變就是永久性的躲叼，即便是在數據庫系統遇到故障的情況下也不會丟失提交事務的操作。

事務隔離級別有哪些企巢？

先了解下幾個概念：臟讀枫慷、不可重復讀、幻讀浪规。

• 臟讀是指在一個事務處理過程里讀取了另一個未提交的事務中的數據或听。
• 不可重復讀是指在對于數據庫中的某行記錄，一個事務范圍內多次查詢卻返回了不同的數據值笋婿，這是由于在查詢間隔誉裆，另一個事務修改了數據并提交了。
• 幻讀是當某個事務在讀取某個范圍內的記錄時缸濒，另外一個事務又在該范圍內插入了新的記錄足丢，當之前的事務再次讀取該范圍的記錄時，會產生幻行绍填，就像產生幻覺一樣霎桅，這就是發(fā)生了幻讀。

不可重復讀和臟讀的區(qū)別是讨永，臟讀是某一事務讀取了另一個事務未提交的臟數據滔驶，而不可重復讀則是讀取了前一事務提交的數據。

幻讀和不可重復讀都是讀取了另一條已經提交的事務卿闹，不同的是不可重復讀的重點是修改揭糕，幻讀的重點在于新增或者刪除。

事務隔離就是為了解決上面提到的臟讀锻霎、不可重復讀著角、幻讀這幾個問題。

MySQL數據庫為我們提供的四種隔離級別：

• Serializable (串行化)：通過強制事務排序旋恼，使之不可能相互沖突吏口，從而解決幻讀問題。
• Repeatable read (可重復讀)：MySQL的默認事務隔離級別冰更，它確保同一事務的多個實例在并發(fā)讀取數據時产徊，會看到同樣的數據行，解決了不可重復讀的問題蜀细。
• Read committed (讀已提交)：一個事務只能看見已經提交事務所做的改變舟铜。可避免臟讀的發(fā)生奠衔。
• Read uncommitted (讀未提交)：所有事務都可以看到其他未提交事務的執(zhí)行結果谆刨。

查看隔離級別：

select @@transaction_isolation;

設置隔離級別：

set session transaction isolation level read uncommitted;

索引

什么是索引塘娶？

索引是存儲引擎用于提高數據庫表的訪問速度的一種數據結構。

索引的優(yōu)缺點痊夭？

優(yōu)點：

• 加快數據查找的速度
• 為用來排序或者是分組的字段添加索引刁岸，可以加快分組和排序的速度
• 加快表與表之間的連接

缺點：

• 建立索引需要占用物理空間
• 會降低表的增刪改的效率，因為每次對表記錄進行增刪改她我，需要進行動態(tài)維護索引难捌，導致增刪改時間變長

索引的作用？

數據是存儲在磁盤上的鸦难，查詢數據時根吁，如果沒有索引，會加載所有的數據到內存合蔽，依次進行檢索击敌，讀取磁盤次數較多。有了索引拴事，就不需要加載所有數據沃斤，因為B+樹的高度一般在2-4層，最多只需要讀取2-4次磁盤刃宵，查詢速度大大提升衡瓶。

什么情況下需要建索引？

1. 經常用于查詢的字段
2. 經常用于連接的字段建立索引牲证，可以加快連接的速度
3. 經常需要排序的字段建立索引哮针，因為索引已經排好序，可以加快排序查詢速度

什么情況下不建索引坦袍？

1. where條件中用不到的字段不適合建立索引
2. 表記錄較少
3. 需要經常增刪改
4. 參與列計算的列不適合建索引
5. 區(qū)分度不高的字段不適合建立索引十厢，如性別等

索引的數據結構

索引的數據結構主要有B+樹和哈希表，對應的索引分別為B+樹索引和哈希索引捂齐。InnoDB引擎的索引類型有B+樹索引和哈希索引蛮放，默認的索引類型為B+樹索引。

B+樹索引

B+ 樹是基于B 樹和葉子節(jié)點順序訪問指針進行實現奠宜，它具有B樹的平衡性包颁，并且通過順序訪問指針來提高區(qū)間查詢的性能。

在 B+ 樹中压真，節(jié)點中的 key 從左到右遞增排列娩嚼，如果某個指針的左右相鄰 key 分別是 key_i 和 key_i+1，則該指針指向節(jié)點的所有 key 大于等于 key_i 且小于等于 key_i+1榴都。

image

進行查找操作時待锈，首先在根節(jié)點進行二分查找漠其，找到key所在的指針嘴高，然后遞歸地在指針所指向的節(jié)點進行查找竿音。直到查找到葉子節(jié)點，然后在葉子節(jié)點上進行二分查找拴驮，找出key所對應的數據項春瞬。

MySQL 數據庫使用最多的索引類型是BTREE索引，底層基于B+樹數據結構來實現套啤。

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

哈希索引

哈希索引是基于哈希表實現的宽气，對于每一行數據，存儲引擎會對索引列進行哈希計算得到哈希碼潜沦，并且哈希算法要盡量保證不同的列值計算出的哈希碼值是不同的萄涯，將哈希碼的值作為哈希表的key值，將指向數據行的指針作為哈希表的value值唆鸡。這樣查找一個數據的時間復雜度就是O(1)涝影，一般多用于精確查找。

Hash索引和B+樹索引的區(qū)別争占？

• 哈希索引不支持排序燃逻，因為哈希表是無序的。
• 哈希索引不支持范圍查找臂痕。
• 哈希索引不支持模糊查詢及多列索引的最左前綴匹配伯襟。
• 因為哈希表中會存在哈希沖突，所以哈希索引的性能是不穩(wěn)定的握童，而B+樹索引的性能是相對穩(wěn)定的姆怪，每次查詢都是從根節(jié)點到葉子節(jié)點。

為什么B+樹比B樹更適合實現數據庫索引澡绩？

• 由于B+樹的數據都存儲在葉子結點中片效，葉子結點均為索引，方便掃庫英古，只需要掃一遍葉子結點即可淀衣，但是B樹因為其分支結點同樣存儲著數據，我們要找到具體的數據召调，需要進行一次中序遍歷按序來掃膨桥，所以B+樹更加適合在區(qū)間查詢的情況水孩，而在數據庫中基于范圍的查詢是非常頻繁的矿酵，所以通常B+樹用于數據庫索引。

• B+樹的節(jié)點只存儲索引key值洛波，具體信息的地址存在于葉子節(jié)點的地址中艺沼。這就使以頁為單位的索引中可以存放更多的節(jié)點册舞。減少更多的I/O支出。

• B+樹的查詢效率更加穩(wěn)定障般，任何關鍵字的查找必須走一條從根結點到葉子結點的路调鲸。所有關鍵字查詢的路徑長度相同盛杰，導致每一個數據的查詢效率相當。

索引有什么分類藐石？

1即供、主鍵索引：名為primary的唯一非空索引，不允許有空值于微。

2逗嫡、唯一索引：索引列中的值必須是唯一的，但是允許為空值株依。唯一索引和主鍵索引的區(qū)別是：唯一約束的列可以為null且可以存在多個null值驱证。唯一索引的用途：唯一標識數據庫表中的每條記錄，主要是用來防止數據重復插入恋腕。創(chuàng)建唯一索引的SQL語句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3雷滚、組合索引：在表中的多個字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時吗坚，索引才會被使用祈远，使用組合索引時需遵循最左前綴原則。

4商源、全文索引：只有在MyISAM引擎上才能使用车份，只能在CHAR、VARCHAR和TEXT類型字段上使用全文索引牡彻。

什么是最左匹配原則扫沼？

如果 SQL 語句中用到了組合索引中的最左邊的索引，那么這條 SQL 語句就可以利用這個組合索引去進行匹配庄吼。當遇到范圍查詢(>缎除、<、between总寻、like)就會停止匹配器罐，后面的字段不會用到索引。

對(a,b,c)建立索引渐行，查詢條件使用 a/ab/abc 會走索引轰坊，使用 bc 不會走索引。如果查詢條件為a = 1 and b > 2 and c = 3祟印，那么a肴沫、b個字兩段能用到索引，而c無法使用索引蕴忆，因為b字段是范圍查詢颤芬，導致后面的字段無法使用索引。

如下圖，對(a, b) 建立索引站蝠，a 在索引樹中是全局有序的汰具，而 b 是全局無序，局部有序（當a相等時沉衣，會根據b進行排序）。

image

當a的值確定的時候减牺，b是有序的豌习。例如a = 1時，b值為1拔疚，2是有序的狀態(tài)肥隆。當執(zhí)行a = 1 and b = 2時a和b字段能用到索引。而對于查詢條件a < 4 and b = 2時稚失，a字段能用到索引栋艳，b字段則用不到索引。因為a的值此時是一個范圍句各，不是固定的吸占，在這個范圍內b的值不是有序的，因此b字段無法使用索引凿宾。

什么是聚集索引矾屯？

InnoDB使用表的主鍵構造主鍵索引樹，同時葉子節(jié)點中存放的即為整張表的記錄數據初厚。聚集索引葉子節(jié)點的存儲是邏輯上連續(xù)的件蚕，使用雙向鏈表連接，葉子節(jié)點按照主鍵的順序排序产禾，因此對于主鍵的排序查找和范圍查找速度比較快排作。

聚集索引的葉子節(jié)點就是整張表的行記錄。InnoDB 主鍵使用的是聚簇索引亚情。聚集索引要比非聚集索引查詢效率高很多妄痪。

對于InnoDB來說，聚集索引一般是表中的主鍵索引楞件，如果表中沒有顯示指定主鍵拌夏，則會選擇表中的第一個不允許為NULL的唯一索引。如果沒有主鍵也沒有合適的唯一索引履因，那么InnoDB內部會生成一個隱藏的主鍵作為聚集索引障簿，這個隱藏的主鍵長度為6個字節(jié)，它的值會隨著數據的插入自增栅迄。

什么是覆蓋索引站故？

select的數據列只用從索引中就能夠取得，不需要回表進行二次查詢，也就是說查詢列要被所使用的索引覆蓋西篓。對于innodb表的二級索引愈腾，如果索引能覆蓋到查詢的列，那么就可以避免對主鍵索引的二次查詢岂津。

不是所有類型的索引都可以成為覆蓋索引虱黄。覆蓋索引要存儲索引列的值，而哈希索引吮成、全文索引不存儲索引列的值橱乱，所以MySQL使用b+樹索引做覆蓋索引。

對于使用了覆蓋索引的查詢粱甫，在查詢前面使用explain泳叠，輸出的extra列會顯示為using index。

比如user_like 用戶點贊表茶宵，組合索引為(user_id, blog_id)危纫，user_id和blog_id都不為null。

explain select blog_id from user_like where user_id = 13;

explain結果的Extra列為Using index乌庶，查詢的列被索引覆蓋种蝶，并且where篩選條件符合最左前綴原則，通過索引查找就能直接找到符合條件的數據瞒大，不需要回表查詢數據蛤吓。

explain select user_id from user_like where blog_id = 1;

explain結果的Extra列為Using where; Using index， 查詢的列被索引覆蓋糠赦，where篩選條件不符合最左前綴原則会傲，無法通過索引查找找到符合條件的數據，但可以通過索引掃描找到符合條件的數據拙泽，也不需要回表查詢數據淌山。

image

索引的設計原則？

• 索引列的區(qū)分度越高顾瞻，索引的效果越好泼疑。比如使用性別這種區(qū)分度很低的列作為索引，效果就會很差荷荤。
• 盡量使用短索引退渗，對于較長的字符串進行索引時應該指定一個較短的前綴長度，因為較小的索引涉及到的磁盤I/O較少蕴纳，查詢速度更快会油。
• 索引不是越多越好，每個索引都需要額外的物理空間古毛，維護也需要花費時間翻翩。
• 利用最左前綴原則都许。

索引什么時候會失效？

導致索引失效的情況：

• 對于組合索引嫂冻，不是使用組合索引最左邊的字段胶征，則不會使用索引
• 以%開頭的like查詢如%abc，無法使用索引桨仿；非%開頭的like查詢如abc%睛低，相當于范圍查詢，會使用索引
• 查詢條件中列類型是字符串服傍，沒有使用引號钱雷，可能會因為類型不同發(fā)生隱式轉換，使索引失效
• 判斷索引列是否不等于某個值時
• 對索引列進行運算
• 查詢條件使用or連接伴嗡，也會導致索引失效

什么是前綴索引急波？

有時需要在很長的字符列上創(chuàng)建索引从铲，這會造成索引特別大且慢瘪校。使用前綴索引可以避免這個問題。

前綴索引是指對文本或者字符串的前幾個字符建立索引名段，這樣索引的長度更短阱扬，查詢速度更快。

創(chuàng)建前綴索引的關鍵在于選擇足夠長的前綴以保證較高的索引選擇性伸辟。索引選擇性越高查詢效率就越高麻惶，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的數據行。

建立前綴索引的方式：

// email列創(chuàng)建前綴索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

常見的存儲引擎有哪些信夫？

MySQL中常用的四種存儲引擎分別是： MyISAM窃蹋、InnoDB、MEMORY静稻、ARCHIVE警没。MySQL 5.5版本后默認的存儲引擎為InnoDB。

InnoDB存儲引擎

InnoDB是MySQL默認的事務型存儲引擎振湾，使用最廣泛杀迹，基于聚簇索引建立的。InnoDB內部做了很多優(yōu)化押搪，如能夠自動在內存中創(chuàng)建自適應hash索引树酪，以加速讀操作。

優(yōu)點：支持事務和崩潰修復能力大州；引入了行級鎖和外鍵約束续语。

缺點：占用的數據空間相對較大。

適用場景：需要事務支持厦画，并且有較高的并發(fā)讀寫頻率绵载。

MyISAM存儲引擎

數據以緊密格式存儲。對于只讀數據，或者表比較小娃豹、可以容忍修復操作焚虱，可以使用MyISAM引擎。MyISAM會將表存儲在兩個文件中懂版，數據文件.MYD和索引文件.MYI鹃栽。

優(yōu)點：訪問速度快。

缺點：MyISAM不支持事務和行級鎖躯畴，不支持崩潰后的安全恢復民鼓，也不支持外鍵。

適用場景：對事務完整性沒有要求蓬抄；表的數據都會只讀的丰嘉。

MEMORY存儲引擎

MEMORY引擎將數據全部放在內存中，訪問速度較快嚷缭，但是一旦系統奔潰的話饮亏，數據都會丟失。

MEMORY引擎默認使用哈希索引阅爽，將鍵的哈希值和指向數據行的指針保存在哈希索引中路幸。

優(yōu)點：訪問速度較快。

缺點：

1. 哈希索引數據不是按照索引值順序存儲付翁，無法用于排序简肴。
2. 不支持部分索引匹配查找，因為哈希索引是使用索引列的全部內容來計算哈希值的百侧。
3. 只支持等值比較砰识，不支持范圍查詢。
4. 當出現哈希沖突時佣渴，存儲引擎需要遍歷鏈表中所有的行指針辫狼，逐行進行比較，直到找到符合條件的行观话。

ARCHIVE存儲引擎

ARCHIVE存儲引擎非常適合存儲大量獨立的予借、作為歷史記錄的數據。ARCHIVE提供了壓縮功能频蛔，擁有高效的插入速度灵迫，但是這種引擎不支持索引，所以查詢性能較差晦溪。

MyISAM和InnoDB的區(qū)別瀑粥？

1. 是否支持行級鎖 : MyISAM 只有表級鎖，而InnoDB 支持行級鎖和表級鎖三圆，默認為行級鎖狞换。

2. 是否支持事務和崩潰后的安全恢復： MyISAM 不提供事務支持避咆。而InnoDB提供事務支持，具有事務修噪、回滾和崩潰修復能力查库。

3. 是否支持外鍵： MyISAM不支持，而InnoDB支持黄琼。

4. 是否支持MVCC ：MyISAM不支持樊销，InnoDB支持。應對高并發(fā)事務脏款，MVCC比單純的加鎖更高效围苫。

5. MyISAM不支持聚集索引，InnoDB支持聚集索引撤师。

MVCC 實現原理剂府？

MVCC(Multiversion concurrency control) 就是同一份數據保留多版本的一種方式，進而實現并發(fā)控制剃盾。在查詢的時候腺占，通過read view和版本鏈找到對應版本的數據。

作用：提升并發(fā)性能万俗。對于高并發(fā)場景湾笛，MVCC比行級鎖開銷更小饮怯。

MVCC 實現原理如下：

MVCC 的實現依賴于版本鏈闰歪，版本鏈是通過表的三個隱藏字段實現。

• DB_TRX_ID：當前事務id蓖墅，通過事務id的大小判斷事務的時間順序库倘。
• DB_ROLL_PRT：回滾指針，指向當前行記錄的上一個版本论矾，通過這個指針將數據的多個版本連接在一起構成undo log版本鏈教翩。
• DB_ROLL_ID：主鍵，如果數據表沒有主鍵贪壳，InnoDB會自動生成主鍵饱亿。

每條表記錄大概是這樣的：

image

使用事務更新行記錄的時候，就會生成版本鏈闰靴，執(zhí)行過程如下：

1. 用排他鎖鎖住該行彪笼；
2. 將該行原本的值拷貝到undo log，作為舊版本用于回滾蚂且；
3. 修改當前行的值配猫，生成一個新版本，更新事務id杏死，使回滾指針指向舊版本的記錄泵肄，這樣就形成一條版本鏈捆交。

下面舉個例子方便大家理解。

1腐巢、初始數據如下品追，其中DB_ROW_ID和DB_ROLL_PTR為空。

image

2冯丙、事務A對該行數據做了修改诵盼，將age修改為12，效果如下：

image

3银还、之后事務B也對該行記錄做了修改风宁，將age修改為8，效果如下：

image

4蛹疯、此時undo log有兩行記錄戒财，并且通過回滾指針連在一起。

接下來了解下read view的概念捺弦。

read view可以理解成將數據在每個時刻的狀態(tài)拍成“照片”記錄下來饮寞。在獲取某時刻t的數據時，到t時間點拍的“照片”上取數據列吼。

在read view內部維護一個活躍事務鏈表幽崩，表示生成read view的時候還在活躍的事務。這個鏈表包含在創(chuàng)建read view之前還未提交的事務寞钥，不包含創(chuàng)建read view之后提交的事務慌申。

不同隔離級別創(chuàng)建read view的時機不同。

• read committed：每次執(zhí)行select都會創(chuàng)建新的read_view理郑，保證能讀取到其他事務已經提交的修改蹄溉。

• repeatable read：在一個事務范圍內，第一次select時更新這個read_view您炉，以后不會再更新柒爵，后續(xù)所有的select都是復用之前的read_view。這樣可以保證事務范圍內每次讀取的內容都一樣赚爵，即可重復讀棉胀。

read view的記錄篩選方式

前提：DATA_TRX_ID 表示每個數據行的最新的事務ID；up_limit_id表示當前快照中的最先開始的事務冀膝；low_limit_id表示當前快照中的最慢開始的事務唁奢，即最后一個事務。

image

• 如果DATA_TRX_ID < up_limit_id：說明在創(chuàng)建read view時畸写，修改該數據行的事務已提交驮瞧，該版本的記錄可被當前事務讀取到。
• 如果DATA_TRX_ID >= low_limit_id：說明當前版本的記錄的事務是在創(chuàng)建read view之后生成的枯芬，該版本的數據行不可以被當前事務訪問论笔。此時需要通過版本鏈找到上一個版本采郎，然后重新判斷該版本的記錄對當前事務的可見性。
• 如果up_limit_id <= DATA_TRX_ID < low_limit_i：
  1. 需要在活躍事務鏈表中查找是否存在ID為DATA_TRX_ID的值的事務狂魔。
  2. 如果存在蒜埋，因為在活躍事務鏈表中的事務是未提交的，所以該記錄是不可見的最楷。此時需要通過版本鏈找到上一個版本整份，然后重新判斷該版本的可見性。
  3. 如果不存在籽孙，說明事務trx_id 已經提交了烈评，這行記錄是可見的。

總結：InnoDB 的MVCC是通過 read view 和版本鏈實現的犯建，版本鏈保存有歷史版本記錄讲冠，通過read view 判斷當前版本的數據是否可見，如果不可見适瓦，再從版本鏈中找到上一個版本竿开，繼續(xù)進行判斷，直到找到一個可見的版本玻熙。

快照讀和當前讀

表記錄有兩種讀取方式镀琉。

• 快照讀：讀取的是快照版本蚊逢。普通的SELECT就是快照讀份企。通過mvcc來進行并發(fā)控制的劳坑，不用加鎖恋谭。

• 當前讀：讀取的是最新版本嫌吠。UPDATE匾竿、DELETE砌烁、INSERT筷转、SELECT … LOCK IN SHARE MODE姑原、SELECT … FOR UPDATE是當前讀。

快照讀情況下呜舒，InnoDB通過mvcc機制避免了幻讀現象锭汛。而mvcc機制無法避免當前讀情況下出現的幻讀現象。因為當前讀每次讀取的都是最新數據袭蝗，這時如果兩次查詢中間有其它事務插入數據唤殴，就會產生幻讀。

下面舉個例子說明下：

1到腥、首先朵逝，user表只有兩條記錄，具體如下：

image

2乡范、事務a和事務b同時開啟事務start transaction配名；

3啤咽、事務a插入數據然后提交；

insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);

4渠脉、事務b執(zhí)行全表的update宇整；

update user set user_name = 'a';

5、事務b然后執(zhí)行查詢芋膘，查到了事務a中插入的數據鳞青。（下圖左邊是事務b，右邊是事務a为朋。事務開始之前只有兩條記錄臂拓，事務a插入一條數據之后，事務b查詢出來是三條數據）

image

以上就是當前讀出現的幻讀現象习寸。

那么MySQL是如何避免幻讀埃儿？

• 在快照讀情況下，MySQL通過mvcc來避免幻讀融涣。
• 在當前讀情況下童番，MySQL通過next-key來避免幻讀（加行鎖和間隙鎖來實現的）。

next-key包括兩部分：行鎖和間隙鎖威鹿。行鎖是加在索引上的鎖剃斧，間隙鎖是加在索引之間的。

Serializable隔離級別也可以避免幻讀忽你，會鎖住整張表幼东，并發(fā)性極低，一般不會使用科雳。

共享鎖和排他鎖

SELECT 的讀取鎖定主要分為兩種方式：共享鎖和排他鎖根蟹。

select * from table where id<6 lock in share mode;--共享鎖
select * from table where id<6 for update;--排他鎖

這兩種方式主要的不同在于LOCK IN SHARE MODE多個事務同時更新同一個表單時很容易造成死鎖。

申請排他鎖的前提是糟秘，沒有線程對該結果集的任何行數據使用排它鎖或者共享鎖简逮，否則申請會受到阻塞。在進行事務操作時尿赚，MySQL會對查詢結果集的每行數據添加排它鎖散庶，其他線程對這些數據的更改或刪除操作會被阻塞（只能讀操作），直到該語句的事務被commit語句或rollback語句結束為止凌净。

SELECT... FOR UPDATE 使用注意事項：

1. for update 僅適用于innodb悲龟，且必須在事務范圍內才能生效。
2. 根據主鍵進行查詢冰寻，查詢條件為like或者不等于须教，主鍵字段產生表鎖。
3. 根據非索引字段進行查詢斩芭，會產生表鎖轻腺。

大表怎么優(yōu)化羹奉？

某個表有近千萬數據，查詢比較慢约计，如何優(yōu)化诀拭？

當MySQL單表記錄數過大時，數據庫的性能會明顯下降煤蚌，一些常見的優(yōu)化措施如下：

• 限定數據的范圍耕挨。比如：用戶在查詢歷史信息的時候，可以控制在一個月的時間范圍內尉桩；
• 讀寫分離： 經典的數據庫拆分方案筒占，主庫負責寫，從庫負責讀蜘犁；
• 通過分庫分表的方式進行優(yōu)化翰苫，主要有垂直拆分和水平拆分。

bin log/redo log/undo log

MySQL日志主要包括查詢日志这橙、慢查詢日志奏窑、事務日志、錯誤日志屈扎、二進制日志等埃唯。其中比較重要的是 bin log（二進制日志）和 redo log（重做日志）和 undo log（回滾日志）。

bin log

bin log是MySQL數據庫級別的文件鹰晨，記錄對MySQL數據庫執(zhí)行修改的所有操作墨叛，不會記錄select和show語句，主要用于恢復數據庫和同步數據庫模蜡。

redo log

redo log是innodb引擎級別漠趁，用來記錄innodb存儲引擎的事務日志，不管事務是否提交都會記錄下來忍疾，用于數據恢復闯传。當數據庫發(fā)生故障，innoDB存儲引擎會使用redo log恢復到發(fā)生故障前的時刻膝昆，以此來保證數據的完整性丸边。將參數innodb_flush_log_at_tx_commit設置為1，那么在執(zhí)行commit時會將redo log同步寫到磁盤荚孵。

undo log

除了記錄redo log外，當進行數據修改時還會記錄undo log纬朝，undo log用于數據的撤回操作收叶，它保留了記錄修改前的內容。通過undo log可以實現事務回滾共苛，并且可以根據undo log回溯到某個特定的版本的數據判没，實現MVCC蜓萄。

bin log和redo log有什么區(qū)別？

1. bin log會記錄所有日志記錄澄峰，包括InnoDB嫉沽、MyISAM等存儲引擎的日志；redo log只記錄innoDB自身的事務日志俏竞。
2. bin log只在事務提交前寫入到磁盤绸硕，一個事務只寫一次；而在事務進行過程魂毁，會有redo log不斷寫入磁盤玻佩。
3. bin log是邏輯日志，記錄的是SQL語句的原始邏輯席楚；redo log是物理日志咬崔，記錄的是在某個數據頁上做了什么修改。

講一下MySQL架構烦秩？

image

MySQL主要分為 Server 層和存儲引擎層：

• Server 層：主要包括連接器垮斯、查詢緩存、分析器只祠、優(yōu)化器甚脉、執(zhí)行器等，所有跨存儲引擎的功能都在這一層實現铆农，比如存儲過程牺氨、觸發(fā)器、視圖墩剖，函數等猴凹，還有一個通用的日志模塊 binglog 日志模塊。
• 存儲引擎： 主要負責數據的存儲和讀取岭皂。server 層通過api與存儲引擎進行通信郊霎。

Server 層基本組件

• 連接器： 當客戶端連接 MySQL 時，server層會對其進行身份認證和權限校驗爷绘。
• 查詢緩存: 執(zhí)行查詢語句的時候书劝，會先查詢緩存，先校驗這個 sql 是否執(zhí)行過土至，如果有緩存這個 sql购对，就會直接返回給客戶端，如果沒有命中陶因，就會執(zhí)行后續(xù)的操作骡苞。
• 分析器: 沒有命中緩存的話，SQL 語句就會經過分析器，主要分為兩步解幽，詞法分析和語法分析贴见，先看 SQL 語句要做什么，再檢查 SQL 語句語法是否正確躲株。
• 優(yōu)化器： 優(yōu)化器對查詢進行優(yōu)化片部，包括重寫查詢、決定表的讀寫順序以及選擇合適的索引等霜定，生成執(zhí)行計劃档悠。
• 執(zhí)行器： 首先執(zhí)行前會校驗該用戶有沒有權限，如果沒有權限然爆，就會返回錯誤信息站粟，如果有權限，就會根據執(zhí)行計劃去調用引擎的接口曾雕，返回結果奴烙。

分庫分表

當單表的數據量達到1000W或100G以后，優(yōu)化索引剖张、添加從庫等可能對數據庫性能提升效果不明顯切诀，此時就要考慮對其進行切分了。切分的目的就在于減少數據庫的負擔搔弄，縮短查詢的時間幅虑。

數據切分可以分為兩種方式：垂直劃分和水平劃分。

垂直劃分

垂直劃分數據庫是根據業(yè)務進行劃分顾犹，例如購物場景倒庵，可以將庫中涉及商品、訂單炫刷、用戶的表分別劃分出成一個庫擎宝，通過降低單庫的大小來提高性能。同樣的浑玛，分表的情況就是將一個大表根據業(yè)務功能拆分成一個個子表绍申，例如商品基本信息和商品描述，商品基本信息一般會展示在商品列表顾彰，商品描述在商品詳情頁极阅，可以將商品基本信息和商品描述拆分成兩張表。

image

優(yōu)點：行記錄變小涨享，數據頁可以存放更多記錄筋搏，在查詢時減少I/O次數。

缺點：

• 主鍵出現冗余灰伟，需要管理冗余列拆又；
• 會引起表連接JOIN操作儒旬，可以通過在業(yè)務服務器上進行join來減少數據庫壓力栏账；
• 依然存在單表數據量過大的問題帖族。

水平劃分

水平劃分是根據一定規(guī)則，例如時間或id序列值等進行數據的拆分挡爵。比如根據年份來拆分不同的數據庫竖般。每個數據庫結構一致，但是數據得以拆分茶鹃，從而提升性能涣雕。

image

優(yōu)點：單庫（表）的數據量得以減少，提高性能闭翩；切分出的表結構相同挣郭，程序改動較少。

缺點：

• 分片事務一致性難以解決
• 跨節(jié)點join性能差疗韵，邏輯復雜
• 數據分片在擴容時需要遷移

什么是分區(qū)表兑障？

分區(qū)表是一個獨立的邏輯表，但是底層由多個物理子表組成蕉汪。

當查詢條件的數據分布在某一個分區(qū)的時候流译，查詢引擎只會去某一個分區(qū)查詢，而不是遍歷整個表者疤。在管理層面福澡，如果需要刪除某一個分區(qū)的數據，只需要刪除對應的分區(qū)即可驹马。

分區(qū)表類型

按照范圍分區(qū)革砸。

CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (
      PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
   );

在/var/lib/mysql/data/可以找到對應的數據文件，每個分區(qū)表都有一個使用#分隔命名的表文件：

   -rw-r----- 1 MySQL MySQL    65 Mar 14 21:47 db.opt
   -rw-r----- 1 MySQL MySQL  8598 Mar 14 21:50 test_range_partition.frm
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...

list分區(qū)

對于List分區(qū)糯累，分區(qū)字段必須是已知的算利，如果插入的字段不在分區(qū)時枚舉值中，將無法插入寇蚊。

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash分區(qū)

可以將數據均勻地分布到預先定義的分區(qū)中笔时。

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

查詢語句執(zhí)行流程？

查詢語句的執(zhí)行流程如下：權限校驗仗岸、查詢緩存允耿、分析器、優(yōu)化器扒怖、權限校驗较锡、執(zhí)行器、引擎盗痒。

舉個例子蚂蕴，查詢語句如下：

select * from user where id > 1 and name = '大彬';

1. 首先檢查權限低散，沒有權限則返回錯誤；
2. MySQL8.0以前會查詢緩存骡楼，緩存命中則直接返回熔号，沒有則執(zhí)行下一步；
3. 詞法分析和語法分析鸟整。提取表名引镊、查詢條件，檢查語法是否有錯誤篮条；
4. 兩種執(zhí)行方案弟头，先查 id > 1 還是 name = '大彬'，優(yōu)化器根據自己的優(yōu)化算法選擇執(zhí)行效率最好的方案涉茧；
5. 校驗權限赴恨，有權限就調用數據庫引擎接口，返回引擎的執(zhí)行結果伴栓。

更新語句執(zhí)行過程伦连？

更新語句執(zhí)行流程如下：分析器、權限校驗挣饥、執(zhí)行器除师、引擎、redo log（prepare狀態(tài)）扔枫、binlog汛聚、redo log（commit狀態(tài)）

舉個例子，更新語句如下：

update user set name = '大彬' where id = 1;

1. 先查詢到 id 為1的記錄短荐，有緩存會使用緩存倚舀。
2. 拿到查詢結果，將 name 更新為大彬忍宋，然后調用引擎接口痕貌，寫入更新數據，innodb 引擎將數據保存在內存中糠排，同時記錄redo log舵稠，此時redo log進入 prepare狀態(tài)。
3. 執(zhí)行器收到通知后記錄binlog入宦，然后調用引擎接口哺徊，提交redo log為commit狀態(tài)。
4. 更新完成乾闰。

為什么記錄完redo log落追，不直接提交，而是先進入prepare狀態(tài)涯肩？

假設先寫redo log直接提交轿钠，然后寫binlog巢钓，寫完redo log后，機器掛了疗垛，binlog日志沒有被寫入症汹，那么機器重啟后，這臺機器會通過redo log恢復數據继谚，但是這個時候binlog并沒有記錄該數據烈菌，后續(xù)進行機器備份的時候阵幸，就會丟失這一條數據花履，同時主從同步也會丟失這一條數據。

exist和in的區(qū)別挚赊？

exists用于對外表記錄做篩選诡壁。exists會遍歷外表，將外查詢表的每一行荠割，代入內查詢進行判斷妹卿。當exists里的條件語句能夠返回記錄行時，條件就為真蔑鹦，返回外表當前記錄夺克。反之如果exists里的條件語句不能返回記錄行，條件為假嚎朽，則外表當前記錄被丟棄铺纽。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后邊的語句查出來放到臨時表中，然后遍歷臨時表哟忍，將臨時表的每一行狡门，代入外查詢去查找。

select * from Awhere id in(select id from B)

子查詢的表比較大的時候锅很，使用exists可以有效減少總的循環(huán)次數來提升速度其馏；當外查詢的表比較大的時候，使用in可以有效減少對外查詢表循環(huán)遍歷來提升速度爆安。

truncate叛复、delete與drop區(qū)別？

相同點：

1. truncate和不帶where子句的delete扔仓、以及drop都會刪除表內的數據褐奥。

2. drop、truncate都是DDL語句（數據定義語言）当辐，執(zhí)行后會自動提交抖僵。

不同點：

1. truncate 和 delete 只刪除數據不刪除表的結構；drop 語句將刪除表的結構被依賴的約束缘揪、觸發(fā)器耍群、索引义桂；
2. 一般來說，執(zhí)行速度: drop > truncate > delete蹈垢。

having和where的區(qū)別慷吊？

• 二者作用的對象不同，where子句作用于表和視圖曹抬，having作用于組溉瓶。
• where在數據分組前進行過濾，having在數據分組后進行過濾谤民。

什么是MySQL主從同步堰酿？

主從同步使得數據可以從一個數據庫服務器復制到其他服務器上，在復制數據時张足，一個服務器充當主服務器（master）触创，其余的服務器充當從服務器（slave）。

因為復制是異步進行的为牍，所以從服務器不需要一直連接著主服務器哼绑，從服務器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務器。通過配置文件碉咆，可以指定復制所有的數據庫抖韩，某個數據庫，甚至是某個數據庫上的某個表疫铜。

為什么要做主從同步茂浮？

1. 讀寫分離，使數據庫能支撐更大的并發(fā)块攒。
2. 在主服務器上生成實時數據励稳，而在從服務器上分析這些數據，從而提高主服務器的性能囱井。
3. 數據備份驹尼，保證數據的安全。

樂觀鎖和悲觀鎖是什么庞呕？

數據庫中的并發(fā)控制是確保在多個事務同時存取數據庫中同一數據時不破壞事務的隔離性和統一性以及數據庫的統一性新翎。樂觀鎖和悲觀鎖是并發(fā)控制主要采用的技術手段。

• 悲觀鎖：假定會發(fā)生并發(fā)沖突住练，在查詢完數據的時候就把事務鎖起來地啰，直到提交事務。實現方式：使用數據庫中的鎖機制讲逛。
• 樂觀鎖：假設不會發(fā)生并發(fā)沖突亏吝，只在提交操作時檢查是否數據是否被修改過。給表增加version字段盏混，在修改提交之前檢查version與原來取到的version值是否相等蔚鸥，若相等惜论，表示數據沒有被修改，可以更新止喷，否則馆类，數據為臟數據，不能更新弹谁。實現方式：樂觀鎖一般使用版本號機制或CAS算法實現乾巧。

用過processlist嗎？

show processlist 或 show full processlist 可以查看當前 MySQL 是否有壓力预愤，正在運行的SQL沟于，有沒有慢SQL正在執(zhí)行。返回參數如下：

1. id：線程ID鳖粟，可以用kill id殺死某個線程
2. db：數據庫名稱
3. user：數據庫用戶
4. host：數據庫實例的IP
5. command：當前執(zhí)行的命令社裆，比如Sleep，Query向图，Connect等
6. time：消耗時間，單位秒
7. state：執(zhí)行狀態(tài)标沪，主要有以下狀態(tài)：
   • Sleep榄攀，線程正在等待客戶端發(fā)送新的請求
   • Locked，線程正在等待鎖
   • Sending data金句，正在處理SELECT查詢的記錄檩赢，同時把結果發(fā)送給客戶端
   • Kill，正在執(zhí)行kill語句违寞，殺死指定線程
   • Connect贞瞒，一個從節(jié)點連上了主節(jié)點
   • Quit，線程正在退出
   • Sorting for group趁曼，正在為GROUP BY做排序
   • Sorting for order军浆，正在為ORDER BY做排序
8. info：正在執(zhí)行的SQL語句