笔记

# MySQL存储引擎

存储引擎是数据库的核心，对于 MySQL 来说，存储引擎是以插件的形式运行的。虽然 MySQL 支持种类繁多的存储引擎，但是常用的就那么几种

# 1. 存储引擎介绍

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，虽然，MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复

5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB

# 查看 MySQL 提供的所有存储引擎
mysql> show engines;
# 查看 MySQL 当前默认的存储引擎
mysql> show variables like '%storage_engine%';
# 查看表的存储引擎
mysql> show table status like "table_name";

# 2. MyISAM和InnoDB

介绍

# 2.1. MyISAM

MySQL 5.5 版本之前的默认存储引擎，在 5.0 以前最大表存储空间最大 4G，5.0 以后最大 256TB。由 .myd（数据）和 .myi（索引文件）组成，.frm文件存储表结构（所以存储引擎都有）

特性

  • 并发性和锁级别 （对于读写混合的操作不好，为表级锁，写入和读互斥）
  • 表损坏修复
  • Myisam 表支持的索引类型（全文索引）
  • Myisam 支持表压缩（压缩后，此表为只读，不可以写入。使用 myisampack 压缩）

应用场景

  • 没有事务
  • 只读类应用（插入不频繁，查询非常频繁）
  • 空间类应用（唯一支持空间函数的引擎）
  • 做很多 count 的计算

# 2.2. InnoDB

MySQL 5.5 及之后版本的默认存储引擎

特性

  • InnoDB为事务性存储引擎
  • 完全支持事物的 ACID 特性
  • Redo log （实现事务的持久性） 和 Undo log（为了实现事务的原子性，存储未完成事务log，用于回滚）
  • InnoDB支持行级锁
  • 行级锁可以最大程度的支持并发
  • 行级锁是由存储引擎层实现的

应用场景

  • 可靠性要求比较高，或者要求事务
  • 表更新和查询都相当的频繁，并且行锁定的机会比较大的情况

# 2.3. 对比

区别 InnoDB MyISAM
事务 安全 非安全
锁 行级 表级
索引 聚集索引 非聚集索引
外键 支持 不支持
崩溃恢复 支持 不支持
场景 需要事务，大量增、改 多查询，不需要事务

事务

  • MyISAM 不提供事务支持
  • InnoDB 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力

锁

  • MyISAM 只有表级锁(table-level locking)
  • InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁

也就说，MyISAM 一锁就是锁住了整张表，所以在并发写的情况下效率比较低，而 InnoDB 在并发写的时候，性能更强

表级锁

MySQL 中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁

行级锁

MySQL 中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁

锁的算法

  • Record lock：记录锁，单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap临键锁，锁定一个范围，包含记录本身
  • 详细查看：MySQL的那些锁-InnoDB锁算法

MVCC

  • MyISAM 不支持，连行级锁都不支持，肯定不支持 MVCC
  • InnoDB 支持，MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能

索引

  • MyISAM 是非聚集索引，支持全文索引
  • InnoDB 是聚集索引，不支持全文索引

外键

  • MyISAM 不支持
  • InnoDB 支持

一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定

是否支持数据库异常崩溃后的安全恢复

  • MyISAM 不支持
  • InnoDB 支持，数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log

崩溃恢复

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

# 2.4. MyISAM和InnoDB的选择

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）

《MySQL 高性能》上面有一句话这样写到:

不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的

因此，对于咱们日常开发的业务系统来说，你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎

# 3. 其他存储引擎

其他存储引擎介绍

# 3.1. CSV

文件系统存储特点

  • 数据以文本方式存储在文件中
  • .csv文件存储表内容
  • .csm文件存储表的元数据，如表状态和数据量
  • .frm存储表的结构

CSV存储引擎特点

  • 以 CSV 格式进行数据存储
  • 所有列必须都是不能为 NULL
  • 不支持索引
  • 可以对数据文件直接编辑（其他引擎是二进制存储，不可编辑）

引用场景

  • 作为数据交换的中间表

# 3.2. Archive

特性

  • 以 zlib 对表数据进行压缩，磁盘 I/O 更少
  • 数据存储在ARZ为后缀的文件中（表文件为 a.arz，a.frm）
  • 只支持 insert 和 select 操作（不可以 delete 和 update，会提示没有这个功能）
  • 只允许在自增ID列上加索引

应用场景

  • 日志和数据采集类应用

# 3.3. Memory

特性

  • 也称为 HEAP 存储引擎，所以数据保存在内存中（数据库重启后会导致数据丢失）
  • 支持 HASH 索引（等值查找应选择 HASH）和 BTree 索引（范围查找应选择）
  • 所有字段都为固定长度，varchar(10) == char(10)
  • 不支持 BLOG 和 TEXT 等大字段
  • Memory 存储使用表级锁（性能可能不如 innodb）
  • 最大大小由 max_heap_table_size 参数决定
  • Memory存储引擎默认表大小只有 16M，可以通过调整 max_heap_table_size 参数

应用场景

  • 用于查找或是映射表，例如右边和地区的对应表
  • 用于保存数据分析中产生的中间表
  • 用于缓存周期性聚合数据的结果表

注意： Memory 数据易丢失，所以要求数据可再生

# 3.4. Federated

特性

  • 提供了访问远程 MySQL 服务器上表的方法
  • 本地不存储数据，数据全部放在远程服务器上

使用 Federated

默认是禁止的。如果需要启用，需要在启动时增加Federated参数

# 4. 独立表空间和系统表空间

两者比较

  • 系统表空间：无法简单的收缩大小（这很恐怖，会导致 ibdata1 一直增大，即使删除了数据也不会变小）
  • 独立表空间：可以通过 optimize table 命令收缩系统文件
  • 系统表空间：会产生I/O瓶颈（因为只有一个文件）
  • 独立表空间：可以向多个文件刷新数据

总结

强烈建议：对Innodb引擎使用独立表空间（mysql5.6版本以后默认是独立表空间）

系统表转移为独立表的步骤（非常繁琐）

  • 使用 mysqldump 导出所有数据库表数据
  • 停止 mysql 服务，修改参数，并且删除Innodb相关文件
  • 重启 mysql 服务，重建mysql系统表空间
  • 重新导入数据

参考

上次更新时间: 2023-12-15 03:14:55

← MySQL索引及结构 →