sqlserver count,order by 查询慢

cow_sheep 2009-09-22 11:03:30
表中数据 700万条左右，29个字段，自动增长列设为主键， 使用count（1） 查询总条数需要1分钟，
select top 1000 字段名 from 表 order by 时间(该字段有重复值和null值) 这样的语句查询更是慢得基本查不出来。而且就是在自己的本机测试的。不知道是什么原因请各位赐教。
...全文
1176 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
DB牛牛 2011-08-25
  • 打赏
  • 举报
 回复
700万数据用分区表也未尝不可啊，要根据实际情况来分析，不能说是只有几千万的数据才能分区
如果有某个表的查询插入都特别频繁，而且是历史数据使用率低的情况，一样可以用分区来解决问题
我这里有个只有1百万条数据的表，我最近也要分区，因为，我这边的查询和插入的速度要求都特别高
昵称被占用了 2009-09-22
  • 打赏
  • 举报
 回复
count问题,如果查整个表，用以下语句快很多

select rows

from sysindexes

where indid<2

and id = object_id('你的表名')
billpu 2009-09-22
  • 打赏
  • 举报
 回复
700万条不算多 也不算少了
建立个索引还是有必要的,你可以试着在时间列上设置聚集索引 把自动增长的那个去掉
聚集所以是针对物理排序 对需要order的可以设置 但只有一个
cow_sheep 2009-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 xuam 的回复:]
引用 12 楼 wbfsa 的回复:
引用 10 楼 viva369 的回复:
1.order by的时间列上加个索引
2.count(*)有聚集索引都那么慢，你机器配置差吧。
如果统计信息正确，可以用以下方法差count(*)
sp_spaceused '表名


count 的问题 只有你回答啊，这个问题重要些， 时间字段上建立索引这个问题解决了



count 什么問題？
[/Quote]

表中数据 700万条左右，29个字段，自动增长列设为主键， 使用count（1） 查询总条数需要1分钟

我的机器 2.4G双核， 3G内存， 应该不算差的，为什么需要那么长时间？
cow_sheep 2009-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 fredrickhu 的回复:]
700W条数据不算特别多啊 时间字段建立一个索引试下 不行的话检查下 有可能是其他问题
[/Quote]

分区 还用不到的，还是谢谢了~~ count 的问题怎么解决？
xuam 2009-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 wbfsa 的回复:]
引用 10 楼 viva369 的回复:
1.order by的时间列上加个索引
2.count(*)有聚集索引都那么慢，你机器配置差吧。
如果统计信息正确，可以用以下方法差count(*)
sp_spaceused '表名


count 的问题 只有你回答啊，这个问题重要些， 时间字段上建立索引这个问题解决了
[/Quote]


count 什么問題？
cow_sheep 2009-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 viva369 的回复:]
1.order by的时间列上加个索引
2.count(*)有聚集索引都那么慢，你机器配置差吧。
如果统计信息正确，可以用以下方法差count(*)
sp_spaceused '表名

[/Quote]
count 的问题 只有你回答啊，这个问题重要些， 时间字段上建立索引这个问题解决了
SQL77 2009-09-22
  • 打赏
  • 举报
 回复
对排序字段加索引,
viva369 2009-09-22
  • 打赏
  • 举报
 回复
1.order by的时间列上加个索引
2.count(*)有聚集索引都那么慢，你机器配置差吧。
如果统计信息正确，可以用以下方法差count(*)
sp_spaceused '表名
csdyyr 2009-09-22
  • 打赏
  • 举报
 回复 
create index ix_time on tb(时间)

include (字段名)
sdhdy 2009-09-22
  • 打赏
  • 举报
 回复 
select top 1000 字段名  from 表 order by convert(varchar(10),时间,120)
sdhdy 2009-09-22
  • 打赏
  • 举报
 回复
时间字段是什么类型的？
--小F-- 2009-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 soft_wsx 的回复:]
一是索引！

还有试一下用分区表！
SQL code什么是数据库分区？
数据库分区是一种对表的横向分割，Sql server 2005企业版和之后的Sql server版本才提供这种技术，这种对表的横向分割不同于2000中的表分割，它对访问用户是透明的，用户并不会感觉的表被横向分割了。（2000中的表横向分割是建n个表例如按时间建表每月一个表，表名不同，最后需要做一个大视图）

关于具体的如何做分区，请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html

为什么要分区？
显而易见分区是为了提高数据库的读写性能，提高数据库的效率；

