面试题：MySQL覆盖索引在复杂查询场景（如多表连接）下的优化效果及应用方式

1. 覆盖索引概念

覆盖索引指一个索引包含（或者说覆盖）满足查询结果的数据列，使得查询在执行时，仅需要扫描该索引，而无需回表操作（即无需再访问表的数据行），从而大大提高查询性能。

2. 结合表结构与SQL查询说明优化设计

假设存在以下两张表：

CREATE TABLE `orders` (
    `order_id` INT PRIMARY KEY AUTO_INCREMENT,
    `customer_id` INT NOT NULL,
    `order_date` DATE NOT NULL,
    `total_amount` DECIMAL(10, 2) NOT NULL
);

CREATE TABLE `customers` (
    `customer_id` INT PRIMARY KEY AUTO_INCREMENT,
    `customer_name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL
);

查询语句为：

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2023-01-01'
  AND o.total_amount > 100;

为了优化此查询，在orders表上创建覆盖索引：

CREATE INDEX idx_order_date_amount_customer ON orders (order_date, total_amount, customer_id);

在上述索引中，order_date和total_amount是过滤条件列，customer_id是连接列。由于查询结果只需要order_id和customer_name，而customer_name可以通过连接customers表获取，在orders表上此索引覆盖了order_date、total_amount和customer_id，使得查询执行时可以直接从索引获取这些列数据，无需回表，提高查询性能。

3. 局限性分析

索引维护成本：覆盖索引会增加索引的大小，占用更多磁盘空间。同时，在插入、更新和删除数据时，数据库需要维护索引结构，导致操作性能下降。例如，每次插入一条新的orders记录时，不仅要更新数据行，还要更新idx_order_date_amount_customer索引。
查询条件变化影响：如果查询条件发生变化，例如查询语句变为SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 100;，原覆盖索引中order_date不在过滤条件中，可能导致索引失效，无法达到优化效果。
索引数量限制：每个表上的索引数量是有限的，过多的覆盖索引会增加系统负担，且可能导致查询优化器难以选择最优索引。

星途面试题库

面试题：MySQL覆盖索引在复杂查询场景（如多表连接）下的优化效果及应用方式

知识考点

面试题答案

1. 覆盖索引概念

2. 结合表结构与SQL查询说明优化设计

3. 局限性分析