Resumo : neste tutorial, você aprenderá sobre o índice e como usar a CREATE INDEX
instruçã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 Bob
o 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 phonebooks
e 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 INDEX
instruçã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 INDEX
instrução acima é uma versão simplificada da CREATE INDEX
instruçã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 SELECT
instruçã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 Rep
cargo.
Agora, vamos criar um índice para a jobTitle
coluna usando a CREATE INDEX
instrução:
CREATE INDEX jobTitle
ON employees(jobTitle);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Execute a EXPLAIN
instruçã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 INDEXES
instruçã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 INDEX
instrução para criar um novo índice para uma tabela.