Session 09

Chương 9

Chỉ mục và chỉ mục toàn văn

Mục tiêu bài học: Cuối bài học này, bạn có thể:

Định nghĩa một chỉ mục Hiểu được quá trình tạo chỉ mục Định nghĩa chỉ mục Clustered và chỉ mục Non-Clustered với các đặc tính duy nhất và phức hợp Xem các chỉ mục của bảng Nắm vững cách sử dụng chỉ mục Xoá các chỉ mục Hiểu rõ tìm kiếm toàn văn và các chỉ mục toàn văn Hiểu rõ cách sử dụng các từ khoá CONTAINS và FREETEXT trong các câu truy vấn toàn văn Giới thiệu Ở chương trước, chúng ta đã được học về các giao dịch và các cơ chế khoá bản ghi. Khái niệm về các giao dịch, lợi ích của nhật ký các giao dịch và các cơ chế khoá bản ghi được đề cập rất sâu. Các cơ chếkhoá chết bản ghi và việc sử lý các cơ chế khoá chết cũng được đề cập ở chương trước. Các chỉ mục làm cho việc truy nhập tới các hàng của một bảng dựa vào giá trị của một hoặc nhiều cột trở nên nhanh. Chúng cung cấp một thứ tự logic cho dữ liệu trong các bảng. Một chỉ mục hợp lý có thể nâng cao khả năng thực thi của cơ sở dữ liệu bằng việc giảm thiểu thời gian truy nhập. Trong chương này chúng ta sẽ học về các chỉ mục. 9.1

Các chỉ mục

Các chỉ mục trong SQL Server tương tự như các chỉ mục sách. Khi các thông tin về một chủ đề đưa ra được yêu cầu từ một quyển sách, chúng ta không tìm từ trang thứ nhất đến trang cuối cùng. Thay vì như thế, chúng ta sẽ định vị chủ đề đưa ra dựa trên các chỉ mục ở cuối cuốn sách. Chỉ mục trợ giúp cho việc tìm kiếm nhanh thông tin mà không cần phải đọc hết toàn bộ cuốn sách. Trong một cơ sở dữ liệu, một chỉ mục cho phép chương trình tìm kiếm các dữ liệu cần thiết mà không cần duyệt toàn bộ bảng. Chỉ mục được duyệt để tìm một giá trị đặc biệt. Sau đó con trỏ chỉ mục được dùng để định vị và nhận lấy hàng chứa giá trị yêu cầu trong bảng. Con trỏ này được tham chiếu đến như khoảng chừa trống (offset). Vì vậy, sử dụng chỉ mục để truy nhập thông tin trở nên nhanh hơn nhiều so với truy nhập dữ liệu trực tiếp. Điển hình là một chỉ mục sách chứa danh sách các từ khoá (tương đương các khoá) sắp xếp theo thứ tự alphabetic và số trang (tương đương con trỏ) mà ở đó chứa chủ để được đề cập đến trong cuốn sách. Hình 9.1 hiển thị một ví dụ về chỉ mục của sách.

120

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

A Access, 408 Action menu, 335 Active status, 408 Administration table tools, 23-24 Advanced query techniques, 216-218

Số trang: Các con trỏ

B BACKUP, 498 Base data type, 105 Base table, 110 Binary data type, 112 Bit data type, 113

Các chủ đề: Các khoá

