MySQL, comme Oracle, permet de visualiser les plans des requêtes et offre, à travers des hints, des moyens pour les influencer. Passer d’Oracle à MySQL est donc ultra-simple ! En théorie d’accord mais en pratique ?
Cet article fait un petit parallèle entre Oracle et MySQL s’agissant de ces 2 outils que sont la commande explain et les hints…
Schéma d’exemple
Pour les besoins de notre test, nous allons créer un schéma d’exemple dans MySQL et dans Oracle ; Pour cela, on créé un table nommée X contiendra 3 colonnes col1, col2 et col3 ; nous la remplirons de 10000 lignes à l’aide du script load.sql généré ci-dessous :
for i in {1..10000}; do
echo "insert into X values ($i, 1, 'XXXXXXXXXXXXXXXXXXX');"
>> load.sql;
done
Voici la commande pour créer notre table, la charger et collecter les statiques sous Oracle :
create table X (
col1 number primary key,
col2 number not null,
col3 varchar2(20));
@load
commit;
create index xidx on x(col2);
exec dbms_stats.gather_table_stats(user,'X');
La syntaxe équivalence sous MySQL:
create table X (
col1 int primary key,
col2 int not null,
col3 varchar(20)) engine INNODB;
source load.sql
create index XIDX on X(col2);
analyze table X;
Lire un plan
Pour obtenir un plan il suffit d’utiliser la commande explain dans les 2 cas. Avec Oracle, vous utiliserez probablement également le package dbms_xplan comme ci-dessous :
explain plan for
select count(col3) from X where col2=2;
select * from table(dbms_xplan.display(format=>'basic +cost +rows'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
Plan hash value: 910516349
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0) |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| X | 1 | 2 (0) |
| 3 | INDEX RANGE SCAN | XIDX | 1 | 1 (0) |
------------------------------------------------------------------
Dans le cas de MySQL, utilisez explain tout simplement :
mysql> explain extended
select count(col3) from X where col2=2;
+----+-------------+-------+------+---------------+------+---------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+-------+------+
| 1 | SIMPLE | X | ref | XIDX | XIDX | 4 | const | 1 |
+----+-------------+-------+------+---------------+------+---------+-------+------+
1 row in set, 1 warning (0.00 sec)
mysql> show status like '%cost%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.199000 |
+-----------------+----------+
1 row in set (0.00 sec)
Si les commandes sont similaires le résultat est très différent. Ainsi dans le cas de MySQL, regardez comment lire un plan d’exécution dans la documentation, vous voyez que l’accès à la table X est effectué via l’index XIDX (colonne « key ») à partir d’une constante. Contrairement à Oracle, vous ne voyez pas le détail de chaque opération (qui dépend du moteur) ni le coût pour chacune de ces étapes. MySQL ne vous donne simplement que le coût global.
Cette complexité est due au fait que le calcul d’un plan d’exécution est en fait issue de MySQL mais également du moteur de stockage (InnoDB dans mon cas). Par conséquent le détail de chaque opération dépendant des algorithmes de ce moteur, MySQL n’est pas (encore ?) capable de l’afficher directement. De la même manière, le détail des calculs n’est pas accessible pour les adeptes de la 10053.
MySQL loin d’être bête !
Cela étant, ne sous-estimez pas non plus les capacités de MySQL ; par exemple en version 5.5.10, la plugin InnoDB arrive bien à voir quand un index n’est pas pertinent :
mysql> explain extended
-> select count(col3) from X where col2=1;
+----+-------------+-------+------+---------------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | rows | Extra |
+----+-------------+-------+------+---------------+------+-------+-------------+
| 1 | SIMPLE | X | ALL | XIDX | NULL | 10241 | Using where |
+----+-------------+-------+------+---------------+------+-------+-------------+
1 row in set, 1 warning (0.00 sec)
Le résultat peut être différent selon les versions et les paramètres. On voit bien ici que, dans mon cas, si l’index XIDX peut-être utilisé, il ne l’est pas ; pour cela InnoDB n’utilise pas des histogrammes de distribution mais un algorithme correspondant plutôt à du dynamique sampling comme c’est expliqué dans cette section de la documentation. C’est loin d’être riducule !
Utiliser des hints
Continuons l’exemple précédent ; avec MySQL, comme pour Oracle, il est possible d’influer sur le plan d’une requête avec des hints. La syntaxe de ces hints est directement appliquée sur les tables de l’ordre SQL et non pas sous la forme d’un commentaire spécial comme pour Oracle ; voici un exemple d’utilisation du hint force index; le premier exemple n’utilise pas le hint alors que le second si :
mysql> explain extended select count(col3) from X where col2=1;
+----+-------------+-------+------+---------------+------+---------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+-------+
| 1 | SIMPLE | X | ALL | XIDX | NULL | NULL | NULL | 10241 |
+----+-------------+-------+------+---------------+------+---------+------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show status like '%cost%';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2081.199000 |
+-----------------+-------------+
1 row in set (0.00 sec)
explain extended select count(col3) from X force index (XIDX) where col2=1;
+----+-------------+-------+------+---------------+------+---------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+-------+------+
| 1 | SIMPLE | X | ref | XIDX | XIDX | 4 | const | 9999 |
+----+-------------+-------+------+---------------+------+---------+-------+------+
1 row in set, 1 warning (0.00 sec)
mysql> show status like '%cost%';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2098.799000 |
+-----------------+-------------+
1 row in set (0.00 sec)
Du fait du caractère figé de la syntaxe du hint dans MySQL, peut faire échouer la requête :
mysql> drop index XIDX on X;
mysql> select count(col3) from X force index (XIDX) where col2=1;
ER ROR 1176 (42000): Key 'XIDX' doesn't exist in table 'X'
Dans Oracle, le hint n’est simplement pas appliqué dans ce type de cas :
explain plan for
select /*+ index(X XIDX) */ count(col3)
from X where col2=1;
select * from table(dbms_xplan.display(format=>'basic +cost +rows'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
Plan hash value: 910516349
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| X | 10000 | 65 (0)|
| 3 | INDEX RANGE SCAN | XIDX | 10000 | 20 (0)|
------------------------------------------------------------------
drop index XIDX;
explain plan for
select /*+ index(X XIDX) */ count(col3)
from X where col2=1;
select * from table(dbms_xplan.display(format=>'basic +cost +rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------
Plan hash value: 989401810
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| X | 10000 | 14 (0)|
--------------------------------------------------------
Mais pourquoi ça s’appelle un hint alors ?
Si la syntaxe et l’existence des structures liées au hints sont vérifiées, le hint reste pourtant un hint, c’est à dire une indication qui doit être suivi si c’est possible. Ainsi si votre moteur ne peut pas suivre l’indication, elle sera malgré tout ignorée sans que pour autant cela ne déclenche une erreur. Je ne pouvais quand même pas manquer l’opportunité de montrer un exemple où Oracle est capable de prendre un plan que l’on force :
create index xidx on x(col2);
explain plan for
select /*+ index(X XIDX) */ count(col3)
from X
where col2 in (select max(col1) from X);
select * from table(dbms_xplan.display(format=>'basic +cost +rows'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1286095740
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 (0) |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID | X | 10000 | 65 (0) |
| 3 | INDEX RANGE SCAN | XIDX | 10000 | 20 (0) |
| 4 | SORT AGGREGATE | | 1 | |
| 5 | INDEX FULL SCAN (MIN/MAX)| SYS_C0011832 | 1 | 2 (0) |
---------------------------------------------------------------------------
Et que MySQL 5.5.10 sans patch et malgré le hint n’arrive pas à reproduire :
mysql> explain select count(col3) from X force index (XIDX) where col2 in (select max(col1) from X);
+----+--------------------+-------+------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | rows |
+----+--------------------+-------+------+---------------+------+-------+
| 1 | PRIMARY | X | ALL | NULL | NULL | 10241 |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL |
+----+--------------------+-------+------+---------------+------+-------+
2 rows in set (0.00 sec)
Evidemment tout ça est très général. Les algorithmes, le placement et les structures des données sont très différents. Le jeu des parallèles est donc très limité et s’arrête assez vite. Disons simplement que, si vous avez diagnostiqué un problèmes lié à l’exécution d’une requête avec MySQL ou Oracle, vous avez des outils similaires à votre portée, quoique !
