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 DESC
em 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 TABLE
no 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 t
tabela 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 t
tabela:
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 low
e high
nas colunas a
e b
da t
tabela.
Vamos inserir 10,000
linhas na t
tabela 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 t
tabela 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_asc
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está a saída:
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_desc
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A saída é:
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_asc
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O seguinte ilustra a saída:
Classifique os valores em ambas as colunas a
e b
em ordem decrescente:
EXPLAIN SELECT
*
FROM
t
ORDER BY a DESC , b DESC; -- use index a_desc_b_desc
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O seguinte mostra a saída:
Resumo
- Use o índice descendente do MySQL para melhorar o desempenho da consulta.