Hình 9.1: Ví dụ về chỉ mục sách Mặc dù hầu hết các cuốn sách chứa một chỉ mục đơn, một số cuốn sách chứa các đa chỉ mục. Ví dụ, một cuốn sách về âm nhạc có thể có một chỉ mục cho các ca sĩ và một chỉ mục khác cho các bài hát. Khái niệm về chỉ mục cơ sở dữ liệu cũng tương tự như vậy. Các đa chỉ mục có thể được tạo ra trên một bảng đơn tuỳ theo yêu cầu. Ví dụ, bảng titles của cơ sở dữ liệu pubs sẽ được đánh chỉ mục theo ID của nhà xuất bản hoặc kết hợp ID của nhà xuất bản và ID của tiêu đề. Các chỉ mục cơ sở dữ liệu chứa một danh sách các giá trị của cột đơn hoặc nhiều cột của bảng (các khoá) và địa chỉ của các trang dữ liệu (các con trỏ) mà tại đó dữ liệu được lưu trữ. Một chỉ mục có thể được tạo cho một trường đơn hoặc một tập hợp các trường. SQL Server tự động tạo ra các chỉ mục cho các kiểu ràng buộc như các ràng buộc PRIMARY KEY và UNIQUE. Các chỉ mục không mang tính bắt buộc, nhưng nếu dữ liệu trong các bảng được truy nhập mà không có chỉ mục thì việc tìm kiếm có thể mất rất nhiều thời gian. SQL Server sử dụng thành phần tối ưu hoá truy vấn để tìm ra cách hiệu quả nhất khi thực hiện một câu truy vấn, và thành phần này dựa vào các chỉ mục. Tiện ích tối ưu hoá truy vấn (Query Optimizer) là một công cụ thông minh, luôn thiết lập phương pháp tốt nhất để nhận lấy dữ liệu yêu cầu thông qua các câu truy vấn. Tuy nhiên, những lợi ích về mặt thực thi trong cơ sở dữ liệu khi sử dụng chỉ mục luôn đi kèm với một chi phí. Các bảng với các chỉ mục yêu cầu thêm các vùng lưu trữ trong cơ sở dữ liệu. Các lệnh xử lý dữ liệu cần thêm thời gian vì chúng cần cập nhật chỉ mục. Khi các chỉ mục được thiết kế và tạo ra, nó cần phải đảm bảo rằng các ích lợi về việc thực thi luôn lớn hơn chi phí dùng cho việc lưu trữ nó. 9.1.1

Tạo chỉ mục

Câu lệnh CREATE INDEX được sử dụng để tạo ra chỉ mục trên bảng. Câu lệnh hoặc thay đổi thứ tự vật lý của bảng, hoặc đưa ra việc tối ưu hoá truy vấn với một thứ tự logic của bảng nhằm tăng hiệu quả cho các câu truy vấn. Chỉ có quyền sở hữu bảng mới có thể tạo các chỉ mục trên bảng.

121

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Cú pháp của lệnh CREATE INDEX là: CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name[, column_name]…) [WITH [PAD_INDEX] [[,]FILLFACTOR=x] [[,]DROP_EXISTING] ] trong đó:  UNIQUE chỉ ra rằng không bao giờ hai hàng có chung một giá trị chỉ mục.  [CLUSTERED][NONCLUSTERED] là các kiểu chỉ mục khác nhau.  PAD_INDEX chỉ ra khoảng trống còn lại mở ra trong mỗi trang ở mức trung của chỉ mục. Tuỳ chọn PAD_INDEX chỉ hữu ích khi FILLFACTOR (hệ số điền) được xác định, bởi vì PAD_INDEX sử dụng tỷ lệ được xác định bởi hệ số điền.  FILLFACTOR là một giá trị từ 0 đến 100 mà xác định phần trăm để trống của trang chỉ mục.  DROP_EXISTING xoá một chỉ mục nào đó có cùng tên trong hệ thống. 9.1.2

Nguyên tắc tạo chỉ mục

Nói chung, các cột để tạo chỉ mục nên được chọn lựa cẩn thận. Một chỉ mục không nên được tạo trên một cột với chỉ hai hoặc một số ít các giá trị riêng biệt, vì quá nhiều hàng có khả năng được lựa chọn cho cùng một khoảng chừa trống (offset). Một cột có thể được chọn để tạo chỉ mục dựa trên các quy tắc dưới đây:  Cột được sử dụng cho việc tìm kiếm một cách thường xuyên.  Cột được sử dụng cho việc sắp xếp dữ liệu. Không đánh chỉ mục các cột trong các trường hợp sau: 

Cột chỉ chứa một vài các giá trị duy nhất.



Bảng chỉ chứa một vài hàng. Việc đánh chỉ mục trên các bảng nhỏ có thể không là tối ưu vì nó khiến cho SQL Server mất nhiều thời gian để duyệt chỉ mục để tìm kiếm dữ liệu hơn là thực hiện việc tìm kiếm tuần tự từng hàng trong bảng.

