表的存储引擎（Storage Engine）

概述

每张表都是数据库中的逻辑对象，但需要将其数据（记录）物理存储在磁盘和/或内存中。表通过存储引擎（Storage Engine, SE）实现这一功能。存储引擎是插件，可以安装或卸载（除内置引擎外）。
存储引擎会影响性能、稳定性、锁定类型、查询缓存的使用、磁盘空间需求以及特殊功能。
未来版本的 MySQL 可能允许分区表的不同分区使用不同的存储引擎。

注意：
“表类型”（Table Type）是 MySQL 的旧术语，现已废弃。建议使用 ENGINE 或 STORAGE ENGINE。

主要存储引擎

MyISAM 与 InnoDB

  • 锁机制：

    • MyISAM：表级锁定（table-level locking）。
    • InnoDB：行级锁定（row-level locking）。

  • 事务支持：

    • MyISAM：不支持事务。
    • InnoDB：支持事务（ACID）。

  • 性能差异：

    • MyISAM：速度较快，特别是在只读或插入操作较多时。
    • InnoDB：对事务、数据完整性和外键的支持使其适用于大型应用，但插入操作可能较慢。

  • 默认存储引擎：

    • Linux 上默认是 MyISAM，Windows 上通常是 InnoDB。

MyISAM

优点：

  1. 支持全文搜索（Fulltext Search）。
  2. 支持几何数据类型（Geometric Datatypes）。
  3. 读取速度通常更快。
  4. 支持自动更新 AUTO_INCREMENT 列，效率提升约 10%。

缺点：

  1. 仅支持表级锁定，无法进行更精细的并发控制。
  2. 不支持外键。
  3. 在断电后表检查和恢复速度较慢，不适合高可用性场景。

InnoDB

优点：

  1. 支持事务，符合 ACID 标准。
  2. 支持外键。
  3. 行级锁定提供更高的并发性。
  4. 内置缓冲池，可在主内存中缓存数据和索引。
  5. 文件大小不受操作系统限制（即使系统限制为 2GB，表仍可更大）。
  6. 提供快速可靠的断电恢复。

缺点：

  1. 数据存储空间需求更高。
  2. 事务的同步机制增加了写操作的开销。
  3. 索引创建速度较慢，建议在数据导入后创建索引。

合并表（Merge Table）

定义：
合并表是一组相同的 MyISAM 表，这些表可以作为一个整体使用。
特性：

  • 所有表的列和索引结构必须完全相同。
  • 合并表可以解决 MySQL 或操作系统的文件大小限制问题，因为限制只影响单个 MyISAM 数据文件，不影响整个合并表。

示例：

CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) ENGINE=MyISAM;
CREATE TABLE delhi (first_name VARCHAR(30), amount INT(10)) ENGINE=MyISAM;
CREATE TABLE total (first_name VARCHAR(30), amount INT(10)) ENGINE=MERGE UNION=(mumbai, delhi);

内存表（MEMORY/HEAP）

  • 历史：
    • 在 MySQL 4.1 之前称为 HEAP。
    • 在 MySQL 4.1 之后改名为 MEMORY。
  • 特性：
    • 数据存储在内存中，速度快，但断电时数据会丢失。
    • 适用于临时数据的高性能查询。

总结

  • MyISAM：适合需要快速读取和全文搜索的场景。
  • InnoDB：适合需要事务支持、数据完整性和高并发的应用。
  • 合并表：适合跨越文件大小限制或逻辑上需要合并多个表的场景。
  • 内存表：适合临时表和对速度要求极高的数据处理任务。

针对不同需求选择合适的存储引擎，能有效提升系统性能和可靠性。

存储引擎：BDB 和 BLACKHOLE

BDB（BerkeleyDB）

  • 别名：BDB, BerkeleyDB
  • 状态：从 MySQL 5.1 开始被移除，原因是使用率低。

BDB 是 SleepyCat 开发的一系列免费嵌入式数据库管理系统，后来被 Oracle 收购。SleepyCat 提供了用于 MySQL 的 BDB 存储引擎。

  • 支持功能：
    • 事务（Transactions）。
    • 页级锁定（Page-level locking）。
  • 局限性：
    • 在 MySQL 内部有许多限制。

BLACKHOLE

  • 特性：
    • 丢弃所有存储在其中的数据，但仍会写入二进制日志（binary log）。
    • 适用于复制扩展场景，或用于安全的二进制日志过滤场景。
    • 可用于基准测试服务器高层功能。

其他存储引擎

CSV

  • 简单的逗号分隔值（CSV）存储引擎，数据以 CSV 格式存储。
  • 用途：
    • 数据与支持 CSV 的其他应用程序共享。
  • 限制：
    • 不支持索引。

EXAMPLE

  • 用于开发者的占位存储引擎。

ISAM

  • 仅用于兼容 MySQL 3.23 之前的版本，已在 5.1 中被移除。

存储引擎的元数据

显示存储引擎信息

可以通过以下 SQL 查询获取存储引擎的相关元数据：

SHOW STORAGE ENGINES;

返回结果包含以下列：

  1. Engine：存储引擎的名称。
  2. Support：存储引擎是否被支持：
    • DEFAULT：支持，并且是默认存储引擎。
    • YES：支持。
    • DISABLED：已编译，但启动 MySQL 时禁用了该引擎。
    • NO：不支持。
  3. Comment：存储引擎的简要描述。
  4. Transactions：是否支持事务（MySQL 5.1 添加）。
  5. XA：是否支持 XA 事务（MySQL 5.1 添加）。
  6. Savepoints：是否支持保存点和回滚（MySQL 5.1 添加）。

INFORMATION_SCHEMA 的 ENGINES 表

  • ENGINES 是 INFORMATION_SCHEMA 数据库中的虚拟表。
  • 其列与 SHOW ENGINES 返回的结果相同。
  • 新增时间：MySQL 5.1.5。

获取更多信息

可以使用 HELP 命令查询存储引擎的详细信息：

HELP 'myisam';

或在命令行中省略引号：

help myisam \g

修改存储引擎

为新表指定存储引擎

CREATE TABLE ... ENGINE=InnoDB;

如果未指定 ENGINE，则使用默认存储引擎（通常为 MyISAM）。可以通过以下方式更改默认存储引擎：

  1. 在会话中设置：

    SET storage_engine=InnoDB;

  2. 在 my.cnf 配置文件中修改：

    default-storage-engine=InnoDB

更改现有表的存储引擎

ALTER TABLE `stats` ENGINE=MyISAM;

mysql_convert_table_format 工具

mysql_convert_table_format 是 MySQL 提供的工具，用于将指定数据库中的所有表转换为另一种存储引擎。
语法：

mysql_convert_table_format [options] database

参数说明：

  • --help：显示帮助并退出。
  • --version：显示版本号并退出。
  • --host=host：指定 MySQL 运行的主机，默认是 localhost。
  • --port=port：指定 TCP 端口。
  • --user=user：指定用户名。
  • --password=password：指定密码（不安全，建议使用选项文件）。
  • --type=storage_engine：目标存储引擎类型。
  • --force：在发生错误时继续执行。
  • --verbose：显示详细的转换信息。

示例：

mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test
  • 此命令将数据库 test 中的所有表转换为 InnoDB 存储引擎。
  • 如果某些表无法转换，脚本会跳过这些表并继续转换其他表（--force 参数）。
最后修改: 2025年01月17日 星期五 19:30