数据库索引策略指南：B-tree、Hash、GiST、BRIN索引优化查询性能与维护

database-indexing-strategy by aj-geddes/useful-ai-prompts

164 周安装量

160 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/aj-geddes/useful-ai-prompts --skill database-indexing-strategy

数据库开发运维性能优化

🇨🇳中文介绍

数据库索引策略

概述

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

使用场景

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

快速开始

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;

参考指南

references/ 目录下的详细实现：

指南	内容

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

🇺🇸English

Database Indexing Strategy

Overview
When to Use
Quick Start
Reference Guides
Best Practices

Overview

Design comprehensive indexing strategies to improve query performance, reduce lock contention, and maintain data integrity. Covers index types, design patterns, and maintenance procedures.

When to Use

Index creation and planning
Query performance optimization through indexing
Index type selection (B-tree, Hash, GiST, BRIN)
Composite and partial index design
Index maintenance and monitoring
Storage optimization with indexes
Full-text search index design

Quick Start

B-tree Indexes (Default):

-- Standard equality and range queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite indexes for multi-column queries
CREATE INDEX idx_orders_user_status
ON orders(user_id, status)
WHERE cancelled_at IS NULL;

Reference Guides

Detailed implementations in the references/ directory:

Guide	Contents
PostgreSQL Index Types	PostgreSQL Index Types
MySQL Index Types	MySQL Index Types
Single Column Indexes	Single Column Indexes, Composite Indexes, Partial/Filtered Indexes, Expression Indexes

Best Practices

✅ DO

Follow established patterns and conventions
Write clean, maintainable code
Add appropriate documentation
Test thoroughly before deploying

❌ DON'T

Skip testing or validation
Ignore error handling
Hard-code configuration values

Weekly Installs

114

Repository

aj-geddes/usefu…-prompts

GitHub Stars

116

First Seen

Jan 21, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

opencode96

gemini-cli94

claude-code92

codex91

cursor87

github-copilot77