MySQL存储引擎概述

7.表类型 (存储引擎)的选择

7.1 MySQL 存储引擎概述

默认的存储引擎

创建新表不指定表的存储引擎，则新表是默认存储引擎的

修改默认存储引擎：

可修改参数文件中设置 default_table_type

查看当前的默认存储引擎，可以使用以下命令：

mysql> show variables like 'table_type';

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

| Variable_name | Value |

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

| table_type | MyISAM |

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

方法一：

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

.......

第二种方法：

mysql> SHOW VARIABLES LIKE 'have%';

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

| Variable_name | Value |

Linux公社 www.linuxidc.com

109

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

| 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 |

..........

，其中 Value 显示为“DISABLED”的记录表示支持该存储引擎，但是数据库启动的时候被禁用。

创建表时设置引擎

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

CREATE TABLE ai (

i bigint(20) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (i)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

修改已有表引擎

使用 ALTER TABLE 语句，将一个已经存在的表修改成其他的存储引擎

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)

7.2 各种存储引擎的特性

MyISAM

存储限制：有

事务安全：不支持

锁机制：表锁

B树索引：支持

哈希索引：不支持

全文索引：支持

集群索引：不支持

数据缓存：不支持

索引缓存：支持

数据可压缩：支持

空间使用：低

内存使用：低

批量插入数据的速度：高

支持外键：不支持

特点：

MyISAM 不支持事务，不支持外键。

访问速度快,对事务完整性没有要求，或者以INSERT 或者SELECT 为主的应用可以使用该引擎。

每个MyISAM 存储三个文件：

.frm(存储表定义)

.MYD(MYDate, 存储数据)

.MYI(MYIndex,存储索引)

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

指定数据文件和索引文件的目录，需要在创建表时，可以通过DATA_DIRECTORY和INDEX_DIRECTORY来设定。文件路径需要时绝对路径，并有访问权限。

MyISAM表可能损坏，可以通过 CHECK TABLE检查表健康状态，REPIRE TATBLE 修复，详细步骤31章

MyISAM 支持的三种不同存储格式

静态(固定长度)表

动态表

压缩表

静态表是默认存储格式，静态表中的字段都是非变长度字段，这样每个记录都是固定长度。

优点: 存储迅速，容易缓存，出现故障容易恢复。

缺点：占用空间比动态表多。

静态表存储时会按照列的宽度补足空格，应用访问是并不会得到这些空格，这些空格在返回应用前已经去掉。当存储时尾部真的需要空格时 也会被去掉。

动态表中包含变长字段，记录不是固定长度的。

占用空间相对较少，但是频繁的更新个删除记录会产生碎片，需要定期执行OPTIMIZE TABLE 或者 myisamchk -r命令来改善性能。并且在出现故障时恢复相对较困难。

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

InnoDB

存储限制：64TB

事务安全：支持

锁机制：行锁

B树索引：支持

哈希索引：NO

全文索引：NO

集群索引：支持

数据缓存：支持

索引缓存：支持

数据可压缩：NO

空间使用：高

内存使用：高

批量插入数据的速度：低

支持外键：支持

特点 ：

具有提交、回滚、和崩溃恢复能力的事务安全。

但是相对于MyISAM ，InnoDB写的效率差一些，并且会占用更多的磁盘空间以保存数据和索引。

自动增长列：

InnoDB的自动增长列可以手工插入，但是插入的值如果是0或者空时 将自动增长。可以通过ALTER TABLE tablename AUTO_INCREMENT = n 来设置自动增长的初始值。但是该值是保留在内存中的。如果重新启动 数据库，该值将丢失。

可以使用LAST_INSERT_ID(),来查询当前线程最后插入使用的值。如果插入多条，则返回第一条记录使用的值。

对于InnoDB的自增列必须是索引，如果是组合索引，必须是组合索引的第一列。但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照前几列进行排序后自增的。

外键约束

只有InnoDB支持外键约束。创建外键时，父表必须有对应的索引，字表在创建外建时也会创建对应的索引

修改：

ALTER TABLE tablename ADD CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 关联表名(关联字段名);

