Cách lập chỉ mục trong MySQL

Nâng cao hiệu suất truy vấn MySQL bằng cách lập chỉ mục

Index là công cụ thiết yếu trong cơ sở dữ liệu, đảm bảo truy xuất dữ liệu hiệu quả. Index trong MySQL đóng vai trò then chốt trong việc tối ưu hóa hiệu suất truy vấn, đặc biệt là khi tập dữ liệu của bạn phát triển. Nếu không có index phù hợp, bạn có thể gặp phải tình trạng sử dụng CPU cao trên máy chủ cơ sở dữ liệu, thời gian phản hồi chậm và cuối cùng là người dùng không hài lòng. Index là cấu trúc dữ liệu lưu trữ một tập hợp con dữ liệu trong một bảng, được sắp xếp theo cách cho phép hệ thống quản lý cơ sở dữ liệu nhanh chóng định vị và truy xuất các hàng khớp với các tiêu chí cụ thể.

Bắt đầu lập chỉ mục trong MySQL

Sử dụng lệnh CREATE INDEX

Để thêm chỉ mục vào bảng trong MySQL hoặc tạo chỉ mục bảng, bạn có thể sử dụng CREATE INDEX lệnh. Cú pháp cơ bản là:

CREATE INDEX index_name ON table_name (column_name);

Ví dụ, nếu bạn muốn thêm chỉ mục vào emailcột trong usersbảng, lệnh sẽ là:

CREATE INDEX email_idx ON users (email);

Chỉ mục nhiều cột

Đôi khi, bạn có thể cần thêm một chỉ mục trải dài nhiều cột, đặc biệt là nếu các cột đó thường được sử dụng cùng nhau trong các truy vấn. Một chỉ mục nhiều cột thường hoạt động tốt hơn nhiều chỉ mục một cột. Cú pháp là:

CREATE INDEX index_name ON table_name (column1, column2);

Nếu column1và column2chứa ID người dùng và ID tổ chức, truy vấn của bạn sẽ trông như thế này:

CREATE INDEX user_id_and_org_id_idx ON users (user_id, org_id);

Chỉ số duy nhất

Một chỉ mục duy nhất đảm bảo rằng các cột được lập chỉ mục không có giá trị trùng lặp. Điều này có thể đặc biệt hữu ích cho các cột như địa chỉ email, nơi tính duy nhất là rất quan trọng. Cú pháp chuẩn để tạo chỉ mục duy nhất là:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Để tạo chỉ mục duy nhất cho emailcột, với tên chỉ mục users_email_uq, đây là truy vấn:

CREATE UNIQUE INDEX users_email_uq ON users (email);

Chỉ mục một phần hoặc chỉ mục được lọc

Trong một số trường hợp, bạn có thể chỉ muốn lập chỉ mục một phần cụ thể của một cột chuỗi thay vì toàn bộ bảng. Kiểu lập chỉ mục này đặc biệt hữu ích để lập chỉ mục dữ liệu lịch sử, các giá trị hiếm hoặc cực đoan hoặc lập chỉ mục dựa trên trạng thái. Ví dụ: bạn có thể tạo chỉ mục trên 20 ký tự đầu tiên của cột namechứa tên công ty:

CREATE INDEX company_part_name_idx ON companies (name(20));

Thứ tự lưu trữ trong chỉ mục

Bắt đầu từ MySQL phiên bản 8.x, bạn có thể chỉ định thứ tự lưu trữ của một cột trong một chỉ mục. Điều này có thể có lợi nếu bạn cũng cần hiển thị cột theo thứ tự cụ thể. Theo mặc định, thứ tự là tăng dần. Trong ví dụ sau, chúng tôi đang thay đổi thứ tự thành giảm dần bằng cách sử dụng DESC:

CREATE INDEX reverse_name_idx ON companies (name DESC);

Các bộ phận chính chức năng

