【轉載】mysql36問

MySQL經典36問施逾！ (qq.com)

事務的四大特性棺棵？
事務隔離級別有哪些？
索引
什么是索引？
索引的優(yōu)缺點丁侄？
索引的作用棋凳？
什么情況下需要建索引？
什么情況下不建索引坠陈？
索引的數(shù)據(jù)結構
Hash索引和B+樹索引的區(qū)別萨惑？
為什么B+樹比B樹更適合實現(xiàn)數(shù)據(jù)庫索引？
索引有什么分類仇矾？
什么是最左匹配原則庸蔼？
什么是聚集索引？
什么是覆蓋索引贮匕？
索引的設計原則姐仅？
索引什么時候會失效？
什么是前綴索引粗合？
常見的存儲引擎有哪些萍嬉？
MyISAM和InnoDB的區(qū)別？
MVCC 實現(xiàn)原理隙疚？
快照讀和當前讀
共享鎖和排他鎖
大表怎么優(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热押，一個事務只能讀到已經提交的修改西傀。
持久性是指一個事務一旦被提交了，那么對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久性的桶癣，即便是在數(shù)據(jù)庫系統(tǒng)遇到故障的情況下也不會丟失提交事務的操作拥褂。

事務隔離級別有哪些？

先了解下幾個概念：臟讀牙寞、不可重復讀饺鹃、幻讀。

臟讀是指在一個事務處理過程里讀取了另一個未提交的事務中的數(shù)據(jù)间雀。
不可重復讀是指在對于數(shù)據(jù)庫中的某行記錄悔详，一個事務范圍內多次查詢卻返回了不同的數(shù)據(jù)值，這是由于在查詢間隔惹挟，另一個事務修改了數(shù)據(jù)并提交了茄螃。
幻讀是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄连锯，當之前的事務再次讀取該范圍的記錄時归苍，會產生幻行用狱，就像產生幻覺一樣，這就是發(fā)生了幻讀拼弃。

不可重復讀和臟讀的區(qū)別是夏伊，臟讀是某一事務讀取了另一個事務未提交的臟數(shù)據(jù)，而不可重復讀則是讀取了前一事務提交的數(shù)據(jù)吻氧。

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

事務隔離就是為了解決上面提到的臟讀砸狞、不可重復讀、幻讀這幾個問題镀梭。

MySQL數(shù)據(jù)庫為我們提供的四種隔離級別：

Serializable (串行化)：通過強制事務排序，使之不可能相互沖突踱启，從而解決幻讀問題报账。
Repeatable read (可重復讀)：MySQL的默認事務隔離級別，它確保同一事務的多個實例在并發(fā)讀取數(shù)據(jù)時埠偿，會看到同樣的數(shù)據(jù)行透罢，解決了不可重復讀的問題。
Read committed (讀已提交)：一個事務只能看見已經提交事務所做的改變冠蒋∮鹌裕可避免臟讀的發(fā)生。
Read uncommitted (讀未提交)：所有事務都可以看到其他未提交事務的執(zhí)行結果抖剿。

查看隔離級別：

設置隔離級別：

索引

什么是索引朽寞？

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

索引的優(yōu)缺點斩郎？

優(yōu)點：

加快數(shù)據(jù)查找的速度
為用來排序或者是分組的字段添加索引脑融，可以加快分組和排序的速度
加快表與表之間連接的速度

缺點：

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

索引的作用？

數(shù)據(jù)是存儲在磁盤上的锻煌，查詢數(shù)據(jù)時妓布，如果沒有索引，會加載所有的數(shù)據(jù)到內存宋梧，依次進行檢索匣沼，讀取磁盤次數(shù)較多。有了索引乃秀，就不需要加載所有數(shù)據(jù)肛著，因為B+樹的高度一般在2-4層圆兵，最多只需要讀取2-4次磁盤，查詢速度大大提升枢贿。

什么情況下需要建索引殉农？

經常用于查詢的字段
經常用于連接的字段建立索引，可以加快連接的速度
經常需要排序的字段建立索引局荚，因為索引已經排好序超凳，可以加快排序查詢速度

什么情況下不建索引？

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

索引的數(shù)據(jù)結構

索引的數(shù)據(jù)結構主要有B+樹和哈希表轮傍，對應的索引分別為B+樹索引和哈希索引。InnoDB引擎的索引類型有B+樹索引和哈希索引首装，默認的索引類型為B+樹索引创夜。

B+樹索引

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

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

圖片

進行查找操作時缺亮，首先在根節(jié)點進行二分查找翁涤，找到key所在的指針，然后遞歸地在指針所指向的節(jié)點進行查找萌踱。直到查找到葉子節(jié)點葵礼，然后在葉子節(jié)點上進行二分查找，找出key所對應的數(shù)據(jù)項虫蝶。

MySQL 數(shù)據(jù)庫使用最多的索引類型是BTREE索引章咧，底層基于B+樹數(shù)據(jù)結構來實現(xiàn)。

哈希索引

哈希索引是基于哈希表實現(xiàn)的能真，對于每一行數(shù)據(jù)赁严，存儲引擎會對索引列進行哈希計算得到哈希碼，并且哈希算法要盡量保證不同的列值計算出的哈希碼值是不同的粉铐，將哈希碼的值作為哈希表的key值疼约，將指向數(shù)據(jù)行的指針作為哈希表的value值。這樣查找一個數(shù)據(jù)的時間復雜度就是O(1)蝙泼，一般多用于精確查找程剥。

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

哈希索引不支持排序，因為哈希表是無序的织鲸。
哈希索引不支持范圍查找舔腾。
哈希索引不支持模糊查詢及多列索引的最左前綴匹配。
因為哈希表中會存在哈希沖突搂擦，所以哈希索引的性能是不穩(wěn)定的稳诚，而B+樹索引的性能是相對穩(wěn)定的，每次查詢都是從根節(jié)點到葉子節(jié)點瀑踢。

為什么B+樹比B樹更適合實現(xiàn)數(shù)據(jù)庫索引扳还？

由于B+樹的數(shù)據(jù)都存儲在葉子結點中，葉子結點均為索引橱夭，方便掃庫氨距，只需要掃一遍葉子結點即可，但是B樹因為其分支結點同樣存儲著數(shù)據(jù)棘劣，我們要找到具體的數(shù)據(jù)俏让，需要進行一次中序遍歷按序來掃，所以B+樹更加適合在區(qū)間查詢的情況茬暇，而在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的舆驶，所以通常B+樹用于數(shù)據(jù)庫索引。
B+樹的節(jié)點只存儲索引key值而钞，具體信息的地址存在于葉子節(jié)點的地址中。這就使以頁為單位的索引中可以存放更多的節(jié)點拘荡。減少更多的I/O支出臼节。
B+樹的查詢效率更加穩(wěn)定，任何關鍵字的查找必須走一條從根結點到葉子結點的路珊皿。所有關鍵字查詢的路徑長度相同网缝，導致每一個數(shù)據(jù)的查詢效率相當。

索引有什么分類蟋定？

1粉臊、主鍵索引：名為primary的唯一非空索引，不允許有空值驶兜。

2扼仲、唯一索引：索引列中的值必須是唯一的，但是允許為空值抄淑。唯一索引和主鍵索引的區(qū)別是：唯一約束的列可以為null且可以存在多個null值屠凶。唯一索引的用途：唯一標識數(shù)據(jù)庫表中的每條記錄，主要是用來防止數(shù)據(jù)重復插入肆资。創(chuàng)建唯一索引的SQL語句如下：

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相等時涮因，會根據(jù)b進行排序）废睦。

圖片

當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é)點中存放的即為整張表的記錄數(shù)據(jù)挺份。聚集索引葉子節(jié)點的存儲是邏輯上連續(xù)的，使用雙向鏈表連接贮懈，葉子節(jié)點按照主鍵的順序排序匀泊，因此對于主鍵的排序查找和范圍查找速度比較快。

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

