MySQL中一條SQL是如何被執(zhí)行的?

SQL執(zhí)行順序

連接

??MySQL服務(wù)監(jiān)聽的端口默認(rèn)是3306珠十,客戶端連接服務(wù)端的方式有很多種料扰。可以是異步焙蹭,同步晒杈,短連接,長連接壳嚎,可以是TCP也可以是Unix Socket。

如何查看當(dāng)前MySQL當(dāng)前有多少個(gè)連接末早?

??可以使用show global status 烟馅,模糊匹配Thread:

show global status like 'Thread%';
字段 含義
Threads_cached 緩存中的線程連接數(shù)
Threads_connected 當(dāng)前打開的連接數(shù)
Threads_created 為處理連接創(chuàng)建的線程數(shù)
Threads_running 非睡眠狀態(tài)的連接數(shù),通常指并發(fā)連接數(shù)
為什么查看連接數(shù)是查看線程然磷?客戶端連接和服務(wù)端的線程是什么關(guān)系郑趁?

??客戶端每產(chǎn)生一個(gè)連接或者一個(gè)會(huì)話,在服務(wù)端就會(huì)創(chuàng)建一個(gè)線程來處理姿搜。想要?dú)⑺罆?huì)話就要Kill一個(gè)線程寡润。

??mysql 有兩個(gè)參數(shù)來管理不活動(dòng)的連接。

show global variables like 'wait_timeout'; -- 非交互式超時(shí)時(shí)間舅柜,如JDBC
show global variables like 'interactive_timeout'; -- 交互式超時(shí)時(shí)間梭纹,如數(shù)據(jù)庫工具

默認(rèn)是28800秒,8個(gè)小時(shí)致份。

MySQL默認(rèn)的連接數(shù)(并發(fā)數(shù))是多少舷礼?

??5.7版本中默認(rèn)是151個(gè)颓哮,最大值可以設(shè)置為十萬。

show  variables like 'max_connections';

MySQL參數(shù)的作用域

??有全局(Global)和會(huì)話(Session)基本,分別作用于全局和當(dāng)前會(huì)話笋粟。并不是所有參數(shù)都擁有兩種作用域。比如說巴比,max_connections就只有全局級(jí)別闷哆。

??當(dāng)語句中沒有Global時(shí)候,默認(rèn)是Session級(jí)別蝠引。

??比如下面這個(gè)只是臨時(shí)修改阳谍,建議修改為session級(jí)別。如果需要在其他會(huì)話中生效螃概,必須顯式加上Global參數(shù)边坤。

show variables like 'autocommit';
set autocommit = true;

建立完連接之后該怎么做呢?

查詢緩存

??mysql的默認(rèn)緩存是關(guān)閉的谅年。

show variables like 'query_cache%';
Variables Value
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 1048576
query_cache_type OFF
query_cache_wlock_invalidate OFF
為什么默認(rèn)關(guān)閉呢茧痒?

?使用場(chǎng)景極其有限,SQL語句必須一模一樣融蹂,中間不允許多一個(gè)空格旺订,而且大小寫敏感弄企;表里有任何一條數(shù)據(jù)的變化產(chǎn)生的時(shí)候,這張表的緩存都會(huì)失效区拳。對(duì)于有大量數(shù)據(jù)更新的應(yīng)用拘领,也不適合;

8.0版本中已經(jīng)移除

語法解析和預(yù)處理(Parser & Preprocessor)

為什么一條SQL能被正確識(shí)別呢樱调?

??這是由Parser解析器和Preprocessor預(yù)處理來完成的约素。這一步主要就是對(duì)語句基于SQL語法進(jìn)行詞法和語法分析語義分析。

詞法分析

??把一個(gè)完整的SQL語句打散為一個(gè)個(gè)單詞笆凌。

select name from `user` where id = 1;

??這段SQL會(huì)被打散為8個(gè)符號(hào)圣猎,每個(gè)符號(hào)的類型,起始的位置乞而。

語法分析

??對(duì)SQL做一些語法檢查送悔,比如單引號(hào)是否閉合,再根據(jù)MySQ定義的語法規(guī)則爪模,根據(jù)SQL語句生成一個(gè)數(shù)據(jù)結(jié)構(gòu)欠啤。叫做解析樹(select_lex)。

詞法語法分析-解析樹

??詞法語法分析屋灌,是一個(gè)非辰喽危基礎(chǔ)的功能,Java的編譯器共郭、百度搜索引擎如果要識(shí)別語句眉撵,也必須要有詞法語法分析的功能。
?? 任何數(shù)據(jù)庫的中間件落塑,要解析SQL完成路由功能纽疟,也必須要有詞法和語法分析的功能,比如MyCat憾赁,Sharding-JDBC(Druid Parser)污朽。在市面上也有很多的開源詞法解析工具(LEX,YACC)龙考。

預(yù)處理器

問題:如果我寫了一個(gè)條SQL蟆肆,但是表名或者字段不存在,會(huì)在哪里報(bào)錯(cuò)晦款?在數(shù)據(jù)庫的執(zhí)行層還是解析器炎功?

