Índice de prefixo MySQL – Acervo Lima Tutorials

Resumo : neste tutorial, você aprenderá como usar o índice de prefixo MySQL para criar índices para colunas de strings de caracteres.

Introdução ao índice de prefixo MySQL

Quando você cria um índice secundário para uma coluna, o MySQL armazena os valores das colunas em uma estrutura de dados separada, por exemplo, B-Tree e Hash.

Caso as colunas sejam colunas de string, o índice consumirá muito espaço em disco e potencialmente tornará as INSERToperações mais lentas.

Para resolver esse problema, o MySQL permite que você crie um índice para a parte inicial dos valores das colunas string usando a seguinte sintaxe:

column_name(length)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por exemplo, a instrução a seguir cria a parte chave do prefixo da coluna no momento da criação da tabela:

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ou adicione um índice a uma tabela existente:

CREATE INDEX index_name
ON table_name(column_name(length));
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, o comprimento é o número de caracteres para tipos de string não binários, como CHAR,, VARCHARe TEXTe o número de bytes para tipos de string binários, por exemplo BINARY,,, VARBINARYe BLOB.

O MySQL permite que você crie opcionalmente partes-chave de prefixo de coluna para colunas CHAR, VARCHAR, BINARYe VARBINARY. Se você criar índices para colunas BLOBe TEXT, deverá especificar as partes-chave do prefixo da coluna.

Observe que o suporte ao prefixo e os comprimentos dos prefixos, se suportados, dependem do mecanismo de armazenamento . Para tabelas InnoDB com formato de linha REDUNDANTou COMPACT, o comprimento máximo do prefixo é 767 bytes. No entanto, para tabelas InnoDB com formato de linha DYNAMICou COMPRESSED, o comprimento do prefixo é 3.072 bytes. As tabelas MyISAM têm um comprimento de prefixo de até 1.000 bytes.

Exemplo de índice de prefixo MySQL

Usaremos a productstabela do banco de dados de amostra para a demonstração.

A consulta a seguir localiza os produtos cujos nomes começam com a string 1970:

SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Como não há índice para a productNamecoluna, o otimizador de consulta precisa varrer todas as linhas para retornar o resultado conforme mostrado na saída da EXPLAINinstrução abaixo:

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Se você costuma encontrar os produtos pelo nome do produto, deve criar um índice para esta coluna, pois será mais eficiente para pesquisas.

O tamanho da coluna do nome do produto é de 70 caracteres. Podemos usar as partes principais do prefixo da coluna.

A próxima pergunta é como você escolhe o comprimento do prefixo? Para fazer isso, você pode investigar os dados existentes. O objetivo é maximizar a exclusividade dos valores na coluna ao usar o prefixo.

Para fazer isso, siga estas etapas:

Passo 1. Encontre o número de linhas na tabela:

SELECT
   COUNT(*)
FROM
   products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Passo 2. Avalie diferentes comprimentos de prefixo até conseguir uma exclusividade razoável das linhas:

SELECT
   COUNT(DISTINCT LEFT(productName, 20)) unique_rows
FROM
   products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Índice de prefixo MySQL - contagem de linhas

Conforme mostrado na saída, 20 é um bom comprimento de prefixo neste caso porque se usarmos os primeiros 20 caracteres do nome do produto para o índice, todos os nomes de produtos serão exclusivos.

Vamos criar um índice com comprimento de prefixo 20 para a productNamecoluna:

CREATE INDEX idx_productname 
ON products(productName(20));Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

E execute novamente a consulta que encontra produtos cujo nome começa com a string 1970:

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )