課堂講義第八課innodb內(nèi)核

1、InnoDB內(nèi)核老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎介紹MySQL從5.5版本開始將InnoDB作為默認(rèn)存儲(chǔ)引擎，該存儲(chǔ)引擎是第一個(gè)完 整支持事務(wù)ACID特性的存儲(chǔ)引擎，且支持?jǐn)?shù)據(jù)行鎖，多版本并發(fā)控制（ MVCC），外鍵，以及一致性非鎖定讀。 作為默認(rèn)存儲(chǔ)引擎，也就意味著默認(rèn)創(chuàng)建的表都會(huì)使用此存儲(chǔ)引擎，除非使用ENGINE=參數(shù)指定創(chuàng)建其他存儲(chǔ)引擎的表。 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎介紹InnoDB的關(guān)鍵屬性包括：ACID事務(wù)特性支持，包括commit，rollback以及crash恢復(fù)的能力 行級(jí)別鎖以及多版本并發(fā)控制MVCC 利用主鍵的聚簇索引(

2、clusteredindex)在底層存儲(chǔ)數(shù)據(jù)，以提升對(duì)主鍵查詢的IO性能支持外鍵功能，管理數(shù)據(jù)的完整性 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB和ACID模型ACID模型是關(guān)系型數(shù)據(jù)庫(kù)普遍支持的事務(wù)模型，用來(lái)保證數(shù)據(jù)的一致性，其中 的ACID分別代表： A：atomicity原子性：事務(wù)是一個(gè)不可再分割的工作單位，事務(wù)中的操 作要么都發(fā)生，要么都不發(fā)生C：consistency一致性：事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整 性約束沒有被破壞。這是說(shuō)數(shù)據(jù)庫(kù)事務(wù)不能破壞關(guān)系數(shù)據(jù)的完整性以 及業(yè)務(wù)邏輯上的一致性I：isolation獨(dú)立性：多個(gè)事務(wù)并發(fā)訪問務(wù)之間是隔離的，一個(gè) 事務(wù)不應(yīng)該影

3、響其它事務(wù)運(yùn)行效果D：durability持續(xù)性：在事務(wù)完成以后，該事務(wù)所對(duì)數(shù)據(jù)庫(kù)所作的更改 便持久的保存在數(shù)據(jù)庫(kù)之中，并不會(huì)被回滾 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB和ACID模型舉例來(lái)說(shuō)，比如銀行的匯款1000元的操作，簡(jiǎn)單可以拆分成A賬戶的余額- 1000，B賬戶的余額+1000，還要分別在A和B的賬戶流水上記錄余額變更日 志，這四個(gè)操作必須放在一個(gè)事務(wù)中完成，否則丟失其中的任何一條記錄對(duì)整個(gè)系統(tǒng)來(lái)說(shuō)都是不完整的。 對(duì)上述例子來(lái)說(shuō)，原子性體現(xiàn)在要么四條操作每個(gè)都成功，意味著匯款成功， 要么其中某一個(gè)操作失敗，則整個(gè)事務(wù)中的四條操作都回滾，匯款失?。灰恢?性表示當(dāng)匯款結(jié)束時(shí)，

4、A賬戶和B賬戶里的余額變化和操作日志記錄是可以對(duì)應(yīng)起來(lái)的；獨(dú)立性表示當(dāng)匯款操作過程中如果有C賬戶也在往B賬戶里匯款的話， 兩個(gè)事務(wù)相互不影響，即A-B有四個(gè)獨(dú)立操作，C-B有四個(gè)獨(dú)立操作； 持久性表示當(dāng)匯款成功時(shí)，A和B的余額就變更了，不管是數(shù)據(jù)庫(kù)重啟還是什么原因，該數(shù)據(jù)已經(jīng)寫入到磁盤中作為永久存儲(chǔ)，不會(huì)再變化，除非有新的事 務(wù)其中事務(wù)的隔離性是通過MySQL鎖機(jī)制實(shí)現(xiàn) 原子性，一致性，持久性則通過MySQL的redo和undo日志記錄來(lái)完成 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB和ACID模型mysql select * from score where sid=1;+ + + +

5、| sid | course_id | score |+ + + +|1 |1 |1 |1 |1 |2 |3 |4 |78 |69 |80 |63 |+ + + + 9 rows in set (0.00 sec)mysql update score set score=90 where sid=1;mysql select * from score where sid=1;+ + + +| sid | course_id | score |+ + + +|1 |1 |1 |1 |1 |2 |3 |4 |90 |90 |90 |90 |mysql rollback;mysql select *

6、 from score where sid=1;+ + + +| sid | course_id | score |+ + + +#rollbck不起作用|1 |1 |1 |1 |1 |2 |3 |4 |90 |90 |90 |90 |原因是： mysql show variables like %autocommit%;+ + +| Variable_name | Value |+ + +| autocommit| ON|01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB和ACID模型mysql start transaction;Query OK, 0 rows affected (0.00

7、 sec)mysql update score set score=85 where sid=1; Query OK, 4 rows affected (0.00 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql select * from score where sid=1;+ + + +| sid | course_id | score |+ + + +|1 |1 |1 |1 |1 |2 |3 |4 |85 |85 |85 |85 |+ + + + 4 rows in set (0.00 sec)mysql rollback;Query OK,

8、 0 rows affected (0.00 sec)mysql select * from score where sid=1;+ + + +| sid | course_id | score |+ + + +#事務(wù)執(zhí)行最后rollback，回滾數(shù)據(jù)|1 |1 |1 |1 |1 |2 |3 |4 |90 |90 |90 |90 |+ + + +01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB多版本控制為保證并發(fā)操作和回滾操作，InnoDB會(huì)將修改前的數(shù)據(jù)存放在回滾段中。 InnoDB會(huì)在數(shù)據(jù)庫(kù)的每一行上額外增加三個(gè)字段以實(shí)現(xiàn)多版本控制，第一 個(gè)字段是DB_TRX_ID用來(lái)存放針對(duì)該行最后一次

9、執(zhí)行insert、update操作的事務(wù)ID，而delete操作也會(huì)被認(rèn)為是update，只是會(huì)有額外的一位來(lái)代 表事務(wù)為刪除操作；第二個(gè)字段是DB_ROLL_PTR指針指向回滾段里對(duì)應(yīng)的undo日志記錄；第三個(gè)字段是DB_ROW_ID代表每一行的行ID。 回滾段中的undo日志記錄只有在事務(wù)commit提交之后才會(huì)被丟棄，為避免回滾段越來(lái)越大，要注意及時(shí)執(zhí)行commit命令 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB多版本控制初始數(shù)據(jù)行的情況，六個(gè)字段的值分別是1,2,3,4,5,6事務(wù)1修改該數(shù)據(jù)行，將六個(gè)字段的值分別*10，并生成回滾日志記錄事務(wù)2讀取該數(shù)據(jù)行 事務(wù)2按照自己的事務(wù)

10、ID和行數(shù)據(jù)中的事務(wù)ID做對(duì)比，并按照事務(wù)隔離級(jí)別選取事務(wù)1 修改前的回滾段中的數(shù)據(jù)返回 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB和ACID模型在兩個(gè)數(shù)據(jù)庫(kù)鏈接下實(shí)驗(yàn)多版本控制鏈接1：mysql start transaction;鏈接2：mysql start transaction;鏈接1 ：mysql update score set score=88 where sid=1;鏈接2：mysql select * from score where sid=1;+ + -+ -+| sid | course_id | score |+ + -+ -+#鏈接1鎖數(shù)據(jù)未釋放，鏈接2也能

11、訪問相同數(shù)據(jù) |1 |1 |1 |1 |1 |2 |3 |4 |90 |90 |90 |90 |鏈接1： mysqlcommit;鏈接2：mysql select * from score where sid=1;+ + -+ -+| sid | course_id | score |+ + -+ -+#鏈接1鎖釋放，但鏈接2訪問到的數(shù)據(jù)依然是之前的數(shù)據(jù) |1 |1 |1 |1 |1 |2 |3 |4 |90 |90 |90 |90 |鏈接2：mysql commit;鏈接2：mysql select * from score where sid=1;+ + -+ -+| sid | cour

12、se_id | score |+ + -+ -+#鏈接2提交之后，再訪問到的數(shù)據(jù)是修改后的數(shù)據(jù) |1 |1 |1 |1 |1 |2 |3 |4 |88 |88 |88 |88 |01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎介紹在MySQL實(shí)例中執(zhí)行show engines命令查看存儲(chǔ)引擎情況Support=YES代表當(dāng)前支持的存儲(chǔ)引擎，DEFAULT代表默認(rèn)存儲(chǔ)引擎mysql show engines; + + + + + + +| Support | Comment| Transactions | XA|Engine Savepoints | + + + + + + +MyISA

13、M| YES| MyISAMstorageengine| NO| NO | NO|CSV| YES| CSVstorageengine|NO| NO| NO|YES|Performance Schema| NONO|PERFORMANCE_SCHEMA BLACKHOLE | NO you write to| it| YES| /dev/null storage engine (anythingdisappears) |NO | NO | NOMRG_MYISAMInnoDB |YES| Collection of identical MyISAM| DEFAULT | Supports tr

14、ansactions,tables | NO| NO| NO| | YES | YESrow-levellocking,and foreignkeys|YES|ARCHIVE| YES| Archive storage engine| NO|NO | NO|MEMORY| YES| Hash based, stored in memory,usefulfor temporary tables| NO| NO| NOFEDERATED| NO| Federated MySQL storage engine| NULL| NULL | NULL| + + + + + + +01老男孩IT教育，只培

15、養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)InnoDB表數(shù)據(jù)存儲(chǔ)格式老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)特點(diǎn)：根據(jù)主鍵尋址速度很快主鍵值遞增的insert插入效率較好主鍵值隨機(jī)insert插入操作效率差老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu) 緩存池buffer pool緩存池是InnoDB在內(nèi)存中開辟的用來(lái)緩存表數(shù)據(jù)和索引數(shù)據(jù)的區(qū)域，一般 可以設(shè)置為50%80%的物理內(nèi)存大小，通過對(duì)經(jīng)常訪問的數(shù)據(jù)放置到內(nèi)存當(dāng)中來(lái)加快訪問速度。 Buffer pool以page頁(yè)的格式組成，頁(yè)之間組成list列表，并通過LRU算法（最近最少

