数据库中有2亿4千万的数据，查询时超时，求解决方法！

2018-08-04 11:01:08 866 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

如何使用QuickAPI导出千万级数据：内置数据库管理与SQL操作指南在如今的大数据时代，处理和导出千万级数据是一项常见任务，但对许多人来说也充满挑战。目前即使专业的数据库管理工具一般导出就是20万行，高一些的50万行，但到千万行级别非常不稳定，失败率极高。QuickAPI 提供了一种简单高效的解决方案，它内置了数据库管理功能和自研数据导出模块，支持通过SQL语句直接导出千万行级别数据。本文将详细介绍如何利用 QuickAPI 完成千万级数据的导出，全程无需编写代码，仅通过界面操作即可实现。QuickAPI 让千万级数据的导出变得简单高效。阅读详情

数据库中有2亿4千万的数据（数据结果如图），在SQL Server里查询，虽不提示超时，等待的时间很长，自己用vb.net写了个程序去访问数据库，一访问这个表就提示超时。
现在我想问的是，有什么方法可以查得更快（虽然不要求秒开，但也别等待的太长时间，最好就是控制在2秒左右），随后附上数据结构图及我用SQL语句，还有就是我写的程序访问数据库的超时提示图片。
这个数据库是已经分区了的，分了47个区，表索引也建立有。
Sql查询语句：select PackCode,BoxNo from PackCodeInfo where ProductID='3' and Batchno='1305131' and LevelNo=2 order by Boxno
数据表结构图：

我自己写的程序访问数据表出错的图：

...全文

mysql 千万数据分页查询优化只适合小型应用的快速开发，对于ERP,OA，大型网站，数据层包括逻辑层的东西都不能用框架。1、到了100万的数据，160万数据，15G表，190M索引，就算走索引，limit都得0.49秒。综上：如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！查询字段一较长字符串的时候，表设计时要为该字段多加一个字段,如，存储网址的字段查询的时候，不要直接查询字符串，效率低下，应该查诡该字串的crc32或md5。阅读详情

sg_linfeng

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

+ 关注 + 加入社区

切换为时间正序

weixin_41664753 2018-08-14

打赏
举报

数据库连接设置超时时长：SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = Conn_his;
sqlcom.CommandText = proc_name;
sqlcom.CommandTimeout = 1000000;
sqlcom.CommandType = CommandType.StoredProcedure;
查询SQL时，尽量用主键、索引查。若用主键索引查询时，where 后面尽量不要带其它非索引条件。若非要带其它非索引条件（字段），那这个字段尽量创建索引；

薛定谔的DBA 2018-08-09

打赏
举报

1. 有这个索引是最好的：PackCodeInfo(ProductID,Batchno,LevelNo,Boxno)include(PackCode,BoxNo)
当然该索引键列的顺序：1.业务优先；2.重复值最少的优先。

2. 创建一个聚集索引。聚集索引那么珍贵那么重要那么有用。

3. 不知增删改是否频繁，若是这样，查询加上 nolock，不要给查询加任何锁。

4. 在查询后面加 option(maxdop N)，可以指定多线程执行，N为线程数，

5.既然分区了，可以设置分区锁定方式“LOCK_ECALATION = AUTO”，参考：SqlServer 表选项LOCK_ESCALATION对分区的影响

6.定期创建索引，表太大不好重建。若是sql server 2014及以上版本，可以按分区重建。若不是，单个重建非聚集索引吧，或者创建新表迁移数据再更名。

开着拖拉机泡妞 2018-08-09

打赏
举报

引用 11 楼 yenange 的回复:

[quote=引用 10 楼 sywcf 的回复:]
--5. 所有不包含中文的字符串类字段，都应该由 nvarchar 改为 varchar ？？我不太同意这个做法，以我的经验，好多时，我还要把varchar 改为nvarchar，用varchar时，会很慢，但改为nvarchar就快多了。

你改列类型，只是主动更新了统计信息导致变快吧，如果你现在再改回 varchar ，你再看看是否会变慢？

如果你还不信，举一个可以重现的例子出来：
把脚本贴出来，
或者把你的测试库用云盘共享出来。[/quote]

不太懂你所谓的修改列的数据类型会主动更新统计信息是如何得来的。
你建一个表，在字段上价格统计信息或者索引，你再去修改列的数据类型试试。

吉普赛的歌 2018-08-08

打赏
举报

