700万数据，查询速度慢，怎么优化？

2009-06-02 08:39:52 1.7k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

实战：上亿数据如何秒查最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度有从半小时以上(甚至查不出)到秒查的质变。从修改SQL查询语句逻辑到决定创建存储过程实现，花了我3天多的时间，在此总结一下，希望对朋友们有帮助。数据背景首先项目是西门子中国在我司实施部署的MES项目，由于项目是在产线上运作(3 years+)，数据累积很大。在项目的数据库中，大概上亿条数据的表有5个以上，千万级数据的表10个以上，百万级数据的表，很多… (历史问题，当初实施无人监管，无人监控数据库这块的性能问题。ps:我刚入职不久…) 不阅读详情

目前有一数据库，里面有700多万的数据记录，现在对其操作很慢

里面的数据是分了4级的，市，区，街道，社区，我现在想把数据分表存储，以街道为单位进行分表存储

但这样，汇总的时候也比较麻烦，不知道大家有什么好的意见！

...全文

混合编程实战：C#集成C++ PCL点云处理库，打造跨语言动态链接库本文详细介绍了C#与C++混合编程实战，重点讲解如何通过动态链接库（DLL）集成C++ PCL点云处理库，实现跨语言协作。内容涵盖环境配置、PCL功能封装、C#调用DLL的完整方案，以及性能优化和错误处理等进阶技巧，帮助开发者高效处理大规模点云数据，提升工业软件开发效率。阅读详情

x1234521

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

dollyxz 2010-09-25

打赏
举报

你把表结构和SQL语句贴出来，大家才好帮你,还有你慢的查询应该是汇总部分吧．我有这方面处理经验，
Dolly_xz@hotmail.com,你把要怎么做发给我，我可以帮你看看．

icelovey 2009-11-28

打赏
举报

学习~

jackterq 2009-11-28

打赏
举报

我來看看... 我不說話

envykok 2009-11-27

打赏
举报

这么多数据水平分区好了

建个聚集索引，对非常慢的建覆盖索引，查询丢失索引，运行索引优化

ydm5201314 2009-11-27

打赏
举报

飞过，学习

Mirana_NightShade 2009-11-27

打赏
举报

学习

icelovey 2009-11-27

打赏
举报

存储过程编写经验和优化措施 

阅读提示：本文介绍MS SQL的存储过程编写经验和优化措施 

一、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。　 

二、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。　 

三、内容：　 

1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。 

2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。　 

3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点： 

a)SQL的使用规范： 

　i.　尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。 

　ii.　尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。 

　iii.　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。 

　iv.　注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。 

　v.　不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 

　vi.　尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。 

　vii.　尽量使用“>=”，不要使用“>”。 

　viii.　注意一些or子句和union子句之间的替换 

　ix.　注意表之间连接的数据类型，避免不同类型数据之间的连接。 

　x.　注意存储过程中参数和数据类型的关系。 

　xi.　注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过个数据页面（k），那么系统将会进行锁升级，页级锁会升级成表级锁。 

b)索引的使用规范： 

　i.　索引的创建要与应用结合考虑，建议大的OLTP表不要超过个索引。 

　ii.　尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引 

　iii.　避免对大表查询时进行table scan，必要时考虑新建索引。 

　iv.　在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 

　v.　要注意索引的维护，周期性重建索引，重新编译存储过程。 

c)tempdb的使用规范： 

　i.　尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。 

　ii.　避免频繁创建和删除临时表，减少系统表资源的消耗。 

　iii.　在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。 

　iv.　如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。 

　v.　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。 

　vi.　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。 

d)合理的算法使用： 

根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。

gfgen 2009-06-05

打赏
举报

我目前也为此问题发愁，呵呵，只是想知道各位是如何知道查询速度的快慢的，第一次查询30秒没结果，第二次不到一秒，呵呵这个问题我早就提出了。

测试查询的速度时，有必要多次运行此查询，然后取一个平均值。因为查询(或存储过程)可能会存储在 SQL Server 内存中的过程缓存中，因此第一次尝试耗费的时间好像稍长一些，而所有后续尝试耗费的时间都较短。另外，运行您的查询时，可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时，可能会导致您的查询要排队等待。例如，如果您进行查询时某人正在更新此表中的数据，则在更新提交时您的查询可能需要耗费更长时间来执行

lingzhongzhao 2009-06-05

打赏
举报

表结构呢！贴出来。

Q832198 2009-06-05

打赏
举报

贴出你的结构来看看 C/S or B/S 开发语言还有是如何操作比较慢.....

stromboy007 2009-06-05

打赏
举报

SQL优化-索引　

qizhengsheng 2009-06-05

打赏
举报

顶ls的

nzperfect 2009-06-05

打赏
举报

[Quote=引用 49 楼 Q832198 的回复:]
700万只能说是很普通的表我们测试数据都是至少几千万条的记录的单表.

重要的是你返回的最好不必全部返回吧只返回你所需要的一小部分一般是没有任何问题的

700 万条你去 select * from tablename 而没有where 条件过滤这肯定是疯了
你可以去 Select top 1000 你需要的字段 from TableName where 。。。

如果是需要统计更简单了结果一般只会有几行到几百或者上千行应当都不算多的了,
速度也会很快啊不知道…
[/Quote]

你几千W？我几亿也一个表。
关键不是看记录数的多少，而是要看数据占用了多数据页。
以及你并发数量及io处理情况.

Q832198 2009-06-05

打赏
举报

700万只能说是很普通的表我们测试数据都是至少几千万条的记录的单表.

重要的是你返回的最好不必全部返回吧只返回你所需要的一小部分一般是没有任何问题的

700 万条你去 select * from tablename 而没有where 条件过滤这肯定是疯了
你可以去 Select top 1000 你需要的字段 from TableName where 。。。

如果是需要统计更简单了结果一般只会有几行到几百或者上千行应当都不算多的了,
速度也会很快啊不知道你们这种表结构和程序是如何设计的.

x1234521 2009-06-04

打赏
举报

首先感谢各位的帮忙

有人要看SQL语句，其实说实话，这些表设计不合理，但这些是上级机构给的，不能变的

现在执行SELECT * FROM TABLENAME都需要30~50秒，这个表有96个列，有三种类型，GUID，DATETIME，VARCHAR

索引也反复测试了，没有明显效果，目前为了不耽误用户使用，已经开始改成分表存储。

目前只能这样了！

再次感谢各位帮助！

bzr2004 2009-06-04

打赏
举报

1、根椐业务访问特点进行分区
2、建合适的索引，更重要的是要看索引是否起作用

ai_li7758521 2009-06-03

打赏
举报

最优考虑加合理索引，分区表的话工作量不小..

lianyisoft 2009-06-03

打赏
举报

关注这个问题，我也遇到同样的问题，期待解决中;

JDR梦之旅 2009-06-03

打赏
举报

表结构
索引
优化查询

aqbeyond 2009-06-03

打赏
举报

索引不能随便加,多了,查询可能会提高效率,但是当删除数据的时候又会很慢了.
建议LZ把主要的查询SQL贴出来,大家一起来分析一下好了.

加载更多回复（42）

700万数据，查询速度慢，怎么优化？

相关推荐