Index và các kiểu đánh index

1. Index là gì?

index là việc cấu trúc dữ liệu, lưu trữ theo một cơ chế nào đó để tìm ra các record một cách nhanh chóng.

Index là rất cần thiết để tăng performace và ngày càng trở nên quan trọng hơn nếu dữ liệu trong database của bạn ngày một lớn, đối với các hệ thống nhỏ thì việc đánh index hầu như không có ý nghĩa, nhưng một khi data trong db ngày một tăng, thì các truy vấn db có thể trở nên chậm chập tỷ lệ với độ tăng data trong db.

Ưu, nhược điểm:

Ưu điểm:

index đóng vai trò là tăng tốc truy xuất, do truy xuất cũng giống như khi bạn tra từ điễn, HT cần tìm kiếm thông tin trong hàng ngàn dữ liệu nên việc sắp xếp là cần thiết, mặt khác, do việc sắp xếp các dữ liệu là duy nhất (như trong từ điễn không thể có hai từ như nhau vì như vậy rất khó sắp xếp) nên có thể cho rằng index cũng tăng tính duy nhất trong CSDL. Và cuỗi cùng, như đã biết các câu lệnh JOIN, ORDER BY, GROUP BY cũng yêu cầu việc truy xuất dữ liệu liên tục nên index đồng thời cũng tăng tốc cho việc này.

Nhược điểm:

- Thứ 1, để có index, bạn cần tạo nó, mà việc này rất tốn thời gian, đối với các project mà chúng ta làm thì chả thấy gì, nhưng nếu đó là một CSDL lớn thì việc này là cực kì lâu.

- Thứ 2, do index là tạo một cấu trúc bảng trong nên việc này gây tốn tài nguyên (nếu ai có cài google search desktop sẽ biết điều này).

- Và cuối cùng, do index là sắp xếp các dữ liệu nên nếu có sự thay đổi thì index cũng được update theo.

2. Các loại index

index trong CSDL có hai loại: Clustered Index và Non-Clustered Index.

a) Clustered Index

Clustered index: thường được tự tạo ra khi bảng có primary key do primary key đã duy trì độ duy nhất dữ liệu của cột, nên có thể nói clustered index chính là unique index. Trong clustered index, các dữ liệu ở cấu trúc bảng trong được sắp xếp một cách vật lý, tức là trong clustered index, dữ liệu bảng trong được sắp xếp đúng theo thư mục cây dựa vào bảng chữ cái Trong một bảng CHỈ được có duy nhất một clustered index

b) Non-Clustered Index

khác với clustered Index, non-Clustered Index không sắp xếp dữ liệu theo một trật tự vật lý như clustered mà là "loạn xà ngầu" trong bảng thông tin, miễn sao nó nằm trong một logic do index qui định . trong một bảng có thể chứa đến 249 non-clustered index. còn cách hoạt động thì tương tự clustered index, có khác là khi truy xuất đến bảng thông tin cuối thì thông tin không được sắp xếp theo trật tự thôi ví dụ như A--->C---->B----->E---->D.....

3. Cách đánh index

Mỗi một kiểu được thiết kế cho các mục đích khác nhau, Index được thực thì ở tầng lưu trữ dữ liệu, do đó nó không có một chuẩn nào cả. Có rất nhiều kiểu index mà điển hình là B-tree index, hash index, R-tree index, full-text index … Do R-Tree được sử dụng cho các loại dữ liệu không gian spacial data và thường ít khi gặp phải nên bài viết này chúng ta sẽ tập chung so sánh 2 loại index là B-Tree và Hash:

a) B-Tree index

Thông thường khi ta nói đến index mà không chỉ rõ loại index thì đó là ám chỉ đến B-Tree index, nó sử dụng B-Tree data structure để lưu trữ dữ liệu. Hầu hết các bộ máy lưu trữ dữ liệu của MySQL đều hỗ trợ B-TRee index.

Ý tưởng chính của B-Tree đó là tất cả các giá trị được lưu theo một trật tự. và mỗi node lá sẽ có chung khoảng cách với gốc.

Một B-Tree index có thể làm nhanh tốc độ truy vấn là vì bộ máy lưu trữ dữ liệu sẽ không scan dữ liệu trên toàn bộ bảng để tìm dữ liệu cần có. Thay vào đó nó sẽ bắt đầu từ node gốc, mỗi một điểm trên nốt gốc sẽ giữ những con trỏ trỏ tới những nốt con và bộ máy lưu trữ sẽ dựa vào những con trỏ để scan dữ liệu. Nó sẽ tìm con trỏ bên phải bằng cách nhìn vào dữ liệu ở node pages, các node page có chứa dữ liệu của các node con. Bộ máy lưu trữ sẽ xác định việc dữ liệu có tồn tại hay không hay tìm được dữ liệu ở leaf page.

b) Hash Index

Hash index có các đặc điểm sau: Hash index có một vài đặc điểm khác biệt so với Btree index. Dữ liệu index được tổ chức theo dạng Key - Value được liên kết với nhau. Khác với B-Tree, thì Hash index chỉ nên sử dụng trong các biểu thức toán tử là = và <>. Không sử dụng cho toán từ tìm kiếm 1 khoảng giá trị như > hay < . Không thể tối ưu hóa toán tử ORDER BY bằng việc sử dụng Hash index bởi vì nó không thể tìm kiếm được phần từ tiếp theo trong Order. Toàn bộ nội dung của Key được sử dụng để tìm kiếm giá trị records, khác với B-Tree một phần của node cũng có thể được sử dụng để tìm kiếm. Hash có tốc độ nhanh hơn kiểu Btree. => Việc chọn index theo kiểu B-Tree hay Hash ngoài yếu tố về mục đích sử dụng index thì nó còn phụ thuộc vào việc Storage Engine có hỗ trợ loại index đó hay không. Ví dụ MyISAM, InnoDB hay Archive chỉ hỗ trợ B-Tree, trong khi MyISAM lại hỗ trợ cho cả 2.