MySQL - Index và cách sử dụng Index hiệu quả

Giới Thiệu về Index trong Database

Index trong database giống như mục lục ở cuối một cuốn sách. Nó giúp database tìm kiếm các hàng có giá trị cột cụ thể một cách nhanh chóng mà không cần quét toàn bộ bảng. Khi một truy vấn được thực hiện, database sẽ kiểm tra index để xem liệu có thể tìm thấy dữ liệu cần thiết trực tiếp hay không.

Cách index store các key và reference tới database record

Index trong Database hoạt động như thế nào?

Index thường được triển khai dưới dạng các cấu trúc dữ liệu như B-tree hoặc hash table, lưu trữ các con trỏ tới các hàng dữ liệu. Khi một truy vấn được chạy, database sử dụng các cấu trúc dữ liệu này để xác định các record trong DB. Nếu không có Index, database có thể phải thực hiện quét toàn bộ bảng, kiểm tra từng record để tìm những record phù hợp với điều kiện truy vấn.

Các Loại Index Trong MySQL

Primary Index:

Chắc chắn rồi, đây là loại index hầu như bắt buộc đối với mọi table trong database. Được tạo tự động khi một khóa chính (primary key) được định nghĩa, được dùng để phân biệt record này với record khác. Khi khởi tạo primary index cho một column cũng có nghĩa là ta mark column đó unique.

1
2
3
4
5
6
7
8


CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Email varchar(255) NOT NULL,
    Age int,
    PRIMARY KEY (ID) -- auto create primary index here
);

Unique Index:

Sử dụng cho các cột không cho phép giá trị trùng lặp nhưng không nhất thiết là khóa chính.

1

create unique index indx_email ON Persons(Email)

Full-Text Index:

