mysql笔记-- mysql命令札记8 --MySql 数据表类型

选择正确的表类型，能提高性能和速度，在mysql中支持：

   事务安全的表类型：InnoDB & BDB

    非事务安全的表类型:ISAM、 MySAM（为ISAM的升级版）、MERGE、HEAP；

MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

要明确表示你想要用一个MyISAM表格，请用ENGINE表选项指出来：

MyISAM支持三种不同存储格式。其中两个（固定格式和动态格式)根据正使用的列的类型来自动选择。第三个，即已压缩格式，只能使用myisampack工具来创建。

当你CREATE或ALTER一个没有BLOB或TEXT列的表，你可以用ROW_FORMAT表选项强制表的格式为FIXED或DYNAMIC。这会导致CHAR和VARCHAR列因FIXED格式变成CHAR，或因DYNAMIC格式变成VARCHAR。

通过用ALTER TABLE指定ROW_FORMAT={COMPRESSED | DEFAULT}，你可以压缩或解压缩表，

1. 静态（固定长度）表特征

静态格式是MyISAM表的默认存储格式。当表不包含变量长度列（VARCHAR, BLOB, 或TEXT）时，使用这个格式。每一行用固定字节数存储。

MyISAM的三种存储格式中，静态格式就最简单也是最安全的（至少对于崩溃而言）。静态格式也是最快的on-disk格式。快速来自于数据文件中的行在磁盘上被找到的容易方式：当按照索引中的行号查找一个行时，用行长度乘以行号。同样，当扫描一个表的时候，很容易用每个磁盘读操作读一定数量的记录。

当MySQL服务器正往一个固定格式MyISAM文件写的时候，如果计算机崩溃了，安全是显然的。在这种情况下，myisamchk可以容易地决定每行从哪里开始到哪里结束，所以它通常可以收回所有记录，除了写了一部分的记录。注意，基于数据行，MyISAM表索引可以一直被重新构建。

静态格式表的一般特征：

·         CHAR列对列宽度是空间填补的。

·         非常快。

·         容易缓存。

·         崩溃后容易重建，因为记录位于固定位置。

·         重新组织是不必要的，除非你删除巨量的记录并且希望为操作系统腾出磁盘空间。为此，可使用OPTIMIZE TABLE或者myisamchk -r。

2. 动态表特征

如果一个MyISAM表包含任何可变长度列（VARCHAR, BLOB或TEXTDynamic），或者如果一个表被用ROW_FORMAT=DYNAMIC选项来创建，动态存储格式被使用。

这个格式更为复杂一点，因为每行有一个表明行有多长的头。当一个记录因为更新的结果被变得更长，该记录也可以在超过一个位置处结束。

你可以使用OPTIMIZE TABLE或myisamchk来对一个表整理碎片。如果在一个表中有你频繁访问或改变的固定长度列，表中也有一些可变长度列，仅为避免碎片而把这些可变长度列移到其它表可能是一个好主意。

动态格式表的一般特征：

·         除了长度少于4的列外，所有的字符串列是动态的。

·         在每个记录前面是一个位图，该位图表明哪一列包含空字符串（对于字符串列）或者0（对于数字列）。注意，这并不包括包含NULL值的列。如果一个字符列在拖曳空间移除后长度为零，或者一个数字列为零值，这都在位图中标注了且列不被保存到磁盘。 非空字符串被存为一个长度字节加字符串的内容。

·         通常比固定长度表需要更少的磁盘空间。

·         每个记录仅使用必需大小的空间。尽管如此，如果一个记录变大，它就按需要被分开成多片，造成记录碎片的后果。比如，你用扩展行长度的信息更新一行，该行就变得有碎片。在这种情况下，你可以时不时运行OPTIMIZE TABLE或myisamchk -r来改善性能。可使用myisamchk -ei来获取表的统计数据。

·         动态格式表在崩溃后要比静态格式表更难重建，因为一个记录可能被分为多个碎片且链接（碎片）可能被丢失。

·         动态尺寸记录期望的行长度用下列表达式来计算：

·                3

·                + (number of columns + 7) / 8

·                + (number of char columns)

·                + (packed size of numeric columns)

·                + (length of strings)

·                + (number of NULL columns + 7) / 8

对每个链接需要额外的6字节。在一个更新导致一个记录的扩大之时，一个动态记录被链接了。每个新链接至少是20字节，所以下一个扩大可能在同样的链接里进行。如果不是，则另一个链接将被建立。你可以使用myisamchk -ed来找出链接的数目。所有的链接可以用myisamchk -r来移除。 

3. 已压缩表特征

已压缩存储格式是由myisampack工具创建的只读格式。

所有MySQL分发版里都默认包括myisampack。已压缩表可以用myisamchk来解压缩。

已压缩表有下列特征：

·         已压缩表占据非常小的磁盘空间。这最小化了磁盘用量，当使用缓慢的磁盘（如CD-ROM)之时，这是很有用的。

·         每个记录是被单独压缩的，所以只有非常小的访问开支。依据表中最大的记录，一个记录的头在每个表中占据1到3个字节。每个列被不同地压缩。通常每个列有一个不同的Huffman树。一些压缩类型如下：

o        后缀空间压缩。

-        前缀空间压缩。

-        零值的数用一个位来存储。

-        如果在一个整型列中的值有一个小的范围，列被用最小可能的类型来存储。比如，一个BIGINT列（8字节），如果所有它的值在-128到127范围内，它可以被存储为TINYINT列（1字节）

-        如果一个列仅有一小组可能的值，列的类型被转化成ENUM。

-        一个列可以使用先前压缩类型的任意合并。

·         可以处理固定长度或动态长度记录。

3. MERGE存储引擎

MERGE存储引擎，也被认识为MRG_MyISAM引擎，是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。你不能合并列被以不同顺序列于其中的表，没有恰好同样列的表，或有不同顺序索引的表。而且，任何或者所有的表可以用myisampack来压缩。。表选项的差异，比如AVG_ROW_LENGTH, MAX_ROWS或PACK_KEYS都不重要。

当你创建一个MERGE表之时，MySQL在磁盘上创建两个文件。文件名以表的名字开始，并且有一个扩展名来指明文件类型。一个.frm文件存储表定义，一个.MRG文件包含被当作一个来用的表的名字。这些表作为MERGE表自身，不必要在同一个数据库中。

你可以对表的集合用SELECT, DELETE, UPDATE和INSERT。你必须对你映射到一个MERGE表的这些表有SELECT, UPDATE和DELETE 的权限。

如果你DROP MERGE表，你仅在移除MERGE规格。底层表没有受影响。

当你创建一个MERGE表之时，你必须指定一个UNION=(list-of-tables)子句，它说明你要把哪些表当作一个来用。如果你想要对MERGE表的插入发生在UNION列表中的第一个或最后一个表上，你可以选择地指定一个INSERT_METHOD选项。使用FIRST或LAST值使得插入被相应地做在第一或最后一个表上。如果你没有指定INSERT_METHOD选项，或你用一个NO值指定该选项。往MERGE表插入记录的试图导致错误。

下面例子说明如何创建一个MERGE表：

mysql> CREATE TABLE t1 (

    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    ->    message CHAR(20));

mysql> CREATE TABLE t2 (

    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    ->    message CHAR(20));

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (

    ->    a INT NOT NULL AUTO_INCREMENT,

    ->    message CHAR(20), INDEX(a))

    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

注意，一个列在MERGEN表中被索引，但没有被宣告为一个PRIMARY KEY，因为它是在更重要的MyISAM表中。这是必要的，因为MERGE表在更重要的表中的设置上强制非唯一性。

创建MERGE表之后，你可以发出把一组表当作一体来操作的查询：

mysql> SELECT * FROM total;

+---+---------+

| a | message |

+---+---------+

| 1 | Testing |

| 2 | table   |

| 3 | t1      |

| 1 | Testing |

| 2 | table   |

| 3 | t2      |

+---+---------+

注意，你也可以直接从MySQL之外直接操作.MRG文件：

shell> cd /mysql-data-directory/current-database

shell> ls -1 t1 t2 > total.MRG

shell> mysqladmin flush-tables

要重映射一个MERGE表到一个不同的MyISAM表集，你可以执行下列之一：

·         DROP MERGE表并且重建它。

·         使用ALTER TABLE tbl_name UNION=(...)来改变底层表的列表。

·         改变.MRG文件，并对MERGE表或者所有底层表发出一个FLUSH TABLE语句来强制存储引擎去读新的定义文件。

MERGE表可以帮你解决以下问题：

·         容易地管理一套日志表。比如，你可以把不同月的数据放进分离的表中，用myisampack压缩其中的一些，并随后创建一个MERGE表来把它们当作一个来使用。

·         获得更快的速度。你可以以一些标准来分割大的只读表，然后放进不同磁盘上的单个表中。基于此的一个MERGE表可比使用大表要快得多。

·         执行更有效的搜索。如果你确切知道要搜索什么，对一些查询你可以只在被分割的表的其中之一来搜索，并且对其它使用MERGE。你甚至有许多不同的MERGE表，它们使用有重叠的表套。

·         执行更有效的修补。修补被映射到一个MERGE表中的单个表比修补单个大型表要更轻松。

·         即刻映射许多表成一个。MERGE表不需要维护它自己的索引，因为它使用大哥表的所用。因此，MERGE表集合是非常块地创建或重映射。（注意，当你创建一个MERGE表之时，即使没有索引被创建，你必须仍然指定索引定义）。

·         如果根据需要或按照批次，你有一组要合起来作为一个大表的表，你应该根据需要对它们创建一个MERGE表来替代大表。这样要快得多而且节约大量的磁盘空间。

·         超过操作系统的文件尺寸限制。每个MyISAM表都受制于这个限制，但是一个MyISAM表的集合则不然。

·         你可以通过定义一个映射到单个表的MERGE表来为一个MyISAM表创建一个别名或“同物异名”。这样做应该没有真实的可察觉的性能影响 （对每个读只有一些间接调用和memcpy()调用）。

MERGE表的缺点：

·         你可以对MERGE表使用仅相同的MyISAM表。

·         你不能在MERGE表中使用很多MyISAM功能。比如，你不能在MERGE表上创建FULLTEXT索引。（当然，你可以在底层MERGE 表上创建FULLTEXT索引，但是你不能用全文搜索来搜索MERGE表）。

·         如果MERGE表是非临时的，所有底层MyISAM表也必须是永久的。如果MERGE表是临时的，MyISAM表可以是任何临时＆非临时的混合。

·         MERGE表使用更多的文件描述符。如果是个客户端正使用一个映射到10个表的MERGE表，服务器使用(10*10)+10个文件描述符。（10个数据文件描述符给10个客户端每人一个，并且在客户端之间共享10个索引文件描述符）。

· 键读会更慢。当你读一个键的时候，MERGE存储引擎需要在所有 底层表上发出一个读以检查哪一个接近匹配给定的键。如果你随后做了一个read-next，MERGE存储引擎需要搜索读缓冲来找出下一个键。只有当一个键缓冲被耗尽，存储引擎才需要读下一个键块。这使得MERGE键在eq_ref搜索中非常慢，但在ref搜索中不是太慢。

15.3.1. MERGE表方面的问题

下列是已知关于MERGE表的问题：

·         如果你使用ALTER TABLE 来把MERGE表变为其它表类型，到底层表的映射就被丢失了。取而代之的，来自底层MyISAM表的行被复制到已更换的表中，该表随后被指定新类型。

·         REPLACE不起作用。

·         没有WHERE子句，或者在任何被映射到一个打开的MERGE表上的任何一个表上的REPAIR TABLE，TRUNCATE TABLE, OPTIMIZE TABLE或ANALYZE TABLE，你不能使用DROP TABLE, ALTER TABLE, DELETE FROM。如果你这么做了，MERGE表将仍旧指向原始表，这样产生意外结果。解决这个不足最简单的办法是在执行任何一个这些操作之前发出一个FLUSH TABLES语句来确保没有MERGE表仍旧保持打开。

·         一个MERGE表不能在整个表上维持UNIQUE约束。当你执行一个INSERT, 数据进入第一个或者最后一个MyISAM表（取决于INSERT_METHOD选项的值）。MySQL确保唯一键值在那个MyISAM表里保持唯一，但不是跨集合里所有的表。

·         当你创建一个MERGE表之时，没有检查去确保底层表的存在以及有相同的机构。当MERGE表被使用之时，MySQL检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的MyISAM表创建一个MERGE表，你非常有可能撞见奇怪的问题。

·         在MERGE表中的索引的顺序和它的底层表中的索引应该一样。如果你使用ALTER TABLE给一个被用在MERGE表中的表添加一个UNIQUE索引，然后使用ALTER TABLE在MERGE表上添加一个非唯一索引，如果在底层表上已经有一个非唯一索引，对表的索引排序是不同的。（这是因为ALTER TABLE把UNIQUE索引放在非唯一索引之前以利于重复键的快速检测 ）。因此对使用这样索引的表的查询可能返回不期望的结果。

·         在Windows中，在一个被MERGE表使用的表上DROP TABLE不起作用，因为MERGE引擎的表映射对MySQL的更上层隐藏。因为

15.4. MEMORY (HEAP)存储引擎

MEMORY存储引擎用存在内存中的内容来创建表。这些在以前被认识为HEAP表。MEMORY是一个首选的术语，虽然为向下兼容，HEAP依旧被支持。

每个MEMORY表和一个磁盘文件关联起来。文件名由表的名字开始，并且由一个.frm的扩展名来指明它存储的表定义。

要明确指出你想要一个MEMORY表，可使用ENGINE选项来指定：

CREATE TABLE t (i INT) ENGINE = MEMORY;

如它们名字所指明的，MEMORY表被存储在内存中，且默认使用哈希索引。这使得它们非常快，并且对创建临时表非常有用。可是，当服务器关闭之时，所有存储在MEMORY表里的数据被丢失。因为表的定义被存在磁盘上的.frm文件中，所以表自身继续存在，在服务器重启动时它们是空的。

这个例子显示你如何可以创建，使用并删除一个MEMORY表：

mysql> CREATE TABLE test ENGINE=MEMORY

    ->     SELECT ip,SUM(downloads) AS down

    ->     FROM log_table GROUP BY ip;

mysql> SELECT COUNT(ip),AVG(down) FROM test;

mysql> DROP TABLE test;

MEMORY表有下列特征：

·         给MEMORY表的空间被以小块来分配。表对插入使用100%动态哈希来。不需要溢出区或额外键空间。自由列表无额外的空间需求。已删除的行被放在一个以链接的列表里，并且在你往表里插入新数据之时被重新使用。MEMORY表也没有通常与在哈希表中删除加插入相关的问题。

·         MEMORY表可以有多达每个表32个索引，每个索引16列，以及500字节的最大键长度。

·         MEMORY存储引擎执行HASH和BTREE索引。你可以通过添加一个如下所示的USING子句为给定的索引指定一个或另一个：

·                CREATE TABLE lookup

·                    (id INT, INDEX USING HASH (id))

·                    ENGINE = MEMORY;

·                CREATE TABLE lookup

·                    (id INT, INDEX USING BTREE (id))

·                    ENGINE = MEMORY;

·         你可以在一个MEMORY表中有非唯一键。（对哈希索引的实现，这是一个不常用的功能）。

·         你页可以对MEMORY表使用INSERT DELAYED。

·         如果你在一个有高度键重复的（许多索引条目包含同一个值）MEMORY表上有一个哈希索引，对影响键值的表的更新及所有删除都是明显地慢的。这个变慢的程度比例于重复的程度（或者反比于索引cardinality）。你可以使用一个B树索引来避免这个问题。

·         MEMORY表使用一个固定的记录长度格式。

·         MEMORY不支持BLOB或TEXT列。

·         MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。

·         MEMORY表在所有客户端之间共享（就像其它任何非TEMPORARY表）。

·         MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理之时的空闲中创建的内部表共享。可是，两个类型的表不同在于MEMORY表不会遇到存储转换，而内部表是：

-        如果一个内部表变得太大，服务器自动把它转换为一个磁盘表。尺寸限制由tmp_table_size系统变量的值来确定。

-        MEMORY表决不会转换成磁盘表。要确保你不会偶尔做点傻事，你可以设置max_heap_table_size系统变量给MEMORY表加以最大尺寸。对于单个的表，你也可以在CREATE TABLE语句中指定一个MAX_ROWS表选项。

·         服务器需要足够内存来维持所有在同一时间使用的MEMORY表。

·         当你不再需要MEMORY表的内容之时，要释放被MEMORY表使用的内存，你应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE）。

·         当MySQL服务器启动时，如果你想开拓MEMORY表，你可以使用--init-file选项。例如，你可以把INSERT INTO ... SELECT 或LOAD DATA INFILE这样的语句放入这个文件中以便从持久稳固的的数据源装载表。

·         如果你正使用复制，当主服务器被关闭且重启动之时，主服务器的MEMORY表变空。可是从服务器意识不到这些表已经变空，所以如果你从它们选择数据，它就返回过时的内容。自从服务器启动后，当一个MEMORY表在主服务器上第一次被使用之时，一个DELETE FROM语句被自动写进主服务器的二进制日志，因此再次让从服务器与主服务器同步。注意，即使使用这个策略，在主服务器的重启和它第一次使用该表之间的间隔中，从服务器仍旧在表中有过时数据。可是，如果你使用--init-file选项于主服务器启动之时在其上推行MEMORY表。它确保这个时间间隔为零。

·         在MEMORY表中，一行需要的内存使用下列表达式来计算：

·                SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)

·                + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)

·                + ALIGN(length_of_row+1, sizeof(char*))

ALIGN()代表round-up因子，它使得行的长度为char指针大小的确切倍数。sizeof(char*)在32位机器上是4，在64位机器上是8。