MySQL之存储引擎概述

谁借莪１个温暖的怀抱￠ 2022-12-19 00:54 55阅读 0赞

> **关键词：**存储引擎    InnoDB    MyISAM

--------------------

<文中案例操作使用的MySQL版本为：5.7.32，版本不同会存在差异>

在MySQL中，存储引擎是为不同的表类型处理SQL操作的一种组件，也叫表的类型。因此存储引擎是表级别的，不同的表可以设置不同的存储引擎。MySQL 5.7版本开始，默认的存储引擎是InnoDB。简单的说，存储引擎就是数据存储的不同文件表现，即以不同的文件方式存储数据。比如在《MySQL之索引》中有提到，MyISAM的数据和索引是在不同的文件中存放的，文件格式是myd和myi，InnoDB的数据和索引是存放在同一个文件中的，文件格式是ibd。

# **█ 操作命令** #

**①通过show engines；或SELECT \* FROM INFORMATION\_SCHEMA.ENGINES;命令查看当前MySQL支持的存储引擎类型：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70][]

从上图结果可以看出，5.7.32版本支持的存储引擎有哪些，其中Engine列显示的是存储引擎的名称，Support列显示当前版本是否支持对应的存储引擎，DEFAULT表示默认，YES表示支持，NO表示不支持，所以5.7.32版本的默认存储引擎是InnoDB。Comment列简单的对相应的存储引擎做了描述，从InnoDB存储引擎的Comment内容可以看到，其是支持事务、行级锁以及外键的。Transactions列表示是否支持事务，可以看出只有InnoDB是支持事务的。XA列表示是否支持分布式事务，也是只有InnoDB支持。Savepoints是事务的相关功能，当然只有支持事务的InnoDB支持喽。

**②创建表时，如果没有指定具体的存储引擎，则会设置为默认的存储引擎。**

/*创建表时没有指定具体的存储引擎*/
    drop table if exists engines_test;
    create table engines_test(
        id int primary key,
        name varchar(10)
    );

通过show create table engines\_test;查看建表语句，发现被指定了默认的InnoDB存储引擎：

![20201104210640998.png][]

**③创建表时，显示指定具体的存储引擎**

drop table if exists engines_test;
    CREATE TABLE `engines_test` (
      `id` int(11) NOT NULL,
      `name` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM;/*指定表的存储引擎是MyISAM*/

通过show create table engines\_test;查看建表语句，发现存储引擎不再是默认的InnoDB了，而变成了指定的MyISAM：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 1][]

**④修改表的存储引擎**

对于已经创建好的表，如果需要修改它的存储引擎，可以使用ALTER语句进行修改，比如将第（3）步中创建的表engines\_test的存储引擎改为InnoDB:

alter table engines_test engine = InnoDB;

再通过show create table engines\_test;查看，发现存储引擎已经变成了InnoDB：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 2][]

*需要注意的是，当修改表的存储引擎时，因为不同的存储引擎支持的索引类型、数据类型和大小是有所不同的，所以修改了存储引擎有可能会造成数据丢失。*

当创建表或者通过ALTER命令修改表的存储引擎时，如果指定的存储引擎不在当前版本的提供范围内，则会报错：

![20201104210909895.png][]

--------------------

# **█ 存储引擎之间主要区别点** #

不同的存储引擎提供了不同的存储机制、索引类型和结构（哈希索引、B+树索引等）、锁的类型（表级别、行级别），并最终提供一系列不同的功能和能力。

**①字段和数据类型**

尽管所有的存储引擎都支持整形等字段类型，但是对于一些类型，有些存储引擎是不支持的，比如BOLB和TEXT等，还有的存储引擎对字段的长度还会有限制。在对表选择存储引擎的时候，要结合你的表字段类型来考虑，去选择合适的存储引擎。

**②锁的类型**

锁机制控制着在并发中，将如何访问数据。比如：当一个修改数据被加了锁，其他的会话将无法再对锁定的数据进行修改，直到持有锁的会话释放了锁。锁不紧决定了同时可以有多少个会话可以修改数据，也决定了同时可以有多少个会话可以查询数据（因为可能查询的数据正在被其他会话修改）。

不同的存储引擎支持的锁的粒度是不同的。MySQL主要支持三种锁的类型，表级别、页级别和行级别的锁。

MyISAM只支持表级别的锁，当修改表中数据的时候，MyISAM会对整张表加锁，这样就造成了其他会话无法访问整张表的数据的问题。比如一个会话正在修改第一行数据，此时另一个会话如果要查询第二行数据的话，将会阻塞等待，直到第一个会话释放了锁。表级别的锁将在并发访问中造成资源浪费。

页级别的锁是Berkeley DB（BDB）存储引擎支持的。存储引擎在加载磁盘文件中的数据到内存中时，是以页为单位加载的。所以，页级别的锁锁住的就是当前已经加载的页数据，在BDB存储引擎中，页的大小是8kb。当对同一页的数据进行修改时，也会存在并发的资源浪费。

InnoDB是支持行级别的锁。这种级别的锁具有最好的并发控制。它只锁定表中的一行数据，所以当其他会话同时访问了表中其他行的数据，是不会受到锁的影响的。

**③索引**

不同的存储引擎中索引的数据结构也会有所不同，比如InnoDB和MyISAM支持B+树结构的索引，MEMORY支持hash结构的索引。而且还有些存储引擎是不支持索引的，比如：BLACKHOLE

**④事务**

事务提供了数据的可靠性。在所有的存储引擎中只有InnoDB是支持事务的。（关于事务后续会专门有一篇文章来说明）

--------------------

### **各存储引擎对于各种功能的支持：** ###

（引用自[MySQL5.7版本官方文档][MySQL5.7]）：

<table> 
 <tbody> 
 <tr> 
 <td> Feature </td> 
 <td> 功能 </td> 
 <td> InnoDB </td> 
 <td> MyISAM </td> 
 <td> Memory </td> 
 <td> Archive </td> 
 <td> NDB </td> 
 </tr> 
 <tr> 
 <td> B-tree indexes </td> 
 <td> B树/B+树索引 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Backup/point-in-time recovery </td> 
 <td> 备份和恢复数据，即binlog日志 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Cluster database support </td> 
 <td> 支持集群 </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Clustered indexes </td> 
 <td> 聚簇索引 </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Compressed data </td> 
 <td> 数据压缩 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> Yes </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Data caches </td> 
 <td> 数据缓存 </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> N/A </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Encrypted data </td> 
 <td> 数据加密 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Foreign key support </td> 
 <td> 支持外键 </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Full-text search indexes </td> 
 <td> 全文索引 </td> 
 <td> Yes （从5.6版本开始支持） </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Geospatial data type support </td> 
 <td> 支持地理空间数据类型 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Geospatial indexing support </td> 
 <td> （支持地理空间数据类型索引） </td> 
 <td> Yes （从5.7版本开始支持） </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Hash indexes </td> 
 <td> 哈希索引 </td> 
 <td> No （支持自适应哈希） </td> 
 <td> No </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Index caches </td> 
 <td> 索引缓存 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> N/A </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Locking granularity </td> 
 <td> 最小锁粒度 </td> 
 <td> Row </td> 
 <td> Table </td> 
 <td> Table </td> 
 <td> Row </td> 
 <td> Row </td> 
 </tr> 
 <tr> 
 <td> MVCC（multiversion concurrency control） </td> 
 <td> 多版本并发控制 </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Replication support </td> 
 <td> 支持副本 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Limited </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Storage limits </td> 
 <td> 存储数据大小限制 </td> 
 <td> 64TB </td> 
 <td> 256TB </td> 
 <td> RAM </td> 
 <td> None </td> 
 <td> 384EB </td> 
 </tr> 
 <tr> 
 <td> T-tree indexes </td> 
 <td> T树索引 </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Transactions </td> 
 <td> 事务 </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Update statistics for data dictionary </td> 
 <td> 更新数据字典统计，即ANALYZE table </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70]: /images/20221120/1dbdd6ff7aba490b845cda4aa421ebf9.png
[20201104210640998.png]: https://img-blog.csdnimg.cn/20201104210640998.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201104210736246.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201104210827585.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ==,size_16,color_FFFFFF,t_70
[20201104210909895.png]: https://img-blog.csdnimg.cn/20201104210909895.png
[MySQL5.7]: https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html