MySQL phiên bản 8.0.13 trở lên hỗ trợ các phần khóa chức năng. Các phần khóa chức năng cho phép bạn tạo chỉ mục trên một hàm của một hoặc nhiều cột thay vì trên chính các cột đó. Tính năng này có thể đặc biệt hữu ích trong các tình huống mà bạn muốn lập chỉ mục các giá trị được tính toán, áp dụng các hàm cho các cột hoặc sử dụng các biểu thức trong các truy vấn của mình:

CREATE INDEX index_name ON table_name (expression_function(column_name));

Trong ví dụ sau, idx_full_name chỉ mục được tạo dựa trên kết quả của CONCAT hàm được áp dụng cho các cột first_name và last_name.

CREATE INDEX idx_full_name ON employees((CONCAT(first_name, ' ', last_name)));

Xác định và giải quyết các vấn đề lập chỉ mục trong MySQL

Xử lý sự cố lập chỉ mục MySQL có thể giúp cải thiện khả năng phản hồi và hiệu suất của các truy vấn cơ sở dữ liệu của bạn. Hãy nhớ lập kế hoạch và kiểm tra cẩn thận mọi thay đổi chỉ mục trong môi trường phát triển hoặc dàn dựng trước khi áp dụng chúng vào cơ sở dữ liệu sản xuất. Các quyết định lập chỉ mục phải dựa trên các mẫu truy vấn và trường hợp sử dụng cụ thể của bạn và việc giám sát và bảo trì thường xuyên là điều cần thiết để duy trì hiệu suất tối ưu.

Thiếu chỉ số

Chỉ mục bị thiếu, đề cập đến các chỉ mục chưa được tạo trên các cột thường được sử dụng trong các điều kiện truy vấn (ví dụ: trong mệnh WHERE đề) hoặc trong các điều kiện liên kết (ví dụ: trong JOIN các phép toán). Khi các chỉ mục này không có, các truy vấn có thể trở nên kém hiệu quả, dẫn đến việc truy xuất dữ liệu chậm hơn và giảm hiệu suất cơ sở dữ liệu tổng thể.

SELECT * FROM orders WHERE customer_id = 123;

Để xác định các chỉ mục bị thiếu, bạn có thể sử dụng EXPLAIN hoặc EXPLAIN ANALYZE. Đối với ví dụ trên, chúng ta hãy tạo một chỉ mục trên customer_idcột.

CREATE INDEX idx_customer_id ON orders(customer_id);

Chỉ số dư thừa

Redundant index trong MySQL đề cập đến các index được tạo trên cùng một tập hợp các cột như các index hiện có khác, cung cấp ít hoặc không có lợi ích bổ sung nào về mặt hiệu suất truy vấn. Các index dư thừa này có thể dẫn đến yêu cầu lưu trữ tăng lên, hoạt động sửa đổi dữ liệu chậm hơn và chi phí bảo trì bổ sung mà không cải thiện hiệu quả của các truy vấn cơ sở dữ liệu. Chúng ta hãy xem ví dụ sau đây, trong đó có hai index cho cùng một cột column1:

CREATE INDEX idx_column1 ON table1(column1);
CREATE INDEX idx_column1_column2 ON table1(column1, column2);

Để giải quyết vấn đề lập chỉ mục trên, chúng ta hãy kết hợp các chỉ mục trùng lặp thành một chỉ mục tổng hợp hiệu quả hơn (chỉ mục duy nhất cho table1):

DROP INDEX idx_column1 ON table1;

Thứ tự chỉ số tổng hợp

Đôi khi, thứ tự các cột trong chỉ mục tổng hợp (nhiều cột) không khớp với các điều kiện truy vấn hoặc thứ tự các cột được sử dụng trong một JOIN thao tác. Điều này có thể dẫn đến hiệu suất truy vấn không tối ưu.

SELECT * FROM products WHERE category_id = 1 AND brand_id = 2;

