CFSDN nhấn mạnh vào việc tạo ra giá trị thông qua mã nguồn mở. Chúng tôi cam kết xây dựng một nền tảng chia sẻ tài nguyên để mọi người làm CNTT có thể tìm thấy thế giới tuyệt vời của riêng mình tại đây.

Bài đăng trên blog CFSDN SQL Optimization này được tác giả thu thập và biên soạn. Nếu bạn quan tâm đến bài viết này, vui lòng thích nó.

1. Hiểu cấu trúc chỉ mục theo cách đơn giản và dễ hiểu.

Trên thực tế, bạn có thể coi chỉ mục là một loại thư mục đặc biệt. SQL SERVER của Microsoft cung cấp hai loại chỉ mục: chỉ mục cụm (còn gọi là chỉ mục cụm, clustered index) và chỉ mục không cụm (còn gọi là chỉ mục không cụm, nonclustered index). Dưới đây, chúng tôi đưa ra một ví dụ để minh họa sự khác biệt giữa chỉ mục cụm và chỉ mục không cụm:

Trên thực tế, văn bản chính của từ điển tiếng Trung của chúng tôi cũng là một chỉ mục cụm. Ví dụ, nếu chúng ta muốn tra cứu chữ "安", chúng ta sẽ tự nhiên mở vài trang đầu tiên của từ điển, vì bính âm của "安" là "an", và từ điển chữ Hán được sắp xếp theo bính âm bắt đầu bằng chữ cái tiếng Anh "a" và kết thúc bằng chữ "z", vì vậy chữ "安" tự nhiên sẽ được đặt ở đầu từ điển. Nếu bạn đã tìm kiếm qua tất cả các phần bắt đầu bằng "a" mà vẫn không tìm thấy từ đó, điều đó có nghĩa là từ đó không có trong từ điển của bạn; tương tự, nếu bạn đang tìm từ "张", bạn cũng sẽ phải chuyển sang phần cuối cùng trong từ điển vì bính âm của "张" là "zhang". Nói cách khác, bản thân nội dung của từ điển là một thư mục và bạn không cần phải tra cứu các thư mục khác để tìm những gì bạn cần.

Chúng tôi gọi loại nội dung văn bản này, là một thư mục được sắp xếp theo các quy tắc nhất định, là "chỉ mục cụm".

Nếu bạn biết một từ nào đó, bạn có thể nhanh chóng tra cứu từ đó trong chế độ Tự động. Tuy nhiên, bạn cũng có thể gặp phải những từ mà bạn không nhận ra và không biết cách phát âm. Trong trường hợp này, bạn không thể tìm thấy từ bạn muốn tìm kiếm bằng phương pháp vừa rồi. Thay vào đó, bạn cần tìm từ bạn muốn tìm kiếm dựa trên "gốc" và sau đó trực tiếp chuyển đến một trang dựa trên số trang sau từ để tìm từ bạn muốn tìm kiếm. Tuy nhiên, thứ tự các ký tự bạn tìm thấy bằng cách kết hợp "Danh mục bộ thủ" và "Mục lục ký tự" không phải là phương pháp sắp xếp thực tế của văn bản. Ví dụ, nếu bạn tra cứu ký tự "张", chúng ta có thể thấy rằng trong mục lục ký tự sau khi tra cứu bộ thủ, số trang của "张" là trang 672. Phía trên "张" trong mục lục ký tự là ký tự "驰", nhưng số trang là trang 63. Phía dưới "张" là ký tự "弩", và số trang là trang 390. Rõ ràng là các ký tự này không thực sự nằm ở phía trên và phía dưới ký tự "张". Ba ký tự liên tiếp "驰、张、弩" mà bạn thấy hiện tại thực ra là thứ tự của chúng trong chỉ mục không phân cụm, tức là ánh xạ các ký tự trong văn bản từ điển trong chỉ mục không phân cụm. Chúng ta có thể sử dụng phương pháp này để tìm từ bạn cần, nhưng nó đòi hỏi hai bước: đầu tiên là tìm kết quả trong thư mục, sau đó chuyển đến trang bạn cần.

Chúng tôi gọi phương pháp sắp xếp này khi thư mục chỉ là thư mục và văn bản chỉ là văn bản là "chỉ mục không nhóm".

Qua các ví dụ trên, chúng ta có thể hiểu được "clustered index" và "non-clustered index" là gì.

Mở rộng ra, chúng ta có thể dễ dàng hiểu rằng mỗi bảng chỉ có thể có một chỉ mục cụm, vì thư mục chỉ có thể được sắp xếp theo một cách.

(ii) Khi nào sử dụng chỉ mục cụm hoặc chỉ mục không cụm.

Bảng sau đây tóm tắt thời điểm sử dụng chỉ mục cụm hoặc không cụm (điều này rất quan trọng).

Mô tả hành động.	Sử dụng chỉ mục cụm.	Sử dụng chỉ mục không phân cụm.
Các cột thường được sắp xếp theo nhóm.	trả lời.	trả lời.
Trả về dữ liệu trong một phạm vi.	trả lời.	KHÔNG.
Một hoặc rất ít giá trị riêng biệt.	KHÔNG.	KHÔNG.
Số lượng nhỏ các giá trị riêng biệt.	trả lời.	KHÔNG.
Số lượng lớn các giá trị riêng biệt.	KHÔNG.	trả lời.
Các cột được cập nhật thường xuyên.	KHÔNG.	trả lời.
Cột khóa ngoại.	trả lời.	trả lời.
Cột khóa chính.	trả lời.	trả lời.
Các cột chỉ mục thường xuyên được sửa đổi.	KHÔNG.	trả lời.

Trên thực tế, chúng ta có thể hiểu bảng trên thông qua các ví dụ trước về định nghĩa chỉ mục cụm và chỉ mục không cụm. Ví dụ: trả về một mục dữ liệu trong một phạm vi nhất định. Ví dụ, nếu một trong các bảng của bạn có một cột thời gian và bạn tình cờ tạo một chỉ mục cụm trên cột này, khi bạn truy vấn tất cả dữ liệu trong khoảng thời gian từ ngày 1 tháng 1 năm 2004 đến ngày 1 tháng 10 năm 2004, tốc độ sẽ rất nhanh, vì văn bản trong từ điển của bạn được sắp xếp theo ngày và chỉ mục cụm chỉ cần tìm dữ liệu bắt đầu và kết thúc của tất cả dữ liệu cần truy xuất; không giống như chỉ mục không cụm, trước tiên bạn phải tìm số trang tương ứng với từng dữ liệu trong thư mục, sau đó tìm nội dung cụ thể theo số trang.

(3) Dựa vào tình hình thực tế, hãy thảo luận những hiểu lầm trong việc sử dụng chỉ số.

Mục đích của lý thuyết là ứng dụng. Mặc dù chúng tôi vừa liệt kê thời điểm sử dụng chỉ mục cụm hoặc chỉ mục không cụm, nhưng trên thực tế, các quy tắc trên dễ bị bỏ qua hoặc không thể phân tích toàn diện dựa trên các điều kiện thực tế. Tiếp theo, chúng ta sẽ thảo luận về những hiểu lầm trong việc sử dụng chỉ số dựa trên các vấn đề thực tế gặp phải trong thực tế, để mọi người có thể nắm vững phương pháp lập chỉ số.

1. Khóa chính là chỉ mục cụm.

Tôi nghĩ ý tưởng này cực kỳ sai lầm và lãng phí các chỉ mục cụm. Mặc dù SQL Server tạo một chỉ mục cụm trên khóa chính theo mặc định.

Thông thường, chúng ta sẽ tạo một cột ID trong mỗi bảng để phân biệt từng phần dữ liệu và cột ID này sẽ tự động tăng lên, kích thước bước thường là 1. Đây là trường hợp của cột Gid trong ví dụ tự động hóa văn phòng của chúng ta. Tại thời điểm này, nếu chúng ta đặt cột này làm khóa chính, SQL SERVER sẽ mặc định cột này là chỉ mục cụm. Ưu điểm của cách này là dữ liệu của bạn có thể được sắp xếp theo ID trong cơ sở dữ liệu, nhưng tôi nghĩ cách này không có nhiều ý nghĩa.

Rõ ràng, lợi thế của chỉ mục cụm là rất rõ ràng và quy tắc chỉ có thể có một chỉ mục cụm trong mỗi bảng làm cho chỉ mục cụm thậm chí còn có giá trị hơn.

Từ định nghĩa về chỉ mục cụm mà chúng ta đã thảo luận trước đó, chúng ta có thể thấy rằng lợi thế lớn nhất của việc sử dụng chỉ mục cụm là nó có thể nhanh chóng thu hẹp phạm vi truy vấn theo yêu cầu truy vấn và tránh quét toàn bộ bảng. Trong các ứng dụng thực tế, vì số ID được tạo tự động nên chúng ta không biết số ID của từng bản ghi, do đó khó có thể sử dụng số ID để truy vấn trong thực tế. Điều này làm lãng phí tài nguyên khi sử dụng số ID làm chỉ mục nhóm. Thứ hai, việc sử dụng trường có nhiều số ID khác nhau làm chỉ mục cụm không tuân theo quy tắc "không nên tạo chỉ mục tổng hợp khi có nhiều giá trị khác nhau"; tất nhiên, tình huống này chỉ có tác động tiêu cực khi người dùng thường xuyên sửa đổi nội dung bản ghi, đặc biệt là khi lập chỉ mục các mục, nhưng không ảnh hưởng đến tốc độ truy vấn.

Trong hệ thống tự động hóa văn phòng, cho dù đó là các tài liệu hiển thị trên trang chủ của hệ thống cần người dùng ký, các cuộc họp hay người dùng tìm kiếm tệp tin thì các trường không thể tách rời khỏi "ngày" và "tên người dùng" của người dùng chính là các trường truy vấn dữ liệu.

Thông thường, trang chủ của ứng dụng tự động hóa văn phòng sẽ hiển thị các tài liệu hoặc cuộc họp mà mỗi người dùng chưa ký. Mặc dù câu lệnh where của chúng ta chỉ có thể giới hạn tình huống mà người dùng hiện tại chưa ký, nhưng nếu hệ thống của bạn đã được thiết lập trong một thời gian dài và lượng dữ liệu lớn, thì việc thực hiện quét toàn bộ bảng mỗi lần mỗi người dùng mở trang chủ là vô nghĩa. Hầu hết người dùng đã duyệt các tệp từ một tháng trước và điều này sẽ chỉ làm tăng thêm gánh nặng cho cơ sở dữ liệu. Trên thực tế, chúng ta có thể cho phép cơ sở dữ liệu chỉ truy vấn các tệp mà người dùng chưa xem trong ba tháng qua khi người dùng mở trang chủ hệ thống. Chúng ta có thể sử dụng trường "ngày" để giới hạn việc quét bảng và tăng tốc độ truy vấn. Nếu hệ thống tự động hóa văn phòng của bạn đã được thiết lập trong 2 năm, thì về mặt lý thuyết, tốc độ hiển thị trang chủ của bạn sẽ nhanh hơn trước gấp 8 lần, thậm chí còn nhanh hơn.

Lý do tôi đề cập đến từ "về mặt lý thuyết" ở đây là nếu chỉ mục cụm của bạn vẫn được xây dựng một cách mù quáng trên ID khóa chính, thì tốc độ truy vấn của bạn sẽ không cao, ngay cả khi bạn xây dựng chỉ mục trên trường "Ngày" (chỉ mục không cụm). Tiếp theo, chúng ta hãy xem xét hiệu suất tốc độ của nhiều truy vấn khác nhau với 10 triệu bản ghi (250.000 bản ghi trong vòng 3 tháng):

(1) Tạo chỉ mục cụm trên khóa chính và không phân chia khoảng thời gian:

Chọn gid,fariqi,neibuyonghu,title từ tgongwen 。

Thời gian thực hiện: 128470 mili giây (tức là: 128 giây).

(2) Tạo một chỉ mục cụm trên khóa chính và một chỉ mục không cụm trên fariq:

chọn gid,fariqi,neibuyonghu,title từ Tgongwen 。

nơi fariqi> dateadd(day,-90,getdate()) .

Thời gian thực hiện: 53763 mili giây (54 giây).

(3) Tạo một chỉ mục cụm trên cột ngày (fariqi):

chọn gid,fariqi,neibuyonghu,title từ Tgongwen 。

nơi fariqi> dateadd(day,-90,getdate()) .

Thời gian thực hiện: 2423 mili giây (2 giây).

Mặc dù mỗi câu lệnh trích xuất 250.000 mục dữ liệu, nhưng sự khác biệt trong nhiều tình huống là rất lớn, đặc biệt là khi chỉ mục cụm được thiết lập trên cột ngày. Trên thực tế, nếu cơ sở dữ liệu của bạn thực sự có dung lượng là 10 triệu và khóa chính được thiết lập trên cột ID, như trong trường hợp thứ nhất và thứ hai ở trên, thì trang web sẽ hết thời gian chờ và không thể hiển thị được. Đây cũng là lý do quan trọng nhất khiến tôi từ bỏ cột ID như một chỉ mục cụm.

Phương pháp để đạt được tốc độ trên là: thêm lệnh declare @d datetime trước mỗi câu lệnh select.

đặt @d=getdate() .

Và thêm vào sau câu lệnh select:

select [thời gian thực hiện câu lệnh (mili giây)]=datediff(ms,@d,getdate()) .

2. Chỉ cần tạo một chỉ mục có thể cải thiện đáng kể tốc độ truy vấn.

Trên thực tế, chúng ta có thể thấy rằng trong ví dụ trên, câu lệnh thứ hai và thứ ba hoàn toàn giống nhau và các trường được lập chỉ mục cũng giống nhau; điểm khác biệt duy nhất là câu lệnh thứ hai tạo ra một chỉ mục không tổng hợp trên trường fariqi và câu lệnh thứ hai tạo ra một chỉ mục tổng hợp trên trường này, nhưng tốc độ truy vấn lại rất khác nhau. Do đó, không thể cải thiện tốc độ truy vấn chỉ bằng cách tạo chỉ mục trên bất kỳ trường nào.

Từ câu lệnh tạo bảng, chúng ta có thể thấy rằng trường fariqi trong bảng này với 10 triệu dữ liệu có 5003 bản ghi khác nhau. Sẽ là một ý tưởng hay nếu tạo một chỉ mục cụm trên trường này. Trên thực tế, chúng tôi xuất bản nhiều tệp mỗi ngày và ngày xuất bản của các tệp này là giống nhau, đáp ứng đầy đủ các yêu cầu để thiết lập chỉ mục cụm: "phần lớn các tệp không thể giống nhau, cũng không chỉ một số ít tệp có thể giống nhau". Từ đó, có thể thấy rằng việc thiết lập một chỉ mục tổng hợp "phù hợp" là rất quan trọng để cải thiện tốc độ truy vấn.

3. Thêm tất cả các trường cần cải thiện tốc độ truy vấn vào chỉ mục cụm để cải thiện tốc độ truy vấn.

Như đã đề cập ở trên, các trường không thể tách biệt khi thực hiện truy vấn dữ liệu là "ngày" và "tên người dùng" của người dùng. Vì hai trường này rất quan trọng nên chúng ta có thể kết hợp chúng để tạo thành một chỉ mục hợp thành.

Nhiều người nghĩ rằng chỉ cần thêm bất kỳ trường nào vào chỉ mục cụm, tốc độ truy vấn có thể được cải thiện. Một số người cũng bối rối: nếu các trường chỉ mục cụm tổng hợp được truy vấn riêng biệt, tốc độ truy vấn có bị chậm lại không? Với câu hỏi này, chúng ta hãy xem xét các tốc độ truy vấn sau (tất cả các tập kết quả đều là 250.000 dữ liệu): (cột ngày fariqi được sắp xếp đầu tiên trong cột bắt đầu của chỉ mục cụm tổng hợp và tên người dùng neibuyonghu được sắp xếp trong cột cuối cùng).

