Index và Tối Ưu Hiệu Suất trong SQL Server

Trong bài viết này, chúng ta sẽ tìm hiểu về Index và các kỹ thuật tối ưu hiệu suất trong SQL Server.

Index là gì?

Index là cấu trúc dữ liệu giúp tăng tốc độ truy vấn dữ liệu. SQL Server hỗ trợ nhiều loại index khác nhau:

Clustered Index

-- Tạo Clustered Index
CREATE CLUSTERED INDEX IX_Orders_OrderID
ON Orders(OrderID);

Non-Clustered Index

-- Tạo Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_Customers_City
ON Customers(City);

Composite Index

-- Tạo Composite Index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON Orders(CustomerID, OrderDate);

Filtered Index

-- Tạo Filtered Index
CREATE NONCLUSTERED INDEX IX_Products_Active
ON Products(ProductID, ProductName)
WHERE Discontinued = 0;

Tối ưu hiệu suất truy vấn

Sử dụng Execution Plan

-- Bật Execution Plan
SET SHOWPLAN_TEXT ON;
GO

-- Truy vấn cần phân tích
SELECT 
    c.CustomerName,
    COUNT(o.OrderID) as OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

Tối ưu JOIN

-- Sử dụng INNER JOIN thay vì LEFT JOIN khi có thể
SELECT 
    c.CustomerName,
    o.OrderID
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- Sử dụng EXISTS thay vì IN
SELECT ProductName
FROM Products p
WHERE EXISTS (
    SELECT 1 
    FROM OrderDetails od 
    WHERE od.ProductID = p.ProductID
);

Tối ưu WHERE

-- Sử dụng Index Seek
SELECT ProductName
FROM Products
WHERE ProductID = 1;

-- Tránh sử dụng hàm trong WHERE
-- Không tốt
SELECT OrderID
FROM Orders
WHERE YEAR(OrderDate) = 2023;

-- Tốt hơn
SELECT OrderID
FROM Orders
WHERE OrderDate >= '2023-01-01' 
AND OrderDate < '2024-01-01';

Monitoring và Maintenance

Kiểm tra Index Fragmentation

SELECT 
    OBJECT_NAME(ips.OBJECT_ID) as TableName,
    i.name as IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, NULL) ips
JOIN sys.indexes i ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;

Rebuild Index

-- Rebuild một index
ALTER INDEX IX_Orders_OrderID ON Orders REBUILD;

-- Rebuild tất cả index của một bảng
ALTER INDEX ALL ON Orders REBUILD;

Update Statistics

-- Update statistics cho một bảng
UPDATE STATISTICS Orders;

-- Update statistics cho toàn bộ database
EXEC sp_updatestats;

Best Practices

1. Tạo index cho các cột thường xuyên tìm kiếm
2. Tránh tạo quá nhiều index
3. Thường xuyên bảo trì index
4. Sử dụng Execution Plan để phân tích
5. Tối ưu câu truy vấn

Các công cụ monitoring

Dynamic Management Views (DMVs)

-- Kiểm tra index usage
SELECT 
    OBJECT_NAME(i.object_id) as TableName,
    i.name as IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id
    AND ius.index_id = i.index_id;

SQL Server Profiler

• Theo dõi các truy vấn chậm
• Phân tích deadlock
• Kiểm tra resource usage

Kết luận

Index và tối ưu hiệu suất là những chủ đề quan trọng trong SQL Server. Hiểu và áp dụng đúng các kỹ thuật này sẽ giúp cải thiện đáng kể hiệu suất của database. Trong bài viết tiếp theo, chúng ta sẽ tìm hiểu về Backup và Restore trong SQL Server.

admin

Biên tập viên, Hướng Nghiệp Dữ Liệu

737 Bài viết

15.4k Người theo dõi

120k+ Lượt đọc

Biên tập viên nội dung tại Hướng Nghiệp Dữ Liệu, phụ trách tổng hợp và biên soạn các bài viết về lập trình Python, dữ liệu và công nghệ.

Theo dõi Liên hệ Zalo