MySQL은 트리거에서 최소 및 제한을 사용하여 쿼리를 최적화합니다.

이 mysql 쿼리가 있습니다.

SELECT MIN(v.ifr) FROM (SELECT v.ifr FROM tbl_valuation v WHERE v.stock_id = 1 ORDER BY v.created_at DESC LIMIT 19) as v;

쿼리 설명 :

+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+---------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  |   19 |   100.00 | NULL                                  |
|  2 | DERIVED     | v          | NULL       | ref  | idx_stock     | idx_stock  | 9       | const | 2873 |   100.00 | Using index condition; Using filesort |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+---------------------------------------+

문제는이 쿼리와 이와 유사한 다른 쿼리가 트리거에 있으므로 삽입하기 전에이 쿼리가 일부 값을 실행하고 업데이트한다는 것입니다. 트리거는 분당 약 천 번 활성화됩니다.

1m 레코드 후에는 mysql이 약 54587 행을 통과하기 때문에 속도가 느려지고 있습니다.

이 쿼리를 최적화하는 방법이 있습니까?

이것이 내 방아쇠입니다.

CREATE TRIGGER BUY_WARNING_TRIG BEFORE INSERT 
ON tbl_valuation
FOR EACH ROW
BEGIN
    DECLARE warn_counter INT DEFAULT 0;
    DECLARE min_ifr DECIMAL(17,12);
    DECLARE min_lgui DECIMAL(17,12);
    DECLARE stock VARCHAR(100);    

    IF New.ls >= New.macd THEN
        SELECT MIN(v.ifr) 
            FROM (SELECT v.ifr FROM tbl_valuation v WHERE v.stock_id = New.stock_id ORDER BY v.created_at DESC LIMIT 9) as v INTO min_ifr;

        IF New.ifr <= min_ifr THEN
            SELECT MIN(v.lgui) 
                FROM (SELECT v.lgui FROM tbl_valuation v WHERE v.stock_id = New.stock_id ORDER BY v.created_at DESC LIMIT 9) as v INTO min_lgui;

            IF New.lgui <= min_lgui THEN
                SET warn_counter = warn_counter + 1;
            END IF;
        END IF;

        SELECT MIN(v.ifr) FROM (SELECT v.ifr FROM tbl_valuation v WHERE v.stock_id = New.stock_id ORDER BY v.created_at DESC LIMIT 4) as v INTO min_ifr;

        IF New.ifr <= min_ifr THEN
            SELECT MIN(v.lgui) 
                    FROM (SELECT v.lgui FROM tbl_valuation v WHERE v.stock_id = New.stock_id ORDER BY v.created_at DESC LIMIT 4) as v INTO min_lgui;

            IF New.lgui <= min_lgui THEN
                SET warn_counter = warn_counter + 1;
            END IF;
        END IF;

        SELECT MIN(v.ifr) FROM (SELECT v.ifr FROM tbl_valuation v WHERE v.stock_id = New.stock_id ORDER BY v.created_at DESC LIMIT 19) as v INTO min_ifr;

        IF New.ifr <= min_ifr THEN
            SELECT MIN(v.lgui) 
                    FROM (SELECT v.lgui FROM tbl_valuation v WHERE v.stock_id = New.stock_id ORDER BY v.created_at DESC LIMIT 19) as v INTO min_lgui;

            IF New.lgui <= min_lgui THEN
                SET warn_counter = warn_counter + 1;
            END IF;
        END IF;
    END IF;

    IF warn_counter > 0 THEN
        SELECT t.stock FROM tbl_stock t WHERE t.id = New.stock_id INTO stock;
        CASE warn_counter
            WHEN 1 THEN INSERT INTO tbl_warning (created_at, stock, level, rate, `type`) VALUES (NOW(), stock, 'LOW', New.rate, 'BUY');
            WHEN 2 THEN INSERT INTO tbl_warning (created_at, stock, level, rate, `type`) VALUES (NOW(), stock, 'MED', New.rate, 'BUY');
            WHEN 3 THEN INSERT INTO tbl_warning (created_at, stock, level, rate, `type`) VALUES (NOW(), stock, 'HIGH', New.rate, 'BUY');
        END CASE;
    END IF;
END$$