怎樣深入學(xué)習(xí)理解mysql

要做DBA只學(xué)習(xí)MySQL肯定是不行的。因?yàn)閿?shù)據(jù)庫(kù)只是用來(lái)存儲(chǔ)數(shù)據(jù)的，而數(shù)據(jù)是提供給程序使用的。當(dāng)MySQL的數(shù)據(jù)量達(dá)到千萬(wàn)級(jí)別甚至億級(jí)，那樣性能會(huì)急劇下降，再通過(guò)數(shù)據(jù)庫(kù)本身進(jìn)行優(yōu)化，收效甚微。這時(shí)需要考慮很多方面，數(shù)據(jù)庫(kù)擴(kuò)展，集群，拆分（包括拆表，拆庫(kù)。拆表又可以水平拆分，垂直拆分）。但是這些對(duì)性能的提升都是百分之幾十或者幾倍。如果你懂程序，那么從業(yè)務(wù)層面優(yōu)化，那樣對(duì)性能的提升可以是數(shù)量級(jí)的提升。

創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供拉薩網(wǎng)站建設(shè)、拉薩做網(wǎng)站、拉薩網(wǎng)站設(shè)計(jì)、拉薩網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、拉薩企業(yè)網(wǎng)站模板建站服務(wù)，10多年拉薩做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

另外DBA不止精通MySQL。還有Oracle，DB2等比較重量級(jí)的數(shù)據(jù)庫(kù)也要深入了解。

深入理解mysql的聯(lián)合索引

最近在學(xué)習(xí)MySQL的存儲(chǔ)引擎和索引的知識(shí)。看了許多篇介紹MyISAM和InnoDB的索引的例子，都能理解。

像這張索引圖：

PS:該圖來(lái)自大神張洋的《MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理》一文。

但許多文章講述的都是單列索引，我很好奇 聯(lián)合索引對(duì)應(yīng)的結(jié)構(gòu)圖是怎樣的。

比方說(shuō)聯(lián)合索引 (col1, col2,col3)，我知道在邏輯上是先按照col1進(jìn)行排序再按照col2進(jìn)行排序最后再按照col3進(jìn)行排序。因此如果是select * from table where col1 = 1 and col3 = 3的話，只有col1的索引部分能生效。但是其物理結(jié)構(gòu)上這個(gè)聯(lián)合索引是怎樣存在的，我想不懂。

上網(wǎng)查閱了許多資料，總算有點(diǎn)眉目了。

假設(shè)這是一個(gè)多列索引(col1, col2,col3)，對(duì)于葉子節(jié)點(diǎn)，是這樣的：

PS:該圖改自《MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理》一文的配圖。

也就是說(shuō)，聯(lián)合索引(col1, col2,col3)也是一棵B+Tree，其非葉子節(jié)點(diǎn)存儲(chǔ)的是第一個(gè)關(guān)鍵字的索引，而葉節(jié)點(diǎn)存儲(chǔ)的則是三個(gè)關(guān)鍵字col1、col2、col3三個(gè)關(guān)鍵字的數(shù)據(jù)，且按照col1、col2、col3的順序進(jìn)行排序。

配圖可能不太讓人滿意，因?yàn)閏ol1都是不同的，也就是說(shuō)在col1就已經(jīng)能確定結(jié)果了。自己又畫了一個(gè)圖（有點(diǎn)丑），col1表示的是年齡，col2表示的是姓氏，col3表示的是名字。如下圖：

PS:對(duì)應(yīng)地址指的是數(shù)據(jù)記錄的地址。

如圖，聯(lián)合索引(年齡, 姓氏,名字)，葉節(jié)點(diǎn)上data域存儲(chǔ)的是三個(gè)關(guān)鍵字的數(shù)據(jù)。且是按照年齡、姓氏、名字的順序排列的。

因此，如果執(zhí)行的是：

select * from STUDENT where 姓氏='李' and 名字='安';

或者

select * from STUDENT where 名字='安';

那么當(dāng)執(zhí)行查詢的時(shí)候，是無(wú)法使用這個(gè)聯(lián)合索引的。因?yàn)槁?lián)合索引中是先根據(jù)年齡進(jìn)行排序的。如果年齡沒(méi)有先確定，直接對(duì)姓氏和名字進(jìn)行查詢的話，就相當(dāng)于亂序查詢一樣，因此索引無(wú)法生效。因此查詢是全表查詢。

如果執(zhí)行的是：

select * from STUDENT where 年齡=1 and 姓氏='李';

