Oracle/MySQL/PostgreSQL并发创建索引测试报告

Posted on 2026-03-20 18:29 a-speed 阅读(8) 评论(0) 收藏举报

数据库并发测试报告：批量更新与索引创建

测试概述

本测试旨在对比 Oracle、MySQL、PostgreSQL 三种主流数据库在表被频繁批量更新时，另一个会话是否能够成功创建索引，并分析各数据库的并发 DDL 机制差异。

测试环境

数据库	版本	操作系统
Oracle	AI Database 26ai Enterprise Edition 23.26.1.2.0	CentOS Stream 9
MySQL	8.0.45 (InnoDB)	Ubuntu 24.04.4 LTS
PostgreSQL	16.13	Ubuntu 24.04.4 LTS

测试场景

会话	操作	说明
会话1	批量更新数据	事务未提交，保持活跃
会话2	创建索引	在会话1更新期间尝试创建

一、Oracle 测试结果

1.1 测试一：ONLINE 模式

执行语句

CREATE INDEX idx_test_value ON test_concurrent(value) ONLINE;

测试时间线

时间	事件
09:22:55	会话1 开始批量更新
09:22:56	会话1 更新完成 50,000 行，事务未提交
09:23:04	会话2 开始创建索引
09:23:26	会话2 索引创建成功！ (耗时 22.46 秒)
09:23:27	会话1 事务提交

测试结果

INDEX_NAME          STATUS    INDEX_TYPE
─────────────────────────────────────────
SYS_C0030176        VALID     NORMAL    (主键索引)
IDX_TEST_VALUE      VALID     NORMAL    (新创建的索引)

结论：✅ ONLINE 模式创建索引成功！

1.2 测试二：普通模式（无 ONLINE）

执行语句

CREATE INDEX idx_test_value2 ON test_concurrent2(value);

测试结果

ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description:
"Synchronizes accesses to an object") because it is currently held by another
session. The resource being locked can be identified by 136643 ("Table")

结论：❌ 普通模式创建索引失败，报错 ORA-00054！

1.3 Oracle 锁机制对比

┌─────────────────────────────────────────────────────────────────────┐
│                    普通 CREATE INDEX                                 │
├─────────────────────────────────────────────────────────────────────┤
│  会话1: UPDATE → 持有 TM 锁 (Row Exclusive)                          │
│  会话2: CREATE INDEX → 需要 TM 锁 (Share/Exclusive)                  │
│          ↓                                                          │
│      ❌ 锁冲突 → ORA-00054 错误                                      │
└─────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│                    CREATE INDEX ... ONLINE                           │
├─────────────────────────────────────────────────────────────────────┤
│  会话1: UPDATE → 正常执行                                            │
│  会话2: CREATE INDEX ONLINE → 使用低级锁                              │
│          ↓                                                          │
│      ✅ 并行执行，索引创建成功                                        │
└─────────────────────────────────────────────────────────────────────┘

二、MySQL 测试结果

2.1 测试环境

innodb_version: 8.0.45
innodb_online_alter_log_max_size: 134217728 (128MB)

2.2 测试：默认模式创建索引

执行语句

CREATE INDEX idx_test_value ON test_concurrent(value);

测试时间线

时间	事件
09:34:22	会话1 开始批量更新 25,000 行
09:34:27	会话2 开始创建索引
09:35:21	会话2 索引创建成功！ (耗时 54 秒)
-	会话1 事务提交

测试结果

Table             Key_name         Column_name    Index_type
────────────────────────────────────────────────────────────
test_concurrent   PRIMARY          id             BTREE
test_concurrent   idx_test_value   value          BTREE

结论：✅ MySQL 默认支持并发创建索引（Online DDL）！

2.3 MySQL Online DDL 机制

MySQL 8.0 的 InnoDB 存储引擎默认支持 Online DDL，创建索引时：

阶段	说明
1. 初始化	短暂获取排他锁（瞬间完成）
2. 扫描数据	允许并发 DML 操作
3. 合并变更	将 DML 日志合并到新索引
4. 完成	短暂获取排他锁完成索引构建

