Tìm hiểu Index, Khi nào cần tạo index trong cơ sở dữ liệu ?

Index database là gì?

• Index là một cấu trúc dữ liệu được dùng để định vị và truy cập nhanh nhất vào dữ liệu trong các bảng database
• Index là một cách tối ưu hiệu suất truy vấn database bằng việc giảm lượng truy cập vào bộ nhớ khi thực hiện truy vấn

>>>Đọc thêm:

Khoá học SQL – Truy vấn cơ sở dữ liệu từ cơ bản đến nâng cao

Lộ trình trở thành Data Engineer (Kỹ sư dữ liệu)

Index database để làm gì?

• Giả sử ta có một bảng User lưu thông tin của người dùng, ta muốn lấy ra thông tin của người dùng có trường tên (Name) là “HauNguyen” . Ta có truy vấn SQL sau: SELECT * FROM User WHERE Name = 'HauNguyen';

Khi không có Index cho cột Name, truy vấn sẽ phải chạy qua tất cả các Row của bảng User để so sánh và lấy ra những Row thỏa mãn. Vì vậy, khi số lượng bản ghi lớn, việc này thực sự là ác mộng @@ Index được sinh ra để giải quyết vấn đề này. Nói đơn giản, index trỏ tới địa chỉ dữ liệu trong một bảng, giống như Mục lục của một cuốn sách (Gồm tên đề mục và số trang), nó giúp truy vấn trở nên nhanh chóng như việc bạn xem mục lục và tìm đúng trang cần đọc vậy 

• Index có thể được tạo cho một hoặc nhiều cột trong database. Index thường được tạo mặc định cho primary key, foreign key. Ngoài ra, ta cũng có thể tạo thêm index cho các cột nếu cần.

Cấu trúc của Index

• Index là một cấu trúc dữ liệu gồm:

1. Cột Search Key: chứa bản sao các giá trị của cột được tạo Index
2. Cột Data Reference: chứa con trỏ trỏ đến địa chỉ của bản ghi có giá trị cột index tương ứng

Khi nào cần tạo index

Để phát huy hiệu quả của việc sử dụng index, ta tạo index cho từng cột hoặc nhóm cột tham gia trong mệnh đề WHERE của câu lệnh truy vấn (chú ý điều kiện WHERE này phải hay dùng, ít dùng thì không nên tạo vì sẽ làm chậm câu lệnh DML và tốn dung lượng lưu trữ)

Ví dụ:

1. Tạo index tăng tốc độ truy vấn tên nhân viên:

SELECT * FROM emp WHERE emp_id=100;

Lệnh tạo index

CREATE INDEX idx ON emp (emp_id);

Nếu câu lệnh

SELECT * FROM emp WHERE emp_id=100, city_id=10;

Lệnh tạo index 2 cột emp_id, city_id:

CREATE INDEX idx ON emp (emp_id,city_id);

2. Tạo index tăng tốc độ truy vấn tên nhân viên chữ hoa:

SELECT * FROM emp WHERE UPPER(emp_name) LIKE 'JOH%';

Lệnh tạo index

CREATE INDEX idx ON emp (UPPER(emp_name));

3. Với câu lệnh truy vấn trên biểu thức:

SELECT a FROM t WHERE a + b * (c - 1) < 100;

Lệnh tạo index

CREATE INDEX idx ON t (a + b * (c - 1), a, b);

4. Tạo index hỗ trợ sắp xếp chuỗi ký tự dựa trên ngôn ngữ của từng quốc gia:

SELECT * FROM t_table ORDER BY name;

Lệnh tạo index

CREATE INDEX nls_indexON t_table NLSSORT(name, 'NLS_SORT = German'));

5. Sử dụng index trên nhiều cột khác nhau:

SELECT * FROM empWHERE UPPER(emp_name) LIKE 'JOH%'ORDER BY name;

Lệnh tạo index

CREATE INDEX empiON emp UPPER ((ename), NLSSORT(ename));

Đây là khuyến cáo của Oracle cũng như mọi cơ sở dữ liệu khác:

