面试题：复杂查询场景下MySQL覆盖索引的优化实践

索引设计

联合索引：在商品表上创建一个联合索引，包含品牌ID和价格字段，即 CREATE INDEX idx_brand_price ON products(brand_id, price);。这样的索引结构，在按照品牌ID和价格区间查询时，可以直接通过索引获取数据，减少回表操作。

查询语句优化思路

使用覆盖索引：编写查询语句时，确保查询的字段尽量在索引列中。例如，假设商品表名为 products，品牌表名为 brands，查询语句如下：

SELECT b.brand_name, 
       -- 根据具体需求划分价格区间，这里以简单示例说明
       CASE 
           WHEN p.price < 100 THEN '低于100'
           WHEN p.price >= 100 AND p.price < 200 THEN '100到199'
           ELSE '200及以上'
       END AS price_range, 
       COUNT(p.product_id) AS product_count
FROM brands b
JOIN products p ON b.brand_id = p.brand_id
GROUP BY b.brand_name, 
         CASE 
           WHEN p.price < 100 THEN '低于100'
           WHEN p.price >= 100 AND p.price < 200 THEN '100到199'
           ELSE '200及以上'
       END;

这里查询的 brand_name 可通过 JOIN 从品牌表获取，而统计的 product_count 可以通过覆盖索引快速定位到商品表中的数据。

可能遇到的挑战和解决方案

索引维护成本
- 挑战：随着数据量的增加，索引占用的存储空间会增大，插入、更新和删除操作时，索引的维护成本也会提高，可能导致性能下降。
- 解决方案：定期评估索引的使用情况，对于不再使用或很少使用的索引进行删除。可以使用 SHOW INDEX FROM table_name; 查看索引使用情况，并结合 EXPLAIN 分析查询执行计划，判断索引是否被有效利用。
索引选择性问题
- 挑战：如果品牌ID或价格字段的选择性（即不同值的比例）较低，索引的效果可能不佳。例如，如果大部分商品价格都集中在某一个很小的区间内，索引的区分度不高，优化效果有限。
- 解决方案：可以考虑增加其他字段到联合索引中，提高索引的选择性。或者对数据进行更细致的分区，例如按价格范围进行表分区，结合索引使用，提高查询性能。
复杂价格区间划分
- 挑战：如果价格区间划分非常复杂，例如存在很多个细分区间，CASE 语句会变得冗长，查询优化难度增大，同时索引也可能无法完全覆盖所有复杂的查询场景。
- 解决方案：尽量简化价格区间的划分，使其更符合业务常见查询需求。如果无法简化，可以考虑创建多个针对不同价格区间范围的索引，以提高查询性能。同时，在查询语句中合理使用 EXISTS 子查询等方式，优化复杂逻辑的处理。

星途面试题库

面试题：复杂查询场景下MySQL覆盖索引的优化实践

知识考点

面试题答案

索引设计

查询语句优化思路

可能遇到的挑战和解决方案