面试题：MySQL慢查询日志中棘手SQL重写与索引优化

重写过程

分析执行计划：
- 使用 EXPLAIN 关键字分析原复杂 SQL 的执行计划，了解数据库引擎如何处理每个表的连接、过滤条件以及窗口函数的执行顺序。例如：
```
EXPLAIN SELECT column1, column2, 
    ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY column4) AS row_num
FROM table1
JOIN table2 ON table1.id = table2.table1_id
JOIN table3 ON table2.id = table3.table2_id
WHERE table1.some_condition AND table2.other_condition AND table3.yet_another_condition;
```
- 从执行计划中找出性能瓶颈点，比如某些表连接使用了全表扫描，或者窗口函数的计算代价过高。
分解复杂 SQL：
- 将大的联合查询分解为多个较小的查询。例如，如果原查询中有多个表连接，可以先将部分表连接起来，得到一个中间结果集，然后再与其他表连接。
- 例如，原 SQL 是 SELECT... FROM table1 JOIN table2 JOIN table3...，可以先执行 SELECT... FROM table1 JOIN table2 INTO TEMPORARY TABLE temp1;，然后再执行 SELECT... FROM temp1 JOIN table3...。这样做可以减少单次查询的数据量和复杂度。
优化窗口函数：
- 如果窗口函数依赖的列有大量重复值，可以考虑先对数据进行预处理。例如，在窗口函数 ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY column4) 中，如果 column3 有很多重复值，可以先对 table1 按照 column3 进行分组聚合，减少数据量，再应用窗口函数。
- 可以使用临时表或 WITH 子句（CTE）来实现。例如：
```
WITH grouped_table AS (
    SELECT column3, COUNT(*) AS count, SUM(some_value) AS total_value
    FROM table1
    GROUP BY column3
)
SELECT column3, count, total_value,
    ROW_NUMBER() OVER (ORDER BY count) AS row_num
FROM grouped_table;
```

索引设计思路

连接列索引：
- 对于连接条件中的列，如 table1.id = table2.table1_id 和 table2.id = table3.table2_id，在 table2 的 table1_id 列和 table3 的 table2_id 列上创建索引。例如：
```
CREATE INDEX idx_table2_table1_id ON table2(table1_id);
CREATE INDEX idx_table3_table2_id ON table3(table2_id);
```
- 如果连接条件是多列的，比如 table1.id = table2.table1_id AND table1.other_column = table2.other_column，可以创建复合索引 CREATE INDEX idx_table1_table2 ON table2(table1_id, other_column);
过滤列索引：
- 对于 WHERE 子句中的过滤条件列，如 table1.some_condition 和 table2.other_condition，在相应的列上创建索引。例如：
```
CREATE INDEX idx_table1_some_condition ON table1(some_condition);
CREATE INDEX idx_table2_other_condition ON table2(other_condition);
```
- 如果过滤条件中有多个列，且经常一起使用，可以考虑创建复合索引，但要注意复合索引的顺序，把选择性高的列放在前面。
窗口函数列索引：
- 对于窗口函数中 PARTITION BY 和 ORDER BY 的列，如 PARTITION BY column3 ORDER BY column4，在 table1 的 column3 和 column4 列上创建索引。例如：
```
CREATE INDEX idx_table1_column3_column4 ON table1(column3, column4);
```

预估优化后的性能提升比例

性能提升比例难以精确预估，因为它取决于多个因素：

数据量：如果表中的数据量非常大，优化后的性能提升可能会很显著，可能达到 50% - 90% 甚至更高。例如，每个大表有百万级以上的数据，通过合理的索引和 SQL 重写，查询时间可能从几分钟缩短到几秒。
原查询复杂度：原查询越复杂，优化空间越大，性能提升比例越高。如果原查询涉及多个大表的笛卡尔积（虽然一般会避免，但极端复杂查询可能出现），优化后可能有几倍甚至几十倍的性能提升。
硬件环境：在高性能服务器上，性能提升比例可能相对较小，而在资源有限的服务器上，优化后的提升可能更明显。例如，在配置较低的数据库服务器上，优化后查询时间可能从几十秒缩短到几秒，提升比例可能达到 80% - 90%；而在高性能服务器上，可能从 1 秒缩短到 0.5 秒，提升比例为 50%。

总体而言，合理的 SQL 重写和索引设计通常能使复杂查询的性能提升 30% - 80% 左右，但具体数值需根据实际情况通过测试确定。

面试题：MySQL慢查询日志中棘手SQL重写与索引优化

知识考点

面试题答案

重写过程

索引设计思路

预估优化后的性能提升比例