(1) chọn gid, fariqi, neibuyonghu, tiêu đề từ Tgongwen nơi fariqi>'2004-5-5' .

Tốc độ truy vấn: 2513 mili giây.

(2) chọn gid, fariqi, neibuyonghu, tiêu đề từ Tgongwen trong đó fariqi>'2004-5-5' và neibuyonghu='Office' .

Tốc độ truy vấn: 2516 mili giây.

(3)chọn gid,fariqi,neibuyonghu,title từ Tgongwen nơi neibuyonghu='Office' .

Tốc độ truy vấn: 60280 mili giây.

Từ các thí nghiệm trên, chúng ta có thể thấy rằng tốc độ truy vấn khi chỉ sử dụng cột bắt đầu của chỉ mục cụm làm điều kiện truy vấn gần giống với tốc độ sử dụng tất cả các cột của chỉ mục cụm tổng hợp cùng một lúc và thậm chí còn nhanh hơn một chút so với khi sử dụng tất cả các cột chỉ mục cụm (khi số lượng tập kết quả truy vấn là như nhau); và nếu chỉ sử dụng các cột không phải cột bắt đầu của chỉ mục cụm tổng hợp làm điều kiện truy vấn, thì chỉ mục này sẽ không đóng bất kỳ vai trò nào. Tất nhiên, tốc độ truy vấn của câu lệnh 1 và 2 là như nhau vì số lượng mục được truy vấn là như nhau. Nếu tất cả các cột của chỉ mục tổng hợp được sử dụng và có ít kết quả truy vấn, điều này sẽ hình thành "phạm vi chỉ mục" và hiệu suất có thể được tối ưu hóa. Đồng thời, hãy nhớ: bất kể bạn có thường xuyên sử dụng các cột khác của chỉ mục cụm hay không, cột dẫn đầu của chỉ mục cụm phải là cột được sử dụng thường xuyên nhất.

(IV) Tóm tắt kinh nghiệm sử dụng các chỉ mục không có trong các sách khác.

1. Sử dụng chỉ mục cụm nhanh hơn sử dụng khóa chính không phải là chỉ mục cụm.

Sau đây là một số câu lệnh ví dụ: (cả hai đều trích xuất 250.000 dữ liệu).

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-9-16' .

Thời gian sử dụng: 3326 mili giây.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó gid<=250000.

Thời gian sử dụng: 4470 mili giây.

Ở đây, việc sử dụng chỉ mục cụm nhanh hơn gần 1/4 so với việc sử dụng khóa chính không phải là chỉ mục cụm.

2. Sử dụng chỉ mục cụm nhanh hơn sử dụng khóa chính chung theo thứ tự, đặc biệt là khi lượng dữ liệu nhỏ.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen theo thứ tự của fariqi 。

Thời gian thực hiện: 12936.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen theo thứ tự gid.

Thời gian thực hiện: 18843.

Ở đây, sử dụng chỉ mục cụm nhanh hơn 3/10 so với sử dụng khóa chính thông thường theo thứ tự. Trên thực tế, nếu lượng dữ liệu nhỏ, việc sử dụng chỉ mục cụm làm cột sắp xếp sẽ nhanh hơn đáng kể so với việc sử dụng chỉ mục không cụm; nếu lượng dữ liệu lớn, chẳng hạn như hơn 100.000, thì sự khác biệt về tốc độ giữa hai cách này là không rõ ràng.

3. Khi sử dụng khoảng thời gian trong chỉ mục cụm, thời gian tìm kiếm sẽ được giảm theo tỷ lệ phần trăm dữ liệu trong toàn bộ bảng dữ liệu, bất kể có bao nhiêu chỉ mục cụm được sử dụng.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen nơi fariqi>'2004-1-1' .

Thời gian thực hiện: 6343 mili giây (trích xuất 1 triệu bản ghi).

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen nơi fariqi>'2004-6-6' .

Thời gian thực hiện: 3170 mili giây (trích xuất 500.000 bản ghi).

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-9-16' .

Thời gian thực hiện: 3326 mili giây (kết quả chính xác giống như câu trước. Nếu số lượng dữ liệu thu thập được là như nhau thì việc sử dụng dấu lớn hơn và dấu bằng cũng giống nhau).

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi>'2004-1-1' và fariqi<'2004-6-6'.

Thời gian thực hiện: 3280 mili giây.

4. Cột ngày sẽ không làm chậm tốc độ truy vấn do phải nhập phút và giây.

Trong ví dụ sau, có 1 triệu bản ghi dữ liệu. Sau ngày 1 tháng 1 năm 2004, có 500.000 bản ghi chỉ có hai ngày khác nhau, chính xác đến từng ngày. Trước đó, có 500.000 bản ghi với 5.000 ngày khác nhau, chính xác đến từng giây.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen nơi fariqi>'2004-1-1' theo thứ tự của fariqi.

Thời gian thực hiện: 6390 mili giây.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen nơi fariqi<'2004-1-1' theo thứ tự của fariqi.

Thời gian thực hiện: 6453 mili giây.

(V) Những vấn đề khác cần lưu ý

“Nước có thể nâng thuyền, nhưng cũng có thể làm thuyền lật úp.” Điều tương tự cũng đúng với việc lập chỉ mục. Chỉ mục giúp cải thiện hiệu suất truy xuất, nhưng chỉ mục quá nhiều hoặc không phù hợp cũng có thể dẫn đến hệ thống kém hiệu quả. Bởi vì mỗi lần người dùng thêm chỉ mục vào bảng, cơ sở dữ liệu phải thực hiện nhiều công việc hơn. Quá nhiều chỉ mục thậm chí có thể dẫn đến tình trạng phân mảnh chỉ mục.

Do đó, chúng ta cần thiết lập một hệ thống chỉ mục "phù hợp", đặc biệt là việc tạo các chỉ mục tổng hợp, cần được tinh chỉnh để cho phép cơ sở dữ liệu của bạn đạt được hiệu suất cao.

Tất nhiên, trên thực tế, với tư cách là một quản trị viên cơ sở dữ liệu tận tâm, bạn cần thử nghiệm nhiều giải pháp hơn để tìm ra giải pháp nào hiệu quả nhất.

2. Cải thiện các câu lệnh SQL.

Nhiều người không biết các câu lệnh SQL được thực thi như thế nào trong SQL SERVER và họ lo lắng rằng các câu lệnh SQL họ viết sẽ bị SQL SERVER hiểu sai. Ví dụ:

chọn * từ bảng1 nơi tên = 'zhangsan' và tID > 10000.

và thực hiện:

chọn * từ bảng1 nơi tID > 10000 và tên = 'zhangsan'.

Một số người không biết hiệu suất thực hiện của hai câu lệnh trên có giống nhau không, vì nếu chỉ nhìn vào thứ tự của các câu lệnh thì hai câu lệnh này thực sự khác nhau. Nếu tID là một chỉ mục tổng hợp, thì câu lệnh sau chỉ cần tìm kiếm từ các bản ghi sau 10.000 trong bảng; trong khi câu lệnh trước phải tìm kiếm toàn bộ bảng trước để xem có bao nhiêu tên = 'zhangsan', sau đó đưa ra kết quả truy vấn dựa trên điều kiện hạn chế tID>10.000.

Trên thực tế, những lo lắng như vậy là không cần thiết. SQL Server có "trình tối ưu hóa phân tích truy vấn" có thể tính toán các điều kiện tìm kiếm trong mệnh đề where và xác định chỉ mục nào có thể thu hẹp không gian tìm kiếm để quét bảng, tức là có thể đạt được tối ưu hóa tự động.

Mặc dù trình tối ưu hóa truy vấn có thể tự động tối ưu hóa các truy vấn dựa trên mệnh đề where, nhưng vẫn cần phải hiểu nguyên lý hoạt động của "trình tối ưu hóa truy vấn". Nếu không, đôi khi trình tối ưu hóa truy vấn sẽ không thực hiện các truy vấn nhanh theo ý định của bạn.

