MariaDB查询timestamp列导致CPU占用过高运行缓慢如何解决？

问题原因 #

本次性能差异的核心原因是覆盖索引命中情况不同，结合MariaDB InnoDB引擎的特性，具体逻辑如下：

1. 查询orders_dates.id时，id是该表的主键，InnoDB的二级索引（你关联用的order_id字段的索引）会自动携带主键值作为索引组成部分，此时整个查询只需要通过order_id索引就能拿到所需的全部字段，不需要回表访问实际的行数据，执行计划的Extra列会出现Using index标记，查询效率极高。
2. 查询orders_dates.date_for_stats_filter时，这个timestamp字段不在order_id的二级索引中，数据库每匹配到一条关联记录，都需要回表读取完整的行数据来获取该字段值。如果匹配的记录量较大，大量随机IO操作在1核2G的低配置VPS上会直接占满CPU资源；同时优化器可能因为回表成本的计算偏差，放弃使用orders_super_parents表上row_number字段的排序索引，导致全表扫描后再排序，进一步放大性能损耗。

修复方案 #

• 优先创建覆盖索引：给orders_dates表创建联合索引，将需要查询的date_for_stats_filter字段附加到关联字段order_id的索引中，避免回表：

  -- 执行前可先查询是否已经存在order_id的单独索引，若存在可删除旧索引替换为该联合索引，减少冗余索引
  CREATE INDEX idx_orders_dates_order_id_date_stats ON orders_dates (order_id, date_for_stats_filter);
  

• 优化主表索引：给orders_super_parents表创建联合索引，同时覆盖过滤条件、排序规则和关联需要的字段，让数据库可以直接按索引顺序取前15条数据，不需要额外排序和回表：

  CREATE INDEX idx_osp_deleted_row_num ON orders_super_parents (deleted_at, row_number DESC, id, order_id_last_updated_at);
  

• 可选优化：如果date_for_stats_filter是高频查询字段，可以考虑将其冗余到orders表中，省去关联orders_dates的步骤，进一步降低查询开销。

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