Thực đơn
trang chủYandex

Tối ưu hóa truy vấn SQL. Lý do tại sao các truy vấn SQL không hiệu quả trong Oracle. Tối ưu hóa hiệu suất truy vấn SQL

Tôi sẽ chia sẻ kinh nghiệm tôi đã có được sau nhiều năm tối ưu hóa các truy vấn SQL. Hầu hết các lời khuyên đều liên quan đến cơ sở dữ liệu ORACLE.
Nếu bài viết có vẻ quá rõ ràng đối với bất kỳ ai, thì hãy coi đây là một ghi chú hoàn toàn dành cho chính bạn, để không quên.

1. Không có truy vấn phụ, chỉ THAM GIA
Như tôi đã viết trước đó, nếu mẫu là 1 trên 1 hoặc bạn cần tính tổng một cái gì đó thì không cần truy vấn phụ, chỉ cần tham gia.
Cần lưu ý rằng trong hầu hết các trường hợp, trình tối ưu hóa sẽ có thể mở rộng truy vấn con thành một phép nối, nhưng điều này không phải lúc nào cũng xảy ra.

2. Chọn VÀO hay TỒN TẠI?
Thực ra nó là Lựa chọn khó khăn Và giải pháp đúng chỉ có thể có được thông qua kinh nghiệm.
Tôi sẽ chỉ cho bạn một vài lời khuyên:
* Nếu có nhiều hàng trong vùng chọn chính và ít hàng trong truy vấn phụ, thì lựa chọn của bạn TRONG. Bởi vì trong trường hợp này, truy vấn trong in sẽ được thực thi một lần và sẽ ngay lập tức hạn chế bảng chính lớn.
* Nếu trong truy vấn phụ truy vấn phức tạp và có tương đối ít hàng trong vùng chọn chính, thì lựa chọn của bạn Tồn tại. Trong trường hợp này, một truy vấn phức tạp sẽ không được thực hiện thường xuyên.
* Nếu cả hai đều khó thì đây là lý do để thay đổi logic để nối.

3. Đừng quên các chỉ mục
Lời khuyên dành cho người mới bắt đầu: đính kèm chỉ mục vào các cột mà bạn nối các bảng trên đó.

4. Nếu có thể, hãy tránh sử dụng OR.
Thực hiện các bài kiểm tra, có lẽ UNION trông không sang trọng cho lắm nhưng yêu cầu có thể được thực hiện nhanh hơn nhiều. Lý do là trong trường hợp OR, các chỉ mục hầu như không được sử dụng trong phép nối.

5. Nếu có thể, đừng sử dụng VỚI trong nhà tiên tri.
Sẽ dễ dàng hơn nhiều nếu truy vấn with cần được sử dụng nhiều lần (với gợi ý cụ thể hóa) trong lựa chọn chính hoặc nếu số lượng hàng trong truy vấn con không đáng kể.
Trong tất cả các trường hợp khác, cần sử dụng truy vấn con trực tiếp từ hoặc một bảng được chuẩn bị trước với các chỉ mục và dữ liệu cần thiết từ VỚI.
Gây ra công việc tệ hại VỚI là khi tham gia nó, không có chỉ mục nào được sử dụng và nếu có nhiều dữ liệu trong đó thì mọi thứ sẽ ổn. Lý do thứ hai là trình tối ưu hóa khó xác định lượng dữ liệu sẽ trả về cho chúng ta và trình tối ưu hóa không thể xây dựng kế hoạch truy vấn chính xác.
Trong hầu hết các trường hợp, WITH không có +materialize vẫn sẽ được mở rộng thành truy vấn chính.

6. Đừng yêu cầu km
Thông thường trên web, vấn đề ngược lại là có nhiều yêu cầu nhỏ trong một vòng lặp và chúng được khuyên nên kết hợp thành một yêu cầu lớn. Nhưng có những hạn chế ở đây: nếu bạn có một truy vấn được bao bọc nhiều lần thì (các) phần bên trong phải được đặt trong một vùng chọn riêng biệt, điền vào một bảng tạm thời, chỉ định các chỉ mục và sau đó được sử dụng trong vùng chọn chính. Tốc độ làm việc sẽ cao hơn đáng kể (chủ yếu là do sự phức tạp của việc xây dựng một kế hoạch tối ưu trên một số lượng lớn các kết hợp bảng)

7. Sử dụng KEEP thay vì truy vấn con tương quan.
ORACLE có các chức năng phân tích rất hữu ích giúp đơn giản hóa các truy vấn của bạn. Một trong số đó là GIỮ.
KEEP sẽ cho phép bạn sắp xếp hoặc nhóm lựa chọn chính mà không cần yêu cầu bổ sung.
Ví dụ: chọn một đối tác cho một mặt hàng được liên kết với nó sớm hơn những mặt hàng khác. Một mặt hàng có thể có nhiều nhà cung cấp.
CHỌN n.ID, MIN(c.ID) GIỮ (DENSE_RANK ĐẶT HÀNG ĐẦU TIÊN THEO c.date ASC) dưới dạng cnt_id FROM nmcl n, cnt c WHERE n.cnt_id = c.id GROUP BY n.ID Với cách tiếp cận thông thường, chúng ta sẽ có để thực hiện truy vấn con tương quan cho từng mục với việc chọn ngày tối thiểu.
Nhưng đừng lạm dụng một số lượng lớn chức năng phân tích, đặc biệt nếu chúng có cách sắp xếp khác nhau. Mỗi cách sắp xếp khác nhau là một lần quét mới của cửa sổ.

8. Đi lên và xuống mẫu
Một tính năng ít phổ biến hơn nhưng không kém phần hữu ích. Cho phép bạn chuyển đổi dòng hiện tại lựa chọn theo N phần tử lên hoặc xuống. Nó có thể hữu ích nếu bạn cần so sánh các chỉ số của các hàng liền kề.
Ví dụ sau chọn doanh số bán hàng của bộ phận được sắp xếp theo ngày. Các cột có giá trị doanh thu tiếp theo và trước đó sẽ được thêm vào lựa chọn chính. Tham số thứ hai là dịch chuyển bao nhiêu dòng, tham số thứ ba là tham số mặc định nếu không tìm thấy dữ liệu của hàng xóm. CHỌN deptno, empno, sal, LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER THE date) NEXT_LOWER_SAL, LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER THE date) PREV_HIGHER_SAL FROM emp; ĐẶT HÀNG THEO phòng, ngày DESC; Với cách tiếp cận thông thường, điều này sẽ phải được thực hiện thông qua logic ứng dụng.

