MySQL數(shù)據(jù)表類型
作者：vwFisher
時間：2019-11-08
目錄
1 概述
MySQL 支持多種數(shù)據(jù)表類型己英。比較重要的 3 種類型是：MyISAM、InnoDB荐开、Heap
默認(rèn)的數(shù)據(jù)表類型由 MySQL 配置文件里的 default-table-type 決定
2 數(shù)據(jù)表類型
MySQL 版本支持哪些數(shù)據(jù)表類型吧寺，可以通過 SHOW ENGINES 命令來查看
MySQL文檔里有一章是專門介紹各種MySQL數(shù)據(jù)表類型的，可以在以下網(wǎng)址找到它
http://dev.mysql.com/doc/mysql/en/storage-engines.html
2.1 MyISAM
特點：成熟琼蚯、穩(wěn)定地粪、易于管理
內(nèi)部：可以細(xì)分 靜態(tài) 和 動態(tài) 兩種首量，MySQL服務(wù)器將自行選擇它認(rèn)為最適合的一種來使用
1.MyISAM Static(靜態(tài) MyISAM)
數(shù)據(jù)表里的數(shù)據(jù)列各自都有預(yù)先定義好的固定長度時，就會自動選擇該類型不皆。
這種數(shù)據(jù)表的數(shù)據(jù)存取(SELECT贯城、INSERT、UPDATE霹娄、DELETE)效率非常高能犯。安全性也高，即使出現(xiàn)文件受損或其他問題犬耻，數(shù)據(jù)記錄的提取和恢復(fù)工作相比其他類型要容易踩晶。
2.MyISAM Dynamic(動態(tài) MyISAM)
數(shù)據(jù)表定義出現(xiàn) VARCHAR、xxxTEXT枕磁、xxxBLOB 類型字段渡蜻，就會自動選擇該類型。
與 靜態(tài)MyISAM 相比透典，該類型的數(shù)據(jù)表的空間需求量往往小得多，存儲 字符串 和 二進(jìn)制對象 所需要的字節(jié)數(shù)都是它們的實際長度(VARCHAR多了標(biāo)記長度的1~2字節(jié))顿苇。
因為是不定長度峭咒，記錄被修改時，就可能導(dǎo)致同一條記錄的各個字段不一定存儲在一個連續(xù)的字節(jié)塊里纪岁，而是分散各處(會引起碎片化)凑队。當(dāng)編輯的數(shù)據(jù)表變得越來越碎片化，數(shù)據(jù)存取消耗的時間也就越長幔翰。所以常需要通過 SQL 命令 OPTIMIZE TABLE 或 優(yōu)化工具(如 myiamchk) 進(jìn)行碎片整理
3.MyISAM Compressed(壓縮MyISAM)
動態(tài) 和 靜態(tài) MyISAM漩氨，都可以用 myiasmchk 工具壓縮。壓縮效果可以使數(shù)據(jù)表的空間占用量減少到原來的一半以下遗增。但是注意叫惊，壓縮后，不能再對它們進(jìn)行修改做修，等于變成了只讀數(shù)據(jù)表
2.2 InnoDB
1.事務(wù)
支持 ANSI-SQL/92 標(biāo)準(zhǔn)里定義的 4種 事務(wù)級別(READ UNCOMMITTED霍狰、READ COMMITTED、REPEATTABLE READ饰及、SERIALIZABLE)
2.數(shù)據(jù)行鎖機(jī)制
在執(zhí)行一個事務(wù)時(begin ... commit)蔗坯，通過 FOR UPDATE 對查詢到的數(shù)據(jù)進(jìn)行行鎖(阻塞 SELECT ... FOR UPDATE、INSERT燎含、UPDATE宾濒、DELETE)。InnoDB 驅(qū)動程序能夠自動識別 死鎖 現(xiàn)象屏箍，并自動終止兩個進(jìn)程中的一個
3.外鍵約束條件
數(shù)據(jù)表之間定義了關(guān)系绘梦，InnoDB 驅(qū)動程序?qū)⒆詣颖ＷC數(shù)據(jù)表的引用一執(zhí)行在執(zhí)行過 DELETE 命令之后也能保持橘忱。即 不可能出現(xiàn)數(shù)據(jù)表 A 里的一條記錄引用數(shù)據(jù)表 B 里一條已經(jīng)不復(fù)存在的記錄的問題。
4.崩潰恢復(fù)
發(fā)生崩潰后谚咬，InnoDB 數(shù)據(jù)表能夠迅速地自動恢復(fù)到一個穩(wěn)定可用的狀態(tài)(前提是計算機(jī)的文件系統(tǒng)沒有被破壞)
問題和缺陷
1.表空間的管理
MyISAM 數(shù)據(jù)表驅(qū)動程序把每個數(shù)據(jù)表分別保存在它自己的文件里鹦付，這些文件會根據(jù)實際情況增大或縮小
InnoDB 數(shù)據(jù)表驅(qū)動程序是把所有數(shù)據(jù)和索引都保存在一個表空間(tablespace)里。
表空間由一個或多個文件構(gòu)成择卦，它們形成了一個虛擬的文件系統(tǒng)敲长。這些文件在被創(chuàng)建之后只能增大，不能縮小秉继。如果想復(fù)制某個 InnoDB 數(shù)據(jù)表祈噪，把 MySQL 服務(wù)器停下來復(fù)制有關(guān)文件的辦法是行不通的。因此尚辑，在管理 InnoDB 數(shù)據(jù)表時辑鲤，mysqldump 命令的使用頻率要比管理 MyISAM 數(shù)據(jù)表的時候高得多。
2.數(shù)據(jù)記錄的長度
InnoDB 數(shù)據(jù)表中的單條數(shù)據(jù)記錄最多可以占用 8000 個字節(jié)的空間杠茬。這一限制并不包括 TEXT 和 BLOB 數(shù)據(jù)列月褥，它們只有前 512 個字節(jié)是隨其他數(shù)據(jù)列一起存儲在數(shù)據(jù)庫里的，超過這個長度的數(shù)據(jù)將被存放在表空間的其他頁面瓢喉。
3.存儲空間占用量
存儲空間占用量 要比 同樣內(nèi)容的 MyISAM 數(shù)據(jù)表大很多(最多時會是 MyISAM 的兩倍)
4.全文索引
InnoDB 不支持全文索引(full-text index)
5.GIS數(shù)據(jù)
InnoDB 數(shù)據(jù)表不能用來了保存二維地理數(shù)據(jù)
6.COUNT問題
因為支持事務(wù)宁赤，InnoDB 數(shù)據(jù)表驅(qū)動程序在統(tǒng)計一個數(shù)據(jù)表里的記錄個數(shù)時更麻煩。所以執(zhí)行 SELECT COUNT(*) FROM TABLE 命令比 MyISAM 數(shù)據(jù)表要慢得多
7.數(shù)據(jù)表鎖定
InnoDB 驅(qū)動程序在執(zhí)行事務(wù)時使用的是它自己的鎖定算法栓票。因此决左，盡量避免 LOCK TABLE ... READ/WRITE 命令。
應(yīng)該使用 SELECT ... IN SHARE MODE 或 SELECT ... FOR UPDATE 命令走贪，鎖定個別記錄而不是鎖定整個數(shù)據(jù)表
8.mysql數(shù)據(jù)表
用于管理 MySQL 訪問權(quán)限的 mysql 數(shù)據(jù)表不能被轉(zhuǎn)換為 InnoDB水表佛猛。必須是 MyISAM 格式。
9.許可證費用
商用坠狡，InnoDB 支持 將收取雙倍費用继找。
2.3 HEAP
HEAP 數(shù)據(jù)表紙存在于內(nèi)存中(不是硬盤上)。使用了一個散列索引(hash index)逃沿，所以數(shù)據(jù)記錄的存取速度非陈肜螅快，HEAP 數(shù)據(jù)表的主要用途是充當(dāng)臨時 數(shù)據(jù)表感挥。
與普通的數(shù)據(jù)表相比缩搅，HEAP 數(shù)據(jù)表在功能上受到了許多限制，其中最重要的有：不允許使用 xxxTEXT 和 xxxBLOB 數(shù)據(jù)類型：只允許使用 = 和 <=> 操作符來搜索記錄(不允許使用 <触幼、>硼瓣、<=、=> 操作符)；不支持 AUTO_INCREMENT 屬性堂鲤，只允許對 NOT NULL 數(shù)據(jù)列進(jìn)行索引亿傅。
HEAP 數(shù)據(jù)表適用于數(shù)據(jù)量相對較小、但對訪問速度要求很高的場合瘟栖。請注意葵擎，因為 HEAP 數(shù)據(jù)表只存在于內(nèi)存，所以一旦 MySQL 服務(wù)器停止運行半哟，HEAP 數(shù)據(jù)表也就消失了酬滤。從這個意義上講，HEAP 數(shù)據(jù)表是一種臨時性的數(shù)據(jù)表寓涨。但它與特意使用 CREATE TEMPORARY TABLE 命令或是 MySQL 為了保存中間結(jié)果而臨時創(chuàng)建的數(shù)據(jù)表是有區(qū)別的盯串；HEAP 數(shù)據(jù)表對于來訪問同一個數(shù)據(jù)庫的其他 MySQL 連接是可見的，在鏈接意外中斷時也不丟失戒良。HEAP 數(shù)據(jù)表的最大長度由 MySQL 配置文件里的 max_heap_table_size 參數(shù)決定体捏。
2.4 臨時數(shù)據(jù)表
通過 CREATE TEMPORARY TABLE 命令或是 MySQL 為了保存中間結(jié)果而臨時創(chuàng)建的數(shù)據(jù)表
數(shù)據(jù)類型可以為任何一種，由 MySQL 自行判斷糯崎。這種數(shù)據(jù)表在 MySQL 服務(wù)器意外掉電時不一定會丟失几缭，但在 MySQL 服務(wù)器正常關(guān)機(jī)、本次 MySQL 連接正常結(jié)束 或 意外中斷時都將全部丟失沃呢。這種數(shù)據(jù)表對于訪問同一個數(shù)據(jù)庫的其 MySQL 連接是不可見的年栓，兩個不同的用戶可以在同一個數(shù)據(jù)庫里使用相同的名字創(chuàng)建臨時數(shù)據(jù)庫而不發(fā)生沖突。
臨時數(shù)據(jù)表與其他 MySQL 數(shù)據(jù)表時分開保存的樟插，MySQL 會把它們存放到一個臨時子目錄去韵洋。這個臨時子目錄在 Windows 環(huán)境通常是 C:\Windows\Temp竿刁，在 Unix/Linux 環(huán)境通常是 /tmp 或 /var/tmp/ 或 /usr/tmp黄锤。這個子目錄可以在 MySQL 服務(wù)器啟動時設(shè)置。
2.5 其他的數(shù)據(jù)類型
1.BDB數(shù)據(jù)表
是最早具備事務(wù)支持能力的 MySQL 數(shù)據(jù)表類型食拜。但隨著 InnoDB 數(shù)據(jù)表驅(qū)動程序的日益成熟鸵熟，BDB 數(shù)據(jù)表就淘汰了。
2.ARCHIVE數(shù)據(jù)表(壓縮數(shù)據(jù)表,始見于MySQL 4.1)
為了保存和備份海量數(shù)據(jù)而設(shè)計的负甸。優(yōu)點是在保存數(shù)據(jù)記錄之前會先對數(shù)據(jù)記錄進(jìn)行壓縮流强。
ARCHIVE 數(shù)據(jù)表只適合用來保存不再需要修改的數(shù)據(jù)記錄(它允許用戶執(zhí)行 INSERTR 命令,但不允許執(zhí)行 UPDATE 和 DELETE 命令)。
ARCHIVE 數(shù)據(jù)表不能建立索引呻待，所以每執(zhí)行一條 SELECT 命令打月，就必須讀取-遍全部的數(shù)據(jù)記錄。因此蚕捉，這種數(shù)據(jù)表類型僅適用于數(shù)據(jù)訪問量非常少的場合奏篙。
3.CSV數(shù)據(jù)表(文本格式的數(shù)據(jù)表，始見于MySQL 4.1)
CSV 數(shù)據(jù)表里的記錄都保存在文本文件里，數(shù)據(jù)之間用逗號隔開秘通，如“"123","1 am a character string"". CSV數(shù)據(jù)表不能建立索引为严。
4.NDB數(shù)據(jù)表(MySQL集簇，始見于MySQL 4.1)
NDB(network database肺稀、網(wǎng)絡(luò)數(shù)據(jù)庫) 數(shù)據(jù)表類型是集成在 MySQL Max 版本里的 MySQL 集簇功能中的一種第股。這種數(shù)據(jù)表類型支持事務(wù)，最適合用來建設(shè)數(shù)據(jù)分布在大量計算機(jī)上的網(wǎng)絡(luò)數(shù)據(jù)庫话原。使用這種數(shù)據(jù)表類型的前提是必須有多臺安裝了 MySQL Max 版本的聯(lián)網(wǎng)計算機(jī)并配置使它們支持集簇操作夕吻。
這方面的細(xì)節(jié)信息可以參見 http://dev.mysql.com/doc/mysq1/en/ndbcluster.html
5.FEDERATED數(shù)據(jù)表(外部數(shù)據(jù)表，始見于MySQL 5.0)
這種數(shù)據(jù)表類型能夠讓用戶去訪問外部數(shù)據(jù)庫里的數(shù)據(jù)表稿静，而那個數(shù)據(jù)庫系統(tǒng)可以位于本地網(wǎng)絡(luò)中的另一臺計算機(jī)上梭冠。就目
前而言，外部數(shù)據(jù)庫必須也是一個MySQL數(shù)據(jù)庫改备，但未來的MySQL版本可能會允許使用這種數(shù)據(jù)表類型與其他品牌的數(shù)據(jù)庫系統(tǒng)建立連接控漠。
FEDERATED 數(shù)據(jù)表類型還有許多值得完善的地方：FEDERATED 數(shù)據(jù)表上的事務(wù)和查詢都無法用 Query Cache 工具優(yōu)化；不能對外部數(shù)據(jù)表的結(jié)構(gòu)進(jìn)行修改(但數(shù)據(jù)記錄可以)悬钳。換句話說盐捷，不能對 FEDERATED 數(shù)據(jù)表執(zhí)行 ALTER TABLE 命令，但可以執(zhí)行 INSERT默勾、UPDATE碉渡、DELETE 命令。
3 數(shù)據(jù)表文件
可以在啟動 MySQL 服務(wù)器時為數(shù)據(jù)庫文件指定以個存放位置母剥。這個位置在 UNIX/Linux 環(huán)境下通常是 /var/11b/mysql 子目錄滞诺，在 Windows 環(huán)境下通常是 `C:\Programs\MySQL\MySQL Srever n.n\data子目錄。下面給出的文件路徑都是相對于這個子目錄而言的环疼。
每個數(shù)據(jù)表都有一個 *.frm 定義文件习霹，同一個數(shù)據(jù)庫的 *. frm 文件統(tǒng)-存放在以這個數(shù)據(jù)庫名字命名的子目錄里：data/dbname/tablename.frm。這個文件的內(nèi)容是數(shù)據(jù)表的結(jié)構(gòu)定義(數(shù)據(jù)列的名字炫隶、數(shù)據(jù)類型等)淋叶。
從 MySQL4.1 版本開始，MySQL 在每一個數(shù)據(jù)庫子目錄里增加了一個與整個數(shù)據(jù)庫有關(guān)的 db.opt 文件：data/dbname/db.opt 這個文件的內(nèi)容是整個數(shù)據(jù)庫的結(jié)構(gòu)定義和設(shè)置伪阶。
MySQL 還將為每個 MyISAM 數(shù)據(jù)表創(chuàng)建兩個文件煞檩。
- 一個是
data/dbname/tablename.MYD文件，用來存放 MyISAM 數(shù)據(jù)表的數(shù)據(jù) - 另一個是
data/dbname/tablename.MYI文件栅贴，用來存放MyISAM索引(數(shù)據(jù)表的全部索引)斟湃。
根據(jù) MySQL 配置文件中 innodb_file_per_table 選項的設(shè)置情況，InnoDB數(shù)據(jù)表既可以各自存為一個文件檐薯，也可以統(tǒng)一存放在一個所謂的表空間(tablespace)里凝赛。表空間的存放位置和名字由配置設(shè)置決定。MySQL 現(xiàn)在的默認(rèn)安排是把 InnoDB 數(shù)據(jù)表的數(shù)據(jù)和索引存放在 data/dbname/tablename.idb 文件里，把表空間和撤銷日志(undo log)存放在 data/ibdatal哄酝、-2友存、- 3等文件里，把 InnoDB 日志數(shù)據(jù)存放在 data/ib_ logf1e0陶衅、-1屡立、 -2 等文件里。
如果用戶還為數(shù)據(jù)表定義了觸發(fā)器(trigger搀军，詳見第13章), MySQL 現(xiàn)在的做法是把它們的代碼存放在 data/dbname/tblenamb.TRG 文件里膨俐，但這個路徑在未來的 MySQL 版本里可能會發(fā)生變化。
4 數(shù)據(jù)表對比
InnoDB 是一套放在 MySQL 后臺的完整數(shù)據(jù)庫系統(tǒng)罩句，InnoDB 有它自己的緩沖池焚刺，能緩沖數(shù)據(jù)和索引，InnoDB 還把數(shù)據(jù)和索引存放在表空間里面门烂，可能包含好幾個文件乳愉，這和 MyISAM 表完全不同，在 MyISAM 中屯远，表被存放在單獨的文件中蔓姚，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB
新增功能：
- 事務(wù)：MySQL支持4種事務(wù)級別(READ UNCOMMITTED慨丐、READ COMMITED坡脐、REPEATABLE READ、SERIALIZABLE)
- 數(shù)據(jù)行級鎖定機(jī)制：行鎖房揭，被鎖定的是正在接受食物處理的數(shù)據(jù)記錄(MyISAM執(zhí)行LOCK TABLE會導(dǎo)致整個表鎖定
- 外健約束條件：在執(zhí)行DELETE時會檢查定義的外鍵約束對應(yīng)的數(shù)據(jù)
- 崩潰恢復(fù)：發(fā)生崩潰后备闲，InnoDB數(shù)據(jù)表能迅速地自動恢復(fù)到一個穩(wěn)定可用的狀態(tài)
