先拋出幾個問題
1.為什么不建議使用訂單號作為主鍵?
2.為什么要在需要排序的字段上加索引?
3.for update 的記錄不存在會導(dǎo)致鎖住全表?
4.redolog 和 binlog 有什么區(qū)別?
5.MySQL 如何回滾一條 sql ?
6.char(50) 和 varchar(50) 效果是一樣的么?
索引知識回顧
對于 MySQL 數(shù)據(jù)庫而言,數(shù)據(jù)是存儲在文件里的,而為了能夠快速定位到某張表里的某條記錄進(jìn)行查詢和修改,我們需要將這些數(shù)據(jù)以一定的數(shù)據(jù)結(jié)構(gòu)進(jìn)行存儲蛙粘,這個數(shù)據(jù)結(jié)構(gòu)就是我們說的索引萍歉∧把。回憶一下我們大學(xué)里學(xué)過的算法與數(shù)據(jù)結(jié)構(gòu),能夠支持快速查找的數(shù)據(jù)結(jié)構(gòu)有:順序數(shù)組蕊苗、哈希、搜索樹。
數(shù)組要求 insert 的時候保證有序是尔,這樣查找的時候可以利用二分查找法達(dá)到 O(log(N)) 的時間復(fù)雜度,對范圍查詢支持也很好开仰,但是 insert 的時候如果不是在數(shù)組尾部拟枚,就需要摞動后面所有的數(shù)據(jù),時間復(fù)雜度為 O(N) 众弓。所以有序數(shù)組只適合存儲靜態(tài)數(shù)據(jù)恩溅,例如幾乎很少變動的配置數(shù)據(jù),或者是歷史數(shù)據(jù)谓娃。這里應(yīng)該會有人有疑問:我用另外一種線性數(shù)據(jù)結(jié)構(gòu)鏈表來替代數(shù)組不就可以解決數(shù)組因為要移動數(shù)據(jù)導(dǎo)致太慢的問題了么脚乡,要回答這個問題我們需要了解操作系統(tǒng)讀取文件的流程,磁盤 IO 是一個相對很慢的操作滨达,為了提高讀取速度每窖,我們應(yīng)該盡量減少磁盤 IO 操作,而操作系統(tǒng)一般以∠蚁ぁ4kb 為一個數(shù)據(jù)頁讀取數(shù)據(jù)窒典,而 MySQL 一般為 16kb 作為一個數(shù)據(jù)塊,已經(jīng)讀取的數(shù)據(jù)塊會在內(nèi)存進(jìn)行緩存稽莉,如果多次數(shù)據(jù)讀取在同一個數(shù)據(jù)塊瀑志,則只需要一次磁盤 IO ,而如果順序一致的記錄在文件中也是順序存儲的污秆,就可以一次讀取多個數(shù)據(jù)塊劈猪,這樣范圍查詢的速度也可以大大提升,顯然鏈表沒有這方面的優(yōu)勢良拼。
類似于 jdk 中的 hashmap 战得,哈希表通過一個特定的哈希函數(shù)將 key 值轉(zhuǎn)換為一個固定的地址,然后將對應(yīng)的 value 放到這個位置庸推,如果發(fā)生哈希碰撞就在這個位置拉出一個鏈表常侦,由于哈希函數(shù)的離散特性浇冰,所以經(jīng)過哈希函數(shù)處理后的 key 將失去原有的順序,所以哈希結(jié)構(gòu)的索引無法滿足范圍查詢聋亡,只適合等值查詢的情況例如一些緩存的場景肘习。
二叉樹在極端情況下會變成線性結(jié)構(gòu),也就是每個節(jié)點都只有左子節(jié)點或者只有右子節(jié)點坡倔,這樣就無法利用二分查找只能從第一個節(jié)點開始向后遍歷了漂佩,所以為了維持 O(log(N)) 的時間復(fù)雜度,我們需要在插入節(jié)點的時候?qū)?jié)點進(jìn)行調(diào)整以保證樹的平衡罪塔,所以平衡二叉樹插入的時間復(fù)雜度也是 O(log(N)) 投蝉,二叉樹只有兩個子節(jié)點,如果數(shù)據(jù)量很大則樹就很高征堪,樹的每一層一般不在同一個數(shù)據(jù)塊中存儲瘩缆,為了盡量的減少磁盤讀寫次數(shù),我們用N叉樹來代替二叉樹请契,在 MySQL 中這個N一般為 1200 咳榜,這樣樹高是∠淖怼4 的話也可以存儲億級別的數(shù)據(jù)爽锥,而且樹的前面兩層一般都在內(nèi)存中, MySQL 中用到的∨先帷B+ 樹氯夷,一般用非葉子節(jié)點構(gòu)建索引,而葉子節(jié)點用來存儲具體的值靶擦。
InnoDB 中腮考,有聚簇索引和普通索引之分,聚簇索引根據(jù)主鍵來構(gòu)建玄捕,葉子節(jié)點存放的是該主鍵對應(yīng)的這一行記錄踩蔚,而普通索引根據(jù)申明這個索引時候的列來構(gòu)建,葉子節(jié)點存放的是這一行記錄對應(yīng)的主鍵的值枚粘,而普通索引中還有唯一索引和聯(lián)合索引兩個特例馅闽,唯一索引在插入和修改的時候會校驗該索引對應(yīng)的列的值是否已經(jīng)存在,而聯(lián)合索引將兩個列的值按照申明時候的順序進(jìn)行拼接后在構(gòu)建索引馍迄。
根據(jù)以上描述我們可以得到以下信息:
數(shù)據(jù)是以行為單位存儲在聚簇索引里的福也,根據(jù)主鍵查詢可以直接利用聚簇索引定位到所在記錄,根據(jù)普通索引查詢需要先在普通索引上找到對應(yīng)的主鍵的值攀圈,然后根據(jù)主鍵值去聚簇索引上查找記錄暴凑,俗稱回表。
普通索引上存儲的值是主鍵的值赘来,如果主鍵是一個很長的字符串并且建了很多普通索引现喳,將造成普通索引占有很大的物理空間凯傲,這也是為什么建議使用 自增ID 來替代訂單號作為主鍵,另一個原因是 自增ID 在 insert 的時候可以保證相鄰的兩條記錄可能在同一個數(shù)據(jù)塊拿穴,而訂單號的連續(xù)性在設(shè)計上可能沒有自增ID好泣洞,導(dǎo)致連續(xù)插入可能在多個數(shù)據(jù)塊,增加了磁盤讀寫次數(shù)默色。
如果我們查詢一整行記錄的話球凰,一定要去聚簇索引上查找,而如果我們只需要根據(jù)普通索引查詢主鍵的值腿宰,由于這些值在普通索引上已經(jīng)存在呕诉,所以并不需要回表,這個稱為索引覆蓋吃度,在一定程度上可以提高查詢效率甩挫,由于聯(lián)合索引上通過多個列構(gòu)建索引,有時候我們可以將需要頻繁查詢的字段加到聯(lián)合索引里面椿每,例如如果經(jīng)常需要根據(jù) name 查找 age 我們可以建一個 name 和 age 的聯(lián)合索引伊者。
查詢的時候如果在索引上用了函數(shù),將導(dǎo)致無法用到根據(jù)之前列上的值構(gòu)建的索引间护,索引遵循最左匹配原則亦渗,所以如果需要查詢某個列的值中間是否包含某個字符串,將無法利用索引汁尺,如果有這種需求可以利用全文索引法精,而如果查詢是否以某個字符串開頭就可以,聯(lián)合索引根據(jù)第一個列查詢可以用到索引痴突,僅僅根據(jù)第二個列將無法用到索引搂蜓,查詢的時候用 IN 的效率高于 NOT = 。另外建議將索引的列設(shè)置為非空辽装,這個和 NULL 字段的存儲有關(guān)帮碰,下文在分析。
存儲格式
有了以上的索引知識我們在來分析數(shù)據(jù)是怎么存儲的拾积,InnoDB 存儲引擎的邏輯存儲結(jié)構(gòu)從大到小依次可以分為:表空間殉挽、段、區(qū)殷勘、頁此再、行。
表空間作為存儲結(jié)構(gòu)的最高層玲销,所有數(shù)據(jù)都存放在表空間中输拇,默認(rèn)情況下用一個共享表空間 ibdata1 ,如果開啟了 innodb_file_per_table 則每張表的數(shù)據(jù)將存儲在單獨的表空間中贤斜,也就是每張表都會有一個文件策吠,
表空間由各個段構(gòu)成逛裤,InnoDB存儲引擎由索引組織的,而索引中的葉子節(jié)點用來記錄數(shù)據(jù)猴抹,存儲在數(shù)據(jù)段带族,而非葉子節(jié)點用來構(gòu)建索引,存儲在索引段蟀给,而回滾段我們在后面分析鎖的時候在聊蝙砌。
區(qū)是由連續(xù)的頁組成,任何情況下一個區(qū)都是 1MB 跋理,
一個區(qū)中可以有多個頁择克,每個頁默認(rèn)為 16KB ,所以默認(rèn)情況下一個區(qū)中可以包含64個連續(xù)的頁前普,頁的大小是可以通過 innodb_page_size 設(shè)置肚邢,頁中存儲的是具體的行記錄。一行記錄最終以二進(jìn)制的方式存儲在文件里拭卿,我們要能夠解析出一行記錄中每個列的值骡湖,存儲的時候就需要有固定的格式,至少需要知道每個列占多少空間峻厚,而 MySQL 中定義了一些固定長度的數(shù)據(jù)類型响蕴,例如 int、tinyint目木、bigint换途、char數(shù)組懊渡、float刽射、double、date剃执、datetime誓禁、timestamp 等,這些字段我們只需要讀取對應(yīng)長度的字節(jié)肾档,然后根據(jù)類型進(jìn)行解析即可摹恰,對于變長字段,例如 varchar怒见、varbinary 等俗慈,需要有一個位置來單獨存儲字段實際用到的長度,當(dāng)然還需要頭信息來存儲元數(shù)據(jù)遣耍,例如記錄類型闺阱,下一條記錄的位置等。下面我們以 Compact 行格式分析一行數(shù)據(jù)在 InnoDB 中是怎么存儲的舵变。
變長字段長度列表酣溃,該位置用來存儲所申明的變長字段中非空字段實際占有的長度列表瘦穆,例如有3個非空字段,其中第一個字段長度為3赊豌,第二個字段為空扛或,第三個字段長度為1,則將用 01 03 表示碘饼,為空字段將在下一個位置進(jìn)行標(biāo)記熙兔。變長字段長度不能超過 2 個字節(jié)艾恼,所以 varchar 的長度最大為 65535蒂萎。
NULL 標(biāo)志位五慈,占 1 個字節(jié)毙芜,如果對應(yīng)的列為空則在對應(yīng)的位上置為 1 腋粥,否則為 0 隘冲,由于該標(biāo)志位占一個字節(jié),所以列的數(shù)量不能超過 255罗珍。如果某字段為空脚粟,在后面具體的列數(shù)據(jù)中將不會在記錄核无。這種方式也導(dǎo)致了在處理索引字段為空的時候需要進(jìn)行額外的操作。
記錄頭信息堕担,固定占 5 字節(jié),包含下一條記錄的位置齐疙,該行記錄總長度,記錄類型,是否被刪除勾缭,對應(yīng)的 slot 信息等
列數(shù)據(jù) 包含具體的列對應(yīng)的值,加上兩個隱藏列幻梯,事務(wù) ID 列和回滾指針列努释。如果沒有申明主鍵碘梢,還會增加一列記錄內(nèi)部 ID洽洁。
下面我們以《MySQL 技術(shù)內(nèi)幕》第二版中的例子分析下一行記錄在表空間具體的存儲結(jié)構(gòu)菲嘴。
CREATETABLEmytest(t1varchar(10),t2varchar(10),t3char(10),t4varchar(10))engine=innodb;insertintomytestVALUES('a','bb','bb','ccc');insertintomytestVALUES('d',NULL,NULL,'fff');
該表定義了 3 個變長字段和 1 個定長字段饿自,然后插入兩行記錄龄坪,第二行記錄包含空值昭雌,我們打開表空間 mytest.ibd 文件健田,轉(zhuǎn)換為 16 進(jìn)制烛卧,并定位到如下內(nèi)容:
//第一行記錄030201為變長字段長度列表,這里是倒序存放的,分別對應(yīng) ccc炬搭、bb融虽、a 的長度。00表示沒有為空的字段000010002c 為記錄頭0000002b6800沒有申明主鍵灼芭,維護(hù)內(nèi)部 ID000000000605事務(wù)ID80000000320110回滾指針61第一列 a 的值6262第二列 bb 的值62622020202020202020第三列 bb 的值有额,固定長度char(10) 以20進(jìn)行填充636363第四列 ccc 的值//第二行記錄0301為變長字段長度列表,這里是倒序存放的彼绷,分別對應(yīng) fff谆吴、a 的長度,第二列位空苛预。06轉(zhuǎn)換為二進(jìn)制為00000110表示第二列和第三列為空000020ff98為記錄頭0000002b6801沒有申明主鍵句狼,維護(hù)內(nèi)部 ID000000000606事務(wù)ID80000000320110回滾指針64第一列 d 的值656565第四列 fff 的值
到此,我們了解了一個數(shù)據(jù)行是怎么存儲的热某,然而數(shù)據(jù)行并不是存儲引擎管理的最小存儲單位腻菇,索引只能夠幫助我們定位到某個數(shù)據(jù)頁,每一次磁盤讀寫的最小單位為也是數(shù)據(jù)頁昔馋,而一個數(shù)據(jù)頁內(nèi)存儲了多個數(shù)據(jù)行筹吐,我們需要了解數(shù)據(jù)頁的內(nèi)部結(jié)構(gòu)才能知道存儲引擎怎么定位到某一個數(shù)據(jù)行。InnoDB 的數(shù)據(jù)頁由以下 7 個部分組成:
文件頭(File Header) 固定 38 個字節(jié) (頁的位置秘遏,上一頁下一頁位置丘薛,checksum , LSN)
數(shù)據(jù)頁頭( Page Header)固定 56 個字節(jié) 包含slot數(shù)目,可重用空間起始地址邦危,第一個記錄地址洋侨,記錄數(shù),最大事務(wù)ID等
虛擬的最大最小記錄 (Infimum + Supremum Record)
用戶記錄 (User Records) 包含已經(jīng)刪除的記錄以鏈表的形式構(gòu)成可重用空間
待分配空間 (Free spaces) 未分配的空間
頁目錄 (Page Directory) slot 信息倦蚪,下面單獨介紹
文件尾 (File Trailer) 固定8個字節(jié),用來保證頁的完整性
頁目錄里維護(hù)多個 slot 希坚,一個 slot 包含多個行記錄。每個 slot 占 2 個字節(jié)陵且,記錄這個 slot 里的行記錄相對頁初始位置的偏移量裁僧。由于索引只能定位到數(shù)據(jù)頁,而定位到數(shù)據(jù)頁內(nèi)的行記錄還需要在內(nèi)存中進(jìn)行二分查找,而這個二分查找就需要借助 slot 信息聊疲,先找到對應(yīng)的 slot 茬底,然后在 slot 內(nèi)部通過數(shù)據(jù)行中記錄頭里的下一個記錄地址進(jìn)行遍歷。每一個 slot 可以包含 4 到 8 個數(shù)據(jù)行获洲。如果沒有 slot 輔助桩警,鏈表本身是無法進(jìn)行二分查找的。
排序
排序有好多種算法來實現(xiàn)昌妹,在 MySQL 中經(jīng)常會帶上一個 limit ,表示從排序后的結(jié)果集中取前 100 條捶枢,或者取第 n 條到第 m 條,要實現(xiàn)排序飞崖,我們需要先根據(jù)查詢條件獲取結(jié)果集烂叔,然后在內(nèi)存中對這個結(jié)果集進(jìn)行排序,如果結(jié)果集數(shù)量特別大固歪,還需要將結(jié)果集寫入到多個文件里蒜鸡,然后單獨對每個文件里的數(shù)據(jù)進(jìn)行排序,然后在文件之間進(jìn)行歸并牢裳,排序完成后在進(jìn)行 limit 操作逢防。沒錯,這個就是 MySQL 實現(xiàn)排序的方式蒲讯,前提是排序的字段沒有索引忘朝。
CREATETABLE`person`(`id`int(11)NOTNULL,`city`varchar(16)NOTNULL,`name`varchar(16)NOTNULL,`age`int(11)NOTNULL,`addr`varchar(128)DEFAULTNULL,? PRIMARYKEY(`id`),KEY`city`(`city`))ENGINE=InnoDB;selectcity,name,agefrompersonwherecity='武漢'orderbynamelimit100;
使用 explain 發(fā)現(xiàn)該語句會使用 city 索引,并且會有 filesort . 我們分析下該語句的執(zhí)行流程
1.初始化 sortbuffer 判帮,用來存放結(jié)果集
2.找到 city 索引局嘁,定位到 city 等于武漢的第一條記錄,獲取主鍵索引ID
3.根據(jù) ID 去主鍵索引上找到對應(yīng)記錄晦墙,取出 city,name,age 字段放入 sortbuffer
4.在 city 索引取下一個 city 等于武漢的記錄的主鍵ID
5.重復(fù)上面的步驟悦昵,直到所有 city 等于武漢的記錄都放入 sortbuffer
6.對 sortbuffer 里的數(shù)據(jù)根據(jù) name 做快速排序
7.根據(jù)排序結(jié)果取前面 1000 條返回
這里是查詢 city,name,age 3個字段,比較少晌畅,如果查詢的字段較多但指,則多個列如果都放入 sortbuffer 將占有大量內(nèi)存空間,另一個方案是只區(qū)出待排序的字段和主鍵放入 sortbuffer 這里是 name 和 id ,排序完成后在根據(jù) id 取出需要查詢的字段返回抗楔,其實就是時間換取空間的做法棋凳,這里通過 max_length_for_sort_data 參數(shù)控制,是否采用后面的方案進(jìn)行排序谓谦。
另外如果 sortbuffer 里的條數(shù)很多贫橙,同樣會占有大量的內(nèi)存空間,可以通過參數(shù) sort_buffer_size 來控制是否需要借助文件進(jìn)行排序反粥,這里會把 sortbuffer 里的數(shù)據(jù)放入多個文件里,用歸并排序的思路最終輸出一個大的文件。
以上方案主要是 name 字段沒有加上索引才顿,如果 name 字段上有索引莫湘,由于索引在構(gòu)建的時候已經(jīng)是有序的了,所以就不需要進(jìn)行額外的排序流程只需要在查詢的時候查出指定的條數(shù)就可以了郑气,這將大大提升查詢速度幅垮。我們現(xiàn)在加一個 city 和 name 的聯(lián)合索引。
altertablepersonaddindexcity_user(city,name);
這樣查詢過程如下:
1.根據(jù) city,name 聯(lián)合索引定位到 city 等于武漢的第一條記錄尾组,獲取主鍵索引ID
2.根據(jù) ID 去主鍵索引上找到對應(yīng)記錄忙芒,取出 city,name,age 字段作為結(jié)果集返回
3.繼續(xù)重復(fù)以上步驟直到 city 不等于武漢,或者條數(shù)大于 1000
由于聯(lián)合所以在構(gòu)建索引的時候讳侨,在 city 等于武漢的索引節(jié)點中的數(shù)據(jù)已經(jīng)是根據(jù) name 進(jìn)行排序了的呵萨,所以這里只需要直接查詢就可,另外這里如果加上 city, name, age 的聯(lián)合索引跨跨,則可以用到索引覆蓋潮峦,不行到主鍵索引上進(jìn)行回表。
總結(jié)一下勇婴,我們在有排序操作的時候忱嘹,最好能夠讓排序字段上建有索引,另外由于查詢第一百萬條開始的一百條記錄耕渴,需要過濾掉前面一百萬條記錄拘悦,即使用到索引也很慢,所以可以根據(jù) ID 來進(jìn)行區(qū)分橱脸,分頁遍歷的時候每次緩存上一次查詢結(jié)果最后一條記錄的 id 窄做, 下一次查詢加上 id > xxxx limit 0,1000 這樣可以避免前期掃描到的結(jié)果被過濾掉的情況。
InnoDB 存儲模型
InnoDB 通過一些列后臺線程將相關(guān)操作進(jìn)行異步處理慰技,如下圖所示椭盏,同時借助緩沖池來減小 CPU 和磁盤速度上的差異。當(dāng)查詢的時候會先通過索引定位到對應(yīng)的數(shù)據(jù)頁吻商,然后檢測數(shù)據(jù)頁是否在緩沖池內(nèi)掏颊,如果在就直接返回,如果不在就去聚簇索引中通過磁盤 IO 讀取對應(yīng)的數(shù)據(jù)頁并放入緩沖池艾帐。一個數(shù)據(jù)頁會包含多個數(shù)據(jù)行乌叶。緩存池通過 LRU 算法對數(shù)據(jù)頁進(jìn)行管理,也就是最頻繁使用的數(shù)據(jù)頁排在列表前面柒爸,不經(jīng)常使用的排在隊尾准浴,當(dāng)緩沖池滿了的時候會淘汰掉隊尾的數(shù)據(jù)頁。從磁盤新讀取到的數(shù)據(jù)頁并不會放在隊列頭部而是放在中間位置捎稚,這個中間位置可以通過參數(shù)進(jìn)行修乐横。緩沖池也可以設(shè)置多個實例求橄,數(shù)據(jù)頁根據(jù)哈希算法決定放在哪個緩沖池。
InnoDB 在更新數(shù)據(jù)的時候會采用 WAL 技術(shù)葡公,也就是 Write Ahead Logging 罐农,這個日志就是 redolog 用來保證數(shù)據(jù)庫宕機后可以通過該文件進(jìn)行恢復(fù)。這個文件一般只會順序?qū)懘呤玻挥性跀?shù)據(jù)庫啟動的時候才會讀取 redolog 文件看是否需要進(jìn)行恢復(fù)涵亏。該文件記錄了對某個數(shù)據(jù)頁的物理操作,例如某個 sql 把某一行的某個列的值改為 10 蒲凶,對應(yīng)的 redolog 文件格式可能為:把第5個數(shù)據(jù)頁中偏移量為99的位置寫入一個值 10 气筋。redolog 不是無限大的,他的大小是可以配置的旋圆,并且是循環(huán)使用的宠默,例如配置大小為 4G ,一共 4 個文件臂聋,每個文件 1G 光稼。 首先從第一個文件開始順序?qū)懀瑢懙降谒膫€文件后在從第一個文件開始寫孩等,類似一個環(huán)艾君,用一個后臺線程把 redolog 里的數(shù)據(jù)同步到聚簇索引上的數(shù)據(jù)頁上。寫入 redolog 的時候不能將沒有同步到數(shù)據(jù)頁上的記錄覆蓋肄方,如果碰到這種情況會停下來先進(jìn)行數(shù)據(jù)頁同步然后在繼續(xù)寫入 redolog 冰垄。另外執(zhí)行更新操作的時候,會先更新緩沖池里的數(shù)據(jù)頁权她,然后寫入 redolog 虹茶, 這個時候真正存儲數(shù)據(jù)的地方還沒有更新,也就是說這時候緩沖池中的數(shù)據(jù)頁和磁盤不一致隅要,這種數(shù)據(jù)頁稱為臟頁蝴罪,當(dāng)臟頁由于內(nèi)存不足或者其他原因需要丟棄的時候,一定要先將該臟頁對應(yīng)的redolog 刷新到磁盤里的真實數(shù)據(jù)頁步清,不然下次查詢的時候由于 redolog 沒有同步到磁盤要门,而查詢直接通過索引定位到數(shù)據(jù)頁就會查詢出臟數(shù)據(jù)。
更新的時候先從磁盤或者緩沖池中讀取對應(yīng)的數(shù)據(jù)頁廓啊,然后對數(shù)據(jù)頁里的數(shù)據(jù)進(jìn)行更改并生成 redolog 到對應(yīng)的緩沖池(redolog buffer)進(jìn)行緩存欢搜,當(dāng)事務(wù)提交的時候?qū)⒕彺鎸懭氲健edolog 的物理磁盤文件上。這里由于操作系統(tǒng)的文件寫入 InnoDB 并沒有使用 O_DIRECT 直接寫入到文件谴轮,為了保證性能而是先寫入操作系統(tǒng)的緩存炒瘟,之后在進(jìn)行 flush ,所以事務(wù)提交的時候 InnoDB 需要在調(diào)用一次 fsync 的系統(tǒng)調(diào)用來確保數(shù)據(jù)落盤第步。為了提高性能 InnoDB 可以通過參數(shù) innodb_flush_log_at_trx_commit 來控制事務(wù)提交時是否強制刷盤疮装。默認(rèn)為 1 缘琅,事務(wù)每次提交都需要調(diào)用 fsync 進(jìn)行刷盤,0 表示事務(wù)提交的時候不會調(diào)用 redolog 的文件寫入斩个,通過后臺線程每秒同步一次胯杭,2 表示事務(wù)提交的時候會寫入文件但是只保證寫入操作系統(tǒng)緩存驯杜,不進(jìn)行 fsync 操作受啥。 redolog 文件只會順序?qū)懀源疟P操作性能不會太慢鸽心,所以建議生產(chǎn)環(huán)境都設(shè)置為」鼍帧1 ,以防止數(shù)據(jù)庫宕機導(dǎo)致數(shù)據(jù)丟失顽频。
在執(zhí)行更新邏輯的時候還會寫入另外一個日志:undolog 藤肢。這個文件存儲在共享表空間中,也就是即使打開了 innodb_file_per_table 參數(shù)糯景,所有的表的 undolog 都存儲在同一個文件里嘁圈。該文件主要用來做事務(wù)回滾和 MVCC 。undolog 是邏輯日志蟀淮,也就是他不是記錄的將物理的數(shù)據(jù)頁恢復(fù)到之前的狀態(tài)最住,而是記錄的和原 sql 相反的 sql , 例如 insert 對應(yīng) delete , delete 對應(yīng) insert ,update 對應(yīng)另外一個 update 怠惶。事務(wù)回滾很好理解涨缚,執(zhí)行相反的操作回滾到之前的狀態(tài),而 MVCC 是指鏡像讀策治,當(dāng)一個事務(wù)需要查詢某條記錄脓魏,而該記錄已經(jīng)被其他事務(wù)修改,但該事務(wù)還沒提交通惫,而當(dāng)前事務(wù)可以通過 undolog 計算到之前的值茂翔。這里我們只需要知道和 redolog 一樣, undolog 也是需要在執(zhí)行 update 語句的時候在事務(wù)提交前需要寫入到文件的履腋。另外 undolog 的寫入也會有對應(yīng)的 redolog 珊燎,因為 undolog 也需要持久化,通過 WAL 可以提高效率府树。這里可以總結(jié)下俐末,在事務(wù)提交的時候要保證 redolog 寫入到文件里,而這個 redolog 包含 主鍵索引上的數(shù)據(jù)頁的修改奄侠,以及共享表空間的回滾段中 undolog 的插入卓箫。 另外 undolog 的清理通過一個后臺線程定時處理,清理的時候需要判斷該 undolog 是否所有的事務(wù)都不會用到垄潮。
熟悉 MySQL 的都知道烹卒,他通過 binlog 來進(jìn)行高可用闷盔,也就是通過 binlog 來將數(shù)據(jù)同步到集群內(nèi)其他的 MySQL 實例。binlog 和 redolog 的區(qū)別是旅急,他是在存儲引擎上層 Server 層寫入的逢勾,他記錄的是邏輯操作,也就是對應(yīng)的 sql ,而 redolog 記錄的底層某個數(shù)據(jù)頁的物理操作藐吮,redolog 是循環(huán)寫的溺拱,而binlog 是追加寫的,不會覆蓋以前寫的數(shù)據(jù)谣辞。而binlog 也需要在事務(wù)提交前寫入文件迫摔。binlog 的寫入頁需要通過 fsync 來保證落盤,為了提高 tps 泥从,MySQL 可以通過參數(shù) sync_binlog 來控制是否需要同步刷盤句占,該策略會影響當(dāng)主庫宕機后備庫數(shù)據(jù)可能并沒有完全同步到主庫數(shù)據(jù)。由于事務(wù)的原子性躯嫉,需要保證事務(wù)提交的時候 redolog 和 binlog 都寫入成功纱烘,所以 MySQL 執(zhí)行層采用了兩階段提交來保證 redolog 和 binlog 都寫入成功后才 commit,如果一方失敗則會進(jìn)行回滾祈餐。
下面我們理一下一條 update 語句的執(zhí)行過程:
updatepersonsetage =30whereid=1;
1.分配事務(wù) ID 擂啥,開啟事務(wù),獲取鎖昼弟,沒有獲取到鎖則等待啤它。
2.執(zhí)行器先通過存儲引擎找到 id = 1 的數(shù)據(jù)頁,如果緩沖池有則直接取出舱痘,沒有則去主鍵索引上取出對應(yīng)的數(shù)據(jù)頁放入緩沖池变骡。
3.在數(shù)據(jù)頁內(nèi)找到 id = 1 這行記錄,取出芭逝,將 age 改為 30 然后寫入內(nèi)存
4.生成 redolog undolog 到內(nèi)存塌碌,redolog 狀態(tài)為 prepare
5.將 redolog undolog 寫入文件并調(diào)用 fsync
6.server 層生成 binlog 并寫入文件調(diào)用 fsync
7.事務(wù)提交,將 redolog 的狀態(tài)改為 commited 釋放鎖
鎖
數(shù)據(jù)庫使用鎖是為了對共享資源進(jìn)行并發(fā)訪問控制旬盯,從而保證數(shù)據(jù)的完整性和一致性台妆。InnoDB 中鎖的最小粒度為行,和 jdk 中的 ReadWriteLock 一樣胖翰,InnoDB提供了共享鎖和排他鎖接剩,分別用來讀和寫。共享鎖之間可以兼容萨咳,其他都互斥懊缺。根據(jù)加鎖的范圍,可以分為:全局鎖培他、表級鎖鹃两、行鎖遗座。全局鎖會把整個數(shù)據(jù)庫實例加鎖,命令為 flush tables withs read lock 俊扳, 將使數(shù)據(jù)庫處于只讀狀態(tài)途蒋,其他數(shù)據(jù)寫入和修改表結(jié)構(gòu)等語句會阻塞,一般在備庫上做全局備份使用馋记。而表級鎖有兩種号坡,一種是表鎖,命令為 lock table with read/write 抗果,和讀寫鎖一樣筋帖,另外一種是元數(shù)據(jù)鎖奸晴,也叫意向鎖冤馏,不需要顯示申明,當(dāng)執(zhí)行修改表結(jié)構(gòu)寄啼,加索引的時候會自動加元數(shù)據(jù)寫鎖逮光,對表進(jìn)行增刪改查的時候會加元數(shù)據(jù)讀鎖墩划。這樣當(dāng)兩條修改語句的事務(wù)之間元數(shù)據(jù)鎖都是讀鎖不互斥涕刚,但是修改表結(jié)構(gòu)的時候執(zhí)行更新由于互斥就需要阻塞。還有一種行級鎖稱為間隙鎖乙帮,他鎖定的是兩條記錄之間的間隙杜漠,防止其他事務(wù)往這個間隙插入數(shù)據(jù)氢卡,間隙鎖是隱式鎖,是存儲引擎自己加上的。
非鎖定讀
普通的 select 操作都是非鎖定讀,如果存在事務(wù)沖突,會利用 undolog 獲取新事務(wù)操作之前的鏡像返回殊者,在讀已提交的隔離級別下,會獲取新事務(wù)修改前的最新的一份已經(jīng)提交的數(shù)據(jù),而在可重復(fù)讀的隔離級別下,會讀取該事務(wù)開始時的數(shù)據(jù)版本。當(dāng)有多個事務(wù)并發(fā)操作同一行記錄時壤玫,該記錄會同時存在多個 undolog 欲间,每個 undolog 就是一個版本她渴,這種模式稱為多版本并發(fā)控制(MVCC) 苏携,該模式能夠極大的提高數(shù)據(jù)庫的性能纱扭,想一想肃叶,如果基于鎖來控制的話,當(dāng)對某個記錄進(jìn)行修改的時候,另一個事務(wù)將需要等待,不管他是要讀取還是寫入低缩,MVCC 允許寫入的時候還能夠進(jìn)行讀操作么介,這對大部分都是查詢操作的應(yīng)用來說極大的提高了 tps 慨仿。
鎖定讀
有時候我們在查詢的時候需要顯示的給記錄加鎖來保證一致性摧找,select for update 將對掃描到的記錄加上排他鎖,而 select in share lock 將對掃描的記錄加上共享鎖牢硅。這兩個語句必須在一個事物內(nèi)蹬耘,也就是需要顯示開啟事物,begin transaction; 當(dāng)事物提交的時候會釋放鎖减余。具體加鎖的邏輯我們后面在分析综苔。另外所有的鎖定讀都是當(dāng)前讀,也就是讀取當(dāng)前記錄的最新版本,不會利用 undolog 讀取鏡像如筛。另外所有的 insert堡牡、update、delete 操作也是當(dāng)前讀杨刨,update悴侵、delete 會在更新之前進(jìn)行一次當(dāng)前讀,然后加鎖拭嫁,而 insert 因為會觸發(fā)唯一索引檢測可免,也會包含一個當(dāng)前讀伶唯。
自增長鎖:
在主鍵設(shè)置為自增長的情況下帚稠,該表會維護(hù)一個計數(shù)器,每個插入操作都會先獲取這個計數(shù)器的當(dāng)前值彤断,然后加 1 作為新的主鍵怕品,顯然這個計數(shù)器是一個共享變量需要加排他鎖妇垢,而這個鎖不需要等到事物提交后才釋放,他在 sql 語句插入完成后就會釋放肉康,新版本的 innoDB 采用互斥量來實現(xiàn)提高了插入速度闯估。
鎖的問題
臟讀
不可重復(fù)讀
丟失更新
死鎖和熱點
臟讀是指事務(wù)A對某個數(shù)據(jù)頁進(jìn)行了更改,但是并沒有提交吼和,這個數(shù)據(jù)就成為臟數(shù)據(jù)涨薪,這里稍微和上面提到的臟頁做下區(qū)分,臟頁是指內(nèi)存中已經(jīng)更改但是還沒有刷新到磁盤的數(shù)據(jù)炫乓,臟頁是正常的刚夺,而臟讀是指一個事物讀取了另外一個事物沒有提交的數(shù)據(jù),如果另外一個數(shù)據(jù)對這個數(shù)據(jù)又進(jìn)行了更改末捣,則出現(xiàn)數(shù)據(jù)一致性侠姑,臟讀違背了數(shù)據(jù)庫的隔離性。臟讀目前只能出現(xiàn)在讀未提交這個隔離級別下箩做,目前 MySQL 默認(rèn)的隔離級別為可重復(fù)讀莽红。
不可重復(fù)讀是指一個事務(wù)先后兩次讀取同一條記錄的結(jié)果不一樣,因為第二次讀取的時候可能其他事務(wù)已經(jīng)進(jìn)行更改并提交邦邦,不可重復(fù)讀只發(fā)生在隔離級別為讀未提交和讀已提交里安吁。
丟失更新是指兩個事務(wù)同時更新某一條記錄,導(dǎo)致其中一個事務(wù)更新失效圃酵,理論上任何一個隔離級別都不會發(fā)生丟失更新柳畔,因為更新的時候會加上排他鎖,但是應(yīng)用中卻經(jīng)常發(fā)生郭赐,例如一個計數(shù)器應(yīng)用薪韩,事務(wù)A查詢計數(shù)器的值 v=5,在內(nèi)存中加 1 寫入到數(shù)據(jù)庫确沸,在寫入之前另外一個事務(wù)讀取到計數(shù)器的值 v=5 ,然后加 1 寫入數(shù)據(jù)庫俘陷,這樣本來應(yīng)該為 7 罗捎, 現(xiàn)在卻是 6 ,這是因為 我們是先讀取在寫入拉盾,而讀取和寫入對數(shù)據(jù)庫而言是兩個操作桨菜,并不是一個原子操作,這里可以通過把查詢的記錄加上排他鎖 select for update 來防止丟失更新現(xiàn)象捉偏。當(dāng)然這里直接將 sql 改為 v = v + 1 也可以倒得。
死鎖是指兩個或兩個以上事務(wù)因爭奪資源而互相等待的情況,InnoDB 提供了死鎖檢測和超時機制來防止死鎖的影響夭禽,死鎖檢測是非常耗 CPU 的霞掺,當(dāng)很多個事務(wù)同時競爭同一個資源的時候,例如搶購的時候扣商品份額讹躯,或者支付的時候所有的訂單都會用到一個公共賬戶菩彬,同一個資源競爭的事務(wù)越多,死鎖檢測越耗 CPU 潮梯。為了減少這種情況的影響骗灶,建議盡量在業(yè)務(wù)層減少熱點的產(chǎn)生,例如將熱點賬戶拆分成若個個同樣功能的賬戶秉馏,萬一發(fā)生高并發(fā)耙旦,建議在應(yīng)用層做限流或者排隊,當(dāng)然也可以在數(shù)據(jù)庫層做排隊沃饶,這個需要修改數(shù)據(jù)庫源碼母廷。
加鎖的流程
InnoDB的加鎖過程比較復(fù)雜,大致可以記住一個原則是:將所有掃描到的記錄都加鎖糊肤,范圍查詢會加間隙鎖,然后加鎖過程按照兩階段鎖 2PL 來實現(xiàn)氓鄙,也就是先加鎖馆揉,然后所有的鎖在事物提交的時候釋放。怎么加鎖和數(shù)據(jù)庫的隔離級別有關(guān)抖拦,然而我們一般很少更改 MySQL 的隔離級別升酣,所以下面我們均按照可重復(fù)讀的隔離級別進(jìn)行分析,另外一個因素是查詢條件中是否包含索引态罪,是主鍵索引還是普通索引噩茄,是否是唯一索引等。我們以下面這條 sql 語句來分析加鎖過程复颈。
select*fromtrade_orderwhereorder_no ='201912102322'forupdate;
order_no 是主鍵索引 绩聘,這種情況將在主鍵索引上的 order_no = '201912102322' 這條記錄上加排他鎖。
order_no 是普通索引,并且是唯一索引 將會對 普通索引上對應(yīng)的一套記錄加排他鎖凿菩,對主鍵索引上對應(yīng)的記錄加排他鎖
order_no 是普通索引机杜,并且不是唯一索引 將會對 普通索引上 order_no = '201912102322' 一條或者多條記錄加鎖,并且對這些記錄對應(yīng)的主鍵索引上的記錄加鎖衅谷。這里除了加上行鎖外椒拗,還會加上間隙鎖,防止其他事物插入 order_no = '201912102322' 的記錄获黔,然而如果是唯一索引就不需要間隙鎖蚀苛,行鎖就可以。
order_no 上沒有索引玷氏,innoDB 將會在主鍵索引上全表掃描堵未,這里并沒有加表鎖,而是將所有的記錄都會加上行級排他鎖预茄,而實際上 innoDB 內(nèi)部做了優(yōu)化兴溜,當(dāng)掃描到一行記錄后發(fā)現(xiàn)不匹配就會把鎖給釋放,當(dāng)然這個違背了 2PL 原則在事務(wù)提交的時候釋放耻陕。這里除了對記錄進(jìn)行加鎖拙徽,還會對每兩個記錄之間的間隙加鎖,所以最終將會保存所有的間隙鎖和 order_no = '201912102322' 的行鎖诗宣。
order_no = '201912102322' 這條記錄不存在的情況下膘怕,如果order_no 是主鍵索引,則會加一個間隙鎖召庞,而這個間隙是主鍵索引中 order_no 小于 201912102322 的第一條記錄到大于 201912102322 的第一條記錄岛心。試想一下如果不加間隙鎖,如果其他事物插入了一條 order_no = '201912102322' 的記錄篮灼,由于 select for update 是當(dāng)前讀忘古,即使上面那個事物沒有提交,如果在該事物中重新查詢一次就會發(fā)生幻讀诅诱。
如果沒有索引髓堪,則對掃描到的所有記錄和間隙都加鎖,如果不匹配行鎖將會釋放只剩下間隙鎖娘荡「膳裕回憶一下上面講的數(shù)據(jù)頁的結(jié)果中又一個最大記錄和最小記錄,Infimum 和 Supremum Record炮沐,這兩個記錄在加間隙鎖的時候就會用到争群。
事務(wù)
InnoDB 存儲引擎的事務(wù)需完全符合 ACID 特性。下面我們一起看下 InnoDB 做了哪些事情大年。
原子性 : 是指一個事務(wù)內(nèi)的所有操作要么全部成功要么全部失敗换薄,數(shù)據(jù)庫中將 redolog 和 binlog 的寫入采用兩階段提交就是為了保證事務(wù)的原子性玉雾。另外由于 InnodDB 是按頁進(jìn)行存儲的,每個頁大小為 16kb 而操作系統(tǒng)的一般以 4KB 為一頁進(jìn)行讀取专控,所以可能出現(xiàn)一個 InnoDB 的數(shù)據(jù)頁只寫了一部分的情況抹凳。而 InnoDB 為了防止這種情況的發(fā)生采用雙寫機制,除了寫入磁盤上的數(shù)據(jù)頁還會在共享空間中寫入伦腐。而 redolog 按塊存儲赢底,每個塊 512 字節(jié),正好和扇區(qū)大小一樣所以柏蘑,可以保證原子性幸冻,不需要進(jìn)行雙寫。
一致性 :保證磁盤和緩存的數(shù)據(jù)一致咳焚,binlog 數(shù)據(jù)和 主庫中的數(shù)據(jù)一致洽损。
隔離性 : 默認(rèn)為可重復(fù)讀,采用 undolog 來實現(xiàn)革半。
持久性 : 事務(wù)一旦提交碑定,其結(jié)果就是永久的,redolog 需要在事務(wù)提交前進(jìn)行刷盤又官,磁盤采用 RAID 等延刘。
覺得不錯請點贊支持,歡迎留言或進(jìn)我的個人群855801563領(lǐng)取【架構(gòu)資料專題目合集90期】六敬、【BATJTMD大廠JAVA面試真題1000+】碘赖,本群專用于學(xué)習(xí)交流技術(shù)、分享面試機會外构,拒絕廣告普泡,我也會在群內(nèi)不定期答題、探討审编。