Convertir et optimiser des requêtes depuis la base de données Oracle vers Cloud SQL pour MySQL

Ce document décrit les différences entre les requêtes de base Oracle® et Cloud SQL pour MySQL, ainsi que la correspondance entre les fonctionnalités Oracle et celles de Cloud SQL pour MySQL. Il décrit également les considérations de performances pour Cloud SQL pour MySQL, ainsi que les méthodes d'analyse et d'optimisation des performances des requêtes sur Google Cloud. Bien que ce document aborde les techniques d'optimisation des procédures et des déclencheurs stockés pour Cloud SQL pour MySQL, il ne décrit pas comment traduire le code PL/SQL en procédures et fonctions stockées MySQL.

Lorsque vous convertissez des requêtes de base de données Oracle vers Cloud SQL pour MySQL, vous devez prendre en compte certaines différences dans le langage SQL. Il existe également plusieurs fonctions intégrées qui sont différentes ou incompatibles entre les deux plates-formes de base de données.

Différences des requêtes de base

Bien qu'Oracle et Cloud SQL pour MySQL soient toutes deux compatibles avec ANSI SQL, il existe plusieurs différences essentielles dans l'interrogation des données, principalement liées à l'utilisation des fonctions système.

Le tableau suivant met en évidence les différences de syntaxe SELECT et FROM pour Oracle et Cloud SQL pour MySQL.

Vues intégrées

Les vues intégrées (également appelées tables dérivées) sont des instructions SELECT situées dans la clause FROM et utilisées en tant que sous-requête. Les vues intégrées peuvent simplifier les requêtes complexes en supprimant les calculs composés ou en éliminant les opérations de jointure, tout en regroupant plusieurs requêtes distinctes en une seule requête simplifiée.

L'exemple suivant présente un exemple de conversion depuis Oracle 11g/12c vers Cloud SQL pour MySQL pour une vue intégrée.

Une vue intégrée dans Oracle 11g/12c :

 SELECT FIRST_NAME,
            DEPARTMENT_ID,
            SALARY,
            DATE_COL
     FROM EMPLOYEES,
          (SELECT SYSDATE AS DATE_COL FROM DUAL);

Une vue de travail dans Cloud SQL pour MySQL 5.7 avec un alias :

SELECT FIRST_NAME,
              DEPARTMENT_ID,
              SALARY,
              DATE_COL
       FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL) AS A1;

Jointures

Les types de jointure d'Oracle sont compatibles avec Cloud SQL pour MySQL, à l'exception de FULL JOIN. Les jointures Cloud SQL pour MySQL sont compatibles avec l'utilisation d'une syntaxe alternative, telle que la clause USING, la clause WHERE au lieu de la clause ON et SUBQUERY dans l'instruction JOIN.

Le tableau suivant présente un exemple de conversion de JOIN.

Bien que Cloud SQL pour MySQL soit compatible avec les fonctions UNION et UNION ALL, il n'est pas compatible avec les fonctions INTERSECT et MINUS d'Oracle :

• UNION associe les ensembles de résultats de deux instructions SELECT après avoir supprimé les enregistrements en double.
• UNION ALL associe les ensembles de résultats de deux instructions SELECT sans supprimer les enregistrements en double.
• INTERSECT renvoie l'intersection de deux instructions SELECT, uniquement si un enregistrement figure dans les ensembles de résultats des deux requêtes.
• MINUS compare deux instructions SELECT ou plus, et ne renvoie que des lignes distinctes de la première requête qui ne sont pas renvoyées par la deuxième requête.

Le tableau suivant présente quelques exemples de conversions Oracle vers Cloud SQL pour MySQL.

Fonctions scalaires et de groupe

Cloud SQL pour MySQL fournit une longue liste de fonctions scalaires (à une seule ligne) et d'agrégation. Certaines fonctions Cloud SQL pour MySQL sont similaires à leurs équivalents Oracle (nom et fonctionnalité identique, ou nom différent et fonctionnalité similaire). Bien que certaines fonctions Cloud SQL pour MySQL puissent avoir des noms identiques à leurs équivalents Oracle, elles peuvent également présenter des fonctionnalités différentes.