16、使用算法）對(duì)長(zhǎng)久不使用的頁(yè)進(jìn)行置換。 數(shù)據(jù)的讀寫需要經(jīng)過緩存（緩存在buffer pool 即在內(nèi)存中） 數(shù)據(jù)以整頁(yè)（16K）位單位讀取到緩存中 緩存中的數(shù)據(jù)以LRU策略換出（最少使用策略） IO效率高，性能好 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)Adaptive Hash Index（自適應(yīng)哈希索引）AdaptiveHashindex屬性使得InnoDB更像是內(nèi)存數(shù)據(jù)庫(kù)。該屬性通過innodb_adapitve_hash_index開啟，也可以通過skip-innodb_adaptive_hash_index參數(shù)關(guān)閉 InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上索引的查找，如果觀察到建立

17、哈希索引可以帶來(lái)速 度的提升，則建立哈希索引，所以稱之為自適應(yīng)（adaptive） 的。自適應(yīng)哈希索引通過緩沖池的B+樹構(gòu)造而來(lái)，因此建立的速度很快。而且不需要將整個(gè)表都建 來(lái)為某些頁(yè)建立哈希索哈希索引，InnoDB存儲(chǔ)引擎會(huì)自動(dòng)根據(jù)訪問的頻率和模式 引。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)自適應(yīng)哈希索引哈希（hash）是一種非?？斓牡戎挡檎曳椒?，在一般情況下這種查找的時(shí)間復(fù)雜度為O(1),即一般僅需要一次查找就能定位數(shù) 據(jù)。 而B+樹的查找次數(shù)，取決于B+樹的高度，在生產(chǎn)環(huán)境中，B+樹的高度一般3-4層，故需要3-4次的查詢。 innodb會(huì)監(jiān)控對(duì)表上個(gè)索引頁(yè)的查詢。如果觀

18、察到建立哈希索引可以帶來(lái)速度提升，則自動(dòng)建立哈希索引，稱之為自適應(yīng)哈希索引（Adaptive Hash Index，AHI）。 AHI有一個(gè)要求，就是對(duì)這個(gè)頁(yè)的連續(xù)訪問模式必須是一樣的。 例如對(duì)于（a,b）訪問模式情況： where a = xxxwhere a = xxx and b = xxxAHI啟動(dòng)后，讀寫速度提高了2倍，輔助索引的連接操作性能可以提高5倍。 AHI，是數(shù)據(jù)庫(kù)自動(dòng)優(yōu)化的，DBA只需要指導(dǎo)開發(fā)人員去盡量使用符合AHI條件的查詢，以提高效率。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu) Redo log bufferRedologbuffer是一塊用來(lái)存放寫入re

19、do log文件內(nèi)容的內(nèi)存區(qū)域，內(nèi)存的大小由log文件中。innodb_log_buffer_size參數(shù)確定。該buffer的內(nèi)容會(huì)定期刷新到磁盤的redo 參數(shù)innodb_flush_log_at_trx_commit決定了刷新到文件的方式，參數(shù)innodb_flush_log_at_timeout參數(shù)決定了刷新的頻率。 系統(tǒng)表空間 InnoDB的系統(tǒng)表空間用來(lái)存放表和索引數(shù)據(jù)，同時(shí)也是doublewriter緩存，change緩存和回 滾日志的存儲(chǔ)空間，系統(tǒng)表空間是被多個(gè)表共享的表空間。 默認(rèn)情況下，系統(tǒng)表空間只有一個(gè)系統(tǒng)數(shù)據(jù)文件，名為ibdata1。系統(tǒng)數(shù)據(jù)文件的位置和個(gè)數(shù)由參數(shù)in

20、nodb_data_file_path參數(shù)決定。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)Doublewrite緩存Doublewrite緩存是位于系統(tǒng)表空間的存儲(chǔ)區(qū)域，用來(lái)緩存InnoDB的數(shù)據(jù)頁(yè)從 innodbbufferpool中flush之后并寫入到數(shù)據(jù)文件之前，所以當(dāng)操作系統(tǒng)或者數(shù)據(jù)庫(kù)進(jìn)程在數(shù)據(jù)頁(yè)寫磁盤的過程中到數(shù)據(jù)頁(yè)的備份而用來(lái)執(zhí)行crash恢復(fù)。 ，Innodb可以在doublewrite緩存中找數(shù)據(jù)頁(yè)寫入到doublewrite緩存的動(dòng)作所需要的IO消耗要小于寫入到數(shù)據(jù)文件的 消耗，因?yàn)榇藢懭氩僮鲿?huì)以一次大的連續(xù)塊的方式寫入。 老男孩IT教育，只培養(yǎng)技術(shù)精英 Inn