引用 10 楼 sywcf 的回复:

--5. 所有不包含中文的字符串类字段，都应该由 nvarchar 改为 varchar ？？我不太同意这个做法，以我的经验，好多时，我还要把varchar 改为nvarchar，用varchar时，会很慢，但改为nvarchar就快多了。

你改列类型，只是主动更新了统计信息导致变快吧，如果你现在再改回 varchar ，你再看看是否会变慢？

如果你还不信，举一个可以重现的例子出来：
把脚本贴出来，
或者把你的测试库用云盘共享出来。

sywcf 2018-08-08

打赏
举报

--5. 所有不包含中文的字符串类字段，都应该由 nvarchar 改为 varchar ？？我不太同意这个做法，以我的经验，好多时，我还要把varchar 改为nvarchar，用varchar时，会很慢，但改为nvarchar就快多了。

sywcf 2018-08-08

打赏
举报

--5. 所有不包含中文的字符串类字段，都应该由 nvarchar 改为 varchar ？？

开着拖拉机泡妞 2018-08-07

打赏
举报

select PackCode,BoxNo from PackCodeInfo where ProductID='3' and Batchno='1305131' and LevelNo=2 order by Boxno

索引如何设计的？
如果有在where后面的字段加索引，建议把这个索引对应的统计信息贴出来看一下
DBCC SHOW_STATISTICS(表名，索引名）
然后SET STATISTICS PROFILE ON，
同时，SET STATISTICS IO ON
SET STATISTICS TIME ON
先定位问题到底是由什么原因引起的再决定解决方案吧。

吉普赛的歌 2018-08-07

打赏
举报

这个表的初始设计就有问题，没办法很简单就解决了，老话——不破不立。
必须分表。
如果你不分表，这么大的表，频繁的读写之间必然阻塞、死锁严重。
不同进程之间的锁有相互影响，越大的表越严重，你的查询优化得再好也没有用。

先按我 #2 的第 6 点来吧。
分表之后，再考虑分区。
分区尽量按时间来，主要是热数据和冷数据要分开。
而且查询时必须要能用上分区字段，如果用不上，宁可不要分区。

吉普赛的歌 2018-08-06

打赏
举报

1， 2 步只是查询操作，能有什么影响？先贴出来再说吧。
3. 分区字段你查询时 where 里用不到，那分区有什么意义？还不如不分区，把聚集索建立在经常查的字段上。
4. 主要是关心是否频繁，你没回答。
5. 如果你确定只是纯英文或数字就换 varchar ，没什么好怕的。如果什么都不相信，来论坛就没什么意义了。
6. 如果你不按我说的分表，也不想做报表，这么大的数据量还想快速查询，很困难。思路要转变过来——不破不立。
-- 即使按你们现在的做法，也可以把转移的周期缩短，比如 2、3 个月转一次。

索引碎片整理之后，有一定的效果，你可以用一下，建立作业，每个周末的凌晨执行一次。但你的数据量这么大，还是解决不了根本。
https://blog.csdn.net/yenange/article/details/52412297

sg_linfeng 2018-08-06

打赏
举报

引用 4 楼 yenange 的回复:

1， 2 步只是查询操作，能有什么影响？先贴出来再说吧。
3. 分区字段你查询时 where 里用不到，那分区有什么意义？还不如不分区，把聚集索建立在经常查的字段上。
4. 主要是关心是否频繁，你没回答。
5. 如果你确定只是纯英文或数字就换 varchar ，没什么好怕的。如果什么都不相信，来论坛就没什么意义了。
6. 如果你不按我说的分表，也不想做报表，这么大的数据量还想快速查询，很困难。思路要转变过来——不破不立。
-- 即使按你们现在的做法，也可以把转移的周期缩短，比如 2、3 个月转一次。

索引碎片整理之后，有一定的效果，你可以用一下，建立作业，每个周末的凌晨执行一次。但你的数据量这么大，还是解决不了根本。
https://blog.csdn.net/yenange/article/details/52412297

问题是数据库不在我这里，所以无法去做第1、2步，并截图。
这个表是整个包装的主库，读、写、增，操作都颇为频繁的，而且同时操作并不是1台工控机对一条包装线在用，是十几台工控机对十几条包装线在用。

吉普赛的歌 2018-08-05

打赏
举报

--1. 执行下这个， 贴出截图出来：

EXEC sp_helpindex 'PackCodeInfo'



--2. 这个的结果， 也贴截图出来

SELECT 

 COUNT(DISTINCT ProductID) AS ProductID_CNT

,COUNT(DISTINCT Batchno) AS Batchno_CNT

,COUNT(DISTINCT LevelNo) AS LevelNo_CNT

FROM PackCodeInfo WITH(NOLOCK)



--3. 分区不是空口白话，分区字段必须放在 where 之中才能提高效率

--   3.1 你的 where 为什么不带分区字段？是否可以加上？

--   3.2 你经常用的查询就是下面这个？

/*

select PackCode,BoxNo 

from PackCodeInfo where ProductID='3' and Batchno='1305131' and LevelNo=2 

order by Boxno

*/



--4. 这个表的增、删、改是否频繁？



--5. 所有不包含中文的字符串类字段， 都应该由 nvarchar 改为 varchar 



--6. 2 亿多条数据， 已经是一个比较大的数据量， 想很快反应其实是比较困难的。

--   首先， 不需要的历史数据应该归档到历史表。 

--     按日期分 历史表（一年前），较常用表（一年内），当前表（一个月内） 比较好;

--   如果分 3 个表了还是卡或者不想分， 那你就得把常用的结果， 以报表形式弄出来， 

--   每天凌晨统计出结果报表， 让用户查最终的报表， 而不是直接查源表。

sg_linfeng 2018-08-05

打赏
举报

引用 2 楼 yenange 的回复:

--1. 执行下这个， 贴出截图出来：

EXEC sp_helpindex 'PackCodeInfo'



--2. 这个的结果， 也贴截图出来

SELECT 

 COUNT(DISTINCT ProductID) AS ProductID_CNT

,COUNT(DISTINCT Batchno) AS Batchno_CNT

,COUNT(DISTINCT LevelNo) AS LevelNo_CNT

FROM PackCodeInfo WITH(NOLOCK)



--3. 分区不是空口白话，分区字段必须放在 where 之中才能提高效率

--   3.1 你的 where 为什么不带分区字段？是否可以加上？

--   3.2 你经常用的查询就是下面这个？

/*

select PackCode,BoxNo 

from PackCodeInfo where ProductID='3' and Batchno='1305131' and LevelNo=2 

order by Boxno

*/



--4. 这个表的增、删、改是否频繁？



--5. 所有不包含中文的字符串类字段， 都应该由 nvarchar 改为 varchar 



--6. 2 亿多条数据， 已经是一个比较大的数据量， 想很快反应其实是比较困难的。

--   首先， 不需要的历史数据应该归档到历史表。 

--     按日期分 历史表（一年前），较常用表（一年内），当前表（一个月内） 比较好;

--   如果分 3 个表了还是卡或者不想分， 那你就得把常用的结果， 以报表形式弄出来， 

--   每天凌晨统计出结果报表， 让用户查最终的报表， 而不是直接查源表。

1、第1、2步的操作对数据库有没有什么影响？
2、我们这个数据库是生产库来的，每天都要使用，删除的操作没有，但是有查询、修改字段里的值的操作，还有需要往表里导入一些PackCode的新数据。
3、分区字段（PartionID字段）不是空值，是有值的，值的范围是1~47，在导入PackCode数据时，由包装系统自动分配。而且可能查询的PackCode存放的分区的值不一样，所以在Select查询时，不加入分区字段，并且我是精确到批号，产品编号、级别号来进行查询的，目的就是想要把整个批次的数据PackCode和Boxno查出，所以才用到select PackCode,BoxNo from PackCodeInfo where ProductID='3' and Batchno='1305131' and LevelNo=2 order by Boxno。
4、表里的字段有包含nvarchar类型，这个是系统自带的，不敢去修改字段的类型，担心影响整个系统正常运行。
5、每隔半年，我们都会将历史数据通过包装系统将数据转移至另一个历史表中，但由于我们的包装线比较多，即使有转移数据，数据量还是有大约1.5亿左右。

这两天都在网上找了相关的资料，大部分都是说索引的问题，说碎片太多，需要重新生成或重新组织索引，这样查询速度会快一些，不知这个方法是否可行？

sg_linfeng 2018-08-04

打赏
举报

每次查询出来的数据量最多1000条记录左右。最少也有200条以上。

数据库中有2亿4千万的数据，查询时超时，求解决方法！

相关推荐