這篇文章將從查詢緩存,索引,優(yōu)化器,explain,redo 日志,undo 日志,事務(wù)隔離級(jí)別,鎖等方面來(lái)講解 MySQL。
這篇文章將從查詢緩存,索引,優(yōu)化器,explain,redo 日志,undo 日志,事務(wù)隔離級(jí)別,鎖等方面來(lái)講解 MySQL。
開局一張圖
這張圖是重點(diǎn)!!!咱要先對(duì) MySQL 有一個(gè)宏觀的了解,知道他的執(zhí)行流程。
一條 SQL 語(yǔ)句過(guò)來(lái)的流程是什么樣的?
①當(dāng)客戶端連接到 MySQL 服務(wù)器時(shí),服務(wù)器對(duì)其進(jìn)行認(rèn)證。可以通過(guò)用戶名與密碼認(rèn)證,也可以通過(guò) SSL 證書進(jìn)行認(rèn)證。登錄認(rèn)證后,服務(wù)器還會(huì)驗(yàn)證客戶端是否有執(zhí)行某個(gè)查詢的操作權(quán)限。
②在正式查詢之前,服務(wù)器會(huì)檢查查詢緩存,如果能找到對(duì)應(yīng)的查詢,則不必進(jìn)行查詢解析,優(yōu)化,執(zhí)行等過(guò)程,直接返回緩存中的結(jié)果集。
③MySQL 的解析器會(huì)根據(jù)查詢語(yǔ)句,構(gòu)造出一個(gè)解析樹,主要用于根據(jù)語(yǔ)法規(guī)則來(lái)驗(yàn)證語(yǔ)句是否正確,比如 SQL 的關(guān)鍵字是否正確,關(guān)鍵字的順序是否正確。而預(yù)處理器主要是進(jìn)一步校驗(yàn),比如表名,字段名是否正確等。
④查詢優(yōu)化器將解析樹轉(zhuǎn)化為查詢計(jì)劃,一般情況下,一條查詢可以有很多種執(zhí)行方式,最終返回相同的結(jié)果,優(yōu)化器就是根據(jù)成本找到這其中最優(yōu)的執(zhí)行計(jì)劃。
⑤執(zhí)行計(jì)劃調(diào)用查詢執(zhí)行引擎,而查詢引擎通過(guò)一系列 API 接口查詢到數(shù)據(jù)。
⑥得到數(shù)據(jù)之后,在返回給客戶端的同時(shí),會(huì)將數(shù)據(jù)存在查詢緩存中。
查詢緩存
我們先通過(guò) show variables like '%query_cache%' 來(lái)看一下默認(rèn)的數(shù)據(jù)庫(kù)配置,此為本地?cái)?shù)據(jù)庫(kù)的配置。
概念
①have_query_cache:當(dāng)前的 MySQL 版本是否支持“查詢緩存”功能。
②query_cache_limit:MySQL 能夠緩存的最大查詢結(jié)果,查詢結(jié)果大于該值時(shí)不會(huì)被緩存。默認(rèn)值是 1048576(1MB)。
③query_cache_min_res_unit:查詢緩存分配的最小塊(字節(jié))。默認(rèn)值是 4096(4KB)。
當(dāng)查詢進(jìn)行時(shí),MySQL 把查詢結(jié)果保存在 query cache,但是如果保存的結(jié)果比較大,超過(guò)了 query_cache_min_res_unit 的值,這時(shí)候 MySQL 將一邊檢索結(jié)果,一邊進(jìn)行保存結(jié)果。
他保存結(jié)果也是按默認(rèn)大小先分配一塊空間,如果不夠,又要申請(qǐng)新的空間給他。
如果查詢結(jié)果比較小,默認(rèn)的 query_cache_min_res_unit 可能造成大量的內(nèi)存碎片,如果查詢結(jié)果比較大,默認(rèn)的 query_cache_min_res_unit 又不夠,導(dǎo)致一直分配塊空間。
所以可以根據(jù)實(shí)際需求,調(diào)節(jié) query_cache_min_res_unit 的大小。
注:如果上面說(shuō)的內(nèi)容有點(diǎn)彎彎繞,那舉個(gè)現(xiàn)實(shí)生活中的例子,比如咱現(xiàn)在要給運(yùn)動(dòng)員送水,默認(rèn)的是 500ml 的瓶子,如果過(guò)來(lái)的是少年運(yùn)動(dòng)員,可能 500ml 太大了,他們喝不完,造成了浪費(fèi)。
那我們就可以選擇 300ml 的瓶子,如果過(guò)來(lái)的是成年運(yùn)動(dòng)員,可能 500ml 不夠,那他們一瓶喝完了,又開一瓶,直接不渴為止。那么那樣開瓶子也要時(shí)間,我們就可以選擇 1000ml 的瓶子。
④query_cache_size:為緩存查詢結(jié)果分配的總內(nèi)存。
⑤query_cache_type:默認(rèn)為 on,可以緩存除了以 select sql_no_cache 開頭的所有查詢結(jié)果。
⑥query_cache_wlock_invalidate:如果該表被鎖住,是否返回緩存中的數(shù)據(jù),默認(rèn)是關(guān)閉的。
原理
MySQL 的查詢緩存實(shí)質(zhì)上是緩存 SQL 的 Hash 值和該 SQL 的查詢結(jié)果,如果運(yùn)行相同的 SQL,服務(wù)器直接從緩存中去掉結(jié)果,而不再去解析,優(yōu)化,尋找最低成本的執(zhí)行計(jì)劃等一系列操作,大大提升了查詢速度。
但是萬(wàn)事有利也有弊:
第一個(gè)弊端就是如果表的數(shù)據(jù)有一條發(fā)生變化,那么緩存好的結(jié)果將全部不再有效。這對(duì)于頻繁更新的表,查詢緩存是不適合的。
比如一張表里面只有兩個(gè)字段,分別是 id 和 name,數(shù)據(jù)有一條為 1,張三。
我使用 select * from 表名 where name=“張三”來(lái)進(jìn)行查詢,MySQL 發(fā)現(xiàn)查詢緩存中沒有此數(shù)據(jù),會(huì)進(jìn)行一系列的解析,優(yōu)化等操作進(jìn)行數(shù)據(jù)的查詢。
查詢結(jié)束之后將該 SQL 的 Hash 和查詢結(jié)果緩存起來(lái),并將查詢結(jié)果返回給客戶端。
但是這個(gè)時(shí)候我又新增了一條數(shù)據(jù) 2,張三。如果我還用相同的 SQL 來(lái)執(zhí)行,他會(huì)根據(jù)該 SQL 的 Hash 值去查詢緩存中,那么結(jié)果就錯(cuò)了。
所以 MySQL 對(duì)于數(shù)據(jù)有變化的表來(lái)說(shuō),會(huì)直接清空關(guān)于該表的所有緩存。這樣其實(shí)效率是很差的。
第二個(gè)弊端就是緩存機(jī)制是通過(guò)對(duì) SQL 的 Hash,得出的值為 Key,查詢結(jié)果為 Value 來(lái)存放的,那么就意味著 SQL 必須完完全全一模一樣,否則就命不中緩存。
我們都知道 Hash 值的規(guī)則,就算很小的查詢,哈希出來(lái)的結(jié)果差距是很多的,所以 select * from 表名 where name=“張三”和SELECT * FROM 表名 WHERE NAME=“張三”和select * from 表名 where name = “張三”,三個(gè)SQL 哈希出來(lái)的值是不一樣的。
大小寫和空格影響了他們,所以并不能命中緩存,但其實(shí)他們搜索結(jié)果是完全一樣的。
生產(chǎn)如何設(shè)置 MySQL Query Cache
先來(lái)看線上參數(shù):
我們發(fā)現(xiàn)將 query_cache_type 設(shè)置為 OFF,其實(shí)網(wǎng)上資料和各大云廠商提供的云服務(wù)器都是將這個(gè)功能關(guān)閉的,從上面的原理來(lái)看,在一般情況下,他的弊端大于優(yōu)點(diǎn)。
索引
例子:創(chuàng)建一個(gè)名為 user 的表,其包括 id,name,age,sex 等字段信息。此外,id 為主鍵聚簇索引,idx_name 為非聚簇索引。
CREATE TABLE `user` (
`id` varchar(10) NOT NULL DEFAULT '',
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我們將其設(shè)置 10 條數(shù)據(jù),便于下面的索引的理解:
INSERT INTO `user` VALUES ('1', 'andy', '20', '女');
INSERT INTO `user` VALUES ('10', 'baby', '12', '女');
INSERT INTO `user` VALUES ('2', 'kat', '12', '女');
INSERT INTO `user` VALUES ('3', 'lili', '20', '男');
INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');
INSERT INTO `user` VALUES ('5', 'bill', '20', '男');
INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');
INSERT INTO `user` VALUES ('7', 'hay', '20', '女');
INSERT INTO `user` VALUES ('8', 'tony', '20', '男');
INSERT INTO `user` VALUES ('9', 'rose', '21', '男');
聚簇索引(主鍵索引)
先來(lái)一張圖鎮(zhèn)樓,接下來(lái)就是看圖說(shuō)話:
他包含兩個(gè)特點(diǎn):
使用記錄主鍵值的大小來(lái)進(jìn)行記錄和頁(yè)的排序。頁(yè)內(nèi)的記錄是按照主鍵的大小順序排成一個(gè)單項(xiàng)鏈表。各個(gè)存放用戶記錄的頁(yè)也是根據(jù)頁(yè)中用戶記錄的主鍵大小順序排成一個(gè)雙向鏈表。
葉子節(jié)點(diǎn)存儲(chǔ)的是完整的用戶記錄。
注:聚簇索引不需要我們顯示的創(chuàng)建,他是由 InnoDB 存儲(chǔ)引擎自動(dòng)為我們創(chuàng)建的。如果沒有主鍵,其也會(huì)默認(rèn)創(chuàng)建一個(gè)。
非聚簇索引(二級(jí)索引)
上面的聚簇索引只能在搜索條件是主鍵時(shí)才能發(fā)揮作用,因?yàn)榫鄞厮饕梢愿鶕?jù)主鍵進(jìn)行排序的。
如果搜索條件是 name,在剛才的聚簇索引上,我們可能遍歷,挨個(gè)找到符合條件的記錄,但是,這樣真的是太蠢了,MySQL 不會(huì)這樣做的。
如果我們想讓搜索條件是 name 的時(shí)候,也能使用索引,那可以多創(chuàng)建一個(gè)基于 name 的二叉樹,如下圖:
他與聚簇索引的不同:
葉子節(jié)點(diǎn)內(nèi)部使用 name 字段排序,葉子節(jié)點(diǎn)之間也是使用 name 字段排序。
葉子節(jié)點(diǎn)不再是完整的數(shù)據(jù)記錄,而是 name 和主鍵值。
為什么不再是完整信息?MySQL 只讓聚簇索引的葉子節(jié)點(diǎn)存放完整的記錄信息,因?yàn)槿绻泻脦讉€(gè)非聚簇索引,他們的葉子節(jié)點(diǎn)也存放完整的記錄績(jī)效,那就不浪費(fèi)空間啦。
如果我搜索條件是基于 name,需要查詢所有字段的信息,那查詢過(guò)程是啥?
根據(jù)查詢條件,采用 name 的非聚簇索引,先定位到該非聚簇索引某些記錄行。
根據(jù)記錄行找到相應(yīng)的 id,再根據(jù) id 到聚簇索引中找到相關(guān)記錄。這個(gè)過(guò)程叫做回表。
聯(lián)合索引
圖就不畫了,簡(jiǎn)單來(lái)說(shuō),如果 name 和 age 組成一個(gè)聯(lián)合索引,那么先按 name 排序,如果 name 一樣,就按 age 排序。
一些原則
①最左前綴原則。一個(gè)聯(lián)合索引(a,b,c),如果有一個(gè)查詢條件有 a,有 b,那么他則走索引,如果有一個(gè)查詢條件沒有 a,那么他則不走索引。
②使用唯一索引。具有多個(gè)重復(fù)值的列,其索引效果最差。例如,存放姓名的列具有不同值,很容易區(qū)分每行。
而用來(lái)記錄性別的列,只含有“男”,“女”,不管搜索哪個(gè)值,都會(huì)得出大約一半的行,這樣的索引對(duì)性能的提升不夠高。
③不要過(guò)度索引。每個(gè)額外的索引都要占用額外的磁盤空間,并降低寫操作的性能。
在修改表的內(nèi)容時(shí),索引必須進(jìn)行更新,有時(shí)可能需要重構(gòu),因此,索引越多,所花的時(shí)間越長(zhǎng)。
④索引列不能參與計(jì)算,保持列“干凈”,比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引。
原因很簡(jiǎn)單,B+ 樹中存的都是數(shù)據(jù)表中的字段值,但進(jìn)行檢索時(shí),需要把所有元素都應(yīng)用函數(shù)才能比較,顯然成本太大。
所以語(yǔ)句應(yīng)該寫成:
create_time = unix_timestamp(’2014-05-29’);
⑤一定要設(shè)置一個(gè)主鍵。前面聚簇索引說(shuō)到如果不指定主鍵,InnoDB 會(huì)自動(dòng)為其指定主鍵,這個(gè)我們是看不見的。
反正都要生成一個(gè)主鍵的,還不如我們?cè)O(shè)置,以后在某些搜索條件時(shí)還能用到主鍵的聚簇索引。
⑥主鍵推薦用自增 id,而不是 uuid。上面的聚簇索引說(shuō)到每頁(yè)數(shù)據(jù)都是排序的,并且頁(yè)之間也是排序的,如果是 uuid,那么其肯定是隨機(jī)的,其可能從中間插入,導(dǎo)致頁(yè)的分裂,產(chǎn)生很多表碎片。
如果是自增的,那么其有從小到大自增的,有順序,那么在插入的時(shí)候就添加到當(dāng)前索引的后續(xù)位置。當(dāng)一頁(yè)寫滿,就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)。
注:如果自增 id 用完了,那將字段類型改為 bigint,就算每秒 1 萬(wàn)條數(shù)據(jù),跑 100 年,也沒達(dá)到 bigint 的最大值。
萬(wàn)年面試題(為什么索引用 B+ 樹)
①B+ 樹的磁盤讀寫代價(jià)更低:B+ 樹的內(nèi)部節(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針,因此其內(nèi)部節(jié)點(diǎn)相對(duì) B 樹更小。
如果把所有同一內(nèi)部節(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中,那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多,一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多,相對(duì) IO 讀寫次數(shù)就降低了。
②由于 B+ 樹的數(shù)據(jù)都存儲(chǔ)在葉子結(jié)點(diǎn)中,分支結(jié)點(diǎn)均為索引,方便掃庫(kù),只需要掃一遍葉子結(jié)點(diǎn)即可。
但是 B 樹因?yàn)槠浞种ЫY(jié)點(diǎn)同樣存儲(chǔ)著數(shù)據(jù),我們要找到具體的數(shù)據(jù),需要進(jìn)行一次中序遍歷按序來(lái)掃,所以 B+ 樹更加適合在區(qū)間查詢的情況,所以通常 B+ 樹用于數(shù)據(jù)庫(kù)索引。
優(yōu)化器
在開篇的圖里面,我們知道了 SQL 語(yǔ)句從客戶端經(jīng)由網(wǎng)絡(luò)協(xié)議到查詢緩存,如果沒有命中緩存,再經(jīng)過(guò)解析工作,得到準(zhǔn)確的 SQL,現(xiàn)在就來(lái)到了我們這模塊說(shuō)的優(yōu)化器。
首先,我們知道每一條 SQL 都有不同的執(zhí)行方法,要不通過(guò)索引,要不通過(guò)全表掃描的方式。
那么問(wèn)題就來(lái)了,MySQL 是如何選擇時(shí)間最短,占用內(nèi)存最小的執(zhí)行方法呢?
什么是成本?
I/O 成本。數(shù)據(jù)存儲(chǔ)在硬盤上,我們想要進(jìn)行某個(gè)操作需要將其加載到內(nèi)存中,這個(gè)過(guò)程的時(shí)間被稱為 I/O 成本。默認(rèn)是 1。
CPU 成本。在內(nèi)存對(duì)結(jié)果集進(jìn)行排序的時(shí)間被稱為 CPU 成本。默認(rèn)是 0.2。
單表查詢的成本
先來(lái)建一個(gè)用戶表 dev_user,里面包括主鍵 id,用戶名 username,密碼 password,外鍵 user_info_id,狀態(tài) status,外鍵 main_station_id,是否外網(wǎng)訪問(wèn) visit,這七個(gè)字段。
索引有兩個(gè),一個(gè)是主鍵的聚簇索引,另一個(gè)是顯式添加的以 username 為字段的唯一索引 uname_unique。
如果搜索條件是 select * from dev_user where username='XXX',那么 MySQL 是如何選擇相關(guān)索引呢?
①使用所有可能用到的索引
我們可以看到搜索條件 username,所以可能走 uname_unique 索引。也可以做聚簇索引,也就是全表掃描。
②計(jì)算全表掃描代價(jià)
我們通過(guò) show table status like ‘dev_user’命令知道 rows 和 data_length 字段,如下圖:
rows:表示表中的記錄條數(shù),但是這個(gè)數(shù)據(jù)不準(zhǔn)確,是個(gè)估計(jì)值。
data_length:表示表占用的存儲(chǔ)空間字節(jié)數(shù)。data_length=聚簇索引的頁(yè)面數(shù)量 X 每個(gè)頁(yè)面的大小。
反推出頁(yè)面數(shù)量=1589248÷16÷1024=97:
I/O 成本:97X1=97
CPU 成本:6141X0.2=1228
總成本:97+1228=1325
③計(jì)算使用不同索引執(zhí)行查詢的代價(jià)
因?yàn)橐樵兂鰸M足條件的所有字段信息,所以要考慮回表成本:
I/O 成本=1+1X1=2(范圍區(qū)間的數(shù)量+預(yù)計(jì)二級(jí)記錄索引條數(shù))
CPU 成本=1X0.2+1X0.2=0.4(讀取二級(jí)索引的成本+回表聚簇索引的成本)
總成本= I/O 成本+CPU 成本=2.4
④對(duì)比各種執(zhí)行方案的代價(jià),找出成本最低的那個(gè)
上面兩個(gè)數(shù)字一對(duì)比,成本是采用 uname_unique 索引成本最低。
多表查詢的成本
對(duì)于兩表連接查詢來(lái)說(shuō),他的查詢成本由下面兩個(gè)部分構(gòu)成:
單次查詢驅(qū)動(dòng)表的成本
多次查詢被驅(qū)動(dòng)表的成本(具體查詢多次取決于對(duì)驅(qū)動(dòng)表查詢的結(jié)果集有多少個(gè)記錄)
index dive
如果前面的搜索條件不是等值,而是區(qū)間,如 select * from dev_user where username>'admin' and username<'test' 這個(gè)時(shí)候我們是無(wú)法看出需要回表的數(shù)量。
步驟 1:先根據(jù) username>'admin' 這個(gè)條件找到第一條記錄,稱為區(qū)間最左記錄。
步驟 2:再根據(jù) username<'test' 這個(gè)條件找到最后一條記錄,稱為區(qū)間最右記錄。
步驟 3:如果區(qū)間最左記錄和區(qū)間最右記錄相差不是很遠(yuǎn),可以準(zhǔn)確統(tǒng)計(jì)出需要回表的數(shù)量。
如果相差很遠(yuǎn),就先計(jì)算 10 頁(yè)有多少條記錄,再乘以頁(yè)面數(shù)量,最終模糊統(tǒng)計(jì)出來(lái)。
Explain
產(chǎn)品來(lái)索命:
產(chǎn)品:為什么這個(gè)頁(yè)面出來(lái)這么慢?
開發(fā):因?yàn)槟悴榈臄?shù)據(jù)多唄,他就是這么慢
產(chǎn)品:我不管,我要這個(gè)頁(yè)面快點(diǎn),你這樣,客戶怎么用啊
開發(fā):......你行你來(lái)
哈哈哈哈,不瞎 BB 啦,如果有些 SQL 賊慢,我們需要知道他有沒有走索引,走了哪個(gè)索引,這個(gè)時(shí)候我就需要通過(guò) explain 關(guān)鍵字來(lái)深入了解 MySQL 內(nèi)部是如何執(zhí)行的。
id:一般來(lái)說(shuō)一個(gè) select 一個(gè)唯一 id,如果是子查詢,就有兩個(gè) select,id 是不一樣的,但是凡事有例外,有些子查詢的,他們 id 是一樣的。
這是為什么呢?那是因?yàn)?MySQL 在進(jìn)行優(yōu)化的時(shí)候已經(jīng)將子查詢改成了連接查詢,而連接查詢的 id 是一樣的。
select_type:
simple:不包括 union 和子查詢的查詢都算 simple 類型。
primary:包括 union,union all,其中最左邊的查詢即為 primary。
union:包括 union,union all,除了最左邊的查詢,其他的查詢類型都為 union。
table:顯示這一行是關(guān)于哪張表的。
type 訪問(wèn)方法:
ref:普通二級(jí)索引與常量進(jìn)行等值匹配
ref_or_null:普通二級(jí)索引與常量進(jìn)行等值匹配,該索引可能是 null
const:主鍵或唯一二級(jí)索引列與常量進(jìn)行等值匹配
range:范圍區(qū)間的查詢
all:全表掃描
possible_keys:對(duì)某表進(jìn)行單表查詢時(shí)可能用到的索引。
key:經(jīng)過(guò)查詢優(yōu)化器計(jì)算不同索引的成本,最終選擇成本最低的索引。
rows:
如果使用全表掃描,那么 rows 就代表需要掃描的行數(shù)
如果使用索引,那么 rows 就代表預(yù)計(jì)掃描的行數(shù)
filtered:
如果全表掃描,那么 filtered 就代表滿足搜索條件的記錄的滿分比
如果是索引,那么 filtered 就代表除去索引對(duì)應(yīng)的搜索,其他搜索條件的百分比
redo 日志(物理日志)
InnoDB 存儲(chǔ)引擎是以頁(yè)為單位來(lái)管理存儲(chǔ)空間的,我們進(jìn)行的增刪改查操作都是將頁(yè)的數(shù)據(jù)加載到內(nèi)存中,然后進(jìn)行操作,再將數(shù)據(jù)刷回到硬盤上。
那么問(wèn)題就來(lái)了,如果我要給張三轉(zhuǎn)賬 100 塊錢,事務(wù)已經(jīng)提交了,這個(gè)時(shí)候 InnoDB 把數(shù)據(jù)加載到內(nèi)存中,這個(gè)時(shí)候還沒來(lái)得及刷入硬盤,突然停電了,數(shù)據(jù)庫(kù)崩了。
重啟之后,發(fā)現(xiàn)我的錢沒有轉(zhuǎn)成功,這不是尷尬了嗎?
解決方法很明顯,我們?cè)谟脖P加載到內(nèi)存之后,進(jìn)行一系列操作,一頓操作猛如虎,還未刷新到硬盤之前,先記錄下,在 XXX 位置我的記錄中金額減 100,在 XXX 位置張三的記錄中金額加 100。
然后再進(jìn)行增刪改查操作,最后刷入硬盤。如果未刷入硬盤,在重啟之后,先加載之前的記錄,那么數(shù)據(jù)就回來(lái)了。
這個(gè)記錄就叫做重做日志,即 redo 日志。他的目的是想讓已經(jīng)提交的事務(wù)對(duì)數(shù)據(jù)的修改是永久的,就算他重啟,數(shù)據(jù)也能恢復(fù)出來(lái)。
log buffer(日志緩沖區(qū))
為了解決磁盤速度過(guò)慢的問(wèn)題,redo 日志不能直接寫入磁盤,咱先整一大片連續(xù)的內(nèi)存空間給他放數(shù)據(jù)。
這一大片內(nèi)存就叫做日志緩沖區(qū),即 log buffer。到了合適的時(shí)候,再刷入硬盤。至于什么時(shí)候是合適的,這個(gè)下一章節(jié)說(shuō)。
我們可以通過(guò) show VARIABLES like 'innodb_log_buffer_size' 命令來(lái)查看當(dāng)前的日志緩存大小。
下圖為線上的大小:
redo 日志刷盤時(shí)機(jī)
由于 redo 日志一直都是增長(zhǎng)的,且內(nèi)存空間有限,數(shù)據(jù)也不能一直待在緩存中,我們需要將其刷新至硬盤上。
那什么時(shí)候刷新到硬盤呢?
log buffer 空間不足。上面有指定緩沖區(qū)的內(nèi)存大小,MySQL 認(rèn)為日志量已經(jīng)占了總?cè)萘康囊话胱笥遥托枰獙⑦@些日志刷新到磁盤上。
事務(wù)提交時(shí)。我們使用 redo 日志的目的就是將他未刷新到磁盤的記錄保存起來(lái),防止丟失,如果數(shù)據(jù)提交了,我們是可以不把數(shù)據(jù)提交到磁盤的,但為了保證持久性,必須把修改這些頁(yè)面的 redo 日志刷新到磁盤。
后臺(tái)線程不同的刷新后臺(tái)有一個(gè)線程,大概每秒都會(huì)將 log buffer 里面的 redo 日志刷新到硬盤上。
checkpoint 下下小節(jié)講。
redo 日志文件組
我們可以通過(guò) show variables like 'datadir' 命令找到相關(guān)目錄,底下有兩個(gè)文件,分別是 ib_logfile0 和 ib_logfile1,如下圖所示:
我們將緩沖區(qū) log buffer 里面的 redo 日志刷新到這個(gè)兩個(gè)文件里面,他們寫入的方式是循環(huán)寫入的,先寫 ib_logfile0,再寫 ib_logfile1,等 ib_logfile1 寫滿了,再寫 ib_logfile0。
那這樣就會(huì)存在一個(gè)問(wèn)題,如果 ib_logfile1 寫滿了,再寫 ib_logfile0,之前 ib_logfile0 的內(nèi)容不就被覆蓋而丟失了嗎?這就是 checkpoint 的工作啦。
checkpoint
redo 日志是為了系統(tǒng)崩潰后恢復(fù)臟頁(yè)用的,如果這個(gè)臟頁(yè)可以被刷新到磁盤上,那么他就可以功成身退,被覆蓋也就沒事啦。
沖突補(bǔ)習(xí):從系統(tǒng)運(yùn)行開始,就不斷的修改頁(yè)面,會(huì)不斷的生成 redo 日志。
redo 日志是不斷遞增的,MySQL 為其取了一個(gè)名字日志序列號(hào) Log Sequence Number,簡(jiǎn)稱 lsn。他的初始化的值為 8704,用來(lái)記錄當(dāng)前一共生成了多少 redo 日志。
redo 日志是先寫入 log buffer,之后才會(huì)被刷新到磁盤的 redo 日志文件。MySQL 為其取了一個(gè)名字 flush_to_disk_lsn。
用來(lái)說(shuō)明緩存區(qū)中有多少的臟頁(yè)數(shù)據(jù)被刷新到磁盤上啦。他的初始值和 lsn 一樣,后面的差距就有了。
做一次 checkpoint 分為兩步:
計(jì)算當(dāng)前系統(tǒng)可以被覆蓋的 redo 日志對(duì)應(yīng)的 lsn 最大值是多少。redo 日志可以被覆蓋,意味著他對(duì)應(yīng)的臟頁(yè)被刷新到磁盤上。
只要我們計(jì)算出當(dāng)前系統(tǒng)中最早被修改的 oldest_modification,只要系統(tǒng)中 lsn 小于該節(jié)點(diǎn)的 oldest_modification 值,磁盤的 redo 日志都是可以被覆蓋的。
將 lsn 過(guò)程中的一些數(shù)據(jù)統(tǒng)計(jì)。
undo 日志
undo log 有兩個(gè)作用:提供回滾和多個(gè)行版本控制(MVCC)。
undo log 和 redo log 記錄物理日志不一樣,它是邏輯日志。
可以認(rèn)為當(dāng) delete 一條記錄時(shí),undo log 中會(huì)記錄一條對(duì)應(yīng)的 insert 記錄,反之亦然,當(dāng) update 一條記錄時(shí),它記錄一條對(duì)應(yīng)相反的 update 記錄。
舉個(gè)例子:
insert into a(id) values(1);(redo)
這條記錄是需要回滾的。回滾的語(yǔ)句是:
delete from a where id = 1;(undo)
試想想看:如果沒有做 insert into a(id) values(1);(redo),那么 delete from a where id = 1;(undo) 這句話就沒有意義了。
現(xiàn)在看下正確的恢復(fù):
先 insert into a(id) values(1);(redo)
然后 delete from a where id = 1;(undo)
系統(tǒng)就回到了原先的狀態(tài),沒有這條記錄了
存儲(chǔ)方式:是存在段之中。
事務(wù)
事務(wù)中有一個(gè)隔離性特征,理論上在某個(gè)事務(wù)對(duì)某個(gè)數(shù)據(jù)進(jìn)行訪問(wèn)時(shí),其他事務(wù)應(yīng)該排序,當(dāng)該事務(wù)提交之后,其他事務(wù)才能繼續(xù)訪問(wèn)這個(gè)數(shù)據(jù)。
但是這樣子對(duì)性能影響太大,我們既想保持事務(wù)的隔離性,又想讓服務(wù)器在出來(lái)多個(gè)事務(wù)時(shí)性能盡量高些,所以只能舍棄一部分隔離性而去性能。
事務(wù)并發(fā)執(zhí)行的問(wèn)題:
①臟寫:這個(gè)太嚴(yán)重了,任何隔離級(jí)別都不允許發(fā)生)。
sessionA:修改了一條數(shù)據(jù),回滾掉
sessionB:修改了同一條數(shù)據(jù),提交掉
對(duì)于 sessionB 來(lái)說(shuō),明明數(shù)據(jù)更新了也提交了事務(wù),不能說(shuō)自己啥都沒干。
②臟讀:一個(gè)事務(wù)讀到另一個(gè)未提交事務(wù)修改的數(shù)據(jù)。
session A:查詢,得到某條數(shù)據(jù)
session B:修改某條數(shù)據(jù),但是最后回滾掉啦
session A:在 sessionB 修改某條數(shù)據(jù)之后,在回滾之前,讀取了該條記錄
對(duì)于 session A 來(lái)說(shuō),讀到了 session 回滾之前的臟數(shù)據(jù)。
③不可重復(fù)讀:前后多次讀取,同一個(gè)數(shù)據(jù)內(nèi)容不一樣。
session A:查詢某條記錄
session B : 修改該條記錄,并提交事務(wù)
session A : 再次查詢?cè)摋l記錄,發(fā)現(xiàn)前后查詢不一致
④幻讀:前后多次讀取,數(shù)據(jù)總量不一致。
session A:查詢表內(nèi)所有記錄
session B:新增一條記錄,并查詢表內(nèi)所有記錄
session A:再次查詢?cè)摋l記錄,發(fā)現(xiàn)前后查詢不一致
數(shù)據(jù)庫(kù)都有的四種隔離級(jí)別,MySQL 事務(wù)默認(rèn)的隔離級(jí)別是可重復(fù)讀,而且 MySQL 可以解決了幻讀的問(wèn)題:
未提交讀:臟讀,不可重復(fù)讀,幻讀都有可能發(fā)生
已提交讀:不可重復(fù)讀,幻讀可能發(fā)生
可重復(fù)讀:幻讀可能發(fā)生
可串行化:都不可能發(fā)生
但凡事沒有百分百,emmmm,其實(shí) MySQL 并沒有百分之百解決幻讀的問(wèn)題。
舉個(gè)例子:
session A:查詢某條不存在的記錄。
session B:新增該條不存在的記錄,并提交事務(wù)。
session A:再次查詢?cè)摋l不存在的記錄,是查詢不出來(lái)的,但是如果我嘗試修改該條記錄,并提交,其實(shí)他是可以修改成功的。
MVCC
版本鏈:對(duì)于該記錄的每次更新,都會(huì)將值放在一條 undo 日志中,算是該記錄的一個(gè)舊版本,隨著更新次數(shù)的增多,所有版本都會(huì)被 roll_pointer 屬性連接成一個(gè)鏈表,即為版本鏈。
readview:
未提交讀:因?yàn)榭梢宰x到未提交事務(wù)修改的記錄,所以可以直接讀取記錄的最新版本就行
已提交讀:每次讀取之前都生成一個(gè) readview
可重復(fù)讀:只有在第一次讀取的時(shí)候才生成 readview
可串行化:InnoDB 涉及了加鎖的方式來(lái)訪問(wèn)記錄
參考文獻(xiàn):
【原創(chuàng)】面試官:講講 MySQL 表設(shè)計(jì)要注意啥
【原創(chuàng)】雜談自增主鍵用完了怎么辦
MySQL 是怎樣運(yùn)行的:從根兒上理解 MySQL
詳細(xì)分析 MySQL 事務(wù)日志(redo log 和 undo log)
來(lái)源:本文內(nèi)容搜集或轉(zhuǎn)自各大網(wǎng)絡(luò)平臺(tái),并已注明來(lái)源、出處,如果轉(zhuǎn)載侵犯您的版權(quán)或非授權(quán)發(fā)布,請(qǐng)聯(lián)系小編,我們會(huì)及時(shí)審核處理。
聲明:江蘇教育黃頁(yè)對(duì)文中觀點(diǎn)保持中立,對(duì)所包含內(nèi)容的準(zhǔn)確性、可靠性或者完整性不提供任何明示或暗示的保證,不對(duì)文章觀點(diǎn)負(fù)責(zé),僅作分享之用,文章版權(quán)及插圖屬于原作者。
Copyright?2013-2024 JSedu114 All Rights Reserved. 江蘇教育信息綜合發(fā)布查詢平臺(tái)保留所有權(quán)利
蘇公網(wǎng)安備32010402000125
蘇ICP備14051488號(hào)-3技術(shù)支持:南京博盛藍(lán)睿網(wǎng)絡(luò)科技有限公司
南京思必達(dá)教育科技有限公司版權(quán)所有 百度統(tǒng)計(jì)