Trong giai đoạn phân tích truy vấn, trình tối ưu hóa truy vấn sẽ xem xét từng giai đoạn của truy vấn và quyết định xem có nên giới hạn lượng dữ liệu cần quét hay không. Một giai đoạn được gọi là có thể tối ưu hóa nếu nó có thể được sử dụng làm tham số quét (SARG) và có thể sử dụng chỉ mục để nhanh chóng có được dữ liệu cần thiết.

Định nghĩa của SARG: Một hoạt động được sử dụng để hạn chế tìm kiếm vì nó thường đề cập đến một kết quả khớp cụ thể, một kết quả khớp trong một phạm vi giá trị hoặc kết nối AND của hai hoặc nhiều điều kiện. Định dạng như sau:

Toán tử tên cột .

hoặc.

Tên cột toán tử .

Tên cột có thể xuất hiện ở một bên của toán tử và hằng số hoặc biến có thể xuất hiện ở bên kia. giống:

Tên = 'Trương Tam'.

Giá>5000.

5000<>

Tên='张三' và giá>5000.

Nếu một biểu thức không thỏa mãn dạng SARG, nó không thể giới hạn phạm vi tìm kiếm, nghĩa là SQL SERVER phải xác định đối với mỗi hàng liệu nó có thỏa mãn tất cả các điều kiện trong mệnh đề WHERE hay không. Vì vậy, chỉ mục sẽ vô dụng đối với những biểu thức không thỏa mãn dạng SARG.

Sau khi giới thiệu về SARG, chúng ta hãy tóm tắt lại kinh nghiệm sử dụng SARG và những kết luận khác nhau từ một số tài liệu:

1. Câu lệnh Like có thuộc về SARG hay không tùy thuộc vào loại ký tự đại diện được sử dụng.

Ví dụ: tên như '张%', tên này thuộc về SARG.

Tuy nhiên, tên như '%张' không thuộc về SARG.

Nguyên nhân là do ký tự đại diện % trong chuỗi khiến cho chỉ mục không sử dụng được.

2. hoặc sẽ quét toàn bộ bảng.

Tên='张三' và giá>5000 là SARG, nhưng Tên='张三' hoặc giá>5000 không phải là SARG. Sử dụng hoặc sẽ quét toàn bộ bảng.

3. Các câu lệnh không thỏa mãn dạng SARG do các toán tử và hàm không phải toán tử gây ra.

Các câu lệnh điển hình nhất không thỏa mãn dạng SARG là các câu lệnh bao gồm các toán tử không phải toán tử, chẳng hạn như: NOT, !=, <>, !<, !>, NOT EXISTS, NOT IN, NOT LIKE, v.v., ngoài các hàm. Sau đây là một số ví dụ không đáp ứng được mẫu SARG:

ABS(giá)<5000.

Tên như '%三' .

Một số cách diễn đạt như sau:

Ở đâu giá*2>5000 .

SQL Server cũng sẽ coi đây là SARG và sẽ chuyển đổi công thức này thành:

Ở đâu giá > 2500/2 .

Tuy nhiên, chúng tôi không khuyến khích sử dụng phương pháp này vì đôi khi SQL Server không thể đảm bảo rằng việc chuyển đổi hoàn toàn tương đương với biểu thức gốc.

4. Chức năng của IN tương đương với OR.

Tuyên bố:

Chọn * từ bảng1 nơi tid trong (2,3).

Và.

Chọn * từ bảng 1 trong đó tid=2 hoặc tid=3.

Giống nhau, và sẽ gây ra việc quét toàn bộ bảng. Nếu có một chỉ mục trên tid, chỉ mục của nó cũng sẽ trở nên không hợp lệ.

5. KHÔNG sử dụng càng ít càng tốt.

6. Hiệu suất thực hiện của exists và in là như nhau.

Nhiều tài liệu cho thấy exists hiệu quả hơn in, và not exists nên được sử dụng thay cho not in bất cứ khi nào có thể. Nhưng thực tế, tôi đã thử và thấy rằng hiệu quả thực hiện của cả hai là như nhau bất kể chúng có theo not hay not trước đó. Vì nó liên quan đến các truy vấn phụ, chúng tôi sử dụng cơ sở dữ liệu pubs đi kèm với SQL SERVER cho thử nghiệm này. Trước khi chạy, chúng ta có thể bật trạng thái thống kê I/O của SQL SERVER.

(1)chọn tiêu đề, giá từ tiêu đề có title_id trong (chọn title_id từ doanh số bán hàng có số lượng> 30).

Kết quả thực hiện của câu này là:

Bảng 'doanh số'. Số lần quét là 18, số lần đọc logic là 56, số lần đọc vật lý là 0, số lần đọc trước là 0.

Bảng 'tiêu đề'. Số lần quét là 1, số lần đọc logic là 2, số lần đọc vật lý là 0, số lần đọc trước là 0.

(2)chọn tiêu đề, giá từ tiêu đề nếu có (chọn * từ doanh số bán hàng khi sales.title_id=titles.title_id và số lượng>30).

Kết quả thực hiện của câu thứ hai là:

Bảng 'doanh số'. Số lần quét là 18, số lần đọc logic là 56, số lần đọc vật lý là 0, số lần đọc trước là 0.

Bảng 'tiêu đề'. Số lần quét là 1, số lần đọc logic là 2, số lần đọc vật lý là 0, số lần đọc trước là 0.

Từ đó ta thấy hiệu quả thực hiện của việc sử dụng exists và sử dụng in là như nhau.

7. Hiệu quả thực thi khi sử dụng hàm charindex() cũng giống như khi sử dụng hàm LIKE có ký tự đại diện % ở phía trước.

Trước đó, chúng ta đã thảo luận rằng nếu thêm ký tự đại diện % trước LIKE, nó sẽ gây ra việc quét toàn bộ bảng, do đó hiệu quả thực hiện của nó thấp. Nhưng một số thông tin cho biết sử dụng hàm charindex() thay vì LIKE sẽ cải thiện tốc độ đáng kể. Sau khi thử nghiệm, tôi thấy rằng câu lệnh này cũng sai:

chọn gid,title,fariqi,reader từ tgongwen nơi charindex('刑探支队',reader)>0 và fariqi>'2004-5-5' .

Thời gian thực hiện: 7 giây, ngoài ra: số lần quét là 4, đọc logic 7155 lần, đọc vật lý 0 lần, đọc trước 0 lần.

chọn gid,title,fariqi,reader từ tgongwen nơi reader như '%' + '刑探支队' + '%' và fariqi>'2004-5-5'.

Thời gian thực hiện: 7 giây, ngoài ra: số lần quét là 4, đọc logic 7155 lần, đọc vật lý 0 lần, đọc trước 0 lần.

8. Liên hợp không hoàn toàn hiệu quả hơn hoặc.

Chúng ta đã thảo luận rằng sử dụng or trong mệnh đề where sẽ gây ra việc quét toàn bộ bảng. Nhìn chung, thông tin tôi thấy khuyến nghị sử dụng union thay vì or ở đây. Thực ra, tuyên bố này có thể áp dụng cho hầu hết các trường hợp.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-9-16' hoặc gid>9990000 .

Thời gian: 68 giây. Số lần quét là 1, đọc logic 404008 lần, đọc vật lý 283 lần, đọc trước 392163 lần.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-9-16' .

công đoàn .

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen nơi gid>9990000.

Thời gian: 9 giây. Số lần quét là 8, số lần đọc logic là 67489, số lần đọc vật lý là 216, số lần đọc trước là 7499.

Có vẻ như sử dụng union thường hiệu quả hơn nhiều so với sử dụng or.

Nhưng sau khi thử nghiệm, tôi thấy rằng nếu các cột truy vấn ở cả hai bên của or giống nhau thì tốc độ thực thi khi sử dụng union chậm hơn nhiều so với khi sử dụng or, mặc dù union quét chỉ mục và or quét toàn bộ bảng.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-9-16' hoặc fariqi='2004-2-5' .

Thời gian thực hiện: 6423 mili giây. Số lần quét là 2, số lần đọc logic là 14726, số lần đọc vật lý là 1, số lần đọc trước là 7176.

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-9-16' .

công đoàn .

chọn gid,fariqi,neibuyonghu,reader,title từ Tgongwen trong đó fariqi='2004-2-5' .

Thời gian thực hiện: 11640 mili giây. Số lần quét 8, số lần đọc logic 14806, số lần đọc vật lý 108, số lần đọc trước 1144.

9. Việc trích xuất dữ liệu thực địa phải tuân theo nguyên tắc "trích xuất vừa đủ" và tránh "chọn *".

Chúng ta hãy làm một bài kiểm tra:

chọn 10000 gid,fariqi,reader,title hàng đầu từ tgongwen theo thứ tự gid desc.

Thời gian thực hiện: 4673 mili giây.

chọn 10000 gid,fariqi,title hàng đầu từ tgongwen theo thứ tự gid desc.

Thời gian thực hiện: 1376 mili giây.

chọn 10000 gid,fariqi hàng đầu từ tgongwen theo thứ tự gid desc.

Thời lượng: 80 mili giây.

Từ đó có thể thấy rằng với mỗi trường chúng ta trích xuất, tốc độ trích xuất dữ liệu sẽ được cải thiện tương ứng. Tốc độ cải thiện phụ thuộc vào kích thước của các trường bạn loại bỏ.

10. count(*) không chậm hơn count(field).

Một số thông tin cho biết: Sử dụng * sẽ đếm tất cả các cột, rõ ràng là kém hiệu quả hơn so với việc sử dụng tên cột của thế giới. Tuyên bố này thực ra là không có căn cứ. Chúng ta hãy xem xét:

chọn count(*) từ Tgongwen.

Thời lượng: 1500 mili giây.

chọn count(gid) từ Tgongwen 。

Thời gian thực hiện: 1483 mili giây.

chọn count(fariqi) từ Tgongwen 。

Thời gian thực hiện: 3140 mili giây.

chọn count(title) từ Tgongwen 。

Thời gian thực hiện: 52050 mili giây.

Từ những điều trên, ta có thể thấy tốc độ sử dụng count(*) và count(primary key) là tương đương nhau, nhưng count(*) nhanh hơn bất kỳ trường nào khác ngoại trừ trường khóa chính và trường càng dài thì tốc độ tổng hợp càng chậm. Tôi nghĩ nếu bạn sử dụng count(*), SQL SERVER có thể tự động tìm trường tối thiểu để tóm tắt. Tất nhiên, sẽ đơn giản hơn nếu bạn viết trực tiếp count(primary key).

11. Sắp xếp theo cột chỉ mục cụm là hiệu quả nhất.

Chúng ta hãy cùng xem xét: (gid là khóa chính, fariqi là cột chỉ mục tổng hợp).

chọn 10000 gid,fariqi,reader,title hàng đầu từ tgongwen.

Thời gian: 196 ms. Số lần quét là 1, đọc logic 289 lần, đọc vật lý 1 lần, đọc trước 1527 lần.

chọn 10000 gid,fariqi,reader,title hàng đầu từ tgongwen theo thứ tự gid tăng dần.

Thời gian thực hiện: 4720 mili giây. Số lần quét là 1, số lần đọc logic là 41956, số lần đọc vật lý là 0, số lần đọc trước là 1287.

chọn 10000 gid,fariqi,reader,title hàng đầu từ tgongwen theo thứ tự gid desc.

Thời gian thực hiện: 4736 mili giây. Số lần quét là 1, đọc logic 55350 lần, đọc vật lý 10 lần, đọc trước 775 lần.

chọn 10000 gid,fariqi,reader,title hàng đầu từ tgongwen theo thứ tự tăng dần của fariqi.

Thời gian: 173 mili giây. Số lần quét là 1, số lần đọc logic là 290, số lần đọc vật lý là 0, số lần đọc trước là 0.

chọn 10000 gid,fariqi,reader,title hàng đầu từ tgongwen theo thứ tự fariqi desc.

Thời gian: 156 mili giây. Số lần quét là 1, số lần đọc logic là 289, số lần đọc vật lý là 0, số lần đọc trước là 0.

Từ trên, ta có thể thấy rằng tốc độ chưa sắp xếp và số lượng các lần đọc logic tương đương với tốc độ của "sắp xếp theo cột chỉ mục cụm", nhưng nhanh hơn nhiều so với tốc độ truy vấn của "sắp xếp theo cột chỉ mục không cụm".

Đồng thời, khi sắp xếp theo một trường nhất định, dù theo thứ tự tăng dần hay giảm dần, tốc độ về cơ bản là như nhau.

12. TOP hiệu quả.

Trên thực tế, khi truy vấn và trích xuất các tập dữ liệu cực lớn, yếu tố lớn nhất ảnh hưởng đến thời gian phản hồi của cơ sở dữ liệu không phải là tra cứu dữ liệu mà là các hoạt động I/O vật lý. giống:

chọn 10 * hàng đầu từ ( .

chọn 10000 gid,fariqi,title hàng đầu từ tgongwen.

trong đó neibuyonghu='văn phòng'.

sắp xếp theo gid desc) như một .

sắp xếp theo gid tăng dần.

Về mặt lý thuyết, thời gian thực hiện của toàn bộ câu lệnh phải dài hơn thời gian thực hiện của các mệnh đề, nhưng thực tế thì ngược lại. Bởi vì sau khi mệnh đề được thực thi, 10.000 bản ghi được trả về, trong khi toàn bộ câu lệnh chỉ trả về 10 câu lệnh, do đó yếu tố ảnh hưởng nhiều nhất đến thời gian phản hồi của cơ sở dữ liệu là hoạt động I/O vật lý. Một trong những cách hiệu quả nhất để hạn chế các hoạt động I/O vật lý ở đây là sử dụng từ khóa TOP. Từ khóa TOP là một từ được tối ưu hóa cho hệ thống trong SQL SERVER, được sử dụng để trích xuất một vài hoặc một vài phần trăm dữ liệu hàng đầu. Qua việc tác giả áp dụng vào thực tế, tôi thấy TOP thực sự dễ sử dụng và rất hiệu quả. Nhưng từ này không được tìm thấy trong cơ sở dữ liệu lớn khác là ORACLE, thật đáng tiếc, mặc dù có thể sử dụng các phương pháp khác (như: rownumber) để giải quyết vấn đề này trong ORACLE. Trong phần thảo luận sau đây về "triển khai quy trình lưu trữ phân trang hiển thị hàng chục triệu dữ liệu", chúng ta sẽ sử dụng từ khóa TOP.

Cho đến nay, chúng ta đã thảo luận về cách truy vấn nhanh dữ liệu bạn cần từ cơ sở dữ liệu có dung lượng lớn. Tất nhiên, các phương pháp chúng tôi giới thiệu đều là phương pháp "mềm". Trong thực tế, chúng ta cũng cần xem xét các yếu tố "cứng" khác nhau, chẳng hạn như: hiệu suất mạng, hiệu suất máy chủ, hiệu suất hệ điều hành và thậm chí cả card mạng, bộ chuyển mạch, v.v.

3. Triển khai các quy trình lưu trữ hiển thị phân trang chung cho khối lượng dữ liệu nhỏ và lớn.

