Oracle/MySQL/PostgreSQL索引对比实验详解

Posted on 2026-03-21 20:11 a-speed 阅读(9) 评论(0) 收藏举报

Oracle / MySQL / PostgreSQL 索引对比实验详解

概述

本文通过实际实验对比 Oracle、MySQL、PostgreSQL 三种主流数据库的索引特性，包括索引类型、创建语法、使用场景和性能差异。

一、索引类型对比

1.1 三数据库索引类型汇总

索引类型	Oracle	MySQL	PostgreSQL	说明
B-Tree	✅ 默认	✅ 默认	✅ 默认	标准索引，支持范围查询
Hash	❌	✅ Memory引擎	✅	仅支持等值查询
Bitmap	✅	❌	❌	位图索引，适合低基数列
全文索引	✅	✅	✅	全文搜索
空间索引	✅	✅	✅	GIS地理数据
函数索引	✅	✅ 8.0+	✅	基于表达式
反向键索引	✅	❌	❌	解决热点块问题
分区索引	✅	✅	✅	分区表索引
GIN	❌	❌	✅	数组、JSONB、全文
GiST	❌	❌	✅	几何、范围、全文
BRIN	❌	❌	✅	块范围索引
部分索引	✅	❌	✅	条件索引

二、Oracle 索引实验

2.1 实验环境

Oracle AI Database 26ai Enterprise Edition
版本: 23.26.1.2.0

2.2 创建测试数据

-- 创建测试表
CREATE TABLE idx_test (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    department VARCHAR2(50),
    salary NUMBER(10,2),
    status VARCHAR2(20),
    create_date DATE DEFAULT SYSDATE
);

-- 插入10万条测试数据
INSERT INTO idx_test SELECT level, 'Employee_'||level, 
    CASE WHEN MOD(level,5)=0 THEN '技术部'
         WHEN MOD(level,5)=1 THEN '销售部'
         WHEN MOD(level,5)=2 THEN '财务部'
         WHEN MOD(level,5)=3 THEN '人事部'
         ELSE '行政部' END,
    10000 + MOD(level, 10000),
    CASE WHEN MOD(level,3)=0 THEN 'ACTIVE'
         WHEN MOD(level,3)=1 THEN 'INACTIVE'
         ELSE 'PENDING' END,
    SYSDATE - MOD(level, 365)
FROM dual CONNECT BY level <= 100000;

2.3 创建各种索引

-- 1. B-Tree索引（默认）
CREATE INDEX idx_test_name ON idx_test(name);

-- 2. 位图索引（低基数列）
CREATE BITMAP INDEX idx_test_status ON idx_test(status);

-- 3. 函数索引
CREATE INDEX idx_test_upper_name ON idx_test(UPPER(name));

-- 4. 组合索引
CREATE INDEX idx_test_dept_sal ON idx_test(department, salary);

-- 5. 反向键索引
CREATE INDEX idx_test_id_rev ON idx_test(id) REVERSE;

2.4 Oracle索引特点

特性	说明
位图索引	Oracle独有，适合低基数列（如性别、状态）
反向键索引	解决顺序插入热点块问题
函数索引	支持复杂表达式
分区索引	支持本地/全局分区索引

三、MySQL 索引实验

3.1 实验环境

MySQL 8.0.45
存储引擎: InnoDB

3.2 创建测试数据

-- 创建测试表
CREATE TABLE idx_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    status VARCHAR(20),
    create_date DATE,
    description TEXT,
    INDEX idx_name (name)
);

-- 插入10万条测试数据
INSERT INTO idx_test (name, department, salary, status, create_date, description)
SELECT 
    CONCAT('Employee_', seq),
    CASE WHEN seq % 5 = 0 THEN '技术部' ... END,
    10000 + (seq % 10000),
    CASE WHEN seq % 3 = 0 THEN 'ACTIVE' ... END,
    DATE_SUB(CURDATE(), INTERVAL (seq % 365) DAY),
    CONCAT('Description for employee ', seq)
FROM ...;

3.3 创建各种索引

-- 1. 普通B-Tree索引
CREATE INDEX idx_department ON idx_test(department);

-- 2. 组合索引
CREATE INDEX idx_dept_sal ON idx_test(department, salary);

-- 3. 前缀索引
CREATE INDEX idx_name_prefix ON idx_test(name(20));

-- 4. 全文索引
CREATE FULLTEXT INDEX idx_fulltext ON idx_test(description);

