如何优化MySQL的常量查询？

问题描述：

注意：原始问题没有实际意义，但扫描到底部以获取相关内容。如何优化MySQL的常量查询？

我有一个查询我想优化，看起来是这样的：

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

我想知道正在使用哪些密钥，但无论我传递给解释一下，它能够优化where子句（“不可能在哪里注意到...”），因为我给它一个常量。

有没有办法告诉mysql不要在解释中做不断的优化？
我错过了什么吗？
有没有更好的方式来获取我需要的信息？

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

我错过了吗？

Edit2：在其他地方问一下，似乎MySQL总是重新生成查询计划，除非你不想让它重新使用它们。即使在存储过程中。从这看来，我的问题似乎没有实际意义。

但是，这并不使我真正想知道的实际意义：如何优化包含任何特定的查询中是恒定的，但在那里我，程序员，事先不知道值查询什么价值将被使用？ - 例如，假设我的客户端代码正在使用where子句中的数字生成查询。在某些情况下，该号码将导致不可能在其他时间条款不会。我如何使用解释来检查查询的优化程度？

我现在看到的最好的方法就是运行EXPLAIN上的存在/不存在案例的完整矩阵。真的，这不是一个很好的解决方案，因为这很难，也很容易出错。

答

例如，说我的客户端代码正在生成一个查询，其中有where子句中的数字。

有些时候这个数字会导致一个不可能的where子句其他时间不会。

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

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

在此article你可以阅读的时候做的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复检将每个记录索引RANGE访问从table1，并决定是否使用上飞RANGE访问。

您可以在我的博客，这些文章读到它：

Selecting timestamps for a time zone - 如何使用粗过滤，过滤掉时间戳没有时区
Emulating SKIP SCAN - 如何模拟SKIP SCAN访问方法MySQL
Analytic functions: optimizing LAG, LEAD, FIRST_VALUE, LAST_VALUE - 如何模拟Oracle的分析功能MySQL
Advanced row sampling - 如何选择N点的记录从每个组中MySQL

所有这些事情都采用RANGE CHECKING FOR EACH RECORD

回到你的问题：没有办法知道哪些计划将MySQL利用每一个给定的常量，因为没有计划在给定常数之前。

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

，可以控制通过使用STRAIGHT_JOIN和FORCE INDEX子句所选用的JOIN顺序和INDEX“ES，但它们不会强迫上索引的某些访问路径或禁止IMPOSSIBLE WHERE。

另一方面，对于所有JOIN's，MySQL只雇用NESTED LOOPS。这意味着如果您订购JOIN订单或选择正确的索引，MySQL可能会受益于所有IMPOSSIBLE WHERE的。

答

由于您指定的值不在列中，而不仅仅是因为它是一个常量，所以您会收到“不可能的WHERE注意事项”。你可以：1）使用该列中存在或值2）只是说col = col：

explain select cols from tbl where col = col;

那些幽冥解决了我的问题。我想知道查询计划是优化器*不知道的地方*如果值在列中。 – BCS 2008-11-23 03:01:28

下面是它的工作原理：优化器通过读取const和系统表来确定选择是否可能，如果是，则获取查询计划。我的解决方案将为您提供查询计划，因为优化程序不会提前停止，因为它认为查询是不可能的。 – 2008-11-23 03:28:24

是的，它会给你一个查询计划并避免where子句永不传递的问题，但是它会假设where子句将总是*传递，而且也不是这种情况。我想要的是知道事情将如何执行这两种情况。（见编辑2） – BCS 2008-11-23 21:19:28

答

如何优化查询与价值，只有到查询是恒定的，但在那里我，程序员，事先不知道将使用什么价值？

通过使用特定列上的索引（或者如果您总是一起查询给定的列，甚至可以组合列）。如果您有索引，查询计划员可能会使用它们。

关于“不可能”的数值：查询规划可以得出结论，一个给定的值不在表从几个来源：

如果有特定的列的索引，其可以观察到特定值大于或小于索引中的任何值（最小/最大值需要从索引中抽取一段时间）
如果传入的类型错误（如果要求数字列与文本相等）

PS。一般来说，查询计划的创建并不昂贵，重新创建比重新使用它更好，因为自查询计划生成并且可能存在更好的查询计划以来，条件可能已发生变化。

如何优化MySQL的常量查询？

相关推荐