Để xây dựng một ứng dụng web, chức năng duyệt phân trang là rất cần thiết. Đây là một vấn đề rất phổ biến trong xử lý cơ sở dữ liệu. Phương pháp phân trang dữ liệu cổ điển là: Phương pháp phân trang tập bản ghi ADO, nghĩa là sử dụng chức năng phân trang riêng của ADO (sử dụng con trỏ) để thực hiện phân trang. Tuy nhiên, phương pháp phân trang này chỉ áp dụng được trong những tình huống có khối lượng dữ liệu nhỏ hơn vì bản thân con trỏ có nhược điểm: con trỏ được lưu trữ trong bộ nhớ, chiếm rất nhiều bộ nhớ. Khi con trỏ được tạo, các bản ghi liên quan sẽ bị khóa cho đến khi con trỏ bị hủy. Con trỏ cung cấp phương tiện để quét từng hàng một bộ sưu tập cụ thể. Nhìn chung, con trỏ được sử dụng để duyệt dữ liệu từng hàng và thực hiện các thao tác khác nhau dựa trên các điều kiện khác nhau để truy xuất dữ liệu. Đối với các vòng lặp con trỏ được xác định trong nhiều bảng hoặc các bảng lớn (bộ dữ liệu lớn), chương trình có thể dễ dàng phải chờ lâu hoặc thậm chí bị sập.

Quan trọng hơn, đối với các mô hình dữ liệu rất lớn, việc tải toàn bộ nguồn dữ liệu mỗi lần khi tìm kiếm theo trang bằng phương pháp truyền thống là rất lãng phí. Phương pháp phân trang phổ biến hiện nay là truy xuất dữ liệu theo từng phần có kích thước bằng một trang, thay vì truy xuất toàn bộ dữ liệu rồi duyệt qua hàng hiện tại.

Cách sớm nhất và tốt nhất để triển khai phương pháp trích xuất dữ liệu dựa trên kích thước trang và số trang này có lẽ là "quy trình lưu trữ của Nga". Quy trình được lưu trữ này sử dụng con trỏ, nhưng do hạn chế của con trỏ nên phương pháp này chưa được công nhận rộng rãi.

Sau đó, một người nào đó trên Internet đã sửa đổi quy trình lưu trữ này. Quy trình lưu trữ sau đây là một quy trình lưu trữ phân trang được viết kết hợp với ví dụ tự động hóa văn phòng của chúng tôi:

TẠO thủ tục phân trang1.

(@pagesize int, --page size, chẳng hạn như lưu trữ 20 bản ghi trên mỗi trang.

@pageindex int --Số trang hiện tại.

) .

BẰNG .

đặt nocount trên .

bắt đầu.

khai báo @indextable table(id int identity(1,1),nid int) --xác định biến bảng.

khai báo @PageLowerBound int --Xác định giới hạn dưới cùng của trang này.

khai báo @PageUpperBound int --Xác định giới hạn trên cùng của trang này.

đặt @PageLowerBound=(@pageindex-1)*@pagesize .

đặt @PageUpperBound=@PageLowerBound+@pagesize .

đặt rowcount @PageUpperBound .

chèn vào @indextable(nid) chọn gid từ TGongwen nơi fariqi >dateadd(day,-365,getdate()) sắp xếp theo fariqi desc。

chọn O.gid,O.mid,O.title,O.fadanwei,O.fariqi từ TGongwen O,@indextable t trong đó O.gid=t.nid .

và t.id>@PageLowerBound và t.id<=@PageUpperBound sắp xếp theo t.id.

kết thúc .

thiết lập nocount tắt.

Quy trình được lưu trữ ở trên sử dụng công nghệ mới nhất của SQL SERVER - biến bảng. Có thể nói rằng thủ tục lưu trữ này cũng là một thủ tục lưu trữ phân trang rất tuyệt vời. Tất nhiên, trong quá trình này, bạn cũng có thể viết các biến bảng vào các bảng tạm thời: CREATE TABLE #Temp. Nhưng rõ ràng là trong SQL SERVER, sử dụng bảng tạm thời không nhanh bằng sử dụng biến bảng. Vì vậy, khi tôi bắt đầu sử dụng quy trình được lưu trữ này, tôi cảm thấy nó rất tốt và tốc độ cũng tốt hơn so với ADO gốc. Nhưng sau đó, tôi tìm ra một cách tốt hơn thế này.

Tác giả đã từng thấy một bài viết ngắn trên Internet có tiêu đề "Cách trích xuất các bản ghi từ thứ n đến thứ m từ một bảng dữ liệu". Toàn văn như sau:

Lấy các bản ghi từ n đến m khỏi bảng xuất bản:

CHỌN ĐẦU M-N+1 * .

TỪ xuất bản .

Ở ĐÂU (id KHÔNG CÓ TRONG .

(CHỌN TOP n-1 id.

TỪ xuất bản)).

id là từ khóa của bảng xuất bản.

