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

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

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

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

4、A賬戶和B賬戶里的余額變化和操作日志記錄是可以對應(yīng)起來的；獨立性表示當匯款操作過程中如果有C賬戶也在往B賬戶里匯款的話， 兩個事務(wù)相互不影響，即A-B有四個獨立操作，C-B有四個獨立操作； 持久性表示當匯款成功時，A和B的余額就變更了，不管是數(shù)據(jù)庫重啟還是什么原因，該數(shù)據(jù)已經(jīng)寫入到磁盤中作為永久存儲，不會再變化，除非有新的事 務(wù)其中事務(wù)的隔離性是通過MySQL鎖機制實現(xiàn) 原子性，一致性，持久性則通過MySQL的redo和undo日志記錄來完成 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會將修改前的數(shù)據(jù)存放在回滾段中。 InnoDB會在數(shù)據(jù)庫的每一行上額外增加三個字段以實現(xiàn)多版本控制，第一 個字段是DB_TRX_ID用來存放針對該行最后一次

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

10、ID和行數(shù)據(jù)中的事務(wù)ID做對比，并按照事務(wù)隔離級別選取事務(wù)1 修改前的回滾段中的數(shù)據(jù)返回 01老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB和ACID模型在兩個數(shù)據(jù)庫鏈接下實驗多版本控制鏈接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存儲引擎介紹在MySQL實例中執(zhí)行show engines命令查看存儲引擎情況Support=YES代表當前支持的存儲引擎，DEFAULT代表默認存儲引擎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ù)存儲格式老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)特點：根據(jù)主鍵尋址速度很快主鍵值遞增的insert插入效率較好主鍵值隨機insert插入操作效率差老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu) 緩存池buffer pool緩存池是InnoDB在內(nèi)存中開辟的用來緩存表數(shù)據(jù)和索引數(shù)據(jù)的區(qū)域，一般 可以設(shè)置為50%80%的物理內(nèi)存大小，通過對經(jīng)常訪問的數(shù)據(jù)放置到內(nèi)存當中來加快訪問速度。 Buffer pool以page頁的格式組成，頁之間組成list列表，并通過LRU算法（最近最少

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

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

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

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

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

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

22、此這個過程是順序?qū)懙?，開銷并不是很大。在完成doublewrite頁 的寫入后，再將doublewrite buffer 中的頁寫入各個表空間文件中，此時的寫入則是離散的。如果，在恢復(fù)過程中，innodb可以從共享表空間中的 操作系統(tǒng)在將頁寫入磁盤的過程中發(fā)生了doublewrite中找到該頁的一個副本，將其復(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)原始信息，所以當其它的事務(wù)需要查看修

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

24、g的原理很簡單，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先 將數(shù)據(jù)備份到一個地方 （這個存儲數(shù)據(jù)備份的地方稱為Undo Log）。然后進行數(shù)據(jù)的修改。如果出現(xiàn)了錯誤或者用戶執(zhí)行了 ROLLBACK語句，系統(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ù)表不是共享一個系統(tǒng)表空間，而是每個表一個獨立的表空間?？梢酝ㄟ^設(shè)置innodb_file_per_table開啟此屬性。開啟之后每個表數(shù)據(jù)和索引數(shù)據(jù)都會默認單獨存放在數(shù)據(jù)文件夾

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

26、BA手動去刪除表空間文件或重啟服務(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ù)據(jù)文件比較大，可以考慮重啟MySQL來釋放空間大小。老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)redo logredo日志是存在于磁盤上的文件，包括ib_logfile0和ib_logfile1兩個文件，常 用于在crash

