mysql - 如何在“where”子句中使用用户定义的参数优化查询？

Question

我正在学习如何使用索引进行适当的查询优化。假设我有一个庞大的产品表，其中包含每种产品的各种详细信息，例如价格，类别，购买数量，平均评价等。当具有多个“ where”条件时，我了解到最好将多列索引放在出现的位置的任何“ where”条件上。

但是，如果有许多不同目的的查询以及用户选择如何过滤产品表，那么我很难弄清楚如何扩展它。例如，用户可以浏览产品WHERE rating > 4 AND purchases > 100，也可以是WHERE category = 'x' AND price < 100 AND price > 20。如果选择要过滤的列是随机的，那么正确的多列索引将如何工作？

Answer 1

我了解到，最好将多列索引按出现的顺序放在“何处”条件上。


您了解到...不太正确。

WHERE子句中的出现顺序没有意义，因为优化器可以自由地以任何逻辑上有效的方式评估条件，当然要服从括号和逻辑运算符（AND，OR等）。表达方式。

多列索引中的列顺序很重要，因为从左到右，只要在where子句中未提及的索引中遇到列，就无法再使用该索引右侧的内容。

如果索引了3列（a，b，c），并且查询为WHERE a = 1 AND c = 6，则优化器将只能使用该索引中最左边的“ a”列值，而不能使用“ c”。

在那种情况下，它可能仍会选择使用索引来查找a = 1的行，然后只扫描所有已标识的行以查找c = 6的行。

您可以将多列索引可视化为多维数组。在没有已知值或范围的情况下，您需要与第一列（a）匹配，第二列（b）的值是无意义的，无序的数据混杂，因为它们按“ a”组进行排序。 ..您必须遍历每个“ a”以找到匹配的“ b”值，并遍历每个“ a，b”以找到匹配的“ c”值。由于在上面的示例中，由于未指定“ b”值，因此它是“ anything”，因此对于优化查询，“ c”值的排序是没有意义的，并且无法访问（尽管SELECT列表中的每一列如果在单个索引中有可用索引，优化器可能会扫描索引而不是扫描整个表，而是将其视为“覆盖索引”，这通常比全表扫描要好，但仍然不是最佳选择。

如果您的WHERE子句包括两列都分别进行索引的列，则优化器将检查索引统计信息，并尝试使用最有可能产生最少匹配项的列...如果分别为“ a”和“ c”有一个单独的索引，索引统计信息表明“ c”（高基数）有很多值，但“ a”（低基数）只有几个值，优化器通常会使用“ c”上的索引来查找匹配项行，然后在所有这些行中扫描请求的“ a”值。

或者，它可能尝试使用两个索引的并集来精确标识哪些行同时满足两个条件。

这两种策略都不是最佳选择，但仍然比全表扫描要好得多，因此它建议您-至少-将每个可独立搜索的列作为索引中的最左列... ，可以单独查询的任何列，而WHERE子句中没有其他列，并返回合理大小的结果集。如果结果集的大小不合理，则可能希望限制用户在应用程序中搜索其他属性。

在WHERE category = 'x' AND price < 100 AND price > 20的情况下，更好的索引是（category，price）而不是（price，category），但这不是因为WHERE子句中的表达式排序。因为类别是相等性测试，但是价格是范围。 WHERE price < 100 AND price > 20 AND category ='x'是等效的，并且（category，price）仍然是合适的索引-因为索引是按第一列排序，然后在第一列的每个值内，它们按第二列的值排序，然后在每对（第一对，第二对），它们都是根据第三列中的值进行无穷排序...因此，对于（类别，价格），服务器将直接转到类别='x'的所有行，并在该分组中在索引中，所引用的行已按价格排序，因此只需选择索引类别“ x”内的价格范围即可。最佳。 （价格，类别）索引要求检查范围内的所有价格，然后为所有这些价格确定类别值。索引仍然可以使用，但是根据条件，优化器仍然可以选择扫描整个表。

如果在未建立索引的WHERE子句中添加第三个条件，则将遵循相同的路径，但是服务器将扫描已标识的行以查找与未建立索引的列的所需值匹配的项。同样，取决于您的业务需求，它不是最理想的，但通常可以接受-在确定此问题的正确答案方面起着一定的作用。

每个索引都需要空间和资源，因为每个插入，更新和删除操作都要求服务器（此时）对受表更改影响的每个索引进行必要的更改。

还要注意，如果在（a，b）或（a，b，c）等上有一个索引，那么（a）上的单独索引通常被认为是浪费空间，因为（a ,. ..anything-else ...）还将用作（a）的索引。

尝试使用EXPLAIN SELECT（从MySQL 5.6开始还支持INSERT / UPDATE / DELETE）和真正地understanding its output是理解索引如何工作的必不可少的工具。 MySQL 5.6还支持optimizer tracing，它为您提供有关优化器如何理解查询，所考虑的各种计划，每个计划的估计成本以及如何得出如何执行特定查询的决定的详细输出。