9.1.3

Hệ số điền (Fill Factor)

Hệ số điền được xác định tại thời điểm tạo một chỉ mục. Khi một chỉ mục clustered được tạo, dữ liệu trong bảng được lưu trữ trong các trang dữ liệu của cơ sở dữ liệu theo thứ tự của các giá trị trong các cột chỉ mục. Khi các bản ghi mới được thêm vào trong bảng hoặc các giá trị trong các cột chỉ mục thay đổi, SQL Server có thể phải tổ chức lại các mẫu dữ liệu được lưu trữ trong bảng để tạo ra chỗ cho các bản ghi mới và duy trì sự lưu trữ dữ liệu được sắp xếp. Điều này cũng được áp dụng cho chỉ mục non-clustered.

Chỉ mục và chỉ mục toàn văn

122

Khi dữ liệu được thêm vào hoặc thay đổi, SQL Server có thể phải tổ chức lại việc lưu trữ dữ liệu trong trang chỉ mục non-clustered. Trong khi thêm mới một hàng vào một trang chỉ mục đầy đủ, SQL Server sẽ chuyển khoảng chừng một nửa tổng số các hàng đến một trang mới để tạo chỗ cho hàng mới. Kiểu tổ chức lại dữ liệu này được gọi là phân trang. Điều này có thể ảnh hưởng tới tốc độ thực thi và phá vỡ việc lưu trữ dữ liệu trong bảng. Khi một chỉ mục được tạo, hệ số điền được xác định để dành ra các vùng trống mở rộng và dự trữ một phần không sử dụng trong từng trang chỉ mục. Nó để dành các vùng lưu trữ cho việc mở rộng dữ liệu của bảng trong tương lai và giảm thiểu công việc phân trang có thể. Giá trị của hệ số điền là một giá trị từ 0 đến 100 được tính bằng đơn vị phần trăm, xác định dữ liệu được đưa vào các trang sau khi chỉ mục được tạo. Giá trị 100 có nghĩa là các trang sẽ bị đầy và sẽ chiếm giữ vùng lưu trữ nhỏ nhất. Thiết lập này chỉ nên sử dụng khi bảng không có thay đổi nào về dữ liệu chẳng hạn như một bảng chỉ đọc. Một giá trị nhỏ hơn 100 sẽ giảm thiểu nhu cầu phân trang khi các chỉ mục lớn lên nhưng đòi hỏi tốn nhiều vùng lưu trữ hơn. Những thiết lập này phù hợp với những bảng dữ liệu hay phải thay đổi. 9.2

Các kiểu chỉ mục

Sau khi hiểu rõ việc tạo chỉ mục, chúng ta tiếp tục với các khái niệm về các kiểu chỉ mục dưới đây.  Chỉ mục Clustered  Chỉ mục Non-clustered 9.2.1

Chỉ mục Clustered

Một chỉ mục clustered xác định thứ tự lưu trữ của dữ liệu trong một bảng. Một bảng có thể có chỉ một chỉ mục clustered bởi vì chỉ mục clustered xác định thứ tự lưu trữ vật lý của dữ liệu. Tuy nhiên, chỉ mục có thể bao gồm nhiều cột, giống như cách thức sắp xếp bằng họ và tên của thư mục điện thoại. Ví dụ, các bản ghi của bảng roysched trong cơ sở dữ liệu pubs không được sắp xếp. Để tạo một chỉ mục clustered trên trường title_id, câu lệnh sẽ là: CREATE CLUSTERED INDEX CLINDX_titleid ON roysched (title_id) Sau khi tạo chỉ mục, dữ liệu trong cột title_id được sắp xếp bằng cách định vị lại tất cả các bản ghi. Hình 9.2 và 9.3 hiển thị nội dung của bảng trước và sau khi tạo chỉ mục.

123

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Hình 9.2: Bảng roysched trước khi tạo chỉ mục Clustered

