数据库查询优化实战

IMS 系统承载着大量业务数据，查询性能直接影响用户体验。本文从索引设计、SQL 调优、慢查询分析三个维度，介绍生产环境下的数据库优化实践。

一、索引设计原则

• 离散度高的列优先：性别字段建立索引效果差，用户状态同理
• 覆盖索引：尽量让索引包含查询所需的全部列，避免回表
• 复合索引顺序：遵循最左前缀原则，把区分度高的列放前面
• 避免过多索引：每个索引都是额外写开销，权衡读写比例

-- 好的复合索引示例
-- 查询：WHERE status = 'active' AND created_at > '2026-01-01'
CREATE INDEX idx_order_status_created ON orders(status, created_at);

-- 覆盖索引示例（查询所需列都在索引中）
CREATE INDEX idx_user_cover ON users(id, name, email) INCLUDE (status);

二、常见 SQL 优化技巧

1. 避免 SELECT *

-- 低效
SELECT * FROM orders WHERE id = 123;

-- 高效（只取需要的列）
SELECT order_no, amount, status FROM orders WHERE id = 123;

2. 分页优化

-- 低效（OFFSET 越大越慢）
SELECT * FROM orders ORDER BY id LIMIT 100 OFFSET 10000;

-- 高效（基于游标）
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 100;

3. 避免隐式转换

-- 隐式转换导致索引失效
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 VARCHAR 类型

-- 正确写法
SELECT * FROM users WHERE phone = '13800138000';

三、慢查询分析与定位

-- PostgreSQL 开启慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 记录超过 1 秒的查询

-- MySQL 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

使用 EXPLAIN 分析执行计划

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'pending';

-- 重点关注：
-- - seq scan vs index scan（应尽量避免全表扫描）
-- - rows（估算行数是否准确）
-- - actual time（实际执行时间）

四、分表分库策略

• 按时间分表：适合历史数据查询较少的场景，如订单表按月分表
• 按业务ID分片：如用户表按用户ID取模分散到多个库
• 冷热数据分离：将历史数据归档到冷库，当前数据保留在热库

-- 按月份分表的命名示例
orders_202605  -- 2026年5月订单
orders_202604  -- 2026年4月订单
orders_202603  -- 2026年3月订单

五、总结

• 索引是优化的第一杠杆，设计好索引能解决 90% 性能问题
• 避免 SELECT *、隐式转换、深度分页等常见坑
• 用 EXPLAIN 定位慢查询，分析执行计划
• 分表分库是数据量大了之后的必然选择