MySQL数据库引擎详解

阳光穿透心脏的1/2处 2022-05-22 04:05 301阅读 0赞

作为经常使用MySQL数据库的人，不得不对MySQL的数据库引擎有一定的了解，在之前我也是只会使用MySQL，但两次的面试中都被面试官问及项目用到的数据库引擎（血与泪），因此，有必要写一篇文章，好好总结一下MySQL的数据库引擎。

# 1. 数据库引擎定义 #

数据库引擎是数据库用于存储、处理和保护数据的核心服务，不同的数据库引擎有其各自的特点，如存储机制、索引技巧、主键的处理、锁的粒度等特点便随着引擎的不同而变化。因此，针对自己项目特点选择合适的数据库引擎可以改善服务器端存储性能。

常见的MySQL数据库引擎有以下几个：InnoDB，MyIsam，Memory，Mrg\_Myisam，Blackhole等。

# 2.如何查看及设置数据库引擎 #

在mysql(版本5.6)命令行中使用如下命令：

show engines；

可以看到如下结果：

![20180614155549873][]

可以看出，默认的数据库引擎是InnoDB。

## 2.1 如何更改默认数据库引擎 ##

找到MySQL配置文件mysql.ini，首先将其备份(这是个好习惯，当需要更改Linux上配置文件时)即

cp mysql.ini mysql.ini.bak

在\[mysqld\]后面添加default-storage-engine=引擎名字 ，保存，重启MySQL服务。

## 2.2 如何在建表时设置引擎及建表后更改引擎 ##

建表时设置引擎：

create table 表名（
        ...
    )type=引擎名

建表后更改表的引擎：

alter table 表名 type=引擎名；

##  ##

## 2.3 如何查看引擎是否修改成功 ##

该命令会列出数据库所有表的属性

show table status from 数据库名字;

# 3. 引擎详解 #

#  #

##  ##

## 3.1 InnoDB引擎 ##

InnoDB是一个事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中独一份，仅有它支持外键）

该引擎的设计目标便是处理大容量数据的数据库系统，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。

可能的缺点：

1. 该引擎不支持FULLTEXT类型的索引

2. 没有保存表的行数，在执行select count(\*) from 表名 时，需要遍历扫描全表

适用场景：

1. 经常需要更新的表，适合处理多重并发的更新请求

2. 支持事务

3. 外键约束

4. 可以从灾难中恢复（通过bin-log日志等）

5. 支持自动增加列属性auto\_increment

## 3.2 MyIsam引擎 ##

MyIsam引擎是MySQL主流引擎之一，但它相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立与操作系统，可以在windows及linux上使用。

可能的缺点：

不能在表损坏后恢复数据

适用场景：

1. MyIsam极度强调快速读取

2. MyIsam表中自动存储了表的行数，需要时直接获取即可

3. 适用于不需要事物支持、外键功能、及需要对整个表加锁的情形

## 3.3 Memory（Heap）引擎 ##

使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多

可能的缺点：

1. 要求存储的数据是数据长度不变的格式，Blob和Text类型数据不可用（长度不固定）

2. 用完表格后表格便被删除

适用场景：

1. 那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果

2. 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max\_heap\_table\_size控制Memory表的大小，限制Memory表的最大的大小

3. 数据是临时的，而且必须立即能取出用到，于是可存放在内存中

4. 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系

## 3.4 Mrg\_MyIsam引擎 ##

是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。也就是说，它将MyIsam引擎的多个表聚合起来，但是它的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。

比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表，如果这时候进行查询的话，就会比较麻烦，Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义），原来的数据不会影响。