Hình 9.3: Bảng Roysched sau khi tạo chỉ mục Clustered Một chỉ mục clustered có hiệu quả đối với các cột được truy nhập dữ liệu thường xuyên. Mỗi khi giá trị của hàng đầu tiên được tìm thấy bằng chỉ mục clustered, các hàng với các giá trị chỉ mục tiếp theo chắc chắn sẽ nằm ở vị trí vật lý tiếp theo. Ví dụ, một ứng dụng thường xuyên yêu cầu nhận về các bản ghi giữa hai ngày, một chỉ mục clustered có thể xác định nhanh chóng hàng chứa ngày bắt đầu, và sau đó nhận về tất cả các hàng tiếp theo trong bảng cho đến khi tìm thấy ngày kết thúc. Điều này có thể cải thiện tốc độ thực thi. Thêm nữa, nếu một cột được sử dụng thường xuyên để sắp xếp dữ liệu, việc tập hợp lại các cột của bảng để tiết kiệm chi phí sắp xếp mỗi khi các cột được truy nhập là rất thuận lợi. Các chỉ mục Clustered cũng có hiệu quả để tìm kiếm một hàng xác định khi giá trị của chỉ mục là duy nhất. Ví dụ, cách nhanh nhất để tìm một nhân viên sử dụng trường Employee_Id sẽ là tạo một chỉ mục clustered hoặc sử dụng ràng buộc PRIMARY KEY trên cột Employee_Id. Ghi chú: Ràng buộc PRIMARY KEY tự động tạo chỉ mục clustered nếu một chỉ mục clustered không tồn tại trên bảng.

Chỉ mục và chỉ mục toàn văn

124

Mọi bảng nên có một chỉ mục clustered để tăng tốc quá trình truy vấn dữ liệu. Một số điểm cần quan tâm trong khi tạo một chỉ mục clustered: 

Chọn một cột mà có phần trăm cao giá trị duy nhất. Một cột khoá chính thường là một lựa chọn tốt.



Tạo ra một chỉ mục clustered trước khi một chỉ mục non-clustered được tạo vì như vậy một chỉ mục non-clustered ko phải xây dựng lại sau khi một chỉ mục clustered được tạo ra.

 Sử dụng tuỳ chọn FILLFACTOR khi tạo các chỉ mục clustered để đảm bảo rằng có một khoảng trống phù hợp khi các hàng mới thêm vào. 9.2.2

Chỉ mục Non-Clustered

Một chỉ mục non-clustered xác định cách sắp xếp logic của bảng. Vì vậy, một bảng có thể có rất nhiều chỉ mục non-clustered (có thể lên tới 249). Một chỉ mục non-clustered giống như một chỉ mục trong một cuốn sách giáo khoa. Dữ liệu được lưu trữ trong một nơi, chỉ mục ở một nơi khác cùng với các con trỏ trỏ tới vị trí lưu trữ của dữ liệu. Các nhãn trong chỉ mục được lưu trữ theo thứ tự các giá trị khoá của chỉ mục, nhưng thông tin trong bảng không hề thay đổi. Nếu không có chỉ mục clustered nào được tạo ra trên bảng, các hàng không có một trật tự nhất định nào. Một chỉ mục non-clustered là một chỉ mục trong đó thứ tự logic của chỉ mục không trùng khớp với thứ tự vật lý, thứ tự lưu trữ của các hàng trên ổ đĩa. SQL Server tìm kiếm một giá trị dữ liệu bằng cách tìm theo chỉ mục non-clustered để xác định được vị trí của dữ liệu trong bảng và sau đó nhận về trực tiếp dữ liệu từ vị trí đó. Qúa trình này giống như việc sử dụng chỉ mục trong một quyển sách. Nếu bảng dữ liệu phía dưới được sắp xếp sử dụng một chỉ mục clustered, vị trí là giá trị của khoá; Ngược lại, vị trí là ID của hàng bao gồm số hiệu file, số trang, và số hiệu của hàng. Ví dụ, để tạo một chỉ mục non-clustered trên cột ord_num trong bảng sales của cơ sở dữ liệu pubs, lệnh sẽ lệnh: CREATE NONCLUSTERED INDEX NCLINDX_ordnum ON sales (ord_num) Bây giờ để tìm kiếm một ord_num xác định trong bảng sales, SQL Server thông qua chỉ mục NCLINDX_ordnum để tìm kết quả mà liệt kê hàng và trang chính xác trong bảng tương ứng với giá trị ord_num, và sau đó truy nhập trực tiếp đến trang và hàng đó. Chú ý các quy tắc dưới đây khi tạo các chỉ mục non-clustered:  Thêm một chỉ mục chỉ khi nó thực sự là cần thiết, bởi vì một chỉ mục yêu cầu thêm chi phí để duy trì chúng.  Đánh chỉ mục các cột hay phải truy xuất dữ liệu. 

