Índice descendente do MySQL – Acervo Lima Tutorials

Resumo : neste tutorial, você aprenderá sobre o índice descendente do MySQL e como aproveitá-lo para aumentar o desempenho das consultas.

Introdução ao índice descendente do MySQL

Um índice descendente é um índice que armazena valores-chave em ordem decrescente. Antes do MySQL 8.0, você pode especificar o DESCem uma definição de índice. No entanto, o MySQL ignorou isso. Enquanto isso, o MySQL poderia varrer o índice na ordem inversa, mas isso tem um custo alto.

A instrução a seguir cria uma nova tabela com um índice:

CREATE TABLE t(
    a INT NOT NULL,
    b INT NOT NULL,
    INDEX a_asc_b_desc (a ASC, b DESC)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ao usar o SHOW CREATE TABLEno MySQL 5.7, você descobrirá que o DESCé ignorado conforme mostrado abaixo:

mysql> SHOW CREATE TABLE t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a_asc_b_desc` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A partir do MySQL 8.0, os valores-chave são armazenados em ordem decrescente se você usar a palavra-chave DESC na definição do índice. O otimizador de consulta pode aproveitar o índice decrescente quando a ordem decrescente é solicitada na consulta.

O seguinte mostra a estrutura da tabela no MySQL 8.0:

mysql> SHOW CREATE TABLE t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a_asc_b_desc` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplo de índice descendente do MySQL

Primeiro, crie a ttabela com quatro índices em ordens diferentes:

DROP TABLE t;

CREATE TABLE t (
    a INT,
    b INT,
    INDEX a_asc_b_asc (a ASC , b ASC),
    INDEX a_asc_b_desc (a ASC , b DESC),
    INDEX a_desc_b_asc (a DESC , b ASC),
    INDEX a_desc_b_desc (a DESC , b DESC)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, use o seguinte procedimento armazenado para inserir linhas na ttabela:

DELIMITER $$

CREATE PROCEDURE insertSampleData(
    IN rowCount INT, 
    IN low INT, 
    IN high INT
)
BEGIN
    DECLARE counter INT DEFAULT 0;
    REPEAT
        SET counter := counter + 1;
        -- insert data
        INSERT INTO t(a,b)
        VALUES(
            ROUND((RAND() * (high-low))+high),
            ROUND((RAND() * (high-low))+high)
        );
    UNTIL counter >= rowCount
    END REPEAT;
END$$    

DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O procedimento armazenado insere diversas linhas ( rowCount) com os valores entre lowe highnas colunas ae bda ttabela.

Vamos inserir 10,000linhas na ttabela com valores aleatórios entre 1 e 1000:

CALL insertSampleData(10000,1,1000);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, consulte os dados da ttabela com diferentes ordens de classificação:

Classifique os valores nas colunas a e b em ordem crescente:

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b; -- use index a_asc_b_ascLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Exemplo 1 de índice descendente do MySQL

Classifique os valores na coluna a em ordem crescente e os valores na coluna b em ordem decrescente:

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b DESC; -- use index a_asc_b_descLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída é:

Exemplo 2 de índice descendente do MySQL

Classifique os valores na coluna a em ordem decrescente e os valores na coluna b em ordem crescente:

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b; -- use index a_desc_b_ascLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O seguinte ilustra a saída:

Exemplo 3 de índice descendente do MySQL

Classifique os valores em ambas as colunas ae bem ordem decrescente:

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b DESC; -- use index a_desc_b_descLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )