MySQL 工作台  / 第 7 章性能工具  /  7.5 教程:使用 Explain 提高查询性能

7.5 教程:使用 Explain 提高查询性能

本教程介绍如何使用 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

图 7.9 DBT-3 解释教程:使用全表扫描进行可视化解释

内容在周围的文字中描述。

或者,您可以切换到 Tabular Explain,如下图所示。使用下拉列表在视觉和表格表示之间切换。

图 7.10 DBT-3 解释教程:带全表扫描的表格解释

内容在周围的文字中描述。

关于查询的问题:

  • 为什么这个查询会产生全表扫描?

  • 为什么索引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上一个查询示例生成的图像。接下来的两个图显示了修改后的查询示例的视觉和表格表示。

图 7.11 DBT-3 解释教程:使用索引范围扫描进行可视化解释

内容在周围的文字中描述。

图 7.12 DBT-3 解释教程:带索引范围扫描的表格解释

内容在周围的文字中描述。

注意差异。类型从ALL 变为range,可能的键(和使用的键)从NULL变为i_o_orderdate,扫描行数从 150 万变为大约 33000。尽管如此,扫描 33,000 行而仅返回 18 行是不必要的,因此焦点可以转移到 o_clerk列上。下一个查询示例(和 Tabular Explain 图)添加了以下应该提高性能的索引。

CREATE INDEX i_o_clerk ON orders(o_clerk);

图 7.13 DBT-3 解释教程:使用索引范围扫描和索引后的表格解释

内容在周围的文字中描述。

新索引未被视为可能的键,因为查询正在搜索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 中更新查询示例的效果。

图 7.14 DBT-3 解释教程:使用索引范围扫描和完整 ID 的可视化解释

内容在周围的文字中描述。

图 7.15 DBT-3 解释教程:带索引范围扫描和完整 ID 的表格解释

内容在周围的文字中描述。

o_clerk考虑并使用了 新索引,查询扫描了 1546 行而不是 32642 行,查询执行时间从 0.281 秒提高到 0.234 秒。但是, EXPLAIN估计该查询扫描了1546行,返回18行。再次查看查询后,认为多列索引可以满足下 一条语句显示WHERE的同时基于the o_orderdateo_clerk 列的子句的条件。

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)
笔记

o_clerk显示为索引中的第一列,因为o_orderdate使用了一个范围。

现在,执行调整后的查询会产生更好的结果。估计扫描并返回了 18 行,查询示例的执行时间为 0.234 秒,如下一个 Visual Explain 和 Tabular Explain 图所示。

图 7.16 DBT-3 解释教程:使用多列索引范围扫描进行可视化解释

内容在周围的文字中描述。

图 7.17 DBT-3 解释教程:使用多列索引范围扫描的表格解释

内容在周围的文字中描述。

下表总结了本教程期间对查询所做修改的结果。

表 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