Título

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;

+----------+---------+-------+---------+


+----------+---------+-------+---------+

| 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


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}] ...


[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


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

+----------+--------+-------+---------+


+----------+--------+-------+---------+

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



+----------+------------+---------------------------------------------------+


+----------+------------+---------------------------------------------------+

| 1 | 0.00037400 | explain select * from actor where nome = 'manuel' |

+----------+------------+---------------------------------------------------+


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


-> (

-> n_bi char(8),


-> idade integer,

-> salario float,


-> index(nome)

-> )ENGINE=MyISAM;



+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+


+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | actor | ref | nome | nome | 23 | const | 1 | Using where |

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+



+----------+------------+---------------------------------------------------+


+----------+------------+---------------------------------------------------+


+----------+------------+---------------------------------------------------+


35

• Utilização do índice na máquina InnoDB


-> (

-> n_bi char(8),


-> idade integer,

-> salario float,


-> index(nome)

-> )ENGINE=InnoDB;



+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+


+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | actor | ref | nome | nome | 23 | const | 2 | Using where |

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+



+----------+------------+---------------------------------------------------+


+----------+------------+---------------------------------------------------+


+----------+------------+---------------------------------------------------+


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 t1, t2 combination in join buffer {


send to client

}

}

empty buffer

}

}

}

if buffer is not empty {

51


for each t1, t2 combination in join buffer {


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


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


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

Documents

Título