高分再问索引,两种方法哪种更优?

naturelai 2008-03-11 10:32:55
有一表A,表中有一字段id(char),top(char),path(char)
id是主键,top是根结点id,path是本结点从顶层到本结点所经过的结点的id串成的字符串
整个表的记录则形成一棵棵"树"

例如,表A中有以下记录:
id top level path
-----------------------------------------------
........
1001 1001 1 1001
2001 1001 2 10012001
2002 1001 2 10012002
2003 1001 2 10012003
3001 1001 3 100120023001
3002 1001 3 100120023002
4001 1001 4 1001200230014001
5001 1001 5 10012002300140015001
1111 1111 1 1111
2111 1111 2 11112111
.......


我想在path字段上建立索引,以提高以下操作的效率:查询某结点的孩子和查询某结点以下子树的所有结点

我的问题是:
path字段应该怎样建立,查询的效率会比较高?
是按上面的的方法构造,即从根结点id一直到本结点id的字符串,这样好呢?
还是,从根结点id到本结点id的双亲字符串,即:
id top level path
-----------------------------------------------
........
1001 1001 1 NULL
2001 1001 2 1001
2002 1001 2 1001
2003 1001 2 1001
3001 1001 3 10012002
3002 1001 3 10012002
4001 1001 4 100120023001
5001 1001 5 1001200230014001
1111 1111 1 NULL
2111 1111 2 1111
.........
这样好呢?

同样是搜索结点2002的下一层孩子结点,和2002以下子树所有结点,
按第一种方法构造,查询语句则分别为:
select * from A where path like '10012002%' and path<>'10012002' and level=3(查下一层)
select * from A where path like '10012002%' and path<>'10012002' (查下层所有)

按第二种方法构造,查询语句则分别为:
select * from A where path = '10012002' (查下一层)
select * from A where path like '10012002%' (查下层所有)

我有朋友说支持第一种方法,原因是这样构造可以让每个结点的path有唯一值,说这样做,索引的过滤因子比较小,查询效率可以更高。
各位认为,是第一种构造方法的效率高,还是第二种购房方法的效率高呢?
如果可以的话,请各位详细分析一下原因。
非常感谢啦!!
...全文
89 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
fcuandy 2008-03-13
另外我想知道的是,当where后面接多个条件的时候,例如where 条件1 and 条件2 and 条件3,那么查询的步骤是A和B的哪一种呢?
A.先查询满足条件1的结果集1,再在结果集1里查询满足条件2的结果集2,最后在结果集2中查询满足条件3的结果集3
B.每查到一条满足条件1的记录,就马上看这条记录是否满足条件2,如果又满足条件2,再看是否满足条件3,若同时满足3个条件,才把这条记录放到结果集
谢谢!!!

-------------------------
不是的,看执行计划.
比如我举的例子,你把id betwenn 100000 and 200000 放在后面也一样.
id若是聚集索引, 索引随物理顺序走, 即使date列上建了索引, 也是先以id过滤, 效率差别是数量级的.
回复
-狙击手- 2008-03-12
回复
$扫地僧$ 2008-03-12
树的层数少 就用第一个方法
树的层树多 就用第二个方法

回复
naturelai 2008-03-12
再问fcuandy:
你举的例子:
select * from tb where id between 100000 and 200000 and [date] between '2007-1-1' and '20087-3-1' /*在id与date列走势相同的情况,查看表数据粗略估计2007-1-1到2007-3-1的id分布为界于100000到200000之间.*/
还是要多言一下,呵呵,你的date字段有没有建立索引呢?如果建立了的话,还是语句一快吗?
另外我想知道的是,当where后面接多个条件的时候,例如where 条件1 and 条件2 and 条件3,那么查询的步骤是A和B的哪一种呢?
A.先查询满足条件1的结果集1,再在结果集1里查询满足条件2的结果集2,最后在结果集2中查询满足条件3的结果集3
B.每查到一条满足条件1的记录,就马上看这条记录是否满足条件2,如果又满足条件2,再看是否满足条件3,若同时满足3个条件,才把这条记录放到结果集
谢谢!!!

再问scmail81:
为什么层数少用第一个方法,层数二用第二个方法呢?
现在题目的要求是:树节点的孩子最多不超过9999个,整棵树最多不超过9层
按你的意思,是用第一个方法了?可以分析一下原因吗?

谢谢!!!
回复
fcuandy 2008-03-12
第一种查询方法的查询条件多至3个,第二种方法的查询条件只有1个,查询效率不是第2种相对会快些吗?
-------------------------------------------
首先你朋友说的有一定道理.
在查询过滤上并非条件越多就越慢的.举个简单的例子.
100w条记录的表tb, id int型,聚集索引, 查询 2007-1-1到2007-3-1的数据.
语句一
select * from tb where id between 100000 and 200000 and [date] between '2007-1-1' and '20087-3-1' /*在id与date列走势相同的情况,查看表数据粗略估计2007-1-1到2007-3-1的id分布为界于100000到200000之间.*/
语句二
select * from tb where [date] between '2007-1-1' and '20087-3-1'

这两个语句勿需多言,语句一是效率远高于二的.

到于你的语句,其实也可以写在一个语句.
比如:
select * from A where path like '10012002[0-9][0-9][0-9][0-9]'

至于数据分布, 对于你的查子级记录来说, 是一样的(因为你不是精确的定位某一条),对于查具体的,当然是有区别. 具体的我也说不好了

个人看法,仅供参考.
回复
naturelai 2008-03-12
实际业务需要是读操作比写操作多很多
再请教下5楼,取决及依赖于实际的数据分布,具体是看怎样的数据分布呢?
怎样分布查询效率较高?怎样会较低?
第一种查询方法的查询条件多至3个,第二种方法的查询条件只有1个,查询效率不是第2种相对会快些吗?
其他高手路过也欢迎解释一下,万分感谢!
回复
w2jc 2008-03-12
同意5楼fcuandy的看法,主要看LZ的树是更新操作多还是读操作多。
回复
fcuandy 2008-03-12
楼主你的两种设计其实是同一种设计方式,只不过对path的数据定义有区别.
从效率上来看的话,我觉得你朋友说的有一定道理,但也很取决及依赖于实际的数据分布. 个人觉得效率差别大同小异.

id+父id+深度+路径 的设计方法.查询方便一点,差的地方是维护不容易,写操作时,比如,新加一条记录,或是当某一个含下级记录的记录被移动到另一分类下,或删除等等,代码量会增加

而乌龟所说的邹老大的那种设计,有那种设计的好处.
id + 父id 的设计方法, 取得任何相应的数据都需要递规查询.效率反而差了一些,好处是灵活性高.读操作时,代码量增加,写操作时方便.

采用哪种设计,看你自己的实际业务了.

个人看法,仅供参考.
回复
tongki 2008-03-12
我问过的一个问题,表结构是否可以参考一下?
http://topic.csdn.net/u/20080226/17/ceb0afaa-67b4-494e-9db5-a7860e39f548.html
回复
dawugui 2008-03-11
你的设计,我觉得有问题,建议使用我1楼转帖邹建大侠的方法.
回复
dawugui 2008-03-11
--查找指定节点的所有子节点的示例函数
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO

--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-11 10:32
社区公告
暂无公告