help !! 此sql语句如何优化。1千万条数据中查需要7，8分钟

此sql语句如何优化。
有表多个同结构表命名为dev_log_table1，dev_log_table2，dev_log_table3，结构详细如下：
+---------------------+------+----------+-----------+-----------+------------+----------+---------+
| time                | type | subtype  | src_ip    | dst_ip    | log_repeat | dev_name | dev_pri |
+---------------------+------+----------+-----------+-----------+------------+----------+---------+
| 2009-06-29 16:25:31 | ips  | IPSEVENT | 100.1.1.2 | 200.1.1.2 |          1 | mjl.cc   |       0 |
| 2009-06-29 16:25:32 | ips  | IPSEVENT | 100.1.1.2 | 200.1.4.2 |          4 | mjl.cc   |       0 |
| 2009-06-29 16:25:33 | ips  | IPSEVENT | 100.1.1.2 | 200.1.1.2 |          1 | mjl.cc   |       0 |
| 2009-06-29 16:25:33 | ips  | IPSEVENT | 100.3.1.2 | 200.1.0.2 |          4 | mjl.cc   |       0 |
| 2009-06-29 16:25:33 | ips  | IPSEVENT | 100.1.1.2 | 200.1.1.2 |          1 | mjl.cc   |       0 |
+---------------------+------+----------+-----------+-----------+------------+----------+---------+
(字段意思分别为，时间，类型，子类型，源IP，目的IP，事件发生次数,设备名,设备优先级)需求：求出一段时间内，某个设备，发生的事件总数，事件是由多少个源IP，多少目的IP产生的。如果用：SELECT distinct src_ip ,distinct dst_ip, sum(log_repeat) from  （满足条件后的union多表)
当 union多表得出的数据很大时，C盘马上暴满。所以没有使用。
下面是我写的SQL，在三个表1千万条数据中查需要7，8分钟。显然用户受不了。大家帮忙看一下如何优化，或如果写。
SELECT    
(
SELECT   COUNT(distinct src_ip) SRCCOUNT 
FROM  
(
( 
SELECT distinct src_ip 
FROM dev_log_table3 
WHERE  type='ips' 
AND (time>=' 2009-06-29 00:00:17' AND time<'2009-06-29 23:41:17')  
AND subtype='ipsevent' 
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
 ) 
UNION ALL 
( 
SELECT distinct src_ip 
FROM dev_log_table2 
WHERE  type='ips' 
AND (time>=' 2009-06-29 00:00:17' AND time<'2009-06-29 23:41:17')  
AND subtype='ipsevent' 
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
 ) 
UNION ALL 
( 
SELECT distinct src_ip 
FROM dev_log_table1 
WHERE  type='ips' 
AND (time>=' 2009-06-29 00:00:17' AND time<'2009-06-29 23:41:17')  
AND subtype='ipsevent' 
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
 )
)  temptb ) SRCCOUNT, COUNT(distinct dst_ip) DSTCOUNT, SUM(log_repeat) SUMEVENTCOUNT, CEIL(SUM(log_repeat) / 23) AVGCOUNT FROM  
(
( 
SELECT   dst_ip, sum(log_repeat)  log_repeat 
FROM dev_log_table3 
WHERE  type='ips' 
AND (time>=' 2009-06-29 00:00:17' AND time<'2009-06-29 23:41:17')  
AND subtype='ipsevent' 
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc')) 
GROUP BY dst_ip
) 
UNION ALL 
( 
SELECT   dst_ip, sum(log_repeat)  log_repeat 
FROM dev_log_table2 
WHERE  type='ips' 
AND (time>=' 2009-06-29 00:00:17' AND time<'2009-06-29 23:41:17')  
AND subtype='ipsevent' 
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc')) 
GROUP BY dst_ip
) 
UNION ALL 
( 
SELECT   dst_ip, sum(log_repeat)  log_repeat 
FROM dev_log_table1 
WHERE  type='ips' 
AND (time>=' 2009-06-29 00:00:17' AND time<'2009-06-29 23:41:17')  
AND subtype='ipsevent' 
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc')) 
GROUP BY dst_ip
) 
)  temptb 
 