21、oDB體系結(jié)構(gòu)在應(yīng)用（apply）重做日志前，用戶需要一個(gè)頁(yè)的副本，當(dāng)寫入失效發(fā)生時(shí)，先通過頁(yè)的副本來(lái)還原該頁(yè)，再進(jìn)行重做，這就是double writedoublewrite組成： 內(nèi)存中的doublewrite buffer,大小2M， 物理磁盤上共享表空間中連續(xù)的128個(gè)頁(yè)，即2個(gè)區(qū)（extend），大小同樣為2M。 對(duì)緩沖池的臟頁(yè)進(jìn)行刷新時(shí)，不是直接寫磁盤，而是會(huì)通過memcpy()函數(shù)將臟頁(yè)先復(fù)制到內(nèi)存中的doublewrite buffer， 之后通過doublewrite 再分兩次，每次1M順序地寫入共享表空間的物理磁盤上，在這個(gè)過程中， 因?yàn)閐oublewrite頁(yè)是連續(xù)的，因

22、此這個(gè)過程是順序?qū)懙?，開銷并不是很大。在完成doublewrite頁(yè) 的寫入后，再將doublewrite buffer 中的頁(yè)寫入各個(gè)表空間文件中，此時(shí)的寫入則是離散的。如果，在恢復(fù)過程中，innodb可以從共享表空間中的 操作系統(tǒng)在將頁(yè)寫入磁盤的過程中發(fā)生了doublewrite中找到該頁(yè)的一個(gè)副本，將其復(fù)制到表空間文件，再應(yīng)用重做日志。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)Undo日志Undo日志是由一系列事務(wù)的undo日志記錄組成，每一條undo日志記錄包含了事務(wù) 數(shù)據(jù)回滾的相關(guān)原始信息，所以當(dāng)其它的事務(wù)需要查看修

23、改前的原始數(shù)據(jù)，則會(huì) 從此undo日志記錄中獲取。Undo日志存放在回滾段中的undo日志段中。默認(rèn)情況 下回滾段是作為系統(tǒng)表空間的一部分，但也可以有自己獨(dú)立的undo表空間，通過 設(shè)置innodb_undo_tablespaces和innodb_undo_directory兩個(gè)參數(shù)。 Innodb支持最大128個(gè)回滾段，其中的32個(gè)用來(lái)服務(wù)臨時(shí)表的相關(guān)事務(wù)操作，剩下的96個(gè)服務(wù)非臨時(shí)表，每個(gè)回滾段可以同時(shí)支持1023個(gè)數(shù)據(jù)修改事務(wù)，也就是 總共96K個(gè)數(shù)據(jù)修改事務(wù)。 Innodb_undo_logs參數(shù)用來(lái)設(shè)置回滾段的個(gè)數(shù)。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)Undo Lo

24、g的原理很簡(jiǎn)單，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先 將數(shù)據(jù)備份到一個(gè)地方 （這個(gè)存儲(chǔ)數(shù)據(jù)備份的地方稱為Undo Log）。然后進(jìn)行數(shù)據(jù)的修改。如果出現(xiàn)了錯(cuò)誤或者用戶執(zhí)行了 ROLLBACK語(yǔ)句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之 前的狀態(tài) 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu) File-per-table表空間 File-per-table表空間意味著innodb的數(shù)據(jù)表不是共享一個(gè)系統(tǒng)表空間，而是每個(gè)表一個(gè)獨(dú)立的表空間?？梢酝ㄟ^設(shè)置innodb_file_per_table開啟此屬性。開啟之后每個(gè)表數(shù)據(jù)和索引數(shù)據(jù)都會(huì)默認(rèn)單獨(dú)存放在數(shù)據(jù)文件夾

25、下的.ibd數(shù)據(jù) 文件中。 mysql show variables like %per_table%; + + + | Variable_name+ | Value |+ +| innodb_file_per_table | ON+ + +temporary表空間 temporary臨時(shí)表空間用來(lái)存放臨時(shí)表，默認(rèn)情況下是在數(shù)據(jù)文件夾下的ibtmp1數(shù)據(jù)文件，此數(shù)據(jù)文件被設(shè)置為每次自動(dòng)增長(zhǎng)12MB大小，當(dāng)然也可以設(shè)置innodb_temp_data_file_path來(lái)指定臨時(shí)表空間文件的存放位置。 臨時(shí)表空間文件在正常的shutdown之后會(huì)自動(dòng)清除，但在crash發(fā)生時(shí)不會(huì)清除，這就需要D