Le tableau suivant détaille les fonctions de caractères Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant détaille les fonctions numériques Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant décrit les fonctions datetime Oracle et Cloud SQL pour MySQL qui sont équivalentes par leur nom et leur fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant indique lorsque les fonctions d'encodage et de décodage Oracle et Cloud SQL for MySQL sont équivalentes (même nom et mêmes fonctionnalités) et lorsqu'une conversion est recommandée.

Le tableau suivant détaille les fonctions de conversion Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant détaille les fonctions conditionnelles SELECT Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant détaille les fonctions null Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant détaille les fonctions d'environnement Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant détaille les fonctions d'agrégation (groupe) Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Le tableau suivant détaille les cas où la fonction FETCH d'Oracle et Cloud SQL pour MySQL est équivalente par nom et par fonctionnalité.

Filtrage, opérateurs et sous-requêtes de base

Le filtrage, les fonctions d'opérateur et les sous-requêtes de base sont relativement simples à convertir, avec un effort minimal requis. L'essentiel de cet effort porte sur la conversion des formats de date, car Oracle et Cloud SQL pour MySQL utilisent des formats de date par défaut différents :

• Par défaut, la fonction SYSDATE d'Oracle renvoie le format 01-AUG-19.
• Par défaut, la fonction SYSDATE() de Cloud SQL pour MySQL renvoie le format 2019-08-01 12:04:05.

Pour définir les formats de date et d'heure, utilisez les fonctions MySQL DATE_FORMAT ou STR_TO_DATE.

Le tableau suivant détaille les fonctions de filtrage, d'opérateurs et de sous-requêtes de base Oracle et Cloud SQL pour MySQL qui sont équivalentes par nom et fonctionnalité, et celles pour lesquelles une conversion est recommandée.

Bonnes pratiques en matière de requêtes Cloud SQL pour MySQL

Pour maintenir des niveaux de performances similaires entre Cloud SQL pour MySQL et Oracle, vous devrez peut-être optimiser vos requêtes. Ces optimisations incluent la modification des structures d'index et l'ajustement du schéma de base de données. Cette section fournit quelques consignes pour vous aider à obtenir des performances de requête similaires sur Cloud SQL pour MySQL.

Créer un index en cluster

Lorsque vous utilisez le moteur de stockage InnoDB, il est recommandé de définir une table avec une clé primaire, car cette clé crée un index en cluster sur la table. En plus d'améliorer les performances des requêtes, cette approche vous permet également de créer des index secondaires supplémentaires. Toutefois, il n'est pas souhaitable de créer trop d'index. Les index redondants n'améliorent pas les performances et peuvent ralentir l'exécution du DML. Cette bonne pratique conduit à une deuxième bonne pratique qui consiste à surveiller régulièrement les index redondants et, le cas échéant, les supprimer de la base de données.

Utilisez la requête suivante pour identifier les tables sans clé primaire afin de pouvoir créer ces clés le cas échéant :

mysql> SELECT t.table_schema, t.table_name
       FROM information_schema.tables t LEFT JOIN
       information_schema.statistics s
       ON t.table_schema=s.table_schema AND t.table_name=s.table_name
       AND s.non_unique=0
       WHERE s.table_name IS NULL
       AND t.table_schema NOT IN('sys', 'information_schema', 'mysql',
       'performance_schema')
       AND t.`TABLE_TYPE` <> 'VIEW';

Utilisez la requête suivante pour rechercher des tables sans index afin de pouvoir créer ces index le cas échéant :

mysql> SELECT t.table_schema, t.table_name FROM INFORMATION_SCHEMA.tables t
       WHERE table_name NOT IN
             (SELECT  table_name FROM (
                      SELECT  table_name, index_name
                      FROM information_schema.statistics
                  GROUP BY  table_name, index_name) tab_ind_cols
           GROUP BY table_name)
AND table_schema NOT IN('sys', 'information_schema', 'mysql', 'performance_schema')
AND TABLE_TYPE <> 'VIEW';

