面试题：从MySQL B+树索引代价与收益角度，优化复杂查询的策略探究

基于连接字段
- 策略：在连接的字段上创建索引。例如，若查询涉及 table1 和 table2 通过 table1.id = table2.table1_id 进行连接，在 table1.id 和 table2.table1_id 上创建索引。
- 代价与收益：
  - 收益：B+树索引能够快速定位匹配连接条件的数据行，减少表扫描的范围，显著提高连接操作的效率，从而加快查询速度。
  - 代价：索引本身会占用额外的存储空间，并且每次数据插入、更新和删除操作时，都需要维护索引，增加了写操作的开销。
基于过滤条件
- 策略：对于复杂过滤条件中的字段创建索引。如 WHERE 子句中有 table1.status = 'active' AND table1.created_at > '2023 - 01 - 01'，在 table1.status 和 table1.created_at 上创建索引。
- 代价与收益：
  - 收益：索引可快速筛选出符合过滤条件的数据，降低需要处理的数据量，提升查询性能。
  - 代价：同样增加存储开销和写操作维护索引的成本。

复合索引优化
- 策略：如果多个条件经常一起使用，可创建复合索引。比如 WHERE table1.category = 'electronics' AND table1.price > 100，可创建 (category, price) 的复合索引。注意索引字段顺序，将选择性高（离散度大）的字段放在前面。
- 代价与收益：
  - 收益：复合索引可以同时利用多个字段的索引信息，在满足多个条件的查询中更高效地定位数据，相比单个索引效率更高。
  - 代价：复合索引占用的存储空间比单个索引更大，写操作时维护索引的开销也更高。
冗余索引清理
- 策略：查找并删除冗余索引。如果有索引 (a, b) 和索引 (a)，在 (a) 没有被其他查询单独使用的情况下，(a) 索引可视为冗余索引。
- 代价与收益：
  - 收益：释放冗余索引占用的存储空间，减少写操作时索引维护的开销。
  - 代价：可能需要仔细分析现有查询，确保删除的索引不会影响其他查询的性能。

子查询优化
- 策略：如果查询中有子查询，尝试将子查询改写为连接。例如，原查询为 SELECT * FROM table1 WHERE id IN (SELECT table1_id FROM table2 WHERE status = 'completed')，可改写为 SELECT table1.* FROM table1 JOIN table2 ON table1.id = table2.table1_id AND table2.status = 'completed'。
- 代价与收益：
  - 收益：连接操作利用索引的效率通常高于子查询，特别是在涉及多个表和复杂条件时，改写后可更好地利用索引，提升查询性能。
  - 代价：改写可能需要对业务逻辑有更深入的理解，确保改写后的逻辑与原逻辑一致。
聚合操作优化
- 策略：在聚合操作前尽量减少数据量。如在 SELECT COUNT(*), category FROM table1 WHERE status = 'active' GROUP BY category 中，先通过 status 过滤条件减少参与聚合的数据，再进行分组聚合。
- 代价与收益：
  - 收益：减少参与聚合的数据量，可降低聚合操作的计算成本，同时也可能更好地利用索引来筛选数据，提高整体查询性能。
  - 代价：需要确保过滤条件不会误删需要聚合的数据，对查询逻辑的正确性要求更高。

星途面试题库