⚡

数据库索引策略 database-indexing-strategy

本技能涉及数据库索引策略的设计和实施，包括索引创建、查询性能优化、索引类型选择、复合和部分索引设计、索引维护和监控等，旨在提高数据库查询效率和数据完整性。

数据工程 0 次安装 0 次浏览 更新于 3/3/2026

数据库索引策略

概览

设计全面的索引策略以提高查询性能，减少锁争用，并维护数据完整性。涵盖索引类型、设计模式和维护程序。

何时使用

  • 索引创建和规划
  • 通过索引优化查询性能
  • 索引类型选择（B-tree, Hash, GiST, BRIN）
  • 复合和部分索引设计
  • 索引维护和监控
  • 存储优化与索引
  • 全文搜索索引设计

索引类型和用例

PostgreSQL索引类型

B-tree索引（默认）：

-- 标准等值和范围查询
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- 复合索引用于多列查询
CREATE INDEX idx_orders_user_status
ON orders(user_id, status)
WHERE cancelled_at IS NULL;

Hash索引：

-- 仅精确匹配查询
CREATE INDEX idx_product_sku USING hash ON products(sku);

-- 适用于大型文本字段的等值查找
CREATE INDEX idx_uuid_hash USING hash ON sessions(session_id);

BRIN索引（块范围）：

-- 适用于具有单调递增列的大型表
CREATE INDEX idx_events_timestamp USING brin ON events(created_at)
WITH (pages_per_range = 128);

-- 非常适合时间序列数据
CREATE INDEX idx_logs_timestamp USING brin
ON application_logs(log_timestamp);

GiST & GIN索引：

-- GiST用于空间数据和复杂类型
CREATE INDEX idx_locations_geom USING gist ON locations(geom);

-- GIN用于JSONB和数组列
CREATE INDEX idx_products_metadata USING gin ON products(metadata);
CREATE INDEX idx_user_tags USING gin ON users(tags);

MySQL索引类型

B-tree索引：

-- 大多数查询的标准索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

-- 大型列的前缀索引
CREATE INDEX idx_description_prefix
ON products(description(100));

FULLTEXT索引：

-- 文本列上的全文搜索
CREATE FULLTEXT INDEX idx_products_search
ON products(name, description);

-- 使用MATCH...AGAINST查询
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('laptop' IN BOOLEAN MODE);

空间索引：

-- 用于地理数据
CREATE SPATIAL INDEX idx_locations
ON locations(geom);

索引设计模式

单列索引

PostgreSQL：

-- 仅对活动记录的过滤索引
CREATE INDEX idx_users_active
ON users(created_at)
WHERE deleted_at IS NULL;

-- 用于LIMIT查询的降序
CREATE INDEX idx_posts_published DESC
ON posts(published_at DESC)
WHERE status = 'published';

MySQL：

-- 简单的等值查找
CREATE INDEX idx_users_verified ON users(email_verified);

-- 数值列上的范围查询
CREATE INDEX idx_products_price ON products(price);

复合索引

PostgreSQL - 优化排序：

-- 顺序：等值列，然后范围，然后排序
-- 查询：WHERE user_id = X AND created_at > Y ORDER BY id
CREATE INDEX idx_optimal_composite
ON orders(user_id, created_at, id);

-- 覆盖索引以消除表访问
CREATE INDEX idx_covering_orders
ON orders(user_id, status, created_at)
INCLUDE (total, currency);

MySQL - 最左前缀：

-- MySQL使用最左前缀匹配
-- 可以被：(user_id), (user_id, status), (user_id, status, created_at)使用
CREATE INDEX idx_users_complex
ON users(user_id, status, created_at);

-- 对于查询：user_id + status + created_at
SELECT * FROM orders
WHERE user_id = 1 AND status = 'completed' AND created_at > '2024-01-01';

部分/过滤索引

PostgreSQL：

-- 仅索引活动产品
CREATE INDEX idx_active_products
ON products(category_id)
WHERE active = true;

-- 减少大型表的索引大小并提高性能
CREATE INDEX idx_not_cancelled_orders
ON orders(user_id, created_at)
WHERE status != 'cancelled';

-- 复杂的过滤条件
CREATE INDEX idx_vip_orders
ON orders(total DESC)
WHERE total > 10000 AND customer_type = 'vip';

表达式索引

PostgreSQL：

-- 在计算值上的索引
CREATE INDEX idx_users_email_lower
ON users(LOWER(email));

-- 启用不区分大小写的搜索
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- 日期提取索引
CREATE INDEX idx_orders_year
ON orders(EXTRACT(YEAR FROM created_at));

索引维护

PostgreSQL索引分析：

-- 检查索引大小和使用情况
SELECT schemaname, tablename, indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as size,
  idx_scan as scans,
  idx_tup_read as tuples_read
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找未使用的索引
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_toast%';

-- 重建碎片化索引
REINDEX INDEX idx_users_email;

MySQL索引统计：

-- 检查索引基数
SELECT object_schema, object_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema != 'mysql'
ORDER BY count_star DESC;

-- 更新表统计信息
ANALYZE TABLE users;
ANALYZE TABLE orders;

并发索引创建

PostgreSQL - 非阻塞索引创建：

-- 创建索引时不锁定表（PostgreSQL 9.2+）
CREATE INDEX CONCURRENTLY idx_new_column
ON large_table(new_column);

-- 安全生产
REINDEX INDEX CONCURRENTLY idx_products_price;

MySQL - 并发索引创建：

-- MySQL 8.0支持ALGORITHM=INPLACE与LOCK=NONE
ALTER TABLE users ADD INDEX idx_created (created_at),
ALGORITHM=INPLACE, LOCK=NONE;

-- 检查在线DDL进度
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

性能监控

PostgreSQL - 索引性能：

-- 扫描次数最多的前10个索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC LIMIT 10;

-- 具有高读取/扫描比率的索引
SELECT indexname, idx_scan, idx_tup_read,
  CASE WHEN idx_scan = 0 THEN 0
    ELSE ROUND(idx_tup_read::numeric / idx_scan, 2) END as efficiency
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY efficiency DESC;

MySQL - 索引统计：

-- 显示表索引信息
SHOW INDEX FROM products;

-- 检查基数（分布）
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'products'
ORDER BY SEQ_IN_INDEX;

索引创建清单

  • 使用EXPLAIN/EXPLAIN ANALYZE识别慢查询
  • 检查过滤列、JOIN条件、ORDER BY子句
  • 考虑索引顺序（等值 → 范围 → 排序）
  • 对大型表使用部分索引以减小大小
  • 为覆盖索引包含列
  • 创建后监控索引使用情况
  • 定期删除未使用的索引以节省空间
  • 定期重建碎片化索引

常见错误

❌ 不要创建太多索引（写入性能影响） ❌ 不要在不测试的情况下创建索引 ❌ 不要忽略索引大小和存储影响 ❌ 在批量操作后不要忘记更新表统计信息 ❌ 不要创建重复的索引

✅ 要在 foreign keys 上创建索引 ✅ 测试索引对INSERT/UPDATE性能的影响 ✅ 为常见查询使用覆盖索引 ✅ 定期删除未使用的索引 ✅ 监控索引碎片化

资源