125

Một cách mặc định, lệnh CREATE INDEX tạo ra một chỉ mục non-clustered.

Thiết kế cơ sở dữ liệu và thực hành với SQL Server



Sử dụng chỉ mục non-clustered cho các cột chứa nhiều giá trị riêng biệt, chẳng hạn như tập hợp họ và tên (nếu một chỉ mục clustered được sử dụng cho các cột khác). Nếu có ít giá trị riêng biệt, chẳng hạn như chỉ gồm 1 và 0, thì chúng ta không cần tạo chỉ mục.

9.3

Đặc trưng của các chỉ mục

Chúng ta có thể tạo các chỉ mục clustered và non-clustered với các tính năng duy nhất hoặc phức hợp để tăng cường tốc độ thực thi trong tương lai. Các chỉ mục duy nhất không cho phép trùng lặp giá trị trong cột chỉ mục, và các chỉ mục phức hợp cho phép hai hoặc nhiều cột được sử dụng để tạo chỉ mục. 9.3.1

Chỉ mục duy nhất

Một chỉ mục duy nhất đảm bảo rằng cột chỉ mục không chứa bất kỳ một giá trị trùng lặp nào. Trong trường hợp các chỉ mục duy nhất gồm nhiều cột, chỉ mục phải đảm bảo rằng mỗi tập hợp giá trị trong cột chỉ mục là duy nhất. Ví dụ, nếu một chỉ mục duy nhất được tạo trên một tập hợp các cột Last_name, First_name, và Middle_initial, hai người bất kỳ không bao giờ được có trùng tên đầy đủ trong bảng. Cả hai loại chỉ mục clustered và non-clustered đều có thể có tính năng duy nhất. Vì thế, nếu dữ liệu trong cột là duy nhất, chúng ta có thể tạo ra một chỉ mục clustered và nhiều giá trị nhưng có tính năng duy nhất, các chỉ mục non-clustered trên cùng một bảng. Chú ý các quy tắc sau trong khi tạo các chỉ mục có tính duy nhất:  Tính toàn vẹn của thực thể là bắt buộc trong các chỉ mục duy nhất bởi vì một giá trị duy nhất tồn tại cho mỗi hàng  Một giá trị NULL trong một cột khoá được tính như một giá trị duy nhất.  Một chỉ mục duy nhất không thể được tạo ra trên một cột chứa các dữ liệu trùng lặp. Dữ liệu trùng lặp phải được cập nhật hoặc xoá trước khi chỉ mục duy nhất được tạo.  Việc tạo khoá chính hoặc các ràng buộc duy nhất tự động sinh ra chỉ mục duy nhất trên cột chỉ định của bảng. 9.3.2

Các chỉ mục phức hợp

Một chỉ mục phức hợp bao gồm hai hoặc nhiều cột được đánh chỉ mục. Số cột tối đa là 16 và kích thước lớn nhất được phép là 900 bytes. Các chỉ mục phức hợp yêu cầu chi phí ít hơn các chỉ mục đơn trong suốt quá trình xử lý dữ liệu và có thể được sử dụng thay cho nhiều chỉ mục đơn. Ví dụ, bảng sales của cơ sở dữ liệu pubs có một chỉ mục clustered được tạo tên tập hợp các cột stor_id, ord_num và title_id. Chỉ mục được tạo ra trên ba cột để đảm bảo rằng các giá trị duy nhất cho từng hàng. Câu lệnh để tạo chỉ mục phức hợp như sau: CREATE UNIQUE CLUSTERED INDEX UPKCL_sales ON sales (stor_id, ord_num, title_id) Chú ý các quy tắc dưới đây khi tạo chỉ mục phức hợp :

Chỉ mục và chỉ mục toàn văn