select xxx from userxxxx;

??其實(shí)還是在解析器這里報(bào)錯(cuò),解析SQL的環(huán)節(jié)里面有一個(gè)預(yù)處理器缓溅。
??預(yù)處理器會(huì)檢查生成的解析樹蛇损,解決解析器無法解析的語義。檢查表和列名是否存在,檢查名字和別名淤齐,保證沒有歧義股囊。

預(yù)處理之后,得到一個(gè)新的解析樹更啄。

查詢優(yōu)化器(Query Optimizer)與查詢執(zhí)行計(jì)劃稚疹。

思考一下:得到解析樹之后,是否就直接執(zhí)行SQL語句了祭务?一條SQL是否只有一種執(zhí)行方法内狗?數(shù)據(jù)庫執(zhí)行的SQL是否就是我們發(fā)送的SQL?

??答案是否定的义锥,一條SQL可以有很多種執(zhí)行方式柳沙,最終返回相同的結(jié)果,他們是等價(jià)的缨该。

多種的執(zhí)行方式偎行,如何得到川背,如何選擇那種進(jìn)行執(zhí)行贰拿,根據(jù)什么標(biāo)準(zhǔn)判斷?

??這些都是MySQL的查詢優(yōu)化器模塊Optimizer去完成的。

查詢優(yōu)化器根據(jù)解析樹生成不同的執(zhí)行計(jì)劃(Execution Plan)熄云,然后選擇一種最優(yōu)的執(zhí)行計(jì)劃膨更,MySQL里面使用的是基于開銷(cost)的優(yōu)化器,哪一種執(zhí)行計(jì)劃的開銷最小缴允,就選擇哪種荚守。

使用這個(gè)命令查看查詢的開銷:

show status like 'Last_query_cost';
MySQL優(yōu)化器可以做什么?

??1.多表關(guān)聯(lián)查詢练般,以哪個(gè)表作為基準(zhǔn)表矗漾。
??2.多個(gè)索引時(shí)候,使用哪個(gè)索引薄料。

??優(yōu)化器也不是萬能的敞贡,并不是再垃圾的SQL都能夠優(yōu)化,也不是每次都能選擇到最優(yōu)的執(zhí)行計(jì)劃摄职。

??MySQL提供了一個(gè)執(zhí)行計(jì)劃工具誊役,只需要在SQL語句前面加上EXPLAN。

EXPLAIN select xxx from xxx where xx =xx;
---------格式化輸出
EXPLAIN FORMAT=JSON select xxx from xxx where xx =xx;

存儲(chǔ)引擎

??顧名思義谷市,存儲(chǔ)數(shù)據(jù)的引擎蛔垢,存儲(chǔ)引擎包含了存儲(chǔ)方式、存儲(chǔ)結(jié)構(gòu)迫悠、檢索方式等鹏漆。

??MySQL5.5之后默認(rèn)的存儲(chǔ)引擎是INNODB,除此之外還有 MYISAM、MEMORY等等甫男。

創(chuàng)建表的時(shí)候我們可以指定引擎且改,使用ENGINE關(guān)鍵字。
CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

??默認(rèn)情況下板驳,每個(gè)數(shù)據(jù)庫都有自己的文件夾又跛,任何一個(gè)存儲(chǔ)引擎都有一個(gè)frm文件,這個(gè)是表結(jié)構(gòu)定義文件若治。


存儲(chǔ)方式

??不同的存儲(chǔ)引擎產(chǎn)生的文件不一樣慨蓝,INNODB是一個(gè),MEMORY沒有端幼,MYISAM是兩個(gè)礼烈。

不同的存儲(chǔ)引擎是為了應(yīng)對(duì)不一樣的業(yè)務(wù)場(chǎng)景:

??一張表,需要很高的訪問速度婆跑,而不需要考慮持久化問題此熬,可以選擇把數(shù)據(jù)放在內(nèi)存。
??一張表滑进,是用來做歷史數(shù)據(jù)存檔犀忱,不需要修改,也不需要索引扶关,需要支持?jǐn)?shù)據(jù)壓縮等等阴汇。
??一張表,用于讀寫并發(fā)很多的業(yè)務(wù)中节槐,是不是需要支持讀寫不干擾搀庶,需要保證較高的數(shù)據(jù)一致性?

存儲(chǔ)引擎的比較

存儲(chǔ)引擎的比較

MYISAM(三個(gè)文件)

??應(yīng)用范圍較小铜异。表級(jí)別的鎖定限制了讀寫的性能哥倔,在WEB中嘗嘗用于只讀或者以讀為主的功能。

特點(diǎn):

??支持表級(jí)別鎖(插入和更新會(huì)鎖表)揍庄。不支持事務(wù)咆蒿。
??擁有較高的插入和查詢速度。
??存儲(chǔ)了表的行數(shù)(count速度快)币绩。

(如何快速向數(shù)據(jù)庫插入100W行數(shù)據(jù)蜡秽?表的引擎先用MYISAM插入數(shù)據(jù),然后修改引擎為INNODB操作)缆镣。

場(chǎng)景