27、恢復(fù)發(fā)生時將還沒來得及寫入到數(shù)據(jù)文件中但已經(jīng)完成提交的事務(wù)在 InnoDB對redologbuffer寫入到redolog文件的數(shù)據(jù)庫初始化時重新執(zhí)行一遍。方式提供了組提交(group commit)的方式，意味著針對一次寫磁盤操作可以包含多個事務(wù)數(shù)據(jù)，用此方法提 高性能。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB體系結(jié)構(gòu)為了IO效率，數(shù)據(jù)庫修改的文件都在內(nèi)存緩存中完成的；那么我們知道一旦斷電 ，內(nèi)存中的數(shù)據(jù)將消失，而數(shù)據(jù)庫是如何保證數(shù)據(jù)的完整性？那就是數(shù)據(jù)持久化與事務(wù)日志 如果宕機了則：應(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：每秒寫入并持久化一次（不安全，性能高，無論mysql或服務(wù)器宕機，都會 丟數(shù)據(jù)最多1秒的數(shù)據(jù)） 1：每次commit都持久化（安全，性能低，IO負擔重） 2：每次commit都寫入內(nèi)存的內(nèi)存緩存，每秒再刷新到磁盤（安全，性能折中 ，mysql宕機數(shù)據(jù)不會丟失，服務(wù)器宕機數(shù)據(jù)會丟失最多1秒的數(shù)據(jù)） innodb_flush_log_at_timeout參數(shù)決定最多丟失多少秒的數(shù)據(jù)，默認是1秒老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存

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

30、file_path=datafile_spec1;datafile_spec2. datafile_spec1= file_name:file_size:autoextend:max:max_file_size其中autoextend和max選項只能用作最后的這個數(shù)據(jù)文件。Autoextend默認情況下是一次增加64MB，如果要修改此值可通過innodb_autoextend_increment參數(shù)。Max用來指定可擴展數(shù)據(jù)文件的最大容量用來避免數(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存儲引擎配置舉例如下： mysqld innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 表示指定ibdata1和ibdata2兩個數(shù)據(jù)文件，其中ibdata1文件為固定的50M大小，而ibdata2文件初 始化為50M并可自動擴展容量 mysqld innodb_data_file_path=ibdata1:

32、12M:autoextend:max:500M innodb_data_home_dir參數(shù)用來顯示指定數(shù)據(jù)文件的存儲目錄，默認是MySQL安裝后的數(shù)據(jù)文件目 錄，舉例如下： mysqld innodb_data_home_dir = /path/to/myibdata/ innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 當然也可以在innodb_data_file_path中指定絕對路徑的數(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存儲引擎配置日志文件配置默認情況下InnoDB會在數(shù)據(jù)文件夾下創(chuàng)建兩個48M的日志文件，分別是ib_logfile0和ib_logfile1。 Innodb_log_group_home_dir參數(shù)用來定義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ù)用來定義日志文件的個數(shù)，默認和推薦值都是2 innodb_log_file_size參數(shù)定義了每個日志文件的大小，日志文件越大意味著buffer pool進行文件間切換的操作越少，從而減少IO，一般至少要保證在高峰期的1小時內(nèi)的所有日志都能存放在一個日 志文件里而不發(fā)生切換，當然文件大小也有最大限制，就是所有日志文件的總大小不能超過512G。 老男孩IT教育，只培養(yǎng)技術(shù)

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

36、me | Value |+ + +|innodb_undo_directory innodb_undo_log_truncate innodb_undo_logs innodb_undo_tablespaces |./ OFF 1280 |臨時表空間配置 默認情況下，innodb會創(chuàng)建一個自增長的ibtmp1文件在數(shù)據(jù)文件夾為臨時表空間數(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存儲引擎配置數(shù)據(jù)頁配置Innodb_page_size參數(shù)用來指定所有innodb表空間的數(shù)據(jù)頁大小。默認是16K大小，也可以設(shè) 置為64K、32K、8K和4K。一般設(shè)置為存儲磁盤的block size接近的大小.內(nèi)存相關(guān)配置 Innodb_buffer_pool_size參數(shù)確定了緩存表數(shù)據(jù)和索引數(shù)據(jù)的內(nèi)存區(qū)域大小，默認為128M， 推薦設(shè)置為系統(tǒng)內(nèi)存的50%80%。 在服務(wù)器有大量內(nèi)存的情況下，也可以設(shè)置多個緩存以提高系統(tǒng)并發(fā)度。Innodb_

38、buffer_pool_instances參數(shù)就是用來做這個設(shè)置。 Innodb_log_buffer_size參數(shù)確定了redo log緩存的大小，默認值是16M，其大小取決于是否 有某些大的事務(wù)會大量修改數(shù)據(jù)而導(dǎo)致在事務(wù)執(zhí)行過程中就要寫日志文件。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB存儲引擎配置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)存中用來緩存數(shù)據(jù)和索引的存儲區(qū)域，其是MySQL性能調(diào)優(yōu)的重要一環(huán)。 理想情況下，設(shè)置的size越大，則緩存到內(nèi)存的數(shù)據(jù)越多，InnoDB就越像是內(nèi)存數(shù)據(jù)庫。Buffer pool的底層是一個列表，通過LRU算法進行數(shù)據(jù)頁的換進換出操作。當空間原因?qū)е滦马摰募尤胄枰獡Q出一頁時，InnoDB取出最近最少使用的頁并將這個新的數(shù)據(jù)頁加入到列表 的中央。從方向上看，列表的頭部是最常使用的數(shù)據(jù)頁，而在尾部則是最少使用的數(shù)據(jù)頁。Buffer pool中3

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

41、nk_size*innodb_buffer_pool_instances的整數(shù)倍，如果不是，則 buffershellpool會被調(diào)整成大于設(shè)定值且最接近的一個值，如下例： 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為單位，并只能在啟動前 修改，修改后的值*innodb_buffer_pool_instances不能大于buffer pool的大小，否則修改無效。 老男孩IT教育，只培養(yǎng)技術(shù)精英 InnoDB buffer pool配置mysqld innodb_buffer_pool_chunk_size=134217728buffer pool的大小可以動態(tài)修改，用set語句直接修改，當語句發(fā)起時，會一直等到當前 pool就所有的事務(wù)結(jié)束后才執(zhí)行，而一旦執(zhí)行則執(zhí)行過程中的其他事務(wù)如果要訪問buffer 會等待語句執(zhí)行完畢。 mysqlSETGLOBALinno

43、db_buffer_pool_size=402653184; log或者innodb_buffer_pool_resize_status 當執(zhí)行online的調(diào)整大小時，可以通過error 查看進度 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、置配置多個buffer pool實例當bufferpool的大小是GB級別時，將一個bufferpool分割成幾個獨立的實例能降低多個線程同時讀寫緩存頁的競爭性而提高并發(fā)性。通過innodb_buffer_pool_instances參數(shù)可以調(diào) 整實例個數(shù)。如果有多個實例，則緩存的數(shù)據(jù)頁會隨機放置到任意的實例中，且每個實例都有獨立的buffer pool所有的特性。 Innodb_buffer_pool_instances的默認值是1，最大可以調(diào)整成64。 Making the Buffer Pool Scan Resistant新讀取的數(shù)據(jù)頁入到bufferpool的LRU列表的中間位置，默

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

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

47、的數(shù)據(jù)頁來預(yù)測哪些頁會被隨后訪問到 。如果13個連續(xù)的處于相同區(qū)的頁存在于bufferpool中，則InnoDB會把同一個區(qū)的其它頁都讀取進來。通過設(shè)置innodb_random_read_ahead=ON來開啟此方式。 通過執(zhí)行showengineinnodbstatus命令顯示的三個參數(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會在將buffer pool中的臟頁(已經(jīng)修改但沒有寫到數(shù)據(jù)文件)flush掉。當 bufferpool中的臟頁所占百分比達到innodb_max_dirty_pages_pct_lvm會觸發(fā)flush，當 所占比例達到innodb_max_dirty_pages_pct時，則innodb會“ 強烈”的flush。針對數(shù)據(jù)修改操作頻繁的系統(tǒng)，flush可能會嚴重滯后導(dǎo)致有大量的buffer pool內(nèi)存占用，有一些參數(shù)專門針對修改繁忙的系統(tǒng)可以調(diào)整：Innodb_adaptive_flushing_lwm：為防止red

49、olog被填滿，此參數(shù)設(shè)置一個閾值，如果 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ù)來確保在mysql正常重啟時部分經(jīng)常 使用的數(shù)據(jù)頁能直接加載到bufferpool中，通過批量加載的方式，以節(jié)省重啟mysql

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

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