mysql - 有没有办法提高 mysql 事件执行的性能？

Question

我有一个 MySQL 数据库，每天在一个称为流的表中包含 200 万条数据(行/天)。我编写了一个事务来读取和汇总来自该表的数据，以写入这里称为 top_tcp_ports 和 top_udp_ports 的临时表。当我在 Mysql 控制台上运行以下事务查询时，通常需要 30 秒或更短的时间来处理数据并将数据插入到这两个表(top_tcp_ports 和 top_udp_ports)。但相反，当 MySQL 事件调度程序运行此事务时，处理整个事务需要 7 分钟。此事件每 1 小时运行一次。这里的performance和timing都在考虑。

DELIMITER |
    CREATE EVENT IF NOT EXISTS top_tcp_udp_ports_hourly
    ON SCHEDULE EVERY 1 HOUR starts CURRENT_TIMESTAMP  
    DO
    BEGIN
        INSERT INTO messages(message,created_at) VALUES('Start Event Execution Log ....',NOW());

        CREATE TABLE IF NOT EXISTS top_tcp_ports
        (
        time_stamp datetime,
        tcp_port mediumint(8) signed,
        octetsTotal bigint(20) unsigned,
        packetTotal bigint(20) unsigned
        )ENGINE=MyISAM;

        CREATE TABLE IF NOT EXISTS top_udp_ports
        (
        time_stamp datetime,
        udp_port mediumint(8) signed,
        octetsTotal bigint(20) unsigned,
        packetTotal bigint(20) unsigned
        )ENGINE=MyISAM;

        start transaction;

        SET @v1 :=  (select max(saved_max_id ) from saved_id);

        INSERT INTO top_tcp_ports (time_stamp, tcp_port, octetsTotal, packetTotal)
        SELECT (timestamp(res2.date, maketime(res2.hour,00,00))), res2.tcp_port, res2.octetsTotal, res2.packetTotal from
        (SELECT dt as date, hr as hour, tcp_port, sum(byt) as octetsTotal, sum(pkt) as packetTotal from
            (
                (SELECT -1 as tcp_port, sum(packetTotalCount+reversePacketTotalCount) as pkt, sum(octetTotalCount+reverseOctetTotalCount) as byt, date(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as dt, hour(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as hr FROM flows where protocol = 6 and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00') and (SELECT @v1) < id group by dt, hr)
            union
                (SELECT srcport as tcp_port, sum(packetTotalCount+reversePacketTotalCount) as pkt, sum(octetTotalCount+reverseOctetTotalCount) as byt, date(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as dt, hour(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as hr FROM flows where protocol = 6 and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00') and (SELECT @v1) < id group by srcport, dt, hr having byt > 1048576)
            union
                (SELECT  dstport as tcp_port, sum(packetTotalCount+reversePacketTotalCount) as pkt, sum(octetTotalCount+reverseOctetTotalCount) as byt, date(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as dt, hour(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as hr FROM flows where protocol = 6 and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00') and (SELECT @v1) < id group by dstport, dt, hr having byt > 1048576)
            ) as res
        group by tcp_port, dt, hr) as res2;
        INSERT INTO messages(message,created_at) VALUES('Mid_1 Event Execution Log ....',NOW());

        INSERT INTO top_udp_ports (time_stamp, udp_port, octetsTotal, packetTotal)
        SELECT (timestamp(res3.date, maketime(res3.hour,00,00))), res3.udp_port, res3.octetsTotal, res3.packetTotal from
        (SELECT dt as date, hr as hour, udp_port, sum(byt) as octetsTotal, sum(pkt) as packetTotal from
            (
                (SELECT -1 as udp_port, sum(packetTotalCount+reversePacketTotalCount) as pkt, sum(octetTotalCount+reverseOctetTotalCount) as byt, date(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as dt, hour(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as hr FROM flows where protocol = 17 and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00') and (SELECT @v1) < id group by dt, hr)
            union
                (SELECT srcport as udp_port, sum(packetTotalCount+reversePacketTotalCount) as pkt, sum(octetTotalCount+reverseOctetTotalCount) as byt, date(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as dt, hour(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as hr FROM flows where protocol = 17 and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00') and (SELECT @v1) < id group by srcport,dt,hr having byt > 1048576)
            union
                (SELECT  dstport as udp_port, sum(packetTotalCount+reversePacketTotalCount) as pkt, sum(octetTotalCount+reverseOctetTotalCount) as byt, date(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as dt, hour(CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')) as hr FROM flows where protocol = 17 and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00') and (SELECT @v1) < id group by dstport,dt,hr having byt > 1048576)
            ) as res
        group by udp_port, dt, hr) as res3;
        INSERT INTO messages(message,created_at) VALUES('Mid_2 Event Execution Log ....',NOW());

        SET SQL_SAFE_UPDATES = 0;
        update saved_id set saved_max_id=IFNULL((select max(id) from flows where id> (SELECT @v1) and CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM')<DATE_FORMAT(now(),'%Y-%m-%d %H:00:00')),(SELECT @v1)) where row_id=1;

        commit; 

        INSERT INTO messages(message,created_at) VALUES('End Event Execution Log ....',NOW());

    END |
    DELIMITER ;

这里的Message表用于跟踪事件级别的进程。

The question is how can I improve performance of MySQL events scheduling or boost it up ?

Is there any body face this problem?

Answer 1

我怀疑 @v1 是问题所在。请添加 EXPLAIN SELECT ... inside 存储过程，以查看其在那里 是如何工作的。然后在 SP 之外做同样的事情。我猜 EXPLAINs 会有所不同，并且希望能提供信息。

这些会加快速度吗？

CONVERT_TZ(flowEndMilliseconds,'UTC','SYSTEM') <
    DATE_FORMAT(now(), '%Y-%m-%d %H:00:00')
-->
flowEndMilliseconds < CONVERT_TZ(NOW(), 'SYSTEM', 'UTC')

和

AND ( SELECT  @v1) < id

加号

INDEX(protocol, flowEndMilliseconds)
-->
AND @v1 < id