??只讀之類的數(shù)據(jù)分析芽突。

INNODB

??MySQL的默認(rèn)引擎,INNODB是一個(gè)事務(wù)安全(acid兼容)的存儲(chǔ)引擎董瞻,具有提交寞蚌、回滾田巴、崩潰恢復(fù)功能來保護(hù)用戶數(shù)據(jù)。INNODB行級(jí)別的鎖(不升級(jí)為更粗粒度的鎖)和ORACLE風(fēng)格一致非鎖讀提高了多用戶并發(fā)和性能挟秤。INNODB將用戶數(shù)據(jù)存儲(chǔ)在聚集索引中壹哺,以減少基于主鍵的IO查詢,為了保證數(shù)據(jù)完整性艘刚,INNODB還支持外鍵引用完整性約束管宵。

特點(diǎn)

??支持事務(wù)、外鍵攀甚。數(shù)據(jù)的完整性箩朴,一致性高。
??支持行級(jí)別的鎖和表級(jí)別的鎖秋度。
??支持讀寫并發(fā)炸庞,寫不阻塞讀(MVCC)。
??特殊的索引存放方式荚斯,可以減少IO埠居,提升查詢效率。

場(chǎng)景

??經(jīng)常更新事期,存在并發(fā)讀寫或者有事務(wù)處理的業(yè)務(wù)系統(tǒng)滥壕。

MOMERY

??所有數(shù)據(jù)都存放于內(nèi)存中,提供快速查找(非關(guān)鍵數(shù)據(jù))的場(chǎng)景下使用刑赶。INNODB及其緩沖內(nèi)存區(qū)域捏浊,提供一種通用懂衩、持久的方法將大部分?jǐn)?shù)據(jù)保存在內(nèi)存中撞叨,ndbcluster為大型分布式系統(tǒng)數(shù)據(jù)集提供了快速的鍵值查找。
正在淘汰浊洞。

特點(diǎn):

??數(shù)據(jù)存放于內(nèi)存中牵敷,讀寫速度很快,但是數(shù)據(jù)庫重啟或者崩潰法希,數(shù)據(jù)會(huì)完全消失枷餐。適合做臨時(shí)表。

場(chǎng)景

??不重要的數(shù)據(jù)存儲(chǔ)苫亦。
??臨時(shí)表毛肋。

CSV

??他的表其實(shí)是帶有逗號(hào)分割值的文本文件。允許CSV格式的數(shù)據(jù)導(dǎo)入或者轉(zhuǎn)存數(shù)據(jù)屋剑。

特點(diǎn)

??不允許空行润匙,不支持索引。格式通用唉匾,可以直接編輯孕讳,適合不同數(shù)據(jù)庫之間導(dǎo)入導(dǎo)出匠楚。

ARCHIVE(兩個(gè)文件)

??這些緊湊的未索引的表,用于存儲(chǔ)和檢索大量很少引用的歷史數(shù)據(jù)厂财、存檔和安全審計(jì)信息芋簿。

特點(diǎn)

??不支持索引,不支持update delete璃饱。

如何選擇存儲(chǔ)引擎与斤?
??對(duì)數(shù)據(jù)庫一致性要求較高,需要事務(wù)支持荚恶。innodb
??查詢多更新少幽告,對(duì)查詢性能要求高。myisam
??用于查詢的臨時(shí)表裆甩。memory
??如果都滿足不了冗锁,自己用C寫一個(gè)。

執(zhí)行引擎(Query Execution Engine)嗤栓,返回結(jié)果冻河。

??執(zhí)行引擎負(fù)責(zé)執(zhí)行(執(zhí)行計(jì)劃)最后返回?cái)?shù)據(jù)給客戶端。

MYSQL架構(gòu)分層

??總體分層:連接池茉帅、服務(wù)層叨叙、存儲(chǔ)引擎層

mysql架構(gòu).jpg
連接層

??客戶端需要連接到MYSQL服務(wù)器3306端口,必須要跟服務(wù)端建立連接堪澎,那么管理連接和驗(yàn)證連接都在連接層完成擂错。

服務(wù)層

??通過連接層獲得session,發(fā)送sql語句給服務(wù)層樱蛤。
??比如查詢緩存钮呀,根據(jù)SQL調(diào)用對(duì)應(yīng)接口,詞法解析昨凡,語法分析爽醋,執(zhí)行計(jì)劃,索引選擇等等便脊。
??然后就是優(yōu)化器蚂四,得到執(zhí)行計(jì)劃,交給執(zhí)行器去執(zhí)行哪痰。

存儲(chǔ)引擎

??真正存儲(chǔ)數(shù)據(jù)的地方遂赠。

Buffer pool

MsqlBufferPool.jpg

??MySQL的數(shù)據(jù)庫文件都是存儲(chǔ)在磁盤的。

??內(nèi)存寫向磁盤是IO操作晌杰,比較耗費(fèi)時(shí)間跷睦,MySQL提出來一個(gè)叫緩沖區(qū)的概念,先寫入緩沖區(qū)再由緩沖區(qū)寫入磁盤乎莉,這個(gè)操作我們稱之為刷臟送讲。

