MySQL 常用存储引擎详解和区别

  • Z时代
  • 2024-01-10
  • 分类：IT

MySQL 提供多种存储引擎对数据表进行处理，MySQL 5.1 (如无特殊说明, 本文所使用版本均为 5.1) 引入了新的插件式存储引擎体系结构，对于编程人员而言，插拔及修改存储引擎都是极其方便的。

MySQL 常用存储引擎详解和区别

MyISAM

  • 表级锁

  • 数据非自动恢复

  • 不支持事务

  • 仅索引加载在内存中，数据缓存由操作系统

  • 紧凑的数据存储，正是这样才能实现高速的数据扫描

HEAP

  • 表级锁

  • 不支持动态数据类型Text和BLOB，varchar会被当做成char来处理

  • 默认索引类型是Hash索引

  • 不支持索引统计

  • MySQL数据库服务器重启后数据全部丢失

InnoDB

  • 支持事务

  • 支持外键（version 5.0，only the storage engine level support）

  • 行级锁

  • 多版本支持

  • 主键聚集，所有InnoDB表都依赖于主键聚集

  • 所有的非主键索引内容包含主键列的值

  • 最优化缓存，缓存中包含索引和数据，innodb_buffer_pool_size.InnoDB缓存采用的是自动hash便于查找

  • 非压缩的索引

  • 缓慢的数据加载，因为MySQL 5.0没有特别优化数据加载操作，每条记录建一次索引，所以大数据量的服务重启会很慢。

  • AUTO_INCREMENT锁表，在MySQL 5.1以前的InnoDB中，如果主键列采用了AUTO_INCREMENT则是表级锁。在MySQL5.1以后支持innodb_autoinc_lock_mode，默认值1，含义是bulk insert。0是传统的表级锁，2是交叉的表级锁（interleaved：fastest and most scalable lock mode，but not safe）。

  • 不缓存COUNT（*）的值

纵观以上3中常用的存储引擎，InnoDB和Heap在以后新的应用中更有可用性。

以下再看看MyISAM和InnoDB的索引图示

MySQL 常用存储引擎详解和区别

存储引擎简介

这些引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

  • MyISAM 管理非事务表. 提供高速存储和检索, 以及全文搜索能力. 主要用于 Web, 数据仓库等应用环境. 是 5.5 之前的默认存储引擎 (可以通过配置修改).

  • InnoDB 提供事务安全表 (ACID 兼容). 支持 commit, rollback, crash-recovery.

  • BDB 提供事务安全表.

  • Memory 将所有数据存储在 RAM 中以提高存取速度, 其正式称谓为 HEAP 引擎.

  • Merge 管理非事务表. 允许 DBA 或开发者将一组逻辑相关的 MyISAM 表视为一张单独的表.

  • Archive 存储及索引大批量存档数据.

  • Federated 联合多台物理数据库服务器, 组合成一个逻辑数据库.

  • NDBCLUSTER (NDB) 集群数据库存储引擎.

  • CSV 把数据以逗号分隔的格式存储在文本文件中. 用于应用程序间导入/导出 CSV 格式数据.

  • Blackhole 接受但不存储数据, 并且检索总是返回一个空集. 可用于分布式数据库设计 (数据自动复制, 不在本地存储).

  • Example “存根 (stub)” 引擎, 不做什么事. 可以用它创建表, 但不能用于存储或检索数据. 用于向开发者展示如何编写 MySQL 存储引擎.

FeatureMyISAMMemoryInnoDBArchiveNDB
Storage limits256TBRAM64TBNone384EB
TransactionsNoNoYesNoYes
Locking granularityTableTableRowRowRow
MVCCNoNoYesNoNo
Geospatial data type supportYesNoYesYesYes
Geospatial indexing supportYesNoNoNoNo
B-tree indexesYesYesYesNoYes
Hash indexesNoYesNoNoYes
Full-text search indexesYesNoNoNoNo
Clustered indexesNoNoYesNoNo
Data cachesNoN/AYesNoYes
Index cachesYesN/AYesNoYes
Compressed dataYesNoYesYesNo
Encrypted dataYesYesYesYesYes
Cluster database supportNoNoNoNoYes
Replication supportYesYesYesYesYes
Foreign key supportNoNoYesNoNo
Backup / point-in-time recoveryYesYesYesYesYes
Query cache supportYesYesYesYesYes
Update statistics for data dictionaryYesYesYesYesYes

一般应用程序中常用的存储引擎主要是 MyISAM 与 InnoDB 两种.

MyISAM

MyISAM 是 MySQL 5.5 之前 (不含 5.5) 的默认存储引擎, MyISAM 的最大缺陷在于不支持事务处理 (transaction).

每个 MyISAM 表在磁盘上存储成三个文件 (如 /var/lib/mysql/db 下). 文件名即为表名, 扩展名指出文件类型. 表格式文件的扩展名为 .frm; 数据文件的扩展名为.MYD (MYData); 索引文件的扩展名是.MYI (MYIndex).

InnoDB

从 MySQL 5.5 开始, InnoDB 成为 MySQL 的默认存储引擎.

InnoDB 为 MySQL 提供了具有提交, 回滚和崩溃恢复能力的事务安全 (ACID兼容) 存储引擎. InnoDB 设计目的是为处理巨大数据量时发挥最大性能. 它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的.

InnoDB 存储引擎被完全与 MySQL 服务器整合, InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池. InnoDB 将其表 & 索引存储在一个表空间中, 表空间可以包含数个文件 (或原始磁盘分区), 这与 MyISAM 表中每个表被存在分离的文件中有所不同. InnoDB 表可以是任何尺寸, 即使在文件尺寸被限制为 2GB 的操作系统上.

InnoDB 被用在众多需要高性能的大型数据库站点上.

常用命令

创建表时指定存储引擎

mysql> CREATE TABLE t (i INT) ENGINE = MYISAM;

修改表的存储引擎

mysql> ALTER TABLE t ENGINE = MYISAM;

显示存储引擎状态信息

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
显示当前默认存储引擎
mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

显示当前默认存储引擎

$ mysqlshow -u<user> -p<password> --status <database>
mysql> SHOW TABLE STATUS FROM trac;

数据库备份与恢复

MyISAM 表保存为文件方式, 很容易备份. 为保持备份一致性, 对相关表执行 LOCK TABLES 操作进行读锁定 (复制数据库目录中的文件时, 允许其它客户继续查询表), 然后对表执行 FLUSH TABLES. 你只需要读锁定; 这样当你复制数据库目录中的文件时, 允许其它客户继续查询表. 需要 FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入硬盘.

只要服务器不再进行更新, 还可以只复制所有表文件(.frm、.MYD和*.MYI文件). mysqlhotcopy 脚本使用该方法. (但请注意如果数据库包含 InnoDB 表, 这些方法不工作. InnoDB 不将表的内容保存到数据库目录中, mysqlhotcopy 只适合 MyISAM 表）.

如果你在服务器上进行备份, 并且表均为MyISAM表, 应考虑使用mysqlhotcopy, 因为可以更快地进行备份和恢复:

$ mysqlhotcopy db_name [/path/to/new_directory]
$ mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
$ mysqlhotcopy db_name./regex/

对于 InnoDB 表, 可以进行在线备份, 不需要对表进行锁定:

$ mysqldump [options] db_name [tables]
$ mysqldump [options] ---database DB1 [DB2 DB3...]
$ mysqldump [options] --all--database

以上是 MySQL 常用存储引擎详解和区别 的全部内容， 来源链接： utcz.com/p/231775.html

回到顶部