Có thể tạo bao nhiêu chỉ mục trên một bảng. Máy chủ Sql - Thứ tự có quan trọng khi tạo chỉ mục bao trùm trong Microsoft SQL không? Khái niệm cơ bản về chỉ mục trong SQL Server

chỉ mục ROWID là các đối tượng cơ sở dữ liệu cung cấp khả năng hiển thị tất cả các giá trị trong một cột của bảng, cũng như ROWID của tất cả các hàng trong bảng chứa các giá trị của cột.

ROWID là một cột giả, là mã định danh duy nhất cho một hàng trong bảng và thực sự mô tả vị trí thực tế chính xác của hàng cụ thể đó. Dựa trên thông tin này Lời tiên tri sau đó có thể tìm thấy dữ liệu liên quan đến hàng của bảng. Mỗi lần một hàng được di chuyển, xuất, nhập hoặc bất kỳ thao tác nào khác làm thay đổi vị trí của nó, ROWID dòng vì nó chiếm một vị trí vật lý khác. Để lưu trữ dữ liệu ROWID Yêu cầu 80 bit (10 byte). Số nhận dạng ROWID bao gồm bốn thành phần: số đối tượng (32 bit), số tệp tương đối (10 bit), số khối (22 bit) và số dòng (16 bit). Các mã định danh này được hiển thị dưới dạng chuỗi 18 ký tự cho biết vị trí của dữ liệu trong cơ sở dữ liệu, với mỗi ký tự được biểu thị ở định dạng cơ sở 64 bao gồm các ký tự A-Z, a-z, 0-9, + và /. Sáu ký tự đầu tiên là số đối tượng dữ liệu, ba ký tự tiếp theo là số tệp tương đối, sáu ký tự tiếp theo là số khối và ba ký tự cuối cùng là số dòng.

Ví dụ:

SELECT gia đình, ROWID TỪ sinh viên;

FAM ROWID

——————————————

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

Trong cơ sở dữ liệu Lời tiên tri các chỉ mục được sử dụng cho các mục đích khác nhau: để đảm bảo tính duy nhất của các giá trị trong cơ sở dữ liệu, để cải thiện hiệu suất tìm kiếm các bản ghi trong bảng, v.v. Hiệu suất được cải thiện bằng cách đưa tham chiếu đến cột hoặc các cột được lập chỉ mục trong tiêu chí tìm kiếm cho dữ liệu trong bảng. TRONG Lời tiên tri chỉ mục có thể được tạo trên bất kỳ cột nào trong bảng ngoại trừ các cột DÀI. Các chỉ mục phân biệt giữa ứng dụng không nhạy cảm với tốc độ và ứng dụng hiệu suất cao, đặc biệt khi làm việc với các bảng lớn. Tuy nhiên, trước khi quyết định tạo chỉ mục, bạn cần cân nhắc những ưu và nhược điểm liên quan đến hiệu suất hệ thống. Hiệu suất sẽ không được cải thiện nếu bạn chỉ nhập một chỉ mục và quên nó đi.

Mặc dù cải tiến hiệu suất lớn nhất đến từ việc tạo chỉ mục trên một cột trong đó tất cả các giá trị là duy nhất, nhưng bạn có thể nhận được kết quả tương tự cho các cột chứa giá trị trùng lặp hoặc NULL. Không nhất thiết các giá trị cột phải là duy nhất để tạo chỉ mục. Dưới đây là một số đề xuất giúp bạn đạt được mức tăng hiệu suất mong muốn khi sử dụng chỉ mục tiêu chuẩn và chúng tôi cũng sẽ xem xét các vấn đề liên quan đến sự cân bằng giữa hiệu suất và mức tiêu thụ dung lượng ổ đĩa khi tạo chỉ mục.

Việc sử dụng chỉ mục để tra cứu thông tin trong bảng có thể mang lại những cải tiến hiệu suất đáng kể so với việc quét các bảng có cột không được lập chỉ mục. Tuy nhiên, việc lựa chọn chỉ số phù hợp không hề dễ dàng chút nào. Tất nhiên, một cột có tất cả các giá trị là duy nhất sẽ thích hợp hơn để lập chỉ mục bằng chỉ mục cây B, nhưng một cột không đáp ứng các yêu cầu này là một ứng cử viên sáng giá miễn là khoảng 10% số hàng của nó chứa các giá trị giống hệt nhau và không nhiều. Các cột “chuyển đổi” hoặc “cờ”, ví dụ như các cột lưu trữ thông tin về giới tính của một người, không phù hợp với chỉ mục cây B. Các cột được sử dụng để lưu trữ một số lượng nhỏ “giá trị đáng tin cậy”, cũng như các cột lưu trữ một số giá trị nhất định cũng không phù hợp, sau đó là các dấu hiệu, ví dụ: “độ tin cậy” hoặc “không đáng tin cậy”, “hoạt động” hoặc “không hoạt động”, “có” hoặc “không”, v.v., v.v. Cuối cùng, các chỉ mục có khóa đảo ngược là thường được sử dụng ở nơi nó được cài đặt và vận hành Lời tiên tri Parallel Server và bạn cần tăng mức độ song song trong cơ sở dữ liệu lên mức tối đa.

6. Tối ưu hóa chỉ mục và hiệu suất

Chỉ mục trong cơ sở dữ liệu: mục đích, tác động đến hiệu suất, nguyên tắc tạo chỉ mục

6.1 Chỉ mục dùng để làm gì?

Chỉ mục là các cấu trúc đặc biệt trong cơ sở dữ liệu cho phép bạn tăng tốc độ tìm kiếm và sắp xếp theo một trường hoặc tập hợp trường cụ thể trong bảng, đồng thời cũng được sử dụng để đảm bảo tính duy nhất của dữ liệu. Cách dễ nhất để so sánh các chỉ mục là sử dụng các chỉ mục trong sách. Nếu không có chỉ mục thì chúng ta sẽ phải xem qua toàn bộ cuốn sách để tìm đúng vị trí, nhưng với chỉ mục, hành động tương tự có thể được thực hiện nhanh hơn nhiều.

Thông thường, càng nhiều chỉ mục thì hiệu suất truy vấn cơ sở dữ liệu càng tốt. Tuy nhiên, nếu số lượng chỉ mục tăng quá mức, hiệu suất của các hoạt động sửa đổi dữ liệu (chèn/thay đổi/xóa) sẽ giảm và kích thước của cơ sở dữ liệu tăng lên, do đó, việc thêm chỉ mục cần được xử lý một cách thận trọng.

Một số nguyên tắc chung liên quan đến việc tạo chỉ mục:

· Các chỉ mục phải được tạo cho các cột được sử dụng trong các phép nối, thường được sử dụng cho các hoạt động tìm kiếm và sắp xếp. Xin lưu ý rằng các chỉ mục luôn được tạo tự động cho các cột bị ràng buộc khóa chính. Thông thường, chúng được tạo cho các cột có khóa ngoại (trong Access - tự động);

· một chỉ mục phải được tạo tự động cho các cột có ràng buộc về tính duy nhất;

· Tốt nhất nên tạo chỉ mục cho những trường trong đó có số lượng giá trị lặp lại tối thiểu và dữ liệu được phân bổ đều. Oracle có các chỉ mục bit đặc biệt cho các cột có số lượng lớn các giá trị trùng lặp; SQL Server và Access không cung cấp loại chỉ mục này;

· nếu việc tìm kiếm được thực hiện liên tục trên một tập hợp các cột cụ thể (đồng thời), thì trong trường hợp này, có thể tạo một chỉ mục tổng hợp (chỉ trong SQL Server) - một chỉ mục cho một nhóm cột;

· Khi thực hiện thay đổi đối với bảng, các chỉ mục được xếp chồng lên bảng này sẽ tự động thay đổi. Kết quả là chỉ mục có thể bị phân mảnh cao, ảnh hưởng đến hiệu suất. Bạn nên kiểm tra định kỳ mức độ phân mảnh chỉ mục và chống phân mảnh chúng. Khi tải một lượng lớn dữ liệu, đôi khi, trước tiên bạn nên xóa tất cả các chỉ mục và tạo lại chúng sau khi thao tác hoàn tất;

· Các chỉ mục có thể được tạo không chỉ cho các bảng mà còn cho các dạng xem (chỉ trong SQL Server). Ưu điểm - khả năng tính toán các trường không phải tại thời điểm yêu cầu mà tại thời điểm các giá trị mới xuất hiện trong bảng.

Một trong những cách quan trọng nhất để đạt được năng suất cao Máy chủ SQL là việc sử dụng các chỉ mục. Chỉ mục giúp tăng tốc quá trình truy vấn bằng cách cung cấp khả năng truy cập nhanh vào các hàng dữ liệu trong bảng, giống như chỉ mục trong sách giúp bạn nhanh chóng tìm thấy thông tin mình cần. Trong bài viết này tôi sẽ cung cấp một cái nhìn tổng quan ngắn gọn về các chỉ mục trong Máy chủ SQL và giải thích cách chúng được tổ chức trong cơ sở dữ liệu cũng như cách chúng giúp tăng tốc các truy vấn cơ sở dữ liệu.

Các chỉ mục được tạo trên bảng và xem các cột. Các chỉ mục cung cấp cách tìm kiếm dữ liệu nhanh chóng dựa trên các giá trị trong các cột đó. Ví dụ: nếu bạn tạo chỉ mục trên khóa chính rồi tìm kiếm một hàng dữ liệu bằng các giá trị khóa chính thì Máy chủ SQLđầu tiên sẽ tìm giá trị chỉ mục và sau đó sử dụng chỉ mục để tìm nhanh toàn bộ hàng dữ liệu. Nếu không có chỉ mục, việc quét toàn bộ tất cả các hàng trong bảng sẽ được thực hiện, điều này có thể có tác động đáng kể đến hiệu suất.
Bạn có thể tạo chỉ mục trên hầu hết các cột trong bảng hoặc dạng xem. Ngoại lệ chủ yếu là các cột có kiểu dữ liệu để lưu trữ các đối tượng lớn ( LOB), chẳng hạn như hình ảnh, chữ hoặc biến thể (tối đa). Bạn cũng có thể tạo chỉ mục trên các cột được thiết kế để lưu trữ dữ liệu ở định dạng XML, nhưng các chỉ mục này có cấu trúc hơi khác so với các chỉ mục tiêu chuẩn và việc xem xét chúng nằm ngoài phạm vi của bài viết này. Ngoài ra, bài viết không đề cập đến kho chuyên mục chỉ mục. Thay vào đó, tôi tập trung vào những chỉ mục được sử dụng phổ biến nhất trong cơ sở dữ liệu Máy chủ SQL.
Một chỉ mục bao gồm một tập hợp các trang, các nút chỉ mục, được tổ chức theo cấu trúc cây - cây cân đối. Cấu trúc này có bản chất phân cấp và bắt đầu bằng nút gốc ở đầu hệ thống phân cấp và các nút lá, các lá, ở phía dưới, như thể hiện trong hình:

