SQL 语句调优_减少或者避免笛卡尔乘积的发生

最新推荐文章于 2023-12-04 13:20:22 发布
最新推荐文章于 2023-12-04 13:20:22 发布
阅读量453 收藏 2
点赞数
文章标签： 数据库 开发工具

通过AWRPT的报告，发现了一个SQL 语句消耗CPU 资源比较厉害，而且这个SQL语句是一个核心的SQL 语句，每天被执行很多次。SQL 语句如下：

sql_id=9bukprkb82k31

SELECT * FROM PUB_MENU T WHERE ((T.MENU_STATE = 'Y' AND (T.MENU_ID IN (SELECT DISTINCT TC.MENU_ID FROM PUB_MENU TC START WITH TC.MENU_ID IN (SELECT DISTINCT TA.PARENT_MENU_ID FROM PUB_PAGE TB, PUB_MENU TA WHERE ( TB.NEED_ASSIGN = 0 OR EXISTS (SELECT 1 FROM PUB_ROLE_USER TB1, PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = :1)) AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N')) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID)) ) OR (T.MENU_STATE = 'N' AND (T.PAGE_ID = ' ' OR T.PAGE_ID = 'NO' OR T.PAGE_ID IS NULL OR (T.PAGE_ID IN (SELECT P.PAGE_ID FROM PUB_PAGE P WHERE P.NEED_ASSIGN = 0 OR EXISTS (SELECT 1 FROM PUB_ROLE_USER T1, PUB_PAGE_OF_ROLE T2 WHERE T1.ROLE_ID = T2.ROLE_ID AND T2.PAGE_ID = P.PAGE_ID AND T1.CAN_BE_ASSIGN = 0 AND T1.USER_ID = :2)))))) AND T.PARENT_MENU_ID = :3 ORDER BY T.SORT_ID

[@more@]

优化步骤：

  1. 格式化SQL语句:

SELECT * FROM SCDC.PUB_MENU T WHERE

(

(T.MENU_STATE = 'Y' AND

(T.MENU_ID IN

(SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

(SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

( TB.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID

AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = :1

)

) AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N') ####OR 条件里没有对TB表进行约束,造成TB 全表与OR 条件筛选后的TA记录进行了笛卡尔乘积

) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

)

)

)

OR

(T.MENU_STATE = 'N' AND

(T.PAGE_ID = ' ' OR T.PAGE_ID = 'NO' OR T.PAGE_ID IS NULL OR

(T.PAGE_ID IN

(SELECT P.PAGE_ID FROM SCDC.PUB_PAGE P WHERE P.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER T1, SCDC.PUB_PAGE_OF_ROLE T2 WHERE T1.ROLE_ID = T2.ROLE_ID AND T2.PAGE_ID = P.PAGE_ID AND T1.CAN_BE_ASSIGN = 0 AND T1.USER_ID = :2

)

)

)

)

)

)

AND T.PARENT_MENU_ID = :3 ORDER BY T.SORT_ID

  1. 寻找问题的原因

使用PL/SQL 开发工具分析该语句的执行计划,发现存在笛卡尔乘积的现象,该SQL语句在执行是占用了大量的CPU 资源,buffer gets也很,仔细分析了该语句,发现了产生笛卡尔乘积的SQL 所在: AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N')。

原因是:OR 条件里没有对TB表进行约束,造成TB 全表与OR 条件筛选后的TA记录进行了笛卡尔乘积

  1. 进行SQL 语句优化，使用UNION ALL 代替OR.

SELECT * FROM SCDC.PUB_MENU T WHERE

(

(T.MENU_STATE = 'Y' AND

(T.MENU_ID IN

(SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

(SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

( TB.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID

AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = :1

)

) AND TA.PAGE_ID = TB.PAGE_ID

UNION ALL

SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_MENU TA WHERE

(TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N'

) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

)

)

)

OR

(T.MENU_STATE = 'N' AND

(T.PAGE_ID = ' ' OR T.PAGE_ID = 'NO' OR T.PAGE_ID IS NULL OR

(T.PAGE_ID IN

(SELECT P.PAGE_ID FROM SCDC.PUB_PAGE P WHERE P.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER T1, SCDC.PUB_PAGE_OF_ROLE T2 WHERE T1.ROLE_ID = T2.ROLE_ID AND T2.PAGE_ID = P.PAGE_ID AND T1.CAN_BE_ASSIGN = 0 AND T1.USER_ID = :2

)

)

)

)

)

)

AND T.PARENT_MENU_ID = :3 ORDER BY T.SORT_ID

  1. 对新的SQL语句进行执行计划分析,发现笛卡尔乘积被消除，系统开销明显减少

  1. 附录: 数据库的执行计划

1. 原语句:

SQL> SELECT * FROM SCDC.PUB_MENU T WHERE

2 (

3 (T.MENU_STATE = 'Y' AND

4 (T.MENU_ID IN

5 (SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

6 ( SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

7 ( TB.NEED_ASSIGN = 0 OR EXISTS

8 (SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = '00000002' )

9 ) AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N')

10 ) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

11 )

12 )

13 )

14 ) AND T.PARENT_MENU_ID = '1000091' ORDER BY T.SORT_ID;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3411878230

---------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 130 | 4 (25)| 00:00:01 |

| 1 | SORT ORDER BY | | 1 | 130 | 4 (25)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 130 | 4 (25)| 00:00:01 |

| 3 | VIEW | VW_NSO_1 | 1 | 6 | 2 (0)| 00:00:01 |

| 4 | HASH UNIQUE | | 1 | 12 | | |

|* 5 | CONNECT BY WITH FILTERING | | | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | | | | |

| 7 | CONCATENATION | | | | | |

| 8 | MERGE JOIN CARTESIAN | | 1379 | 73087 | 5 (0)| 00:00:01 |####执行计划里有笛卡尔乘积

| 9 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN | IX_PUB_MENU_PAGEID | 1 | 23 | 2 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | 6 | 0 (0)| 00:00:01 |

| 12 | BUFFER SORT | | 1255 | 30120 | 5 (0)| 00:00:01 |

| 13 | INDEX FAST FULL SCAN | IX_PUB_PAGE | 1255 | 30120 | 3 (0)| 00:00:01 |

|* 14 | FILTER | | | | | |

| 15 | NESTED LOOPS | | 1471 | 77963 | 8 (13)| 00:00:01 |

|* 16 | HASH JOIN | | 1534 | 72098 | 8 (13)| 00:00:01 |

| 17 | INDEX FAST FULL SCAN | IX_PUB_PAGE | 1255 | 30120 | 3 (0)| 00:00:01 |

|* 18 | INDEX FAST FULL SCAN | IX_PUB_MENU_PAGEID | 1547 | 35581 | 4 (0)| 00:00:01 |

|* 19 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | 6 | 0 (0)| 00:00:01 |

| 20 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | IX_PUB_ROLE_USER | 73 | 1168 | 3 (0)| 00:00:01 |

|* 22 | INDEX UNIQUE SCAN | PK_PUB_PAGE_OF_ROLE | 1 | 23 | 0 (0)| 00:00:01 |

| 23 | NESTED LOOPS | | | | | |

| 24 | CONNECT BY PUMP | | | | | |

| 25 | TABLE ACCESS BY INDEX ROWID| PUB_MENU | 1 | 12 | 2 (0)| 00:00:01 |

|* 26 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 1 (0)| 00:00:01 |

|* 27 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | 1 | 124 | 1 (0)| 00:00:01 |

|* 28 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

10 - access("TA"."PAGE_ID"='NO' AND "TA"."MENU_STATE"='N')

11 - access("TC"."MENU_ID"="TA"."PARENT_MENU_ID")

14 - filter("TB"."NEED_ASSIGN"=0 OR EXISTS (SELECT 0 FROM "SCDC"."PUB_PAGE_OF_ROLE"

"TB2","SCDC"."PUB_ROLE_USER" "TB1" WHERE "TB1"."CAN_BE_ASSIGN"=0 AND "TB1"."USER_ID"='00000002'

AND "TB2"."PAGE_ID"=:B1 AND "TB1"."ROLE_ID"="TB2"."ROLE_ID"))

16 - access("TA"."PAGE_ID"="TB"."PAGE_ID")

18 - filter("TA"."PAGE_ID" IS NOT NULL AND (LNNVL("TA"."MENU_STATE"='N') OR

LNNVL("TA"."PAGE_ID"='NO')))

19 - access("TC"."MENU_ID"="TA"."PARENT_MENU_ID")

21 - access("TB1"."USER_ID"='00000002' AND "TB1"."CAN_BE_ASSIGN"=0)

22 - access("TB1"."ROLE_ID"="TB2"."ROLE_ID" AND "TB2"."PAGE_ID"=:B1)

26 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

27 - filter("T"."PARENT_MENU_ID"=1000091 AND "T"."MENU_STATE"='Y')

28 - access("T"."MENU_ID"="$nso_col_1")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

33320 consistent gets

0 physical reads

0 redo size

1063 bytes sent via SQL*Net to client

481 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

7 sorts (memory)

0 sorts (disk)

0 rows processed

2. 新语句:

SQL> set autotrace on;

SQL> set lines 200

SQL> SELECT * FROM SCDC.PUB_MENU T WHERE

2 (

3 (T.MENU_STATE = 'Y' AND

4 (T.MENU_ID IN

5 (SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

6 ( SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

7 ( TB.NEED_ASSIGN = 0 OR EXISTS

8 (SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = '00000002' )

9 ) AND TA.PAGE_ID = TB.PAGE_ID

10 UNION ALL

11 SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_MENU TA WHERE

12 (TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N'

13 ) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

14 )

15 )

16 )

17 ) AND T.PARENT_MENU_ID = '1000091' ORDER BY T.SORT_ID;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3770468488

####执行计划里没有笛卡尔乘积.COST 明显减少

---------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 130 | 4 (25)| 00:00:01 |

| 1 | SORT ORDER BY | | 1 | 130 | 4 (25)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 130 | 4 (25)| 00:00:01 |

| 3 | VIEW | VW_NSO_2 | 1 | 6 | 2 (0)| 00:00:01 |

| 4 | HASH UNIQUE | | 1 | 12 | | |

|* 5 | CONNECT BY WITH FILTERING | | | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | | | | |

| 7 | NESTED LOOPS | | 191 | 3629 | 17 (18)| 00:00:01 |

| 8 | VIEW | VW_NSO_1 | 191 | 2483 | 17 (18)| 00:00:01 |

| 9 | UNION-ALL | | | | | |

| 10 | SORT UNIQUE | | 190 | 8550 | 14 (15)| 00:00:01 |

|* 11 | FILTER | | | | | |

|* 12 | HASH JOIN | | 1245 | 56025 | 13 (8)| 00:00:01 |

| 13 | INDEX FAST FULL SCAN | IX_PUB_PAGE | 1255 | 30120 | 3 (0)| 00:00:01 |

|* 14 | TABLE ACCESS FULL | PUB_MENU | 1548 | 32508 | 9 (0)| 00:00:01 |

| 15 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |

|* 16 | INDEX RANGE SCAN | IX_PUB_ROLE_USER | 73 | 1168 | 3 (0)| 00:00:01 |

|* 17 | INDEX UNIQUE SCAN | PK_PUB_PAGE_OF_ROLE | 1 | 23 | 0 (0)| 00:00:01 |

| 18 | SORT UNIQUE | | 1 | 23 | 3 (34)| 00:00:01 |

|* 19 | INDEX RANGE SCAN | IX_PUB_MENU_PAGEID | 1 | 23 | 2 (0)| 00:00:01 |

|* 20 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | 6 | 0 (0)| 00:00:01 |

| 21 | NESTED LOOPS | | | | | |

| 22 | CONNECT BY PUMP | | | | | |

| 23 | TABLE ACCESS BY INDEX ROWID| PUB_MENU | 1 | 12 | 2 (0)| 00:00:01 |

|* 24 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 1 (0)| 00:00:01 |

|* 25 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | 1 | 124 | 1 (0)| 00:00:01 |

|* 26 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

11 - filter("TB"."NEED_ASSIGN"=0 OR EXISTS (SELECT 0 FROM "SCDC"."PUB_PAGE_OF_ROLE"

"TB2","SCDC"."PUB_ROLE_USER" "TB1" WHERE "TB1"."CAN_BE_ASSIGN"=0 AND "TB1"."USER_ID"='00000002'

AND "TB2"."PAGE_ID"=:B1 AND "TB1"."ROLE_ID"="TB2"."ROLE_ID"))

12 - access("TA"."PAGE_ID"="TB"."PAGE_ID")

14 - filter("TA"."PAGE_ID" IS NOT NULL)

16 - access("TB1"."USER_ID"='00000002' AND "TB1"."CAN_BE_ASSIGN"=0)

17 - access("TB1"."ROLE_ID"="TB2"."ROLE_ID" AND "TB2"."PAGE_ID"=:B1)

19 - access("TA"."PAGE_ID"='NO' AND "TA"."MENU_STATE"='N')

20 - access("TC"."MENU_ID"="$nso_col_1")

24 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

25 - filter("T"."PARENT_MENU_ID"=1000091 AND "T"."MENU_STATE"='Y')

26 - access("T"."MENU_ID"="$nso_col_1")

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

34339 consistent gets

0 physical reads

0 redo size

1063 bytes sent via SQL*Net to client

481 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

0 rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1040473/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1040473/

cps9988
关注
SQL语句-入门到精通
gqren003
07-31 1376
SQL，Structured Query Language，中文结构化查询语言，是一种对关系数据库 数据定义和操作语言。sql_mode是对SQL语法控制和效验的一套规则having用途having子句常用在group by子句过滤后，再进行判断筛选。having子句实践#1.统计中国每个省的城市个数，并把超过10个城市个数的输出#使用聚合函数,GROUP_CONCAT(NAME)#生产经验：having子句不走索引，效率低，尽量减少使用，除非结果集很少，如果结果集大，且非要用，可以从库做。
SQL server 数据库 SQL语句高级用法
最新发布
m0_71071763的博客
12-21 1567
SQLserver
如何避免查询结果出现笛卡尔积
Gorden的博客
08-31 1万+
首先，先简单解释一下笛卡尔积。 现在，我们有两个集合A和B。 A = {0,1}     B = {2,3,4} 集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式： A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}； B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}； 以上A×B和B×A的结果就可以...
join使用注意，防止笛卡尔乘积
05-12 1204
在数据库的表操作中，两个表求交经常会使用
sql 如何避免出现笛卡尔积运算
热门推荐
yuhr61的专栏
08-14 1万+
如何避免出现笛卡尔积运算. 如何避免出现笛卡尔积运算.   两表关联的时候通过最小的粒度关联 SQL>create volatile MULTISET TABLE   tb_test_01 ( statis_month number(6) ,area_code varchar2(5) ) ; insert into tb_test_01 values(200902,'A210
数据库中的笛卡尔积：定义、生成与避免策略
全栈川川
12-04 4627
数据库中的笛卡尔积：定义、生成与避免策略
Operation CONNECT BY WITH FILTERING
jackred的专栏
07-02 756
It happened to me several times to being asked about the mysterious full table scan in CONNECT BY operations. In this post I would like to share with you some of the information I wrote about it in
笛卡尔乘积查询.rar
07-02
本文将深入探讨笛卡尔乘积的概念、作用以及如何在SQL语句中实现它。 笛卡尔乘积是两个集合的组合，其中每个元素来自一个集合，并与另一个集合中的所有元素配对。如果集合A有m个元素，集合B有n个元素，那么A与B的...
sql和MySQL的语句执行顺序分析
09-09
理解SQL语句的执行顺序对于编写高性能的查询至关重要，因为它可以帮助开发者避免不必要的计算，提高查询效率，并且能够更准确地预测查询结果。在实际工作中，应根据具体需求灵活运用这些知识，以达到最优的数据库...
sql语句技巧小demo
05-18
### SQL语句技巧小demo #### 一、连接操作（Join） **1. 内连接 (Inner Join):** - **定义：** 内连接（INNER JOIN）返回两个表中存在匹配的所有行。如果某行在其中一个表中没有匹配，那么查询结果集中不会显示...
[Oracle SQL]最基本的connect by的用法
weixin_33697898的博客
06-18 833
最基本的connect by的用法: 需求1: 我需要下钻所有的树得到level和parent_name create table test_lvl1 (id number, parent_id number, name varchar2(10)); insert into test_lvl1 values (1,null,'SLI1'); insert into...
基于Oracle的SQL优化（崔华著）-学习笔记
dnil27295的博客
04-17 540
201704171025 01、 列rows记录的就是执行计划中每一个执行步骤所对应的Cardinality的值 列Cost(%CPU)记录的就是执行计划中的每一个执行步骤对应的成本 02、 Computed Cardinality=Original Cardinality * Selectivity 03、简单谓词连接 04、B树索引 ...
06 执行计划
weixin_34342578的博客
08-14 76
本章提要-------------------------------------------如何设置, 来获取执行计划如何读懂执行计划-------------------------------------------执行计划描述了SQL引擎为执行SQL语句进行的操作. 在任何时候, 要处理执行计划, 需以下三步, 获取, 解释, 判定效率.6.1 获取执行计划    获取的办法:    1)...
头疼的null值，自敬彬
cotchte0421的博客
07-10 110
11、NULL的研究在我看来,NULL是ORACLE中最"阴险"的东东,给开发人员带来了许多的麻烦！怎么个阴险狡诈呢？下面例子都来自我在工作中发现并总结的小小心得，现在以实验的形式整理如下，希望对大家有用。11.1、upda...
SQL优化案例之exists中套or not exists
落落的专栏 专注SQL调优 性能调优
10-04 1204
一个SQL带你了解SQL等价改写各种姿势
sql 语句中or条件之种种情况--
weixin_30952103的博客
08-17 453
sql 语句中or条件之种种情况 1、相同字段or条件，转换为inlist 走index range scanSQL> select * from test_or a2where a.object_id=20 or a.object_id=21;执行计划----------------------------------------------------------Plan ha...
如何让Not in语句跑出与not exists相当的效率
cuiqiong6888的博客
11-10 91
关于Not in和Not exists的讨论由来已久，not exists在性能上要优于not in，一时间几乎所有包含not in的select语句都被要求替换成not exists，那么not in是否真的那么实力不...
[20190524]使用use_concat or_expand提示优化.txt
weixin_34250434的博客
05-24 135
[20190524]使用use_concat or_expand提示优化.txt--//上午看了链接https://connor-mcdonald.com/2019/05/22/being-generous-to-the-optimizer，突然想起我们生产系统类似语句。--//现在想想觉得开发的想象力太丰富，写这些语句是否考虑长期运行导致的结果。对方例子相对简单，我优化的例子简直就是变态。--/...
MySQL学习之《多表查询》
一个野生自学的运维狗
06-08 2115
多表查询 1.熟悉常见的几个表2.出现笛卡尔积的错误 3.多表查询的正确方式:需要有连接条件 4.如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。5.可以给表起别名，在SELECT和WHERE中使用表的别名。 等值连接vs非等值连接 自连接vs非自连接内连接vs外连接内连接 UNION和UNION ALL的使用7种JOIN的实现: 10.sQL99语法的新特性1:自然连接 2:USING ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值