MySQL数据库
Completion requirements
表的存储引擎(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
优点:
- 支持全文搜索(Fulltext Search)。
- 支持几何数据类型(Geometric Datatypes)。
- 读取速度通常更快。
- 支持自动更新
AUTO_INCREMENT
列,效率提升约 10%。
缺点:
- 仅支持表级锁定,无法进行更精细的并发控制。
- 不支持外键。
- 在断电后表检查和恢复速度较慢,不适合高可用性场景。
InnoDB
优点:
- 支持事务,符合 ACID 标准。
- 支持外键。
- 行级锁定提供更高的并发性。
- 内置缓冲池,可在主内存中缓存数据和索引。
- 文件大小不受操作系统限制(即使系统限制为 2GB,表仍可更大)。
- 提供快速可靠的断电恢复。
缺点:
- 数据存储空间需求更高。
- 事务的同步机制增加了写操作的开销。
- 索引创建速度较慢,建议在数据导入后创建索引。
合并表(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;
返回结果包含以下列:
- Engine:存储引擎的名称。
- Support:存储引擎是否被支持:
DEFAULT
:支持,并且是默认存储引擎。YES
:支持。DISABLED
:已编译,但启动 MySQL 时禁用了该引擎。NO
:不支持。
- Comment:存储引擎的简要描述。
- Transactions:是否支持事务(MySQL 5.1 添加)。
- XA:是否支持 XA 事务(MySQL 5.1 添加)。
- 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)。可以通过以下方式更改默认存储引擎:
-
在会话中设置:
SET storage_engine=InnoDB;
-
在
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
参数)。
Last modified: Friday, 17 January 2025, 7:30 PM