一个两亿条记录的表，一个简单却耗时的查询，如何建索引？

[mengmou]

有一表TableName，共2.3亿条记录

timestamp DATETIME --时间部分都是零点，日期从2006年至今，即700多个唯一值，各唯一值的比例相当，每个唯一值即每天大概30万条记录
site VARCHAR(10) --10个唯一值，各唯一值的比例相当
userName VARCHAR(50) --唯一值很多,各唯一值比例无规律
pages INT --绝大部分都是小于10

SELECT site,SUM(pages),COUNT(DISTINCT userName)
FROM TableName
WHERE timestamp BETWEEN '20080301' and '20080318'
GROUP BY site

原来表上有个非聚集索引(timestamp,site)，查询大概半小时。后来又加了一个非聚集索引(timestamp,site,pages,userName)，无明显改善。
那么应该如何建索引？请说明理由。因为我几乎不可能一一去偿试，因为太慢了，建第一条索引用了一个小时，第二条用了两个半小时。

[华芸智森]

有一表TableName，共2.3亿条记录

timestamp DATETIME --时间部分都是零点，日期从2006年至今，即700多个唯一值，各唯一值的比例相当，每个唯一值即每天大概30万条记录
site VARCHAR(10) --10个唯一值，各唯一值的比例相当
userName VARCHAR(50) --唯一值很多,各唯一值比例无规律
pages INT --绝大部分都是小于10

SELECT site,SUM(pages),COUNT(DISTINCT userName)
FROM TableName
WHERE timestamp BETWEEN '20080301' and '20080318'
GROUP BY site
======================================
1.分表.每个表是一年数据.表主键顺序:userName,timestamp,site,pages,将数据从原表分别抽到各表.可以加一个 timestamp 索引.
2.如果你要求速度的话,不要再企图用一条SQL语句来达到目的了.
3.访问时使用存储过程.从各表抽数据,放到一个建立好的表,表结构与原tablename基本一样,但多一个标识列.

[JiangHongTao]

原来表上有个非聚集索引(timestamp,site)
-----------------------------------------------
从语义上分析似乎(site,timestamp)要好些。
(site,timestamp,username 似乎更有利于本查询。
至于最为聚合函数中的列，建立INDEX对查询优化是毫无意义的。

[JiangHongTao]

不过，毕竟你的原始记录有：
18×30 ＝540万条
无论你采用哪种方式，这540万条数据都要参与计算，速度应该和硬件有相当的关系了。

[mengmou]

后来又加了一个非聚集索引(timestamp,site,pages,userName)，无明显改善。
-----------
这种情况可能是个特例，因为我只测试了一次。

我刚才加了一个非聚集索引timestamp,site,userName

然后运行，大概一分半，看查询计划，发现用的的索引是(timestamp,site,pages,userName），于是删掉timestamp,site

再运行，大概两分钟，看查询计划，发现用的的索引是(timestamp,site,pages,userName），于是删掉timestamp,site,userName

再运行，大概两分半。

以上我只测试了一次，等有时间各种情况都测一下。

[liangCK]

高度关注..

[wzy_love_sly]

关注学习

[wzjpsq]

关注

[wuxinyuzhu]

帮你顶一下

[hlq8210]

关注学习

[昵称被占用了]

Try:
非聚集索引(timestamp,site,userName)

[zlp321002]

这么大的数据量,那就建分区视图吧!!查询效果语句都一样.

[昵称被占用了]

有没有效果真很难说

[mengmou]

如果不建分区视图，有没有一个合理的索引能尽可能提高速度？

[toe999]

增加一个timestamp列的聚集索引试一下。

以下是一点资料：

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

[skybug]

把日志型的数据记录在SQL表里不是最明智的做法

[warmpoty]

学习了