-- 5. 函数索引（MySQL 8.0+）
CREATE INDEX idx_lower_name ON idx_test((LOWER(name)));

-- 6. 降序索引
CREATE INDEX idx_sal_desc ON idx_test(salary DESC);

3.4 MySQL索引特点

特性	说明
前缀索引	支持字符串前缀，节省空间
降序索引	MySQL 8.0+支持真正的降序索引
函数索引	MySQL 8.0+支持，底层是虚拟列
全文索引	InnoDB和MyISAM都支持

四、PostgreSQL 索引实验

4.1 实验环境

PostgreSQL 16.13

4.2 创建测试数据

-- 创建测试表
CREATE TABLE idx_test (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    status VARCHAR(20),
    create_date DATE,
    description TEXT,
    tags TEXT[]
);

-- 插入10万条测试数据
INSERT INTO idx_test (name, department, salary, status, create_date, description, tags)
SELECT 
    'Employee_' || g,
    CASE WHEN g % 5 = 0 THEN '技术部' ... END,
    10000 + (g % 10000),
    CASE WHEN g % 3 = 0 THEN 'ACTIVE' ... END,
    CURRENT_DATE - (g % 365),
    'Description for employee ' || g,
    ARRAY['tag' || (g % 10), 'category' || (g % 5)]
FROM generate_series(1, 100000) AS g;

4.3 创建各种索引

-- 1. B-Tree索引（默认）
CREATE INDEX idx_name ON idx_test(name);

-- 2. Hash索引
CREATE INDEX idx_status_hash ON idx_test USING hash(status);

-- 3. 组合索引
CREATE INDEX idx_dept_sal ON idx_test(department, salary);

-- 4. 函数索引
CREATE INDEX idx_lower_name ON idx_test(LOWER(name));

-- 5. 部分索引（条件索引）
CREATE INDEX idx_active_emp ON idx_test(name) WHERE status = 'ACTIVE';

-- 6. GIN索引（数组）
CREATE INDEX idx_tags_gin ON idx_test USING gin(tags);

-- 7. 表达式索引
CREATE INDEX idx_year ON idx_test(EXTRACT(YEAR FROM create_date));

4.4 PostgreSQL索引特点

特性	说明
GIN索引	支持数组、JSONB、全文搜索
GiST索引	支持几何、范围、全文搜索
BRIN索引	块范围索引，适合大表有序数据
部分索引	只索引满足条件的数据
表达式索引	支持任意表达式

五、索引使用场景对比

5.1 等值查询

数据库	推荐索引	示例
Oracle	B-Tree / Bitmap	`WHERE status = 'ACTIVE'`
MySQL	B-Tree	`WHERE name = 'Employee_1000'`
PostgreSQL	B-Tree / Hash	`WHERE name = 'Employee_1000'`

5.2 范围查询

数据库	推荐索引	示例
Oracle	B-Tree	`WHERE salary > 15000`
MySQL	B-Tree	`WHERE salary BETWEEN 10000 AND 20000`
PostgreSQL	B-Tree	`WHERE salary > 15000`

5.3 模糊查询

数据库	推荐索引	示例
Oracle	函数索引	`WHERE UPPER(name) = 'EMPLOYEE'`
MySQL	前缀索引 / 函数索引	`WHERE name LIKE 'Employee%'`
PostgreSQL	函数索引 / pg_trgm扩展	`WHERE name LIKE '%employee%'`

5.4 全文搜索

数据库	推荐索引	示例
Oracle	Context索引	`CONTAINS(description, 'keyword')`
MySQL	FULLTEXT索引	`MATCH(description) AGAINST('keyword')`
PostgreSQL	GIN + tsvector	`to_tsvector(description) @@ to_tsquery('keyword')`

5.5 数组/JSON查询

数据库	推荐索引	示例
Oracle	❌ 不支持	-
MySQL	❌ 不支持	-
PostgreSQL	GIN索引	`WHERE tags @> ARRAY['tag1']`

六、索引创建语法对比

6.1 基本索引创建

-- Oracle
CREATE INDEX idx_name ON table_name(column_name);
CREATE BITMAP INDEX idx_name ON table_name(column_name);

-- MySQL
CREATE INDEX idx_name ON table_name(column_name);
CREATE INDEX idx_name ON table_name(column_name(20));  -- 前缀索引

-- PostgreSQL
CREATE INDEX idx_name ON table_name(column_name);
CREATE INDEX idx_name ON table_name USING hash(column_name);
CREATE INDEX idx_name ON table_name USING gin(column_name);

