Resumo : neste tutorial, você aprenderá sobre o índice invisível do MySQL e as instruções para gerenciar a visibilidade do índice.
Introdução ao índice invisível MySQL
Os índices invisíveis permitem marcar índices como indisponíveis para o otimizador de consulta. O MySQL mantém os índices invisíveis e os mantém atualizados quando os dados nas colunas associadas aos índices mudam.
Por padrão, os índices ficam visíveis. Para torná-los invisíveis, é necessário declarar explicitamente sua visibilidade no momento da criação ou por meio do ALTER TABLE
comando. O MySQL nos fornece as palavras-chave VISIBLE
e INVISIBLE
para manter a visibilidade do índice.
Para criar um índice invisível, você segue a seguinte instrução:
CREATE INDEX index_name
ON table_name( c1, c2, ...) INVISIBLE;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe:
- Primeiro, você especifica o nome do índice após a
CREATE INDEX
cláusula. - Em segundo lugar, você lista o nome da tabela e a lista de colunas que deseja adicionar ao índice. A
INVISIBLE
palavra-chave indica que o índice que você está criando é invisível.
Por exemplo, a instrução a seguir cria um índice na extension
coluna da employees
tabela no banco de dados de amostra e marca-o como um índice invisível:
CREATE INDEX extension
ON employees(extension) INVISIBLE;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para alterar a visibilidade dos índices existentes, use a seguinte instrução:
ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por exemplo, para tornar o extension
índice visível, você usa a seguinte instrução:
ALTER TABLE employees
ALTER INDEX extension VISIBLE;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Você pode encontrar os índices e sua visibilidade consultando a statistics
tabela no information_schema
banco de dados:
SELECT
index_name,
is_visible
FROM
information_schema.statistics
WHERE
table_schema = 'classicmodels'
AND table_name = 'employees';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está a saída:
Além disso, você pode usar o SHOW INDEXES
comando para exibir todos os índices de uma tabela:
SHOW INDEXES FROM employees;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Conforme mencionado anteriormente, o otimizador de consulta não usa um índice invisível, então por que você usa o índice invisível em primeiro lugar?
Na prática, os índices invisíveis têm diversas aplicações. Por exemplo, você pode tornar um índice invisível para ver se ele tem impacto no desempenho e marcar o índice como visível novamente se isso acontecer.
Índice invisível MySQL e chave primária
O índice na coluna da chave primária não pode ser invisível. Se você tentar fazer isso, o MySQL emitirá um erro.
Além disso, um índice de chave primária implícito também não pode ser invisível. Quando você define um UNIQUE
índice em uma NOT NULL
coluna de uma tabela que não possui uma chave primária, o MySQL entende implicitamente que esta coluna é a coluna de chave primária e não permite tornar o índice invisível.
Considere o seguinte exemplo.
Primeiro, crie uma nova tabela com um UNIQUE
índice em uma NOT NULL
coluna:
CREATE TABLE discounts (
discount_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
amount DEC(5 , 2 ) NOT NULL DEFAULT 0,
UNIQUE discount_id(discount_id)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, tente tornar o discount_id
índice invisível:
ALTER TABLE discounts
ALTER INDEX discount_id INVISIBLE;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
MySQL emitiu a seguinte mensagem de erro:
Error Code: 3522. A primary key index cannot be invisible
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Variáveis do sistema de índice invisível MySQL
Para controlar os índices visíveis usados pelo otimizador de consulta, o MySQL usa o use_invisible_indexes
sinalizador da optimizer_switch
variável do sistema. Por padrão, use_invisible_indexes
está desativado:
SELECT @@optimizer_switch;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste tutorial, você aprendeu sobre o índice invisível do MySQL, como criar um índice invisível e como alterar a visibilidade de um índice existente.