Khi đọc bài viết này, tôi thực sự cảm thấy hứng thú và nghĩ rằng ý tưởng này rất hay. Sau này, khi tôi làm việc trên một hệ thống tự động hóa văn phòng (ASP.NET + C# + SQL SERVER), tôi đột nhiên nhớ đến bài viết này. Tôi nghĩ rằng nếu câu lệnh này được sửa đổi, nó có thể là một quy trình lưu trữ phân trang rất tốt. Vì vậy, tôi đã tìm kiếm khắp Internet bài viết này. Thật bất ngờ, tôi không tìm thấy bài viết, nhưng tôi tìm thấy một quy trình lưu trữ phân trang dựa trên tuyên bố này. Quy trình lưu trữ này cũng là một quy trình lưu trữ phân trang phổ biến. Tôi hối tiếc vì đã không nhanh chóng chuyển đổi văn bản này thành một quy trình lưu trữ:

TẠO THỦ TỤC phân trang2.

( .

@SQL nVARCHAR(4000), --Câu lệnh SQL không có câu lệnh sắp xếp.

@Page int, --số trang.

@RecsPerPage int, --Số bản ghi trên mỗi trang.

@ID VARCHAR(255), --Số ID duy nhất cần được sắp xếp.

@Sort VARCHAR(255) -- Sắp xếp các trường và quy tắc.

) .

BẰNG .

KHAI BÁO @Str nVARCHAR(4000) .

ĐẶT @Str='CHỌN TOP '+CAST(@RecsPerPage LÀ VARCHAR(20))+' * TỪ ('+@SQL+') T ĐÂU T.'+@ID+'KHÔNG CÓ TRONG .

(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 SẮP XẾP THEO '+@Sort+') SẮP XẾP THEO '+@Sort .

IN @Str .

Thực hiện sp_ExecuteSql @Str .

ĐI.

Trên thực tế, câu phát biểu trên có thể được đơn giản hóa thành:

CHỌN kích thước trang ĐẦU TRANG * .

TỪ Bảng 1.

Ở ĐÂU (ID KHÔNG CÓ TRONG .

(CHỌN TOP page_size*page_id.

TỪ bảng.

SẮP XẾP THEO id)) .

SẮP XẾP THEO ID.

Nhưng quy trình được lưu trữ này có một lỗi nghiêm trọng, đó là nó chứa từ NOT IN. Mặc dù tôi có thể biến đổi nó thành:

CHỌN kích thước trang ĐẦU TRANG * .

TỪ Bảng 1.

WHERE không tồn tại.

(chọn * từ (chọn đầu trang (kích thước trang * số trang) * từ bảng1 theo thứ tự id) b trong đó b.id=a.id ) .

sắp xếp theo id.

Nghĩa là, sử dụng not exists thay vì not in, nhưng như chúng ta đã thảo luận trước đó, thực tế không có sự khác biệt nào về hiệu quả thực thi của hai cách này.

Mặc dù vậy, sử dụng TOP kết hợp với NOT IN vẫn nhanh hơn sử dụng con trỏ.

Mặc dù việc sử dụng not exists không thể lưu lại hiệu quả của quy trình được lưu trữ trước đó, nhưng việc sử dụng từ khóa TOP trong SQL SERVER là một lựa chọn rất khôn ngoan. Bởi vì mục tiêu cuối cùng của việc tối ưu hóa phân trang là tránh tạo ra một tập bản ghi quá lớn và chúng tôi đã đề cập đến những ưu điểm của TOP, qua đó có thể kiểm soát được lượng dữ liệu.

Trong thuật toán phân trang, có hai yếu tố chính ảnh hưởng đến tốc độ truy vấn của chúng ta: TOP và NOT IN. TOP có thể cải thiện tốc độ truy vấn của chúng ta, trong khi NOT IN sẽ làm chậm tốc độ truy vấn của chúng ta. Do đó, để cải thiện tốc độ của toàn bộ thuật toán phân trang của chúng ta, chúng ta phải chuyển đổi hoàn toàn NOT IN và thay thế nó bằng các phương pháp khác.

Chúng ta biết rằng đối với hầu hết mọi trường, chúng ta có thể sử dụng max(field) hoặc min(field) để trích xuất giá trị lớn nhất hoặc nhỏ nhất trong một trường, vì vậy nếu trường này không lặp lại, thì chúng ta có thể sử dụng giá trị lớn nhất hoặc nhỏ nhất của các trường không lặp lại này làm ranh giới, biến nó thành tham chiếu để phân tách từng trang trong thuật toán phân trang. Tại đây, chúng ta có thể sử dụng toán tử ">" hoặc "<" để hoàn thành nhiệm vụ này và làm cho câu lệnh truy vấn tuân thủ theo mẫu SARG. giống:

Chọn top10*từ table1nơi id>200.

Vì vậy, có sơ đồ phân trang như sau:

chọn kích thước trang đầu* .

từ bảng 1.

trong đó id> .

(chọn max (id) từ .

(chọn ((số trang - 1) * kích thước trang) id từ bảng1 theo thứ tự id) làm T.

) .

sắp xếp theo id.

Khi chọn cột không có giá trị trùng lặp và dễ phân biệt về kích thước, chúng ta thường chọn khóa chính. Bảng sau đây liệt kê các bảng trong hệ thống tự động hóa văn phòng với 10 triệu dữ liệu. Tác giả sử dụng GID (GID là khóa chính, nhưng không phải là chỉ mục nhóm) làm cột sắp xếp, trích xuất các trường gid, fariqi và title, và lấy trang thứ 1, thứ 10, thứ 100, thứ 500, thứ 1000, thứ 10.000, thứ 100.000, thứ 250.000 và thứ 500.000 làm ví dụ để kiểm tra tốc độ thực hiện của ba lược đồ phân trang trên: (đơn vị: mili giây).

số trang.	Lựa chọn 1.	Lựa chọn 2.	Lựa chọn 3.
1.	60.	30.	76.
10.	46.	16.	63.
100.	1076.	720.	130.
500.	540.	12943.	83.
1000.	17110 .	470.	250.
10 nghìn.	24796.	4500.	140.
100.000.	38326.	42283.	1553.
250.000.	28140 .	128720 .	2330.
500.000.	121686.	127846.	7168.

Từ bảng trên, chúng ta có thể thấy rằng cả ba thủ tục được lưu trữ đều đáng tin cậy và có tốc độ tốt khi thực hiện các lệnh phân trang dưới 100 trang. Tuy nhiên, tốc độ của giải pháp đầu tiên chậm lại sau khi thực hiện phân trang hơn 1.000 trang. Giải pháp thứ hai bắt đầu chậm lại sau khi thực hiện phân trang cho hơn 10.000 trang. Tuy nhiên, lựa chọn thứ ba vẫn chưa cho thấy xu hướng giảm đáng kể và vẫn có tiềm năng mạnh mẽ.

Sau khi xác định lược đồ phân trang thứ ba, chúng ta có thể viết một thủ tục được lưu trữ dựa trên lược đồ đó. Như chúng ta đã biết, các thủ tục lưu trữ SQL SERVER là các câu lệnh SQL được biên dịch trước và hiệu quả thực thi của chúng cao hơn so với các câu lệnh SQL được truyền qua các trang WEB. Quy trình được lưu trữ sau đây không chỉ chứa lược đồ phân trang mà còn xác định xem có thực hiện thống kê tổng dữ liệu dựa trên các tham số được truyền qua trang hay không.

-- Lấy dữ liệu của trang được chỉ định.

TẠO THỦ TỤC phân trang3.

@tblName varchar(255), -- tên bảng.

@strGetFields varchar(1000) = '*', -- Các cột sẽ được trả về.

@fldName varchar(255)='', -- Tên trường cần sắp xếp.

@PageSize int = 10, -- Kích thước trang.

@PageIndex int = 1, -- Số trang.

@doCount bit = 0, -- Trả về tổng số bản ghi, nếu không phải 0 thì sẽ trả về.

@OrderType bit = 0, -- Đặt kiểu sắp xếp, giá trị khác không nghĩa là thứ tự giảm dần.

@strWhere varchar(1500) = '' -- Điều kiện truy vấn (lưu ý: không thêm where).

BẰNG .

khai báo @strSQL varchar(5000) -- Câu lệnh chính.

khai báo @strTmp varchar(110) -- Biến tạm thời.

khai báo @strOrder varchar(400) -- Kiểu sắp xếp.

nếu @doCount != 0 .

bắt đầu.

nếu @strWhere != '' .

đặt @strSQL = "chọn count(*) làm Tổng số từ [" + @tblName + "] trong đó "+@strWhere .

khác .

đặt @strSQL = "chọn count(*) làm Tổng số từ [" + @tblName + "]" .

kết thúc .

--Đoạn mã trên có nghĩa là nếu @doCount khác 0, tổng số đếm sẽ được thực thi. Tất cả các mã sau đây đều dựa trên trường hợp @doCount bằng 0.

khác .

bắt đầu.

nếu @OrderType != 0 .

bắt đầu.

đặt @strTmp = "<(chọn phút" .

đặt @strOrder = "sắp xếp theo [" + @fldName + "] mô tả".

--Nếu @OrderType không phải là 0, thực hiện thứ tự giảm dần. Câu này rất quan trọng.

kết thúc .

khác .

bắt đầu.

đặt @strTmp = ">(chọn max".

đặt @strOrder = "sắp xếp theo [" + @fldName + "] tăng dần".

kết thúc .

nếu @PageIndex = 1.

bắt đầu.

nếu @strWhere != '' .

đặt @strSQL = "chọn đầu trang " + str(@PageSize) +" "+@strGetFields+ " từ [" + @tblName + "] trong đó " + @strWhere + " " + @strOrder .

khác .

đặt @strSQL = "chọn đầu trang " + str(@PageSize) +" "+@strGetFields+ " từ ["+ @tblName + "] "+ @strOrder .

--Nếu đoạn mã trên được thực thi ở trang đầu tiên, nó sẽ tăng tốc độ thực thi.

kết thúc .

khác .

bắt đầu.

--Đoạn mã sau cung cấp cho @strSQL mã SQL thực sự được thực thi.

đặt @strSQL = "chọn đầu trang " + str(@PageSize) +" "+@strGetFields+ " từ [" .

+ @tblName + "] trong đó [" + @fldName + "]" + @strTmp + "(["+ ​​​​@fldName + "]) từ (chọn đầu trang " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] từ [" + @tblName + "]" + @strOrder + ") là tblTmp)"+ @strOrder .

nếu @strWhere != '' .

đặt @strSQL = "chọn đầu trang " + str(@PageSize) +" "+@strGetFields+ " từ [" .

+ @tblName + "] trong đó [" + @fldName + "]" + @strTmp + "([" .

+ @fldName + "]) từ (chọn phần đầu " + str((@PageIndex-1)*@PageSize) + " [" .

+ @fldName + "] từ [" + @tblName + "] trong đó " + @strWhere + " " .

+ @strOrder + ") là tblTmp) và " + @strWhere + " " + @strOrder .

kết thúc .

kết thúc .

thực thi (@strSQL).

ĐI.

Quy trình được lưu trữ ở trên là một quy trình được lưu trữ phổ biến và các chú thích của nó được viết trong đó.

Trong trường hợp dữ liệu có dung lượng lớn, đặc biệt là khi truy vấn một vài trang cuối, thời gian truy vấn thường không quá 9 giây; sử dụng các thủ tục được lưu trữ khác trên thực tế sẽ dẫn đến tình trạng hết thời gian chờ, do đó thủ tục được lưu trữ này rất phù hợp để truy vấn cơ sở dữ liệu có dung lượng lớn.