Các lưu ý khi tạo index:

• Index làm tăng tốc độ của các câu lệnh truy vấn nhưng làm chậm tốc độ của các câu lệnh DML. Vì thế, cần giảm tối thiểu index trên các bảng hay xảy ra thay đổi dữ liệu (DML).
• Đặt index trong các tablespace riêng biệt, không đặt index trong tablespace chứa rollback segment, temporary segment và table.
• Để làm giảm sự phân mảnh trong các tablespace dùng chứa index sử dụng chuẩn kích thước extent là bội số của 5*DB_BLOCK_SIZE.
• Hiệu năng có thể tăng lên nếu không sử dụng mệnh đề LOGGING. Vì vậy, cần xem xét khi sử dụng mệnh đề LOGGING khi tạo các index lớn.
• Vì các điểm vào cho index là nhỏ hơn đối với các rows được index nên index block sẽ có nhiều điểm vào cho một block. Vì vậy, giá trị của INITRANS đối với index nói chung nên đặt lớn hơn là giá trị của tham số này trên table sử dụng index.

KHÔNG PHẢI LÚC NÀO CŨNG TẠO INDEX VÌ ĐÔI LÚC QUÉT FULL NHANH HƠN INDEX KHI DỮ LIỆU TRẢ VỀ  NHIỀU (>4%)

Index chậm hơn quét full bảng, chỉ hiệu quả khi lượng dữ liệu trả về < 4%

Type	Số bản ghi	%/Tổng	FULL	Dùng index I7
0	45304503	15.26	Nhanh hơn
1	63454748	21.37	Nhanh hơn
2	123116203	41.46	Nhanh hơn
3	760951	0.26		Nhanh hơn
4	745957	0.25		Nhanh hơn
5	1917303	0.65		Nhanh hơn
6	442206	0.15		Nhanh hơn
7	3124481	1.05		Nhanh hơn
8	34567	0.01		Nhanh hơn
1	6	0.00		Nhanh hơn
10	464908	0.16		Nhanh hơn
11	56955192	19.18	Nhanh hơn
55	596007	0.20		Nhanh hơn
Tổng	296.917.032

select type, count(*) from test_table group by type;

(có 1 INDEX theo trường TYPE test_table_I7)

–Plan: dùng index

–SELECT STATEMENT  ALL_ROWSCost: 51,365  Bytes: 3  Cardinality: 1              

—    3 SORT AGGREGATE  Bytes: 3  Cardinality: 1          

––        2 PARTITION RANGE ALL  Cost: 51,365  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 – #208    

—            1 INDEX STORAGE FAST FULL SCAN INDEX test_table_I7 Cost: 51,365  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 – #208

—36.766s khi dùng index test_table_I7

select count(*) from test_table where type not in  (’10’, ’11’, ’55’);

–Plan: Quét full

–SELECT STATEMENT  ALL_ROWSCost: 433,385  Bytes: 3  Cardinality: 1              

—    3 SORT AGGREGATE  Bytes: 3  Cardinality: 1          

—        2 PARTITION RANGE ALL  Cost: 433,385  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 – #208    

—            1 TABLE ACCESS STORAGE FULL TABLE test_table Cost: 433,385  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 – #208

—13.81s khi quét FULL bảng

select  /*+ FULL(a)*/   count(*) from test_table a where type not in  (’10’, ’11’, ’55’);

Đã thử nghiệm và kết quả như trên: Với dữ liệu trả về < 1% thì dùng index nhanh hơn, dữ liệu chiếm nhiều như  15,19,21,41% như trên dùng full nhanh hơn index

–> Dữ liệu trả về (select * from table_a where …) <4% thì quyết định tạo index và thường xuyên sử dụng (không thường xuyên sử dụng thì tạo, chạy xong là xóa)

>>>Đọc thêm:

Khoá học SQL – Truy vấn cơ sở dữ liệu từ cơ bản đến nâng cao

Lộ trình trở thành Data Engineer (Kỹ sư dữ liệu)