Khi bạn truy vấn một cột được lập chỉ mục, công cụ truy vấn sẽ bắt đầu ở đầu nút gốc và đi xuống các nút trung gian, với mỗi lớp trung gian chứa thông tin chi tiết hơn về dữ liệu. Công cụ truy vấn tiếp tục di chuyển qua các nút chỉ mục cho đến khi nó đạt đến mức dưới cùng với chỉ mục rời đi. Ví dụ: nếu bạn đang tìm kiếm giá trị 123 trong một cột được lập chỉ mục, công cụ truy vấn trước tiên sẽ xác định trang ở cấp trung gian đầu tiên ở cấp cơ sở. Trong trường hợp này, trang đầu tiên trỏ đến giá trị từ 1 đến 100 và trang thứ hai từ 101 đến 200, do đó công cụ truy vấn sẽ truy cập trang thứ hai ở cấp độ trung gian này. Tiếp theo bạn sẽ thấy rằng bạn nên chuyển sang trang thứ ba của trình độ trung cấp tiếp theo. Từ đây, hệ thống con truy vấn sẽ đọc giá trị của chỉ mục ở mức thấp hơn. Các lá chỉ mục có thể chứa chính dữ liệu bảng hoặc đơn giản là một con trỏ tới các hàng có dữ liệu trong bảng, tùy thuộc vào loại chỉ mục: chỉ mục được nhóm hoặc chỉ mục không được phân cụm.

Chỉ mục cụm

Một chỉ mục được nhóm lưu trữ các hàng dữ liệu thực tế trong các lá của chỉ mục. Quay lại ví dụ trước, điều này có nghĩa là hàng dữ liệu được liên kết với giá trị khóa 123 sẽ được lưu trữ trong chính chỉ mục. Một đặc điểm quan trọng của chỉ mục được nhóm là tất cả các giá trị được sắp xếp theo thứ tự cụ thể, tăng dần hoặc giảm dần. Do đó, một bảng hoặc dạng xem chỉ có thể có một chỉ mục được nhóm. Ngoài ra, cần lưu ý rằng dữ liệu trong một bảng chỉ được lưu trữ ở dạng được sắp xếp nếu một chỉ mục được nhóm đã được tạo trên bảng này.
Một bảng không có chỉ mục nhóm được gọi là heap.

Chỉ mục không phân cụm

Không giống như chỉ mục được nhóm, các lá của chỉ mục không được nhóm chỉ chứa các cột đó ( chìa khóa) mà chỉ mục này được xác định và cũng chứa một con trỏ tới các hàng có dữ liệu thực trong bảng. Điều này có nghĩa là hệ thống truy vấn con yêu cầu một thao tác bổ sung để định vị và truy xuất dữ liệu cần thiết. Nội dung của con trỏ dữ liệu phụ thuộc vào cách dữ liệu được lưu trữ: bảng hoặc đống. Nếu một con trỏ trỏ đến một bảng được phân cụm, nó sẽ trỏ đến một chỉ mục được phân cụm có thể được sử dụng để tìm dữ liệu thực tế. Nếu một con trỏ tham chiếu đến một đống thì nó trỏ đến một mã định danh hàng dữ liệu cụ thể. Các chỉ mục không được phân cụm không thể được sắp xếp giống như các chỉ mục được nhóm, nhưng bạn có thể tạo nhiều chỉ mục không được phân cụm trên một bảng hoặc dạng xem, tối đa 999. Điều này không có nghĩa là bạn nên tạo càng nhiều chỉ mục càng tốt. Các chỉ mục có thể cải thiện hoặc làm giảm hiệu suất hệ thống. Ngoài việc có thể tạo nhiều chỉ mục không được nhóm, bạn cũng có thể bao gồm các cột bổ sung ( cột bao gồm) vào chỉ mục của nó: các lá của chỉ mục sẽ lưu trữ không chỉ giá trị của chính các cột được lập chỉ mục mà còn lưu trữ giá trị của các cột bổ sung không được lập chỉ mục này. Cách tiếp cận này sẽ cho phép bạn bỏ qua một số hạn chế được đặt ra đối với chỉ mục. Ví dụ: bạn có thể bao gồm một cột không thể lập chỉ mục hoặc bỏ qua giới hạn độ dài chỉ mục (trong hầu hết các trường hợp là 900 byte).

Các loại chỉ mục

Ngoài việc là chỉ mục được nhóm hoặc không được nhóm, nó có thể được định cấu hình thêm dưới dạng chỉ mục tổng hợp, chỉ mục duy nhất hoặc chỉ mục bao phủ.

Chỉ số tổng hợp

Một chỉ mục như vậy có thể chứa nhiều hơn một cột. Bạn có thể bao gồm tối đa 16 cột trong một chỉ mục nhưng tổng chiều dài của chúng bị giới hạn ở 900 byte. Cả hai chỉ mục được nhóm và không được nhóm đều có thể là tổng hợp.

Chỉ mục duy nhất

Chỉ mục này đảm bảo rằng mỗi giá trị trong cột được lập chỉ mục là duy nhất. Nếu chỉ mục là tổng hợp thì tính duy nhất áp dụng cho tất cả các cột trong chỉ mục chứ không áp dụng cho từng cột riêng lẻ. Ví dụ: nếu bạn tạo một chỉ mục duy nhất trên các cột TÊN Và HỌ, thì tên đầy đủ phải là duy nhất nhưng có thể trùng lặp họ hoặc tên.
Một chỉ mục duy nhất được tạo tự động khi bạn xác định ràng buộc cột: ràng buộc khóa chính hoặc giá trị duy nhất:

Khóa chính
Khi bạn xác định ràng buộc khóa chính trên một hoặc nhiều cột thì Máy chủ SQL tự động tạo chỉ mục được nhóm duy nhất nếu chỉ mục được nhóm chưa được tạo trước đó (trong trường hợp này, chỉ mục không được nhóm duy nhất được tạo trên khóa chính)
Tính duy nhất của các giá trị
Khi bạn xác định một ràng buộc về tính duy nhất của các giá trị thì Máy chủ SQL tự động tạo một chỉ mục không được nhóm duy nhất. Bạn có thể chỉ định rằng một chỉ mục nhóm duy nhất sẽ được tạo nếu chưa có chỉ mục nhóm nào được tạo trên bảng

Chỉ số che phủ

Chỉ mục như vậy cho phép một truy vấn cụ thể lấy ngay tất cả dữ liệu cần thiết từ các phần của chỉ mục mà không cần truy cập bổ sung vào các bản ghi của chính bảng đó.

Thiết kế chỉ mục

Dù các chỉ mục có hữu ích đến đâu thì chúng cũng phải được thiết kế cẩn thận. Vì các chỉ mục có thể chiếm dung lượng ổ đĩa đáng kể nên bạn không muốn tạo nhiều chỉ mục hơn mức cần thiết. Ngoài ra, các chỉ mục được cập nhật tự động khi chính hàng dữ liệu được cập nhật, điều này có thể dẫn đến tiêu tốn thêm tài nguyên và suy giảm hiệu suất. Khi thiết kế các chỉ mục, một số cân nhắc liên quan đến cơ sở dữ liệu và các truy vấn đối với nó phải được tính đến.

Cơ sở dữ liệu

Như đã lưu ý trước đó, các chỉ mục có thể cải thiện hiệu suất hệ thống vì họ cung cấp cho công cụ truy vấn một cách nhanh chóng để tìm dữ liệu. Tuy nhiên, bạn cũng nên tính đến tần suất bạn định chèn, cập nhật hoặc xóa dữ liệu. Khi bạn thay đổi dữ liệu, các chỉ mục cũng phải được thay đổi để phản ánh các hành động tương ứng trên dữ liệu, điều này có thể làm giảm đáng kể hiệu suất hệ thống. Hãy xem xét các nguyên tắc sau khi lập kế hoạch chiến lược lập chỉ mục của bạn:

Đối với các bảng được cập nhật thường xuyên, hãy sử dụng càng ít chỉ mục càng tốt.
Nếu bảng chứa một lượng lớn dữ liệu nhưng những thay đổi nhỏ thì hãy sử dụng càng nhiều chỉ mục càng cần thiết để cải thiện hiệu suất truy vấn của bạn. Tuy nhiên, hãy suy nghĩ cẩn thận trước khi sử dụng chỉ mục trên các bảng nhỏ, bởi vì... Có thể việc sử dụng tìm kiếm chỉ mục có thể mất nhiều thời gian hơn là chỉ quét tất cả các hàng.
Đối với các chỉ mục được nhóm, hãy cố gắng giữ các trường càng ngắn càng tốt. Cách tiếp cận tốt nhất là sử dụng chỉ mục được nhóm trên các cột có giá trị duy nhất và không cho phép NULL. Đây là lý do tại sao khóa chính thường được sử dụng làm chỉ mục nhóm.
Tính duy nhất của các giá trị trong một cột ảnh hưởng đến hiệu suất của chỉ mục. Nói chung, bạn càng có nhiều bản sao trong một cột thì chỉ mục càng hoạt động kém. Mặt khác, càng có nhiều giá trị độc đáo thì hiệu suất của chỉ mục càng tốt. Sử dụng một chỉ mục duy nhất bất cứ khi nào có thể.
Đối với chỉ mục tổng hợp, hãy tính đến thứ tự của các cột trong chỉ mục. Các cột được sử dụng trong biểu thức Ở ĐÂU(Ví dụ, Ở ĐÂU Tên = "Charlie") phải đứng đầu trong chỉ mục. Các cột tiếp theo phải được liệt kê dựa trên tính duy nhất của các giá trị của chúng (các cột có số lượng giá trị duy nhất cao nhất sẽ được xếp trước).
Bạn cũng có thể chỉ định chỉ mục trên các cột được tính toán nếu chúng đáp ứng các yêu cầu nhất định. Ví dụ: các biểu thức được sử dụng để lấy giá trị của một cột phải có tính xác định (luôn trả về cùng một kết quả cho một tập hợp tham số đầu vào nhất định).

Truy vấn cơ sở dữ liệu

