MySQL存储历史股票数据按交易日查询性能优化问题咨询

认知误区说明 #

你对InnoDB联合聚集索引的生效规则存在认知偏差：

• InnoDB的联合索引严格按照索引定义的字段顺序排序，你当前的联合主键顺序为(ts_code, trade_date)，索引会先按ts_code排序，同一ts_code下再按trade_date排序。
• 联合索引遵循最左前缀匹配原则，只有查询条件包含索引的第一个字段（也就是ts_code）时，才能用到索引的快速定位能力。你单独按trade_date查询时，trade_date在索引中分散在不同ts_code的分组下，数据库只能执行全表/全索引扫描，自然性能很差。
• 你参考的方案能实现毫秒级按日期查询，大概率是它的聚集索引将date字段放在了联合索引的最左侧，和你当前的索引顺序正好相反。

优化方案 #

方案1：调整联合主键顺序（优先推荐） #

将联合主键的顺序调整为(trade_date, ts_code)：

• 调整后索引先按trade_date排序，同一交易日下再按ts_code排序，按单个交易日查询时可以直接走聚集索引的连续扫描，性能可以提升到毫秒级。
• 调整后如果需要保证单只股票历史查询的性能，额外创建一个基于ts_code的二级索引即可，原来的单只股票查询性能不会下降。
• 调整后主键的唯一性约束依然有效，同一交易日同一股票只会有一条记录，不会出现主键冲突。

方案2：新增trade_date二级索引（不改动主键的备选方案） #

如果不方便修改主键结构，单独为trade_date创建普通二级索引：

• 查询时会先通过二级索引定位到对应交易日的所有主键值，再回表拉取全量数据，性能比全表扫描提升3~10倍，但如果单日数据量超过10万条，性能会弱于方案1。

方案3：按trade_date做表分区 #

如果你的数据量超过千万条，还可以叠加range分区策略，按trade_date的范围做分区，单个查询可以直接命中对应分区，跳过其他所有数据的扫描，进一步提升查询性能。

小优化提示 #

你当前的查询语句SELECT * FROM StockDailyQuotations WHERE trade_date='20201231';中，trade_date是int类型，不要加字符串引号，避免隐式类型转换导致的索引失效，改成trade_date=20201231即可。

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