對于InnoDB來說抡医，聚集索引一般是表中的主鍵索引躲因，如果表中沒有顯示指定主鍵，則會選擇表中的第一個不允許為NULL的唯一索引忌傻。如果沒有主鍵也沒有合適的唯一索引大脉，那么InnoDB內部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵長度為6個字節(jié)水孩，它的值會隨著數(shù)據(jù)的插入自增镰矿。

什么是覆蓋索引？

select的數(shù)據(jù)列只用從索引中就能夠取得俘种，不需要回表進行二次查詢衡怀，也就是說查詢列要被所使用的索引覆蓋。對于innodb表的二級索引安疗，如果索引能覆蓋到查詢的列，那么就可以避免對主鍵索引的二次查詢够委。

不是所有類型的索引都可以成為覆蓋索引荐类。覆蓋索引要存儲索引列的值，而哈希索引茁帽、全文索引不存儲索引列的值玉罐，所以MySQL使用b+樹索引做覆蓋索引。

對于使用了覆蓋索引的查詢潘拨，在查詢前面使用explain吊输，輸出的extra列會顯示為using index。

比如user_like 用戶點贊表铁追，組合索引為(user_id, blog_id)季蚂，user_id和blog_id都不為null。

explain結果的Extra列為Using index，查詢的列被索引覆蓋扭屁，并且where篩選條件符合最左前綴原則算谈，通過索引查找就能直接找到符合條件的數(shù)據(jù)，不需要回表查詢數(shù)據(jù)料滥。