6.2 组合索引创建

-- 三数据库语法相同
CREATE INDEX idx_name ON table_name(col1, col2, col3);

6.3 函数/表达式索引

-- Oracle
CREATE INDEX idx_name ON table_name(UPPER(column_name));

-- MySQL 8.0+
CREATE INDEX idx_name ON table_name((LOWER(column_name)));

-- PostgreSQL
CREATE INDEX idx_name ON table_name(LOWER(column_name));
CREATE INDEX idx_name ON table_name((EXTRACT(YEAR FROM date_col)));

6.4 条件/部分索引

-- Oracle（函数索引模拟）
CREATE INDEX idx_name ON table_name(CASE WHEN status='A' THEN id END);

-- MySQL（不支持）

-- PostgreSQL
CREATE INDEX idx_name ON table_name(column_name) WHERE status = 'ACTIVE';

七、索引维护对比

7.1 重建索引

数据库	语法
Oracle	`ALTER INDEX idx_name REBUILD;`
MySQL	`ANALYZE TABLE table_name;` 或 `OPTIMIZE TABLE table_name;`
PostgreSQL	`REINDEX INDEX idx_name;` 或 `REINDEX TABLE table_name;`

7.2 统计信息更新

数据库	语法
Oracle	`EXEC DBMS_STATS.GATHER_TABLE_STATS('schema', 'table');`
MySQL	`ANALYZE TABLE table_name;`
PostgreSQL	`ANALYZE table_name;`

7.3 索引监控

-- Oracle: 查看索引使用情况
SELECT * FROM v$object_usage WHERE index_name = 'IDX_NAME';

-- MySQL: 查看索引统计
SHOW INDEX FROM table_name;

-- PostgreSQL: 查看索引大小
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) 
FROM pg_stat_user_indexes;

八、性能对比总结

8.1 索引类型丰富度

PostgreSQL > Oracle > MySQL

8.2 特殊场景支持

场景	最佳选择
低基数列（性别、状态）	Oracle Bitmap
数组/JSON查询	PostgreSQL GIN
全文搜索	三者都支持，PostgreSQL最灵活
空间数据	三者都支持
大表有序数据	PostgreSQL BRIN

8.3 索引使用建议

数据库	建议
Oracle	善用位图索引处理低基数列，使用函数索引优化复杂查询
MySQL	合理使用前缀索引，注意组合索引最左前缀原则
PostgreSQL	根据数据类型选择合适的索引类型，善用部分索引节省空间

九、实验结论

9.1 核心差异

对比项	Oracle	MySQL	PostgreSQL
索引类型数量	6种	4种	6种+
独有特性	位图索引、反向键	前缀索引	GIN、GiST、BRIN、部分索引
函数索引	✅ 强大	✅ 8.0+	✅ 强大
数组索引	❌	❌	✅ GIN
条件索引	模拟	❌	✅ 原生支持

9.2 选型建议

场景	推荐数据库
传统OLTP业务	三者皆可
数据仓库/OLAP	Oracle（位图索引）
JSON/数组查询	PostgreSQL
全文搜索	PostgreSQL / MySQL
GIS应用	PostgreSQL（PostGIS）

刷新页面返回顶部

A-speed

Oracle/MySQL/PostgreSQL索引对比实验详解

Oracle / MySQL / PostgreSQL 索引对比实验详解

概述

一、索引类型对比

1.1 三数据库索引类型汇总

二、Oracle 索引实验

2.1 实验环境

2.2 创建测试数据

2.3 创建各种索引

2.4 Oracle索引特点

三、MySQL 索引实验

3.1 实验环境

3.2 创建测试数据

3.3 创建各种索引

3.4 MySQL索引特点

四、PostgreSQL 索引实验

4.1 实验环境

4.2 创建测试数据

4.3 创建各种索引

4.4 PostgreSQL索引特点

五、索引使用场景对比

5.1 等值查询

5.2 范围查询

5.3 模糊查询

5.4 全文搜索

5.5 数组/JSON查询

六、索引创建语法对比

6.1 基本索引创建

6.2 组合索引创建

6.3 函数/表达式索引

6.4 条件/部分索引

七、索引维护对比

7.1 重建索引

7.2 统计信息更新

7.3 索引监控

八、性能对比总结

8.1 索引类型丰富度

8.2 特殊场景支持

8.3 索引使用建议

九、实验结论

9.1 核心差异

9.2 选型建议