Index Cơ Bản

Index là cấu trúc dữ liệu đặc biệt giúp SQL Server tìm kiếm và truy cập dữ liệu nhanh hơn nhiều so với quét toàn bộ bảng. Hiểu index đúng là yếu tố quyết định hiệu suất của ứng dụng database.

1. Tại sao cần Index?

Không có index, SQL Server phải thực hiện Table Scan - đọc toàn bộ mọi trang dữ liệu của bảng từ đầu đến cuối. Với bảng hàng triệu hàng, điều này cực kỳ tốn kém.

B-tree Structure

SQL Server dùng cấu trúc B-tree (Balanced Tree) cho hầu hết loại index:

                    [Root Page]
                   /     |      \
          [Branch]   [Branch]   [Branch]
          /    \     /    \     /    \
       [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
         ↓      ↓      ↓      ↓      ↓      ↓
       Data   Data   Data   Data   Data   Data

• Root page: điểm xuất phát tìm kiếm
• Branch pages: định hướng tìm kiếm
• Leaf pages: chứa dữ liệu thực (clustered) hoặc row pointer (non-clustered)

Seek vs Scan

2. Clustered Index

Khái niệm

Clustered Index quyết định thứ tự vật lý lưu trữ dữ liệu trong bảng. Leaf pages của clustered index chứa chính data rows.

• Mỗi bảng chỉ có tối đa 1 clustered index
• Khi tạo PRIMARY KEY, SQL Server mặc định tạo clustered index trên đó

-- Tạo clustered index tường minh
CREATE CLUSTERED INDEX CIX_Orders_OrderId ON Orders (OrderId);

-- PRIMARY KEY tự động tạo clustered index
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,  -- → Clustered index tự động
    OrderDate DATETIME,
    CustomerId INT
);

-- Tạo PRIMARY KEY nhưng NON-clustered
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY NONCLUSTERED,  -- Primary key không phải clustered
    OrderDate DATETIME,
    CustomerId INT
);

Heap vs Clustered Table

-- Kiểm tra bảng có clustered index không (heap nếu type_desc = HEAP)
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc
FROM sys.tables t
LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.type <= 1
ORDER BY t.name;

Chọn Clustered Index Key tốt

Một clustered index key tốt cần:

-- ✅ Tốt: INT IDENTITY - hẹp, unique, tăng dần
CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    ...
);

-- ❌ Kém: GUID ngẫu nhiên - random insert gây fragmentation cao
CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ...
);

-- ✅ Dùng NEWSEQUENTIALID() nếu cần GUID
CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    ...
);

3. Non-Clustered Index (NCX)

Khái niệm

Non-Clustered Index là cấu trúc B-tree riêng biệt với bảng dữ liệu:

• Leaf pages chứa key columns + row locator (clustered key hoặc RID)
• Mỗi bảng có thể có tối đa 999 non-clustered indexes

-- Tạo non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId 
ON Orders (CustomerId);

-- Với included columns
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Include
ON Orders (CustomerId)
INCLUDE (OrderDate, TotalAmount);

-- Composite key
CREATE NONCLUSTERED INDEX IX_OrderDetails_Composite
ON OrderDetails (OrderId, ProductId);

Key Columns vs Included Columns

Leaf Page của NCX:
┌─────────────────────────────────────────────┐
│ Key Column(s): CustomerId                   │
│ Included Columns: OrderDate, TotalAmount    │  ← Chỉ ở leaf level
│ Row Locator: OrderId (clustered key)        │
└─────────────────────────────────────────────┘

• Key columns: xuất hiện ở tất cả levels của B-tree, dùng cho seeking và sorting
• Included columns: chỉ ở leaf level, không dùng để seek nhưng tránh key lookup

-- Query này có thể được thỏa mãn hoàn toàn bởi index (covering index)
SELECT CustomerId, OrderDate, TotalAmount  -- Covered!
FROM Orders
WHERE CustomerId = 12345;                  -- Seek trên CustomerId

-- Nếu index không có TotalAmount → key lookup xảy ra
-- Với INCLUDE (TotalAmount) → không cần lookup

4. Index Pages: Page Splits và Fill Factor

