MySQL/Table types

　 每張表在硬碟或內存的物理存儲，需要由存儲引擎（Storage Engine， SE）完成。 存儲引擎對資料庫是外掛程式（plugin），可以安裝/卸載。SE影響了表的有效性能、穩定性、鎖類型、查詢cache的使用、需要的磁碟空間等等。

表類型（Table Type）已經是個過時概念，現在使用引擎（ENGINE）或存儲引擎（STORAGE ENGINE）代替。

存儲引擎

MyISAM 與 InnoDB

MyISAM是表級上鎖，InnoDB提供行級上鎖。

除了外鍵，InnoDB支持事務。

MySQL在Linux上的默認表類型是MyISAM, 在Windows上默認表類型是InnoDB.

實現細節對比

InnoDB存儲引擎默認每個頁的大小為16KB。在某個頁內插入新行時，為了減少數據的移動，通常是插入到當前行的後面或者是已刪除行留下來的空間，所以在某一個頁內的數據並不是完全有序的，但是為了為了數據訪問順序性，在每個記錄中都有一個指向下一條記錄的指針，以此構成了一條單向有序鍊表。新建的表，由於數據還比較少，一個頁就能容下，所以只有一個根結點（page1），主鍵和數據也都是保存在根結點。隨著行數的增加，Page1放不下了，這時候就需要進行頁分裂，產生一個新的Page，流程為：

1. 產生新的Page2，然後將Page1的內容複製到Page2。
2. 產生新的Page3，新行數據放入Page3。這不符合B+樹的節點分裂原則，是InnoDB的優化。這裡有個隱含條件，那就是主鍵自增！如果一個表沒有主鍵，那默認會找建了唯一索引的列rowid，如果也沒有，則會生成一個隱形的欄位作為主鍵！
3. 原來的Page1依然作為根結點，但是變成了一個不存放數據只存放索引的頁，並且有兩個子結點Page2、Page3。根結點是會預讀到內存中的，所以根結點的物理地址固定會比較好

如果表頻繁插入和刪除，會導致數據頁產生碎片，頁的空間利用率低，還會導致樹變的「虛高」，降低查詢效率。這可以通過索引重建來消除碎片提高查詢效率。

InnoDB數據頁結構：

• Page Directory由多個slot有序組成，是一個稀疏索引結構。每個slot代表多個記錄，最少屬於4條記錄，最多屬於8條記錄。當尋找一條數據的時候可以先在槽中通過二分法查找到一個大致的位置。
• 數據行形成一個單向鍊表。表頭、表尾為兩個虛記錄。每個slot指向的數據行與前一個slot指向的數據行之間有4-8個數據行，形成一個邏輯塊。塊內是順序查找。

InnoDB的主鍵形成聚集索引；非主鍵形成了非聚集索引，需要「回表」來查出具體的數據行。「回表」是指其他索引通過主鍵查詢表的某一行。

MyISAM主鍵索引的存儲結構：

• 主鍵索引樹的葉子結點的數據區域沒有存放實際的數據，存放的是數據記錄的地址。
• 數據的存儲不是按主鍵順序存放的，按寫入的順序存放。

MyISAM

• 優點
  • 全文搜索
  • Geometric數據類型
  • 讀速度更快
  • 數值是大尾序存儲，利於索引壓縮
  • 內部支持每張表一個AUTO_INCREMENT列。

• 缺點
  • 表級鎖
  • 無外鍵
  • 掉電後恢復更慢

InnoDB

• 優點
  • 事務安全(ACID兼容)存儲引擎具有commit, rollback, 崩潰恢復能力
  • XA事務
  • 外鍵
  • 行級枷鎖
  • 在主存維持自己的緩存池用於數據與索引的緩衝
  • 當主鍵物理排序或者hash索引時，查詢速度更快
  • 表的大小不限，即使作業系統限制文件最大2GB.
  • 掉電後更快與可靠恢復

• 缺點
  • 更多存儲空間
  • ACID保證要求在事務提交時完全同步到硬碟。對於速度非常關鍵應用，也可以關閉這條。
  • 數據版本與事務，增加了表管理開銷
  • 管理大量行鎖要求很高內存
  • 對已有的表，增加索引很慢

Merge Table

同義詞為Merge, MRG_MYISAM

• 一張MERGE table是相同的MyISAM表的集合。相同是指所有表有相同的列與索引信息。

CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE delhi  (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE total  (first_name VARCHAR(30), amount INT(10)) TYPE=MERGE UNION=(mumbai,delhi)

Merges可以用於克服MySQL或作業系統的文件尺寸限制。也曾用作代替view。MySQL 5.0開始支持view，因而Merge tables過時了

MEMORY / HEAP

MySQL 4.1之前叫做HEAP。此後叫做MEMORY

3.23引入。

BDB

Berkeley DB（BDB）在5.1版之後由於用戶太少而被去除。

BLACKHOLE

Discards all data stored in it but does still write to the binary log, so it is useful in replication scale-out or secure binlog-do filtering situations where slaves aren't trustworthy and for benchmarking the higher layers of the server.

其他

• CSV:
• EXAMPLE (a stub for developers)
• ISAM (3.23版本之前使用，從5.1刪除)

存儲引擎的元數據

通過SQL可以得到你的server使用的存儲引擎的元數據

MySQL 5.0開始可以用下述語句：

SHOW STORAGE ENGINES

STORAGE這個詞可以省略。返回結果包含的列：

• Engine - 名字.
• Support - 是否支持，取值可以為：
  • 'DEFAULT' - 默認引擎
  • 'YES' - 支持;
  • 'DISABLED' - 已經編譯,但MySQL啟動時禁用該引擎，如啟動參數選項：--skip-engine-name
  • 'NO' - 不支持
• Comment - 描述
• Transactions - 引擎是否支持SQL transactions
• XA - 引擎是否支持XA transactions
• Savepoints - 引擎是否支持savepoints與rollbacks

INFORMATION_SCHEMA `ENGINES` table

`ENGINES`是INFORMATION_SCHEMA資料庫中的虛擬的表. MySQL 5.1.5增加。

HELP 語句

HELP 'myisam'

客戶端可以輸入:

help myisam \g

改變存儲引擎

SQL

創建表時，使用 ENGINE 子句指定存儲引擎：

CREATE TABLE ... ENGINE=InnoDB

否則，storage_engine變量的值被使用。你可以改變該變量：

SET storage_engine=InnoDB

或者修改MySQL伺服器配置文件my.cnf中的default-storage-engine

修改已經存在的表的存儲引擎:

ALTER TABLE `stats` ENGINE=MyISAM

mysql_convert_table_format

MySQL提供了工具mysql_convert_table_format, 用Perl寫成. 把表從一種存儲引擎轉換為另外的存儲引擎。

語法:

mysql_convert_table_format [options] database

選項：

--help

--version

--host=host MySQL正在運行的host. 預設: localhost.

--port=port TCP埠.

--user=user

--password=password

--type=storage_engine 轉換後的存儲引擎。

--force 錯誤發生時不停止執行

--verbose

例如:

mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test