??在操作系統(tǒng)奸笤、存儲(chǔ)引擎,都有一個(gè)預(yù)讀概念哼鬓。當(dāng)磁盤上的一塊數(shù)據(jù)被讀取時(shí)候监右,其他附近位置的數(shù)據(jù)也會(huì)馬上被讀到,這個(gè)就叫局部性原理异希。
??INNODB設(shè)定了一個(gè)存儲(chǔ)引擎從磁盤讀取數(shù)據(jù)到內(nèi)存的最小單位是頁健盒,在操作系統(tǒng)中頁的大小為4kb,在INNODB里面這個(gè)最小的默認(rèn)單位為16KB称簿。

Redo log

??刷臟并不是實(shí)時(shí)的扣癣,如果Buffer Pool的臟頁沒有刷完,數(shù)據(jù)庫宕機(jī)或者停電了憨降,數(shù)據(jù)會(huì)丟失父虑。
??這個(gè)時(shí)候引入了一個(gè) redo log的持久化措施。

Redo Log.jpg

??為了避免這個(gè)問題授药,mysql會(huì)把所有對(duì)頁面修改操作專門寫入一個(gè)日志文件(RedoLog)士嚎。
??如果有未同步到磁盤的數(shù)據(jù),數(shù)據(jù)庫會(huì)在啟動(dòng)的時(shí)候悔叽,從這個(gè)日志文件進(jìn)行恢復(fù)操作(實(shí)現(xiàn)crash-safe)莱衩。我們數(shù)據(jù)庫事務(wù)的持久性就是用它來實(shí)現(xiàn)。

寫日志文件和寫到數(shù)據(jù)文件的區(qū)別是什么娇澎?
??寫入速度

??硬盤物理上主要是盤片笨蚁、機(jī)械手臂、磁頭趟庄、和主軸等組成括细。

磁盤物理結(jié)構(gòu)

??在盤片邏輯劃分上又分為磁道、扇區(qū)岔激,例如下圖:


磁道勒极,扇區(qū)

??磁盤的寫入是需要尋址的是掰,磁盤是一個(gè)圓虑鼎,磁頭固定旋轉(zhuǎn)后產(chǎn)生磁道,然后每個(gè)磁道根據(jù)半徑的劃分的區(qū)域就是扇區(qū)键痛,磁盤寫入的就是需要找到這些扇區(qū)然后進(jìn)行寫入炫彩。
??如果數(shù)據(jù)是隨機(jī)散落在不同的扇區(qū),那么需要磁頭旋轉(zhuǎn)找到對(duì)應(yīng)的數(shù)據(jù)頁絮短,然后盤片找到對(duì)應(yīng)扇區(qū)才能找到一塊數(shù)據(jù)江兢,一次次進(jìn)行這個(gè)過程直到找到所有數(shù)據(jù)為止。
??刷盤是隨機(jī)IO丁频,而記錄日志是順序IO(連續(xù)讀寫)杉允,順序IO的效率上更高邑贴。本質(zhì)上就是數(shù)據(jù)集中處理和分散存儲(chǔ)的區(qū)別。因此如果寫入日志文件能夠大大的保證數(shù)據(jù)的安全性叔磷,可以延遲刷盤 拢驾,提高吞吐。

redolog 位于/var/lib/mysql目錄下的ib_logfile0和ib_logfile1 默認(rèn)兩個(gè)文件每個(gè)48M改基。

show  global variables like 'innodb_log%';
特點(diǎn)

??redo log是INNODB存儲(chǔ)引擎的實(shí)現(xiàn)繁疤,支持崩潰恢復(fù)的INNODB的一個(gè)特性。
??redo log不是記錄數(shù)據(jù)頁更新之后的狀態(tài)秕狰,而是記錄”在某項(xiàng)數(shù)據(jù)頁上做了什么修改“稠腊。屬于物理日志
??redo log的大小是固定的,前面的內(nèi)容會(huì)被覆蓋鸣哀,一旦寫滿會(huì)觸發(fā)buffer pool到磁盤同步架忌,以便騰出空間記錄后面的修改。

Undo Log

??Undo log(撤銷或者回滾日志)記錄了事務(wù)發(fā)生之前的數(shù)據(jù)狀態(tài)我衬,分別為 inser undo log和update undo log鳖昌。如果修改數(shù)據(jù)發(fā)生異常,可以使用undo log來實(shí)現(xiàn)回滾低飒。(保證原子性)

??可以理解undo log記錄的是反向操作许昨,比如insert會(huì)記錄delete,update 會(huì)記錄update 原來的值褥赊,跟redolog記錄在哪個(gè)物理頁面操作不一樣糕档,所以叫做邏輯日志。

show  global variables like '%undo%';

一條更新語句的過程

update user set name = 'dia' where id = 1;