Một điều cần cân nhắc khác khi thiết kế chỉ mục là những truy vấn nào đang được chạy trên cơ sở dữ liệu. Như đã nêu trước đó, bạn phải xem xét tần suất dữ liệu thay đổi. Ngoài ra, nên áp dụng các nguyên tắc sau:

Cố gắng chèn hoặc sửa đổi nhiều hàng nhất có thể trong một truy vấn thay vì thực hiện việc đó trong nhiều truy vấn đơn lẻ.
Tạo chỉ mục không được nhóm trên các cột thường được sử dụng làm cụm từ tìm kiếm trong truy vấn của bạn. Ở ĐÂU và các kết nối trong THAM GIA.
Xem xét lập chỉ mục các cột được sử dụng trong truy vấn tra cứu hàng để có giá trị khớp chính xác.

Và bây giờ, thực sự:

14 câu hỏi về chỉ mục trong SQL Server mà bạn ngại hỏi

Tại sao một bảng không thể có hai chỉ mục được nhóm?

Bạn muốn một câu trả lời ngắn gọn? Một chỉ mục được nhóm là một bảng. Khi bạn tạo một chỉ mục được nhóm trên một bảng, công cụ lưu trữ sẽ sắp xếp tất cả các hàng trong bảng theo thứ tự tăng dần hoặc giảm dần, theo định nghĩa chỉ mục. Chỉ mục được nhóm không phải là một thực thể riêng biệt như các chỉ mục khác mà là một cơ chế sắp xếp dữ liệu trong bảng và tạo điều kiện truy cập nhanh vào các hàng dữ liệu.
Hãy tưởng tượng rằng bạn có một bảng chứa lịch sử giao dịch bán hàng. Bảng Sales bao gồm các thông tin như ID đơn hàng, vị trí sản phẩm trong đơn hàng, mã số sản phẩm, số lượng sản phẩm, số đơn hàng, ngày tháng…. Bạn tạo một chỉ mục nhóm trên các cột ID đơn hàng Và Số dòng, sắp xếp theo thứ tự tăng dần như sau T-SQL mã số:
TẠO CHỈ SỐ CỤM ĐỘC ĐÁO ix_oriderid_lineid TRÊN dbo.Sales(OrderID, LineID);
Khi bạn chạy tập lệnh này, tất cả các hàng trong bảng sẽ được sắp xếp vật lý trước tiên theo cột OrderID, sau đó là LineID, nhưng bản thân dữ liệu sẽ vẫn ở trong một khối logic duy nhất, đó là bảng. Vì lý do này, bạn không thể tạo hai chỉ mục nhóm. Chỉ có thể có một bảng có một dữ liệu và bảng đó chỉ có thể được sắp xếp một lần theo một thứ tự cụ thể.

Nếu bảng phân cụm mang lại nhiều lợi ích thì tại sao lại sử dụng bảng đống?

Bạn đúng. Các bảng được nhóm rất tốt và hầu hết các truy vấn của bạn sẽ hoạt động tốt hơn trên các bảng có chỉ mục được nhóm. Nhưng trong một số trường hợp, bạn có thể muốn để các bảng ở trạng thái nguyên sơ, tự nhiên, tức là. ở dạng đống và chỉ tạo các chỉ mục không được nhóm để duy trì các truy vấn của bạn.
Heap, như bạn nhớ, lưu trữ dữ liệu theo thứ tự ngẫu nhiên. Thông thường, hệ thống con lưu trữ sẽ thêm dữ liệu vào bảng theo thứ tự được chèn vào, nhưng hệ thống con lưu trữ cũng thích di chuyển các hàng xung quanh để lưu trữ hiệu quả hơn. Kết quả là bạn không có cơ hội dự đoán thứ tự dữ liệu sẽ được lưu trữ.
Nếu công cụ truy vấn cần tìm dữ liệu mà không có chỉ mục không được phân cụm, nó sẽ quét toàn bộ bảng để tìm các hàng nó cần. Trên các bảng rất nhỏ, điều này thường không phải là vấn đề, nhưng khi vùng heap tăng kích thước, hiệu suất sẽ nhanh chóng giảm xuống. Tất nhiên, chỉ mục không được nhóm có thể hữu ích bằng cách sử dụng con trỏ tới tệp, trang và hàng nơi lưu trữ dữ liệu cần thiết - đây thường là giải pháp thay thế tốt hơn nhiều cho việc quét bảng. Mặc dù vậy, thật khó để so sánh lợi ích của chỉ mục được nhóm khi xem xét hiệu suất truy vấn.
Tuy nhiên, vùng heap có thể giúp cải thiện hiệu suất trong một số trường hợp nhất định. Hãy xem xét một bảng có nhiều phần chèn nhưng ít cập nhật hoặc xóa. Ví dụ: một bảng lưu trữ nhật ký chủ yếu được sử dụng để chèn các giá trị cho đến khi nó được lưu trữ. Trên vùng nhớ heap, bạn sẽ không thấy phân trang và phân mảnh dữ liệu giống như với chỉ mục được nhóm vì các hàng chỉ được thêm vào cuối vùng nhớ heap. Việc chia các trang quá nhiều có thể tác động đáng kể đến hiệu suất và không theo chiều hướng tốt. Nói chung, vùng heap cho phép bạn chèn dữ liệu tương đối dễ dàng và bạn sẽ không phải đối mặt với chi phí lưu trữ và bảo trì như khi sử dụng chỉ mục nhóm.
Nhưng việc thiếu cập nhật và xóa dữ liệu không nên được coi là lý do duy nhất. Cách lấy mẫu dữ liệu cũng là một yếu tố quan trọng. Ví dụ: bạn không nên sử dụng vùng nhớ heap nếu bạn thường xuyên truy vấn các phạm vi dữ liệu hoặc dữ liệu bạn truy vấn thường cần được sắp xếp hoặc nhóm lại.
Tất cả điều này có nghĩa là bạn chỉ nên cân nhắc việc sử dụng vùng heap khi bạn đang làm việc với các bảng rất nhỏ hoặc tất cả sự tương tác của bạn với bảng bị giới hạn ở việc chèn dữ liệu và các truy vấn của bạn cực kỳ đơn giản (và bạn đang sử dụng các chỉ mục không được phân cụm). Dẫu sao thì). Mặt khác, hãy sử dụng chỉ mục được nhóm được thiết kế tốt, chẳng hạn như chỉ mục được xác định trên trường khóa tăng dần đơn giản, giống như một cột được sử dụng rộng rãi với DANH TÍNH.

Làm cách nào để thay đổi hệ số điền chỉ mục mặc định?

Thay đổi hệ số điền chỉ mục mặc định là một chuyện. Hiểu cách hoạt động của tỷ lệ mặc định lại là một vấn đề khác. Nhưng trước tiên, hãy lùi lại vài bước. Hệ số lấp đầy chỉ mục xác định lượng không gian trên trang để lưu trữ chỉ mục ở cấp dưới cùng (cấp lá) trước khi bắt đầu điền vào một trang mới. Ví dụ: nếu hệ số được đặt là 90 thì khi chỉ số tăng lên, nó sẽ chiếm 90% trang rồi chuyển sang trang tiếp theo.
Theo mặc định, giá trị hệ số điền chỉ mục nằm trong Máy chủ SQL là 0, tương đương với 100. Do đó, tất cả các chỉ mục mới sẽ tự động kế thừa cài đặt này trừ khi bạn chỉ định cụ thể một giá trị trong mã khác với giá trị tiêu chuẩn của hệ thống hoặc thay đổi hành vi mặc định. Bạn có thể dùng Studio quản lý máy chủ SQLđể điều chỉnh giá trị mặc định hoặc chạy thủ tục lưu trữ hệ thống sp_configure. Ví dụ như bộ sau T-SQL lệnh đặt giá trị hệ số thành 90 (trước tiên bạn phải chuyển sang chế độ cài đặt nâng cao):
EXEC sp_configure "hiển thị các tùy chọn nâng cao", 1; ĐI CẤU HÌNH LẠI; GO EXEC sp_configure "hệ số điền", 90; ĐI CẤU HÌNH LẠI; ĐI
Sau khi thay đổi giá trị hệ số điền chỉ mục, bạn cần khởi động lại dịch vụ Máy chủ SQL. Bây giờ bạn có thể kiểm tra giá trị đã đặt bằng cách chạy sp_configure mà không cần đối số thứ hai được chỉ định:
EXEC sp_configure "hệ số điền" GO
Lệnh này sẽ trả về giá trị 90. Kết quả là tất cả các chỉ mục mới được tạo sẽ sử dụng giá trị này. Bạn có thể kiểm tra điều này bằng cách tạo chỉ mục và truy vấn giá trị hệ số điền:
SỬ DỤNG AdventureWorks2012; -- cơ sở dữ liệu của bạn TẠO CHỈ SỐ KHÔNG BAO GỒM ix_people_lastname TRÊN Person.Person(LastName); ĐI CHỌN fill_factor TỪ sys.indexes Ở ĐÂU object_id = object_id("Person.Person") VÀ name="ix_people_lastname";
Trong ví dụ này, chúng tôi đã tạo một chỉ mục không được nhóm trên một bảng Người trong cơ sở dữ liệu AdventureWorks2012. Sau khi tạo chỉ mục, chúng ta có thể nhận giá trị hệ số điền từ các bảng hệ thống sys.indexes. Truy vấn sẽ trả về 90.
Tuy nhiên, hãy tưởng tượng rằng chúng tôi đã xóa chỉ mục và tạo lại chỉ mục, nhưng bây giờ chúng tôi đã chỉ định một giá trị hệ số điền cụ thể:
TẠO CHỈ SỐ KHÔNG BAO GỒM ix_people_lastname TRÊN Person.Person(LastName) VỚI (fillfactor=80); ĐI CHỌN fill_factor TỪ sys.indexes Ở ĐÂU object_id = object_id("Person.Person") VÀ name="ix_people_lastname";
Lần này chúng tôi đã thêm hướng dẫn VỚI và tùy chọn yếu tố lấp đầy cho hoạt động tạo chỉ mục của chúng tôi TẠO CHỈ SỐ và chỉ định giá trị 80. Toán tử LỰA CHỌN bây giờ trả về giá trị tương ứng.
Cho đến nay, mọi thứ đã khá đơn giản. Nơi bạn thực sự có thể bị đốt cháy trong toàn bộ quá trình này là khi bạn tạo một chỉ mục sử dụng giá trị hệ số mặc định, giả sử bạn biết giá trị đó. Ví dụ, có người đang mày mò cài đặt máy chủ và bướng bỉnh đến mức họ đặt hệ số điền chỉ mục là 20. Trong khi đó, bạn tiếp tục tạo chỉ mục, giả sử giá trị mặc định là 0. Rất tiếc, bạn không có cách nào để tìm ra cách điền nhân tố cho đến khi bạn không tạo chỉ mục rồi kiểm tra giá trị như chúng tôi đã làm trong các ví dụ của mình. Nếu không, bạn sẽ phải đợi đến thời điểm hiệu suất truy vấn giảm xuống mức bạn bắt đầu nghi ngờ điều gì đó.
Một vấn đề khác bạn cần lưu ý là việc xây dựng lại các chỉ mục. Giống như việc tạo chỉ mục, bạn có thể chỉ định giá trị hệ số điền chỉ mục khi xây dựng lại nó. Tuy nhiên, không giống như lệnh tạo chỉ mục, lệnh xây dựng lại không sử dụng các cài đặt mặc định của máy chủ, bất chấp vẻ ngoài của nó. Hơn nữa, nếu bạn không chỉ định cụ thể giá trị hệ số điền chỉ mục thì Máy chủ SQL sẽ sử dụng giá trị của hệ số mà chỉ số này tồn tại trước khi tái cơ cấu. Ví dụ, thao tác sau THAY ĐỔI CHỈ SỐ xây dựng lại chỉ mục chúng ta vừa tạo:
THAY ĐỔI CHỈ SỐ ix_people_lastname TRÊN Person.Person XÂY DỰNG LẠI; ĐI CHỌN fill_factor TỪ sys.indexes Ở ĐÂU object_id = object_id("Person.Person") VÀ name="ix_people_lastname";
Khi kiểm tra giá trị hệ số lấp đầy, chúng tôi sẽ nhận được giá trị 80, vì đó là giá trị chúng tôi đã chỉ định khi tạo chỉ mục lần cuối. Giá trị mặc định bị bỏ qua.
Như bạn có thể thấy, việc thay đổi giá trị hệ số điền chỉ mục không quá khó. Việc biết giá trị hiện tại và hiểu khi nào nó được áp dụng sẽ khó khăn hơn nhiều. Nếu bạn luôn chỉ định cụ thể hệ số khi tạo và xây dựng lại chỉ mục thì bạn luôn biết kết quả cụ thể. Trừ khi bạn phải lo lắng về việc đảm bảo người khác không làm hỏng cài đặt máy chủ nữa, khiến tất cả các chỉ mục được xây dựng lại với hệ số lấp đầy chỉ mục thấp đến mức nực cười.

