《MySQL技術内幕：InnoDB存儲引擎(第2版)》書摘

MySQL技術内幕：InnoDB存儲引擎(第2版)

姜承堯

第1章 MySQL體系結構和存儲引擎

>> 在上述例子中使用了mysqld_safe指令來啟動資料庫，當然啟動MySQL執行個體的方法還有很多，在各種平台下的方式可能又會有所不同。

>> 當啟動執行個體時，MySQL資料庫會去讀取配置檔案，根據配置檔案的參數來啟動資料庫執行個體。這與Oracle的參數檔案（spfile）相似，不同的是，Oracle中如果沒有參數檔案，在啟動執行個體時會提示找不到該參數檔案，資料庫啟動失敗。而在MySQL資料庫中，可以沒有配置檔案，在這種情況下，MySQL會按照編譯時的預設參數設定啟動執行個體

>> 從概念上來說，資料庫是檔案的集合，是依照某種資料模型組織起來并存放于二級存儲器中的資料集合；資料庫執行個體是程式，是位于使用者與作業系統之間的一層資料管理軟體，使用者對資料庫資料的任何操作，包括資料庫定義、資料查詢、資料維護、資料庫運作控制等都是在資料庫執行個體下進行的，應用程式隻有通過資料庫執行個體才能和資料庫打交道。

>> 需要特别注意的是，存儲引擎是基于表的，而不是資料庫。

>> 關于NDB存儲引擎，有一個問題值得注意，那就是NDB存儲引擎的連接配接操作（JOIN）是在MySQL資料庫層完成的，而不是在存儲引擎層完成的

>> 相信在任何一本關于資料庫原理的書中，可能都會提到資料庫與傳統檔案系統的最大差別在于資料庫是支援事務的

>> MySQL提供了一個非常好的用來示範MySQL各項功能的示例資料庫，如SQL Server提供的AdventureWorks示例資料庫和Oracle提供的示例資料庫。據我所知，知道MySQL示例資料庫的人很少，可能是因為這個示例資料庫沒有在安裝的時候提示使用者是否安裝（如Oracle和SQL Server）以及這個示例資料庫的下載下傳竟然和文檔放在一起

>> 在Linux和UNIX環境下，還可以使用UNIX域套接字。UNIX域套接字其實不是一個網絡協定，是以隻能在MySQL用戶端和資料庫執行個體在一台伺服器上的情況下使用。使用者可以在配置檔案中指定套接字檔案的路徑，如--socket=/tmp/mysql.sock。當資料庫執行個體啟動後，使用者可以通過下列指令來進行UNIX域套接字檔案的查找：

第2章 InnoDB存儲引擎

>> 從MySQL資料庫的官方手冊可得知，著名的Internet新聞站點Slashdot.org運作在InnoDB上。Mytrix、Inc.在InnoDB上存儲超過1 TB的資料，還有一些其他站點在InnoDB上處理插入/更新操作的速度平均為800次/秒。這些都證明了InnoDB是一個高性能、高可用、高可擴充的存儲引擎。

>> 背景線程的主要作用是負責重新整理記憶體池中的資料，保證緩沖池中的記憶體緩存的是最近的資料。此外将已修改的資料檔案重新整理到磁盤檔案，同時保證在資料庫發生異常的情況下InnoDB能恢複到正常運作狀态。

>> 在InnoDB存儲引擎中大量使用了AIO（Async IO）來處理寫IO請求，這樣可以極大提高資料庫的性能。而IO Thread的工作主要是負責這些IO請求的回調（call back）處理

>> 可以通過指令SHOW ENGINE INNODB STATUS來觀察InnoDB中的IO Thread：

>> 具體來看，緩沖池中緩存的資料頁類型有：索引頁、資料頁、undo頁、插入緩沖（insert buffer）、自适應哈希索引（adaptive hash index）、InnoDB存儲的鎖資訊（lock info）、資料字典資訊（data dictionary）等

>> 從InnoDB 1.0.x版本開始，允許有多個緩沖池執行個體。每個頁根據哈希值平均配置設定到不同緩沖池執行個體中。這樣做的好處是減少資料庫内部的資源競争，增加資料庫的并發處理能力。可以通過參數innodb_buffer_pool_instances來進行配置，該值預設為1。

>> 從MySQL 5.6版本開始，還可以通過information_schema架構下的表INNODB_BUFFER_POOL_STATS來觀察緩沖的狀态

>> 在InnoDB存儲引擎中，緩沖池中頁的大小預設為16KB，同樣使用LRU算法對緩沖池進行管理

>> 。稍有不同的是InnoDB存儲引擎對傳統的LRU算法做了一些優化。在InnoDB的存儲引擎中，LRU清單中還加入了midpoint位置。新讀取到的頁，雖然是最新通路的頁，但并不是直接放入到LRU清單的首部，而是放入到LRU清單的midpoint位置。這個算法在InnoDB存儲引擎下稱為midpoint insertion strategy。

>> 那為什麼不采用樸素的LRU算法，直接将讀取的頁放入到LRU清單的首部呢？這是因為若直接将讀取到的頁放入到LRU的首部，那麼某些SQL操作可能會使緩沖池中的頁被重新整理出，進而影響緩沖池的效率。

>> 常見的這類操作為索引或資料的掃描操作。這類操作需要通路表中的許多頁，甚至是全部的頁，而這些頁通常來說又僅在這次查詢操作中需要，并不是活躍的熱點資料。如果頁被放入LRU清單的首部，那麼非常可能将所需要的熱點資料頁從LRU清單中移除，而在下一次需要讀取該頁時，InnoDB存儲引擎需要再次通路磁盤。

>> Buffer pool hit rate，表示緩沖池的命中率，這個例子中為100%，說明緩沖池運作狀态非常良好。通常該值不應該小于95%。若發生Buffer pool hit rate的值小于95%這種情況，使用者需要觀察是否是由于全表掃描引起的LRU清單被污染的問題。

>> 執行指令SHOW ENGINE INNODB STATUS顯示的不是目前的狀态，而是過去某個時間範圍内InnoDB存儲引擎的狀态。從上面的例子可以發現，Per second averages calculated from the last 24 seconds代表的資訊為過去24秒内的資料庫狀态。

>> 在LRU清單中的頁被修改後，稱該頁為髒頁（dirty page），即緩沖池中的頁和磁盤上的頁的資料産生了不一緻。這時資料庫會通過CHECKPOINT機制将髒頁重新整理回磁盤，而Flush清單中的頁即為髒頁清單。需要注意的是，髒頁既存在于LRU清單中，也存在于Flush清單中

>> 重做日志緩沖一般不需要設定得很大，因為一般情況下每一秒鐘會将重做日志緩沖重新整理到日志檔案，是以使用者隻需要保證每秒産生的事務量在這個緩沖大小之内即可

>> 目前3TB的MySQL資料庫已并不少見，但是3 TB的記憶體卻非常少見。目前Oracle Exadata旗艦資料庫一體機也就隻有2 TB的記憶體。

>> 是以Checkpoint（檢查點）技術的目的是解決以下幾個問題：□ 縮短資料庫的恢複時間；□ 緩沖池不夠用時，将髒頁重新整理到磁盤；□ 重做日志不可用時，重新整理髒頁。

>> 對于InnoDB存儲引擎而言，其是通過LSN（Log Sequence Number）來标記版本的。而LSN是8位元組的數字，其機關是位元組。每個頁有LSN，重做日志中也有LSN，Checkpoint也有LSN。可以通過指令SHOW ENGINE INNODB STATUS來觀察：

>> InnoDB存儲引擎的關鍵特性包括：□ 插入緩沖（Insert Buffer）□ 兩次寫（Double Write）□ 自适應哈希索引（Adaptive Hash Index）□ 異步IO（Async IO）□ 重新整理鄰接頁（Flush Neighbor Page）

>> InnoDB存儲引擎開創性地設計了Insert Buffer，對于非聚集索引的插入或更新操作，不是每一次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個Insert Buffer對象中，好似欺騙。資料庫這個非聚集的索引已經插到葉子節點，而實際并沒有，隻是存放在另一個位置。

>> 然後再以一定的頻率和情況進行Insert Buffer和輔助索引頁子節點的merge（合并）操作，這時通常能将多個插入合并到一個操作中（因為在一個索引頁中），這就大大提高了對于非聚集索引插入的性能。

>> 輔助索引不能是唯一的，因為在插入緩沖時，資料庫并不去查找索引頁來判斷插入的記錄的唯一性。如果去查找肯定又會有離散讀取的情況發生，進而導緻Insert Buffer失去了意

>> 正如前面所說的，目前Insert Buffer存在一個問題是：在寫密集的情況下，插入緩沖會占用過多的緩沖池記憶體（innodb_buffer_pool），預設最大可以占用到1/2的緩沖池記憶體。

>> InnoDB從1.0.x版本開始引入了Change Buffer，可将其視為Insert Buffer的更新。從這個版本開始，InnoDB存儲引擎可以對DML操作——INSERT、DELETE、UPDATE都進行緩沖，他們分别是：Insert Buffer、Delete Buffer、Purge buffer。

>> innodb_change_buffer_max_size值預設為25，表示最多使用1/4的緩沖池記憶體空間。而需要注意的是，該參數的最大有效值為50。

>> 可能令絕大部分使用者感到吃驚的是，Insert Buffer的資料結構是一棵B+樹。在MySQL 4.1之前的版本中每張表有一棵Insert Buffer B+樹。而在現在的版本中，全局隻有一棵Insert Buffer B+樹，負責對所有的表的輔助索引進行Insert Buffer。

>> 而這棵B+樹存放在共享表空間中，預設也就是ibdata1中。是以，試圖通過獨立表空間ibd檔案恢複表中資料時，往往會導緻CHECK TABLE失敗

>> 。這是因為表的輔助索引中的資料可能還在Insert Buffer中，也就是共享表空間中，是以通過ibd檔案進行恢複後，還需要進行REPAIR TABLE操作來重建表上所有的輔助索引。

>> 如果說Insert Buffer帶給InnoDB存儲引擎的是性能上的提升，那麼doublewrite（兩次寫）帶給InnoDB存儲引擎的是資料頁的可靠性。

>> 當發生資料庫當機時，可能InnoDB存儲引擎正在寫入某個頁到表中，而這個頁隻寫了一部分，比如16KB的頁，隻寫了前4KB，之後就發生了當機，這種情況被稱為部分寫失效（partial page write）。在InnoDB存儲引擎未使用doublewrite技術前，曾經出現過因為部分寫失效而導緻資料丢失的情況。有經驗的DBA也許會想，如果發生寫失效，可以通過重做日志進行恢複。這是一個辦法。但是必須清楚地認識到，重做日志中記錄的是對頁的實體操作，如偏移量800，寫'aaaa'記錄。如果這個頁本身已經發生了損壞，再對其進行重做是沒有意義的。這就是說，在應用（apply）重做日志前，使用者需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewrite

>> 在對緩沖池的髒頁進行重新整理時，并不直接寫磁盤，而是會通過memcpy函數将髒頁先複制到記憶體中的doublewrite buffer，之後通過doublewrite buffer再分兩次，每次1MB順序地寫入共享表空間的實體磁盤上，然後馬上調用fsync函數，同步磁盤，避免緩沖寫帶來的問題。在這個過程中，因為doublewrite頁是連續的，是以這個過程是順序寫的，開銷并不是很大。在完成doublewrite頁的寫入後，再将doublewrite buffer中的頁寫入各個表空間檔案中，此時的寫入則是離散的。

>> InnoDB存儲引擎會監控對表上各索引頁的查詢。如果觀察到建立哈希索引可以帶來速度提升，則建立哈希索引，稱之為自适應哈希索引（Adaptive Hash Index，AHI）。

>> 值得注意的是，哈希索引隻能用來搜尋等值的查詢，如SELECT*FROM table WHERE index_col='xxx'。而對于其他查找類型，如範圍查找，是不能使用哈希索引的，是以這裡出現了non-hash searches/s的情況

>> 使用者可以在發出一個IO請求後立即再發出另一個IO請求，當全部IO請求發送完畢後，等待所有IO操作的完成，這就是AIO。AIO的另一個優勢是可以進行IO Merge操作，也就是将多個IO合并為1個IO，這樣可以提高IOPS的性能

>> 需要注意的是，Native AIO需要作業系統提供支援。Windows系統和Linux系統都提供Native AIO支援，而Mac OSX系統則未提供

>> 參數innodb_use_native_aio用來控制是否啟用Native AIO，在Linux作業系統下，預設值為ON

>> 在關閉時，參數innodb_fast_shutdown影響着表的存儲引擎為InnoDB的行為。該參數可取值為0、1、2，預設值為1。

>> 參數innodb_force_recovery影響了整個InnoDB存儲引擎恢複的狀況。該參數值預設為0，代表當發生需要恢複時，進行所有的恢複操作，當不能進行有效恢複時，如資料頁發生了corruption，MySQL資料庫可能發生當機（crash），并把錯誤寫入錯誤日志中去。

>> 參數innodb_force_recovery還可以設定為6個非零值：1～6。大的數字表示包含了前面所有小數字表示的影響

第3章 檔案

>> 預設情況下，MySQL執行個體會按照一定的順序在指定的位置進行讀取，使用者隻需通過指令mysql--help | grep my.cnf來尋找即可。

>> Oracle資料庫存在所謂的隐藏參數（undocumented parameter），以供Oracle“内部人士”使用，SQL Server也有類似的參數。有些DBA曾問我，MySQL中是否也有這類參數。我的回答是：沒有，也不需要。即使Oracle和SQL Server中都有些所謂的隐藏參數，在絕大多數的情況下，這些資料庫廠商也不建議使用者在生産環境中對其進行很大的調整。

>> MySQL資料庫中的參數可以分為兩類：□ 動态（dynamic）參數□ 靜态（static）參數動态參數意味着可以在MySQL執行個體運作中進行更改，靜态參數說明在整個執行個體生命周期内都不得進行更改，就好像是隻讀（read only）的

>> 當出現MySQL資料庫不能正常啟動時，第一個必須查找的檔案應該就是錯誤日志檔案，該檔案記錄了錯誤資訊，能很好地指導使用者發現問題。

>> 設定long_query_time這個門檻值後，MySQL資料庫會記錄運作時間超過該值的所有SQL語句，但運作時間正好等于long_query_time的情況并不會被記錄下。也就是說，在源代碼中判斷的是大于long_query_time，而非大于等于

>> 另一個和慢查詢日志有關的參數是log_queries_not_using_indexes，如果運作的SQL語句沒有使用索引，則MySQL資料庫同樣會将這條SQL語句記錄到慢查詢日志檔案。

>> MySQL 5.6.5版本開始新增了一個參數log_throttle_queries_not_using_indexes，用來表示每分鐘允許記錄到slow log的且未使用索引的SQL語句次數。該值預設為0，表示沒有限制。在生産環境下，若沒有使用索引，此類SQL語句會頻繁地被記錄到slow log，進而導緻slow log檔案的大小不斷增加，故DBA可通過此參數進行配置。

>> MySQL 5.1開始可以将慢查詢的日志記錄放入一張表中，這使得使用者的查詢更加友善和直覺。慢查詢表在mysql架構下，名為slow_log

>> 檢視slow_log表的定義會發現該表使用的是CSV引擎，對大資料量下的查詢效率可能不高。使用者可以把slow_log表的引擎轉換到MyISAM，并在start_time列上添加索引以進一步提高查詢的效率。

>> 不能忽視的是，将slow_log表的存儲引擎更改為MyISAM後，還是會對資料庫造成額外的開銷。

>> 使用者可以通過額外的參數long_query_io将超過指定邏輯IO次數的SQL語句記錄到slow log中。該值預設為100，即表示對于邏輯讀取次數大于100的SQL語句，記錄到slow log中。而為了相容原MySQL資料庫的運作方式，還添加了參數slow_query_type，用來表示啟用slow log的方式

>> 查詢日志記錄了所有對MySQL資料庫請求的資訊，無論這些請求是否得到了正确的執行。預設檔案名為：主機名.log

>> 。同樣地，從MySQL 5.1開始，可以将查詢日志的記錄放入mysql架構下的general_log表中，該表的使用方法和前面小節提到的slow_log基本一樣

>> 二進制日志（binary log）記錄了對MySQL資料庫執行更改的所有操作，但是不包括SELECT和SHOW這類操作，因為這類操作對資料本身并沒有修改。

>> 使用事務的表存儲引擎（如InnoDB存儲引擎）時，所有未送出（uncommitted）的二進制日志會被記錄到一個緩存中去，等該事務送出（committed）時直接将緩沖中的二進制日志寫入二進制日志檔案，而該緩沖的大小由binlog_cache_size決定，預設大小為32K。

>> 此外，binlog_cache_size是基于會話（session）的，也就是說，當一個線程開始一個事務時，MySQL會自動配置設定一個大小為binlog_cache_size的緩存，是以該值的設定需要相當小心，不能設定過大。當一個事務的記錄大于設定的binlog_cache_size時，MySQL會把緩沖中的日志寫入一個臨時檔案中，是以該值又不能設得太小

>> Binlog_cache_use記錄了使用緩沖寫二進制日志的次數，binlog_cache_disk_use記錄了使用臨時檔案寫二進制日志的次數

>> 預設情況下，二進制日志并不是在每次寫的時候同步到磁盤（使用者可以了解為緩沖寫）。是以，當資料庫所在作業系統發生當機時，可能會有最後一部分資料沒有寫入二進制日志檔案中，這會給恢複和複制帶來問題

>> 即使将sync_binlog設為1，還是會有一種情況導緻問題的發生。當使用InnoDB存儲引擎時，在一個事務發出COMMIT動作之前，由于sync_binlog為1，是以會将二進制日志立即寫入磁盤。如果這時已經寫入了二進制日志，但是送出還沒有發生，并且此時發生了當機，那麼在MySQL資料庫下次啟動時，由于COMMIT操作并沒有發生，這個事務會被復原掉。但是二進制日志已經記錄了該事務資訊，不能被復原。

>> 如果目前資料庫是複制中的slave角色，則它不會将從master取得并執行的二進制日志寫入自己的二進制日志檔案中去。如果需要寫入，要設定log-slave-update。如果需要搭建master=>slave=>slave架構的複制，則必須設定該參數。

>> MySQL 5.1開始引入了binlog_format參數，該參數可設的值有STATEMENT、ROW和MIXED

>> 上面的這個例子告訴我們，将參數binlog_format設定為ROW，會對磁盤空間要求有一定的增加。而由于複制是采用傳輸二進制日志方式實作的，是以複制的網絡開銷也有所增加。

>> 要檢視二進制日志檔案的内容，必須通過MySQL提供的工具mysqlbinlog。對于STATEMENT格式的二進制日志檔案，在使用mysqlbinlog後，看到的就是執行的邏輯SQL語句

>> 但不論表采用何種存儲引擎，MySQL都有一個以frm為字尾名的檔案，這個檔案記錄了該表的表結構定義。

>> frm還用來存放視圖的定義，如使用者建立了一個v_a視圖，那麼對應地會産生一個v_a.frm檔案，用來記錄視圖的定義，該檔案是文本檔案，可以直接使用cat指令進行檢視

>> 設定innodb_data_file_path參數後，所有基于InnoDB存儲引擎的表的資料都會記錄到該共享表空間中。若設定了參數innodb_file_per_table，則使用者可以将每個基于InnoDB存儲引擎的表産生一個獨立表空間

>> 。獨立表空間的命名規則為：表名.ibd。通過這樣的方式，使用者不用将所有資料都存放于預設的表空間

>> 這些單獨的表空間檔案僅存儲該表的資料、索引和插入緩沖BITMAP等資訊，其餘資訊還是存放在預設的表空間中

>> 在預設情況下，在InnoDB存儲引擎的資料目錄下會有兩個名為ib_logfile0和ib_logfile1的檔案。在MySQL官方手冊中将其稱為InnoDB存儲引擎的日志檔案，不過更準确的定義應該是重做日志檔案（redo log file）。為什麼強調是重做日志檔案呢？因為重做日志檔案對于InnoDB存儲引擎至關重要，它們記錄了對于InnoDB存儲引擎的事務日志。

>> 每個InnoDB存儲引擎至少有1個重做日志檔案組（group），每個檔案組下至少有2個重做日志檔案，如預設的ib_logfile0和ib_logfile1。為了得到更高的可靠性，使用者可以設定多個的鏡像日志組（mirrored log groups），将不同的檔案組放在不同的磁盤上，以此提高重做日志的高可用性。在日志組中每個重做日志檔案的大小一緻，并以循環寫入的方式運作。

>> InnoDB存儲引擎先寫重做日志檔案1，當達到檔案的最後時，會切換至重做日志檔案2，再當重做日志檔案2也被寫滿時，會再切換到重做日志檔案1中。

>> 若磁盤本身已經做了高可用的方案，如磁盤陣列，那麼可以不開啟重做日志鏡像的功能

>> 二進制日志會記錄所有與MySQL資料庫有關的日志記錄，包括InnoDB、MyISAM、Heap等其他存儲引擎的日志。而InnoDB存儲引擎的重做日志隻記錄有關該存儲引擎本身的事務日志。

>> 其次，記錄的内容不同，無論使用者将二進制日志檔案記錄的格式設為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關于一個事務的具體操作内容，即該日志是邏輯日志。而InnoDB存儲引擎的重做日志檔案記錄的是關于每個頁（Page）的更改的實體情況。