那么當(dāng)執(zhí)行查詢的時(shí)候，索引是能生效的，從圖中很直觀的看出，age=1的是第一個(gè)葉子節(jié)點(diǎn)的前6條記錄，在age=1的前提下，姓氏=’李’的是前3條。因此最終查詢出來(lái)的是這三條，從而能獲取到對(duì)應(yīng)記錄的地址。

如果執(zhí)行的是：

select * from STUDENT where 年齡=1 and 姓氏='黃' and 名字='安';

那么索引也是生效的。

而如果執(zhí)行的是：

select * from STUDENT where 年齡=1 and 名字='安';

那么，索引年齡部分能生效，名字部分不能生效。也就是說(shuō)索引部分生效。

因此我對(duì)聯(lián)合索引結(jié)構(gòu)的理解就是B+Tree是按照第一個(gè)關(guān)鍵字進(jìn)行索引，然后在葉子節(jié)點(diǎn)上按照第一個(gè)關(guān)鍵字、第二個(gè)關(guān)鍵字、第三個(gè)關(guān)鍵字…進(jìn)行排序。

而之所以會(huì)有最左原則，是因?yàn)槁?lián)合索引的B+Tree是按照第一個(gè)關(guān)鍵字進(jìn)行索引排列的。

聯(lián)合索引在B+樹(shù)上的結(jié)構(gòu)介紹

如何學(xué)好MYSQL數(shù)據(jù)庫(kù)呢

1、從最簡(jiǎn)單的Access（文件型數(shù)據(jù)庫(kù)）開(kāi)始吧，軟件網(wǎng)上有下載，首先基礎(chǔ)的概念必須掌握，比如什么叫表、什么叫主鍵。。。。。。

2、根據(jù)教材上的示例自己建幾個(gè)簡(jiǎn)單的庫(kù)，熟悉各種操作。

3、學(xué)習(xí)SQL語(yǔ)句，掌握查詢的用法。

4、做更多的實(shí)例加深理解，掌握學(xué)到的東西。

5、學(xué)習(xí)SQLserver掌握試圖、存儲(chǔ)過(guò)程、觸發(fā)器、游標(biāo)等用法，掌握關(guān)系型數(shù)據(jù)庫(kù)的基本操作。

6、深入學(xué)習(xí)大型數(shù)據(jù)庫(kù)的知識(shí)，熟練復(fù)雜SQL語(yǔ)句的書(shū)寫。

7、根據(jù)需要再掌握Oracle、DB2等大型數(shù)據(jù)庫(kù)。有了前面的基礎(chǔ)，不論學(xué)習(xí)什么品牌的數(shù)據(jù)庫(kù)都會(huì)比較容易上手。

MySQL innodb引擎深入講解

表空間(ibd文件),一個(gè)MySQL實(shí)例可以對(duì)應(yīng)多個(gè)表空間，用于存儲(chǔ)記錄，索引等數(shù)據(jù)。

段，分為數(shù)據(jù)段、索引段、回滾段，innodb是索引組織表，數(shù)據(jù)段就是B+Tree的葉子節(jié)點(diǎn)，索引段為非葉子節(jié)點(diǎn)，段用來(lái)管理多個(gè)區(qū)。

區(qū)，表空間的單元結(jié)構(gòu)，每個(gè)區(qū)的大小為1M,默認(rèn)情況下，innodb存儲(chǔ)引擎頁(yè)大小為16K，即一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁(yè)。

頁(yè)，是innodb存儲(chǔ)引擎磁盤管理的最小單元，每個(gè)頁(yè)的大小為16K，為了保證頁(yè)的連續(xù)性，innodb存儲(chǔ)引擎每次從磁盤申請(qǐng)4~5個(gè)區(qū)。

行，innodb存儲(chǔ)引擎數(shù)據(jù)是按行進(jìn)行存儲(chǔ)的。Trx_id 最后一次事務(wù)操作的id、roll_pointer滾動(dòng)指針。

i nnodb的內(nèi)存結(jié)構(gòu) ，由Buffer Pool、Change Buffer和Log Buffer組成。

Buffer Pool : 緩沖池是主內(nèi)存中的一個(gè)區(qū)域，里面可以緩存磁盤上經(jīng)常操作的真實(shí)數(shù)據(jù)，在執(zhí)行增刪改查操作時(shí)，先操作緩沖池中的數(shù)據(jù)(若緩沖池么有數(shù)據(jù)，則從磁盤加載并緩存)，然后再以一定頻率刷新磁盤，從而減少磁盤IO，加快處理速度。

緩沖池以page頁(yè)為單位，底層采用鏈表數(shù)據(jù)結(jié)構(gòu)管理page，根據(jù)狀態(tài)，將page分為三種類型:

1、free page 即空閑page，未被使用。

2、clean page 被使用page，數(shù)據(jù)沒(méi)有被修改過(guò)。

3、dirty page 臟頁(yè)，被使用page，數(shù)據(jù)被修改過(guò)，這個(gè)page當(dāng)中的數(shù)據(jù)和磁盤當(dāng)中的數(shù)據(jù) 不一致。說(shuō)得簡(jiǎn)單點(diǎn)就是緩沖池中的數(shù)據(jù)改了，磁盤中的沒(méi)改，因?yàn)檫€沒(méi)刷寫到磁盤。

Change Buffer ：更改緩沖區(qū)(針對(duì)于非唯一二級(jí)索引頁(yè))，在執(zhí)行DML語(yǔ)句時(shí)，如果這些數(shù)據(jù)page沒(méi)有在Buffer Pool中，不會(huì)直接操作磁盤，而會(huì)將數(shù)據(jù)變更存在更改緩沖區(qū)Change Buffer中，在未來(lái)數(shù)據(jù)被讀取時(shí)。再將數(shù)據(jù)合并恢復(fù)到Buffer Pool中，再將合并后的數(shù)據(jù)刷新到磁盤中。

二級(jí)索引通常是非唯一的，并且以相對(duì)隨機(jī)的順序插入二級(jí)索引頁(yè)，同樣，刪除和更新可能會(huì)影響索引樹(shù)中不相鄰的二級(jí)索引頁(yè)。如果每一次都操作磁盤，會(huì)造成大量磁盤IO，有了Change Buffer之后，我們可以在緩沖池中進(jìn)行合并處理，減少磁盤IO。

Adaptive Hash Index： 自適應(yīng)hash索引，用于優(yōu)化對(duì)Buffer Pool數(shù)據(jù)的查詢，InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上各索引頁(yè)的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自適應(yīng)hash索引。無(wú)需人工干預(yù)，系統(tǒng)根據(jù)情況自動(dòng)完成。

參數(shù)：innodb_adaptive_hash_index

Log Buffer: 日志緩沖區(qū)，用來(lái)保存要寫入到磁盤中的log日志數(shù)據(jù)(redo log、undo log)，默認(rèn)大小為16M，日志緩沖區(qū)的日志會(huì)定期刷新到磁盤中，如果需要更新，插入或刪除許多行的事務(wù)，增加日志緩沖區(qū)的大小可以節(jié)省磁盤IO。

參數(shù): innodb_log_buffer_size 緩沖區(qū)大小

innodb_flush_log_at_trx_commit 日志刷新到磁盤時(shí)機(jī)

innodb_flush_log_at_trx_commit=1 表示日志在每次事務(wù)提交時(shí)寫入并刷新到磁盤

2 表示日志在每次事務(wù)提交后寫入，并每秒刷新到磁盤一次

0 表示每秒將日志寫入并刷新到磁盤一次。

InnoDB 的磁盤結(jié)構(gòu)，由系統(tǒng)表空間(ibdata1)，獨(dú)立表空間(*.ibd),通用表空間，撤銷表空間(undo tablespaces), 臨時(shí)表空間(Temporary Tablespaces), 雙寫緩沖區(qū)(Doublewrite Buffer files), 重做日志(Redo Log).

系統(tǒng)表空間(ibdata1)： 系統(tǒng)表空間是更改緩沖區(qū)的存儲(chǔ)區(qū)域，如果表是在系統(tǒng)表空間而不是每個(gè)表文件或者通用表空間中創(chuàng)建的，它也可能包含表和索引數(shù)據(jù)。

參數(shù)為: innodb_data_file_path

獨(dú)立表空間(*.ibd): 每個(gè)表的文件表空間包含單個(gè)innodb表的數(shù)據(jù)和索引，并存儲(chǔ)在文件系 統(tǒng)上的單個(gè)數(shù)據(jù)文件中。 參數(shù): innodb_file_per_table

通用表空間: 需要通過(guò)create tablespace 語(yǔ)法創(chuàng)建，創(chuàng)建表時(shí) 可以指定該表空間。

create tablespace xxx add datafile 'file_name' engine=engine_name

create table table_name .... tablespace xxx

撤銷表空間(undo tablespaces): MySQL實(shí)例在初始化時(shí)會(huì)自動(dòng)創(chuàng)建兩個(gè)默認(rèn)的undo表空間(初始大小16K，undo_001,undo_002)，用于存儲(chǔ)undo log 日志

臨時(shí)表空間(Temporary Tablespaces)： innodb使用會(huì)話臨時(shí)表空和全局表空間，存儲(chǔ)用 戶創(chuàng)建的臨時(shí)表等數(shù)據(jù)。

雙寫緩沖區(qū)(Doublewrite Buffer files)： innodb引擎將數(shù)據(jù)頁(yè)從Buffer Pool刷新到磁盤前，先將數(shù)據(jù)頁(yè)寫入緩沖區(qū)文件中，便于系統(tǒng)異常時(shí)恢復(fù)數(shù)據(jù)。

重做日志(Redo Log)： 是用來(lái)實(shí)現(xiàn)事務(wù)的持久性，該日志文件由兩部分組成，重做日志緩沖區(qū)(redo log buffer)以及重做日志文件(redo log)，前者是在內(nèi)存中，后者在磁盤中，當(dāng)事務(wù)提交之后會(huì)把修改信息都會(huì)存儲(chǔ)到該日志中，用于在刷新臟頁(yè)到磁盤時(shí)，發(fā)送錯(cuò)誤時(shí)，進(jìn)行數(shù)據(jù)恢復(fù)使用。以循環(huán)方式寫入重做日志文件，涉及兩個(gè)文件ib_logfile0,ib_logfile1。

那內(nèi)存結(jié)構(gòu)中的數(shù)據(jù)是如何刷新到磁盤中的? 在MySQL中有4個(gè)線程負(fù)責(zé)刷新日志到磁盤。

1、Master Thread， mysql核心后臺(tái)線程，負(fù)責(zé)調(diào)度其它線程，還負(fù)責(zé)將緩沖池中的數(shù)據(jù)異 步刷新到磁盤中，保持?jǐn)?shù)據(jù)的一致性，還包括臟頁(yè)的刷新，合并插入緩沖、undo頁(yè)的回 收。

2、IO Thread，在innodb存儲(chǔ)引擎中大量使用了AIO來(lái)處理IO請(qǐng)求，這樣可以極大地提高數(shù) 據(jù)庫(kù)的性能，而IO Thead主要負(fù)責(zé)這些IO請(qǐng)求的回調(diào)。

4個(gè)讀線程 Read thread負(fù)責(zé)讀操作

4個(gè)寫線程write thread負(fù)責(zé)寫操作

1個(gè)Log thread線程 負(fù)責(zé)將日志緩沖區(qū)刷新到磁盤

1個(gè)insert buffer線程 負(fù)責(zé)將寫入緩沖區(qū)內(nèi)容刷新到磁盤

3、Purge Thread，主要用于回收事務(wù)已經(jīng)提交了的undo log，在事務(wù)提交之后，undo log 可能不用了，就用它來(lái)回收。

4、Page Cleaner Thread， 協(xié)助Master Thread 刷新臟頁(yè)到磁盤的線程，它可以減輕主線程 的壓力，減少阻塞。

事務(wù)就是一組操作的集合，它是一個(gè)不可分割的工作單位，事務(wù)會(huì)把所有的操作作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求，即這些操作要么同時(shí)成功，要么同時(shí)失效。

事務(wù)的4大特性分為:

如何保證事務(wù)的4大特性，原子性，一致性和持久性是由innodb存儲(chǔ)引擎底層的兩份日志來(lái)保證的，分別是redo log和undo log。對(duì)于隔離性是由鎖機(jī)制和MVCC(多版本并發(fā)控制)來(lái)實(shí)現(xiàn)的。

redo log，稱為重做日志，記錄的是事務(wù)提交時(shí)數(shù)據(jù)頁(yè)的物理修改，是用來(lái)實(shí)現(xiàn)事務(wù)的持久性。該日志文件由兩部分組成: 重做日志緩沖redo log buffer及重做日志文件redo log file，前者是在內(nèi)存中，后者是在磁盤中，當(dāng)事務(wù)提交之后會(huì)把所有修改信息都存到該日志文件中，用于在刷新臟頁(yè)到磁盤，發(fā)送錯(cuò)誤時(shí)，進(jìn)行數(shù)據(jù)的恢復(fù)使用，從而保證事務(wù)的持久性。

具體的操作流程是：

1、客戶端發(fā)起事務(wù)操作，包含多條DML語(yǔ)句。首先去innodb中的buffer pool中的數(shù)據(jù)頁(yè)去查找有沒(méi)有我們要更新的這些數(shù)據(jù)，如果沒(méi)有則通過(guò)后臺(tái)線程從磁盤中加載到buffer pool對(duì)應(yīng)的數(shù)據(jù)頁(yè)中，然后就可以在緩沖池中進(jìn)行數(shù)據(jù)操作了。

