数据库性能优化最重要的是SQL优化,SQL优化的关键离不开三点:表的连接方式、访问路径和执行顺序,本文重点介绍了几种常见的连接方式。
查询优化器的执行步骤如下。
1):查询语句涉及多个对象,每个对象数据的检索方法可以根据成本确定,无论是全表扫描还是索引访问。
2):结果集之间的关联方式主要包括、、等等。优化器判断结果集之间的连接方式尤为重要,因为判断结果会直接影响SQL执行效率。
3):当关联对象超过2个时,首先选择关联两个对象的结果集,然后关联第三个结果集。
以下是几种常见的连接方式。
图1显示嵌套循环连接示意图。
图1 嵌套循环连接示意图
1)两表相关,优化器将首先确定驱动表,也称为外表(outer table),另一个是驱动表,也称为内表(inner table)。一般情况下,优化器会将数据量小的定义为驱动表,在执行计划中,驱动表在上,被驱动表在下。
2)驱动表确认之后,会从其中提取一行有效数据,在被驱动表(内部表)中查找和匹配有效数据并提取。
3)将数据返回给客户端。
从以上步骤可以看出,,例如,驱动表最终根据筛选条件返回10行有效数据,每个返回将传输值与驱动表匹配,驱动表需要循环访问10次。示例代码如下:
SQL>SELECT/* USE_NL(ed)*/e.first_name,e.last_name,e.salary,d.department_name FROMhr.employeese,hr.departmentsd WHEREd.department_nameIN('Marketing','Sales') ANDe.department_id=d.department_id; SQL>select*fromtable(dbms_xplan.DISPLAY_CURSOR(null,null,'ALLSTATSLAST')); SQL_ID3nsqhdh150bx5,childnumber0 ------------------------------------- SELECT/* USE_NL(ed)*/e.first_name,e.last_name,e.salary, d.department_nameFROMhr.employeese,hr.departmentsdWHERE d.department_nameIN('Marketing','Sales')ANDe.department_id= d.department_id Planhashvalue:2968905875 ------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers| ------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||36|00:00:00.01|23| |1|NESTEDLOOPS||1|19|36|00:00:00.01|23| |*2|TABLEACCESSFULL|DEPARTMENTS|1|2|2|00:00:00.01|8| |*3|TABLEACCESSFULL|EMPLOYEES|2|10|36|00:00:00.01|15| -------------------------------------------------------------------------------------
从上述示例代码中可以看出,DEPARTMENTS为驱动表,Starts为1,说明只访问1次,返回2行有效数据(A-Rows实际返回的行数),EMPLOYEES被驱动表,Starts说明两次访问。
学过C 编程的学生应该记住,C 嵌套循环与以下循环有些相似:
#include<stdio.h> intmain() { inti,j; for(i=1;i<100;i ){ for(j=1;j<=100;j ) if(!(i%j))break; if(j>(i/j))printf("%d\n",i); } return0; }
j循环次数取决于i的取值范围,我们可以将i视为驱动表,j被视为被驱动表。
嵌套循环连接性能主要限于以下几点。
驱动表的返回行数。
驱动表的访问方式:如果驱动表的连接列基数小,选择性差,会导致整个表扫描的访问方式,效率很低。因此,我们建议连接列有索引,基数大,选择性高。
驱动表筛选后将返回少量数据。
相关字段需要驱动表有索引(连接列基数较大或选择性较高)。
两表关联后将返回少量数据。
适合于OLTP系统。
如果优化器选择了错误的连接方式,那么我们可以使用提示(hint)强制执行使用嵌套循环的连接方式:“/*+ USE_NL(TABLE1,TABLE2) LEADING(TABLE1) */”,其中TABLE1和TABLE2为关联表的别名,LEADING(TABLE1)用于将TABLE1指定为驱动表。
图2所示的是哈希连接示意图。
图2 哈希连接示意图
嵌套循环连接适用于两表关联后将返回少量数据的情况,那么。
1)两表等值关联。
2)优化器将数据量小的表作为驱动表,在PGA的SQL 工作区域(work areas)中,将驱动表的连接列构建成一张哈希表。
3)读取大表,对连接列进行哈希运算(检查哈希表,以查找连接的行)。
4)将数据返回给客户端。
从以上步骤中我们可以看出,示例代码如下:
SQL> SELECT /*+ USE_HASH(o l) */o.customer_id, l.unit_price * l.quantity
2 FROM oe.orders o, oe.order_items l
3 WHERE l.order_id = o.order_id;
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
SQL_ID cu980xxpu0mmq, child number 0
-------------------------------------
SELECT /*+ USE_HASH(o l) */o.customer_id, l.unit_price * l.quantity
FROM oe.orders o, oe.order_items l WHERE l.order_id = o.order_id
Plan hash value: 864676608
-------------------------------------------------------------------------------------------------------------
| Id | Operation |Name |Starts|E-Rows|A-Rows|A-Time |Buffers|Reads|OMem |1Mem |Used-Mem|
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 665 |00:00:00.04 | 57 | 5 | | | |
|* 1 | HASH JOIN | | 1 | 665 | 665 |00:00:00.04 | 57 | 5 |1888K|1888K|1531K (0)|
| 2 | TABLE ACCESS FULL|ORDERS | 1 | 105 | 105 |00:00:00.04 | 6 | 5 | | | |
| 3 | TABLE ACCESS FULL|ORDER_ITEMS| 1 | 665 | 665 |00:00:00.01 | 51 | 0 | | | |
-------------------------------------------------------------------------------------------------------------
从上述示例代码中我们可以看出,ORDERS为驱动表,Starts为1,说明访问1次,返回105行有效数据(A-Rows为实际返回的行数),ORDER_ITEMS为被驱动表,Starts也为1,说明仅访问1次。其中,OMem、1Mem为执行所需的PGA评估值,Used-Mem为实际执行时PGA中SQL工作区域消耗的内存(即发生磁盘交换的次数),当驱动表较大,PGA的SQL 工作区域无法完全容纳时,就会溢出到临时表空间产生磁盘交互,进而影响性能。
哈希连接性能主要受限于以下两点。
等值连接。
PGA SQL工作区域较小,且驱动表为大表时,容易出现性能问题。
当同时满足以下条件时,哈希连接方式将会非常有用。
两表等值关联后返回大量数据。
不同于嵌套循环连接,哈希连接被驱动表的连接字段时不需要有索引。
同样,我们也可以使用提示强制执行使用哈希连接的方式:“/*+ USE_HASH (TABLE1,TABLE2) LEADING(TABLE1) */”。
图3所示的是排序合并连接示意图。
图3 排序合并连接示意图
哈希连接适用于两表等值关联后返回大量数据的情况,那么
同时满足以下条件时,排序合并连接的性能要比哈希连接得好。
两表非等值关联(>、>=、<、<=、<>)。
数据源自身有序。
不必额外执行排序操作。
排序合并连接方式中没有驱动表的概念,连接查询流程具体如下。
1)两表根据关联列各自排序。
2)在内存中进行合并处理。
从以上实现步骤中我们可以看出,由于匹配的对象是连接列各自排序后的值,因此,示例代码如下:
SQL> SELECT o.customer_id, l.unit_price * l.quantity
FROM oe.orders o, oe.order_items l
WHERE l.order_id > o.order_id;
32233 rows selected..
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
SQL_ID ajyppymnhwfyf, child number 1
-------------------------------------
SELECT o.customer_id, l.unit_price * l.quantity FROM oe.orders o,
oe.order_items l WHERE l.order_id > o.order_id
Plan hash value: 2696431709
-----------------------------------------------------------------------------------------------------------
| Id | Operation |Name |Starts| E-Rows | A-Rows | A-Time |Buffers|OMem |1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 32233 |00:00:00.10 | 21 | | | |
| 1 | MERGE JOIN | | 1 | 3 4580 | 32233 |00:00:00.10 | 21 | | | |
| 2 | SORT JOIN | | 1 | 105 | 105 |00:00:00.01 | 4 |11264|11264|10240 (0)|
| 3 | TABLE ACCESS FULL |ORDERS | 1 | 105 | 105 |00:00:00.01 | 4 | | | |
|* 4 | SORT JOIN | | 105 | 665 | 32233 |00:00:00.05 | 17 |59392|59392|53248 (0)|
| 5 | TABLE ACCESS FULL |ORDER_ITEMS| 1 | 665 | 665 |00:00:00.01 | 17 | | | |
------------------------------------------------------------------------------------------------------------
从上述示例所示的执行计划中我们可以看出,ID=3的ORDERS表Starts为1,说明访问1次,返回105行有效数据(A-Rows为实际返回行数),ORDER_ITEMS表的Starts为1,说明也只访问1次,但ID=4的SORT JOIN表Starts为105,说明在内存中进行了105次匹配。其中,OMem、1Mem为执行排序操作所需的PGA评估值,Used-Mem为实际执行时PGA中SQL工作区域消耗的内存(即发生磁盘交换的次数)。
从以上步骤中我们可以看出,由于比较对象是两张表的连接列order_id,所以需要各自的连接列先完成排序(ID=2和ID=4),之后再进行匹配。
同样,我们也可以使用提示强制执行选择排序合并连接的方式:“/*+ USE_MERGE(TABLE1,TABLE2) */”。
优化器将一个数据源的每一行与另一个数据源的每一行连接在一起,以创建两组数据集的笛卡儿积。示例代码如下:
SQL> SELECT o.customer_id, l.unit_price * l.quantity
FROM oe.orders o, oe.order_items l;
69825 rows selected.
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
SQL_ID d3xygy88uqzny, child number 0
-------------------------------------
SELECT o.customer_id, l.unit_price * l.quantity FROM oe.orders o,
oe.order_items l
Plan hash value: 2616129901
-----------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts | E-Rows | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 125 | | | |
| 1 | MERGE JOIN CARTESIAN| | 1 | 69825 | 125 | | | |
| 2 | TABLE ACCESS FULL |ORDERS | 1 | 105 | 108 | | | |
| 3 | BUFFER SORT | | 105 | 665 | 17 | 27648 | 27648 |24576 (0)|
| 4 | TABLE ACCESS FULL |ORDER_ITEMS| 1 | 665 | 17 | | | |
-----------------------------------------------------------------------------------------------
从以上执行计划中我们可以看出,先对表order_items进行排序,然后进行两表的笛卡儿乘积操作,由于没有过滤条件,当数据量很大的时候,返回的行数将会非常多,因此
本文摘编于《DBA攻坚指南:左手Oracle,右手MySQL》。
本书是美创科技资深Oracle、MySQL技术专家呕心沥血之作,积作者多年的经验结晶和实践经验,也是目前市场上为数不多Oracle和MySQL相结合的数据库技术书籍。
书中内容主要分为Oracle和MySQL两大部分。
第一部分介绍Oracle的日常运维,该部分主要由四个章节构成,内容由浅入深逐步展开。包括生产环境搭建,上线前的压力测试,日常运维以及故障处理,迁移升级,SQL的优化技巧等内容;
第二部分是MySQL的运维实战,主要介绍MySQL常用的运维操作及实践,包括软件安装、备份恢复、迁移升级、架构设计、监控和性能优化等。书中提供了全面、实用的建议及具体的操作案例,以保证在复杂的核心生产环境中,能帮助读者可靠且高效地运行Oracle和MySQL数据库。
作者简介
扫码【华章计算机】视频号
每天来听华章哥讲书
更多精彩回顾
书讯 | 9月书讯 | 秋天的第一本书,来了
资讯 | 什么是Serverless?有哪些应用?终于有人讲明白了
书单 | 10本书,帮你看清未来的科技趋势
干货 | Linux30岁生日:Linux 成功的真正原因
收藏 | 终于有人把Scrapy爬虫框架讲明白了
上新 | 【新书速递】“帆船书”——数据库领域殿堂级作品,全新升级第7版!
赠书 |【第70期】终于找到了!AI学习路线图——从零基础到就业
点击购买