keys

Antes de começar a escrever suas buscas pelos dados , recomendo você a primeiro ter uma ideia do que é mais importante para seus usuários.
Quais valores e o tamanho de seus subconjunto vão conduzir a escolha do(s) índice(s) . O índice nada mais é que uma forma de acessar
mais rapidamente uma informação específica. Aqui é muito importante perceber que “informação específica” não é “todas as informações”.
E apesar de contribuirem para o acesso a determinados dados, eles vêm com um custo, tanto em armazenamento quanto em processamento. Por isso
a estratégia adotada na criação de um indice deve considerar, por exemplo, que o volume de armazenamento de um indice pode ser muito maior
que o volume de armazenamento do dado que está sendo indexado. Considere os índices em suas estratégias de backup/restore.
E quanto ao processamento? Toda ação de insert/delete/update no banco, refletirá em ajustes nos indices da tabela. E estes, ajustes se aplicam
não só nos indices simples ( que referenciam apenas a coluna alterada) mas também aos indices compostos (compound index) , toda esta alteração ( ou manutenção)
reflete em mais recursos de CPU sendo usados para varrer dados em memória, I/O porque essas atividades consomem log e ainda alguma manutencao de armazenamento
nos arquivos de banco.

A seguir algumas estrategias que podem ajudar na escolha dos seus índices:

– Conhecer as características da base : Se ocorrem muitas modificações no banco de dados ( OLTP ) ou se é um sistema de apoio a decisão (DSS) ou Data
Warehousing (possui normalmente dados apenas de leitura e em grande volume ).

– Conhecer as características das consultas mais executadas : se usam muitos “joins” ou funções para validar dados.
– Identificar as colunas mais utilizadas como filtros,, bem como suas caracteristicas ( o idel sao colunas que nao permitem valores nulos, que possuam valores unicos e que sejam do tipo inteiro)
– Determinar o tipo de indice que pode ser criado ( em geral varia com o sistema de banco de dados usados, mas a grande maioria possui “clustered index” e “nonclustered index”)
– Determinar o armazenamento do indice. De preferencia num grupo de arquivos separados dos dados .

Existem ainda algumas considerações quanto ao Base de Dados :

– Quanto maior a quantidade de indices em uma tabela mais a perfomance em INSERT, UPDATE, DELETE é afetada , porque todos os indices devem ser ajustados de acordo com as alterações nos dados.

– Se possível manter a combinacao de indices com um numero pequeno de colunas .

– Algumas vezes considerar o tempo que otimizador de consulta usa para varrer indices em tabelas pequenas, as vezes, é menos custoso varrer diretamente a tabela de dados e mais, podem ainda nunca
serem usados, apenas consumindo espaco .

– indices em views podem ser uteis quando a view contem agregações, joins ou ambos. ( Depende do Sistema de Banco de dados)

E com relação as consultas :

– Verificar a ordem de uso das colunas indexadas, especialmente as que participam de indices compostos. A coluna usada no WHERE em igual a (=), maior que (>), menor que (<), ou BETWEEN, ou se participar um join , deve ser usada primeiro. Colunas adicionais devem ser ordenadas
com base em seu nível de distinção, ou seja, do mais distinto ao menos distinto.
Por exemplo, se o índice for definido como a, b o índice será útil quando a busca for WHERE a = ‘xxx’ ou WHERE b =’zzzz’ AND a LIKE ‘x%’.
Se a consulta que tivesse pesquisado apenas em b (WHERE b = ‘Jane’), o otimizador de consulta não usaria o índice.

– Prefira consultas que insiram ou modifiquem o máximo de registros possíveis em uma única instrução,
em vez de usar consultas múltiplas para atualizar esses mesmos registros. Ao usar apenas uma instrução, ocorre manutenção otimizada do índice, evitando maior fragmentacao.

federated_select_performancedb

