如何使用常量优化MySQL的查询? [英] How do I optimize MySQL's queries with constants?

问题描述

注意:最初的问题尚无定论，但请扫描至底部以查找相关的内容.

我有一个要优化的查询，看起来像这样:

select cols from tbl where col = "some run time value" limit 1;

我想知道正在使用哪些键，但是无论我通过什么解释，它都可以将where子句优化为空(不可能在哪里注意到...")，因为我为它提供了一个常量.

• 有没有办法告诉mysql在说明中不进行常量优化?
• 我想念什么吗?
• 是否有更好的方法来获取我需要的信息?

EXPLAIN似乎给了我将由常量值产生的查询计划.由于查询是存储过程的一部分(并且spoc中的IIRC查询计划是在调用它们之前生成的)，这对我没有好处，因为该值不是恒定的.我想要的是找出优化器在不知道实际值是什么时将生成的查询计划.

我想念东西吗?

Edit2:在其他地方问，似乎MySQL总是会重新生成查询计划，除非您竭尽全力使其重新使用它们.即使在存储过程中.由此看来，我的问题似乎没有任何意义.

但是，这并没有使我真正想知道的事情变得毫无意义: 如何优化查询，该查询包含在任何特定查询中恒定的值，但是我(程序员)预先不知道将使用什么值? –例如，说我的客户端代码正在生成一个查询，该查询的where子句中带有数字.有时，该数字将导致不可能的where子句，而有时则不会.如何使用解释来检查查询的优化程度如何?

我立即发现的最佳方法是对存在/不存在的案例的完整矩阵在其上运行EXPLAIN.确实，这不是一个很好的解决方案，因为手工操作既困难又容易出错.

解决方案

例如，假设我的客户端代码正在where子句中生成带有数字的查询.

有时，数字将导致无法执行where子句，而在其他时候则不会.

如何使用解释来检查查询的优化程度如何?

MySQL为绑定参数的不同值构建不同的查询计划.

在此> 文章 中，您可以阅读MySQL优化器何时执行以下操作的列表:

    Action                                      When

    Query parse                                 PREPARE
    Negation elimination                        PREPARE
    Subquery re-writes                          PREPARE

    Nested JOIN simplification                  First EXECUTE
    OUTER->INNER JOIN conversions               First EXECUTE

    Partition pruning                           Every EXECUTE
    COUNT/MIN/MAX elimination                   Every EXECUTE
    Constant subexpression removal              Every EXECUTE
    Equality propagation                        Every EXECUTE
    Constant table detection                    Every EXECUTE
    ref access analysis                         Every EXECUTE
    range/index_merge analysis and optimization Every EXECUTE
    Join optimization                           Every EXECUTE

此列表中还缺少一件事.

MySQL可以在每次JOIN迭代上重建查询计划:这样的名称range checking for each record.

如果表上有复合索引:

CREATE INDEX ix_table2_col1_col2 ON table2 (col1, col2)

和类似的查询:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 = t1.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

，MySQL将不使用从(t1.value1, t1.value2_lowerbound)到(t1.value1, t1.value2_upperbound)的索引RANGE访问.取而代之的是，它将在(t1.value)上使用索引REF进行访问，只是过滤出错误的值.

但是，如果您这样重写查询:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 <= t1.value1
        AND t2.col1 >= t2.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

，然后MySQL将为table1 中的每个记录重新检查索引RANGE访问，并决定是否即时使用RANGE访问.

您可以在我的博客中的以下文章中了解其内容:

• > 为时间选择时间戳区域 -如何使用粗略过滤来过滤没有时区的时间戳
• 模拟跳过扫描 -如何在MySQL
中模拟SKIP SCAN访问方法
• 分析函数:优化LAG，LEAD，FIRST_VALUE，LAST_VALUE -如何在MySQL
中模拟Oracle的分析功能
• 高级行采样 -如何从MySQL
中的每个组中选择N记录

所有这些东西都使用RANGE CHECKING FOR EACH RECORD

回到您的问题:由于在给出常数之前没有计划，因此无法确定每个给定常数MySQL将使用哪个计划.

不幸的是，没有办法强制MySQL对绑定参数的每个值使用一个查询计划.

您可以使用STRAIGHT_JOIN和FORCE INDEX子句控制JOIN的顺序和INDEX的选择，但是它们不会在索引上强制使用某个访问路径或禁止IMPOSSIBLE WHERE. /p>

另一方面，对于所有JOIN，MySQL仅使用NESTED LOOPS.这意味着，如果您建立正确的JOIN订单或选择正确的索引，则MySQL可能会受益于所有IMPOSSIBLE WHERE.

NOTE: the original question is moot but scan to the bottom for something relevant.

I have a query I want to optimize that looks something like this:

select cols from tbl where col = "some run time value" limit 1;

I want to know what keys are being used but whatever I pass to explain, it is able to optimize the where clause to nothing ("Impossible WHERE noticed...") because I fed it a constant.

• Is there a way to tell mysql to not do constant optimizations in explain?
• Am I missing something?
• Is there a better way to get the info I need?

Edit: EXPLAIN seems to be giving me the query plan that will result from constant values. As the query is part of a stored procedure (and IIRC query plans in spocs are generated before they are called) this does me no good because the value are not constant. What I want is to find out what query plan the optimizer will generate when it doesn't known what the actual value will be.

Am I missing soemthing?

Edit2: Asking around elsewhere, it seems that MySQL always regenerates query plans unless you go out of your way to make it re-use them. Even in stored procedures. From this it would seem that my question is moot.

However that doesn't make what I really wanted to know moot: How do you optimize a query that contains values that are constant within any specific query but where I, the programmer, don't known in advance what value will be used? -- For example say my client side code is generating a query with a number in it's where clause. Some times the number will result in an impossible where clause other times it won't. How can I use explain to examine how well optimized the query is?

The best approach I'm seeing right off the bat would be to run EXPLAIN on it for the full matrix of exist/non-exist cases. Really that isn't a very good solution as it would be both hard and error prone to do by hand.

解决方案

For example say my client side code is generating a query with a number in it's where clause.

Some times the number will result in an impossible where clause other times it won't.

How can I use explain to examine how well optimized the query is?

MySQL builds different query plans for different values of bound parameters.

In this article you can read the list of when does the MySQL optimizer does what:

    Action                                      When

    Query parse                                 PREPARE
    Negation elimination                        PREPARE
    Subquery re-writes                          PREPARE

    Nested JOIN simplification                  First EXECUTE
    OUTER->INNER JOIN conversions               First EXECUTE

    Partition pruning                           Every EXECUTE
    COUNT/MIN/MAX elimination                   Every EXECUTE
    Constant subexpression removal              Every EXECUTE
    Equality propagation                        Every EXECUTE
    Constant table detection                    Every EXECUTE
    ref access analysis                         Every EXECUTE
    range/index_merge analysis and optimization Every EXECUTE
    Join optimization                           Every EXECUTE

There is one more thing missing in this list.

MySQL can rebuild a query plan on every JOIN iteration: a such called range checking for each record.

If you have a composite index on a table:

CREATE INDEX ix_table2_col1_col2 ON table2 (col1, col2)

and a query like this:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 = t1.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

, MySQL will NOT use an index RANGE access from (t1.value1, t1.value2_lowerbound) to (t1.value1, t1.value2_upperbound). Instead, it will use an index REF access on (t1.value) and just filter out the wrong values.

But if you rewrite the query like this:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 <= t1.value1
        AND t2.col1 >= t2.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

, then MySQL will recheck index RANGE access for each record from table1, and decide whether to use RANGE access on the fly.

You can read about it in these articles in my blog:

• Selecting timestamps for a time zone - how to use coarse filtering to filter out timestamps without a timezone
• Emulating SKIP SCAN - how to emulate SKIP SCAN access method in MySQL
• Analytic functions: optimizing LAG, LEAD, FIRST_VALUE, LAST_VALUE - how to emulate Oracle's analytic functions in MySQL
• Advanced row sampling - how to select N records from each group in MySQL

All these things employ RANGE CHECKING FOR EACH RECORD

Returning to your question: there is no way to tell which plan will MySQL use for every given constant, since there is no plan before the constant is given.

Unfortunately, there is no way to force MySQL to use one query plan for every value of a bound parameter.

You can control the JOIN order and INDEX'es being chosen by using STRAIGHT_JOIN and FORCE INDEX clauses, but they will not force a certain access path on an index or forbid the IMPOSSIBLE WHERE.

On the other hand, for all JOIN's, MySQL employs only NESTED LOOPS. That means that if you build right JOIN order or choose right indexes, MySQL will probably benefit from all IMPOSSIBLE WHERE's.

这篇关于如何使用常量优化MySQL的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！