mysql数据表类型

    创建mysal表时可以设置数据表的类型，这种做法在其他数据库中比较少见。mysql支持多种类型数据表，它们有各自的特点和属性，其中最重要的3种类型是：MyISAM, InnoDB, HEAP. 如果在创建表时没有设置表类型，mysql会根据配置文件中的default-table-type选项来在MyISAM和InnoDB之间做出选择。

    本文对mysql支持的数据表类型做个简单的介绍。

1、MyISAM数据表

    MyISAM的特点是成熟、稳定以及易于管理，通常情况下建议选择这种类型。这种类型内部又分为静态MyISAM和动态MyISAM两种类型，mysql会根据数据表中的数据列的情况来自动做出选择。

1.1 静态MyISAM

    如果数据表中的所有列都有预先定义好的固定长度，mysql服务器将选择这种表类型。这种表的特点是存取效率非常高，即使是对于INSERT、UPDATE和DELETE这些修改指令的频繁操作。除此以外，这种类型的安全性也很高，即使文件受损或其他问题，数据记录的提取和恢复也比其他类型容易。

1.2 动态MyISAM

    当数据表中存在长度不确定的列，如VARCHAR、xxxTEXT、xxxBLOB等，mysql会自动选择这种类型。与MyISAM相比，它的优点在于数据表的空间需求量会小很多：存储字符串和二进制兑现给所需的空间仅仅是他们的实际长度。但这样就意味着数据记录很可能是不一样长度的，如果记录要被修改，那这条记录在数据库文件中的存储位置就可能发生变化，并在原先的位置留下一个洞。于是，同一条记录的各个字段就有可能在数据库文件不是连续存储的，而是会散布于各处。当数据表变得越来越碎片化时，数据的存取效率就会越来越差，因此这种类型的表需要经常使用SQL命令OPTIMIZE TABLE或者某个优化工具来金鑫个碎片整理（myisamchk).

1.3 压缩MyISAM

    静态和动态MyISAM都可以使用myiasmchk工具来压缩。压缩后通常能够使数据表占用的空间减小一半以上。尽管压缩后，在读取数据时需要进行解压，但在某些情况下访问速度甚至会更快——这在“低速硬盘+高速CPU”的系统上尤为明显。压缩MyISAM最大的缺点是，压缩之后数据便不能再修改，即数据表变成了只读数据表。

2、InnoDB数据表

    InnoDB可以看做是MyISAM的一种更新替代产品，它至少增加了以下几种新功能：

      事务：InnoDB数据表里的数据库操作可以以事务的形式执行。

     数据行级锁定机制：在执行一个事务时，InnoDB数据表的驱动程序使用的是它自己内建的数据行级锁定机制，而非mysql本身提供的数据表级锁定机制。即在操作过程中，数据表不会被锁定，其他用户仍然可以访问它，被锁定的只有事务操作中访问的几行数据记录，而MyISAM使用的LOCK TABLE命令会在执行期间锁定整张表。如果有多个用户在同时对一个大数据表进行修改，那InnoDB会大大提高处理效率。

    另外，InnoDB能够自动识别“死锁“的状态，并自动终止”死锁“中的一个进程来打破死锁。

     外键约束条件：如果数据表之间定义了关系，InnoDB驱动程序会保证在执行DELETE指令之后，数据表之间的引用一致性。即数据表A中的记录不可能引用一条数据表B中一条不存在的记录。

     崩溃恢复：在发生崩溃之后，InnoDB数据表能够迅速的自动恢复到一个稳定可用状态（前提是计算机文件系统没有被破坏）。

    当然，除了以上这些优点以外，InnoDB也存在一些缺点：

     表空间的管理：MyISAM数据表驱动程序把每个数据表分别保存在它自己的文件里，文件会根据实际情况增大或者缩小。而InnoDB数据表驱动程序将所有的数据表和索引都保存在一个表空间中，表空间有一个或多个文件，它们形成了一个虚拟文件系统，这些文件创建之后，只能增大不能减小。

     数据记录的长度：InnoDB数据表中单挑数据记录最多可以占用8000个字节空间，这以限制并不暴扣TEXT和BLOB数据列，它们只有前512个字节是随其他数据一切存储在数据库里的，超过这个长度的数据将被存储于表空间的其他页面。

     存储空间占用量：InnoDB数据表空间占用会比同样内容的MyISAM数据表大很多，最多能达到后者的两倍。

     全文索引：InnoDB数据表不支持全文索引。

     GIS数据：InnoDB不能用于保存二位地理数据。

     COUNT问题：因为支持事务，InnoDB在统计表中记录数时经常会遇到困难，所以InnoDB数据表执行SELECT COUNT(*) FROM TABLE命令的速度会比MyISAM数据上慢很多。

     数据表锁定：InnoDB在执行事务时，使用自己的锁定算法。索引应该尽量避免LOCK TABLE ... READ/WRITE命令。应该尽量使用SELECT ... IN SHARE MODE 或者SELECT ... FOR UPDATE命令。

     mysql数据表：用于管理MySQL访问权限的mysql数据表不能被转换为InnoDB数据表，它必须是MyISAM格式。

3、HEAP数据表

    HEAP数据表值存在于内存中，它们使用一个散列索引，所以数据记录的存取数据非常快。它们主要用于临时数据表。与普通数据表相比，HEAP数据表在功能上受到许多限制，其中最重要的有：

• 不允许使用xxxTEXT和xxxBLOB数据类型；
• 只允许使用=和<=>操作符来搜索记录，不允许使用<、>、<=、>=操作符；
• 不支持AUTO_INCREMENT属性；
• 只允许对NOT NULL数据列进行索引。

    HEAP数据表适用于数据量小，但对访问速度要求很高的场合。由于HEAP数据表存在于内存，所以一旦mysql服务器停止，HEAP数据表也就消失了。从这个意义上讲，HEAP数据表是一种临时的数据表，但它和特意使用CREATE TEMPORARY TABLE命令或是mysql为了保存中间结果而临时创建的数据表是有区别的：HEAP数据表对于来访问同一数据的其他链接是可见的，在连接以外中断时也不会丢失。HEAP数据表的的最大长度由mysql配置文件中的max_heap_table_size来决定。

4、临时数据表

    此处临时数据表是指使用CREATE TEMPORARY TABLE命令或是mysql为了保存中间结果而临时创建的数据表，其数据类型可以是前面介绍的任何一种。这种数据表在服务器意外当机时不一定会丢失，但在mysql服务器正常关闭、本次mysql连接正常结束或意外中断时将全部丢失。此外，这种数据表对于访问同一数据库的其它连接时不可见的，两个用户在同一个数据库里同时创建相同名称的临时数据表不会产生冲突。临时数据表与其它mysql数据表时分开存储的，它们会被存储到一个临时的目录中。这个临时的目录在Windows上一般是C:\Windows\Temp，而Linux/Unix环境下，通常是/var/tmp或/usr/tmp，具体存储路径可以在启动mysql服务时进行设置。

5、其它类型

    除了以上介绍的类型外，mysql还支持一些其他的类型，更多的介绍可以参见： http://dev.mysql.com/doc/refman/5.7/en/storage-engines.html