分区是否总是可以提高效率？
分区是一把双刃剑，并不总能提高效率，这和具体情况有关系。
之所以有分区技术，分区技术用的好的话可以提高性能，是因为一方面分区把一大块数据分成了n小块，这样查询的时候很快定位到某一小块上，在小块中寻址要快很多；另一方面CPU比磁盘IO快很多倍，而硬件上又有多个磁盘，或者是RAID（廉价磁盘冗余阵列），可以让数据库驱动CPU同时去读写不同的磁盘，这样才有可能可以提高效率。
分区在有些时候并不能提高读写效率，比如说我们经常看到的按照日期字段去分区MSDN例子，这个实例中是按照记录的生成时间来分区的，把一年的数据分割成12个分区，每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能，因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是：这样分区是否可以提高读取性能呢？答案是不一定，要看根据什么字段来查询，如果是根据时间来查询，根据时间生成报表那么这种分区肯定会提高查询的效率，但是如果是按照某个客户查询客户最近1年内的账单数据，这样数据分布到不同的分区上，这样的话效率就不一定能提高了，这要看数据在同一个分区上连续分布的读性能高，还是CPU从几个磁盘上同步读取，然后在合并数据的性能更高一些，这和读取数据的记录数也有关系。

如何分区？用什么字段做分区依据？
具体如何分区和涉及的业务有关系，要看业务上最经常的写入和读取操作是什么，然后再考虑分区的策略。

既然与具体业务相关，我们就假定一个业务环境，假如我们要做一个论坛，对论坛的帖子和回复表进行分区。
论坛中最常见的写操作是1）发帖2）回复帖子，
最常见的读操作是1） 根据帖子id显示帖子详情和分页的帖子回复2） 根据帖子版面帖子列表页根据版面id分页读取帖子列表数据
怎么分区更合适呢？现在还没有准确答案，我有两种可能的方案，写下来，大家讨论看看。
方案1. 根据帖子ID区域段分区（1-300w一个分区、300w-600w一个分区…），这样理论上可以提高帖子详细页的读取速度，而对于写操作性能没有益处，对于根据版面id读取帖子列表页有可能有益
方案2. 根据版面id进行分区，这样对于写性能应该有提高，不同的分区对应不同的版面，当有两个版面同时有发帖回帖操作时，有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能，而对于帖子详细信息页没有性能影响。

多大的数据量才需要分区？
这个问题我只能说一个内部标准，如果一张表的记录超过在超过1000w，并以每月百万的数据量增长，那就需要分区。大家有不同的看法请回复讨论

关于具体的如何做分区，请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html
代码加注释，希望对初学者有用。USE[master]GOifexists (select*from sys.databaseswhere name='Test_1')dropdatabase Test_1GO--创建新库，要演练分区所以我们会多创建两个文件组Test_A,Test_B，以便在后面的分区方案中使用。CREATEDATABASE[Test_1]ONPRIMARY 

( NAME= N'test_1', FILENAME= N'D:\sqldata\test_1.mdf' , SIZE= 10240KB , MAXSIZE= UNLIMITED, FILEGROWTH= 1024KB ),

 FILEGROUP[test_A] 

( NAME= N'Test_A', FILENAME= N'D:\sqldata\test_A.ndf' , SIZE= 1024KB , MAXSIZE= UNLIMITED, FILEGROWTH= 1024KB ),

FILEGROUP[test_B] 

( NAME= N'Test_B', FILENAME= N'D:\sqldata\test_B.ndf' , SIZE= 1024KB , MAXSIZE= UNLIMITED, FILEGROWTH= 1024KB )LOGON 

( NAME= N'Test_log', FILENAME= N'D:\sqldata\Test_log.ldf' , SIZE= 7616KB , MAXSIZE= 2048GB , FILEGROWTH=10%)

 COLLATE Chinese_PRC_CI_ASGOUSE[Test_1]GO--若分区函数存在则先drop掉IFEXISTS (SELECT*FROM sys.partition_functionsWHERE name= N'test_partition')DROP PARTITIONFUNCTION[test_partition]GO/**//*创建分区函数给后面的分区方案使用，分区函数很简单就是指定一个范围确定在某个值为什么的时候放在那个分区上*/--新建一个简单的分区函数，该函数以1000为界分两个区create partitionfunction test_partition(int)AS

RANGELEFTFORVALUES (1000)go/**//*看分区方案是否存在，若存在先drop掉*/IFEXISTS (SELECT*FROM sys.partition_schemesWHERE name= N'test_scheme')DROP PARTITION SCHEME test_schemeGO--创建分区方案，分区方案需要指定一个分区函数，并指定在分区函数中分的区需要放在哪一个文件组上create partition scheme test_schemeAS 

PARTITION[test_partition]TO (test_A,test_B)GO--创建分区表ifobject_id('student','U')isnotnulldroptable student;gocreatetable student