Utilisez la requête suivante pour rechercher des index redondants afin de supprimer les redondances :

mysql> SELECT * FROM sys.schema_redundant_indexes;

Ajuster les paramètres de requête

Pour ajuster les performances des requêtes, vous aurez peut-être besoin d'ajuster les paramètres de session. Cloud SQL pour MySQL dispose d'un ensemble d'options que vous pouvez modifier à cette fin, y compris les options suivantes :

• Paramètres liés à InnoDB
• Paramètres SORT
• Paramètres JOIN
• Paramètres de gestion du cache

Surveiller les requêtes

Les requêtes lentes peuvent empêcher le système de répondre ou générer d'autres goulots d'étranglement. Il est donc important de surveiller régulièrement les requêtes.

Il existe plusieurs façons de diagnostiquer les instructions SQL lentes :

• Utilisez le tableau de bord Cloud SQL pour MySQL pour obtenir des informations en temps réel et historiques sur les requêtes lentes.
• Utilisez Cloud Monitoring pour surveiller le journal des requêtes lentes de Cloud SQL pour MySQL.

• Utilisez la vue statement_analysis de Cloud SQL pour MySQL afin d'afficher les statistiques d'exécution d'une instruction SQL :

  mysql> SELECT * FROM sys.statement_analysis;
  

Analyser les requêtes Cloud SQL pour MySQL

L'optimiseur de requêtes de Cloud SQL pour MySQL génère un plan d'exécution pour les instructions SELECT, INSERT, UPDATE et DELETE. Ces plans sont utiles lorsque vous ajustez une requête lente. Les points suivants sont à prendre en compte :

• Les plans d'exécution ne sont pas des objets de base de données qui doivent être migrés, mais plutôt un outil permettant d'analyser les différences de performances entre Oracle et Cloud SQL pour MySQL en exécutant la même instruction sur des ensembles de données identiques.
• Cloud SQL pour MySQL ne prend pas en charge les mêmes syntaxes, fonctionnalités ou sorties de plan d'exécution qu'Oracle.

Voici un exemple de plan illustrant les différences entre un plan d'exécution Oracle et un plan d'exécution Cloud SQL pour MySQL :

SQL> EXPLAIN PLAN FOR
     SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

mysql> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;

+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMPLOYEES | NULL       | const | PRIMARY       | PRIMARY | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Optimiser les procédures stockées et les déclencheurs

Contrairement à Oracle, les procédures et fonctions stockées Cloud SQL pour MySQL sont analysées à chaque exécution. L'utilitaire MySQL BENCHMARK() permet de comparer les performances des procédures et des fonctions stockées. Cet outil prend deux paramètres, à savoir un nombre d'itérations et une expression, et estime la durée d'exécution de l'expression donnée (par exemple, procédure stockée, fonction et instruction SELECT). Le résultat représente le temps d'exécution total approximatif sur toutes les itérations.

L'exemple suivant présente l'utilitaire BENCHMARK() :

-- SELECT Expression Example

mysql> select benchmark(10000000, 'select sysdate()');
+-----------------------------------------+
| benchmark(10000000, 'select sysdate()') |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.12 sec)

-- Result: Run time of 0.12 sec for 1,0000,000 iterations

-- FUNCTION Example
mysql> select benchmark(1000000, func1());
+-----------------------------+
| benchmark(1000000, func1()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (2.54 sec)

-- Result: Run time of 2.54 sec for 1,000,000 iterations

Si vous remarquez une régression des performances lors de la conversion, utilisez la commande MySQL EXPLAIN pour identifier les facteurs pouvant contribuer à la régression. Une solution courante pour les performances lentes consiste à modifier la structure d'un index de table pour l'adapter à l'optimiseur MySQL. Une autre pratique courante consiste à optimiser un code PL/SQL converti en réduisant la récupération inutile de données ou en utilisant des tables temporaires dans le code MySQL procédural.

Étape suivante

• En savoir plus sur les comptes utilisateur MySQL
• Découvrez des architectures de référence, des schémas et des bonnes pratiques concernant Google Cloud. Consultez notre Centre d'architecture cloud.