MySQL学习笔记(二)：MyISAM存储引擎

MyISAM 存储引擎

10余年的阿拉善盟网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。全网营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整阿拉善盟建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“阿拉善盟网站设计”,“阿拉善盟网站推广”以来，每个客户项目都认真落实执行。

MyISAM 基于旧的 (不再可用) ISAM 存储引擎, 但有许多有用的扩展。

每个 MyISAM 表都存储在三个文件中的磁盘上。这些文件具有以表名开头的名称, 并具有用于指示文件类型的扩展名。

.frm文件存储表定义。

数据文件的扩展名为.MYD (MYData)。

索引文件的扩展名是.MYI (MYIndex)

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

CREATE TABLE t (i INT) ENGINE = MYISAM;

在 MySQL 5.7 中, 通常需要使用 ENGINE 来指定 MyISAM 存储引擎, 因为 InnoDB 是默认引擎。

可以使用 mysqlcheck 客户端或 myisamchk 实用程序检查或修复 MyISAM 表。还可以使用 myisampack 压缩 MyISAM 表, 以减少更少的空间。

如下是MyISAM存储引擎的一些特征:

1. 所有数据值首先以低字节存储。这使得数据机器和操作系统独立。二进制可移植性的唯一要求是机器使用两个补间符号整数和 IEEE 浮点格式。这些要求广泛应用于在主流机器中。二进制兼容性可能不适用于有特殊的处理器的嵌入式系统。

2. 先存储数据低字节并不严重地影响速度；数据行中的字节一般是未联合的，从一个方向读未联合的字节并不比从反向读更占用更多的资源。服务器上的获取列值的代码与其它代码相比并不显得时间紧。

3. 所有数字键值以高字节为先被存储以允许一个更高地索引压缩

4. 大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持

5. MyISAM表最大行数为 18440000000000000000

6. 每个MyISAM表最大索引数是64

7. 每个索引最大的列数是16个

8. 最大的键长度是1000字节。这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的的键块被用上。

9. 当记录以排好序的顺序插入（就像你使用一个AUTO_INCREMENT列之时），索引树被劈开以便高节点仅包含一个键。这改善了索引树的空间利用率。

10. 支持每表一个AUTO_INCREMEN列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。（当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况 ）。AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置。

11. 当把删除和更新及插入混合的时候，动态尺寸的行更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。

12. MyISAM 支持并发插入:如果数据文件中间的表没有自由块了，在其它线程从表读的同时，你可以INSERT新行到表中。（这被认识为并发操作 ）。自由块的出现是作为删除行的结果，或者是用比当前内容多的数据对动态长度行更新的结果。当所有自由块被用完（填满），未来的插入又变成并发。请参阅8.11.3 节 "并发插入"。

13. 你可以把数据文件和索引文件放在不同目录，用DATA DIRECTORY和INDEX DIRECTORY选项CREATE TABLE以获得更高的速度，请参阅13.3.18节，“CREATE TABLE语法”。

14. BLOB和TEXT列可以被索引。

15. NULL值被允许在索引的列中。这个占每个键的0-1个字节

16. 每个字符列可以又不同的字符集，请参阅第10.1章 ：“字符集支持”。

17. 在MyISAM索引文件里又一个标志，它表明表是否被正确关闭。如果用--myisam-recover选项启动mysqld，MyISAM表在打开得时候被自动检查，如果被表被不恰当地关闭，就修复表。

18. 如果你用--update-state选项运行myisamchk，它标注表为已检查。myisamchk --fast只检查那些没有这个标志的表。

19. myisamchk --analyze为部分键存储统计信息，也为整个键存储统计信息。

20. myisampack可以打包BLOB和VARCHAR列

MyISAM也支持下列特征：

1. 支持true VARCHAR类型；VARCHAR列以存储在2个字节中的长度来开始。

2. 有VARCHAR的表可以有固定或动态记录长度。

3. VARCHAR和CHAR列可以多达64KB。

