哲思官方群认证群组 - 讨论区
标题：[zeuux-universe] 请教一个数据库索引问题
2009年08月17日 星期一 10:19
Kermit Mei kermit.mei在gmail.com
星期一 八月 17 10:19:23 CST 2009
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
星期一 八月 17 10:19:23 CST 2009
大家好，我有个关于数据库索引的问题不是佷清楚，望能指点一二： 有表 KeyItem(id,icon,background, ... , HomeDevId, pageNum)， 其中id是主码(primary key), HomeDevId 和 pageNum是整型的，且在表中不唯一 的数。 表KeyItem可能会含有上万个项，而下面的SELECT是最常用到的，且要求查找速度 尽可能快(MyDevId为一个已知id)： SELECT * FROM KeyItem WHERE HomeDevId=MyDevId ORDER BY pageNum; 现在我的做法是加一个索引，但我不确定加哪个更好： 1.只建一个索引： CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId, pageNum ASC); 2.同时建下面两个索引： CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId ASC); CREATE INDEX index_KeyItem_HP ON KeyItem(pageNum ASC); 请问哪个的优化效果更好一些？ 环境：sqlite3 + Qt4 + Linux On ARM2440. BTW: 这个问题可能不太符合当前mail list的主题，但我一时想不到更好的解决 途径，若有跑题之嫌还望见谅:) Kermit
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
2009年08月17日 星期一 12:10
Xia Qingran qingran在zeuux.org
星期一 八月 17 12:10:15 CST 2009
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
星期一 八月 17 12:10:15 CST 2009
Kermit Mei wrote: > 大家好，我有个关于数据库索引的问题不是佷清楚，望能指点一二： > > 有表 KeyItem(id,icon,background, ... , HomeDevId, pageNum)， > 其中id是主码(primary key), HomeDevId 和 pageNum是整型的，且在表中不唯一 > 的数。 > > 表KeyItem可能会含有上万个项，而下面的SELECT是最常用到的，且要求查找速度 > 尽可能快(MyDevId为一个已知id)： > > SELECT * FROM KeyItem WHERE HomeDevId=MyDevId ORDER BY pageNum; > > 现在我的做法是加一个索引，但我不确定加哪个更好： > > 1.只建一个索引： > CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId, pageNum ASC); > 直观感觉上面的方案不可行。除非查询语句是： SELECT * FROM KeyItem WHERE HomeDevId=MyDevId and pageNum=MyPageNum; > 2.同时建下面两个索引： > CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId ASC); > CREATE INDEX index_KeyItem_HP ON KeyItem(pageNum ASC); > > 请问哪个的优化效果更好一些？ > > 环境：sqlite3 + Qt4 + Linux On ARM2440. > 在sqlite3下，索引是btree的还是hash型的？如果是btree，那么where 和 order by使用索引均有加速的机会；如果是hash索引，那么只会对where查询加速。 另外在sqlite3下where条件和order by条件是否会使用索引？ 建议两种索引都测试一下，然后用explain看一下select查询执行的详细情况。最 好查一下sqlite3关于索引的文档。 > BTW: 这个问题可能不太符合当前mail list的主题，但我一时想不到更好的解决 > 途径，若有跑题之嫌还望见谅:) > > > Kermit > > _______________________________________________ > zeuux-universe mailing list > zeuux-universe at zeuux.org > http://www.zeuux.org/mailman/listinfo/zeuux-universe > > ZEUUX Project - Free Software, Free Society! > http://www.zeuux.org -- 夏清然 Xia Qingran E-mail: qingran at zeuux.org Gtalk: qingran.xia at gmail.com MSN: supermanxqr at msn.com
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
2009年08月17日 星期一 13:10
Kermit Mei kermit.mei在gmail.com
星期一 八月 17 13:10:20 CST 2009
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
星期一 八月 17 13:10:20 CST 2009
On Mon, 2009-08-17 at 12:10 +0800, Xia Qingran wrote: > Kermit Mei wrote: > > 大家好，我有个关于数据库索引的问题不是佷清楚，望能指点一二： > > > > 有表 KeyItem(id,icon,background, ... , HomeDevId, pageNum)， > > 其中id是主码(primary key), HomeDevId 和 pageNum是整型的，且在表中不唯一 > > 的数。 > > > > 表KeyItem可能会含有上万个项，而下面的SELECT是最常用到的，且要求查找速度 > > 尽可能快(MyDevId为一个已知id)： > > > > SELECT * FROM KeyItem WHERE HomeDevId=MyDevId ORDER BY pageNum; > > > > 现在我的做法是加一个索引，但我不确定加哪个更好： > > > > 1.只建一个索引： > > CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId, pageNum ASC); > > > 直观感觉上面的方案不可行。除非查询语句是： > > SELECT * FROM KeyItem WHERE HomeDevId=MyDevId and pageNum=MyPageNum; > > > > 2.同时建下面两个索引： > > CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId ASC); > > CREATE INDEX index_KeyItem_HP ON KeyItem(pageNum ASC); > > > > 请问哪个的优化效果更好一些？ > > > > 环境：sqlite3 + Qt4 + Linux On ARM2440. > > > > 在sqlite3下，索引是btree的还是hash型的？如果是btree，那么where 和 order > by使用索引均有加速的机会；如果是hash索引，那么只会对where查询加速。 粗看了代码和一些文档，sqlite3是btree型的。 > > 另外在sqlite3下where条件和order by条件是否会使用索引？ 这个我也不太清楚……我在sqlite 的mail list 里问下吧。 > 建议两种索引都测试一下，然后用explain看一下select查询执行的详细情况。 我看不太明白explain的结果，贴出来如果大家有时间请帮我看看： 1.没有建index的时候： sqlite> explain select * from KeyItem where HomeDevId=2 order by pageNum; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain select * from KeyItem where HomeDevId=2 order by pageNum; 00 1 OpenEphemeral 1 3 0 keyinfo(1,BINARY) 00 2 Integer 2 1 0 00 3 Goto 0 53 0 00 4 SetNumColumns 0 13 0 00 5 OpenRead 0 4 0 00 6 Rewind 0 29 0 00 7 Column 0 11 2 00 8 Ne 1 28 2 collseq(BINARY) 6c 9 Column 0 0 4 00 10 Column 0 1 5 00 11 Rowid 0 6 0 00 12 Column 0 3 7 00 13 Column 0 4 8 00 14 Column 0 5 9 00 15 Column 0 6 10 00 16 Column 0 7 11 00 17 Column 0 8 12 00 18 Column 0 9 13 00 19 Column 0 10 14 00 20 Column 0 11 15 00 21 Column 0 12 16 00 22 MakeRecord 4 13 2 00 23 SCopy 16 17 0 00 24 Sequence 1 18 0 00 25 Move 2 19 1 00 26 MakeRecord 17 3 3 00 27 IdxInsert 1 3 0 00 28 Next 0 7 0 01 29 Close 0 0 0 00 30 SetNumColumns 0 13 0 00 31 OpenPseudo 2 1 0 00 32 Sort 1 51 0 00 33 Column 1 2 2 00 34 Integer 1 3 0 00 35 Insert 2 2 3 00 36 Column 2 0 4 00 37 Column 2 1 5 00 38 Column 2 2 6 00 39 Column 2 3 7 00 40 Column 2 4 8 00 41 Column 2 5 9 00 42 Column 2 6 10 00 43 Column 2 7 11 00 44 Column 2 8 12 00 45 Column 2 9 13 00 46 Column 2 10 14 00 47 Column 2 11 15 00 48 Column 2 12 16 00 49 ResultRow 4 13 0 00 50 Next 1 33 0 00 51 Close 2 0 0 00 52 Halt 0 0 0 00 53 Transaction 0 0 0 00 54 VerifyCookie 0 80 0 00 55 TableLock 0 4 0 KeyItem 00 56 Goto 0 4 0 00 sqlite> 2.建立索引 CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId, pageNum ASC); 的情况： sqlite> explain select * from KeyItem where HomeDevId=2 order by pageNum; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain select * from KeyItem where HomeDevId=2 order by pageNum; 00 1 Noop 0 0 0 00 2 Integer 2 1 0 00 3 Goto 0 32 0 00 4 SetNumColumns 0 13 0 00 5 OpenRead 0 4 0 00 6 SetNumColumns 0 3 0 00 7 OpenRead 2 18 0 keyinfo(2,BINARY,BINARY) 00 8 IsNull 1 29 0 00 9 Affinity 1 1 0 ddb 00 10 SeekGe 2 29 1 1 00 11 IdxGE 2 29 1 1 01 12 IdxRowid 2 2 0 00 13 Seek 0 2 0 00 14 Column 0 0 3 00 15 Column 0 1 4 00 16 IdxRowid 2 5 0 00 17 Column 0 3 6 00 18 Column 0 4 7 00 19 Column 0 5 8 00 20 Column 0 6 9 00 21 Column 0 7 10 00 22 Column 0 8 11 00 23 Column 0 9 12 00 24 Column 0 10 13 00 25 Column 2 0 14 00 26 Column 2 1 15 00 27 ResultRow 3 13 0 00 28 Next 2 11 0 00 29 Close 0 0 0 00 30 Close 2 0 0 00 31 Halt 0 0 0 00 32 Transaction 0 0 0 00 33 VerifyCookie 0 81 0 00 34 TableLock 0 4 0 KeyItem 00 35 Goto 0 4 0 00 sqlite> 3.建了两个索引： CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId ASC); CREATE INDEX index_KeyItem_P ON KeyItem(pageNum ASC); explain结果如下： sqlite> explain select * from KeyItem where HomeDevId=2 order by pageNum; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain select * from KeyItem where HomeDevId=2 order by pageNum; 00 1 OpenEphemeral 1 3 0 keyinfo(1,BINARY) 00 2 Integer 2 1 0 00 3 Goto 0 59 0 00 4 SetNumColumns 0 13 0 00 5 OpenRead 0 4 0 00 6 SetNumColumns 0 2 0 00 7 OpenRead 2 18 0 keyinfo(1,BINARY) 00 8 IsNull 1 34 0 00 9 Affinity 1 1 0 db 00 10 SeekGe 2 34 1 1 00 11 IdxGE 2 34 1 1 01 12 IdxRowid 2 2 0 00 13 Seek 0 2 0 00 14 Column 0 0 3 00 15 Column 0 1 4 00 16 IdxRowid 2 5 0 00 17 Column 0 3 6 00 18 Column 0 4 7 00 19 Column 0 5 8 00 20 Column 0 6 9 00 21 Column 0 7 10 00 22 Column 0 8 11 00 23 Column 0 9 12 00 24 Column 0 10 13 00 25 Column 2 0 14 00 26 Column 0 12 15 00 27 MakeRecord 3 13 2 00 28 SCopy 15 16 0 00 29 Sequence 1 17 0 00 30 Move 2 18 1 00 31 MakeRecord 16 3 19 00 32 IdxInsert 1 19 0 00 33 Next 2 11 0 00 34 Close 0 0 0 00 35 Close 2 0 0 00 36 SetNumColumns 0 13 0 00 37 OpenPseudo 3 1 0 00 38 Sort 1 57 0 00 39 Column 1 2 2 00 40 Integer 1 19 0 00 41 Insert 3 2 19 00 42 Column 3 0 3 00 43 Column 3 1 4 00 44 Column 3 2 5 00 45 Column 3 3 6 00 46 Column 3 4 7 00 47 Column 3 5 8 00 48 Column 3 6 9 00 49 Column 3 7 10 00 50 Column 3 8 11 00 51 Column 3 9 12 00 52 Column 3 10 13 00 53 Column 3 11 14 00 54 Column 3 12 15 00 55 ResultRow 3 13 0 00 56 Next 1 39 0 00 57 Close 3 0 0 00 58 Halt 0 0 0 00 59 Transaction 0 0 0 00 60 VerifyCookie 0 84 0 00 61 TableLock 0 4 0 KeyItem 00 62 Goto 0 4 0 00 sqlite> explain出来的结果很多，我也看不太明白它每项的具体含意，但单从结果看，是 不是可以说第二种情况的优化最好？ > 最好查一下sqlite3关于索引的文档。 sqlite关于index部分的官方文档好象很少，我只找到了命令的用法。 http://www.sqlite.org/lang_createindex.html Thanks. Regards Kermit
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
2009年08月18日 星期二 09:44
Kermit Mei kermit.mei在gmail.com
星期二 八月 18 09:44:31 CST 2009
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
星期二 八月 18 09:44:31 CST 2009
On Mon, 2009-08-17 at 12:10 +0800, Xia Qingran wrote: > Kermit Mei wrote: > > 大家好，我有个关于数据库索引的问题不是佷清楚，望能指点一二： > > > > 有表 KeyItem(id,icon,background, ... , HomeDevId, pageNum)， > > 其中id是主码(primary key), HomeDevId 和 pageNum是整型的，且在表中不唯一 > > 的数。 > > > > 表KeyItem可能会含有上万个项，而下面的SELECT是最常用到的，且要求查找速度 > > 尽可能快(MyDevId为一个已知id)： > > > > SELECT * FROM KeyItem WHERE HomeDevId=MyDevId ORDER BY pageNum; > > > > 现在我的做法是加一个索引，但我不确定加哪个更好： > > > > 1.只建一个索引： > > CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId, pageNum ASC); 昨天用Python生成了一些随机数据在ARM上测试了一下这种情况： 序号 总行数 选中行数 Index(Y/N) Real Time 1 51000 5485 N 4.73s Y 3.95s 2 10000 1130 N 1.01s Y 0.88s 3 3000 337 N 0.40s Y 0.35s 这个索引是最佳优化了，其它的索引方式效果都不好。 BTW: 看来没有我想像中那种“神奇”的优化效果……呵呵
[导入自Mailman归档：http://www.zeuux.org/pipermail/zeuux-universe]
Zeuux © 2024
京ICP备05028076号