解决方案 »

  1.   

    1,将时间和设备建立非聚集索引
    2,不要用distinct,它没有group效率高
    3,查看各字符型字段,字段宽度尽可能的小另外,如果你的SQL是2005的话,建成分区表,然后将三个表导入一个表
      

  2.   

    楼上已经介绍的很清楚了。1.关键字段加上索引。（这是最重要的，效率也会有明显提升的。）
    2.最好不要用distinct.
      

  3.   

     查询语句优化
        T-SQL的写法上有很大的讲究，DBMS处理查询计划的过程是：a、查询语句的词法、语法检查；b、将语句提交给DBMS的查询优化器；c、优化器做代数优化和存取路径的优化；d、由预编译模块生成查询规划；e、在合适的时间提交给系统处理执行；f、将执行结果返回给用户。
    （1）    COMMIT和ROLLBACK的区别：ROLLBACK回滚所有的事务；COMMIT提交当前的事务。在动态语句中写事务，请将事务写在外面，如：BEGIN TRAN EXEC（@SQL） COMMIT TRANS或者将动态SQL写成函数或者存储过程。
    （2）    在大数据两的查询输出SELECT语句中尽量不要使用自定义函数，调用自定义函数的函数时系统调用是一个迭代过程，很影响查询输出性能的。在查询字段时尽可能使用小字段两输出，并在WHERE子句或者使用SELECT TOP 10/1 PERCENT来限制返回的记录数，使用SET ROWCOUNT来限制操作的记录数，避免整表扫描。返回不必要的数据，不但浪费了服务器的I/O资源，加重了网络的负担，如果表很大的话，在表扫描期间将表锁住，禁止其他的联接访问，后过很严重的。
    （3）    SQL的注释申明对执行查询输出没有任何影响。
    （4）    使用计算列对数据进行简单计算，尽量避免在查询语句中对数据进行运算。
    （5）    尽可能不使用光标，它会占用大量的资源。如果需要ROW-BY-ROW地执行，尽量采用非光标技术，如：客户端循环、临时表、TABLE变量、子查询、CASE语句等等。
    （6）    使用PROFILER来跟踪查询，得到查询所需的时间，找出SQL的问题所在，用索引优化器优化索引。
    （7）    注意UNION和UNION ALL的区别。在没有必要的时候不要用DISINCT，它同UNION一样会降低查询速度，重复的记录在查询里是没有问题的。
    （8）    用sp_configure ‘query governor cost limit’或者 
            SET QUERY_COVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的        资源超出限制时，服务器自动取消查询，在查询之前就扼杀掉。SET LOCKTIME        设置锁的时间。
    （9）    不要在WHERE子句中列名加函数，如CONVERT，SUBSTRING等，如果必须用函数的时候，创建计算列在创建索引来替代。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，其中EXISTS比IN更快，最慢的NOT操作。
    （10）    使用QUERY ANALYZER，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般20%的代码占用了80%的资源，优化的重点就是这些慢的地方。
    （11）    如果使用了IN或者OR等时发现查询没有走索引，使用显式申明指定索引，如：Select * From FA01(INDEX=IX_SEX) Where AA0107 IN(‘01’,‘02’)。
    （12）    在需要对已有数据进行比较复杂计算才能获得查询的结果数据时,将需要查询的结果预先计算好放在表中,查询的时候在SELECT。
    （13）    数据库有一个原则是代码离数据越近越好，所有有限选择DEFAULT，依次为RULES，CONSTRAINT，PROCEDURE来编写程序的质量高，速度快。如果要插入大的二进制到IMAGE列，使用存储过程，千万不要用内嵌INSERT直接插入。因为这样应用程序首先将二进制转换成字符串，服务器收到字符后又将他转换成二进制。存储过程直接传入二进制参数即可，处理速度明显改善，如：CREATE PROCEDURE image_insert @image varbinary as Insert into table(fImage) values(@image)。
    （14）    Between在某些时候比IN速度更快，更快地根据索引找到范围。由于IN会比较多次，所以有时会慢些。
    （15）    尽量不要建没有作用的事务例如产生报表时，浪费资源，只有在必须使用事务时才建立合适的事务。
    （16）    用OR的字句可以分解成多个查询，并通过UNION连接多个查询。速度取决与是否使用索引。如果查询需要用联合索引，用UNION ALL执行的效率更高些。
    （17）    尽量少用视图，视图的效率低。对视图操作比直接对表操作慢，可以用SRORED PROCEDURE来代替。特别是不要用视图嵌套，嵌套视图增加了寻找原始资料的难度。视图是存放在服务器上的被优化好了的已经产生查询规划的SQL。对单表数据检索时，不要使用指向多表的视图，否则增加了不必要的系统开销，查询也会受到干扰。没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行，增加了额外的开销，这同UNION和UNION ALL原理相同。
    （18）    当使用SELECT INTO和CREATE TABLE时，会锁住系统表（SYSOBJECTS，SYSINDEXES等），从而阻塞其他的连接的存取。所以千万不要在事务内部使用。如果经常要用到临时表时请使用实表或者临时表变量。尽量少用临时表，用结果集和TABLE类型的变量来代替。
    （19）    在使用GROUP BY HAVING子句时，在使用前剔除多余的行，尽量避免使用HAVING子句剔除行工作。剔除行最优的执行顺序是：SELECT的WHERE子句选择所有合适的行，GROUP BY用来分组统计行，HAVING字句用来剔除多余的分组。如果只是分组不进行计算则DISTINCT比GROUP BY速度快。
      

  4.   

    谢谢大家，忘了告诉大家，time和type,已经加加上了索引。
      

  5.   

    谢谢大家 不用 distinct不会慢快很多。  关键字time ,type发贴前就加了加上了索引
    300万条数据测试结果：mysql> SELECT distinct src_ip
        -> FROM dev_log_table3
        -> WHERE  type='ips'
        -> AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17')
        -> AND subtype= 'ipsevent'
        -> AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
        -> ;
    +-----------+
    | src_ip    |
    +-----------+
    | 100.1.1.2 |
    | 200.1.1.2 |
    +-----------+
    2 rows in set (14.66 sec)mysql> SELECT src_ip
        -> FROM dev_log_table3
        -> WHERE  type='ips'
        -> AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17')
        -> AND subtype='ipsevent'
        -> AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
        -> group by src_ip;
    +-----------+
    | src_ip    |
    +-----------+
    | 100.1.1.2 |
    | 200.1.1.2 |
    +-----------+
    2 rows in set (14.55 sec)
      

  6.   

    这是mysql的吧？mysql的还是建议到专门板块询问
      

  7.   

    dev_log_table1，dev_log_table2，dev_log_table3
    同结构的表在mysql里面做成分区表嘛按时间分区就ok