14 InnoDB存儲引擎... 1
14.1 InnoDB說明... 5
14.1.1 InnoDB作為默認存儲引擎... 5
14.1.1.1 存儲引擎的趨勢... 5
14.1.1.2 InnoDB變成默認存儲引擎之後... 5
14.1.1.3 InnoDB表好處... 6
14.1.1.4 InnoDB表最佳實踐... 6
14.1.1.5 InnoDB表提升... 6
14.1.1.6 InnoDB作為默認存儲引擎測試... 6
14.1.1.7 驗證InnoDB是默認存儲引擎... 7
14.1.2 檢查InnoDB可用... 7
14.1.3 關閉InnoDB. 7
14.2 InnoDB概念和體系結構... 8
14.2.1 Mysql和ACID模型... 8
14.2.2 InnoDB事務模式和鎖... 9
14.2.3 InnoDB鎖定模式... 9
14.2.3.1 意向鎖... 9
14.2.3.2 死鎖例子... 10
14.2.4 一致性無鎖讀... 10
14.2.5 鎖讀... 11
14.2.6 InnoDB Record，Gap，Next-key鎖... 11
14.2.6.1 Record Lock. 11
14.2.6.2 Next-Key Lock. 11
14.2.6.3 Gap Lock. 11
14.2.7 使用Next-key鎖定避免幻影... 11
14.2.8 不同語句的鎖定設置... 12
14.2.9 事務提交和回滾的影響... 13
14.2.10 死鎖發現和回滾... 13
14.2.11 如何處理死鎖... 13
14.2.12 InnoDB多版本... 14
14.2.12.1 多版本內部細節... 14
14.2.12.2 Rollback Segment管理指導... 14
14.2.13 InnoDB表和索引結構... 14
14.2.13.1 .frm文件在innodb表的角色... 14
14.2.13.2 聚集和secondary索引... 15
14.2.13.3 FULLTEXT索引... 15
14.2.13.4 索引的物理結構... 15
14.2.13.5 Insert Buffering. 15
14.2.13.6 自適應hash索引... 16
14.2.13.7 物理行結構... 16
14.3 InnoDB配置... 16
14.3.1 配置InnoDB為只讀... 17
14.3.1.1 如何啟動... 17
14.3.1.2 使用場景... 17
14.3.1.3 如何工作... 17
14.4 InnoDB管理... 18
14.5 InnoDB表空間管理... 18
14.5.2 InnoDB File-Per-Table模式... 18
14.5.3 啟動禁用File-Per-Table模式... 19
14.5.4 指定TableSpace的路徑... 19
14.5.5 把表空間復制到另外一個服務上... 20
14.5.6 把undo log移出System表空間... 21
14.5.7 修改InnoDB日志文件，增長InnoDB表空間... 21
14.5.8 使用原生磁盤分區作為系統表空間... 22
14.6 InnoDB表管理... 23
14.6.1 創建InnoDB表... 23
14.6.2 把InnoDB表移動到另一個機器... 24
14.6.2.1 跨平台復制移動使用小寫... 24
14.6.2.2 傳輸表空間... 24
14.6.2.3 MySQL企業備份... 24
14.6.2.4 復制數據文件... 24
14.6.2.5 ibd文件可移植性... 24
14.6.3 使用事務批量DML操作... 25
14.6.4 把MyISAM轉化為InnoDB. 25
14.6.4.1 減少MyISAM內存使用，增加InnoDB內存使用... 25
14.6.4.2 查看太長或者太短的事務... 25
14.6.4.3 不要太擔心死鎖... 26
14.6.4.4 計劃存儲分布... 26
14.6.4.5 轉化一個現有的表... 26
14.6.4.6 克隆表結構... 26
14.6.4.7 轉化已存在數據... 26
14.6.4.8 存儲需求... 27
14.6.4.9 仔細選擇Primary Key. 27
14.6.4.10 應用程序性能考慮... 27
14.6.4.11 InnoDB表和文件的關聯... 27
14.6.5 InnoDB自增處理... 27
14.6.5.1 傳統InnoDB自增長鎖... 27
14.6.5.2 配置InnoDB自增鎖... 28
14.6.6 InnoDB和外鍵約束... 30
14.6.7 InnoDB表的限制... 30
14.6.7.1 最大和最小... 30
14.6.7.2 索引類型... 31
14.6.7.3 InnoDB表的限制... 31
14.6.7.4 鎖定和事務... 32
14.7 InnoDB壓縮表... 32
14.7.1 表壓縮概述... 32
14.7.2 啟動壓縮表... 32
14.7.3 InnoDB壓縮表調優... 33
14.3.7.1 什麼時候使用壓縮... 33
14.3.7.2 數據特性和壓縮... 34
14.3.7.3 數據庫壓縮VS應用程序壓縮... 34
14.3.7.4 工作負荷特點和壓縮... 34
14.3.7.5 配置特點和壓縮... 34
14.3.7.6 選擇壓縮page大小... 35
14.7.4 監視壓縮... 35
14.7.5 InnoDB如何壓縮... 35
14.7.5.1 壓縮算法... 35
14.7.5.2 InnoDB存儲引擎和壓縮... 35
14.7.5.3 B樹頁壓縮... 36
14.7.5.4 壓縮BLOB,VARCHAR,TEXT列... 36
14.7.5.5 壓縮和innodb buffer pool36
14.7.5.6 壓縮和InnoDB Redo Log文件... 37
14.7.6 OLTP負荷的壓縮... 37
14.7.7 壓縮語法警告和錯誤... 37
14.8 InnoDB文件格式管理... 39
14.8.1 啟動文件格式... 39
14.8.2 驗證文件兼容模式... 39
14.8.2.1 InnoDB打開時的兼容性檢查... 40
14.8.2.2 表打開時的兼容性檢查... 40
14.8.3 識別使用的文件格式... 41
14.8.4 文件格式降級... 41
14.9 InnoDB 行存儲和行格式... 41
14.9.1 InnoDB行存儲概述... 41
14.9.2 位表指定行格式... 42
14.9.3 DYNAMIC和COMPRESSED行格式... 42
14.9.4 Compact和REDUNDANT行格式... 42
14.10 InnoDB磁盤I/O和文件空間管理... 42
14.10.1 InnoDB磁盤I/O.. 42
14.10.2 文件空間管理... 43
14.10.3 InnoDB 檢查點... 43
14.10.4 表整理... 43
14.10.5 使用TRUNCATE TABLE清理表空間... 44
14.11 InnoDB和Online DDL. 44
14.11.1 Online DDL概述... 44
14.11.2 Online DDL的性能和並發考慮... 45
14.11.3 Online DDL的SQL語法... 46
14.11.4 組合和獨立DDL語句... 46
14.11.5 Online DDL的例子... 47
14.11.6 Online DDL的細節... 47
14.11.7 Online DDl Crash如何恢復... 48
14.11.8 分區表上的Online DDL. 48
14.11.9 Online DDL限制... 48
14.12 innodb啟動選項和系統變量... 49
14.13 InnoDB性能... 49
14.13.1 InnoDB buffer pool配置... 49
14.13.1.1 配置InnoDB Buffer Pool預讀(Read-Ahead)49
14.13.1.2 配置InnoDB刷新比率... 49
14.13.1.3 Buffer Pool處理scan. 50
14.13.1.4 使用多buffer pool實例... 51
14.13.1.5 快速重啟後加載buffer pool51
14.13.1.6 調整InnoDB buffer pool刷新... 52
14.13.2 InnoDB信號量和Read/Write鎖實現... 52
14.13.3 配置InnoDB的內存分配... 53
14.13.4 配置innodb Change Buffer53
14.13.5 配置InnoDB的線程並發... 54
14.13.6 配置innodb I/O後台線程數量... 54
14.13.7 分組提交... 54
14.13.8 配置InnoDB主線程I/O率... 55
14.13.9 在InnoDB自旋循環中使用PAUSE指令... 55
14.13.10 設置自旋鎖輪詢... 55
14.13.11 InnoDB使用MySQL性能框架整合... 55
14.13.12 使用多RollBack Segment獲得更好的擴展性... 56
14.13.13 配置InnoDB清理調度... 56
14.13.14 優化InnoDB只讀事務... 56
14.13.15 使用CRC32 Checksum算法，快速checksum.. 56
14.13.16 undo log保存在獨立包空間... 57
14.13.17 優化統計信息... 57
14.13.17.1 配置持久優化統計信息參數... 57
14.13.17.2 配置非持久性統計信息值... 58
14.13.18 評估ANALYZE TABLE復雜性... 59
14.14 InnoDB INFORMATION_SCHEMA表... 59
14.15 InnoDB監視器... 59
14.15.1 InnoDB監視器類型... 59
14.15.2 啟動innodb監視器... 60
14.15.3 InnoDB標准監控輸出和鎖監控輸出... 61
14.15.4 InnoDB表空間監視器輸出... 62
14.15.5 表監視器... 63
14.16 InnoDB備份和恢復... 63
14.16.1 innodb恢復進程... 63
14.17 InnoDB和MySQL復制... 63
14.18 InnoDB集成memcached.. 64
14.19 InnoDB Troubleshooting. 64
14.19.1 troubleshooting InnoDB I/O問題... 64
14.19.2 啟動損壞的數據庫... 65
14.19.3 排查InnoDB數據字典操作... 65
14.19.4 InnoDB錯誤處理... 67
14.19.5 InnoDB錯誤代碼
14.19.6 系統錯誤代碼
Innodb是通用的存儲引擎，平衡高可用和高性能。從Mysql 5.5開始作為Mysql的默認存儲引擎。
InnoDB重要特點
InnoDB重點：
1.DML操作遵循ACID，支持事務提交，回滾和災難恢復來保護數據。
2.支持行鎖，oracle類型的讀一致性。增加多用戶並發。
3.InnoDB保存在磁盤中，通過primary key來優化查詢。
4.為了維護數據完整性，InnoDB也支持外鍵約束。
5.InnoDB可以和mysql的其他存儲引擎混合。
6.InnoDB設計，當處理大數據時，最大化性能。
InnoDB存儲引擎特性：
InnoDB自己維護了buffer pool來緩存數據和索引。默認啟動innodb_file_per_table，這樣InnoDB表的索引和數據都被存放在獨立的一個文件中。如果disable，被保存在系統表空間中。
在MySQL 5.6之後版本：
1.InnoDB支持全文索引。
2.InnoDB在執行只讀，或者讀多寫少比較出色。
3.在只讀媒體上可以使用InnoDB表。
從MySQL 5.5.5之後默認存儲引擎變成了InnoDB，可以再create table語句中使用engine=myisam來制定表的存儲引擎。Mysql和informateion_schema任然在使用myisam。對於grant表，不能轉化為InnoDB。
1.如果服務crash，在服務啟動後不需要做其他事情。InnoDB會自動recovery數據庫。
2.InnoDB buffer pool緩存表和索引，把數據和索引放在內存中可以提高處理速度。
3.放在不同表上的數據可以使用外鍵來關聯。
4.若數據在磁盤或者內存中損壞，checksume機制會警告你。
5.當每個表上都設計了合適的索引，設計的列都會被優化。
6.insert，update，delete會被change buffer機制優化。
7.性能不會因為giant表的long running query而限制。
1.指定primary key為最常用的列，若沒有可以用自增。
2.為了更好的join性能，外鍵定義在join字段上，並且保持字段數據類型一致。
3.關閉自動提交，一次性提交可以提高性能。
4.可以使用start transaction commit包裹DML語句組成一個事務。
5.停用lock table語句，InnoDB可以控制多個會話在一個表上讀寫。
6.啟動innodb_file_per_table選項
7.InnoDB壓縮特性，row_format=compressed
8.—sql_mode-no_engine_substitution防止創建和指定engine不同的表。
1.可以壓縮索引和表。
2.可以創建，刪除索引對性能和可用性影響變小。
3.truncate table很快，並且把空間還給OS，而不是保留在系統表空間中。
4.使用DYNAMIC行格式來保存blob和text效率更高。
5.通過information_schema，查看存儲引擎內部情況。
6.performance_schema查看存儲引擎性能。
7.一些性能提升。
從之前版本升級MySQL 5.5之後，還是要檢查InnoDB是不是能夠正常在被程序使用。使用參數—default-storage-engine=Innodb設置默認存儲引擎。設置之後只會影響後來新建的表。測試應用程序可以正常運行，確定讀寫正常，如果有依賴myisam特性可能會失敗。如果想要知道老表在innodb運行的情況，可以使用alter table table_name ENGINE=InnoDB。
或者
CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;
檢查InnoDB狀態：
1.使用SHOW ENGINE查看所有MySQL所有的引擎，查看DEFAULT列。
2.如果沒有InnoDB說明編譯的時候，沒有加InnoDB。
3.如果InnoDB存在但是不可用，回到配置文件或者配置，去掉skip-innodb選項。
使用SHOW ENGINE查看InnoDB是否存在來決定InnoDB是否可用。
如果你不要使用InnoDB：
1.使用—InnoDB=OFF或者—skip-innodb選項禁用innodb存儲引擎
2.因為innodb是默認的存儲引擎，所以啟動報錯，需要設置—default-storage-engine和—default-tmp-storage-engine，
3.為了讓服務在查詢information_schema innodb相關的表是不會奔潰，還需要配置一下參數
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0
ACID模式是數據庫設計的標准的集合。
A: atomicity原子性
C:consistency 一致性
I:isolation 隔離性
D:durability 持久性
原子性
原子性主要由InnoDB事務來保持
一致性
一致性方面 ACID主要設計InnoDB內部處理來保護數據。
1.InnoDB雙寫 buffer
2.InnoDB災難恢復
隔離性
ACID的隔離性的模型，主要涉及InnoDB事務在指定隔離級別上的執行。
1.自動提交設置
2.set isolation level隔離級別
3.低級InnoDB鎖，可以通過INFORMATION_SCHEMA查看。
持久性
ACID的持久性，涉及mysql和指定的硬件的交互，
1.innodb 雙寫
2.innodb_flush_log_at_trx_commit配置參數
3.sync_binlog 配置參數
4.innodb_file_per_table配置參數
5.存儲設備的，write buffer
6.存儲設備battery-backed cache
7.運行MySQL的操作系統，特別是支持fsync系統調用的
8.UPS支持的環境
9.備份策略
10.硬件設備和網絡連接
在InnoDB事務模式，目標是把高性能的多版本的數據和2階段鎖定結合。InnoDB鎖定在行級別並且查詢默認以nolocking一致性執行。和Oracle一樣。Lock信息在InnoDB是空間高效實用的。鎖不需要創建。也不會照成內存壓力。
InnoDB所有用戶行為都在事務內。若打開了自動提交，每個SQL都是一個事務。默認會話都是自動提交的，若出錯會根據錯誤回滾。自動提交的會話，可以通過start transaction 或者begin語句，以commit或者rollback結束，啟動一個事務。通過set autocommit=0取消自動提交。
默認InnoDB的隔離級別是REPEATABLE READ，InnoDB提供了4個隔離級別，READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ和SERIALIZABLE。
用戶可以通過SET TRANSACTION語句設置隔離級別，使用—transaction-isolation選項來設置默認隔離級別。
對於行級鎖，InnoDB通常使用next-key鎖定。也就是說除了索引記錄，InnoDB還可以lock頁內空隙，不讓其他會話插入數據。Next-key lock是鎖定了index record和它之前的gap。
InnoDB實現標准的行鎖有2中強度類型，S鎖和X鎖。粒度類型有3類，record鎖，gap鎖，next-key鎖。
InnoDB可以支持多粒度鎖定允許，就引入了意向鎖。
IS:事務T意向在表上使用S鎖。
IX:事務T以上在表上使用X鎖。
意向鎖協議：
1.為了獲取在行上獲取S鎖，就必須在表上獲取IS鎖。
2.為了獲取行上的X鎖，必須在表上限獲取IX鎖。
Lock兼容表
X
IX
S
IS
X
Conflict
Conflict
Conflict
Conflict
IX
Conflict
Compatible
Conflict
Compatible
S
Conflict
Conflict
Compatible
Compatible
IS
Conflict
Compatible
Compatible
Compatible
略
一致性讀的意思是，InnoDB使用多版本來生產查詢結果的快照。查詢只能看到事務啟動時，已經提交完的事務修改。有個異常就是查詢可以看到事務內前面語句的修改內容。這個異常會導致一個情況，在其他事務剛好也更新了同樣的表，這種情況下，你看到的表狀態重來就沒出現在數據庫過。
如果隔離級別是所有的一致性讀，從第一個讀開始。
使用READ COMMIT隔離級別，每個事務中的一致性讀來至於自己刷新的快照。
不管是READ COMMIT,REPEATABLE READ都默認使用一致性讀。一致性讀不會鎖定表訪問，所以其他會話可以同時更新這些表。
假設你默認是REPEATABLE READ隔離級別。當查詢數據是會給一個時間點，當行被其他事務刪除，在查詢不會發現行被刪除。
Note
快照在事務內有效。盡管查不到數據，但是update，delete還是可以修改這些數據庫的。
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.
如果要推進快照點，可以執行事務提交，再執行另一個select或者START TRANSACTION WITH CONSISTENT SNAPSHOT。
如果想要看最新的數據庫狀態，可以使用read committed，或者鎖定讀。
SELECT * FROM t LOCK IN SHARE MODE;
使用READ COMMITTED隔離級別，事務裡面的每次一致性讀都是獨自己刷新的快照。使用LOCK IN SHARE MODE,發生讀鎖。
一致性讀不能覆蓋DDL語句,insert into select ,update…(select)，create table select都不能指定for update或者 lock in shared mode。
在事務裡select不會被保護，其他事務可以同時修改查詢的值。Innodb提供額外的鎖讀，提供安全性：
1.Select … lock in share mode設置
2.select … for update
使用以上標記的鎖，到事務完成時才能釋放。
InnoDB有幾類鎖：record lock，Gap lock，Next-key lock
Record Lock鎖定Index記錄，不管有沒有定義索引。
默認，InnoDB使用REPEATABLE READ隔離級別並且innodb_locks_unsafe_for_binlog不可用。Next-key lock查詢可以防止幻讀出現。
Next-key是index-row和gap的組合。Next-key鎖定record和，record之前的gap。若會話共享，獨占鎖定了R，那麼另外一個就不能再R之前插入數據。
Gap lock不會使用在使用唯一索引查詢上。
有一種gap lock類型被稱為插入意向gap lock，由Insert操作設置。這個鎖表示嘗試插入。多事務插入到同一個index gap就會被堵塞。
使用read commit或者 innodb_locks_unsafe_for_binlog來取消gap lock。
幻影問題，事務內相同查詢在不同時間運行，返回的數據不一樣。
如：
SELECT * FROM child WHERE id > 100 FOR UPDATE;
如果不加gap鎖，就可以插入101，那麼再次使用查詢，就會出現幻影行。
為了避免幻影問題，使用next-key lock鎖定index record和gap。
鎖讀，update，delete，當掃描是，會對所有的掃描到的index record上鎖。並不會理睬是不是在where條件之外。InnoDB只知道要掃描的Index Range。
InnoDB指定lock類型：
1.select from 一致性讀不加鎖,除非在SERIALIZABLE隔離級別
2.select from lock in share mode 共享鎖讀，next-key lock。
3.select from for update 排他鎖讀
4.update where排他的next-key lock
5.delte from where排他next-key lock
6.insert在插入的行上設置排他鎖，是record鎖，不是next-key鎖。
如果發生重復鍵錯誤，那麼index record會設置共享鎖，當多個事務同時獲得共享鎖，就有可能會出現死鎖。
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
Now suppose that three sessions perform the following operations in order:
Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:
ROLLBACK;
當第一個在會話1執行，那麼會話2和會話3會報主鍵沖突，會共享鎖。當會話1釋放。會話2，會話3同時去獲取排他鎖。就會導致2,3死鎖。
7.Insert on duplicate key upate，和insert不同，當出現重復鍵錯誤，或使用x鎖，不是s鎖。
8.replace當在沒有沖突的情況下，和insert一樣。如果有沖突獲取next-key x鎖，替換行。
39insert into t select * from s where，會在T中設置record x鎖。若隔離級別是read committed或者設置了innodb_locks_unsafe_for_binlog，並且不是SERIALIZABLE否則innodb設置共享next-key鎖在S表上。
Create table select,insert select都會申請共享next-key鎖。如果表在結構replace into t selct from s where 或者update t where col in(select from s)innodb都會在s上設置共享next-key鎖。
10.在初始化auto_increament時，會在相關的索引上加上x鎖。在訪問自增counter時，使用auto-inc鎖，語句執行完就釋放。
11.如果表上定義了外鍵，任何寫入操作都需要去檢查行，需要設置共享行鎖查看。
12.lock tables設置表鎖
Innodb自動死鎖診斷，不會發現設計到table locks的死鎖。因為table locks是mysql層的不知道innodb的行級別鎖。
默認MySQL啟動會話，以自動提交方式提交事務。如果語句返回錯誤，會根據錯誤處理commit和rollback。
如果會話取消了自動提交，沒有顯示提交，最終會回滾。一些語句會影響事務提交，在commit執行的話，會導致影響事務提交。
InnoDB自動診斷發現事務死鎖回滾事務或者打破事務回滾。
如果配置innodb_table_locks=1默認並且autocommit=0 InnoDB會認識table locks否則不認識。
在InnoDB中一個事務rollback，所有的鎖都會被釋放。然而對於單個語句因為出錯rollback，可能鎖會被保留，因為InnoDB不知道鎖是屬於哪個語句的。
如果select調用了用戶函數在事務中，語句出錯，語句被回滾。
死鎖是事務數據庫的典型問題，除非經常發生，不然不是問題。在寫程序的時候要進行重試。
InnoDB使用自動行鎖。在單行寫入也可能發生死鎖，因為不是真的原子性。他們自動在index record上設置鎖。
可以使用如下方法來減少死鎖：
1. 任何時候，可以使用show engine innodb status來確認最近死鎖。
2. 使用innodb_print_all_deadlocks，把錯誤信息print到error log上。
3. 發生錯誤的時候要重試
4. 完成修改後，立馬提交
5. 如果使用鎖讀，使用比較低的隔離級別，比如read committed
6. 當修改不同的表，或者不同的行集，使用一致的順序。
7. 增加合適的索引。
8. 減少鎖，如果可以使用老的快照，就不要使用鎖讀。
9. 如果還是沒有緩減，使用串行事務，表級鎖。
10. 另外一個方式是創建一個輔助的信號量表。事務update先要訪問信號量表。
InnoDB是多版本存儲引擎，保存了老的修改的行。支持並發和回滾。這些數據被保存在rollback segment。InnoDB使用在rollback segment的信息來執行undo。
內部，InnoDB為每個行存儲增加了3個列。6個字節的DB_TRX_ID表示最後寫入的事務ID，7字節DB_ROLL_PTR roll指針，roll指針指向rollback segment中undo log record。6字節的DB_ROW_ID，包含rowid，rowid是單調遞增的。
undo log在rollback segment被分為insert，和update undo log。insert undo log只在回滾時需要commit才能釋放。update undo logs也可以用在一致性讀上，只有在事務沒有使用的時候才會消失。
InnoDB不能從update undo log中放數據，回滾段會變得很大。undo log記錄在回滾段是一般比insert和update的行小。
在InnoDB多版本結構，一個行不會在刪除後，馬上物理上刪除。只有當取消update undo log記錄後才會在物理上刪除。刪除操作被稱為清空，通常清空的順序和sql順序一樣。
如果insert和delete行。
如果你插入和刪除行在小批處理。清理現場會滯後會越來越大導致變慢。這時，可以使用innodb_max_purge_lag參數來調整清理操作。
MySQL以.frm文件方式，把數據目錄保存在數據庫目錄中。InnoDB也會把表的元數據信息存放在表空間中。當MySQL drop表或者數據庫，會刪除一個或者多個.frm文件。但也不能通過移動.frm文件來移動表。
每個InnoDB表有一個特別的聚集索引，聚集索引和primary key同義。
1. 當你定義了primary key，InnoDB會作為聚集索引。
2. 如果沒有定義primary key，MySQL會使用unique，並且非null的列作為聚集索引
3. 如果表沒有primary key和unique索引，InnoDB自動生成一個隱藏聚集索引聚合列和rowid。
如果通過訪問聚集索引訪問一行很快是因為開銷索引查詢可以直接訪問到有所有數據的page。如果表很大，通過聚集索引訪問會減少I/O。
除了聚集索引外，其他都是secondary索引，InnoDB secondary 索引，都包含了primary key的列。如果primary key很長，secondary就需要更多的空間，所以建議primary key比較短。
特別的索引，FULLTEXT索引，幫助innodb處理關於基於文字的查詢。全文索引可以使用create table，alter table，create index創建。 MATCH() ... AGAINST 語法查詢。
所有InnoDB所以是b樹結構，索引的page為16KB，當新的記錄插入，InnoDB試圖保留1/16的空間用於未來的插入和修改。
如果index record是順序插入的，page為15/16，若是隨機插入的1/2~15/16。當page低於1/2，page會試圖調整釋放page。
Note
page的大小可以通過參數innodb_page_size制定。
數據庫應用程序總是以主鍵自增的循序插入新行，因為聚集索引和primary key順序一樣，插入table不需要做隨機I/O。
secondary index是不唯一的，insert到secondary順序是隨機的。刪除和update會影響數據頁是隨機的。
當索引被刪改，innodb會檢查page是否在buffer pool。如果在，會直接修改index page，如果不在，InnoDB會記錄到insert buffer中。insert buffer會保持的很小，可以放到buffer pool，修改會快速修改。這個進程被稱為change buffering。
定期，insert buffer會合並到secondary index上，通常吧多個修改合並到一個page，來減少I/O操作。
insert buffer在事務提交後，會合並到insert buffer。當很多索引要被delete和update，insert buffer合並會話很長時間。這個時候磁盤I/O會增長，會導致依賴於磁盤查詢延遲。另外一個重要I/O操作時清理線程。
自適應hash索引讓innodb很像內存數據庫，適應負荷和內存。不會犧牲任何事物特性和可靠性，使用innodb_adaptive_hash_index來啟動和禁用。
根據查詢部分，mysql創建一個hash索引使用固定的索引鍵。固定鍵的長度可以是任意長度的。只有btree的一些值會出現在hash索引上。hash索引只會的page進行訪問。
當表整個放入住內存，hash index可以加快訪問速度。innodb有機制可以監控索引訪問。如果InnoDB覺得查詢可以從創建hash index後獲得好處，就會自動創建。
在某些情況下，hash索引查找提高的速度遠遠大於額外的監控的和維護hash索引結構。有時候，read/write鎖守護數據在搞負荷情況下，會變成爭用焦點。
你可以使用show engine innodb status的semaphores段來監控hash index。若看到大量的RW-latch在btr0sea.c被創建，可能取消自適應hash索引更好。
物理行結構依賴於InnoDB表依賴於表創建時候的row format。可以使用show table status檢查。
InnoDB表空間和日志文件概述
2個重要的磁盤管理的資源是，InnoDB的數據文件和日志文件。如果沒有InnoDB選項。MySQL創建自動擴展的文件大於12MB，ibdata1和2個日志文件ib_logfile0,ib_logfile1。大小由innodb_log_file_size系統參數。
考慮存儲設備
把log文件放到不同的磁盤可以提升性能。也可以使用原始的磁盤分區作為innodb數據文件，可能可以提升性能。
指定innodb表空間位置和大小
innodb_data_file_path選項指定了數據文件，使用分號隔開。
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
以下設置顯示的創建最小系統表空間
[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend
若指定了autoextend InnoDB會自動擴展。max指定文件最大限制
[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M
innodb_data_home_dir指定了表空間文件的路徑
[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Note
innodb不會創建目錄所以要保證，目錄存在。並且保證有權限。
innodb_data_home_dir默認值為mysql 數據目錄。
也可以直接在innodb_data_file_path上指定文件路徑。
創建InnoDB系統表空間
當創建系統表空間時，最好使用命令行啟動。
C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --console
如果MySQL數據目錄在只讀媒體中，帶—innodb-read-only來啟動服務。就可以查詢了。
准備只讀操作，首先要保證數據被刷新到數據文件中。然後取消change buffer innodb_change_buffering=0，使用slow shutdown。使用參數—innodb-read-only =1。
1.有一部分數據在只讀介質中
2.多個MySQL實例並發查詢相同的數據目錄
3.因為數據安全或者數據集成的需要設置為只讀
如果MySQL啟動了—innodb-read_only選項，InnoDB會關閉一些特性和組件：
1.沒有啟動change buffer
2.啟動階段沒有carsh recovery
3.因為只讀不需要redo log，可以把redo log 設置到最小
4.素有後台IO除了IO都可以關閉
5.deadlock，monitor輸出等等都不會被寫入臨時文件。Show engine innodb status不會輸出任何信息。
6.如果Mysql以—innodb-read-only啟動，如果數據目錄放在讀寫上，還是可以運行DCL命令grant，revoke。
7.修改配置選項可以修改寫入操作，當read-only時，沒有影響。
8.MVCC強制隔離級別被關閉
9.undo log不能使用。
InnoDB管理設置到很多方面：
1.管理數據文件表示系統表空間，innodb表和其他相關的索引
2.管理redo log文件
3.盡量使用innodb
4.性能相關的通用管理
啟動前，先檢查innodb數據文件存在，並且有足夠的權限訪問。第一次啟動使用命令行比較好。因為會把信息都print到命令行上。
File-Per-Table的好處：
1.當表刪除或者truncate，空間可以被OS回收。
2.Truncate table在單個.ibd文件上執行更快。
3.可以為每個表指定一個特定存儲。優化IO，空間管理。CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory。
4.運行OPTIMEIZE TABLE，壓縮或者重建創建表空間。運行OPTIMIZE TABLE InnoDB會創建一個新的ibd文件。當完成時，老的表空間會被新的代替。
5.可以移動單個表，不需要移動整個數據庫
6.可以把表復制到另外一個實例
7.innodb_file_per_table啟動後才能使用Barracuda文件格式。
8.可以更有效的存儲帶BLOB，TEXT使用動態行模式的表。
9.使用innodb_file_per_table可以提高recovery的成功率，減少損壞錯誤發生恢復的時間。
10.可以快速的備份，恢復單個表。
11. innodb_file_per_table 可以從備份中去除一個表
12. innodb_file_per_table在備份和復制表時，容易觀察每個表的狀態。
13.可以通過文件系統直接觀察表的大小。
14.當innodb_flish_method設置為O_DIRECT，通常linux文件系統不允許並發的寫入同一個文件。使用innodb_file_per_table就會有性能提升。
15.不啟用innodb_file_per_table，數據都會放在系統表空間中，最大64TB，如果使用innodb_file_per_table每個表可以64TB。
File-Per-Table一些缺點：
1.表空間中的空間只能被這個表使用
2.fsync操作必須在每個表上都運行一遍
3.mysqld必須保持一個打開的文件句柄，表太多會影響性能。
4.會消耗很多文件描述
5. innodb_file_per_tablezhiyou 5.6.6或更高版本才能用，有向下兼容問題。
6.如果很多表都增長，會出現文件碎片問題。導致drop 表和表掃描性能下降。
7.當drop表的時候會掃描buffer pool，如果太大會比較耗時。
8.innodb_autoextend_increment指定當文件滿了之後增長的空間。
File-Per-Table啟動禁用方式：
設置配置文件或者全局變量：innodb_file_per_table。
啟動innodb_file_per_table，InnoDB會吧數據存放在ibd文件中，和MyISAM不通，MyISAM存放在tbl_name.myd,tbl_name.myi。
InnoDB的數據和索引都放在ibd文件中，frm文件依然還是會創建。如果關閉innodb_file_per_table那麼InnoDB的表都會創建在系統表空間中。
通過以下方法可以移動innodb到自己的表空間：
-- Move table from system tablespace to its own tablespace.
SET GLOBAL innodb_file_per_table=1;
ALTER TABLE table_name ENGINE=InnoDB;
-- Move table from its own tablespace to system tablespace.
SET GLOBAL innodb_file_per_table=0;
ALTER TABLE table_name ENGINE=InnoDB;
啟動了innodb_file_per_table，可以再創建表的時候指定文件路徑。具體看create table。
這個位置很重要，因為之後不能通過alter table修改。在數據目錄上，MySQL在目標目錄上創建一個以數據庫命名的子目錄，裡面存放新的ibd文件。在DataDir上創建一個tbl_name.isl文件，包含了這個文件的路徑。這個文件相當於symbolic link。
Note
1.MySQL初始化的時候會把ibd文件保持打開裝填，以防止設備被unmount。不要在mysql運行的時候unmount，不要在unmount的時候啟動服務。
2.不要把Mysql表放在NFS mounted卷上。NFS使用消息來修改文件，如果消息不對，可能會影響數據一致性。
3.如果使用LVM快照，file copy，或者基於文件的備份，先執行FLUSH TABLS FRO EXPORT.
4.data directory子句可以使用symbolic link來代替。
表空間復制的限制和注意點
1.表空間復制過程只有當innodb_file_per_table啟用的時候才能用。
2.只有讀操作可以再該表上使用。
3.導入時，page size必須和被導入實例上的一致。
4.DISCARD TABLESPACE不支持分區表。
5. DISCARD TABLESPACE有主外鍵約束的也不支持，在discard之前要先設置foreign_key_checks=0。
6.alter table import tablespace不會強制外鍵約束
7.alter table import tablespace 不需要cfg文件，但是當沒有cfg文件的時候導入時不會檢查元數據。
8.MySQL 5.6之後，如果2個服務都是GA狀態，並且在一個系列裡面，可以導入。否則不許先在沒導入服務上創建文件。
9.在windows上innodb，表空間，表名都是小寫，為了避免這個問題，要注意linux，unix上的大小寫。
例子：移動表空間到另外一個服務上
1. 源服務器上創建創建表:
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
2. 在目標服務器上創建表，如果不存在:
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
3. 目標服務器上discard表
mysql> ALTER TABLE t DISCARD TABLESPACE;
4. 源服務器上運行 FLUSH TABLES ... FOR EXPORT 限制表，並創建了cfg文件:
mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;
cfg文件被創建在數據目錄上.
5. 復制ibd和cfg文件:
shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test
6. Unlock 表釋放flush table for export的鎖:
mysql> use test;
mysql> UNLOCK TABLES;
7. 導入到目標服務:
mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;
表空間復制內部細節
當運行alter table discard tablespace，目標服務：
1.表會被X鎖
2.表空間會被分離
當運行flush tables for export：
1.表被S鎖
2.清理線程停止
3.髒數據被同步到磁盤
4.表元數據生產到cfg文件
當運行unlock tables
1.cfg被刪除
2.s鎖被釋放，清理線程啟動
當運行alter table import tablespace：
1.檢查表空間page是否損壞
2.空間Id和LSN被更新
3.檢查flag，LSN被更新
4.b樹被更新
5.page設置為dirty，讓數據庫可以寫入磁盤。
你可以把undo log分離到獨立的表空間文件。詳細看14.13.16
MySQL 5.6.8之後，修改innodb日志文件和大小：
1.沒有錯誤下，關閉服務。
2.編輯my.cnf，設置innodb_log_file_size,innodb_log_files_in_group
3.啟動服務。
當innodb 發現innodb_log_file_size和老的不一樣。會刪除redo log文件，創建新的文件。
MySQL 5.6.7和之前的版本，修改innodb文件個數和文件大小：
1.設置innodb_fast_shutdown 為1
2.關閉服務沒有錯誤
3.復制老的日志文件到其他地方
4.在目錄中刪除日志文件
5.編輯配置文件
6.啟動服務
增加InnoDB表空間
最簡單的方法是通過自增長來實現，innodb_autoextend_incremnt參數。也可以通過增加新的文件方式來擴展空間：
1.關閉服務
2.若之前的文件定義了自增長，修改變為固定長度，設置大小。可以再參數innodb_data_file_path中顯示指定。
3.在innodb_data_file_path後增加新的文件，可以設置為自增長。
4.啟動服務。
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
一段時間之後，文件增長到了988M。
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
減少InnoDB表空間
減少innodb表空間過程：
1.使用mysqldump，導出所有innodb表包括在mysql數據庫中的表。
2.停止服務
3.刪除所有存在的ibd文件，ibdata，ib_log
4.刪除frm文件
5.配置新的表空間
6.啟動服務
7.導入文件。
你可以把系統表空間放到原生分區中。當使用原生磁盤分區，保證服務有讀寫分區的權限。
在Linux和unix系統分配原生磁盤分區
1.當你創建一個新的數據文件，指定在innodb_data_file_path選項設置newraw
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
2.重啟服務，InnoDB注意到newraw關鍵字，並初始化這個行分區，不創建或者修改任何InnoDB表。否則下次重啟InnoDB會重新初始化修改會丟失。
3.InnoDB初始化新分區之後，停止服務，把newraw修改為raw。
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
4.重啟服務，InnoDB允許修改。
創建表
-- Default storage engine = InnoDB.
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a));
-- Backward-compatible with older MySQL.
CREATE TABLE t2 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;
根據innodb_file_per_table設置，InnoDB創建每個表在系統表空間或者獨立的表空間，使用show table status語句查看這些表的屬性：
mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2015-03-16 16:26:52
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
使用新的行模式
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=barracuda;
CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC;
CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;
為primary key設置主鍵，列的選擇：
1.被很多查詢引用
2.左邊無空白
3.不會有重復鍵
4.一旦插入後很難修改
為了跨平台移動，創建的數據庫和表使用小寫。在配置文件中設置
[mysqld]
lower_case_table_names=1
使用flush tables for export准備innodb表復制到另外一個服務。當然InnoDB必須開啟innodb_file_per_table
略
可以使用InnoDB數據庫，簡單的復制所有相關文件。像MyISAM數據文件，InnoDB數據和日志文件是binary，所有的平台都兼容。
當移動ibd文件，數據庫目錄名必須相同。如果你有干淨的ibd文件備份，你可以恢復到新裝MySQL：
1.復制ibd之後，不要drop或者truncate，因為這樣會修改表空間中存在的表ID。
2.alter table刪除元數據
ALTER TABLE tbl_name DISCARD TABLESPACE;
3.復制ibd文件到目標庫中
4.使用alter table語句導入元數據
ALTER TABLE tbl_name IMPORT TABLESPACE;
干淨的ibd文件備份要滿足一下條件：
1.沒有未提交的修改。
2.沒有未合並的insert buffer
3.清理所有刪除標記的index record
4.把所有修改也從buffer pool寫入到文件中。
使用以下步驟創建干淨的ibd備份。
1.停止所有活動並提交所有事務
2.等待直到show engine innodb status沒有活動，主線程的狀態為waiting for server activity。
另外一個方式是：
1.使用Mysql企業版備份備份mysql
2.啟動mysqld服務，清理ibd文件。
導入導出
可以使用mysqldump導出表，然後在另一個表上導入。在導入時關閉自動提交可以提高性能。
連接到MySQL默認是自動提交模式。多余多語句事務，可以set autocommit=0取消自動提交。使用start transaction開啟，commit，rollback結束。
從MyISAM中過渡，減少key_buffer_size配置釋放內存。增加innodb_buffer_pool_size配置。
1.盡可能的多分配內存，最多80%的內存
2.當os總是出現內存短切，減少innodb_buffer_pool_size
3.若innodb_buffer_pool_size有好幾個G可以使用innodb_buffer_pool_instance增加buffer pool，減少訪問buffer pool的沖突。
4.在忙綠服務，把query cache關閉。
因為MyISAM不支持事務，所以不用在意自動提交。主要是用在innodb上的。當大量的寫入的服務上，若事務太長會生成大量的負荷。因此小心避免事務運行時間太長：
1.如果你使用mysql交互式會話，完成的時候總是使用commit，rollback。及時關閉交互式會話。
2.保證任何錯誤，rollback不完整修改或者commit完整提交
3.ROLLBACK是比較昂貴的操作。當處理大量數據後，避免執行rollback
4.大量批量insert語句，同期的commit可以提高性能。
事務太長，會浪費內存和磁盤空間。事務太短，因為commit太頻繁，也會浪費I/O。對於1.頻繁使用InnoDB表，應該取消自動提交避免不必要的I/O。
2.自動提交適合與生產報表或者分析統計。
3.如果做了一列相關修改，最後要使用一個commit。
4.select語句也會打開一個事務。
MySQL會很快發現死鎖並且取消小的事務。應用應該處理錯誤並重啟事務。如果死鎖發生很頻繁，就要檢查應用程序代碼，整理代碼順序，或者縮短事務。Innodb_print_all_deadlock會吧錯誤信息輸出到error log中。
為了獲得做好的性能，需要調整幾個和存儲有關的參數。當你轉化一個MyISAM表，數據大，訪問頻繁，並且重要的。可以考慮innodb_file_per_table,innodb_file_format和innodb_page_size配置，和create table中的row_format，key_block_size。最重要的參數是innodb_file_per_table。
使用alter命令轉換
ALTER TABLE table_name ENGINE=InnoDB;
使用show create table table_name 獲取表結構，然後修改engine子句。
把數據轉移到空的innodb表，可以使用：
INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.
可以再插入之後在創建索引，以前索引創建是很慢的，現在消耗已經降低。如果有唯一性約束，可以先關閉約束檢查，提高插入性能
SET unique_checks=0;
... import operation ...
SET unique_checks=1;
對於達標，可以減少I/O因為innodb可以使用insert buffer來批量寫入secondary index的修改。
對於大表也可以使用如下來控制insert
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
不管是轉化MyISAM到InnoDB，都要保證有足夠的空間存放新老2份數據。如果alter table出現空間不足，會回滾，並且化很長時間。Innodb使用insert buffer批量合並到index rolback沒有這樣的機制，會比insert多近30倍的時間。
Primary key是總要的因素，會影響mysql查詢性能和表索引空間使用。
創建primary key的指導：
1.為每個表定義主鍵
2.最好在創建表的時候就定義，而不是使用alter table
3.仔細的選擇列和數據類型，最好使用數值列
4.如果沒有合適的列，考慮自增列
5.如果不再到主鍵會不會被修改的情況下，自增列也是很好的選擇。
根據表的預計大小，使用最小的類型，可以讓表收窄減少空間，若有secondary索引，可以減少索引空間。
創建表，沒有指定主鍵，mysql會自動創建一個6字節長度的主鍵，但是不能用於查詢。
額外的可用性和擴展性，需要的空間比MyISAM多。可以窄設計來節省空間。
InnoDB文件需要比MyISAM更加小心，有計劃性：
1.不能刪除innodb系統表空間
2.復制innodb到另外一個服務，需要使用flush table for export並且復制cfg和ibd文件。
InnoDB對自增列的插入做了優化。InnoDB表的自增必須是索引的一部分。
若在InnoDB上使用了自增，InnoDB數據目錄包含auto-increment計數器。用來分配自增值，計數器保存在內存中，不是在磁盤中。
InnoDB初始化自增計數器，服務啟動後，第一個插入的語句會運行如下語句：
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
InnoDB增加這個值，並分配給counter和這個insert。默認增加為1，可以通過auto_increment_increment來配置。
若表是空的，就使用1，可以用auto_increment_offset來設置便宜。
若在初始化錢使用了語句show table status，innodb初始化計數器，但不增加。初始化使用排它鎖。
在初始化之後，如果不顯示的指定值，自增計數器會分配一個值。如果指定的值大於計數器，計數器就會被更新。如果為自增列指定了null或者0，innodb會為自增分配一個新值。
訪問計數器，會使用auto-inc鎖，知道語句結束才釋放。Auto-inc提高了並發性，但是長期持有會導致性能問題。如：
Insert into t select from t1;
服務重啟後，第一次插入會初始化自增計數器，也會取消create table中auto_increment設置。
若事務rollback，計數器不會rollback。
InnoDB的自增使用特殊的鎖AUTO-INC。語句結束就釋放。對於基於語句的復制，意味著slave，master使用想用的語句，會導致insert語句不確定。
有表如下：
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
插入數據
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InnoDB不知道有多少行要通過select插入。在語句執行是一個一個的分配。Tx1生產的自增時連續的，Tx2分配的自增，和Tx1比大小，由誰先運行決定。
只要執行順序和binary log一樣，結果是一樣的。所以在插入的時候可以使用表鎖，這樣自增分配對復制來說是安全的，然而當有多個會話insert時表鎖會影響並發性和可擴展性。
如果沒有加表鎖，那麼tx1和tx2分配的自增將是不確定的。有類insert事先知道插入的行數，就可以避免表級auto-inc鎖，任然可以保證給予語句復制的安全性。如果不需要使用binlog來恢復或者復制，可以取消表級auto-inc鎖來提高性能。但是會出現自增值是交錯的。
對於可以知道insert行數的InnoDB可以快速分配值，而不用加鎖，前提是auto-inc沒有分配。這些insert語句獲取自增值是有mutex控制，分配過程完成就釋放。
新的鎖機制可以提高可擴展性，但是和之前比原理有些不同，可以通過參數innodb_autoinc_lock_mode配置.insert因為auto-inc鎖定的問題被分為幾類：
Insert-like:
所有在表上生成新行的語句,insert，insert select，replace,replace select,load data
Simple insert:
行插入可以預知行數。Insert on duplicate key update除外。
Bulk insert:
插入行數無法預知，insert select，replace select，load data。
Mixed-Mode Insert:
其實是simple insert 但是中間有些指定的自增列
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
有以下一些取值：
0：傳統鎖模式，所有的insert-like語句都獲取auto-inc鎖保持知道語句結束
1： 連續鎖模式，bluk insert語句會使用auto-inc鎖，simple-insert可以使用輕量的mutex。但是如果有語句使用了auto-inc就要鎖釋放。分配的自增值都是順序的。對於mixed-mode insert因為用戶指定，所以會分配比需要的更多的自增。
2：交錯鎖模式，多個語句可以同時執行，但是對於給予語句的復制是不安全的。
Innodb_autoinc_lock_mode對其他使用的影響：
在復制中自增：如果使用基於語句的復制，建議設置為1,0，保證自增值確定性。
丟失自增值和順序空隙：在所有的模式下，如果事務回滾，都會導致自增值丟失。
Bulk insert自增值空隙：在1,0下自增值分配是順序的。沒有空隙，但是如果為2，bluk insert和insert like語句一起執行的時候就可能會出現空隙。
Mixed-mode insert自增值的分配：因為混合模式，有些會指定自增，有些不會。語句在不同鎖定模式下，反應也不同：
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
Innodb_autoinc_lock_mode = 0
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
下一個值是103
Innodb_autoinc_lock_mode = 1
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
下一個值是105而不是103
Innodb_autoinc_lock_mode = 2
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| x | b |
| 5 | c |
| y | d |
+-----+------+
X,y是唯一的大於之前生成的任何行。
如果下一個自增時4，那麼上面insert語句就會報錯，建沖突。因為5會分配給b，和c沖突。
略
不要把MySQL數據庫上的表轉移到InnoDB上，不支持。一旦轉化後，MySQL不能重啟除非還原到以前的備份。不要把InnoDB表創建在NFS上。
1.一個表最多能夠包含1017個列
2.最多能有64個secondary 索引
3.單列索引，key的長度最多只能767個字節，使用innodb_large_prefix可以突破767的限制最多為3072.如果再復制情況下，如果slave不能設置，master也不要設置
4.InnoDB最大key長度為3500，但是MySQL限制到3072字節
5.若page size奢侈為8KB或者4KB對應的index key也會按比例收縮：
16KB->3072B,8KB->1536B,4KB->768B
6.最大行長度，也會根據page size變化而變化
16KB->8000B,8KB->4000B,4KB->2000B
7.在老的操作系統上，文件做多為2GB。
8.innodb 日志文件最多為512GB。
9.表空間最小為10MB，最大為64TB，也是表做大的大小。
10.默認數據庫page size為16KB。
1.全文索引
2.InnoDB支持空間數據類型，但是不能創建索引
1.通過analyze table確定索引中數據密度。，analyze table隨機采樣並且更新表的評估值。
當啟動innodb_stats_persistent啟動，要在大量修改之後運行analyze table，因為啟動後，統計信息不會被定期計算。
在生產統計信息是，可以通過innodb_stats_persistent_sample_page修改采樣率，或者使用innodb_stats_transient_sample_pages。
若join優化器優化有問題，可以使用analyze table重新分析。也可以使用force index強制縮影使用。也可以通過變量max_seeks_for_key，設置最大seek，超過就變掃描。
2.若語句或者事務在一個表上運行，並且有一個analyze table運行，後面還跟了一個analyze table。那麼第二個analyze table就會被堵塞。直到語句或者事務完成。因為analyze table 運行完成必須要標記當前加載的表定義過期。新的事務或者語句必須加載新的表定義。在當前事務沒有完成前老的表定義不能被清理。
3.show table status不能提供准確的統計信息，只能看被表保留的物理空間。InnoDB內部不保存行數。准確的行數需要count，若不需要太准確可以使用show table status的結果。
4.在windows平台上，innodb數據庫和表名內部都用小寫保存
5.auto_increment列必須為索引的一部分
6.在auto_increment初始化的時候，innodb會在和自增列相關的索引尾上加一個排它鎖。當訪問auto-increment列時innodb使用特定的auto-inc鎖，直到語句完成。
7.當重啟mysql服務，innodb可能重用老的值，因為分配後沒有被保存到表裡面。
8.當auto_increment超出之後的insert會報錯重復key錯誤。
9.delete from table_name 不會重新生成表，是刪除所有行。
10.外鍵的級聯操作，不會觸發觸發器
11.不能創建列和innodb內部列一樣的名字。
1.在MySQL層lock table會鎖定MySQL層，也會請求InnoDB 表鎖。如果innodb_table_locks=1，lock tables會申請mysql層的表鎖和innodb的表鎖。如果為0不需要申請innodb的表鎖。在沒有innodb表鎖情況下，lock tables在其他事務在表上鎖定了一些記錄的時候，也可以成功鎖定。
2.innodb鎖在事務內有效，事務結束後釋放。
3.不能再事務中鎖定其他表，因為lock tables會隱式執行commit和unlock tables。
4.從1023並發事務修改，被升級到128*1023,同事寫入undo record.
使用SQL語法和MySQL配置，可以創建壓縮保存數據的表。壓縮可以提高原設備的性能和可擴展性。壓縮也表示內存和磁盤傳輸變小。占用的空間變小。
InnoDB可以創建壓縮表，row_format=compressed並制定較小的page size。Page越小請求的I/O越少，特別是SSD。
Page size可以通過key_block_size指定。不同的page size 表示表必須在自己的ibd文件裡面，必須啟動innodb_file_per_table。如果key_block_size越小，因為page越小，I/O效率提高。但是如果過分小，如果不能壓縮的單個page中，就需要額外的操作去重組page。
在buffer pool壓縮數據以小page保存。Page size依據key_block_size，對於提取和更新，MySQL會分配16KB的非壓縮數據。然後在生產壓縮頁。可能需要加大buffer pool來保存壓縮和非壓縮頁，盡管非壓縮頁會在內存不夠時被犧牲。
在創建壓縮表前，先要啟動innodb_file_per_table和innodb_file_format=Barracuda然後創建表或者修改，並指定row_format=compressed,key_block_size。
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1
(c1 INT PRIMARY KEY)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
1.若指定了row_format=commpressed可以忽略key_block_size,默認page是innodb_page_size的一半。
2.指定了key_block_size，可以忽略row_format=compressed，會自動啟動壓縮。
3.確定key_block_size的最好方法是，對同一個表按size不同分配創建壓縮，並且比較負荷。
4.key_block_size被認為是提示，如果是0就表示innodb_page_size的一般，key_block_size<=innodb_page_size,若innodb_page_size>key_block_size就會被忽略，並設置為innodb_page_size的一半。如果innodb_strict_mode=on則會報錯。
5.其他性能相關的看14.7.3 壓縮表調優
MySQL key_block_size可以1KB，2KB，4KB，8KB，16KB，但是不會影響壓縮本身的算法，只是決定每個chunk的大小。
設置Key_block_size等於InnoDB page size就表示可能沒有太大的壓縮比率。但是對於TEXT，BLOB，varchar還是有用的，可以減少overflow page。
表上的所有索引被壓縮都使用相同的page size。由create table或者alter table指定。Create index中的row_format，key_block_size不是innodb表的屬性，如果指定會被忽略。
壓縮表的限制：
Innodb系統表空間不能使用表壓縮。壓縮只能應用在整個表和索引上。不能用在指定行集上。
壓縮比率和數據有關，但是可以決定壓縮表的性能影響：
1.什麼表要壓縮。
2.使用多大的page size
3.runtime情況下如何調整buffer pool大小。
4.若系統執行了DML操作在壓縮表上，而數據是分布方式導致高昂的壓縮操作失敗，可能需要額外的高級配置選項。
一般壓縮在讀多寫少的表上效果好。
關鍵決定壓縮效果好壞的是數據本身。決定是否使用壓縮表，需要測試，可以使用gzip對ibd文件壓縮，簡單的評估壓縮比率。MySQL的壓縮比率比工具的壓縮比率少，MySQL只能以key_block_size進行壓縮，並且系統數據不會被壓縮。而壓縮工具可以有更大的chunk，壓縮比率會更高。
另外一種方法是復制為壓縮的表，然後進行壓縮，看壓縮比率。
查看在特定負荷下，壓縮是否是有效率的：
1.簡單的測試，在實例上只有一個壓縮表，測試並查看information_schema.innodb_cmp表。
2.若果要做精心的測試，運行information_schema.innodb_cmp_per_index表，因為表信息收集開銷比較大，需要啟動innodb_cmp_per_index_enabled。
3.檢查成功壓縮的比率，對比innodb_cmp_per_index表和innodb_cmp表。
4.如果壓縮成功操作率很高，那麼比較適合做壓縮。
5.如果壓縮失敗比較好，應該調整innodb_compression_level，innodb_compression_failure_threshold_pct,innodb_compression_pad_pct_max選項。
決定是否在數據庫壓縮或者在應用程序壓縮，如果都壓縮只會浪費cpu。
當啟動，MySQL表的壓縮是自動應用到所有的列和索引上。
在應用程序中壓縮，insert之前程序需要壓縮文本數據。可以減少負荷，但是會出現有的數據壓縮比率高，有的數據壓縮比率低。
在決定要壓縮什麼表的時候，工作負荷特點也是會影響性能的重要因素之一。若應用程序以讀為主，基本不需要重新組織和壓縮索引page。Delete相對高效，因為可以通過修改非壓縮數據表示數據已經被刪除。
如果你的工作負荷是IO密集的，不是cpu密集，使用壓縮表可能可以提高性能。
壓縮試圖通過CPU壓縮，解壓，來減少I/O。尤其是在多用戶，多cpu環境下。Page在內存中被壓縮往往會保留非壓縮壓在內存中。LRU算法會試圖平和壓縮頁和非壓縮頁。
壓縮page大小設置，依賴於數據類型和分布，壓縮page size必須必最大的行大小大，不然會報錯。
Page太大會浪費空間，若果太小insert，update要花時間重新壓縮導致b樹分頁頻繁，導致文件變更大，但是查詢效率很低。
通常，可以設置壓縮page 為8k，4K。
查看整體程序性能，CPU，I/O和文件大小可以很好的表示壓縮效率。要深入分析壓縮表性能，你需要監控information_schema中的表，這些表整體的反應了內部的內存使用和壓縮率。
Innodb_cmp表，根據page大小來收集信息。在沒有其他壓縮表情況下，可以使用這個數據。
Innodb_cmp_per_index,對每個表和索引進行信息收集。這些信息對於診斷性能問題很有幫助。需要去啟動參數innodb_cmp_per_index_enabled才能啟動表數據的收集。
最關鍵的是考慮壓縮和解壓的次數。比較innodb_cmp_per_index在innodb_cmp中成功壓縮的比率，調整buffer pool，調整page size，或者不壓縮。
如果因為壓縮cpu上升，可以升級cpu，增加buffer pool，可以讓壓縮和非壓縮的頁都放在內存減少減壓操作。
大量的壓縮操作，說明寫入壓縮表操作頻繁，影響壓縮效率。如果成功壓縮操作 (COMPRESS_OPS_OK)在總操作(COMPRESS_OPS)比率高，表示系統執行良好。若比率低，考慮不要壓縮這個表或者增加頁大小，比率低說明MySQL重新組織，重新壓縮，分頁操作頻繁。如果壓縮失敗大於1%~2%考慮停止壓縮。
MySQL使用LZ77壓縮，是無損壓縮通常壓縮率在50%以上。InnoDB壓縮應用在用戶數據和索引中，很多情況下，索引占了40%~50%甚至更高的比率，所以差異是很明顯的。可以通過參數innodb_compression_level來平衡cpu和壓縮率。
Innodb所有數據都以b樹方式存儲，索引也是b樹，索引會包含索引key和一個指向聚集索引的指針。Btree的壓縮和overflow的壓縮不同，後面章節解釋。
因為連續的更新，b樹要特別對待，最小化分頁，最小化解壓重新壓縮。MySQL以非壓縮的方式，可以提高一些in-place update，比如delete-marked。
另外MySQL視圖避免不必要的解壓，重新壓縮。在每個btree page系統維護了一個未解壓的“modification log”來記錄page的修改。小的insert和update會被記錄在這個log中，沒必要去重構整個page。
當modification log空間不足，InnoDB解壓page，應用修改並重新壓縮page。如果重新壓縮失敗，btree分頁，這個操作會持續直到成功insert或者update。
為了避免頻繁出現壓縮錯誤在寫入密集負荷中，MySQL有時候會保留一些空閒空間，就是pge的padding空間。這樣modification log會執行的比較快。Page重新壓縮也有足夠的空間，不會分頁。
在繁忙的系統壓縮表插入頻繁，可以調整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max。
通常MySQL要求page要包含2條記錄，對於壓縮表，要求在非壓縮頁上容納一行記錄。如果再innodb_strict_mode=on MySQL在create table，create index的時候檢查最大行大小，如果不能放入，會出現row too big的錯誤。
如果innodb_strict_mode=off,接下來的insert或者update試圖創建row的時候因為無法插入就會報錯。為了解決這個問題重新設置key_block_size，或者不要壓縮。
在InnoDB中BLOB,VARCHAR,TEXT可能保存在overflow。Row_format=dynamic或者compressed，BLOB,VARCHAR,TEXT字段可能保存在page外面，聚集索引保存了20B的指針指向這些page。當行太長不能被保存在同一個page中，MySQL會選擇一個最長的列保存到off-page中.
Note
如果row_format=dynamic或者compressed如果text，blob>=40個字節會被in-line保存。
如果是Antelope文件格式，MySQL會保存前768字節，後面的20字節指向後面的overflow page。
如果表是compressed格式的，所有的overflow page也會被壓縮。MySQL使用zlib壓縮算法壓縮一個數據項。壓縮的overflow頁包含非壓縮的頭和一個page checksum和連接到下一個overflow的連接。所以如果blob，varchar，text是重大的空間節約。Image數據一般都是被壓縮過的，再壓縮只會浪費cpu。
使用16KB壓縮頁大小，也可以減少blob，varchar，text的IO消耗。
在壓縮的innodb表，壓縮頁是1KB，2KB，4KB，8KB。16KB不壓縮。為了最小化I/O和page解壓，buffer pool包含壓縮的page和未壓縮的page。為了空間MySQL會犧牲buffer pool中的非壓縮，或當有一段時間沒有訪問後，會把壓縮寫入disk釋放內存。
MySQL使用自適應LRU算法來決定壓縮頁和非壓縮頁之間的平衡。目標是避免cpu密集的時候，花時間去解壓。Cpu空間的時候避免I/O。
當I/O忙，會犧牲非壓縮，釋放內存。
當cpu忙，會保留壓縮和非壓縮page。
在壓縮頁寫入數據文件前page會被寫入到redo log，保證redo log了，crash之後依然可用。因此一些實例會變大，或者checkpoint會變頻繁，由壓縮頁被修改導致重新組織和重新壓縮決定。
通常推薦在只讀，或者讀多寫少的負荷下做壓縮。在SSD上使用壓縮,可以減少空間和IOps，即使在寫多的負荷下使用。
壓縮的配置：
1.innodb_compression_level，設置壓縮級別，越高，就可以放入越多的數據
2.innodb_compression_failure_threshold_pct，指定壓縮錯誤率的中止點，如果超過這個值，MySQL在分配新頁的時候保留一些空白空間，自動調整空閒比率。最大值由innodb_compression_pad_pct_max來決定。
3.innodb_compression_pad_pct_max,可以調整每個頁做大的保留空間。
Row_format=comressed或者key_block_size在create table或者alter table語句中，如果沒有啟動Barracuda文件格式，會有用以下幾個錯誤。
Level
Code
Message
Warning
1478
InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning
1478
InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1
Warning
1478
InnoDB: ignoring KEY_BLOCK_SIZE=4.
Warning
1478
InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning
1478
InnoDB: assuming ROW_FORMAT=COMPACT.
默認這些警告，會導致表創建但是不帶壓縮功能。當啟動了innodb_strict_mode就會報錯。
在“non-strict”條件下，mysqldump可以導入到不支持壓縮的數據庫。如果源數據庫中有壓縮表，那麼就會創建row_format-compact而不是阻止錯誤。當dumpfile導入到新數據庫，在原始庫上的表都會被重建。保證服務設置了適當的innodb_file_format或者innodb_file_per_table。
Key_block_size設置只允許在row_format=compressed下使用，或者被忽略。
Level
Code
Message
Warning
1478
InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.
如果設置了innodb_strict_mode就不會警告，會直接報錯，表不會被創建。
Create table和alter table選項
Option
Usage Notes
Description
ROW_FORMAT=REDUNDANT
Storage format used prior to MySQL 5.0.3
Less efficient than ROW_FORMAT=COMPACT; for backward compatibility
ROW_FORMAT=COMPACT
Default storage format since MySQL 5.0.3
Stores a prefix of 768 bytes of long column values in the clustered index page, with the remaining bytes stored in an overflow page
ROW_FORMAT=DYNAMIC
Available only withinnodb_file_format=Barracuda
Store values within the clustered index page if they fit; if not, stores only a 20-byte pointer to an overflow page (no prefix)
ROW_FORMAT=COMPRESSED
Available only withinnodb_file_format=Barracuda
Compresses the table and indexes using zlib
KEY_BLOCK_SIZE=n
Available only withinnodb_file_format=Barracuda
Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies ROW_FORMAT=COMPRESSED
當innodb_strict_mode=off,會忽略設置，並生產警告，如果為on，會生產錯誤，不會被創建。
Syntax
Warning or Error Condition
Resulting ROW_FORMAT, as shown in SHOW TABLE STATUS
ROW_FORMAT=REDUNDANT
None
REDUNDANT
ROW_FORMAT=COMPACT
None
COMPACT
ROW_FORMAT=COMPRESSED orROW_FORMAT=DYNAMIC orKEY_BLOCK_SIZE is specified
Ignored unless bothinnodb_file_format=Barracuda andinnodb_file_per_table are enabled
COMPACT
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16)
KEY_BLOCK_SIZE is ignored
the requested row format, or COMPACT by default
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZE are specified
None; KEY_BLOCK_SIZE specified is used
COMPRESSED
KEY_BLOCK_SIZE is specified withREDUNDANT, COMPACT or DYNAMICrow format
KEY_BLOCK_SIZE is ignored
REDUNDANT, COMPACT orDYNAMIC
ROW_FORMAT is not one ofREDUNDANT, COMPACT, DYNAMIC orCOMPRESSED
Ignored if recognized by the MySQL parser. Otherwise, an error is issued.
COMPACT or N/A
在InnoDB中，新的文件格式，就意味著新的特性。
Innodb_file_format選項在創建新的表的時候起作用。並且必須要啟動innodb_file_per_table。目前只支持Antelope和Barracuda文件格式。Innodb_file_format可以再配置文件中設置，也可以通過set global設置。
向後兼容性
新的InnoDB創建的表可能不會安全被上個版本的InnoDB讀寫。InnoDB 1.1提供了保證這些條件的機制，來保證數據文件和innodb版本的兼容性，這個機制可以使用新版本提醒，有要保留這些選項，並防止不兼容特性的使用。
若改版本InnoDB支持某版本的文件格式，那麼就可以讀寫任意這個版本之前版本的表。特定的文件格式會限制一些特性，反過來若表空間使用了當前不支持的文件格式，那麼就無法訪問。只能降級innodb表空間到之前的版本，然後復制到新的表。
最簡單決定文件格式的方法是使用show table status命令或者查詢表tables的row_format字段。
內部細節
每個innodb表空間都有一個文件格式標記，系統表空間是hightest。創建壓縮表和dynamic會更新ibd文件頭，在innodb數據字典中標記barracuda文件格式。InnoDB會根據這個標記來檢查兼容性。
Ib_file集合的定義
Ib_file集合包含：
1.系統表空間一個或者多個ibdata文件，包含了系統內部信息。
2.多個簡單表空間，*.ibd文件
3.InnoDB日志文件，ib_logfile0,ib_logfile1。Redo日志文件。
Ib-file集合，不包含frm文件。Frm郵件是由mysql創建的。
為了防止在打開ib-file set的時候出現crash，就會檢查是否完全支持ib-file的文件格式。若當前軟件的格式太新，在恢復的階段，會照成驗證的數據破壞，因此啟動檢查文件格式可以防止之後的一些列問題。
從innodb 1.0.1版本開始系統表空間會記錄，把使用過的最高的文件格式作為ib-file set的一部分。文件格式檢查有參數innodb_file_format_check控制。如果當參數為on，並且mysql支持版本低於文件的版本。
InnoDB: Error: the system tablespace is in a
file format that this version doesn't support
有些情況下，可能要讀取太新的文件，如果innodb_file_format_check=off。Innodb打開數據庫的時候會警告：
InnoDB: Warning: the system tablespace is in a
file format that this version doesn't support
Note
如果允許怎麼運行，是很危險的。
參數innodb_file_format_check,影響數據庫被打開的時候，innodb_file_format只決定在新表是否可以以某格式創建，並不影響是否可以打開數據庫。
若有更高的格式的表被創建或者已經存在可讀寫的表的格式高於當前軟件。系統表不會被更新，但是會啟動表級別的兼容性檢查。
當表被訪問，InnoDB會檢查文件格式。防止當表使用太新的數據結構時，crash，損壞。只要release支持可以讀寫任意文件格式的表。指定innodb_file_format會阻止創建一個指定特定文件格式的新表，甚至是release是支持的。這樣設置會阻止向後兼容性，但是不會妨礙對支持格式的訪問。
InnoDB在文件打開的會後檢查文件格式兼容性。若現在InnoDB把呢吧不支持InnoDB文件中的標記。
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
也會寫入error log
InnoDB: table test/t1: unknown table type 33
盡管innodb設置了參數innodb_file_format_check,允許打開不支持文件格式，表級別檢查還是會被應用。
Innodb_file_format只影響新增的表，若你新增了一個表，表被標記使用最早的文件格式。比如啟動了barracuda並且創建了新表，沒有使用dynamic，compressed。那麼表會被標記為Antelope。
如果得知表或者表空間，可以使用表tables，或者show create table獲得文件格式。若沒有使用comressed，dynamic。就會使用Antelope的文件格式，row_format為redundant或者compact。
內部細節
InnoDB有2個文件格式，Antelope，Barracuda，4個不同的行格式。文件和行格式，以32bit卸載文件的54位置。可以使用od -t x1 -j 54 -N 4 tablename.ibd.查看。
表空間flag錢10個bit：
0bit:0表示Antelope,1-5bit為0,1表示Barracuda，1-5為1.
1-4bit：表示壓縮文件的page size，1=1k，2-2k，3=4k，4=8k。
5bit：antelope=0,Barracuda=1.
6-9bit：4字節數值，0=16K,3=4K,4=8K,5=16K
10bit：表空間位置，0=默認，1=使用了data directory子句。
Table flag要使用語句查看中的flag：
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
前7bit：
0bit:0=Redundant 行格式，1-5就為0。1=compact 行格式。
1-4bit：4bit數值，表示壓縮頁，1=1k，2-2k，3=4k，4=8k。
5bit:0:Antelope,1=Barracuda，如果為1,0bit也要為1
6bit: 表空間位置，0=默認，1=使用了data directory子句。
最簡單的方法：
ALTER TABLE t ROW_FORMAT=format_name;
行和相關列的存儲會影響查詢和DML性能。更多的行放到一個page裡面，查詢的時候會更快，內存更好，I/O也會變少。InnoDB中數據被放入page中，page通過構成B樹結構來保存。一行的數據都被保存在page裡面，但是變長字段是例外。因為太長會溢出，所以這些只被保存在overflow page上。有個link連接到這些page。
語句如下：
CREATE TABLE t1 (f1 int unsigned) ROW_FORMAT=DYNAMIC ENGINE=INNODB;
Dynamic，compressed只有在文件格式為Barracuda才能使用。Barracuda也支持compact，redundant格式。
使用Dynamic，compressed長列會被保存在off-page上。聚集索引上會保留20byte的執行。
是否使用off-page，取決於page大小和行大小。當行太長，innodb會吧最長的列作為溢出直接放到溢出page。Text，blob如果小於40字節都會in-line保存。
如果行可以存放在page中，Dynamic行模式也是很有效的。Dynamic為有長數據保存在off-page設計的。因此效率比老的行模式好。
Compressed行格式off page的存儲和dynamic類似。區別是compressed對表和索引進行了壓縮。
早前的InnoDB使用者2個行格式。對於溢出，InnoDB會把錢768字節保存在page上，後面的保存在溢出頁上。對於少於768的列會被保存在page內，對於有很多blob的列會顯得太滿。
在linux和windows平台，innodb使用OS自帶的來支持原生異步I/O。在其他平台使用模擬的異步I/O。
預讀
如果innodb可以決定，數據被馬上需要的可能性很高，就會執行預讀把輸入讀入內存，預讀有2種方式：
1.線性預讀，當innodb發現訪問方式是順序的，發起額外的page讀。
2.隨機預讀，innodb發現數據可能要全部處理，會持續的讀。
雙寫buffer
InnoDB使用新文件刷新技術，涉及到雙寫buffer。增加了crash的安全性和提高性能減少fsync()。
在寫入數據文件之前，會先寫入到雙寫buffer。寫入到雙寫buffer之後innodb把這些寫入到數據文件中。
為了避免index和表都保存在系統表空間，使用參數innodb_file_per_table，每個新的創建表有獨立的表空間文件，這樣可以減少文件碎片。當表被截斷可以返回給系統，而不是系統表空間。
page，extents，segments和tablespace
tablespace由page組成innodb_page_size來制定page的大小。組成1MB的extents，表空間中的文件在innodb中被稱為segment。一開始innodb分配32page，之後會分配整個extents。innodb可以一次性分配4個extents。innodb會為每個索引分配2個segments，一個非葉子，一個葉子。葉子節點在磁盤中連續，順序I/O操作順序會更好。因為葉子節點包含了表的實際數據。
表空間中的有些page包含其他page的bitmap。因此有些extents在表空間中，不能被整體分配。只能page單獨分配。
當使用show table status查看表空間中可用的空閒空間。會發現有些extents是空閒的。innodb為了清理和啟動目的而保留的一些空間。
空閒出來的空間是可以讓別的用戶使用依賴於是否釋放了幾個page還是extentds。drop table或者delete所有行空間釋放給用戶，但是物理刪除操作在事務回滾和一致性讀不需要的時候執行。
page如何關聯表記錄
行最大記錄為8000字節。longblob和longtext必須少於4GB，所有的行長度，包含blob和text必須少於4gb。
若行少於一半的page，則都有放在page中，若超過了一半page會選擇變成放入overflow，直到可以放入page的一半。
檢查點如何處理進程
innodb實現了一個模糊檢查點的機制。innodb會刷新小批量的髒數據，但是刷新時會中斷語句執行。
在crash恢復，innodb會查看寫入到log文件的檢查點標記。檢查點標記表示在標記之前的數據都被寫入到了磁盤中。innodb應用之後的log即可。
隨機插入和刪除會造成碎片，碎片表示空隙。碎片的症狀是會花比更多的空間。另外一個症狀是掃描會花更多的時間。
為了整理碎片要定期執行空的alter table。
ALTER TABLE tbl_name ENGINE=INNODB
alter table tbl_name engine=innodb和alter table tbl_name force都使用online DDL(ALGORITHM=COPY)。
表有自己的表空間時，truncate table空間會被os回收。truncate table表和其他表不能有外鍵約束。
當表被truncate，會刪除並且重建表，生成ibd文件，並且空閒空間返回給OS。
Online DDL有一些好處：
1.提高了繁忙環境的相應和可用性，避免在繁忙環境下為了一個新的索引停止很長時間。
2.可以讓你在性能和並發性上平衡。
3.可以in-place完成，不需要創建一個新的表。
歷史上，沒有online操作，很多alter table操作會創建一個新表來代替老的表。MySQL 5.5，MySQL 5.1之後 innodb plugin create table和drop table來避免表復制行為。叫做快速索引創建。MySQL 5.6之後所有的alter table都可以避免表復制。
新的機制通過先導入數據再創建索引方式來提高速度。雖然在語法上沒有改變，但是一些特性會影響性能，空間使用和操作的語義。
Online DDL增強了很多DDL操作，表復制，DML Blocked。
Secondary Index
創建索引，CREATE INDEX name ON table (col_list) or ALTER TABLE table ADD INDEXname (col_list).
刪除索引，DROP INDEX name ON table; or ALTER TABLE table DROP INDEX name
MySQL 5.6版本之後，索引被創建或刪除時，可以讓DML繼續使用。
類屬性
設置默認值，ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal or ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT
修改自增下一個值, ALTER TABLE table AUTO_INCREMENT=next_value;
重命名列， ALTER TABLE tbl CHANGE old_col_name new_col_name datatype
外鍵
外鍵創建和刪除
ALTER TABLEtbl1ADD CONSTRAINTfk_nameFOREIGN KEYindex(col1) REFERENCEStbl2(col2)referential_actions;
ALTER TABLEtblDROP FOREIGN KEYfk_name;
使用foreign_key_checks啟動主鍵和外鍵。
若不知道約束名，可以使用語句查看：
show create table table\G
也可以在一個語句內刪除外鍵和索引
ALTER TABLEtableDROP FOREIGN KEYconstraint, DROP INDEXindex;
如果已經存在altered表中，應用DDL操作有額外的限制。
1.在這些子表上在線DDL不允許並發DML
2.alter table子表會等待另外一個事務提交。如果在父表上的修改會影響到子表，比如設置了cascade，或者set null。
若一個表是父表，若DML帶有on update，on delete那麼就要等alter table 完成。
注意ALGORITHM=COPY
alter table使用algorithm=copy,並發DML就不能使用。並發查詢還是允許。表復制操作在lock=shared下運行，也可以在lock=exculusive下。
並發DML和表復制
有些DML操作允許並發DML但是還是要執行表復制。以下操作比MySQL 5.5快：
1.添加，刪除，重排列
2.增加，刪除主鍵
3.修改row_format和key_block_size
4.修改列是否為空
5.optimize table
6.force選項來創建表
7.alter table engine=innodb來重建表。
Note
盡管 online DDL很有效，但是還是要創建一個穩定的表結構。主要的例外是secondary index，對於大表，通常是最後建比較有效。不管create table，index，alter table什麼順序都可以使用show create table來獲得當前表結構。
Online DDL有幾個方面的提升：
1.DDL操作時，也可以查詢和DML，可用性更高。
2.對於in-place操作，重建表時，避免disk I/O，CPU。
3.對於in-place操作，讀入的數據更少，讀入buffer pool的數據比復制所有的數據少，避免臨時的性能問題。
如果online操作需要臨時文件，InnoDB創建在零食文件夾中，而不是在數據目錄中。
Online DDL鎖操作
在DDL操作時，表可能會被鎖，取決於內部操作過程和lock子句。默認mysql是使用最小的鎖定。如使用的鎖不支持就會報錯。
1.對於DDL操作，使用lock=none,查詢和並發DML都可以被允許。如果DDL不允許lock=none，alter table就會報錯。
2.對於DDL操作，使用lock=shared，任何寫入都會被block，若不支持鎖定類型就會報錯。
3.對於DDL操作，使用lock=default，mysql使用最小的鎖級別。
4.對於DDL操作，使用lock=exclusive,查詢和DML都會被block。
Online DDL語句都等innodb表上的事務運行完成後再運行，因為DDL在准備時要申請X鎖。online DDL操作可能要比老的DDL操作要慢。
In-Place vs Table-Copy DDL
online ddl的性能受到in-place，copying和rebuilding整個表影響很大。in-place使用在secondary上，不能使用在primary，primary上依然只能使用copying data。
當使用子句ALGORITHM=INPLACE,盡管使用的是copy data，但是性能還是要比ALGOTITHM=COPY好：
1.沒有undo或者徐昂管的redo生成。
2.secondary可以預先排序，所以可以按順序被導入
3.沒有使用change buffer，因為沒有隨機寫入到secondary索引。
簡單的識別是否使用了inplace或者copy就是看相應行數，如果響應為0行，說明inplace：
1.修改列的默認值，沒有響應行數
Query OK, 0 rows affected (0.07 sec)
2.增加索引(雖然然有時間花費，但是沒有響應行)
Query OK, 0 rows affected (21.42 sec)
3.修改數據類型(時間消耗，並且重建了表)
Query OK, 1671168 rows affected (1 min 35.54 sec)
在處理大表之前，最好先進行測試：
1.克隆表結構
2.導入少量數據
3.執行DDL操作
4.檢查響應行數，看是否inplace
Table 14.6, “Summary of Online Status for DDL Operations” 列出了那些DDL操作可以inplace，允許並發DML，那些需要特定的設置或者alter table。
可以通過ALGORITHM和lock來控制online DDL的不同方面。Lock是並發性控制，ALGORITHM是和老的copy table最主要的區別之一，如：
1.避免了不小心，讓表處於不可讀，寫狀態，可以通過alter table lock=none來避免。
2.可以使用ALGORITHM=inplace和ALGORITHM=copy來比較性能。
3.使用ALGORITHM=inplace,如果語句不能用inplace方式，會立刻停止。
DDL語句，可以把多個DDL操作放在一起，因為每個alter table都會涉及復制和重建表，可以一次性全部完成。
如果可以使用inplace，就可以把多個DDL分解成多個語句
ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),
CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;
分解為：
ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;
有些情況下，還可以使用多alter table：
1.如果操作需要特定的順序
2.如果操作使用相同的lock，並且要一起成功或者失敗。
3.操作不能以inplace方式執行，任然需要復制，重建表。
4.或者指定了，ALGORITHM=COPY或者old_alter_table=1
具體看：14.10.5 Examples of Online DDL
任何alter table操作都有以下幾個方面：
1.是否會修改表的物理表示，或者只修改元數據，不會涉及表本身。
2.數據量是否會變化
3.修改的表是否涉及，聚集索引，secondary索引或者兩則
4.是否和其他表有外鍵關系
5.是否分區，如果是分區表，會被調到低級別操作涉及到多個表，這些操作要是用Online DDL需要特定的規則。
6.表是否一定復制，是否表可以被以inplace方式重組。
7.表是否包含自增列
8.需要的鎖級別。
Online DDL的錯誤條件
以下是online ddl報錯的主要原因：
1.如果指定了鎖級別過低，DDL操作不支持。
2.等到鎖曹氏
3.tmpdir文件系統超出了空間
4.alter table太長導致超出innodb_online_alter_log_max_size。
5.如果並發DML修改表，在之前的定義被允許，但是在新的不被允許。
Online DDL在有沒有啟動innodb_file_per_tale無關。innodb中有2個索引，聚集索引和secondary索引。聚集索引是表本身，所以所有的修改都會導致，copy。secondary索引是表的副本，也只是一部分數據，不需要copy。
刪除secondary索引比較簡單，只要修改元數據即可。增加索引，innodb會掃描並使用buffer和臨時表來排序。這樣比隨機插入到一個建好的索引效率高。因為當page滿了會產生分頁。
Primary Key和Secondary Key索引
MySQL服務和InnoDB會保存他們的元數據。MySQL服務會把信息保存在frm文件中。InnoDB把數據字典保存在系統表空間中。如果DDL操作crash中斷，造成2邊數據不一致，會出現啟動問題，表不能被訪問。
雖然不會丟失數據，但是恢復方式和b樹索引不同。如果在創建secondary索引crash。MySQL會刪除創建的索引，需要重新創建。
創建聚集索引時，因為數據從一個表復制到另外一個表，crash會比較復雜。MySQL在創建時先復制數據到臨時表，一旦完成，新表替換老的表。
若在創建新聚集索引，系統crash，沒有數據丟失但是使用臨時表來恢復。因為很少出現所以手冊不提供這樣的場景。
除了alter table分區語句， online ddl分區表和普通表類似。alter table分區子句內部api和非分區表不同只能使用ALGORITHN=DEFAULT和lock=default。
如果在alter table上使用alter table分區子句，使用alter table copy算法分區表會被重新分區。也就是說分區表以新的結構被重建。
若不使用alter table修改分區表分區，alter table會在每個分區上使用inplace算法。使用inplace的alter table選項，分區越多會需要越多的資源。
盡管分區表和非分區表使用的alter table api不同，MySQL依然視圖最小化數據復制和鎖定：
1.add partition和drop partition在range或者list分區上不會復制數據。
2.truncate partition不會復制數據。
3.在range，list上進行add partition和coalesce partition，可以和查詢並發。
4. REORGANIZE PARTITION, REBUILD PARTITION,ADD PARTITION 或者 COALESCE PARTITION在linear hash或者list上，允許並發查詢。
當執行DDL有一下限制：
1.當執行online ddl時，復制表，寫入臨時目錄，臨時表要足夠大。
2.alter table drop inxex和add index子句，2就會使用表復制，而不是快速索引創建。
3.在臨時表上創建索引，使用表復制。
4.在刪除被外鍵使用的索引會報錯。
5.不能在on…cascade或者on…set null語句下使用alter table lock=none
6.在online DDL時不管使用什麼lock子句，在開始和結束都會請求X鎖。所以要等待上個事務完成。
7.當執行online alter table，執行alter table的會啟動一個online log，用於記錄其他會話的DML操作。當執行DML操作就有可能會出現重復鍵錯誤。可能錯誤是暫時的，之後在online log中被恢復。
8.InnoDB表的OPTIMIZE TABLE被映射到alter table操作，重建表並更新索引，釋放沒用的空間。
9.在5.6版本之前，innodb不支持alter table ALGORITHM=INPLACE。
查看：14.11 InnoDB Startup Options and System Variables
預讀是I/O請求異步的獲取多個page。InnoDB有2個預讀算法來提高I/O性能：
Liner
線性預讀根據buffer pool中的page，預測需要什麼page。通過參數調整什麼時候執行預讀，讀取多少page，使用innodb_read_ahead_threshold就可以。
innodb_read_ahead_threshold值越大越不會觸發。
Random
若buffer pool找打了13個page來自同一個extent，innodb會啟動異步I/O獲取其他page。由innodb_randmon_read_ahead參數控制。
SHOW ENGINE ENNODB STATUS顯示統計信息來評估預讀算法的效果。innodb_buffer_pool_read_ahead通過預讀讀入的page，innodb_buffer+pool_ahead_evicted,通過預讀讀入但是被犧牲的page。
當髒頁超過innodb_max_dirty_page_pct時之後會啟動刷新進程。innodb使用算法根據當前redo log生產和當前的刷新來生產當前刷新率。
innodb使用log文件，在重用log前，要把相關的髒頁寫入到數據文件中，這就是sharp checkpoint，當log file中所有可用空間都用完了，也會發生sharp checkpoint，即使innodb_max_dirty_page_pct沒到達也會發生。
innodb通過啟發式的算法，避免這種情況發生。通過評估dirty和生成的redo來決定flush多少髒頁。
因為自適應flush會影響I/O,innodb_adaptive_flushing可以開關自適應flush，默認為true。
InnoDB的LRU算法目的是讓hot數據盡量保存在內存中。新的page被讀入默認插入到LRU列表距離尾部3/8處。若再次被提到隊列前面，如果不再被訪問，就不放到隊列前面。可以通過參數innodb_old_block_pct來調整插入到LRU列表的點。默認是37也就是3/8處。取值范圍是5-95。
為了避免預讀有類似問題，影響buffer pool配置innodb_old_blocks_time判斷第一次訪問後放入列表頭的倒計時。
innodb_old_blocks_pct和innodb_old_blocks_time可以在配置文件，啟動參數，也可以set global設置。
通過show engine innodb status命令查看：
Total memory allocated 1107296256; in additional pool allocated 0
Dictionary memory allocated 80360
Buffer pool size 65535
Free buffers 0
Database pages 63920
Old database pages 23600
Modified db pages 34969
Pending reads 32
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 414946, not young 2930673
1274.75 youngs/s, 16521.90 non-youngs/s
Pages read 486005, created 3178, written 160585
2132.37 reads/s, 3.40 creates/s, 323.74 writes/s
Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000
Pages read ahead 1510.10/s, evicted without access 0.00/s
LRU len: 63920, unzip_LRU len: 0
I/O sum[43690]:cur[221], unzip sum[0]:cur[0]
old database pages表示LRU 列表 old段的page個數
pages made young和not young，表示old段的page被標記為young和沒有被標記的個數。
youngs/s和non-youngs/s，表示表示被youngs的速度。
young-making rate和not提供了一樣的速率，但是是整個buffer pool的不是old段。
Note
每秒的速度是通過上次監視器和這次監視器的取值的差值。
這些選項根據硬件配置關系較大，所以在設置前要先進行壓力測試。
在混合負荷下，oltp為主，和定期的報表查詢，可以使用innodb_old_blocks_time參數。
當讀取的表放不進buffer pool可以通過設置innodb_old_blocks_pct，如果為5 表示只有5%的buffer pool別使用，不影響其他的buffer pool。
若掃描表的表，innodb_old_blocks_pct可以默認或者更高，對性能影響較小。
innodb_old_blocks_time更難預測，因為和負荷類型關系密切。
對於有幾個G的buffer pool可以吧buffer pool分為多個實例提高並發減少沖突。可以使用innodb_buffer_pool_instances來配置。使用innodb_buffer_pool_size來配置buffer大小。
當buffer pool變大，很多數據可以存放在buffer pool中，但是多個線程同時訪問buffer pool可能會出現瓶頸。可以使用多個buffer pool來最小化爭用。每個page被分配到哪個buffer pool是隨機的。每個buffer pool都有自己的信號量。
Innodb_buffer_pool_instances取值1~64，只有當innodb_buffer_pool_size>1GB，上面的參數才有用。指定的總大小會被分到所有的buffer pool。
為了避免重啟後長時間的warm up，你可以再服務關閉的時候保存innodb buffer pool狀態，在啟動的時候，恢復buffer pool狀態。
一般重啟之後，warm up是逐步增加的，把之前的page加入到buffer pool。Preload可以再關閉之前的buffer pool加載到內存。而不需要DML來warmup。
你可以再服務運行的任何時間，保存buffer pool狀態。盡管buffer pool有好幾G的大小，但是保存數據還是很少的，值保存了表空間id，page id用來定位page的數據。這些數據來源於INNODB_BUFFER_PAGE_LRU 默認表空間id，page id被保存在ib_buffer_pool,innodb_buffer_pool_filename可以設置buffer pool狀態文件位置。
後台專門有個線程來處理buffer pool dump和load。壓縮表上的page，以壓縮方式被加載到buffer pool。
保存buffer pool 狀態
在關閉服務前，保存buffer pool狀態
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
在服務運行時，保存buffer pool狀態
SET GLOBAL innodb_buffer_pool_dump_now=ON;
恢復buffer pool狀態
在恢復時，load buffer pool狀態
mysqld --innodb_buffer_pool_load_at_startup=ON;
在服務運行時，恢復buffer pool狀態
SET GLOBAL innodb_buffer_pool_load_now=ON;
查看buffer pool dump進度
顯示Save進度
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
或者
SELECT variable_value FROM information_schema.global_status WHERE
variable_name = 'INNODB_BUFFER_POOL_DUMP_STATUS';
如果沒有運行，顯示not started，如果已經完成顯示 completed at 150928 17:27:51，還在處理，顯示Dumping buffer pool 5/7, page 237/2873。
取消buffer pool load
SET GLOBAL innodb_buffer_pool_load_abort=ON;
參數innodb_flush_neighbors和innodb_lru_scan_depth可以調整innodb buffer pool的刷新。
innodb_flush_neighbors：刷新innodb page是否把同一個extent的也刷新。
innodb_lru_scan_depth：掃描深度，若I/O有余可以放大。
對於DML活躍的若不夠聚合刷新操作會被拉開，造成高昂的內存消耗，如果太激進會導致I/O能力耗光。最好的辦法是依賴工作負荷，數據訪問方式和存儲來配置。
對於基本不變的繁忙負荷，或者負荷波動較大的，以下一些參數可以調整innodb表的刷新行為：innodb_adaptive_flushing_lwm，innodb_max_dirty_pages_pct_lwm，innodb_io_capacity_max，innodb_flushing_avg_loops這些通過公式被innodb_adaptive_flushing使用。
Innodb_adaptive_flushing,innodb_io_capacity和innodb_max_dirty_pages_pct 受限於:
innodb_adaptive_flushing_lwm,innodb_io_capacity_max ,innodb_max_dirty_pages_pct_lwm:
1.InnoDB自適應刷新，並不適用於所有case，可能在redo log在filling up危險時又好處。Innodb_adptive_flusing_lwm表示一個redo log能力的低水位百分比，當達到閥值，innodb可以在沒有指定innodb_adaptive_flushing的情況下啟動flush。
2.若flush活動，被落的很多，innodb會比innodb_io_capacity_max更加積極的進行刷新。Innodb_io_capacity_max表示I/O能力的上限，為了不讓I/O丟被耗光。
3.為了讓dirty page不超過innodb_max_dirty_pages_pct，innodb會刷新buffer pool中的數據。默認參數值為75。
Note
Innodb_max_dirty_pages_pct，建立起刷新活動的目標。
Innodb_max_dirty_pages_pct_lwm選項指定低水位表示髒頁的百分比，來控制髒頁比率防止到達閥值innodb_max_dirty_pages_pct。
很多這些選項適用於長時間寫入並幾乎不會沒有減少負荷的時間，讓寫入磁盤跟上。
Innodb_flushing_avg_loops表示之前被計算刷新狀態的快照，控制了自適應flush如何快速的反應前台負荷變化。Innodb_flushing_avg_loops值越大，保存快照的時間越長，自適應刷新響應越慢。值大，也會減少前後台工作的反饋，設置的很高但是要保證innodb redo log不能到達75%，並且根據負荷合理設置innodb_max_dirty_pages_pct。
系統負荷基本不變，大innodb_log_file_size和small spike沒有到達redo log的75%空間使用，可以把innodb_flushing_avg_loops設置的稍微高一點，減慢flush。對於極端的情況或者空間不夠，可以考慮設置的小一點，避免redo log超過75%，更上負荷。
Innodb_flush_avg_loops關於刷新頻率控制在代碼 buf0flu.cc::page_cleaner_flush_pages _if_needed，參考：InnoDB原生Checkpoint策略及各版本優化詳解
InnoDB多線程訪問共享數據，innodb同步使用自己實現的mutex和read/write lock。在很多平台，有很多更高效的方式來實現read/write lock。原子操作通常用來同步多個線程的行為比pthread更有效。每個操作獲取和釋放鎖需要的指令更少。在訪問沖突時浪費的時間更少，也就是說在多核平台上擴展性更好。
Innodb實現的mutex，read/write lock使用gcc自帶的原子內存訪問而不是pthread。特別是innodb通過gcc 4.1.2編譯，使用gcc自帶的原子訪問來代替pthread_mutex_t。
這些修改提高了innodb在多核系統中的擴展性。對於原子內存訪問不可用的，innodb會使用傳統的pthreads方法來代替。
在啟動mysql，innodb會寫一個消息到日志文件表示原子內存訪問是否被用於mutex，read/write lock。另外compare-and-swap操作可以被用在線程表示，然後innodb同時也可以使用read-write lock。
在innodb被開發，內存分配由os和runtime library提供。如果innodb在mem子系統自己實現內存分配，分配器由一個mutex決定，可能會變成瓶頸。Innodb也對系統分配器最了分裝，像mutex一樣。
現在多核系統廣泛使用，OS也成熟，OS提供的內存分配頁有提升，比之前的更好，擴展性也更好。高效的內存分配器如Hoard, libumem, mtmalloc, ptmalloc, tbbmalloc, 和TCMalloc。對於跟多workload，內存分配釋放頻繁，使用高調整的分配器，比使用innodb的分配器更好。可以通過innodb_use_sys_malloc來決定使用自己的分配器或者OS的，1使用OS的分配器，0，使用自己的分配器。
當innodb內存分配器禁用了，innodb會忽略參數innodb_additional_mem_pool_size的配置，innodb使用額外的內存池，保證安全分配不會被退回到系統內存分配器。當innodb內存分配禁用，所有分配都由系統內存分配器完成。
當使用系統備份分配器(innodb_use_sys_malloc=on)，因為innodb不能根治所有的內存使用。在show engine innodb status命令的BUFFER POOL AND MEMEORY段輸出只有total memory allocated的統計。
當insert，update，delete操作在表上執行。Innodb有一個insert buffer當page不在內存中會緩存secondary index的修改，避免不必要的I/O操作。當被load到buffer pool就會和insert buffer進行合並。Innodb主線程在空間或者shutdown的時候會meger緩存的修改。
Insert buffer是buffer pool的一部分，會占用一些內存。如果workset幾乎要占用所有內存，取消insert buffer可能會更好。
Innodb_change_buffering用來控制insert buffer，取值如下：
All：默認值，buffer insert，delete-marking和purges
None:不buffer任何操作
Insert：buffer insert操作
Delete：buffer delete-marking操作
Changes：buffer insert和delete-marking
Purges：buffer 後台清理操作。
可以設置的my.ini裡面，也可以使用set global設置。
Innodb使用os線程來處理用戶事務，現在的os和服務器，有多個核，上下文切換高效，很多工作在沒有並發線程限制下運行的很好。
Innodb會使用很多技術限制osthread並發執行數量，從而最小化上下文切換。當innodb接到用戶會話的請求，若並發線程的數量被限制，那麼新的請求會sleep，請求也不會sleep後被調度而是進去了先進先出的隊列。
通過innodb_thread_concurrency來限制並發線程，在innodb_thread_sleep_delay在進入隊列之前sleep的微秒。
MySQL 5.6.3之後設置innodb_adaptive_max_sleep_delay可以自適應調節sleep，是innodb_thread_sleep_delay的最高值。
Innodb_thread_concurrency=0表示並發線程沒有限制。只有當並發線程數被限制的時候，才會sleep。如果不限制innodb_thread_sleep_delay被忽略。
當有線程限制時，innodb通過允許多個請求進入一個正在執行的會話，來減少上下文切換，不需要去觀察是否超出innodb_thread_concurrency。因為SQL語句包含了多個行操作，innodb會分配tickets允許線程重復被調用。
當SQL開始被運行,沒有tickets會看innodb_thread_concurrency獲得進去innodb的權力，並分配tickets。當tickets被用完會就查看innodb_thread_concurrency獲得ticket，分配的tickets個數由innodb_concurrency_tickets決定。
InnoDB使用一些後台線程來為不同的I/O服務。使用innodb_read_io_threads和innodb_write_io_threads來配置讀寫的線程個數。這些參數的默認值為4，范圍在1-64。
這個目的是為了讓innodb在高端system上有更好的擴展性。每個後台線程可以控制256個堵塞I/O請求。大多數後台I/O的來源是預讀。Innodb視圖平衡輸入的負荷，很多後台線程都是共享任務的。Innodb也試圖把對同一個extent的讀放到同一個線程上。如果再高端的I/O子系統上看到大於64*innodb_read_io_threads的堵塞讀請求，在show engine innodb status上，可以增加innodb_read_io_threads。
歷史上InnoDB和其他數據庫一樣，在commit之前先要flush redo log。Innodb使用group commit，避免每個commit做一次flush。使用分組提交，innodb使用一個寫入對多個用戶事務進行提交，可提高吞吐量。
在MySQL 5.1之後被取消了。
分組提交功能，在innodb內不是使用二階段提交協議的，重新使用是為了保證binlog和
中的順序和以前一樣。當啟動了binlog，只有當sync_binlog=0時，才能使用分組提交。
InnoDB主線程執行各種後台任務，大多數和I/O有關：1.flush髒頁，2.把insert buffer寫入到對應的index。
主線程試圖以新的方式執行這任務，並不影響其他正常工作。主線程嘗試評估I/O帶寬可用，在空閒時執行。
參數innodb_io_capacity表示innodb可用的I/O，這個參數表示每秒的I/O操作。主線程根據innodb_io_capacity評估可用的帶寬。
Innodb_io_capacity設置也限制所有的buffer pool實例。當髒數據刷新，buffer pool會均分innodb_io_capacity。
在MySQL5.6.5和更高版本，這些IO操作被移動到了後台其他線程，這些值由innodb_purge_threads控制。
Innodb內部同步通常會使用自旋鎖，在等待的時候，innodb運行語段小的循環指令，避免重新調度。若自旋運行的太多會浪費資源，而造成失誤吞吐量下降。大多數現代處理器都實現了PAUSE指令在自旋上的使用。這樣處理器可以更加高效。
Innodb使用PAUSE指令可以增加CPU密集操作的性能。
很多innodb mutexes和rw-lock被保存很短的是時間，多和系統，一個線程在sleep之前，連續的檢查mutex和rw-lock可能會更加有效。若mutex或者rw-lock在輪詢變可用，thread可以再同一個時間片上馬上運行。如果太頻繁的輪詢會導致cache ping pong。Innodb最小化這個問題是，在輪詢之間等待一個隨機事件。
通過參數innodb_spin_wait_delay輪詢之間的最大延遲，延遲的時間由編譯器和目標處理器決定。對於所有的cores共享一個cache可以減少延遲，或者設置innodb_spin_wait_delay=0限制繁忙循環在同時進行。
默認innodb_spin_wait_delay=6,可以設置在my.cnf或者set global。
從InnoDB 1.1開始，可以profile整個innodb操作,這種tuning是為了專家級用戶。DBA也可以使用這個功能做容量計劃，看是典型的workload是否有性能瓶頸問題。來判斷是否可以通過增加一些系統能力來提高性能。具體查看22章
使用這個特性需要檢查：
1.必須使用MySQL5.5或者更高版本，啟動了performance schema功能。查看：22.2 Performance Schema Configuration
2.必須熟悉如何使用performance schema特性
3.在performance_schema檢查一下不同的innodb對象。查看：22.9 Performance Schema Table Descriptions
a.mutex在mutex_instances表
b.rw-lock在rwlock_instances表
c.文件io操作，file_instances,file_summary_by_event_name和 file_summy_by_instance表
d.線程在PROCESSLIST表。
4.在性能測試的時候檢查，events_waits_current,events_waits_history_long表中的數據。
從innodb 1.1開始並發事務有極大的擴展，刪除了之前innodb rollback segment的瓶頸限制。
現在從一個rollback segment分成128個segment每個都支持1023個事務讀寫。總共可以適應128K個並發事務。
每個事務都會被分到一個rollback segment。使用innodb_rollback_segment來配置rollback segment個數。
清理操作由innodb獨立自動運行的一個或者多個線程執行，而不是master thread的一部分。可以使用innodb_purge_threads配置線程個數。若DML比較集中就設置的小一點，這樣就不會和其他訪問爭用資源。若比較分散就設置的大一點。
另外一個相關的參數innodb_purge_batch_size默認為20，最大為5000
如果是只讀事務，可以避免啟動事務ID的一些相關開銷。事務ID只有在寫事務，或者select for update才會被需要。
InnoDB如何識別只讀事務：
1.以start transaction read only啟動的事務。
2.自動提交事務非鎖定select，沒有for update或者lock in shared mode。因此讀密集的應用，可以把一組查詢放在start transaction read only和commit內。
CRC32 checksum算法，在mysql5.6.3加入，一次掃描32bits，通過優化只需要掃描8bit。可以使用innodb_checksum_algorithm=crc32來啟動。
當crc32算法啟動，一旦被使用crc32算法寫入，以前的版本就無法讀取。當啟動新的mysql實例。並且innodb數據使用crc32算法創建。可以使用strict_crc32比crc32快，因為不需要向後兼容。
從MySQL 5.6.3可以創建InnoDB undo log可以存放在獨立表空間中。Undo log也就是rollback segment。有幾個相關的配置參數：
1.innodb_undo_tablesspaces
2.innodb_undo_directory
3.innodb_rollback_segment,變成了innodb_undo_logs，前綴為了兼容而存在。
注意點
1.決定使用高速存儲存放undo logs，使用innodb_undo_directory指定路徑。
2.決定innodb_undo_logs的非0值，一開始可以使用較小的，之後在放大。
3.innodb_undo_tablespaces非0只，有innodb_undo_logs個數的undo log被創建在制定的獨立表空間中。
4.創建新的MySQL實例，把這些值寫到配置文件上
5.測試I/O性能。
6.定期增加innodb_undo_logs並且重新性能測試。
7.以理想的配置部署到生產環節。
性能和可擴展性考慮
把undo logs放到另外的文件裡面允許MySQL統一的維護I/O和內存。如undo數據被寫入磁盤並且很少被使用，就沒必要文件系統內存cache，可以把更多內存給buffer pool使用。
內部
物理表空間文件已undoN命名,N是空間ID
InnoDB計算innodb每個表的統計信息機幫助優化器查找最有效的執行計劃。這個特性默認是啟動的，通過innodb_stats_persistent參數。
可以通過innodb_stats_persistent_sample_pages采樣page個數。是否自動重新計算統計信息通過參數innodb_stats_auto_recalc控制。當數據有大於10%被修改就會重新統計。
如果關閉了innodb_stats_auto_recalc,需要通過analyze table來保證優化器統計信息的准確性。當新索引被增加到表中，索引統計信息會被計算並且添加到innodb_index_stats表上不管有沒有開innodb_stats_auto_recalc。
在創建表時，可以使用innodb_stats_persistent，innodb_stats_auto_recalc，或者使用create table和alter table的stats_persistent,stats_auto_recalc，stats_sample_pages子句。後者會覆蓋前者。
統計信息在服務重啟後被清理，在表下次被訪問的時候重新計算。統計信息使用隨機采樣，2次統計信息可能不一樣，會導致不同的執行計劃。
配置innodb優化統計信息采樣
MySQL查詢優化器使用評估的統計信息來選擇索引。采樣page個數通過設置參數innodb_stats_persistent_sample_pages，默認為20。
修改采樣率時考慮一下狀況：
1.若統計信息不夠優化器選擇的計劃可能不是罪理想的。統計信息准確性可以通過使用select distinct和mysql.innodb_index_stats對比。
innodb_stats_persistent_sample_pages太少會導致統計信息不夠准確，太多會導致分析執行太慢。
2.analyze table太慢可以減少采樣率，若太少會導致統計信息不准。
InnoDB持久化統計信息表
持久化特性依賴於MySQL內部的表管理，innodb_table_stats和innodb_index_stats.
innodb_table_stats：
Column name
Description
database_name
數據庫名
table_name
表名，分區名或者子分區名
last_update
最後一次更新時間戳
n_rows
表中數據行數
clustered_index_size
聚集索引page個數
sum_of_other_index_sizes
非聚集索引page個數
innodb_index_stats：
Column name
Description
database_name
數據庫名
table_name
表名，分區名或者子分區名
index_name
索引名
last_update
最後一次更新時間戳
stat_name
統計信息名
stat_value
統計信息不同值個數
sample_size
采樣page個數
stat_description
描述
從MySQL 5.6.6開始統計信息默認是持久化的。通過參數innodb_stats_persistent控制。優化器通過統計信息來選擇索引。Innodb_stats_transient_sample_pages控制采樣pages個數，默認為8。Innodb_stats_transient_sample_pages可以runtime設置。
Note
Innodb_stats_transient_sample_pages在innodb_stats_persistent=0的時候影響采樣。注意點：
1.若值太小，會導致評估不准
2.若果值太大，會導致disk read增加。
3.會生產很不同的執行計劃，因為統計信息不同。
當使用show table status，show index時，查詢 INFORMATION_SCHEMA.TABLES 或INFORMATION_SCHEMA.STATISTICS並設置了innodb_stats_on_metadata必須更新統計信息。
Note
在MySQL 5.6.6,默認啟動統計信息持久化，默認innodb_stats_on_metadata為0.可能會減少schema的訪問速度，若表或所有太大。
當MySQL Client以—auto-rehash啟動，innodb所有的表都會被打開，相關的索引統計信息都會被更新。
不管innodb_stats_transient_sample_pages是什麼，選擇一個值會被有太多I/O有能保證統計信息相對准確。
Analyze table的復雜性依賴於：
1.innodb_stats_persistent_sample_pages
2.被索引的列數
3.分區個數
復雜度=采樣個數*索引列*分區個數
具體看：14.12 InnoDB INFORMATION_SCHEMA Tables
有4種InnoDB監視器：
標准InnoDB監視有一下信息：
1.每個活躍事務保留的表和記錄鎖
2.事務鎖等待
3.線程信號量等待
4.掛起文件的IO請求。
5.buffer pool統計信息。
6.清理和insert buffer合並活動
InnoDB鎖監視器提供額外的鎖信息。
InnoDB表空間監視器打印出所有共享表空間中的segment，驗證表空間分配的數據結構
InnoDB表監視器打印innodb內部數據字典。
當可以啟動innodb監視器定期輸出，innodb會定期輸出到mysqld標准錯誤輸出。一般會輸出到錯誤日志中。
innodb會把診斷輸出到stderr或者文件中，不會輸出到buffer中，以免buffer溢出。SHOW ENGINE INNODB STATUS輸出，每個15秒鐘都會寫入到一個狀態文件，innodb_status.pid，文件在服務關閉後被刪除。pid是服務的進程id。只有在innodb-status-file=1的情況下才會被創建。
innodb監視器可以在使用時啟動。但是最好只在想要看的時候啟動，監視器會照成一定的性能下降。如果把監視器信息輸出到相關表，如果忘記把表關閉了你的錯誤日志可能會變的很大。
啟動標准innodb監視器
啟動監視器定時輸出：
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
禁用標准監視器定時輸出：
DROP TABLE innodb_monitor;
MySQL 5.6.16之後，可以使用參數innodb_status_output來啟動和禁用。
set GLOBAL innodb_status_output=ON;
需要時輸出標准監視器
mysql> SHOW ENGINE INNODB STATUS\G
啟動InnoDB鎖監視器
啟動定時輸出
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
關閉定時輸出
DROP TABLE innodb_lock_monitor;
MySQL 5.6.16之後，啟動innodb鎖監視器，需要設置innodb_status_output，innodb_status_output_locks。
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
因為標准監視器會輸出鎖信息。所以啟動鎖監視器必須全部配置。
啟動InnoDB表空間監視器
啟動定時輸出：
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
關閉定時輸出：
DROP TABLE innodb_tablespace_monitor;
啟動InnoDB表監視器
啟動定時輸出：
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
關閉定時輸出：
DROP TABLE innodb_table_monitor;
鎖監控和標准監控基本一樣，特別是鎖信息部分。如果啟動了標准監控和鎖監控，其實是一個輸出流，就是多了額外的鎖信息。
具體輸出的例子：14.14.3 InnoDB Standard Monitor and Lock Monitor Output
輸出段
Status：
顯示時間戳，監視器名，生產結果基於之前幾秒鐘
BACKGROUND THREAD：
srv_master_thread顯示，主後台顯示是如何工作的。
SEMAPHORES：
輸出線程等待信號量和統計信息關於多少次spin等到，等待mutex或者rw-lock。大量的線程等待，在innodb中可能是磁盤IO,和資源爭用問題。innodb_thread_concurrency減少並發線程，可以減少並發。spin rounds per wait顯示每個os wai等待mutex的spinlock的rounds。
LATEST FOREIGN KEY ERROR
這個信息是關於最近的外鍵錯誤。
LATEST DETECTED DEADLOCK
最近的deadlock信息。
TRANSACTIONS
當前信息鎖等待信息報告，可以發現鎖沖突。
FILE I/O
顯示I/O線程信息，執行各種不同類型的I/O。由innodb_read_io_threads和innodb_write_io_threads來控制這些線程個數。
INSERT BUFFER AND ADAPTIVE HASH INDEX
顯示innodb insert buffer和自適應hash index，和各自的統計信息。
LOG
顯示了innodb log信息，當前log順序，flushed的log，checkpoint的log信息。也顯示了掛起的寫入和寫入性能信息。
BUFFER POOL AND MEMORY
page的寫入和讀取統計信息。
ROW OPERATIONS
顯示了主線程的行為，包括每個類型行操作的性能。
參考：
https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
show engine innodb status 詳解
InnoDB表空間監視器輸出在共享表空間中，文件segment和驗證表空間分配數據結構。表空間監視器不描述由innodb_file_per_table分配的表。
整體的表空間信息：
FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845
id：表空間id
size：當前表空間的page個數
free limit：free list沒有被初始化的最小的page number。
free extents:free extents個數。
not full frag extents:沒有被完全填滿的extent。
used page:被分配的pages。
full frag extents：被分配滿的extents。
first seg id not used:沒有使用的第一個segment ID
segment信息：
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0
id:segment id
space，page:表空間號 0 表示共享表空間，page號 2表示inode
res：segment保留的page
used：segment分配的頁的使用。
full text：被完全使用的extent
fragm pages：分配的初始化。
free extents：空閒的extents
not full extents：不滿的extents
pages:非滿的extents使用的page
因為要在未來版本刪除，略具體看：14.14.5 InnoDB Table Monitor Output
熱備
mysql企業版提供備份工具，可以熱備innodb和myisam。可以查看：http://dev.mysql.com/doc/mysql-enterprise-backup/en/.
percona 也出了一個免費的熱備工具具體可以看：
Percona XtraBackup User Manual 閱讀筆記
冷備
冷備就是在mysql服務關閉之後，復制走所有的文件。
替換備份類型
另外一種備份類型是mysqldump+binlog的方式。在之前有介紹。
執行恢復
innodb恢復在服務啟動時自動運行。若出現損失或者磁盤錯誤，必須通過備份恢復。可以使用checktable來檢查表是否順滑哦。有時候page順壞，可能是系統的文件緩存造成的，而不是磁盤數據。最好的方法是重啟電腦。
innodb crash恢復有一些步驟：
1.應用redo log。如果redo log找不到了就會被跳過。跳過redo log會加快恢復過程，但是不建議。
2.這一步可以並發執行：
a.回滾沒有完成的事務。
b.insert buffer 合並,把insert buffer修改到secondary index上。
c.清理，刪除刪除標記的記錄，不需要再被活動事務可見。
InnoDB復制和MyISAM復制沒什麼區別。
對於為master安裝一個新的slave，復制innodb表空間和log文件，也要復制innodb表的frm文件。把復制移動到slave上，如果innodb_file_per_table啟動，還要復制ibd文件。
master中事務失敗，並不影響整個復制，MySQL復制是基於binlog的，事務失敗並不會被寫入bin log。
master上外鍵的級聯炒作，只有在slave上也有外鍵並設置了級聯，才有效果。
具體看：14.16 InnoDB and MySQL Replication
因為網上有對比性能測試，集成memcached性能遠不如memcached。所以不做筆記
具體看：14.17 InnoDB Integration with memcached
常用troubleshooting指南：
1.若出現錯誤，或者bug，查看error log
2.若關系到deadlock，啟動了innodb_print_all_deadlocks，所有死鎖信息都會打印到error log中。
3.發生DDL錯誤，無法打開innodb文件，就會有system cannot find the path specified錯誤。
4.當troubleshooting最好在命令行執行。
5.啟動innodb監視器獲取錯誤信息，若問題和鎖有關啟動lock 監視器，若和DDL有關開table監視器。InnoDB臨時啟動標准監視器輸出，要一下條件：
a.長型號等待。
b.innodb不能buffer pool中空閒的block
c.67%的buffer pool被lock heap或者自適應hash index占用
6.使用check table診斷錯誤表。
排查步驟依賴於問題發生在啟動時，還是執行DML和DDL語句等。
初始化問題
如果innodb在試圖初始化表空間或者日志文件出錯，刪除所有innodb創建的文件然後嘗試重建。最簡單的方式是以命令行啟動，就可以看的很清楚。
runtime 問題
如果在炒作文件是，innodb打印一個操作系統問題，如下方式解決問題：
1.保證innodb文件目錄存在，innodb log目錄存在
2.保證mysqld有訪問權限
3.保證mysqld可以讀取配置文件
4.保證磁盤空間未滿
5.保證數據文件目錄沒有沖突。
6.檢查innodb_data_home_dir和innodb_data_file_path的值。
為了查詢數據庫page損壞，可以使用select into outfile通常是沒有問題的，嚴重損壞會導致innodb後台操作crash，觸發assert，導致redo crash。
這時候使用innodb_force_recovery強制innodb啟動，阻止後台操作執行，這樣可以dump表。
[mysqld]
innodb_force_recovery = 1
Warning
在使用參數錢保證已經做了備份，並從小的值開始遞增，使用到了3以上要先在測試環境嘗試，MySQL 5.6.6之後4以上都是只讀的。
innodb_force_recovery默認是0，高值包容低值的特性。
1(SRV_FORCE_IGNORE_CORRUPT)
如果發現損壞page，讓服務繼續運行。
2(SRV_FORCE_NO_BACKGROUND)
阻止master thread和任何清理線程運行。
3(SRV_FORCE_NO_TRX_UNDO)
crash recovery之後不運行事務rollback
4(SRV_FORCE_NO_IBUF_MERGE)
防止insert buffer merge操作運行
5(SRV_FORCE_NO_UNDO_LOG_SCAN)
當啟動數據庫的時候，不查看undo log
6(SRV_FORCE_NO_LOG_REDO)
不使用redo log來恢復，這個值只能select * from t復雜的語句會導致錯誤發生。若表中的損壞不能讓你dump全表數據，可以使用order by pk desc部分導出。為了安全起見不能使用寫入。5.6.16版本之後4以上只能只讀。
表定義保存在frm文件和innodb數據字典中，若移走frm文件或者服務crash信息會變的不一致。
CREATE TABLE問題
當error log有以下錯誤，表示有數據字典信息但是沒有數據文件。
InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
打開表問題
mysql輸出如下錯誤：
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
如果error log有以下錯誤，表示有遺留的frm文件
InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?
被遺忘的中間表
如果alter table時crash，可能會有遺留的中間表。中間表名以“#sql-”開頭。可以在數據目錄上看到，表監視器，還有INFORMATION_SCHEMA庫的表中看到。
移除遺留的中間表，執行一下步驟：
1.確定中間表，是修改前，還是修改後的。可以通過表監視器或者INFORMATION_SCHEMA的INNODB_SYS_TABLES或者INNODB_SYS_COLUMNS和INNODB_SYS_INDEXES查詢表結構。
2.一旦確定是alter前，或者alter後，創建一個和中間表一樣的數據庫目錄。
ysql> CREATE TABLE tmp LIKE employees.salaries; ALTER TABLE tmp DROP COLUMN to_date;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.復制到數據目錄
shell> cp tmp.frm employees/#sql-ib87.frm
4.刪除中間表
mysql> DROP TABLE `#mysql50##sql-ib87`;
Query OK, 0 rows affected (0.01 sec)
5.如果左邊能夠覆蓋#sql-*.frm文件，刪除。出現錯誤可以忽略
mysql> DROP TABLE `#mysql50##sql-36ab_2`;
ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2'
表空間消失錯誤
如果啟動了innodb_file_per_table,出現以下錯誤
InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
解決步驟如下：
1.在其他數據庫目錄中創建相符的frm文件，並且復制到數據庫目錄中。
2.drop原來的表，這樣可以成功刪除表，並且innodb可以打印警告到錯誤信息，ibd文件消失。
以下items表示innodb如何錯誤處理：
1.如果運行超出空間，出現table is full錯誤並且innodb回滾錯誤處理。
2.事務死鎖回滾事務，重試整個事務。鎖超時innodb只回滾一個等待超時的語句。當事務回滾，因為死鎖或者鎖等待超時會取消事務內所有語句，如果以start transaction或者begin語句開始，rollback不會取消語句，後面的語句會變成事務的一部分知道commit或者rollback或者隱式提交的語句。（mariadb 測試中發現，關閉自動提交和start transaction一樣，只回滾出錯語句。）
3.重復鍵錯誤回滾sql語句，如果語句沒有指定ignore選項。
4.行太長錯誤，回滾SQL語句
5.其他錯誤由MySQL層代碼發現，回滾相應的SQL語句。
具體看：14.18.5 InnoDB Error Codes
略
