每日一题之 MySQL
點擊上方“朱小廝的博客”，選擇“設為星標”
后臺回復"書"，獲取
來源：r6a.cn/eKe7
事務的基本要素
原子性：事務是一個原子操作單元，其對數據的修改，要么全都執行，要么全都不執行
一致性：事務開始前和結束后，數據庫的完整性約束沒有被破壞。
隔離性：同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。
持久性：事務完成后，事務對數據庫的所有更新將被保存到數據庫，不能回滾。
Mysql的存儲引擎
InnoDB存儲引擎：InnoDB存儲引擎支持事務，其設計目標主要面向在線事務處理（OLTP）的應用。其特點是行鎖設計，支持外鍵，并支持非鎖定鎖，即默認讀取操作不會產生鎖。從Mysql5.5.8版本開始，InnoDB存儲引擎是默認的存儲引擎。
MyISAM存儲引擎：MyISAM存儲引擎不支持事務、表鎖設計，支持全文索引，主要面向一些OLAP數據庫應用。InnoDB的數據文件本身就是主索引文件，而MyISAM的主索引和數據是分開的。
NDB存儲引擎：NDB存儲引擎是一個集群存儲引擎，其結構是share nothing的集群架構，能提供更高的可用性。NDB的特點是數據全部放在內存中（從MySQL 5.1版本開始，可以將非索引數據放在磁盤上），因此主鍵查找的速度極快，并且通過添加NDB數據存儲節點可以線性地提高數據庫性能，是高可用、高性能的集群系統。NDB存儲引擎的連接操作是在MySQL數據庫層完成的，而不是在存儲引擎層完成的。這意味著，復雜的連接操作需要巨大的網絡開銷，因此查詢速度很慢。如果解決了這個問題，NDB存儲引擎的市場應該是非常巨大的。
Memory存儲引擎：Memory存儲引擎（之前稱HEAP存儲引擎）將表中的數據存放在內存中，如果數據庫重啟或發生崩潰，表中的數據都將消失。它非常適合用于存儲臨時數據的臨時表，以及數據倉庫中的緯度表。Memory存儲引擎默認使用哈希索引，而不是我們熟悉的B+樹索引。雖然Memory存儲引擎速度非常快，但在使用上還是有一定的限制。比如，只支持表鎖，并發性能較差，并且不支持TEXT和BLOB列類型。最重要的是，存儲變長字段時是按照定常字段的方式進行的，因此會浪費內存。
Archive存儲引擎：Archive存儲引擎只支持INSERT和SELECT操作，從MySQL 5.1開始支持索引。Archive存儲引擎使用zlib算法將數據行（row）進行壓縮后存儲，壓縮比一般可達1∶10。正如其名字所示，Archive存儲引擎非常適合存儲歸檔數據，如日志信息。Archive存儲引擎使用行鎖來實現高并發的插入操作，但是其本身并不是事務安全的存儲引擎，其設計目標主要是提供高速的插入和壓縮功能。
Maria存儲引擎：Maria存儲引擎是新開發的引擎，設計目標主要是用來取代原有的MyISAM存儲引擎，從而成為MySQL的默認存儲引擎。它可以看做是MyISAM的后續版本。Maria存儲引擎的特點是：支持緩存數據和索引文件，應用了行鎖設計，提供了MVCC功能，支持事務和非事務安全的選項，以及更好的BLOB字符類型的處理性能。
事務的并發問題
臟讀：事務A讀取了事務B更新的數據，然后B回滾操作，那么A讀取到的數據是臟數據
不可重復讀：事務A多次讀取同一數據，事務B在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A多次讀取同一數據時，結果不一致。
幻讀：A事務讀取了B事務已經提交的新增數據。注意和不可重復讀的區別，這里是新增，不可重復讀是更改（或刪除）。select某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入，此時就發生了幻讀。
MySQL事務隔離級別
| 讀未提交 | 是 | 是 | 是 |
| 不可重復讀 | 否 | 是 | 是 |
| 可重復讀 | 否 | 否 | 是 |
| 串行化 | 否 | 否 | 否 |
Mysql的邏輯結構
最上層的服務類似其他CS結構，比如連接處理，授權處理。
第二層是Mysql的服務層，包括SQL的解析分析優化，存儲過程觸發器視圖等也在這一層實現。
最后一層是存儲引擎的實現，類似于Java接口的實現，Mysql的執行器在執行SQL的時候只會關注API的調用，完全屏蔽了不同引擎實現間的差異。比如Select語句，先會判斷當前用戶是否擁有權限，其次到緩存（內存）查詢是否有相應的結果集，如果沒有再執行解析sql，檢查SQL 語句語法是否正確，再優化生成執行計劃，調用API執行。
SQL執行順序
SQL的執行順序：from---where--group by---having---select---order by
MVCC,redolog,undolog,binlog
undoLog 也就是我們常說的回滾日志文件 主要用于事務中執行失敗，進行回滾，以及MVCC中對于數據歷史版本的查看。由引擎層的InnoDB引擎實現,是邏輯日志,記錄數據修改被修改前的值,比如"把id='B' 修改為id = 'B2' ，那么undo日志就會用來存放id ='B'的記錄”。當一條數據需要更新前,會先把修改前的記錄存儲在undolog中,如果這個修改出現異常,,則會使用undo日志來實現回滾操作,保證事務的一致性。當事務提交之后，undo log并不能立馬被刪除,而是會被放到待清理鏈表中,待判斷沒有事物用到該版本的信息時才可以清理相應undolog。它保存了事務發生之前的數據的一個版本，用于回滾，同時可以提供多版本并發控制下的讀（MVCC），也即非鎖定讀。
redoLog 是重做日志文件是記錄數據修改之后的值，用于持久化到磁盤中。redo log包括兩部分：一是內存中的日志緩沖(redo log buffer)，該部分日志是易失性的；二是磁盤上的重做日志文件(redo log file)，該部分日志是持久的。由引擎層的InnoDB引擎實現,是物理日志,記錄的是物理數據頁修改的信息,比如“某個數據頁上內容發生了哪些改動”。當一條數據需要更新時,InnoDB會先將數據更新，然后記錄redoLog 在內存中，然后找個時間將redoLog的操作執行到磁盤上的文件上。不管是否提交成功我都記錄，你要是回滾了，那我連回滾的修改也記錄。它確保了事務的持久性。每個InnoDB存儲引擎至少有1個重做日志文件組（group），每個文件組下至少有2個重做日志文件，如默認的ib_logfile0和ib_logfile1。為了得到更高的可靠性，用戶可以設置多個的鏡像日志組（mirrored log groups），將不同的文件組放在不同的磁盤上，以此提高重做日志的高可用性。在日志組中每個重做日志文件的大小一致，并以循環寫入的方式運行。InnoDB存儲引擎先寫重做日志文件1，當達到文件的最后時，會切換至重做日志文件2，再當重做日志文件2也被寫滿時，會再切換到重做日志文件1中。
MVCC多版本并發控制是MySQL中基于樂觀鎖理論實現隔離級別的方式，用于讀已提交和可重復讀取隔離級別的實現。在MySQL中，會在表中每一條數據后面添加兩個字段：最近修改該行數據的事務ID，指向該行（undolog表中）回滾段的指針。Read View判斷行的可見性，創建一個新事務時，copy一份當前系統中的活躍事務列表。意思是，當前不應該被本事務看到的其他事務id列表。已提交讀隔離級別下的事務在每次查詢的開始都會生成一個獨立的ReadView,而可重復讀隔離級別則在第一次讀的時候生成一個ReadView，之后的讀都復用之前的ReadView。
binlog和redolog的區別
redolog是在InnoDB存儲引擎層產生，而binlog是MySQL數據庫的上層服務層產生的。
兩種日志記錄的內容形式不同。MySQL的binlog是邏輯日志，其記錄是對應的SQL語句，對應的事務。而innodb存儲引擎層面的重做日志是物理日志，是關于每個頁（Page）的更改的物理情況。
兩種日志與記錄寫入磁盤的時間點不同，binlog日志只在事務提交完成后進行一次寫入。而innodb存儲引擎的重做日志在事務進行中不斷地被寫入，并日志不是隨事務提交的順序進行寫入的。
binlog不是循環使用，在寫滿或者重啟之后，會生成新的binlog文件，redolog是循環使用。
binlog可以作為恢復數據使用，主從復制搭建，redolog作為異常宕機或者介質故障后的數據恢復使用。
Mysql讀寫分離以及主從同步
原理：主庫將變更寫binlog日志，然后從庫連接到主庫后，從庫有一個IO線程，將主庫的binlog日志拷貝到自己本地，寫入一個中繼日志中，接著從庫中有一個sql線程會從中繼日志讀取binlog，然后執行binlog日志中的內容，也就是在自己本地再執行一遍sql，這樣就可以保證自己跟主庫的數據一致。
問題：這里有很重要一點，就是從庫同步主庫數據的過程是串行化的，也就是說主庫上并行操作，在從庫上會串行化執行，由于從庫從主庫拷貝日志以及串行化執行sql特點，在高并發情況下，從庫數據一定比主庫慢一點，是有延時的，所以經常出現，剛寫入主庫的數據可能讀不到了，要過幾十毫秒，甚至幾百毫秒才能讀取到。還有一個問題，如果突然主庫宕機了，然后恰巧數據還沒有同步到從庫，那么有些數據可能在從庫上是沒有的，有些數據可能就丟失了。所以mysql實際上有兩個機制，一個是半同步復制，用來解決主庫數據丟失問題，一個是并行復制，用來解決主從同步延時問題。
半同步復制：semi-sync復制，指的就是主庫寫入binlog日志后，就會將強制此時立即將數據同步到從庫，從庫將日志寫入自己本地的relay log之后，接著會返回一個ack給主庫，主庫接收到至少一個從庫ack之后才會認為寫完成。
并發復制：指的是從庫開啟多個線程，并行讀取relay log中不同庫的日志，然后并行重放不同庫的日志，這樣庫級別的并行。（將主庫分庫也可緩解延遲問題）
Next-Key Lock
InnoDB 采用 Next-Key Lock 解決幻讀問題。在insert into test(xid) values (1), (3), (5), (8), (11);后，由于xid上是有索引的，該算法總是會去鎖住索引記錄。現在，該索引可能被鎖住的范圍如下：(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)。Session A（select * from test where id = 8 for update）執行后會鎖住的范圍：(5, 8], (8, 11]。除了鎖住8所在的范圍，還會鎖住下一個范圍，所謂Next-Key。
InnoDB的關鍵特性
插入緩沖：對于非聚集索引的插入或更新操作，不是每一次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個Insert Buffer對象中。然后再以一定的頻率和情況進行Insert Buffer和輔助索引頁子節點的merge（合并）操作，這時通常能將多個插入合并到一個操作中（因為在一個索引頁中），這就大大提高了對于非聚集索引插入的性能。
兩次寫：兩次寫帶給InnoDB存儲引擎的是數據頁的可靠性，有經驗的DBA也許會想，如果發生寫失效，可以通過重做日志進行恢復。這是一個辦法。但是必須清楚地認識到，如果這個頁本身已經發生了損壞（物理到page頁的物理日志成功頁內邏輯日志失敗），再對其進行重做是沒有意義的。這就是說，在應用（apply）重做日志前，用戶需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做。在對緩沖池的臟頁進行刷新時，并不直接寫磁盤，而是會通過memcpy函數將臟頁先復制到內存中的doublewrite buffer，之后通過doublewrite buffer再分兩次，每次1MB順序地寫入共享表空間的物理磁盤上，這就是doublewrite。
自適應哈希索引：InnoDB存儲引擎會監控對表上各索引頁的查詢。如果觀察到建立哈希索引可以帶來速度提升，則建立哈希索引，稱之為自適應哈希索引。
異步IO：為了提高磁盤操作性能，當前的數據庫系統都采用異步IO（AIO）的方式來處理磁盤操作。AIO的另一個優勢是可以進行IO Merge操作，也就是將多個IO合并為1個IO，這樣可以提高IOPS的性能。
刷新鄰接頁：當刷新一個臟頁時，InnoDB存儲引擎會檢測該頁所在區（extent）的所有頁，如果是臟頁，那么一起進行刷新。這樣做的好處顯而易見，通過AIO可以將多個IO寫入操作合并為一個IO操作，故該工作機制在傳統機械磁盤下有著顯著的優勢。
Mysql如何保證一致性和持久性
MySQL為了保證ACID中的一致性和持久性，使用了WAL(Write-Ahead Logging,先寫日志再寫磁盤)。Redo log就是一種WAL的應用。當數據庫忽然掉電，再重新啟動時，MySQL可以通過Redo log還原數據。也就是說，每次事務提交時，不用同步刷新磁盤數據文件，只需要同步刷新Redo log就足夠了。
InnoDB的行鎖模式
共享鎖(S)：用法lock in share mode，又稱讀鎖，允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。若事務T對數據對象A加上S鎖，則事務T可以讀A但不能修改A，其他事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。
排他鎖(X)：用法for update，又稱寫鎖，允許獲取排他鎖的事務更新數據，阻止其他事務取得相同的數據集共享讀鎖和排他寫鎖。若事務T對數據對象A加上X鎖，事務T可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到T釋放A上的鎖。在沒有索引的情況下，InnoDB只能使用表鎖。
為什么選擇B+樹作為索引結構
Hash索引：Hash索引底層是哈希表，哈希表是一種以key-value存儲數據的結構，所以多個數據在存儲關系上是完全沒有任何順序關系的，所以，對于區間查詢是無法直接通過索引查詢的，就需要全表掃描。所以，哈希索引只適用于等值查詢的場景。而B+ 樹是一種多路平衡查詢樹，所以他的節點是天然有序的（左子節點小于父節點、父節點小于右子節點），所以對于范圍查詢的時候不需要做全表掃描
二叉查找樹：解決了排序的基本問題，但是由于無法保證平衡，可能退化為鏈表。
平衡二叉樹：通過旋轉解決了平衡的問題，但是旋轉操作效率太低。
紅黑樹：通過舍棄嚴格的平衡和引入紅黑節點，解決了 AVL旋轉效率過低的問題，但是在磁盤等場景下，樹仍然太高，IO次數太多。
B+樹：在B樹的基礎上，將非葉節點改造為不存儲數據純索引節點，進一步降低了樹的高度；此外將葉節點使用指針連接成鏈表，范圍查詢更加高效。
B+樹的葉子節點都可以存哪些東西
可能存儲的是整行數據，也有可能是主鍵的值。B+樹的葉子節點存儲了整行數據的是主鍵索引，也被稱之為聚簇索引。而索引B+ Tree的葉子節點存儲了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引
覆蓋索引
指一個查詢語句的執行只用從索引中就能夠取得，不必從數據表中讀取。也可以稱之為實現了索引覆蓋。
查詢在什么時候不走（預期中的）索引
模糊查詢 %like
索引列參與計算,使用了函數
非最左前綴順序
where對null判斷
where不等于
or操作有至少一個字段沒有索引
需要回表的查詢結果集過大（超過配置的范圍）
數據庫優化指南
創建并使用正確的索引
只返回需要的字段
減少交互次數（批量提交）
設置合理的Fetch Size（數據每次返回給客戶端的條數）
想知道更多？掃描下面的二維碼關注我
后臺回復"技術"，加入技術群
【精彩推薦】
超清晰的DNS入門指南
如何用ELK搭建TB級的日志系統
深度好文：Linux系統內存知識
日志采集系統都用到哪些技術？
面試官：為什么HashMap的加載因子是0.75？
原創|OpenAPI標準規范
如此簡單| ES最全詳細使用教程
ClickHouse到底是什么？為什么如此牛逼！
原來ElasticSearch還可以這么理解
點個贊+在看，少個 bug?????
總結
以上是生活随笔為你收集整理的每日一题之 MySQL的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 抛弃 Java 改用 Kotlin 的六
- 下一篇： 最精美详尽的 HTTPS 原理图