>> 此外，寫入的時間也不同，二進制日志檔案僅在事務送出前進行送出，即隻寫磁盤一次，不論這時該事務多大。而在事務進行的過程中，卻不斷有重做日志條目（redo entry）被寫入到重做日志檔案中。

>> 在InnoDB存儲引擎中，對于各種不同的操作有着不同的重做日志格式。到InnoDB 1.2.x版本為止，總共定義了51種重做日志類型。

>> 在第2章中已經提到，寫入重做日志檔案的操作不是直接寫，而是先寫入一個重做日志緩沖（redo log buffer）中，然後按照一定的條件順序地寫入日志檔案

>> 從重做日志緩沖往磁盤寫入時，是按512個位元組，也就是一個扇區的大小進行寫入。因為扇區是寫入的最小機關，是以可以保證寫入必定是成功的。是以在重做日志的寫入過程中不需要有doublewrite。

>> 是以為了保證事務的ACID中的持久性，必須将innodb_flush_log_at_trx_commit設定為1，也就是每當有事務送出時，就必須確定事務都已經寫入重做日志檔案。那麼當資料庫因為意外發生當機時，可以通過重做日志檔案恢複，并保證可以恢複已經送出的事務。

第4章 表

>> 在InnoDB存儲引擎中，表都是根據主鍵順序組織存放的，這種存儲方式的表稱為索引組織表（index organized table）。

>> 在InnoDB存儲引擎表中，每張表都有個主鍵（Primary Key），如果在建立表時沒有顯式地定義主鍵，則InnoDB存儲引擎會按如下方式選擇或建立主鍵：□ 首先判斷表中是否有非空的唯一索引（Unique NOT NULL），如果有，則該列即為主鍵。□ 如果不符合上述條件，InnoDB存儲引擎自動建立一個6位元組大小的指針。

>> 當表中有多個非空唯一索引時，InnoDB存儲引擎将選擇建表時第一個定義的非空唯一索引為主鍵

>> 。這裡需要非常注意的是，主鍵的選擇根據的是定義索引的順序，而不是建表時列的順序。

>> 可以通過下面的SQL語句判斷表的主鍵值：

>> _rowid可以顯示表的主鍵，是以通過上述查詢可以找到表z的主鍵

>> 另外需要注意的是，_rowid隻能用于檢視單個列為主鍵的情況，對于多列組成的主鍵就顯得無能為力了

>> 從InnoDB存儲引擎的邏輯存儲結構看，所有資料都被邏輯地存放在一個空間中，稱之為表空間（tablespace）。表空間又由段（segment）、區（extent）、頁（page）組成。頁在一些文檔中有時也稱為塊（block），

>> InnoDB存儲引擎的邏輯存儲結構大緻如圖4-1所示。

>> 第3章中已經介紹了在預設情況下InnoDB存儲引擎有一個共享表空間ibdata1，即所有資料都存放在這個表空間内。如果使用者啟用了參數innodb_file_per_table，則每張表内的資料可以單獨放到一個表空間内。

>> 如果啟用了innodb_file_per_table的參數，需要注意的是每張表的表空間記憶體放的隻是資料、索引和插入緩沖Bitmap頁，其他類的資料，如復原（undo）資訊，插入緩沖索引頁、系統事務資訊，二次寫緩沖（Double write buffer）等還是存放在原來的共享表空間内。這同時也說明了另一個問題：即使在啟用了參數innodb_file_per_table之後，共享表空間還是會不斷地增加其大小。

>> InnoDB存儲引擎不會在執行rollback時去收縮這個表空間。雖然InnoDB不會回收這些空間，但是會自動判斷這些undo資訊是否還需要，如果不需要，則會将這些空間标記為可用空間，供下次undo使用。

>> 我用python寫了一個py_innodb_page_info小工具，用來檢視表空間中各頁的類型和資訊，使用者可以在code.google.com上搜尋david-mysql-tools進行查找

>> 因為前面已經介紹過了InnoDB存儲引擎表是索引組織的（index organized），是以資料即索引，索引即資料。那麼資料段即為B+樹的葉子節點（圖4-1的Leaf node segment），索引段即為B+樹的非索引節點（圖4-1的Non-leaf node segment）。

>> 區是由連續頁組成的空間，在任何情況下每個區的大小都為1MB。為了保證區中頁的連續性，InnoDB存儲引擎一次從磁盤申請4～5個區。在預設情況下，InnoDB存儲引擎頁的大小為16KB，即一個區中一共有64個連續的頁。

>> InnoDB 1.0.x版本開始引入壓縮頁，即每個頁的大小可以通過參數KEY_BLOCK_SIZE設定為2K、4K、8K，是以每個區對應頁的數量就應該為512、256、128。

>> InnoDB 1.2.x版本新增了參數innodb_page_size，通過該參數可以将預設頁的大小設定為4K、8K，但是頁中的資料庫不是壓縮。這時區中頁的數量同樣也為256、128。總之，不論頁的大小怎麼變化，區的大小總是為1M。

>> 在使用者啟用了參數innodb_file_per_talbe後，建立的表預設大小是96KB。區中是64個連續的頁，建立的表的大小至少是1MB才對啊？其實這是因為在每個段開始時，先用32個頁大小的碎片頁（fragment page）來存放資料，在使用完這些頁之後才是64個連續頁的申請。這樣做的目的是，對于一些小表，或者是undo這類的段，可以在開始時申請較少的空間，節省磁盤容量的開銷

>> 因為已經用完了32個碎片頁，新的頁會采用區的方式進行空間的申請，如果此時使用者再通過py_innodb_page_info工具來看表空間檔案t1.ibd，應該可以看到很多類型為Freshly Allocated Page的頁：

>> 從InnoDB 1.2.x版本開始，可以通過參數innodb_page_size将頁的大小設定為4K、8K、16K。若設定完成，則所有表中頁的大小都為innodb_page_size，不可以對其再次進行修改。除非通過mysqldump導入和導出操作來産生新的庫

>> 每個頁存放的行記錄也是有硬性定義的，最多允許存放16KB / 2-200行的記錄，即7992行記錄

>> InnoDB存儲引擎提供了Compact和Redundant兩種格式來存放行記錄資料，

>> 在MySQL 5.1版本中，預設設定為Compact行格式。使用者可以通過指令SHOW TABLE STATUS LIKE 'table_name'來檢視目前表使用的行格式，其中row_format屬性表示目前所使用的行記錄結構類型。

>> 每行資料除了使用者定義的列外，還有兩個隐藏列，事務ID列和復原指針列，分别為6位元組和7位元組的大小。

>> 若InnoDB表沒有定義主鍵，每行還會增加一個6位元組的rowid列。

>> InnoDB存儲引擎可以将一條記錄中的某些資料存儲在真正的資料頁面之外。一般認為BLOB、LOB這類的大對象列類型的存儲會把資料存放在資料頁面之外。但是，這個了解有點偏差，BLOB可以不将資料放在溢出頁面，而且即便是VARCHAR列資料類型，依然有可能被存放為行溢出資料

>> 從錯誤消息可以看到InnoDB存儲引擎并不支援65535長度的VARCHAR。這是因為還有别的開銷，通過實際測試發現能存放VARCHAR類型的最大長度為65532。

>> 是以從這個例子中使用者也應該了解VARCHAR（N）中的N指的是字元的長度。而文檔中說明VARCHAR類型最大支援65535，機關是位元組。

>> 此外需要注意的是，MySQL官方手冊中定義的65535長度是指所有VARCHAR列的長度總和，如果列的長度總和超出這個長度，依然無法建立

>> 即使能存放65532個位元組，但是有沒有想過，InnoDB存儲引擎的頁為16KB，即16384位元組，怎麼能存放65532位元組呢？是以，在一般情況下，InnoDB存儲引擎的資料都是存放在頁類型為B-tree node中。但是當發生行溢出時，資料存放在頁類型為Uncompress BLOB頁中。

>> InnoDB存儲引擎表是索引組織的，即B+Tree的結構，這樣每個頁中至少應該有兩條行記錄（否則失去了B+Tree的意義，變成連結清單了）。是以，如果頁中隻能存放下一條記錄，那麼InnoDB存儲引擎會自動将行資料存放到溢出頁中

>> 經過多次試驗測試，發現這個門檻值的長度為8098

>> 對于TEXT或BLOB的資料類型，使用者總是以為它們是存放在Uncompressed BLOB Page中的，其實這也是不準确的。是放在資料頁中還是BLOB頁中，和前面讨論的VARCHAR一樣，至少保證一個頁能存放兩條記錄

>> InnoDB 1.0.x版本開始引入了新的檔案格式（file format，使用者可以了解為新的頁格式），以前支援的Compact和Redundant格式稱為Antelope檔案格式，新的檔案格式稱為Barracuda檔案格式。Barracuda檔案格式下擁有兩種新的行記錄格式：Compressed和Dynamic。

>> 從MySQL 4.1版本開始，CHR（N）中的N指的是字元的長度，而不是之前版本的位元組長度。也就說在不同的字元集下，CHAR類型列内部存儲的可能不是定長的資料

>> SELECT a,CHAR_LENGTH(a),LENGTH(a)

>> SELECT a,HEX(a)        -> FROM j\G;

>> SHOW VARIABLES LIKE 'innodb_file_format'\G;

>> 關系型資料庫系統和檔案系統的一個不同點是，關系資料庫本身能保證存儲資料的完整性，不需要應用程式的控制，而檔案系統一般需要在程式端進行控制

>> 通過設定參數sql_mode的值為STRICT_TRANS_TABLES，這次MySQL資料庫對于輸入值的合法性進行了限制，而且針對不同的錯誤，提示的錯誤内容也都不同。參數sql_mode可設的值有很多，具體可參考MySQL官方手冊。

>> 最多可以為一個表建立6個觸發器，即分别為INSERT、UPDATE、DELETE的BEFORE和AFTER各定義一個。

>> 假設有張使用者消費表，每次使用者購買一樣物品後其金額都是減的，若這時有“不懷好意”的使用者做了類似減去一個負值的操作，這樣使用者的錢沒減少反而會不斷增加

>> 一般來說，稱被引用的表為父表，引用的表稱為子表。外鍵定義時的ON DELETE和ON UPDATE表示在對父表進行DELETE和UPDATE操作時，對子表所做的操作

>> CASCADE表示當父表發生DELETE或UPDATE操作時，對相應的子表中的資料也進行DELETE或UPDATE操作。SET NULL表示當父表發生DELETE或UPDATE操作時，相應的子表中的資料被更新為NULL值，但是子表中相對應的列必須允許為NULL值。NO ACTION表示當父表發生DELETE或UPDATE操作時，抛出錯誤，不允許這類操作發生。RESTRICT表示當父表發生DELETE或UPDATE操作時，抛出錯誤，不允許這類操作發生。

