MySQL数据表类型
作者：vwFisher
时间：2019-11-08
目录
1 概述
MySQL 支持多种数据表类型。比较重要的 3 种类型是：MyISAM、InnoDB、Heap
默认的数据表类型由 MySQL 配置文件里的 default-table-type 决定
2 数据表类型
MySQL 版本支持哪些数据表类型，可以通过 SHOW ENGINES 命令来查看
MySQL文档里有一章是专门介绍各种MySQL数据表类型的，可以在以下网址找到它
http://dev.mysql.com/doc/mysql/en/storage-engines.html
2.1 MyISAM
特点：成熟、稳定、易于管理
内部：可以细分 静态 和 动态 两种，MySQL服务器将自行选择它认为最适合的一种来使用
1.MyISAM Static(静态 MyISAM)
数据表里的数据列各自都有预先定义好的固定长度时，就会自动选择该类型。
这种数据表的数据存取(SELECT、INSERT、UPDATE、DELETE)效率非常高。安全性也高，即使出现文件受损或其他问题，数据记录的提取和恢复工作相比其他类型要容易。
2.MyISAM Dynamic(动态 MyISAM)
数据表定义出现 VARCHAR、xxxTEXT、xxxBLOB 类型字段，就会自动选择该类型。
与 静态MyISAM 相比，该类型的数据表的空间需求量往往小得多，存储 字符串 和 二进制对象 所需要的字节数都是它们的实际长度(VARCHAR多了标记长度的1~2字节)。
因为是不定长度，记录被修改时，就可能导致同一条记录的各个字段不一定存储在一个连续的字节块里，而是分散各处(会引起碎片化)。当编辑的数据表变得越来越碎片化，数据存取消耗的时间也就越长。所以常需要通过 SQL 命令 OPTIMIZE TABLE 或 优化工具(如 myiamchk) 进行碎片整理
3.MyISAM Compressed(压缩MyISAM)
动态 和 静态 MyISAM，都可以用 myiasmchk 工具压缩。压缩效果可以使数据表的空间占用量减少到原来的一半以下。但是注意，压缩后，不能再对它们进行修改，等于变成了只读数据表
2.2 InnoDB
1.事务
支持 ANSI-SQL/92 标准里定义的 4种 事务级别(READ UNCOMMITTED、READ COMMITTED、REPEATTABLE READ、SERIALIZABLE)
2.数据行锁机制
在执行一个事务时(begin ... commit)，通过 FOR UPDATE 对查询到的数据进行行锁(阻塞 SELECT ... FOR UPDATE、INSERT、UPDATE、DELETE)。InnoDB 驱动程序能够自动识别 死锁 现象，并自动终止两个进程中的一个
3.外键约束条件
数据表之间定义了关系，InnoDB 驱动程序将自动保证数据表的引用一执行在执行过 DELETE 命令之后也能保持。即 不可能出现数据表 A 里的一条记录引用数据表 B 里一条已经不复存在的记录的问题。
4.崩溃恢复
发生崩溃后，InnoDB 数据表能够迅速地自动恢复到一个稳定可用的状态(前提是计算机的文件系统没有被破坏)
问题和缺陷
1.表空间的管理
MyISAM 数据表驱动程序把每个数据表分别保存在它自己的文件里，这些文件会根据实际情况增大或缩小
InnoDB 数据表驱动程序是把所有数据和索引都保存在一个表空间(tablespace)里。
表空间由一个或多个文件构成，它们形成了一个虚拟的文件系统。这些文件在被创建之后只能增大，不能缩小。如果想复制某个 InnoDB 数据表，把 MySQL 服务器停下来复制有关文件的办法是行不通的。因此，在管理 InnoDB 数据表时，mysqldump 命令的使用频率要比管理 MyISAM 数据表的时候高得多。
2.数据记录的长度
InnoDB 数据表中的单条数据记录最多可以占用 8000 个字节的空间。这一限制并不包括 TEXT 和 BLOB 数据列，它们只有前 512 个字节是随其他数据列一起存储在数据库里的，超过这个长度的数据将被存放在表空间的其他页面。
3.存储空间占用量
存储空间占用量 要比 同样内容的 MyISAM 数据表大很多(最多时会是 MyISAM 的两倍)
4.全文索引
InnoDB 不支持全文索引(full-text index)
5.GIS数据
InnoDB 数据表不能用来了保存二维地理数据
6.COUNT问题
因为支持事务，InnoDB 数据表驱动程序在统计一个数据表里的记录个数时更麻烦。所以执行 SELECT COUNT(*) FROM TABLE 命令比 MyISAM 数据表要慢得多
7.数据表锁定
InnoDB 驱动程序在执行事务时使用的是它自己的锁定算法。因此，尽量避免 LOCK TABLE ... READ/WRITE 命令。
应该使用 SELECT ... IN SHARE MODE 或 SELECT ... FOR UPDATE 命令，锁定个别记录而不是锁定整个数据表
8.mysql数据表
用于管理 MySQL 访问权限的 mysql 数据表不能被转换为 InnoDB水表。必须是 MyISAM 格式。
9.许可证费用
商用，InnoDB 支持 将收取双倍费用。
2.3 HEAP
HEAP 数据表纸存在于内存中(不是硬盘上)。使用了一个散列索引(hash index)，所以数据记录的存取速度非常快，HEAP 数据表的主要用途是充当临时 数据表。
与普通的数据表相比，HEAP 数据表在功能上受到了许多限制，其中最重要的有：不允许使用 xxxTEXT 和 xxxBLOB 数据类型：只允许使用 = 和 <=> 操作符来搜索记录(不允许使用 <、>、<=、=> 操作符)；不支持 AUTO_INCREMENT 属性，只允许对 NOT NULL 数据列进行索引。
HEAP 数据表适用于数据量相对较小、但对访问速度要求很高的场合。请注意，因为 HEAP 数据表只存在于内存，所以一旦 MySQL 服务器停止运行，HEAP 数据表也就消失了。从这个意义上讲，HEAP 数据表是一种临时性的数据表。但它与特意使用 CREATE TEMPORARY TABLE 命令或是 MySQL 为了保存中间结果而临时创建的数据表是有区别的；HEAP 数据表对于来访问同一个数据库的其他 MySQL 连接是可见的，在链接意外中断时也不丢失。HEAP 数据表的最大长度由 MySQL 配置文件里的 max_heap_table_size 参数决定。
2.4 临时数据表
通过 CREATE TEMPORARY TABLE 命令或是 MySQL 为了保存中间结果而临时创建的数据表
数据类型可以为任何一种，由 MySQL 自行判断。这种数据表在 MySQL 服务器意外掉电时不一定会丢失，但在 MySQL 服务器正常关机、本次 MySQL 连接正常结束 或 意外中断时都将全部丢失。这种数据表对于访问同一个数据库的其 MySQL 连接是不可见的，两个不同的用户可以在同一个数据库里使用相同的名字创建临时数据库而不发生冲突。
临时数据表与其他 MySQL 数据表时分开保存的，MySQL 会把它们存放到一个临时子目录去。这个临时子目录在 Windows 环境通常是 C:\Windows\Temp，在 Unix/Linux 环境通常是 /tmp 或 /var/tmp/ 或 /usr/tmp。这个子目录可以在 MySQL 服务器启动时设置。
2.5 其他的数据类型
1.BDB数据表
是最早具备事务支持能力的 MySQL 数据表类型。但随着 InnoDB 数据表驱动程序的日益成熟，BDB 数据表就淘汰了。
2.ARCHIVE数据表(压缩数据表,始见于MySQL 4.1)
为了保存和备份海量数据而设计的。优点是在保存数据记录之前会先对数据记录进行压缩。
ARCHIVE 数据表只适合用来保存不再需要修改的数据记录(它允许用户执行 INSERTR 命令,但不允许执行 UPDATE 和 DELETE 命令)。
ARCHIVE 数据表不能建立索引，所以每执行一条 SELECT 命令，就必须读取-遍全部的数据记录。因此，这种数据表类型仅适用于数据访问量非常少的场合。
3.CSV数据表(文本格式的数据表，始见于MySQL 4.1)
CSV 数据表里的记录都保存在文本文件里，数据之间用逗号隔开，如“"123","1 am a character string"". CSV数据表不能建立索引。
4.NDB数据表(MySQL集簇，始见于MySQL 4.1)
NDB(network database、网络数据库) 数据表类型是集成在 MySQL Max 版本里的 MySQL 集簇功能中的一种。这种数据表类型支持事务，最适合用来建设数据分布在大量计算机上的网络数据库。使用这种数据表类型的前提是必须有多台安装了 MySQL Max 版本的联网计算机并配置使它们支持集簇操作。
这方面的细节信息可以参见 http://dev.mysql.com/doc/mysq1/en/ndbcluster.html
5.FEDERATED数据表(外部数据表，始见于MySQL 5.0)
这种数据表类型能够让用户去访问外部数据库里的数据表，而那个数据库系统可以位于本地网络中的另一台计算机上。就目
前而言，外部数据库必须也是一个MySQL数据库，但未来的MySQL版本可能会允许使用这种数据表类型与其他品牌的数据库系统建立连接。
FEDERATED 数据表类型还有许多值得完善的地方：FEDERATED 数据表上的事务和查询都无法用 Query Cache 工具优化；不能对外部数据表的结构进行修改(但数据记录可以)。换句话说，不能对 FEDERATED 数据表执行 ALTER TABLE 命令，但可以执行 INSERT、UPDATE、DELETE 命令。
3 数据表文件
可以在启动 MySQL 服务器时为数据库文件指定以个存放位置。这个位置在 UNIX/Linux 环境下通常是 /var/11b/mysql 子目录，在 Windows 环境下通常是 `C:\Programs\MySQL\MySQL Srever n.n\data子目录。下面给出的文件路径都是相对于这个子目录而言的。
每个数据表都有一个 *.frm 定义文件，同一个数据库的 *. frm 文件统-存放在以这个数据库名字命名的子目录里：data/dbname/tablename.frm。这个文件的内容是数据表的结构定义(数据列的名字、数据类型等)。
从 MySQL4.1 版本开始，MySQL 在每一个数据库子目录里增加了一个与整个数据库有关的 db.opt 文件：data/dbname/db.opt 这个文件的内容是整个数据库的结构定义和设置。
MySQL 还将为每个 MyISAM 数据表创建两个文件。
- 一个是
data/dbname/tablename.MYD文件，用来存放 MyISAM 数据表的数据 - 另一个是
data/dbname/tablename.MYI文件，用来存放MyISAM索引(数据表的全部索引)。
根据 MySQL 配置文件中 innodb_file_per_table 选项的设置情况，InnoDB数据表既可以各自存为一个文件，也可以统一存放在一个所谓的表空间(tablespace)里。表空间的存放位置和名字由配置设置决定。MySQL 现在的默认安排是把 InnoDB 数据表的数据和索引存放在 data/dbname/tablename.idb 文件里，把表空间和撤销日志(undo log)存放在 data/ibdatal、-2、- 3等文件里，把 InnoDB 日志数据存放在 data/ib_ logf1e0、-1、 -2 等文件里。
如果用户还为数据表定义了触发器(trigger，详见第13章), MySQL 现在的做法是把它们的代码存放在 data/dbname/tblenamb.TRG 文件里，但这个路径在未来的 MySQL 版本里可能会发生变化。
4 数据表对比
InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
新增功能：
- 事务：MySQL支持4种事务级别(READ UNCOMMITTED、READ COMMITED、REPEATABLE READ、SERIALIZABLE)
- 数据行级锁定机制：行锁，被锁定的是正在接受食物处理的数据记录(MyISAM执行LOCK TABLE会导致整个表锁定
- 外健约束条件：在执行DELETE时会检查定义的外键约束对应的数据
- 崩溃恢复：发生崩溃后，InnoDB数据表能迅速地自动恢复到一个稳定可用的状态
数据表对比.jpg
网友评论