9. Đọc đường dẫn trực tiếp
Đặt cài đặt này (bằng cách điều chỉnh hoặc bằng truy vấn song song) là đọc dữ liệu trực tiếp vào PGA, bỏ qua bộ đệm đệm. Điều này làm chậm các giai đoạn tiếp theo của yêu cầu, bởi vì UNDO và chốt chia sẻ không được sử dụng.

10. IO trực tiếp
Sử dụng ghi/đọc trực tiếp từ đĩa mà không cần sử dụng bộ đệm hệ thống tệp ( hệ thống tập tin dành riêng cho DBMS).
* Trong trường hợp đọc, ưu điểm là sử dụng bộ đệm đệm cơ sở dữ liệu, thay thế bộ đệm FS (bộ đệm cơ sở dữ liệu được thiết kế tốt hơn để hoạt động với sql)
* Trong trường hợp ghi, ghi trực tiếp đảm bảo dữ liệu không bị mất trong bộ đệm FS trong trường hợp mất điện (đối với redolog, nó luôn sử dụng fsync, bất kể loại FS)

Sau khi viết chương trình và xuất hiện dữ liệu “trực tiếp”, hóa ra phản hồi của chương trình đối với các tập kiểm tra đôi khi rất khác so với làm việc với dữ liệu thực. Các lập trình viên thường ít chú ý tới việc hình thành bộ dụng cụ kiểm tra dữ liệu, đó là một lỗi nghiêm trọng. Đặt cược vào việc sử dụng các DBMS “tuyệt vời” hiện đại có khả năng tự cấu hình. Thật không may, điều này không hoàn toàn đúng và cần phải hết sức chú ý khi làm việc với cơ sở dữ liệu. Lý tưởng nhất là các chuyên gia phải chịu trách nhiệm xử lý logic nghiệp vụ. Nhưng nó cũng hữu ích cho những lập trình viên bình thường có kỹ năng và kiến ​​thức về kiến ​​trúc DBMS và viết các truy vấn SQL.

Thường có những trường hợp khi trình tạo tập lệnh và Mã chương trìnhđể truy cập dữ liệu. Các tác giả của chương trình hy vọng rằng những đổi mới công nghệ hiện đại sẽ giải quyết mọi việc cho bạn. Kết quả là, thường có trường hợp vài tháng sau khi triển khai chương trình, người dùng bắt đầu phàn nàn rằng chương trình “hầu như không di chuyển”. Sự hoảng loạn chung bắt đầu với sự tham gia của các chuyên gia đắt giá, những người có thể tìm ra “nút cổ chai” đang làm chậm chương trình và cứu dự án.

Thực tiễn cho thấy rằng bằng cách phân tích và xây dựng lại các truy vấn SQL, bạn có thể giảm thời gian thực hiện của chúng xuống hàng chục, đôi khi hàng trăm lần. Sau khi phát triển một số dự án, các lập trình viên sẽ phát triển kỹ năng viết các truy vấn nhanh hơn. Tuy nhiên, việc phân tích định kỳ chi phí tài nguyên máy chủ khi chạy tác phẩm của bạn vẫn hữu ích. Và mặc dù nhìn chung phân tích việc sử dụng tài nguyên máy chủ là công việc của người quản trị cơ sở dữ liệu; có kỹ năng tối ưu hóa chương trình sẽ không gây hại cho ai. Hơn nữa, nó không khó như thoạt nhìn.