??1.事務(wù)開啟拌喉,從(buffer pool)或者磁盤讀人倌恰(datafile)包含這條數(shù)據(jù)的頁,返回給Server的執(zhí)行器尿背。
??2.Server的執(zhí)行器修改數(shù)據(jù)頁的這一行數(shù)據(jù)為 dia端仰。
??3.記錄name = 舊值 到undolog。
??4.記錄name = 新值 到redolog田藐。
??5.調(diào)用存儲(chǔ)引擎接口荔烧,記錄數(shù)據(jù)頁到buffer pool(修改name=dia)。
??6.事務(wù)提交汽久。

INNODB架構(gòu)圖

image.png
內(nèi)存結(jié)構(gòu)

??Buffer Pool主要分為三個(gè)部分:Buffer Pool鹤竭、Change Buffer、Adaptive HashIndex景醇,另外還有一個(gè)(redo)log buffer臀稚。

Buffer Pool

??Buffer Pool緩存的是頁面信息,包括數(shù)據(jù)頁三痰,索引頁吧寺。
??Buffer Pool默認(rèn)大小是128m窜管。(可以調(diào)整)

show  global variables like '%innodb_buffer_pool_size%';

??這個(gè)是會(huì)問到Buffer Pool寫滿了怎么辦(Rdis設(shè)置的內(nèi)存滿了怎么辦?)LRU
算法來管理緩沖池(鏈表實(shí)現(xiàn)稚机,不是傳統(tǒng)的LRU微峰,分成了young和old)經(jīng)過淘汰的數(shù)據(jù)就是熱點(diǎn)數(shù)據(jù)。

LRU

??傳統(tǒng)LRU抒钱,可以用Map+鏈表實(shí)現(xiàn)蜓肆。value存的是鏈表中的地址。

mysql double link.jpg

??mysql innodb使用雙向鏈表谋币,LRU list仗扬。但是這個(gè)LRUlist 不是存放datapage,而是指向緩存頁面的指針蕾额。

??如果buffer pool的時(shí)候發(fā)現(xiàn)沒有空閑頁了早芭,就要從buffer pool中淘汰數(shù)據(jù)頁了。

為什么這么設(shè)計(jì)诅蝶?

??因?yàn)閕nnodb的預(yù)讀機(jī)制退个,數(shù)據(jù)頁并不是在被訪問的時(shí)候才緩存到buffer pool。設(shè)計(jì)者認(rèn)為调炬,訪問某個(gè)page頁的數(shù)據(jù)時(shí)候语盈,相鄰的page 可能也會(huì)很快被訪問到,所以先把這些page先緩存起來缰泡。

緩存機(jī)制又分為兩種類型
一種叫線性預(yù)讀(異步)(Linear read-ahead)刀荒。
??innodb 把64個(gè)相鄰的page叫做一個(gè)extent區(qū),如果順序訪問了一個(gè)extent的56個(gè)page棘钞,這個(gè)時(shí)候innodb就會(huì)把下一個(gè)extent區(qū)緩存到buffer pool中缠借。順序訪問了多少個(gè)page 才緩存下一個(gè)extent,由一個(gè)參數(shù)控制:

show variables like 'innodb_read_ahead_threshold';

一種叫隨機(jī)預(yù)讀(Random read-ahead)
??如果buffer pool宜猜,已經(jīng)緩存了同一個(gè)extent區(qū)的數(shù)據(jù)頁個(gè)數(shù)超過13時(shí)候泼返,就會(huì)把這個(gè)extent剩余的所有page 全部緩存到buffer pool。
但是隨機(jī)預(yù)讀功能是不開啟的姨拥,由一個(gè)參數(shù)控制

show variables liek 'innodb_random_read_ahead';

??線性預(yù)讀可以提高IO性能绅喉,但是也會(huì)帶來占用空間多的副作用。

如果buffer pool size 不是很大垫毙,而且預(yù)讀的數(shù)量很多霹疫,很可能那些真正被需要緩存的數(shù)據(jù)被預(yù)讀數(shù)據(jù)擠出buffer pool。

這個(gè)問題mysql 通過冷熱數(shù)據(jù)區(qū)來解決综芥。

image.png

??所有數(shù)據(jù)加入到buffer pool 的時(shí)候,一律放在冷區(qū)head猎拨,不管是預(yù)讀還是普通讀操作膀藐。所以如果預(yù)讀數(shù)據(jù)沒有被讀取屠阻,會(huì)在old sublist冷區(qū)直接淘汰。
??放入LRU List以后额各,如果再次被訪問国觉,都會(huì)把它移動(dòng)到熱區(qū)的Head。
??如果熱區(qū)的數(shù)據(jù)沒有被訪問虾啦,會(huì)被移動(dòng)冷區(qū)head麻诀,然后慢慢被淘汰。

??熱區(qū)5/8傲醉,冷區(qū)3/8蝇闭,這個(gè)值由innodb_old_blocks_pct控制。
它代表的是old區(qū)的大小硬毕,默認(rèn)是37%呻引。如果這個(gè)值太小,old區(qū)沒有被訪問的數(shù)據(jù)淘汰會(huì)更快吐咳。

這樣是否沒有問題了逻悠?