Có thể tạo chỉ mục nhóm trên một cột chứa các bản sao không?

Có và không. Có, bạn có thể tạo chỉ mục được nhóm trên cột khóa chứa các giá trị trùng lặp. Không, giá trị của cột khóa không thể duy trì ở trạng thái không duy nhất. Hãy để tôi giải thích. Nếu bạn tạo một chỉ mục được nhóm không duy nhất trên một cột, công cụ lưu trữ sẽ thêm một mã duy nhất vào giá trị trùng lặp để đảm bảo tính duy nhất và do đó có thể xác định từng hàng trong bảng được nhóm.
Ví dụ: bạn có thể quyết định tạo chỉ mục được nhóm trên một cột chứa dữ liệu khách hàng Họ giữ họ. Cột chứa các giá trị Franklin, Hancock, Washington và Smith. Sau đó bạn chèn lại các giá trị Adams, Hancock, Smith và Smith. Nhưng giá trị của cột khóa phải là duy nhất, do đó công cụ lưu trữ sẽ thay đổi giá trị của các bản sao để chúng trông giống như thế này: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 và Smith5678.
Thoạt nhìn, cách tiếp cận này có vẻ ổn, nhưng giá trị số nguyên làm tăng kích thước của khóa, điều này có thể trở thành vấn đề nếu có số lượng lớn bản sao và các giá trị này sẽ trở thành cơ sở của chỉ mục không được phân cụm hoặc chỉ mục ngoại. tài liệu tham khảo chính. Vì những lý do này, bạn phải luôn cố gắng tạo các chỉ mục được nhóm duy nhất bất cứ khi nào có thể. Nếu điều này là không thể thì ít nhất hãy cố gắng sử dụng các cột có nội dung có giá trị duy nhất rất cao.

Bảng được lưu trữ như thế nào nếu chỉ mục nhóm chưa được tạo?

Máy chủ SQL hỗ trợ hai loại bảng: các bảng được nhóm có chỉ mục và bảng heap được nhóm hoặc chỉ là các đống. Không giống như các bảng được nhóm, dữ liệu trên heap không được sắp xếp theo bất kỳ cách nào. Về bản chất, đây là một đống (đống) dữ liệu. Nếu bạn thêm một hàng vào bảng như vậy, công cụ lưu trữ sẽ chỉ thêm nó vào cuối trang. Khi trang chứa đầy dữ liệu, nó sẽ được thêm vào một trang mới. Trong hầu hết các trường hợp, bạn sẽ muốn tạo một chỉ mục được nhóm trên một bảng để tận dụng khả năng sắp xếp và tốc độ truy vấn (hãy thử tưởng tượng việc tra cứu một số điện thoại trong sổ địa chỉ chưa được sắp xếp). Tuy nhiên, nếu bạn chọn không tạo chỉ mục được nhóm, bạn vẫn có thể tạo chỉ mục không được phân cụm trên vùng nhớ heap. Trong trường hợp này, mỗi hàng chỉ mục sẽ có một con trỏ tới hàng heap. Chỉ mục bao gồm ID tệp, số trang và số dòng dữ liệu.

Mối quan hệ giữa các ràng buộc về tính duy nhất của giá trị và khóa chính với các chỉ mục bảng là gì?

Khóa chính và ràng buộc duy nhất đảm bảo rằng các giá trị trong một cột là duy nhất. Bạn chỉ có thể tạo một khóa chính cho một bảng và nó không thể chứa các giá trị VÔ GIÁ TRỊ. Bạn có thể tạo một số hạn chế về tính duy nhất của một giá trị cho một bảng và mỗi giá trị trong số chúng có thể có một bản ghi duy nhất với VÔ GIÁ TRỊ.
Khi bạn tạo khóa chính, công cụ lưu trữ cũng tạo một chỉ mục được nhóm duy nhất nếu chỉ mục được nhóm chưa được tạo. Tuy nhiên, bạn có thể ghi đè hành vi mặc định và chỉ mục không được nhóm sẽ được tạo. Nếu một chỉ mục được nhóm tồn tại khi bạn tạo khóa chính thì một chỉ mục không được nhóm duy nhất sẽ được tạo.
Khi bạn tạo một ràng buộc duy nhất, công cụ lưu trữ sẽ tạo một chỉ mục duy nhất, không được phân cụm. Tuy nhiên, bạn có thể chỉ định việc tạo chỉ mục nhóm duy nhất nếu chưa được tạo trước đó.
Nói chung, ràng buộc giá trị duy nhất và chỉ mục duy nhất là như nhau.

Tại sao các chỉ mục được nhóm và không được nhóm được gọi là cây B trong SQL Server?

Các chỉ mục cơ bản trong SQL Server, được nhóm hoặc không được nhóm, được phân phối trên các tập hợp trang được gọi là nút chỉ mục. Các trang này được tổ chức theo một hệ thống phân cấp cụ thể với cấu trúc cây được gọi là cây cân bằng. Ở cấp cao nhất có nút gốc, ở phía dưới có các nút lá, với các nút trung gian giữa cấp trên và cấp dưới, như trong hình:

Nút gốc cung cấp điểm vào chính cho các truy vấn cố gắng truy xuất dữ liệu thông qua chỉ mục. Bắt đầu từ nút này, công cụ truy vấn bắt đầu điều hướng xuống cấu trúc phân cấp tới nút lá thích hợp chứa dữ liệu.
Ví dụ: hãy tưởng tượng rằng một yêu cầu đã được nhận để chọn các hàng chứa giá trị khóa là 82. Hệ thống con truy vấn bắt đầu hoạt động từ nút gốc, nút này đề cập đến nút trung gian phù hợp, trong trường hợp của chúng tôi là 1-100. Từ nút trung gian 1-100 có sự chuyển tiếp sang nút 51-100 và từ đó đến nút cuối cùng 76-100. Nếu đây là một chỉ mục được phân cụm thì lá nút chứa dữ liệu của hàng được liên kết với khóa bằng 82. Nếu đây là một chỉ mục không được phân cụm thì lá chỉ mục chứa một con trỏ tới bảng được phân cụm hoặc một hàng cụ thể trong đống.

Làm cách nào một chỉ mục có thể cải thiện hiệu suất truy vấn nếu bạn phải duyệt qua tất cả các nút chỉ mục này?

Đầu tiên, các chỉ mục không phải lúc nào cũng cải thiện hiệu suất. Quá nhiều chỉ mục được tạo không chính xác sẽ biến hệ thống thành vũng lầy và làm giảm hiệu suất truy vấn. Sẽ chính xác hơn khi nói rằng nếu các chỉ mục được áp dụng cẩn thận, chúng có thể mang lại hiệu suất tăng đáng kể.
Hãy nghĩ đến một cuốn sách khổng lồ dành riêng cho việc điều chỉnh hiệu suất Máy chủ SQL(bản giấy, không phải bản điện tử). Hãy tưởng tượng bạn muốn tìm thông tin về cấu hình Resource Governor. Bạn có thể kéo ngón tay từng trang trong toàn bộ cuốn sách hoặc mở mục lục và tìm ra số trang chính xác với thông tin bạn đang tìm kiếm (với điều kiện cuốn sách được lập chỉ mục chính xác và nội dung có chỉ mục chính xác). Điều này chắc chắn sẽ giúp bạn tiết kiệm thời gian đáng kể, mặc dù trước tiên bạn phải truy cập vào một cấu trúc hoàn toàn khác (chỉ mục) để lấy thông tin bạn cần từ cấu trúc chính (sách).
Giống như mục lục cuốn sách, mục lục trong Máy chủ SQL cho phép bạn chạy các truy vấn chính xác trên dữ liệu bạn cần thay vì quét hoàn toàn tất cả dữ liệu có trong bảng. Đối với các bảng nhỏ, việc quét toàn bộ thường không phải là vấn đề, nhưng các bảng lớn chiếm nhiều trang dữ liệu, điều này có thể dẫn đến thời gian thực hiện truy vấn đáng kể trừ khi tồn tại một chỉ mục để cho phép công cụ truy vấn ngay lập tức lấy được vị trí chính xác của dữ liệu. Hãy tưởng tượng bạn bị lạc tại một ngã ba đường nhiều tầng phía trước một đô thị lớn mà không có bản đồ và bạn sẽ hiểu được ý tưởng.

Nếu các chỉ mục quá tuyệt vời, tại sao không tạo một chỉ mục trên mỗi cột?

Không có việc tốt nào mà không bị trừng phạt. Ít nhất đó là trường hợp với các chỉ mục. Tất nhiên, các chỉ mục hoạt động tốt miễn là bạn chạy các truy vấn tìm nạp toán tử LỰA CHỌN, nhưng ngay khi các cuộc gọi thường xuyên tới các nhà điều hành bắt đầu CHÈN, CẬP NHẬT Và XÓA BỎ, nên cảnh quan thay đổi rất nhanh.
Khi bạn bắt đầu yêu cầu dữ liệu từ nhà điều hành LỰA CHỌN, công cụ truy vấn sẽ tìm chỉ mục, di chuyển qua cấu trúc cây của nó và khám phá dữ liệu mà nó đang tìm kiếm. Điều gì có thể đơn giản hơn? Nhưng mọi thứ sẽ thay đổi nếu bạn bắt đầu một tuyên bố thay đổi như CẬP NHẬT. Có, đối với phần đầu tiên của câu lệnh, công cụ truy vấn có thể sử dụng lại chỉ mục để xác định vị trí hàng đang được sửa đổi - đó là tin tốt. Và nếu có một thay đổi đơn giản về dữ liệu trong một hàng mà không ảnh hưởng đến những thay đổi trong các cột chính thì quá trình thay đổi sẽ hoàn toàn không gây khó khăn. Nhưng điều gì sẽ xảy ra nếu sự thay đổi khiến các trang chứa dữ liệu bị chia tách hoặc giá trị của một cột khóa bị thay đổi khiến nó bị chuyển sang một nút chỉ mục khác - điều này sẽ dẫn đến việc chỉ mục có thể cần được sắp xếp lại, ảnh hưởng đến tất cả các chỉ mục và hoạt động liên quan , dẫn đến năng suất sụt giảm trên diện rộng.
Các quá trình tương tự xảy ra khi gọi một nhà điều hành XÓA BỎ. Chỉ mục có thể giúp xác định vị trí dữ liệu bị xóa nhưng việc xóa chính dữ liệu đó có thể dẫn đến việc xáo trộn lại trang. Về người điều hành CHÈN, kẻ thù chính của tất cả các chỉ mục: bạn bắt đầu thêm một lượng lớn dữ liệu, điều này dẫn đến những thay đổi trong chỉ mục cũng như việc sắp xếp lại chúng và mọi người đều phải chịu đựng.
Vì vậy, hãy xem xét các loại truy vấn tới cơ sở dữ liệu của bạn khi nghĩ về loại chỉ mục nào và số lượng cần tạo. Nhiều hơn không có nghĩa là tốt hơn. Trước khi thêm chỉ mục mới vào bảng, hãy xem xét chi phí không chỉ của các truy vấn cơ bản mà còn cả dung lượng ổ đĩa đã sử dụng, chi phí duy trì chức năng và chỉ mục, có thể dẫn đến hiệu ứng domino đối với các hoạt động khác. Chiến lược thiết kế chỉ mục của bạn là một trong những khía cạnh quan trọng nhất trong quá trình triển khai của bạn và cần bao gồm nhiều cân nhắc, từ kích thước của chỉ mục, số lượng giá trị duy nhất đến loại truy vấn mà chỉ mục sẽ hỗ trợ.

Có cần thiết phải tạo chỉ mục nhóm trên một cột bằng khóa chính không?

Bạn có thể tạo chỉ mục nhóm trên bất kỳ cột nào đáp ứng các điều kiện bắt buộc. Đúng là một chỉ mục được nhóm và một ràng buộc khóa chính được tạo ra cho nhau và là một sự trùng khớp tuyệt vời, vì vậy hãy hiểu thực tế rằng khi bạn tạo một khóa chính thì một chỉ mục được nhóm sẽ tự động được tạo nếu chưa có. được tạo ra trước đó. Tuy nhiên, bạn có thể quyết định rằng một chỉ mục được nhóm sẽ hoạt động tốt hơn ở nơi khác và thường thì quyết định của bạn sẽ hợp lý.
Mục đích chính của chỉ mục được nhóm là sắp xếp tất cả các hàng trong bảng của bạn dựa trên cột khóa được chỉ định khi xác định chỉ mục. Điều này giúp tìm kiếm nhanh chóng và dễ dàng truy cập vào dữ liệu bảng.
Khóa chính của bảng có thể là một lựa chọn tốt vì nó xác định duy nhất từng hàng trong bảng mà không cần phải thêm dữ liệu bổ sung. Trong một số trường hợp, lựa chọn tốt nhất sẽ là khóa chính thay thế, khóa này không chỉ duy nhất mà còn có kích thước nhỏ và có giá trị tăng tuần tự, giúp các chỉ mục không được phân nhóm dựa trên giá trị này hiệu quả hơn. Trình tối ưu hóa truy vấn cũng thích sự kết hợp giữa chỉ mục được nhóm và khóa chính vì việc nối các bảng nhanh hơn so với việc nối theo cách khác không sử dụng khóa chính và chỉ mục được liên kết của nó. Như tôi đã nói đó là một trận đấu được thực hiện trên thiên đường.
Tuy nhiên, cuối cùng, cần lưu ý rằng khi tạo một chỉ mục được nhóm, có một số khía cạnh cần xem xét: có bao nhiêu chỉ mục không được nhóm sẽ dựa trên nó, tần suất giá trị của cột chỉ mục chính sẽ thay đổi và mức độ lớn như thế nào. Khi các giá trị trong các cột của một chỉ mục được nhóm thay đổi hoặc chỉ mục đó không hoạt động như mong đợi thì tất cả các chỉ mục khác trên bảng đều có thể bị ảnh hưởng. Chỉ mục được nhóm phải dựa trên cột ổn định nhất có giá trị tăng theo một thứ tự cụ thể nhưng không thay đổi một cách ngẫu nhiên. Chỉ mục phải hỗ trợ các truy vấn đối với dữ liệu được truy cập thường xuyên nhất của bảng, do đó các truy vấn tận dụng tối đa thực tế là dữ liệu được sắp xếp và có thể truy cập được tại các nút gốc, các lá của chỉ mục. Nếu khóa chính phù hợp với trường hợp này thì hãy sử dụng nó. Nếu không, hãy chọn một tập hợp cột khác.

Điều gì sẽ xảy ra nếu bạn lập chỉ mục một chế độ xem, liệu nó có còn là một chế độ xem không?

Chế độ xem là một bảng ảo tạo dữ liệu từ một hoặc nhiều bảng. Về cơ bản, đây là một truy vấn được đặt tên lấy dữ liệu từ các bảng cơ bản khi bạn truy vấn dạng xem đó. Bạn có thể cải thiện hiệu suất truy vấn bằng cách tạo chỉ mục được nhóm và các chỉ mục không được nhóm trên chế độ xem này, tương tự như cách bạn tạo chỉ mục trên một bảng, nhưng lưu ý chính là trước tiên bạn phải tạo một chỉ mục được nhóm và sau đó bạn có thể tạo một chỉ mục không được nhóm.
Khi một chế độ xem được lập chỉ mục (chế độ xem cụ thể hóa) được tạo thì bản thân định nghĩa chế độ xem vẫn là một thực thể riêng biệt. Rốt cuộc thì đây chỉ là một toán tử được mã hóa cứng LỰA CHỌN, được lưu trữ trong cơ sở dữ liệu. Nhưng chỉ số lại là một câu chuyện hoàn toàn khác. Khi bạn tạo một chỉ mục được nhóm hoặc không được nhóm trên một nhà cung cấp, dữ liệu sẽ được lưu vật lý vào đĩa, giống như một chỉ mục thông thường. Ngoài ra, khi dữ liệu thay đổi trong các bảng cơ bản, chỉ mục của khung nhìn sẽ tự động thay đổi (điều này có nghĩa là bạn có thể muốn tránh lập chỉ mục các khung nhìn trên các bảng thay đổi thường xuyên). Trong mọi trường hợp, chế độ xem vẫn là chế độ xem - chế độ xem của các bảng, nhưng được thực thi vào lúc này, với các chỉ mục tương ứng với nó.
Trước khi bạn có thể tạo chỉ mục trên một dạng xem, nó phải đáp ứng một số ràng buộc. Ví dụ: một dạng xem chỉ có thể tham chiếu các bảng cơ sở chứ không thể tham chiếu các dạng xem khác và các bảng đó phải nằm trong cùng một cơ sở dữ liệu. Thực tế còn có nhiều hạn chế khác, vì vậy hãy nhớ kiểm tra tài liệu để biết Máy chủ SQL cho tất cả các chi tiết bẩn thỉu.

Tại sao nên sử dụng chỉ mục bao trùm thay vì chỉ mục tổng hợp?

