数据库存储引擎（主要分析对比InnoDB和MyISAM）

清疚 2023-06-08 06:34 125阅读 0赞

# 一、存储引擎的概述 #

## （1）为什么要合理选择数据库存储引擎     ##

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

## （2）如何修改数据库存储引擎 ##

### 方式一： ###

修改配置文件my.ini：

将mysql.ini另存为my.ini，在\[mysqld\]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB

### 方式二： ###

在建表的时候指定

create table mytbl(   
        id int primary key,   
        name varchar(50)   
    )type=MyISAM;

### 方式三： ###

建表后更改

alter table table_name type = InnoDB;

## （3）怎样查看修改成功 ##

### 方式一： ###

show table status from table_name;

### 方式二： ###

show create table table_name

# 二、InnoDB（默认存储引擎） #

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(\*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。
    SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

## 适用场景 ##

1）经常更新的表，适合处理多重并发的更新请求。  
2）支持事务。  
3）可以从灾难中恢复（通过bin-log日志等）。  
4）外键约束。只有他支持外键。  
5）支持自动增加列属性auto\_increment。

## MySQL官方对InnoDB的讲解： ##

1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。  
2）InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。  
3）InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。  
4）InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。  
5） InnoDB被用来在众多需要高性能的大型数据库站点上产生。

# 三、MyISAM #

MyIASM是MySQL5.5之前默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

意味着：引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。

## 适用场景 ##

1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。

2）不支持外键的表设计。

3）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。

4）整天 对表进行加锁的场景。

5）MyISAM极度强调快速读取操作。

6）MyIASM中存储了表的行数，于是SELECT COUNT(\*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

# 四、各种存储引擎比对 #

![format_png][]

参考：

[https://www.zhihu.com/question/20596402][https_www.zhihu.com_question_20596402]

[format_png]: /images/20230531/62c548b2d5ba4d87b7ec8f5c8f679208.png
[https_www.zhihu.com_question_20596402]: https://www.zhihu.com/question/20596402