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

    存储引擎：用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等。存储引擎包括：myisam ,innodb ,bdb ,memory ,merge ,example ,ndb cluster , archive, csv,blackhole ,federated等，其中innodb和dbd提供事务安全，其他存储引擎都是非事务安全表。

    查看当前的存储引擎：

mysql> show create table t8;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t8    | CREATE TABLE `t8` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `age` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    查看当前数据库支持的存储引擎

方法一，

mysql> show engines \g
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

方法二，

mysql> show variables like 'have%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| have_compress          | YES      |
| have_crypt             | YES      |
| have_dynamic_loading   | YES      |
| have_geometry          | YES      |
| have_openssl           | DISABLED |
| have_profiling         | YES      |
| have_query_cache       | YES      |
| have_rtree_keys        | YES      |
| have_ssl               | DISABLED |
| have_statement_timeout | YES      |
| have_symlink           | YES      |
+------------------------+----------+
11 rows in set (0.01 sec)

    在创建表的时候，可以通过增加engine关键字来设置新建表的存储引擎：

mysql> create table t9(id bigint(20) primary key,name varchar(63))engine=innodb default charset=gbk;

mysql> show create table t9;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| t9    | CREATE TABLE `t9` (
  `id` bigint(20) NOT NULL,
  `name` varchar(63) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

   修改表的存储引擎：

mysql> alter table t9 engine=myisam;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t9;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| t9    | CREATE TABLE `t9` (
  `id` bigint(20) NOT NULL,
  `name` varchar(63) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    
    二， 各种存储引擎的特性

在平时我们最常用的4种存储引擎有：

     

mysql存储引擎对比

特点	myisam	innodb	memory	merge
存储限制	有	64tb	有	没有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁
b树索引	支持	支持	支持	支持
哈希索引			支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	n/a	低
内存使用	低	高	中等	低
批量插入速度	高	低	高	高
支持外键		支持