Oracle SQL建立有效索引减少回表
上一篇 / 下一篇 2012-09-28 09:47:50 / 个人分类:数据库
回表:在数据中,当查询数据的时候,在索引中查找索引后,获得该行的rowid,根据rowid再查询表中数据,就是回表。51Testing软件测试网Rjf8w#kJ#d
51Testing软件测试网'^jjC_"|'oa在数据库中,数据的存储都是以块为单位的,称为数据块,表中每一行数据都有唯一的地址标志ROWID。每次使用SQL进行查询的时候,都要扫描数据块,找到行所在的ROWID,再扫描该表的数据块。回表将会导致扫描更多的数据块。
Im}"BA3_051Testing软件测试网%Xu5Uu o,A'Ctj例如:SELECT a,b,cFROM TEST_DB WHERE b=151Testing软件测试网~1k,o!]#l)|,P+M%D-P9P
8E%H)A4|.p4{} ]pp0 在该查询语句执行的时候,可分为两种情况:
o2E w"F.@1} j2g:W0~051Testing软件测试网u7iE/@qb2d&T%iA、在b上没有建立索引
2`-yPs4p C_\%x09}m"w"TR0 如果在b上没有建立索引,那么该条SQL语句执行时,要进行全表扫描,扫描所有该表中的数据块。从该数据块中找到记录,并进行过滤。在没有索引时,查找数据会导致扫描表中所有数据块,性能较低。51Testing软件测试网f$|/q!g6w8N
51Testing软件测试网'QrHXzB、在b上建立索引51Testing软件测试网j{s"dxWR
XD:E:g1WF{8PDh0 如果在b上建立索引,那么在执行该条SQL语句时,先进行索引扫描,在索引中找到b=1所在的位置(一般只需要扫描3个块数据即可),获得改行的 ROWID,根据其ROWID再查询数据(回表),如果所查找的数据量较少,则回表次数就少。如上面的例子,要查询的数据只有b在索引中,a并不在索引 中,那么就要回表一次查询a;如果a也在索引中,那么就不需要回表。
Fjw]1O0^t051Testing软件测试网L;_0lLo0]1cS在数据库查询中,需要用到回表的地方很多,如分页查询。一般要竟量在索引上分页,然后返回ROWID,在通过ROWID进行回表查询。51Testing软件测试网/]%ef z&l$@
51Testing软件测试网5A4I,`N|@.\如分页语句: SELECT * FROM ( SELECT ROW_NUMBER OVER (ORDER BY A ) RN,T.* FROM TABLE T WHERE B=? AND C=? ) WHERE RN>=1 AND RN <=20
G Y]q!Y.DC-Y(c#^051Testing软件测试网5cYZ[+iB\f)H在该分页查询语句中,我们建立B,C,A的索引,那么查询时,步骤如下:
E'f3G8m'Y$b051Testing软件测试网V9i3}ZA$D!qe1~k1、先查询内层语句 SELECT * FROM TABLE T WHERE B=? AND C=?,假设返回1000行数据。
y~ V'j2| r\\zg;p051Testing软件测试网+Lp5zzSd2I)G h2、通过索引找到这1000行数据的ROWID,由于索引时连续的,所以假设这1000行数据的索引分布在3个数据块中,一般需要读取6个数据块。再根 据ROWID取回表查询数据,最差的情况是这1000行数据分布在1000个数据块中,则需要读取1000块。那么总共需要读取的数据块区为1006块。51Testing软件测试网-i8j\?xazvvd