??如果在同一時(shí)間很多冷區(qū)數(shù)據(jù)被訪問,會(huì)導(dǎo)致大量的數(shù)據(jù)都移動(dòng)到了熱區(qū)韭脊,有可能會(huì)導(dǎo)致大量的熱區(qū)數(shù)據(jù)失效童谒。這個(gè)問題怎么解決?

??加大加入冷區(qū)后的訪問間隔沪羔,INNODB_OLD_BLOCKS_TIME(默認(rèn)1s)這個(gè)參數(shù)來控制惠啄,加入冷區(qū)多少S后被訪問才放入熱區(qū)數(shù)據(jù)。

Change buffer

??Change Buffer 是Buffer pool的一部分任内。
??如果這個(gè)數(shù)據(jù)頁不是唯一索引撵渡,不存在數(shù)據(jù)重復(fù)的情況,也就不需要從磁盤判斷數(shù)據(jù)是否唯一(唯一性檢查)死嗦。這種情況可以先把修改記錄和內(nèi)存的緩存池中趋距,從而提升(inser,delete越除,update)性能节腐。
??最后把Change Buffer記錄到數(shù)據(jù)頁的操作叫做merge。什么時(shí)候發(fā)生merge摘盆?有幾種情況:在訪問這個(gè)數(shù)據(jù)頁的時(shí)候翼雀,或者用過訪問后臺(tái)線程、或者數(shù)據(jù)庫shutdown孩擂、redolog寫滿 時(shí)候觸發(fā)狼渊。

可以通過以下這個(gè)值,改變change 大小,以支持寫多讀少的業(yè)務(wù)場(chǎng)景狈邑。

show variables like 'innodb_change_buffer_max_size';

代表Change Buffer占Buffer Pool的比例城须,默認(rèn)25%。

Adaptive HASH INDEAX

??哈希索引放內(nèi)存米苹。為甚糕伐? 我也不知道

Redo log Buffer

??RedoLog 也不是每次都寫入磁盤,在Buffer pool里面有一塊內(nèi)存區(qū)域(Log Buffer)專門用于保存 將要寫入的內(nèi)存文件的數(shù)據(jù)蘸嘶,默認(rèn)是16m良瞧,它一樣可以節(jié)省磁盤io。

show variables like 'innodb_log_buffer_size' ;

需要注意:redo log的內(nèi)容主要是用于崩潰數(shù)據(jù)的恢復(fù)训唱。磁盤文件的數(shù)據(jù)文件褥蚯,數(shù)據(jù)來自于buffer pool,redo log 寫入磁盤雪情,而不是寫入文件遵岩。
寫入時(shí)間是參數(shù)配置的,默認(rèn)是1

show variables like 'innodb_flush_log_at_tx_commit';
log buffer 刷入時(shí)機(jī)

log buffer 刷入?yún)^(qū)別

刷盤越快巡通,越安全尘执,也越消耗性能。

磁盤結(jié)構(gòu)

??表空間可以看做是INNODB的存儲(chǔ)引擎邏輯結(jié)構(gòu)的最高層宴凉,所有的數(shù)據(jù)都存放在表空間中誊锭。INNODB的表空間分為五大塊。

系統(tǒng)表空間

??默認(rèn)情況下弥锄,innodb存儲(chǔ)引擎有一個(gè)共享表空間(/var/lib/mysql/ibdata1)系統(tǒng)表空間丧靡。

??INNODB系統(tǒng)表空間包括innodb數(shù)據(jù)字典和雙寫緩沖區(qū),changebuffer和undologs籽暇,如果沒有指定file-per-table温治,也包含用戶創(chuàng)建的表和索引數(shù)據(jù)。

1.undo(不詳)戒悠,也可以設(shè)置為單獨(dú)表空間熬荆。
2.數(shù)據(jù)字典:由內(nèi)部表構(gòu)成,存儲(chǔ)表和索引的元數(shù)據(jù)(定義信息)绸狐。
3.雙寫緩沖(INNODB的特性)卤恳。

INNODB頁結(jié)構(gòu)

??如圖,innodb的頁大小為16寒矿,操作系統(tǒng)的頁大學(xué)為4k突琳,一頁數(shù)據(jù)需要寫四次。

??在存儲(chǔ)引擎寫的過程如果宕機(jī)符相,可能出現(xiàn)頁只寫了一部分的情況(partial page write部分寫失效)可能會(huì)導(dǎo)致數(shù)據(jù)丟失拆融。

show variables like 'innodb_doublewrite';

這里就是雙寫緩沖的配置了。

為什么需要雙寫緩沖?
??如果頁崩潰之前它已經(jīng)損壞了冠息,那么用來做崩潰恢復(fù)沒有任何意義挪凑。所以在運(yùn)用redo log的時(shí)候需要一個(gè)頁副本孕索,如果出現(xiàn)部分寫失效就用頁的 副本還原頁再完成崩潰恢復(fù)逛艰。這個(gè)頁的副本就是double write,innodb的雙寫技術(shù)搞旭。

默認(rèn)情況下散怖,所有表共享一個(gè)表空間,這個(gè)文件會(huì)越來越大肄渗,而且不會(huì)收縮镇眷。

獨(dú)占表空間(file-per-talbe tablespace)

