我有这个 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$$
您的查询绝对需要一个tbl_valuation (stock_id, created_at)支持where-condition 和order by. 这将摆脱Using filesort.
为了使它更快一点，您应该包括您在查询中使用的其他两列tbl_valuation (stock_id, created_at, ifr, lgui)，以使其成为覆盖索引。这将节省在表中查找这些值的时间（并将显示为using index）。
由于您基本上执行了 6 次非常相似的查询，因此您的触发器代码本身也可以通过重新组织或使用不同的方法来优化，尽管我不打算这样做。一个快速优化：您可以通过组合查询lgui和将查询数量减少一半ifr：
SELECT min(v.ifr), min(v.lgui) into min_ifr, min_lgui
FROM (SELECT v.ifr, v.lgui
FROM tbl_valuation v WHERE v.stock_id = new.stock_id
ORDER BY v.created_at DESC LIMIT 9) as v
这样做不会花费您任何时间，因此如果您min_lgui在下一步中不需要-value，则不会造成任何伤害，但是如果您确实需要它，则可以节省一个select。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句