O MySQL tornou-se o SGBD (Sistema de gestão de base de dados) open source mais popular da actualidade, muito por culpa da sua boa performance, fiabilidade e facilidade de utilização. Actualmente na versão 5.1 (ultima versão estável), o MySQL é usado por todo o mundo, seja em websites, sistemas críticos, ou pacotes de software. É também utilizado em grandes empresas como a Yahoo!, Google, Nokia e até YouTube.
A sua portabilidade também é um factor de sucesso, pois funciona em mais de 20 plataformas incluindo Linux, Windows e OS X, oferecendo uma flexibilidade bastante importante ao programador. Também devido ao enorme número de web hostings que o adoptaram, o MySQL tornou-se um dos SGBDs favoritos dos programadores web que pretendem construir um website, procurando acima de tudo rapidez e performance, onde o downtime e outros problemas são intoleráveis, podendo levar a prejuízos consideráveis.
É importante referir que este artigo é orientado para programadores que já têm alguma experiência com o MySQL e pretendem ficar a conhecer a arquitectura de modo a poderem realizar queries com um custo computacional mais reduzido e consequentemente mais rápidas. O objectivo deste artigo passa por perceber o que se deve ou não fazer quando escrevemos uma query, para podermos avaliar o que é eficiente ou não de modo a podermos explorar as vantagens e evitando as fraquezas deste SGDB.
Arquitectura MySQL
A arquitectura do MySQL é um pouco diferente dos restantes SGBDs, possuindo particularidades como os motores de armazenamento (storage engines). Estes motores podem ser utilizados em runtime e é possível para tabelas específicas definir como a informação é armazenada e qual a performance, além de outras características que necessitamos. Torna-se portanto importante perceber o que são e quais as razões que nos levam a optar por cada um dos motores.
Arquitectura Lógica
Como o objectivo é esclarecer e não confundir, o diagrama a seguir apresentado representa o essencial da arquitectura do MySQL. A Figura 1 é a imagem virtual que necessitamos de possuir para obter uma ideia da arquitectura lógica do MySQL.
O nível mais alto do diagrama, que a título puramente indicativo vou chamar de nível 1, contém serviços que não são únicos ao MySQL, e que são baseados em cliente/servidor, onde por exemplo, numa linguagem de programação é feita a ligação com o SGBD e consequentemente a necessidade de autenticação.
O segundo nível é onde se situam as características e os pilares centrais do MySQL, incluindo o código para query parsing, análise, optimização, caching e funções (date, math, encryption, por exemplo).
O terceiro nível contém os motores de armazenamento, que são responsáveis por armazenar e devolver toda a informação guardada no MySQL, possuindo cada um as suas vantagens e desvantagens.
O servidor de base de dados comunica com os motores através de uma API chamada, storage engine API, que esconde as diferenças dos motores de pesquisa e torna-os claramente transparentes para os níveis superiores do MySQL. A API contém algumas funções de baixo nível para processar operações como begin transaction ou fetch the row that has primary key. Os vários motores não comunicam entre si, simplesmente respondem a pedidos do servidor.
Optimização e execução de queries
O MySQL analisa as queries para criar uma estrutura interna (em forma de árvore), e então aplica uma variedade de optimizações que incluem a reescrita da query, determinando a ordem de como as tabelas vão ser lidas assim como escolhendo que índices vai utilizar.
Na fase de optimização não interessa qual foi o motor de base de dados que determinada tabela usa, mas o motor da base de dados afecta a forma como o servidor optimiza as queries.
O optimizador de queries pergunta ao motor de base de dados quais as suas capacidades, custo de determinadas operações, e estatísticas sobre a informação nas tabelas, de modo a poder maximizar a eficiência da optimização. Por exemplo, alguns motores de base de dados suportam tipos de índices que podem ser prestáveis para certo tipo de queries.
Antes mesmo de analisar a query, o servidor consulta o cache da query, no qual pode armazenar apenas as cláusulas SELECT, junto com os seus resultados. Se alguém executar uma query que já se encontra na cache, então o servidor não necessita de analisar, optimizar ou executar a query, pode simplesmente devolver o resultado previamente armazenado.
Motores de base de dados MySQL
O MySQL armazena cada base de dados (também chamado de schema) como um subdirectório da directoria subjacente, ou seja o FileSystem. Quando criamos uma tabela, o MySQL armazena a definição da tabela com extensão .frm com o mesmo nome da tabela.
Deste modo, quando é criado uma tabela chamada MinhaTabela, é armazenado a definição da tabela em MinhaTabela.frm. Isto porque o MySQL usa o file system para armazenar os nomes da base de dados e a definição das tabelas, que são case sensitive (faz distinção entre maiúsculas e minúsculas) e dependem da plataforma. Ou seja no Windows tabelas e nome da base de dados são case insensitive, ou seja, não faz distinção de maiúsculas e minúsculas, por isso uma tabela com nome PESSOA é a mesma com o nome pessoa. Já os sistemas Unix são case sensitive, o que faz com o exemplo anterior não seja válido, pois PESSOA é diferente de pessoa. Isto pode trazer problemas para quem gosta de trabalhar hoje no Windows e amanhã no Linux. Por isso sugiro que coloquem o nome de tabela e atributos sempre em minúsculas.
Cada motor de base de dados armazena a informação e os índices de forma distinta, mas o servidor actua sobre a definição da tabela. Para determinar que motor de base de dados uma determinada tabela utiliza, podemos usar o comando: SHOW TABLE STATUS. Por exemplo, para examinar o utilizador de uma base de dados MySQL podemos fazer da seguinte forma:
SHOW TABLE STATUS LIKE 'user' ************************ 1. linha ************************* Name: user Engine: MyISAM Row_format: Dynamic Rows: 6 Avg_row_length: 59 Data_length: 356 Max_data_length: 4294967295 Index_length: 2048 Data_free: 0 Auto_increment: NULL Create_time: 2002-01-24 18:07:17 Update_time: 2002-01-24 21:56:29 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges 1 row in set (0.00 sec)
O output mostra que é uma tabela com motor MyISAM. Para além disso também é retornada outra informação e estatísticas facilmente identificáveis.
O motor MyISAM
O MyISAM é o motor por default do MySQL e providencia um bom equilíbrio entre a performance e funcionalidades úteis, como full-text indexing (porção de texto completa do catalogo que armazena todas palavras completas e as suas localizações para uma dada tabela) e compressão de dados. Contudo o MyISAM não suporta transacções (controlo concorrencial) ou locks ao nível de linha.
Armazenamento
O MyISAM armazena cada tabela em dois ficheiros: um ficheiro de dados e o ficheiro de índice. Os dois ficheiros têm extensão .MYD e .MYI, respectivamente. O MyISAM é um formato de plataforma neutral, ou seja, pode-se copiar os dados e os índices de um processador Intel para uma arquitectura PowerPC ou arquitectura Sun SPARC sem qualquer problema. As tabelas do MyISAM podem conter registos estáticos e dinâmicos. Registos estáticos são registos que não sofrem variações em termos de tamanho, ou seja, os seus campos têm todos o mesmo tamanho, Por exemplo, uma declaração do tipo CHAR é um campo estático com um máximo de 8 caracteres. Já um registo dinâmico contém um tamanho variável. Como por exemplo se for declarado VARCHAR, indica que o campo não é de tamanho fixo e pode variar em termos de tamanho. O número de registos que o MyISAM pode suportar é limitado primeiramente pelo espaço disponível em disco e o maior ficheiro criado e o limite suportado pelo sistema operativo.
Resumidamente podemos afirmar que o MyISAM possui as seguintes vantagens:
- Simplicidade: è um motor fácil de entender e fácil de construir aplicações que interajam com ele, possui também ferramentas grátis muito boas como o mysqlhotcopy.
- Optimização: Existem sistemas optimizados especialmente para este motor, pois este é um motor que embora não apresente características que imponham a integridade referencial (como o uso do
CASCADE), é um motor desenhado para um retorno eficiente dos dados. - Utilização de recursos: é o motor que melhor aproveita os recursos do sistema, torna-se por isso um excelente motor para servidores com capacidades limitadas.
Motor InnoDB
O motor InnoDB providencia uma transacção segura das tabelas. É incluído por default no MySQL 5.0, e possui capacidades de Commit, Rollback e recuperação de crashes. Possui também boas capacidades concorrênciais multi-utilizador, assim como performance. Suporta chaves estrangeiras e cascades, reforçando a integridade entre os dados. O motor InnoDB foi desenhado para tirar o máximo de performance no processamento de grande quantidade de informação. É utilizado para a produção de grandes quantidades de informação e em sites que requerem alta performance. Sites como Slashdot.org corre InnoDB. Contudo o InnoDB é um motor complexo e não tão acessível como o MyISAM.
Se uma aplicação não possui acessos concorrenciais e as inserções não são abundantes, então o motor MyISAM é o mais rápido, contudo o InnoDB possui várias vantagens:
- Updates e inserções não bloqueiam a leitura.
- Possibilita transacções.
- Suporta crash recovery.
- Suporta uma maior integridade dos dados.
Motor MEMORY
Formalmente conhecido como HEAP engine, este motor é útil quando necessitamos de acesso rápido aos dados que não sofrem alterações e que não necessitem de ficar armazenados depois de um restart. Toda a informação é armazenada em memória (RAM, ou seja memória volátil), portanto são queries que não necessitam de esperar por operações de I/O (processo tradicional que um SGBD executa para ler um bloco de dados, é geralmente uma das operações mais custosas de um SGBD). A estrutura de uma tabela persiste depois do restart, contudo a informação não persiste. Este motor é útil:
- Para a “pesquisa” ou “mapeamento” de tabelas, como por exemplo uma tabela que mapeia códigos postais para indicar nomes.
- Para caching periódico de dados.
- Para armazenar resultados temporários de análise de informação.
Motor Archive
É usado para armazenar grandes quantidades de informação sem a utilização de índices. Na prática é um motor simples para inserções rápidas e de boa compressão e dados.
Motor Federated Engine
O motor Federated foi adicionado na versão 5.0.3 do MySQL e é um motor que armazena dados numa base de dados remota, contudo é ainda um motor pouco desenvolvido e que possui problemas com queries agregadas, junções e outras aplicações básicas.
Motor NDB Cluster
Conhecido também como NDB é um motor usado pelo MySQL Cluster (versão especial do MySQL adaptada para sistemas distribuídos) para implementar tabelas que estão particionadas por vários computadores. É actualmente suportado por diversas distribuições Unix, e está previsto ser compatível com o Windows nas versões posteriores do MySQL Cluster. Este motor não é suportado pelas versões tradicionais do MySQL, como a 5.1.
Motor Falcon
O motor Falcon é um motor demasiado recente, desenhado para servidores com múltiplos processadores 64 bits, mas também corre em sistemas mais modestos. O objectivo deste motor é correr totalmente as transacções em memória e assim tornar rollbacks e operações de recovery extremamente rápidas. Falcon é ainda um motor em desenvolvimento, de tal forma que ainda não é descrito no site do MySQL.
Existem mais motores não abordados neste artigo, como tal ainda muito ficou por falar de outros motores. Mesmo dos motores abordados, foi apenas descrito as características base de cada um dos motores.
Selecionar o motor correcto
Quando desenhamos uma aplicação que seja baseada em MySQL, necessitamos de decidir qual motor de base de dados é o melhor para enfrentar complicações futuras no projecto. É necessário analisar se o motor padrão (MyISAM) providencia todas as características que necessitamos como transacções ou acessos concorrenciais por exemplo. Como é possível escolher um motor de base de dados por tabela, necessitamos de ter uma ideia clara como cada tabela é usada e como a informação vai ser armazenada. É necessário entender os requisitos da aplicação e o seu potencial de crescimento.
Com esta informação é possível começar a tomar boas opções sobre os motores que vão realizar a tarefa. Contudo não é necessariamente uma boa ideia utilizar um motor de base de dados diferente para cada tabela. Se nos for possível afastar disso, mais simples se torna a tarefa.
Optimização de queries
A razão principal para uma query estar com fraca performance é porque está a trabalhar com demasiada informação. No geral a maioria das queries com fraco desempenho podem ser optimizadas acedendo a uma menor quantidade de dados. Podemos analisar uma query com fraco desempenho em dois passos:
- Descobrir se a aplicação que acedemos à base de dados não está a devolver mais informação do que aquela que é necessária, ou seja, devolver demasiadas linhas e colunas.
- Descobrir se o MySQL está a analisar mais linhas do que aquelas que necessita.
Estamos a devolver mais informação do que aquela que necessitamos?
Muitas queries devolvem mais informação do que necessitam, o que faz com que essa informação seja lixo. Este tipo de prática é um trabalho extra para o MySQL que consome memória e recursos de CPU. Aqui ficam alguns erros típicos:
- Devolver mais campos do que é necessário é um dos principais erros quando executamos uma query.
- Outro erro comum é assumir que o MySQL fornece resultados sobre a procura em vez de calcular e devolver o resultado completo conjunto. Ou seja, são utilizadas técnicas como a declaração da cláusula
SELECTque retorna muitas filas, e em seguida retira-se apenas as linhas necessárias. Por exemplo o MySQL retorna 100 linhas e o utilizador apenas precisa de 10. Muitas vezes é assumido erradamente que o MySQL devolve aqueles 10 resultados e pára de executar a consulta, mas o que realmente o MySQL faz é gerar um conjunto completo de resultados, e descarta aqueles que não são necessários. Uma solução é utilizar a cláusula LIMIT que limita o número de linhas devolvidas. Exemplos:SELECT p.nome, p. morada, p.telefone, p.bi FROM pessoa p ORDER BY p.bi DESC LIMIT 10; SELECT p.nome, p. morada, p.telefone, p.bi FROM pessoa p ORDER BY p.bi LIMIT 0, 50;Faz o mesmo que a anterior mas devolve o intervalo de linhas especificado, ou seja, da linha 0 até á linha 50. - Devolver várias colunas com uma junção de várias tabelas. Exemplo:
-- Se pretendêssemos devolver todos os actores que dão voz no filme Shrek não devemos elaborar a query desta seguinte forma: SELECT * FROM actor INNER JOIN filmes_actor USING(actor_id) INNER JOIN filmes USING(film_id) WHERE filmes.titulo= 'Shrek' ;Esta query devolve todas as colunas de todas as 3 tabelas e terá uma performance péssima. Mas também podemos escrever a query da seguinte forma:SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM filmes WHERE titulo='Shrek');Construímos uma query mais simples e mais optimizada que devolve menos colunas de uma forma mais eficiente, evitando as junções.