首页 新闻 赞助 找找看

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

0
悬赏园豆:10 [已关闭问题]

此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

qqcrazyer的主页 qqcrazyer | 初学一级 | 园豆:110
提问于:2009-07-06 17:59
< >
分享
其他回答(1)
0

千万级数据应该考虑自开发了,或者迁移到非关系数据库,鉴于如此简单的结构,用关系数据库很浪费性能。

也许这则新闻能给你和你的公司以启示:http://www.cnbeta.com/articles/88079.htm

 

或者,如果你的需求比较单一的话,按日期分表,或者直接在文件系统中按日期划分目录存储数据,都会比这样用起来好得多吧。

斯克迪亚 | 园豆:4124 (老鸟四级) | 2009-07-06 21:07
0

没太看懂,不过 获取SRCCOUNT的方法好像有优化空间。

还有就是看执行计划,然后分段分析了。

阿水 | 园豆:506 (小虾三级) | 2009-07-08 16:33
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册