Como converter e otimizar consultas do Oracle Database para o Cloud SQL para MySQL

Neste documento, discutimos as diferenças básicas de consulta entre o Oracle® e o Cloud SQL para MySQL e como os recursos no Oracle são mapeados para os recursos no Cloud SQL para MySQL. Também descrevemos considerações de desempenho do Cloud SQL para MySQL e maneiras de analisar e otimizar o desempenho da consulta no Google Cloud. Neste documento, abordamos técnicas para otimizar procedimentos e acionadores armazenados para o Cloud SQL para MySQL, mas não abordamos como traduzir o código PL/SQL para procedimentos e funções armazenados do MySQL.

Ao converter consultas do banco de dados Oracle para o Cloud SQL para MySQL, há algumas diferenças de dialeto SQL a serem consideradas. Há 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

Ainda que o Oracle e o Cloud SQL para MySQL sejam compatíveis com ANSI SQL, há várias 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 in-line

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

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

Uma visualização in-line no Oracle 11g/12c:

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

Uma visualização de trabalho 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;

Mesclagens

Os tipos mesclagens da Oracle são compatíveis com o Cloud SQL para MySQL, exceto o FULL JOIN. As mesclagens 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 ON e a SUBQUERY na instrução JOIN.

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

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

• UNION anexa os conjuntos de resultados de duas instruções SELECT depois de 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 houver 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 do Oracle para o Cloud SQL para MySQL.

Funções escalares e de grupo

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

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 as funções de identificador e ambiente 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 agregação (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 do Cloud SQL para MySQL é equivalente por nome e funcionalidade.

Filtragem básica, operadores e subconsultas

A filtragem básica, as funções do operador e as subconsultas são relativamente simples de converter, com esforço mínimo necessário. A maior parte do esforço envolve a 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 SYSDATE da Oracle 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 DATE_FORMAT ou STR_TO_DATE do MySQL.

Na tabela a seguir, descrevemos onde as funções básicas de filtragem, operadores e 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 de desempenho comparáveis entre o Cloud SQL para MySQL e o Oracle, talvez seja necessário otimizar suas consultas. Essas otimizações incluem alterar as estruturas de índice e ajustar o esquema do banco de dados. Nesta seção, fornecemos algumas diretrizes para ajudar você a alcançar um desempenho de consulta comparável no Cloud SQL para MySQL.

Criar um índice em cluster

Ao usar o mecanismo de armazenamento InnoDB (em inglês), uma prática recomendada é definir uma tabela com uma chave primária, porque ela cria um índice em cluster nessa tabela. Além de melhorar o desempenho da consulta, essa abordagem também permite criar índices secundários adicionais. No entanto, você quer evitar a criação de muitos índices. Ter índices redundantes não melhora o desempenho e pode atrasar a execução do DML. Essa prática recomendada leva a uma segunda prática recomendada: monitore regularmente os índices redundantes e, se houver algum, remova-os do banco de dados.

Use a consulta a seguir para identificar tabelas sem chaves primárias para 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 consulta a seguir para encontrar tabelas que não têm í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 consulta a seguir para verificar índices redundantes e remover os redundantes:

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 flags que podem ser alteradas para essa finalidade, incluindo as seguintes:

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

Monitorar consultas

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

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

• Use o painel do Cloud SQL para MySQL para conferir insights históricos e em tempo real sobre consultas de execução lenta.
• Use o Cloud Monitoring para monitorar o registro de consulta lenta 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 consultas no Cloud SQL para MySQL gera um plano de execução para as instruções SELECT, INSERT, UPDATE e DELETE. Esses planos são úteis quando você ajusta uma consulta de execução lenta. Considere as seguintes informações:

• 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 as diferenças de desempenho entre o Oracle e o 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 do Oracle.

Veja um exemplo de plano para ilustrar as diferenças entre um plano de execução da 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  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Otimizar gatilhos e procedimentos armazenados

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 comparar o procedimento armazenado e o desempenho da função é o utilitário BENCHMARK() do MySQL. Essa ferramenta usa dois parâmetros, uma contagem de iteração e uma expressão, e estima o tempo de execução da expressão especificada (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.

Veja a seguir 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 EXPLAIN do MySQL 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 do MySQL. Outra prática comum é otimizar um código PL/SQL convertido reduzindo a recuperação de dados desnecessária ou usando tabelas temporárias no código processual do MySQL.

A seguir

• Descubra mais sobre as contas de usuário do MySQL.
• Confira arquiteturas de referência, diagramas, tutoriais e práticas recomendadas do Google Cloud. Confira o Centro de arquitetura do Cloud.