??我們可以讓每個(gè)表都獨(dú)占一個(gè)表空間。

show variables like 'innodb_file_per_table';

??開啟后翎嫡,每個(gè)表都會(huì)開辟一個(gè)表空間欠动,這個(gè)文件就是數(shù)據(jù)目錄下的ibd文件,存放表的索引和數(shù)據(jù)惑申。
??但是其他類的數(shù)據(jù)具伍,如回滾(undo)信息,插入緩沖索引頁圈驼、系統(tǒng)事務(wù)信息人芽,雙寫緩沖還是放在原來的表空間。

通用表空間 (general tablespaces)

??也是 一種共享表空間绩脆,跟ibdate1類似萤厅。
??可以創(chuàng)建一個(gè)通用的表空間,用來存儲(chǔ)不同數(shù)據(jù)庫的表靴迫,數(shù)據(jù)路徑文件和自己定義惕味。

create tablespace xx_tbspace  add data file '/var/lib/mysql/xxx_tbspace.ibd' file_block_size=16k engine = innodb;
臨時(shí)表空間 temporary tablespaces

??存儲(chǔ)臨時(shí)表的數(shù)據(jù),包括用戶創(chuàng)建的臨時(shí)表玉锌,和磁盤的內(nèi)部臨時(shí)表名挥,對(duì)應(yīng)ibtmp1文件,當(dāng)數(shù)據(jù)庫服務(wù)關(guān)閉時(shí)候芬沉,該表空間刪除躺同,下次重新產(chǎn)生。

redo log

??看上面

undo log

??undo log的數(shù)據(jù)默認(rèn)在系統(tǒng)表空間ibdata1文件中丸逸,因?yàn)楣蚕肀砜臻g是不自動(dòng)收縮的蹋艺,也可以單獨(dú)創(chuàng)建表空間。

后臺(tái)線程

??主要負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù)和修改的數(shù)據(jù)頁刷新到磁盤黄刚。

master thread 負(fù)責(zé)刷新緩存數(shù)據(jù)到磁盤并協(xié)調(diào)調(diào)度其他線程捎谨。
IO thread 分別為insert buffer、log、read涛救、write進(jìn)程畏邢,分別處理insert buffer、重做日志检吆、讀寫請(qǐng)求的IO回調(diào)舒萎。
purge thread 用于回收undo 頁
page cleanner thread 用來刷新臟頁
mysql server 層 還有一個(gè) binlog,它可以被所有存儲(chǔ)引擎使用蹭沛。

BinLog

SQL語言分為3種: DDL, DML, DCL

DML(data manipulation language)是數(shù)據(jù)操縱語言:它們是SELECT臂寝、UPDATE、INSERT摊灭、DELETE咆贬,就象它的名字一樣,這4條命令是用來對(duì)數(shù)據(jù)庫里的數(shù)據(jù)進(jìn)行操作的語言帚呼。

DDL(data definition language)是數(shù)據(jù)定義語言:DDL比DML要多掏缎,主要的命令有CREATE、ALTER煤杀、DROP等眷蜈,DDL主要是用在定義或改變表(TABLE)的結(jié)構(gòu),數(shù)據(jù)類型怜珍,表之間的鏈接和約束等初始化工作上端蛆,他們大多在建立表時(shí)使用。

DCL(DataControlLanguage)是數(shù)據(jù)庫控制語言:是用來設(shè)置或更改數(shù)據(jù)庫用戶或角色權(quán)限的語句酥泛,包括(grant,deny,revoke等)語句今豆。

??binlog 以事件的形式記錄所有的DDL和DML語句(因?yàn)樗涗浀氖遣僮鞫皇菙?shù)據(jù)值,屬于邏輯日志)可以用來做主從復(fù)制和數(shù)據(jù)恢復(fù)柔袁。

??跟redo log不一樣呆躲,內(nèi)容是可以追加的,沒有固定大小限制捶索。

??開啟了binlog功能的情況下插掂,我們可以把binlog導(dǎo)出成SQL語句,把所有的操作重放一遍腥例,來實(shí)現(xiàn)數(shù)據(jù)恢復(fù)辅甥。
??還有一種功能就是主從復(fù)制,原理就是從服務(wù)器中讀取主服務(wù)器的binlog燎竖,然后執(zhí)行一遍璃弄。

binlog.jpg

??執(zhí)行:update user set name = 'dia' where id = 1;
??1.先查到這條數(shù)據(jù),如果有緩存則使用构回。
??2.把name值修改夏块,然后調(diào)用引擎api接口疏咐,寫入這條數(shù)據(jù)到內(nèi)存中。這個(gè)時(shí)候redolog進(jìn)入prepare狀態(tài)脐供,告訴執(zhí)行器執(zhí)行完成浑塞,可以提交。
??3.執(zhí)行器收到后通知binlog政己,然后調(diào)用存儲(chǔ)引擎接口提交酌壕,并這是redolog為commit狀態(tài)。
??4.更新完成匹颤。

重點(diǎn):

