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 存储引擎的相关特性。

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

						常用存储引擎的对比

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B 树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持