本教程介绍如何使用 Explain 报告来定位和修复有问题的(缓慢的)查询。它使用 DBT-3 数据库并从以下简单查询示例开始。
SELECT * FROM orders
WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4
AND o_clerk LIKE '%0223';
如下图所示,查询示例首先在 Visual SQL 编辑器中执行。接下来,通过单击“查询”菜单中的“解释当前语句”生成一个解释报告。初始报告显示一个 Visual Explain 图像,其中包含在全表扫描中将
指针设备移到表上时出现的信息
。orders
或者,您可以切换到 Tabular Explain,如下图所示。使用下拉列表在视觉和表格表示之间切换。
关于查询的问题:
为什么这个查询会产生全表扫描?
为什么索引
o_orderdate
列作为可能的键丢失?
仔细观察,还会注意到索引列在表达式中用作 as "WHERE YEAR(o_orderdate) = 1992
AND MONTH(o_orderdate) = 4"
,因此未使用索引。要使用现有索引,您可以按如下方式调整查询。
SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE '%0223';
更新后的查询示例生成一个 Visual Explain 图像,其中
Index Range Scan
替换了Full Table
Scan
上一个查询示例生成的图像。接下来的两个图显示了修改后的查询示例的视觉和表格表示。
注意差异。类型从ALL
变为range
,可能的键(和使用的键)从NULL
变为i_o_orderdate
,扫描行数从 150 万变为大约 33000。尽管如此,扫描 33,000 行而仅返回 18 行是不必要的,因此焦点可以转移到
o_clerk
列上。下一个查询示例(和 Tabular Explain 图)添加了以下应该提高性能的索引。
CREATE INDEX i_o_clerk ON orders(o_clerk);
新索引未被视为可能的键,因为查询正在搜索o_clerk
列的后缀并且索引不适用于后缀(尽管它们确实适用于前缀)。相反,这个简单的示例可以使用整个职员 ID。如下调整查询显示更好的结果。
SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';
下图分别表示 Visual Explain 和 Tabular Explain 中更新查询示例的效果。
o_clerk
考虑并使用了
新索引,查询扫描了 1546 行而不是 32642 行,查询执行时间从 0.281 秒提高到 0.234 秒。但是,
EXPLAIN
估计该查询扫描了1546行,返回18行。再次查看查询后,认为多列索引可以满足下
一条语句显示WHERE
的同时基于the
o_orderdate
和o_clerk
列的子句的条件。
CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)
o_clerk
显示为索引中的第一列,因为o_orderdate
使用了一个范围。
现在,执行调整后的查询会产生更好的结果。估计扫描并返回了 18 行,查询示例的执行时间为 0.234 秒,如下一个 Visual Explain 和 Tabular Explain 图所示。
下表总结了本教程期间对查询所做修改的结果。
表 7.2 DBT-3 解释教程查询比较
类型 | 可能的键 | 钥匙 | 扫描的行 | 持续时间(秒) | 额外信息 | 返回的行 |
---|---|---|---|---|---|---|
全部 | 无效的 | 无效的 | 1.50M | 1.201 | 在哪里使用 | 18 |
范围 | i_o_orderdate | i_o_orderdate | 32642 | 0.281 | 使用索引条件;在哪里使用 | 18 |
范围 | i_o_orderdate,i_o_clerk | 我_o_文员 | 1546 | 0.234 | 使用索引条件;在哪里使用 | 18 |
范围 | i_o_orderdate,i_o_clerk,i_o_clerk_date | i_o_clerk_date | 18 | 0.234 | 使用索引条件 | 18 |