??1.先記錄到內(nèi)存仅孩,再到文件托猩。
??2.記錄redo log兩個(gè)狀態(tài)兩個(gè)階段印蓖。
??3.存儲(chǔ)引擎和server 記錄不一樣的日志。
??4.先記錄redo再記錄binlog京腥。

為什么需要兩個(gè)階段提交赦肃?

??在存儲(chǔ)引擎寫的過程如果宕機(jī),可能出現(xiàn)頁只寫了一部分的情況(partial page write部分寫失效)可能會(huì)導(dǎo)致數(shù)據(jù)丟失公浪。

??比如我們執(zhí)行把name改為dia他宛,如果寫完了redo log鉴裹,還沒有寫入binlog的時(shí)候mysql 重啟了何陆。
??redolog 可用于數(shù)據(jù)恢復(fù),所以寫入磁盤的dia戈二,然后binlog沒有這個(gè)邏輯日志预柒,如果這個(gè)時(shí)候出現(xiàn)主從同步队塘,就會(huì)出現(xiàn)數(shù)據(jù)不一致的情況。

??所以在寫兩個(gè)日志的情況下宜鸯,binlog就充當(dāng)一個(gè)事務(wù)的協(xié)調(diào)者憔古。通過innodb來執(zhí)行prepare或者commit、rollback淋袖。如果 binlog寫入失敗就不會(huì)提交鸿市。

??在崩潰的時(shí)候判斷事務(wù)是否需要提交:

1.binlog無記錄,redolog無記錄:在redolog寫之前crash即碗⊙媲椋恢復(fù)操作:回滾。
2.binlog無記錄剥懒,redolog狀態(tài)prepare:在binlog寫之前crash内舟。恢復(fù)操作:回滾蕊肥。
3.binlog有記錄谒获,redolog狀態(tài)prepare:在binlog寫完提交事務(wù)之前的crash 蛤肌。恢復(fù)操作:提交批狱。
4.binlog有記錄裸准,redolog狀態(tài)commit:無須操作。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末赔硫,一起剝皮案震驚了整個(gè)濱河市炒俱,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌爪膊,老刑警劉巖权悟,帶你破解...
    沈念sama閱讀 218,682評(píng)論 6 507
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異推盛,居然都是意外死亡峦阁,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,277評(píng)論 3 395
  • 文/潘曉璐 我一進(jìn)店門耘成,熙熙樓的掌柜王于貴愁眉苦臉地迎上來榔昔,“玉大人,你說我怎么就攤上這事瘪菌∪龌幔” “怎么了?”我有些...
    開封第一講書人閱讀 165,083評(píng)論 0 355
  • 文/不壞的土叔 我叫張陵师妙,是天一觀的道長诵肛。 經(jīng)常有香客問我,道長默穴,這世上最難降的妖魔是什么怔檩? 我笑而不...
    開封第一講書人閱讀 58,763評(píng)論 1 295
  • 正文 為了忘掉前任,我火速辦了婚禮壁顶,結(jié)果婚禮上珠洗,老公的妹妹穿的比我還像新娘。我一直安慰自己若专,他們只是感情好许蓖,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,785評(píng)論 6 392
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著调衰,像睡著了一般膊爪。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上嚎莉,一...
    開封第一講書人閱讀 51,624評(píng)論 1 305
  • 那天米酬,我揣著相機(jī)與錄音,去河邊找鬼趋箩。 笑死赃额,一個(gè)胖子當(dāng)著我的面吹牛加派,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播跳芳,決...
    沈念sama閱讀 40,358評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼芍锦,長吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來了飞盆?” 一聲冷哼從身側(cè)響起娄琉,我...
    開封第一講書人閱讀 39,261評(píng)論 0 276
  • 序言:老撾萬榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎吓歇,沒想到半個(gè)月后孽水,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 45,722評(píng)論 1 315
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡城看,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,900評(píng)論 3 336
  • 正文 我和宋清朗相戀三年女气,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片析命。...
    茶點(diǎn)故事閱讀 40,030評(píng)論 1 350
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡主卫,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出鹃愤,到底是詐尸還是另有隱情,我是刑警寧澤完域,帶...
    沈念sama閱讀 35,737評(píng)論 5 346
  • 正文 年R本政府宣布软吐,位于F島的核電站,受9級(jí)特大地震影響吟税,放射性物質(zhì)發(fā)生泄漏凹耙。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,360評(píng)論 3 330
  • 文/蒙蒙 一肠仪、第九天 我趴在偏房一處隱蔽的房頂上張望肖抱。 院中可真熱鬧,春花似錦异旧、人聲如沸意述。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,941評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽荤崇。三九已至,卻和暖如春潮针,著一層夾襖步出監(jiān)牢的瞬間术荤,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,057評(píng)論 1 270
  • 我被黑心中介騙來泰國打工每篷, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留瓣戚,地道東北人端圈。 一個(gè)月前我還...
    沈念sama閱讀 48,237評(píng)論 3 371
  • 正文 我出身青樓,卻偏偏與公主長得像子库,于是被迫代替她去往敵國和親枫笛。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,976評(píng)論 2 355

推薦閱讀更多精彩內(nèi)容