le trigger au secours des function-based index (FBI)

1 avril 2009 par arnaud

Constat : les FBI (function-based index) ne sont pas disponibles en MySQL.

Comment faire dans ce cas pour obtenir par exemple l’équivalent de l’index suivant ?

CREATE INDEX idx_str_len ON my_table (length(str));

Une des solutions les plus simples consiste à utiliser un trigger qui assurera la mise à jour d’une colonne supplémentaire que l’on créera dans la table cible.

Partons de la table suivante :

CREATE TABLE `t` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`date` timestamp NOT NULL,
`str` varchar(100) NOT NULL default '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

Le but est de pouvoir obtenir une réponse rapide à la requête suivante :

select sql_no_cache count(*) from t where length(str) between 20 and 70;

Afin de simuler un jeu d’essai similaire à ma table de production, j’alimente ma table de test grâce à une procédure stockée déjà évoquée sur dbnewz.

Je la modifie ici afin d’obtenir une longueur aléatoire pour ma chaîne de caractère « str » :

delimiter //
CREATE PROCEDURE fill_table(nb_rows INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
INSERT INTO t (str) VALUES(repeat('a', round(rand()*100)));
UNTIL i >= nb_rows
END REPEAT;
END;
//
delimiter ;

Afin de valider deux scénarios pour ce billet, j’ai utilisé deux machines, une machine perso et un serveur pro. Deux configurations différentes : la perso n’est absolument pas « tunée », en configuration strictement d’origine (5.1.32), la seconde a des processeurs et de la RAM à revendre.
J’afficherai donc les temps d’exécution des deux machines à titre de comparaison.

Pour renseigner ma table de test à hauteur de 2 millions d’enregistrements, j’appelle ma procédure stockée :

Perso :
mysql> call fill_table(2000000);
Query OK, 1 row affected (1 min 52.97 sec)

Pro :
mysql> call fill_table(2000000);
Query OK, 1 row affected (42.23 sec)

Je souhaite tester ma requête sous InnoDB, je modifie le moteur de la table :

mysql> alter table t engine=’innodb’;
Query OK, 2000000 rows affected (43.57 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> alter table t engine=’innodb’;
Query OK, 2000000 rows affected (13.10 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

J’ai testé pour vous : il est bien plus rapide d’insérer en MyISAM et de faire l’ALTER en InnoDB que d’insérer directement en InnoDB :

Test de chargement avec table en InnoDB d’origine sur la machine perso :
mysql> call fill_table(100000);
Query OK, 1 row affected (1 min 14.15 sec)

=> Déjà 30 secondes de plus (70% plus lent) pour 20x moins de données !

La machine pro (en 5.0.56) est bien plus rapide mais valide elle aussi la stratégie de passer par MyISAM en premier lieu :
Chargement d’une table en InnoDB sur serveur pro :
mysql> call fill_table(2000000);
Query OK, 1 row affected (1 min 11.16 sec)

=> A comparer avec les 42 sec (chargement MyISAM natif) + 13 sec (alter InnoDB) = 55 s. Au moins 30% plus rapide.

On obtient une table contenant des enregistrements du type :

mysql> select * from t limit 5;
+—-+———————+——————-
| id | date                | str
+—-+———————+——————-
|  1 | 2009-03-30 22:18:02 | aaaaaaaa
|  2 | 2009-03-30 22:18:02 | aaaaaaaaaaaaaaaa
|  3 | 2009-03-30 22:18:02 | aaaaaaaaaaaaaaaaaaaaaaa
|  4 | 2009-03-30 22:18:02 | aaaaaaaaaaa
|  5 | 2009-03-30 22:18:02 | aaa
+—-+———————+——————–

Nous voici donc avec un jeu d’essai raisonnable, prêt à en découdre avec cette requête…

mysql> explain select sql_no_cache count(*) from t where length(str) between 20 and 70;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2015427
Extra: Using where
1 row in set (0.00 sec)

… Au temps d’exécution pitoyable. Sur la machine perso, j’obtiens en moyenne :

mysql> select sql_no_cache count(*) from t where length(str) between 20 and 70;
+———-+
| count(*) |
+———-+
|  1019970 |
+———-+
1 row in set (1.83 sec)

Sur le serveur pro :

mysql> select sql_no_cache count(*) from t where length(str) between 20 and 70;
+———-+
| count(*) |
+———-+
|  1020008 |
+———-+
1 row in set (0.82 sec)

L’innodb_buffer_pool_size de la machine perso est complètement sous-dimensionné pour contenir toutes les données de ma table. L’intérêt ici est de tester ce qui se passe quand les données ne sont pas montées en RAM (comportement de MyISAM qui charge les datas dans le cache OS).

Constatons les « dégats » sur la machine perso :

mysql> show variables like ‘innodb_buffer_pool_size’;
+————————-+———+
| Variable_name           | Value   |
+————————-+———+
| innodb_buffer_pool_size | 8 388 608
+————————-+———+

… le jour et la nuit avec :

mysql> show variables like ‘innodb_buffer_pool_size’;
+————————-+————+
| Variable_name           | Value      |
+————————-+————+
| innodb_buffer_pool_size | 3 984 588 800
+————————-+————+

Voyons si un index peut changer la donne ? Plaçons-le sur la colonne « str » :

mysql> create index idx_str on t (str);
Query OK, 2000000 rows affected (1 min 1.95 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> create index idx_str on t (str);
Query OK, 2000000 rows affected (24.95 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

Le nouveau plan d’exécution est le suivant :

mysql> explain select sql_no_cache count(*) from t where length(str) between 20 and 70;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx_str
key_len: 102
ref: NULL
rows: 1957767
Extra: Using where; Using index
1 row in set (0.00 sec)

Nous sommes dans le cas d’un covering index mais celui-ci n’est pas hélas pas d’une grande utilité sur la machine perso :

mysql> select sql_no_cache count(*) from t where length(str) between 20 and 70;
+———-+
| count(*) |
+———-+
|  1019970 |
+———-+
1 row in set (1.98 sec)

On frôle les 2 secondes.

Sur la machine de prod c’est en revanche légèrement plus rapide :

mysql> select sql_no_cache count(*) from t where length(str) between 20 and 70;
+———-+
| count(*) |
+———-+
|  1020008 |
+———-+
1 row in set (0.73 sec)

Stratégie : rajouter une colonne « str_nb » à ma table t et mettre à jour cette colonne selon la longueur de « str ».

Machine Perso :
mysql> ALTER table t ADD str_nb tinyint unsigned, ADD index idx_str_nb(str_nb);
Query OK, 2000000 rows affected (1 min 9.22 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> UPDATE t SET str_nb = length(str);
Query OK, 2000000 rows affected (2 min 3.97 sec)
Rows matched: 2000000  Changed: 2000000  Warnings: 0

Serveur Pro :
mysql> ALTER table t ADD str_nb tinyint unsigned, ADD index idx_str_nb(str_nb);
Query OK, 2000000 rows affected (30.91 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> UPDATE t SET str_nb = length(str);

Query OK, 2000000 rows affected (1 min 22.54 sec)
Rows matched: 2000000  Changed: 2000000  Warnings: 0

mysql> explain select sql_no_cache count(*) from t where str_nb between 20 and 70\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: idx_str_nb
key: idx_str_nb
key_len: 2
ref: NULL
rows: 914077
Extra: Using where; Using index
1 row in set (0.00 sec)

Notre requête modifiée tire parti du nouvel index :

mysql> select sql_no_cache count(*) from t where str_nb between 20 and 70;
+———-+
| count(*) |
+———-+
|  1019970 |
+———-+
1 row in set (0.99 sec)

Sur la machine de prod :

mysql> select sql_no_cache count(*) from t where str_nb between 20 and 70;
+———-+
| count(*) |
+———-+
|  1020008 |
+———-+
1 row in set (0.37 sec)

Résultat de ces tests : tirer parti d’une colonne « précalculée » permet de diviser par deux le temps d’exécution sur les deux machines.

Comment maintenir à jour cette colonne ?

C’est là que les triggers rentrent en jeu, on en crée deux :

Le premier concerne les INSERT

DELIMITER |
CREATE TRIGGER maj_str_nb_ins BEFORE INSERT ON t
FOR EACH ROW BEGIN
SET NEW.str_nb = length(NEW.str);
END;
|
DELIMITER ;

… Et le second les UPDATE :

DELIMITER |
CREATE TRIGGER maj_str_nb_upd BEFORE UPDATE ON t
FOR EACH ROW BEGIN
SET NEW.str_nb = length(NEW.str);
END;
|
DELIMITER ;

Le mot clé « NEW » permet de faire référence à la nouvelle donnée qui est insérée. Dans le cadre d’un UPDATE il existe également le mot clé « OLD » qui permet de manipuler l’ancienne donnée, celle qui va être mise à jour.

Une fois insérés, ces triggers s’occuperont pour nous de mettre à jour automatiquement la colonne « str_nb ». Quelques exemples :

mysql> insert into t (str) values (‘dbnewz’);
Query OK, 1 row affected (0.45 sec)

mysql> select * from t where id = 2000001;
+———+———————+——–+——–+
| id      | date                | str    | str_nb |
+———+———————+——–+——–+
| 2000001 | 2009-04-01 00:35:58 | dbnewz |      6 |
+———+———————+——–+——–+

mysql> update t set date = ’2009-04-01 03:33:33′ where id = 2000001;
Query OK, 1 row affected (0.35 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where id = 2000001;
+———+———————+——–+——–+
| id      | date                | str    | str_nb |
+———+———————+——–+——–+
| 2000001 | 2009-04-01 03:33:33 | dbnewz |      6 |
+———+———————+——–+——–+
1 row in set (0.00 sec)

mysql> update t set str = ‘MySQL’ where id = 2000001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where id = 2000001;
+———+———————+——-+——–+
| id      | date                | str   | str_nb |
+———+———————+——-+——–+
| 2000001 | 2009-04-01 00:38:18 | MySQL |      5 |
+———+———————+——-+——–+
1 row in set (0.00 sec)

A vous de tester si ces triggers peuvent correspondre à un cas que vous cherchez à résoudre, dans ce cas précis, ils fonctionnent.

Merci à Jonathon Coombes avec qui j’ai échangé autour de ce sujet.

Mots-clefs : ,

2 commentaires sur “le trigger au secours des function-based index (FBI)”

  1. Merci, c’est exactement ce que je cherchais :)

  2. [...] ce qui empêche l’utilisation de l’index (rappelez-vous, MySQL ne connaît pas les function-based index) et conduit donc à un parcours complet de la [...]

Laisser une réponse