Indexes cho phép nhanh chóng tìm kiếm và trả về hàng triệu thậm trí hàng tỷ bản ghi mà 1 table có thể chứa.
Đối với nhiều người dùng indexes như là một nghệ thuật hắc ám. Đôi khi nó thật kỳ diệu, đôi khi nói lại làm mọi thứ trở nên tồi tệ hơn.
Trong bài này mình sẽ cùng các bạn đi tìm hiểu indexes là gì và làm sao để sử dụng indexes một cách hiệu quả.
Các khái niệm về index.
Hãy tưởng tượng bạn vào một thư viện sách tìm một cuốn sách có tên là “Zawodny”
SELECT * FROM book WHERE name = 'Zawodny'
nhưng thư viện sách này không được sắp xếp. Bạn sẽ phải đi tìm từng cuốn sách một và xem tên của nó có phải là Zawodny không. Trường hợp xấu nhất bạn nhận được là phải xem bìa của toàn bộ những cuốn sách trong thư viện thì mới có thể tìm thấy được cuốn sách bạn cần.
Nhưng nếu thư viện này sách được sắp xếp theo alpha b thì sao?. Bạn chỉ cần đến tủ sách có chữ Z và tìm trong chiếc tủ đó.
Nó thật dễ dàng phải không.
Indexes tương tự vậy. Nếu có index, database có thể nhanh chóng loại bỏ các hàng không cần thiết khỏi câu truy vấn.
ALTER TABLE book ADD INDEX (name)
Partial indexes (Index 1 phần)
Để có hiệu suất nhanh ta phải đánh đổi với việc tốn ổ cứng. Nhưng đôi khi ta không muốn đánh đổi nhiều như thế. Thật may mắn MYSQL cho phép ta đánh index 1 phần.
Giả sử bảng book có 2 tỷ dòng. Ta đánh index name, nếu độ dài trung bình của name là 8 bytes bạn sẽ mất khoảng 16GB space cho index. Ta có thể giới hạn xuống 4 bytes như sau.
ALTER TABLE book ADD INDEX (name(4))
Tất nhiên là ta vừa tiết kiệm được 1 nửa space và bây giờ khi truy vấn mysql sẽ hoạt động thế này:
SELECT * FROM book WHERE name = 'Smith'
Nó sẽ trả về tất những bản ghi bắt đầu bằng Smit bao gồm cả Smith, Smitty … sau đó truy vấn phải loại bỏ những bản ghi không hợp lệ như Smitty,…
Multicolumn indexes (Index nhiều cột)
Để đáp ứng nhu cầu where nhiều cột cùng 1 lúc thì mysql cung cấp index nhiều cột.
ALTER TABLE book ADD INDEX (name, author); SELECT * FROM book WHERE name = 'High Performance MySQL' AND author = 'Derek';
Câu hỏi đặt ra là tại sao ta không đánh 2 index 1 là name 1 là author?
Đó là vì MYSQL chỉ sử dụng 1 index cho 1 lần truy vấn.
Index Structures (Cấu trúc index)
MySQL có 3 loại index B-Tree (balanced tree), Hash, R-Tree.
Ở bài này ta sẽ đề cập đến B-Tree và Hash.
R-Tree ta sẽ tìm hiểu ở những bài sau
B-Tree
Như cái tên B-Tree hay còn gọi là cây cân bằng, dữ liệu được sắp xếp dưới dạng tree và nó sẽ không bao giờ bị mất cân bằng khi nó 1 node mới thêm hoặc xoá đi. Trong trường hợp xấu nhất thì hiệu suất vẫn khá tốt.
Hiệu suất O(log n) cho việc tìm kiếm 1 bản ghi đơn.
B-Tree index được sử dụng trong các biểu thức so sánh dạng: =, >, >=, <, <=, BETWEEN và LIKE.
Hash
Lưu trữ dưới dạng key – value.
Sử dụng trong các biểu thức toán tử <=>
Không sử dụng để tìm kiếm trong khoảng
Không có tác dụng khi dùng OrderBy
Nhanh hơn B-Tree
Tips
Chỉ nên sử dụng index cho những bảng mà Read nhiều hơn Insert, Update, Delete.
Cột index không nên null
Sử dụng câu lệnh Explain để tối ưu query.
explain select * from `chi_product`
Vậy là chúng ta đã hiểu cơ bản về indexes trong mysql. Những bài sau ta sẽ tìm hiểu sâu hơn về cấu trúc dữ liệu và các engine trong Mysql.
Cảm ơn và hẹn gặp lại
Tài liệu tham khảo: https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html, High Performance MySQL by Derek J. Balling; Jeremy D. Zawodny
(3 lượt thả tim)
Loading...
