Índice Invisível MySQL

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 TABLEcomando. O MySQL nos fornece as palavras-chave VISIBLEe INVISIBLEpara 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:

1. Primeiro, você especifica o nome do índice após a CREATE INDEXcláusula.
2. Em segundo lugar, você lista o nome da tabela e a lista de colunas que deseja adicionar ao índice. A INVISIBLEpalavra-chave indica que o índice que você está criando é invisível.

Por exemplo, a instrução a seguir cria um índice na extensioncoluna da employeestabela 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 statisticstabela no information_schemabanco 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 INDEXEScomando 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 NULLcoluna 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 NULLcoluna:

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_indexessinalizador da optimizer_switchvariável do sistema. Por padrão, use_invisible_indexesestá 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.