4. 一个被搞乱的已计算索引对可对UNIQUE来使用。这允许你在表内任何列的合并上有UNIQUE。（尽管如此，你不能在一个UNIQUE已计算索引上搜索）。

MyISAM 启动选项:

mysqld 的以下选项可用于更改 MyISAM 表的行为。有关其他信息, 请参见5.1.4 节 "服务器命令选项"。

1. --myisam-recover-options=mode

设置为崩溃MyISAM表自动恢复的模式

2. --delay-key-write=ALL

对任何MyISAM表的写操作之间不要刷新键缓冲区。

注释：如果要这么做。当MyISAM表在使用中之时，不应该使用另一个程序来访问它（比如从另一个MySQL服务器或用myisamchk）。这么做会导致索引被破坏。对使用--delay-key-write的表，--external-locking不会生效

以下系统变量会影响 MyISAM 表的行为。有关其他信息, 请参见5.1.5 节 "服务器系统变量"。

1. bulk_insert_buffer_size

用在块插入优化中的树缓冲区的大小。注释：这是一个per thread的限制。

2. myisam_max_sort_file_size

在重新创建 MyISAM 索引 (在修复表、更改表或加载数据 INFILE 期间) 允许 MySQL 使用的临时文件的最大大小。如果文件大小大于此值, 则使用密钥缓存来创建索引, 这会比较慢。该值以字节为单位给出。

3. myisam_sort_buffer_size

设置恢复表时使用的缓冲区大小。

如果用--myisam-recover选项启动mysqld，自动恢复被激活。在这种情况下，当服务器打开一个MyISAM表之时，服务器会检查是否表被标注为崩溃，或者表的打开计数变量是否不为0且你正用--skip-external-locking运行服务器。如果这些条件的任何一个为真，下列情况发生：

1. 表被查错。

2. 如果服务器发现一个错误，它试着做快速表修复（排序且不重新创建数据文件）。

3. 如果修复因为数据文件中的一个错误而失败（例如，一个重复键错误），服务器会再次尝试修复，这一次重建数据文件。

4. 如果修复仍然失败，服务器用旧修复选项方法再重试一次修复（一行接一行地写，不排序）。这个方法应该能修复任何类型的错误，并且需要很低的磁盘空间。

如果恢复不能够从先前完成的语句里恢复所有行，而且你不能在--myisam-recover选项值指定FORCE，自动修复会终止，并在错误日志里写一条错误信息：

Error: Couldn't repair table: test.g00pages

如果你指定FORCE，取而代之地，类似这样的一个警告被给出：

Warning: Found 344 of 354 rows when repairing ./test/g00pages

注释：如果自动恢复值包括BACKUP，恢复进程创建文件并用tbl_name-datetime.BAK形式取名。你应该有一个cron脚本，它自动把这些文件从数据库目录移到备份媒质上。

键所需的空间

1. MyISAM表使用B型树索引。你可以粗略地计算索引文件的大小为(key_length+4)/0.67, 加上所有的键之和。当所有键以排序的顺序插入并且表没有任何压缩的键之时，以上估计是对最坏的情况的。

2. 字符串索引是被空间压缩的。如果第一个字符串索引部分是字符串，它也被加前缀压缩。如果字符串列有许多拖曳空间，或字符串 列是一个总是不用完全长度的VARCHAR列，空间压缩使得索引文件比最坏情况时的数值要小。前缀压缩被用在以字符串开始的键上。如果有许多具有同一前缀的字符串，前缀压缩是有帮助的。

3. 在MyISAM表，你也可以在创建表的时候通过指定PACK_KEYS=1来前缀压缩数字。当数字被以高字节优先存储之时，若你有许多具有同一前缀的整数键，上述方法是有帮助的。

MyISAM表的存储格式

MyISAM 支持三种不同的存储格式。根据所使用的列类型, 将自动选择其中的两个, 即固定和动态格式。第三个压缩格式只能使用 myisampack 实用程序创建 (请参见4.6.5 节 "myisampack 生成压缩的只读 MyISAM 表")。

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