Tác giả hy vọng rằng việc phân tích các thủ tục lưu trữ trên có thể mang lại một số cảm hứng cho mọi người và nâng cao hiệu quả công việc. Đồng thời, tôi hy vọng rằng các đồng nghiệp có thể đề xuất các thuật toán phân trang dữ liệu thời gian thực tốt hơn.

4. Tầm quan trọng của chỉ mục cụm và cách chọn chỉ mục cụm.

Trong tiêu đề của phần trước, tác giả đã viết: Triển khai một quy trình lưu trữ hiển thị phân trang chung cho khối lượng dữ liệu nhỏ và lớn. Bởi vì khi áp dụng thủ tục lưu trữ này vào thực tiễn hệ thống "tự động hóa văn phòng", tác giả nhận thấy thủ tục lưu trữ thứ ba này có hiện tượng sau trong trường hợp khối lượng dữ liệu nhỏ:

1. Tốc độ phân trang thường được duy trì trong khoảng từ 1 giây đến 3 giây.

2. Khi tìm kiếm trang cuối cùng, tốc độ thường là 5 đến 8 giây, ngay cả khi tổng số trang chỉ là 3 hoặc 300.000.

Mặc dù quá trình phân trang rất nhanh trong trường hợp dung lượng lớn, tốc độ 1-3 giây cho vài trang đầu tiên chậm hơn phương pháp phân trang đầu tiên thậm chí còn không được tối ưu hóa. Theo lời người dùng, "nó không nhanh bằng cơ sở dữ liệu ACCESS", đủ để khiến người dùng từ bỏ việc sử dụng hệ thống mà bạn phát triển.

Tác giả đã phân tích điều này và nhận thấy rằng nguyên nhân của hiện tượng này rất đơn giản nhưng lại rất quan trọng: trường được sắp xếp không phải là chỉ mục cụm.

Tiêu đề của bài viết này là: "Giải pháp tối ưu hóa truy vấn và thuật toán phân trang". Lý do tôi đặt hai chủ đề "tối ưu hóa truy vấn" và "thuật toán phân trang" lại với nhau là vì cả hai đều yêu cầu một điều rất quan trọng - chỉ mục cụm.

Như chúng tôi đã đề cập trong phần thảo luận trước, chỉ mục cụm có hai lợi thế lớn nhất:

1. Thu hẹp phạm vi tìm kiếm càng nhanh càng tốt.

2. Sắp xếp các trường ở tốc độ nhanh nhất.

Phương pháp đầu tiên chủ yếu được sử dụng để tối ưu hóa truy vấn, trong khi phương pháp thứ hai chủ yếu được sử dụng để sắp xếp dữ liệu trong quá trình phân trang.

Và chỉ có thể thiết lập một chỉ mục cụm trong mỗi bảng, điều này làm cho chỉ mục cụm trở nên quan trọng hơn. Việc lựa chọn chỉ mục cụm có thể được coi là yếu tố quan trọng nhất để đạt được "tối ưu hóa truy vấn" và "phân trang hiệu quả".

Nhưng thường xảy ra mâu thuẫn khi làm cho cột chỉ mục cụm đáp ứng được nhu cầu của cả cột truy vấn và cột sắp xếp.

Trong phần thảo luận trước của tác giả về "chỉ mục", fariqi, ngày người dùng đăng bài viết, được sử dụng làm cột bắt đầu của chỉ mục nhóm và độ chính xác của ngày là "ngày". Ưu điểm của cách tiếp cận này đã được đề cập trước đó. Nó có lợi thế lớn hơn so với việc sử dụng cột khóa chính ID trong các truy vấn nhanh cho các khoảng thời gian.

Tuy nhiên, trong quá trình phân trang, vì có các bản ghi trùng lặp trong cột chỉ mục nhóm này nên không thể sử dụng max hoặc min làm tham chiếu để phân trang và do đó không thể sắp xếp hiệu quả hơn. Nếu cột khóa chính ID được sử dụng làm chỉ mục cụm, thì chỉ mục cụm không có tác dụng gì ngoại trừ việc sắp xếp, điều này thực sự lãng phí tài nguyên có giá trị của chỉ mục cụm.

Để giải quyết mâu thuẫn này, sau đó tác giả đã thêm một cột ngày với giá trị mặc định là getdate(). Khi người dùng ghi một bản ghi, cột này sẽ tự động ghi thời gian hiện tại, chính xác đến từng mili giây. Mặc dù vậy, để tránh khả năng chồng chéo không mong muốn, hãy tạo một ràng buộc DUY NHẤT trên cột này. Sử dụng cột ngày này làm cột chỉ mục nhóm.

Với cột chỉ mục cụm theo thời gian này, người dùng có thể sử dụng cột này để tìm kiếm các truy vấn trong một khoảng thời gian nhất định khi chèn dữ liệu và cũng có thể sử dụng nó như một cột duy nhất để triển khai max hoặc min, trở thành tham chiếu cho các thuật toán phân trang.

Sau khi tối ưu hóa như vậy, tác giả nhận thấy rằng bất kể khối lượng dữ liệu lớn hay nhỏ, tốc độ phân trang thường là hàng chục mili giây hoặc thậm chí 0 mili giây. Tốc độ truy vấn thu hẹp phạm vi theo phân đoạn ngày không chậm hơn trước.

Chỉ số cụm rất quan trọng và quý giá, vì vậy tác giả tóm tắt rằng chỉ số cụm phải được thiết lập trên:

1. Các trường bạn sử dụng thường xuyên nhất để thu hẹp phạm vi tìm kiếm,

2. Trên các trường bạn sử dụng thường xuyên nhất và cần sắp xếp.

Phần kết luận:

Bài viết này tổng hợp kinh nghiệm gần đây của tác giả trong việc sử dụng cơ sở dữ liệu, là sự tích lũy kinh nghiệm thực tế khi làm việc trên các hệ thống "tự động hóa văn phòng". Tôi hy vọng bài viết này không chỉ giúp ích cho công việc của bạn mà còn giúp bạn hiểu được phương pháp phân tích vấn đề. Quan trọng nhất là tôi hy vọng bài viết này có thể truyền cảm hứng cho những người khác cùng nghiên cứu và thảo luận, để chúng ta cùng nhau nỗ lực hết mình vì sự nghiệp tăng cường lực lượng cảnh sát bằng khoa học công nghệ và Dự án Lá chắn vàng.

Cuối cùng, tôi cần chỉ ra rằng trong các thí nghiệm của mình, tôi thấy rằng khi người dùng thực hiện các truy vấn dữ liệu lớn, tác động lớn nhất đến tốc độ cơ sở dữ liệu không phải là kích thước bộ nhớ mà là CPU. Khi tôi thử nghiệm trên máy P4 2.4 của mình, tôi đã kiểm tra "Trình quản lý tài nguyên" và thấy rằng mức sử dụng CPU thường vẫn ở mức 100%, trong khi mức sử dụng bộ nhớ không thay đổi hoặc thay đổi không nhiều. Ngay cả khi thử nghiệm trên máy chủ HP ML 350 G3 của chúng tôi, CPU có thể đạt tới 90% và thường duy trì ở mức 70%.

Dữ liệu thử nghiệm trong bài viết này đều được lấy từ máy chủ HP ML 350 của chúng tôi. Cấu hình máy chủ: CPU siêu phân luồng Intel Xeon kép 2,4G, bộ nhớ 1G, hệ điều hành Windows Server 2003 Enterprise Edition, cơ sở dữ liệu SQL Server 2000 SP3.

Cuối cùng, bài viết này về tối ưu hóa SQL kết thúc tại đây. Nếu bạn muốn biết thêm về tối ưu hóa SQL, vui lòng tìm kiếm các bài viết trên CFSDN hoặc tiếp tục duyệt các bài viết liên quan. Tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .