日常练习

之前在做一个课程项目时，我第一次真切地感受到了“数据库性能”的压力。我们在测试时发现，一个简单的用户分页查询，在数据量增长到几万条后，速度变得异常缓慢。这促使我打算练习之前只停留在课本概念上的数据库索引。

我们的查询语句类似这样（以MySQL为例）：

SELECT id, username, email, created_at 
FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000; -- 获取第500页的数据

当users表只有几百条数据时，一切正常。但当数据超过5万条后，这个查询的响应时间超过了2秒。使用EXPLAIN命令分析后，我看到了type: ALL（全表扫描）和Using filesort（在磁盘上进行排序）。

问题：

1. 全表扫描 (WHERE status = 'active')： 为了找到所有status为active的用户，数据库需要逐行检查整张表。

2. 文件排序 (ORDER BY created_at DESC)： 在找到所有活跃用户后，数据库需要将结果集在内存或磁盘上进行排序，这是一个O(n log n)的昂贵操作。

3. 大偏移量分页 (OFFSET 10000)： 数据库需要先找到前10020条记录，然后丢弃前10000条，只返回最后的20条。偏移量越大，需要跳过的无效数据就越多。

我的解决方案：

问题的核心在于，WHERE条件和ORDER BY排序使用了不同的字段。课本告诉我，一个设计良好的索引可以同时加速查询和排序。

我创建了如下索引：

CREATE INDEX idx_status_created_at ON users(status, created_at DESC);

这个索引的原理是：
它是一个复合索引，先按status排序，在相同的status内部，再按created_at降序排列。

当执行查询时，数据库可以直接在这个已经按created_at排好序的索引树中，快速定位到status='active'的区块，并按顺序读取数据，完全避免了“文件排序”。

由于索引中包含了查询所需的列（id是主键，会被自动包含），数据库甚至可以直接从索引中获取数据（覆盖索引），无需回表查询原数据行，效率极高。

创建索引后，同样的查询，响应时间从2秒以上降到了20毫秒以内。EXPLAIN的结果也变成了令人愉悦的type: ref和Using index。

但这次优化也让我思考更多：

1. 索引的代价：索引加快了读操作，但会减慢写操作（INSERT/UPDATE/DELETE），因为数据变更时需要维护索引树。不能盲目创建。
2. 分页优化：对于超深分页（OFFSET很大），即使有索引，性能依然会因OFFSET而下降。更优的方案是使用“游标分页”（Cursor-based Pagination），即记录上一页最后一条记录的created_at和id，下一页查询时使用WHERE created_at < ? AND id < ?。