26、BA手動(dòng)去刪除表空間文件或重啟服務(wù)器。 mysql show variables like %innodb_temp%; + + |+ | Variable_name+ | Value+| innodb_temp_data_file_path | ibtmp1:12M:autoextend |老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)如果發(fā)現(xiàn)臨時(shí)表空間數(shù)據(jù)文件比較大，可以考慮重啟MySQL來(lái)釋放空間大小。老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)redo logredo日志是存在于磁盤上的文件，包括ib_logfile0和ib_logfile1兩個(gè)文件，常 用于在crash

27、恢復(fù)發(fā)生時(shí)將還沒來(lái)得及寫入到數(shù)據(jù)文件中但已經(jīng)完成提交的事務(wù)在 InnoDB對(duì)redologbuffer寫入到redolog文件的數(shù)據(jù)庫(kù)初始化時(shí)重新執(zhí)行一遍。方式提供了組提交(group commit)的方式，意味著針對(duì)一次寫磁盤操作可以包含多個(gè)事務(wù)數(shù)據(jù)，用此方法提 高性能。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)為了IO效率，數(shù)據(jù)庫(kù)修改的文件都在內(nèi)存緩存中完成的；那么我們知道一旦斷電 ，內(nèi)存中的數(shù)據(jù)將消失，而數(shù)據(jù)庫(kù)是如何保證數(shù)據(jù)的完整性？那就是數(shù)據(jù)持久化與事務(wù)日志 如果宕機(jī)了則：應(yīng)用已經(jīng)持久化好了的日志文件，讀取日志文件中沒有被持久化到數(shù)據(jù)文件里面的記錄；將這些記錄重新持久化到我

28、們的數(shù)據(jù)文件中 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)innodb日志持久化相關(guān)參數(shù)innodb_flush_log_at_trx_commit0：每秒寫入并持久化一次（不安全，性能高，無(wú)論mysql或服務(wù)器宕機(jī)，都會(huì) 丟數(shù)據(jù)最多1秒的數(shù)據(jù)） 1：每次commit都持久化（安全，性能低，IO負(fù)擔(dān)重） 2：每次commit都寫入內(nèi)存的內(nèi)存緩存，每秒再刷新到磁盤（安全，性能折中 ，mysql宕機(jī)數(shù)據(jù)不會(huì)丟失，服務(wù)器宕機(jī)數(shù)據(jù)會(huì)丟失最多1秒的數(shù)據(jù)） innodb_flush_log_at_timeout參數(shù)決定最多丟失多少秒的數(shù)據(jù)，默認(rèn)是1秒老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存

29、儲(chǔ)引擎配置啟動(dòng)配置 InnoDB合理的規(guī)劃方法是在創(chuàng)建數(shù)據(jù)庫(kù)實(shí)例之前就定義好數(shù)據(jù)文件，日志文件和數(shù)據(jù)頁(yè)大小 等相關(guān)屬性 指定配置文件位置 MySQL實(shí)例啟動(dòng)需要依賴f配置文件，而配置文件可以存在于多個(gè)操作系統(tǒng)目錄下f文件的默認(rèn)查找路徑，從上到下找到的文件先讀，但優(yōu)先級(jí)逐級(jí)提升 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎配置系統(tǒng)表空間數(shù)據(jù)文件配置可以通過innodb_data_file_path和innodb_data_home_dir來(lái)配置系統(tǒng)表空間數(shù)據(jù)文件Innodb_data_file_path可以包含一個(gè)或多個(gè)數(shù)據(jù)文件，中間用；號(hào)分開innodb_data_

30、file_path=datafile_spec1;datafile_spec2. datafile_spec1= file_name:file_size:autoextend:max:max_file_size其中autoextend和max選項(xiàng)只能用作最后的這個(gè)數(shù)據(jù)文件。Autoextend默認(rèn)情況下是一次增加64MB，如果要修改此值可通過innodb_autoextend_increment參數(shù)。Max用來(lái)指定可擴(kuò)展數(shù)據(jù)文件的最大容量用來(lái)避免數(shù)據(jù)文件大小超過可用磁盤空間大小。 mysql show variableslike %innodb_data%;+ + +| Variable_na

31、me| Value|+ + +| innodb_data_file_path|innodb_data_home_dir| ibdata1:12M:autoextend| |+ + +老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎配置舉例如下： mysqld innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 表示指定ibdata1和ibdata2兩個(gè)數(shù)據(jù)文件，其中ibdata1文件為固定的50M大小，而ibdata2文件初 始化為50M并可自動(dòng)擴(kuò)展容量 mysqld innodb_data_file_path=ibdata1:

32、12M:autoextend:max:500M innodb_data_home_dir參數(shù)用來(lái)顯示指定數(shù)據(jù)文件的存儲(chǔ)目錄，默認(rèn)是MySQL安裝后的數(shù)據(jù)文件目 錄，舉例如下： mysqld innodb_data_home_dir = /path/to/myibdata/ innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 當(dāng)然也可以在innodb_data_file_path中指定絕對(duì)路徑的數(shù)據(jù)文件mysqld innodb_data_home_dir = innodb_data_file_path=/path/to/myibda

33、ta/ibdata1:50M;/path/to/myibdata/ibdata2:50M:auto extend 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎配置日志文件配置默認(rèn)情況下InnoDB會(huì)在數(shù)據(jù)文件夾下創(chuàng)建兩個(gè)48M的日志文件，分別是ib_logfile0和ib_logfile1。 Innodb_log_group_home_dir參數(shù)用來(lái)定義redo日志的文件位置mysql show variables like %innodb_log_file%; + + +| Variable_name+ | Value|+ +| innodb_log_file_size| 50331

34、648 | innodb_log_files_in_group | 2|+ + mysqld innodb_log_group_home_dir = /dr3/iblogs innodb_log_files_in_group參數(shù)用來(lái)定義日志文件的個(gè)數(shù)，默認(rèn)和推薦值都是2 innodb_log_file_size參數(shù)定義了每個(gè)日志文件的大小，日志文件越大意味著buffer pool進(jìn)行文件間切換的操作越少，從而減少IO，一般至少要保證在高峰期的1小時(shí)內(nèi)的所有日志都能存放在一個(gè)日 志文件里而不發(fā)生切換，當(dāng)然文件大小也有最大限制，就是所有日志文件的總大小不能超過512G。 老男孩IT教育，只培養(yǎng)技術(shù)

35、精英 InnoDB存儲(chǔ)引擎配置 Undo表空間配置 默認(rèn)情況下，undo日志是存放在系統(tǒng)表空間里，但也可以選擇在獨(dú)立的一個(gè)或多個(gè)undo表空間中存放undo日志Innodb_undo_directory參數(shù)決定了獨(dú)立的undo表空間存放目錄Innodb_undo_logs參數(shù)決定了回滾段的個(gè)數(shù)，該變量可以動(dòng)態(tài)調(diào)整 Innodb_undo_tablespaces參數(shù)決定了獨(dú)立undo表空間的個(gè)數(shù)，比如設(shè)置為16時(shí)則會(huì)在undo表空間存放目錄下創(chuàng)建16個(gè)undo文件，默為10M mysql show variables like %innodb_undo%; + + +認(rèn)| Variable_na

36、me | Value |+ + +|innodb_undo_directory innodb_undo_log_truncate innodb_undo_logs innodb_undo_tablespaces |./ OFF 1280 |臨時(shí)表空間配置 默認(rèn)情況下，innodb會(huì)創(chuàng)建一個(gè)自增長(zhǎng)的ibtmp1文件在數(shù)據(jù)文件夾為臨時(shí)表空間數(shù)據(jù)文件。Innodb_temp_data_file_path參數(shù)可以指定文件路徑，文件名和文件大小mysql show variables like %innodb_temp%; + + +| Value| Variable_name + + +| innod

37、b_temp_data_file_path | ibtmp1:12M:autoextend |+ + +老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎配置數(shù)據(jù)頁(yè)配置Innodb_page_size參數(shù)用來(lái)指定所有innodb表空間的數(shù)據(jù)頁(yè)大小。默認(rèn)是16K大小，也可以設(shè) 置為64K、32K、8K和4K。一般設(shè)置為存儲(chǔ)磁盤的block size接近的大小.內(nèi)存相關(guān)配置 Innodb_buffer_pool_size參數(shù)確定了緩存表數(shù)據(jù)和索引數(shù)據(jù)的內(nèi)存區(qū)域大小，默認(rèn)為128M， 推薦設(shè)置為系統(tǒng)內(nèi)存的50%80%。 在服務(wù)器有大量?jī)?nèi)存的情況下，也可以設(shè)置多個(gè)緩存以提高系統(tǒng)并發(fā)度。Innodb_

38、buffer_pool_instances參數(shù)就是用來(lái)做這個(gè)設(shè)置。 Innodb_log_buffer_size參數(shù)確定了redo log緩存的大小，默認(rèn)值是16M，其大小取決于是否 有某些大的事務(wù)會(huì)大量修改數(shù)據(jù)而導(dǎo)致在事務(wù)執(zhí)行過程中就要寫日志文件。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲(chǔ)引擎配置InnoDB只讀設(shè)置InnoDB可以通過innodb-read-only參數(shù)設(shè)置數(shù)據(jù)表只能讀取mysqldinnodb-read-only=1mysql updatetempsetid2=100;ERROR1015(HY000):Cantlockfile(errno:165-Tableisr

39、eadonly)老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置InnoDB buffer pool設(shè)置Buffer pool是內(nèi)存中用來(lái)緩存數(shù)據(jù)和索引的存儲(chǔ)區(qū)域，其是MySQL性能調(diào)優(yōu)的重要一環(huán)。 理想情況下，設(shè)置的size越大，則緩存到內(nèi)存的數(shù)據(jù)越多，InnoDB就越像是內(nèi)存數(shù)據(jù)庫(kù)。Buffer pool的底層是一個(gè)列表，通過LRU算法進(jìn)行數(shù)據(jù)頁(yè)的換進(jìn)換出操作。當(dāng)空間原因?qū)е滦马?yè)的加入需要換出一頁(yè)時(shí)，InnoDB取出最近最少使用的頁(yè)并將這個(gè)新的數(shù)據(jù)頁(yè)加入到列表 的中央。從方向上看，列表的頭部是最常使用的數(shù)據(jù)頁(yè)，而在尾部則是最少使用的數(shù)據(jù)頁(yè)。Buffer pool中3

