MariaDB查询Candlestick表最新16条数据耗时过长如何优化

优化方案 #

1. 创建覆盖索引避免回表
   你现有的ix1索引仅包含过滤、排序字段，查询时需要额外回表读取业务字段，你可以删除原有ix1，重建为覆盖所有查询字段的索引，完全避免回表开销：

ALTER TABLE Candlestick DROP INDEX ix1;
ALTER TABLE Candlestick ADD INDEX ix1 (market, coin, period, openTime DESC, high, low, a, b, c, d, e);

2. 调整主键顺序（推荐长期方案）
   你当前的主键顺序(openTime,market,coin,period)和高频查询场景不匹配，K线数据的唯一标识天然为(market,coin,period,openTime)，可以将主键调整为该顺序：

ALTER TABLE Candlestick DROP PRIMARY KEY, ADD PRIMARY KEY (market, coin, period, openTime) USING BTREE;

InnoDB主键为聚集索引，直接存储整行数据，调整后你的查询可以直接走主键索引取数，无需依赖二级索引，性能最优。注意该DDL操作建议在业务低峰期执行，避免影响线上服务。

3. 强制走索引+简化查询逻辑
   查询时强制指定使用ix1索引，避免优化器选错索引扫描全表；同时你完全可以在应用层把查询到的16条倒序结果反转得到升序结果，不需要在数据库层多做一次排序：

SELECT openTime, high, low, a, b, c, d, e 
FROM Candlestick FORCE INDEX(ix1)
WHERE market = 'USDT' AND coin = 'ETH' AND period = '5m' 
ORDER BY openTime DESC LIMIT 16;

4. 辅助优化操作

• 执行ANALYZE TABLE Candlestick;更新表的索引统计信息，帮助优化器正确判断索引选择成本
• 如果表碎片较多，可以在业务低峰期执行OPTIMIZE TABLE Candlestick;整理碎片，提升整体查询效率

内容的提问来源于stack exchange，提问作者A. Vreeswijk