Trước tiên, hãy đảm bảo rằng chúng ta hiểu sự khác biệt giữa hai điều này. Chỉ mục ghép đơn giản là chỉ mục thông thường chứa nhiều hơn một cột. Nhiều cột khóa có thể được sử dụng để đảm bảo rằng mỗi hàng trong bảng là duy nhất hoặc bạn có thể có nhiều cột để đảm bảo rằng khóa chính là duy nhất hoặc bạn có thể đang cố gắng tối ưu hóa việc thực hiện các truy vấn được gọi thường xuyên trên nhiều cột. Tuy nhiên, nói chung, một chỉ mục càng chứa nhiều cột khóa thì chỉ mục đó sẽ càng kém hiệu quả, điều đó có nghĩa là các chỉ mục tổng hợp nên được sử dụng một cách thận trọng.
Như đã nêu, một truy vấn có thể có lợi rất nhiều nếu tất cả dữ liệu cần thiết được đặt ngay trên các phần cuối của chỉ mục, giống như chính chỉ mục đó. Đây không phải là vấn đề đối với chỉ mục được nhóm vì tất cả dữ liệu đều đã có sẵn (đó là lý do tại sao việc suy nghĩ cẩn thận khi tạo chỉ mục nhóm lại rất quan trọng). Nhưng chỉ mục không được nhóm trên các lá chỉ chứa các cột chính. Để truy cập tất cả các dữ liệu khác, trình tối ưu hóa truy vấn yêu cầu các bước bổ sung, có thể tăng thêm chi phí đáng kể để thực hiện các truy vấn của bạn.
Đây là lúc chỉ số bao phủ phát huy tác dụng. Khi xác định một chỉ mục không được bao gồm, bạn có thể chỉ định các cột bổ sung cho các cột chính của mình. Ví dụ: giả sử ứng dụng của bạn thường xuyên truy vấn dữ liệu cột ID đơn hàng Và Ngày đặt hàng trong bàn Việc bán hàng:
CHỌN ID đơn hàng, Ngày đặt hàng TỪ Doanh số bán hàng Ở đâu OrderID = 12345;
Bạn có thể tạo chỉ mục tổng hợp không được nhóm trên cả hai cột, nhưng cột OrderDate sẽ chỉ thêm chi phí bảo trì chỉ mục mà không đóng vai trò là cột khóa đặc biệt hữu ích. Giải pháp tốt nhất là tạo chỉ mục bao phủ trên cột khóa ID đơn hàng và bổ sung thêm cột Ngày đặt hàng:
TẠO CHỈ SỐ KHÔNG BAO GỒM ix_orderid TRÊN dbo.Sales(OrderID) BAO GỒM (OrderDate);
Điều này tránh được nhược điểm của việc lập chỉ mục các cột dư thừa trong khi vẫn duy trì lợi ích của việc lưu trữ dữ liệu trong các lá khi chạy truy vấn. Cột được đưa vào không phải là một phần của khóa nhưng dữ liệu được lưu trữ trên nút lá, lá chỉ mục. Điều này có thể cải thiện hiệu suất truy vấn mà không cần thêm bất kỳ chi phí nào. Ngoài ra, các cột có trong chỉ mục bao gồm ít hạn chế hơn so với các cột chính của chỉ mục.

Số lượng bản sao trong một cột khóa có quan trọng không?

Khi tạo chỉ mục, bạn phải cố gắng giảm số lượng trùng lặp trong các cột chính của mình. Hay chính xác hơn là cố gắng giữ tỷ lệ lặp lại ở mức thấp nhất có thể.
Nếu bạn đang làm việc với một chỉ mục tổng hợp thì việc sao chép sẽ áp dụng cho toàn bộ các cột chính. Một cột có thể chứa nhiều giá trị trùng lặp nhưng cần có sự lặp lại tối thiểu giữa tất cả các cột chỉ mục. Ví dụ: bạn tạo một chỉ mục tổng hợp không được phân cụm trên các cột Tên đầu tiên Và Họ, bạn có thể có nhiều giá trị John Doe và nhiều giá trị Doe, nhưng bạn muốn có càng ít giá trị John Doe càng tốt hoặc tốt nhất là chỉ một giá trị John Doe.
Tỷ lệ duy nhất của các giá trị của cột khóa được gọi là độ chọn lọc chỉ mục. Càng có nhiều giá trị duy nhất thì độ chọn lọc càng cao: một chỉ mục duy nhất có độ chọn lọc lớn nhất có thể. Công cụ truy vấn thực sự thích các cột có giá trị chọn lọc cao, đặc biệt nếu các cột đó được bao gồm trong mệnh đề WHERE của các truy vấn được thực hiện thường xuyên nhất của bạn. Chỉ mục càng chọn lọc thì công cụ truy vấn có thể giảm kích thước của tập dữ liệu kết quả càng nhanh. Tất nhiên, nhược điểm là các cột có tương đối ít giá trị duy nhất sẽ hiếm khi là ứng cử viên tốt để lập chỉ mục.

Có thể tạo chỉ mục không được nhóm trên một tập hợp con cụ thể của dữ liệu của cột chính không?

Theo mặc định, chỉ mục không được bao gồm chứa một hàng cho mỗi hàng trong bảng. Tất nhiên, bạn có thể nói điều tương tự về một chỉ mục được nhóm, giả sử rằng chỉ mục đó là một bảng. Nhưng khi nói đến chỉ mục không được phân cụm, mối quan hệ một-một là một khái niệm quan trọng bởi vì, bắt đầu từ phiên bản Máy chủ SQL 2008, bạn có tùy chọn tạo chỉ mục có thể lọc để giới hạn các hàng có trong đó. Chỉ mục được lọc có thể cải thiện hiệu suất truy vấn vì... nó có kích thước nhỏ hơn và chứa số liệu thống kê được lọc, chính xác hơn tất cả các số liệu dạng bảng - điều này dẫn đến việc tạo ra các kế hoạch thực hiện được cải thiện. Chỉ mục được lọc cũng yêu cầu ít không gian lưu trữ hơn và chi phí bảo trì thấp hơn. Chỉ mục chỉ được cập nhật khi dữ liệu phù hợp với bộ lọc thay đổi.
Ngoài ra, một chỉ mục có thể lọc rất dễ tạo. Trong toán tử TẠO CHỈ SỐ bạn chỉ cần cho biết trong Ở ĐÂU tình trạng lọc. Ví dụ: bạn có thể lọc ra tất cả các hàng chứa NULL khỏi chỉ mục, như trong mã:
TẠO CHỈ SỐ KHÔNG BAO GỒM ix_trackingnumber TRÊN Sales.SalesOrderDetail(CarrierTrackingNumber) Ở ĐÂU CarrierTrackingNumber KHÔNG CÓ NULL;
Trên thực tế, chúng tôi có thể lọc ra bất kỳ dữ liệu nào không quan trọng trong các truy vấn quan trọng. Nhưng hãy cẩn thận, vì... Máy chủ SQLáp đặt một số hạn chế đối với các chỉ mục có thể lọc, chẳng hạn như không thể tạo chỉ mục có thể lọc trên một dạng xem, vì vậy hãy đọc kỹ tài liệu.
Cũng có thể bạn có thể đạt được kết quả tương tự bằng cách tạo chế độ xem được lập chỉ mục. Tuy nhiên, chỉ mục được lọc có một số ưu điểm, chẳng hạn như khả năng giảm chi phí bảo trì và cải thiện chất lượng kế hoạch thực hiện của bạn. Các chỉ mục đã lọc cũng có thể được xây dựng lại trực tuyến. Hãy thử điều này với chế độ xem được lập chỉ mục.

Và một lần nữa một chút từ người dịch

Mục đích của sự xuất hiện của bản dịch này trên các trang của Habrahabr là để kể hoặc nhắc nhở bạn về blog SimpleTalk từ cổng đỏ.
Nó xuất bản nhiều bài viết thú vị và thú vị.
Tôi không liên kết với bất kỳ sản phẩm nào của công ty cổng đỏ, cũng như với việc bán hàng của họ.

Như đã hứa, sách dành cho những ai muốn tìm hiểu thêm
Tôi tự giới thiệu cho mình ba cuốn sách rất hay (các liên kết dẫn tới thiêu phiên bản trong cửa hàng Amazon):

Về nguyên tắc, bạn có thể mở các chỉ mục đơn giản Thêm thẻ

Nguyên tắc cơ bản về Microsoft SQL Server 2012 T-SQL (Tài liệu tham khảo dành cho nhà phát triển)
Tác giả Itzik Ben-Gan
Ngày xuất bản: 15 tháng 7 năm 2012
Tác giả, một bậc thầy trong nghề, cung cấp kiến thức cơ bản về làm việc với cơ sở dữ liệu.
Nếu bạn đã quên mọi thứ hoặc chưa từng biết thì chắc chắn nó rất đáng đọc.

Chỉ mục- đây là điều đầu tiên bạn cần hiểu rõ trong công việc của mình Máy chủ SQL, nhưng những câu hỏi cơ bản đến lạ lại không được hỏi thường xuyên trên các diễn đàn và không nhận được nhiều câu trả lời.
Rob Sheldon trả lời những câu hỏi gây nhầm lẫn trong giới chuyên môn về các chỉ số trong Máy chủ SQL: một số trong số chúng tôi chỉ đơn giản là xấu hổ khi hỏi, nhưng trước khi hỏi những người khác, trước tiên chúng tôi sẽ suy nghĩ kỹ.

Thuật ngữ được sử dụng:

mục lục	mục lục
đống	một bó
bàn	bàn
xem	hiệu suất
cây B	cây cân đối
chỉ mục cụm	chỉ mục cụm
chỉ mục không bao gồm	chỉ mục không bao gồm
chỉ số tổng hợp	chỉ số tổng hợp
chỉ số bao phủ	chỉ số bao phủ
ràng buộc khóa chính	ràng buộc khóa chính
hạn chế duy nhất	hạn chế về tính duy nhất của các giá trị
truy vấn	lời yêu cầu
công cụ truy vấn	hệ thống con truy vấn
cơ sở dữ liệu	cơ sở dữ liệu
cơ sở dữ liệu	hệ thống con lưu trữ
hệ số lấp đầy	hệ số điền chỉ mục
khóa chính thay thế	khóa chính thay thế
trình tối ưu hóa truy vấn	trình tối ưu hóa truy vấn
độ chọn lọc chỉ số	độ chọn lọc chỉ số
chỉ mục được lọc	chỉ mục có thể lọc
kế hoạch thực hiện	kế hoạch thực hiện

Khái niệm cơ bản về chỉ mục trong SQL Server.

Khi bạn truy vấn một cột được lập chỉ mục, công cụ truy vấn sẽ bắt đầu ở đầu nút gốc và đi xuống các nút trung gian, với mỗi lớp trung gian chứa thông tin chi tiết hơn về dữ liệu. Công cụ truy vấn tiếp tục di chuyển qua các nút chỉ mục cho đến khi nó đạt đến mức dưới cùng với chỉ mục rời đi. Ví dụ: nếu bạn đang tìm kiếm giá trị 123 trong một cột được lập chỉ mục, công cụ truy vấn trước tiên sẽ xác định trang ở cấp trung gian đầu tiên ở cấp cơ sở. Trong trường hợp này, trang đầu tiên trỏ đến giá trị từ 1 đến 100 và trang thứ hai từ 101 đến 200, do đó công cụ truy vấn sẽ truy cập trang thứ hai ở cấp độ trung gian này. Tiếp theo bạn sẽ thấy rằng bạn nên chuyển sang trang thứ ba của trình độ trung cấp tiếp theo. Từ đây, hệ thống con truy vấn sẽ đọc giá trị của chỉ mục ở mức thấp hơn. Các lá chỉ mục có thể chứa chính dữ liệu bảng hoặc đơn giản là một con trỏ tới các hàng có dữ liệu trong bảng, tùy thuộc vào loại chỉ mục: chỉ mục được nhóm hoặc chỉ mục không được phân cụm.

Chỉ mục cụm

Chỉ mục không phân cụm

Các loại chỉ mục

Chỉ số tổng hợp

