面试题：MySQL复杂场景下结合分区表的最大值最小值查询优化

Question

假设有一张非常大的`log_data`表，记录了系统的操作日志，包含`log_id`（主键自增）、`user_id`、`operation_time`（日期时间类型）、`operation_result`（数值类型）等字段。为了提高查询性能，此表按`operation_time`进行了分区（按月分区）。现在需要查询每个月内`operation_result`的最大值和最小值，并且要尽可能优化查询性能。请给出完整的优化方案，包括索引设计、查询语句优化以及对分区使用的考虑等，并解释每一步优化的原理。

Answer 1

索引设计

1. 在 operation_time 和 operation_result 字段上创建联合索引：
   • 原理：由于表是按 operation_time 分区，在 operation_time 上创建索引可以帮助快速定位到特定月份的分区数据。而 operation_result 是我们要查询最值的字段，与 operation_time 一起创建联合索引，能够在找到对应分区数据后，快速对 operation_result 进行最值计算。索引语句如下：

   CREATE INDEX idx_op_time_result ON log_data (operation_time, operation_result);
   

查询语句优化

1. 使用窗口函数：
   • 查询语句：

   SELECT 
       operation_time,
       MAX(operation_result) OVER (PARTITION BY DATE_TRUNC('month', operation_time)) AS max_result,
       MIN(operation_result) OVER (PARTITION BY DATE_TRUNC('month', operation_time)) AS min_result
   FROM 
       log_data;
   

   • 原理：PARTITION BY DATE_TRUNC('month', operation_time) 按月份对 operation_time 进行分区，窗口函数 MAX 和 MIN 在每个月的分区内分别计算 operation_result 的最大值和最小值。这种方式避免了传统分组查询需要对整个表数据进行扫描和分组操作，通过利用分区特性，可以更高效地在每个分区内计算最值。

对分区使用的考虑

1. 利用分区裁剪：
   • 原理：由于表按 operation_time 按月分区，查询时数据库可以自动进行分区裁剪。例如，当查询特定月份的数据时，数据库可以直接定位到对应的分区，而不需要扫描其他无关分区的数据，大大减少了扫描的数据量，从而提高查询性能。在上述查询语句中，DATE_TRUNC('month', operation_time) 与分区依据 operation_time 配合，能更好地利用分区裁剪特性。