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. Show 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 MySQLDescending 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ả: 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ả: 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ả: 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ầnMySQL 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:
Đị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:
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. |