>> 在其他資料庫中，如Oracle資料庫，有一種稱為延時檢查（deferred check）的外鍵限制，即檢查在SQL語句運作完成後再進行。而目前MySQL資料庫的外鍵限制都是即時檢查（immediate check）

>> 雖然視圖是基于基表的一個虛拟表，但是使用者可以對某些視圖進行更新操作，其本質就是通過視圖的定義來更新基本表。一般稱可以進行更新操作的視圖為可更新視圖（updatable view）。視圖定義中的WITH CHECK OPTION就是針對于可更新的視圖的，即更新的值是否需要檢查

>> MySQL資料庫DBA的一個常用的指令是SHOW TABLES，該指令會顯示出目前資料庫下所有的表。但因為視圖是虛表，同樣被作為表顯示出來

>> 使用者隻想檢視目前架構下的基表，可以通過information_schema架構下的TABLE表來查詢，并搜尋表類型為BASE TABLE的表

>> Oracle資料庫支援物化視圖——該視圖不是基于基表的虛表，而是根據基表實際存在的實表，即物化視圖的資料存儲在非易失的儲存設備上。物化視圖可以用于預先計算并儲存多表的連結（JOIN）或聚集（GROUP BY）等耗時較多的SQL操作結果。這樣，在執行複雜查詢時，就可以避免進行這些耗時的操作，進而快速得到結果。物化視圖的好處是對于一些複雜的統計類查詢能直接查出結果。在Microsoft SQL Server資料庫中，稱這種視圖為索引視圖。

>> 分區功能并不是在存儲引擎層完成的，是以不是隻有InnoDB存儲引擎支援分區，常見的存儲引擎MyISAM、NDB等都支援。但也并不是所有的存儲引擎都支援，如CSV、FEDORATED、MERGE等就不支援。在使用分區功能前，應該對選擇的存儲引擎對分區的支援有所了解。

>> MySQL資料庫支援的分區類型為水準分[插圖]，并不支援垂直分[插圖]。此外，MySQL資料庫的分區是局部分區索引，一個分區中既存放了資料又存放了索引。而全局分區是指，資料存放在各個分區中，但是所有資料的索引放在一個對象中。目前，MySQL資料庫還不支援全局分區。

>> 不論建立何種類型的分區，如果表中存在主鍵或唯一索引時，分區列必須是唯一索引的一個組成部分

>> 唯一索引可以是允許NULL值的，并且分區列隻要是唯一索引的一個組成部分，不需要整個唯一索引列都是分區列

>> 如果建表時沒有指定主鍵，唯一索引，可以指定任何一個列為分區列

>> 檢視表在磁盤上的實體檔案，啟用分區之後，表不再由一個ibd檔案組成了，而是由建立分區時的各個分區ibd檔案組成，如下面的t#P#p0.ibd，t#P#p1.ibd

>> 通過EXPLAIN PARTITION指令我們可以發現，在上述語句中，SQL優化器隻需要去搜尋p2008這個分區，而不會去搜尋所有的分區——稱為Partition Pruning（分區修剪），故查詢的速度得到了大幅度的提

>> 在前面介紹的RANGE、LIST、HASH和KEY這四種分區中，分區的條件是：資料必須是整型（interger），如果不是整型，那應該需要通過函數将其轉化為整型，如YEAR（），TO_DAYS（），MONTH（）等函數。MySQL5.5版本開始支援COLUMNS分區，可視為RANGE分區和LIST分區的一種進化。COLUMNS分區可以直接使用非整型的資料進行分區，分區根據類型直接比較而得，不需要轉化為整型。此外，RANGE COLUMNS分區可以對多個列的值進行分區。

>> 子分區（subpartitioning）是在分區的基礎上再進行分區，有時也稱這種分區為複合分區（composite partitioning）。MySQL資料庫允許在RANGE和LIST的分區上再進行HASH或KEY的子分區

>> 子分區的建立需要注意以下幾個問題：□ 每個子分區的數量必須相同。□ 要在一個分區表的任何分區上使用SUBPARTITION來明确定義任何子分區，就必須定義所有的子分區。

>> 子分區可以用于特别大的表，在多個磁盤間分别配置設定資料和索引

>> MySQL資料庫允許對NULL值做分區，但是處理的方法與其他資料庫可能完全不同。MYSQL資料庫的分區總是視NULL值視小于任何的一個非NULL值，這和MySQL資料庫中處理NULL值的ORDER BY操作是一樣的

>> 。是以對于不同的分區類型，MySQL資料庫對于NULL值的處理也是各不相同。

>> HASH和KEY分區對于NULL的處理方式和RANGE分區、LIST分區不一樣。任何分區函數都會将含有NULL值的記錄傳回為0

>> 對于OLAP的應用，分區的确是可以很好地提高查詢的性能，因為OLAP應用大多數查詢需要頻繁地掃描一張很大的表。假設有一張1億行的表，其中有一個時間戳屬性列。使用者的查詢需要從這張表中擷取一年的資料。如果按時間戳進行分區，則隻需要掃描相應的分區即可。這就是前面介紹的Partition Pruning技術。

>> 然而對于OLTP的應用，分區應該非常小心。在這種應用下，通常不可能會擷取一張大表中10%的資料，大部分都是通過索引傳回幾條記錄即可。而根據B+樹索引的原理可知，對于一張大表，一般的B+樹需要2～3次的磁盤IO。是以B+樹可以很好地完成操作，不需要分區的幫助，并且設計不好的分區會帶來嚴重的性能問題。

>> 我發現很多開發團隊會認為含有1000W行的表是一張非常巨大的表，是以他們往往會選擇采用分區，如對主鍵做10個HASH的分區，這樣每個分區就隻有100W的資料了，是以查詢應該變得更快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有沒有考慮過這樣一種情況：100W和1000W行的資料本身構成的B+樹的層次都是一樣的，可能都是2層。

>> MySQL 5.6開始支援ALTER TABLE … EXCHANGE PARTITION文法。該語句允許分區或子分區中的資料與另一個非分區的表中的資料進行交換。如果非分區表中的資料為空，那麼相當于将分區中的資料移動到非分區表中。若分區表中的資料為空，則相當于将外部表中的資料導入到分區中。

第5章 索引與算法

>>  B+樹中的B不是代表二叉（binary），而是代表平衡（balance），因為B+樹是從最早的平衡二叉樹演化而來，但是B+樹不是一個二叉樹。

>> 另一個常常被DBA忽視的問題是：B+樹索引并不能找到一個給定鍵值的具體行。B+樹索引能找到的隻是被查找資料行所在的頁。然後資料庫通過把頁讀入到記憶體，再在記憶體中進行查找，最後得到要查找的資料。

>> 平衡二叉樹的查找性能是比較高的，但不是最高的，隻是接近最高性能。最好的性能需要建立一棵最優二叉樹，但是最優二叉樹的建立和維護需要大量的操作，是以，使用者一般隻需建立一棵平衡二叉樹即可。

>> 平衡二叉樹的查詢速度的确很快，但是維護一棵平衡二叉樹的代價是非常大的。通常來說，需要1次或多次左旋和右旋來得到插入或更新後樹的平衡性。

>> B+樹由B樹和索引順序通路方法（ISAM，是不是很熟悉？對，這也是MyISAM引擎最初參考的資料結構）演化而來，但是在現實使用過程中幾乎已經沒有使用B樹的情況了。

>> B+樹是為磁盤或其他直接存取輔助裝置設計的一種平衡查找樹。在B+樹中，所有記錄節點都是按鍵值的大小順序存放在同一層的葉子節點上，由各葉子節點指針進行連接配接。

>> 可以看到，不管怎麼變化，B+樹總是會保持平衡。但是為了保持平衡對于新插入的鍵值可能需要做大量的拆分頁（split）操作。因為B+樹結構主要用于磁盤，頁的拆分意味着磁盤的操作，是以應該在可能的情況下盡量減少頁的拆分操作。

>> 是以，B+樹同樣提供了類似于平衡二叉樹的旋轉（Rotation）功能。

>> B+樹索引的本質就是B+樹在資料庫中的實作。

>> 但是B+索引在資料庫中有一個特點是高扇出性，是以在資料庫中，B+樹的高度一般都在2～4層，這也就是說查找某一鍵值的行記錄時最多隻需要2到4次IO，這倒不錯。因為目前一般的機械磁盤每秒至少可以做100次IO，2～4次的IO意味着查詢時間隻需0.02～0.04秒。

>> 資料庫中的B+樹索引可以分為聚集索引（clustered inex）和輔助索引（secondary index[插圖]，但是不管是聚集還是輔助的索引，其内部都是B+樹的，即高度平衡的，葉子節點存放着所有的資料。聚集索引與輔助索引不同的是，葉子節點存放的是否是一整行的資訊。

>> 聚集索引（clustered index）就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄資料，也将聚集索引的葉子節點稱為資料頁。

>> 由于實際的資料頁隻能按照一棵B+樹進行排序，是以每張表隻能擁有一個聚集索引

>> 許多資料庫的文檔會這樣告訴讀者：聚集索引按照順序實體地存儲資料。如果看圖5-14，可能也會有這樣的感覺。但是試想一下，如果聚集索引必須按照特定順序存放實體記錄，則維護成本顯得非常之高。是以，聚集索引的存儲并不是實體上連續的，而是邏輯上連續的。

>> 對于輔助索引（Secondary Index，也稱非聚集索引），葉子節點并不包含行記錄的全部資料。葉子節點除了包含鍵值以外，每個葉子節點中的索引行中還包含了一個書簽（bookmark）。該書簽用來告訴InnoDB存儲引擎哪裡可以找到與索引相對應的行資料

>> 。由于InnoDB存儲引擎表是索引組織表，是以InnoDB存儲引擎的輔助索引的書簽就是相應行資料的聚集索引鍵。

>> 如果在一棵高度為3的輔助索引樹中查找資料，那需要對這棵輔助索引樹周遊3次找到指定主鍵，如果聚集索引樹的高度同樣為3，那麼還需要對聚集索引樹進行3次查找，最終找到一個完整的行資料所在的頁，是以一共需要6次邏輯IO通路以得到最終的一個資料頁。

>> 使用者可以設定對整個列的資料進行索引，也可以隻索引一個列的開頭部分資料，如前面建立的表t，列b為varchar（8000），但是使用者可以隻索引前100個字段

>>  Cardinality：非常關鍵的值，表示索引中唯一值的數目的估計值。Cardinality表的行數應盡可能接近1，如果非常小，那麼使用者需要考慮是否可以删除此索引。

>> Cardinality值非常關鍵，優化器會根據這個值來判斷是否使用這個索引。但是這個值并不是實時更新的，即并非每次索引的更新都會更新該值，因為這樣代價太大了

