第2篇 开发篇

第7章 表类型（存储引擎）的选择

和大多数数据库不同，MySQL中有一个

存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。本章将详细介绍存储引擎的概念、分类以及实际应用中的选择原则。

7.1 MySQL存储引擎概述

插件式存储引擎是MySQL数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制性。

MySQL 5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

默认情况下，创建新表不指定表的存储引擎，则新表是默认存储引擎的，如果需要修改默认的存储引擎，则可以在参数文件中设置default-table-type。查看当前的默认存储引擎，可以使用以下命令：

mysql> show variables like 'table_type';

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

| Variable_name | Value |

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

| table_type | MyISAM |

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

1 row in set (0.00 sec)

可以通过下面两种方法查询当前数据库支持的存储引擎，第一种方法为：

mysql> SHOW ENGINES \G

*************************** 1. row ***************************

Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

Transactions: NO

XA: NO

Savepoints: NO

*************************** 2. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 3. row ***************************

Engine: MRG_MYISAM

Support: YES

Comment: Collection of identical MyISAM tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 4. row ***************************

Engine: InnoDB

Support: YES

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

*************************** 5. row ***************************

Engine: CSV

Support: YES

Comment: CSV storage engine

Transactions: NO

XA: NO

Savepoints: NO

5 rows in set (0.00 sec)

或者采用第二种方法：

mysql> SHOW VARIABLES LIKE 'have%';

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

| Variable_name | Value |

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

| have_archive | NO |

| have_bdb | NO |

| have_blackhole_engine | NO |

| have_compress | YES |

| have_crypt | YES |

| have_csv | YES |

| have_dlopen | YES |

| have_example_engine | NO |

| have_federated_engine | NO |

| have_geometry | YES |

| have_innodb | YES |

| have_ndbcluster | NO |

| have_openssl | NO |

| have_partitioning | YES |

| have_query_cache | YES |

| have_row_based_replication | YES |

| have_rtree_keys | YES |

| have_symlink | YES |

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

18 rows in set (0.00 sec)

以上两种方法都可以用来查看当前支持哪些存储引擎，其中Value显示为“DISABLED”的记录表示支持该存储引擎，但是数据库启动的时候被禁用。

在创建新表的时候，可以通过增加ENGINE关键字设置新建表的存储引擎，例如，在下面的例子中，表ai就是MyISAM存储引擎的，而country表就是InnoDB存储引擎的：

