MySQL的存储引擎概述

2020年2月27日
| 没有评论
摘要：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:

  1. Implemented in the server, rather than in the storage engine.
  2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.
  3. Implemented in the server via encryption functions.
  4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.
  5. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.
  6. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.
  7. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.
  8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.
  9. See the discussion later in this section.

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

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: , , , , , , , , , , ,