Como o MariaDB Utiliza Índices

Os índices são utilizados para encontrar registros com um valor específico de uma coluna rapidamente. Sem um índice o MariaDB tem de iniciar com o primeiro registro e depois ler através de toda a tabela até que ele encontre os registros relevantes. Quanto maior a tabela, maior será o custo. Se a tabela possui um índice para as colunas em questão, o MariaDB pode rapidamente obter uma posição para procurar no meio do arquivo de dados sem ter que varrer todos os registros. Se uma tabela possui 1000 registros, isto é pelo menos 100 vezes mais rápido do que ler todos os registros sequencialmente. Note que se você precisar acessar quase todos os 1000 registros, seria mais rápido acessá-los sequencialmente porque evitaria acessos ao disco.

Todos os índices do MariaDB (PRIMARY, UNIQUE e INDEX) são armazenados em árvores B. Strings são automaticamente compactadas nos espaços finais e prefixados. See "Sintaxe CREATE INDEX".

Índices são utilizados nos seguintes modos:

• Para encontrar rapidamente os registros que coincidam com uma cláusula WHERE.
• Para recuperar registros de outras tabelas ao realizar joins.
• Para encontrar o valor MAX() ou MIN() para uma coluna indexada espeifica. Isto é otimizado por um preprocessador que confere se você está utilizando WHERE key_part_#=constante em todas as partes da chave < N. Neste caso o MariaDB irá fazer uma simples procura na chave e trocar a expressão MIN() com uma constante. Se todas as expressões forem trocadas por constantes, a consulta retornará imediatamente:

  SELECT MIN(key_part2),MAX(key_part2) FROM nome_tabela where key_part1=10
  

• Para ordenar ou agrupar uma tabela se a ordenação ou agrupamento for feito em um prefixo mais à esquerda de uma chave util (por exemplo, ORDER BY key_part_1, key_part_2 ). A chave é lida na ordem invertida se todas as partes da chave forem seguidas por DESC. Leia "Como o MariaDB Otimiza Cláusulas ORDER BY".
• Em alguns casos uma consulta pode ser otimizada para recuperar valores sem consultar o arquivo de dados. Se todas colunas utilizadas para alguma tabela são numéricas e formam um prefixo mais à esquerda para alguma chave, os valores podem ser recuperados da árvore de índices para aumentar a velocidade:

  SELECT key_part3 FROM nome_tabela WHERE key_part1=1
  

Suponha que você utilize a seguinte instrução SELECT:

mysql> SELECT * FROM nome_tabela WHERE col1=val1 AND col2=val2;

Se um índice de colunas múltiplas existir em col1 e col2, os registros apropriados podem ser recuperados diretamente. Se índices separados de únicas colunas existirem em col1 e col2, o otimizador tentará encontrar o índice mais restritivo decidindo qual índice irá encontrar menos registros e usará este índice para recuperar os registros.

Se a tabela possuir um índice de múltiplas colunas, qualquer prefixo mais à esquerda do índice pode ser usado pelo otimizador para encontrar registros. Por exemplo, se você possui um índice de três colunas em (col1, col2, col3), você tem capacidades de busca indexada em (col1), (col1, col2) e (col1, col2, col3).

O MariaDB não pode utilizar um índice parcial se as colunas não formarem um prefixo mais à esquerda do índice. Suponha que você tenha as instruções SELECT mostradas abaixo:

mysql> SELECT * FROM nome_tabela WHERE col1=val1;
mysql> SELECT * FROM nome_tabela WHERE col2=val2;
mysql> SELECT * FROM nome_tabela WHERE col2=val2 AND col3=val3;

Se um índice existir em (col1, col2, col3), somente a primeira consulta anteriores utiliza o índice. A segunda e terceira consultas involvem colunas indexadas, mas (col2) e (col2, col3) não são os prefixos mais à esquerda de (col1, col2, col3).

O MariaDB também utiliza índices para comparações do tipo LIKE se o argumento para LIKE for uma string constante que não inicie com um meta caracter Por exemplo as seguintes instruções SELECT utilizam índices:

mysql> SELECT * FROM nome_tbl WHERE key_col LIKE 'Patrick%';
mysql> SELECT * FROM nome_tbl WHERE key_col LIKE 'Pat%_ck%';

Na primeira instrução, somente os registros com 'Patrick' <= key_col < 'Patricl' são considerados. Na segunda instrução, somente registros com 'Pat' <= key_col < 'Pau' são considerados.

As seguintes instruções SELECT não usarão índices:

mysql> SELECT * FROM nome_tbl WHERE key_col LIKE '%Patrick%';
mysql> SELECT * FROM nome_tbl WHERE key_col LIKE other_col;

Na primeira instrução, o valor LIKE inicia com um meta caracter. Na segunda instrução, o valor LIKE não é uma constante.

O MariaDB faz outra otimização em LIKE. Se você usar ... LIKE '%string%' e string tiver mais de 3 caracteres, o MariaDB usará o algorítmo Turbo Boyer-Moore para inicializar o padrão para a string e então usar este padrão para realizar a pesquisa mais rápido.

Buscas usando nome_coluna IS NULL usa índices se nome_coluna é um índice.

O MariaDB normalmente utiliza o índice que encontra o menor número de registros. Um índice é usado para colunas que você compara com os seguintes operadores: =, >, >=, <, <=, BETWEEN ou um LIKE com um padrão que começa com um prefixo sem meta caracteres como 'algo%'.

Qualquer índice que não cobrem todos os níveis de AND na cláusula WHERE não é utilizado para otimizar a consulta. Em outras palavras: Para poder usar um índice, um prefixo do índice deve ser utilizado em todo agrupamento AND.

A seguinte cláusula WHERE utilizará índices:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
 /* optimised like 'index_part1='hello'' */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
 /* Can use index on index1 but not on index2 or index 3 */

Estas cláusulas WHERE não utilizam índices:

... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* Index is not used in
 both AND parts */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

Perceba que algumas vezes o MariaDB não utilizará um índice, mesmo se algum estiver disponível. Um exemplo deste caso é quando o uso do índice necessita que o MariaDB acesse mais de 30% dos registros na tabela. (Neste caso uma varredura da tabela é provavelmente mais rápido, já que ela necessitará de menos pesquisas em discos). No entanto, se uma consulta utiliza LIMIT para recuperar somente parte dos registros, o MariaDB irá utilizar um índice de qualquer forma, pois assim pode encontrar os poucos registros mais rapidamente e retornar o resultado.

Retornar