跳转到内容

MySQL/表类型

Edit links
来自维基教科书，为开放世界而开放的书籍

每个表都是数据库中的一个逻辑对象；但它也需要在磁盘或内存中物理存储其数据（记录）。表使用存储引擎来完成此操作。存储引擎是可安装或卸载到服务器中的插件（如果它们不是内置的）。

服务器请求许多操作，但实际由存储引擎完成。因此，我们为表选择的存储引擎会影响性能、稳定性、锁类型、查询缓存的使用、所需的磁盘空间和特殊功能。

在 MySQL 的未来版本中，分区表将能够为不同的分区使用不同的存储引擎。

让我们看看哪个存储引擎适合哪些用途。

注意
表类型是 MySQL 最近版本中已弃用的旧术语。它仍然被某些 SQL 命令为了向后兼容而接受，但应该优先使用 ENGINE[s] 或 STORAGE ENGINE[s]。

 一位维基教科书用户建议将本书或章节合并到MySQL/优化中。
请在讨论页面上讨论是否应该进行此合并。

存储引擎

[编辑 | 编辑源代码]

MyISAM 和 InnoDB

[编辑 | 编辑源代码]

MyISAM 执行表级锁定，而 InnoDB 执行行级锁定。除了外键之外，InnoDB 还提供事务支持，这在处理大型应用程序时至关重要。速度可能会下降，特别是对于具有完整事务保证的插入操作，因为所有这些外键/事务内容都会增加开销。

MySQL 在 Linux 上的默认表类型是 MyISAM，在 Windows 上通常是 InnoDB。MyISAM 使用表级锁定，这意味着在 UPDATE 期间，任何人都无法访问同一表的任何其他记录。然而，InnoDB 使用行级锁定。行级锁定确保在 UPDATE 期间，在锁定事务发出 COMMIT 之前，任何人都无法访问该特定行。许多人在需要速度时使用 MyISAM，在需要数据完整性时使用 InnoDB。

MyISAM

[编辑 | 编辑源代码]
  • 优点
    • 全文本搜索目前仅适用于 MyISAM 表
    • 几何数据类型
    • 有时读取速度更快
    • 所有数字键值都以高字节优先存储，以允许更好的索引压缩
    • 支持每个表内部处理一个 AUTO_INCREMENT 列。MyISAM 会自动为 INSERT 和 UPDATE 操作更新此列。这使得 AUTO_INCREMENT 列更快（至少快 10%）
  • 缺点
    • 仅表级锁定（而不是行级锁定）
    • 没有外键约束（但计划在 MySQL 6.x 中添加）
    • 断电后表检查和重启速度较慢，对于那些需要高可用性的人来说这是一个问题

InnoDB

[编辑 | 编辑源代码]
  • 优点
    • 为 MySQL 提供了一个事务安全的（符合 ACID）存储引擎，具有提交、回滚和崩溃恢复功能
    • XA 事务
    • 外键
    • 行级锁定
    • 维护自己的缓冲池，用于在主内存中缓存数据和索引
    • 对于某些工作负载更快，特别是那些按主键物理排序或自动构建的哈希索引加速记录查找的工作负载
    • 即使在文件大小限制为 2GB 的操作系统上，表也可以是任何大小。
    • 断电后快速可靠的恢复。
  • 缺点
    • 存储数据需要更多空间
    • ACID 保证需要在事务提交时完全同步到磁盘，可以在速度比完全 ACID 保证更重要的场合将其关闭。
    • 数据版本控制和事务会给表管理增加开销。
    • 它们可能导致高内存需求，以管理用于行锁定的大量锁。
    • 在创建表后添加索引时，索引构建速度很慢。因此，索引应该在数据批量加载时创建。

总的来说，InnoDB 应该用于高度依赖数据完整性或需要事务的应用程序，而 MyISAM 可以用于不需要数据完整性或需要全文本索引的应用程序。如果速度更重要，则应该尝试两者，因为哪一个更快取决于应用程序。

Drizzle 是 MySQL 的一个分支，由 Sun Microsystems 支持，它使用 InnoDB 作为其默认引擎，不支持 MyISAM。

合并表

[编辑 | 编辑源代码]

同义词：Merge，MRG_MYISAM

  • 合并表是相同 MyISAM 表的集合，可以作为一个表使用。
  • 相同意味着所有表都具有相同的列和索引信息，不允许任何偏差。
CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE delhi  (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE total  (first_name VARCHAR(30), amount INT(10)) TYPE=MERGE UNION=(mumbai,delhi)

合并可以用来解决 MySQL 或系统的文件大小限制。事实上，这些限制会影响单个 MyISAM 数据文件，但不会影响整个合并表，因为合并表没有数据文件。

在过去，在某些情况下，合并和 MyISAM 可以用来替代视图，而视图不受 MySQL 支持。合并可以用作基表，而 MyISAM 表可以用作包含部分基表数据的视图。对合并表的 SELECT 返回所有有效数据。视图支持在 MySQL 5.0 中添加，因此这种合并表的使用已经过时。

MEMORY / HEAP

[编辑 | 编辑源代码]

在 MySQL 4.1 之前，此表类型称为 HEAP。MEMORY 是新的首选名称。

此引擎是在 3.23 版本中引入的。

BDB

[编辑 | 编辑源代码]

同义词：BDB，BerkleyDB

BDB 已从 5.1 及更高版本中移除，因为使用率太低。

BerkeleyDB 是一系列由 SleepyCat 开发的免费软件可嵌入式 DBMS，SleepyCat 是一家已被 Oracle 收购的公司。SleepyCat 为 MySQL 提供了一个名为 BDB 的存储引擎。

BDB 支持事务和页面级锁定，但在 MySQL 中也有许多限制。

BLACKHOLE

[编辑 | 编辑源代码]

丢弃存储在其中的所有数据，但仍然写入二进制日志，因此它在复制扩展或安全的 binlog-do 过滤情况下很有用，其中从服务器不可靠，以及用于对服务器更高层的基准测试。

其他

[编辑 | 编辑源代码]

为了完整性，其他存储引擎包括

  • CSV：简单的逗号分隔值引擎，它使用 CSV 格式存储数据。可能用于与其他支持 CSV 的应用程序共享数据库？由于其格式简单，因此无法使用索引。
  • 示例（开发者存根）
  • ISAM（用于 3.23 之前的向后兼容性，在 5.1 中已移除）

关于存储引擎的元数据

[编辑 | 编辑源代码]

您可以通过 SQL 获取有关官方 MySQL 存储引擎和其他存储引擎（存在于您的服务器上）的元数据。

SHOW STORAGE ENGINES

[编辑 | 编辑源代码]

从 MySQL 5.0 开始，您可以使用 SHOW STORAGE ENGINES 语句获取有关可以使用哪些存储引擎的信息。

SHOW STORAGE ENGINES

STORAGE 字词是可选的。此命令返回一个包含以下列的数据集。

  • Engine - 存储引擎的名称。
  • Support - 存储引擎是否受支持。可能的数值
    • 'DEFAULT' - 它受支持并且是默认引擎；
    • 'YES' - 受支持；
    • 'DISABLED' - 它已被编译，但 MySQL 启动时禁用了该引擎（可能通过 --skip-engine-name 等选项）；
    • 'NO' - 不受支持。
  • Comment - 引擎的简短描述。
  • Transactions - 引擎是否支持 SQL 事务。在 MySQL 5.1 中添加。
  • XA - 引擎是否支持 XA 事务。在 MySQL 5.1 中添加。
  • Savepoints - 引擎是否支持保存点和回滚。在 MySQL 5.1 中添加。

INFORMATION_SCHEMA `ENGINES` 表

[编辑 | 编辑源代码]

`ENGINES` 是 INFORMATION_SCHEMA 数据库中的一个虚拟表。它可用于获取有关存储引擎的信息。它的列与 SHOW ENGINES 语句返回的列相同（见上文）。

ENGINES 已在 MySQL 5.1.5 中添加。

HELP 语句

[编辑 | 编辑源代码]

如果您想了解更多关于官方 MySQL 存储引擎的信息，可以使用 HELP 命令。

HELP 'myisam'

如果您使用的是命令行客户端，则可以省略引号。

help myisam \g

更改存储引擎

[编辑 | 编辑源代码]

SQL

[编辑 | 编辑源代码]

当您想要使用给定的存储引擎创建表时，可以在 CREATE TABLE 命令中使用 ENGINE 子句。

CREATE TABLE ... ENGINE=InnoDB

如果未指定 ENGINE 子句，将使用 storage_engine 变量的值。默认情况下，它是 MyISAM，但您可以更改它。

SET storage_engine=InnoDB

或者您可以在启动 MySQL 服务器之前修改 my.cnf 中的 default-storage-engine 值。

您也可以更改现有表的存储引擎。

ALTER TABLE `stats` ENGINE=MyISAM

mysql_convert_table_format

[编辑 | 编辑源代码]

mysql_convert_table_format 是 MySQL 提供的一个工具，用 Perl 编写。它将指定数据库中包含的所有表转换为另一个存储引擎。

语法是

mysql_convert_table_format [options] database

database 是程序将在其中运行的数据库的名称。它是强制性的。

选项是

--help 打印帮助信息并退出。

--version 打印版本号并退出。

--host=host MySQL 运行所在的宿主机。默认值：localhost。

--port=port TCP 端口。

--user=user 指定用户名。

--password=password 指定密码。由于它不安全（例如，它在 coomand top 中可见），因此您可以使用选项文件代替。

--type=storage_engine 表转换后将使用的存储引擎。

--force 如果发生错误，不要停止执行。

--verbose 打印关于转换的详细信息。

示例

mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test

此命令指定访问数据（localhost、用户名、密码）并将 `test` 数据库中的所有表转换为 InnoDB。如果某些表无法转换，脚本将跳过它们并转换其他表（--force）。 *斜体文本*

检索自 "https://wikibooks.cn/w/index.php?title=MySQL/Table_types&oldid=3663566"
华夏公益教科书