Bài viết này tôi sẽ giúp anh em biết được tậm quan trọng của việc lựa chọn thứ tự các cột trong Composite Index.
1. Composite Index là gì ?
Composite Index là kỹ thuật sử dụng Index kết hợp trên nhiều cột của bảng.
Ví dụ như sau:
Bảng EMP lưu thông tin các nhân viên trong một công ty
Bảng này có chứa một số trường thông tin như sau:
- ID
- FIRST_NAME
- LAST_NAME
- SALARY
- NOTE
- ADDRESS
- GENDER
Nếu bạn chỉ tạo Index trên một cột duy nhất, cột FIRST_NAME thì Index này có thuật ngữ gọi là Index đơn lẻ (Single Column Index)
Nếu chúng ta tạo Index trên đồng thời 2 cột (ID, LAST_NAME) thì đây gọi là một composite Index (Index kết hợp).
Lưu ý rằng:
- Việc tạo Index trên cả 2 cột (ID, LAST_NAME) hoàn toàn khác với việc bạn tạo 2 Index đơn lẻ (một Index trên cột ID và một Index trên cột LAST_NAME)
2. Cú pháp tạo Composite Index là gì?
Để thực hiện tạo Composite Index trên 2 cột ID, LAST_NAME chúng ta làm như sau:
CREATE INDEX HUYTQ.IDX_ID_LASTNAME ON HUYTQ.EMP (ID, LAST_NAME);
Để thực hiện tạo Composite Index trên 2 cột LAST_NAME, ID (đổi thứ tự lại so với Index bên trên), chúng ta làm như sau:
CREATE INDEX HUYTQ.IDX_LASTNAME_ID ON HUYTQ.EMP(LAST_NAME, ID);
3. Vấn đề quan trọng ảnh hưởng đến hiệu năng của Composite Index
Thứ tự của các cột được lựa chọn trong Composite Index sẽ ảnh hưởng rất lớn đến hiệu năng khi sử dụng của câu lệnh SQL.
Chúng ta cùng xem một ví dụ sau để thấy rõ điều này
Giả sử chúng ta cần tối ưu câu lệnh sau
select * from emp where last_name is null
Một số thông tin thống kê về bảng, để bạn có thể hiểu rõ hơn về hiện trạng câu lệnh trên
Bảng EMP có 1,100,014 bản ghi, dung lượng của bảng này là 1.12GB
Số bản ghi thỏa mãn điều kiện LAST_NAME IS NULL là 01 bản ghi
3.1. Chiến lược thực thi của câu lệnh khi chưa có bất kỳ Index nào
select * from emp where last_name is null
Câu lệnh này có chiến lược thực thi là: TABLE ACCESS FULL (quét toàn bộ block dữ liệu của bảng EMP). Chính vì chiến lược cần thực hiện quét qua quá nhiều block, nên chi phí (COST) của câu lệnh rất lớn: 40K.
3.2. Câu lệnh khi chúng ta chỉ tạo Index trên cột LAST_NAME
Tạo Index trên một cột LAST_NAME với lệnh sau:
CREATE INDEX IDX_LAST_NAME ON emp (LAST_NAME);
Chiến lược thực thi của câu lệnh sau khi có Index:
Câu lệnh có chiến lược thực thi không khác gì lúc trước: vẫn phải quét toàn bộ các block dữ liệu của bảng (TABLE ACCESS FULL), và vì thế chi phí của câu lệnh không thay đổi: COST = 40K.
Index bên trên không có hiệu quả vì kiến trúc của B-TREE INDEX không thể lưu trữ được giá trị NULL.
Bạn có thể đọc bài viết sau của tôi để hiểu hơn về B-TREE INDEX: B-Tree Index và Bitmap Index khác nhau thế nào khi tối ưu SQL
3.3. Đánh giá hiệu năng câu lệnh khi chúng ta tạo Composite Index trên cột ID, LAST_NAME
Câu lệnh tạo Index như sau:
CREATE INDEX HUYTQ.IDX_ID_LASTNAME ON HUYTQ.EMP (ID, LAST_NAME);
Chiến lược thực thi của câu lệnh lúc này là:
Câu lệnh lúc này đã có thể sử dụng INDEX IDX_ID_LAST_NAME mà chúng ta vưa tạo. Giải thuật được sử dụng ở đây là INDEX SKIP SCAN.
Chi phí câu lệnh lúc này đã cải thiện 10 lần (từ COST = 40K xuống COST = 4K).
3.4. Đánh giá hiệu năng của câu lệnh khi chúng ta tạo Composite Index trên cột LAST_NAME, ID
Câu lệnh tạo Index như sau:
CREATE INDEX HUYTQ.IDX_LASTNAME_ID ON HUYTQ.EMP(LAST_NAME, ID)
Chiến lược thực thi của câu lệnh lúc này là:
Chúng ta có thể thấy: COST của câu lệnh đã giảm xuống còn 2 khi sử dụng COMPOISTE INDEX (LAST_NAME, ID).
Giải thuật mà hệ thống sử dụng tại đây là INDEX RANGE SCAN.
3.5. Kết luận
Trong ví dụ trên, chúng ta có thể thấy một số vấn đề:
- Thứ nhất: việc sử dụng Index cần có một chiến lược bài bản, không phải cứ tạo Index là hệ thống sẽ sử dụng.
- Thứ hai: Một trong các yếu tố quan trọng ảnh hưởng đến hiệu năng của câu lệnh khi sử dụng Composite Index đó là: thứ tự của các cột khi chúng ta lựa chọn đánh Index
- Cùng là Index trên 2 cột LAST_NAME và ID, tuy nhiên Index trên cụm (LAST_NAME, ID) có chi phí va hiệu năng tốt hơn 2000 lần so với Index trên cột (ID, LAST_NAME)
- Thứ ba: Cần hiểu rõ về các giải thuật sử dụng Index, nếu bạn muốn thật sự làm chủ kỹ năng tối ưu SQL và Index.
- Khi phân tích các bài toán trong dự án thực tế, bạn cần hiểu rõ thế nào là INDEX RANGE SCAN, thế nào là INDEX SKIP SCAN.
- Tại sao trong trường hợp bên trên, hệ thống phải sử dụng INDEX SKIP SCAN, mà không thể sử dụng INDEX RANGE SCAN cho cả 2 trường hợp.
- Có phải INDEX SKIP SCAN tồi hơn INDEX RANGE SCAN hay không? (không phải đâu, tùy trường hợp thôi anh em nhá)
- …
4. Nếu bạn muốn làm chủ toàn bộ các kỹ thuật tối ưu, tiết kiếm 2-3 năm tự mình mày mò, tìm kiếm trên Google
Bạn biết được tất cả nhữn kỹ thuật và kinh nghiệm tối ưu trong các hệ thống trọng yếu (tối ưu Core banking, Core chứng khoán,…) bằng cách tham dự chương trình Từ điển tối ưu 100x hiệu năng: https://wecommit.com.vn/tu-dien-toi-uu-100x-hieu-nang/
5. Một số bài viết khác liên quan
Nếu bạn chưa biết về chiến lược thực thi, COST của câu lệnh là gì, bạn có thể xem lại một số bài viết khác của tôi:
- Chiến lược thực thi của câu lệnh trong tối ưu SQL: https://wecommit.com.vn/sql-execution-plan-trong-toi-uu-sql/
6. Thông tin tác giả
Chào các bạn, tôi là Trần Quốc Huy – Founder Wecommit và đồng thời là tác giả của chương trình Từ điển tối ưu 100x hiệu năng.
Tôi chỉ tập trung vào 1 lĩnh vực duy nhất hơn 10 năm nay: Tối ưu cơ sở dữ liệu.
Các bạn có thể liên hệ với tôi qua các kênh sau
Facebook cá nhân của tôi: https://www.facebook.com/tran.q.huy.71/
Youtube: https://www.youtube.com/channel/UCtsYzL7iN7rBCPnkjYp4XYw
