查询优化-WHERE子句中的表达式顺序 [英] Query optimization - order of expressions in WHERE clause

问题描述

让我们说我有一个名为数字的表:

Lets say I have a table named numbers:

userID  ColA  ColB
------------------
25      10     11
25      10     16
28      10     11
28      10     16
29      12     14  
29      10     16

我想查找所有具有用户ID 28且colA = 10且colB = 16的行

I want to find all rows which have userID 28 and colA = 10 and colB = 16

我的问题是查询结构.如果我先查找userID，然后再查找colA和colB值，例如

My question is of query structure. If I look for the userID first, and then the colA and colB values e.g.

select * from numbers where userID=28 AND (colA=10 AND colB=16)

这比例如首先查找值要快

is this faster than looking for values first e.g.

select * from numbers where (colA=10 AND colB=16) AND userID=28

还是真的没有太大区别?我想问的是引擎如何读取查询结果，因为(colA = 10 AND colB = 16)的结果多于userID = 28的结果.因此，我会首先假设可能性最小的吗?

Or is there really not much difference? I guess I'm asking about how the engine reads the query results as there are more results for (colA=10 AND colB=16) than there are userID=28. Therefore I would assume least possibles first?

我也了解索引等.

推荐答案

您的RDBMS将为该查询找出最佳执行计划.您将无法更改这些子句的顺序来提高性能，因为RDBMS在制定计划时已经考虑了类似的事情.

Your RDBMS will figure out the best execution plan for that query. You won't be able to change the order of those clauses to improve performance because the RDBMS will already consider things like that when coming up with a plan.

您可以使用 EXPLAIN 命令查看更多信息有关RDBMS决定使用的实际执行计划.

You can use the EXPLAIN command to see more information about the actual execution plan the RDBMS decided to use.

这篇关于查询优化-WHERE子句中的表达式顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！