Hướng dẫn mysql descending index - chỉ số giảm dần mysql

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.

Hướng dẫn mysql descending index - chỉ số giảm dần mysql

Hướng dẫn mysql descending index - chỉ số giảm dần mysql

Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.freetuts.net, không được copy dưới mọi hình thức.

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.

Bài viết này được đăng tại [free tuts .net]

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

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)
3 trong MySQL 5.7, bạn sẽ thấy rằng DESC bị bỏ qua như dưới đây:

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)

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

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: 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ả:

Hướng dẫn mysql descending index - chỉ số giảm dần mysql

Yêu cầu 2: Sắp xếp cột a tăng dần, cột b giảm dần: 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ả:

Hướng dẫn mysql descending index - chỉ số giảm dần mysql

Yêu cầu 3: Sắp xếp cột a giảm dần, cột b tăn dần.: 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ả:

Hướng dẫn mysql descending index - chỉ số giảm dần mysql

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

8.3.13 & NBSP; Chỉ số giảm dần

MySQL hỗ trợ các chỉ mục giảm dần:

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)
4 trong một định nghĩa chỉ số không còn bị bỏ qua mà gây ra việc lưu trữ các giá trị chính theo thứ tự giảm dần. Trước đây, các chỉ mục có thể được quét theo thứ tự ngược lại nhưng tại một hình phạt hiệu suất. Một chỉ số giảm dần có thể được quét theo thứ tự chuyển tiếp, hiệu quả hơn. Các chỉ mục giảm dần cũng giúp trình tối ưu hóa có thể sử dụng các chỉ mục nhiều cột khi thứ tự quét hiệu quả nhất trộn lẫn thứ tự tăng dần cho một số cột và thứ tự giảm dần cho người khác.

Hãy xem xét định nghĩa bảng sau, chứa hai cột và bốn định nghĩa chỉ số hai cột cho các kết hợp khác nhau của các chỉ mục tăng dần và giảm dần trên các cột:

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

Định nghĩa bảng dẫn đến bốn chỉ số riêng biệt. Trình tối ưu hóa có thể thực hiện quét chỉ số chuyển tiếp cho từng điều khoản

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)
5 và không cần sử dụng thao tác
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)
6:

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

Việc sử dụng các chỉ mục giảm dần phải tuân theo các điều kiện này:

  • Các chỉ mục giảm dần chỉ được hỗ trợ cho công cụ lưu trữ

    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)
    7, với những hạn chế này:

    • Bộ đệm thay đổi không được hỗ trợ cho chỉ mục thứ cấp nếu chỉ mục chứa cột khóa chỉ mục giảm dần hoặc nếu khóa chính bao gồm cột chỉ mục giảm dần.

    • Trình phân tích cú pháp SQL

      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)
      7 không sử dụng các chỉ mục giảm dần. Đối với tìm kiếm toàn văn bản
      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)
      7, điều này có nghĩa là chỉ số cần thiết trên cột
      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)
      0 của bảng được lập chỉ mục không thể được định nghĩa là một chỉ mục giảm dần. Để biết thêm thông tin, xem Phần & NBSP; 15.6.2.4, Chỉ số toàn văn bản của InnoDB.

  • Các chỉ mục giảm dần được hỗ trợ cho tất cả các loại dữ liệu có sẵn các chỉ mục tăng dần.

  • Các chỉ số giảm dần được hỗ trợ cho các cột thông thường (Nongenerated) và được tạo (cả

    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)
    1 và
    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)
    2).

  • 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)
    3 có thể sử dụng bất kỳ chỉ mục nào chứa các cột phù hợp, bao gồm các phần chính giảm dần.

  • Các chỉ mục có các bộ phận chính giảm dần không được sử dụng cho ____ 24/________ 25 Tối ưu hóa các truy vấn gọi các chức năng tổng hợp nhưng không có mệnh đề

    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)
    6.

  • Các chỉ mục giảm dần được hỗ trợ cho

    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)
    7 nhưng không phải là
    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)
    8. Các chỉ mục giảm dần không được hỗ trợ cho các chỉ mục
    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)
    9 hoặc
    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)
    );
    0.

    Được chỉ định rõ ràng

    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)
    );
    1 và
    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)
    4 Người chỉ định cho các chỉ mục
    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)
    8,
    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)
    9 và
    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)
    );
    0 dẫn đến một lỗi.

Bạn có thể thấy trong cột

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)
);
6 của đầu ra của
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)
);
7 rằng trình tối ưu hóa có thể sử dụng chỉ mục giảm dần, như được hiển thị ở đây:
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)
);
6
column of the output of
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)
);
7 that the optimizer is able to use a descending index, as shown here:

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

Trong đầu ra

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)
);
8, việc sử dụng chỉ số giảm dần được biểu thị bằng cách bổ sung
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)
);
9 theo tên của chỉ mục, như thế này:

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

Xem thêm giải thích thông tin bổ sung.