View
214
Download
2
Category
Preview:
DESCRIPTION
Descrição
Citation preview
Departamento de Informática
Mestrado em Engenharia Informática
Sistemas de Base de Dados
MySQL
Grupo nº 14:
Artur Miguel Coelho Barros (nº 25104)
Cátia Alexandra Matos da Silva (nº 28053)
João Luís de Matos Gil Milheiriço (nº 29657)
1º Semestre de 2010/11
2
Índice geral
1 Introdução 7
1.1 Introdução histórica e aplicabilidade do sistema estudado 8
1.2 Notas sobre instalação do sistema 10
2 Cobertura do SQL 11
2.1 Modos SQL 11
2.2 Sintaxe SQL 12
2.2.1 Manipulação de Dados (Data Manipulation Statements) 12
2.2.2 Definição de Dados (Data Definition Statements) 17
2.2.3 Outros 19
3 Armazenamento e File Structure 21
3.1 Introdução 21
3.2 Storage Engines 22
3.2.1 InnoDB 25
3.3 Query Cache 27
3.4 Particionamento 28
4 Indexação e Hashing 30
4.1 Introdução 30
4.2 Indexação em MySQL 31
4.2.1 Índices Aglomerados (Clustering Indices) e Índices Secundários 33
4.3 Conceitos Básicos de Indexação 36
4.3.1 Índices Parciais 36
4.3.2 Índices Multi-Coluna 36
4.3.3 Índices Unique e Chaves Primárias 37
4.4 Tipos e Estruturas de Indexação 37
4.4.1 B-Tree 38
4.4.2 R-Tree (Índices Espaciais) 39
3
4.4.3 Índices Dispersos (Hash Function) 39
4.4.4 Indices FULLTEXT 40
4.5 Manutenção de Indices 41
5 Processamento e optimização de programas 42
5.1 Optimizador de Perguntas 43
5.1.1 Configuração do Optimizador 43
5.1.2 Optimização de Consultas no InnoDB 44
5.2 Ferramentas de Optimização 45
5.2.1 Analyse Table 45
5.2.2 Benchmark 45
5.2.3 Explain 45
5.3 Estimativas do custo de Consultas 49
5.4 Algoritmos de Junção 49
5.4.1 Nested Loop 49
5.4.2 Nested Loop por bloco 50
5.5 Optimização de Junções 51
5.6 Optimização de Operações 51
6 Gestão de transacções e controlo de concorrência 53
6.1 Introdução 53
6.2 Níveis de Isolamento 54
6.2.1 READ UNCOMMITED 55
6.2.2 READ COMMITTED 56
6.2.3 REPEATABLE READ 56
6.2.4 SERIALIZABLE 56
6.3 Controlo de Transacções 57
6.3.1 Autocommit 57
6.3.2 Controlo Manual de Transacções 57
6.3.3 SavePoints 58
6.3.4 Bloqueios 58
6.4 Controlos de Concorrência e Multi-Versão 60
7 Suporte para Bases de Dados Distribuidas 62
7.1 Ferramentas de Distribuição 62
7.1.1 MySQL Replication 62
7.1.2 MySQL Cluster 64
4
7.2 Transacções Distribuidas (XA) 65
8 Outras caracteristicas do sistema estudado 67
8.1 Tipos de Dados 67
8.1.1 Tipos Numéricos 67
8.1.2 Tipos Temporais 68
8.1.3 Tipos de Caracteres 70
8.2 Suporte de formas Procedimentais 72
8.2.1 Triggers 72
8.2.2 Procedimentos e Funções 72
8.3 Suporte XML 73
8.4 Conectores e API’s 73
8.5 Utilizadores e Segurança 75
8.5.1 Grant 75
8.5.2 Revoke 75
8.6 Ferramentas 76
8.6.1 MySQL Workbench 76
Conclusão 80
Referências 81
5
Índice de figuras
Figura 1 Arquitectura do sistema MySQL. ............................................................................... 22
Figura 2 Índice B-Tree numa tabela com índices não aglomerados. ........................................ 38
Figura 3 Protocolo de Execução de uma consulta em MySQL ................................................ 43
Figura 4 Extensão do Tipo de Dados INT. ............................................................................... 68
Figura 5 Formato dos Tipos de dados Temporais .................................................................... 69
Figura 6 MySQL Workbench – SQL Editor ............................................................................. 77
Figura 7 MySQL Workbench – Data Modelling ...................................................................... 78
Figura 8 MySQL Workbench – Administrator Browser .......................................................... 79
6
Índice de tabelas
Tabela 1 Funcionalidades suportadas pelo motor InnoDB ........................................................ 25
Tabela 2 Informação relativa a Actores .................................................................................... 31
Tabela 3 Lista ordenada com os nomes dos actores .................................................................. 32
Tabela 4 Listagem de API’s e interface do MySQL ................................................................. 74
7
1 Introdução
“Um Sistema de Gestão de Bases de Dados (SGBD) não é nada mais do que
um conjunto de programas que permitem armazenar, modificar e extrair
informação de um banco de dados. (…) Um Sistema de Gestão de Base de
Dados implica a criação e manutenção de bases de dados, elimina a
necessidade de especificação de definição de dados, age como interface entre
os programas de aplicação e os ficheiros de dados físicos e separa as visões
lógica e de concepção dos dados.”[1]
Hoje em dia, todas as organizações utilizam um Sistema de Base de Dados de modo a obter um
controlo efectivo sobre todo o seu grande volume de informação. Neste sentido e de acordo com os
objectivos da disciplina de Sistemas de Base de Dados, não sendo um Engenheiro Informático um
mero utilizador é necessário que este conheça a implementação de um dado SGBD de forma a
manipular toda a informação presente neste de forma eficiente.
Deste modo este documento constitui uma análise do SGBD MySQL do ponto de vista da gestão
de armazenamento da informação, estruturas de indexação suportadas, mecanismos para
processamento e optimização de perguntas, gestão de transacções e controlo de concorrência e
ainda o suporte de base de dados distribuídas. Segue portanto um sumário superficial sobre o
conteúdo de cada um dos capítulos:
Capitulo 2, Cobertura do SQL: neste capítulo serão levantados os conceitos que o sistema
MySQL cobre do standard SQL. Neste sentido serão mencionados aspectos de sintaxe, tipos
8
de dados, operadores e cobertura ao nível de DML (Data Manipulation Language) e DDL
(Data Definition Language).
Capitulo 3, Armazenamento e File Structure: serão apresentados os vários mecanismos para
controlo de armazenamento de dados (storage engines), assim como aspectos de acesso a
disco e organização de ficheiros.
Capitulo 4, Indexação e Hashing: neste capítulo serão examinados como funcionam os
vários índices de MySQL, explorando os vários algoritmos de acordo com os vários tipos de
dados e tamanho das tabelas.
Capitulo 5, Processamento e Optimização de Perguntas: aqui será discutido como o
MySQL implementa e quais os algoritmos associados a cada uma das operações básicas.
Serão também discutidos aspectos relacionados com o processamento de expressões
complexas e quais as ferramentas disponíveis para a análise de perguntas MySQL.
Capitulo 6, Gestão de Transacções e Controlo de Concorrência: será analisado o conceito
de transacção e respectivo processamento; Serão também discutidos aspectos como
Bloqueios, DeadLocks e os vários níveis de isolamento comparativamente ao standard SQL.
Capitulo 7, Suporte para base de dados distribuídas: serão discutidos aspectos inerentes a
base de dados distribuídas, nomeadamente o tipo de distribuição e as várias ferramentas.
Será abordado também conceitos como replicação e cluster.
Capitulo 8, Outras características: Por fim serão apresentados outros aspectos relacionados
não com a implementação do sistema, nomeadamente suporte para Web, Segurança e
ferramentas disponibilizadas para por exemplo reporting, dataWarehousing.
Ao longo do documento serão efectuadas análises comparativas tanto com o standard SQL como
o sistema estudado nas aulas: Oracle.
1.1 Introdução histórica e aplicabilidade do sistema
estudado
“MySQL, the world’s most popular open source database”.
9
MySQL é um sistema de base de dados open source desenvolvido por David Axmark, Allan
Larsson e Michael Widenius, cuja primeira versão foi lançada ao público no final do ano de 1996.
Em 1985, Michael Widenius fundara a TCX DataKonsult, uma empresa de DataWarehousing,
conjuntamente com Allan Larsson. Na empresa surgira a necessidade de disponibilizar uma
interface SQL para determinados clientes. Neste sentido tentou utilizar-se o sistema mSQL através
das rotinas ISAM1, mas rapidamente se conclui que o sistema mSQL não era rápido e flexível o
suficiente. Assim surge uma nova interface SQL (baseada na interface mSQL), denominada por
MySQL.
Em 1996, são publicadas as primeiras versões de MySQL sobre a forma de distribuições binárias
para Linux e Solaris. Hoje em dia, o sistema MySQL opera numa variedade considerável de
plantaformas, assim como está disponível para uma variedade de linguagens de programação,
nomeadamente C, C++, Java, PHP entre outras. Para além das várias distribuições que
disponibiliza, MySQL passou a disponibilizar também o seu código fonte.
Após a publicação da primeira versão, MySQL torna-se bastante popular e em 2001 é fundada
uma nova empresa focada apenas no sistema MySQL, denominada de MySQL AB. No entanto em
2008, a empresa MySQL AB é comprada pela Sun Microsystems.
Desde a implementação de MySQL que fora dada real importância aos conceitos de
performance, fiabilidade e portabilidade, sendo estes os aspectos que permitem que MySQL se
distinga entre os vários sistemas de base de dados. Para além destes conceitos e à medida que foram
sendo lançadas novas versões do sistema, foram incluídas novas funcionalidades cuja ausência
inicialmente levara a uma forte critica. Neste sentido actualmente MySQL disponibiliza diversas
funcionalidades, nomeadamente Query Caching, Replicação, Triggers, Subqueries, Visões e
Cursores.
O sistema MySQL pode ser utilizado em qualquer área, no entanto ele é maioritariamente
utilizado no desenvolvimento de aplicações Web, de que são exemplo projectos como a Wikipedia,
Yahoo! Finance e também o Facebook.
1 ISAM (Indexed Sequential Acess Method) é uma técnica para acesso a dados armazenados por exemplo num disco. No sistema MySQL as tabelas de tipo ISAM que já estavam obsoletas na release 4.0 e foram retiradas na 5.0, tendo sido substituído pela implementação MyISAM. (http://dev.mysql.com/doc/refman/4.1/pt/isam.html)
10
1.2 Notas sobre instalação do sistema
A MySQL encontra-se disponível tanto em formato binário como em source e a sua instalação é
bastante simples e intuitiva. Neste momento o MySQL apresenta suporte para uma grande
variedade de sistemas operativos:
• Microsoft Windows
• Mac OS X
• Linux
• Solaris e OpenSolaris
• IBM AIX
• HP-UX
• FreeBSD
• I5/OS.
11
2 Cobertura do SQL
“Our aim is to support the full ANSI/ISO SQL standard, but without making
concessions to speed and quality of the code”.
O principal objectivo do sistema MySQL é manter sempre uma concordância com o standard SQL,
sem sacrificar a rapidez ou fiabilidade no sistema. Desta forma, por vezes vamos encontrar no
MySQL algumas extensões ao standard ou mesmo novas funcionalidades, sempre que as diferenças
justifiquem um aumento da usabilidade por parte dos utilizadores. Noutros casos encontraremos
algumas diferenças tanto a nível de sintaxe ou de funcionalidades não suportadas.
Antes de efectuarmos uma análise comparativa relativamente aos dois sistemas, vamos focar-nos
num aspecto relativo ao sistema em estudo: MySQL permite a possibilidade de operar em diferentes
modos.
2.1 Modos SQL
A especificação de determinados modos SQL através de uma variável de sistema, permite controlar
aspectos relativos a validações de dados de input ou até mesmo permitir melhor compatibilidade
com outros sistemas de base de dados.
12
Por defeito o sistema MySQL opera no modo non-strict, que faz com que os valores de entrada
errados sejam convertidos para os correctos. Por exemplo se tentarmos atribuir um valor negativo a
um campo que apenas aceita valores positivos, este é convertido para o valor nulo. Para definir:
SET sql_mode = '';
De outro modo se pretendermos activar as restrições de input, para por exemplo efectuar
validações ao nível dos valores nulos ou valores fora de um dado intervalo tem-se:
SET sql_mode = STRICT_TRANS_TABLES;
SET sql_mode = STRICT_ALL_TABLES;
Caso o sistema tenha de ser mais restrito a nível de validações e comportar-se de modo mais
semelhante a um sistema tradicional, tem-se:
SET sql_mode = TRADITIONAL;
Neste modo estão activas por exemplo as validações ao nível de datas e divisões por zero. No
entanto, existem muitos mais modos que são suportados e podem ser consultados em
http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html.
2.2 Sintaxe SQL
De modo a analisar a cobertura que o sistema MySQL faz do standard SQL, serão descritas de
seguida algumas das operações principais suportadas pelo sistema.
2.2.1 Manipulação de Dados (Data Manipulation Statements)
a) DELETE
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Tal como acontece com o standard SQL a remoção de tuplos em MySQL é feita através da
especificação do nome da tabela. Caso se pretenda poderá especificar-se também as
condições que identificam os tuplos a remover, assim como a ordem pela qual os tuplos
devem ser apagados.
13
No entanto o MySQL permite a especificação da cláusula LIMIT de modo a indicar o
número de tuplos a remover. Por exemplo:
mysql> SELECT * FROM actor;
+----------+---------+-------+---------+
| n_bi | nome | idade | salario |
+----------+---------+-------+---------+
| 12345678 | maria | 40 | 3000.5 |
| 12868493 | antonio | 34 | 400.56 |
| 46897878 | manuel | 56 | 9000.56 |
| 46893456 | manuel | 26 | 1234.56 |
+----------+---------+-------+---------+
4 rows in set (0.00 sec)
mysql> DELETE FROM actor
-> WHERE nome = 'manuel'
-> ORDER BY salario
-> LIMIT 1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM actor;
+----------+---------+-------+---------+
| n_bi | nome | idade | salario |
+----------+---------+-------+---------+
| 12345678 | maria | 40 | 3000.5 |
| 12868493 | antonio | 34 | 400.56 |
| 46897878 | manuel | 56 | 9000.56 |
+----------+---------+-------+---------+
3 rows in set (0.00 sec)
Verifica-se assim que numa primeira fase foram seleccionados os tuplos de acordo com o
especificado na cláusula WHERE; posteriormente os registos seleccionados foram ordenados
e depois foi removido apenas o primeiro tuplo.
Ao contrário do standard MySQL permite especificar várias tabelas simultaneamente na
cláusula DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
14
Neste caso já não é possível a especificação das cláusulas ORDER BY e LIMIT.
Após a execução da instrução DELETE é devolvido o número de tuplos eliminados, que
pode ser obtido através da função ROW_COUNT().
b) UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Em paralelismo com a instrução anterior, UPDATE permite a especificação de múltiplas
tabelas. Outro aspecto comum a ambas as instruções e que difere um pouco do SQL
standard são as cláusulas LOW_PRIORITY e IGNORE:
LOW_PRIORITY - o servidor “adia” a remoção dos tuplos até que não exista nenhum
cliente a efectuar a leitura da tabela em questão;
IGNORE – indica ao servidor que deverá ignorar todos os erros durante o
processamento e devolve-los como warnings.
No entanto o comportamento da instrução UPDATE difere do standard uma vez que na
actualização de uma coluna é utilizado sempre o valor mais recente. Por exemplo:
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
Deste modo, col2 toma o novo valor de col1, e não o valor original; col1 e col2 têm
assim o mesmo valor.
c) INSERT
O sistema MySQL suporta a sintaxe padrão de inserção do standard SQL, onde se
especifica os tuplos de forma sequencial ou individual:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
15
Para além da sintaxe apresentada acima, MySQL permite também a inserção de novos
registos através da especificação do nome da coluna e respectivo valor a actualizar.
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Sempre que não se indique o nome do campo a actualizar é necessário que as valores a
inserir respeitem a ordem pela qual as colunas foram criadas na tabela.
Caso se pretenda utilizar o valor pré-definido para uma dada coluna é necessário utilizar a
palavra-chave DEFAULT.
Em paralelo com o standard, MySQL suporta também a inserção de múltiplos registos para
uma dada tabela, por exemplo:
mysql> insert into realizador (n_bi, nome, idade)
-> values (24668747,'maria',50),
-> (65856785,'manuel',23),
-> (12345643,'joao',45);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
Outra particularidade do MySQL é que este não suporta a sintaxe SELECT ... INTO
TABLE do standard. No entanto, MySQL suporta INSERT INTO ... SELECT que
basicamente traduz o mesmo efeito:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
d) SELECT
Uma consulta genérica MySQL é em tudo igual ao standard. A única excepção é que
MySQL não suporta a sintaxe SELECT ... INTO, tal como já foi referido.
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
16
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
Uma vez que o âmbito deste capítulo é analisar a cobertura que o sistema em estudo faz do
standard, não iremos descrever o funcionamento do SELECT, pois este encontra-se
descrito no manual.
Á semelhança do que sucede com a sintaxe SELECT ... INTO, MySQL não permite a
especificação directa de SELECT ... INTERSECT SELECT ... e SELECT ... EXCEPT
SELECT .... No entanto é possível obter exactamente o mesmo resultado através da
especificação de por exemplo INNER JOIN e LEFT JOIN, respectivamente:
-- Definição da Intersecção em MySQL:
/* SINTAXE CORRECTA */
SELECT tbl_name1.colA, tbl_name1.colB
FROM tbl_name1 INNER JOIN tbl_name2
USING (colA, colB);
/* SINTAXE INCORRECTA */
SELECT tbl_name1.colA, tbl_name1.colB FROM tbl_name1
INTERSECT
SELECT tbl_name2.colA, tbl_name2.colB FROM tbl_name2;
17
-- Definição da Excepção em MySQL:
/* SINTAXE CORRECTA */
SELECT DISTINCT tbl_name1.colA, tbl_name1.colB
FROM tbl_name1 LEFT JOIN tbl_name2
USING (colA, colB)
WHERE tbl_name2.colA IS NULL; -- Permite encontrar as linhas na
-- Tabela tbl_name1 que não
-- existem na tabela tbl_name2
/* SINTAXE INCORRECTA */
SELECT tbl_name1.colA, tbl_name1.colB FROM tbl_name1
EXCEPT
SELECT tbl_name2.colA, tbl_name2.colB FROM tbl_name2;
O sistema MySQL disponibiliza ainda mais alguns comandos de manipulação de dados que
podem ser consultados no manual, mas que não são relevantes para o assunto em análise.
2.2.2 Definição de Dados (Data Definition Statements)
1. ASSERTION
Em 1992, foi introduzido no SQL Standard a possibilidade de dada uma expressão,
garantir a satisfação da mesma em toda a base de dados. Este conceito foi inserido através
da sintaxe CREATE ASSERTION ... CHECK ...
No entanto e ao contrário do standard, MySQL não disponibiliza a possibilidade de criação
de ASSERTIONS.
2. FOREIGN KEY
Desviando-nos um pouco do âmbito deste documento, relembramos que o conceito de
chave externa (Foreign Key) trata de uma relação de referência entre duas tabelas
impedindo assim a criação de inconsistências numa dada base de dados.
Neste sentido, o sistema MySQL oferece a possibilidade de verificação de chave externa:
18
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
No entanto e de forma a permitir que os próprios programadores possam ter opção de
escolha tendo em conta o sistema a implementar, MySQL disponibiliza também a opção de
não utilização de chave estrangeira.
Assim, se pretender o uso de chave externa (sem esquecer os custos a ela associados)
deverá utilizar-se a InnoDB storage engine (o conceito de storage engine será explorado e
analisado no capitulo seguinte). Caso contrário deverá utilizar-se outra storage engine, por
exemplo MyISAM (que oferece uma boa performance nas operações de inserção e
selecção). A nível de sintaxe, as storage engine que não suportam as validações de chave
externa compilam o código correspondente à sintaxe acima citada, mas não o utilizam.
Para além deste aspecto, MySQL, ou mais especificamente a InnoDB storage engine,
possui mais algumas diferenças no que se refere à utilização de validações de chave
externa, nomeadamente:
• Ao efectuar inserções, remoções ou actualizações nas tabelas, são verificadas as
restrições de chave externa linha a linha e não apenas no final do comando SQL ter
sido processado. Isto faz com que determinadas situações não sejam possíveis em
MySQL, nomeadamente remoções de linhas que possuam uma referência para si
própria como chave externa;
• MySQL reconhece sintacticamente a cláusula MATCH disponível no standard SQL,
mas no entanto não tem o efeito esperado. Para além disso quando indicada, esta
implica que as cláusulas ON DELETE e ON CASCADE também não sejam processadas;
• Em SQL, a cláusula REFERENCES permite especificar a tabela a referenciar e o
nome dos campos, caso não estejamos a referenciar completamente a chave
primária da tabela de referência. No entanto em MySQL é sempre necessário a
especificação completa dos campos.
Caso se pretenda informações mais detalhadas sobre a utilização de chave externa deverá
consultar-se o manual.
19
3. TRANSACTION
À semelhança das restrições para chave externa, apenas a storage engine InnoDB, suporta
o conceito de Transacção tal como é conhecido no standard. Neste sentido, pretende-se que
os próprios programas possam escolher a abordagem pretendida tendo em conta os
requisitos.
No entanto, apesar da storage engine InnoDB suportar transacções, estas não funcionam
como especificado pelo standard no que se refere ao processamento de erros. ISO SQL
especifica que quando ocorre um dado erro durante a execução de um dado comando será
efectuado o rollback de toda a declaração. No entanto em MySQL, apenas é efectuado
rollback de parte da declaração ou então a totalidade da transacção.
Por exemplo, sempre que ocorrer um deadlock ou um timeout por não se conseguir
efectuar determinado bloqueio, todas as declarações da transacção serão canceladas. Caso
contrário apenas o comando em questão será anulado.
Para uma análise mais aprofundada relativamente à gestão de transacções no MySQL
deverá ser consultado o capítulo seis deste documento.
2.2.3 Outros
Para além dos aspectos referenciados nos dois últimos tópicos referentes a manipulação e definição
de dados, o sistema MySQL possui ainda alguns aspectos que se distinguem da implementação do
standard SQL. Uma vez que os conceitos mais importantes já foram discutidos acima, faremos de
seguida a enumeração de mais algumas diferenças:
1. Tal como sucede no SQL, MySQL utiliza a sintaxe /* Isto é um comentário */ para
efectuar comentários. Para além disto permite também a sintaxe:
mysql> SELECT 1+1; # Este comentário continua até ao fim da linha
mysql> SELECT 1+1; -- Este comentário continua até ao fim da linha
-- mas sem esquecer pelo menos um espaço em
-- branco após o segundo -
2. As strings podem ser delimitadas por “"” ou “'”, não apenas por “'”.
3. Pode-se especificar ASC e DESC tanto com ORDER BY como GROUP BY.
20
4. Para concatenação de strings deverá ser utilizada a função CONCAT() e não || como no
standard.
5. Em MYSQL, || e OR são sinónimos tal como && e AND.
6. Utilização do comando REPLACE, que funciona à semelhança do INSERT, excepto nos
casos em que se uma dada linha na tabela tiver o mesmo valor para um índice único ou
de chave primária, a linha antiga é removido antes da inserção da nova linha.
7. Utilização do operador := para afectar variáveis.
21
3 Armazenamento e File Structure
3.1 Introdução
Uma das principais funcionalidades de um sistema de base de dados é a mecânica de acesso a
ficheiros. Esta funcionalidade assegura a eficiência e uma maior performance do sistema. Cada
sistema utiliza técnicas próprias de organização de ficheiros havendo mesmo aqueles que
implementam o seu próprio sistema de ficheiros.
O MySQL não implementa um sistema de ficheiros próprio usando o do Sistema Operativo
correspondente. No entanto, dá suporte a diversos mecanismos de acessos a ficheiros (storage
engines). O MySQL suporta também um mecanismo de buffer e cache próprio.
A arquitectura de motores de armazenamento do MySQL permite a um profissional escolher um
motor de armazenamento especializado para uma aplicação particular sem ter que se preocupar com
requisitos específicos. A arquitectura é demonstrada na figura em baixo:
22
Figura 1 Arquitectura do sistema MySQL.
3.2 Storage Engines
Ter vários motores de armazenamento permite ao MySQL ter múltiplas funcionalidades
diferentes, muitos motores de armazenamento são produzidos por outras empresas. O InnoDB é um
dos mais utilizados e foi desenvolvido por outra empresa. Isto significa que qualquer pessoa pode
desenvolver um motor de armazenamento que se adapte às suas necessidades sem ter que esperar
por que uma certa funcionalidade seja lançada.
Aquando a escolha do motor de armazenamento existe uma série de funcionalidades que se têm
de ter em conta de forma a obter a melhor relação entre as funcionalidades do sistema face à sua
performance, sendo algumas destas:
• Suporte de transacções: O uso deste mecanismo requer uma maior sobrecarga em
termos de memória, espaço de armazenamento e uso de CPU. Aplicações que não usem
transacções e possam utilizar motores de armazenamento não transaccionais poderão ver
a sua performance aumentada.
23
• Concorrência: No MySQL existem a possibilidade de bloquear uma tabela inteira. No
entanto, existem motores de armazenamento com funções de bloqueio mais flexíveis
permitindo bloquear apenas linhas da tabela.
• Implementação de Indicies: Existem várias formas de implementar e tirar partido desta
funcionalidade, os designers de motores de armazenamento implementam aqueles que
pensam dar maior performance aos seu sistema.
• Integridade referencial: O uso de chaves estrangeiras para reforçar a integridade
referencial entre tabelas é uma prática bastante comum. No entanto, nem todas as
aplicações fazem uso desta funcionalidade e muitos motores de armazenamento não a
implementam.
• Buffering: O buffering de dados, indicies e logs são tratados pelos motores de
armazenamento. Alguns motores escolhem não suportar certos âmbitos deste mecanismo
e noutros suportam vários. Exemplo disto é o MyISAM que não suporta buffering de
dados mas implementa vários buffers para índices.
• Armazenamento de Ficheiros: Alguns motores guardam os dados e índices em ficheiros
próprios, permitindo assim a cópia de uma tabela pela cópia destes ficheiros. Enquanto
outros usam meta data centralizada num único ficheiro dificultando assim a cópia de uma
tabela.
• Backup: Alguns motores têm ferramentas que permitem backups consistentes sem
bloqueio do sistema ao passo que outros causam distúrbios na aplicação se estiver a ser
efectuado um backup numa tabela que esteja a ser utilizada.
Como os motores de armazenamento são usados ao nível das tabelas são muitas vezes chamados
de tipo de tabela. Os comandos CREATE TABLE e ALTER TABLE podem usar a opção ENGINE para
definir/alterar o motor associado à tabela. Por exemplo:
CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;
É possível ainda consulta o motor de armazenamento associado a uma dada tabela através do
comando:
24
SHOW TABLE STATUS LIKE ‘TABLE_NAME’;
Para determinar quais os motores de armazenamento suportados usa-se o comando SHOW
ENGINES. O valor na coluna Support indica se um motor de armazenamento pode ou não ser
utilizado. Um valor de YES, NO, ou DEFAULT indica respectivamente se o motor está disponível,
indisponível, ou a ser utilizado como default. Por exemplo:
mysql> SHOW ENGINES\G
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
Desta forma, enumera-se de seguida alguns dos motores suportados pelo MySQL:
• MyISAM: Usado por defeito no MySQL e o mais utilizado na Web.
• InnoDB: Um motor com sistema de transacções seguras que suporta operações de
commit, rollback, e mecanismos de crash-recovery para proteger a informação guardada.
O InnoDB guarda a informação num cluster de índices para reduzir o I/O para consultas
baseadas em chaves primárias. O InnoDB também suporta o conceito de chave
estrangeira para manter a integridade referencial.
• Memory: Também conhecido como HEAP engine, guarda todos os dados em RAM para
ambientes que requeiram acessos rápidos à informação.
• Blackhole: Motor que aceita mas não guarda qualquer tipo de dados devolvendo sempre
vazio. É utilizado para criar logs ou filtrar consultas.
• Merge: Permite agrupar tabelas MyISAM idênticas logicamente de forma a ficarem
referenciadas como uma só.
• Archive: Utilizado para manter grandes quantidades de informação sem índices.
• NBD: permite o clustering de uma base de dados.
De forma a não alongar muito o capitulo em questão, optámos por analisar apenas o motor
InnoDB.
25
3.2.1 InnoDB
Foi desenvolvido para obtenção de máxima performance no processamento de grandes volumes
de dados. Tem sua própria área de buffer para armazenar dados e índices na memória principal. As
tabelas e índices são armazenados em tablespaces.
O InnoDB estrutura os dados (tablespaces) nos denominados clustered indeces, de forma a
reduzir operações de I/O para consultas baseadas nas chaves primárias. Para manter a coerência e
integridade das base de dados, o InnoDB suporta o uso de chaves estrangeiras, mas não permite
multitable clustering.
De seguida apresenta-se um resumo das funcionalidades suportadas no InnoDB:
Tabela 1 Funcionalidades suportadas pelo motor InnoDB
Storage limits 64TB Transactions Yes Locking granularity Row
MVCC Yes Geospatial data type support Yes Geospatial indexing support No
B-tree indexes Yes Hash indexes No Full-text search indexes No
Clustered indexes Yes Data caches Yes Index caches Yes
Compressed data Yes[a] Encrypted data[b] Yes Cluster database support No
Replication support[c] Yes Foreign key support Yes
Backup / point-in-time recovery[d] Yes
Query cache support Yes
Update statistics for data dictionary Yes
[a
] Compressed InnoDB tables require the InnoDB Barracuda file format. [b
] Implemented in the server (via encryption functions), rather than in the storage engine. [c
] Implemented in the server, rather than in the storage product [d
] Implemented in the server, rather than in the storage product
Arquitectura de Armazenamento
No motor InnoDB toda a informação (dados, índices e estruturas internas) reside em páginas, que
por defeito têm um tamanho de 16K. Apesar de haver 16K de tamanho de página estas não são
completamente preenchidas com dados. Em vez disso, consoante os padrões de acessos aos dados o
InnoDB poderá tentar preencher uma página com até mais de 90% do seu tamanho. No entanto,
algumas páginas não passam dos 50%. Se o preenchimento das páginas for menor que este valor o
InnoDB baralha e equilibra internamente as páginas por forma a manter um equilíbrio.
As páginas são então agrupadas em segmentos (conjuntos sequencial de 64 páginas). A maior
estrutura de armazenamento gerida pelo InnoDB são as tablespaces. Contêm um ou mais ficheiros
de dados (compostos por segmentos) repartidos por um ou mais discos. Estes ficheiros podem ser
26
configurados para expandir automaticamente (até não haver mais espaço em disco) com o
crescimento da base de dados. O InnoDB usa tablespaces para guardar tanto os dados como a
informação dos índices. Por defeito o InnoDB utiliza uma tablespace partilhada que é criada
automaticamente; poderá no entanto ser especificada uma tablespace dedicada para uma só tabela
(ficheiros .idb).
Quando um segmento dentro da tablespace aumenta, são alocadas as primeiras 32 páginas para
ele, sendo posteriormente feita a alocação de toda a extensão do segmento. O InnoDB pode
adicionar a um grande segmento até 4 extensões de uma vez para assegurar a boa sequencialidade
dos dados.
Quando são removidos dados de uma tabela, o InnoDB contrairá o índice de árvore-B
correspondente.
Estrutura de Registos
Um registo no InnoDB está dividido em 3 partes:
• Campo de início de offsets (1 ou 2 bytes) – é uma lista de números que contêm informação
relativa ao campo seguinte no registo.
• Bytes extra (6 bytes) – é um cabeçalho de tamanho fixo com metadata sobre o registo:
• Flag que Indica se o registo foi apagado (1bit).
• Apontador para o próximo registo da página (16 bits).
• Número do registo no heap (13bits).
• Número de campos no registo (10bits).
• …
• Conteúdo do campo (depende do conteúdo) – contem a informação do campo.
Buffer Pool
O InnoDB mantém uma zona de armazenamento denominada de buffer pool para cache de dados
e índices em memória.
O controlo deste mecanismo é feito usando uma variação do algoritmo LRU (Least Recently
Used). Quando é necessário arranjar espaço para inserir um novo bloco no buffer o InnoDB despeja
27
o bloco que não é usado há mais tempo e insere o novo bloco no meio da lista. Esta inserção a meio
do buffer permite tratar a lista como duas sublistas:
• À cabeça uma sublista de novos blocos que foram acedidos recentemente.
• À cauda uma sublista de blocos velhos que não são acedidos há mais tempo.
Este algoritmo mantém os blocos que são bastante usados em pesquisas na sublista de novos e a
lista de velhos com blocos menos usados candidatos a serem substituídos.
3.3 Query Cache
A cache de consultas (query cache) guarda os resultados do comando SELECT assim como a
pesquisa que o cliente envia para o servidor. Se uma consulta idêntica é recebida mais tarde o
servidor devolve o resultado da query cash invés de executar a pesquisa novamente. A cache é
partilhada entre as diversas sessões o que implica que um resultado obtido por um cliente pode ser
devolvido como resposta à mesma pesquisa por parte de outro.
Modo de Funcionamento
As consultas recebidas são comparadas com as que existem antes do parse. No entanto as
consultas têm de ser exactamente iguais (byte a byte) para ser consideradas idênticas. Por exemplo,
as consultas seguintes tratam de consultas diferentes:
SELECT * FROM TABLE_NAME
Select * from TABLE_NAME
Consultas que usem bases de dados diferentes ou versões diferentes de protocolos são
consideradas diferentes e guardas em cache separadamente.
Como a comparação das consultas em cache é feita antes do parsing, não é usada a cache para
guardar consultas executadas dentro de funções, triggers ou outras consultas (subconsultas).
Antes de serem devolvidos resultados da cache de uma dada consulta são verificadas as
permissões do utilizador para as bases de dados e tabelas envolvidas e só são devolvidos resultados
se o utilizador tiver as permissões necessárias.
Caso uma consulta seja devolvida da cache o servidor incrementa a variável Qcache_hits.
28
Se uma tabela for modificada todas as consultas guardadas são removidas.
É possível explicitar no comando SELECT a intenção de guardar (ou não) a consulta em cache
através dos parâmetros [SQL_CACHE|SQL_NO_CACHE], por exemplo:
SELECT SQL_CACHE id, name FROM students;
SELECT SQL_NO_CACHE id, name FROM students;
As variáveis de sistema responsáveis por comandar a cache de consultas podem ser encontradas
através do comando:
SHOW VARIABLES LIKE ‘%QUERY_CACHE%’;
3.4 Particionamento
O particionamento permite distribuir porções de tabelas individuais por um sistema de ficheiros.
Várias partes de uma tabela são guardadas como tabelas separadas em diferentes sítios. A forma
como um utilizador decide fazer a partição da tabela é denominada de função de particionamento. A
função é associada a um certo tipo de partição que o utilizador escolhe.
Os tipos de partição disponíveis no MySQL são:
• Range
• List
• Hash
• Key.
Para a demonstração dos tipos de partição existentes, considere-se a seguinte tabela:
A tabela utilizada na demonstração desta funcionalidade é a seguinte: CREATE TABLE students (
id INT PRIMARY KEY,
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
birthday DATE NOT NULL,
average INT );
• Range partitioning: particiona a tabela de acordo com um intervalo de valores.
29
PARTITION BY RANGE( YEAR(birthday) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990)
);
• List partitioning: semelhante à partição por range mas a selecção da partição é feita com
base na igualdade dos valores da coluna com os valores listados.
PARTITION BY LIST(average) (
PARTITION goodStudents VALUES IN (15,16,17,18,19,20),
PARTITION badStudents VALUES IN (0,1,2,3,4,5,6,7,8,9),
PARTITION averageStudents VALUES IN (10,11,12,13,14)
);
• Hash partitioning: particiona a tabela com base num valor retornado por uma função
definida pelo utilizador que, actua sobre um dos atributos do registo.
PARTITION BY HASH(id)
PARTITIONS 4;
• Key partitioning: Semelhante à partição por hash mas ao invés de a função hash ser dada
pelo utilizador, a função hash para a partição por key é disponibilizada pelo MySQL.
PARTITION BY KEY(id)
PARTITIONS 10;
30
4 Indexação e Hashing
4.1 Introdução
Muitas consultas efectuadas numa base de dados, referenciam apenas uma parte dos registos
existentes na tabela. Idealmente, o sistema deveria conseguir localizar esses registos directamente
sem percorrer todos os registos dessa tabela. Neste sentido, a utilização de índices tem como
objectivo acelerar as operações de pesquisa, à semelhança do que acontece por exemplo com um
índice de um livro.
Um índice trata assim de uma ferramenta que permite encontrar os registos pretendidos de uma
forma eficiente. Assim, a velocidade e eficiência com que o servidor consegue obter os dados de um
ficheiro depende em larga escala da informação que o sistema tem dos dados que estão
armazenados. Por exemplo, o sistema MySQL consegue mais rapidamente encontrar os dados que
estão armazenados em registos de tamanho fixo, uma vez que não tem de o determinar em tempo de
execução. Neste sentido, e como se pôde constatar no capitulo anterior, a storage engine MyISAM
consegue formatar os dados de forma a conter tipos de dados de tamanho fixo, de forma eficiente,
permitindo efectuar inserções e remoções como uma boa performance.
Apesar da utilização de índices melhorarem a leitura dos dados do disco, nem sempre é
aconselhável a utilização de índices, nomeadamente em tabelas com poucos registos ou em
consultas que obtêm a maior parte dos registos de uma tabela. Nestas situações a leitura sequencial
dos dados de uma tabela é mais eficiente que a utilização de índices.
31
4.2 Indexação em MySQL
Para uma abordagem mais intuitiva da utilização de índices em MySQL, vamos começar por
imaginar que temos um conjunto de informação relativa a actores. Actores são pessoas e por isso
possuem um número de bilhete de identidade, nome, idade e ainda o respectivo salário:
Tabela 2 Informação relativa a Actores
Nº Bilhete de Identidade Nome Idade Salário
12345678 maria 40 3000,50
46897878 manuel 56 9000,56
12868493 antónio 34 400,56
46893456 manuel 26 1234,56
Note-se que a informação não está ordenada por nenhum campo da tabela. Intuitivamente pode
pensar-se que ao definir uma chave primária, o servidor organiza a informação tendo em conta o
campo em questão, mas não. Vamos por isso pensar que neste caso o sistema irá organizar os
registos da forma mais conveniente, tendo em conta as operações de consulta, inserção e remoção.
Se assumirmos que pretendemos pesquisar os actores com nome igual a “Manuel”, rapidamente
se conclui que o servidor efectuou um conjunto de operações ineficientes:
1. Todos os registos da tabela foram carregados para memória;
2. Uma vez que não existe nenhum critério de ordenação para a coluna em questão, o
servidor não possui nenhum método para eliminar os registos que não satisfazem a
cláusula WHERE. Neste sentido tem de percorrer todos os registos da tabela.
mysql> select * from actor where nome ='manuel';
+----------+--------+-------+---------+
| n_bi | nome | idade | salario |
+----------+--------+-------+---------+
| 46897878 | manuel | 56 | 9000.56 |
| 46893456 | manuel | 26 | 1234.56 |
+----------+--------+-------+---------+
32
mysql> explain select * from actor where nome = 'manuel';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00040200 | select * from actor where nome = 'manuel' |
+----------+------------+-------------------------------------------+
1 row in set (0.00 sec)
Verifica-se pela coluna type = ALL, que toda a tabela foi percorrida, tratando-se por isso de
uma operação ineficiente. Para contornar este problema, podemos ordenar a informação referente ao
campo que estamos a restringir na cláusula WHERE:
Tabela 3 Lista ordenada com os nomes dos actores
Nº Bilhete de Identidade Nome
12868493 António
46897878 Manuel
46893456 Manuel
12345678 Maria
Uma vez que a informação está ordenada, o servidor poderá utilizar o algoritmo de pesquisa
binária. Este algoritmo retornará os registos correctos de uma forma mais eficiente que a enunciada
inicialmente, uma vez que não precisa de percorrer todos os registos da tabela.
O último cenário ilustra de uma forma superficial como funcionam os índices. Deste modo, um
índice permite ao servidor de uma forma eficiente de comparar apenas determinados registos dado
um critério de selecção. Tendo em conta a última situação, podemos visualizar a criação de um
índice no sistema em estudo:
mysql> create table if not exists actor
-> (
-> n_bi char(8),
-> nome varchar(20),
33
-> idade integer,
-> salario float,
-> primary key(n_bi),
-> index(nome)
-> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 1 | 0.00037400 | explain select * from actor where nome = 'manuel' |
+----------+------------+---------------------------------------------------+
1 row in set (0.00 sec)
Rapidamente se conclui que a consulta foi mais eficiente com a utilização de um índice no
campo nome. O estilo de índice ilustrado na tabela 3 é denominado por index sequential access
method. A storage engine MyISAM utiliza este tipo de índice mas de uma forma um pouco mais
complexa, onde os registos são organizados de forma sequencial num dado ficheiro, com o
denominado índex record que consiste num apontador para o(s) registo(s) que possuem o valor de
acordo com a chave de pesquisa.
Ao invés a storage engine InnoDB tem um comportamento diferente e que será abordado já de
seguida.
4.2.1 Índices Aglomerados (Clustering Indices) e Índices
Secundários
Até este ponto abordámos o método ISAM (index sequential access method) que vai de encontro
ao tipo de índices utilizado em MyISAM. Como já foi referido este tipo de índice ordena os campos
índice e não os registos da tabela, delegando assim no registo pivot a função de fornecer um
apontadador para o registo em si. Este tipo de organização é denominado de Indice não Aglomerado
(NonCluster index), uma vez que os dados não estão ordenados em disco pela respectiva chave.
Tal como foi analisado no capitulo anterior, o sistema MySQL oferece outros mecanismos de
armazenamento (storage engines) e que possuem um comportamento diferente no que se refere a
indices. Assim, InnoDB utiliza uma organização denominada de Indice Aglomerado (Clustered
Index). Este tipo de organização obriga que cada tabela possua uma unica chave primária; e é com
34
base no(s) campo(s) correspondente(s) à chave primária que os registos são organizados/ordenados
em disco.
Com base nesta filosofia, cada tabela apenas poderá ter um único Indice Aglomerado. Desta
forma todos os índices que forem criados à priori serão construídos tendo por base o índice da
chave primária, e serão denominados de índices secundários. Os índices secundários em tudo são
semelhantes aos índices não aglomerados, e neste caso cada registo pivot será composto pelo índice
aglomerado e pela chave do índice secundário.
A principal vantagem da utilização da máquina InnoDB remete para a utilização de índices
aglomerados, ou seja nestas tabelas as consultas por chave primária serão bastante rápidas.
É bastante importante que se perceba que os índices secundários construídos sobre um índice
agomerado não são a mesma coisa que um índice não aglomerado, como se pode verificar tanto
pelo tempo de execução, como pelo número de tuplos examinados pelo servidor:
• Utilização do índice na máquina MyISAM
mysql> create table if not exists actor
-> (
-> n_bi char(8),
-> nome varchar(20),
-> idade integer,
-> salario float,
-> primary key(n_bi),
-> index(nome)
-> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.13 sec)
mysql> explain select * from actor where nome = 'manuel';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | actor | ref | nome | nome | 23 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 1 | 0.00043300 | explain select * from actor where nome = 'manuel' |
+----------+------------+---------------------------------------------------+
1 row in set (0.00 sec)
35
• Utilização do índice na máquina InnoDB
mysql> create table if not exists actor
-> (
-> n_bi char(8),
-> nome varchar(20),
-> idade integer,
-> salario float,
-> primary key(n_bi),
-> index(nome)
-> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)
mysql> explain select * from actor where nome = 'manuel';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | actor | ref | nome | nome | 23 | const | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 1 | 0.00046100 | explain select * from actor where nome = 'manuel' |
+----------+------------+---------------------------------------------------+
1 row in set (0.00 sec)
Desta forma, em circunstâncias erradas um índice aglomerado pode ter um impacto negativo na
performance do sistema.
Outra aspecto negativo dos índices aglomerados, mas pouco comum, sucede quando existe
alteração da chave primária de um tuplo:
1. Alterar o tuplo em si;
2. Determinar a nova chave primária para o tuplo em questão;
3. Realocar todas as entradas guardadas, tendo em conta a nova posição do tuplo;
4. Actualizar os índices secundários que apontem para esse tuplo;
36
4.3 Conceitos Básicos de Indexação
4.3.1 Índices Parciais
Tal como foi referido até agora, a utilização de indices permite obter uma melhor performance
na realização de determinadas consultas à base de dados. No entanto, por vezes em determinadas
pesquisas seria mais útil se tivessemos um índice aplicado a parte do índice. É neste sentido que
existem os índices parciais.
Os índices parciais pode ser aplicados a campos do tipo CHAR, VARCHAR e TEXT, sendo a sua
sintaxe:
ALTER TABLE actor ADD INDEX l_nome(3);
Utilizando índices parciais, faz com que se obtenha um ficheiro de índices mais pequeno e ocupe
por isso menos espaço físico. Para além disto, é também mais rápido a indexação do registo na
totalidade.
4.3.2 Índices Multi-Coluna
Tal como a maior parte dos sistemas de base de dados, MySQL também permite a criação de
índices compostos por várias colunas, no caso do MySQL até um máximo de 16 colunas.
Criar um índice composto por exemplo com dois campos é totalmente diferente de criar dois
índices individuais. No ultimo caso, o servidor tem de optar por um dos índices (por exemplo
aquele que considere um menor número de linhas), ou até mesmo a recorrer à utilização de um dos
algoritmos de optimização, por exemplo Index Merge Optimization.
Se na tabela actor (que temos vindo a utilizar) se criar um índice sobre os campos nome e a
idade, podemos verificar que:
/* Utilização do Indice */
SELECT * FROM actor WHERE nome = ‘manuel’ AND idade > 30;
/* Utilização do Indice */
SELECT * FROM actor WHERE nome = ‘manuel’ AND
(idade < 30 OR idade > 50);
37
/* Indice não é utilizado */
SELECT * FROM actor WHERE nome = ‘manuel’ OR idade > 30
/* Indice não é utilizado */
SELECT * FROM actor WHERE idade > 30;
4.3.3 Índices Unique e Chaves Primárias
Em determinadas circunstâncias, a definição de um indice não permite apenas melhorar a
performance de uma dada pesquisa, mas também restringir os valores desse campo como é o caso
da cláusula UNIQUE. Assim a cláusula restringe que todos os valores do índice têm de ser distintos
à excepção dos valores NULL. Deste modo um índice UNIQUE não é mais do que um campo chave
primária, mas que permite valores NULL, tanto na storage engine MyISAM como na InnoDB. No
entanto na máquina BDB, a restrição não se aplica aos valores NULL.
A sintaxe para criação de índices UNIQUE é:
CREATE UNIQUE INDEX índex_name [index_type]
ON tbl_name(index_col_name,...);
4.4 Tipos e Estruturas de Indexação
Após a analise de alguns dos conceitos básicos de indexação em MySQL, abordamos de seguida as
várias estruturas de indexação disponíveis. MySQL suporta três estruturas de indexação, sendo elas:
B-Tree, R-Tree e Dispersão, sendo que os dois primeiros são também utilizados na organização de
ficheiros.
A maior parte dos índices em MySQL (PRIMARY KEY, UNIQUE_INDEX e FULLTEXT) são
guardados em B-Tree, à excepção dos índices utilizados em dados espaciais, que recorrem a R-Tree
e as tabelas MEMORY que suportam indices dispersos. Ao contrário do Oracle, MySQL não suporta
bitmaps.
38
4.4.1 B-Tree
A B-Tree ou árvore equilibrada, é o tipo mais comum de índices. A maioria dos Sistemas de
Base de Dados oferecem B-Tree como tipo predefenido de índice. Esta escolha é derivada das
características equilibradas em termos de flexibilidade, tamanho e performance global.
Como o nome implica, B-Tree baseia-se numa estrutura em árvore. Os nós estão organizados e
ordenados de acordo com os valores da chave. Uma B-Tree diz-se equilibrada devido ao facto de
permanecer assim, nos casos de inserção e remoção de nós. A vantagem deste equilíbrio é a
performance bastante aceitável no pior caso.
As árvores equilibradas oferecem uma complexidade O(log n) para uma pesquisa efectuada com
base numa única entrada. Ao contrário das árvores binárias, em que cada nó tem no máximo dois
filhos, as B-Trees têm múltiplas chaves por nó, e não crescem em altura e profundidade, tão
rapidamente como as árvores binárias.
Trata-se assim do conceito já explorado e analisado durante as aulas da disciplina. Neste sentido
é desnecessário estar a ilustrar novamente o funcionamento dos índices B-Tree. No entanto de
forma a sintetizar um ponto em análise, segue um esquema ilustrativo da estrutura de uma B-Tree
retirado de uma das bibliografias deste documento:
Figura 2 Índice B-Tree numa tabela com índices não aglomerados.
39
4.4.2 R-Tree (Índices Espaciais)
O motor MyISAM suporta a criação de indices do tipo R-Tree. A estrutura R-Tree não é mais do
que uma estrutura que permite representar dados espaciais. Tipos de Dados espaciais correspondem
a coordenadas geográficas ou a dados a três dimensões.
A nível de organização a estrutura R-Tree não é mais do que uma árvore B-Tree, mas como uma
implementação diferente ao nível do indice.
Uma aplicação bastante intuitiva e utilizada na bibliografia deste tema pode ser consultada aqui.
A sintaxe para criação de índices R-Tree, consiste:
CREATE TABLE tbl_name (field GEOMETRY NOT NULL, SPATIAL INDEX(field))
ENGINE=MyISAM;
CREATE SPATIAL INDEX sp_index ON tbl_name (field);
4.4.3 Índices Dispersos (Hash Function)
Para além dos índices B-Tree, outro indice bastante conhecido e utilizado na implementação de
indices em vários sistemas de base de dados são os índices dispersos ou índices baseados em hash.
Por definição hash implicar o par chave / valor e consequentemente uma tabela de hash consiste
numa colecção destes vários pares. Uma função de hash corresponde ao mapeamento que a uma
dada chave faz corresponder um buckets. Desta forma o conceito de índice disperso em MySQL, a
nível de organização é em tudo igual ao efectuado no Oracle.
Deste modo ambos os motores InnoBD e MEMORY suportam a implementação de índices
dispersos, no entanto apenas MEMORY suporta índices dispersos por omissão. Enquanto que
InnoDB implementa um tipo especifico de hashing, nomeadamente adaptive hash índex, que
implica que não se tenha muito controlo sobre a utilização dos mesmos. Por exemplo, se houver um
dado valor que é acedido com muita frequência, o próprio sistema constrói um índice hash para
esses valores.
40
Este tipo de índice permite consultas muito rápidas da ordem dos O(1) a menos que se utilize
uma função de hash desadequada.
4.4.4 Indices FULLTEXT
MySQL suporta também a indexação denominada de FULLTEXT. Encontra-se apenas disponível no
motor MyISAM.
Este tipo de índice é normalmente utilizado para retornar a localização de uma palavra especifica
num determinado campo de texto (CHAR, VARCHAR, TEXT). Os dados do índice são assim
guardados de uma forma normalizada / condensada do texto actual através de stepwords. Isto faz
com que o índice possua uma entrada para cada palavra e que cada entrada possua um apontador
para os dados em si e respectivas estatísticas que permitem acelerar o algoritmo de pesquisa.
A sintaxe para criação de índices FULLTEXT:
CREATE FULLTEXT INDEX índex_name ON tbl_name(index_col_name,..);
Contudo a elaboração de perguntas, difere um pouco dos indices até agora analisados, uma vez
que a utilização do índice FULLTEXT requer o uso da função MATCH(), por exemplo:
CREATE TABLE IF NOT EXISTS filme
(
titulo varchar(20),
ano year,
realizador char(8),
descricao varchar(255),
primary key(titulo),
foreign key (realizador) REFERENCES realizador(n_bi)
fulltext (titulo,descricao)
)ENGINE=’MyISAM’;
/*Permite pesquisar os filmes que */
SELECT * FROM filme WHERE MATCH(titulo,descricao)
AGAINST (‘comedia’ IN NATURAL LANGUAGE MODE);
41
4.5 Manutenção de Indices
Ao longo do tempo, uma tabela sofre muitas alterações, alterando assim a eficácio dos respectivos
índices, devido por exemplo a estatísticas imprecisas. Desta forma, para se optimizar os índices de
uma tabela basta efectuar o comando OPTIMIZE TABLE, que irá reconstruir os índices, actualizando
as estatísticas. O comando apenas deverá ser utilizado nas storage engine MyISAM, InnoDB e
Archieve.
No motor MyISAM, o comando OPTIMIZE TABLE funciona da seguinte forma:
1. Se os ficheiros de índices não tiverem ordendados, estes são reordenados;
2. Se as estatísticas não estão actualizadas, estas são actualizadas.
3. Caso existam tuplos danificados, a tabela é reparada.
Enquanto que no InnoDB o comportamento é ligeiramente diferente, pois é necessário activar
uma das seguintes opções: --skip-new ou --safe-mode. E posteriormente o comando OPTIMIZE
TABLE é directamente mapeado em ALTER TABLE, que reconstrói a tabela, actualiza as estatísticas
e liberta espaço já não utilizado.
42
5 Processamento e optimização de
programas
Neste capítulo iremos expor a forma como o sistema MySQL trata as optimizações e quais os
algoritmos que utiliza. Será também discutida a importância dos planos de avaliação de consultas.
Mas por forma a compreender como uma consulta em MySQL pode ser melhorada, é
conveniente perceber primeiro como esta é executada. Assim segue um protocolo de execução de
uma consulta MySQL:
1. O utilizador envia a consulta em SQL para o servidor e aguarda a resposta.
2. O servidor consulta a cache e se encontrar a consulta devolve o resultado guardado em
cache, caso contrário passa a consulta para o parser (Conceito de Query Cache abordado
no terceiro capitulo).
3. O servidor faz o parse, pré-processamento e optimiza a consulta SQL para um plano de
execução.
4. O motor de execução de consultas executa o plano efectuando chamadas ao motor de
armazenamento através da sua API.
5. O resultado é transmitido ao utilizador.
A figura seguinte transmite de uma forma mais intuitiva o plano enumerado:
43
Figura 3 Protocolo de Execução de uma consulta em MySQL
5.1 Optimizador de Perguntas
Consoante os detalhes das tabelas, colunas, índices e as condições da cláusula WHERE, o
optimizador do MySQL considera várias técnicas para tornar as pesquisas mais eficientes. Uma
consulta numa tabela de grande dimensão pode ser executada sem ter que percorrer todos os
registos da tabela, uma junção que envolva várias tabelas pode ser executada sem fazer a
combinação de todas as linhas. O conjunto de operações que o optimizador escolhe para executar as
consultas de forma mais eficiente é chamado de plano de execução de consultas.
5.1.1 Configuração do Optimizador
O papel do optimizador de consultas é encontrar um bom plano de execução para cada consulta.
Como a diferença de performance entre um bom e um mau plano pode resultar em várias horas/dias
a maior parte dos optimizadores fazem uma pesquisa exaustiva por um plano óptimo entre todos os
possíveis. Por exemplo para consulta de junção o número de planos investigados cresce
exponencialmente com o número de tabelas.
44
Um método de optimização mais flexível consiste em permitir a configuração do rigor do
optimizador por parte do utilizador. O comportamento do optimizador pode ser regulado através das
duas variáveis seguintes:
• optimizer_prune_level: variável que diz ao optimizador para saltar certos planos de
execução com base em estimativas do número de linhas acedidas para cada tabela. Este
tipo de estimativa reduz bastante o tempo de compilação das consultas. Por defeito este
modo está sempre activo (optimizer_prune_level = 1), sendo no entanto, possível
desactivar este modo (optimizer_prune_level = 0) correndo o risco de aumentar o
tempo de compilação.
• optimizer_search_depth: variável que define a distância que optimizador deve
percorrer num plano de execução para melhor avaliar o plano. Valores muito grandes
podem levar a dias de compilação da consulta, caso contrário com valores pequenos a
mesma consulta pode ser compilada em minutos. Para deixar que o optimizador se
encarregue do valor desta variável o seu valor deve ser instanciado a zero
(optimizer_search_depth=0).
Para consultar os valores das variáveis do optimizador podemos usar o comando:
SHOW VARIABLES LIKE ‘%optimizer%’;
5.1.2 Optimização de Consultas no InnoDB
Para optimizar consultas em tabelas InnoDB devem ser criados os índices apropriados para cada
tabela:
• Como cada tabela tem uma chave primária (mesmo que esta não tenha sido especificada)
é importante especificar um conjunto de chaves primárias para colunas que tenham maior
relevância em consultas.
• Não especificar muitas colunas ou colunas de grande dimmensão para chaves primárias
porque esses valores serão duplicados para cada índice secundário.
• Não especificar índices secundários para cada coluna porque cada consulta utiliza apenas
um índice.
45
• Declarar colunas de índices que não tomam valores nulos como NOT NULL na criação da
tabela. O optimizador consegue determinar qual o melhor índice a utilizar numa consulta
sabendo se cada coluna toma valores nulos.
• Se houver recorrências de consultas para tabelas que são raramente alteradas, permitir o
uso da cache.
5.2 Ferramentas de Optimização
5.2.1 Analyse Table
A função do analyse é guardar/analisar a distribuição de chaves para determinar a ordem pela
qual as tabelas devem ser juntas, assim como decidir que índices usar para uma determinada tabela.
Para analisar uma tabela com o analyse usamos o comando:
ANALYZE TABLE TABLE_NAME;
5.2.2 Benchmark
Ferramenta utiliza para calcular a velocidade de execução de uma dada expressão/função.
Para calcular a velocidade com o benchmark utilizamos o comando:
SELECT BENCHMARK(LOOP_COUNT,EXPRESSION);
Por exemplo:
SELECT BENCHMARK(100000,1+1);
SELECT BENCHMARK(1000,(SELECT id FROM STUDENTS));
5.2.3 Explain
Quando é precedido de um SELECT o comando EXPLAIN, o optimizador devolve informação
relativa ao plano de execução da consulta, ou seja, é explicado como será processado o SELECT
(incluindo informação de como seriam feitas as junções das tabelas). Por exemplo:
EXPLAIN SELECT * FROM actor WHERE nome = ‘manuel’;
46
Resultado devolvido
O EXPLAIN devolve uma linha de informação para cada tabela usada no comando SELECT. As
tabelas são listadas no resultado pela ordem em que o MySQL as consultaria enquanto processaria a
consulta. A tabela devolvida pelo EXPLAIN contem as seguintes colunas:
• Id: identificador da consulta.
• Select type: tipo de SELECT.
- SIMPLE: selecção sem sub-perguntas.
- PRIMARY: selecção externa.
- UNION: segunda ou posteriores selecções numa união.
- DEPENDENT union: segunda ou posteriores selecções numa união, dependente de
uma consulta externa.
- UNION RESULT: resultado de uma união.
- SUBQUERY: primeira selecção numa subconsulta.
- DEPENDENT SUBQUERY: primeira selecção numa subconsulta, dependente de
uma consulta externa.
- DERIVED: tabela derivada SELECT.
- UNCACHEABLE SUBQUERY: subconsulta para a qual o resultado não pode ser
guardado em cache e deve ser reavaliada para cada registo da consulta externa.
- UNCACHEABLE UNION: semelhante a Uncacheable Subquery mas para a
segunda ou posteriores selecções numa união.
• Table: tabela referente à linha.
• Type: tipo de junção.
- system: tabela com uma só linha.
- const: a tabela tem no máximo uma linha correspondente à condição WHERE. Como
existe apenas uma linha os valores são tratados como constantes.
- eq_ref: é o melhor tipo de junção possível. É lida uma linha da tabela para cada
combinação de linhas das tabelas anteriores, usado quando todas as partes de um
índice são usadas pela junção e o índice é chave primária ou único e não nulo.
- ref: são lidas todas as linhas que correspondem a valores de índices da tabela para
cada combinação de linhas da tabela anterior.
- fulltext: a junção é feita usando um índice FULLTEXT.
47
- ref_or_null: junção do tipo ref utilizado para processar subconsultas. Efectua uma
pesquisa extra por registos que tenham valores nulos.
- index_merge: este tipo de junção utiliza o método índice merge. A coluna Key
contém a lista de índices usados e Key_len contem a lista dos tamanhos das
maiores Keys usadas.
- unique_subquery: tipo substituto do ref em algumas subconsultas com a cláusula
IN.
- index_subquery: semelhante ao anterior, mas usado para índices não únicos.
- range: são obtidas linhas que respeitem um determinado intervalo, utilizando um
índice para fazer a selecção.
- index: semelhante a all mas apenas a árvore de índices é percorrida.
- all: é feito um full scan para cada combinação de linhas das tabelas anteriores.
• Possible keys: índices que podem ser escolhidos para referenciarem a tabela.
• Key: chave escolhida.
• Key_len: comprimento da chave escolhida.
• Ref: colunas/constantes comparadas com a Key escolhida.
• Rows: estimativa do número de registos a aceder.
• Extra: informação adicional relevante ao processamento da consulta.
- Distinct: são procurados valores distintos. A pesquisa pára quando encontrar a
primeira ocorrência desse valor.
- Full scan on Null key: para casos de optimização de uma subconsulta quando o
optimizador não consegue usar index-lookup.
- Impossible Having: a cláusula Having é sempre falsa e não selecciona nenhuma
linha.
- Impossible Where: semelhante à cláusula Impossible Having.
- Impossible Where noticed after Reading const tables: foram lidas todas as tabelas
cosnt e verificou-se que a cláusula Where é sempre falsa.
- No matching row in const table: para uma consulta com uma junção havia uma
tabela vazia ou uma tabela sem linhas a satisfazerem a condição de unique índex.
- No tables used: a consulta não tem cláusula FROM.
48
- Not exists: foi feita uma optimização Left Join e não vão ser examinadas mais
linhas da tabela após encontrar uma linha que satisfaça o critério Left Join.
- Range checked for each record (índex map: N): não foi encontrado um bom índice
para usar. Encontrou no entanto, após conhecer os valores das tabelas anteriores,
alguns índices que podem ser usados.
- Scanned N databases: número de pesquisas por directoria efectuadas no
processamento de uma expressão de tabelas Information_Schema.
- Select tables optimized away: contém apenas funções de agregação que foram
resolvidas através do uso de um índice e sem cláusula GROUP BY.
- Skip_open_table: Os ficheiros da tabela não precisam de ser abertos. A informação
para a expressão está disponível através de um scan na directoria da base de dados.
- Open_frm_only: É apenas necessário aceder ao ficheiro .frm da tabela.
- Open_trigger_only: É apenas necessário aceder ao ficheiro .TRG da tabela.
- Open_full_table: É necessário aceder aos ficheiro .frm, .MYD e .MYI.
- Using filesort: O MySQL tem de recorrer a um passo extra para descobrir como
obter as linhas de forma ordenada.
- Using índex: A informação da tabela é obtida usando apenas informação disponível
na árvore de índice, sem haver a necessidade de efectuar um seek adicional para ler
a linha.
- Using index for group-by: semelhante ao Using index. Significa que o MySQL
encontrou um índice que pode ser usado para obter todas as colunas de uma
expressão GROUP BY ou DISTINCT sem serem necessários acessos ao disco extra.
- Using join buffer: As tabelas das junções anteriores são lidas em várias porções
para o join buffer e as suas linhas são depois usadas a partir do buffer para efectuar
a junção com a tabela corrente.
- Using sort_union (...), Using union (...), Using intersect (...): Indica como os index
scans são fundidos (merged), para ao tipo de junção index_merge.
- Using temporary: O MySQL tem de criar uma tabela temporária em memória para
guardar o resultado, de maneira a poder resolver a expressão.
- Using where: É usada uma cláusula WHERE para restringir as linhas a comparar com
a próxima tabela.
- Using where with pushed condition: É aplicado apenas a tabelas NDBCLUSTER.
Significa que o MySQL Cluster está a usar a optimização Condition Pushdown de
49
modo a aumentar a eficiência de uma comparação directa entre uma coluna não
indexada e uma constante.
5.3 Estimativas do custo de Consultas
A estimativa do custo das consultas pode ser feita contando o número de acessos a disco. Para
tabelas pequenas, é possível encontrar um registo num único acesso a disco. Para tabelas maiores é
possível fazer a estimativa (usando os índices de b-trees) do número de acessos pela seguinte
fórmula:
No MySQL um bloco de índice tem normalmente 1024 bytes e um apontador de dados 4 bytes.
Para uma tabela com 500mil registos temos uma estimativa de:
acessos a disco.
5.4 Algoritmos de Junção
5.4.1 Nested Loop
O algoritmo consiste em ler as linhas da primeira tabela uma de cada vez num ciclo, passando
cada linha para um nested-loop que processa a tabela seguinte da junção. Este processo é repetido
enquanto houver tabelas para juntar.
Assumindo uma junção entre 3 tabelas t1, t2 e t3 descritas em baixo:
Tabela Join Type
T1 Range
50
T2 ref
T3 ALL
O comportamento do algoritmo seria o seguinte:
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}
5.4.2 Nested Loop por bloco
Utiliza um buffer de linhas lidas em ciclos exteriores para reduzir o número de vezes que uma
tabela é lida em ciclos interiores.
Para a junção das tabelas descritas anteriormente o comportamento do algoritmo seria:
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
51
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
5.5 Optimização de Junções
Quando é feita uma consulta que utilize várias tabelas o optimizer fica encarregue de verificar
qual a melhor maneira de fazer a junção das tabelas (é melhor a que devolver menos linhas), para
isso é preciso calcular todas as maneiras possíveis de juntar as tabelas.
Para n tabelas temos combinações possíveis de junção.
No entanto se souber à partida que uma junção é mais vantajosa podemos forçar o MySQL a usá-
la com o comando STRAIGHT_JOIN.
5.6 Optimização de Operações
De forma a aumentar a performance podemos ver-nos tentados a sacrificar a legibilidade das
consultas, no entanto, esse processo é desnecessário uma vez que o MySQL efectua optimizações
semelhantes automaticamente. Algumas das optimizações realizadas pelo MySQL são listadas em
seguida:
• Remoção de parênteses desnecessários.
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
• Resolução de constantes:
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
• Remoção de condições sob constantes:
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
52
-> B=5 OR B=6
• A avaliação de expressões constantes usadas por índices é avaliada apenas uma vez.
• COUNT(*) numa tabela sem cláusula WHERE é obtido directamente da informação da
tabela (no caso das tabelas MyISAM e MEMORY). Também é o caso para expressões NOT
NULL quando usadas apenas numa única tabela.
• Detecção de expressões inválidas. O MySQL detecta antecipadamente que certas
expressões SELECT são impossíveis e não devolve resultados.
• Se não houver cláusula GROUP BY ou funções de agregação (MIN(), MAX(), COUNT()…) é
feita uma fusão do HAVING com o WHERE.
• Para cada tabela de uma junção é construído um WHERE mais simples para permitir uma
rápida avaliação desta cláusula.
• As tabelas vazias ou com apenas uma linha são lidas primeiro. São exemplos:
SELECT * FROM actor WHERE n_bi = 12345678;
A melhor combinação de junções de tabelas é encontrada combinando todas as possibilidades.
Se todas as cláusulas ORDER BY e GROUP BY forem provenientes da mesma tabela, esse deverá ser a
primeira usada na operação de junção.
Caso exista cláusulas ORDER BY e GROUP BY diferentes, ou o GROUP BY ou ORDER BY têm colunas
de outras tabelas que não a primeira da junção é criada uma tabela temporária (é possível definir
uma tabela temporária em memória usando a opção sql_small_result).
• Cada índice da tabela é estudado e é escolhido sempre o melhor a não ser que o
optimizador acredite que seja mais eficiente fazer um full scan à tabela.
• Antes de ser exportado o resultado de cada linha é feita uma comparação com a cláusula
HAVING e são ignoradas linhas.
53
6 Gestão de transacções e controlo de
concorrência
6.1 Introdução
A definição de transacção remete o conceito de unidade de trabalho a efectuar numa base de dados.
Esta é constituída por várias queries, onde determinados valores da base de dados podem ser
alterados, adicionados ou apagados. Deve ser vista como um todo e só fará sentido se todas as
queries forem concluídas de forma atómica, consistente, isolada e durável, ou seja o denominado
conceito de ACID que remete para os seguintes requisitos:
• Atomicidade:
Uma transacção deverá ser executada integralmente, sendo que apenas é concluída se todas
as operações o forem, ou não é dada como concluída caso alguma das operações falhe
(conceito all or nothing).
• Consistência:
Após a conclusão da transacção a base de dados deverá manter a integridade dos dados,
não violando qualquer regra do desenho da base de dados.
• Isolamento:
Uma transacção não deve em qualquer momento ser alvo de operações que interajam com
tuplos que tenham sido “violados” após o começo da transacção em curso. Todas as
alterações a esses mesmos tuplos são obrigatoriamente alterados antes do inicio da
transacção em curso ou após a sua conclusão.
54
• Durabilidade:
Após a conclusão de uma transacção as suas alterações têm que ser reflectidas na base de
dados, de modo a que as próximas leituras já apresentem tais alterações.
No que respeita a transacções o sistema MySQL garante:
• Transacções ACID;
• Operações de commit e rollback;
• Chaves estrangeiras;
• Integridade referencial;
• Bloqueio a nível dos tuplos
• Níveis personalizáveis de bloqueio;
• Transacções distribuídas (XA);
• Snapshots da base de dados;
• Detecção temporal de deadlocks;
6.2 Níveis de Isolamento
Antes de definirmos os vários níveis de isolamento permitidos pelo sistema em estudo, temos de
definir certos conceitos, nomeadamente:
• Consistent Read (Leitura Consistente): Se tivermos duas transacções activas e se uma
estiver a fazer alterações a determinada tabela, os valores alterados só estarão visíveis à
segunda transacção no momento em que a primeira efectuar a submissão das suas
alterações (commit).
• Dirty Read: Uma transacção pode efectuar a leitura de dados alterados por um segunda
transacção sem esta ter efectuado a submissão dos mesmos.
• Non-repeatable Read: Se uma transacção fizer alterações em termos de [UPDATE] ou
[DELETE] e fizer a submissão destes, uma outra transacção é capaz de fazer a leitura dos
dados alterados.
55
• Phantom Read: Uma mesma leitura numa transacção pode produzir diferentes leituras
em tempos diferentes.
Deste modo, o MySQL permite definir quatro níveis de isolamento transaccional, sendo estes:
• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
O nível pode ser estabelecido a nível global, da sessão corrente ou das próximas utilizando a
sintaxe:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
É possível ainda definir o nível global no inicio do processo utilizando o parâmetro
transaction-isolation = X.
6.2.1 READ UNCOMMITED
Todas as operações de SELECT são efectuadas sem qualquer tipo de bloqueio, este nível
possibilita assim que possam ocorrer dirty reads, podendo ser lido o valor de transacções ainda não
concluídas. Tendo como exemplo, os dados da Tabela 2:
Transacção 1 Transacção 2
/* Query 1 */
SELECT * FROM actor WHERE
nome = ‘maria’;
/* Query 2 */
UPDATE actor SET idade = 50
WHERE nome = ‘maria’;
/* Não foi efectuado commit */
/* Query 1 */
56
SELECT * FROM actor WHERE
nome = ‘maria’;
ROLLBACK;
Na situação representada a transacção 2 não efectuou commit, mas a transacção obtem a nova
informação. No entanto ao efectuar-se rollback na transacção 2, as alterações são revertidas e a
transacção 1 obtem a informação errada.
6.2.2 READ COMMITTED
As leituras são todas efectuadas dentro do um snapshot referente à transacção em curso. Neste
nível de isolamento, deixam de ocorrer dirty reads, dado que apenas são visíveis dados provenientes
de transacções concluídas.
No entanto existe a possibilidade de casos de inconsistent read, quando uma transacção poderá
ler informação de um recurso que já não está disponível ou contem outro valor, após conclusão de
uma transacção concorrente. No entanto, MySQL, mais especificamente InnoDB, opera no modo
consistent read.
6.2.3 REPEATABLE READ
Trata-se do nível de isolamento por omissão da storage engine InnoDB e similarmente ao nível
READ COMMITTED define que cada transacção trabalha dentro do seu snapshot mas obriga a que
todos as leituras repetidas efectuadas durante a transacção devolvam o mesmo valor que a primeira
leitura.
Deste modo evita as inconsistent reads mas causa por outro lado provoca phantom reads caso
inverso do apresentado em que uma leitura de uma tabela, posterior a uma transacção já terminada
poderá não devolver os dados mais recentes, mas sim os inicialmente definidos. No entanto caso se
pretenda obter os dados mais recentes poderá efectuar-se commit na transacção em questão e
efectuar novas pesquisa.
6.2.4 SERIALIZABLE
Trata-se do nível de isolamento por omissão do SQL, permitindo que as várias transacções sejam
executadas em série, sem nenhum tipo de concorrência. Nos casos em que a funcionalidade
57
autocommit não está activa todas as leituras são efectuadas com bloqueio partilhado, [SELECT …
LOCK IN SHARE MODE]
Deste modo são evitados os problemas dos níveis READ COMMITTED e REPEATABLE READ.
6.3 Controlo de Transacções
6.3.1 Autocommit
Esta funcionalidade define que após cada operação seja feito commit automaticamente caso esta
não devolva erro, ou rollback caso devolva. As operações em questão podem ser consultadas no
manual.
Por omissão todas as novas ligações são iniciadas com o autocommit iniciado sendo que poderá
ser alterado via a sintaxe:
Caso esta opção seja activada então deverá ser feito um controlo manual.
6.3.2 Controlo Manual de Transacções
Caso seja decidido especificar quais as operações relativas à transacção a efectuar são
disponibilizados os comandos:
• START TRANSACTION
- Inicia o espectro de uma nova transacção;
- Guarda o estado anterior ao inicio da transacção caso seja necessário reverter
a esse mesmo estado;
• COMMIT
- Finaliza a transacção, concretizando as alterações, tornando-as permanentes;
- O snapshot guardado é fundido com a instância da base de dados;
• ROLLBACK
- Cancela a transacção, recolhendo todas as alterações efectuadas;
- Descarta o snapshot guardado inicialmente.
58
Caso seja iniciado o comando START TRANSACTION irá sobrepor-se à opção autocommit caso
esta esteja activa.
6.3.3 SavePoints
Em MySQL é possível definir também pontos da transacção, originando um snapshot adicional
ao da transacção, sendo possível efectuar um rollback para o savepoint continuando a transacção
com novas operações.
A sintaxe em questão é:
SAVEPOINT identifier
Caso seja efectuado um commit o savepoint é descartado, sendo impossível voltar ao mesmo,
após o término da transacção.
Se desejarmos voltar a um determinado ponto anteriormente guardado com SAVEPOINT, é
necessário efectuar:
ROLLBACK [WORK] TO [SAVEPOINT] identifier
É possível igualmente apagar um savepoint utilizando o comando RELEASE SAVEPOINT:
RELEASE SAVEPOINT identifier
6.3.4 Bloqueios
O MySQL disponibiliza dois tipos de bloqueios: partilhados e exclusivos, que estão associados
às operações de leitura e escrita, respectivamente.
Estes diferem nas permissões que são disponibilizadas às outras transacções:
• Os bloqueios partilhados permitem que várias transacções efectuem leituras
paralelamente sobre um mesmo recurso sem que interfiram umas com as outras;
• Os bloqueios exclusivos bloqueiam todas as transacções excedentes de aceder ao
recurso bloqueado, independentemente das novas transacções tentarem ler e/ou
escrever no mesmo.
Desta forma são disponibilizados os comandos LOCK TABLES para bloqueio das tabelas na
transacção corrente e UNLOCK TABLES que remove o bloqueio imposto pela transacção corrente. Os
comandos são utilizados com a seguinte sintaxe:
59
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
Ao usar-se LOCK TABLES é necessário definir o tipo de bloqueio, nomeadamente: READ ou
WRITE. Ao escolher READ, só leituras serão possíveis de ser efectuadas para qualquer transacção.
No caso do READ LOCAL é possível efectuar inserções na tabela desde que estas não sejam
conflituosas. O bloqueio da(s) tabela(s) estará em vigor até à realização do desbloqueio.
Ao efectuar um bloqueio de escrita para determinada(s) tabela(s) , apenas a própria transacção
poderá efectuar operações de escrita e leitura até à remoção do bloqueio. Se fizer uso da cláusula
LOW_PRIORITY WRITE, outras quaisquer transacções apenas poderão efectuar um bloqueio para
leitura.
A realização de dois bloqueios consecutivos, implica o desbloqueio de todos os bloqueios
realizados anteriormente. Nas situações em que for iniciada uma nova transacção na sessão corrente
será efectuado também será efectuado implicitamente um desbloqueio.
Níveis de Granularidade
Consoante o motor da base de dados (nomeadamente o InnoDB e Falcon) existe ainda suporte
para granularidades a nível dos bloqueios, sendo assim possível efectuar bloqueios a nível de
tabelas, assim como bloqueios ao nível de tuplos / linhas, que são denominados de intention blocks.
Como tal estes também podem ser partilhados ou exclusivos:
Intention share (intenção de lock partilhado)
SELECT ... LOCK IN SHARE MODE
Define o bloqueio de modo exclusivo nas linhas (tuplos) que forem lidos. As outras
sessões podem ler os tuplos bloqueados, mas não os podem modificar até que os dados
sejam submetidos.
60
Intention exclusive (intenção de lock exclusivo)
SELECT ... FOR UPDATE
Esta instrução efectua a leitura dos dados mais recentes disponíveis na base de dados e
activa o bloqueio exclusivo à semelhança da instrução UPDATE.
DeadLocks
Utilizando o motor InnoDB, os deadlocks são detectados automaticamente se o MySQL for
iniciado com as opções por omissão innodb_table_locks = 1 e autocommit = 0.
Nestes casos são efectuados rollbacks de modo a acabar com o problema. A escolha das
transacções a cancelar passa pelo número de linhas afectadas.
Caso os deadlocks sejam causados por utilização indevida do comando LOCK TABLES é
necessário indicar o tempo máximo de “vida” de uma transacção em espera utilizando a variável
innodb_lock_wait_timeout onde é igualmente aplicada a regra anteriormente apresentada.
É ainda disponibilizado o comando SHOW ENGINE STATUS para determinar a causa do último
deadlock.
6.4 Controlos de Concorrência e Multi-Versão
A storage engine InnoDB disponibiliza um controlo de multi-versões a nível dos tuplos de modo
a suportar funções como transacções concorrentes e a operação de rollback. Este é feito
guardando informação sobre versões anteriores das linhas alteradas, esta informação é guardada
numa estrutura denominada de rollback segment.
A informação guardada consiste em três campos essenciais:
• DB_TRX_ID - Identifica a transacção que originou a alteração em questão;
• DB_ROLL_PTR - Um apontador para o log que guarda a informação que foi alterada
(seja um update ou delete);
• DB_ROW_ID - Identificador da linha onde a alteração foi efectuada.
61
Com estes três campos é possível reconstruir a informação préviamente inserida, e repor a base
de dados para um estado anterior. Caso pretenda obter informação mais aprofundada relativamente
à monotorização do InnoDB deverá consultar o manual.
62
7 Suporte para Bases de Dados
Distribuidas
O MySQL disponibiliza várias soluções para garantir a disponibilidade dos dados da base de dados,
em vários servidores, nomeadamente:
• MySQL Replication
• MySQL Cluster
• Distributed Replicated Block Device
• Memcached
• Heartbeat
7.1 Ferramentas de Distribuição
As principais soluções são o MySQL Replication e Cluster, soluções essas que passaremos a
explicar:
7.1.1 MySQL Replication
Esta solução permite que todas as alterações efectuadas numa instância do MySQL sejam
replicadas para outra instância independente. Tipicamente é representada por uma instância
principal (master) para várias instâncias sombra (slaves).
A replicação é efectuada de modo assíncrono sendo que não é garantido que em um dado
momento todas as alterações efectuadas no master já tenham sido replicadas para os slaves.
63
A solução é recomendada para ambientes onde exista um elevado número de leituras e poucas
escritas como webservers ou em ambientes de logging e analise de dados onde podem ser
efectuadas pesquisas na base de dados num dos slaves sem que as escritas no master sejam
afectadas. É aconselhada igualmente para ambientes em que os vários nós estejam separados
geograficamente e em redes dispersas.
Vantagens
• Independente de plataformas ou sistemas operativos;
• As replicações podem ser paradas e reiniciadas a qualquer momento sendo possível
controlar melhores momentos para instâncias sobre redes lentas, instáveis ou com
disponibilidade esporádica;
• É possível replicar dados de um master para qualquer número de slaves;
• As leituras podem ser efectuadas em qualquer slaves sem influenciar o comportamento
do master.
Desvantagens
• Out-of-the-box apenas é possível efectuar escritas no master;
• Não existe garantia que a um determinado momento, a informação entre o master e os
slaves seja consistente.
Replicação
A replicação entre os servidores é efectuada utilizado o mecanismo de logging binário.
O master guarda as alterações efectuadas a nível da transacção no log e por sua vez os slaves
ficam encarregues de ir ler ao log do master as alterações que foram feitas desde a última vez que
foi lido. Os slaves ficam encarregues de analisar o log e aplicar as alterações à sua versão da base de
dados.
Este processo é independente entre os slaves implicando que estes podem não ter os dados
coerentes; podendo até ter períodos sem conexão para o master e retomar a leitura do log de sítios
diferentes.
64
7.1.2 MySQL Cluster
A segunda solução apresentada colmata as falhas da primeira, disponibilizando uma opção
síncrona de replicação entre todas as instâncias da base de dados. Trata-se de uma versão melhorada
do MySQL e oferece alta disponibilidade e redundância com o objectivo para ambientes de
computação distribuída.
Utiliza um motor específico para o objectivo o NDBCLUSTER que não está disponível na versão
standart do MySQL. Desta forma é possível efectuar leituras e escritas a partir de qualquer instância
com a certeza que as alterações são propagadas para todas as instâncias.
Solução recomendada para ambientes que necessitem de elevada disponibilidade, e/ou aplicações
com elevadas operações de escrita comparativamente com as de leitura.
Vantagens
• É possível efectuar leituras e escritas de qualquer um dos nós da base de dados;
• Oferece recuperação automática da base de dados em caso de falha de um dos nós, sendo
que apenas a informação a decorrer no nó que falha poderá ser perdida;
• Alterações efectuadas numa das instâncias são automaticamente propagadas para as
restantes.
Desvantagens
• Apenas disponível em determinadas plantaformas e sistemas operativos;
• Os nós da base de dados devem estar ligados sobre ligações de alta velocidade, se
possível uma ligação LAN de modo a que as propagações de informação sejam feitas
com sucesso.
Funcionamento
O MySQL Cluster tem como objectivo disponibilizar uma base de dados partilhada entre vários
sistemas completamente independentes a nível físico. Como tal o cluster é constituído por um
65
conjunto de computadores que correm os processos necessários para o bom funcionamento do
sistema. Estes são descritos como nodes no MySQL e existem três tipo de nodes:
- Management node: responsável por gerir todos os outros nodes do cluster. Está
encarregue de, entre outros, fornecer as configurações de cada node, ligar ou desligar
outros nodes. Dado o ser perfil administrativo, o cluster obriga que management node
seja iniciado previamente a todos os outros.
- Data Node: responsável por armazenar os dados da cluster; são necessários tantos data
nodes quanto o número de replicações vezes o número de fragmentos.
É possível correr o cluster com apenas um data node, mas obviamente não existirá
redundância em caso de falhar do data node, logo o MySQL Cluster aconselha a um
mínimo de duas replicações.
Nota: Fragmentos são o número de vezes que os dados são possíveis de dividir,
consoante o número de data nodes disponíveis.
Por omissão os dados são guardados em memória e não em disco nos data nodes de
modo a fornecer um serviço mais eficiente, não impossibilitando no entanto o seu
armazenamento em disco.
- SQL Node: responsável por aceder aos dados do cluster nos data nodes. No arranque,
obtém a partir do management node a configuração de data nodes disponíveis.
7.2 Transacções Distribuídas (XA)
O MySQL disponibiliza suporte para transacções distribuídas (XA), ou seja permite a existência
de múltiplas transacções separadas.
Um transacção global consiste em várias transacções singulares, em que para existir commit da
transacção global é necessário que todas as singulares tenham sido efectuadas com sucesso, ou caso
uma falhe, todas falharão. Este facto está de acordo com o standard ACID.
As aplicações que utilizam transacções globais necessitam de um ou mais Resource Managers, e
um Transaction Manager:
• Resource Manager
66
- Disponibiliza acesso a recursos transaccionais; um servidor de base de dados é
visto como um resource manager;
- É necessário que seja capaz de efectuar operações de commit ou rollback sobre
transacções iniciadas pelo resource manager.
• Transaction Manager
- Coordena as transacções que constituem a transacção global;
- Comunica com os resource managers de modo a que efectuem determinadas
transacção e as operações de commit e rollback.
De modo a conseguir concretizar os requisitos ACID o MySQL usa o protocolo two phase
commit (2PC):
• Na primeira fase todas as transacções são “avisadas” para prepararem a operação de
commit, tipicamente isto significa que o resource manager deverá escrever as operações
em memória não volátil, de seguida devem indicar se obtiveram sucesso neste acto, e
caso tal suceda serão estes dados que irão ser usados na segunda fase.
• Na segunda fase o transaction manager indica a todos os resource managers se devem
efectuar commit ou rollback. A decisão do transaction manager passa pelo estado de
todos os resource managers na primeira fase.
Em alguns casos uma transacção global pode usar o protocolo one-phase commit (1PC). Isto
acontece quando o Transaction Manager verifica que a transacção global consiste apenas em uma
transacção, podendo esta efectuar o commit simultaneamente.
As operações devem seguir a sintaxe:
XA {START|BEGIN} xid [JOIN|RESUME]
XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid
XA RECOVER
67
8 Outras caracteristicas do sistema
estudado
8.1 Tipos de Dados
O MySQL suporta um vasto tipo de dados entre eles:
• Numéricos;
• Datas e horas;
• Caracteres;
8.1.1 Tipos Numéricos
São disponibilizados todos os tipos standart incluindo números de tamanho fixo:
• INTEGER (alias INT)
• SMALL INT
• DECIMAL (alias DEC, FIXED)
• NUMERIC
Como extensão ao tipo INT são ainda disponibilizados os tipos TINYINT, MEDIUMINT e BIGINT
de modo a poder utilizar tamanhos mais apropriados a cada cenário. Comparando temos:
68
Figura 4 Extensão do Tipo de Dados INT.
Quando a números reais são disponibilizados:
• FLOAT
• REAL
• DOUBLE PRECISION (alias DOUBLE)
Os tipos FLOAT e DOUBLE representam aproximações numéricas, sendo utilizados 4 bytes para
single-precision e 8 bytes para double-precision. É possível definir no tipo FLOAT e DOUBLE a
precisão da aproximação no número de casas decimais mas não do valor inteiro.
8.1.2 Tipos Temporais
São disponibilizados para representação de valores temporais os tipos:
• DATETIME
• DATE
• TIMESTAMP
• TIME
• YEAR
69
Cada um tem associado um intervalo de valores permitidos, assim como o valor “0” que é
utilizado quando é inserido um valor que o MySQL não consegue representar, este é representado
da seguinte forma:
Figura 5 Formato dos Tipos de dados Temporais
Por omissão não são permitidas datas inválidas sendo que se a base de dados for iniciada no
modo ALLOW INVALID DATES é possível adicionar registos com datas impossíveis.
É possível inserir datas em vários formatos:
• 'YYYY-MM-DD'
• 'YY-MM-DD'
• 'YYYYMMDDHHMMSS'
• 'YYMMDDHHMMSS'
• 'YYYYMMDD'
• 'YYMMDD'
Sendo que cada tipo irá fazer o mapeamento para as suas propriedades.
É possível ainda utilizar a função NOW() ou a CURRENT DATE para obter a data corrente.
Quanto ao tempo é possível utilizar:
• 'D HH:MM:SS.fraction'
• 'HH:MM:SS.fraction'
• 'HH:MM:SS'
• 'HH:MM'
70
• 'D HH:MM:SS'
• 'D HH:MM'
• 'D HH'
• 'SS'
Pode igualmente ser utilizada a função CURRENT TIME para obter a hora corrente.
8.1.3 Tipos de Caracteres
Para armazenamento de texto são disponibilizados os tipos:
• CHAR
• VARCHAR
• BINARY
• VARBINARY
• BLOB
• TEXT
• ENUM
• SET
Os tipos CHAR e VARCHAR são semelhantes, mas diferem no modo como são armazenados e
obtidos, assim como no tamanho máximo, ambos declaram um tamanho máximo no momento da
sua definição sendo que o CHAR tem um tamanho máximo de 255 e o VARCHAR de 65535 (apesar do
ultimo ser o tamanho máximo da tabela, logo este tamanho poderá não estar disponível).
Quando valores CHAR são armazenados, estes são preenchidos à direita com espaços até
atingirem o tamanho especificado; quando são extraídos da tabela, os espaços são removidos.
Os valores de um campo VARCHAR são strings de tamanho variável e que são armazenados
utilizando um (ou dois, consoante o tamanho) byte(s) extra para informação do tamanho da string
guardada.
71
Se foi inserido um valor para uma coluna CHAR ou VARCHAR que exceda o tamanho máximo
especificado, o valor é truncado para o tamanho definido na coluna.
A seguinte tabela ilustra os dois parágrafos anteriores:
Os tipos BINARY e VARBINARY são semelhantes aos tipos CHAR e VARCHAR, mas armazenam
strings binárias, ou seja, contém bytes, ao invés de caracteres. Neste processo de codificação não é
utilizado qualquer encoding.
Um BLOB é um objecto binário de grande tamanho, e que pode armazenar uma quantidade
variável de informação. Este tipo é dividido em TINYBLOB, BLOB, MEDIUMBLOB e LONGBLOB, onde
apenas diferem o tamanho máximo suportado.
Analogamente, o tipo TEXT é dividido em TINYTEXT, TEXT, MEDIUMTEXT e LONGTEXT. As colunas
TEXT são tratadas como caracteres, ao passo que as colunas BLOBS são guardadas como bytes, sem
charset definido e sem parâmetros de ordenação.
O tipo ENUM consiste numa string cujos valores possíveis são especificados explicitamente na sua
definição. Por exemplo:
CREATE TABLE tamanhos (
Name ENUM(‘pequeno’, ‘médio’, ‘grande’)
);
O tipo SET representa um aglomerado na forma de uma string de valores associados a uma
coluna só. Estes podem ter zero ou mais valores, os quais devem ser escolhidos de uma lista de
valores possíveis, especificada quando a tabela é criada. Os valores da coluna são especificados pela
sua sequência, separados por vírgulas.
72
8.2 Suporte de formas Procedimentais
8.2.1 Triggers
Triggers são utilizados para automatização da base de dados e são activados quando é feita uma
alteração à tabela a que estão associados. Podem ser executados antes ou depois da acção que os
activa.
Para criar um trigger executamos o comando:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body
Para remover um trigger executamos o comando:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
8.2.2 Procedimentos e Funções
Uma função calcula o valor de retorno quando chamada na utilização de expressões, como
acontece com funções do sistema (RAND(), DATE(), …).
Para criar uma função executamos o comando:
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION function_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
Para eliminar uma função executamos o comando:
DROP FUNCTION [IF EXISTS] function_name
Um procedimento não retorna qualquer valor e não é utilizado em funções, mas sim chamado
usando o comando CALL.
Para criar um procedimento executamos o comando:
CREATE
[DEFINER = { user | CURRENT_USER }]
73
PROCEDURE procedure_name ([proc_parameter[,...]])
[characteristic ...] routine_body
Para eliminar um procedimento executamos o comando:
DROP PROCEDURE [IF EXISTS] procedure_name
8.3 Suporte XML
Estão disponíveis duas funções que tiram partido da linguagem XML e tecnologia XPath são
elas:
• ExtractValue(xml_frag, xpath_expr)
Esta recebe como argumentos um fragmento de código XML e uma expressão XPath, e
retorna o primeiro nó de texto descendente do(s) elemento(s) especificado(s) no fragmento
que validam a expressão XPath;
Se nenhum nó for encontrado, a função retorna uma string vazia.
• UpdateXML(xml_target, xpath_expr, new_xml)
Recebe como argumentos um fragmento de código XML a alterar, uma expressão XPath
especificando o caminho onde se devem efectuar as alterações e o novo código XML que
deve ser inserido em todas as entradas devolvidas pela query XPath.
É possível ainda obter uma representação da base de dados em código XML utilizando as linhas
de comando mysql e mysqldump com a opção “--xml”.
8.4 Conectores e API’s
Os conectores do MySQL permitem a programas clientes acesso ao MySQL ao passo que a API
disponibiliza acesso a protocolos e recursos. Tanto a API como os Conectores permitem contactar e
executar operações no MySQL a partir de outra aplicação ou linguagem.
Exitem vários conectores desenvolvidos pelo MySQL:
• Connector/OBDC
• Connector/NET
74
• Connector/J
• Connector/MXJ
• Connector/C++
• Connector/C
• Connector/OpenOffice.org
Todas as API’s são desenvolvidas de uma de duas formas:
• Utilizando libmysql que oferece compatibilidade total com o MySQL mas poderá reduzir
a performance.
• Utilizando drivers nativas que são uma implementação do protocolo de rede do MySQL
no sistema/linguagem nativo.
Segue uma lista das diversas API’s e interfaces do MySQL:
Tabela 4 Listagem de API’s e interface do MySQL
Ambiente API Tipo
Ada MySQL Bindings for GNU Ada libmysql C Connector/C Replacement for libmysql
C++
Connector/C++ libmysql MySQL++ libmysql MySQL wrapped libmysql
Cocoa MySQL-Cocoa libmysql D MySQL for D libmysql Eiffel Eiffel MySQL libmysql Erlang erlang-mysql-driver libmysql Haskell
Haskell MySQL Bindings Native Driver hsql-mysql libmysql
Java/JDBC Connector/J Native Driver Kaya MyDB libmysql Lua LuaSQL libmysql .NET/Mono Connector/NET Native Driver Objective Caml MySQL Bindings for OBjective Caml libmysql Octave Database bindings for GNU Octave libmysql ODBC Connector/ODBC libmysql OpenOffice MySQL Connector/OpenOffice.org libmysql Perl
DBI/DBD::mysql libmysql Net::MySQL Native Driver
PHP
mysql, ext/mysqlinterface (deprecated) libmysql mysqli, ext/mysqliinterface libmysql PDO_MYSQL libmysql PDO mysqlnd Native Driver
Python MySQLdb libmysql Ruby
MySQL/Ruby libmysql Ruby/MySQL Native Driver
Scheme Myscsh libmysql
75
SPL sql_mysql libmysql Tcl MySQLtcl libmysql
8.5 Utilizadores e Segurança
No MySQL o controlO de acessos é feito a partir de listas de acessos (ACL’s). Tal como em
outros sistemas de bases de dados é possível dar ou retirar permissões de acesso a utilizadores.
Para isso o MySQL disponibiliza dois comandos de controlo GRANT e REVOKE.
8.5.1 Grant
Comando utilizado para estender privilégios de acesso:
GRANT PRIVILEGE ON OBJECT TO USER;
8.5.2 Revoke
Comando utlizado para retirar privilégios de acesso.:
REVOKE PRIVILEGE ON OBJECT FROM USER;
Tanto no comando GRANT e REVOKE, estão disponíveis:
• [OBJECT]
- TABLE
- FUNCTION.
- PROCEDURE.
• [PRIVILEGE]
- ALL: dá privilégios para tudo excepto GRANT.
- ALTER: permite modificar tabelas.
- ALTER ROUTINE: permite alterar ou remover rotinas.
- CREATE: dá permissão para criar tabelas e bases de dados.
- CREATE ROUTINE: permite a criação de rotinas.
76
- CREATE TEMPORARY TABLES: permite a criação de tabelas temporárias.
- CREATE USER: permite criar, eliminar, alterar o nome e retirar permissões a
utilizadores.
- CREATE VIEW: permite criar e alterar vistas
- DELETE: permite o uso do comando delete.
- DROP: permite remover bases de dados, tabelas e vistas.
- EXECUTE: permite executar rotinas.
- FILE: permite que o utilizador cause o servidor a ler ou escrever ficheiros.
- GRANT OPTION: permite dar ou remover privilégios de outras contas.
- INDEX: permite criar ou remover índices.
- INSERT: permite o uso do comando INSERT.
- LOCK TABLES: permite bloquear tabelas para as quais tenhamos permissão de
SELECT.
- PROCESS: permite ver a lista de processos com o comando SHOW PROCESSLIST.
- RELOAD: permite o uso de operações de flush.
- SELECT: permite o uso do comando SELECT.
- SHOW DATABASES: permite ver a lista de bases de dados com o comando SHOW
DATABASES.
- SHOW VIEW: permite o uso do comando SHOW CREATE VIEW.
- SHUTDOWN: permite o uso do shutdown no mysqladmin.
- SUPER: dá permissões de outras operações administrativas como CHANGE MASTER
TO, KILL, SET GLOBAL, ETC.
- UPDATE: permite o uso do comando UPDATE.
8.6 Ferramentas
8.6.1 MySQL Workbench
Este software fornece um ambiente gráfico para trabalhar com servidores e bases de dados
MySQL. A sua interface divide-se em três áreas de funcionalidade:
77
SQL Development
Nesta vista é possível gerir as ligações a bases de dados tal como executar queries a ligações
estabelecidas utilizando um editor de SQL nativo.
Figura 6 MySQL Workbench – SQL Editor
Data Modeling
Permite criar um modelo da base de dados num ambiente gráfico, disponibilizando wizards para
criação de tabelas, relações, índices, triggers e views.
É possível utilizar ferramentas de reverse e forward engeneering para criar diagramas ER a partir
de instâncias de bases de dados (ou ficheiros SQL) e vice-versa.
78
Figura 7 MySQL Workbench – Data Modelling
Server Administrator
Vista de administração de uma instância MySQL Server, possibilitando alterar todas as
configurações inerentes ao serviço que por norma seria apenas editáveis via linha de comandos.
Disponibiliza igualmente uma fácil edição de utilizadores e privilégios com acessos à base de
dados.
Permite ainda ter uma visão do estado da base de dados e retirar indicadores sobre o seu
comportamento, e recursos utilizados.
79
Figura 8 MySQL Workbench – Administrator Browser
80
Conclusão
Após o estudo e análise do sistema de base de dados MySQL que permitiram a elaboração deste
documento foram muitos os conceitos apreendidos, no âmbito da disciplina de Sistemas de Bases de
Dados, que permitiram entender o funcionamento do mesmo e compará-lo com outros sistemas
como o Oracle. Apesar do objectivo deste documento não ser a comparação entre sistemas, ao
longo do documento foram sendo confrontados aspectos tanto ao nível de outros sistemas quer ao
nível do SQL standard.
De modo a não tornar este documento demasiado teórico, ao longo do mesmo foram
disponibilizados pequenos exemplos que permitiram uma compreensão mais prática e intuitiva de
MySQL. Em alguns pontos foi também dada a devida a atenção ao conceito de performance, pois
trata-se de um conceito bastante importante num sistema de base de dados que manipule grandes
quantidades de informação.
Pode concluir-se assim que o sistema MySQL permite a implementação de sistemas de bases de
dados completos, pois cobre a maior parte dos conceitos e técnicas do SQL standard, oferecendo em
alguns casos algumas extensões ao mesmo, assim como alguma artelharia no que respeita a
optimização de perguntas SQL, no entanto o sistema estudado nas aulas práticas apresenta uma
maior flexibilidade em questões de indexação que MySQL.
81
Referências
[1] http://pt.wikipedia.org/wiki/Sistema_de_gerenciamento_de_banco_de_dados
[2] http://dev.mysql.com/doc/refman/5.5/en/index.html, Manual de Referência MySQL
[3] Cabral, S., & Murphy, K., MySQL Administrator’s Bible, Indiana: Wiley Publishing, Inc., 2009
[4] Schneider, R., MySQL Database Design and Tuning, Sams Publishing, 2005
[5] Kruckenberg, M. & Pipes, J., Pro MySQL, New York: Apress
Recommended