海量数据，datetime字段做索引非常慢，怎么办？怎么办？？？？求大牛，，，求大牛，，，求大牛

rd16 2014-05-13 10:59:31
加精
我有一个表大概有5百万条数据，表结构大致如下：


id (int 类型，自增主键+聚集索引）
i_本级代理id （int 类型，非聚集索引）
i_下级代理id （int 类型，非聚集索引）
i_本级占成金额
.....其它字段略
i_结单时间 （datetime类型， 非聚集索引）


***************************************************

我的SQL语句如下（大致返回20条数据行，用时27秒）



declare @d datetime

set @d=getdate()



  DECLARE @userid int

  DECLARE @startsj datetime

  DECLARE @endjs datetime





  SET @userid =1

  SET @startsj ='2011-03-31 00:00:00'

  SET @endjs   ='2015-03-31 00:00:00'

  





SELECT 

 下级代理名=(select top 1 i_用户名 FROM  T_会员 WHERE i_用户id=A.i_下级代理id),

 下级占成金额=(select SUM(i_本级占成所得) FROM  T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs),

 下级退佣金额=(select SUM(i_本级退佣金额) FROM  T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs),

 下级赚佣金额=(select SUM(i_本级赚佣金额) FROM  T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs),

 下级盈亏=(select SUM(i_本级盈亏) FROM  T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs),

 本级交上级=(select SUM(i_本级交上级) FROM  T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs),

 



 SUM(i_会员购买金额) AS 会员购买金额,

 SUM(i_本级占成所得) AS  本级占成所得,

 SUM(i_本级退佣金额) AS  本级退佣金额,

 SUM(i_本级赚佣金额) AS  本级赚佣金额,

 SUM(i_本级盈亏) AS  本级盈亏,

 SUM(i_本级交上级) AS  本级交上级



FROM T_代理报表 A

   WHERE A.i_本级代理id=@userid

     AND (A.i_结单时间 between @startsj AND @endjs)

     GROUP BY

       A.i_本级代理id,

       A.i_下级代理id

 

 

 

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())


****************************************************

问题：

当我把【 (A.i_结单时间 between @startsj AND @endjs)】 条件去掉，测试只要2.3秒



也就是说，只要加了【i_结单时间 】字段查询，效率就慢了几十倍，但这个字段我是有加索引的。


如果说数据表太大导致慢也没道理，如果是数据量问题，应该没加这个条件会更慢



另外我这个表几乎每秒钟都在写入数据，不知道会不会跟这个有关系
...全文
4525 106 打赏 收藏 转发到动态 举报
写回复
用AI写文章
106 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-05-26
  • 打赏
  • 举报
 回复
insert 是自动提交事务，即使你没有放在begin tran里面，它自己也会成为一个事务，而且insert要申请的锁比较高级，具体看表结构和insert语句等等。提交了事务，insert才算有效，索引才会开始维护。不过这一过程往往都很快，你可能感觉不出来，维护B-Tree的速度会比单纯的插入快
rd16 2014-05-25
  • 打赏
  • 举报
 回复
引用 100 楼 zqtsx 的回复:
你需要符合索引，独立索引起步到作用几乎，因为后面用到了group by A.i_本级代理+A.i_结单时间+A.i_本级代理id+A.i_下级代理id组合的符合索引，注意顺序 要跟 where 后面的 查询字段的顺序一致，如果需要按照id排序，还需要在末尾不上ID字段的索引，我这里只是举个例子，不代表可以直接实用于你的情况，具体还要自己根据实际索引使用频繁度 进行组合/复合索引的 建设 具体可以关于 复合索引 的优化案例 可以参照 下这个 http://blog.csdn.net/zqtsx/article/details/8929625
“注意顺序 要跟 where 后面的 查询字段的顺序一致” <-------------兄台，这个能详细说明一下吗？ 你的文章我看了，写得非常棒，MYSQL分页似乎比MSSQL方便很多啊。。。。。
rd16 2014-05-25
  • 打赏
  • 举报
 回复
引用 104 楼 DBA_Huangzj 的回复:
insert要看你的事务，如果insert所在的事务结束了就开始创建维护索引，至于统计信息，假设你有1亿的数据，只insert个100条，是不会更新的，关系数据库的瓶颈通常是在插入，而不是在查询， 但是第三范式有利于插入，不过对查询相对不利。如果null很多，可以用filter index，
“insert所在的事务”<----这个不太理解 不知道你说的事物是不是 Begin Tran，我没有用到这个脚本。 现在表内数据现在一千万左右，有时候每秒同时insert 10-20条。 不知道insert完之后是不是马上生成索引？？
發糞塗牆 2014-05-25
  • 打赏
  • 举报
 回复
