mysql storage type_MySQL的存储引擎概述

MySQL中有一个存储引擎的概念，其决定了如何存储和检索数据、是否支持事务等，用户可以针对不同的需求选择合适的存储引擎。插件式存储引擎是MySQL数据库最重要的特征之一，MySQL不仅默认内置了多种存储引擎，用户还可以制作自己的存储引擎。

MySQL 5.7支持的存储引擎包括InnoDB、MyISAM、Memory、CSV、Archive、Blackhole、NDB、Merge、Federated、Example等。

root@database-one 23:24: [(none)]> select version();

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

| version() |

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

| 5.7.16-log |

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

1 row in set (0.00 sec)

root@database-one 23:24: [(none)]> show engines;

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

| Engine | Support | Comment | Transactions | XA | Savepoints |

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

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

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

9 rows in set (0.00 sec)

上面可以看到，默认的存储引擎是InnoDB。其实MySQL 5.5之前默认存储引擎是MyISAM，之后改为了InnoDB。

创建表的时候，可以通过ENGINE关键字设置表的存储引擎，如果不指定，就会使用默认的存储引擎。

root@database-one 23:40: [gftest]> CREATE TABLE t1 (i INT);

Query OK, 0 rows affected (0.00 sec)

root@database-one 23:40: [gftest]> show create table t1 \G

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

Table: t1

Create Table: CREATE TABLE `t1` (

`i` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

root@database-one 23:40: [gftest]> CREATE TABLE t2 (i INT not null) ENGINE = CSV;

Query OK, 0 rows affected (0.01 sec)

root@database-one 23:40: [gftest]> show create table t2 \G

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

Table: t2

Create Table: CREATE TABLE `t2` (

`i` int(11) NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

root@database-one 23:41: [gftest]> CREATE TABLE t3 (i INT) ENGINE = MEMORY;

Query OK, 0 rows affected (0.00 sec)

root@database-one 23:41: [gftest]> show create table t3 \G

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

Table: t3

Create Table: CREATE TABLE `t3` (

`i` int(11) DEFAULT NULL

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

对于已经存在的表，也可以使用alter table语句修改存储引擎。

root@database-one 23:43: [gftest]> alter table t2 engine=innodb;

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@database-one 23:43: [gftest]> show create table t2 \G

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

Table: t2

Create Table: CREATE TABLE `t2` (

`i` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

那么这些存储引擎到底有什么区别呢？先看看官方文档中对五种最常用存储引擎的特性对比。

Feature

MyISAM

Memory

InnoDB

Archive

NDB

B-tree indexes

Yes

Yes

Yes

No

No

Backup/point-in-time recovery (note 1)

Yes

Yes

Yes

Yes

Yes

Cluster database support

No

No

No

No

Yes

Clustered indexes

No

No

Yes

No

No

Compressed data

Yes (note 2)

No

Yes

Yes

No

Data caches

No

N/A

Yes

No

Yes

Encrypted data

Yes (note 3)

Yes (note 3)

Yes (note 4)

Yes (note 3)

Yes (note 3)

Foreign key support

No

No

Yes

No

Yes (note 5)

Full-text search indexes

Yes

No

Yes (note 6)

No

No

Geospatial data type support

Yes

No

Yes

Yes

Yes

Geospatial indexing support

Yes

No

Yes (note 7)

No

No

Hash indexes

No

Yes

No (note 8)

No

Yes

Index caches

Yes

N/A

Yes

No

Yes

Locking granularity

Table

Table

Row

Row

Row

MVCC

No

No

Yes

No

No

Replication support (note 1)

Yes

Limited (note 9)

Yes

Yes

Yes

Storage limits

256TB

RAM

64TB

None

384EB

T-tree indexes

No

No

No

No

Yes

Transactions

No

No

Yes

No

Yes

Update statistics for data dictionary

Yes

Yes

Yes

Yes

Yes

Notes:

Implemented in the server, rather than in the storage engine.

Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

Implemented in the server via encryption functions.

Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.

Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.

InnoDB support for geospatial indexing is available in MySQL 5.7 and later.

InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

See the discussion later in this section.

接下来我们将花几天时间，对常用的存储引擎进行介绍和实测。