PostgreSQL 的索引Ooracle、Mysql索引的类型对比和说明
目录
- PostgreSQL 的索引Ooracle、Mysql索引的类型对比和说明
- 一、索引类型对比概览
- 通用索引类型
- 特定数据库索引类型
- 二、索引类型对比总览
- 三、使用案例
- PostgreSQL
- Oracle
- MySQL
- 四、索引类型详解与使用案例
- 1. B-Tree 索引
- 2. Hash 索引
- 3. PostgreSQL 特有索引
- GIN 索引
- GiST 索引
- BRIN 索引
- 部分索引
- 表达式索引
- INCLUDE 索引
- 4. Oracle 特有索引
- Bitmap 索引
- Function-Based 索引
- Domain 索引
- 5. MySQL 特有索引
- Full-Text 全文索引
- Spatial 空间索引
- 前缀索引
- 五、索引选择建议
- 六、总结要点
- 一、索引类型对比概览
一、索引类型对比概览
通用索引类型
- B-Tree 索引：三大数据库都支持，最常用的索引类型，适用于等值查询、范围查询和排序操作
- Hash 索引：PostgreSQL 和 MySQL 支持，Oracle 不支持原生 Hash 索引
特定数据库索引类型
- PostgreSQL 特有：GIN、GiST、SP-GiST、BRIN、部分索引、表达式索引、INCLUDE 索引
- Oracle 特有：Bitmap 索引、Function-Based 索引、Domain 索引、分区索引
- MySQL 特有：Full-Text 全文索引、Spatial 空间索引、前缀索引
二、索引类型对比总览
| 数据库 | 索引类型 | 特点 | 使用场景 |
|---|---|---|---|
| PostgreSQL | B-Tree（默认） | 平衡树结构，支持范围查询、等值查询 | 常用主键、唯一约束、范围查询（<, <=, =, >=, >, BETWEEN） |
| Hash 索引 | 哈希存储，适合等值查询（Postgres 10+支持 WAL 持久化） | 精确匹配，等值查询，性能高于 B-Tree | |
| GIN (Generalized Inverted Index) | 倒排索引，适合数组、JSON、全文搜索 | 全文检索、数组字段查询、JSONB 查询 | |
| GiST (Generalized Search Tree) | 可扩展，支持模糊匹配、范围、空间数据 | 空间查询（PostGIS）、模糊匹配、相似度查询 | |
| BRIN (Block Range Index) | 轻量级索引，按块存储统计信息 | 大范围顺序扫描，大数据量但低选择性列（如日志时间） | |
| SP-GiST | 空间分区索引 | 非平衡数据结构，适合树型、空间数据 | |
| Partial Index | 部分索引 | 只索引满足条件的数据，节省空间 | |
| Expression Index | 表达式索引 | 针对表达式结果建索引，例如 lower(col) |
|
| Oracle | B-Tree 索引（默认） | 普通索引，支持等值、范围 | 主键、唯一约束、常见查询 |
| Bitmap 索引 | 位图存储，适合低基数字段 | 性别、状态标识、布尔值 | |
| Function-Based Index | 表达式索引 | 计算列、函数结果查询 | |
| Reverse Key Index | 键值反转 | 避免热点索引块（如递增序列） | |
| Clustered Index (IOT 索引组织表) | 表和索引存储在一起 | 主键访问非常快 | |
| Partitioned Index | 分区索引 | 大表分区场景 | |
| MySQL（以 InnoDB 为主） | B-Tree（聚簇索引+二级索引） | 聚簇索引（主键），二级索引叶子节点存储主键 | 主键、范围、等值 |
| Hash 索引（仅 Memory 引擎） | 哈希存储，非范围支持 | 内存表，等值查询 | |
| Fulltext 索引（MyISAM、InnoDB） | 全文搜索 | 文本搜索 | |
| Spatial Index (R-Tree) | 空间索引（MyISAM/InnoDB支持GIS） | 地理位置查询 | |
| Prefix Index | 字符串前缀索引 | 长字符串列的前缀匹配 | |
| Invisible Index (8.0+) | 不可见索引 | 索引调优实验 |
三、使用案例
PostgreSQL
-- 1. 普通 B-Tree 索引
CREATE INDEX idx_users_name ON users(name);-- 2. Hash 索引（适合等值）
CREATE INDEX idx_users_email_hash ON users USING hash(email);-- 3. GIN 索引（全文搜索）
CREATE INDEX idx_docs_content_gin ON docs USING gin(to_tsvector('english', content));
SELECT * FROM docs WHERE to_tsvector('english', content) @@ to_tsquery('database');-- 4. BRIN 索引（大范围顺序扫描，如日志时间）
CREATE INDEX idx_logs_time_brin ON logs USING brin(log_time);-- 5. Expression 索引（忽略大小写查询）
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'test@example.com';
Oracle
-- 1. 普通 B-Tree 索引
CREATE INDEX idx_emp_name ON employees(name);-- 2. Bitmap 索引（适合性别这种低基数字段）
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);-- 3. Function-Based Index
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'TOM';-- 4. Reverse Key Index
CREATE INDEX idx_emp_id_rev ON employees(id REVERSE);-- 5. IOT 索引组织表
CREATE TABLE emp_iot (emp_id NUMBER PRIMARY KEY,name VARCHAR2(50),dept_id NUMBER
) ORGANIZATION INDEX;
MySQL
-- 1. 普通 B-Tree 索引
CREATE INDEX idx_users_name ON users(name);-- 2. Fulltext 索引
CREATE FULLTEXT INDEX idx_articles_content ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('database');-- 3. 空间索引 (InnoDB GIS)
CREATE SPATIAL INDEX idx_locations_geom ON locations(geom);-- 4. 前缀索引
CREATE INDEX idx_users_email_prefix ON users(email(10));-- 5. 不可见索引（8.0+）
CREATE INDEX idx_users_phone ON users(phone) INVISIBLE;
四、索引类型详解与使用案例
1. B-Tree 索引
适用场景：等值查询、范围查询、排序
PostgreSQL 示例：
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
Oracle 示例：
CREATE INDEX idx_employees_last_name ON employees(last_name);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);
MySQL 示例：
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
2. Hash 索引
适用场景：仅适用于等值查询，不支持范围查询和排序
PostgreSQL 示例：
CREATE INDEX idx_users_session_hash ON users USING hash(session_id);
MySQL 示例：
CREATE INDEX idx_users_session_hash ON users USING hash(session_id);
注意：Oracle 没有原生 Hash 索引，但可以通过创建 Hash Cluster 实现类似功能
3. PostgreSQL 特有索引
GIN 索引
适用场景：数组、JSONB、全文搜索等多值数据类型
案例：查询包含特定标签的文章
CREATE INDEX idx_articles_tags ON articles USING gin(tags);
-- 查询
SELECT * FROM articles WHERE tags @> ARRAY['database', 'postgres'];
GiST 索引
适用场景：地理位置 (GIS)、全文搜索、范围重叠查询
案例：查找指定区域内的 POI
CREATE INDEX idx_pois_geom ON pois USING gist(geom);
-- 查询
SELECT * FROM pois WHERE ST_Contains(ST_MakeBox2D(ST_Point(116.3, 39.9), ST_Point(116.4, 40.0)), geom);
BRIN 索引
适用场景：大表、按时间顺序存储的数据，如日志表
案例：日志表按时间查询
CREATE INDEX idx_logs_created_at ON logs USING brin(created_at);
-- 查询
SELECT * FROM logs WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02';
部分索引
适用场景：只对表的一部分数据建立索引，减少索引大小
案例：只对活跃用户建立索引
CREATE INDEX idx_active_users_email ON users(email) WHERE status = 'active';
表达式索引
适用场景：对函数结果或表达式建立索引
案例：不区分大小写的邮箱查询
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- 查询
SELECT * FROM users WHERE lower(email) = 'user@example.com';
INCLUDE 索引
适用场景：覆盖查询，避免回表
案例：查询用户信息，避免回表
CREATE INDEX idx_users_email_inc ON users(email) INCLUDE (name, status);
4. Oracle 特有索引
Bitmap 索引
适用场景：低基数列 (值种类少)，如性别、状态，适合数据仓库
案例：查询特定状态的订单
CREATE BITMAP INDEX idx_orders_status ON orders(status);
Function-Based 索引
适用场景：对函数结果建立索引，类似 PostgreSQL 的表达式索引
案例：不区分大小写的姓名查询
CREATE INDEX idx_employees_last_name_upper ON employees(UPPER(last_name));
Domain 索引
适用场景：自定义数据类型的索引，如全文搜索、空间数据
案例：Oracle Text 全文索引
CREATE INDEX idx_documents_text ON documents(text) INDEXTYPE IS CTXSYS.CONTEXT;
5. MySQL 特有索引
Full-Text 全文索引
适用场景：文本内容的关键词搜索
案例：文章内容搜索
CREATE FULLTEXT INDEX idx_articles_content ON articles(content);
-- 查询
SELECT * FROM articles WHERE MATCH(content) AGAINST('database postgres');
Spatial 空间索引
适用场景：地理位置数据查询
案例：查找附近的 POI
CREATE SPATIAL INDEX idx_pois_location ON pois(location);
-- 查询
SELECT * FROM pois WHERE ST_DWithin(location, ST_GeomFromText('POINT(116.3 39.9)'), 1000);
前缀索引
适用场景：长字符串列，只对前 N 个字符建立索引
案例：对长 URL 建立前缀索引
CREATE INDEX idx_urls_path_prefix ON urls(path(20));
五、索引选择建议
- 1.高频查询字段优先：为 WHERE、JOIN、ORDER BY 中的字段建立索引
- 2.组合索引顺序：遵循 "等值查询字段在前，范围查询字段在后" 的原则
- 3.避免过度索引：索引会增加写入和更新成本，只建立必要的索引
- 4.根据数据特性选择索引类型：
- 文本搜索：PostgreSQL 用 GIN/GiST，MySQL 用 Full-Text，Oracle 用 Domain
- 地理位置：PostgreSQL 用 GiST，MySQL 用 Spatial，Oracle 用 Spatial
- 多值数据：PostgreSQL 用 GIN，其他数据库需额外设计
- 5.定期维护索引：
- PostgreSQL: REINDEX
- Oracle: ALTER INDEX REBUILD
- MySQL: OPTIMIZE TABLE
六、总结要点
- 1.PostgreSQL 索引类型最丰富，适合复杂查询（全文、数组、空间、表达式、部分索引）。
- 2.Oracle 在数据仓库场景优势明显（Bitmap、IOT、分区索引）。
- 3.MySQL 以 B-Tree 为核心，配合 Fulltext/Spatial/Prefix 实现扩展功能。
速记口诀
- OLTP 高并发写：三家默认 B-tree，PG 记得加 INCLUDE 做覆盖。
- OLAP 低基数：Oracle Bitmap 最快，PG/MySQL 用 Partial B-tree 顶。
- 全文/JSON/数组：PG 的 GIN 一把梭，Oracle/MySQL 各需组件或虚拟列。
- 超大型时序：PG BRIN 体积忽略，Oracle/MySQL 靠分区 Local 索引。
- 空间数据：都要 R-tree，PG 装 PostGIS，Oracle 买 Spatial，MySQL 8.0 自带。