Descending Index trong MySQL - MySQL nâng cao

Trong bài này chúng ta sẽ học cách đặt Descending Index trong MySQL, đây là cách đạt chỉ mục giúp cho việc sắp xếp kết quả trả về nhanh chóng hơn.

Thông thường bạn hay sắp xếp tăng dần và giảm dần theo ID kiểu integer với mong muốn tốc độ trả về tối ưu hơn. Tuy nhiên có một số trường hợp bạn muốn sắp xếp theo nhiều field nhưng tốc độ phải nhanh một chút. Đây chính là lúc sử dụng chỉ mục sắp xếp.

1. Giới thiệu Descending Index trong MySQL

Descending Index còn gọi là chỉ mục giảm dần, lưu trữ các giá trị theo thứ tự giảm dần. Các phiên bản MySQL 8.0 trở về trước bạn có thể chỉ định DESC trong khai báo chỉ mục, tuy nhiên trong phiên bản MySQL này đã bỏ qua nó.

Thực tế MySQL cũng thể quét chỉ mục theo thứ tự giảm dần nhưng chi phí thực hiện quá cao, hay nói cách khác là không tối ưu.

Câu lệnh SQL dưới đây tạo ra một table và một index trong table đó.

CREATE TABLE t(
    a INT NOT NULL,
    b INT NOT NULL,
    INDEX a_asc_b_desc (a ASC, b DESC)
);

Khi bạn sử dụng lệnh SHOW CREATE TABLE trong MySQL 5.7, bạn sẽ thấy rằng DESC bị bỏ qua như dưới đây:

mysql> SHOW CREATE TABLE tG;
*************************** 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)

Bắt đầu từ MySQL 8.0 thì các giá trị được lưu trữ theo thứ tự giảm dần nếu bạn sử dụng từ khóa DESC để tạo chỉ mục.

Sau đây cho thấy cấu trúc bảng trong MySQL 8.0:

mysql> SHOW CREATE TABLE tG;
*************************** 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)

2. Ví dụ Descending Index trong MySQL

Đầu tiên hãy tạo ra bảng t và nhiều index khác nhau như sau:

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)
);

Tiếp theo hãy sử dụng đoạn code SQL Procedure dưới đây để insert 10000 dòng dữ liệu vào table này.

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$$   

Chạy lệnh sau để insert:

CALL insertSampleData(10000,1,1000);

Bây giờ mình có một vài yêu cầu cụ thể như sau:

Yêu cầu 1: Sắp xếp giảm dần theo hai cột a và b

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b; -- use index a_asc_b_asc

Kết quả:

Yêu cầu 2: Sắp xếp cột a tăng dần, cột b giảm dần

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b DESC; -- use index a_asc_b_desc

Kêt quả:

Yêu cầu 3: Sắp xếp cột a giảm dần, cột b tăn dần.

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b; -- use index a_desc_b_asc

Kết quả:

Như vậy là mình đã hướng dẫn xong cách sử dụng sắp xếp chỉ mục trong MySQL, hay còn gọi là Descending Index trong MySQL. Chúc bạn thực hiện thành công!