- 浏览: 8607 次
- 性别:
- 来自: 南京
文章分类
最新评论
见 http://www.itpub.net/forum.php?mod=viewthread&tid=239011
Web翻页优化实例
提交时间: 2004-6-18 15:37:49 回复 发消息
环境:
Linux version 2.4.20-8custom (root@web2) (gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)) #3 SMP Thu Jun 5 22:03:36 CST 2003
Mem: 2113466368
Swap: 4194881536
CPU:两个超线程的Intel(R) Xeon(TM) CPU 2.40GHz
优化前语句在mysql里面查询15秒左右出来,转移到oracle后进行在不调整索引和语句的情况下执行时间大概是4-5秒,调整后执行时间小于0.5秒。
翻页语句:
SELECT * FROM (SELECT T1.*, rownum as linenum FROM (
SELECT /*+ index(a ind_old)*/
a.category FROM auction_auctions a WHERE a.category =' 170101 ' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641
被查询的表:auction_auctions(产品表)
表结构:
SQL> desc auction_auctions;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(32)
USERNAME VARCHAR2(32)
TITLE CLOB
GMT_MODIFIED NOT NULL DATE
STARTS NOT NULL DATE
DESCRIPTION CLOB
PICT_URL CLOB
CATEGORY NOT NULL VARCHAR2(11)
MINIMUM_BID NUMBER
RESERVE_PRICE NUMBER
BUY_NOW NUMBER
AUCTION_TYPE CHAR(1)
DURATION VARCHAR2(7)
INCREMENTNUM NOT NULL NUMBER
CITY VARCHAR2(30)
PROV VARCHAR2(20)
LOCATION VARCHAR2(40)
LOCATION_ZIP VARCHAR2(6)
SHIPPING CHAR(1)
PAYMENT CLOB
INTERNATIONAL CHAR(1)
ENDS NOT NULL DATE
CURRENT_BID NUMBER
CLOSED CHAR(2)
PHOTO_UPLOADED CHAR(1)
QUANTITY NUMBER(11)
STORY CLOB
HAVE_INVOICE NOT NULL NUMBER(1)
HAVE_GUARANTEE NOT NULL NUMBER(1)
STUFF_STATUS NOT NULL NUMBER(1)
APPROVE_STATUS NOT NULL NUMBER(1)
OLD_STARTS NOT NULL DATE
ZOO VARCHAR2(10)
PROMOTED_STATUS NOT NULL NUMBER(1)
REPOST_TYPE CHAR(1)
REPOST_TIMES NOT NULL NUMBER(4)
SECURE_TRADE_AGREE NOT NULL NUMBER(1)
SECURE_TRADE_TRANSACTION_FEE VARCHAR2(16)
SECURE_TRADE_ORDINARY_POST_FEE NUMBER
SECURE_TRADE_FAST_POST_FEE NUMBER
表记录数及大小
SQL> select count(*) from auction_auctions;
COUNT(*)
----------
537351
SQL> select segment_name,bytes,blocks from user_segments where segment_name ='AUCTION_AUCTIONS';
SEGMENT_NAME BYTES BLOCKS
AUCTION_AUCTIONS 1059061760 129280
表上原有的索引
create index ind_old on auction_auctions(closed,approve_status,category,ends) tablespace tbsindex compress 2;
SQL> select segment_name,bytes,blocks from user_segments where segment_name = 'IND_OLD';
SEGMENT_NAME BYTES BLOCKS
IND_OLD 20971520 2560
表和索引都已经分析过,我们来看一下sql执行的费用
SQL> set autotrace trace;
SQL> SELECT * FROM (SELECT T1.*, rownum as linenum FROM (SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum <18681) WHERE linenum >= 18641;
40 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19152 Card=18347 Byt
es=190698718)
1 0 VIEW (Cost=19152 Card=18347 Bytes=190698718)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=19152 Card=18347 Bytes=190460207)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'AUCTION_AUCTIONS'
(Cost=19152 Card=18347 Bytes=20860539)
5 4 INDEX (RANGE SCAN) OF 'IND_OLD' (NON-UNIQUE) (Cost
=810 Card=186003)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19437 consistent gets
18262 physical reads
0 redo size
114300 bytes sent via SQL*Net to client
56356 bytes received via SQL*Net from client
435 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40 rows processed
我们可以看到这条sql语句通过索引范围扫描找到最里面的结果集,然后通过两个view操作最后得出数据。其中18502 consistent gets,17901 physical reads
我们来看一下这个索引建的到底合不合理,先看下各个查寻列的distinct值
select count(distinct ends) from auction_auctions;
COUNT(DISTINCTENDS)
-------------------
338965
SQL> select count(distinct category) from auction_auctions;
COUNT(DISTINCTCATEGORY)
-----------------------
1148
SQL> select count(distinct closed) from auction_auctions;
COUNT(DISTINCTCLOSED)
---------------------
2
SQL> select count(distinct approve_status) from auction_auctions;
COUNT(DISTINCTAPPROVE_STATUS)
-----------------------------
5
页索引里列平均存储长度
SQL> select avg(vsize(ends)) from auction_auctions;
AVG(VSIZE(ENDS))
----------------
7
SQL> select avg(vsize(closed)) from auction_auctions;
AVG(VSIZE(CLOSED))
------------------
2
SQL> select avg(vsize(category)) from auction_auctions;
AVG(VSIZE(CATEGORY))
--------------------
5.52313106
SQL> select avg(vsize(approve_status)) from auction_auctions;
AVG(VSIZE(APPROVE_STATUS))
--------------------------
1.67639401
我们来估算一下各种组合索引的大小,可以看到closed,approve_status,category都是相对较低集势的列(重复值较多),下面我们来大概计算下各种页索引需要的空间
column distinct num column len
ends 338965 7
category 1148 5.5
closed 2 2
approve_status 5 1.7
index1: (ends,closed,category,approve_status) compress 2
ends:distinct number---338965
closed: distinct number---2
index size=338965*2*(9+2)+ 537351*(1.7+5.5+6)=14603998
index2: (closed,category,ends,approve_status)
closed: distinct number---2
category: distinct number---1148
index size=2*1148*(2+5.5)+537351*(7+1.7+6)=7916279
index3: (closed,approve_status,category,ends)
closed: distinct number---2
approve_status: distinct number―5
index size=2*5*(2+1.7)+537351*(7+5.5+6)=9941030
结果出来了,index2: (closed,category,ends,approve_status)的索引最小
我们再来看一下语句
SELECT * FROM (SELECT T1.*, rownum as linenum FROM (SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum <18681) WHERE linenum >= 18641;
可以看出这个sql语句有很大优化余地,首先最里面的结果集SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends,这里的话会走index range scan,然后table scan by rowid,这样的话如果符合条件的数据多的话相当耗资源,我们可以改写成
SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends
这样的话最里面的结果集只需要index fast full scan就可以完成了,再改写一下得出以下语句
select * from auction_auctions where rowid in (SELECT rid FROM (
SELECT T1.rowid rid, rownum as linenum FROM
(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
(a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)
下面我们来测试一下这个索引的查询开销
select * from auction_auctions where rowid in (SELECT rid FROM (
SELECT T1.rowid rid, rownum as linenum FROM
(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
(a.approve_status>=0) ORDER BY a.closed,a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18698 Card=18344 Byt
es=21224008)
1 0 NESTED LOOPS (Cost=18698 Card=18344 Bytes=21224008)
2 1 VIEW (Cost=264 Card=18344 Bytes=366880)
3 2 SORT (UNIQUE)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=264 Card=18344 Bytes=128408)
6 5 SORT (ORDER BY STOPKEY) (Cost=264 Card=18344 Byt
es=440256)
7 6 INDEX (FAST FULL SCAN) OF 'IDX_AUCTION_BROWSE'
(NON-UNIQUE) (Cost=159 Card=18344 Bytes=440256)
8 1 TABLE ACCESS (BY USER ROWID) OF 'AUCTION_AUCTIONS' (Cost
=1 Card=1 Bytes=1137)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2080 consistent gets
1516 physical reads
0 redo size
114840 bytes sent via SQL*Net to client
56779 bytes received via SQL*Net from client
438 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40 rows processed
可以看到consistent gets从19437降到2080,physical reads从18262降到1516,查询时间也丛4秒左右下降到0。5秒,可以来说这次sql调整取得了预期的效果。
又修改了一下语句,
SQL> select * from auction_auctions where rowid in
2 (SELECT rid FROM (
3 SELECT T1.rowid rid, rownum as linenum FROM
4 (SELECT a.rowid FROM auction_auctions a
5 WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
a.approve_status>=0
6 7 ORDER BY a.closed,a.category,a.ends) T1
8 WHERE rownum < 18600) WHERE linenum >= 18560) ;
40 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17912 Card=17604 Byt
es=20367828)
1 0 NESTED LOOPS (Cost=17912 Card=17604 Bytes=20367828)
2 1 VIEW (Cost=221 Card=17604 Bytes=352080)
3 2 SORT (UNIQUE)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=221 Card=17604 Bytes=123228)
6 5 INDEX (RANGE SCAN) OF 'IDX_AUCTION_BROWSE' (NON-
UNIQUE) (Cost=221 Card=17604 Bytes=422496)
7 1 TABLE ACCESS (BY USER ROWID) OF 'AUCTION_AUCTIONS' (Cost
=1 Card=1 Bytes=1137)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
550 consistent gets
14 physical reads
0 redo size
117106 bytes sent via SQL*Net to client
56497 bytes received via SQL*Net from client
436 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
在order by里加上索引前导列,消除了
6 5 SORT (ORDER BY STOPKEY) (Cost=264 Card=18344 Byt
es=440256)
,把consistent gets从2080降到550
Web翻页优化实例
提交时间: 2004-6-18 15:37:49 回复 发消息
环境:
Linux version 2.4.20-8custom (root@web2) (gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)) #3 SMP Thu Jun 5 22:03:36 CST 2003
Mem: 2113466368
Swap: 4194881536
CPU:两个超线程的Intel(R) Xeon(TM) CPU 2.40GHz
优化前语句在mysql里面查询15秒左右出来,转移到oracle后进行在不调整索引和语句的情况下执行时间大概是4-5秒,调整后执行时间小于0.5秒。
翻页语句:
SELECT * FROM (SELECT T1.*, rownum as linenum FROM (
SELECT /*+ index(a ind_old)*/
a.category FROM auction_auctions a WHERE a.category =' 170101 ' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641
被查询的表:auction_auctions(产品表)
表结构:
SQL> desc auction_auctions;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(32)
USERNAME VARCHAR2(32)
TITLE CLOB
GMT_MODIFIED NOT NULL DATE
STARTS NOT NULL DATE
DESCRIPTION CLOB
PICT_URL CLOB
CATEGORY NOT NULL VARCHAR2(11)
MINIMUM_BID NUMBER
RESERVE_PRICE NUMBER
BUY_NOW NUMBER
AUCTION_TYPE CHAR(1)
DURATION VARCHAR2(7)
INCREMENTNUM NOT NULL NUMBER
CITY VARCHAR2(30)
PROV VARCHAR2(20)
LOCATION VARCHAR2(40)
LOCATION_ZIP VARCHAR2(6)
SHIPPING CHAR(1)
PAYMENT CLOB
INTERNATIONAL CHAR(1)
ENDS NOT NULL DATE
CURRENT_BID NUMBER
CLOSED CHAR(2)
PHOTO_UPLOADED CHAR(1)
QUANTITY NUMBER(11)
STORY CLOB
HAVE_INVOICE NOT NULL NUMBER(1)
HAVE_GUARANTEE NOT NULL NUMBER(1)
STUFF_STATUS NOT NULL NUMBER(1)
APPROVE_STATUS NOT NULL NUMBER(1)
OLD_STARTS NOT NULL DATE
ZOO VARCHAR2(10)
PROMOTED_STATUS NOT NULL NUMBER(1)
REPOST_TYPE CHAR(1)
REPOST_TIMES NOT NULL NUMBER(4)
SECURE_TRADE_AGREE NOT NULL NUMBER(1)
SECURE_TRADE_TRANSACTION_FEE VARCHAR2(16)
SECURE_TRADE_ORDINARY_POST_FEE NUMBER
SECURE_TRADE_FAST_POST_FEE NUMBER
表记录数及大小
SQL> select count(*) from auction_auctions;
COUNT(*)
----------
537351
SQL> select segment_name,bytes,blocks from user_segments where segment_name ='AUCTION_AUCTIONS';
SEGMENT_NAME BYTES BLOCKS
AUCTION_AUCTIONS 1059061760 129280
表上原有的索引
create index ind_old on auction_auctions(closed,approve_status,category,ends) tablespace tbsindex compress 2;
SQL> select segment_name,bytes,blocks from user_segments where segment_name = 'IND_OLD';
SEGMENT_NAME BYTES BLOCKS
IND_OLD 20971520 2560
表和索引都已经分析过,我们来看一下sql执行的费用
SQL> set autotrace trace;
SQL> SELECT * FROM (SELECT T1.*, rownum as linenum FROM (SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum <18681) WHERE linenum >= 18641;
40 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19152 Card=18347 Byt
es=190698718)
1 0 VIEW (Cost=19152 Card=18347 Bytes=190698718)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=19152 Card=18347 Bytes=190460207)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'AUCTION_AUCTIONS'
(Cost=19152 Card=18347 Bytes=20860539)
5 4 INDEX (RANGE SCAN) OF 'IND_OLD' (NON-UNIQUE) (Cost
=810 Card=186003)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19437 consistent gets
18262 physical reads
0 redo size
114300 bytes sent via SQL*Net to client
56356 bytes received via SQL*Net from client
435 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40 rows processed
我们可以看到这条sql语句通过索引范围扫描找到最里面的结果集,然后通过两个view操作最后得出数据。其中18502 consistent gets,17901 physical reads
我们来看一下这个索引建的到底合不合理,先看下各个查寻列的distinct值
select count(distinct ends) from auction_auctions;
COUNT(DISTINCTENDS)
-------------------
338965
SQL> select count(distinct category) from auction_auctions;
COUNT(DISTINCTCATEGORY)
-----------------------
1148
SQL> select count(distinct closed) from auction_auctions;
COUNT(DISTINCTCLOSED)
---------------------
2
SQL> select count(distinct approve_status) from auction_auctions;
COUNT(DISTINCTAPPROVE_STATUS)
-----------------------------
5
页索引里列平均存储长度
SQL> select avg(vsize(ends)) from auction_auctions;
AVG(VSIZE(ENDS))
----------------
7
SQL> select avg(vsize(closed)) from auction_auctions;
AVG(VSIZE(CLOSED))
------------------
2
SQL> select avg(vsize(category)) from auction_auctions;
AVG(VSIZE(CATEGORY))
--------------------
5.52313106
SQL> select avg(vsize(approve_status)) from auction_auctions;
AVG(VSIZE(APPROVE_STATUS))
--------------------------
1.67639401
我们来估算一下各种组合索引的大小,可以看到closed,approve_status,category都是相对较低集势的列(重复值较多),下面我们来大概计算下各种页索引需要的空间
column distinct num column len
ends 338965 7
category 1148 5.5
closed 2 2
approve_status 5 1.7
index1: (ends,closed,category,approve_status) compress 2
ends:distinct number---338965
closed: distinct number---2
index size=338965*2*(9+2)+ 537351*(1.7+5.5+6)=14603998
index2: (closed,category,ends,approve_status)
closed: distinct number---2
category: distinct number---1148
index size=2*1148*(2+5.5)+537351*(7+1.7+6)=7916279
index3: (closed,approve_status,category,ends)
closed: distinct number---2
approve_status: distinct number―5
index size=2*5*(2+1.7)+537351*(7+5.5+6)=9941030
结果出来了,index2: (closed,category,ends,approve_status)的索引最小
我们再来看一下语句
SELECT * FROM (SELECT T1.*, rownum as linenum FROM (SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum <18681) WHERE linenum >= 18641;
可以看出这个sql语句有很大优化余地,首先最里面的结果集SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends,这里的话会走index range scan,然后table scan by rowid,这样的话如果符合条件的数据多的话相当耗资源,我们可以改写成
SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends
这样的话最里面的结果集只需要index fast full scan就可以完成了,再改写一下得出以下语句
select * from auction_auctions where rowid in (SELECT rid FROM (
SELECT T1.rowid rid, rownum as linenum FROM
(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
(a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)
下面我们来测试一下这个索引的查询开销
select * from auction_auctions where rowid in (SELECT rid FROM (
SELECT T1.rowid rid, rownum as linenum FROM
(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
(a.approve_status>=0) ORDER BY a.closed,a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18698 Card=18344 Byt
es=21224008)
1 0 NESTED LOOPS (Cost=18698 Card=18344 Bytes=21224008)
2 1 VIEW (Cost=264 Card=18344 Bytes=366880)
3 2 SORT (UNIQUE)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=264 Card=18344 Bytes=128408)
6 5 SORT (ORDER BY STOPKEY) (Cost=264 Card=18344 Byt
es=440256)
7 6 INDEX (FAST FULL SCAN) OF 'IDX_AUCTION_BROWSE'
(NON-UNIQUE) (Cost=159 Card=18344 Bytes=440256)
8 1 TABLE ACCESS (BY USER ROWID) OF 'AUCTION_AUCTIONS' (Cost
=1 Card=1 Bytes=1137)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2080 consistent gets
1516 physical reads
0 redo size
114840 bytes sent via SQL*Net to client
56779 bytes received via SQL*Net from client
438 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40 rows processed
可以看到consistent gets从19437降到2080,physical reads从18262降到1516,查询时间也丛4秒左右下降到0。5秒,可以来说这次sql调整取得了预期的效果。
又修改了一下语句,
SQL> select * from auction_auctions where rowid in
2 (SELECT rid FROM (
3 SELECT T1.rowid rid, rownum as linenum FROM
4 (SELECT a.rowid FROM auction_auctions a
5 WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
a.approve_status>=0
6 7 ORDER BY a.closed,a.category,a.ends) T1
8 WHERE rownum < 18600) WHERE linenum >= 18560) ;
40 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17912 Card=17604 Byt
es=20367828)
1 0 NESTED LOOPS (Cost=17912 Card=17604 Bytes=20367828)
2 1 VIEW (Cost=221 Card=17604 Bytes=352080)
3 2 SORT (UNIQUE)
4 3 COUNT (STOPKEY)
5 4 VIEW (Cost=221 Card=17604 Bytes=123228)
6 5 INDEX (RANGE SCAN) OF 'IDX_AUCTION_BROWSE' (NON-
UNIQUE) (Cost=221 Card=17604 Bytes=422496)
7 1 TABLE ACCESS (BY USER ROWID) OF 'AUCTION_AUCTIONS' (Cost
=1 Card=1 Bytes=1137)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
550 consistent gets
14 physical reads
0 redo size
117106 bytes sent via SQL*Net to client
56497 bytes received via SQL*Net from client
436 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
在order by里加上索引前导列,消除了
6 5 SORT (ORDER BY STOPKEY) (Cost=264 Card=18344 Byt
es=440256)
,把consistent gets从2080降到550
相关推荐
SQL优化 SQL优化软件 SQL优化工具 很好用的工具,可以分析优化TSQL语句,oracle数据库语句优化工具
查询sql优化查询 sql查询优化 查询优化sql查询优化 查询优化
海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化...
sql优化sql优化sql优化sql优化
sql查询优化 提高MySQL数据库查询效率的几个技巧
11.3.1 一次Nested Loops Join的优化全过程 318 11.3.2 一次Hash Join 的 优化全过程 320 11.3.3 一次 Merge Sort Join 的优化全过程 324 11.3.4 一次统计信息收集不准确引发的NL性能瓶颈 329 11.4 本章习题、...
优化sql查询速度优化
本书适合于专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者,读者可以通过书中的最佳实践、高级技巧和代码示例来掌握查询调整和优化的技巧,以针对不同问题开发出切合实际的高...
本书适合于专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者,读者可以通过书中的最佳实践、高级技巧和代码示例来掌握查询调整和优化的技巧,以针对不同问题开发出切合实际的高...
sql查询优化总结
1. SQL优化 1 1.1. 优化实战 1 1.1.1. 策略1.尽量全值匹配 1 1.1.2. 策略2.最佳左前缀法则 2 1.1.3. 策略3.不在索引列上做任何操作 2 1.1.4. 策略4.范围条件放最后 3 1.1.5. 策略5.覆盖索引尽量用 3 1.1.6. 策略6.不...
数据优化,连接分析 左连接查询效率!!!!!!!!
, 然而,SQL虽然实现简单可乐,却极易引发性能问题,那时广大SQL使用人员可要“愁”就一个字,心碎无数次了。, 缘何有性能问题?原因也一字概括:“量”。当系统数据量、并发访问量上去后,不良SQL就会拖跨整个系统...
5.2.1 调整一个查询 143 5.2.2 调整一个跟踪工作负载 146 5.3 数据库引擎调整顾问的局限性 148 5.4 小结 149 第6章 书签查找分析 150 6.1 书签查找的目的 150 6.2 书签查找的缺点 152 6.3 分析书签查找的...
ORACLE中SQL查询优化技术.pdf ORACLE中SQL查询优化技术.pdf
Oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是SQL性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖OracleSQL优化与调优技术,主要内容包括: 第一篇“执行计划”...
最后以一个实际的工作负载将所有技巧联系起来,并且提供了“宝典”式的最佳实践列表。 本书适合于关心数据库应用系统性能的开发人员和数据库管理人员阅读。通过阅读本书,不仅可以学习到数据库性能管理的许多知识...
最后以一个实际的工作负载将所有技巧联系起来,并且提供了“宝典”式的最佳实践列表。 本书适合于关心数据库应用系统性能的开发人员和数据库管理人员阅读。通过阅读本书,不仅可以学习到数据库性能管理的许多知识和...
《SQL Server 2008查询...个人感觉不错,对Sqlserver优化很有帮助,主要都是我们经常犯的错,值得借鉴:如:低性能的查询、索引、死锁等等。 一个pdf文件包括全部内容,清晰完整,是数据库管理员不可多得的必备资料!!