每日一题之 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 原理图