Cách đánh Index trong SQL giúp giảm tới 90% thời gian truy vấn

Tìm hiểu về đánh Index trong SQL

Index trong SQL là gì? Index trong SQL là một cơ chế được sử dụng để tăng tốc độ truy xuất dữ liệu từ cơ sở dữ liệu. Index giúp cơ sở dữ liệu tổ chức dữ liệu một cách cấu trúc hóa, tạo ra một bản sao nhỏ của dữ liệu được sắp xếp theo thứ tự nhất định. 

Có nghĩa là, khi bạn thực hiện truy xuất dữ liệu từ cơ sở dữ liệu, hệ thống sẽ sử dụng index để nhanh chóng định vị và truy xuất dữ liệu một cách hiệu quả hơn, thay vì phải quét qua toàn bộ cơ sở dữ liệu. Cơ chế này giúp cải thiện hiệu suất và thời gian phản hồi khi thực hiện các truy vấn SQL, đặc biệt là khi bạn làm việc với các cơ sở dữ liệu lớn và phức tạp.

Một số lợi ích của việc sử dụng đánh Index trong SQL:

• Tăng tốc độ truy vấn: Index giúp giảm thời gian cần thiết để tìm kiếm dữ liệu trong bảng, đặc biệt là đối với các truy vấn có điều kiện.
• Tối ưu hóa hiệu suất: Index trong SQL giúp giảm tải cho cơ sở dữ liệu bằng cách tăng cường hiệu suất truy vấn và giảm thời gian xử lý.
• Giảm tải cho hệ thống: Index giúp giảm thiểu việc quét toàn bộ bảng, giúp giảm tải cho CPU và I/O.
   

>>> XEM THÊM: 

Top 5 công cụ check SEO Onpage chất lượng nhất hiện nay

Các kiểu Index trong SQL

BTREE Index

Khi nói về việc đánh index mà không đề cập rõ loại index thì đa người ta thường sử dụng BTREE index. Dưới đây là cú pháp và một số đặc điểm quan trọng của BTREE Index:

Cú pháp:

CREATE INDEX id_index ON table_name (column_name[, column_name…]) USING BTREE

Hoặc bạn có thể sử dụng cú pháp ALTER TABLE để thêm index:

ALTER TABLE table_name ADD INDEX id_index (column_name[, column_name…])

Đặc điểm của BTREE Index:

• Dữ liệu index theo kiểu BTREE được tổ chức và lưu trữ theo cấu trúc cây với các nút gốc, nút trung gian và nút lá. Không nhất thiết bạn phải tuân theo cấu trúc cây tìm kiếm nhị phân, nghĩa là mỗi nút có thể có nhiều hơn 2 nút con.
• Giá trị của các nút phải sắp xếp theo kiểu tăng dần (trái qua phải).
• BTREE index thường được sử dụng cho các phép so sánh dạng "=", ">", ">=", "<", "<=", "BETWEEN", và "LIKE." 
• Khi truy vấn dữ liệu, hệ thống quản lý cơ sở dữ liệu không cần quét toàn bộ bảng để tìm dữ liệu. Thay vào đó, quá trình tìm kiếm được thực hiện theo cấu trúc cây BTREE, bắt đầu từ nút gốc và tìm kiếm qua các nút trung gian và nút lá để lấy được dữ liệu thỏa mãn điều kiện truy vấn.

Hash Index

Loại thứ 2 là Hash Index. Hash Index sử dụng một giải thuật gọi là Hash Function (có thể hiểu là hàm băm) để tạo các bucket key (giá trị băm) tương ứng với mỗi khối dữ liệu (index) để phân biệt chúng.
Dưới đây là cú pháp và một số đặc điểm quan trọng của Hash Index:

Cú pháp:

CREATE INDEX id_index ON table_name (column_name[, column_name…]) USING HASH

Hoặc bạn có thể sử dụng cú pháp ALTER TABLE để thêm index:

ALTER TABLE table_name ADD INDEX id_index (column_name[, column_name…]) USING HASH

Đặc điểm của Hash Index:

• Khác với BTREE, Hash Index thường nên sử dụng cho các phép so sánh toán tử "=", "<>". Nó không phù hợp cho việc tìm kiếm theo một khoảng giá trị như ">", "<."
• Hash Index không thể tối ưu hóa các toán tử ORDER BY bởi vì nó không thể dự đoán phần tử tiếp theo trong kết quả truy vấn sắp xếp.
• Hash Index có tốc độ tìm kiếm nhanh hơn so với BTREE trong một số trường hợp, giúp cải thiện hiệu suất của truy vấn.

Lựa chọn loại Index phụ thuộc vào Storage Engine

Việc bạn lựa chọn giữa sử dụng index loại BTREE hay Hash không chỉ phụ thuộc vào mục đích của index mà còn phụ thuộc vào việc hệ thống lưu trữ (Storage Engine) có hỗ trợ loại index đó hay không.

Dưới đây là các loại index được hỗ trợ bởi một số Storage Engine phổ biến, bạn có thể dựa vào đó để lựa chọn loại Index phù hợp cho mình: 

• InnoDB: Hỗ trợ index loại BTREE.
• MyISAM: Cũng hỗ trợ index loại BTREE.
• MEMORY/HEAP: Hỗ trợ cả index loại HASH và BTREE.
• NDB: Cung cấp hỗ trợ cho cả index loại HASH và BTREE.

Cơ chế đánh Index trong SQL

Cơ chế đánh Index trong SQL có thể được hiểu dễ dàng thông qua ví dụ sau:
Giả sử bạn có một bảng dữ liệu với 1000 bản ghi chứa tên của nhiều người khác nhau, và bạn đang muốn tìm tên cụ thể là “Nam”. Cơ chế đánh Index trong trường hợp này hoạt động như sau:

• Đầu tiên, khi bạn tạo Index cho cột "name", hệ thống sẽ tự động sắp xếp các tên theo thứ tự bảng chữ cái và gán một số Index tương ứng. Ví dụ, "An" có thể được gán Index 0, "Anh" có thể được gán Index 1, "Ánh" có thể được gán Index 2, và cứ tiếp tục như thế.
• Khi bạn thực hiện lệnh truy vấn theo cấu trúc: SELECT...WHERE name = "Nam", hệ thống sử dụng phương pháp tìm kiếm nhị phân. Đầu tiên, nó sẽ lấy giá trị "Nam" so sánh với giá trị tại vị trí Index trung bình (chẳng hạn như Index 500). Thay vì so sánh trực tiếp, nó so sánh xem "Nam" có lớn hơn hay bé hơn giá trị tại Index 500 (ví dụ: "Hưng").
• Nếu giá trị "Nam" lớn hơn giá trị tại Index 500 (trong trường hợp này, "Nam" > "Hưng" theo thứ tự alphabet), thì nó biết rằng "Nam" nằm ở phía bên phải của Index 500. Nó tiếp tục chia đôi và tìm kiếm trong nửa phía bên phải của danh sách.
• Quá trình này lặp lại cho đến khi nó tìm ra bản ghi có giá trị "Nam" hoặc không tìm thấy sau một số lần so sánh nhất định (trong trường hợp này, tối đa là 10 lần so sánh).
• Khi số lượng bản ghi nhiều hơn, cơ chế đánh Index này thể hiện rõ sức mạnh của nó. Ví dụ, nếu bạn có 1 triệu bản ghi, bạn chỉ cần tối đa 20 lần so sánh để tìm ra "Nam," trong khi phương pháp truy vấn thông thường có thể đòi hỏi thực hiện đến 999,999 lần so sánh. Có thể thấy, đánh Index giúp cải thiện đáng kể hiệu suất truy vấn lên rất nhiều lần. 

Tuy nhiên, việc đánh Index trong SQL cần xem xét cẩn thận, vì nó có thể làm tăng dung lượng của cơ sở dữ liệu và làm chậm quá trình cập nhật dữ liệu. Tốt nhất là bạn nên liên hệ với một chuyên gia có kinh nghiệm để được hướng dẫn, xem xét về cấu trúc dữ liệu, loại truy vấn để quyết định nên tạo Index cho những cột nào trong bảng cơ sở dữ liệu. Bạn có thể kết nối ngay với các mentor SEO giỏi thông qua dịch vụ tư vấn SEO tại ứng dụng Askany. 

>>> ĐỌC THÊM: 

Mẹo sửa lỗi trang có lệnh chuyển hướng, không ảnh hưởng ranking SEO

Hướng dẫn đánh Index trong SQL

Cách đánh Index cho một trường

Đây là cách đơn giản và thường thấy nhất khi đánh index bên trong SQL trong trường hợp chỉ có 1 column hoặc khi muốn tìm kiếm và đánh index cho nó. Bạn cần nên xem xét các loại index sử dụng trong cơ sở dữ liệu, như là BTREE hoặc Hash. Sau khi đánh index, bạn cần thực hiện các truy vấn tìm kiếm và xem xét liệu hiệu suất đã cải thiện hay chưa. 

Lưu ý: Chúng ta thường đánh index cho một trường cụ thể khi đó là trường được sử dụng thường xuyên trong các truy vấn tìm kiếm. Tuy nhiên, cần lưu ý rằng nếu số giá trị duy nhất hoặc giá trị không rỗng (NULL) trong trường đó quá ít so với tổng số bản ghi trong bảng, việc đánh index có thể không mang lại hiệu quả đáng kể.

Ví dụ, nếu trường như là "gender" chỉ có hai giá trị duy nhất (nam và nữ) và bạn thường xuyên tìm kiếm dữ liệu dựa trên trường này, việc đánh index có thể không đem lại nhiều lợi ích, vì mỗi giá trị xuất hiện này được sử dụng lại rất nhiều. Tương tự, việc đánh index cho trường "age" có thể không có lợi ích lớn nếu có rất ít giá trị.

Cách đánh Index cho nhiều trường (BTREE Index)

Cách đánh index cho nhiều trường còn được gọi là phương pháp BTREE, đây là phương pháp giúp tối ưu hóa quá trình truy vấn dựa trên sắp xếp và tạo ra các nhánh trong cơ sở dữ liệu. Chẳng hạn, nếu như bạn cần tìm người ở tỉnh "Đồng Nai" có tên là "Nam". Cơ chế index hoạt động như sau:

• Bước 1: Đánh index cho cả trường :province và trường :name.
• Bước 2: Dữ liệu được nhóm lại theo trường :province, sắp xếp theo thứ tự alphabet, ví dụ "An Giang" index là 0, "Bà Rịa Vũng Tàu" index là 1, "Bắc Giang" có index 2, và tương tự với cách tỉnh còn lại.
• Trong mỗi nhóm tỉnh/thành, các trường :name cũng được đánh index. Ví dụ: "An" = 0, "Anh" = 1, "Bình": 2,...
• Bước 3: Khi thực hiện câu truy vấn "SELECT ... WHERE province = 'Đồng Nai' AND name = 'Nam'", hệ thống sẽ lọc ra những người ở tỉnh "Đồng Nai" bằng cách so sánh "Đồng Nai" với các tỉnh đã được đánh index. Việt Nam có 64 tỉnh nên cần tối đa 6 lần so sánh để tìm người ở tỉnh "Đồng Nai". Sau đó, từ thông tin của người đó, bằng phương pháp đánh Index như thế này hệ thống sẽ lọc ra toàn bộ nhóm người ở tỉnh "Đồng Nai".
• Bước 4: Cuối cùng, từ nhóm người ở tỉnh "Đồng Nai", hệ thống sử dụng phương pháp index để lọc ra người có tên "Nam" thêm một lần nữa.

Bạn cần lưu ý một điều là việc đánh index cho trường nào trước (:province hay :name trước) để có thể tối ưu truy vấn được tốt nhất. Trên thực tế thì bạn nên đánh index cho trường ít record hơn trước.

Quay lại với ví dụ trên đây: :province có 64 tỉnh, còn :name thì lại có hàng trăm ngàn kiểu đặt tên lận. Do đó, việc nhóm theo trường :province trước là lựa chọn theo mình là bước đầu tiên, vì khi lọc ra trường :province, bạn đã lọc được 63/64 tỉnh thành mà không cần quan tâm tới, tương đương với một lượng record cực lớn. Nếu bạn đánh index theo trường :name trước, thì hầu như phải đụng tới mọi record, điều này là không hiệu quả và mất thời gian.

Lưu ý rằng thứ tự đánh index cần áp dụng đúng thứ tự khi thực hiện truy vấn, vì nếu không, việc đánh index của bạn sẽ chẳng có tác dụng gì cả.

Nhược điểm khi đánh Index 

Hiệu quả là thế, tuy nhiên cách đánh Index trong cũng có một vài bất cập. Điển hình là sau khi thực hiện các quy trình thêm, cập nhật hoặc xóa dữ liệu trong cơ sở dữ liệu (DB), việc phải cập nhật lại các chỉ mục có thể làm chậm quá trình truy vấn. 

Ví dụ, ban đầu, "An" có chỉ mục là 0, "Anh" có chỉ mục là 1, và "Ánh" có chỉ mục là 2. Khi chúng ta xóa "An", "Anh" sẽ có chỉ mục là 0, "Ánh" sẽ có chỉ mục là 1, và tất cả các bản ghi sau cũng phải thay đổi tương tự.

Dù bị giảm tốc độ trong các hoạt động thêm, cập nhật, và xóa dữ liệu, tuy nhiên, trong thực tế, khi chúng ta thực hiện cập nhật hoặc xóa bản ghi, chúng ta thường cần truy vấn đến bản ghi đó trước. Do đó, tốc độ giảm trong quá trình này được bù đắp bởi tốc độ tìm kiếm nhanh hơn khi chúng ta sử dụng chỉ mục index. 

Tuy nhiên, nhược điểm này cũng không thật sự gây khó khăn cho bạn quá nhiều khi chúng ta có thể giảm số lần so sánh từ hàng ngàn lần xuống còn vài chục lần.

Khi nào thì không nên đánh Index?

Mặc dù Index được sử dụng để tăng hiệu suất của cơ sở dữ liệu, nhưng đôi khi việc áp dụng chúng không phải lúc nào cũng là lựa chọn tốt. Dưới đây là những tình huống bạn cần xem xét để quyết định có nên sử dụng đánh Index hay không:

• Không nên sử dụng Index trên các bảng nhỏ, có ít bản ghi.
• Tránh sử dụng Index trên các bảng mà các hoạt động update và insert diễn ra thường xuyên với tần suất lớn.
• Hạn chế sử dụng Index trên các cột thường xuyên thay đổi dữ liệu.
• Không nên áp dụng Index cho các cột chứa một số lượng lớn giá trị null.