Composite index là gì?

Trong cơ sở dữ liệu (database), chỉ mục phức hợp (composite index) hoặc chỉ mục nhiều cột (multi-column index) là chỉ mục (index) dựa trên một số cột (column).

Ví dụ chúng ta có bảng (table) users như sau:

ID | first_name | last_name    | class      | position |
------------------------------------------------------------------------------
 1 | Teemo      | Shroomer     | Specialist | Top      |
 2 | Cecil      | Heimerdinger | Specialist | Mid      |
 3 | Annie      | Hastur       | Mage       | Mid      |
 4 | Fiora      | Laurent      | Slayer     | Top      |
 5 | Garen      | Crownguard   | Fighter    | Top      |
 6 | Malcolm    | Graves       | Specialist | ADC      |
 7 | Irelia     | Lito         | Figher     | Top      |
 8 | Janna      | Windforce    | Controller | Support  |
 9 | Jarvan     | Lightshield  | Figher     | Top      |
10 | Katarina   | DuCouteau    | Assassin   | Mid      |
11 | Kayle      | Hex          | Specialist | Top      |
12 | Emilia     | LeBlanc      | Mage       | Mid      |
13 | Lee        | Sin          | Fighter    | Jungle   |
14 | Lux        | Crownguard   | Mage       | Mid      |
15 | Sarah      | Fortune      | Marksman   | ADC      |
16 | Morgana    | Hex          | Controller | Support  |
17 | Orianna    | Reveck       | Mage       | Mid      |
18 | Sona       | Buvelle      | Controller | Support  |
19 | Jericho    | Swain        | Mage       | Mid      |
20 | Shauna     | Vayne        | Marksman   | ADC      |
21 | Xin        | Zhao         | Fighter    | Jungle   |
22 | Yorick     | Mori         | Tank       | Top      |
23 | Wu         | Kong         | Fighter    | Jungle   |

Tạo một chỉ mục phức hợp (composite index) trên cột class và cột position

CREATE INDEX class_pos_index ON users (class, position);

Sẽ tạo một chỉ mục phức hợp (composite index), được sắp xếp theo cách nối các cột đó với một con trỏ (pointer) trỏ đến khóa chính (primary key) giống như:

class-position       Primary Key
--------------------------------
AssassinMid       -> 10
ControllerSupport -> 16
ControllerSupport -> 18
ControllerSupport -> 8
FigherTop         -> 7
FigherTop         -> 9
FighterJungle     -> 13
FighterJungle     -> 21
FighterJungle     -> 23
FighterTop        -> 5
MageMid           -> 12
MageMid           -> 14
MageMid           -> 17
MageMid           -> 19
MageMid           -> 3
MarksmanADC       -> 15
MarksmanADC       -> 20
SlayerTop         -> 4
SpecialistADC     -> 6
SpecialistMid     -> 2
SpecialistTop     -> 1
SpecialistTop     -> 11
TankTop           -> 22

Với chỉ mục phức hợp (composite index) này, một truy vấn (query) như:

SELECT * FROM users 
WHERE 
  class = 'Specialist'
AND 
  position = 'Top';

Sẽ có thời gian truy xuất được cải thiện, vì chỉ mục phức hợp (composite index) được sắp xếp theo class-position. Cơ sở dữ liệu có thể tìm thấy SpecialistTop trong O(log_2 (n)) thay vì đọc toàn bộ bảng.

Tuy nhiên, một truy vấn như:

SELECT * FROM users WHERE position = 'Top';

sẽ KHÔNG được hưởng lợi từ chỉ mục phức hợp (composite index) này vì position là cột thứ hai. Không thể sử dụng chỉ mục phức hợp (composite index) được sắp xếp theo class-position để tìm nhanh bản ghi theo position.

Do đó, thứ tự của các cột cấu thành bao gồm một chỉ mục phức hợp (composite index) là rất quan trọng. Quy tắc là nếu một chỉ mục phức hợp (composite index) được tạo trên column1, column2, column3,…, columnN. Thì các truy vấn như bên dưới sẽ có hiệu suất tăng (performance):

SELECT * FROM table 
WHERE column1 = 'value';
SELECT * FROM table 
WHERE column1 = 'value1'
AND column2 = 'value2';
SELECT * FROM table 
WHERE column1 = 'value1'
AND column2 = 'value2'
AND column3 = 'value3'

...

SELECT * FROM table 
WHERE column1 = 'value1'
AND column2 = 'value2'
AND column3 = 'value3'  
  ...
AND columnN = 'valueN'