mysql存储引擎

MySQL 提供了多种存储引擎，每种存储引擎都有各自的优缺点与使用场景，选择合适的存储引擎取决于具体的业务需求和数据结构，还要考虑数据的大小、访问模式、事务性能、并发性能等因素。

执行 SHOW ENGINES; 可以查看系统所支持的引擎类型以及默认引擎，输出结果中 DEFAULT 对应的引擎就是当前默认的存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

MyISAM 与 InnoDB

MyISAM

  • 在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。由于数据索引和存储数据分离，MyISAM引擎的索引结构是B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际数据是分开的。不过索引指向实际的数据，这种索引也就是非聚合索引。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
  • 但是它没有提供对数据库事务的支持，是表级锁（插入修改锁表），因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
  • 不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。
  • 如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

InnoDB

  • MyISAM和Innodb均采用B+Tree作为索引结构
  • InnoDB表完全支持符合ACID和事务。它们也是性能的最佳选择。InnoDB表支持外键，提交，回滚，前滚操作。InnoDB表的大小最高可达64TB。
  • 特殊的索引存放方式，可以减少IO，提高查询效率
  • 主索引的区别：MyISAM的索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，叶结点的data域存放的是数据记录的地址； 而Innodb存放的是数据，因为InnoDB的数据文件本身就是主索引；
  • 辅助索引的区别：MyISAM的辅助索引和主索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复； 而InnoDB辅助索引的data域存储的是主键的值而不是地址，所以检索辅助索引需要先从辅助索引获得主键，再用主键到主索引中获得记录；
  • innodb的聚集索引：主键 > 第一个唯一非空索引 > 生成一个隐藏的主键

MyISAM 与 InnoDB 优缺点对比

对比项 MyISAM InnoDB
文件格式 .frm（存储表定义）
.MYD（MYData，存储数据）
.MYI（MYIndex，存储索引）		 .frm（存储表定义）
.ibd（数据和索引文件）
版本支持 mysql 5.5之前的默认引擎 mysql 5.5及以后的默认存储引擎
优点1 查询速度快、占用空间小 支持事务、行级锁、外键等，因此数据完整性及一致性高
优点2 支持全文索引、压缩表(压缩后数据不可修改但空间小性能高) 并发性能好(采用MVVC多版本并发控制)，写不阻塞读(基于快照读)
优点3 存储了表的行数（count速度更快） 数据存储在表空间中、支持热备份
缺点1 不支持事务、行级锁、外键等，容易出现数据损坏 占用的空间比较大，对于只读操作的性能不如 MyISAM
缺点2 插入和更新会锁表，效率低 不支持全文索引

可以禁用MyISAM引擎吗

随着MySQL 8.0的推出，系统表已经全面采用InnoDB引擎，不再需要MyISAM引擎。另外，MGR中也不支持MyISAM引擎。因此，基本上可以考虑全面禁止使用MyISAM引擎了，问题是，这可行吗？

答案是肯定的，可以做到。

从MySQL 5.7.8开始，新增一个选项 disabled_storage_engines，只需要设置下即可：

disabled_storage_engines = MyISAM

这就完美地实现禁用MyISAM的目的了。另外，这么设置的话，是不会影响MySQL实例初始化的。即便是在MySQL 5.7版本中，系统表要使用MyISAM引擎，也不会影响生成MyISAM引擎的系统表。

disabled_storage_engines is disabled and has no effect if the server is started with any of these options: --bootstrap, --initialize, --initialize-insecure, --skip-grant-tables.

不过，设置该选项后可能会影响 mysql_upgrade 升级：

Setting disabled_storage_engines might cause an issue with mysql_upgrade. For details, see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

执行 mysql_upgrade 进行升级时可能会报错 mysql_upgrade: [ERROR] 3161: Storage engine MyISAM is disabled (Table creation is disallowed).

这时候需要临时关闭该选项，等待升级完成后再重新启用即可。

其他存储引擎

FEDERATED

  • FEDERATED 存储引擎可让您无需使用群集或复制技术管理从远程MySQL服务器的数据。
  • 本地联合表不存储任何数据。从本地联合表查询数据时，将从远程联合表中自动提取数据。

Memory

  • Memory 存储引擎是一种基于内存的存储引擎，数据存储在内存中，查询速度非常快，但是数据库重启或者崩溃，数据会全部消失。
  • Memory 存储引擎的优点是查询速度快、支持事务等。缺点是数据容易丢失，不适合存储大量数据。
  • 内存表存储在内存中并使用哈希索引，因此它们比MyISAM表更快。内存表数据的生命周期取决于数据库服务器的正常运行时间。内存存储引擎以前称为HEAP。

Archive

  • Archive 存储引擎是一种基于压缩的存储引擎，适合存储大量历史数据。
  • Archive 存储引擎的优点是占用空间小、查询速度快等。缺点是不支持索引和事务等功能。

NDB Cluster

  • NDB Cluster 是一种分布式存储引擎，支持高可用性和高并发性。
  • NDB Cluster 的优点是支持事务、高可用性、高并发性等。缺点是配置和管理比较复杂。

如何选择存储引擎

  • 对数据一致性要求比较高，需要大并发及事务支持，推荐 InnoDB
  • 数据查询多、更新少，对查询性能要求比较高，推荐 MyISAM
  • 需要一个用于查询的临时表，推荐 Memory
Copyright © www.sqlfans.cn 2023 All Right Reserved更新时间： 2023-04-21 10:44:36

results matching ""

    No results matching ""