40、/8的部分是保存最少使用的數(shù)據(jù)頁(yè)，而中央部分其實(shí)是經(jīng)常使用和最少使用的結(jié)合點(diǎn)。當(dāng)在最少使用中保存的數(shù)據(jù)頁(yè)被訪問時(shí)，則數(shù)據(jù)頁(yè)就會(huì)被移動(dòng)到列表的頭部變成 最常使用的。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置配置大小 InnoDB buffer pool的大小可以在啟動(dòng)時(shí)配置，也可以在啟動(dòng)之后配置。 增加和減少buffer pool的大小都是以大塊的方式，塊的大小由參數(shù)innodb_buffer_pool_chunk_size決定，默認(rèn)為128M。 Innodb_buffer_pool_size的大小可以自行設(shè)定，但必須是innodb_buffer_pool_chu

41、nk_size*innodb_buffer_pool_instances的整數(shù)倍，如果不是，則 buffershellpool會(huì)被調(diào)整成大于設(shè)定值且最接近的一個(gè)值，如下例： mysqld -innodb_buffer_pool_size=9G -innodb_buffer_pool_instances=16mysqlSELECT innodb_buffer_pool_size/1024/1024/1024;+ + | innodb_buffer_pool_size/1024/1024/1024+ + |10.000000000000 |+ + Innodb_buffer_pool_chunk_

42、size可以自行設(shè)定，且增加和減少都要以M為單位，并只能在啟動(dòng)前 修改，修改后的值*innodb_buffer_pool_instances不能大于buffer pool的大小，否則修改無(wú)效。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置mysqld innodb_buffer_pool_chunk_size=134217728buffer pool的大小可以動(dòng)態(tài)修改，用set語(yǔ)句直接修改，當(dāng)語(yǔ)句發(fā)起時(shí)，會(huì)一直等到當(dāng)前 pool就所有的事務(wù)結(jié)束后才執(zhí)行，而一旦執(zhí)行則執(zhí)行過程中的其他事務(wù)如果要訪問buffer 會(huì)等待語(yǔ)句執(zhí)行完畢。 mysqlSETGLOBALinno

43、db_buffer_pool_size=402653184; log或者innodb_buffer_pool_resize_status 當(dāng)執(zhí)行online的調(diào)整大小時(shí)，可以通過error 查看進(jìn)度 SHOWSTATUSWHEREVariable_name=InnoDB_buffer_pool_resize_status;+ +mysql+ Variable_name|Value|+ + +Innodb_buffer_pool_resize_status |Resizingalsootherhashtables.|+ + +老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配

44、置配置多個(gè)buffer pool實(shí)例當(dāng)bufferpool的大小是GB級(jí)別時(shí)，將一個(gè)bufferpool分割成幾個(gè)獨(dú)立的實(shí)例能降低多個(gè)線程同時(shí)讀寫緩存頁(yè)的競(jìng)爭(zhēng)性而提高并發(fā)性。通過innodb_buffer_pool_instances參數(shù)可以調(diào) 整實(shí)例個(gè)數(shù)。如果有多個(gè)實(shí)例，則緩存的數(shù)據(jù)頁(yè)會(huì)隨機(jī)放置到任意的實(shí)例中，且每個(gè)實(shí)例都有獨(dú)立的buffer pool所有的特性。 Innodb_buffer_pool_instances的默認(rèn)值是1，最大可以調(diào)整成64。 Making the Buffer Pool Scan Resistant新讀取的數(shù)據(jù)頁(yè)入到bufferpool的LRU列表的中間位置，默

45、認(rèn)位置是從尾部開始算起的 3/8的位置。當(dāng)被放入bufferpool的頁(yè)被第一次訪問時(shí)就開始往列表的前方移動(dòng)，而這樣列 表的后部就是不經(jīng)常訪問的頁(yè)甚至是從不訪問的頁(yè)。 通過參數(shù)innodb_old_blocks_pct可以控制列表中” o l d ” 數(shù)據(jù)頁(yè)所占的百分比，默認(rèn)是37% ，等同于3/8，取值范圍是595。Innodb_old_blocks_time參數(shù)默認(rèn)是1000毫秒，指定了頁(yè)面讀取到buffer pool后但沒有移動(dòng)到經(jīng)常被訪問列表位置的時(shí)間窗口。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置InnoDB buffer pool預(yù)存?。╮ead-a