Hãy tạo một chỉ mục tổng hợp với các cột theo đúng thứ tự để khớp với thứ tự chỉ mục hiện có. Lưu ý rằng điều này không phải lúc nào cũng khả thi, đặc biệt nếu bạn có nhiều truy vấn sử dụng thứ tự cột khác nhau.

CREATE INDEX idx_category_brand ON products(category_id, brand_id);

Chỉ số số lượng thấp

Chỉ số lượng phần tử thấp là chỉ số trên một cột có số lượng giá trị duy nhất tương đối nhỏ so với tổng số hàng trong một bảng. Nói cách khác, một cột lượng phần tử thấp có ít giá trị riêng biệt và nhiều hàng chia sẻ cùng một giá trị. Việc lập chỉ mục một cột có lượng phần tử thấp có thể không cải thiện hiệu suất truy vấn.

CREATE INDEX idx_status ON orders(status);

Hãy cân nhắc xem việc lập chỉ mục một cột như vậy có cần thiết hay có lợi không. Trong một số trường hợp, việc lập chỉ mục một cột có số lượng thấp có thể không mang lại lợi ích đáng kể và có thể hiệu quả hơn khi tập trung vào việc lập chỉ mục các cột có tính chọn lọc cao hơn (tức là các cột có nhiều giá trị riêng biệt) hoặc tối ưu hóa thiết kế truy vấn theo những cách khác. Cần cân nhắc cẩn thận các trường hợp sử dụng cụ thể và các mẫu truy vấn trong cơ sở dữ liệu của bạn để xác định xem việc lập chỉ mục một cột có số lượng thấp có phù hợp hay không.

Phân mảnh chỉ mục

Phân mảnh chỉ mục là tình trạng mà việc lưu trữ vật lý dữ liệu chỉ mục trở nên mất tổ chức hoặc kém hiệu quả theo thời gian do các sửa đổi dữ liệu như các phép toán INSERT, UPDATE, và DELETE. Khi dữ liệu trong bảng thay đổi, các chỉ mục tương ứng có thể trở nên kém hiệu quả hơn, dẫn đến suy giảm hiệu suất khi thực hiện truy vấn.

OPTIMIZE TABLE your_table;

Tối ưu hóa bảng thường xuyên để xây dựng lại chỉ mục và lấy lại hiệu suất.

Chỉ số lớn

Các chỉ mục lớn tiêu tốn không gian lưu trữ đáng kể và có thể làm chậm các hoạt động sửa đổi dữ liệu. Đánh giá xem chỉ mục như vậy có cần thiết không và cân nhắc các đánh đổi.

CREATE INDEX idx_large_column ON table1(large_column);

Lập chỉ mục quá mức

Việc có quá nhiều chỉ mục có thể làm tăng chi phí bảo trì và làm chậm quá trình sửa đổi dữ liệu.

CREATE INDEX idx_column1 ON table1(column1);
CREATE INDEX idx_column2 ON table1(column2);
CREATE INDEX idx_column3 ON table1(column3);

Xem xét lại tính cần thiết của từng chỉ mục và loại bỏ những chỉ mục dư thừa hoặc không sử dụng.

Bao gồm truy vấn chỉ mục hoặc chỉ mục

Covering index là một loại chỉ mục cơ sở dữ liệu bao gồm tất cả các cột cần thiết để thực hiện một truy vấn cụ thể mà không cần truy cập vào các hàng dữ liệu thực tế trong bảng. Nói cách khác, một chỉ mục bao phủ "bao phủ" một truy vấn bằng cách bao gồm tất cả thông tin cần thiết trong chính chỉ mục, cho phép công cụ cơ sở dữ liệu đáp ứng truy vấn trực tiếp từ cấu trúc chỉ mục. Điều này có thể cải thiện đáng kể hiệu suất truy vấn.

SELECT name, email FROM customers WHERE registration_date >= '2023-01-01';

Tạo một chỉ mục bao phủ bao gồm tất cả các cột mà truy vấn yêu cầu.

CREATE INDEX idx_registration_date ON customers(registration_date, name, email);

Chỉ mục MySQL để tối ưu hóa cơ sở dữ liệu

Tạo và quản lý chỉ mục trong MySQL là một khía cạnh quan trọng của việc tối ưu hóa cơ sở dữ liệu. Bằng cách hiểu và sử dụng các loại chỉ mục và tùy chọn khác nhau, bạn có thể đảm bảo việc truy xuất dữ liệu hiệu quả và hiệu suất cơ sở dữ liệu tối ưu. Luôn nhớ theo dõi và điều chỉnh chỉ mục của bạn khi dữ liệu và các mẫu truy vấn của bạn phát triển. Với hướng dẫn này, giờ đây bạn có thể dễ dàng tạo chỉ mục trong MySQL và khắc phục mọi lỗi lập chỉ mục mà bạn có thể gặp phải.

Hướng tới những bước tiếp theo

Bây giờ bạn đã biết cách tạo chỉ mục, nếu bạn muốn xóa chỉ mục, hãy chuyển đến hướng dẫn tiếp theo, Cách xóa chỉ mục trong MySQL bằng lệnh Drop Index và Alter Table

Câu hỏi thường gặp

Chỉ mục trong MySQL là gì?

Chỉ mục trong MySQL là cấu trúc cơ sở dữ liệu cung cấp cách nhanh chóng và hiệu quả để tra cứu các hàng trong bảng dựa trên các giá trị trong một hoặc nhiều cột. Chỉ mục nâng cao hiệu suất truy vấn bằng cách giảm nhu cầu quét toàn bộ bảng khi lọc, sắp xếp hoặc nối dữ liệu.

Có những loại chỉ mục nào trong MySQL?

MySQL hỗ trợ nhiều loại chỉ mục khác nhau, bao gồm chỉ mục một cột, chỉ mục tổng hợp (nhiều cột), chỉ mục duy nhất, chỉ mục toàn văn cho tìm kiếm dựa trên văn bản và chỉ mục không gian cho dữ liệu không gian địa lý. Lựa chọn loại chỉ mục phụ thuộc vào trường hợp sử dụng cụ thể và mẫu truy vấn của bạn.

Khi nào tôi nên sử dụng chỉ mục trong MySQL?

Bạn nên sử dụng chỉ mục trong MySQL khi bạn thường xuyên truy vấn một bảng dựa trên các cột cụ thể trong WHERE mệnh đề, JOIN điều kiện hoặc ORDER BY mệnh đề. Chỉ mục có lợi nhất cho khối lượng công việc đọc nhiều nhưng nên được lựa chọn cẩn thận để tránh lập chỉ mục quá mức và chi phí bảo trì chỉ mục.

Một số lỗi thường gặp cần tránh khi sử dụng chỉ mục trong MySQL là gì?

Những lỗi thường gặp khi sử dụng chỉ mục bao gồm lập chỉ mục quá mức (tạo quá nhiều chỉ mục), không theo dõi việc sử dụng chỉ mục, bỏ qua việc duy trì chỉ mục, sử dụng các cột có số lượng thấp để lập chỉ mục và không xem xét thứ tự các cột trong chỉ mục tổng hợp. Chiến lược lập chỉ mục phù hợp và giám sát thường xuyên là rất quan trọng để tránh những sai lầm này.

Làm thế nào để kiểm tra xem truy vấn của tôi có sử dụng chỉ mục trong MySQL không?

Bạn có thể sử dụng EXPLAIN câu lệnh trước truy vấn của mình để xem kế hoạch thực hiện truy vấn, cung cấp thông tin về chỉ mục nào được sử dụng (hoặc không được sử dụng) trong truy vấn. Ví dụ:

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

Ngoài ra, MySQL còn cung cấp các công cụ và lệnh để theo dõi việc sử dụng chỉ mục, chẳng hạn như MySQL Performance Schema và SHOW INDEX câu lệnh.