Page Structure

SQL Server lưu dữ liệu theo pages 8KB. Mỗi page chứa nhiều rows.

Page Split

Khi INSERT vào giữa một clustered index mà page đã đầy:

1. SQL Server phải tạo page mới
2. Di chuyển ~50% dữ liệu sang page mới
3. Update pointer chain → tốn I/O, gây fragmentation

Trước split:        Sau split:
┌──────────┐        ┌──────────┐    ┌──────────┐
│  1, 2, 3 │  →    │  1, 2    │ →  │  2.5, 3  │
│  (full)  │        │ (50%)    │    │  (50%)   │
└──────────┘        └──────────┘    └──────────┘

Fill Factor

Fill Factor xác định % không gian trống để lại trên mỗi leaf page khi rebuild/create index.

-- Tạo index với fill factor 80% (20% để trống cho insert sau)
CREATE INDEX IX_Orders_OrderDate ON Orders (OrderDate)
WITH (FILLFACTOR = 80);

-- Rebuild với fill factor mới
ALTER INDEX IX_Orders_OrderDate ON Orders
REBUILD WITH (FILLFACTOR = 80);

-- Kiểm tra fill factor của các index
SELECT 
    i.name,
    i.fill_factor
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('Orders');

5. Covering Index

Một index được gọi là Covering Index khi nó chứa tất cả các cột mà query cần - không cần quay về bảng gốc để lấy thêm dữ liệu.

-- Query cần: WHERE CustomerId, SELECT OrderId, OrderDate, TotalAmount
SELECT OrderId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 12345
  AND OrderDate >= '2024-01-01';

-- ✅ Covering Index cho query trên
CREATE INDEX IX_Orders_Cover
ON Orders (CustomerId, OrderDate)     -- Key: dùng để seek + filter
INCLUDE (OrderId, TotalAmount);       -- Included: chỉ cần ở SELECT

-- ❌ Non-covering: chỉ có CustomerId
-- → SQL Server phải làm Key Lookup cho mỗi hàng tìm được
CREATE INDEX IX_Orders_CustomerId ON Orders (CustomerId);

Key Lookup vs Covering

-- Kiểm tra key lookup trong execution plan
-- Tìm queries có nhiều key lookups
SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_logical_reads DESC;

6. Index Seek vs Index Scan vs Table Scan

Index Seek

SQL Server đi qua B-tree từ root đến leaf để tìm đúng rows. Xảy ra khi:

• Có SARGable predicate (Search ARGument ABLE)
• Predicate match với leading key column của index

SARGable vs Non-SARGable

SARGable là predicate mà SQL Server có thể dùng để tìm kiếm trong index.

-- ✅ SARGable - có thể dùng index seek
WHERE CustomerId = 12345
WHERE OrderDate >= '2024-01-01'
WHERE LastName LIKE 'Nguyen%'     -- leading wildcard không có → SARGable
WHERE Price BETWEEN 100 AND 500

-- ❌ Non-SARGable - buộc index/table scan
WHERE YEAR(OrderDate) = 2024              -- Hàm trên cột
WHERE LEFT(LastName, 3) = 'Ngu'          -- Hàm trên cột
WHERE UPPER(Email) = 'TEST@GMAIL.COM'    -- Hàm trên cột
WHERE LastName LIKE '%Nguyen'             -- Leading wildcard
WHERE Price + 10 > 200                    -- Biểu thức trên cột
WHERE CAST(OrderId AS VARCHAR) = '123'   -- Implicit/explicit cast

-- ✅ Cách viết lại SARGable
-- Thay YEAR(OrderDate) = 2024
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'

-- Thay UPPER(Email) = 'TEST@GMAIL.COM'
-- Giải pháp: dùng collation case-insensitive (mặc định SQL Server)
WHERE Email = 'test@gmail.com'   -- CI collation → tự động case-insensitive

Khi SQL Server chọn Scan thay vì Seek

-- SQL Server chọn scan khi:
-- 1. Ước tính % rows được trả về cao (thường > 5-30%)
-- 2. Predicate không SARGable
-- 3. Không có thống kê tốt
-- 4. Index không phù hợp với query

