作者sunyangnj·2014-04-26 11:59

技术经理·苏宁金融研究院

对1亿行数据查询的优化

字数 5260阅读 4756评论 25赞 7

问题：一张表，有1亿行数据，两列：oldaccountid,newaccountid，都是int，要求前台可以根据oldaccountid查newaccountid，也可以根据newaccountid查oldaccountid，要求提供最优化的方式，加速查询时间。

我闲来无事，正好试验一下,服务器配置，XEON 5138，4核，16G内存。

1.创建表

create table test (id1, id2)

2. 注入数据

db2 +c alter table test1 activate not logged initially

db2 +c "begin atomic declare i int default 0; while i<1000000 do insert into test1 values (int(rand()*1000000),int(rand()*1000000)); set i=i+1;end while; end"

3 查询无索引效率

开启解释模式

db2 set current explain mode explain

创建查询语句

db2inst@linux34:~> cat query.sql

select id1 from test where id2=9999;

db2inst@linux34:~> cat query1.sql

select id2 from test where id1=333333;

创建解释表

db2 -tvf EXPLAIN.DDL

执行查询

db2 -tvf query.sql

db2 -tvf.query1.sql

查看解释计划

db2inst@linux34:~> db2exfmt -l -d sample -o query_exfmt(1).out

（query.sql, query1.sql解释计划一样）

Optimized Statement:

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

SELECT Q1.ID1 AS "ID1"

FROM DB2INST.TEST AS Q1

WHERE (Q1.ID2 = 9999)

Access Plan:

-----------

Total Cost: 2981.82

Query Degree: 1

Rows

RETURN

( 1)

Cost

I/O

1.46672

TBSCAN

( 2)

2981.82

2464

1e+06

TABLE: DB2INST

TEST

Extended Diagnostic Information:

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

No extended Diagnostic Information for this statement.

Plan Details:

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

1) RETURN: (Return Result)

Cumulative Total Cost: 2981.82

Cumulative CPU Cost: 2.18226e+09

创建双单索引

create index id1_idx test(id1);

create index id2_idx test(id2);

再次执行两条语句，查看执行计划query_exfmt2.out

Optimized Statement:

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

SELECT Q1.ID2 AS "ID2"

FROM DB2INST.TEST AS Q1

WHERE (Q1.ID1 = 333333)

Access Plan:

-----------

Total Cost: 22.7018

Query Degree: 1

Rows

RETURN

( 1)

Cost

I/O

1.52582

FETCH

( 2)

22.7018

/---+----

1.52582 1e+06

IXSCAN TABLE: DB2INST

( 3) TEST

15.1382 Q1

1e+06

INDEX: DB2INST

ID1_IDX

Extended Diagnostic Information:

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

可以看出双索引，执行计划提升100倍以上。

我删除了双单索引，创建复合索引

create index cid_idx test(id1, id2);

再次执行查询计划，query_exfmt3.out

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

SELECT Q1.ID2 AS "ID2"

FROM DB2INST.TEST AS Q1

WHERE (Q1.ID1 = 333333)

Access Plan:

-----------

Total Cost: 15.1383

Query Degree: 1

Rows

RETURN

( 1)

Cost

I/O

1.52582

IXSCAN

( 2)

15.1383

1e+06

INDEX: DB2INST

CID_IDX

Extended Diagnostic Information:

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

id1在前的复合索引，比双单索引用id1查id2更快一点，但是效果不明显

用(id1, id2)复合索引用id2查id1，看解释计划query_exfmt4.out

Optimized Statement:

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

SELECT Q1.ID1 AS "ID1"

FROM DB2INST.TEST AS Q1

WHERE (Q1.ID2 = 9999)

Access Plan:

-----------

Total Cost: 2981.82

Query Degree: 1

Rows

RETURN

( 1)

Cost

I/O

1.46672

TBSCAN

( 2)

2981.82

2464

1e+06

TABLE: DB2INST

TEST

Extended Diagnostic Information:

虽然有复合索引，但是id1在复合索引首位的化，用id2做谓词去查，效果和全表扫描一样，没有优化。

我又尝试创建了10个表空间，然后创建表分区，效果还不如双索引，而且管理复杂度上升，解释计划略。

所以，对于1亿行表，每行只有两列字段的表，id1查id2, id2查id1，在两列上建立单索引，是最佳选择。

activate style 数据库 default 服务器

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论25 条评论

devil_wl2技术经理新晨科技
2015-01-05 17:44

组合索引，是不是在只有两列的情况是类似于include呀！

stevenluffyDBAIBM
2014-06-06 11:13