126

 Tạo một chỉ mục phức hợp khi hai hoặc nhiều cột thường được xử dụng để tìm kiếm. 

Khi tạo một chỉ mục phức hợp, đầu tiên liệt kê cột có nhiều giá trị riêng biệt nhất.

 Thứ tự và số hiệu của các cột trong một chỉ mục phức hợp ảnh hưởng tới tốc độ thực thi truy vấn.  Không đánh chỉ mục các cột mà tốn nhiều thời gian để nhận về dữ liệu từ nó. 9.4

Xem các chỉ mục

Sau khi tạo các chỉ mục, chúng ta có thể cần xem thông tin về các chỉ mục. Ví dụ, chúng ta có thể muốn xem danh sách các chỉ mục được tạo ra trên bảng chỉ định hoặc các cột được đánh chỉ mục trong một bảng. Câu lệnh T-SQL sử dụng để xem các chỉ mục của một bảng là: sp_helpindex trong đó, sp_helpindex là một thủ tục lưu hệ thống. Nó báo cáo các thông tin về các chỉ mục trong một bảng. 9.5

Sử dụng các chỉ mục

Sau khi các chỉ mục được tạo ra, điều quan trọng là chúng ta phải biết cách thức mà SQL Server chọn lựa và sử dụng các chỉ mục. SQL Server tự động chọn ra cách tốt nhất để thực hiện các truy vấn bao gồm cả việc sử dụng các chỉ mục nào. Khi một truy vấn được đưa ra, SQL Server hoặc duyệt toàn bộ bảng để tìm dữ liệu yêu cầu hoặc sử dụng một chỉ mục đã có trong bảng. Việc duyệt toàn bộ bảng là rất hiệu quả đối với các bảng lớn. Nếu một chỉ mục đã có, SQL Server có thể dễ dàng tìm được hàng dữ liệu yêu cầu. Để thực hiện một truy vấn, SQL Server sử dụng tiện ích tối ưu hoá truy vấn. Công việc tối ưu hoá truy vấn là một quá trình phân tích các truy vấn để xác định cách tốt nhất thực hiện chúng. Công cụ này cố gắng chọn ra phương thức truy nhập chịu chi phí thấp nhất cho việc vào/ra các trang dữ liệu. Chúng ta làm cho tiện ích tối ưu hoá truy vấn trở nên mạnh hơn bằng việc chọn một chỉ mục riêng biệt cho một câu truy vấn thay thế cách xử lý tự động trong tiện ích tối ưu hoá truy vấn. Điều này có thể làm bằng sự gợi ý tối ưu trong mệnh đề FROM của câu lệnh SELECT. Cú pháp là: (INDEX=index_name) trong đó, index_name là tên của một chỉ mục được tạo ra trên bảng. Ví dụ, để đảm bảo rằng chỉ mục NCLINDX_ordnum sẽ được sử dụng khi truy vấn bảng sales, câu lệnh là: SELECT * FROM sales (INDEX = nclindx_ordnum)

127

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

WHERE ord_num = ‘P3087a’ Nó sẽ thay thế bất kỳ một lựa chọn chỉ mục nào đó mà SQL Server đã tự động tạo ra bằng chỉ mục được đưa ra trong câu truy vấn. Tuy nhiên, chú ý là trong hầu hết các trường hợp, tiện ích tối ưu hoá truy vấn luôn chọn ra quy trình tốt nhất để xử lý câu truy vấn. 9.6

Xoá các chỉ mục

Các chỉ mục đã tồn tại có thể được xoá khỏi cơ sở dữ liệu để giải phóng tài nguyên bằng câu lệnh DROP INDEX. Cú pháp là: DROP INDEX table_name.index_name Ví dụ, câu lệnh để xoá chỉ mục NCLINDX_ordnum được tạo trên bảng sales là: DROP INDEX sales.NCLINDX_ordnum Lệnh DROP INDEX không áp dụng cho các chỉ mục được tạo bởi các ràng buộc PRIMARY KEY hoặc UNIQUE. Nó cũng không thể được sử dụng để xoá một chỉ mục trên một bảng hệ thống. 9.7

Tìm kiếm toàn văn

