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

发布于 2017-12-08 23:09:37 字数 8325 浏览 2726 评论 0

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的索引图示

存储引擎简介

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

  • 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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

列表为空,暂无数据

关于作者

JSmiles

生命进入颠沛而奔忙的本质状态,并将以不断告别和相遇的陈旧方式继续下去。

0 文章
0 评论
84935 人气
更多

推荐作者

遥远的她

文章 0 评论 0

情深如许

文章 0 评论 0

18120987591

文章 0 评论 0

女皇必胜

文章 0 评论 0

13002228876

文章 0 评论 0

    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击“接受”或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。