Muitas vezes temos a necessidade de realizar consultas ou até mesmo JOINs com tabelas que estão em outro servidor MySQL/MariaDB. E no nosso cenário não tenos um MySQL Cluster, nem um MySQL Galera Cluster, nem sequer uma replicação, seja isso por qualquer motivo(projetos diferentes, empresas diferentes, etc…).
Para isso o MySQL tem(e faz muito tempo) uma engine específica, a engine FEDERATED. Sim ela permite realizar uma conexão com outro servidor MySQL/MariaDB e executar uma consulta em uma tabela. Mas tenha em mente que a performance vai depender de vários itens, como a velocidade da comunicação pela internet/rede local, quantidade de dados e itens relacionados a performance em geral(estrutura da tabela, índices, engine…).

No exemplo estou usando o MySQL 5.6.28, mas esse mesmo exemplo vai funcionar para o MySQL 5.5 e 5.7, no MySQL 5.1 e 5.0 não testei, se não for isso é muito parecido.

 

Para esse teste, estou usando 2 servidores que vou chamar de mysql56_1 e mysql56_2. O Objetivo é realizar um query que tenha um JOIN com uma tabela que está no mysql56_1 e outra no mysql56_2.

No servidor mysql56_1, tenho o database cidades, com a tabela cidades. Nessa tabela eu tenho o UF e o nome da cidade.
federated_cidades

No servidor mysql56_2, tenho o database estados, com a tabela uf. Nessa tabela eu tenho o UF e o nome do estado.
federated_estados

O objetivo é fazer um select no servidor mysql56_1 e mostrar o nome da cidade e o nome do estado.

Habilitando a engine

A engine FEDERATED não vem habilitada por padrão no MySQL, e a primeira coisa a ser feita é habilitar.

show_engines_sem

A engine já vem instalada, basta habilitar e isso é bem simples, vá até o seu my.cnf(normalmente em /etc/my.cnf) e adicionar a palavra federated dentro da sessão do mysqld, salve e reinicie o serviço do MySQL.

[mysqld]
federated

deferated_show_engines_com

Essa engine só precisa estar habilitada em um dos servidores. Apenas no servidor que a tabela será criada.
No meu exemplo, eu habilitei no mysql56_1.

Criando a tabela

Temos duas opções para criar a tabela FEDERATED, podemos criar um SERVER CONNECTION ou CONNETION.  A diferença é que quando criamos um SERVER CONNECTION, podemos reutilizar a mesma conexão para outras tabelas e a CONNECTION todos os dados da conexão devem ser declarados no comando de CREATE TABLE.

CREATE SERVER

Esse é o modo que eu gosto de utilizar.
A primeira coisa que precisamos fazer é criar um servidor(Não um servidor físico, e sim uma conexão com outro servidor).
Vou criar a conexão no mysql56_1 se conectando no mysql56_2.

CREATE SERVER conn_mysql56_2_estados
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'usr_estados', HOST '192.168.0.21', PASSWORD '123', DATABASE 'estados');

Onde:

conn_mysql56_2_estados: é o nome da conexão, ele será utilizado no momento de criar a tabela FEDERATED.
mysql: Em FOREIGN DATA WRAPPER mysql significa que é uma conexão com outro servidor MySQL, esse valor não deve ser alterado, pois até o momento é a única disponível.
usr_estados: é o nome do usuário no servidor mysql56_2.
192.168.0.21: é o IP do mysql56_2, também pode ser utilizado DNS.
123: é a senha so usuário usr_estados.
estados: é o nome do banco de dados que eu estou me conectando no servidor mysql56_2.

Agora podemos criar a tabela.

