50万行大表按datetime倒序取最新10条记录的SQL查询优化

原查询耗时高的核心原因是未给排序字段create_date加索引，数据库执行时需要扫描全表50万条数据，做全量排序后再取前10条，资源消耗大导致耗时久。以下是可落地的优化方案：

方案1：给排序字段加倒序索引（首选方案，改造成本最低） #

• 给create_date字段建立倒序索引即可，建索引语句如下：

CREATE INDEX idx_create_date_desc ON `big_table` (`create_date` DESC);

• 索引创建完成后原查询语句无需任何修改，数据库会直接通过索引定位到最新的10条记录，无需全表扫描和全量排序，耗时可降至毫秒级。
• 优化提示：如果create_date字段当前允许为NULL，建议先将字段设置为NOT NULL，索引执行效率会更高。

方案2：利用自增主键优化（适合写入顺序与主键递增顺序一致的场景） #

如果你的表使用InnoDB引擎，主键id为连续自增，且不存在数据补录、修改create_date的操作（即id越大create_date越大），可以直接按主键倒序排序查询：

SELECT * FROM `big_table` ORDER BY `id` DESC LIMIT 10;

• InnoDB主键本身是聚簇索引，查询时无需回表，性能比普通索引更高。
• 注意：如果存在修改create_date、插入历史数据的场景，该方案会导致查询结果不准，禁止使用。

方案3：冗余存储/缓存（适合高频访问场景） #

如果该查询是业务高频调用的接口，读写占比差距很大，可以额外新增一张仅存最新10条记录的小表，或者用Redis缓存查询结果，每次有新数据写入big_table时同步更新小表或缓存，查询时直接读取小表/缓存即可，耗时可低至1ms以内。

• 注意：该方案会增加写入逻辑的复杂度，仅适合读请求远多于写请求的场景。

额外优化建议 #

• 尽量避免使用SELECT *，如果业务只需要部分字段，明确指定需要的字段名，可减少回表开销和数据传输量。
• 如果create_date存在大量重复值，可以将排序逻辑调整为ORDER BY create_date DESC, id DESC LIMIT 10，保证排序结果的稳定性。

内容的提问来源于stack exchange，提问作者tomasr