MySQL CRIAR ÍNDICE

Resumo : neste tutorial, você aprenderá sobre o índice e como usar a CREATE INDEXinstrução MySQL para adicionar um índice a uma tabela.

A analogia da lista telefônica

Suponha que você tenha uma lista telefônica que contenha todos os nomes e números de telefone das pessoas de uma cidade.

Digamos que você queira encontrar o número de telefone de Bob Cat. Sabendo que os nomes estão em ordem alfabética, você primeiro procura a página onde está o sobrenome Cat, depois procura Bobo número de telefone dele.

Se os nomes na lista telefônica não estivessem ordenados em ordem alfabética, você precisaria percorrer todas as páginas, lendo cada nome até encontrar Bob Cat.

Isso é chamado de pesquisa sequencial . Você repassa todas as entradas até encontrar a pessoa com o número de telefone que procura.

Relacionando a lista telefônica com a tabela, caso você possua a tabela phonebookse precise encontrar o telefone de Bob Cat, você realizaria a seguinte consulta:

SELECT
    phone_number
FROM 
    phonebooks
WHERE 
    first_name = 'Bob' AND 
    last_name = 'Cat';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

É muito fácil. Embora a consulta seja rápida, o banco de dados precisa varrer todas as linhas da tabela até encontrar a linha. Se a tabela tiver milhões de linhas, sem índice, a recuperação dos dados demoraria muito para retornar o resultado.

Introdução aos índices de banco de dados

Um índice é uma estrutura de dados como uma árvore B que melhora a velocidade de recuperação de dados em uma tabela ao custo de gravações e armazenamento adicionais para mantê-la.

O otimizador de consulta pode usar índices para localizar dados rapidamente sem precisar verificar cada linha de uma tabela para uma determinada consulta.

Quando você cria uma tabela com uma chave primária ou chave única , o MySQL cria automaticamente um índice especial chamado PRIMARY. Esse índice é chamado de índice clusterizado .

O PRIMARYíndice é especial porque o próprio índice é armazenado junto com os dados na mesma tabela. O índice clusterizado impõe a ordem das linhas na tabela.

Outros índices além do PRIMARYíndice são chamados de índices secundários ou índices não clusterizados.

Instrução MySQL CREATE INDEX

Normalmente, você cria índices para uma tabela no momento da criação. Por exemplo, a instrução a seguir cria uma nova tabela com um índice que consiste em duas colunas c2 e c3.

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para adicionar um índice para uma coluna ou conjunto de colunas, use a CREATE INDEXinstrução a seguir:

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

Para criar um índice para uma coluna ou lista de colunas, especifique o nome do índice, a tabela à qual o índice pertence e a lista de colunas.

Por exemplo, para adicionar um novo índice para a coluna c4, use a seguinte instrução:

CREATE INDEX idx_c4 ON t(c4);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por padrão, o MySQL cria o índice B-Tree se você não especificar o tipo de índice.

A tabela a seguir mostra os tipos de índice válidos com base no mecanismo de armazenamento da tabela:

Mecanismo de armazenamento	Tipos de índice permitidos
InnoDB	BTREE
MeuISAM	BTREE
MEMÓRIA/HEAP	HASH, BTREE

Observe que a CREATE INDEXinstrução acima é uma versão simplificada da CREATE INDEXinstrução introduzida pelo MySQL. Abordaremos mais opções nos tutoriais subsequentes.

Exemplo MySQL CREATE INDEX

A declaração a seguir encontra funcionários cujo cargo é Sales Rep:

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Temos 17 linhas indicando que 17 funcionários cujo cargo é Representante de Vendas.

Para ver como o MySQL executou esta consulta internamente, adicione a EXPLAIN cláusula no início da SELECTinstrução como segue:

Como você pode ver, o MySQL teve que varrer toda a tabela que consiste em 23 linhas para encontrar os funcionários com o Sales Repcargo.

Agora, vamos criar um índice para a   jobTitlecoluna usando a CREATE INDEXinstrução:

CREATE INDEX jobTitle 
ON employees(jobTitle);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Execute a EXPLAINinstrução novamente:

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

A saída é:

A saída mostra que o MySQL só precisou localizar 17 linhas do   jobTitleíndice conforme indicado na coluna chave sem varrer a tabela inteira.

Para listar todos os índices de uma tabela, você usa a SHOW INDEXESinstrução, por exemplo:

SHOW INDEXES FROM employees;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Resumo

• Um índice de banco de dados aumenta a velocidade de recuperação, mas acarreta o custo de maior sobrecarga de gravação.
• Use a CREATE INDEXinstrução para criar um novo índice para uma tabela.