面试题：如何突破MySQL查询优化器在特定场景下的局限性并给出优化方案

统计信息不准确：
- 高并发场景下数据变化频繁，统计信息可能过时，导致优化器选择错误的执行计划。例如，某表的行数、数据分布等统计信息不能及时反映实际情况，使得优化器在选择索引或连接算法时做出错误决策。
复杂查询处理能力有限：
- 多表关联、复杂过滤条件和聚合操作结合时，优化器的搜索空间巨大。它可能无法穷举所有可能的执行计划，从而错过最优解。比如，在多个表进行复杂连接时，优化器不能准确判断不同连接顺序的成本，选择了并非最优的连接顺序。
索引使用不当：
- 对于复杂查询，优化器可能无法正确评估索引的选择性。即使存在合适的索引，也可能因为索引列的顺序、复合索引的使用方式等问题，导致优化器不选择最优的索引来执行查询，从而影响性能。

适当冗余字段：
- 在一些频繁查询的表中，适当冗余一些其他表的常用字段。例如，订单表中冗余用户的部分基本信息（如用户名、手机号等），这样在查询订单相关信息时，减少了与用户表的关联操作，降低查询复杂度。但要注意冗余带来的数据一致性问题，需要通过合适的机制（如触发器等）来维护。
分表与分区：
- 分表：如果订单数据量巨大，可以按时间（如按月、按季度）或业务类型对订单表进行分表。例如，将历史订单和当前活跃订单分表存储，查询当前活跃订单时，数据量大幅减少，提高查询效率。
- 分区：对大表进行分区，如按订单ID范围分区。这样在查询时，如果查询条件包含分区字段，可以快速定位到相应分区，减少扫描的数据量。例如，查询某个时间段内的订单，可以通过时间字段进行分区，查询时只扫描对应时间分区的数据。

简化复杂条件：
- 将复杂的过滤条件进行拆分和简化。例如，对于一个包含多个逻辑运算符的条件 (A AND B) OR (C AND D)，可以尝试通过等价变换，转化为更简单的形式，让优化器更容易理解和优化。
合理使用JOIN类型：
- 根据数据量和关联条件，选择合适的JOIN类型。对于小表与大表的关联，如果小表的数据量远小于大表，可以使用 STRAIGHT_JOIN 强制优化器先扫描小表，再与大表进行连接。例如，商品类别表（小表）与商品表（大表）关联查询商品信息时，使用 STRAIGHT_JOIN 可提高查询效率。
避免子查询嵌套过深：
- 尽量将子查询改写为连接查询。例如，对于 SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 123)，可以改写为 SELECT orders.* FROM orders JOIN order_items ON orders.order_id = order_items.order_id WHERE order_items.product_id = 123，这样优化器可以更好地生成执行计划。

创建复合索引：
- 分析查询中的过滤条件和连接条件，创建合适的复合索引。例如，如果经常按照 (user_id, order_date, order_status) 进行查询，可以创建 CREATE INDEX idx_user_date_status ON orders (user_id, order_date, order_status) 复合索引。注意复合索引中列的顺序，一般将选择性高的列放在前面。
覆盖索引：
- 如果查询涉及的字段都包含在索引中，那么查询可以直接通过索引完成，而不需要回表操作。例如，查询订单的 order_id 和 order_amount，可以创建 CREATE INDEX idx_order_id_amount ON orders (order_id, order_amount)，这样查询时可以直接从索引获取数据，提高查询性能。
定期维护索引：
- 在高并发场景下，数据的频繁插入、更新和删除可能导致索引碎片化。定期使用 OPTIMIZE TABLE 或 ALTER TABLE...ENGINE=InnoDB 等命令对表和索引进行优化，整理碎片，提高索引的使用效率。

星途面试题库