insert要看你的事务，如果insert所在的事务结束了就开始创建维护索引，至于统计信息，假设你有1亿的数据，只insert个100条，是不会更新的，关系数据库的瓶颈通常是在插入，而不是在查询， 但是第三范式有利于插入，不过对查询相对不利。如果null很多，可以用filter index，
rd16 2014-05-25
  • 打赏
  • 举报
 回复
引用 78 楼 DBA_Huangzj 的回复:
尽量搞大的数据量再优化查询
版主，再请教一个问题。 我的表每秒钟都在insert数据（频繁的时候有可能一秒钟同时插入几十条数据），那么MSSQL是不是insert完之后就马上生成索引？ 还是有可能insert完之后，可能要等N秒才能生成索引。 注：我的个表只是insert很频繁，没有update和delete操作。 如果换成你来设计这个表，不知道有没有要注意的地方。 比如我的字段是允许NULL的，实际上字段里是没有NULL的，我是不是要在WHERE加上 is not null 会更好。
摘取天上星 2014-05-22
  • 打赏
  • 举报
 回复
内存大了就加内存啊~，不过索引失效 目测也会影响到内存问题的~
摘取天上星 2014-05-22
  • 打赏
  • 举报
 回复
你需要符合索引，独立索引起步到作用几乎，因为后面用到了group by A.i_本级代理+A.i_结单时间+A.i_本级代理id+A.i_下级代理id组合的符合索引，注意顺序 要跟 where 后面的 查询字段的顺序一致，如果需要按照id排序，还需要在末尾不上ID字段的索引，我这里只是举个例子，不代表可以直接实用于你的情况，具体还要自己根据实际索引使用频繁度 进行组合/复合索引的 建设 具体可以关于 复合索引 的优化案例 可以参照 下这个 http://blog.csdn.net/zqtsx/article/details/8929625
woshizhaoxuhui 2014-05-20
  • 打赏
  • 举报
 回复
学习一下。。
發糞塗牆 2014-05-20
  • 打赏
  • 举报
 回复
如果高版本的SQL Server，比如2008，对null有额外处理，问题不是很大，是否允许为null要看业务和系统设计，不能单纯看性能。is null 比is not null是否高效，也要看对应的比例，如果null 占比例少，is null也还是比较有效。另外对于null比较多的列，可以考虑用filter index，有人称过滤索引，有人称筛选索引，不过都不重要，就那么一个意思，创建索引的时候可以加where条件过滤掉
rd16 2014-05-19
  • 打赏
  • 举报
 回复
引用 78 楼 DBA_Huangzj 的回复:
尽量搞大的数据量再优化查询
大牛，现在速度一直保持在10秒以下，比以前的稳定多了。 目前有个顾虑 我创建列的时候，用的都是允许NULL，如： [i_结单时间] [datetime] NULL 允许NULL会不会影响性能，是不是改成 [i_结单时间] [datetime] NOT NULL 会更好
xiaoxiangqing 2014-05-19
  • 打赏
  • 举报
 回复
是没有用到索引导致的
YaMaHaHa01 2014-05-18
  • 打赏
  • 举报
 回复
SELECT 下级代理名=(select top 1 i_用户名 FROM T_会员 WHERE i_用户id=A.i_下级代理id), 下级占成金额=(select SUM(i_本级占成所得) FROM T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs), 下级退佣金额=(select SUM(i_本级退佣金额) FROM T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs), 下级赚佣金额=(select SUM(i_本级赚佣金额) FROM T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs), 下级盈亏=(select SUM(i_本级盈亏) FROM T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs), 本级交上级=(select SUM(i_本级交上级) FROM T_代理报表 WHERE i_本级代理id=A.i_下级代理id AND i_结单时间 between @startsj AND @endjs), SUM(i_会员购买金额) AS 会员购买金额, SUM(i_本级占成所得) AS 本级占成所得, SUM(i_本级退佣金额) AS 本级退佣金额, SUM(i_本级赚佣金额) AS 本级赚佣金额, SUM(i_本级盈亏) AS 本级盈亏, SUM(i_本级交上级) AS 本级交上级 FROM T_代理报表 A WHERE A.i_本级代理id=@userid AND (A.i_结单时间 between @startsj AND @endjs) GROUP BY A.i_本级代理id, A.i_下级代理id 先简化语句吧。同样条件5次查询，咋能不慢。
在路上_- 2014-05-17
  • 打赏
  • 举报
 回复
期待进一步的测试结果……
在路上_- 2014-05-17
  • 打赏
  • 举报
 回复
学习…… 版主，把这贴加精吧
waiwlq 2014-05-17
  • 打赏
  • 举报
 回复