2、此時(shí)緩沖池中的數(shù)據(jù)頁(yè)發(fā)生了變更，還沒(méi)刷寫到磁盤，這個(gè)數(shù)據(jù)頁(yè)稱為臟頁(yè)。臟頁(yè)不是實(shí)時(shí)刷新到磁盤的，而是根據(jù)你配置的刷寫策略進(jìn)行刷寫到磁盤的（innodb_flush_log_at_trx_commit，0,1,2三個(gè)值）。如果臟頁(yè)在往磁盤刷新的時(shí)候出現(xiàn)了故障，會(huì)丟失數(shù)據(jù)，導(dǎo)致事務(wù)的持久性得不到保證。為了避免這種現(xiàn)象，當(dāng)對(duì)緩沖池中的數(shù)據(jù)進(jìn)行增刪改操作時(shí)，會(huì)把增刪改記錄到redo log buffer當(dāng)中，redo log buffer會(huì)把數(shù)據(jù)頁(yè)的物理變更持久化到磁盤文件中(ib_logfile0/ib_logfile1)。如果臟頁(yè)刷新失敗，就可以通過(guò)這兩個(gè)日志文件進(jìn)行恢復(fù)。

undo log，它是用來(lái)解決事務(wù)的原子性的，也稱為回滾日志。用于記錄數(shù)據(jù)被修改前的信息，作用包括:提供回滾和MVCC多版本并發(fā)控制。

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記錄，當(dāng)執(zhí)行rollback時(shí)，就可以從undo log中的邏輯記錄讀取到相應(yīng)的內(nèi)容并進(jìn)行回滾。

undo log銷毀: undo log 在事務(wù)執(zhí)行時(shí)產(chǎn)生，事務(wù)提交時(shí)，并不會(huì)立即刪除undo log，因?yàn)檫@些日子可能用于MVCC。

undo log存儲(chǔ): undo log 采用段的方式進(jìn)行管理和記錄，存放在前面介紹的rollback segment回滾段中，內(nèi)部包含1024個(gè)undo log segment。

mvcc(multi-Version Concurrency Control),多版本并發(fā)控制，指維護(hù)一個(gè)數(shù)據(jù)的多個(gè)版本，使得讀寫操作沒(méi)有沖突，快照讀為MySQL實(shí)現(xiàn)MVCC提供了一個(gè)非阻塞讀功能，MVCC的具體實(shí)現(xiàn)，還需要依賴于數(shù)據(jù)庫(kù)記錄中的三個(gè)隱式字段，undo log日志、readView。

read committed 每次select 都生成一個(gè)快照讀

repeatable read 開(kāi)啟事務(wù)后第一個(gè)select語(yǔ)句才是快照讀的地方

serializable 快照讀會(huì)退化為當(dāng)前讀。

mvcc的實(shí)現(xiàn)原理

DB_TRX_ID: 最近修改事務(wù)ID，記錄插入這條記錄或最后一次修改該記錄的事務(wù)ID

DB_ROLL_PTR: 回滾指針，指向這條記錄的上一個(gè)版本，用于配合undo log，指向上一個(gè) 版本

DB_ROW_ID: 隱藏主鍵，如果表結(jié)構(gòu)沒(méi)有指定主鍵，將會(huì)生成該隱藏字段。

m_ids當(dāng)前活躍的事務(wù)ID集合

min_trx_id: 最小活躍事務(wù)id

max_trx_id: 預(yù)分配事務(wù)ID，當(dāng)前最大事務(wù)id+1，因?yàn)槭聞?wù)id是自增的

creator_trx_id: ReadView創(chuàng)建者的事務(wù)ID

版本鏈數(shù)據(jù)訪問(wèn)規(guī)則:

trx_id: 表示當(dāng)前的事務(wù)ID

1、trx_id == creator_trx_id? 可以訪問(wèn)讀版本--成立的話,說(shuō)明數(shù)據(jù)是當(dāng)前這個(gè)事務(wù)更改的

2、trx_id 成立,說(shuō)明數(shù)據(jù)已經(jīng)提交了。

3、trx_idmax_trx_id？不可用訪問(wèn)讀版本- 成立的話，說(shuō)明該事務(wù)是在ReadView生成后才開(kāi)啟的。

4、min_trx_id

網(wǎng)頁(yè)名稱：mysql怎么深入 mysql進(jìn)去
標(biāo)題路徑：http://www.chinadenli.net/article48/dosiphp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、做網(wǎng)站、、企業(yè)建站、網(wǎng)站建設(shè)、定制開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)