我有一个MySQL表，大约有500万行：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_status_created (status, created_at)
);

我运行以下查询：

SELECT *
FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

查询按预期使用了索引。但是如果我把它改成：

SELECT *
FROM orders
WHERE customer_id = 12345
ORDER BY created_at DESC
LIMIT 100;

MySQL会执行一个filesort，并且不使用 idx_status_created。

MySQL如何为 WHERE + ORDER BY 查询选择索引，在这种情况下哪种索引是最优的？

解决方案

你有一个在 (status, created_at) 上的索引。所以它帮助你按状态查找行，并在一个状态内按created_at排序查找。这正是第一条查询所做的：找到具有特定状态的所有行，然后按created_at排序取前100行。

第二条查询查找一个特定的customer_id。这个索引对它一点也没用。你需要一个在customer_id上的索引。并且同样地，你希望按created_at排序获取前100行，这一列也应尽量包含在索引中：

create index idx_cust_created on orders (customer_id, created_at);

现在让我们更深入地细看。当创建合适的索引时，首先要关注的是查询中的 WHERE 条件。这里我们有两条查询，一条是查找某个状态，另一条是查找某个客户。假设有1000个客户，查找特定客户大致只会影响所有行的约1/1000，查询会从这列建立的索引中获益良多。相反，只有三种不同的状态时，查找某个状态会影响表的很大一部分（平均约1/3，但也可能是90% 的已完成行，只有10% 是开放的）。对状态的索引帮助就不会很大，顺序全表扫描通常会比遍历一个索引结构快得多。

但为什么此处仍然使用 (status, created_at) 的索引呢？原因是查询还有一个第二个筛选条件：ORDER BY created_at DESC LIMIT 100 告诉数据库管理系统只选择该状态下的最后100行。这个索引在 (status, created_at) 上把同一状态的所有行按created_at排序，因此数据库管理系统看到使用该索引的巨大好处：定位到状态为 'completed' 的块，然后从右向左取前100行。就这么简单。

因此在构建索引时，WHERE 条款是最重要的。并且在 WHERE 条款的所有列中，我们必须首先关注最具选择性的列。如果恰好把 WHERE 条款的所有列都包含在索引中，那么 ORDER BY 条款就会发挥作用，我们也可以从将该条款的列加入索引中获益。

甚至有可能把 SELECT 条款的列放进索引，会让查询快得多。看这个：

SELECT created_at
FROM orders
WHERE customer_id = 12345;

在一个在 (customer_id, created_at) 的索引下，DBMS可以直接按客户号12345找到所有订单，并从索引中直接取出created_at。它甚至不需要再查看表。这就叫作覆盖索引。