Chỉ mục duy nhất

Khóa chính
Khi bạn xác định ràng buộc khóa chính trên một hoặc nhiều cột thì Máy chủ SQL tự động tạo chỉ mục được nhóm duy nhất nếu chỉ mục được nhóm chưa được tạo trước đó (trong trường hợp này, chỉ mục không được nhóm duy nhất được tạo trên khóa chính)
Tính duy nhất của các giá trị
Khi bạn xác định một ràng buộc về tính duy nhất của các giá trị thì Máy chủ SQL tự động tạo một chỉ mục không được nhóm duy nhất. Bạn có thể chỉ định rằng một chỉ mục nhóm duy nhất sẽ được tạo nếu chưa có chỉ mục nhóm nào được tạo trên bảng

Chỉ số che phủ

Thiết kế chỉ mục

Cơ sở dữ liệu

Đối với các bảng được cập nhật thường xuyên, hãy sử dụng càng ít chỉ mục càng tốt.
Nếu bảng chứa một lượng lớn dữ liệu nhưng những thay đổi nhỏ thì hãy sử dụng càng nhiều chỉ mục càng cần thiết để cải thiện hiệu suất truy vấn của bạn. Tuy nhiên, hãy suy nghĩ cẩn thận trước khi sử dụng chỉ mục trên các bảng nhỏ, bởi vì... Có thể việc sử dụng tìm kiếm chỉ mục có thể mất nhiều thời gian hơn là chỉ quét tất cả các hàng.
Đối với các chỉ mục được nhóm, hãy cố gắng giữ các trường càng ngắn càng tốt. Cách tiếp cận tốt nhất là sử dụng chỉ mục được nhóm trên các cột có giá trị duy nhất và không cho phép NULL. Đây là lý do tại sao khóa chính thường được sử dụng làm chỉ mục nhóm.
Tính duy nhất của các giá trị trong một cột ảnh hưởng đến hiệu suất của chỉ mục. Nói chung, bạn càng có nhiều bản sao trong một cột thì chỉ mục càng hoạt động kém. Mặt khác, càng có nhiều giá trị độc đáo thì hiệu suất của chỉ mục càng tốt. Sử dụng một chỉ mục duy nhất bất cứ khi nào có thể.
Đối với chỉ mục tổng hợp, hãy tính đến thứ tự của các cột trong chỉ mục. Các cột được sử dụng trong biểu thức Ở ĐÂU(Ví dụ, Ở ĐÂU Tên = 'Charlie') phải đứng đầu trong chỉ mục. Các cột tiếp theo phải được liệt kê dựa trên tính duy nhất của các giá trị của chúng (các cột có số lượng giá trị duy nhất cao nhất sẽ được xếp trước).
Bạn cũng có thể chỉ định chỉ mục trên các cột được tính toán nếu chúng đáp ứng các yêu cầu nhất định. Ví dụ: các biểu thức được sử dụng để lấy giá trị của một cột phải có tính xác định (luôn trả về cùng một kết quả cho một tập hợp tham số đầu vào nhất định).

Truy vấn cơ sở dữ liệu

Cố gắng chèn hoặc sửa đổi nhiều hàng nhất có thể trong một truy vấn thay vì thực hiện việc đó trong nhiều truy vấn đơn lẻ.
Tạo chỉ mục không được nhóm trên các cột thường được sử dụng làm cụm từ tìm kiếm trong truy vấn của bạn. Ở ĐÂU và các kết nối trong THAM GIA.
Xem xét lập chỉ mục các cột được sử dụng trong truy vấn tra cứu hàng để có giá trị khớp chính xác.

Tại sao một bảng không thể có hai chỉ mục được nhóm?

TẠO CHỈ SỐ CỤM ĐỘC ĐÁO ix_oriderid_lineid TRÊN dbo.Sales(OrderID, LineID);

Khi bạn chạy tập lệnh này, tất cả các hàng trong bảng sẽ được sắp xếp vật lý trước tiên theo cột OrderID, sau đó là LineID, nhưng bản thân dữ liệu sẽ vẫn ở trong một khối logic duy nhất, đó là bảng. Vì lý do này, bạn không thể tạo hai chỉ mục nhóm. Chỉ có thể có một bảng có một dữ liệu và bảng đó chỉ có thể được sắp xếp một lần theo một thứ tự cụ thể.

Nếu bảng phân cụm mang lại nhiều lợi ích thì tại sao lại sử dụng bảng đống?

Làm cách nào để thay đổi hệ số điền chỉ mục mặc định?

EXEC sp_configure "hiển thị các tùy chọn nâng cao ", 1; ĐI CẤU HÌNH LẠI; ĐI EXEC sp_configure " hệ số lấp đầy", 90; ĐI CẤU HÌNH LẠI; ĐI

Sau khi thay đổi giá trị hệ số điền chỉ mục, bạn cần khởi động lại dịch vụ Máy chủ SQL. Bây giờ bạn có thể kiểm tra giá trị đã đặt bằng cách chạy sp_configure mà không cần đối số thứ hai được chỉ định:

EXEC sp_configure "hệ số điền" GO

Lệnh này sẽ trả về giá trị 90. Kết quả là tất cả các chỉ mục mới được tạo sẽ sử dụng giá trị này. Bạn có thể kiểm tra điều này bằng cách tạo chỉ mục và truy vấn giá trị hệ số điền:

SỬ DỤNG AdventureWorks2012; -- cơ sở dữ liệu của bạn TẠO CHỈ SỐ KHÔNG BAO GỒM ix_people_lastname TRÊN Person.Person(LastName); ĐI CHỌN fill_factor TỪ sys .indexes Ở ĐÂU object_id = object_id("Person.Person" ) VÀ name ="ix_people_lastname" ;

Trong ví dụ này, chúng tôi đã tạo một chỉ mục không được nhóm trên một bảng Người trong cơ sở dữ liệu AdventureWorks2012. Sau khi tạo chỉ mục, chúng ta có thể nhận giá trị hệ số điền từ các bảng hệ thống sys.indexes. Truy vấn sẽ trả về 90.
Tuy nhiên, hãy tưởng tượng rằng chúng tôi đã xóa chỉ mục và tạo lại chỉ mục, nhưng bây giờ chúng tôi đã chỉ định một giá trị hệ số điền cụ thể:

TẠO CHỈ SỐ KHÔNG BAO GỒM ix_people_lastname TRÊN Person.Person(LastName) VỚI (fillfactor=80 ); ĐI CHỌN fill_factor TỪ sys .indexes Ở ĐÂU object_id = object_id("Person.Person" ) VÀ name ="ix_people_lastname" ;

Lần này chúng tôi đã thêm hướng dẫn VỚI và tùy chọn yếu tố lấp đầy cho hoạt động tạo chỉ mục của chúng tôi TẠO CHỈ SỐ và chỉ định giá trị 80. Toán tử LỰA CHỌN bây giờ trả về giá trị tương ứng.
Cho đến nay, mọi thứ đã khá đơn giản. Nơi bạn thực sự có thể bị đốt cháy trong toàn bộ quá trình này là khi bạn tạo một chỉ mục sử dụng giá trị hệ số mặc định, giả sử bạn biết giá trị đó. Ví dụ, có người đang mày mò cài đặt máy chủ và bướng bỉnh đến mức họ đặt hệ số điền chỉ mục là 20. Trong khi đó, bạn tiếp tục tạo chỉ mục, giả sử giá trị mặc định là 0. Rất tiếc, bạn không có cách nào để tìm ra cách điền nhân tố cho đến khi bạn không tạo chỉ mục rồi kiểm tra giá trị như chúng tôi đã làm trong các ví dụ của mình. Nếu không, bạn sẽ phải đợi đến thời điểm hiệu suất truy vấn giảm xuống mức bạn bắt đầu nghi ngờ điều gì đó.
Một vấn đề khác bạn cần lưu ý là việc xây dựng lại các chỉ mục. Giống như việc tạo chỉ mục, bạn có thể chỉ định giá trị hệ số điền chỉ mục khi xây dựng lại nó. Tuy nhiên, không giống như lệnh tạo chỉ mục, lệnh xây dựng lại không sử dụng các cài đặt mặc định của máy chủ, bất chấp vẻ ngoài của nó. Hơn nữa, nếu bạn không chỉ định cụ thể giá trị hệ số điền chỉ mục thì Máy chủ SQL sẽ sử dụng giá trị của hệ số mà chỉ số này tồn tại trước khi tái cơ cấu. Ví dụ, thao tác sau THAY ĐỔI CHỈ SỐ xây dựng lại chỉ mục chúng ta vừa tạo:

THAY ĐỔI CHỈ SỐ ix_people_lastname TRÊN Person.Person XÂY DỰNG LẠI ; ĐI CHỌN fill_factor TỪ sys .indexes Ở ĐÂU object_id = object_id("Person.Person" ) VÀ name ="ix_people_lastname" ;

Khi kiểm tra giá trị hệ số lấp đầy, chúng tôi sẽ nhận được giá trị 80, vì đó là giá trị chúng tôi đã chỉ định khi tạo chỉ mục lần cuối. Giá trị mặc định bị bỏ qua.
Như bạn có thể thấy, việc thay đổi giá trị hệ số điền chỉ mục không quá khó. Việc biết giá trị hiện tại và hiểu khi nào nó được áp dụng sẽ khó khăn hơn nhiều. Nếu bạn luôn chỉ định cụ thể hệ số khi tạo và xây dựng lại chỉ mục thì bạn luôn biết kết quả cụ thể. Trừ khi bạn phải lo lắng về việc đảm bảo người khác không làm hỏng cài đặt máy chủ nữa, khiến tất cả các chỉ mục được xây dựng lại với hệ số lấp đầy chỉ mục thấp đến mức nực cười.

Có thể tạo chỉ mục nhóm trên một cột chứa các bản sao không?

Bảng được lưu trữ như thế nào nếu chỉ mục nhóm chưa được tạo?

Mối quan hệ giữa các ràng buộc về tính duy nhất của giá trị và khóa chính với các chỉ mục bảng là gì?

Tại sao các chỉ mục được nhóm và không được nhóm được gọi là cây B trong SQL Server?

Làm cách nào một chỉ mục có thể cải thiện hiệu suất truy vấn nếu bạn phải duyệt qua tất cả các nút chỉ mục này?

Nếu các chỉ mục quá tuyệt vời, tại sao không tạo một chỉ mục trên mỗi cột?

Có cần thiết phải tạo chỉ mục nhóm trên một cột bằng khóa chính không?