CREATE TABLE ai (

i bigint(20) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (i)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

CREATE TABLE country (

country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

country VARCHAR(50) NOT NULL,

last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (country_id)

)ENGINE=InnoDB DEFAULT CHARSET=gbk;

也可以使用ALTER TABLE语句，将一个已经存在的表修改成其他的存储引擎。下面的例子介绍了如何将表ai从MyISAM存储引擎修改成InnoDB存储引擎：

mysql> alter table ai engine = innodb;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table ai \G

*************************** 1. row ***************************

Table: ai

Create Table: CREATE TABLE `ai` (

`i` bigint(20) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`i`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

这样修改后，ai表成为InnoDB存储引擎，可以使用InnoDB存储引擎的相关特性。

7.2 各种存储引擎的特性

下面重点介绍几种常用的存储引擎,并对比各个存储引擎之间的区别，以帮助读者理解不同存储引擎的使用方式。

表7-1 常用存储引擎的对比

下面将重点介绍最常使用的4种存储引擎：MyISAM、InnoDB、MEMORY和MERGE。NDB存储引擎会在第33章MySQL CLUSTER中详细介绍，这里不再赘述。

7.2.1 MyISAM

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

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

l .frm（存储表定义）；

l .MYD（MYData，存储数据）；

l .MYI （MYIndex，存储索引）。

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

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

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

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

l 静态（固定长度）表；

l 动态表；

l 压缩表。

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

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

mysql> create table Myisam_char (name char(10)) engine=myisam;

Query OK, 0 rows affected (0.04 sec)

mysql> insert into Myisam_char values('abcde'),('abcde '),(' abcde'),(' abcde ');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select name,length(name) from Myisam_char;

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

| name | length(name) |

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

| abcde | 5 |

| abcde | 5 |

| abcde | 7 |

| abcde | 7 |

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

4 rows in set (0.00 sec)

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

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

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

7.2.2 InnoDB

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

下面将重点介绍InnoDB存储引擎的表使用过程中不同于其他存储引擎的特点。

1、自动增长列

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

mysql> create table autoincre_demo

-> (i smallint not null auto_increment,

-> name varchar(10),primary key(i)

-> )engine=innodb;

Query OK, 0 rows affected (0.13 sec)

mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from autoincre_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.04 sec)

mysql> select LAST_INSERT_ID();

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

| LAST_INSERT_ID() |

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

| 2 |

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

1 row in set (0.00 sec)

mysql> insert into autoincre_demo(name) values('5'),('6'),('7');

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select LAST_INSERT_ID();

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

| LAST_INSERT_ID() |

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

| 5 |

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

1 row in set (0.00 sec)

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

mysql> create table autoincre_demo

-> (d1 smallint not null auto_increment,

-> d2 smallint not null,

-> name varchar(10),

-> index(d2,d1)

-> )engine=myisam;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into autoincre_demo(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3') , (4,'4');

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from autoincre_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.00 sec)

2、外键约束

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

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

CREATE TABLE country (

country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

country VARCHAR(50) NOT NULL,

last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (country_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE city (

city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

city VARCHAR(50) NOT NULL,

country_id SMALLINT UNSIGNED NOT NULL,

last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (city_id),

KEY idx_fk_country_id (country_id),

CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包RESTRICT、CASCADE、SET NULL和NO ACTION。其中RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则表示父表在更新或者删除的时候，子表的对应字段被SET NULL。选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据的丢失。

例如对上面创建的两个表，子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新：

mysql> select * from country where country_id = 1;

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

| country_id | country | last_update |

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

| 1 | Afghanistan | 2006-02-15 04:44:00 |

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

1 row in set (0.00 sec)

mysql> select * from city where country_id = 1;

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

| city_id | city | country_id | last_update |

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

| 251 | Kabul | 1 | 2006-02-15 04:45:25 |

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

1 row in set (0.00 sec)

mysql> delete from country where country_id=1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sakila/city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

mysql> update country set country_id = 10000 where country_id = 1;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from country where country = 'Afghanistan';

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

| country_id | country | last_update |

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

| 10000 | Afghanistan | 2007-07-17 09:45:23 |

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

1 row in set (0.00 sec)

mysql> select * from city where city_id = 251;

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

| city_id | city | country_id | last_update |

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

| 251 | Kabul | 10000 | 2006-02-15 04:45:25 |

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

1 row in set (0.00 sec)

当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。

在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA和ALTER TABLE操作的时候，可以通过暂时关闭外键约束来加快处理的速度，关闭的命令是“SET FOREIGN_KEY_CHECKS = 0;”，执行完成之后，通过执行“SET FOREIGN_KEY_CHECKS = 1;”语句改回原状态。

对于InnoDB类型的表，外键的信息通过使用show create table或者show table status命令都可以显示。

mysql> show table status like 'city' \G

*************************** 1. row ***************************

Name: city

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 427

Avg_row_length: 115

Data_length: 49152

Max_data_length: 0

Index_length: 16384

Data_free: 0

Auto_increment: 601

Create_time: 2007-07-17 09:45:33

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment: InnoDB free: 0 kB; (`country_id`) REFER `sakila/country`(`country_id`) ON UPDATE

1 row in set (0.00 sec)

3、存储方式

InnoDB存储表和索引有以下两种方式。

l 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir 和innodb_data_file_path定义的表空间中，可以是多个文件。

l 使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

要使用多表空间的存储方式，需要设置参数innodb_file_per_table，并重新启动服务后才可以生效，对于新建的表按照多表空间的方式创建，已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式，则新建表会在共享表空间中创建，但已有的多表空间的表仍然保存原来的访问方式。所以多表空间的参数生效后，只对新建的表生效。

多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等参数。

对于使用多表空间特性的表，可以比较方便地进行单表备份和恢复操作，但是直接复制.ibd文件是不行的，因为没有共享表空间的数据字典信息，直接复制的.ibd文件和.frm文件恢复时是不能被正确识别的，但可以通过以下命令：

ALTER TABLE tbl_name DISCARD TABLESPACE;

ALTER TABLE tbl_name IMPORT TABLESPACE;

将备份恢复到数据库中，但是这样的单表备份，只能恢复到表原来在的数据库中，而不能恢复到其他的数据库中。如果要将单表恢复到目标数据库，则需要通过mysqldump和mysqlimport来实现。

注意：即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和未作日志放在这个文件中。

7.2.3 MEMORY

MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

下面例子创建了一个MEMORY的表，并从city表获得记录：

mysql> CREATE TABLE tab_memory ENGINE=MEMORY

-> SELECT city_id,city,country_id

-> FROM city GROUP BY city_id;

Query OK, 600 rows affected (0.06 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> select count(*) from tab_memory;

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

| count(*) |

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

| 600 |

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

1 row in set (0.00 sec)

mysql> show table status like 'tab_memory' \G

*************************** 1. row ***************************

Name: tab_memory

Engine: MEMORY

Version: 10

Row_format: Fixed

Rows: 600

Avg_row_length: 155

Data_length: 127040

Max_data_length: 16252835

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

给MEMORY表创建索引的时候，可以指定使用HASH索引还是BTREE索引：

mysql> create index mem_hash USING HASH on tab_memory (city_id) ;

Query OK, 600 rows affected (0.04 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM tab_memory \G

*************************** 1. row ***************************

Table: tab_memory

Non_unique: 1

Key_name: mem_hash

Seq_in_index: 1

Column_name: city_id

Collation: NULL

Cardinality: 300

Sub_part: NULL

Packed: NULL

Null:

Index_type: HASH

Comment:

1 row in set (0.01 sec)

mysql> drop index mem_hash on tab_memory;

Query OK, 600 rows affected (0.04 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> create index mem_hash USING BTREE on tab_memory (city_id) ;

Query OK, 600 rows affected (0.03 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM tab_memory \G

*************************** 1. row ***************************

Table: tab_memory

Non_unique: 1

Key_name: mem_hash

Seq_in_index: 1

Column_name: city_id

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

1 row in set (0.00 sec)

在启动MySQL服务的时候使用--init-file选项，把INSERT INTO ... SELECT或LOAD DATA INFILE这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。

服务器需要足够内存来维持所有在同一时间使用的MEMORY表，当不再需要MEMORY表的内容之时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE操作）。

每个MEMORY表中可以放置的数据量的大小，受到max_heap_table_size系统变量的约束，这个系统变量的初始值是16MB，可以按照需要加大。此外，在定义MEMORY表的时候，可以通过MAX_ROWS子句指定表的最大行数。

MEMORY类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对MEMORY存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

7.2.4 MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM表进行的。对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作，这个操作只是删除MERGE的定义，对内部的表没有任何的影响。

MERGE表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm文件存储表定义，另一个.MRG文件包含组合表的信息，包括MERGE表由哪些表组成、插入新的数据时的依据。可以通过修改.MRG文件来修改MERGE表，但是修改后要通过FLUSH TABLES刷新。

下面是一个创建和使用MERGE表的例子。

（1）创建3个测试表payment_2006、payment_2007和payment_all，其中payment_all是前两个表的MERGE表：

mysql> create table payment_2006(

-> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> KEY idx_fk_country_id (country_id)

-> )engine=myisam;

Query OK, 0 rows affected (0.03 sec)

mysql> create table payment_2007(

-> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> KEY idx_fk_country_id (country_id)

-> )engine=myisam;

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE payment_all(

-> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> INDEX(country_id)

-> )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

Query OK, 0 rows affected (0.04 sec)

（2）分别向payment_2006和payment_2007表中插入测试数据：

mysql> insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

（3）分别查看这3个表中的记录：

mysql> select * from payment_2006;

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

| country_id | payment_date | amount |

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

| 1 | 2006-05-01 00:00:00 | 100000.00 |

| 2 | 2006-08-15 00:00:00 | 150000.00 |

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

2 rows in set (0.00 sec)

mysql> select * from payment_2007;

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

| country_id | payment_date | amount |

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

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

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

2 rows in set (0.00 sec)

mysql> select * from payment_all;

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

| country_id | payment_date | amount |

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

| 1 | 2006-05-01 00:00:00 | 100000.00 |

| 2 | 2006-08-15 00:00:00 | 150000.00 |

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

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

4 rows in set (0.00 sec)

可以发现，payment_all表中的数据是payment_2006和payment_2007表的记录合并后的结果集。

下面向MERGE表插入一条记录，由于MERGE表的定义是INSERT_METHOD=LAST，就会向最后一个表中插入记录，所以虽然这里插入的记录是2006年的，但仍然会写到payment_2007表中。

mysql> insert into payment_all values(3,'2006-03-31',112200);

Query OK, 1 row affected (0.00 sec)

mysql> select * from payment_all;

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

| country_id | payment_date | amount |

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

| 1 | 2006-05-01 00:00:00 | 100000.00 |

| 2 | 2006-08-15 00:00:00 | 150000.00 |

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

| 3 | 2006-03-31 00:00:00 | 112200.00 |

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

5 rows in set (0.00 sec)

mysql> select * from payment_2007;

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

| country_id | payment_date | amount |

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

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

| 3 | 2006-03-31 00:00:00 | 112200.00 |

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

3 rows in set (0.00 sec)

这也是MERGE表和分区表的区别，MERGE表并不能智能地将记录写到对应的表中，而分区表是可以的（分区功能在5.1版中正式推出）。通常我们使用MERGE表来透明地对多个表进行查询和更新操作，而对这种按照时间记录的操作日志表则可以透明地进行插入操作。

7.3 如何选择合适的存储引擎

在选择存储引擎时，应根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

下面是常用存储引擎的适用环境。

¡ MyISAM：默认的MySQL插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

¡ InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。

¡ MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY的缺陷是对表的大小有限制，太大的表无法CACHE在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

¡ MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

注意：以上只是我们按照实施经验提出的关于存储引擎选择的一些建议，但是不同应用的特点是千差万别的，选择使用哪种存储引擎才是最佳方案也不是绝对的，这需要根据用户各自的应用进行测试，从而得到最适合自己的结果。

7.4 小结

本章重点介绍了MySQL提供的几种主要的存储引擎及其使用、特性，以及如何根据应用的需要选择合适的存储引擎。这些提供的存储引擎有各自的优势和适用的场合，正确地选择存储引擎对改善应用的效率可以起到事半功倍的效果。

正确地选择了存储引擎之后，还需要正确选择表中的数据类型，下一章我们将详细介绍如何选择合适的数据类型。