explain結果的Extra列為`Using where; Using ind【被索引覆蓋然眼，where篩選條件不符合最左前綴原則，無法通過索引查找找到符合條件的數(shù)據(jù)葵腹，但可以通過索引掃描找到符合條件的數(shù)據(jù)高每，也不需要回表查詢數(shù)據(jù)。

image.gif

索引的設計原則践宴？

索引列的區(qū)分度越高鲸匿，索引的效果越好。比如使用性別這種區(qū)分度很低的列作為索引浴井，效果就會很差晒骇。
盡量使用短索引，對于較長的字符串進行索引時應該指定一個較短的前綴長度磺浙，因為較小的索引涉及到的磁盤I/O較少洪囤，查詢速度更快。
索引不是越多越好撕氧，每個索引都需要額外的物理空間瘤缩，維護也需要花費時間霸褒。
利用最左前綴原則香伴。

索引什么時候會失效望忆？

導致索引失效的情況：

對于組合索引坪稽，不是使用組合索引最左邊的字段颤芬，則不會使用索引
以%開頭的like查詢如%abc渣刷，無法使用索引煎饼；非%開頭的like查詢如abc%位岔，相當于范圍查詢防楷，會使用索引
查詢條件中列類型是字符串牺丙，沒有使用引號，可能會因為類型不同發(fā)生隱式轉換复局，使索引失效
判斷索引列是否不等于某個值時
對索引列進行運算
查詢條件使用or連接冲簿，也會導致索引失效

什么是前綴索引？

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

前綴索引是指對文本或者字符串的前幾個字符建立索引角钩，這樣索引的長度更短吝沫，查詢速度更快呻澜。

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

建立前綴索引的方式：

常見的存儲引擎有哪些？

MySQL中常用的四種存儲引擎分別是：MyISAM平道、InnoDB睹欲、MEMORY、ARCHIVE一屋。MySQL 5.5版本后默認的存儲引擎為InnoDB窘疮。

InnoDB存儲引擎

InnoDB是MySQL默認的事務型存儲引擎，使用最廣泛冀墨，基于聚簇索引建立的闸衫。InnoDB內部做了很多優(yōu)化，如能夠自動在內存中創(chuàng)建自適應hash索引诽嘉，以加速讀操作蔚出。

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

缺點：占用的數(shù)據(jù)空間相對較大骄酗。

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

MyISAM存儲引擎

數(shù)據(jù)以緊密格式存儲趋翻。對于只讀數(shù)據(jù)，或者表比較小盒蟆、可以容忍修復操作踏烙，可以使用MyISAM引擎。MyISAM會將表存儲在兩個文件中历等，數(shù)據(jù)文件.MYD和索引文件.MYI讨惩。

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

缺點：MyISAM不支持事務和行級鎖寒屯，不支持崩潰后的安全恢復荐捻，也不支持外鍵。

適用場景：對事務完整性沒有要求浩螺；表的數(shù)據(jù)都會只讀的。

MEMORY存儲引擎

MEMORY引擎將數(shù)據(jù)全部放在內存中仍侥，訪問速度較快要出，但是一旦系統(tǒng)奔潰的話，數(shù)據(jù)都會丟失农渊。

MEMORY引擎默認使用哈希索引患蹂，將鍵的哈希值和指向數(shù)據(jù)行的指針保存在哈希索引中或颊。

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

缺點：

哈希索引數(shù)據(jù)不是按照索引值順序存儲传于，無法用于排序囱挑。
不支持部分索引匹配查找，因為哈希索引是使用索引列的全部內容來計算哈希值的沼溜。
只支持等值比較平挑，不支持范圍查詢。
當出現(xiàn)哈希沖突時系草，存儲引擎需要遍歷鏈表中所有的行指針通熄，逐行進行比較，直到找到符合條件的行找都。

ARCHIVE存儲引擎

ARCHIVE存儲引擎非常適合存儲大量獨立的唇辨、作為歷史記錄的數(shù)據(jù)。ARCHIVE提供了壓縮功能能耻，擁有高效的插入速度赏枚，但是這種引擎不支持索引，所以查詢性能較差晓猛。

MyISAM和InnoDB的區(qū)別饿幅？

是否支持行級鎖 : MyISAM 只有表級鎖，而InnoDB 支持行級鎖和表級鎖鞍帝，默認為行級鎖诫睬。
是否支持事務和崩潰后的安全恢復：MyISAM 不提供事務支持。而InnoDB提供事務支持帕涌，具有事務摄凡、回滾和崩潰修復能力。
是否支持外鍵： MyISAM不支持蚓曼，而InnoDB支持亲澡。
是否支持MVCC ：MyISAM不支持，InnoDB支持纫版。應對高并發(fā)事務床绪，MVCC比單純的加鎖更高效。
MyISAM不支持聚集索引其弊，InnoDB支持聚集索引癞己。

MVCC 實現(xiàn)原理？

MVCC(Multiversion concurrency control) 就是同一份數(shù)據(jù)保留多版本的一種方式梭伐，進而實現(xiàn)并發(fā)控制痹雅。在查詢的時候，通過read view和版本鏈找到對應版本的數(shù)據(jù)糊识。

作用：提升并發(fā)性能绩社。對于高并發(fā)場景摔蓝，MVCC比行級鎖開銷更小。

MVCC 實現(xiàn)原理如下：

MVCC 的實現(xiàn)依賴于版本鏈愉耙，版本鏈是通過表的三個隱藏字段實現(xiàn)贮尉。

DB_TRX_ID：當前事務id，通過事務id的大小判斷事務的時間順序朴沿。
DB_ROLL_PRT：回滾指針猜谚，指向當前行記錄的上一個版本，通過這個指針將數(shù)據(jù)的多個版本連接在一起構成undo log版本鏈悯仙。
DB_ROLL_ID：主鍵龄毡，如果數(shù)據(jù)表沒有主鍵，InnoDB會自動生成主鍵锡垄。

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

圖片

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

用排他鎖鎖住該行货岭；
將該行原本的值拷貝到undo log路操，作為舊版本用于回滾；
修改當前行的值千贯，生成一個新版本屯仗，更新事務id，使回滾指針指向舊版本的記錄搔谴，這樣就形成一條版本鏈魁袜。

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

1敦第、初始數(shù)據(jù)如下峰弹，其中DB_ROW_ID和DB_ROLL_PTR為空。

圖片

2芜果、事務A對該行數(shù)據(jù)做了修改鞠呈，將age修改為12，效果如下：

圖片

3右钾、之后事務B也對該行記錄做了修改蚁吝，將age修改為8，效果如下：

圖片

4舀射、此時undo log有兩行記錄窘茁，并且通過回滾指針連在一起。

接下來了解下read view的概念脆烟。

read view可以理解成將數(shù)據(jù)在每個時刻的狀態(tài)拍成“照片”記錄下來山林。在獲取某時刻t的數(shù)據(jù)時，到t時間點拍的“照片”上取數(shù)據(jù)浩淘。

在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 表示每個數(shù)據(jù)行的最新的事務ID；up_limit_id表示當前快照中的最先開始的事務咙边；low_limit_id表示當前快照中的最慢開始的事務猜煮，即最后一個事務。

圖片

如果DATA_TRX_ID < up_limit_id：說明在創(chuàng)建read view時败许，修改該數(shù)據(jù)行的事務已提交王带，該版本的記錄可被當前事務讀取到。
如果DATA_TRX_ID >= low_limit_id：說明當前版本的記錄的事務是在創(chuàng)建read view之后生成的市殷，該版本的數(shù)據(jù)行不可以被當前事務訪問愕撰。此時需要通過版本鏈找到上一個版本，然后重新判斷該版本的記錄對當前事務的可見性醋寝。
如果up_limit_id <= DATA_TRX_ID < low_limit_i：

需要在活躍事務鏈表中查找是否存在ID為DATA_TRX_ID的值的事務搞挣。
如果存在，因為在活躍事務鏈表中的事務是未提交的甥桂，所以該記錄是不可見的柿究。此時需要通過版本鏈找到上一個版本，然后重新判斷該版本的可見性黄选。
如果不存在蝇摸，說明事務trx_id 已經提交了，這行記錄是可見的办陷。

總結：InnoDB 的MVCC是通過 read view 和版本鏈實現(xiàn)的貌夕，版本鏈保存有歷史版本記錄，通過read view 判斷當前版本的數(shù)據(jù)是否可見民镜，如果不可見啡专，再從版本鏈中找到上一個版本，繼續(xù)進行判斷制圈，直到找到一個可見的版本们童。

快照讀和當前讀

表記錄有兩種讀取方式畔况。

快照讀：讀取的是快照版本。普通的SELECT就是快照讀慧库。通過mvcc來進行并發(fā)控制的跷跪，不用加鎖。
當前讀：讀取的是最新版本齐板。UPDATE吵瞻、DELETE、INSERT甘磨、SELECT … LOCK IN SHARE MODE橡羞、SELECT … FOR UPDATE是當前讀。

快照讀情況下济舆，InnoDB通過mvcc機制避免了幻讀現(xiàn)象卿泽。而mvcc機制無法避免當前讀情況下出現(xiàn)的幻讀現(xiàn)象。因為當前讀每次讀取的都是最新數(shù)據(jù)滋觉，這時如果兩次查詢中間有其它事務插入數(shù)據(jù)又厉，就會產生幻讀。

下面舉個例子說明下：

1椎瘟、首先覆致，user表只有兩條記錄，具體如下：

圖片

2肺蔚、事務a和事務b同時開啟事務start transaction煌妈；

3、事務a插入數(shù)據(jù)然后提交宣羊；

4璧诵、事務b執(zhí)行全表的update；

5仇冯、事務b然后執(zhí)行查詢之宿，查到了事務a中插入的數(shù)據(jù)。（下圖左邊是事務b苛坚，右邊是事務a比被。事務開始之前只有兩條記錄，事務a插入一條數(shù)據(jù)之后泼舱，事務b查詢出來是三條數(shù)據(jù)）

圖片

以上就是當前讀出現(xiàn)的幻讀現(xiàn)象等缀。

那么MySQL是如何避免幻讀？

在快照讀情況下娇昙，MySQL通過mvcc來避免幻讀尺迂。
在當前讀情況下，MySQL通過next-key來避免幻讀（加行鎖和間隙鎖來實現(xiàn)的）。

next-key包括兩部分：行鎖和間隙鎖噪裕。行鎖是加在索引上的鎖蹲盘，間隙鎖是加在索引之間的。

Serializable隔離級別也可以避免幻讀膳音，會鎖住整張表辜限，并發(fā)性極低，一般不會使用严蓖。

共享鎖和排他鎖

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

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

申請排他鎖的前提是颗胡，沒有線程對該結果集的任何行數(shù)據(jù)使用排它鎖或者共享鎖，否則申請會受到阻塞吩坝。在進行事務操作時毒姨，MySQL會對查詢結果集的每行數(shù)據(jù)添加排它鎖，其他線程對這些數(shù)據(jù)的更改或刪除操作會被阻塞（只能讀操作）钉寝，直到該語句的事務被commit語句或rollback語句結束為止弧呐。

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

for update 僅適用于innodb，且必須在事務范圍內才能生效嵌纲。
根據(jù)主鍵進行查詢俘枫，查詢條件為like或者不等于，主鍵字段產生表鎖逮走。
根據(jù)非索引字段進行查詢鸠蚪，會產生表鎖。

大表怎么優(yōu)化师溅？

某個表有近千萬數(shù)據(jù)茅信，查詢比較慢，如何優(yōu)化墓臭？

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

限定數(shù)據(jù)的范圍窿锉。比如：用戶在查詢歷史信息的時候酌摇，可以控制在一個月的時間范圍內；
讀寫分離：經典的數(shù)據(jù)庫拆分方案嗡载，主庫負責寫妙痹，從庫負責讀；
通過分庫分表的方式進行優(yōu)化鼻疮，主要有垂直拆分和水平拆分怯伊。

bin log/redo log/undo log

MySQL日志主要包括查詢日志、慢查詢日志、事務日志耿芹、錯誤日志崭篡、二進制日志等。其中比較重要的是 bin log（二進制日志）和 redo log（重做日志）和 undo log（回滾日志）吧秕。

bin log

bin log是MySQL數(shù)據(jù)庫級別的文件琉闪，記錄對MySQL數(shù)據(jù)庫執(zhí)行修改的所有操作，不會記錄select和show語句砸彬，主要用于恢復數(shù)據(jù)庫和同步數(shù)據(jù)庫颠毙。

redo log

redo log是innodb引擎級別，用來記錄innodb存儲引擎的事務日志砂碉，不管事務是否提交都會記錄下來蛀蜜，用于數(shù)據(jù)恢復。當數(shù)據(jù)庫發(fā)生故障增蹭，innoDB存儲引擎會使用redo log恢復到發(fā)生故障前的時刻滴某，以此來保證數(shù)據(jù)的完整性。將參數(shù)innodb_flush_log_at_tx_commit設置為1滋迈，那么在執(zhí)行commit時會將redo log同步寫到磁盤霎奢。

undo log

除了記錄redo log外，當進行數(shù)據(jù)修改時還會記錄undo log饼灿，undo log用于數(shù)據(jù)的撤回操作幕侠，它保留了記錄修改前的內容。通過undo log可以實現(xiàn)事務回滾碍彭，并且可以根據(jù)undo log回溯到某個特定的版本的數(shù)據(jù)橙依，實現(xiàn)MVCC。

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

bin log會記錄所有日志記錄窗骑，包括InnoDB、MyISAM等存儲引擎的日志漆枚；redo log只記錄innoDB自身的事務日志创译。
bin log只在事務提交前寫入到磁盤，一個事務只寫一次墙基；而在事務進行過程软族，會有redo log不斷寫入磁盤。
bin log是邏輯日志残制，記錄的是SQL語句的原始邏輯立砸；redo log是物理日志，記錄的是在某個數(shù)據(jù)頁上做了什么修改初茶。

講一下MySQL架構颗祝？

圖片

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

Server 層：主要包括連接器、查詢緩存、分析器螺戳、優(yōu)化器搁宾、執(zhí)行器等，所有跨存儲引擎的功能都在這一層實現(xiàn)倔幼，比如存儲過程盖腿、觸發(fā)器、視圖损同，函數(shù)等翩腐，還有一個通用的日志模塊 binglog 日志模塊。
存儲引擎：主要負責數(shù)據(jù)的存儲和讀取膏燃。server 層通過api與存儲引擎進行通信茂卦。

Server 層基本組件

連接器： 當客戶端連接 MySQL 時，server層會對其進行身份認證和權限校驗蹄梢。
查詢緩存: 執(zhí)行查詢語句的時候，會先查詢緩存富俄，先校驗這個 sql 是否執(zhí)行過禁炒，如果有緩存這個 sql，就會直接返回給客戶端霍比，如果沒有命中幕袱，就會執(zhí)行后續(xù)的操作。
分析器: 沒有命中緩存的話悠瞬，SQL 語句就會經過分析器们豌，主要分為兩步，詞法分析和語法分析浅妆，先看 SQL 語句要做什么望迎，再檢查 SQL 語句語法是否正確。
優(yōu)化器： 優(yōu)化器對查詢進行優(yōu)化凌外，包括重寫查詢辩尊、決定表的讀寫順序以及選擇合適的索引等陪拘，生成執(zhí)行計劃傅是。
執(zhí)行器： 首先執(zhí)行前會校驗該用戶有沒有權限，如果沒有權限刊驴，就會返回錯誤信息疮薇，如果有權限胸墙，就會根據(jù)執(zhí)行計劃去調用引擎的接口，返回結果按咒。

分庫分表

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

數(shù)據(jù)切分可以分為兩種方式：垂直劃分和水平劃分补履。

垂直劃分

垂直劃分數(shù)據(jù)庫是根據(jù)業(yè)務進行劃分添坊，例如購物場景，可以將庫中涉及商品箫锤、訂單贬蛙、用戶的表分別劃分出成一個庫，通過降低單庫的大小來提高性能谚攒。同樣的阳准，分表的情況就是將一個大表根據(jù)業(yè)務功能拆分成一個個子表，例如商品基本信息和商品描述馏臭，商品基本信息一般會展示在商品列表野蝇，商品描述在商品詳情頁，可以將商品基本信息和商品描述拆分成兩張表括儒。

圖片

優(yōu)點：行記錄變小绕沈，數(shù)據(jù)頁可以存放更多記錄，在查詢時減少I/O次數(shù)帮寻。

缺點：

主鍵出現(xiàn)冗余乍狐，需要管理冗余列；
會引起表連接JOIN操作固逗，可以通過在業(yè)務服務器上進行join來減少數(shù)據(jù)庫壓力浅蚪；
依然存在單表數(shù)據(jù)量過大的問題。

水平劃分

水平劃分是根據(jù)一定規(guī)則烫罩，例如時間或id序列值等進行數(shù)據(jù)的拆分惜傲。比如根據(jù)年份來拆分不同的數(shù)據(jù)庫。每個數(shù)據(jù)庫結構一致贝攒，但是數(shù)據(jù)得以拆分操漠，從而提升性能。

圖片

優(yōu)點：單庫（表）的數(shù)據(jù)量得以減少饿这，提高性能浊伙；切分出的表結構相同，程序改動較少长捧。

缺點：

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

什么是分區(qū)表？

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

當查詢條件的數(shù)據(jù)分布在某一個分區(qū)的時候舅列，查詢引擎只會去某一個分區(qū)查詢，而不是遍歷整個表卧蜓。在管理層面帐要，如果需要刪除某一個分區(qū)的數(shù)據(jù)，只需要刪除對應的分區(qū)即可弥奸。

分區(qū)表類型

按照范圍分區(qū)榨惠。

<pre data-tool="mdnice編輯器" style="margin: 10px 0px; padding: 0px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important; color: rgb(0, 0, 0); font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">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') ) ); </pre>

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

list分區(qū)

對于List分區(qū)盛霎，分區(qū)字段必須是已知的赠橙，如果插入的字段不在分區(qū)時枚舉值中，將無法插入愤炸。

hash分區(qū)

可以將數(shù)據(jù)均勻地分布到預先定義的分區(qū)中期揪。

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

查詢語句的執(zhí)行流程如下：權限校驗规个、查詢緩存凤薛、分析器、優(yōu)化器诞仓、權限校驗缤苫、執(zhí)行器、引擎狂芋。

舉個例子榨馁，查詢語句如下：

首先檢查權限憨栽，沒有權限則返回錯誤帜矾；
MySQL8.0以前會查詢緩存，緩存命中則直接返回屑柔，沒有則執(zhí)行下一步屡萤；
詞法分析和語法分析。提取表名掸宛、查詢條件死陆，檢查語法是否有錯誤；
兩種執(zhí)行方案唧瘾，先查 id > 1 還是 name = '大彬'措译，優(yōu)化器根據(jù)自己的優(yōu)化算法選擇執(zhí)行效率最好的方案；
校驗權限饰序，有權限就調用數(shù)據(jù)庫引擎接口领虹，返回引擎的執(zhí)行結果。

更新語句執(zhí)行過程求豫？

更新語句執(zhí)行流程如下：分析器塌衰、權限校驗诉稍、執(zhí)行器、引擎最疆、redo log（prepare狀態(tài)）杯巨、binlog、redo log（commit狀態(tài)）

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

先查詢到 id 為1的記錄服爷，有緩存會使用緩存。
拿到查詢結果蚊逢，將 name 更新為大彬层扶，然后調用引擎接口，寫入更新數(shù)據(jù)烙荷，innodb 引擎將數(shù)據(jù)保存在內存中镜会，同時記錄redo log，此時redo log進入 prepare狀態(tài)终抽。
執(zhí)行器收到通知后記錄binlog戳表，然后調用引擎接口，提交redo log為commit狀態(tài)昼伴。
更新完成匾旭。

為什么記錄完redo log，不直接提交圃郊，而是先進入prepare狀態(tài)价涝？

假設先寫redo log直接提交，然后寫binlog持舆，寫完redo log后色瘩，機器掛了，binlog日志沒有被寫入逸寓，那么機器重啟后居兆，這臺機器會通過redo log恢復數(shù)據(jù)，但是這個時候binlog并沒有記錄該數(shù)據(jù)竹伸，后續(xù)進行機器備份的時候泥栖，就會丟失這一條數(shù)據(jù)，同時主從同步也會丟失這一條數(shù)據(jù)勋篓。

exist和in的區(qū)別吧享？

exists用于對外表記錄做篩選。exists會遍歷外表譬嚣，將外查詢表的每一行钢颂，代入內查詢進行判斷。當exists里的條件語句能夠返回記錄行時孤荣，條件就為真甸陌，返回外表當前記錄须揣。反之如果exists里的條件語句不能返回記錄行，條件為假钱豁，則外表當前記錄被丟棄耻卡。

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

子查詢的表比較大的時候谤碳，使用exists可以有效減少總的循環(huán)次數(shù)來提升速度溃卡；當外查詢的表比較大的時候，使用in可以有效減少對外查詢表循環(huán)遍歷來提升速度蜒简。

truncate瘸羡、delete與drop區(qū)別？

相同點：

truncate和不帶where子句的delete搓茬、以及drop都會刪除表內的數(shù)據(jù)犹赖。
drop、truncate都是DDL語句（數(shù)據(jù)定義語言）卷仑，執(zhí)行后會自動提交峻村。

不同點：

truncate 和 delete 只刪除數(shù)據(jù)不刪除表的結構；drop 語句將刪除表的結構被依賴的約束锡凝、觸發(fā)器粘昨、索引；
一般來說窜锯，執(zhí)行速度: drop > truncate > delete张肾。

having和where的區(qū)別？

二者作用的對象不同衬浑，where子句作用于表和視圖捌浩，having作用于組放刨。
where在數(shù)據(jù)分組前進行過濾工秩，having在數(shù)據(jù)分組后進行過濾。

什么是MySQL主從同步进统？

主從同步使得數(shù)據(jù)可以從一個數(shù)據(jù)庫服務器復制到其他服務器上助币，在復制數(shù)據(jù)時，一個服務器充當主服務器（master）螟碎，其余的服務器充當從服務器（slave）眉菱。

因為復制是異步進行的，所以從服務器不需要一直連接著主服務器掉分，從服務器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務器俭缓。通過配置文件克伊，可以指定復制所有的數(shù)據(jù)庫，某個數(shù)據(jù)庫华坦，甚至是某個數(shù)據(jù)庫上的某個表愿吹。

為什么要做主從同步？

讀寫分離惜姐，使數(shù)據(jù)庫能支撐更大的并發(fā)犁跪。
在主服務器上生成實時數(shù)據(jù)，而在從服務器上分析這些數(shù)據(jù)歹袁，從而提高主服務器的性能坷衍。
數(shù)據(jù)備份，保證數(shù)據(jù)的安全条舔。

樂觀鎖和悲觀鎖是什么枫耳？

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

悲觀鎖：假定會發(fā)生并發(fā)沖突嘉涌，在查詢完數(shù)據(jù)的時候就把事務鎖起來，直到提交事務夸浅。實現(xiàn)方式：使用數(shù)據(jù)庫中的鎖機制仑最。
樂觀鎖：假設不會發(fā)生并發(fā)沖突，只在提交操作時檢查是否數(shù)據(jù)是否被修改過帆喇。給表增加version字段警医，在修改提交之前檢查version與原來取到的version值是否相等，若相等坯钦，表示數(shù)據(jù)沒有被修改预皇，可以更新，否則婉刀，數(shù)據(jù)為臟數(shù)據(jù)吟温，不能更新。實現(xiàn)方式：樂觀鎖一般使用版本號機制或CAS算法實現(xiàn)突颊。

用過processlist嗎鲁豪？

show processlist 或 show full processlist 可以查看當前 MySQL 是否有壓力，正在運行的SQL律秃，有沒有慢SQL正在執(zhí)行爬橡。返回參數(shù)如下：

id：線程ID，可以用kill id殺死某個線程
db：數(shù)據(jù)庫名稱
user：數(shù)據(jù)庫用戶
host：數(shù)據(jù)庫實例的IP
command：當前執(zhí)行的命令棒动，比如Sleep糙申，Query，Connect等
time：消耗時間船惨，單位秒
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做排序

info：正在執(zhí)行的SQL語句