MySQL --- 存储引擎-阿里云开发者社区

MySQL --- 存储引擎

2022-05-18 161

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，高可用系列 2核4GB

简介： MySQL --- 存储引擎

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎。

1.存储引擎相关的命令：

>mysql> show engines;  # 查看 MySQL 提供的所有存储引擎
mysql> show variables like '%storage_engine%';  # 默认存储引擎
show table status like "table_name" ;  # 查看表的使用的存储引擎
show table status from database where name="tablename"

2.存储引擎对比：

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键

面试题： MyISAM 和 InnoDB 的区别？如何选择

是否支持事务：InnoDB 支持事务（ACID），MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB的重要原因之一；
是否支持外键： InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
是否支持行级锁：InnoDB 最小的锁粒度是行锁（默认），MyISAM 最小的锁粒度是表锁, 一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
是否支持数据库异常崩溃后的安全恢复：使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（默认支持的隔离级别是可重复读 REPEATABLE-READ ）。
保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

是否支持聚簇索引：InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针（地址）。主键索引和辅助索引是独立的。具有较高的插入和查询速度。
补充：InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

如何选择存储引擎呢？

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）。一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

Innodb三大关键特性

插入缓冲、二次写入和自适应哈希。

1.插入缓冲

适用对象：非唯一索引和非聚集索引

为什么要有这个特性，或者说这个特性解决了什么问题？

Innodb数据表本身就是一个聚集索引表，表中的记录按照聚集索引顺序存储，插入时按聚集索引自增插入，顺序写磁盘，速度是有保证的；
但对于非聚集索引，插入操作就不那么顺利了，非聚集索引并非按顺序插入，因此在插入非聚集索引叶节点时，为随机插入，性能不高；

具体操作：

如果索引页在缓冲池中，则直接插入
如果不在，则先放入一个插入缓冲区中，返回插入成功的结果。
master thread会定时将插入缓冲区中的数据插入数据库
此举将多个插入合并到一个IO操作中，从而提高了性能

2.两次写

先了解：部分写失效

当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，就是部分写失效。

为什么要有这个特性，或者说这个特性解决了什么问题？

部分写失效会导致数据丢失。是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

具体操作：

image

先将数据写入缓冲区
分两次将缓冲区的数据写入磁盘共享表空间，每次写入1MB
将缓冲区的数据写入数据文件
------假如宕机-------
将共享表空间的的页覆盖原有数据页
再应用重做日志（redo log）

两次写性能开销只在第二步

由于磁盘共享表空间是连续的，因此开销不是很大

可通过skip_innodb_doublewrite属性禁用，默认开启

3.自适应哈希

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。
InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

为什么MyISAM查询比Innodb快？

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多：

缓存数据：INNODB要缓存数据块，MYISAM只缓存索引块，这中间还有换进换出的减少；
寻址方式：INNODB寻址要先映射到块，再到行；MYISAM记录的直接是文件的OFFSET，定位比INNODB要快(注：MYISAM 更新频率低，所以索引变更少，所以允许每次更新，即更新主索引，也更新副索引，更新offset)
维护MVCC: INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护，MYISAM 表锁牺牲了写性能，提高了读性能。

数据库保证并发性（MVCC）

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MVCC带来的好处?

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。所以MVCC可以为数据库解决以下问题:

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，不可重复读，幻读等事务隔离问题，但不能解决更新丢失问题

总之，MVCC就是为了解决数据库仅仅采用悲观锁这样性能不佳的形式去解决读-写冲突问题，所以在数据库中我们可以形成两个组合：

MVCC + 悲观锁：MVCC解决读-写冲突，悲观锁解决写-写冲突
MVCC + 乐观锁：MVCC解决读-写冲突，乐观锁解决写-写冲突

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行select count(*) from t时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update。

for update 可以根据条件来完成行锁锁定，并且 id是有索引键的列，如果id不是索引键，那么InnoDB将完成表锁，并发将无从谈起。

以上仅供学习使用

MySQL --- 存储引擎

1.存储引擎相关的命令：