SQL查询优化

Question

请对比以下内容：

INNER JOIN table1 t1 ON t1.someID LIKE 'search.%' AND 
                        t1.someID = ( 'search.' || t0.ID )

对比。

INNER JOIN table1 t1 ON t1.someID = ( 'search.' || t0.ID )

我听说第一种情况是被优化过的。但是你知道，我不明白为什么会这样。就我所理解的来说，第二个例子应该运行得更快。

我们使用Oracle，但我想目前这并不重要。

如果我错了，请解释一下。

谢谢

- Alexandr

---

如果我错了，有人请纠正我，但这不应该放在http://codereview.stackexchange.com上吗？ - James Khoury

我从未见过这种语法：t1.someID = ( 'search.', t0.ID )。在我工作过的大多数数据库中，它都是无效的。你可以使用t1.someID In( 'search.', t0.ID )，但我从未见过一个数据库可以直接将列设置为元组相等。 - Thomas

@Thomas，我已经修复了。这是Oracle的字符串连接。 - Alexandr

Answer 1

因此，这是一个仅在连接的字符串上进行连接的查询的解释计划：

SQL> explain plan for
  2     select e.* from emp e
  3         join big_table bt on bt.col2 = 'search'||trim(to_char(e.empno))
  4  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 179424166

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  1052 | 65224 |    43   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |          |  1052 | 65224 |    43   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP      |    20 |   780 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | BIG_VC_I |    53 |  1219 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("BT"."COL2"='search'||TRIM(TO_CHAR("E"."EMPNO")))

15 rows selected.

SQL>

将查询计划中包含LIKE子句的查询与本计划进行比较和对比:

SQL> explain plan for
  2     select e.* from emp e
  3           join big_table bt on (bt.col2 like 'search%'
  4               and bt.col2 = 'search'||trim(to_char(e.empno)))
  5  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 179424166

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    62 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |          |     1 |    62 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP      |     1 |    39 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | BIG_VC_I |     1 |    23 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter('search'||TRIM(TO_CHAR("E"."EMPNO")) LIKE 'search%')
   3 - access("BT"."COL2"='search'||TRIM(TO_CHAR("E"."EMPNO")))
       filter("BT"."COL2" LIKE 'search%')

17 rows selected.

SQL>

第二个查询的成本比第一个要低得多。但这是因为优化器估计第二个查询返回的行数比第一个要少得多。更多的信息可以使数据库做出更准确的预测。（事实上，查询不会返回任何行）。

当然，这需要假设连接列已经建立索引，否则不会有任何区别。

另一件需要注意的事情是被查询的列可能会影响执行计划。这个版本从BIG_TABLE而不是EMP中选择。

SQL> explain plan for
  2     select bt.* from emp e
  3           join big_table bt on (bt.col2 like 'search%'
  4                        and bt.col2 = 'search'||trim(to_char(e.empno)))
  5  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

Plan hash value: 4042413806

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    46 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |           |       |       |            |          |
|   2 |   NESTED LOOPS               |           |     1 |    46 |     4   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN           | PK_EMP    |     1 |     4 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | BIG_VC_I  |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE |     1 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter('search'||TRIM(TO_CHAR("E"."EMPNO")) LIKE 'search%')
   4 - access("BT"."COL2"='search'||TRIM(TO_CHAR("E"."EMPNO")))
       filter("BT"."COL2" LIKE 'search%')

19 rows selected.

SQL>

- APC

Answer 2

各种数据库引擎的查询分析会真正讲述这个故事，但我的第一反应是第一个表单实际上已经被优化了。原因在于编译器无法猜测连接的结果。它必须做更多的工作来确定匹配的值，并且可能导致表扫描。第一个查询仍然必须这样做，但是它能够首先使用LIKE运算符（假设someID列上存在索引）缩小结果集，从而需要进行更少的连接。

- Thomas