連接
??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ǎ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ǔ)引擎的比較
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服務(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
??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的持久化措施。
??為了避免這個(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ī)械手臂、磁頭趟庄、和主軸等組成括细。
??在盤片邏輯劃分上又分為磁道、扇區(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)圖
內(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 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ū)來解決综芥。
??所有數(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';
刷盤越快巡通,越安全尘执,也越消耗性能。
磁盤結(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的頁大小為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í)行一遍璃弄。
??執(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:無須操作。