MySQL como Oracle

Ha alguns meses realizei um treinamento de MySQL ministrado pela HTI, que acredito seja a única parceira oficial da Oracle Sun para treinamentos em MySQL (pelo menos era antes da aquisição). O treinamento foi muito bom, por sinal recomendo, teve como instrutor o Alexandre Almeida atual colaborador do MariaDB e mantenedor do MySQL Labs. Durante o treinamento tivemos vários assuntos interessantes abordados mas um ponto em particular me chamou bastante a atenção principalmente pela curiosidade, tratava-se de uma configuração regida por um parâmetro chamado SQL Mode.

Antes de explicar sobre a utilização da configuração em questão vou aproveitar a oportunidade para salientar uma das características do MySQL e talvez de muitos aplicativos open source, a premissa da simplicidade. Uma das primeiras coisas que aprendi sobre o MySQL e uma de tantas que um amigo do mundo open source, Marcelo Leal, me ensinou foi isso. Esse é um importante paradigma que tive que entender para começar a apreciar o MySQL. Muitas vezes você vai se pegar reclamando da ausência de uma funcionalidade, ou surpreso de quão simples é a arquitetura do MySQL e a razão possivelmente venha desse paradigma.

Um desses exemplos de simplicidade é o SQL Mode, você já tentou inserir um valor em uma coluna númerica sendo que esse valor ultrapassa o limite daquele tipo de dado no MySQL?

Vamos demonstrar:

mysql; create table teste_sql_mode (col1 smallint);
Query OK, 0 rows affected (0.08 sec)

mysql; select @@global.sql_mode, @@session.sql_mode;
+-------------------+--------------------+
| @@global.sql_mode | @@session.sql_mode |
+-------------------+--------------------+
|                   |                    |
+-------------------+--------------------+
1 row in set (0.00 sec)

mysql; insert into teste_sql_mode values (9),(99),(999),(9999),(99999),(999999), (9999999);
Query OK, 7 rows affected, 3 warnings (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 3

Perceba que apesar do comando ser executado com sucesso 3 warnings são mostrados (o problema é que dificilmente sua aplicação trata esse tipo de resultado). Quando verificamos quais são os warnings veja o resultado:

mysql; show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col1' at row 5 |
| Warning | 1264 | Out of range value for column 'col1' at row 6 |
| Warning | 1264 | Out of range value for column 'col1' at row 7 |
+---------+------+-----------------------------------------------+
3 rows in set (0.00 sec)

Lendo essa saída você imaginaria o que o MySQL fez com as três linhas inseridas que extrapolaram o limite do datatype?

mysql; select * from teste_sql_mode;
+-------+
| col1  |
+-------+
|     9 |
|    99 |
|   999 |
|  9999 |
| 32767 |
| 32767 |
| 32767 |
+-------+
7 rows in set (0.00 sec)

Assustador? Não, nem tanto. Como eu disse devido ao pequeno custo de performance para que o parser tenha que tratar esse e inúmeros outros casos o MySQL (por padrão) opta por esse comportamento de aceitar a maior quantidade de informações possíveis, o que pode ser útil em inúmeros casos. Assustador seria se esse comportamento fosse o único possível, mas para alterar esse comportamento para um mais tradicional é muito simples:

mysql; set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql; select @@global.sql_mode, @@session.sql_mode;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode | @@session.sql_mode                                                                                                            |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
|                   | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Agora vejam o que acontece se repetimos o teste anterior:

mysql; truncate table teste_sql_mode;
Query OK, 0 rows affected (0.00 sec)
mysql; insert into teste_sql_mode values (9),(99),(999),(9999),(99999),(999999), (9999999);
ERROR 1264 (22003): Out of range value for column 'col1' at row 5
mysql; select * from teste_sql_mode;
+------+
| col1 |
+------+
|    9 |
|   99 |
|  999 |
| 9999 |
+------+
4 rows in set (0.00 sec)

Como vocês notaram na tentativa de inserir a quinta linha, por estar fora dos limites do datatype um erro é retornado e somente as 4 primeiras linhas são inseriras.

Esse tipo de comportamento também ocorre em vários outros momentos e na maioria das vezes passa totalmente desapercebido tanto para um usuário utilizando o cliente mysql como para uma aplicação manipulando dados no banco. Acredito que se você parar para analisar o perfil de aplicativos de utilizam como solução de banco de dados (ou de um mero repositório de informações) o MySQL vai chegar a conclusão que esse comportamento é satisfatório para a maioria dos casos, portanto é compreensivel entender o porque do parser de comandos do MySQL ter esse comportamento.

O SQL Mode representa um conjunto de regras que refina como o parser de comandos do MySQL irá tratar os comandos submetidos para ele, para ser mais claro segue abaixo uma listagem das opções de itens que podem ser configurados por essa parametrização:

• ALLOW_INVALID_DATES
• ANSI_QUOTES
• ERROR_FOR_DIVISION_BY_ZERO
• HIGH_NOT_PRECEDENCE
• IGNORE_SPACE
• NO_AUTO_CREATE_USER
• NO_AUTO_VALUE_ON_ZERO
• NO_BACKSLASH_ESCAPE
• NO_DIR_IN_CREATE
• NO_ENGINE_SUBSTITUTION
• NO_FIELD_OPTIONS
• NO_KEY_OPTIONS
• NO_TABLE_OPTIONS
• NO_UNSIGNED_SUBSTRATCION
• NO_ZERO_DATE
• NO_ZERO_IN_DATE
• ONLY_FULL_GROUP_BY
• PIPE_AS_CONCAT
• REAL_AS_FLOAT
• STRICT_ALL_TABLES
• STRICT_TRANS_TABLE

Abaixo segue outras opções que representam grupos de opções:

• ANSI
• DB2
• MAXDB
• MSSSQL
• MYSQL323
• MYSQL40
• ORACLE
• POSTGRESQL
• TRADITIONAL

Um detalhamento sobre cada uma das opções pode ser obtido na documentação do produto.

Com essa explicação e essa listagem agora podemos falar sobre como o MySQL pode trabalhar mais complacente com o Oracle (e com as principais outras tecnologias de banco de dados). Basta você configurar o SQL Mode do seu MySQL como Oracle, ao fazer isso as seguintes parametrizações são habilitadas:

mysql; set session sql_mode='oracle';
Query OK, 0 rows affected (0.00 sec)


mysql; select @@session.sql_mode;
+----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                              |
+----------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Logicamente as diferenças entre MySQL e Oracle não são somente essas, mas com certeza se você pensa em migrar sua aplicação entre essas duas tecnologias de banco de dados isso pode lhe ajudar bastante a diminuir o tempo gasto adaptando códigos, ou ainda pior, ajudando você a evitar que um mesmo código se comporte de maneira diferente em diferentes bancos (isso acontece com grande frequência, principalmente quando falamos de controle de transações e modo de isolamento).

Um último detalhe importante é o fato dessa variável ter dois escopos distintos, ela pode ser configurada globalmente tendo assim seu valor válido para todas as sessões ou pode ser configurada somente para uma sessão específica. Essa última opção aliada com o fato dessa parametrização ser dinâmica ajuda em muito a testar a viabilidade de implementação da mesma, lembrando que quando quiser tornar essa modificação definitiva você deve alterar seu arquivo de opções my.cnf (caso você esteja em ambientes windows esse arquivo é normalmente nomeado como my.ini).

Algumas Diferenças entre o Oracle e o MySQL

Nas últimas semanas venho trabalhando com um banco de dados MySQL que possui algumas tabelas com um volume considerável de dados para a realidade do MySQL, na minha opinião.

São três tabelas que juntas hoje correspondem a aproximadamente 50GB de dados (somando a elas seus respectivos índices) e são acessadas de uma única vez a partir de uma junção. Além disso essa junção é feita a partir das chaves primárias das tabelas e a tabela principal da consulta também é filtrada por um índice, ou seja, podemos assumir quepelo menos alguns cuidados foram tomados na construção da consulta.

Tudo corria bem nesse ambiente até que a quantidade de linhas dessas tabelas chegou na ordem de dezenas e centenas de milhões e foi ai que após alguns ajustes de parâmetros de memória para o storage engine em questão (MyISAM, key_buffer_cache, net_buffer_length, max_allowed_packet, myisam_use_mmap, etc.), muitos explains e muitos show profiles comecei a notar que diferentemente do que eu estava acostumado com o modo do Oracle trabalhar (hash joins, full table scans, etc. para esse perfil de consultas) o MySQL optava pelo velho "nested loop", mas por que?

Consultas e mais consultas em foruns me mostravam que existia um certo "dito popular" que esse seria o limite do MySQL, apesar de ficar claro para mim que não passava de um mito, mais um deles alias, serviu de constatação que realmente o MySQL era mais "recomendado" para o perfil de transações de aplicações web, ou seja, curtas e rápidas.

Além dessa última constatação usei esse fato para motivar um teste comparativo de performance entre os SGBDs que estou mais acostumado a trabalhar, Oracle e MySQL. A idéia foi bem simples, vamos fazer o MySQL imitar o Oracle e o Oracle imitar o MySQL e o resultado mais fatídico ainda: Uma lástima!

Inicialmente apostei que o Oracle trabalharia com Full Table Scans e realizaria o Join utilizando o algoritmo de Hash, com base nessa aposta e a utilização de hints tentei forçar esse comportamento no MySQL resultado: O MySQL não possui algoritmo de Join baseado em Hash na tentativa de forçar outros algoritmos que trabalhassem melhor com o acesso Full da tabela não obtive nenhum resultado satisfatório, na verdade os resultados foram bem piores do que otimizador tinha obtido com seu plano de acesso original.

Chegou a hora então de importarmos os dados no Oracle e ver se a aposta foi certa. Utilizando alguns recursos triviais do MySQL como o Storage Engine CSV e o SQL*Loader do Oracle rapidamente consegui importar os dados do MySQL/MyISAM para o Oracle.

Inicialmente notei que cerca de 25% a mais de espaço foi necessário para comportar a mesma quantidade de dados (ou seja, perspectiva de mais I/O e de mais uso de memória). Uma vez carregado gerei o plano de acesso da consulta e pela primeira vez não obtive surpresa, Full Table Scans e Hash Join, resultado melhor performance que o MySQL, porém nada muito significativo em um primeiro momento, ou seja, não justificava o custo da migração. Os testes com o Oracle utilizando os algoritmos propostos pelo MySQL também não foram interessantes em termos de resultado.

Resultados a parte e considerando que não tive muito a preocupação de isolar as variáveis nesse "benchmark" (hardwares diferentes e o fato de posteriormente nos testes com a utilização de particionamento consegui equilibrar bastante essa diferença de performance) o principal aprendizado que ficou foi o seguinte: Cada SGBD conhece bem seus limites, seja em relação a algoritmos ou em relação a adaptatividade ao hardware disponível e tentar fazer com que um SGBD se comporte como se estivesse influenciado pelo otimizador de outro SGBD não é certeza de sucesso, mesmo que no outro SGBD os resultados sejam tentadores.

Cada SGBD possui seu perfil de aplicação que se comporta melhor com eles, cada um deles também possui um custo (ou nenhum custo) de licenciamento ou suporte, e cada um deles irá exigir mais ou menos trabalho de um DBA para se adaptar as necessidades das aplicações, mas em termos de performance de consultas individuais, seja envolvendo grande volume de dados ou poucas linhas, ainda não encontrei nenhuma bala de prata!