好清晰的思路，厉害

Shane_Qian数据库开发工程师18m
2014-05-13 22:42

sunyangnj: 数据类型确实是unique，是可以的

说的都没错，但是只能是玩玩，对一个表的查询需求很少这么死板的…
另外要考虑的也不止这些；所以综合来说我会比较倾向于楼主最初的那个结论。

wolaos123项目经理澳美制药
2014-05-12 13:51

记录数不代表大数据

sunyangnj技术经理苏宁金融研究院
2014-05-12 07:59

wangyax: wolfop: 最快应该是建两个索引，
create index id1_idx test(id1) include (id2);
create index id2_idx test(id2) include (id1);

这个应该是要unique才能创建的

数据类型确实是unique，是可以的

wangyax软件开发工程师金融机构
2014-05-11 18:51

wolfop: 最快应该是建两个索引，
create index id1_idx test(id1) include (id2);
create index id2_idx test(id2) include (id1);

这个应该是要unique才能创建的吧？

michaelyoung软件开发工程师IBM
2014-05-09 13:44

wolfop: 最快应该是建两个索引，
create index id1_idx test(id1) include (id2);
create index id2_idx test(id2) include (id1);
能省下来索引扫描后再去表里面找另外一

没错，严重同意。大哥，话说我加你好友了，你一直没通过啊！大神，求交友。

yellow-fin项目经理浙江兰德纵横网络技术有限公司
2014-05-09 13:39

wolfop: 最快应该是建两个索引，
create index id1_idx test(id1) include (id2);
create index id2_idx test(id2) include (id1);
能省下来索引扫描后再去表里面找另外一

验证了一下，创建index的时候，只有在指定为unique index类型的时候才支持include子句：The INCLUDE clause can only be specified if UNIQUE is specified.

yellow-fin项目经理浙江兰德纵横网络技术有限公司
2014-05-09 13:21

执行第一条sql时，联合索引貌似没什么效果，但在执行第二条SQL的时候，效果还是非常明显的，所以联合索引跟索引列的排序有很大的关系。。

yellow-fin项目经理浙江兰德纵横网络技术有限公司
2014-05-09 13:20

索引之后runstats，效率也有所提升

start2000系统架构师ABB
2014-05-09 09:48

还得看两列具体的值得分布情况吧

sunyangnj技术经理苏宁金融研究院
2014-05-08 21:33

wolfop: 最快应该是建两个索引，
create index id1_idx test(id1) include (id2);
create index id2_idx test(id2) include (id1);
能省下来索引扫描后再去表里面找另外一

按理说这个是最快的，过两天我在750上试试，看看到底哪个最快，虽然存储空间大了点。

wolfop其它Why should I tell you?
2014-05-08 21:30

最快应该是建两个索引，
create index id1_idx test(id1) include (id2);
create index id2_idx test(id2) include (id1);
能省下来索引扫描后再去表里面找另外一个ID的那步。

sunyangnj技术经理苏宁金融研究院
2014-05-08 20:46

haoqingyun: 确定是1亿行吗？看博主用的“while i<1000000 do”，而且执行计划里也
是“1e+06”
如果是1000000行的话，这个数据量不是很大，每列平均长度是6，两列12
那就是1，000，000

没有用1亿行，因为想要快速看到效果，过两天有时间，我在750上真搞个1亿行试试，表分区用10个独立磁盘做，我会把计划都发上来，给大家瞧瞧。

haoqingyun数据库运维工程师CMBC
2014-05-08 14:59

复合索引（id1,id2）适用于where id1=? and id2=?的查询
这个场景里肯定没啥用。

haoqingyun数据库运维工程师CMBC
2014-05-08 14:56

确定是1亿行吗？看博主用的“while i<1000000 do”，而且执行计划里也
是“1e+06”
如果是1000000行的话，这个数据量不是很大，每列平均长度是6，两列12
那就是1，000，000 * 12 = 12 MB。
这个数据量用来做查询性能的对比，好像小了些。
如果搞个几百兆或者几个G的数据，应该就可以验证出分区表+两个分区索引的优势了。

对于基于这个数据量得出的结论（两个单索引最优），这也是没问题的。
还有一点，这只是针对这个场景（两列相互查询）的。

sunyangnj技术经理苏宁金融研究院
2014-05-08 08:18

qingmugusong: 你表分区是在一个磁盘下，还是每个表分区分别在不同的磁盘上呢

多个磁盘，这个道理我是懂的，独立磁头容器才能发挥出表空间优势，但是表空间只能按一个列来分区，我如果用第二个列来查，效果就非常的差，还不如不分区。