索引详解

嗨！长安来讲索引了！索引是提升查询速度的秘密武器！🚀

🎯 什么是索引？

想象你在查字典：

  • 📚 没有索引：从第一页翻到最后一页
  • 📇 有索引：直接查拼音索引，快速定位

数据库索引也是一样！

📊 索引的作用

没有索引：

SELECT * FROM users WHERE email = 'test@email.com';
-- 扫描1000万行数据，可能需要10秒

有索引：

CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'test@email.com';
-- 直接定位，只需0.01秒！

📝 创建索引

单列索引

-- 基本语法
CREATE INDEX 索引名 ON 表名(列名);

-- 实例
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);
CREATE INDEX idx_products_price ON products(price);

唯一索引

-- 保证值唯一
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 主键和UNIQUE约束会自动创建唯一索引

复合索引（多列索引）

-- 多列组合索引
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

-- 查询时要遵循"最左前缀原则"
SELECT * FROM posts WHERE user_id = 1;  -- ✅ 能用到索引
SELECT * FROM posts WHERE user_id = 1 AND created_at > '2023-01-01';  -- ✅ 能用到索引
SELECT * FROM posts WHERE created_at > '2023-01-01';  -- ❌ 不能用到索引

长安提醒：复合索引的列顺序很重要！把最常用的列放前面。

部分索引

-- 只索引满足条件的行
CREATE INDEX idx_active_users ON users(username)
WHERE is_deleted = FALSE;

-- 节省空间，提高效率

表达式索引

-- 索引计算结果
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 查询时需要用相同的表达式
SELECT * FROM users WHERE LOWER(email) = 'test@email.com';

🎯 索引类型

B-tree索引（默认）

最常用的索引类型，适合：

  • 等值查询：=
  • 范围查询：>, <, BETWEEN
  • 排序：ORDER BY
CREATE INDEX idx_price ON products(price);
-- 自动使用B-tree

Hash索引

只支持等值查询：

CREATE INDEX idx_hash_email ON users USING HASH (email);
-- 只能用于 =，不能用于 >, <, LIKE

GIN索引（全文搜索、JSON、数组）

-- 全文搜索
CREATE INDEX idx_content ON posts USING GIN (to_tsvector('english', content));

-- JSON字段
CREATE INDEX idx_metadata ON products USING GIN (metadata);

-- 数组
CREATE INDEX idx_tags ON posts USING GIN (tags);

GiST索引（地理位置、范围）

-- 地理位置（需要PostGIS扩展）
CREATE INDEX idx_location ON stores USING GIST (location);

📊 查看索引

-- 查看表的所有索引
\d table_name

-- 查询索引信息
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 查看索引大小
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

⚠️ 何时需要索引？

✅ 应该创建索引的情况

  1. WHERE子句常用的列
-- 经常这样查询
SELECT * FROM users WHERE email = 'xxx';
-- 应该创建索引
CREATE INDEX idx_users_email ON users(email);
  1. JOIN关联的列
-- 经常JOIN
SELECT * FROM posts p
JOIN users u ON p.user_id = u.id;
-- 应该创建索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
  1. ORDER BY排序的列
-- 经常排序
SELECT * FROM posts ORDER BY created_at DESC;
-- 应该创建索引
CREATE INDEX idx_posts_created_at ON posts(created_at);
  1. 外键列
-- 外键列一定要有索引！
ALTER TABLE posts ADD FOREIGN KEY (user_id) REFERENCES users(id);
CREATE INDEX idx_posts_user_id ON posts(user_id);

❌ 不应该创建索引的情况

  1. 小表：数据量很小（几百行），全表扫描更快
  2. 频繁更新的列：每次UPDATE都要更新索引
  3. 选择性低的列：比如性别（只有男/女）
  4. 很少查询的列：建了也没用

💡 索引优化技巧

1. 避免索引失效

-- ❌ 对列进行计算，索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- ✅ 改写查询
SELECT * FROM users 
WHERE created_at >= '2023-01-01' 
  AND created_at < '2024-01-01';

-- ❌ 使用函数，索引失效
SELECT * FROM users WHERE UPPER(username) = 'ADMIN';

-- ✅ 使用表达式索引
CREATE INDEX idx_username_upper ON users(UPPER(username));

2. 合理使用复合索引

-- 经常一起查询的列放在一个索引里
-- 查询：WHERE user_id = 1 AND status = 'published' ORDER BY created_at
CREATE INDEX idx_posts_compound ON posts(user_id, status, created_at);

3. 覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_users_email_username ON users(email, username);

-- 查询只需要索引列，不用回表
SELECT username FROM users WHERE email = 'test@email.com';
-- 速度更快！

4. 定期维护索引

-- 重建索引
REINDEX INDEX idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;

-- 分析表（更新统计信息）
ANALYZE users;

🎮 实战案例

电商系统索引设计

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- 自动有索引
    email VARCHAR(100) UNIQUE,  -- UNIQUE自动有索引
    username VARCHAR(50) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP
);

-- 额外索引
CREATE INDEX idx_users_phone ON users(phone);  -- 手机号查询
CREATE INDEX idx_users_created ON users(created_at);  -- 按注册时间查询

-- 商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock INT,
    created_at TIMESTAMP
);

-- 商品索引
CREATE INDEX idx_products_category ON products(category);  -- 分类查询
CREATE INDEX idx_products_price ON products(price);  -- 价格范围
CREATE INDEX idx_products_name ON products USING GIN (to_tsvector('english', name));  -- 全文搜索
CREATE INDEX idx_products_category_price ON products(category, price);  -- 复合索引

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    created_at TIMESTAMP
);

-- 订单索引
CREATE INDEX idx_orders_user_id ON orders(user_id);  -- 外键
CREATE INDEX idx_orders_status ON orders(status);  -- 状态查询
CREATE INDEX idx_orders_created ON orders(created_at DESC);  -- 时间倒序
CREATE INDEX idx_orders_user_status ON orders(user_id, status);  -- 复合索引

性能对比

-- 测试数据（100万条）
INSERT INTO products (name, category, price, stock)
SELECT 
    'Product ' || i,
    (ARRAY['电子', '图书', '服装', '食品'])[floor(random()*4 + 1)],
    random() * 1000,
    floor(random() * 100)
FROM generate_series(1, 1000000) i;

-- 没有索引
EXPLAIN ANALYZE
SELECT * FROM products WHERE category = '电子' AND price < 500;
-- Execution Time: 285 ms

-- 创建索引
CREATE INDEX idx_category_price ON products(category, price);

-- 有索引
EXPLAIN ANALYZE
SELECT * FROM products WHERE category = '电子' AND price < 500;
-- Execution Time: 2 ms

-- 快了140倍！

📊 监控索引使用情况

-- 查看索引使用统计
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

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

-- 删除无用索引
DROP INDEX idx_unused_index;

📚 下一步

掌握了索引，你的查询速度会大幅提升！

继续学习查询优化！

长安的经验：

索引的黄金法则：

  1. 外键必有索引
  2. WHERE常用列建索引
  3. JOIN关联列建索引
  4. 不要过度索引（每个索引都要维护成本）
  5. 定期监控和清理无用索引

合理使用索引，性能提升10倍都不是梦！🚀

有问题来编程指南！

最近更新: