Índice Composto MySQL

Resumo : neste exemplo, você aprenderá sobre o índice composto do MySQL e como usá-lo para agilizar suas consultas.

Introdução ao Índice Composto MySQL

Um índice composto é um índice em múltiplas colunas. O MySQL permite criar um índice composto que consiste em até 16 colunas.

Um índice composto também é conhecido como índice de múltiplas colunas.

O otimizador de consulta usa os índices compostos para consultas que testam todas as colunas do índice ou consultas que testam as primeiras colunas, as duas primeiras colunas e assim por diante.

Se você especificar as colunas na ordem correta na definição do índice, um único índice composto poderá aprimorar o desempenho de consultas envolvendo essas colunas na mesma tabela.

Para criar um índice composto durante a criação da tabela, use a seguinte instrução:

CREATE TABLE table_name (
    c1 data_type PRIMARY KEY,
    c2 data_type,
    c3 data_type,
    c4 data_type,
    INDEX index_name (c2,c3,c4)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, o índice composto consiste em três colunas c2, c3 e c4.

Alternativamente, você pode adicionar um índice composto a uma tabela existente usando a CREATE INDEXinstrução:

CREATE INDEX index_name 
ON table_name(c2,c3,c4);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que se você tiver um índice composto em (c1,c2,c3), terá recursos de pesquisa indexados em uma das seguintes combinações de colunas:

(c1)
(c1,c2)
(c1,c2,c3)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por exemplo:

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1;


SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2;


SELECT  
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2 AND 
    c3 = v3;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O otimizador de consulta não poderá usar o índice para realizar pesquisas se as colunas não formarem o prefixo mais à esquerda do índice. Por exemplo, as consultas a seguir não podem usar o composto para pesquisas:

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c3 = v3;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplo de índice composto MySQL

Usaremos a employeestabela do banco de dados de exemplo para a demonstração:

A instrução a seguir cria um índice composto nas colunas lastNamee firstName:

CREATE INDEX name 
ON employees(lastName, firstName);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Primeiro, o nameíndice pode ser usado para pesquisas nas consultas que especificam um lastNamevalor porque a lastNamecoluna é o prefixo mais à esquerda do índice.

Segundo, o nameíndice pode ser usado para consultas que especificam valores para a combinação dos valores lastNamee firstName.

O nameíndice, portanto, é usado para pesquisas nas seguintes consultas:

1) Encontre funcionários cujo sobrenome sejaPatterson

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Esta consulta usa o nome índice porque o prefixo mais à esquerda do índice, que é a lastNamecoluna, é usado para pesquisas.

Você pode verificar isso adicionando a EXPLAINcláusula à consulta:

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

2) Encontre funcionários cujo sobrenome seja Pattersone o nome seja Steve:

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta consulta, ambas as colunas lastNamee firstNamesão usadas para pesquisas, portanto, ela usa o nameíndice.

Vamos verificar:

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída é:

3) Encontre funcionários cujo sobrenome seja Pattersone o nome seja Steveou Mary:

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Esta consulta é semelhante à segunda, em que ambas lastNameas firstNamecolunas são usadas para pesquisas.

A instrução a seguir verifica o uso do índice:

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída é:

O otimizador de consulta não pode usar o nameíndice para pesquisas nas consultas a seguir porque somente a firstNamecoluna que não é o prefixo mais à esquerda do índice é usada:

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Leslie';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Da mesma forma, o otimizador de consulta não pode usar o índice de nomes para pesquisas na consulta a seguir porque a coluna firstNameou lastNameé usada para pesquisas.

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Anthony' OR
    lastName = 'Steve';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resumo

• Índices compostos são índices que envolvem mais de uma coluna.
• Defina índices compostos quando suas consultas envolverem condições ou classificação em diversas colunas.
• O uso adequado de índices compostos pode melhorar significativamente o desempenho de consultas que filtram ou classificam com base nas colunas indexadas.