zeuux-universe  - 讨论区

标题:[zeuux-universe] 请教一个数据库索引问题

2009年08月17日 星期一 10:19

Kermit Mei kermit.mei在gmail.com
星期一 八月 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

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

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

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哲思注册吗?现在 注册 !

    Zeuux © 2024

    京ICP备05028076号