面试题：复杂函数嵌套下MySQL查询性能优化

Question

假设有一个`orders`表，包含`order_date`（日期时间类型）和`total_amount`（数值类型）字段。现在需要查询每个月订单总金额的平均值，并且要排除订单金额小于100的订单。查询语句如下：`SELECT MONTH(order_date), AVG(total_amount) FROM orders WHERE total_amount >= 100 GROUP BY MONTH(order_date)`。这里函数`MONTH`的使用对查询性能有什么影响？如果要优化这个查询，你会从哪些方面入手？

Answer 1

MONTH函数对查询性能的影响

1. 无法使用索引：在WHERE子句中对order_date字段使用MONTH函数，数据库无法利用order_date字段上可能存在的索引。因为函数操作会对每一行数据进行计算，导致数据库无法通过索引快速定位满足条件的数据，使得查询需要全表扫描，大大增加了查询时间和资源消耗。

优化查询的方面

1. 改变查询逻辑：
   • 添加临时表：先通过WHERE total_amount >= 100筛选出符合金额条件的数据到一个临时表，然后对临时表进行按月分组计算平均值。例如在MySQL中：

CREATE TEMPORARY TABLE temp_orders AS SELECT * FROM orders WHERE total_amount >= 100;
SELECT MONTH(order_date), AVG(total_amount) FROM temp_orders GROUP BY MONTH(order_date);

• 子查询：使用子查询先筛选数据，再进行分组计算。例如：

SELECT MONTH(order_date), AVG(total_amount) 
FROM (SELECT * FROM orders WHERE total_amount >= 100) AS subquery
GROUP BY MONTH(order_date);

2. 索引优化：
   • 如果数据库支持函数索引（如Oracle支持基于函数的索引），可以创建基于MONTH(order_date)的函数索引。例如在Oracle中：

CREATE INDEX idx_month_order_date ON orders (MONTH(order_date));

• 在不使用函数索引的情况下，可以考虑添加一个冗余字段，例如在表中添加order_month字段，类型为INT，在插入数据时同时计算并插入月份值。然后对order_month字段创建普通索引。查询时直接使用order_month字段进行分组和过滤，这样就可以利用索引提高查询性能。例如在MySQL中：

-- 添加字段
ALTER TABLE orders ADD COLUMN order_month INT;
-- 更新字段值
UPDATE orders SET order_month = MONTH(order_date);
-- 创建索引
CREATE INDEX idx_order_month ON orders (order_month);
-- 修改后的查询
SELECT order_month, AVG(total_amount) FROM orders WHERE total_amount >= 100 GROUP BY order_month;

3. 数据库配置优化：根据数据库服务器的硬件资源（如内存、CPU等），合理调整数据库的配置参数，如缓冲区大小、并行处理参数等，以提高查询性能。例如在MySQL中，可以调整innodb_buffer_pool_size参数来增加缓冲池大小，使得更多的数据可以缓存到内存中，减少磁盘I/O操作。