关键参数：

innodb_online_alter_log_max_size：控制 Online DDL 期间 DML 日志大小

三、PostgreSQL 测试结果

3.1 测试一：普通模式创建索引

执行语句

CREATE INDEX idx_test_value ON test_concurrent(value);

测试时间线

时间	事件
09:36:31	会话1 开始批量更新 25,000 行
09:36:36	会话2 开始创建索引
09:37:31	会话2 索引创建成功！ (耗时 55 秒)
-	会话1 事务提交

测试结果

indexname              indexdef
─────────────────────────────────────────────────────────────────────
test_concurrent_pkey   CREATE UNIQUE INDEX ... ON test_concurrent (id)
idx_test_value         CREATE INDEX ... ON test_concurrent (value)

结论：✅ PostgreSQL 普通模式创建索引成功！

3.2 测试二：CONCURRENTLY 模式

执行语句

CREATE INDEX CONCURRENTLY idx_pg_value ON test_concurrent_pg(value);

测试时间线

时间	事件
09:38:42	会话1 开始批量更新 25,000 行
09:38:45	会话2 开始创建索引 (CONCURRENTLY)
09:39:25	会话2 索引创建成功！ (耗时 40 秒)
-	会话1 事务提交

结论：✅ CONCURRENTLY 模式创建索引成功！

3.3 PostgreSQL 索引创建模式对比

模式	锁类型	阻塞 DML	说明
`CREATE INDEX`	SHARE 锁	部分阻塞	等待现有事务完成，但不阻塞新事务
`CREATE INDEX CONCURRENTLY`	最小锁	不阻塞	完全并发，但耗时更长

四、三数据库对比总结

4.1 并发创建索引能力对比

数据库	默认行为	特殊语法	结果
Oracle	❌ 报错 ORA-00054	`ONLINE`	需显式指定 ONLINE
MySQL	✅ 支持 Online DDL	默认即 ONLINE	无需特殊语法
PostgreSQL	✅ 支持（等待现有事务）	`CONCURRENTLY`	默认部分并发，CONCURRENTLY 完全并发

4.2 锁机制对比

数据库	普通模式锁	并发模式锁
Oracle	TM 表级锁（排他）	低级锁，不阻塞 DML
MySQL	短暂排他锁 + 允许 DML	默认即 Online
PostgreSQL	SHARE 锁（等待现有事务）	最小锁，完全并发

4.3 行为差异图解

┌─────────────────────────────────────────────────────────────────────────────┐
│                        三数据库并发创建索引行为对比                            │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  Oracle (普通模式)          Oracle (ONLINE)                                 │
│  ┌─────────────────┐        ┌─────────────────┐                            │
│  │ UPDATE 持有锁    │        │ UPDATE 执行中    │                            │
│  │ CREATE INDEX    │        │ CREATE INDEX    │                            │
│  │      ↓          │        │      ↓          │                            │
│  │  ❌ ORA-00054   │        │  ✅ 成功创建     │                            │
│  └─────────────────┘        └─────────────────┘                            │
│                                                                             │
│  MySQL (默认)               PostgreSQL (默认)                               │
│  ┌─────────────────┐        ┌─────────────────┐                            │
│  │ UPDATE 执行中    │        │ UPDATE 执行中    │                            │
│  │ CREATE INDEX    │        │ CREATE INDEX    │                            │
│  │      ↓          │        │      ↓          │                            │
│  │  ✅ 成功创建     │        │  ✅ 成功创建     │                            │
│  │  (Online DDL)   │        │  (等待事务后创建) │                            │
│  └─────────────────┘        └─────────────────┘                            │
│                                                                             │
│  PostgreSQL (CONCURRENTLY)                                                  │
│  ┌─────────────────┐                                                        │
│  │ UPDATE 执行中    │                                                        │
│  │ CREATE INDEX    │                                                        │
│  │   CONCURRENTLY  │                                                        │
│  │      ↓          │                                                        │
│  │  ✅ 完全并发     │                                                        │
│  └─────────────────┘                                                        │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