(

    idintidentity(1,1)notnull,

    namevarchar(10)notnull,

    classintnotnull,

    gradeint

)on test_scheme(class)--在此处指定该表要使用的分区方案，并将指定分区依据列go--随便插入几条数据insertinto studentvalues ('AQU',10,100);-- 这条数据在A分区上insertinto studentvalues ('AQU_边界',1000,89);-- 这边数据也在A分区上是个边界，因为我们上面在函数中指定的是RANGE LEFT，所以1000在A分区上insertinto studentvalues ('BQU',1001,90);-- 这一条肯定是在B分区上了。go--最后看看结果。$partition.分区函数(分区列)可以返回某一行所在的分区序号select*,分区序号= $partition.test_partition(class)from studentGO

[/Quote]


700W条数据就用分区表 我汗 一个
--小F-- 2009-09-22
  • 打赏
  • 举报
 回复
700W条数据不算特别多啊 时间字段建立一个索引试下 不行的话检查下 有可能是其他问题
xman_78tom 2009-09-22
  • 打赏
  • 举报
 回复
"select top 1000 字段名 from 表 order by 时间" 语句根本没有用到索引，完全使用表扫描，先进行排序，再进行查询，当然会慢。
慢的原因主要就在排序上，消耗了大量的资源。
soft_wsx 2009-09-22
  • 打赏
  • 举报
 回复
一是索引！

还有试一下用分区表！
什么是数据库分区？

数据库分区是一种对表的横向分割，Sql server 2005企业版和之后的Sql server版本才提供这种技术，这种对表的横向分割不同于2000中的表分割，它对访问用户是透明的，用户并不会感觉的表被横向分割了。（2000中的表横向分割是建n个表例如按时间建表每月一个表，表名不同，最后需要做一个大视图）



关于具体的如何做分区，请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html 



为什么要分区？

显而易见分区是为了提高数据库的读写性能，提高数据库的效率；



分区是否总是可以提高效率？

分区是一把双刃剑，并不总能提高效率，这和具体情况有关系。

之所以有分区技术，分区技术用的好的话可以提高性能，是因为一方面分区把一大块数据分成了n小块，这样查询的时候很快定位到某一小块上，在小块中寻址要快很多；另一方面CPU比磁盘IO快很多倍，而硬件上又有多个磁盘，或者是RAID（廉价磁盘冗余阵列），可以让数据库驱动CPU同时去读写不同的磁盘，这样才有可能可以提高效率。

分区在有些时候并不能提高读写效率，比如说我们经常看到的按照日期字段去分区MSDN例子，这个实例中是按照记录的生成时间来分区的，把一年的数据分割成12个分区，每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能，因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是：这样分区是否可以提高读取性能呢？答案是不一定，要看根据什么字段来查询，如果是根据时间来查询，根据时间生成报表那么这种分区肯定会提高查询的效率，但是如果是按照某个客户查询客户最近1年内的账单数据，这样数据分布到不同的分区上，这样的话效率就不一定能提高了，这要看数据在同一个分区上连续分布的读性能高，还是CPU从几个磁盘上同步读取，然后在合并数据的性能更高一些，这和读取数据的记录数也有关系。



如何分区？用什么字段做分区依据？

具体如何分区和涉及的业务有关系，要看业务上最经常的写入和读取操作是什么，然后再考虑分区的策略。



既然与具体业务相关，我们就假定一个业务环境，假如我们要做一个论坛，对论坛的帖子和回复表进行分区。

论坛中最常见的写操作是1）发帖 2）回复帖子，

最常见的读操作是

1） 根据帖子id显示帖子详情和分页的帖子回复 

2） 根据帖子版面帖子列表页根据版面id分页读取帖子列表数据

怎么分区更合适呢？现在还没有准确答案，我有两种可能的方案，写下来，大家讨论看看。

方案1. 根据帖子ID区域段分区（1-300w一个分区、300w-600w一个分区…），这样理论上可以提高帖子详细页的读取速度，而对于写操作性能没有益处，对于根据版面id读取帖子列表页有可能有益

方案2. 根据版面id进行分区，这样对于写性能应该有提高，不同的分区对应不同的版面，当有两个版面同时有发帖回帖操作时，有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能，而对于帖子详细信息页没有性能影响。



多大的数据量才需要分区？

这个问题我只能说一个内部标准，如果一张表的记录超过在超过1000w，并以每月百万的数据量增长，那就需要分区。大家有不同的看法请回复讨论



关于具体的如何做分区，请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html



[code=SQL]代码加注释，希望对初学者有用。



USE [master]

GO

if exists (select * from sys.databases where name = 'Test_1')

drop database Test_1

GO

--创建新库，要演练分区所以我们会多创建两个文件组Test_A,Test_B，以便在后面的分区方案中使用。

CREATE DATABASE [Test_1] ON  PRIMARY 

( NAME = N'test_1', FILENAME = N'D:\sqldata\test_1.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [test_A] 

( NAME = N'Test_A', FILENAME = N'D:\sqldata\test_A.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [test_B] 

( NAME = N'Test_B', FILENAME = N'D:\sqldata\test_B.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'Test_log', FILENAME = N'D:\sqldata\Test_log.ldf' , SIZE = 7616KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

 COLLATE Chinese_PRC_CI_AS

GO

USE [Test_1]

GO

--若分区函数存在则先drop掉

IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'test_partition')

DROP PARTITION FUNCTION [test_partition]

GO

/**//*创建分区函数给后面的分区方案使用，分区函数很简单就是指定一个范围确定在某个值为什么的时候放在那个分区上*/

--新建一个简单的分区函数，该函数以1000为界分两个区

create partition function test_partition(int)

AS

RANGE LEFT FOR VALUES (1000) 

go

/**//*看分区方案是否存在，若存在先drop掉*/

IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'test_scheme')

DROP PARTITION SCHEME test_scheme

GO

--创建分区方案，分区方案需要指定一个分区函数，并指定在分区函数中分的区需要放在哪一个文件组上

create partition scheme test_scheme 

AS 

PARTITION [test_partition] TO (test_A,test_B)

GO

--创建分区表

if object_id('student','U') is not null

drop table student;

go

create table student

(

    id int identity(1,1) not null,

    name varchar(10) not null,

    class int not null,

    grade int

) on test_scheme(class) --在此处指定该表要使用的分区方案，并将指定分区依据列

go

--随便插入几条数据

insert into student values ('AQU',10,100); -- 这条数据在A分区上

insert into student values ('AQU_边界',1000,89); -- 这边数据也在A分区上是个边界，因为我们上面在函数中指定的是RANGE LEFT，所以1000在A分区上

insert into student values ('BQU',1001,90); -- 这一条肯定是在B分区上了。



go

--最后看看结果。$partition.分区函数(分区列)可以返回某一行所在的分区序号

select *,分区序号 = $partition.test_partition(class) from student

GO
[/code]
dawugui 2009-09-22
  • 打赏
  • 举报
 回复
对 时间建立个索引试试
xuam 2009-09-22
  • 打赏
  • 举报
 回复
create index at time
navy887 2009-09-22
  • 打赏
  • 举报
 回复
count 整个表，还是后边有条件。如果是整个表的话，17楼的方法不错；如果有条件的话，考虑在条件字段加索引。
lihan6415151528 2009-09-22
  • 打赏
  • 举报
 回复
建立索引吧
加载更多回复（1）
SQL Server 慢查询语句查询
SQL Server 慢查询语句查询 1、前10条慢查询 SELECT TOP 10 [session_id], [request_id], [start_time] AS '开始时间', [status] AS '状态', [command] AS '命令', dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名', [blocking_session_id] AS '正在阻塞其他会话的会话ID', der.[wait_type
引入pagehelper 分页 sqlserver，select count(0)报错，以及orderby 不能写到sql里面
项目运行的时候报错： Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。select count(0)报错，以及orderby 不能写到sql里
SQL Server 慢查询
例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。
sqlserver查询突然变得很慢
今天早上上班发现应该在周末执行完的脚本执行到了现在，靠着自建的etl日志表发现某个大表的查询 修改速度特别慢 。 后来重新启动了数据库（在控制面板的服务里面重新启动sqlserver），就好了。 猜测原因：可能是因为系统的临时数据库tempdb满了，或者是被阻塞之类的，在活动件事器里面看到我的那个进程一直在报RESOURCE_SEMAPHORE 等待状态 ，阻塞他的进程是tempdb数据库的，...
sql server 运行中的慢sql查询
之前一直用的mysql，所以对sql server不熟悉，结果新公司又有mysql 又有 sql server，瞬间感觉自己短板来了。 问题： 突然某天用户反馈系统部分功能无法使用了，第一反应是不是服务把系统cpu和内存占满了，因为之前出现过一次这种情况，当时就是服务器cpu占满了，后来重启服务暂时先解决了。 但是这次我们去服务器上看cpu和内存很正常，但是的确部分功能使用不了，最后查到是sql server数据库iops太高，当时想查询一下有哪些慢sql结果找了很久才找到，今天记录一下，sqlserver
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章