内存么朋友啊？
lg314 2014-05-16
  • 打赏
  • 举报
 回复 
CREATE INDEX IX_i_T_代理报表_结单时间_i_本级代理 ON T_代理报表 (i_结单时间,i_本级代理id,i_下级代理id) 
INCLUDE (i_本级占成所得,i_本级退佣金额,i_本级赚佣金额,i_本级盈亏,i_本级交上级)
还有创建这个索引，这个数据量单纯的给结单时间加索引没有意义
lg314 2014-05-16
  • 打赏
  • 举报
 回复 
SELECT  下级代理名 = (
                  SELECT TOP 1 i_用户名 FROM T_会员 WHERE i_用户id = A.i_下级代理id
                )
      , SUM(app.下级占成金额)AS 下级占成金额
      , SUM(app.下级退佣金额)AS 下级退佣金额
      , SUM(app.下级赚佣金额)AS 下级赚佣金额
      , SUM(app.下级盈亏)AS 下级盈亏
      , SUM(app.下级交上级)AS 本级交上级
      , SUM(i_本级占成所得) AS 本级占成所得
      , SUM(i_本级退佣金额) AS 本级退佣金额
      , SUM(i_本级赚佣金额) AS 本级赚佣金额
      , SUM(i_本级盈亏) AS 本级盈亏
      , SUM(i_本级交上级) AS 本级交上级
FROM    T_代理报表 A JOIN (
              SELECT  i_本级代理id
					  , SUM(i_本级占成所得) AS 下级占成金额
                      , SUM(i_本级退佣金额) AS 下级退佣金额
                      , SUM(i_本级赚佣金额) AS 下级赚佣金额
                      , SUM(i_本级盈亏) AS 下级盈亏
                      , SUM(i_本级交上级) AS 下级交上级
              FROM      T_代理报表
              WHERE   i_结单时间 BETWEEN @startsj AND @endjs
              GROUP BY i_本级代理id
            ) app
        ON A.i_下级代理id=app.i_本级代理id
WHERE   A.i_本级代理id = @userid
        AND ( A.i_结单时间 BETWEEN @startsj AND @endjs )
GROUP BY A.i_本级代理id, A.i_下级代理id
这样呢？
rd16 2014-05-16
  • 打赏
  • 举报
 回复
引用 85 楼 lg314 的回复:
CREATE INDEX IX_i_T_代理报表_结单时间_i_本级代理 ON T_代理报表 (i_结单时间,i_本级代理id,i_下级代理id) 
INCLUDE (i_本级占成所得,i_本级退佣金额,i_本级赚佣金额,i_本级盈亏,i_本级交上级)
还有创建这个索引，这个数据量单纯的给结单时间加索引没有意义
兄台有何高见？ 把时间列改为 数字类型？ 改成 yyyymmddhhmm数字类型会不会更快？
rd16 2014-05-16
  • 打赏
  • 举报
 回复
引用 84 楼 lg314 的回复:
SELECT  下级代理名 = (
                  SELECT TOP 1 i_用户名 FROM T_会员 WHERE i_用户id = A.i_下级代理id
                )
      , SUM(app.下级占成金额)AS 下级占成金额
      , SUM(app.下级退佣金额)AS 下级退佣金额
      , SUM(app.下级赚佣金额)AS 下级赚佣金额
      , SUM(app.下级盈亏)AS 下级盈亏
      , SUM(app.下级交上级)AS 本级交上级
      , SUM(i_本级占成所得) AS 本级占成所得
      , SUM(i_本级退佣金额) AS 本级退佣金额
      , SUM(i_本级赚佣金额) AS 本级赚佣金额
      , SUM(i_本级盈亏) AS 本级盈亏
      , SUM(i_本级交上级) AS 本级交上级
FROM    T_代理报表 A JOIN (
              SELECT  i_本级代理id
					  , SUM(i_本级占成所得) AS 下级占成金额
                      , SUM(i_本级退佣金额) AS 下级退佣金额
                      , SUM(i_本级赚佣金额) AS 下级赚佣金额
                      , SUM(i_本级盈亏) AS 下级盈亏
                      , SUM(i_本级交上级) AS 下级交上级
              FROM      T_代理报表
              WHERE   i_结单时间 BETWEEN @startsj AND @endjs
              GROUP BY i_本级代理id
            ) app
        ON A.i_下级代理id=app.i_本级代理id
WHERE   A.i_本级代理id = @userid
        AND ( A.i_结单时间 BETWEEN @startsj AND @endjs )
GROUP BY A.i_本级代理id, A.i_下级代理id
这样呢？
SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 (12 行受影响) 表 'T_代理报表'。扫描计数 50，逻辑读取 89730 次，物理读取 2067 次，预读 61412 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 表 'T_会员'。扫描计数 0，逻辑读取 24 次，物理读取 9 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 SQL Server 执行时间: CPU 时间 = 11149 毫秒，占用时间 = 13573 毫秒。 ==================================================== 第二次 (12 行受影响) 表 'T_代理报表'。扫描计数 50，逻辑读取 90155 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 表 'T_会员'。扫描计数 0，逻辑读取 24 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 SQL Server 执行时间: CPU 时间 = 14586 毫秒，占用时间 = 5380 毫秒。
wangsufu77 2014-05-16
  • 打赏
  • 举报
 回复
不明觉厉
加载更多回复（82）
PowerBI系列之Power Query专题
PowerBI系列之Power Query专题1.  获取数据 数据源种类介绍和获取Excel数据源输入数据和拷贝数据：创建辅助表解析Json/XML数据格式获取Web网页数据和URL添加动态参数连接数据的四种模式：Import、DirectQuery、Live Connection、Dual双 属于混合模式连接数据库：Sql server、 Mysql（直连但是必须先安装一个mysql插件）DirectQuery直连查询：Sql serverODBC方式获取数据表关联或多个Sql或调用存储过程获取数据SQL中动态传参和自定义函数: sql中使用参数或数据库名称使用参数连接Sharepoint和OneDrive数据源连接Dataset和Dataflow 替换本地数据源为Sharepoint数据源并保留数据处理操作 终止当前数据刷新Loading：Cancel Query数据源设置-重置数据连接凭证PBIDS连接数据源创建和使用报表模块（输入或值列表）利用报表模板和参数控制线下报表数据权限DirectQuery启用自动页面刷新和更改检测管理聚合表提高DirectQuery查询性能动态M查询参数提高DirectQuery查询性能添加数据刷新时间 DateTime.LocalNow()和Getdate()2.  数据清洗和M语言M语言和官方文档介绍PowerQuery中查阅M函数：=#shared, Ctrl+Space提示数据清洗之常用技能：提升标题、更改数据类型、保留删除错误或空行，删除重复项、选择列和删除列、填充单元格、合并列、拆分、提取、替换、条件替换、添加自定义列，添加条件列、添加索引列、分组、添加年月日列、追加和合并查询透视和逆透视以及转置合并单元格的Excel文件处理导入文件夹中多Excel文件并合并解决多文件合并中列顺序不一致使用参数和函数批量导入文件 文本中提取中文、英文、数字等处理双引号转义 列拆分详解解决列名改变错误解决列丢失错误动态显示、排序和重命名列为所有列名添加前缀列名字母大写和分隔符调整Trim标题列中的多余空格如何处理load数据错误为什么load的Excel数据有null空行为什么load的Excel数据标题在第二行灵活添加占位符规范同类相似数据数据按多列排序为分组添加Index序号分组内值合并诊断工具分析数据处理过程PowerQuery小技巧分享 新冠病例活动轨迹地图标识 
MySQL-在字段上使用函数不会走索引的原因是什么？
题记 我们常常从关于MySQL的各种技术博客、书籍上面看到这样一句话：“不能在字段上使用函数，否则就不会走索引”。那么这句话的依据是什么呢？ 本文就围绕“不能在字段上使用函数，否则就不会走索引”这句话来展开分析。 Talk is cheap. Show me the code 假设存在这样一张交易记录表-trade_log，该表中存在三个字段id(主键)，operator_id(操作人id)、create_time(交易时间)。建表语句： CREATE TABLE `trade_log` ( `id`
多个单列索引和联合索引的区别详解
背景： 为了提高数据库效率，建索引是家常便饭；那么当查询条件为2个及以上时，我们是创建多个单列索引还是创建一个联合索引好呢？他们之间的区别是什么？哪个效率高呢？我在这里详细测试分析下。 一、联合索引测试 注：Mysql版本为 5.7.20 创建测试表(表记录数为63188)： CREATE TABLE `t_mobilesms_11` ( `id` bigint(20) NOT ...
单列索引和联合索引，有什么区别？
作者：深寒丶 来源：blog.csdn.net/abysscarry/article/details/80792876 背景： 为了提高数据库效率，建索引是家常便饭；那么当查询条件为 2 个及以上时，我们是创建多个单列索引还是创建一个联合索引好呢？他们之间的区别是什么？哪个效率高呢？我在这里详细测试分析下。 一、联合索引测试 注：Mysql 版本为 5.7.20 创建测试表(表记录...
0基础学MySQL数据库—从小白到大牛(27)索引的创建与设计原则
0基础学MySQL数据库—从小白到大牛(27)索引的创建与设计原则
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章