Cách đánh index cơ bản trong MySQL

Cách đánh index cơ bản trong MySQL

Khi nào thì sử dụng index, đối với các dự án vừa và nhỏ, table có khoảng 1-2K bản ghi thì chúng ta không cần quan tâm nhiều đến việc đánh index cho bảng. Vì việc đánh index hay không đánh thời gian trả về dữ liệu của một query không có sự khác biệt nhiều.

Ví dụ trong thực tế khi lấy thông tin của 1 tài khoản bằng email hoặc số điện thoại, nếu không có index thì CSDL sẽ phải lặp qua tất cả các bản ghi để tìm kiếm dữ liệu tương ứng, nhưng khi chúng ta có 1 triệu thành viên thì việc duyệt để tìm như thế là vấn đề lớn

Để thực hành Index MySQL mình viết script PHP tạo hơn 4 triêu bản ghi

Ví dụ 1:

SELECT * FROM`users` WHERE `email` = 'useremail5cc26ebfeec1f@gmail.com'

Nếu không sử dụng index thì cầu lện trên mất hơn 11s để trả về kết quả

Để xem MySQl xử lỹ câu lệnh đó như nào chi tiết, chúng ta dùng EXPLAIN để tối ưu truy vấn

EXPLAIN SELECT * FROM`users` WHERE `email` = 'useremail5cc26ebfeec1f@gmail.com'
  • table : bảng truy vấn
  • type : Đây là thông tin quan trọng, nó cho chúng ta biết kiểu query nào nó đang sử dụng. Mức độ từ tốt nhất đến chậm nhất như sau: system, const, eq_ref, ref, range, index, all
  • possible_keys : Đưa ra những Index có thể sử dụng để query
  • key : Index nào đang được sử dụng
  • key_len : Chiều dài của Index
  • ref : Cột nào đang sử dụng
  • rows : Số hàng (rows) mà MySQL phải duyệt
  • extra : Thông tin phụ, thật tệ nếu tại cột này là “using temporary” hay “using filesort”

Quay lại câu truy vấn của ta thì MySQL đang phải duyệt tất cả các hàng để trả về dữ liệu của một bản ghi. Không tối ưu chút nào

Nào giờ cũng phân tích câu truy vấn, kinh nghiệm của mình khi đánh Index thì Where gì, Order gì thì đánh Index cái đó, ở đây mình tìm theo email nên sẽ đánh Index trường email, có một điều đặc biệt nữa email là duy nhất nó gần như khóa chính trong bảng, nên ở đây mình sẽ đanh Index là Unique

ALTER TABLE `users` ADD UNIQUE idx_email (`email`)

Sau khi đợi MySQL đánh index xong thì quay lại câu truy vấn cũ và trải nghiệm tốc độ xử lý

Kết quả thì MySQL chỉ cần lặp qua 1 bản ghi là có thể tìm thấy dữ liệu và không cần tới 0.1 giây đã trả về dữ liệu

Ví dụ 2:

Thống kê tổng số thành viên nam và nư, cách thông thường thì sẽ truy vấn như sau:

SELECT COUNT(*) FROM `users` WHERE `gender` = 'female'

Mất hơn 10 giây để trả về kết quả và nó cũng đang phải duyệt tất cả các bản ghi trong table để lấy dữ liệu. Nhưng đối với email, số điện thoại thì nó là duy nhất còn giới tính thì nó không phải duy nhât, vậy là nó có lớn hơn 1 bản ghỉ, ta dùng cách đang Index thông thường

ALTER TABLE `users` ADD INDEX idx_gender (`gender`)

Kết quả tuy rằng đã trả về dữ liệu nhanh hớn khoản hơn 3s, nhưng vẫn thuộc loại chậm, do khối lượng bản ghỉ nhiều, để tối ưu hơn cho trường hợp này ta có thể dùng thêm limit hoặc bảng phụ tạo trigger insert vào bảng phụ khi có 1 user mới cộng 1 thêm vào trường nam/nư. Do không liên quan đến index nên sẽ chia sẻ trong bài viết khác

Còn kiểu Index Full Text Search đặc thù của tìm kiếm toàn văn nên sẽ giới thiệu trong một buổi khác, với 2 kiểu index như trên đã đủ dùng cho gần hết các trường hợp cần tối ưu truy vấn.

Link SQL data mẫu: https://github.com/thlinhdotcom/demo/blob/master/demo_index_sql.zip