Faça o que eu escrevo, mas não o que eu faço!
Neste artigo, irei abordar sobre algumas técnicas relacionadas à índices de banco de dados, descreverei o por que devemos utiliza-los, quando utiliza-los e qual a melhor forma de utiliza-los. Para isso, irei considerar que você:
- Já leu meu primeiro e o segundo artigo da série sobre performance que eu escrevi.
- Tenha conhecimento básico sobre o conceito de tabela e colunas em Banco de Dados
Index
Uma maneira fácil de pensar em indexes ( ou índices) é pensar num sumário de um livro, ele existe para facilitar a localização de conteúdos em um livro, assim você encontra um conteúdo de maneira mais prática, do que procurar página por página.
Nos bancos de dados, essa pesquisa não seria tão visual como nos livros, mas internamente esses indexes auxiliam como uma forma mais rápida de encontrar o conteúdo que você precisa.
Podemos dividir os index em 3 categorias macros:
Primary Key
Os índices de chave primária, são praticamente os indexes que ordenam os dados de forma física dentro do banco de dados, como se fosse um identificador único .
Dentre suas vantagens, a primeira é que esse index não aceita valores NULL e são considerados os ultra-fast lookups, ou seja, imagine que você seja um garçom e tem que adivinhar o dono de cada pedido, o que seria mais fácil: Sair perguntando para todo mundo quem é o solicitante do pedido? Ou entrega-los para as mesas conforme cada mesa foi solicitando pela ordem ?
CREATE TABLE Persons (
ID int NOT NULL,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Unique
Neste índice, praticamente sinalizamos que a coluna da tabela não poderá ter dois dados iguais, um exemplo disso é um identificador único que não poderá se repetir ( Como um CPF, Placa de Carro, coisas que não podem e não irão se repetir ).
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …);
Index Column
Servem para organizarem os dados da coluna, criando uma “pré-consulta” em formato de Arvore Binária ( BTREE ) organizando os dados em forma de range.
Esse formato de index aceita também a organização dos dados por duas ou mais colunas, porém devemos utilizar todas as colunas do index na momento da consulta para que ele seja utilizado.
CREATE INDEX index_name ON table_name (column1, column2, …);
Até esse momento, percebemos que criar index é bacana e pode nos auxiliar muito na estruturação dos dados do nossos sistemas, mas criar com consciência é melhor ainda, por isso listei alguns pontos de atenção que devemos ter ao utilizarmos esse recurso nos nossos sistemas.
Vantagens
- Retorna dados de forma mais leve: Como eu havia comentado no primeiro artigo, quando fazemos um SELECT na tabela, todas colunas são retornadas mesmo se estivermos sinalizando uma só, mas essa única coluna for indexada o SQL Engine não lerá todas as colunas da tabela.
- Encontrar dados rapidamente: Uma consulta feita em uma coluna com index, é muito mais rápida!
- Eliminar linhas desnecessárias: Podemos eliminar linhas desnecessárias no momento da consulta.
- Múltiplas colunas: Podemos utilizar index em duas colunas caso só uma não atenda.
- Usados em JOIN ( melhor ainda se as colunas forem o do mesmo tipo e tamanho ) : Isso melhora muito a performance de um inner join.
- Ordem dos dados: Se utilizamos uma um index do tipo Chave Unica, ele criará uma ordem física dos dados no disco, caso seja um index normal, ele criará o sistema de busca em árvore
- Podemos retirar valores de colunas sem consultar todos os dados da linha: Quando utilizamos index em uma coluna e somente optamos trazer somente ela no SELECT, isso evita um table full scan no momento da consulta
Desvantagens
- Ocupam espaço e causam lentidão na base, por que o Banco de Dados pode se confundir em qual index usar no momento que foi chamado
- Também pode inserir custos na inserção/remoção/atualização dos dados, pois eles terão sempre que estarem organizados
Pontos de atenção
- Uso de função na coluna indexada, invalida o mesmo
YEAR(o_orderdate) = 1997 - Uso de sufixo ou prefixo na função LIKE, também invalida o uso do index
name LIKE ‘%son’ - Criar index compostos e utilizar apenas uma coluna, também invalidará o uso do index.
b = 10 quando o index foi definido para (a, b) - Erro em tipo do dado da coluna e o dado que está sendo filtrada
ColunaTexto = 10 ao invés de ColunaTexto = ‘10’ - Tabelas com tipo de collation ou tipo de caracteres diferentes sendo comparadas
t1 INNER JOIN t2 ON t1.utf8_string = t2. latin1_string
Dica final
Neste artigo, a ideia era compartilhar com vocês algumas dicas que acabam escapando no momento que estamos modelando nossos dados e podem se tornar futuras dores de cabeças. Nos próximos artigos abordarei um pouco mais sobre o uso de Views e Group By em queries para melhorar o consumo de memória RAM dos bancos de dados.
Eai, já viu como estão seus indexes hoje?
SELECT database_name, table_name, index_name,
round(stat_value*@@innodb_page_size/1024/1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = ‘size’ AND index_name != ‘PRIMARY’
Tem interesse em aprender mais sobre? Entre em contato conosco ❤️!
