一、概述
这篇文章是数据库性能调优技术系列的第四篇。上一篇文章讲解了深入理解嵌套循环连接执行计划。
上一篇文章中提到两张表的连接有三种执行方式:1)嵌套循环连接;2)散列连接;3)归并连接。散列连接是很重要的连接方式,包含比较多的内容,这篇文章中讲解为什么需要散列连接?如何理解散列连接?
和前三篇文章一样,本文讲解的是些比较抽象的内容,不拘泥于具体的数据。所以本文中使用的代价评估模型也是抽象的,假设了数据库缓冲区大小只有一个页,新页的读取必然导致旧页的释放。读完本文之后应该能够读懂达梦数据库、oracle数据库、sqlserver数据库的执行计划。
二、深入理解嵌套循环执行计划
为什么要引入散列连接呢?假设两张表t1(c1 int,c2 int),t2(d1 int,d2 int),查询语句为select c1,d1 from t1 inner join t2 on c1=d1。如果数据库没有实现散列连接、合并连接的话,只能选择使用嵌套循环。从上篇文章中我们可以得到,对于t1的每一条记录,都需要遍历t2的每一条记录。因此,当t1的记录数数为m,t2的记录数为n,那么该查询语句访问的记录次数为m*n。当m=10000、n=10000时,那么m*n=100000000(1亿)。这是比较夸张的浪费时间。如果m是100万,n是100万,那么m*n就是1万亿次,读一万亿次记录,这是不能忍受的。
这里需要提到的一点是:我们不以读取记录的多少作为评价标准,在实际代价评估中,采用数据页(也可称为数据块,I/O的基本单位)。但是两者之间又是有联系的,假设每个页存放100个数据,那么t1的数据页为100页(10000/100),t2的数据页为100页,那么对于t1中的每一条记录,需要遍历t2的100页,加上该记录在t1中也属于一个数据页。因此,对于t1中的每一个记录,需要访问101个数据页。那么该查询的I/O量为:10000*(100+1)=1010000页。如果考虑到数据页的缓冲,情况会更加复杂。代价评估是个很复杂的课题,可能需要单独写个系列来阐述数据库查询优化系统的代价评估模型。这里我们不考虑数据页缓冲,也就相当于假设数据库缓冲区的大小仅仅为1个页。
好了,继续前面的话题。
如果t1(c1)上建立有唯一索引iut1c1(非唯一索引也是一样),那么可以将t2作为外表,对于t2的每一条记录,使用d1的值去命中索引iut1c1对应的B树。假设该B树的高度为3层,那么对于t2的每一条记录,需要访问t1表索引iut1c1中三个页(B树的高度),加上本身在t2中属于一个页。所以,在这种情况下,查询代价为:10000*(3+1)=40000页。
我们来对比一下,没有索引与有索引,两者之间的代价对比约等于25:1(比值1010000:40000)。也可以这么认为,假设没有索引的时候执行需要25s,那么有索引的情况下只需要1s。
这里我们把话题再延展下,如果m,n都为1000000,占用的块都为10000页(1000000/100)。没有索引的情况的I/O量为:1000000*(10000+1)=10001000000页。在t1(c1)有索引,该索引的高度对应的高度为4的情况下,假设I/O量为:100000*(4+1)=5000000。对比一下,没有索引与有索引,两者之间的代价比约等于2000:1。相等于,假设没有索引的情况下执行需要2000s,那么有索引的情况下只需要1s。
从上面的对比当中,我们可以发现索引的重要性,在实际应用当中,80%的查询性能问题来源于没有创建索引或者没有创建合适的索引。
索引,真是个好东西。如果用户没有创建索引,数据库内核也拿用户没办法,只能自己想办法。这里提出两种解决方法:1)建立临时索引;2)使用散列连接。
1)数据库内核使用建立临时索引的方法
大家可能听到过一个这样的概念:“在sqlserver系统中,如果用户没有创建索引,执行查询时,sqlserver会自动创建该索引。”
这里我们先撇开sqlserver到底是使用临时索引还是散列连接,我们只是对这句话加以理解。
对于上文提到的查询语句,执行过程描述如下:
1) create index itemp on t1(c1);
2) 执行查询语句select c1,d1 from t1 inner join t2 on c1=d1;
3) drop index itemp;
我们来评估下代价。如上文锁描述,假设m,n都为1000000,占用的块都为10000页。
首先是计算构造索引的代价:对t1的数据进行全扫描,对于每一条记录要插入到B树中,假设插入操作平均需要使用3个页。(因为起始时,B树只有一层,插入只需要访问1页,B树两层使需要访问2页,等等)。该步骤的代价为:1000000*(3+1)=4000000页。
然后计算查询的代价,前面已经计算过:100000*(4+1)=5000000页。
所以,整个代价为4000000+5000000=9000000页。
进行对比:10000:9:5(比值10001000000:9000000:5000000)。不使用索引的代价为10000,使用临时索引的代价为9,使用用户创建的索引代价为5。
所以,我们发现使用临时索引还是个不错的选择。
2)数据库内核使用散列连接的方法
首先我们讲下散列连接的原理:
1)对t1表(称为构建表)进行全扫描,对于每一个记录,对c1值进行使用内部散列函数,然后将该数据存放到相应的散列桶。
2)开始读t2表(称为探查散列表),对于t2的每一个记录,对d1值使用同样的散列函数,得到相应的散列值,查看该桶中是否有行。
如果相应的桶中没有行,则会丢失t2中这一行记录。如果散列桶中如果有一些行呢,则会精通的检查散列连接判断是否存在合适的匹配。因为不同的值可以产生同样的散列值。找到精确匹配的值,组合成记录放入结果集中。
我们来评估下代价。
1)首先我们先看构建散列的代价,对于t1的每一个记录,一般只需要访问一个散列桶。所以该步骤的代价为:1000000*(1+1)=2000000页。
2)对于t2的每一个记录,一般只需要访问一个散列桶。所以该步骤的代价为:1000000*(1+1)=2000000页。
所以,整个代价为2000000+2000000=4000000页。
进行对比:10000:4:5(比值10001000000:4000000:5000000),不使用索引的代价为10000,使用散列连接的代价为4,使用用户创建的索引代价为5。
是不是觉得不可思议?散列连接的代价竟然比使用索引的连接还小。我们通过一个例子来验证一下:
SQL> create table t1(c1 int,c2 int);
Table created.
SQL> begin
2for colval in 1..10000
3loop
4 insert into t1 values(colval,colval);
5end loop;
6end;
7/
PL/SQL procedure successfully completed.
SQL> create table t2(d1 int,d2 int);
Table created.
SQL> begin
2for colval in 1..10000
3loop
4 insert into t2 values(colval,colval);
5end loop;
6end;
7/
PL/SQL procedure successfully completed.
SQL> create index it1c1 on t1(c1);
Index created.
SQL>
查询语句“select c1,d1 from t1 inner join t2 on c1=d1;”对应的执行计划为:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=10000 Byte
s=260000)
1 0 HASH JOIN (Cost=13 Card=10000 Bytes=260000)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=10000 B
ytes=130000)
3 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=10000 B
ytes=130000)
从执行计划中,我们看出尽管t1(c1)建立了索引,数据库还是采用了散列连接。我们也许会经常疑惑:“为什么我创建了索引,数据库没使用该索引。”
各位可以验证一下,当你觉得应该可以使用索引,而数据库没有使用索引的情况一般会是:数据库使用散列连接代替了嵌套循环连接。千万不要将该结论进行延伸,从而得出:“我们不需要建立索引,数据库不使用索引”。数据库会根据查询代价进行合理的选择。哪种代价小,就会使用哪种执行计划进行执行。
我们再看该执行计划,“TABLE ACCESS (FULL) OF 'T1' (TABLE)”就是构建散列表,散列表构建之后就会执行“TABLE ACCESS (FULL) OF 'T2' (TABLE)”。比如对于t2的记录(1,1),使用散列函数得出hashvalue1,找到hashvalue1对应的桶,里面可能有几个值,这要看使用什么样的散列函数。假设散列函数是mod 10001,那么该桶里只会有一个记录(1,1)。如果散列函数是mod 9000。里面就会有记录(1,1)与(9001,9001)。这种情况下,我们要进行对比,对于记录(1,1)(对应(c1,c2)),因为满足c1=d1,所以构造处记录(1,1)(对应查询项(c1,d1))放入结果集,对于记录(9001,9001)不满足c1=d1,所以该记录不符合。如果t1表中有重复记录(1,1),那么这里就会产生两条记录插入到结果集中,因为:对于每个精确匹配c1=d1的记录都会组合成结果记录放入到结果集中。
分享到:
相关推荐
深入虚拟机---JVM调优总结(摘自网上网上大牛分享),主要是详细介绍了gc的收集,觉得不错。
1、create schema [数据库名称] default character set utf8 collate utf8_general_ci;--创建数据库 采用create schema和create database创建数据库的效果一样。 2、create user '[用户名称]'@'%' identified by...
提高计算机性能的 4 种方法(摘自微软) ....
一篇摘自北大研究生论文里面的网页技术文章
图数据库的架构与算法,摘自《大数据日知录》
PowerBuilder技术讲座(摘自计算机世界报) 数据库的事务管理 调用Windows的动态链接库 PowerBuilder面向对象的程序设计
Java数据库查询结果的输出 摘自:北京海脉信息咨询有限公司 利用Java开发数据库应用时,经常需要在用户界面上显示查询结果。我们可以利用Vector、JTable、AbstractTableModel等三个类较好地解决这一问题。 类...
在Visual Basic中连接和使用Access数据库中,为了确保数据库中信息的安全,往往要求对数据库文件进行加密,以防止非法用户通过其它的常规手段(如使用ACCESS将数据库文件打开、修改)将其打开。那么,在Visual Basic...
DM8是达梦公司在总结DM系列产品研发与应用经验的基础上,坚持开放创新、简洁实用的理念,历经五年匠心打磨,推出的新一代自研数据库。 DM8吸收借鉴当前先进新技术思想与主流数据库产品的优点,融合了分布式、弹性...
下面摘自原文 1. testShooping 是授课时完成的案例,用myeclipse导入即可使用(得配置数据库) 2. JavaMailtest 是一个javamail程序,可以发送邮件. 3. 更新的网上商城-数据库脚本.sql 是数据库脚本,在sql 2000的查询...
Pass4side Microsoft MB4-535 最近才更新的考题 摘自:www.pass4side.com 网站
CAD 技术发展历程概览_ 摘自 计算机世界报 CAD技术起步于50年代后期。进入60年代,随着在计算机屏幕上绘图变为可行而开始迅速发展。人们希望借助此项技术来摆脱繁琐、费时、绘制精度低的传统手工绘图。此时CAD技术的...
C#创建SQL Serve数据库这里我们主要介绍C#创建SQL Server数据库,包括介绍通过CREATE TABLE这句SQL语句完成创建表的操作等方面。转摘自网络
本文主要摘自《SQL编程技巧站》和《ChinaASP网络》,简述了关于数据库的常用知识
《高性能MySQL》是分享MySQL实用经验的图书。它不但可以帮助MySQL初学者提高使用技巧,更为有经验的MySQL DBA指出了开发高性能MySQL应用的途径。 --摘自百度百科
在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如...
熟悉后再一个函数一个函数地深入学习exosip提供的接口函数,就可以深入理解osip 了,达到间接学习oSIP的目的,同时也能从eXoSIP中学习到正确使用oSIP的良好的编程风格和语法格式。 而要成功编译...
在开始之前,先把MR的流程图帖出来(摘自Hadoop权威指南),方便后面对照。另外要说明的是,这个优化只是针对Hive0.9版本,而不是后来Hortonwork发起Stinger项目之后的版本。相对应的Hadoop版本是1.x而非2.x。Map...
Pass4side Oracle 1z0-033 最近才更新的考题,摘自:www.pass4side.com 网站
pgbouncer是postgresql数据库的一种轻量级数据库连接池。 什么是数据库连接池? 数据库连接池负责连接,管理,释放数据库连接,允许连接复用。 为什么要有数据库连接池? 摘自gitbub,解决网友因为公司网络问题不能...