五、最佳实践建议

5.1 Oracle

场景	推荐语法
生产环境	`CREATE INDEX idx ON table(col) ONLINE;`
大表索引	`CREATE INDEX idx ON table(col) ONLINE PARALLEL 4;`
维护窗口	`CREATE INDEX idx ON table(col);` (更快)

5.2 MySQL

场景	推荐语法
生产环境	`CREATE INDEX idx ON table(col);` (默认 Online)
大表索引	调整 `innodb_online_alter_log_max_size`
监控进度	`SHOW PROCESSLIST;` 或 Performance Schema

5.3 PostgreSQL

场景	推荐语法
生产环境	`CREATE INDEX CONCURRENTLY idx ON table(col);`
维护窗口	`CREATE INDEX idx ON table(col);` (更快)
注意事项	CONCURRENTLY 不能在事务块中执行

六、测试结论

6.1 核心发现

Oracle：必须显式使用 ONLINE 关键字才能并发创建索引，否则会因锁冲突报错 ORA-00054。
MySQL：InnoDB 存储引擎默认支持 Online DDL，创建索引时自动允许并发 DML 操作，无需特殊语法。
PostgreSQL：默认模式下会等待现有事务完成后创建索引，使用 CONCURRENTLY 可实现完全并发。

6.2 生产建议

数据库	生产环境建议
Oracle	必须使用 ONLINE，避免阻塞业务
MySQL	默认安全，注意监控 Online DDL 日志大小
PostgreSQL	推荐使用 CONCURRENTLY，避免长时间锁表

6.3 风险提示

风险	说明
长时间事务	可能导致索引创建等待或失败
资源消耗	并发创建索引会增加系统负载
空间不足	索引创建需要额外存储空间

附录：测试脚本

A.1 Oracle 测试脚本

-- 会话1：批量更新
BEGIN
    FOR i IN 1..50000 LOOP
        UPDATE test_concurrent SET value = value + 1 WHERE id = i;
    END LOOP;
END;
/
-- 不提交

-- 会话2：创建索引
CREATE INDEX idx_test_value ON test_concurrent(value) ONLINE;

A.2 MySQL 测试脚本

-- 会话1：批量更新
START TRANSACTION;
UPDATE test_concurrent SET value = value + 1 WHERE id <= 25000;
-- 不提交

-- 会话2：创建索引（默认 Online）
CREATE INDEX idx_test_value ON test_concurrent(value);

A.3 PostgreSQL 测试脚本

-- 会话1：批量更新
BEGIN;
UPDATE test_concurrent SET value = value + 1 WHERE id <= 25000;
-- 不提交

-- 会话2：创建索引
CREATE INDEX CONCURRENTLY idx_test_value ON test_concurrent(value);

刷新页面返回顶部

A-speed

Oracle/MySQL/PostgreSQL并发创建索引测试报告

数据库并发测试报告：批量更新与索引创建

测试概述

测试环境

测试场景

一、Oracle 测试结果

1.1 测试一：ONLINE 模式

执行语句

测试时间线

测试结果

1.2 测试二：普通模式（无 ONLINE）

执行语句

测试结果

1.3 Oracle 锁机制对比

二、MySQL 测试结果

2.1 测试环境

2.2 测试：默认模式创建索引

执行语句

测试时间线

测试结果

2.3 MySQL Online DDL 机制

三、PostgreSQL 测试结果

3.1 测试一：普通模式创建索引

执行语句

测试时间线

测试结果

3.2 测试二：CONCURRENTLY 模式

执行语句

测试时间线

3.3 PostgreSQL 索引创建模式对比

四、三数据库对比总结

4.1 并发创建索引能力对比

4.2 锁机制对比

4.3 行为差异图解

五、最佳实践建议

5.1 Oracle

5.2 MySQL

5.3 PostgreSQL

六、测试结论

6.1 核心发现

6.2 生产建议

6.3 风险提示

附录：测试脚本

A.1 Oracle 测试脚本

A.2 MySQL 测试脚本

A.3 PostgreSQL 测试脚本