Sử dụng khi tìm kiếm trong các trường văn bản lớn, chẳng hạn như tìm kiếm các bài viết hoặc mô tả sản phẩm. Và đương nhiên vì là search by text nên Full-text index chỉ có thể được sử dụng trên các cột có type là `CHAR, VARCHAR, và TEXT.

1

CREATE FULLTEXT INDEX ft_index ON Persons(Email);

Composite Index:

Đây là trường hợp thường được sử dụng nhiều nhất do tính đa dụng của nó, bởi vì chúng ta có thể define index trên nhiều column. Qua đó có thể dễ dàng query với nhiều điều kiện khác nhau trên nhiều cột. Nhờ và sự đa dụng đó là mình thấy anh em develop team thường ưa thích sử dụng composite index

1

CREATE INDEX indx_lastname_firstname ON Persons(ID, Age);

Sử Dụng EXPLAIN để Phân Tích Hiệu Suất câu query

Khi viết xong một câu query mà bạn thắc mắc không biết câu query sẽ được execute như thế nào, hiệu suất ra sao bạn có thể tham khảo thông qua câu lệnh EXPLAIN. Bằng cách sử dụng EXPLAIN, bạn có thể thấy những index nào được sử dụng, cách các bảng được kết hợp, và cách database xử lý truy vấn.

ví dụ về cách sử dụng EXPLAIN

Giả sử bạn có bảng orders và bảng customers với các cột được index như sau:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(50),
    INDEX idx_customer_id (customer_id)
);

Câu truy vấn để lấy thông tin đơn hàng của một khách hàng cụ thể có thể như sau:

1
2
3
4


SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.status = 'shipped';

Để phân tích hiệu suất của câu truy vấn này, bạn có thể sử dụng lệnh EXPLAIN:

1
2
3
4


EXPLAIN SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.status = 'shipped';

Kết quả EXPLAIN

Ở đây chúng ta sẽ tập trung vào field “type” và “ref”

Trường type trong kết quả EXPLAIN cho biết cách MySQL tìm kiếm các hàng phù hợp trong bảng. Đây là một số giá trị phổ biến và ý nghĩa của chúng:

ALL: MySQL thực hiện quét toàn bộ bảng để tìm các hàng phù hợp. Đây là loại truy vấn chậm nhất.
index: MySQL quét toàn bộ index thay vì quét toàn bộ bảng. Tốt hơn ALL, nhưng vẫn có thể chậm nếu index không đủ hẹp.
range: MySQL sử dụng index để tìm các hàng trong một phạm vi cụ thể (ví dụ: tìm giá trị lớn hơn hoặc nhỏ hơn một giá trị nào đó).
ref: MySQL tìm kiếm các hàng trong bảng thông qua giá trị index phù hợp với một cột đơn giản. Đây là một loại truy vấn khá nhanh.
eq_ref: MySQL tìm kiếm các hàng với giá trị index duy nhất cho mỗi hàng. Thường gặp khi sử dụng khóa chính hoặc unique key trong các phép JOIN.
const/system: MySQL tìm kiếm giá trị cố định. Đây là loại truy vấn nhanh nhất.

Trong ví dụ trên:

type cho bảng orders là ref, cho thấy MySQL đang sử dụng index idx_customer_id để tìm các hàng phù hợp với điều kiện orders.status = ‘shipped’.
type cho bảng customers là eq_ref, cho thấy MySQL sử dụng khóa chính PRIMARY của bảng customers để tìm hàng phù hợp với giá trị customer_id từ bảng orders.
Với bảng orders, giá trị ref là const, cho thấy MySQL đang so sánh trực tiếp với một giá trị cố định (trong trường hợp này là status = ‘shipped’).
Với bảng customers, giá trị ref là orders.customer_id, cho thấy MySQL đang sử dụng giá trị customer_id từ bảng orders để tìm hàng trong bảng customers.

Qua kết quả EXPLAIN, chúng ta có thể thấy rằng MySQL đang sử dụng index hiệu quả để tìm kiếm dữ liệu, giúp tối ưu hóa tốc độ truy vấn. Việc hiểu rõ các trường type và ref sẽ giúp bạn phân tích và cải thiện các câu truy vấn trong database của mình.

Cách Sử Dụng Composite Index Hiệu Quả

Giả sử bạn có một bảng orders với cấu trúc sau:

1
2
3
4
5
6
7


CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_number INT,
    amount INT,
    INDEX idx_customer_number_amount (customer_id, order_number, amount)
);

Trong bảng này, bạn tạo một composite index trên ba cột: customer_id, order_number, và amount.

Giả sử bạn có câu truy vấn như sau:

1
2
3


SELECT customer_id
FROM orders 
WHERE order_number = 1 and amount = 2;

Dù bảng orders đã có composite index trên ba cột customer_id, order_number, và amount, câu truy vấn trên có thể chạy chậm hơn mong đợi vì nó không tận dụng được toàn bộ composite index. Hãy thử EXPLAIN câu query và quan sát kết quả nhận được:

Dù đã sử dụng cả 3 cột trong câu query nhưng kết quả ta nhận được là câu query trên sử dụng type là “index” - cái mà mình đã đề cập là chỉ nhanh hơn type “ALL” nhưng vẫn có thể chậm nếu index không đủ hẹp. Và ref ở đây không có đề cập đến một ref nào cả. Cho thấy được câu query đang chưa được tối ưu và chỉ tốt hơn query “ALL” một chút.

Bây giờ, hãy thử đổi lại câu query trên một chút nhé:

Bây giờ khi đổi lại câu điều kiện query từ trái sang phải theo thứ tự các cột khi define composite index, câu query đã trở nên tốt hơn rất nhiều khi sử dụng type “ref” và trong phần ref có reference tới cả 2 constant mà chúng ta đã sử dụng trong câu query.

Composite index hoạt động hiệu quả khi truy vấn sử dụng các cột trong index theo thứ tự từ trái sang phải. Trong ví dụ trên, truy vấn chỉ sử dụng cột order_number và amount, bỏ qua cột customer_id nằm ở ngoài cùng bên trái trong index. Vì vậy, MySQL không thể sử dụng hiệu quả composite index idx_customer_number_amount cho truy vấn này, dẫn đến việc phải thực hiện một quét bảng hoặc quét index không tối ưu.

Nguyên Tắc Ngoài Cùng Bên Trái

Nguyên tắc ngoài cùng bên trái chỉ ra rằng MySQL sẽ chỉ sử dụng composite index nếu câu truy vấn bắt đầu với cột ngoài cùng bên trái của index và tiếp tục theo thứ tự các cột trong index. Điều này có nghĩa là:

Nếu bạn chỉ sử dụng cột thứ nhất của composite index, MySQL có thể sử dụng toàn bộ index.
Nếu bạn sử dụng cả cột thứ nhất và thứ hai, MySQL có thể sử dụng index cho cả hai cột.
Tuy nhiên, nếu bạn bỏ qua cột thứ nhất và chỉ sử dụng cột thứ hai hoặc thứ ba, MySQL sẽ không sử dụng được composite index hoặc chỉ sử dụng được một phần của nó.

Ví dụ:

Composite Index: (customer_id, order_number, amount)

Câu truy vấn sử dụng tốt composite index:

WHERE customer_id = ?
WHERE customer_id = ? AND order_number = ?
WHERE customer_id = ? AND order_number = ? AND amount = ?

Câu truy vấn không sử dụng tốt composite index:

WHERE order_number = ? (bỏ qua customer_id)
WHERE amount = ? (bỏ qua customer_id và order_number)

Nguyên tắc ngoài cùng bên trái yêu cầu rằng thứ tự các cột trong composite index nên tương ứng với cách mà các cột này thường được sử dụng trong truy vấn. Nếu cột đầu tiên trong composite index không được sử dụng, MySQL sẽ không thể sử dụng toàn bộ index một cách hiệu quả.
Ngoài ra, Cột có tính chọn lọc cao nên được đặt ở vị trí đầu tiên trong composite index để MySQL có thể nhanh chóng giảm số lượng hàng cần quét, việc lọc theo cột này sẽ loại bỏ một lượng lớn các hàng không liên quan. Điều này giúp truy vấn hiệu quả hơn vì MySQL chỉ cần xử lý một số ít hàng.

Tổng kết

Index là một công cụ quan trọng để tối ưu hóa hiệu suất database, nhưng chúng cần được sử dụng một cách khôn ngoan. Hiểu rõ các loại index khác nhau, biết cách phân tích hiệu suất truy vấn với EXPLAIN, và sử dụng hiệu quả composite index có thể cải thiện đáng kể tốc độ và hiệu quả của các truy vấn MySQL. Bằng cách tuân theo các nguyên tắc được trình bày trong bài viết này, bạn có thể đảm bảo rằng database của mình hoạt động tốt ngay cả khi dữ liệu của bạn ngày càng lớn.

HAPPY CODING!