-- Kiểm tra selectivity
SELECT 
    COUNT(DISTINCT CustomerId) * 1.0 / COUNT(*) AS Selectivity
FROM Orders;
-- Selectivity gần 1 = index seek rất hiệu quả
-- Selectivity gần 0 = có thể scan tốt hơn

7. Missing Index Recommendations

SQL Server tự động thu thập thông tin về các index mà nó “muốn có” trong sys.dm_db_missing_index_* DMVs.

-- Truy vấn missing indexes được khuyến nghị (sắp xếp theo impact)
SELECT TOP 20
    mid.statement AS TableName,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    -- Tạo lệnh CREATE INDEX gợi ý
    'CREATE INDEX IX_' + 
        REPLACE(REPLACE(mid.statement, '[', ''), ']', '') + '_' +
        ISNULL(REPLACE(mid.equality_columns, ', ', '_'), '') +
        ' ON ' + mid.statement + 
        ' (' + ISNULL(mid.equality_columns, '') + 
        CASE WHEN mid.inequality_columns IS NOT NULL 
             THEN CASE WHEN mid.equality_columns IS NOT NULL THEN ', ' ELSE '' END + mid.inequality_columns 
             ELSE '' END + ')' +
        ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

Cảnh báo: Đừng tạo tất cả suggested indexes mù quáng. Cân nhắc:

• Có bị overlap với index hiện có không?
• Có thực sự cần thiết không (improvement_measure đủ cao)?
• Tổng số indexes không nên quá nhiều → ảnh hưởng write performance

8. Index Maintenance: REBUILD vs REORGANIZE

Kiểm tra Fragmentation

-- Kiểm tra mức độ fragmentation của tất cả index trong database
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(),      -- Database ID (NULL = tất cả DB)
    NULL,         -- Object ID (NULL = tất cả tables)
    NULL,         -- Index ID (NULL = tất cả indexes)
    NULL,         -- Partition number
    'LIMITED'     -- Mode: LIMITED, SAMPLED, DETAILED
) ips
INNER JOIN sys.tables t ON ips.object_id = t.object_id
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 100   -- Chỉ xem index đủ lớn
ORDER BY ips.avg_fragmentation_in_percent DESC;

REORGANIZE vs REBUILD

-- REORGANIZE (online, ít lock)
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;

-- REBUILD (offline, defragment hoàn toàn)
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;

-- REBUILD ONLINE (SQL Server Enterprise)
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD WITH (ONLINE = ON);

-- REBUILD tất cả indexes của bảng
ALTER INDEX ALL ON Orders REBUILD WITH (FILLFACTOR = 85);

-- Script auto-maintain dựa trên fragmentation
DECLARE @fragmentation FLOAT;

SELECT @fragmentation = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Orders'), 
    INDEXPROPERTY(OBJECT_ID('Orders'), 'IX_Orders_CustomerId', 'IndexID'), NULL, 'LIMITED');

IF @fragmentation > 30
    ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;
ELSE IF @fragmentation > 10
    ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;

9. sys.indexes - Querying Index Metadata

-- Xem tất cả indexes của một bảng
SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique,
    i.is_primary_key,
    i.fill_factor,
    -- Key columns
    STRING_AGG(
        CASE WHEN ic.is_included_column = 0 THEN c.name END, ', '
    ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns,
    -- Included columns
    STRING_AGG(
        CASE WHEN ic.is_included_column = 1 THEN c.name END, ', '
    ) AS IncludedColumns
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Orders')
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor
ORDER BY i.type_desc, i.name;

-- Xem index usage statistics
SELECT 
    i.name AS IndexName,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id 
    AND i.index_id = ius.index_id 
    AND ius.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID('Orders')
ORDER BY (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0)) DESC;

-- Tìm các index không được dùng (candidates for removal)
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_updates,
    ius.last_user_seek
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id 
    AND i.index_id = ius.index_id 
    AND ius.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)   -- Chưa từng được seek
  AND (ius.user_scans IS NULL OR ius.user_scans = 0)   -- Chưa từng được scan
ORDER BY ISNULL(ius.user_updates, 0) DESC;  -- Vẫn tốn chi phí maintain