Có một số chương trình cho phép bạn tự động hóa và đơn giản hóa công việc này. Tài liệu này tập trung vào làm việc với máy chủ Oracle, nhưng đối với các cơ sở dữ liệu khác cũng có các công cụ phân tích và điều chỉnh tối ưu hóa tương tự. Trợ lý đầu tiên của chúng tôi sẽ là một chương trình giám sát máy chủ Oracle có tên “Spotlight on Oracle” từ phần mềm Quest (http://www.quest.com). Cái này rất công cụ đắc lực, được thiết kế để kiểm soát hoạt động của máy chủ của bạn. Chương trình nàyđược thực hiện một cách bất thường bảng màu, điều này làm cho nó nổi bật so với các sản phẩm khác. Sau khi chạy chương trình này, bạn phải tạo một tài khoản người dùng, tài khoản này sẽ yêu cầu tài khoản SYS hoặc tài khoản có đặc quyền hệ thống DBA. Trợ lý sáng tạo mới tài khoảnđược gọi từ menu “Tệp > Trình hướng dẫn người dùng”.

Sau khi tạo tài khoản người dùng và kết nối với máy chủ Oracle, chúng ta sẽ thấy một hình ảnh trực quan hiển thị các thành phần và quy trình của máy chủ. Nếu một hoặc nhiều thành phần máy chủ không hoạt động tối ưu hoặc bị quá tải thì màu của nó sẽ chuyển từ xanh sang đỏ, tùy thuộc vào mức độ quá tải. Có thể giám sát nhiều máy chủ cùng một lúc, danh sách các máy chủ này được hiển thị ở bảng bên trái và cũng thay đổi màu sắc. Icon trên taskbar cũng đổi màu đồng bộ với chương trình, giúp bạn phản hồi nhanh khi bị “thu nhỏ” trong ứng dụng. Một ví dụ về giám sát được thể hiện trong Hình 1.

Một tính năng rất hữu ích của chương trình này là hệ thống tự động đề xuất giải quyết vấn đề. Chỉ cần nhấp vào vùng màu đỏ của hình ảnh để có được mô tả chi tiết về vấn đề và phương pháp có thể sự loại bỏ của nó. Nếu mọi thứ đều ổn thì nhờ chương trình này, bạn có thể điều chỉnh các thông số khởi động máy chủ để giảm lượng dữ liệu mà nó sử dụng. tài nguyên hệ thống. Ví dụ: từ Hình 1, chúng ta có thể kết luận rằng kích thước vùng bảng của tệp cơ sở dữ liệu có thể giảm một nửa một cách an toàn và nên phân bổ bộ nhớ bổ sung trong phần “Nhóm dùng chung”.

Nhưng đây hoàn toàn là vấn đề của người quản trị cơ sở dữ liệu. Các nhà phát triển quan tâm nhiều hơn đến cách thức hoạt động của các sản phẩm sáng tạo của họ và lượng truy vấn cơ sở dữ liệu tiêu thụ bao nhiêu tài nguyên. Để thực hiện việc này, hãy gọi mục menu “Điều hướng > Phiên hàng đầu”. Sau khi điền các tham số của bộ lọc chọn dữ liệu, chúng ta sẽ được hiển thị danh sách các yêu cầu hiện tại đến máy chủ cơ sở dữ liệu. Sau khi đã sắp xếp các yêu cầu trước đó theo yêu cầu tài nguyên, bạn có thể chọn những yêu cầu “háu ăn” nhất. Trong cùng một cửa sổ, bạn có thể thấy kế hoạch thực hiện truy vấn, một ví dụ về kế hoạch này được hiển thị trong Hình 2. Hơn nữa, kế hoạch truy vấn có thể được trình bày dưới dạng biểu đồ, cây hoặc mô tả bằng lời nói. Nó cũng được sử dụng ở đây mã màu Các khu vực có vấn đề.

Sau khi xác định các truy vấn SQL có vấn đề, đã đến lúc tối ưu hóa chúng. Để tự động hóa quá trình này chúng ta sẽ sử dụng chương trình SQL Chuyên gia từ LECCO (http://www.leccotech.com). Mở cửa sổ soạn thảo SQL và thêm tập lệnh truy vấn vào đó. Tại đây bạn cũng có thể xem kế hoạch thực hiện truy vấn. Nhưng chúng tôi quan tâm nhất đến chức năng menu “SQL-> Tối ưu hóa”, tạo danh sách lựa chọn thay thế xây dựng một tập lệnh SQL nhất định. Và chức năng “SQL-> Butch Run” cho phép bạn phân tích thời gian thực hiện của tất cả các truy vấn trên dữ liệu “trực tiếp” và hiển thị bảng kết quả, có thể được sắp xếp theo tham số bắt buộc. Sau khi chọn yêu cầu tối ưu nhất, bạn có thể so sánh nó với yêu cầu ban đầu và quyết định khả năng tiếp tục sử dụng nó trong ứng dụng của mình. Một ví dụ về công việc tối ưu hóa truy vấn được hiển thị trong Hình 3.

Do đó, bằng cách sử dụng kỹ thuật đề xuất mà không ảnh hưởng đến mã của chương trình chính, đôi khi có thể tăng đáng kể hiệu suất ứng dụng và cứu được dự án. Khi làm như vậy, bạn sẽ có được kinh nghiệm vô giá trong việc viết các truy vấn hiệu suất cao. Bằng cách phân tích các tùy chọn tập lệnh SQL do chương trình cung cấp, theo thời gian, bạn sẽ phát triển thói quen viết “tối ưu” ngay lập tức, điều này cũng làm tăng hình ảnh của bạn như một chuyên gia giỏi.

Khi người dùng bắt đầu thao tác truy xuất dữ liệu, câu lệnh SQL của người dùng đó sẽ trải qua một số bước tuần tự, được gọi chung là xử lý truy vấn. Một trong những lợi thế chính ngôn ngữ SQL là nó không mang tính thủ tục và do đó không cần liệt kê các bước phải thực hiện để đạt được mục tiêu của người vận hành. Nói cách khác, SQL không cần mô tả cách thực hiện một việc nào đó, thay vào đó, nó chỉ cần mô tả những gì nó muốn lấy từ cơ sở dữ liệu.

Xử lý truy vấn có nghĩa là chuyển đổi câu lệnh SQL thành một kế hoạch thực thi hiệu quả để trả về dữ liệu được yêu cầu từ cơ sở dữ liệu. Tối ưu hóa truy vấn đề cập đến quá trình chọn kế hoạch thực hiện hiệu quả nhất để đạt được kết quả với chi phí ít nhất về mức tiêu thụ tài nguyên, chẳng hạn như tài nguyên I/O và CPU, trên máy chủ nơi cơ sở dữ liệu chạy, cũng như giảm tổng thể thời gian thực hiện truy vấn, đơn giản là tổng các chỉ báo thời gian thực hiện của tất cả các thành phần của yêu cầu này hoạt động. Việc tối ưu hóa hiệu suất này có thể không giống như giảm thiểu thời gian phản hồi. Nếu cần giảm thiểu thời gian tìm nạp n hàng đầu tiên thay vì toàn bộ đầu ra của truy vấn, trình tối ưu hóa có thể chọn một gói khác và nếu cần giảm thiểu thời gian phản hồi cho tất cả dữ liệu truy vấn, nó cũng có thể chọn một gói song song. chế độ thực hiện thao tác.

Nói chung, mọi câu lệnh SQL được người dùng thực thi đều trải qua một giai đoạn phân tích cú pháp, giai đoạn tối ưu hóa và giai đoạn thực hiện. Nếu câu lệnh SQL là một truy vấn thì nó liên quan đến việc truy xuất dữ liệu và do đó trong trường hợp này, trước khi hoàn tất quá trình xử lý, cũng có một giai đoạn truy xuất bổ sung. Các phần phụ sau đây sẽ đi sâu vào chi tiết hơn về những gì Oracle thực hiện trong mỗi giai đoạn này.

Phân tích truy vấn SQL

Giai đoạn phân tích cú pháp chủ yếu bao gồm việc kiểm tra cú pháp và ngữ nghĩa của câu lệnh SQL. Vào cuối giai đoạn này, một cây phân tích được tạo ra để phản ánh cấu trúc của yêu cầu.

Cụ thể, trong giai đoạn này câu lệnh SQL được chuyển thành câu truy vấn đại số quan hệ, được phân tích để xác định xem nó có đúng về mặt cú pháp hay không. Sau đó, truy vấn sẽ được kiểm tra xác thực ngữ nghĩa, sử dụng từ điển dữ liệu để xác minh rằng tất cả các bảng và cột riêng lẻ được tham chiếu trong truy vấn, cũng như mọi đặc quyền đối tượng bắt buộc, thực sự tồn tại. Ngoài ra, các loại cột được kiểm tra để đảm bảo dữ liệu khớp với định nghĩa cột. Câu lệnh sau đó được chuẩn hóa để có thể được xử lý theo cách hiệu quả hơn. Nếu yêu cầu được xây dựng không chính xác, nó sẽ bị từ chối. Khi cây phân tích vượt qua tất cả các kiểm tra cú pháp và ngữ nghĩa, nó được coi là hợp lệ và được gửi đến giai đoạn tạo kế hoạch truy vấn logic. Tất cả các hoạt động này xảy ra trong SGA, đại diện cho bộ đệm thư viện của bộ phận.

Tối ưu hóa truy vấn SQL

Trong giai đoạn tối ưu hóa, Oracle sử dụng trình tối ưu hóa của mình, được gọi là Trình tối ưu hóa cơ sở chi phí (CBO), để chọn phương pháp tốt nhất truy cập để lấy dữ liệu từ các bảng và chỉ mục có trong yêu cầu. Bằng cách sử dụng số liệu thống kê được cung cấp và bất kỳ gợi ý nào được chỉ định trong truy vấn SQL, CBO tạo ra kế hoạch thực hiện tối ưu cho câu lệnh SQL.

TRONG trường hợp chung Bước tối ưu hóa có thể được chia thành hai bước phụ riêng biệt: viết lại truy vấn và tạo kế hoạch thực hiện truy vấn vật lý. Chúng ta hãy xem xét hai bước tối ưu hóa riêng biệt này một cách chi tiết hơn.

Yêu cầu giai đoạn viết lại

Ở giai đoạn này, cây phân tích được chuyển đổi thành một sơ đồ logic trừu tượng để thực hiện truy vấn. Nó đại diện cho phiên bản gốc kế hoạch thực tế thực hiện truy vấn và chỉ chứa một công thức đại số tổng quát của truy vấn ban đầu. Nghĩa là, trong giai đoạn này, các nút và nhánh khác nhau của cây phân tích cú pháp được thay thế bằng các phép toán đại số quan hệ. Lưu ý rằng việc viết lại truy vấn ở đây có ý nghĩa rất khác với việc viết lại truy vấn được thực hiện khi sử dụng các chế độ xem cụ thể hóa.

Giai đoạn tạo kế hoạch thực hiện

Tại thời điểm này, Oracle chuyển đổi sơ đồ logic thành sơ đồ truy vấn vật lý. Để xử lý một truy vấn, trình tối ưu hóa có thể có một số thuật toán để lựa chọn. Nó chọn thuật toán hiệu quả nhất trong số các thuật toán này để trả lời truy vấn và xác định phương pháp hiệu quảđể triển khai các hoạt động. Ngoài việc quyết định những bước vận hành nào là tốt nhất để thực hiện, anh ta còn xác định thứ tự các bước này cần được thực hiện. Ví dụ: sau khi quyết định rằng nó cần thực hiện thao tác nối giữa bảng A và bảng B, trình tối ưu hóa sau đó sẽ xác định loại liên kết nào sẽ là và thứ tự tốt nhất để thực hiện nó.

Nói chung, khi tạo kế hoạch vật lý hoặc kế hoạch thực hiện truy vấn, trình tối ưu hóa sẽ tính đến các yếu tố sau:

  • các thao tác khác nhau (chẳng hạn như thao tác nối) phải được thực hiện trong khi yêu cầu;
  • thứ tự thực hiện các thao tác này;
  • thuật toán nên được sử dụng để thực hiện từng thuật toán đó;
  • cách tốt nhấtđể lấy dữ liệu từ đĩa hoặc bộ nhớ;
  • cách tốt nhất để truyền dữ liệu trong khi yêu cầu từ thao tác này sang thao tác khác.

Trình tối ưu hóa có thể tạo ra nhiều kế hoạch truy vấn vật lý thực tế, đây là những kế hoạch thực hiện tiềm năng. Sau đó, trình tối ưu hóa sẽ đưa ra lựa chọn giữa chúng bằng cách ước tính chi phí của từng phương án vật lý có thể có dựa trên số liệu thống kê về bảng và chỉ mục có sẵn cho nó, đồng thời chọn phương án có chi phí được tính toán là thấp nhất. Quá trình ước tính chi phí của các kế hoạch truy vấn vật lý khả thi này được gọi là tối ưu hóa dựa trên chi phí. Chi phí thực hiện một kế hoạch phụ thuộc trực tiếp vào số lượng tài nguyên (I/O, bộ nhớ và CPU) mà nó yêu cầu. Sau đó, trình tối ưu hóa sẽ chuyển kế hoạch truy vấn vật lý có chi phí thấp nhất đã chọn cho công cụ thực thi Truy vấn Oracle. Phần tiếp theo xem xét một ví dụ đơn giản để giúp bạn hiểu rõ hơn về tối ưu hóa quy trình chi phí.

Ví dụ về tối ưu hóa truy vấn theo chi phí

Giả sử chúng ta muốn chạy truy vấn sau để tìm kiếm thông tin về tất cả người giám sát làm việc tại Dallas:

SQL> CHỌN * TỪ nhân viên e, dept d WHERE e.dept_no = d.dept_no AND(e.job = "SUPERVISOR" AND d.city = "DALLAS"); SQL>

Có ba cách để có được danh sách người quản lý. Chúng ta hãy xem xét ba phương pháp này và tính toán chi phí để đạt được kết quả nếu sử dụng từng phương pháp đó.

Để tính chi phí, hãy bắt đầu với các giả định sau:

  • Bạn chỉ có thể đọc và ghi dữ liệu một hàng mỗi lần (trong thực tế, các thao tác I/O thường được thực hiện ở cấp khối chứ không phải ở cấp hàng);
  • cơ sở dữ liệu ghi từng bước trung gian vào đĩa (một lần nữa, trong thế giới thựcĐiều này có thể không phải là trường hợp);
  • không có chỉ mục nào được liên kết với các bảng;
  • trong bàn người lao động chứa 2000 dòng;
  • trong bàn ban chứa 40 dòng và 40 người quản lý (một người cho mỗi bộ phận);
  • Tổng cộng có mười chi nhánh ở Dallas.

Các phần sau đây trình bày ba truy vấn khác nhau truy xuất cùng một dữ liệu nhưng sử dụng phương pháp khác nhau truy cập. Chi phí sơ bộ được tính toán cho mỗi yêu cầu để có thể so sánh chúng về mức tiêu thụ tài nguyên. Truy vấn đầu tiên liên quan đến việc thực hiện phép nối Descartes.

Truy vấn 1: Nối Descartes

Khi truy vấn này được sử dụng, tích Descartes của các bảng sẽ thu được trước tiên người lao động Và ban, rồi kiểm tra xem dòng nào trong đó thỏa mãn yêu cầu:

Ở ĐÂU e.job=người giám sát VÀ d.dept=hoạt động VÀ e.dept_no=d.dept_no

  • Tích Descartes của các bảng người lao động Và ban sẽ yêu cầu đọc cả hai bảng, tức là 2000 + 40 = 2040 thao tác đọc;
  • tạo ra một sản phẩm Descartes - 2000 * 40 = 80000 ghi;
  • đọc kết quả của tích Descartes để so sánh nó với điều kiện chọn hàng - 2000 * 40 = 80000 thao tác đọc;
  • tổng cộng, tổng chi phí I/O sẽ là: 2040 + 80000 + 80000 = 162040.

Truy vấn 2: Nối hai bảng

Truy vấn thứ hai liên quan đến việc thực hiện nối bảng người lao động Và ban. Nếu truy vấn này được sử dụng, các bảng sẽ được nối trước tiên người lao động Và ban theo cột phòng_no và sau đó chọn từ kết quả của việc nối này tất cả các hàng thỏa mãn điều kiện:

WHERE e.job=người giám sát và thành phố=Dallas

Tổng chi phí thực hiện truy vấn này sẽ như sau:

  • tham gia các bảng người lao động Và dep trước tiên sẽ yêu cầu đọc tất cả các hàng từ cả hai bảng, tức là 2000 + 40 = 2.040 thao tác đọc;
  • tạo một bảng tham gia người lao động Và dep- 2000 thao tác ghi;
  • đọc kết quả kết nối sẽ tốn 2000 lần đọc;
  • tổng cộng, tổng chi phí I/O sẽ là: 2040 + 2000 + 2000 = 6040.

Truy vấn 3: Kết nối phím tắt

Truy vấn thứ ba cũng liên quan đến việc thực hiện nối bảng người lao động Và ban, nhưng với sự kết nối không phải của tất cả, mà chỉ hàng mẫu từ hai bảng này. Nếu được sử dụng, dữ liệu cần thiết sẽ được truy xuất như mô tả bên dưới. Đầu tiên nó sẽ đọc bảng nhân viên để lấy tất cả các hàng có giá trị NGƯỜI GIÁM SÁT. Sau đó, việc đọc từ bảng dept sẽ được thực hiện để truy xuất tất cả các hàng có giá trị DALLAS. Và cuối cùng, việc kết nối các hàng được trích xuất từ ​​các bảng sẽ được thực hiện người lao động Và ban.

Tổng chi phí thực hiện truy vấn này sẽ như sau:

  • đọc bảng người lao độngđể truy xuất các hàng có giá trị NGƯỜI GIÁM SÁT sẽ tốn 2000 thao tác đọc;
  • viết chuỗi có giá trị NGƯỜI GIÁM SÁT, được chiết xuất từ ​​​​ bước trước- 40 thao tác ghi;
  • đọc bảng banđể truy xuất tất cả các hàng có giá trị DALLAS- 40 thao tác đọc;
  • viết chuỗi có giá trị DALLAS, được trích xuất ở bước trước - 10 thao tác ghi;
  • nối chuỗi với giá trị NGƯỜI GIÁM SÁT và có ý nghĩa DALLAS, được trích xuất ở các bước trước khi thực hiện yêu cầu này - tổng cộng 40 + 10 = 50 thao tác ghi;
  • đọc kết quả kết nối thu được ở bước trước - 50 thao tác đọc;
  • tổng chi phí I/O sẽ là: 2000 + 2 (40) + 10 + 2 (50) = 2190.

Ví dụ đơn giản này cho thấy tích Descartes đắt hơn so với các phép nối có nhiều điều kiện hạn chế hơn. Ngay cả thao tác nối có chọn lọc cũng được coi là đắt hơn thao tác chọn. Mặc dù thao tác nối trong Truy vấn 3 là phép nối của hai mối quan hệ đã rút gọn, nhưng kích thước nối dường như nhỏ hơn nhiều so với phép nối trong Truy vấn 2. Tối ưu hóa truy vấn thường liên quan đến việc thực hiện chọn sớm (chỉ chọn một số hàng) và phép chiếu (chỉ chọn một số cột ) hoạt động) để giảm kích thước của nguồn đầu ra hoặc nguồn hàng.

Chiến lược heuristic để xử lý truy vấn

Sử dụng các kỹ thuật tối ưu hóa chi phí không phải là cách duy nhất để thực hiện tối ưu hóa truy vấn. Các kỹ thuật ít hệ thống hơn được gọi là chiến lược heuristic cũng có thể được sử dụng để xử lý các truy vấn cơ sở dữ liệu. Thao tác nối là nhị phân, nhưng thao tác như chọn là đơn nhất. Một chiến lược thành công nói chung là thực hiện một thao tác đơn nhất trên giai đoạn đầu do đó phức tạp hơn và tốn thời gian hơn hoạt động nhị phân sau đó các toán hạng nhỏ hơn đã được sử dụng. Làm điều đó đầu tiên có thể hơn các hoạt động đơn nhất làm giảm nguồn hàng trong các hoạt động nối. Dưới đây là một số chiến lược heuristic phổ biến nhất để xử lý truy vấn.

  • Các thao tác lựa chọn phải được thực hiện sớm để loại bỏ sớm các hàng ứng cử viên trong quá trình xử lý truy vấn. Để lại hầu hết các chuỗi cho đến cuối cùng sẽ thực hiện các so sánh không cần thiết với các chuỗi không hữu ích ở cuối.
  • Các thao tác chiếu nên được thực hiện sớm để hạn chế số lượng cột cần xử lý.
  • Nếu bạn cần thực hiện các thao tác nối tuần tự, trước tiên bạn nên thực hiện thao tác tạo ra phép nối nhỏ nhất.
  • Các biểu thức được sử dụng thường xuyên nhất phải được đánh giá một lần và kết quả được lưu lại.

Thực hiện truy vấn

TRÊN giai đoạn cuối Trong quá trình xử lý truy vấn, truy vấn được tối ưu hóa (kế hoạch truy vấn vật lý đã được chọn) sẽ được thực thi. Nếu đó là một nhà điều hành LỰA CHỌN, thì các hàng tương ứng sẽ được trả về cho người dùng và nếu toán tử CHÈN, CẬP NHẬT hoặc XÓA BỎ, sau đó thực hiện những thay đổi thích hợp cho các dòng. Công cụ thực thi SQL lấy kế hoạch thực hiện thu được trong giai đoạn tối ưu hóa và thực thi nó.

Trong ba giai đoạn xử lý câu lệnh SQL, giai đoạn tối ưu hóa là quan trọng nhất vì nó quyết định tốc độ truy xuất dữ liệu cần thiết. Hiểu cách hoạt động của trình tối ưu hóa vai trò quan trọng trong việc hiểu quá trình tối ưu hóa. Để viết mã SQL hiệu quả, điều quan trọng là phải biết chúng trông như thế nào phương pháp điển hình truy cập, phương thức kết nối và thứ tự kết nối. Vì vậy, phần tiếp theo cung cấp miêu tả cụ thể Trình tối ưu hóa CBO toàn năng của Oracle.

Bây giờ là lúc để tối ưu hóa câu điều kiện SQL. Hầu hết các truy vấn đều sử dụng mệnh đề WHERE của SQL, do đó, bằng cách tối ưu hóa các điều kiện, bạn có thể đạt được hiệu suất truy vấn đáng kể. Đồng thời, vì lý do nào đó chỉ phần nhỏ Các ứng dụng cơ sở dữ liệu sử dụng tối ưu hóa điều kiện.

VÀ

Rõ ràng, trong một loạt các tuyên bố VÀ các điều kiện nên được sắp xếp theo thứ tự tăng dần xác suất đúng tình trạng này. Điều này được thực hiện để khi kiểm tra các điều kiện, cơ sở dữ liệu không kiểm tra phần còn lại của điều kiện. Những khuyến nghị này không áp dụng cho cơ sở dữ liệu Oracle, nơi các điều kiện được kiểm tra từ đầu. Theo đó, thứ tự của chúng nên được đảo ngược - theo thứ tự giảm dần về xác suất của sự thật.

HOẶC

Tình huống với toán tử này hoàn toàn ngược lại với tình huống với AND. Các điều kiện phải được sắp xếp theo thứ tự giảm dần về xác suất đúng. Công ty Microsoft thực sự khuyến khích sử dụng phương pháp này khi xây dựng các truy vấn, mặc dù nhiều người thậm chí còn không biết về nó hoặc ít nhất là không chú ý đến nó. Nhưng một lần nữa, điều này không áp dụng cho cơ sở dữ liệu Oracle, nơi các điều kiện phải được sắp xếp theo thứ tự tăng xác suất đúng.

Một điều kiện khác để tối ưu hóa có thể được coi là nếu cột giống hệt nhauđược đặt gần đó, yêu cầu được hoàn thành nhanh hơn. Ví dụ, yêu cầu ".. Ở ĐÂU cột1 = 1 HOẶC cột2 = 3 HOẶC cột1 = 2" sẽ thực thi chậm hơn yêu cầu "Ở ĐÂU cột1 = 1 HOẶC cột1 = 2 HOẶC cột2 = 3" . Ngay cả khi xác suất cột2 = 3 đúng cao hơn cột1 = 2.

Hồi đi học tôi đã được nghe về luật phân phối. Nó nói rằng MỘT VÀ (B HOẶC C)- giống như (A VÀ B) HOẶC (A VÀ C). Thực nghiệm cho thấy rằng một yêu cầu có dạng "...Ở ĐÂU cột1 = 1 VÀ (cột2 = "A" HOẶC cột2 = "B")" chạy nhanh hơn một chút so với "...Ở ĐÂU (cột1 = 1 VÀ cột2 = "A") HOẶC (cột1 = 1 VÀ cột2 = "B")" . Bản thân một số cơ sở dữ liệu có thể tối ưu hóa các truy vấn loại này, nhưng tốt hơn hết là phải an toàn.

KHÔNG

Thao tác này phải luôn được giảm xuống dạng “dễ đọc” hơn (tất nhiên là trong giới hạn hợp lý). Do đó, truy vấn "...WHERE NOT (column1 > 5)" được chuyển đổi thành "...Ở ĐÂU cột1<= 5" . Những điều kiện phức tạp hơn có thể được biến đổi bằng cách sử dụng quy tắc De Morgan mà lẽ ra bạn cũng phải học ở trường. Theo quy tắc này NOT(A AND B) = (NOT A) OR (NOT B) và NOT(A OR B) = (NOT A) AND (NOT B) . Ví dụ, điều kiện "...KHÔNG Ở ĐÂU (cột1 > 5 HOẶC cột2 = 7)" biến thành nhiều hơn mâu đơn giản: ...Ở ĐÂU cột1<= 5 AND column2 <> 7 .

TRONG

Nhiều người ngây thơ tin rằng yêu cầu "... Ở ĐÂU cột1 = 5 HOẶC cột1 = 6" tương đương với một yêu cầu "...Ở ĐÂU cột1 TRONG (5, 6)". Trên thực tế, điều này là không đúng sự thật. Thao tác IN nhanh hơn nhiều so với chuỗi OR. Do đó, bạn phải luôn thay thế OR bằng IN bất cứ khi nào có thể, mặc dù một số cơ sở dữ liệu tự thực hiện việc tối ưu hóa này. Khi sử dụng dãy số liên tiếp, IN phải được đổi thành GIỮA. Ví dụ, "...Ở ĐÂU cột1 TRONG (1, 3, 4, 5)" được tối ưu hóa để trông giống như: …Ở ĐÂU cột1 GIỮA 1 VÀ 5 VÀ cột1<> 2 . Và truy vấn này thực sự nhanh hơn.

GIỐNG

Thao tác này chỉ nên sử dụng khi thực sự cần thiết, vì sử dụng tìm kiếm dựa trên chỉ mục toàn văn sẽ tốt hơn và nhanh hơn. Thật không may, tôi phải hướng bạn tới World Wide Web để biết thông tin về tìm kiếm.

TRƯỜNG HỢP

Bản thân hàm này có thể được sử dụng để cải thiện tốc độ của một truy vấn khi nó có nhiều lệnh gọi đến một hàm chậm trong một điều kiện. Ví dụ: để tránh gọi lại Slow_function() trong truy vấn "...WHERE Slow_function(column1) = 3 HOẶC Slow_function(column1) = 5" , bạn cần sử dụng CASE:

... WHERE 1 = TRƯỜNG HỢP Slow_function(column1)

KHI 3 THÌ 1

KHI 5 THÌ 1

Maurice Lewis

Thật là một sự khác biệt lớn mà một chỉ mục có thể tạo ra về tốc độ thực hiện truy vấn! Mới đây, tác giả bài báo lại nhận được một xác nhận trực quan khác về sự thật này. Anh ấy đã giới thiệu một chỉ mục bổ sung và thời gian xử lý truy vấn giảm từ 40 phút xuống còn 12 giây. Nhưng chỉ mục chỉ là một trong những công cụ mà người quản trị cơ sở dữ liệu sử dụng để cải thiện hiệu năng hệ thống. Bạn có thể cải thiện hiệu suất bằng cách điều chỉnh nhiều tham số khác nhau - từ cấu hình phần cứng đến việc sử dụng các tiện ích cơ sở dữ liệu. Dưới đây là 9 cách hiệu quả nhất để cải thiện hiệu suất SQL Server 6.5.

1. Phân bổ cho máy chủ nhiều RAM nhất có thể.

Càng nhiều cơ sở dữ liệu có thể vừa với bộ nhớ đệm thì các truy vấn sẽ được xử lý càng nhanh. Vì vậy, nên tăng kích thước RAM tương ứng với kích thước của cơ sở dữ liệu. Ví dụ: nếu cơ sở dữ liệu của bạn chiếm 1 GB thì RAM 1 GB sẽ cho phép bạn chứa gần như toàn bộ cơ sở dữ liệu trong bộ nhớ. Nên để lại một ít RAM cho Windows NT. Tác giả ưu tiên để lại từ 64 MB đến 128 MB cho hệ điều hành và phân bổ phần bộ nhớ còn lại cho SQL Server. Và bạn chắc chắn cần giữ dung lượng bộ nhớ NT vật lý khả dụng ít nhất là 4 MB. Nếu nó giảm xuống dưới giới hạn quy định, NT sẽ ngay lập tức bắt đầu tạo các trang bộ nhớ ảo trên đĩa.

2. Sử dụng mảng RAID cấp 0 hoặc 5 để song song việc truy xuất thông tin từ cơ sở dữ liệu.

RAID cấp 0 và 5 phân phối yêu cầu đọc trên nhiều đĩa vật lý. Bạn có thể biết điều gì sẽ xảy ra trên các đoạn đường dẫn tới cầu trong giờ cao điểm, khi hàng nghìn ô tô cùng lúc cố gắng đi qua một khoảng không gian có chiều rộng hạn chế. Nút cổ chai tương tự cũng xảy ra đối với các yêu cầu đọc tệp từ thiết bị cơ sở dữ liệu của bạn. Nếu bạn có thể định tuyến dữ liệu qua nhiều kênh, máy chủ có thể đọc song song các khối dữ liệu từ mỗi ổ cứng. Trong trường hợp này, sự cải thiện hiệu suất gần như tuyến tính được quan sát thấy. Sự gia tăng thông lượng đọc này là do RAID cấp 0 và 5. Ví dụ: chúng ta hãy xem các con số từ cuốn sách Inside SQL Server 6.5 của Ron Saukup, do Microsoft Press xuất bản năm 1997. Ông viết rằng một ổ cứng 4GB có thể xử lý 80 đến 90 thao tác I/O mỗi giây. Đồng thời, một mảng RAID cấp 0 gồm 8 ổ cứng, mỗi ổ có dung lượng 500 MB (nghĩa là có cùng tổng dung lượng) thực hiện 400 thao tác I/O mỗi giây. Tất nhiên, vấn đề tăng chi phí vẫn còn bỏ ngỏ. Nhưng nói chung, càng có nhiều ổ cứng trong mảng thì thông lượng cơ sở dữ liệu cho các hoạt động đọc càng lớn.

3. Hãy để Max Async I/O tận dụng tối đa máy tính của bạn.

Hệ thống con đĩa của bạn có thể có khả năng xử lý hơn tám thao tác I/O không đồng bộ mỗi giây, nhiều hơn giá trị mặc định được áp dụng hơn ba năm trước khi SQL Server 6.5 được phát hành. Để tối ưu hóa cài đặt này, hãy tăng cài đặt này theo từng mức nhỏ trong khi giám sát bộ đếm độ dài hàng đợi đĩa trung bình, AvgDiskQueueLength, trong Trình theo dõi hiệu suất NT. Miễn là hàng đợi trung bình cho một hệ thống con đĩa không vượt quá gấp đôi số lượng đĩa trong đó, chúng tôi có thể cho rằng bạn không làm nó quá tải.

4. Đặt ngưỡng mở rộng khóa cho toàn bộ bảng.

Ba tham số leo thang khóa (LE) trên toàn bảng: Ngưỡng tối đa LE, Ngưỡng tối thiểu LE và Phần trăm ngưỡng LE, xác định số lượng trang SQL Server phải khóa trước khi toàn bộ bảng bị khóa. Giá trị mặc định cho các tham số này lần lượt là 200, 20 và 0. Đối với các bảng rất lớn, việc khóa toàn bộ bảng sẽ tránh được hàng nghìn khóa. Nếu cơ sở dữ liệu của bạn chứa hàng trăm bảng, việc loại bỏ chi phí này có thể tác động đáng kể đến hiệu suất.

5. Tạo các chỉ mục được nhóm cho các truy vấn đọc phạm vi giá trị.

Bởi vì các chỉ mục được nhóm tổ chức dữ liệu một cách vật lý bằng cách đặt nó theo thứ tự các giá trị trong chỉ mục, nên các chỉ mục đó cung cấp cơ sở tuyệt vời cho các truy vấn tìm kiếm phạm vi giá trị. Ví dụ: nếu một bảng chứa các bản ghi tài khoản cá nhân (hàng) có chỉ mục được nhóm trên cột ID tài khoản thì tất cả các hàng liên quan đến tài khoản 0001 sẽ nằm ở đầu bảng và tất cả các hàng cho tài khoản 9999 sẽ nằm ở sự kết thúc. Thứ tự này có nghĩa là nếu bảng này khớp với phần "nhiều" của mối quan hệ một-nhiều khi nối với một bảng khác, SQL Server có thể dễ dàng tìm thấy hàng đầu tiên có giá trị ID tài khoản mong muốn, sau đó di chuyển tuần tự qua bảng cho đến khi những cái đó cho đến khi giá trị định danh thay đổi.

6. Tạo chỉ mục không được nhóm cho các truy vấn tìm kiếm các giá trị duy nhất.

Không giống như các chỉ mục được nhóm, bao gồm toàn bộ nội dung của một trang, các chỉ mục cấp độ lá không được nhóm chỉ lưu trữ một bản ghi nhỏ cho toàn bộ hàng của bảng. Mục nhập chỉ mục bao gồm số trang và số hàng trong đó, do đó cho biết tọa độ của hàng trong bảng. Nếu truy vấn của bạn đang tìm kiếm thông tin từ một cột không phải là một phần của chỉ mục thì SQL Server phải lưu vào bộ đệm toàn bộ trang chứa hàng đó và tìm kiếm thông tin được lưu trữ trong hàng đó. Ưu điểm chính của các chỉ mục không được nhóm là chúng cho phép SQL Server tìm thấy các giá trị duy nhất rất nhanh chóng. Thông thường, các chỉ mục không được phân cụm được xây dựng trên các khóa chính vì giá trị của chúng là duy nhất trong định nghĩa khóa chính và do đó, các chỉ mục không được phân cụm rất phù hợp với chúng. Tuy nhiên, SQL Server không phải lúc nào cũng sử dụng các chỉ mục không được phân cụm ngay cả khi bạn đã tạo chúng. Do đó, chỉ xây dựng những chỉ mục hỗ trợ nhiều truy vấn và trả về một tỷ lệ rất nhỏ các hàng trong bảng.

7. Tạo các chỉ mục phức hợp để hỗ trợ nhiều truy vấn.

Trong trường hợp các bảng của bạn chủ yếu thực hiện CẬP NHẬT, CHÈN và đọc, việc giảm số lượng chỉ mục sẽ giảm chi phí bảo trì chỉ mục. Thao tác INSERT khiến SQL Server thêm bản ghi mới vào chỉ mục, trong khi thao tác UPDATE có thể khiến một hàng được di chuyển đến vị trí mới trong chỉ mục hoặc thậm chí đến một trang mới trong bảng. Hơn nữa, SQL Server thường thực hiện thao tác xóa dưới dạng một chuỗi gồm hai thao tác: đầu tiên, hàng cũ bị xóa và sau đó hàng mới được chèn vào. Xét về chi phí quản lý chỉ mục, đây là lựa chọn tồi tệ nhất. Giải pháp cho tình huống này là tạo các chỉ mục tổng hợp mà SQL Server có thể sử dụng cho nhiều truy vấn khác nhau.

8. Chỉ mục các cột được nối.

Khi bạn nối hai bảng, SQL Server sẽ tìm kiếm bảng bên trong tất cả các hàng có giá trị thỏa mãn điều kiện được tính toán dựa trên giá trị hiện tại ở bảng bên ngoài. Và SQL Server lặp lại tìm kiếm này cho từng hàng từ bảng bên ngoài. Nếu có một chỉ mục thì trước tiên SQL Server chỉ có thể chọn những hàng đáp ứng điều kiện nối. Khi kích thước của bảng bên trong lớn hơn vài lần so với kích thước của bảng bên ngoài, mức tăng trong thời gian thực hiện kết nối có thể tăng lên vài bậc. (Để biết thêm thông tin về các phép nối bảng, hãy xem bài viết "Các kỹ thuật THAM GIA nâng cao" của Itzik Ben-Gan và Kaylen Delaney, xuất bản trên tạp chí số tháng 12 năm 1999.) Chọn chỉ mục nào - phân cụm hay không phân cụm - chủ yếu phụ thuộc vào về việc các cột khác có trong danh sách CHỌN hay không. Nếu danh sách chỉ bao gồm những cột đang được nối thì tốt nhất nên sử dụng chỉ mục không được nhóm.

9. Tận dụng việc bao phủ các chỉ số.

Theo định nghĩa, chỉ mục bao phủ là chỉ mục chứa tất cả các cột được tham chiếu trong câu lệnh CHỌN, CẬP NHẬT hoặc XÓA. Yêu cầu sau đó được gọi là yêu cầu được bảo hiểm. Vì chỉ mục không được nhóm chứa mục nhập cấp độ lá cho mỗi hàng trong bảng nên tất cả thông tin cần thiết để chạy truy vấn đều có trong chỉ mục. Do đó, bộ xử lý truy vấn không thể quét một bảng lớn mà chỉ quét một chỉ mục nhỏ. Nói chung, nếu bạn có thể xây dựng một chỉ mục bao trùm, bạn sẽ ngay lập tức nhận thấy sự cải thiện đáng kể về hiệu suất truy vấn. Điều này là do chỉ mục không chứa toàn bộ hàng của bảng mà chỉ chứa một tập hợp con của nó. Tuy nhiên, mặt trái của vấn đề là việc thêm các cột bổ sung vào chỉ mục sẽ dẫn đến ít bản ghi phù hợp với trang chỉ mục hơn. Ngược lại, điều này làm cho không gian chỉ mục tăng lên và số lượng thao tác I/O cần thiết để đọc chỉ mục vào bộ đệm cũng tăng lên. Việc xây dựng các chỉ mục bao phủ là hợp lý miễn là tổng chiều dài của tất cả các cột có trong chỉ mục vẫn nhỏ hơn đáng kể so với chiều dài của hàng trong bảng.

Maurice Lewis là chủ tịch của Holitech, một công ty chuyên tư vấn và đào tạo về công nghệ Internet và phát triển cơ sở dữ liệu của Microsoft.