Các chỉ mục toàn văn được sử dụng trong SQL Server để thực hiện tìm kiếm toàn văn. Chúng ta đã sử dụng từ khoá LIKE trong các câu truy vấn để tìm kiếm các kí tự hoặc chuỗi kí tự trong các bảng. Các chỉ mục có thể được xây dựng trên các chuỗi kí tự phi cấu trúc để dùng cho việc tìm kiếm. Chức năng này được cung cấp trong dịch vụ tìm kiếm của Microsoft (Microsoft Search Service). Với Microsoft Search Service, chúng ta có thể thực hiện các tìm kiếm phức tạp sử dụng điều kiện tìm kiếm. Tìm kiếm cho phép tìm các từ hoặc các cụm từ, các từ gần giống với một từ khác, và các từ với các định dạng khác nhau. Các đối tượng thực hiện việc tìm kiếm toàn văn là chỉ mục toàn văn và danh mục toàn văn.  Chỉ mục toàn văn Một chỉ mục toàn văn theo sát các từ có ý nghĩa trong một bảng. Chỉ mục này được sử dụng cho việc tìm kiếm chuỗi văn bản có chứa các từ chỉ mục và chức năng tìm kiếm nâng cao, chẳng hạn như tìm kiếm các cụm từ. Một chỉ mục toàn văn yêu cầu một cột chứa các giá trị khoá duy nhất. Các chỉ mục toàn văn không tự động cập nhật khi dữ liệu trong bảng được thêm mới, sửa đổi hoặc xoá. Chúng phải được cập nhật bằng tay.  Danh mục toàn văn Tất cả các chỉ mục toàn văn được lưu trữ trong các danh mục toàn văn. Một danh mục toàn văn là một thư mục mà chỉ có thể được nhìn thấy bởi Windows và Search Service. Ngầm định, tất cả các chỉ mục toàn văn cho cơ sở dữ liệu được đặt trong một danh mục toàn văn. Tuy nhiên, người quản trị hệ thống có thể tách một danh mục thành nhiều danh mục nếu như số lượng các chỉ mục rất lớn.

Chỉ mục và chỉ mục toàn văn

128

9.7.1

Sử dụng chỉ mục toàn văn

Các chỉ mục toàn văn có thể được tạo ra trong trình Enterprise Manager sử dụng Full-Text Indexing Wizard. Sau khi tạo, các truy vấn toàn văn có thể được thực hiện trên các bảng đã được tạo chỉ mục. Các truy vấn này sử dụng hai từ khoá CONTAINS và FREETEXT.  Từ khoá CONTAINS Từ khoá CONTAINS tìm kiếm các cột chứa các kiểu dữ liệu kí tự phù hợp với các từ đơn và các cụm từ, các từ giống một phần một từ khác, và những sự phù hợp chính xác. Các câu truy vấn trong trường hợp này có thể nhìn giống như những truy vấn được viết với từ khoá LIKE. Tuy nhiên, CONTAINS có thể cung cấp khả năng truy vấn tốt hơn nhiều so với từ khoá LIKE. Cũng như vậy, không giống từ khoá LIKE, CONTAINS là một từ khoá tìm kiếm phân biệt hoa thường. Ví dụ, trong bảng titles của cơ sở dữ liệu pubs. Cột title sẵn sàng cho việc đánh chỉ mục toàn văn. Để tìm kiếm các tiêu đề chứa các từ “computer”, “cooking” và “silicon”, câu truy vấn là: SELECT title FROM titles WHERE CONTAINS(title,'"computer" or "cooking" or "silicon"')  Từ khoá FREETEXT Từ khoá FREETEXT tìm kiếm các cột có chứa giá trị đồng nghĩa hoặc gần nghĩa với các từ được cung cấp trong điều kiện tìm kiếm. Khi sử dụng FREETEXT, chuỗi tìm kiếm được chia thành một số các nhãn tìm kiếm và sau đó tiến hành tìm. Chuỗi tìm kiếm có thể là một tập hợp các từ hoặc các cụm từ, hoặc thậm chí một câu đầy đủ. Ví dụ, để tìm kiếm các tiêu đề chứa các từ “computer”, “cooking” và “silicon” trong bảng titles, câu truy vấn là: SELECT title FROM titles WHERE FREETEXT(title, 'computer cooking and silicon')

129

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Tóm tắt  Chỉ mục giúp việc nhận dữ liệu trả về nhanh hơn.  Câu lệnh CREATE INDEX được sử dụng để tạo chỉ mục trên bảng chỉ định.  FILLFACTOR nhận một giá trị từ 0 đến 100, nó xác định phần trăm của trang chỉ mục để trống.  Có hai kiểu chỉ mục: clustered và non-clustered.  Một chỉ mục clustered thiết lập thứ tự lưu trữ của dữ liệu trong một bảng. Một bảng chỉ có thể có một chỉ mục clustered bởi vì chỉ mục clustered xác định thứ tự lưu trữ vật lý của dữ liệu.

 Một chỉ mục non-clustered xác định thứ tự lưu trữ logic của một bảng. Vì vậy, một bảng có thể có nhiều chỉ mục non-clustered (lên tới 249).  Chỉ mục clustered và non-clustered có thể được tạo với các đặc trưng duy nhất hoặc phức hợp.  Các chỉ mục duy nhất không được phép trùng giá trị trong cột chỉ mục.  Các chỉ mục phức hợp cho phép hai hoặc nhiều cột được sử dụng khi tạo chỉ mục.  Thủ tục lưu hệ thống sp_helpindex báo cáo các thông tin về các chỉ mục trong một bảng.  Gợi ý tối ưu bắt buộc tiện ích tối ưu hoá truy vấn chọn một chỉ mục riêng biệt cho một câu truy vấn.  Lệnh DROP INDEX xoá một chỉ mục.

 Một chỉ mục toàn văn theo sát các từ có ý nghĩa trong một bảng. Chỉ mục này sử dụng cho việc tìm kiếm có chứa các từ chỉ mục và các tìm kiếm nâng cao, chẳng hạn như tìm kiếm các cụm từ.  Một danh mục toàn văn là một thư mục mà tất cả các chỉ mục toàn văn của cho một cơ sở dữ liệu được đặt ở đó.  Từ khoá CONTAINS tìm kiếm các cột chứa các kiểu dữ liệu kí tự phù hợp với các từ đơn và các cụm từ, các từ gần giống một từ khác, và những sự phù hợp chính xác.

 Từ khoá FREETEXT tìm kiếm các cột có chứa giá trị đồng nghĩa hoặc gần nghĩa với các từ được cung cấp trong điều kiện tìm kiếm.

Chỉ mục và chỉ mục toàn văn

130

Kiểm tra tiến độ 1. Kiểu chỉ mục nào dưới đây luôn sắp xếp các hàng dữ liệu? a. Clustered b. Non-Clustered c. Phức hợp d. Duy nhất 2. Có bao nhiêu chỉ mục non-clustered được phép trong một bảng? a. 1 b. 249 c. 250 d. Không giới hạn. 3. Một chỉ mục phức hợp có thể bao gồm nhiều nhất bao nhiêu cột? a. 2 b. 4 c. 8 d. 16 4. Các chỉ mục toàn văn a. Yêu cầu các cột chứa các giá trị khoá duy nhất b. Không được sửa đổi khi dữ liệu trong bảng được xử lý c. Phải được cập nhật bằng tay d. Đối với các cơ sở dữ liệu khác nhau, mặc định, đặt ở các danh mục toàn văn khác nhau.

131

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Thực hành Viết lệnh Transact-SQL cho các bài tập dưới đây trên cơ sở dữ liệu pubs: 1.

Tạo một chỉ mục non-clustered tên là nclindx_titlepub cho bảng titles trên các cột title_id và pub_id. Đảm bảo rằng các trang chỉ mục để trống 20 phần trăm và bất cứ chỉ mục nào đã tồn tại mà có trùng tên phải được xoá.

2.

Cột notes trong bảng titles sẵn sàng cho việc đánh chỉ mục toàn văn. Tìm trường title mà có chứa các từ “technology” và “bestseller”.

Chỉ mục và chỉ mục toàn văn

132

Trang này chủ ý được bỏ trống

133

Thiết kế cơ sở dữ liệu và thực hành với SQL Server