Convertendo e otimizando consultas do Oracle Database para Cloud SQL para MySQL

Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

Este documento discute as diferenças básicas de consulta entre Oracle® e Cloud SQL para MySQL e como os recursos do Oracle são mapeados para recursos do Cloud SQL para MySQL. Ele também descreve considerações de desempenho do Cloud SQL para MySQL e maneiras de analisar e otimizar o desempenho de consultas emGoogle Cloud. Embora este documento aborde técnicas para otimizar procedimentos armazenados e acionadores do Cloud SQL para MySQL, ele não aborda como traduzir o código PL/SQL em procedimentos e funções armazenados do MySQL.

Ao converter consultas do Oracle Database para Cloud SQL para MySQL, há certas diferenças de dialeto SQL a serem consideradas. Existem também várias funções integradas que são diferentes ou incompatíveis entre as duas plataformas de banco de dados.

Diferenças básicas de consulta

Embora o Oracle e o Cloud SQL para MySQL sejam compatíveis com ANSI SQL, há diversas diferenças fundamentais ao consultar dados, principalmente em relação ao uso de funções do sistema.

A tabela a seguir destaca as diferenças na sintaxe SELECT e FROM para Oracle e Cloud SQL para MySQL.

Visualizações embutidas

As visualizações embutidas (também conhecidas como tabelas derivadas) são instruções SELECT , localizadas na cláusula FROM e usadas como uma subconsulta. As visualizações embutidas podem ajudar a simplificar consultas complexas, removendo cálculos compostos ou eliminando operações de junção, ao mesmo tempo em que condensam diversas consultas separadas em uma única consulta simplificada.

O exemplo a seguir descreve um exemplo de conversão do Oracle 11g/12c para Cloud SQL para MySQL para uma visualização in-line.

Uma visualização embutida no Oracle 11g/12c:

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

Uma visualização funcional no Cloud SQL para MySQL 5.7 com um alias:

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

Junta-se

Os tipos de junção da Oracle são compatíveis com o Cloud SQL para MySQL, exceto FULL JOIN . As junções do Cloud SQL para MySQL são compatíveis com o uso de sintaxe alternativa, como a cláusula USING , a cláusula WHERE em vez da cláusula ON e o SUBQUERY na instrução JOIN .

A tabela a seguir mostra um exemplo de conversão JOIN .

Embora o Cloud SQL para MySQL seja compatível com as funções UNION e UNION ALL , ele não é compatível com as funções INTERSECT e MINUS da Oracle:

• UNION anexa os conjuntos de resultados de duas instruções SELECT após eliminar registros duplicados.
• UNION ALL anexa os conjuntos de resultados de duas instruções SELECT sem eliminar registros duplicados.
• INTERSECT retorna a interseção de duas instruções SELECT , somente se existir um registro nos conjuntos de resultados de ambas as consultas.
• MINUS compara duas ou mais instruções SELECT , retornando apenas linhas distintas da primeira consulta que não são retornadas pela segunda consulta.

A tabela a seguir mostra alguns exemplos de conversão de Oracle para Cloud SQL para MySQL.

Funções escalares e de grupo

O Cloud SQL para MySQL fornece uma lista extensa de funções escalares (linha única) e de agregação. Algumas funções do Cloud SQL para MySQL são semelhantes às funções equivalentes do Oracle (por nome e funcionalidade ou com um nome diferente, mas com funcionalidade semelhante). Embora algumas funções do Cloud SQL para MySQL possam ter nomes idênticos aos de suas contrapartes do Oracle, elas também podem exibir funcionalidades diferentes.

A tabela a seguir descreve onde as funções de caracteres do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções numéricas do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções datetime do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções de codificação e decodificação do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções de conversão do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções SELECT condicionais do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções nulas do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde o ambiente Oracle e Cloud SQL para MySQL e as funções de identificador são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde as funções agregadas (grupo) do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

A tabela a seguir descreve onde a função FETCH do Oracle e Cloud SQL para MySQL é equivalente por nome e funcionalidade.

Filtragem básica, operadores e subconsultas

Filtragem básica, funções de operador e subconsultas são relativamente simples de converter, exigindo esforço nominal. A maior parte do esforço gira em torno da conversão de formatos de data porque o Oracle e o Cloud SQL para MySQL usam diferentes formatos de data padrão:

• A função Oracle SYSDATE retorna este formato por padrão: 01-AUG-19 .
• A função SYSDATE() do Cloud SQL para MySQL retorna este formato por padrão: 2019-08-01 12:04:05 .

Para definir formatos de data e hora, use as funções MySQL DATE_FORMAT ou STR_TO_DATE .

A tabela a seguir descreve onde a filtragem básica, os operadores e as funções de subconsulta do Oracle e do Cloud SQL para MySQL são equivalentes por nome e funcionalidade, e onde uma conversão é recomendada.

Práticas recomendadas para consultas do Cloud SQL para MySQL

Para manter níveis comparáveis ​​de desempenho entre o Cloud SQL para MySQL e o Oracle, talvez seja necessário otimizar suas consultas. Essas otimizações incluem a alteração das estruturas do índice e o ajuste do esquema do banco de dados. Esta seção fornece algumas diretrizes para ajudar você a alcançar um desempenho de consulta comparável no Cloud SQL para MySQL.

Crie um índice clusterizado

Ao usar o mecanismo de armazenamento InnoDB , uma prática recomendada é definir uma tabela com uma chave primária, porque essa chave cria um índice clusterizado nessa tabela. Além de melhorar o desempenho da consulta, essa abordagem também permite criar índices secundários adicionais. No entanto, você deseja evitar a criação de muitos índices. Ter índices redundantes não melhora o desempenho e pode retardar a execução do DML . Essa prática recomendada leva a uma segunda prática recomendada: monitorar regularmente índices redundantes e, se você tiver índices redundantes, eliminá-los do banco de dados.

Use a consulta a seguir para identificar tabelas sem chaves primárias para que você possa criar chaves primárias para elas:

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';

Use a seguinte consulta para encontrar tabelas que não possuem índices para que você possa criar índices para elas:

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';

Use a seguinte consulta para verificar índices redundantes para que você possa remover as redundâncias:

mysql> SELECT * FROM sys.schema_redundant_indexes;

Ajustar parâmetros de consulta

Para ajustar o desempenho da consulta, talvez seja necessário ajustar os parâmetros da sessão. O Cloud SQL para MySQL tem um conjunto de sinalizações que você pode alterar para essa finalidade, incluindo as seguintes sinalizações:

• Parâmetros relacionados ao InnoDB
• Parâmetros SORT
• Parâmetros JOIN
• Parâmetros de manipulação de cache

Monitorar consultas

Consultas lentas podem fazer com que o sistema pare de responder ou levar a outros gargalos, por isso é importante monitorar consultas regularmente.

Existem várias maneiras de diagnosticar instruções SQL de execução lenta:

• Use o painel do Cloud SQL para MySQL para obter insights históricos e em tempo real sobre consultas de execução lenta.
• Use o Cloud Monitoring para monitorar o registro de consultas lentas do Cloud SQL para MySQL.

• Use a visualização statement_analysis do Cloud SQL para MySQL para ver as estatísticas de tempo de execução sobre uma instrução SQL:

  mysql> SELECT * FROM sys.statement_analysis;
  

Analisar consultas do Cloud SQL para MySQL

O otimizador de consulta no Cloud SQL para MySQL gera um plano de execução para instruções SELECT , INSERT , UPDATE e DELETE . Esses planos são úteis quando você ajusta uma consulta de execução lenta. Existem algumas considerações a serem lembradas:

• Os planos de execução não são objetos de banco de dados que precisam ser migrados; em vez disso, eles são uma ferramenta para analisar diferenças de desempenho entre Oracle e Cloud SQL para MySQL executando a mesma instrução em conjuntos de dados idênticos.
• O Cloud SQL para MySQL não é compatível com a mesma sintaxe, funcionalidade ou saída do plano de execução que o Oracle.

Aqui está um exemplo de plano para ilustrar as diferenças entre um plano de execução do Oracle e um plano de execução do Cloud SQL para 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  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Otimize procedimentos armazenados e gatilhos

Ao contrário do Oracle, os procedimentos e funções armazenados do Cloud SQL para MySQL são analisados ​​em cada execução. Uma ferramenta útil para avaliar o desempenho de procedimentos armazenados e funções é o utilitário MySQL BENCHMARK() . Esta ferramenta utiliza dois parâmetros, uma contagem de iterações e uma expressão, e estima o tempo de execução da expressão fornecida (por exemplo, procedimento armazenado, função e instrução SELECT ). A saída representa o tempo de execução total aproximado em todas as iterações.

A seguir está um exemplo para ilustrar o utilitário 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

Se você notar uma regressão de desempenho durante a conversão, use o comando MySQL EXPLAIN para identificar possíveis fatores que contribuem para a regressão. Uma solução comum para desempenho lento é alterar uma estrutura de índice de tabela para acomodar o otimizador MySQL. Outra prática comum é otimizar um código PL/SQL convertido reduzindo a recuperação desnecessária de dados ou usando tabelas temporárias dentro do código MySQL processual.

O que vem a seguir

• Explore mais sobre contas de usuário MySQL .
• Explore arquiteturas de referência, diagramas e práticas recomendadas sobre o Google Cloud. Dê uma olhada em nosso Centro de Arquitetura de Nuvem .