SQL FROM大表和小表的顺序, %位置的性能影响

1.B

 

ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving 

 

table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如

 

果有3个以上的表连接查询, 那就需要选择交叉表 (intersection table)作为基础表, 交叉表是指那个被

 

其他表所引用的表.

 

2.如果有索引, 按照性能高低排序 C>A>B, 如果没有索引, 则根据返回数据量判断性能高低

 

我们看有索引的情况:

针对DB2和Oracle数据库, 请看摘自IBM的文档:

General READ SQL optimization for DB2 and Oracle

These techniques apply to both DB2 and Oracle, however, the rules appear to yield a better response time on DB2.

1. Avoid using wildcard (%) at the beginning of a predicate.
   The predicate LIKE '%abc' causes full table scan. For example:SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'

   This is a known performance limitation in all databases.

 

针对SQL Server数据库, 请看摘自Microsoft的文档:

SR0005: Avoid using patterns that start with “%” in LIKE predicates

Rule Description:

You could cause a table scan if you use a WHERE clause that contains a LIKE predicate such as '%pattern string' to search for text that can occur anywhere in a column.

How to Fix Violations:

To resolve this issue, you should change the search string so that it starts with a character that is not a wildcard (%), or you should create a full-text index.

 

其他数据库暂时没有去考证

 

由此看出A,B都会造成full table scan, C不会, 则C肯定比A,B快

 

以下是一段测试数据:

select *
from dbo.v_R_System
where NETBIOS_NAME0 = 'COMPUTER'

select *
from dbo.v_R_System
where NETBIOS_NAME0 LIKE 'COMPUTER'

select *
from dbo.v_R_System
where NETBIOS_NAME0 LIKE 'COMPUT%'

select *
from dbo.v_R_System
where NETBIOS_NAME0 LIKE '%COMPUT%'

When you hit CTRL+L to get the execution plan, for each of these, and mouse-over the operator on the far left, you'll see the following:

 

Notice, the first query uses = instead of LIKE.  It's got the lowest cost and uses an INDEX SEEK.  Very fast and efficient..What we would expect.

The second query uses LIKE, and no wildcards.  The cost is still pretty low and it uses an INDEX SEEK. 

The third query uses LIKE, and a wildcard, but doesn't START with a wildcard.  The cost is the same as the second and still uses INDEX SEEK.

The fourth query however, uses LIKE and starts with a wildcard.  It still uses an index, but the cost is higher and it's an INDEX SCAN so it's going to be less efficient and slower.

 

再来看A和B, A是匹配以TEST字段为后缀的数据, B是匹配以TEST字段为后缀的以及包含TEST字段的数据, 则A的数据返回量最多跟B相等, 所以A的性能高于B