Mysql-存储引擎

搬运自IT码客

简述

MySQL服务器中对数据的存储及数据的操作行为都封装到了一个叫做存储引擎的模块中。我们知道表是由一行一行的记录组成的，这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎的表对数据的存储结构及存储算法也各自不同。

MySQL提供了多种存储引擎，每种存储引擎都有其各自的特点，我们可以具体的需要选择不同的存储引擎。同时MySQL还预定义了存储引擎接口，如果你对其提供的存储引擎性能或功能不够满意，也可以修改其源码，甚至自定义自己的存储引擎。

MySQL 支持的存储引擎

InnoDB 存储引擎

InnoDB 是 MySQL 的事务安全（兼容ACID）的存储引擎。它的特点是支持行级锁、支持外键、支持类似于Oracle的非锁定读，即读取操作不会加锁。 从 MySQL 5.5.8 开始，InnoDB 存储引擎作为默认的存储引擎。

InnoDB 通过使用多版本并发控制（MVCC）来获得高并发性能，并且实现了4种隔离级别，默认是REPEATABLE级别。同时，还是用了一种 next-key locking的策略来避免幻读的产生。另外，InnoDB 存储引擎还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。

对于表中数据的存储，InnoDB 存储引擎采用了聚集的方式，每张表的存储都是按照主键顺序进行存放。如果没有显式的定义表的主键，InnoDB 存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

MyISAM 存储引擎

MyISAM 表级锁设计限制了读/写工作负载中的性能，因此它通常用于Web和数据仓库配置中的只读或只读工作负载中。

MyISAM 存储引擎不支持事务、支持表级锁、支持全文检索，主要面向OLAP数据库的应用。在 。MySQL 5.5.8 版本之前 MyISAM 是默认的存储引擎（除Windows版本）。

MyISAM 存储引擎的缓冲池只缓存索引文件，不缓存数据文件，数据文件的缓存由操作系统本身来完成。

Memory 存储引擎

Memory 存储引擎，之前也叫HEAP存储引擎。它将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中进行快速访问。如果数据库重启或者崩溃，表中的数据就会丢失。因此，它非常适合用于存储临时数据，以及数据仓库中的维度表。

Memory 存储引擎默认使用哈希索引，而不是我们所熟悉的B+树索引。

Memory 存储引擎速度非常快，但是在使用上还是有一定的限制。比如，只支持表级锁，并发性能较差；不支持TEXT和BLOB类型。更重要的是，存储变长字段（varchar）时，仍然按照定长（char）存储，因此会很浪费内存。

Archive 存储引擎

Archive 存储引擎只支持INSERT和SELECT操作，从MySQL 5.1开始支持索引。Archive 存储引擎使用zlib算法将数据行压缩后存储，压缩比一半是1：10。Archive 存储引擎非常适合存储归档数据，比如日志信息。Archive 存储引擎使用行锁来实现高并发的插入操作，但是它本身并不是事务安全的存储引擎，它的设计目标是提供高速的插入和压缩功能。

Merge 存储引擎

Merge 存储引擎的设计目标是用来取代原有的MyISAM存储引擎，因此它可以看作是MyISAM存储引擎的后续版本。

MyISAM存储引擎的特点：

• 支持缓存数据和索引文件
• 支持行级锁
• 提供了MVCC功能
• 支持事务和非事务安全的选项
• 支持BLOG字符类型

Federated 存储引擎

Federated 存储引擎并不存放数据，它只是指向一台远程MySQL数据库服务器上的表。并且Federated 存储引擎只支持MySQL数据库表，不支持异构数据库表。

其它存储引擎

除了上面提到的存储引擎之外，MySQL数据库还有很多其它的存储引擎，包括 Blackhole 存储引擎、NDB（NDBCLUSTER） 存储引擎、CSV 存储引擎、Sphinx 存储引擎和Infobright存储引擎，他们都有各自的使用场景，这里不再一一介绍。

MySQL 存储引擎功能概要

1. 在服务器中实现而不是存储引擎。
2. 仅在使用压缩行格式时才支持压缩MyISAM表。在MyISAM中使用压缩行格式的表是只读的。
3. 在服务器中通过加密功能实现。
4. 通过加密功能在服务器中实现；在MySQL 5.7和更高版本中，支持静态数据表空间加密。
5. MySQL Cluster NDB 7.3和更高版本中提供了对外键的支持。
6. MySQL 5.6和更高版本提供了对FULLTEXT索引的InnoDB支持。
7. MySQL 5.7和更高版本提供了InnoDB对地理空间索引的支持。
8. InnoDB在内部将哈希索引用于其自适应哈希索引功能。

存储引擎的操作

查看存储引擎

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

设置存储引擎

我们创建表没有指定表的存储引擎，就会使用默认的存储引擎InnoDB。我们可以显式的指定一下表的存储引擎。

mysql> CREATE TABLE test (i INT) ENGINE =MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

修改存储引擎

mysql> ALTER TABLE test ENGINE = InnoDB;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)