MySQL 查询运行时间更好，即使它的执行计划很糟糕

Question

我正在尝试优化此 MySQL 查询，但在理解执行计划方面经验不足，我很难理解执行计划。

我的问题是:能否请您帮助我理解为什么新查询的查询执行计划比原始查询的查询执行计划差即使新查询在 Prod 中表现更好。

重现此案例所需的 SQL 是 here
最后还保留了相关的表定义(表bill_range references bill using foreign key bill_id)

原始查询在 PROD 中需要 10 秒才能完成

select * 
from bill_range 
where (4050 between low and high ) 
order by bill_id limit 1;

而新查询(我强制/建议使用索引)在 PROD 中需要 5 秒才能完成

select * 
from bill_range 
use index ( bill_range_low_high_index) 
where (4050 between low and high ) 
order by bill_id limit 1;

但执行计划给出的建议是原始查询更好(这是我理解错误的部分)

原始查询

新查询

1. 原始查询的“类型”列在新查询时建议索引说 ALL
2. “Key”列是 bill_id(可能是 FK 上的索引)原始查询和新查询的Null
3. 原始查询的“行”列是 1，而新查询的“行”列是 9

所以鉴于所有这些信息，这是否意味着新查询实际上比原始查询更糟糕。如果这是真的，为什么新查询的性能更好？还是我读错了执行计划。

表定义

CREATE TABLE bill_range (
    id int(11) NOT NULL AUTO_INCREMENT,
    low varchar(255) NOT NULL,
    high varchar(255) NOT NULL,
    PRIMARY KEY (id),
    bill_id int(11) NOT NULL,
    FOREIGN KEY (bill_id) REFERENCES bill(id)
);
CREATE TABLE bill (
    id int(11) NOT NULL AUTO_INCREMENT,
    label varchar(10),
    PRIMARY KEY (id)
);
create index bill_range_low_high_index on bill_range( low, high);

注意:我提供 2 个表的定义的原因是因为原始查询决定使用基于外键的索引来对表进行计费

Answer 1

您的索引对于您的查询而言不是最佳选择。如果可以，让我解释一下。

MySQL 索引使用 BTREE 数据结构。这些在索引顺序访问模式下运行良好(因此 MyISAM 成为 MySQL 的第一个存储引擎的名称)。它支持跳转到索引中特定位置然后逐个元素遍历索引的查询。典型的例子是这样的，在 col 上有一个索引。

 SELECT whatever FROM tbl WHERE col >= constant AND col <= constant2

这是对 WHERE col BETWEEN constant AND constant2 的重写。

让我们重写您的查询，使这种模式显而易见，并且您想要的列是明确的。

select id, low, high, bill_id 
from bill_range 
where low <= 4050 
  and high >= 4050 
order by bill_id limit 1;

high 列上的索引允许范围扫描从具有 high >= 4050 的第一个符合条件的行开始。然后，我们可以继续将其做成复合索引，包括bill_id和low列。

CREATE INDEX high_billid_low ON bill_range (high, bill_id, low);

因为我们想要最低的匹配 bill_id，所以我们接下来将其放入索引，最后是 low 值。因此查询规划器随机访问索引到第一个符合high 条件的行，然后扫描直到找到满足low 条件的第一个索引项。然后它就完成了:这就是想要的结果。它已按 bill_id 订购，因此可以停止。 ORDER BY 来自索引。查询可以完全从索引中得到满足——它是所谓的覆盖索引。

至于为什么你的两个查询表现不同:首先，查询规划器决定按 bill_id 顺序扫描你的数据，寻找第一个匹配的 low /high 对。可能它决定实际对结果集进行排序可能比按顺序扫描 bill_id 更昂贵。在我看来，您的第二个查询进行了表扫描。为什么会更快，谁知道呢？

请注意，该索引也适用于您。

CREATE INDEX low_billid_high ON bill_range (low DESCENDING, bill_id, high);

在 InnoDB 中，表的 PK id 是每个索引的隐含部分，因此无需在复合索引中提及它。

而且，您仍然可以按照最初的方式编写它；查询规划器会弄清楚你想要什么。

专业提示:避免使用 SELECT * ... * 会使您更难推断出您需要检索的列。