对于多字段组合查询,我想了一种方案,不知道这样是否可以极大提升查询效率,征求各位达人意见.

Question

辅助表肯定是多余的, 而且会增加新/删/修的代价, 而且不好扩展。在那两个字段上建立非聚集索引就足矣.当然，最好的验证方法就是测试，你可以试试。CREATE INDEX ix_price_update ON MainTable(Price,updatedate)
GO

Answer 1

--建立索引
create index in_price on MainTable(price)
create index in_updatedate  on MainTable(updatedate )

Answer 2

聚集索引之所以能提高查询速度,是因为表中的行排列顺序与聚集索引顺序相同,即在索引中查到了,表中也即查到了,特别是对于 where 列名>值 或 列名<值 这样的条件.
另建一个辅助表,将常查之列设置一聚集索引,除了增加系统开销外,对提高查询速度丝毫不起作用,因为,该索引虽贵为聚集索引,但索引顺序却与要查之表的顺序毫不相干,即使利用该索引查出ID到原表中取数据,仍将要在原表中一条条挑出记录来,速度不单不会加快,甚至还要比在原表中建一非聚集索引还要慢!

Answer 3

经过如下测试：在主表中生成220W条记录，其中主记录20W，每条主记录有10条子记录，也就是说这个表里总共有220W条记录；
父子记录通过EntityID以及ParentID来标识，父记录的ParentID为0我主表的表结构如下：CREATE TABLE [Entities] (
[EntityID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NOT NULL ,
[ContainerID] [int] NOT NULL ,
[AuthorID] [int] NOT NULL ,
[AuthorName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Subject] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Body] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[EntityType] [int] NOT NULL ,
[EntityLevel] [int] NOT NULL ,
[CreateDate] [datetime] NULL ,
[PropertyNames] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[PropertyValues] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[Content] [image] NULL ,
[IntField1] [int] NULL ,
[IntField2] [int] NULL ,
[IntField3] [int] NULL ,
[IntField4] [int] NULL ,
[IntField5] [int] NULL ,
[IntField6] [int] NULL ,
[IntField7] [int] NULL ,
[IntField8] [int] NULL ,
[IntField9] [int] NULL ,
[IntField10] [int] NULL ,
[FloatField1] [float] NULL ,
[FloatField2] [float] NULL ,
[FloatField3] [float] NULL ,
[FloatField4] [float] NULL ,
[FloatField5] [float] NULL ,
[VarChar1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar4] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar5] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar6] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar7] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar8] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar9] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar10] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar11] [varchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar12] [varchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar13] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar14] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
[VarChar15] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
[DateTime1] [datetime] NULL ,
[DateTime2] [datetime] NULL ,
[DateTime3] [datetime] NULL ,
[DateTime4] [datetime] NULL ,
[DateTime5] [datetime] NULL ,
[UniqueIdentifier1] [uniqueidentifier] NULL ,
[UniqueIdentifier2] [uniqueidentifier] NULL 
)
GO
另外，按照我的思路，我为ParentID字段建立一张辅助表，如下：CREATE TABLE [EntityChilds] (
[EntityID] [int] NOT NULL ,
[ChildID] [int] NOT NULL 
) ON [PRIMARY]
GO CREATE  CLUSTERED  INDEX [Idx_EntityChilds_EntityId] ON [EntityChilds]([EntityID]) ON [PRIMARY]
GO
上面的辅助表中，EntityID字段是Entities表中ParentID字段的一个外键，而ChildID是Entities表中EntityID字段的一个外键；1）利用辅助表的查询语句：select e.entityid from entitychilds ec inner join entities e on ec.childid = e.entityid where ec.entityid >= 2445937 and ec.entityid <=2445948结果：执行该语句一瞬间完成，返回20条记录；2）不利用辅助表的SQL语句：select e.entityid from entities e where e.parentid>=2445937 and e.parentid<=2445948结果：执行该语句需要2分钟，而且占用了大量内存，返回20条记录；3）不利用辅助表，但为Entities表的ParentID字段建立非聚集索引后，执行2）中的SQL语句：
结果：执行该语句也是一瞬间完成，返回20条记录；结论：各位高手是对的，没有必要建立辅助表，只要建立一个一般索引即可。没想到我辛苦想出来的方案没什么好处，呵呵。不过，我还有一个疑问，就是我的主表中有40多个字段，那如果为这个表除EntityID以及字符串字段之外的其他所有字段都建立一般索引，那在大数据量情况下更新数据的速度和我用辅助表的那种方案比起来，哪个更快呢？我完全不知道答案。

Answer 4

索引会可以帮助查询优化，
但是导致更新降低效率。
------------
对于百万级的数据量，是否可以考虑分表分区之类的来实现？
你数据库是2000还是2005 or 2008?
先做好设计上的问题，
再做优化上的问题。
设计上，使用磁盘阵列，分文件存储提高IO性能，
做分区表或者建立分区视图也可以，
最后在建立索引优化查询。

Answer 5

恩,谢谢大家.我已经放弃建辅助表了,改用一般索引的方式.我昨晚测试过,确实建一般索引更合适.为了感谢大家对我的支持和建议.我晚上把我最新的自己设计的网站实体框架(WebSite Entity Framework)源代码分享给大家! 我的一切努力就是自己去做一些东西,然后分享给大家,和CSDN的兄弟们一起进步,呵呵

Answer 6

楼主一定要给我一份啊!我刚好在研究WEB的应用。

Answer 7

已经共享了我的框架。
下载地址：
http://www.netfocus.cn/downloaddetail10387.html

Answer 8

    private MatchCollection GetMatches(string str, string s, string e)
    {
        string start = Regex.Escape(s);
        string end = Regex.Escape(e);
        Regex reg = new Regex(@"(?is)" + start + @"(?:(?!" + end + @").)*" + end);
        MatchCollection mc = reg.Matches(str);
        return mc;
    }