Hướng dẫn dùng multi-index trong PHP

Còn đây là cú pháp khi muốn tạo INDEX riêng:

ALTER TABLE tbl_name ADD INDEX index_name [column_list]
hoặc:
CREATE UNIQUE INDEX index_name ON table_name [column_list];

và UNIQUE INDEX [có ý nghĩa như khi tạo UNIQUE]

ALTER TABLE tbl_name ADD UNIQUE INDEX index_name [column_list]

ví dụ tạo index multiple columns:

CREATE TABLE `wallets` [
	`id` int not null AUTO_INCREMENT, primary key [`id`],
    `user_id` int[10] unsigned NOT NULL,
    `bank_id` int[10] unsigned NOT NULL,
    `account_id` int[10] unsigned NOT NULL,
    `amount` int[11] NOT NULL DEFAULT 0,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    UNIQUE INDEX multiple_index [user_id, bank_id, account_id]
];

UNIQUE INDEX với 3 columns : user_id, bank_id, account_id sẽ tăng tốc độ với những truy vấn có điều kiện như:

WHERE user_id = 2 and bank_id = 3 and account_id = 4;
WHERE user_id = 2 and banker_id = 3;
WHERE user_id = 2;
WHERE user_id = 2 and account_id = 4; [do không có column bank_id ở giữa, mất thứ tự columns trong index nên index trong trường hợp này sẽ apply cho column user_id, tương tự như khi chỉ truy vấn với user_id]

và sẽ vô dụng với các kiểu truy vấn như:

WHERE bank_id = 3 and account_id = 4;
WHERE banker_id = 3;
WHERE account_id = 4;

Nếu bạn để ý một chút thì index sẽ chỉ apply với những trường hợp có điều kiện user_id đầu tiên, đó là lý do bạn phải hết sức chú ý về thứ tự columns khi tạo index.

Trong case này, bạn không cần tạo index cho column user_id hay [user_id, bank_id], nó sẽ là thừa và gây tốn dung lượng vì index multiple_index đã bao gồm chúng

Cùng xem lại kết quả qua trình truy vấn từ không đến có index nhé [ví dụ với bảng wallets có hơn 100K records]

Chưa có INDEX:

EXPLAIN SELECT * FROM wallets WHERE user_id = 253 AND bank_id = 759982 AND account_id = 47

Số rows cần phải duyệt là 105908 mới tìm thấy bản ghi tương ứng và không có possible_keys nào được sử dụng. Thử tưởng tượng nếu table có 1 triệu hay nhiều hơn thì sao nhỉ? Quá kinh khủng phải không nào. [Nếu mọi người chưa biết hiểu rõ các thống số khi explain thì có thể tham khảo tại đây]

Sau khi có UNIQUE INDEX multiple_index [user_id, bank_id, account_id]:

EXPLAIN SELECT * FROM wallets WHERE user_id = 253 AND bank_id = 759982 AND account_id = 47

Bạn thấy không, sau khi đánh INDEX việc tìm kiếm đã giảm xuống còn 1 row khi sử dụng index [key] là multiple_index. Xem tiếp vài ví dụ với index nhé.

EXPLAIN SELECT * FROM wallets WHERE user_id = 253 AND bank_id = 759982

 SELECT count[*] FROM wallets WHERE user_id = 253 AND bank_id = 759982

Có 41 kết quả của câu truy vấn, trùng khớp với số rows cần duyệt để tìm kiếm.

EXPLAIN SELECT * FROM wallets WHERE user_id = 253

 SELECT count[*] FROM wallets WHERE user_id = 253

Tương tự như trên, có 246 kết quả của cau truy vấn, trùng với số rows cầ duyệt để tìm kiếm, bạn đã thấy sức mình của index chưa nào?

EXPLAIN SELECT * FROM wallets WHERE user_id = 253 AND account_id = 47

 SELECT count[*] FROM wallets WHERE user_id = 253 AND account_id = 47

Bạn thấy không, câu query chỉ có có 6 kết quả. tuy nhiên khi EXPLAIN thì cần duyệt qua 246 bản ghi, lý do là trong câu điều kiện truy vấn, đã bỏ mất column bank_id trong index, không còn giữ đúng thứ tự columns ban đầu [user_id, bank_id, account_id]. Vì vậy explain có kết quả tương tự như khi WHERE user_id = 253

Tổng kết

Trên đây mình đã giới thiệu với tất cả mọi người về việc cách sử dụng INDEX làm sao cho hiệu quả nhất. Việc hiểu và vận dụng chúng là điều vô cùng quan trọng, hãy cẩn thận với con dao hai lưỡi này. Ngoài ra, để mài bóng con dao này, để performance tăng lên gấp bội lần khi gặp bài toán data cực lớn lên cỡ vài chục triệu records, hãy kết hợp sử dụng index với Partitions. Để tìm hiểu về partitions mọi người có thể xem bài viết của mình tại đây Cám ơn mọi người đã dành thời gian đọc bài viết, nếu có bất cứ vấn đề gì, đừng ngại thắc mắc dưới phần bình luận, mình cùng nhau chia sẻ kiến thức, cùng nhau nâng cao trình độ, và nhớ tặng mình 1 vote up nhé. Tks!!!

Tài liệu tham khảo

//www.vertabelo.com/blog/all-about-indexes-the-very-basics/

//www.vertabelo.com/blog/all-about-indexes-part-2-mysql-index-structure-and-performance/