CREATE TABLE mysql56_2_uf (
 uf char(2) NOT NULL,
 nome varchar(50) DEFAULT NULL,
 PRIMARY KEY (`uf`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='conn_mysql56_2_estados/uf';

Onde:

FEDERATED: é a engine da nossa “tabela remota”.
conn_mysql56_2_estados: é o nome da conxão criada no comando CREATE SERVER.
uf: é o nome da tabela alvo, ou seja, a tabela no servidor remoto que vamos fazer o link com a tabela mysql56_2_uf.

Atnção! Veja que no momento em que criei a tabela mysql56_2_uf eu criei com a mesma estrutura que da tabela uf do servidor mysql56_2, mas isso não é uma regra, a tabela pode ser criada apenas com as colunas necessárias.

CONNECTION

A outra opção é não criar uma conexão com um servidor, mas sim declarar toda a string de conexão no momento da criação da tabela.

CREATE TABLE mysql56_2_uf (
 uf char(2) NOT NULL,
 nome varchar(50) DEFAULT NULL,
 PRIMARY KEY (`uf`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://usr_estados:123@192.168.0.21/estados/uf';

Onde:

FEDERATED: é a engine da nossa “tabela remota”.
mysql://: é o FOREIGN DATA WRAPPER esse valor não deve ser alterado, pois até o momento é a única disponível.
usr_estados: é o nome do usuário no servidor mysql56_2.
123: é a senha so usuário usr_estados.
192.168.0.21: é o IP do mysql56_2, também pode ser utilizado DNS.
estados: é o nome do banco de dados que eu estou me conectando no servidor mysql56_2.
uf: é o nome da tabela alvo, ou seja, a tabela no servidor remoto que vamos fazer o link com a tabela mysql56_2_uf.

Atnção! Veja que no momento em que criei a tabela mysql56_2_uf eu criei com a mesma estrutura que da tabela uf do servidor mysql56_2, mas isso não é uma regra, a tabela pode ser criada apenas com as colunas necessárias.

Utilizando a tabela FEDERATED

Agora bata utilizar como se fosse uma tabela qualquer do seu servidor.

federated_select_in_federated_table

Vamos ao JOIN!

federated_select_in_federated_table_join

 

Espero que esse tutorial ajude muito vocês!

Gostou? compartilha esse artigo ai :)

Uma informação legal, mais do que legal, importante, mais do que importante, necessária!

Quem nunca perguntou ou foi perguntado sobre qual o tamanho de um banco de dados ou de uma tabela?
Quando as tabelas são MyISAM da para olhar o tamanho dos arquivos, ou se for InnoDB e estiver com o innodb_per_tables habilitada até conseguimos, mas temos que nos conectarmos no servidor, acessar o diretórios e ai olhar os arquivos, depois somar os valores, etc…

Com esses simples SELECTs conseguimos essas respostas.

Verificar os tamanhos dos databases

SELECT table_schema AS 'DB', FORMAT(SUM(TABLE_ROWS), 0) AS 'Linhas', ROUND(SUM(DATA_LENGTH + INDEX_LENGTH )/1024/1024, 3) AS 'MB'
FROM information_schema.TABLES
GROUP BY table_schema 
WITH ROLLUP;

Verificar os tamanhos das tabelas de um database

SELECT TABLE_NAME AS 'Tabela', FORMAT(SUM(TABLE_ROWS ), 0) AS 'Linhas', ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024, 3 ) AS 'MB'
FROM information_schema.TABLES
WHERE table_schema = 'nome do database'
GROUP BY TABLE_NAME
WITH ROLLUP;

Obs: Os valores apresentados podem sofrer variação, principalmente quando o banco não está sendo usado, isso se deve ao fato desses valores serem atualizados quando o mysqld achar mais conveniente, e normalmente ele prefere nos momentos em que não tem concorrência.

Aulas ao vivo sem que você precise sair de casa.

Se tem algo que é extremamente chato de fazer, principalmente quando não existe online de um Banco de Dados, é localizar uma coluna no meio de dezenas de tabelas.

Com esse SELECT, você pode facilmente localizar as tabelas que contém alguma coluna com o nome que você precisa.

SELECT TABLE_SCHEMA AS DB, TABLE_NAME AS Tabela, COLUMN_NAME AS coluna
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE
 TABLE_SCHEMA = 'nome do seu database'
 AND COLUMN_NAME = 'nome da sua coluna'

Também pode user utilizado o operador LIKE no lugar o =
Ex: AND COLUMN_NAME LIKE ‘%nome da sua coluna%’

Eu uso muito esse SELECT, espero que ajude vocês também.