>> Cardinality為NULL，在某些情況下可能會發生索引建立了卻沒有用到的情況。或者對兩條基本一樣的語句執行EXPLAIN，但是最終出來的結果不一樣：一個使用索引，另外一個使用全表掃描。這時最好的解決辦法就是做一次ANALYZE TABLE的操作。

>> MySQL 5.5版本之前（不包括5.5）存在的一個普遍被人诟病的問題是MySQL資料庫對于索引的添加或者删除的這類DDL操作，MySQL資料庫的操作過程為：

>> InnoDB存儲引擎從InnoDB 1.0.x版本開始支援一種稱為Fast Index Creation（快速索引建立）的索引建立方式——簡稱FIC。

>> 對于輔助索引的建立，InnoDB存儲引擎會對建立索引的表加上一個S鎖。在建立的過程中，不需要重建表，是以速度較之前提高很多，并且資料庫的可用性也得到了提高。删除輔助索引操作就更簡單了，InnoDB存儲引擎隻需更新内部視圖，并将輔助索引的空間标記為可用，同時删除MySQL資料庫内部視圖上對該表的索引定義即可。這裡需要特别注意的是，臨時表的建立路徑是通過參數tmpdir進行設定的。使用者必須保證tmpdir有足夠的空間可以存放臨時表，否則會導緻建立索引失敗。

>> 由于FIC在索引的建立的過程中對表加上了S鎖，是以在建立的過程中隻能對該表進行讀操作，若有大量的事務需要對目标表進行寫操作，那麼資料庫的服務同樣不可用

>> Facebook采用PHP腳本來現實OSC，而并不是通過修改InnoDB存儲引擎源碼的方式。OSC最初由Facebook的員工Vamsi Ponnekanti開發。此外，OSC借鑒了開源社群之前的工具The openarkkit toolkit oak-online-alter-table。實作OSC步驟如下：

>> MySQL 5.6版本開始支援Online DDL（線上資料定義）操作，其允許輔助索引建立的同時，還允許其他諸如INSERT、UPDATE、DELETE這類DML操作，這極大地提高了MySQL資料庫在生産環境中的可用性。

>> LOCK部分為索引建立或删除時對表添加鎖的情況，可有的選擇為：

>> InnoDB存儲引擎實作Online DDL的原理是在執行建立或者删除操作的同時，将INSERT、UPDATE、DELETE這類DML記錄檔寫入到一個緩存中。待完成索引建立後再将重做應用到表上，以此達到資料的一緻性。這個緩存的大小由參數innodb_online_alter_log_max_size控制，預設的大小為128MB。若使用者更新的表比較大，并且在建立過程中伴有大量的寫事務，如遇到innodb_online_alter_log_max_size的空間不能存放日志時，會抛出類似如下的錯誤：

>> 對于這個錯誤，使用者可以調大參數innodb_online_alter_log_max_size，以此獲得更大的日志緩存空間。此外，還可以設定ALTER TABLE的模式為SHARE，這樣在執行過程中不會有寫事務發生，是以不需要進行DML日志的記錄。

>> 如果某個字段的取值範圍很廣，幾乎沒有重複，即屬于高選擇性，則此時使用B+樹索引是最适合的。例如，對于姓名字段，基本上在一個應用中不允許重名的出現。

>> 在InnoDB存儲引擎中，Cardinality統計資訊的更新發生在兩個操作中：INSERT和UPDATE。

>> 聯合索引的第二個好處是已經對第二個鍵值進行了排序處理。例如，在很多情況下應用程式都需要查詢某個使用者的購物情況，并按照時間進行排序，最後取出最近三次的購買記錄，這時使用聯合索引可以避免多一次的排序操作，因為索引本身在葉子節點已經排序了。

>> 正如前面所介紹的那樣，聯合索引（a，b）其實是根據列a、b進行排序，是以下列語句可以直接使用聯合索引得到結果：    SELECT ... FROM TABLE WHERE a=xxx ORDER BY b

>> InnoDB存儲引擎支援覆寫索引（covering index，或稱索引覆寫），即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。使用覆寫索引的一個好處是輔助索引不包含整行記錄的所有資訊，故其大小要遠小于聚集索引，是以可以減少大量的IO操作。

>> 覆寫索引的另一個好處是對某些統計問題而言的

>> 表buy_log有（userid，buy_date）的聯合索引，這裡隻根據列b進行條件查詢，一般情況下是不能進行該聯合索引的，但是這句SQL查詢是統計操作，并且可以利用到覆寫索引的資訊，是以優化器會選擇該聯合索引

>> 這是為什麼呢？原因在于使用者要選取的資料是整行資訊，而OrderID索引不能覆寫到我們要查詢的資訊，是以在對OrderID索引查詢到指定資料後，還需要一次書簽通路來查找整行資料的資訊。雖然OrderID索引中資料是順序存放的，但是再一次進行書簽查找的資料則是無序的，是以變為了磁盤上的離散讀操作。如果要求通路的資料量很小，則優化器還是會選擇輔助索引，但是當通路的資料占整個表中資料的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找資料。因為之前已經提到過，順序讀要遠遠快于離散讀。

>> 是以對于不能進行索引覆寫的情況，優化器選擇輔助索引的情況是，通過輔助索引查找的資料是少量的。這是由目前傳統機械硬碟的特性所決定的，即利用順序讀來替換随機讀的查找。若使用者使用的磁盤是固态硬碟，随機讀操作非常快，同時有足夠的自信來确認使用輔助索引可以帶來更好的性能，那麼可以使用關鍵字FORCE INDEX來強制使用某個索引

>> 是以，USE INDEX隻是告訴優化器可以選擇該索引，實際上優化器還是會再根據自己的判斷進行選擇。而如果使用FORCE INDEX的索引提示，如：

>> MySQL5.6版本開始支援Multi-Range Read（MRR）優化。Multi-Range Read優化的目的就是為了減少磁盤的随機通路，并且将随機通路轉化為較為順序的資料通路，這對于IO-bound類型的SQL查詢語句可帶來性能極大的提升。

>> MRR的工作方式如下：□ 将查詢得到的輔助索引鍵值存放于一個緩存中，這時緩存中的資料是根據輔助索引鍵值排序的。□ 将緩存中的鍵值根據RowID進行排序。□ 根據RowID的排序順序來通路實際的資料檔案。

>> 之前的MySQL資料庫版本不支援Index Condition Pushdown，當進行索引查詢時，首先根據索引來查找記錄，然後再根據WHERE條件來過濾記錄。在支援Index Condition Pushdown後，MySQL資料庫會在取出索引的同時，判斷是否可以進行WHERE條件的過濾，也就是将WHERE的部分過濾操作放在了存儲引擎層

>> 當優化器選擇Index Condition Pushdown優化時，可在執行計劃的列Extra看到Using index condition提示。

>> 自适應哈希索引采用之前讨論的哈希表的方式實作。不同的是，這僅是資料庫自身建立并使用的，DBA本身并不能對其進行幹預。自适應哈希索引經哈希函數映射到一個哈希表中，是以對于字典類型的查找非常快速

>> 全文檢索通常使用反向索引（inverted index）來實作。反向索引同B+樹索引一樣，也是一種索引結構。它在輔助表（auxiliary table）中存儲了單詞與單詞自身在一個或多個文檔中所在位置之間的映射。這通常利用關聯數組實作，其擁有兩種表現形式：

>> full inverted index還存儲了單詞所在的位置資訊，如code這個單詞出現在（1∶6），即文檔1的第6個單詞為code。相比之下，full inverted index占用更多的空間，但是能更好地定位資料，并擴充一些其他的搜尋特性。

>> InnoDB存儲引擎從1.2.x版本開始支援全文檢索的技術，其采用full inverted index的方式

>> 。在InnoDB存儲引擎中，将（DocumentId，Position）視為一個“ilist”。是以在全文檢索的表中，有兩個列，一個是word字段，另一個是ilist字段，并且在word字段上有設有索引

>> 正如之前所說的那樣，反向索引需要将word存放到一張表中，這個表稱為Auxiliary Table（輔助表）。在InnoDB存儲引擎中，為了提高全文檢索的并行性能，共有6張Auxiliary Table，目前每張表根據word的Latin編碼進行分區。

>> Auxiliary Table是持久的表，存放于磁盤上。然而在InnoDB存儲引擎的全文索引中，還有另外一個重要的概念FTS Index Cache（全文檢索索引緩存），其用來提高全文檢索的性能。FTS Index Cache是一個紅黑樹結構，其根據（word，ilist）進行排序。這意味着插入的資料已經更新了對應的表，但是對全文索引的更新可能在分詞操作後還在FTS Index Cache中，Auxiliary Table可能還沒有更新。InnoDB存儲引擎會批量對Auxiliary Table進行更新，而不是每次插入後更新一次Auxiliary Table

>> FTS Document ID是另外一個重要的概念。在InnoDB存儲引擎中，為了支援全文檢索，必須有一個列與word進行映射，在InnoDB中這個列被命名為FTS_DOC_ID，其類型必須是BIGINT UNSIGNED NOT NULL，并且InnoDB存儲引擎自動會在該列上加入一個名為FTS_DOC_ID_INDEX的Unique Index

>> 文檔中分詞的插入操作是在事務送出時完成，然而對于删除操作，其在事務送出時，不删除磁盤Auxiliary Table中的記錄，而隻是删除FTS Cache Index中的記錄

>> 由于文檔的DML操作實際并不删除索引中的資料，相反還會在對應的DELETED表中插入記錄，是以随着應用程式的允許，索引會變得非常大，即使索引中的有些資料已經被删除，查詢也不會選擇這類記錄。

>> 通過設定參數innodb_ft_aux_table來檢視分詞對應的資訊：

>> SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;

>> 目前InnoDB存儲引擎的全文檢索還存在以下的限制：□ 每張表隻能有一個全文檢索的索引。□ 由多列組合而成的全文檢索的索引列必須使用相同的字元集與排序規則。□ 不支援沒有單詞界定符（delimiter）的語言，如中文、日語、韓語等。

第6章 鎖

>> 鎖是資料庫系統差別于檔案系統的一個關鍵特性。鎖機制用于管理對共享資源的并發訪[插圖]

>> 2005版本，Microsoft SQL Server開始支援樂觀并發和悲觀并發，在樂觀并發下開始支援行級鎖，但是其實作方式與InnoDB存儲引擎的實作方式完全不同。使用者會發現在Microsoft SQL Server下，鎖是一種稀有的資源，鎖越多開銷就越大，是以它會有鎖更新。在這種情況下，行鎖會更新到表鎖，這時并發的性能又回到了以前。

>> latch一般稱為闩鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的性能會非常差。在InnoDB存儲引擎中，latch又可以分為mutex（互斥量）和rwlock（讀寫鎖）。其目的是用來保證并發線程操作臨界資源的正确性，并且通常沒有死鎖檢測的機制。

