如何在MySQL中解释带参数的查询

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?

我可以从MySQL获得解释计划而不填写参数值吗?

3 个解决方案

#1

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:

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

SELECT * FROM a WHERE a.foo LIKE ?

Param 1 == Foo - Can use an index scan if an index exists.

Param 1 == Foo - 如果索引存在,可以使用索引扫描。

Param 1 == %Foo - Requires a full table scan, even if an index exists

Param 1 ==%Foo - 即使存在索引,也需要全表扫描

Param 1 == Foo% - May use an index scan, depending on the cardinality of the index and other factors

Param 1 == Foo% - 可以使用索引扫描,具体取决于索引的基数和其他因素

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

如果您正在加入,并且where子句产生一个不可能的组合(因此它将短路)。例如:

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)...

如果第一个和第二个参数相同,则它有一个执行计划,如果它们不同,它将短路(并返回0行而不点击任何数据)......

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

还有其他人,但这些都是我现在能想到的全部......

#2

The explain plan may be different depending on what you put in. I think explain plans without real parameter don't mean anything.

根据你投入的内容,解释计划可能会有所不同。我认为没有真实参数的解释计划并不意味着什么。

#3

I don't think it's possible. WHERE some_column ='value', WHERE some_column = other_column and WHERE some_column = (SELECT .. FROM a JOIN b JOIN c ... WHERE ... ORDER BY ... LIMIT 1 ) return different execution plans.

我不认为这是可能的。 WHERE some_column ='value',WHERE some_column = other_column和WHERE some_column =(SELECT .. FROM JOIN b JOIN c ... WHERE ... ORDER BY ... LIMIT 1)返回不同的执行计划。

#1