有关 ROW_FORMAT 的信息, 请参见13.1.18 节 "创建表语法"。

您可以使用 myisamchk --unpack 解压 (unpack) 压缩的 MyISAM 表，“myisamchk — MyISAM Table-Maintenance Utility”的详细信息见4.6.3章节

静态（固定长度）表特征

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

2. 在三 MyISAM 存储格式中, 静态格式是最简单、最安全的 (最不受损坏的)。由于可以轻松地在磁盘上找到数据文件中的行, 因此它也是最快的on-disk格式: 当按照索引中的行号查找一个行时，用行长度乘以行号。同样，当扫描一个表的时候，很容易用每个磁盘读操作读一定数量的记录。

3. 如果您的计算机在 MySQL 服务器写入固定格式的 MyISAM 文件时崩溃, 安全是显然的。在这种情况下, myisamchk 可以很容易地确定每行的开始和结束位置, 因此它通常可以回收除了部分写入之外的所有行(缓冲区中未写入的所有行)。MyISAM 表索引始终可以基于数据行进行重建。

注意：固定长度行格式仅适用于没有 BLOB 或TEXT列的表。使用显式 ROW_FORMAT 子句创建具有BLOB 或TEXT这些列的表不会引发错误或警告; 格式规范将被忽略。

静态格式表的一般特征：

1. CHAR 和 VARCHAR 列的空间填充到指定的列宽，尽管列类型未被更改。BINARY和 VARBINARY 列用0x00 字节填充到列宽。

2. NULL 列需要行中的额外空间来记录它们的值是否为 NULL。每个 NULL 列需要一个额外的位, 四舍五入到最近的字节。

3. 非常快。

4. 容易缓存。

5. 在崩溃后易于重建, 因为数据行位于固定位置。

6. 除非删除大量的行并希望将可用磁盘空间返回给操作系统, 否则无需进行重组。为此, 请使用 OPTIMIZE TABLE 或 myisamchk -r。

7. 通常比动态格式表需要更多的磁盘空间。

8. 使用以下表达式计算静态大小行的预期行长度 (以字节为单位):

row length = 1

+ (sum of column lengths)

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

+ (number of variable-length columns)

对于具有静态行格式的表, delete_flag 为1。静态表在行记录中使用位, 用于指示是否已删除行的标志。delete_flag 是0用于动态表, 因为该标志存储在动态行标题中。

动态表特性

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

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

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

动态格式表的一般特征：

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

2. 每行前面都有一个位图, 指示哪些列包含空字符串 (对于字符串列) 或0 (对于数值列)。这不包括包含 NULL 值的列。如果字符串列在尾随空格后的长度为0, 或者数值列的值为0, 则在位图中标记, 而不保存到磁盘。非空字符串保存为一个长度字节加上字符串内容。

3. NULL 列需要行中的额外空间来记录它们的值是否为 NULL。每个 NULL 列需要一个额外的位, 四舍五入到最近的字节。

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

5. 每行只使用所需的空间。但是, 如果行变得更大, 则会将其拆分为所需的多个片断, 从而导致行碎片。例如, 如果使用扩展行长度的信息更新行, 则该行将变得有碎片。在这种情况下, 您可能需要时不时运行OPTIMIZE TABLE或myisamchk -r以提高性能。使用 myisamchk -ei 获取表统计信息。

6. 比静态格式表在崩溃后重建更困难, 因为行可能会被分割成许多片段和链接 (碎片), 可能会在重建时丢失。

7. 使用以下表达式计算动态大小行的预期行长度:

row length = 3

+ (number of columns + 7) / 8

+ (number of char columns)

+ (packed size of numeric columns)

+ (length of strings)

+ (number of NULL columns + 7) / 8

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

压缩表特性

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

已压缩表可以用myisamchk来解压缩。

压缩表有下列特征：

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

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

 2.1 后缀空间压缩。

 2.2 前缀空间压缩。

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

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

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

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

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

注意

虽然压缩表是只读的, 因此不能在表中更新或添加行, 但 DDL (数据定义语言) 操作仍然有效。例如, 您仍然可以使用 drop 除去表, 并截断表以清空表。

MyISAM 表问题

MySQL用来存储数据的文件格式已经被广泛测试过，但总是有导致数据表变得损坏的环境。

损坏的MyISAM表

即使MyISAM表格式非常可靠（SQL语句对表做的所有改变在语句返回之前被写下），如果下列任何事件发生，你依然可以获得损坏的表：

1. mysqld 进程在写入过程中被杀死。

2. 发生意外的计算机关机 (例如, 计算机被关闭)。

3. 硬件故障。

4. 使用外部程序 (如 myisamchk) 修改正在被服务器修改的表。

5. MySQL 或 MyISAM 代码中的bug。

表损坏的典型症状为:

1. 从表中查询数据时会出现以下错误:

Incorrect key file for table: '...'. Try to repair it

2. 查询在表中找不到行或返回不完整的结果。

可以使用 CHECK TABLE语句检查 MyISAM 表的健康状况, 并用REPAIR TABLE修复损坏的 MyISAM 表。当 mysqld 未运行时, 还可以使用 myisamchk 命令检查或修复表。

参阅 13.7.2.2 章节 “CHECK TABLE Syntax”, 13.7.2.5 章节 “REPAIR TABLE Syntax”, 4.6.3 章节, “myisamchk — MyISAM Table-Maintenance Utility”.

如果表频繁损坏, 则应尝试确定发生此事件的原因。最重要的是要知道该表是否因服务器崩溃而损坏。通过在错误日志中查找最近 restarted mysqld 消息, 进行验证。如果有这样的消息, 表损坏很可能是服务器崩溃导致的。否则, 损坏可能发生在正常操作期间。这是个 bug。您应该尝试创建一个可重现的测试用例来演示问题。

参阅 B.5.3.3 章节 “What to Do If MySQL Keeps Crashing”, 28.5 章节 “Debugging and Porting MySQL”.

未正确关闭表的问题

每个 MyISAM 索引文件 (.MYI文件) 在头部有一个计数器, 可用于检查表是否已正确关闭。如果从CHECK TABLE或 myisamchk 得到以下警告, 则表示此计数器已经不同步了:

clients are using or haven't closed the table properly

此警告不一定意味着该表已损坏, 但至少应检查表。

该计数器的工作原理如下:

1. 第一次在 MySQL 中更新表时, 索引文件头部中的计数器将递增。

2. 在未来的更新中, 计数器不会更改。

3. 当表的最后实例被关闭（因为一个操作FLUSH TABLE或因为在表缓冲区中没有空间）之时，若表已经在任何点被更新，则计数器减一。

4. 当修复表, 或检查表时它被发现是好的, 计数器被重置为零。

5. 为了避免与其它可能检查表的进程进行交互的问题，若计数器为零，在关闭时计数器不减一。

换言之, 只有在下列情况下, 计数器才会变得不正确:

1. 复制 MyISAM 表, 不先 LOCK TABLES 和 FLUSH TABLES

2.  MySQL在一次更新和最后关闭之间崩溃（注意，表可能依然完好，因为MySQL总是在每个语句之间为每件事发出写操作）

3. 一个表被mysqld使用的同时，又被myisamchk --recover或myisamchk --update-state修改。

4. 多个 mysqld 服务器正在使用该表, 一台服务器在其被另一台服务器使用时在表上执行了修复表或检查表。在这个架构中, 使用CHECK TABLE是安全的, 尽管可能从其他服务器得到警告。但是, 应避免REPAIR TABLE, 因为当一个服务器用一个新的数据文件替代旧的之时，其他服务器不知道这一点。

总的来说，在多服务器之间共用一个数据目录是一个坏主意。

参阅 5.6 章节 “Running Multiple MySQL Instances on One Machine” 获得更多研讨。