46、head） Read ahead是異步地預(yù)先獲取多個(gè)數(shù)據(jù)頁(yè)到buffer pool的IO操作，這些數(shù)據(jù)頁(yè)都是假定會(huì)隨后被用到的。InnoDB通過兩種read-ahead算法提高IO性能： 線性readahead：預(yù)測(cè)哪些頁(yè)會(huì)被順序訪問。通過innodb_read_ahead_threshold參 數(shù)調(diào)整順序數(shù)據(jù)頁(yè)的數(shù)量。當(dāng)從一個(gè)區(qū)中順序讀取的頁(yè)數(shù)量大于等于 innodb_read_ahead_threshold時(shí)，innodb會(huì)觸發(fā)異步read ahead操作將真?zhèn)€區(qū)都讀pool中。該參數(shù)的默認(rèn)值是56，取值范圍是064。 到buffer隨機(jī)readahead：通過已經(jīng)在bufferpool中

47、的數(shù)據(jù)頁(yè)來(lái)預(yù)測(cè)哪些頁(yè)會(huì)被隨后訪問到 。如果13個(gè)連續(xù)的處于相同區(qū)的頁(yè)存在于bufferpool中，則InnoDB會(huì)把同一個(gè)區(qū)的其它頁(yè)都讀取進(jìn)來(lái)。通過設(shè)置innodb_random_read_ahead=ON來(lái)開啟此方式。 通過執(zhí)行showengineinnodbstatus命令顯示的三個(gè)參數(shù)判斷read-ahead算法的有 效性：Innodb_buffer_pool_read_ahead Innodb_buffer_pool_read_ahead_evictedInnodb_buffer_pool_read_ahead_rnd 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer poo

48、l配置InnoDB buffer pool flushing配置Innodb會(huì)在將buffer pool中的臟頁(yè)(已經(jīng)修改但沒有寫到數(shù)據(jù)文件)flush掉。當(dāng) bufferpool中的臟頁(yè)所占百分比達(dá)到innodb_max_dirty_pages_pct_lvm會(huì)觸發(fā)flush，當(dāng) 所占比例達(dá)到innodb_max_dirty_pages_pct時(shí)，則innodb會(huì)“ 強(qiáng)烈”的flush。針對(duì)數(shù)據(jù)修改操作頻繁的系統(tǒng)，flush可能會(huì)嚴(yán)重滯后導(dǎo)致有大量的buffer pool內(nèi)存占用，有一些參數(shù)專門針對(duì)修改繁忙的系統(tǒng)可以調(diào)整：Innodb_adaptive_flushing_lwm：為防止red

49、olog被填滿，此參數(shù)設(shè)置一個(gè)閾值，如果 flush操作。 redolog的容量超過此閾值，則執(zhí)行adaptiveInnodb_max_drity_pages_pct_lwm Innodb_io_capacity_max Innodb_flushing_avg_loops 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置重置buffer pool狀態(tài)InnoDB可以通過配置innodb_buffer_pool_dump_at_shutdown參數(shù)來(lái)確保在mysql正常重啟時(shí)部分經(jīng)常 使用的數(shù)據(jù)頁(yè)能直接加載到bufferpool中，通過批量加載的方式，以節(jié)省重啟mysql

50、導(dǎo)致的warmup時(shí) 間（原先在buffer pool中的數(shù)據(jù)頁(yè)要從磁盤再次加載到內(nèi)存中）。 Buffer pool的狀態(tài)可以在任意時(shí)刻被保存，而重置狀態(tài)也可以恢復(fù)任意保存的副本。在數(shù)據(jù)庫(kù)運(yùn)行期間動(dòng)態(tài)配置buffer pool數(shù)據(jù)頁(yè)保留占比的方式是： SET GLOBAL innodb_buffer_pool_dump_pct=40; 而在配置文件中的配置方法為： mysqld innodb_buffer_pool_dump_pct=40 配置當(dāng)服務(wù)器關(guān)閉時(shí)保存buffer pool的當(dāng)前狀態(tài)的方法是： SET GLOBAL innodb_buffer_pool_dump_at_shutdow

51、n=ON; 當(dāng)服務(wù)器開啟時(shí)重新加載buffer pool的方法是： mysqld -innodb_buffer_pool_load_at_startup=ON; 默認(rèn)情況下innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup兩個(gè)配置是開啟狀態(tài) 在關(guān)閉MySQL時(shí)，會(huì)把內(nèi)存中的熱數(shù)據(jù)保存在磁盤里ib_buffer_pool文件中，位于數(shù)據(jù)目錄下。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置數(shù)據(jù)庫(kù)運(yùn)行期間保存和重新加載bufferpool的方法是：SETSETGLOBALGLOBALinno