Hướng dẫn về indexes (lập chỉ mục) trong MySQL
Các chỉ mục rất quan trọng khi cơ sở dữ liệu của chúng ta phát triển về kích thước. Chúng ta có thể xem các chỉ mục như các Khóa giúp chúng ta thực hiện tìm kiếm các hàng nhanh hơn. Trong bài viết này, chúng ta sẽ tìm hiểu về các loại Chỉ mục khác nhau trong MySQL, các trường hợp sử dụng của chúng, các chiến lược để quyết định chỉ mục cho một bảng, và cuối cùng là lợi ích của việc có các Chỉ mục. Vậy, hãy bắt đầu nào.
Các loại Chỉ mục khác nhau
Chỉ mục Cây B (B-Tree)
Đây là loại Chỉ mục mặc định trong MySQL dựa trên cấu trúc dữ liệu Cây B.
Chỉ mục Cây B không cần quét toàn bộ bảng để tìm dữ liệu cần thiết; do đó nó tăng tốc quá trình truy cập dữ liệu. Tương tự như cách hoạt động của Cây B, trong chỉ mục này, quá trình tìm kiếm bắt đầu từ node gốc và đi đến node lá. Mỗi node chứa các giá trị tối thiểu và tối đa cho các node con. Chúng ta tìm được con trỏ đúng bằng cách xem các giá trị này và đi đến node lá có con trỏ đến dữ liệu cần thiết. Độ sâu của Cây B phụ thuộc vào kích thước dữ liệu của chúng ta.
<Hình ảnh>
Cây B lưu trữ các cột theo thứ tự đã sắp xếp giúp chúng hữu ích cho việc tìm kiếm trên một dải dữ liệu, các thao tác ORDER BY và GROUP BY.
Chỉ mục Cây B thực hiện tra cứu từ các cột được lập chỉ mục ở bên trái nhất. Hãy xem ví dụ dưới đây để hiểu thêm việc thêm một Chỉ mục Cây B sẽ giúp chúng ta như thế nào.
Giả sử chúng ta có một Cơ sở dữ liệu Nhân viên và muốn tìm kiếm các nhân viên.
Cấu trúc Bảng Nhân viên
CREATE TABLE EMPLOYEE (
first_name varchar(40) not null,
last_name varchar(40) not null,
department varchar(40) not null,
joining_date date not null
);
Giả sử chúng ta có các dữ liệu sau trong bảng:
first_name last_name department joining_date
Jaxon Armstrong Engineering 2005-05-20
Donnas Mckee Sales 2015-08-02
Donnas Walton Finance 2005-01-10
Donnas Hardy Engineering 2023-05-20
Donnas Benton Product 2010-07-28
Donnas Sawyer Sales 2005-03-29
Donnas Schaefer Finance 2019-01-18
Donnas Bauer Engineering 2020-09-14
Tomas Pennington Engineering 2019-05-20
Bây giờ chúng ta đã có cấu trúc bảng, hãy thử tìm kiếm một số Nhân viên trong bảng và hiểu cách các chỉ mục có thể được định nghĩa và sử dụng như thế nào.
Chỉ mục cột đơn
ALTER TABLE EMPLOYEE
ADD INDEX first_name,
ADD INDEX department,
ADD INDEX joining_date;
Truy vấn 1:
SELECT * FROM EMPLOYEE WHERE first_name = 'Donna' and department = 'Engineering'
-- Sử dụng chỉ mục trên first_name
Mặc dù có chỉ mục trên cột department, chúng ta chỉ sử dụng chỉ mục trên first_name vì ba chỉ mục đơn lẻ hoạt động như ba bảng riêng biệt.
Bây giờ khi chạy truy vấn này, MySQL sẽ phải quét qua bảy hàng có first_name là Donna; hãy tưởng tượng cùng một lần quét đó trên một bảng lớn với khoảng 1 triệu người có tên tương tự. Thêm chỉ mục cột đơn sẽ không giúp nhiều lắm.
Chỉ mục đa cột
ALTER TABLE EMPLOYEE
ADD INDEX (department, first_name, joining_date);
Bảng dưới đây chứa chỉ mục kết hợp trên (department, first_name, joining_date).
Truy vấn 1:
SELECT * FROM EMPLOYEE WHERE department = 'Engineering' AND first_name = 'Donna'
-- Sử dụng chỉ mục trên (department, first_name)
So với chỉ mục cột đơn, việc này sẽ nhanh hơn vì số hàng cần quét sẽ giảm bằng cách sử dụng chỉ mục kết hợp trên (department, first_name)
Truy vấn 2:
SELECT * FROM EMPLOYEE WHERE last_name = 'Hardy'
-- Không sử dụng bất kỳ chỉ mục nào
Truy vấn 3:
SELECT * FROM EMPLOYEE
WHERE first_name = 'Donna'
AND joining_date > 2010-07-28
-- Không sử dụng bất kỳ chỉ mục nào
-- vì việc tra cứu theo bên trái cùng của cột được chỉ mục
-- chúng ta không đề cập đến department
-- do đó chỉ mục kết hợp không có ích
Truy vấn 4:
SELECT * FROM EMPLOYEE
WHERE department = 'Engineering'
AND first_name LIKE '%S%'
AND joining_date = 2010-07-28
-- Sử dụng chỉ mục trên (department, first_name)
-- vì first_name chúng ta có truy vấn dải
-- nó sẽ chỉ sử dụng hai cột đầu tiên của chỉ mục
Trong trường hợp sử dụng chỉ mục cho các truy vấn dải, chúng ta nên cố gắng đặt trường điều kiện dải cuối cùng trong chỉ mục. Điều này là do sau khi áp dụng truy vấn dải, không có cách nào để Cây B áp dụng bộ lọc tiếp theo. Do đó, chúng ta nên giữ cột điều kiện dải ở cuối cùng.
Đối với các Chỉ mục đa cột trong Bảng này, chúng ta có khả năng tìm kiếm chỉ mục trên (department), (department, first_name) và (department, first_name, joining_date)
ORDER BY
Xem xét truy vấn sau:
SELECT * FROM EMPLOYEE WHERE department = 'Engineering' ORDER BY last_name
Truy vấn sẽ sử dụng chỉ mục department để lấy các bản ghi và sau đó sắp xếp các bản ghi để sắp xếp chúng theo last_name. Điều này là do chúng ta không có bất kỳ chỉ mục nào cho last_name nên các bản ghi không được sắp xếp theo cột đó. Loại sắp xếp này lấy dữ liệu vào một bộ đệm tạm thời trước khi trả về nó (Sắp xếp tệp), dẫn đến tính toán bổ sung. Điều này có thể tránh được bằng cách thêm một chỉ mục trên last_name. Chỉ mục kết hợp mới cho chúng ta sẽ là (department, first_name, joining_date, last_name)
GROUP BY
Chỉ mục hợp lý cũng giúp ích trong thao tác GROUP BY.
Xem xét ví dụ như
SELECT * FROM EMPLOYEE WHERE department = 'Engineering' GROUP BY first_name
Các bản ghi đã được sắp xếp theo department nên tăng tốc quá trình lọc. Sau khi các kết quả này được trả về, chúng cũng được sắp xếp theo first_name do phần thứ hai của chỉ mục, và vì vậy chúng đã được nhóm sẵn cho chúng ta. Điều này sẽ không yêu cầu bất kỳ sắp xếp nào khác, do đó làm cho truy vấn của chúng ta nhanh hơn.
Chọn lọc chỉ mục
Chọn lọc chỉ mục là tỷ lệ của số lượng các giá trị được chỉ mục riêng biệt (độ phong phú) với tổng số hàng trong bảng (T). Phạm vi chọn lọc là từ 1/T đến 1.
Một chỉ mục được coi là có chọn lọc cao nếu với mỗi giá trị, chúng ta có ít hàng hơn. Có một chỉ mục có độ chọn lọc cao là tốt vì nó lọc ra nhiều hàng hơn khi tìm kiếm các khớp truy vấn.
Chọn Đúng Thứ tự
Bây giờ chúng ta đã hiểu Chọn lọc chỉ mục là gì, hãy xem nó giúp quyết định thứ tự cột như thế nào.
Xét lại ví dụ về Cấu trúc Bảng Nhân viên với chỉ mục đa cột trên (department, first_name, joining_date, last_name). Chỉ mục kết hợp này sẽ hoạt động tốt hơn so với chỉ mục cột đơn đối với các truy vấn, nhưng hãy kiểm tra xem đây có phải là thứ tự cột hoàn hảo cho tập các giá trị đã cho trong Bảng Nhân viên hay không.
Đầu tiên, hãy kiểm tra Chọn lọc chỉ mục của mỗi bốn cột:
1. first_name: 3/9 = 0.33
2. Department: 4/9 = 0.44
3. last_name: 9/9 = 1
Vì joining_date là trường ngày tháng, và chúng ta có thể muốn thực hiện các truy vấn dải trên nó; nên tốt nhất nên đặt nó ở cuối trong thứ tự của chỉ mục.
Dựa trên Chọn lọc, thứ tự lý tưởng trông giống như sau (last_name, department, first_name, joining_date).
Nếu xem các Ví dụ Truy vấn ở trên, thứ tự ban đầu chúng tôi xác định hoạt động tốt cho chúng tôi vì chúng tôi có thể sử dụng Chỉ mục cho hầu hết các truy vấn. Do đó, bên cạnh tính chọn lọc, các trường hợp sử dụng của điều kiện WHERE cũng rất quan trọng.
Như vậy, thứ tự Chỉ mục đa cột phụ thuộc vào:
- Điều kiện WHERE để tra cứu dữ liệu hiệu quả
- Sử dụng ORDER BY và GROUP BY
- Sử dụng các điều kiện dải trong truy vấn
- Chọn lọc chỉ mục khi không chắc chắn về thứ tự cột
- Các cột chúng ta muốn chọn trong Truy vấn
Chỉ mục Hash (Băm)
Chỉ mục Hash được sử dụng khi chúng ta muốn thực hiện tra cứu chính xác trên mọi cột trong bảng. Mã Hash được tạo cho mỗi hàng trên các cột được chỉ mục. Vì các giá trị gốc được chuyển đổi thành các giá trị ngắn dựa trên hàm Hash, chúng rất gọn.
Trong trường hợp Chỉ mục Hash, chỉ mục chỉ chứa các con trỏ đến các giá trị thay vì các giá trị hoàn chỉnh. Do đó, MySQL sử dụng các hàng trong bộ nhớ để lấy các giá trị từ các con trỏ.
Điều quan trọng cần lưu ý là chỉ mục không nên được tối ưu hóa chỉ cho điều kiện WHERE mà cho toàn bộ truy vấn.
Một số nhược điểm của Chỉ mục Hash là:
1. Vì chúng tôi đang tạo Hash bằng một hàm, chúng tôi không thể sử dụng nó để khớp một phần các giá trị. Ví dụ: Nếu chúng ta có một Chỉ mục trên (A, B), chúng ta không thể sử dụng Chỉ mục này trong một truy vấn chỉ sử dụng cột A.
2. Trong trường hợp hai hoặc nhiều hàng có cùng giá trị cho một cột, khi tạo Chỉ mục Hash, nó sẽ dẫn đến xung đột. Trong trường hợp đó, công cụ sẽ duyệt qua tất cả các con trỏ hàng và đọc các giá trị để so sánh tìm giá trị đúng dẫn đến tính toán bổ sung.
3. Các chỉ mục Hash không rất hữu ích cho các cột có độ chọn lọc thấp vì chúng sẽ dẫn đến xung đột Hash.
4. Chúng không thể được sử dụng để sắp xếp vì chúng không lưu trữ hàng theo cách đã sắp xếp.
Chỉ mục Hash thích ứng
Trong công cụ lưu trữ InnoDB, khi các giá trị được sử dụng thường xuyên, nó tạo một chỉ mục Hash trên Chỉ mục Cây B, dẫn đến việc tìm kiếm nhanh hơn. Điều này được gọi là chỉ mục Hash thích ứng.
Các loại Chỉ mục khác
Một số loại Chỉ mục khác bao gồm Chỉ mục Không gian, được sử dụng cho các kiểu hình học và địa lý, và Chỉ mục Văn bản đầy đủ, phân tách văn bản thành các từ và tạo chỉ mục trên các từ chứ không phải toàn bộ văn bản. Điều này hoạt động nhanh hơn cho các tìm kiếm văn bản khi tìm kiếm các từ cụ thể.
Lợi ích của việc Lập chỉ mục
Các lợi ích của việc lập chỉ mục được đo lường trên hệ thống ba sao:
1. Một sao nếu Chỉ mục giảm số hàng cần quét.
2. Hai sao nếu nó cải thiện hiệu suất bằng cách loại bỏ gánh nặng sắp xếp và các bảng tạm thời.
3. Ba sao nếu tất cả các cột cần thiết để truy vấn là một phần của chỉ mục.
Những điểm cần lưu ý khi lập chỉ mục
Sử dụng Hàm trong Truy vấn
MySQL chỉ có thể sử dụng các cột nếu chúng được cô lập trong truy vấn. Chúng ta nên tránh sử dụng hàm trong điều kiện WHERE.
Truy vấn xấu:
SELECT * FROM EMPLOYEE
WHERE department = 'Engineering'
AND YEAR(joining_date) = 2010
Truy vấn trên chỉ sử dụng chỉ mục department ngay cả khi có chỉ mục trên joining_date. Điều này là do chúng ta đã đặt joining_date bên trong một hàm.
Kết hợp chỉ mục
Trong trường hợp có nhiều chỉ mục cột đơn, MySQL đôi khi sử dụng nhiều chỉ mục bằng cách thực hiện Kết hợp chỉ mục. Nhưng nó không hiệu quả vì trong trường hợp các điều kiện OR, các thao tác như sắp xếp và nhóm có thể sử dụng rất nhiều tài nguyên. Trong trường hợp các điều kiện AND, việc sử dụng chỉ mục đa cột có ý nghĩa hơn so với nhiều chỉ mục cột đơn.
Các điều cần lưu ý khác
1. Các chỉ mục được triển khai ở cấp độ công cụ lưu trữ chứ không phải cấp độ máy chủ; do đó khác nhau giữa các công cụ.
2. Mặc dù chúng ta sử dụng một ORM, chúng ta không thể dựa vào nó để lập chỉ mục vì chúng thường tạo ra các truy vấn đáp ứng logic cơ bản.
3. Chỉ mục cũng có nhược điểm. Có tác động về hiệu suất lên các thao tác INSERT, UPDATE và DELETE. Mỗi lần thực hiện ghi vào bảng, các chỉ mục phải được duy trì. Vì vậy, việc chọn Chỉ mục một cách khôn ngoan là rất quan trọng.
4. Luôn Kiểm tra các chỉ mục trên các truy vấn mà ứng dụng đang thực hiện bằng cách đo thời gian phản hồi.
5. Sử dụng câu lệnh EXPLAIN để hiểu cách truy vấn được thực thi và tiết lộ lý do tại sao nó chậm. Câu lệnh explain cho thấy các giá trị như các chỉ mục được sử dụng, thứ tự sử dụng để nối các bảng, số hàng truy cập, sử dụng Filesort hay không và bảng tạm được sử dụng hay không.
Hy vọng bài viết này giúp bạn bắt đầu sử dụng Các Chỉ mục trong Cơ sở dữ liệu MySQL của mình.
Comments
Post a Comment