Điều gì sẽ xảy ra nếu bạn lập chỉ mục một chế độ xem, liệu nó có còn là một chế độ xem không?

Tại sao nên sử dụng chỉ mục bao trùm thay vì chỉ mục tổng hợp?

CHỌN OrderID, OrderDate TỪ Doanh số bán hàng ở đâu OrderID = 12345 ;

Bạn có thể tạo chỉ mục tổng hợp không được nhóm trên cả hai cột, nhưng cột OrderDate sẽ chỉ thêm chi phí bảo trì chỉ mục mà không đóng vai trò là cột khóa đặc biệt hữu ích. Giải pháp tốt nhất là tạo chỉ mục bao phủ trên cột khóa ID đơn hàng và bổ sung thêm cột Ngày đặt hàng:

TẠO CHỈ SỐ KHÔNG BAO GỒM ix_orderid TRÊN dbo.Sales(OrderID) BAO GỒM (OrderDate);

Điều này tránh được nhược điểm của việc lập chỉ mục các cột dư thừa trong khi vẫn duy trì lợi ích của việc lưu trữ dữ liệu trong các lá khi chạy truy vấn. Cột được đưa vào không phải là một phần của khóa nhưng dữ liệu được lưu trữ trên nút lá, lá chỉ mục. Điều này có thể cải thiện hiệu suất truy vấn mà không cần thêm bất kỳ chi phí nào. Ngoài ra, các cột có trong chỉ mục bao gồm ít hạn chế hơn so với các cột chính của chỉ mục.

Số lượng bản sao trong một cột khóa có quan trọng không?

Có thể tạo chỉ mục không được nhóm trên một tập hợp con cụ thể của dữ liệu của cột chính không?

TẠO CHỈ SỐ KHÔNG BAO GỒM ix_trackingnumber TRÊN Sales.SalesOrderDetail(CarrierTrackingNumber) Ở ĐÂU CarrierTrackingNumber KHÔNG CÓ NULL ;

Trên thực tế, chúng tôi có thể lọc ra bất kỳ dữ liệu nào không quan trọng trong các truy vấn quan trọng. Nhưng hãy cẩn thận, vì... Máy chủ SQLáp đặt một số hạn chế đối với các chỉ mục có thể lọc, chẳng hạn như không thể tạo chỉ mục có thể lọc trên một dạng xem, vì vậy hãy đọc kỹ tài liệu.
Cũng có thể bạn có thể đạt được kết quả tương tự bằng cách tạo chế độ xem được lập chỉ mục. Tuy nhiên, chỉ mục được lọc có một số ưu điểm, chẳng hạn như khả năng giảm chi phí bảo trì và cải thiện chất lượng kế hoạch thực hiện của bạn. Các chỉ mục đã lọc cũng có thể được xây dựng lại trực tuyến. Hãy thử điều này với chế độ xem được lập chỉ mục.

Trong bài viết trước, chúng tôi đã giới thiệu các cách tối ưu hóa cơ sở dữ liệu quan hệ và thảo luận về cách hoạt động của các chỉ mục được phân cụm và không phân cụm trong bối cảnh tối ưu hóa thời gian thực hiện truy vấn cơ sở dữ liệu. Bây giờ là lúc áp dụng kiến thức này vào thực tế bằng cách học cách tạo chỉ mục tối ưu hóa cho cơ sở dữ liệu MS SQL.

Hãy để tôi nhắc bạn về định nghĩa của lược đồ bảng Staffs mà chúng ta sẽ làm việc với:

Bảng nhân viên

Giả sử chúng ta cần tạo một chỉ mục không được nhóm cho bảng Nhân viên, điều này sẽ tối ưu hóa truy vấn sau:

CHỌN Id, Tên, Công việc TỪ Nội dung CÓ LƯƠNG > 1000 VÀ Ảnh KHÔNG CÓ

Khóa chỉ mục sẽ là cột LƯƠNG và Ảnh, vì lựa chọn được lọc theo các trường này. Và các cột Id, Name và Job sẽ là các cột có trong chỉ mục.

Cú pháp lệnh chung như sau:

SỬ DỤNG ĐI
TẠO CHỈ SỐ KHÔNG BAO GỒM TRÊN (ASC - cột khóa chỉ mục)
BAO GỒM ( -- bao gồm các cột) ĐI

Trong trường hợp của chúng tôi, yêu cầu sẽ như thế này:

(Mức lương, Ảnh) BAO GỒM (Id, Họ tên, Công việc) GO

Chúng tôi đã tạo một chỉ mục không được nhóm. Hay đúng hơn là một chỉ mục bao gồm không phân cụm. Điều này có nghĩa là chỉ mục chứa tất cả các trường cần thiết để thực hiện truy vấn và SQL Server sẽ không truy cập vào bảng cơ sở khi thực hiện truy vấn.

Nếu mã của chúng tôi là như thế này:

TẠO CHỈ SỐ KHÔNG BAO GỒM IDX_StaffsTìm kiếm TRÊN Nội dung
(Lương, Ảnh) BAO GỒM (Id) ĐI

Trong trường hợp này, chỉ mục không còn là chỉ mục bao trùm vì nó không bao gồm tất cả các cột được sử dụng trong truy vấn. Trình tối ưu hóa sẽ vẫn sử dụng chỉ mục này khi thực hiện truy vấn, nhưng hiệu quả của nó sẽ giảm đi một mức độ lớn vì nó sẽ yêu cầu quyền truy cập vào bảng cơ sở.

Chỉ mục nhóm được tạo bằng lệnh sau:

TẠO CHỈ SỐ CLUSTERED IDX_Stsffsid TRÊN Nội dung (Id)

Ở đây, một chỉ mục nhóm duy nhất đã được tạo dựa trên khóa chính của bảng (cột Id).

Ví dụ thực tế

Bây giờ chúng ta hãy phát triển một kịch bản trong đó chúng ta có thể đánh giá một cách thực tế mức độ đạt được hiệu suất trong trường hợp sử dụng chỉ mục.

Hãy tạo một cơ sở dữ liệu mới:

TẠO CƠ SỞ DỮ LIỆU TestDB;

Và một bảng Khách hàng duy nhất sẽ bao gồm bốn cột:

TẠO BẢNG .(
KHÔNG NULL, NULL, NULL, NULL) ĐI

Bây giờ hãy điền vào bảng của chúng tôi dữ liệu ngẫu nhiên. Cột Id sẽ được tăng lên theo vòng lặp và ba cột còn lại của bảng sẽ chứa đầy các số ngẫu nhiên bằng cách sử dụng một phiên bản đặc biệt của hàm ngẫu nhiên:

KHAI THÁC @i int = 0;
Trong khi tôi< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(
@i, abs(checksum(newid())), abs(checksum(newid())), abs(checksum(newid())) SET @i = @i + 1; KẾT THÚC

Tập lệnh này thêm nửa triệu bản ghi vào bảng, vì vậy hãy kiên nhẫn, tập lệnh sẽ chạy trong ít nhất 3 phút.

Mọi thứ đã sẵn sàng cho cuộc thử nghiệm. Chúng tôi sẽ đánh giá các đặc tính hiệu suất của truy vấn. Vì thời gian thực hiện truy vấn có thể phụ thuộc vào máy cụ thể nên chúng tôi sẽ phân tích một chỉ báo độc lập hơn - số lần đọc logic.

Để bật chế độ thu thập số liệu thống kê, bạn phải chạy lệnh sau:

Bây giờ, sau khi thực hiện từng yêu cầu, trên tab Tin nhắn, chúng ta sẽ có quyền truy cập vào số liệu thống kê về việc thực hiện yêu cầu này, như hiển thị bên dưới:

Chúng tôi chỉ quan tâm đến giá trị của tham số đọc logic.

Vì vậy, chưa có chỉ mục nào trong bảng của chúng tôi. Hãy chạy ba truy vấn sau và ghi lại số lần đọc logic cho mỗi truy vấn vào bảng kết quả bên dưới:

1) CHỌN Id, Num1, Num2 TỪ Khách hàng Ở ĐÂU Id = 2000

2) CHỌN Id, Num1, Num2 TỪ Khách hàng Ở ĐÂU Id >= 0 VÀ Id< 1000

3) CHỌN Id, Num1, Num2 TỪ Khách hàng Ở ĐÂU Id >= 0 VÀ Id< 5000

Các truy vấn này sẽ trả về lần lượt 1 hàng, 1000 hàng và 5000 hàng. Không có chỉ mục, chỉ số hiệu suất (số lần đọc logic) cho tất cả các truy vấn đều giống nhau và bằng 1621. Hãy nhập dữ liệu vào bảng kết quả:

Chúng tôi thấy rằng đối với truy vấn thứ hai và thứ ba, khi trả về một số lượng hàng khá lớn, chỉ mục mà chúng tôi tạo không cải thiện hiệu suất. Tuy nhiên, đối với truy vấn trả về một hàng duy nhất, tốc độ tăng lên rất lớn. Vì vậy, chúng ta có thể kết luận rằng việc tạo các chỉ mục không bao phủ là hợp lý khi tối ưu hóa các truy vấn trả về một kết quả duy nhất.

Bây giờ chúng ta hãy tạo một chỉ mục bao phủ để đạt được hiệu suất tối đa.

Đầu tiên, hãy xóa chỉ mục trước đó:

SỬ DỤNG TestDB GO DROP INDEX Khách hàng.TestIndex1

Và hãy tạo một chỉ mục mới:

TẠO CHỈ SỐ KHÔNG BAO GỒM TestIndex2 TRÊN dbo.Customers(Id) BAO GỒM (Num1, Num2);

Bây giờ hãy chạy truy vấn của chúng ta lần thứ ba và ghi kết quả vào bảng:


Không có chỉ mục
Chỉ số không che phủ
Chỉ số che phủ

Thật dễ dàng để thấy rằng hiệu suất tăng lên rất lớn. Như vậy, chúng tôi đã tăng tốc độ thực hiện truy vấn lên hàng chục lần. Khi chạy cơ sở dữ liệu lưu trữ hàng triệu hàng, mức tăng hiệu suất này sẽ khá đáng chú ý.

Trong bài viết này, chúng ta đã xem xét một ví dụ về tối ưu hóa cơ sở dữ liệu bằng cách tạo chỉ mục. Điều đáng chú ý là việc tạo chỉ mục là một quá trình hoàn toàn riêng lẻ cho mỗi yêu cầu. Để xây dựng một chỉ mục thực sự tối ưu hóa hiệu suất truy vấn, bạn phải phân tích cẩn thận chính truy vấn đó và kế hoạch thực hiện truy vấn đó.