Schema與資料類型優化
選擇優化的資料類型
有幾個簡單的原則：
更小的通常更好
一般情況下使用可以正确存儲資料的最小資料類型。
簡單的更好
例如整型比字元操作代價更低。應當使用Mysql的日期類型而不是字元串，應當用整型存儲IP位址
盡量避免NULL
查詢中如果包含NULL的列，對于Mysql來說更難優化，這樣使得索引，索引統計，值都比較複雜。NULL的列會使用更多的存儲空間，在Mysql裡也需要特殊處理。當可為NULL的列被索引時，每個索引記錄需要一個額外的位元組
整數類型
無符号的數字上限可以提高一倍
為整數類型指定寬度，如INT(11)，不會限制值的合法範圍，隻是規定了Mysql的一些互動工具(指令行或用戶端)用來顯示的字元個數。對于存儲和計算來講，int(1) 和 int(20)是相同的。
實數類型
浮點類型在存儲同樣範圍的值時，通常比Decimal使用更少的空間，Float使用4個位元組，Double使用8個位元組相比Float有更高的精度和更大的範圍。這裡能選擇的是存儲類型，Mysql内部使用Double作為内部浮點計算的類型。
字元串類型
CHAR和VARCHAR
VARCHAR節省了存儲空間，如果行占用存儲空間增長，并且在頁内沒有更多的空間存儲，MyISAM拆成不同的片段存儲，InnoDB則需要分裂頁來使行可以放進頁内。
下列情況使用Varchar是合适的：
字元串最大長度比平均長度大很多；
列的更新很少，是以碎片不是問題；
使用了UTF-8字元集，每個字元都使用不同的位元組數進行存儲。
InnoDB把過長的VARCHAR存儲為BLOB
CHAR是定長的，會删除末尾的空格。CHAR(1)需要一個位元組，VARCHAR(1)需要2個位元組，因為還需要多一個位元組存儲長度。
類似的還有BINNARY和VARBINARY，填充使用的\0(0位元組)
BLOB和TEXT
都是為了存儲很大的資料設計的字元串資料類型，分别采用二進制和字元方式存儲。不同在于BLOB存儲的是二進制資料，沒有排序規則或者字元集。
排序也隻是對每個列的max_sort_length位元組而不是整個字元串排序。
查詢如果涉及BLOB，伺服器不能在記憶體臨時表中存儲BLOB，必須要使用磁盤臨時表，無論它多小。
日期和時間類型
DATETIME可以存儲1001到9999年，精度為秒，與時區無關，使用8個位元組的存儲空間。TIMESTAMP儲存了1970年1月1日以來的秒數。隻使用4個位元組的存儲空間。從1970到2038年。
位資料類型
這些類型，不管底層存儲格式和處理方式如何，從技術上來說都是字元串類型。
BIT
5.0之前BIT是TINYINT的同義詞。之後則完全不同。MyISAM會打包所有的BIT列，InnoDB和Memory使用足夠存儲最小整數類型來存放BIT，是以不能節省存儲空間。Mysql把BIT當作字元串類型而不是數字，會造成一些混亂。例如 a bit(8)，值為b'00111001'二進制等于57(ascii顯示值等于9)，a=9，a+0=57。應該謹慎使用，如果想存儲true/false，可以使用CHAR(0)
選擇辨別符(identifier)
整數類型是最好的選擇，很快并且可以使用AUTO_INCREMENT。避免使用字元串作為辨別列，很耗空間，通常比數字類型慢，MyISAM預設對字元串使用壓縮索引，會導緻查詢慢很多。
随機值如MD5，SHA1，UUID會導緻INSERT和一些SELECT語句變慢，因為可能導緻随機寫入索引不同位置，導緻頁分裂，磁盤随機通路，對于聚簇存儲引擎産生聚簇索引碎片。
SELECT語句變慢因為邏輯上相鄰的行會分布在磁盤和記憶體的不同地方。
随機值導緻緩存對所有類型的查詢語句效果都很差。
Scheme設計中的陷阱
太多的列
Mysql的存儲引擎API工作時需要在伺服器層和存儲引擎層之間通過行緩沖格式拷貝資料，然後在伺服器層将緩沖内容解碼成各個列。從行緩沖中将編碼過的列轉換成行資料結構的操作代價是非常高的。非常寬的表可能會使得CPU占用非常高。
太多的關聯
“實體-屬性-值”(EAV)設計模式在Mysql下不能靠譜的工作，限制了每個關聯操作最多隻能有61張表。單個查詢最好在12個表内做關聯。
全能的枚舉
枚舉清單增加資料需要使用到ALTER TABLE，若不是加在最後可能會有影響
變相的枚舉
範式和反範式
在範式化的資料庫中，每個事實資料會出現并且隻出現一次，相反，在反範式化的資料庫中，資訊是備援的。
第一範式
確定資料表中每列(字段)的原子性。
如果資料表中每個字段都是不可再分的最小資料單元，則滿足第一範式。
例如：user使用者表，包含字段id,username,password
第二範式
在第一範式的基礎上更進一步，目标是確定表中的每列都和主鍵相關。
如果一個關系滿足第一範式，并且除了主鍵之外的其他列，都依賴于該主鍵，則滿足第二範式。
例如：一個使用者隻有一種角色，而一個角色對應多個使用者。則可以按如下方式建立資料表關系，使其滿足第二範式。
user使用者表，字段id,username,password,role_id
role角色表，字段id,name
使用者表通過角色id(role_id)來關聯角色表
第三範式
在第二範式的基礎上更進一步，目标是確定表中的列都和主鍵直接相關，而不是間接相關。
例如：一個使用者可以對應多個角色，一個角色也可以對應多個使用者。則可以按如下方式建立資料表關系，使其滿足第三範式。
user使用者表，字段id,username,password
role角色表，字段id,name
user_role使用者-角色中間表，id,user_id,role_id
像這樣，通過第三張表(中間表)來建立使用者表和角色表之間的關系，同時又符合範式化的原則，就可以稱為第三範式。
反範式化
反範式化指的是通過增加備援或重複的資料來提高資料庫的讀性能。
例如：在上例中的user_role使用者-角色中間表增加字段role_name。
反範式化可以減少關聯查詢時，join表的次數。
範式的優點
範式化的更新操作更快
更新需要變更的資料更少
表比較小，可以更好放在記憶體裡
缺點是通常需要關聯，代價相對昂貴，也可能使得一些索引政策無效。
反範式的優點
避免關聯
查詢相對高效(當索引合理)
建立高性能索引
索引可以包含一個或多個列，如果索引包含多個列，那列的順序也十分重要，因為Mysql隻能最高效的使用索引的最左字首列。
B-Tree的索引列是順序組織存儲的，很适合查找範圍資料。适用于全鍵值、鍵值範圍或鍵字首查找。
紅黑樹是一種含有紅黑結點并能自平衡的二叉查找樹。它必須滿足下面性質：
性質1：每個節點要麼是黑色，要麼是紅色。
性質2：根節點是黑色。
性質3：每個葉子節點(NIL)是黑色。
性質4：每個紅色結點的兩個子結點一定都是黑色。
性質5：任意一結點到每個葉子結點的路徑都包含數量相同的黑結點。
從性質5又可以推出：
性質5.1：如果一個結點存在黑子結點，那麼該結點肯定有兩個子結點
哈希索引(hash index)隻有精确比對索引所有列的查詢才有效。隻包含哈希值和行指針，不存儲字段值，是以不能避免讀取行。
并不是按照索引值順序存儲，是以無法用于排序。
也不支援部分索引列比對查找。隻支援等值查詢，不支援範圍查詢。
高性能的索引政策
獨立的列才能使用到索引，列不能使用操作符或者表達式
多列索引，當使用到多個單列索引時，會進行多個索引的聯合操作(索引合并)
選擇合适的索引列順序
正确的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要。
在一個多列B-Tree索引中，索引列的順序意味着索引首先按照最左列進行排序，其次是第二列。
聚簇索引
并非一種單獨的索引類型，而是一種資料存儲方式。InnoDB在同一個結構中儲存了B-Tree索引和資料行。
InnoDB使用主鍵聚集資料，如果沒有定義主鍵，會選擇一個唯一的非空索引代替，如果沒有這樣的索引，會隐式定義一個主鍵作為聚簇索引。InnoDB隻聚集同一個頁面的記錄。
優點：
把相關資料儲存再一起。
資料通路更快
使用覆寫索引掃描的查詢可以直接使用節點中的主鍵值。
缺點：
插入速度依賴于插入順序，如果不是按照主鍵加載資料，加載完成後最好使用OPTIMIZE TABLE重新組織表
更新聚簇索引的代價很高，因為會将被更新的行移動到新位置
插入新航或者主鍵更新需要移動行時，可能面臨“頁分裂(Page Split)”問題
可能導緻全表掃描變慢，尤其是行比較稀疏
二級索引(非聚簇索引)可能比想象的要更大，因為葉子節點包含了引用行的主鍵列。
二級索引需要兩次索引查找，而不是一次
覆寫索引
如果索引的葉子節點中已經包含要查詢的資料，那麼還有什麼必要再回表查詢呢？是以一個索引包含(或者覆寫)所有需要查詢的字段的值，我們就稱之為覆寫索引。
索引排序
隻有索引的列順序和orderby的順序完全一緻，并且列的正序，逆序都一樣時，才能使用索引對結果進行排序。如果查詢需要關聯多張表，則隻有當orderby的引用字段全部為第一個表時，才能使用索引進行排序。
索引和資料的碎片化
B-Tree索引可能會碎片化。
表的資料存儲也可能碎片化：
行碎片
這種碎片指的時資料行被存儲到多個地方的多個片段中。即使隻查詢一行記錄，也會導緻性能下降。
行間碎片
邏輯上順序的頁，或者行再磁盤上不是順序存儲的。行間碎片對諸如全表掃描和聚簇索引掃描之類的操作有很大影響。
剩餘空間碎片
指資料頁中有大量的空餘空間，會導緻伺服器讀取大量不需要的資料造成浪費。
查詢性能優化
查詢的聲明周期大緻按照順序：
從用戶端，到伺服器，然後在伺服器上進行解析，生成執行計劃，執行，并傳回結果給用戶端。執行時最重要的階段，包含了大量為檢索資料到存儲引擎的調用以及調用後的資料處理，包括排序，分組等。
慢查詢基礎：優化資料通路
是否請求了不需要的資料
查詢不需要的記錄
查詢不需要的列 (多表關聯 * )
總是取出全部列(select * )
重複查詢相同的資料
是否在掃描額外的記錄
衡量查詢開銷的三個名額如下：
響應時間
掃描的行數
傳回的行數
響應時間是 服務時間 和 排隊時間 之和。
掃描的行數和傳回的行數理想情況下應該是相同的，一般在1:1到10:1之間
掃描的行數和通路類型：在EXPAIN語句中的type列反應了通路類型。通路類型有很多中，包括全表掃描，索引掃描，範圍掃描，唯一索引查詢，常數引用等。這些速度是從慢到快，掃描行數也是從多到少。
重構查詢的方式
一個複雜查詢還是多個簡單查詢
Mysql支援多個簡單查詢，一個通用伺服器上可以支援每秒10萬的查詢，一個千兆網卡滿足每秒2000次的查詢。Mysql内部每秒能掃描記憶體中上百萬行資料，相比之下響應資料給用戶端就慢得多了
切分查詢
将一個大查詢分而治之，例如一個删除大量資料的語句，拆分為多個小的删除。
分解關聯查詢
有很多好處：
讓緩存的效率更高。無論是應用程式的緩存和Mysql的緩存，都會在單表的情況下更容易命中。
查詢分解後減少了鎖的競争
應用層關聯，更容易對資料庫進行拆分，做到高性能和可擴充
減少備援記錄的查詢
在應用中實作的哈希關聯，而不是使用Mysql的嵌套查詢。
執行查詢的基礎
執行查詢的過程：
用戶端發送一條查詢給伺服器
伺服器先檢查緩存，如果命中了緩存，則立刻傳回存儲在緩存中的結果。否則進入下一個階段。
伺服器進行SQL解析，預處理，再由優化器生成對應的執行計劃。
Mysql根據優化器生成的執行計劃，調用存儲引擎的API執行查詢。
将結果傳回給用戶端
Mysql用戶端/伺服器通信協定
通信協定是“半雙工”的，意味着任何一個時刻，要麼是服務端向用戶端發送資料，要麼是用戶端向服務端發送資料。這種協定讓MySQL通信簡單快速。但是也意味着沒法進行流量控制，一旦一端開始發送消息，另一端要完整接收完整個消息才能響應它。用戶端用一個單獨的資料包将查詢傳給伺服器，是以查詢語句特别長的時候，參數max_allowed_packet特别重要。
查詢狀态
最簡單使用SHOW FULL PROCESSLIST檢視目前狀态，狀态值有如下幾種：
Sleep：線程正在等待用戶端發送新的請求。
Query：線程正在執行查詢或者将查詢結果傳回用戶端。
Locked：伺服器層線程等待表鎖。在存儲引擎基本實作的鎖，例如InnoDB的行所，不會展現線上程狀态中。
Analyzing and statistics：線程收集存儲引擎的統計資訊，并生成查詢的執行計劃。
Copying to tmp table [on disk]：線程執行查詢，并将其結果集複制到一個臨時表中，這種狀态一般要麼是做GROUP BY操作，或者檔案排序操作，或者UNION操作。如果後面有“on disk”标記表示MySQL将記憶體臨時表放到磁盤上。
Sorting result：線程在對結果集排序。
Sending data：線程可能在多個狀态之間傳送資料，或者在生成結果集，或者在向用戶端傳回資料。
查詢緩存
檢查緩存是通過一個對大小寫敏感的哈希查找實作的。查詢和緩存中的查詢即使隻有一個位元組不同頁不會比對，如果命中在傳回結果集之前MySQL會檢查一次使用者權限，這是無需解析SQL的，因為查詢緩存中有儲存目前查詢需要的表資訊。
查詢優化處理
文法解析器和預處理
MySQL通過關鍵字将SQL語句解析，生成文法解析樹，使用MySQL文法規則驗證和解析查詢。例如是否使用了錯誤的關鍵字，關鍵字順序是否正确，引号前後是否正确比對。
預處理根據MySQL規則進一步檢查解析樹是否合法。例如資料表、列是否存在，名字和别名是否有歧義。
下一步預處理器會驗證權限。
查詢優化器
文法樹已經合法，優化器将其轉為了執行計劃。優化器作用就是找到最好的執行計劃。
可以通過查詢目前回話的Last_query_cost的值來得知MySQL計算目前查詢成本。
根據一系列統計資訊計算得來：每個表或者索引的頁面個數，索引的基數(索引中不同值的數量)，索引和資料行的長度，索引分布的情況。
優化器在評估成本的時候不考慮任何緩存，假設讀取任何資料都需要一次磁盤IO
MySQL的查詢優化器是一個複雜部件，使用了很多優化的執行政策。優化政策簡單分為兩種：靜态優化和動态優化。
靜态優化直接對解析樹進行優化，靜态優化在第一次萬能充後就一直有效，使用不同參數執行查詢頁不會發生變化，可以認為是一種“編譯時優化”。
動态優化和查詢的上下文有關，例如WHERE條件中的取值、索引中條目對應的資料行數等。可以認為時“運作時優化”。
MySQL能夠處理的優化類型：
重新定義關聯表的順序：資料表的關聯并不總是按照查詢中指定的順序執行
将外連接配接轉為内連接配接：MySQL識别并重寫查詢，讓其可以調整關聯順序。
使用等價變化規則：通過等價變換來簡化并規範表達式。合并減少一些比較，一定一些恒等或者恒不等的判斷。
優化Count() Max() Min()：min和max可以直接查詢b-tree的最左或者最右端。
預估并轉化位常數表達式：
覆寫索引掃描
子查詢優化；某些情況下可以将子查詢轉換為效率更高的形式
提前終止查詢：在發現已經滿足查詢需求的時候，MySQL總是能夠立刻終止查詢。
等值傳播：兩個列的值通過等值關聯，MySQL能夠傳遞where條件。
清單in()的比較：MySQL将in()清單中的資料先進行排序，然後通過二分查找的方式來确定清單中的值是否滿足條件。這是一個O(log n)的操作。等價轉換為Or的複雜度時O(n)。
MySQL執行關聯查詢
MySQL先從一個表中循環取出單條資料，在嵌套循環到下一個表中尋找比對的行，依次直到找到所有表中比對的行，然後根據各個表比對的行傳回查詢中需要的各個列。MySQL會嘗試在最後一個關聯表中找到所有比對的行，如果不行就傳回上一層次關聯表。
MySQL多表關聯的指令樹時一顆左側深度優先的樹。
關聯查詢優化器
MySQL的最優執行計劃中的關聯表的順序，通過預估需要讀取的資料頁來選擇，讀取的資料頁越少越好。
關聯順序的調整，可能會讓查詢進行更少的嵌套循環和回溯操作。
可以使用STRAIGHT_JOIN關鍵字重寫查詢，讓優化器按照查詢順序執行。
排序優化
排序時成本很高的操作，從性能角度考慮，應該盡量避免排序，或者避免對大量資料進行排序。
當不能用索引生成排序結果時，MySQL需要位元組進行排序，如果資料量小使用記憶體，資料量大使用磁盤。不過統一都稱為檔案排序(filesort)。
MySQL有兩種排序算法：
兩次傳輸排序(舊版本)：讀取指針和需要排序的字段，排序之後，再根據排序結果讀取所需要的資料行。第二次讀取資料的時候可能産生大量随機IOS，成本很高，不過在排序時加載的資料較少，是以在記憶體中就可以讀取更多的行數進行排序。
單次傳輸排序(新版本)：查詢所有需要列，根據給定列進行排序直接傳回結果。在MySQL4.1之後引入。
查詢執行引擎
查詢執行階段就根據執行計劃，調用存儲引擎的實作接口來完成。
查詢結果傳回時，即使不需要傳回結果集給用戶端，MySQL傳回查詢資訊，例如影響到的行數。
查詢優化的局限性
關聯子查詢(in+子查詢)
使用join，或者使用函數GROUP_CONCAT()在in中構造一個由分好分隔的清單，有時候比關聯更快，in加子查詢性能糟糕，一般建議使用exists等效改寫。
優化特定類型的查詢
優化count查詢
MyISAM的count函數非常快，隻有在沒有條件的前提下。
近似值：某些不需要精确值的情況下，可以使用EXPLAIN出來的優化器估算行數。
優化關聯查詢
確定on或者using子句中的列上有索引。
確定任何的group by和order by中的表達式隻設計一個表中的列，這樣MySQL才有可能使用索引來優化過程
優化子查詢
在5.6之前盡量轉換使用join，5.6之後沒有太多差别
優化group by和distinct
groupby 使用主鍵列效率更高。
優化limit
“延遲關聯”，首先使用索引覆寫來選取範圍内的主鍵，接下來根據這些主鍵擷取對應資料。
分區表
分區表限制：
一個表最多隻能有1024個分區
5.1中分區表達式必須是整數，或者是傳回整數的表達式。5.5中某些場景可以直接使用列進行分區。
如果分區字段中有主鍵或者唯一索引列，那麼所有的主鍵列和唯一索引列都必須包含進來。
分區表中無法使用外鍵限制。
在資料量超大的時候B-Tree就無法起作用了，除非是索引覆寫查詢，否則資料庫伺服器需要根據索引掃描的結果回表，查詢所有符合條件的記錄。如果資料量巨大，這将産生大量随機IO，資料庫的響應時間将大到不可接受的程度。
MySQL優化伺服器配置
MySQL配置的工作原理
MySQL從 指令行參數和配置檔案中擷取配置資訊。配置檔案一般是在 /etc/my.cnf 或 /etc/mysql/my.cnf。
确認配置檔案路徑，可以使用下列指令
$ which mysql
/bin/mysql
$/bin/mysql --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
配置檔案分為多個部分，每個部分的開頭是用方括号括起來的分段名稱。用戶端會讀取client部分，伺服器通常讀取mysqld部分。
配置項都使用小鞋，單次之間用下劃線或者橫線隔開。
常用變量及其效果
key_buffer_size
一次性為鍵緩沖區(key buffer)配置設定所有的指定空間。作業系統會在使用時才真正配置設定。
table_cache_size
這個變量會等到下次有線程打開表才有效果，會變更緩存中表的數量。
thread_cache_size
MySQL隻有再關閉連接配接時才在緩存中增加線程，隻在建立新連接配接時才從緩存中删除線程。
query_cache_size
修改這個變量會立刻删除所有緩存的查詢，重新配置設定這片緩存到指定大小，并且重新初始化記憶體。
read_buffer_size
MySQL隻會在查詢需要使用時才會為該緩存配置設定記憶體，并且一次性配置設定該參數指定大小的全部記憶體。
read_rnd_buffer_size
MySQL隻會在查詢需要使用時才會為該緩存配置設定記憶體，并且隻會配置設定該參數需要大小的記憶體。
sort_buffer_size
MySQL隻會在查詢排序需要使用時才會為該緩存配置設定記憶體，并且一次性配置設定該參數指定大小的全部記憶體，不管排序是否需要這想·麼大的記憶體。
InnoDB事務日志
InnoDB使用日志來減少送出事務時的開銷。因為日志中已經記錄了事務，無需在每個事務送出時把緩沖池的髒塊重新整理到磁盤中。
InnoDB用日志把随機IO變成順序IO，一旦日志寫入磁盤，事務就持久化了，即使變更還沒有寫到資料檔案。
InnoDB最後是要把變更寫入資料檔案，日志有固定大小。InnoDB的日志是環形方式寫的：當寫到日志的尾部，會重新跳轉到開頭繼續寫，但不會覆寫到還沒應用到資料檔案的日志記錄，因為這樣會清掉已經送出事務的唯一持久化記錄。
InnoDB使用一個背景線程隻能地重新整理這些變更到資料檔案。這個線程可以批量組合寫入，是的資料寫入更順序，以提高效率。事務日志把資料檔案的随機IO轉換為幾乎順序的日志檔案和資料檔案IO，把重新整理操作轉移到背景使得查詢可以更快完成，并且緩和查詢高峰時IO的壓力。
InnoDB表空間
InnoDB把資料儲存在表空間内，本質上是一個由一或多個磁盤檔案組成的虛拟檔案系統。InnoDB用表空間實作很多功能，不隻是存儲表和索引。它還儲存了復原日志(舊版本号)，插入緩沖(Insert Buffer)、雙寫緩沖(Doublewrite Buffer)，以及其他内部資料結構。
InnoDB使用雙寫緩沖來避免頁沒寫完整鎖導緻的資料損壞。這是一個特殊的保留區域，再一些連續的塊中足夠儲存100個頁。本質上是一個最近寫回的頁面的備份拷貝。當InnoDB從緩沖池重新整理頁面到磁盤時，首先把他們寫到雙寫緩沖，然後再把他們寫到其所屬的資料區域中，可以保證每個頁面的寫入都是原子并且持久化的。頁面在末尾都有校驗值(Checksum)來确認是否損壞。
InnoDB的多線程
Master Thread
非常核心的背景線程，主要負責将緩沖池中的資料異步重新整理到磁盤，保證資料的一緻性，包括髒頁的重新整理、合并插入緩沖(INSERT BUFFER)、UNDO頁的回收等。
IO Thread
InnoDB中大量使用了AIO(Async IO)來處理IO請求，可以極大提高資料庫性能，IO Thread主要是負責這些IO請求的回調(call back)處理。InnoDB1.0之前工有4個IO Thread，分别是write、read、insert buffer、log IO thread。
Purge Thread
事務送出後，其使用的undolog可能不再需要，是以需要PurgeThread來回收已經使用并配置設定的undo頁。
InnoDB的記憶體
緩沖池
InnoDB基于磁盤存儲，記錄按照頁的方式進行管理。在資料庫中進行讀取頁的操作，首先将磁盤讀到的頁存放在緩沖池中，下次讀取先判斷頁是否在緩沖池則直接讀取，否則讀取磁盤上的頁。對頁的修改首先修改緩沖池，然後再以一定的頻率重新整理到磁盤(通過checkpoint機制)。緩沖池配置通過innodb_buffer_pool_size來設定。
緩沖池中緩存的資料頁類型有：索引頁、資料頁、undo頁、插入緩沖(insert buffer)、自适應哈希索引(adaptive hash index)、InnoDB存儲的鎖資訊(lock info)、資料字典資訊(data dictionary)等
LRU List、Free List和Flush List
InnoDB的LRU添加了midpoint位置，新讀取的頁不是放到首部，而是放到midpoint位置。預設是放在LRU清單長度的5/8處。有些操作可能會全表掃描加載大量的頁，如果直接加載到首部則可能刷出有效頁。資料庫開始時，LRU是空的，頁都在FreeList中，查找時從Free清單中查找是否有可用空閑頁，若有則從Free清單中删除放入LRU。當頁從LRU的old部分假如到new時，稱之為page made young，因為innodb_old_blocks_time設定導緻頁沒有從old部分移動到new部分稱為page not made young。
重做日志緩沖(redo log buffer)
三種情況會講redo log buffer中的内容重新整理到日志檔案
Master Thread每秒重新整理一次
每個事務送出時會重新整理
redo log buffer剩餘空間小于1/2時
額外的記憶體池
在對資料庫結構本身的記憶體進行配置設定的時候，需要從額外的記憶體池進行申請。
Checkpoint技術
InnoDB存儲引擎内部有兩種：
Sharp Checkpoint
資料庫關閉時将所有髒頁刷回磁盤，預設工作方式，參數innodb_fast_shuthown=1
Fuzzy Checkpoint
重新整理一部分髒頁。(Master Thread Checkpoint，FLUSH_LRU_LIST Checkpoint，Async/Sync Flush Checkpoint，Dirty Page too much Checkpoint)
InnoDB關鍵特性
插入緩沖
Insert Buffer
對于非聚集索引的插入或者更新操作，不是每一次直接插入到索引頁，而是先判斷插入的非聚集索引是否在緩沖池中，若在則插入，若不在則放入到一個Insert Buffer中。以一定的頻率進行Insert Buffer和非聚集索引子節點的合并操作。需要滿足兩個條件：1.索引是輔助索引。2.索引不是唯一的。
Change Buffer
InnoDB 1.0.x開始可以對DML操作進行緩沖 (Insert，Delete，Update)分别是：Insert Buffer，Delete Buffer，Purge Buffer。
Insert Buffer是一顆B+樹，全局唯一，負責對所有表的輔助索引進行Insert Buffer。
Merge Insert Buffer是合并到真正的輔助索引中的操作，在下面幾種情況時發生：
輔助索引頁被讀取到緩沖池中
Insert Buffer Bitmap 頁追蹤到該輔助索引頁已經沒有空間可用
Master Thread 觸發
自适應Hash索引(Adaptive Hash Index)
InnoDB 會監控各種索引列的查詢，如果判斷建立哈希索引可以提高通路速度，則會自動建立。AHI是通過緩沖池的B+樹建構而來，不需要對整張表結建構立哈希索引。有如下要求：
以相同模式通路了100次
頁通過該模式通路了N次：N=頁中記錄*1/16
異步IO
異步IO(Asychronous IO，AIO)
檔案
參數檔案：
初始化參數檔案
日志檔案：
例如錯誤日志檔案(error log)，二進制日志檔案(binlog)，慢查詢日志檔案(slow query log)，查詢日志檔案(log)
socket檔案：
UNIX域套接字方式進行連接配接是需要的檔案。
pid檔案：
MySQL執行個體的程序ID檔案
MySQL表結構檔案：
用來存放MySQL表結構定義的檔案
存儲引擎檔案：
二進制日志(binlog)
記錄了對MySQL資料庫執行更改的所有操作，不包括SELECT和SHOW。
mysql> mysqlmaster status;
File
Position
Binlog_Do_DB
Binlog_Ignore_DB
Executed_Gtid_Set
binlog.001663
5924141
mysql> show binlog events in 'binlog.001663' limit 5;
binlog檔案名(Log_name)
日志開始位置(Pos)
事件類型(Event_type)
伺服器編号(Server_id)
日志結束位置(End_log_pos)
資訊
binlog.001663
5878887
Anonymous_Gtid
1
5878966
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663
5878966
Query
1
5879057
BEGIN
binlog.001663
5879057
Table_map
1
5879148
table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663
5879148
Update_rows
1
5879340
table_id: 8291 flags: STMT_END_F
binlog.001663
5879340
Xid
1
5879371
COMMIT
binlog.001663
5879371
Anonymous_Gtid
1
5879450
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663
5879450
Query
1
5879541
BEGIN
binlog.001663
5879541
Table_map
1
5879632
table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663
5879632
Update_rows
1
5879824
table_id: 8291 flags: STMT_END_F
binlog.001663
5879824
Xid
1
5879855
COMMIT
binlog.001663
5879855
Anonymous_Gtid
1
5879934
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663
5879934
Query
1
5880025
BEGIN
binlog.001663
5880025
Table_map
1
5880116
table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663
5880116
Update_rows
1
5880308
table_id: 8291 flags: STMT_END_F
binlog.001663
5880308
Xid
1
5880339
COMMIT
MySQL5.1引入了binlog_format參數，參數有STATEMENT、ROW、MIXED三種。
STATEMENT
和之前的MySQL版本一樣，二進制日志檔案記錄的是日志的邏輯SQL語句。
ROW
記錄的是表的行更改情況。如果設定為ROW，可以将InnoDB事務隔離設定為READ COMMITTED擷取更好的并發性。
MIXED
預設使用STATEMENT，某些情況下使用MIXED。
表的存儲引擎為NDB，對表的DML操作以ROW格式記錄。
使用了UUID() USER() CURRENT_USER() FOUND_ROWS() ROW_COUNT()
使用了INSERT DELAY語句
使用了使用者自定義函數
使用了臨時表
要檢視binlog日志檔案的内容，必須使用MySQL提供的工具mysqlbinlog。
表結構定義檔案
MySQL定義了frm為字尾名的檔案，記錄了表結構(視圖)定義。
InnoDB存儲引擎檔案
表空間檔案(tablespace file)
預設有一個初始大小為10MB，名為ibdata1的檔案
重做日志檔案(redo log file)
預設情況下會有 ib_logfile0和ib_logfile1作為 redo log file 。每個InnoDB至少有一個重做日志檔案組(group)，檔案組下有兩個重做日志檔案，使用者可以設定多個鏡像日志組(mirrored log groups)
表
索引組織表(index organized table)
MySQL預設建立一個6位元組大小的指針(_rowid)
InnoDB邏輯存儲結構
所有的資料都被邏輯地存放在一個空間内，稱之為表空間(tablespace)，表空間又由段(segment)，區(extent)、頁(page)組成，頁在某些文檔中也成為塊(block)
表空間
如果啟用了 innodb_file_per_table的參數，每張表的資料可以單獨放到一個表空間内 ，其中存放的是資料、索引、和插入緩沖Bitmap頁，其他類的資料如復原(undo)資訊、插入緩沖索引頁、系統事務資訊、二次寫緩沖還是放在原本的共享表空間。
段
表空間是由各個段組成的，包括資料段、索引段、復原段等。資料段就是B+樹的葉子節點(Leaf node segment)，索引段即B+樹的非索引節點(Non-leaf node segment)。
區
區是連續頁組成的空間，任何情況下每個區的大小都為1MB，為了保證區中頁的連續性，InnoDB一次從磁盤申請4-5個區，預設情況頁大小為16KB，一個區中一共有64個連續的頁。InnoDB1.0.x引入壓縮頁，每個頁的大小可以通過key_block_size設定為2k、4k、8k。1.2.x版本新增了參數innodb_page_size，通過該參數可以将預設頁的大小設定為4k、8k。
頁
InnoDB中常見的頁類型有：
資料頁(B-tree Node)
Undo頁(Undo Log Page)
系統頁(System Page)
事務資料頁(Transaction system Page)
插入緩沖頁位圖(Insert Buffer Bitmap)
插入緩沖空閑清單頁(Insert Buffer Free List)
未壓縮的二進制大對象頁(Uncompressed BLOB Page)
行
MySQL的存儲是面向列的(row-oriented),資料是按行存儲的。頁存放的記錄有硬性定義最多存放16KB/2 - 200行，即7992行。
InnoDB資料頁結構
資料頁由下面7個部分組成：
File Header(檔案頭)固定
Page Header(頁頭)固定
Infimun 和 Supremun Record 固定
頁中兩個虛拟的行記錄，Infimun是指比頁中任何主鍵更小的值，Supremun指比任何值都大的值，這兩個值在頁建立的時候建立，在任何時候情況下都不會删除。
User Record(使用者記錄，即行記錄)
存儲實際記錄，B+樹索引組織。
Free Space(空閑空間)
空閑空間，連結清單資料結構。一條記錄被删除後會放到空閑空間。
Page Directory(頁目錄)
存放了記錄的相對位置，這些記錄指針稱之為槽(slots)或者目錄槽(dictionary slots)，稀疏目錄，可能包含多條記錄。
B+樹索引不能找到實際的記錄，而是找到記錄的頁。
File Trailer(檔案結尾資訊)
檢測頁是否完整寫入了磁盤，checksum值。
行溢出資料
InnoDB會将一條記錄中的某些列存儲在真正的資料列之外，BLOB，LOB字段可能不一定會将字段放在溢出頁面，VARCHAR也有可能會放進溢出頁面。
Oracle VarCHAR2最多存放4000位元組，MSSQL最多8000位元組，MySQL最多65535(存在其他開銷，最長65532)。當發生行溢出時，資料存放在頁類型Uncompress BLOB頁面。資料頁隻儲存資料的前768位元組。
鎖
lock與latch
latch一般稱為闩鎖，輕量級，要求鎖定的時間非常短。在InnoDB中，分為mutex(互斥量)與rwlock(讀寫鎖)。用來保證并發線程操作臨界資源的正确性，并且通常沒有死鎖檢測的機制。
lock的對象是事務，用來鎖定的是資料庫中的對象，如表、頁、行。在commit或者rollback之後釋放，有死鎖檢測機制。
鎖的類型
共享鎖(S Lock)：允許事務讀一行資料
排他鎖(X Lock)：允許事務更新或删除一行資料
上述兩種都是悲觀鎖，樂觀鎖就是CAS(Compare and Swap)
一緻性非鎖定讀(consistent nonlocking read)
是指InnoDB通過MVCC(Multi Version Concurrency Control)讀取資料庫目前行的方式。如果讀取的行正在進行update或者delete操作，則讀取一個快照。在Read Committed和Repeatedable Read中使用。前者讀取最新的快照，後者使用事務開始時的快照。
一緻性鎖定讀(locking read)
也可以顯式的對讀取加鎖，有兩種操作：
select ... for update(加一個X鎖)
select ... lock in share mode(加一個S鎖)
行鎖的3種算法
Record Lock：單個行記錄的鎖
Gap Lock：鎖定一個範圍，不包括記錄本身
Next-Key Lock：Gap+Record，鎖定範圍以及記錄本身。用來解決幻影相關問題(Phantom)
針對的是索引的區間，但是當查詢條件指定唯一索引值(隻針對主鍵索引/聚集索引)時，會降級為Record Lock，若是二級索引則不會。而且InnoDB還會對二級索引的下一個鍵值加上Gap Lock。
例如，二級索引b列有1，3，6，9。當使用X鎖鎖定3時(where b<=3 for update)，會NKL鎖定了範圍(1-3)，同時會使用GL鎖定下一個鍵值(3-6)。
利用這個機制可以用一個事務，首先select id from t where col=xxx lock in share mode，接下來insert t (col) values (xxx)，能夠保證一定插入不存在的值。
死鎖
兩個事務執行時，因争奪鎖資源互相等待的場景。
解決死鎖最簡單的就是逾時，通過innodb_lock_wait_timeout控制逾時時間。
目前普遍使用的是wait-for graph(主動檢測的方式)，這要求資料庫儲存兩種資訊：
鎖的資訊連結清單
事務的等待清單
通過上述資訊，可以在事務請求鎖并發生等待時都進行判斷，在上述兩個資訊構造的圖中是否存在回路，如果存在就表示存在死鎖。
采用深度優先算法實作，InnoDB1.2之前采用遞歸方式，之後采用非遞歸提高了性能。
事務的實作(ACID)
事務的隔離性由鎖來實作，redo log(重做日志)保證事務的原子性和持久性，undo log()保證事務的一緻性。
redo恢複送出事務修改的頁操作，是實體日志，記錄的是頁的實體修改操作。
undo復原某個行記錄到特定版本，是邏輯日志，記錄的是行的修改記錄。
redo
存在 redo log buffer和redo log file，buffer寫入file時需要調用fsync操作，此操作取決于磁盤性能，決定了事務送出的性能也就是資料庫的性能。
UNIX的寫操作
一般情況下，對硬碟(或者其他持久儲存設備)檔案的write操作，更新的隻是記憶體中的頁緩存(page cache)，而髒頁面不會立即更新到硬碟中，而是由作業系統統一排程，如由專門的flusher核心線程在滿足一定條件時(如一定時間間隔、記憶體中的髒頁達到一定比例)内将髒頁面同步到硬碟上(放入裝置的IO請求隊列)。
因為write調用不會等到硬碟IO完成之後才傳回，是以如果OS在write調用之後、硬碟同步之前崩潰，則資料可能丢失。雖然這樣的時間視窗很小，但是對于需要保證事務的持久化(durability)和一緻性(consistency)的資料庫程式來說，write()所提供的“松散的異步語義”是不夠的，通常需要OS提供的同步IO(synchronized-IO)原語來保證
fsync的功能是確定檔案fd所有已修改的内容已經正确同步到硬碟上，該調用會阻塞等待直到裝置報告IO完成。除了同步檔案的修改内容(髒頁)，fsync還會同步檔案的描述資訊(metadata，包括size、通路時間st_atime & st_mtime等等)，因為檔案的資料和metadata通常存在硬碟的不同地方，是以fsync至少需要兩次IO寫操作
undo
delete和update操作産生的删除語句并不是馬上執行，而是将delete_flag标記為1，最後有purge操作來統一完成。用undo log來執行，執行之後的空間不會回收，隻會用于重用。