>> 對于InnoDB存儲引擎中的latch，可以通過指令SHOW ENGINE INNODB MUTEX來進行檢視

>> 相對于latch的檢視，lock資訊就顯得直覺多了。使用者可以通過指令SHOW ENGINE INNODB STATUS及information_schema架構下的表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS來觀察鎖的資訊

>> 如果一個事務T1已經獲得了行r的共享鎖，那麼另外的事務T2可以立即獲得行r的共享鎖，因為讀取并沒有改變行r的資料，稱這種情況為鎖相容（Lock Compatible）。但若有其他的事務T3想獲得行r的排他鎖，則其必須等待事務T1、T2釋放行r上的共享鎖——這種情況稱為鎖不相容。

>> S和X鎖都是行鎖，相容是指對同一記錄（row）鎖的相容性情況。

>> nnoDB存儲引擎支援意向鎖設計比較簡練，其意向鎖即為表級别的鎖。設計目的主要是為了在一個事務中揭示下一行将被請求的鎖類型。

>> 在InnoDB 1.0版本之前，使用者隻能通過指令SHOW FULL PROCESSLIST，SHOW ENGINE INNODB STATUS等來檢視目前資料庫中鎖的請求，然後再判斷事務鎖的情況。從InnoDB1.0開始，在INFORMATION_SCHEMA架構下添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通過這三張表，使用者可以更簡單地監控目前事務并分析可能存在的鎖問題

>> 在通過表INNODB_LOCKS檢視了每張表上鎖的情況後，使用者就可以來判斷由此引發的等待情況了。當事務較小時，使用者就可以人為地、直覺地進行判斷了。但是當事務量非常大，其中鎖和等待也時常發生，這個時候就不這麼容易判斷。但是通過表INNODB_LOCK_WAITS，可以很直覺地反映目前事務的等待

>> 一緻性的非鎖定讀（consistent nonlocking read）是指InnoDB存儲引擎通過行多版本控制（multi versioning）的方式來讀取目前執行時間資料庫中行的資料。如果讀取的行正在執行DELETE或UPDATE操作，這時讀取操作不會是以去等待行上鎖的釋放。相反地，InnoDB存儲引擎會去讀取行的一個快照資料。

>> 之是以稱其為非鎖定讀，因為不需要等待通路的行上X鎖的釋放。快照資料是指該行的之前版本的資料，該實作是通過undo段來完成。而undo用來在事務中復原資料，是以快照資料本身是沒有額外的開銷

>> 非鎖定讀機制極大地提高了資料庫的并發性。在InnoDB存儲引擎的預設設定下，這是預設的讀取方式，即讀取不會占用和等待表上的鎖。但是在不同僚務隔離級别下，讀取的方式不同，并不是在每個事務隔離級别下都是采用非鎖定的一緻性讀。此外，即使都是使用非鎖定的一緻性讀，但是對于快照資料的定義也各不相同。

>> 一個行記錄可能有不止一個快照資料，一般稱這種技術為行多版本技術。由此帶來的并發控制，稱之為多版本并發控制（Multi Version Concurrency Control，MVCC）。

>> 在事務隔離級别READ COMMITTED和REPEATABLE READ（InnoDB存儲引擎的預設事務隔離級别）下，InnoDB存儲引擎使用非鎖定的一緻性讀。然而，對于快照資料的定義卻不相同。在READ COMMITTED事務隔離級别下，對于快照資料，非一緻性讀總是讀取被鎖定行的最新一份快照資料。而在REPEATABLE READ事務隔離級别下，對于快照資料，非一緻性讀總是讀取事務開始時的行資料版本

>> 需要特别注意的是，對于READ COMMITTED的事務隔離級别而言，從資料庫理論的角度來看，其違反了事務ACID中的I的特性，即隔離性

>> 但是在某些情況下，使用者需要顯式地對資料庫讀取操作進行加鎖以保證資料邏輯的一緻性。而這要求資料庫支援加鎖語句，即使是對于SELECT的隻讀操作。InnoDB存儲引擎對于SELECT語句支援兩種一緻性的鎖定讀（locking read）操作：□ SELECT…FOR UPDATE□ SELECT…LOCK IN SHARE MODE

>> SELECT…FOR UPDATE對讀取的行記錄加一個X鎖，其他事務不能對已鎖定的行加上任何鎖。SELECT…LOCK IN SHARE MODE對讀取的行記錄加一個S鎖，其他事務可以向被鎖定的行加S鎖，但是如果加X鎖，則會被阻塞。

>> 對于一緻性非鎖定讀，即使讀取的行已被執行了SELECT…FOR UPDATE，也是可以進行讀取的，這和之前讨論的情況一樣。此外，SELECT…FOR UPDATE，SELECT…LOCK IN SHARE MODE必須在一個事務中，當事務送出了，鎖也就釋放了。

>> 插入操作會依據這個自增長的計數器值加1賦予自增長列。這個實作方式稱做AUTO-INC Locking。這種鎖其實是采用一種特殊的表鎖機制，為了提高插入的性能，鎖不是在一個事務完成後才釋放，而是在完成對自增長值插入的SQL語句後立即釋放

>> 雖然AUTO-INC Locking從一定程度上提高了并發插入的效率，但還是存在一些性能上的問題。首先，對于有自增長值的列的并發插入性能較差，事務必須等待前一個插入的完成（雖然不用等待事務的完成）。其次，對于INSERT…SELECT的大資料量的插入會影響插入的性能，因為另一個事務中的插入會被阻塞。

>> 從MySQL 5.1.22版本開始，InnoDB存儲引擎中提供了一種輕量級互斥量的自增長實作機制，這種機制大大提高了自增長值插入的性能。

>> 還需要特别注意的是InnoDB存儲引擎中自增長的實作和MyISAM不同，MyISAM存儲引擎是表鎖設計，自增長不用考慮并發插入的問題。是以在master上用InnoDB存儲引擎，在slave上用MyISAM存儲引擎的replication架構下，使用者必須考慮這種情況。

>> InnoDB存儲引擎有3種行鎖的算法，其分别是：□ Record Lock：單個行記錄上的鎖□ Gap Lock：間隙鎖，鎖定一個範圍，但不包含記錄本身□ Next-Key Lock∶Gap Lock+Record Lock，鎖定一個範圍，并且鎖定記錄本身

>> 當查詢的索引含有唯一屬性時，InnoDB存儲引擎會對Next-Key Lock進行優化，将其降級為Record Lock，即僅鎖住索引本身，而不是範圍。

>> InnoDB存儲引擎預設的事務隔離級别是REPEATABLE READ，在該隔離級别下，其采用Next-Key Locking的方式來加鎖。而在事務隔離級别READ COMMITTED下，其僅采用Record Lock，是以在上述的示例中，會話A需要将事務的隔離級别設定為READ COMMITTED。

>> 不可重複讀是指在一個事務内多次讀取同一資料集合。在這個事務還沒有結束時，另外一個事務也通路該同一資料集合，并做了一些DML操作。是以，在第一個事務中的兩次讀資料之間，由于第二個事務的修改，那麼第一個事務兩次讀到的資料可能是不一樣的。這樣就發生了在一個事務内兩次讀到的資料是不一樣的情況，這種情況稱為不可重複讀。

>> 不可重複讀和髒讀的差別是：髒讀是讀到未送出的資料，而不可重複讀讀到的卻是已經送出的資料，但是其違反了資料庫事務一緻性的要求。

>> 在MySQL官方文檔中将不可重複讀的問題定義為Phantom Problem，即幻像問題。

>> 丢失更新是另一個鎖導緻的問題，簡單來說其就是一個事務的更新操作會被另一個事務的更新操作所覆寫，進而導緻資料的不一緻。

>> 因為不同鎖之間的相容性關系，在有些時刻一個事務中的鎖需要等待另一個事務中的鎖釋放它所占用的資源，這就是阻塞。阻塞并不是一件壞事，其是為了確定事務可以并發且正常地運作。

>> 在InnoDB存儲引擎中，參數innodb_lock_wait_timeout用來控制等待的時間（預設是50秒），innodb_rollback_on_timeout用來設定是否在等待逾時時對進行中的事務進行復原操作（預設是OFF，代表不復原）。

Spring應該是會幫我們處理復原的。自己用JDBC寫我們捕獲異常後，一般也會手動復原。

>需要牢記的是，在預設情況下InnoDB存儲引擎不會復原逾時引發的錯誤異常。其實InnoDB存儲引擎在大部分情況下都不會對異常進行復原。

>> 解決死鎖問題最簡單的方式是不要有等待，将任何的等待都轉化為復原，并且事務重新開始。

>> 毫無疑問，這的确可以避免死鎖問題的産生。然而線上上環境中，這可能導緻并發性能的下降，甚至任何一個事務都不能進行。而這所帶來的問題遠比死鎖問題更為嚴重，因為這很難被發現并且浪費資源。

>> 解決死鎖問題最簡單的一種方法是逾時，即當兩個事務互相等待時，當一個等待時間超過設定的某一門檻值時，其中一個事務進行復原，另一個等待的事務就能繼續進行。在InnoDB存儲引擎中，參數innodb_lock_wait_timeout用來設定逾時的時間。

>> 是以，除了逾時機制，目前資料庫還都普遍采用wait-for graph（等待圖）的方式來進行死鎖檢測。較之逾時的解決方案，這是一種更為主動的死鎖檢測方式。InnoDB存儲引擎也采用的這種方式

>> 通過上述的介紹，可以發現wait-for graph是一種較為主動的死鎖檢測機制，在每個事務請求鎖并發生等待時都會判斷是否存在回路，若存在則有死鎖，通常來說InnoDB存儲引擎選擇復原undo量最小的事務。

有并發就有可能發生，跟開發的實作也有關系，但是機率也比較小，出現具體問題的時候再具體分析！

>如果程式是串行的，那麼不可能發生死鎖。死鎖隻存在于并發的情況，而資料庫本身就是一個并發運作的程式，是以可能會發生死鎖

>> 如果程式是串行的，那麼不可能發生死鎖。死鎖隻存在于并發的情況，而資料庫本身就是一個并發運作的程式，是以可能會發生死鎖。

理論上Spring應該也是做了這個判斷的…自己用JDBC實作的時候就要注意…

>還記得6.6節中所說的内容嗎？InnoDB存儲引擎并不會復原大部分的錯誤異常，但是死鎖除外。發現死鎖後，InnoDB存儲引擎會馬上復原一個事務，這點是需要注意的。是以如果在應用程式中捕獲了1213這個錯誤，其實并不需要對其進行復原。

>> Oracle資料庫中産生死鎖的常見原因是沒有對外鍵添加索引，而InnoDB存儲引擎會自動對其進行添加，因而能夠很好地避免了這種情況的發生

