如何在 MySQL 中解释带参数的查询 [英] How do I explain a query with parameters in MySQL

问题描述

我有一个问题

SELECT foo FROM bar WHERE some_column = ?

我可以在不填写参数值的情况下从 MySQL 获取解释计划吗?

解决方案

只要你只做一个 equals(而不是一个会产生短路影响的 like)，只需用一个值替换它:

>

EXPLAIN SELECT foo FROM bar WHERE some_column = 'foo';

由于它实际上并未执行查询，因此结果不应与实际不同.在某些情况下这不是真的(我已经提到过 LIKE).以下是 LIKE 不同情况的示例:

SELECT * FROM a WHERE a.foo LIKE ?

1. Param 1 == Foo - 如果索引存在，可以使用索引扫描.
2. Param 1 == %Foo - 需要全表扫描，即使存在索引
3. Param 1 == Foo% - 可能使用索引扫描，取决于索引的基数和其他因素

如果您加入，并且 where 子句产生不可能的组合(因此它会短路).例如:

SELECT * FROM a JOIN b ON a.id = b.id WHERE a.id = ?和 b.id = ?

如果第一个和第二个参数相同，它有一个执行计划，如果它们不同，就会短路(并返回0行而不命中任何数据)...

还有其他的，但我现在能想到的就这些......

I have a query

SELECT foo FROM bar WHERE some_column = ?

Can I get a explain plan from MySQL without filling in a value for the parameter?

解决方案

So long as you're doing only an equals (and not a like, which can have short circuit affects), simply replace it with a value:

EXPLAIN SELECT foo FROM bar WHERE some_column = 'foo';

Since it's not actually executing the query, the results shouldn't differ from the actual. There are some cases where this isn't true (I mentioned LIKE already). Here's an example of the different cases of LIKE:

SELECT * FROM a WHERE a.foo LIKE ?

1. Param 1 == Foo - Can use an index scan if an index exists.
2. Param 1 == %Foo - Requires a full table scan, even if an index exists
3. Param 1 == Foo% - May use an index scan, depending on the cardinality of the index and other factors

If you're joining, and the where clause yields to an impossible combination (and hence it will short circuit). For example:

SELECT * FROM a JOIN b ON a.id = b.id WHERE a.id = ? AND b.id = ?

If the first and second parameters are the same, it has one execution plan, and if they are different, it will short circuit (and return 0 rows without hitting any data)...

There are others, but those are all I can think of off the top of my head right now...

这篇关于如何在 MySQL 中解释带参数的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！