MySQL与Oracle数据库存储架构对比总结

研究契机

在优化任务中，对日志表的配置过程中偶然了解到Oracle的页大小并不是默认的16KiB，Oracle在主存读取中并不是以页（Page）作为单位读取的，因为Oracle数据库并不是像MySQL那样使用了存储引擎，取而代之的是，Oracle是使用了表空间（Tablespace）这一概念，因此抱着查漏补缺的态度下来通过浏览其他博客资料学习进行了研究，以下是它们存储架构的上的对比总结。

目录

1. 数据库架构
2. 存储机制
3. 表空间与存储引擎
4. 数据文件管理
5. 事务处理机制
6. 案例

1. 数据库架构对比

1.1 MySQL架构模型

1.2 Oracle架构模型

架构差异对比表：

2. 存储机制

2.1 MySQL存储引擎机制

引擎类型示例：

SHOW ENGINES;

-- 输出示例
+--------------------+---------+
| Engine             | Support |
+--------------------+---------+
| InnoDB             | DEFAULT |
| MyISAM             | YES     |
| MEMORY             | YES     |
| CSV                | YES     |
+--------------------+---------+

引擎切换示例：

CREATE TABLE my_table (
    id INT PRIMARY KEY
) ENGINE=MyISAM;

2.2 Oracle表空间机制

表空间类型：

• SYSTEM表空间
• SYSAUX表空间
• UNDO表空间
• TEMP表空间
• 用户表空间

表空间创建示例：

CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/APP/app_data01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

3. 表空间与存储引擎

3.1 Oracle表空间技术解析

表空间结构示意图：

表空间
├─ 数据文件（.dbf）
├─ 区（Extent）
│    ├─ 数据块（8KB-32KB）
├─ 段（Segment）
│    ├─ 数据段
│    ├─ 索引段
│    ├─ 临时段

表空间管理操作：

-- 添加数据文件
ALTER TABLESPACE app_data 
ADD DATAFILE '/u02/oradata/APP/app_data02.dbf' SIZE 50M;

-- 表空间扩容
ALTER DATABASE DATAFILE 
'/u01/oradata/APP/app_data01.dbf' RESIZE 200M;

-- 查看表空间信息
SELECT tablespace_name, status, contents 
FROM dba_tablespaces;

3.2 MySQL存储引擎技术解析

InnoDB存储结构：

表空间（ibdata1）
├─ 段（Segment）
│    ├─ 区（Extent，1MB）
│    │    ├─ 页（Page，16KB）
├─ 双写缓冲区
├─ 回滚段

引擎特性对比：

4. 数据文件管理

4.1 Oracle数据文件管理

文件类型：

• 控制文件（.ctl）
• 数据文件（.dbf）
• 重做日志文件（.log）
• 参数文件（.ora）

表空间与文件关系：

SELECT 
    t.tablespace_name,
    f.file_name,
    f.bytes/1024/1024 "Size(MB)",
    f.autoextensible
FROM 
    dba_tablespaces t
JOIN 
    dba_data_files f ON t.tablespace_name = f.tablespace_name;

4.2 MySQL数据文件管理

InnoDB文件结构：

ibdata1        # 系统表空间
ib_logfile0    # 重做日志
ib_logfile1
database_name/
   table_name.ibd  # 独立表空间
   table_name.frm  # 表结构

文件配置参数：

# my.cnf配置示例
[mysqld]
innodb_data_file_path = ibdata1:12M:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 256M

5. 事务处理机制

5.1 Oracle事务管理

UNDO表空间管理：

-- 查看UNDO信息
SELECT 
    tablespace_name, 
    status, 
    retention 
FROM dba_tablespaces 
WHERE contents = 'UNDO';

-- 调整UNDO保留时间
ALTER SYSTEM SET undo_retention = 1800;

事务隔离级别：

-- 设置隔离级别
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

5.2 MySQL事务实现

InnoDB事务示例：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

事务状态监控：

SHOW ENGINE INNODB STATUS;

6. 案例

案例1：Oracle表空间优化

-- 创建自动扩展表空间
CREATE TABLESPACE big_data
DATAFILE '/u01/oradata/BIG/big_data01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 32T;

-- 分区表存储配置
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE
) PARTITION BY RANGE (sale_date)
(
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD')) 
       TABLESPACE ts_sales_q1,
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2025-02-09','YYYY-MM-DD')) 
       TABLESPACE ts_sales_q2
);

案例2：MySQL存储引擎迁移

-- 迁移MyISAM表到InnoDB
ALTER TABLE legacy_table ENGINE=InnoDB;

-- 批量迁移脚本
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') 
FROM information_schema.tables 
WHERE table_schema = 'mydb' 
AND engine = 'MyISAM';

案例3：Oracle表空间恢复

-- 表空间脱机
ALTER TABLESPACE app_data OFFLINE IMMEDIATE;

-- 数据文件恢复
HOST cp /backup/app_data01.dbf /u01/oradata/APP/

-- 表空间联机
ALTER TABLESPACE app_data ONLINE;

总结存储架构设计哲学上的差异

1. 扩展性设计：

   • MySQL通过存储引擎实现垂直扩展
   • Oracle通过表空间分区实现水平扩展

2. 内存管理机制：

   • Oracle SGA自动管理
   • MySQL缓冲池优化策略