创建：

CREATE TABEL table_name (

.....,

CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 关联表名(关联字段名)

) ENGINE = InnoDB;

创建外键时，可以指定在删除 更新 父表时对子表的操作。

RESTRICT

NO ACTION

CASCADE

SET NULL

例子:

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;

在导入多表数据时，可以暂时关闭外键检查 SET FOREIGN_KEY_CHECK = 0 :导入完成再开启 SET FOREIGN_KEY_CHECK = 1;

存储方式

使用共享表空间存储

使用多表空间存储

MEMEORY

存储限制：有

事务安全：NO

锁机制：表锁

B树索引：支持

哈希索引：支持

全文索引：NO

集群索引：NO

数据缓存：支持

索引缓存：支持

数据可压缩：NO

空间使用：N/A

内存使用：中等

批量插入数据的速度：高

支持外键：NO

特点：memory 存储引擎使用存在于内存中的内容来创建表，每个memory实际只对应一个磁盘文件，格式是.frm。 memory 表访问速度非常快，因为他数据存储在内存中的，并默认使用HASH索引。 但是服务一旦关闭 表中的数据就会丢失。

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

CREATE INDEX mem_hash USING HASH ON tablename(col_name)

持久数据:

在启动mysql时使用 --init-file选项，把INSERT INTO ....SELECT 或者LOAD DATA INFILE 这样的语句放入文件中，这样就可以将持久的数据载入表。

数据表的大小：

每个memory 表可以放置的数据大小，收到max_heap_table_size限制，系统默认是16Mb，可以根据需要加大。此外定义表的时候可以设置MAX_ROWS子句指定表的最大长度。

适用环境:

memory主要适用那些内容变化不频繁，或者作为那么统计操作的中间结果表。

要注意数据并没有写入硬盘，重启时数据会丢失。

MERGE [m?:rd?]

存储限制：没有

事务安全：NO

锁机制：表锁

B树索引：支持

哈希索引：NO

全文索引：NO

集群索引：NO

数据缓存：NO

索引缓存：支持

数据可压缩：NO

空间使用：低

内存使用：低

批量插入数据的速度：高

支持外键：NO

是什么

MERGE 存储引擎是一组MyISAM表的组合，这些MyISAM表的结构必须完全相同，MERGE表本身并没有数据，对MERGE标的可以进行查询、更新、删除操作，这些操作实际是对MyISAM标的操作。

定义MERGE表

语法

CRATE TABLE tablename (

....

)ENGINE = MERGE UNION(tablename1,tablename2, ,,) INSERT_METHOD=LAST

范例

(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)

插入操作：

对MERGE表进行插入操作，是通过INSERT_METHOD 定义插入的表，有三个值：LAST 插入到最后一张表，FIRST 插入到第一张表，NO 不允许插入

NDB

存储限制：有

事务安全：NO

锁机制：表锁

B树索引：支持

哈希索引：支持

全文索引：NO

集群索引：NO

数据缓存：支持

索引缓存：支持

数据可压缩：NO

空间使用：N/A

内存使用：中等

批量插入数据的速度：高

支持外键：NO

第三方存储引擎： TokuDB

特点：高写 高压缩性能，

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

MyISAM：

如果应用是读和插入操作为主，只有很少的更新和删除操作。对事务的完整性和并发要求不高，MyISAM 非常合适

InnoDB

用于事务处理程序，支持外键。如果应用对事务完整性有较高的要求，在并发的条件下要保证数据的一致性，数据除了查询和插入操作外还有很多的更新个删除操作，那么InnoDB比较合适，

MEMORY

将所有的数据保存在RAM中，在需要快速定位数据和其他类似的数据环境下，可提供快速访问，

缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次要确保表的可恢复。

用户更新不频繁的小表，用户快速得到访问结果。

MERGE：

用于将一系列等同的表逻辑的组合在一起，

MERGE表的优点在于可以突破单个MyISAM的大小限制，并通过将不同的表分布在不同的磁盘上，可以有效的改善MERGE表的访问效率。

对于数据仓库VLDB非常合适。