>> 此外還存在另一種死鎖，即目前事務持有了待插入記錄的下一個記錄的X鎖，但是在等待隊列中存在一個S鎖的請求，則可能會發生死鎖

>> 鎖更新（Lock Escalation）是指将目前鎖的粒度降低。舉例來說，資料庫可以把一個表的1000個行鎖更新為一個頁鎖，或者将頁鎖更新為表鎖。如果在資料庫的設計中認為鎖是一種稀有資源，而且想避免鎖的開銷，那資料庫中會頻繁出現鎖更新現象。Microsoft SQL Server資料庫的設計認為鎖是一種稀有的資源，在适合的時候會自動地将行、鍵或分頁鎖更新為更粗粒度的表級鎖。這種更新保護了系統資源，防止系統使用太多的記憶體來維護鎖，在一定程度上提高了效率。

>> InnoDB存儲引擎不存在鎖更新的問題。因為其不是根據每個記錄來産生行鎖的，相反，其根據每個事務通路的每個頁對鎖進行管理的，采用的是位圖的方式。是以不管一個事務鎖住頁中一個記錄還是多個記錄，其開銷通常都是一緻的。

>> 假設一張表有3000 000個資料頁，每個頁大約有100條記錄，那麼總共有300000 000條記錄。若有一個事務執行全表更新的SQL語句，則需要對所有記錄加X鎖。若根據每行記錄産生鎖對象進行加鎖，并且每個鎖占用10位元組，則僅對鎖管理就需要差不多需要3GB的記憶體。而InnoDB存儲引擎根據頁進行加鎖，并采用位圖方式，假設每個頁存儲的鎖資訊占用30個位元組，則鎖對象僅需90MB的記憶體。

第7章 事務

>> 事務（Transaction）是資料庫差別于檔案系統的重要特性之一。在檔案系統中，如果正在寫檔案，但是作業系統突然崩潰了，這個檔案就很有可能被破壞。當然，有一些機制可以把檔案恢複到某個時間點。不過，如果需要保證兩個檔案同步，這些檔案系統可能就顯得無能為力了。例如，在需要更新兩個檔案時，更新完一個檔案後，在更新完第二個檔案之前系統重新開機了，就會有兩個不同步的檔案。

>> 這正是資料庫系統引入事務的主要目的：事務會把資料庫從一種一緻狀态轉換為另一種一緻狀态。

>> 理論上說，事務有着極其嚴格的定義，它必須同時滿足四個特性，即通常所說的事務的ACID特性。值得注意的是，雖然理論上定義了嚴格的事務要求，但是資料庫廠商出于各種目的，并沒有嚴格去滿足事務的ACID标準。例如，對于MySQL的NDB Cluster引擎來說，雖然其支援事務，但是不滿足D的要求，即持久性的要求。對于Oracle資料庫來說，其預設的事務隔離級别為READ COMMITTED，不滿足I的要求，即隔離性的要求。雖然在大多數的情況下，這并不會導緻嚴重的結果，甚至可能還會帶來性能的提升，但是使用者首先需要知道嚴謹的事務标準，并在實際的生産應用中避免可能存在的潛在問題。對于InnoDB存儲引擎而言，其預設的事務隔離級别為READ REPEATABLE，完全遵循和滿足事務的ACID特性。

原子性就是說，事務中的兩個SQL肯定都應該是一起執行或者一起不執行，就是一個組合體，最小單元。

>A（Atomicity），原子性

>> 如果事務中的操作都是隻讀的，要保持原子性是很簡單的。一旦發生任何錯誤，要麼重試，要麼傳回錯誤代碼。因為隻讀操作不會改變系統中的任何相關部分。

事務送出以後，要麼成功，要麼失敗，失敗了就一定要復原。

>C（consistency），一緻性。一緻性指事務将資料庫從一種狀态轉變為下一種一緻的狀态

隔離性主要是處理和避免并發情況下出現的一些異常的問題。

>I（isolation），隔離性。隔離性還有其他的稱呼，如并發控制（concurrency control）、可串行化（serializability）、鎖（locking）等。

>> D（durability），持久性。事務一旦送出，其結果就是永久性的。即使發生當機等故障，資料庫也能将資料恢複。需要注意的是，隻能從事務本身的角度來保證結果的永久性。例如，在事務送出後，所有的變化都是永久的。即使當資料庫因為崩潰而需要恢複時，也能保證恢複後送出的資料都不會丢失。但若不是資料庫本身發生故障，而是一些外部的原因，如RAID卡損壞、自然災害等原因導緻資料庫發生問題，那麼所有送出的資料可能都會丢失。是以持久性保證事務系統的高可靠性（High Reliability），而不是高可用性（High Availability）。

>> 扁平事務的主要限制是不能送出或者復原事務的某一部分，或分幾個步驟送出。

>> 帶有儲存點的扁平事務（Flat Transactions with Savepoint），除了支援扁平事務支援的操作外，允許在事務執行過程中復原到同一事務中較早的一個狀态。這是因為某些事務可能在執行過程中出現的錯誤并不會導緻所有的操作都無效，放棄整個事務不合乎要求，開銷也太大

>> 鍊事務（Chained Transaction）可視為儲存點模式的一種變種。帶有儲存點的扁平事務，當發生系統崩潰時，所有的儲存點都将消失，因為其儲存點是易失的（volatile），而非持久的（persistent）。這意味着當進行恢複時，事務需要從開始處重新執行，而不能從最近的一個儲存點繼續執行。

>> 鍊事務的思想是：在送出一個事務時，釋放不需要的資料對象，将必要的處理上下文隐式地傳給下一個要開始的事務

>> 。注意，送出事務操作和開始下一個事務操作将合并為一個原子操作

>> 子事務既可以送出也可以復原。但是它的送出操作并不馬上生效，除非其父事務已經送出。是以可以推論出，任何子事物都在頂層事務送出後才真正的送出。

>> 樹中的任意一個事務的復原會引起它的所有子事務一同復原，故子事務僅保留A、C、I特性，不具有D的特性。

>> redo log稱為重做日志，用來保證事務的原子性和持久性。undo log用來保證事務的一緻性。

>> 有的DBA或許會認為undo是redo的逆過程，其實不然。redo和undo的作用都可以視為是一種恢複操作，redo恢複送出事務修改的頁操作，而undo復原行記錄到某個特定版本。是以兩者記錄的内容不同，redo通常是實體日志，記錄的是頁的實體修改操作。undo是邏輯日志，根據每行記錄進行記錄。

>> redo log基本上都是順序寫的，在資料庫運作時不需要對redo log的檔案進行讀取操作。而undo log是需要進行随機讀寫的。

>> 二進制日志隻在事務送出完成後進行一次寫入。而InnoDB存儲引擎的重做日志在事務進行中不斷地被寫入，這表現為日志并不是随事務送出的順序進行寫入的。

>> InnoDB存儲引擎在啟動時不管上次資料庫運作時是否正常關閉，都會嘗試進行恢複操作。因為重做日志記錄的是實體日志，是以恢複的速度比邏輯日志，如二進制日志，要快很多。與此同時，InnoDB存儲引擎自身也對恢複進行了一定程度的優化，如順序讀取及并行應用重做日志，這樣可以進一步地提高資料庫恢複的速度。

>> 使用者通常對undo有這樣的誤解：undo用于将資料庫實體地恢複到執行語句或事務之前的樣子——但事實并非如此。undo是邏輯日志，是以隻是将資料庫邏輯地恢複到原來的樣子。所有修改都被邏輯地取消了，但是資料結構和頁本身在復原之後可能大不相同。這是因為在多使用者并發系統中，可能會有數十、數百甚至數千個并發事務。資料庫的主要任務就是協調對資料記錄的并發通路。比如，一個事務在修改目前一個頁中某幾條記錄，同時還有别的事務在對同一個頁中另幾條記錄進行修改。是以，不能将一個頁復原到事務開始的樣子，因為這樣會影響其他事務正在進行的工作。

>> 當InnoDB存儲引擎復原時，它實際上做的是與先前相反的工作。對于每個INSERT，InnoDB存儲引擎會完成一個DELETE；對于每個DELETE，InnoDB存儲引擎會執行一個INSERT；對于每個UPDATE，InnoDB存儲引擎會執行一個相反的UPDATE，将修改前的行放回去。

>> 除了復原操作，undo的另一個作用是MVCC，即在InnoDB存儲引擎中MVCC的實作是通過undo來完成。當使用者讀取一行記錄時，若該記錄已經被其他事務占用，目前事務可以通過undo讀取之前的行版本資訊，以此實作非鎖定讀取。

>> 事務送出後并不能馬上删除undo log及undo log所在的頁。這是因為可能還有其他事務需要通過undo log來得到行記錄之前的版本。故事務送出時将undo log放入一個連結清單中，是否可以最終删除undo log及undo log所在頁由purge線程來判斷。

>> 若為每一個事務配置設定一個單獨的undo頁會非常浪費存儲空間，特别是對于OLTP的應用類型

>> 是以，在InnoDB存儲引擎的設計中對undo頁可以進行重用。具體來說，當事務送出時，首先将undo log放傳入連結表中，然後判斷undo頁的使用空間是否小于3/4，若是則表示該undo頁可以被重用，之後新的undo log記錄在目前undo log的後面

>> History list length就代表了undo log的數量，這裡為12。purge操作會減少該值。然而由于undo log所在的頁可以被重用，是以即使操作發生，History list length的值也可以不為0。

>> update undo log記錄的是對delete和update操作産生的undo log。該undo log可能需要提供MVCC機制，是以不能在事務送出時就進行删除

>> InnoSQL對information_schema進行了擴充，添加了兩張資料字典表，這樣使用者可以非常友善和快捷地檢視undo的資訊。首先增加的資料字典表為INNODB_TRX_ROLLBACK_SEGMENT。

>> 另一張資料字典表為INNODB_TRX_UNDO，用來記錄事務對應的undo log，友善DBA和開發人員詳細了解每個事務産生的undo量

>> 通過上面的例子可以看到，delete操作并不直接删除記錄，而隻是将記錄标記為已删除，也就是将記錄的delete flag設定為1。而記錄最終的删除是在purge操作中完成的。

>> 全局動态參數innodb_purge_batch_size用來設定每次purge操作需要清理的undo page數量。在InnoDB1.2之前，該參數的預設值為20。而從1.2版本開始，該參數的預設值為300。通常來說，該參數設定得越大，每次回收的undo page也就越多，這樣可供重用的undo page就越多，減少了磁盤存儲空間與配置設定的開銷。不過，若該參數設定得太大，則每次需要purge處理更多的undo page，進而導緻CPU和磁盤IO過于集中于對undo log的處理，使性能下降。是以對該參數的調整需要由有經驗的DBA來操作，并且需要長期觀察資料庫的運作的狀态。正如官方的MySQL資料庫手冊所說的，普通使用者不需要調整該參數。

>> 為了提高磁盤fsync的效率，目前資料庫都提供了group commit的功能，即一次fsync可以重新整理確定多個事務日志被寫入檔案。對于InnoDB存儲引擎來說，事務送出時會進行兩個階段的操作：1）修改記憶體中事務對應的資訊，并且将日志寫入重做日志緩沖。2）調用fsync将確定日志都從重做日志緩沖寫入磁盤。

>> COMMIT和COMMIT WORK語句基本是一緻的，都是用來送出事務。不同之處在于COMMIT WORK用來控制事務結束後的行為是CHAIN還是RELEASE的。如果是CHAIN方式，那麼事務就變成了鍊事務。

>> 使用者可以通過參數completion_type來進行控制，該參數預設為0，表示沒有任何操作。在這種設定下COMMIT和COMMIT WORK是完全等價的。當參數completion_type的值為1時，COMMIT WORK等同于COMMIT AND CHAIN，表示馬上自動開啟一個相同隔離級别的事務，

>> 參數completion_type為2時，COMMIT WORK等同于COMMIT AND RELEASE。在事務送出後會自動斷開與伺服器的連接配接

>> TRUNCATE TABLE語句是DDL，是以雖然和對整張表執行DELETE的結果是一樣的，但它是不能被復原的（這又是和Microsoft SQL Server資料不同的地方）。

>> 計算TPS的方法是（com_commit+com_rollback）/time。但是利用這種方法進行計算的前提是：所有的事務必須都是顯式送出的，如果存在隐式地送出和復原（預設autocommit=1），不會計算到com_commit和com_rollback變量中。

>> 隔離級别越低，事務請求的鎖越少或保持鎖的時間就越短。這也是為什麼大多數資料庫系統預設的事務隔離級别是READ COMMITTED。

>> 據了解，大部分的使用者質疑SERIALIZABLE隔離級别帶來的性能問題，但是根據Jim Gray在《Transaction Processing》一書中指出，兩者的開銷幾乎是一樣的，甚至SERIALIZABLE可能更優!!!是以在InnoDB存儲引擎中選擇REPEATABLE READ的事務隔離級别并不會有任何性能的損失

>> 因為InnoDB存儲引擎在REPEATABLE READ隔離級别下就可以達到3°的隔離，是以一般不在本地事務中使用SERIALIABLE的隔離級别。SERIALIABLE的事務隔離級别主要用于InnoDB存儲引擎的分布式事務。

>> XA事務允許不同資料庫之間的分布式事務，如一台伺服器是MySQL資料庫的，另一台是Oracle資料庫的，又可能還有一台伺服器是SQL Server資料庫的，隻要參與在全局事務中的每個節點都支援XA事務

>> 在單個節點上運作分布式事務沒有太大的實際意義，但是要在MySQL資料庫的指令下示範多個節點參與的分布式事務也是行不通的。通常來說，都是通過程式設計語言來完成分布式事務的操作的。目前Java的JTA（Java Transaction API）可以很好地支援MySQL的分布式事務，需要使用分布式事務應該認真參考其API

>> 最為常見的内部XA事務存在于binlog與InnoDB存儲引擎之間

>> 對于不同語言的API，自動送出是不同的。MySQL C API預設的送出方式是自動送出，而MySQL Python API則會自動執行SET AUTOCOMMIT=0，以禁用自動送出。是以在選用不同的語言來編寫資料庫應用程式前，應該對連接配接MySQL的API做好研究。

>> 就像之前小節中所講到的，對事務的BEGIN、COMMIT和ROLLBACK操作應該交給程式端來完成，存儲過程需要完成的隻是一個邏輯的操作，即對邏輯進行封裝。

>> 長事務（Long-Lived Transactions），顧名思義，就是執行時間較長的事務。比如，對于銀行系統的資料庫，每過一個階段可能需要更新對應賬戶的利息。如果對應賬号的數量非常大，例如對有1億使用者的表account，需要執行下列語句

>> 在執行過程中，當資料庫或作業系統、硬體等發生問題時，重新開始事務的代價變得不可接受。資料庫需要復原所有已經發生的變化，而這個過程可能比産生這些變化的時間還要長。是以，對于長事務的問題，有時可以通過轉化為小批量（mini batch）的事務來進行處理。當事務發生錯誤時，隻需要復原一部分資料，然後接着上次已完成的事務繼續進行

>> 上述代碼将一個需要處理1億使用者的大事務分解為每次處理10萬使用者的小事務，通過批量處理小事務來完成大事務的邏輯。每完成一個小事務，将完成的結果存放在batchcontext表中，表示已完成批量事務的最大賬号ID。

第8章 備份與恢複

>> 按照備份後檔案的内容，備份又可以分為：□ 邏輯備份□ 裸檔案備份在MySQL資料庫中，邏輯備份是指備份出的檔案内容是可讀的，一般是文本檔案。内容一般是由一條條SQL語句，或者是表内實際資料組成。如mysqldump和SELECT*INTO OUTFILE的方法。這類方法的好處是可以觀察導出檔案的内容，一般适用于資料庫的更新、遷移等工作。但其缺點是恢複所需要的時間往往較長。裸檔案備份是指複制資料庫的實體檔案，既可以是在資料庫運作中的複制（如ibbackup、xtrabackup這類工具），也可以是在資料庫停止運作時直接的資料檔案複制。這類備份的恢複時間往往較邏輯備份短很多。

>> 對于MySQL資料庫來說，官方沒有提供真正的增量備份的方法，大部分是通過二進制日志完成增量備份的工作。這種備份較之真正的增量備份來說，效率還是很低的

>> 最後，任何時候都需要做好遠端異地備份，也就是容災的防範。隻是同一機房的兩台伺服器的備份是遠遠不夠的。我曾經遇到的情況是，公司在2008年的汶川地震中發生一個機房可能被淹的的情況，這時遠端異地備份顯得就至關重要了。

>> replication的工作原理分為以下3個步驟：1）主伺服器（master）把資料更改記錄到二進制日志（binlog）中。2）從伺服器（slave）把主伺服器的二進制日志複制到自己的中繼日志（relay log）中。3）從伺服器重做中繼日志中的日志，把更改應用到自己的資料庫上，以達到資料的最終一緻性。

>> 之前已經說過MySQL的複制是異步實時的，并非完全的主從同步。若使用者要想得知目前的延遲，可以通過指令SHOW SLAVE STATUS和SHOW MASTER STATUS得知，如：

>> 假設目前應用采用了主從的複制架構，從伺服器作為備份。這時，一個初級DBA執行了誤操作，如DROP DATABASE或DROP TABLE，這時從伺服器也跟着運作了。這時使用者怎樣從伺服器進行恢複呢？是以，一個比較好的方法是通過對從伺服器上的資料庫所在分區做快照，以此來避免誤操作對複制造成影響。當發生主伺服器上的誤操作時，隻需要将從伺服器上的快照進行恢複，然後再根據二進制日志進行point-in-time的恢複即可。

>> 還有一些其他的方法來調整複制，比如采用延時複制，即間歇性地開啟從伺服器上的同步，保證大約一小時的延時。這的确也是一個方法，隻是資料庫在高峰和非高峰期間每小時産生的二進制日志量是不同的，使用者很難精準地控制。另外，這種方法也不能完全起到對誤操作的防範作用。

>> 建議在從服務上啟用read-only選項，這樣能保證從伺服器上的資料僅與主伺服器進行同步，避免其他線程修改資料

第9章 性能調優

>> 另一方面，閃存中的資料是不可以更新的，隻能通過扇區（sector）的覆寫重寫，而在覆寫重寫之前，需要執行非常耗時的擦除（erase）操作。擦除操作不能在所含資料的扇區上完成，而需要在删除整個被稱為擦除塊的基礎上完成，這個擦除塊的尺寸大于扇區的大小，通常為128KB或者256KB。此外，每個擦除塊有擦寫次數的限制。已經有一些算法來解決這個問題

>> 因為存在上述寫入方面的問題，閃存提供的讀寫速度是非對稱的。讀取速度要遠快于寫入的速度，是以對于固态硬碟在資料庫中的應用，應該好好利用其讀取的性能，避免過多的寫入操作。

>> 由于将多個硬碟組合成為一個邏輯扇區，RAID看起來就像一個單獨的硬碟或邏輯存儲單元，是以作業系統隻會把它當作一個硬碟。

>> RAID 5具有和RAID 0相近似的資料讀取速度，隻是多了一個奇偶校驗資訊，寫入資料的速度相當慢，若使用Write Back可以讓性能改善不少。

>> RAID 01比RAID 10有着更快的讀寫速度，不過也多了一些會讓整個硬碟組停止運轉的幾率，因為隻要同一組的硬碟全部損毀，RAID 01就會停止運作，而RAID 10可以在犧牲RAID 0的優勢下正常運作。RAID 10巧妙地利用了RAID 0的速度及RAID 1的安全（保護）兩種特性，它的缺點是需要較多的硬碟，因為至少必須擁有四個以上的偶數硬碟才能使用。

>> RAID Write Back功能是指RAID控制器能夠将寫入的資料放入自身的緩存中，并把它們安排到後面再執行。這樣做的好處是，不用等待實體磁盤實際寫入的完成，是以寫入變得更快了

>> 對RAID卡進行配置可以在伺服器啟動時進入一個類似于BIOS的配置界面，然後再對其進行各種設定。此外，很多廠商都開發了各種作業系統下的軟體對RAID進行配置，如果使用者使用的是LSI公司生産提供的RAID卡，則可以使用MegaCLI工具來進行配置。

>> 特别需要注意地是，當RAID卡的寫入政策從Write Back切換為Write Through時，該更改立即生效。然而從Write Through切換為Write Back時，必須重新開機伺服器才能使其生效。

>> 基準測試工具可以用來對資料庫或作業系統調優後的性能進行對比。MySQL資料庫本身提供了一些比較優秀的工具，這裡将介紹另外兩款更為優秀和常用的基準測試工具：sysbench和mysql-tpcc。

>> 對于MySQL資料庫的OLTP測試，和fileio一樣需要經曆prepare、run和cleanup階段。prepare階段會根據選項産生一張指定行數的表，預設表在sbtest架構下，表名為sbtest（sysbench預設生成表的存儲引擎為InnoDB）。例如建立一張8000W的表：

>> TPC（Transaction Processing Performance Council，事務處理性能協會）是一個用來評價大型資料庫系統軟硬體性能的非盈利組織。TPC-C是TPC協會制定的，用來測試典型的複雜OLTP（線上事務處理）系統的性能。目前在學術界和工業界普遍采用TPC-C來評價OLTP應用的性能。