Índices MySQL

A criação de índices do MySQL é muito importante para o funcionamento eficiente do MySQL, os índices podem aumentar significativamente a velocidade de busca do MySQL.

Para ilustrar, se um MySQL bem projetado e com índices é um Lamborghini, então um MySQL sem design e índices é uma triciclo humano.

Para ilustrar, se o dicionário chinês de idioma (índice) é comparado à página de diretório (índice), podemos encontrar rapidamente o caractere necessário conforme a pronúncia, traço, parte complementar e outros critérios de classificação (índice).

Índices são divididos em índices de coluna única e índices de combinação. Índice de coluna única, ou seja, um índice contém apenas uma coluna, uma tabela pode ter vários índices de coluna única, mas isso não é índice de combinação. Índice de combinação, ou seja, um índice contém várias colunas.

Ao criar índices, você precisa garantir que o índice seja aplicado na consulta SQL (geralmente como condição da cláusula WHERE).

Na verdade, o índice também é uma tabela, que armazena a chave primária e os campos de índice, e aponta para os registros da tabela física.

Acima falamos dos benefícios de usar índices, mas o uso excessivo de índices pode causar abuso. Portanto, índices também têm suas desvantagens: embora os índices aumentem significativamente a velocidade de consulta, eles também podem diminuir a velocidade de atualização da tabela, como INSERT, UPDATE e DELETE. Porque ao atualizar a tabela, o MySQL não só salva os dados, mas também os arquivos de índice.

Criar índice ocupará espaço em disco do arquivo de índice.

Índice comum

Criar índice

Este é o índice mais básico, ele não tem nenhuma restrição. Ele tem as seguintes maneiras de criar:

CREATE INDEX indexName ON table_name (column_name)

Se for CHAR, VARCHAR, o comprimento pode ser menor que a comprimento real do campo; se for BLOB e TEXT, deve ser especificado length.

Modificar a estrutura da tabela (adicionar índice)

ALTER table tableName ADD INDEX indexName(columnName)

Especificar diretamente ao criar a tabela

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
INDEX [indexName] (username(length))　　
　
);

Sintaxe para excluir índice

DROP INDEX [indexName] ON mytable;

Índice único

É semelhante ao índice comum anterior, a diferença é: os valores da coluna do índice devem ser únicos, mas podem ter valores nulos. Se for um índice composto, a combinação dos valores das colunas deve ser única. Existem várias maneiras de criar:

Criar índice

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modificar a estrutura da tabela

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Especificar diretamente ao criar a tabela

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
UNIQUE [indexName] (username(length))　　
　
);

Adicionar e excluir índices usando o comando ALTER

Existem quatro maneiras de adicionar índices a uma tabela de dados:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Esta instrução adiciona uma chave primária, o que significa que os valores do índice devem ser únicos e não podem ser NULL.

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): A instrução cria valores de índice únicos (exceto NULL, que pode aparecer várias vezes).

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Adicionar índice comum, onde o valor do índice pode aparecer várias vezes.

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Esta instrução especifica o índice como FULLTEXT, usado para índices de texto completo.

O seguinte exemplo adiciona índices na tabela.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Você ainda pode usar a cláusula DROP no comando ALTER para excluir índices. Tente o seguinte exemplo para excluir índices:

mysql>　ALTER　TABLE　testalter_tbl　DROP　INDEX　c;

Adicionar e excluir chave primária usando o comando ALTER

A chave primária atua sobre a coluna (pode ser uma coluna ou várias colunas como chave primária combinada), ao adicionar o índice da chave primária, você precisa garantir que a chave primária não seja nula por padrão (NOT NULL). Veja o exemplo a seguir:

mysql>　ALTER　TABLE　testalter_tbl　MODIFY　i　INT　NOT　NULL;
mysql>　ALTER　TABLE　testalter_tbl　ADD　PRIMARY　KEY　(i);

Você também pode usar o comando ALTER para excluir a chave primária:

mysql>　ALTER　TABLE　testalter_tbl　DROP　PRIMARY　KEY;

Ao excluir a chave primária, basta especificar PRIMARY KEY, mas ao excluir o índice, você deve saber o nome do índice.

Exibir informações de índice

Você pode usar o comando SHOW INDEX para listar informações de índices relacionadas à tabela. Adicione \G para formatar a saída da informação.

Tente o seguinte exemplo:

mysql>　SHOW　INDEX　FROM　table_name;　\G
........