+- JeuWeb - Crée ton jeu par navigateur (https://jeuweb.org)
+-- Forum : Discussions, Aide, Ressources... (https://jeuweb.org/forumdisplay.php?fid=38)
+--- Forum : Programmation, infrastructure (https://jeuweb.org/forumdisplay.php?fid=51)
+--- Sujet : Performances des Triggers (MySQL 5.7) (/showthread.php?tid=8008)
Performances des Triggers (MySQL 5.7) - Xenos - 14-08-2019
Salutations,
je ne sais plus qui l'avait demandé, mais je suis tombé sur un script de test de performances des triggers que j'avais fait, à l'époque où quelqu'un (moi-même? non, je crois que c'était quelqu'un de normal!) m'avait posé la question: "mais les triggers alors, c'est lent ou pas?"
Voici donc le script:
# noinspection SqlUserVariableUsageForFile
DROP TRIGGER IF EXISTS perf_trig_before_update;
DROP TRIGGER IF EXISTS perf_trig_after_update;
DROP TABLE IF EXISTS perf_trig;
DROP TABLE IF EXISTS perf_trig_other;
DROP TABLE IF EXISTS perf_trig_result;
DROP TRIGGER IF EXISTS perf_trig_before_insert;
DROP TRIGGER IF EXISTS perf_trig_after_insert;
DROP TRIGGER IF EXISTS perf_trig_before_delete;
DROP TRIGGER IF EXISTS perf_trig_after_delete;
CREATE TABLE `perf_trig_result` (
`act` VARCHAR(80) NOT NULL,
`time` DOUBLE NOT NULL
)
ENGINE = InnoDB
;
CREATE TABLE `perf_trig` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`edits` INT(10) UNSIGNED NOT NULL,
`value` VARCHAR(80) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
;
CREATE TABLE `perf_trig_other` (
`id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
;
INSERT INTO perf_trig (id, edits) VALUES (1, 0), (2, 0), (3, 0), (4, 0);
INSERT INTO
perf_trig (id, edits)
(
SELECT p1.id + p2.id * 4, 0
FROM perf_trig AS p1
INNER JOIN perf_trig AS p2 ON TRUE);
INSERT INTO
perf_trig (id, edits)
(
SELECT p1.id + p2.id * 20, 0
FROM perf_trig AS p1
INNER JOIN perf_trig AS p2 ON TRUE);
INSERT INTO
perf_trig (id, edits)
(
SELECT p1.id + p2.id * 420, 0
FROM perf_trig AS p1
INNER JOIN perf_trig AS p2 ON TRUE);
INSERT INTO perf_trig_other (id) (SELECT id FROM perf_trig);
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITHOUT TRIGGER' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITHOUT TRIGGER (BIS)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DELIMITER $$
CREATE TRIGGER `perf_trig_before_update`
BEFORE UPDATE
ON `perf_trig`
FOR EACH ROW
BEGIN
SET new.edits = old.edits + 1;
END$$
DELIMITER ;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGER (SET)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DELIMITER $$
DROP TRIGGER perf_trig_before_update;
CREATE TRIGGER `perf_trig_before_update`
BEFORE UPDATE
ON `perf_trig`
FOR EACH ROW
BEGIN
IF (new.value IS NULL) THEN
SET new.edits = old.edits + 1;
END IF;
END$$
DELIMITER ;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGER (IF...SET)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DELIMITER $$
DROP TRIGGER perf_trig_before_update;
CREATE TRIGGER `perf_trig_before_update`
BEFORE UPDATE
ON `perf_trig`
FOR EACH ROW
BEGIN
IF (EXISTS(SELECT 1 FROM perf_trig_other WHERE id = new.id)) THEN
SET new.edits = old.edits + 1;
END IF;
END$$
DELIMITER ;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGER (IF EXISTS ... SET) x 1 (BEFORE)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DELIMITER $$
CREATE TRIGGER `perf_trig_after_update`
AFTER UPDATE
ON `perf_trig`
FOR EACH ROW
BEGIN
IF (EXISTS(SELECT 1 FROM perf_trig_other WHERE id = new.id)) THEN
SET @x = 1;
END IF;
END$$
DELIMITER ;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGER (IF EXISTS ... SET) x 2 (BEFORE + AFTER)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DROP TRIGGER `perf_trig_before_update`;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGER (IF EXISTS ... SET) x 1 (AFTER)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DELIMITER $$
DROP TRIGGER perf_trig_after_update$$
CREATE TRIGGER `perf_trig_after_update`
AFTER UPDATE
ON `perf_trig`
FOR EACH ROW
BEGIN
END$$
DELIMITER ;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGER (EMPTY)' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
DELIMITER $$
DROP TRIGGER perf_trig_after_update$$
CREATE TRIGGER `perf_trig_before_insert`
BEFORE INSERT
ON `perf_trig`
FOR EACH ROW
BEGIN
END$$
CREATE TRIGGER `perf_trig_after_insert`
AFTER INSERT
ON `perf_trig`
FOR EACH ROW
BEGIN
END$$
CREATE TRIGGER `perf_trig_before_delete`
BEFORE DELETE
ON `perf_trig`
FOR EACH ROW
BEGIN
END$$
CREATE TRIGGER `perf_trig_after_delete`
AFTER DELETE
ON `perf_trig`
FOR EACH ROW
BEGIN
END$$
DELIMITER ;
SET @microtime := UNIX_TIMESTAMP(NOW(6));
UPDATE perf_trig SET value = 'WITH TRIGGERS (EMPTY) x 4' WHERE TRUE;
INSERT INTO perf_trig_result (act, time)
VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);
SELECT * FROM perf_trig_result;
Et en voici les résultats:
Code :
act time
WITHOUT TRIGGER 2.162516
WITHOUT TRIGGER (BIS) 1.412786
WITH TRIGGER (SET) 2.449268
WITH TRIGGER (IF...SET) 2.615878
WITH TRIGGER (IF EXISTS ... SET) x 1 (BEFORE) 7.850129
WITH TRIGGER (IF EXISTS ... SET) x 2 (BEFORE + AFTER) 12.180796
WITH TRIGGER (IF EXISTS ... SET) x 1 (AFTER) 9.335754
WITH TRIGGER (EMPTY) 2.583497
WITH TRIGGERS (EMPTY) x 4 1.65202Que peut-on en dire?
- La présence d'un TRIGGER en lui-même (vide) n'influe pas les performances des requêtes
- Un trigger qui n'exécute pas de query SQL accédant aux tables (ie: un trigger type SET new.val = GREATEST(minval, old.val) A un léger impact, mais peu significatif (en gros, + ~20% de charge apparemment)
- Un trigger nécessitant un accès à une autre table va fatalement faire grimper le temps d'exécution (x3) mais c'est à mettre en comparaison avec le temps requis pour lire ces lignes (ie: faire le SELECT en dehors du TRIGGER revient grosso modo au même)
Si vous pouvez faire vous-même le test, je serai ravi d'avoir d'autres environnements pour comparer, mais si ce test (simplisite) est représentatif, alors les TRIGGER n'ont pas un surcoût collossal en eux-mêmes. Le surcout viendra surtout de l'action que vous voulez faire (IF/SET/SELECT) et non de la façon de le faire (TRIGGER, query à part, etc).
En revanche, c'est clair que cette remarque ne sera valide *que* ligne par ligne: entre faire un TRIGGER et faire un traitement de lot (SELECT de toutes les lignes, calcul sur toutes ces lignes, UPDATE de toutes les lignes résultats), le traitement de lot sera clairement plus rapide (d'où ma préférence pour les PROCEDURE mais c'est un autre sujet).
[PS: je voudrai surtout me concentrer sur l'impact de performance des triggers dans ce topic, si vous avez des avis sur l'impact en terme de maintenabilité, de réplication, de préférences/opinion personnelle, je vous prierai d'en faire un autre topic. En revanche, si vous avec des remarques, résultats, suggestions sur les perf' temps/mémoire/lock/whatever, elles sont les bienvenues
]