mysql 存储引擎的特性_MySQL--各存储引擎的特性

各种存储引擎的特性

特点

MyISAM

InnoDB

MEMORY

MERGE

NDB

存储限制

有

64TB

有

没有

有

事务安全

支持

锁机制

表锁

行锁

表锁

表锁

行锁

B树索引

支持

支持

支持

支持

支持

哈希索引

支持

支持

全文索引

支持

集群索引

支持

数据缓存

支持

支持

支持

索引缓存

支持

支持

支持

支持

支持

数据可压缩

支持

使用空间

低

高

N/A

低

低

内存使用

低

高

中等

低

高

批量插入的速度

高

低

高

高

高

支持外键

支持

MyISAM

MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访 问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用 这个引擎来创建表。

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：

.frm(存储表定义)；

.MYD(MYData，存储数据)；

.MYI (MYIndex，存储索引)。

数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。

要指定索引文件和数据文件的路径，需要在创建表的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定，也就是说不同 MyISAM 表的索引文件和数据文件可以放置到不同的路 径下。文件路径需要是绝对路径，并且具有访问权限。

MyISAM 类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会 提示需要修复或者访问后返回错误的结果。MyISAM 类型的表提供修复的工具，可以用 CHECK TABLE 语句来检查 MyISAM 表的健康，并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。 表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。

MyISAM 的表又支持 3 种不同的存储格式，分别是：

静态(固定长度)表；

动态表；

压缩表。

其中，静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是 固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是 占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格，但 是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。

如果需要保存的内容后面本来就带有空格，那么在返 回结果的时候也会被去掉，开发人员在编写程序的时候需要特别注意，因为静态表是默认的 存储格式，开发人员可能并没有意识到这一点，从而丢失了尾部的空格。以下例子演示了插 入的记录包含空格时处理的情况：

mysql> CREATE TABLE Myisam_char(name CHAR(10)) ENGINE=MyISAM;

Query OK,0 rows affected (0.00sec)

mysql> INSERT INTO Myisam_char VALUES('abcde'),('abcde'),('abcde'),('abcde');

Query OK,4 rows affected (0.00sec)

Records:4 Duplicates: 0 Warnings: 0mysql> SELECT name,LENGTH(name) FROMMyisam_char;+---------+--------------+

| name | LENGTH(name) |

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

| abcde | 5 |

| abcde | 5 |

| abcde | 7 |

| abcde | 7 |

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

4 rows in set (0.00 sec)

从上面的例子可以看出，插入记录后面的空格都被去掉了，前面的空格保留。

动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但 是频繁地更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命 令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表由 myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

InnoDB

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

1、自动增长列

InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将 是自动增长后的值。下面定义新表 autoincre_demo，其中列 i 使用自动增长列，对该表插入 记录，然后查看自动增长列的处理情况，可以发现插入 0 或者空实际插入的都将是自动增长 后的值：

mysql> CREATE TABLEautoincre_demo(-> i SMALLINT NOT NULLAUTO_INCREMENT,-> name VARCHAR(10),PRIMARY KEY(i))-> ENGINE=InnoDB;

Query OK,0 rows affected (0.01sec)

mysql> INSERT INTO autoincre_demo VALUES(1,'1'),(0,'2'),(null,'3');

Query OK,3 rows affected (0.01sec)

Records:3 Duplicates: 0 Warnings: 0mysql> SELECT * FROMautoincre_demo;+---+------+

| i | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

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

3 rows in set (0.00 sec)

可以通过“ALTER TABLE *** AUTO_INCREMENT = n;”语句强制设置自动增长列的初识值， 默认从 1 开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启 动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置。

可以使用 LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条 记录，那么返回的是第一条记录使用的自动增长值。下面的例子演示了使用 LAST_INSERT_ID() 的情况：

mysql> INSERT INTO autoincre_demo VALUES(4,'4');

Query OK,1 row affected (0.01sec)

mysql> SELECTLAST_INSERT_ID();+------------------+

| LAST_INSERT_ID() |

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

| 2 |

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

1 row in set (0.00sec)

mysql> INSERT INTO autoincre_demo(name) VALUES('5'),('6'),('7');

Query OK,3 rows affected (0.01sec)

Records:3 Duplicates: 0 Warnings: 0mysql> SELECTLAST_INSERT_ID();+------------------+

| LAST_INSERT_ID() |

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

| 5 |

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

1 row in set (0.01 sec)

对于 InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一 列，但是对于 MyISAM 表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增 长列是按照组合索引的前面几列进行排序后递增的。例如，创建一个新的 MyISAM 类型的表 autoincre_demo，自动增长列 d1 作为组合索引的第二列，对该表插入一些记录后，可以发 现自动增长列是按照组合索引的第一列 d2 进行排序后递增的：

mysql> CREATE DATABASEtest2;

Query OK,1 row affected (0.01sec)

mysql> USEtest2;Databasechanged

mysql> CREATE TABLEautoincre_demo(-> d1 SMALLINT NOT NULLAUTO_INCREMENT,-> d2 SMALLINT NOT NULL,-> name VARCHAR(10),-> INDEX(d2,d1))-> ENGINE=MyISAM;

Query OK,0 rows affected (0.01sec)

mysql> INSERT INTOautoincre_demo(d2,name)-> VALUES

-> (2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');

Query OK,6 rows affected (0.00sec)

Records:6 Duplicates: 0 Warnings: 0mysql> SELECT * FROMautoincre_demo;+----+----+------+

| d1 | d2 | name |

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

| 1 | 2 | 2 |

| 1 | 3 | 3 |

| 1 | 4 | 4 |

| 2 | 2 | 2 |

| 2 | 3 | 3 |

| 2 | 4 | 4 |

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

6 rows in set (0.01 sec)

2、外键约束

MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的 索引，子表在创建外键的时候也会自动创建对应的索引。

下面是样例数据库中的两个表，country 表是父表，country_id 为主键索引，city 表是子 表，country_id 字段对 country 表的 country_id 有外键。