Điều chỉnh SQL với Optim Query Tuner, Phần 1: Tìm hiểu về các đường dẫn truy cập

Chia sẻ: Nhung Nhung | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST

Báo xấu

84
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Giới thiệu SQL là một ngôn ngữ khai báo theo nghĩa là chỉ mô tả dữ liệu quan trọng trong chương trình, không mô tả thuật toán để thu thập dữ liệu. Vì vậy, thường có nhiều cách để đáp ứng một câu lệnh SQL cụ thể. Những cách khác nhau này được gọi là các đường dẫn truy cập hoặc các kế hoạch truy cập. Để đơn giản, bài này đề cập đến một cách cụ thể để đáp ứng một câu lệnh SQL như là đường dẫn truy cập. Mặc dù các đường dẫn truy cập khác nhau...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Điều chỉnh SQL với Optim Query Tuner, Phần 1: Tìm hiểu về các đường dẫn truy cập

Điều chỉnh SQL với Optim Query Tuner, Phần 1: Tìm hiểu về các đường dẫn truy cập Giới thiệu SQL là một ngôn ngữ khai báo theo nghĩa là chỉ mô tả dữ liệu quan trọng trong chương trình, không mô tả thuật toán để thu thập dữ liệu. Vì vậy, thường có nhiều cách để đáp ứng một câu lệnh SQL cụ thể. Những cách khác nhau này được gọi là các đường dẫn truy cập hoặc các kế hoạch truy cập. Để đơn giản, bài này đề cập đến một cách cụ thể để đáp ứng một câu lệnh SQL như là đường dẫn truy cập. Mặc dù các đường dẫn truy cập khác nhau cho cùng một câu lệnh SQL tạo ra cùng một tập kết quả như nhau, thì chúng hầu như không thể thực hiện nhiệm vụ với một mức hiệu năng như nhau. Trình biên dịch SQL sử dụng tối ưu hóa truy vấn để chọn đường dẫn truy cập thực hiện tốt nhất cho bất kỳ câu lệnh SQL cụ thể nào (tất nhiên là trong một khoảng thời gian hợp lý). Nếu bạn là một người phát triển, người quản trị cơ sở dữ liệu (DBA), hoặc chuyên gia điều chỉnh truy vấn, điều quan trọng là bạn hiểu những điều cơ bản về các đường dẫn truy cập đến mức bạn có thể điều chỉnh đúng các truy vấn và tải truy vấn trước khi chúng gây ra các vấn đề trong môi trường sản xuất của bạn. Sự hiểu biết cơ bản này, cùng với sự hiển thị trực quan và lời khuyên điều chỉnh do các giải pháp điều chỉnh truy vấn Optim của IBM cung cấp, có thể giúp cho bạn hiệu quả hơn với nhiệm vụ này. Sau khi giới thiệu các đường dẫn truy cập và thực hiện truy vấn, bài này mô tả các phương thức truy cập phổ biến nhất và các phương thức nối được DB2® cho z/OS hỗ trợ. Tiếp theo, bài này mô tả một đường dẫn truy cập được trình tối ưu hóa DB2 cho z/OS chọn và duyệt qua biểu đồ đường dẫn truy cập để giải thích thông tin quan trọng liên quan đến việc chọn lựa đường dẫn truy cập. Bài này kết thúc với một kịch bản mẫu diễn tả cách sử dụng chú thích truy vấn, một tính năng trong Optim Query Tuner giúp bạn phân tích truy vấn bằng cách cung cấp thông tin thống kê quan trọng trực tiếp trong câu lệnh SQL. Nếu bạn muốn có cơ hội tự mình tương tác với các biểu đồ đường dẫn truy cập, được chứa trong một tệp dự án mẫu mà bạn có thể thu được từ phần tải về của bài này. Bạn có thể nhập tệp dự án vào Data Studio (gói độc lập với Fix Pack 1 hoặc mới hơn) hoặc bất kỳ một trong các sản phẩm Optim Query Tuner nào. Bạn không cần có một kết nối với cơ sở dữ liệu để tương tác với các kết quả phân tích, được gắn nhãn tương ứng với các số của hình vẽ trong bài này. Để nhập khẩu dự án mẫu, hãy làm như sau: 1. Mở Data Perspective (Phối cảnh dữ liệu) của sản phẩm độc lập Data Studio hoặc Optim Query Tuner của bạn. 2. Chọn File > Import.... 3. Trong Import Wizard (Trình hướng dẫn nhập khẩu), chuyển hướng đến Query Tuner > Projects, sau đó nhấn Next. 4. Nhấn Browse.. (Duyệt) và chọn thư mục chứa tệp zip tải về. Điều này làm xuất hiện một danh sách các dự án trong cửa sổ Projects (Các dự án).
5. Chọn sampleaccesspathproject và nhấn Finish (Kết thúc). 6. Bây giờ dự án mẫu sẽ xuất hiện trong Project Explorer (Trình thám hiểm dự án) của bạn. Nếu bạn không thấy Project Explorer Window, hãy đảm bảo bạn đang ở trong Data Perspective và chọn Window > Reset Perspective. Ngoài ra, bạn có thể chọn Window > Show View > Project Explorer. Về các giải pháp điều chỉnh truy vấn Optim Các giải pháp điều chỉnh truy vấn Optim cung cấp một môi trường để nhận biết và điều chỉnh việc thực hiện các câu lệnh SQL không chạy với các lời khuyên và các công cụ có thể trợ giúp hướng dẫn bạn đến một giải pháp. Các khả năng điều chỉnh truy vấn được cung cấp trong các sản phẩm sau:  Các khả năng định dạng truy vấn và điều chỉnh truy vấn đơn lẻ, cơ bản có sẵn trong bản Data Studio 2.2.1 (cả bản độc lập lẫn bản IDE). Sản phẩm này có sẵn miễn phí cho cả hai DB2 cho z/OS và DB2 cho Linux®, UNIX® và Windows®. Cần hiểu rõ rằng trong khi thông tin trong loạt bài này giải thích cách bạn có thể sử dụng Data Studio để giải thích các giản đồ đường dẫn truy cập, thì không phải tất cả các khả năng được mô tả có sẵn trong Data Studio.  Định dạng truy vấn và điều chỉnh truy vấn đơn lẻ, cũng như tập các trình tư vấn lớn hơn, có sẵn trong Optim Query Tuner. Sản phẩm này hiện có sẵn cho cả hai DB2 cho z/OS và DB2 cho Linux, UNIX, và Windows.  Điều chỉnh tải truy vấn, điều chỉnh truy vấn đơn lẻ, và toàn bộ tập các trình tư vấn có sẵn trong Optim Query Workload Tuner (Trình điều chỉnh tải truy vấn Optim). Sản phẩm này chỉ có sẵn cho DB2 cho z/OS (tại thời điểm viết bài này). Tóm lại, loạt bài này sử dụng tên Optim Query Tuner (Trình điều chỉnh truy vấn Optim) để nói đến tập các trình tư vấn và các công cụ mà các giải pháp điều chỉnh truy vấn Optim cung cấp. Ở đây các tên sản phẩm cụ thể, thích hợp được cung cấp khi mô tả các khả năng có thể không có sẵn trong tất cả các sản phẩm được liệt kê ở trên. Về đầu trang Cách đọc một biểu đồ đường dẫn truy cập Một biểu đồ đường dẫn truy cập không chỉ mô tả "các thông tin chi tiết hoạt động" về thực hiện truy vấn, mà nó còn mô tả cách dữ liệu di chuyển. Một nút lá của một biểu đồ đường dẫn truy cập hoặc là một nút bảng, một nút tệp công việc (workfile), hoặc một nút chỉ mục biểu diễn một nguồn dữ liệu trong kế hoạch thực hiện truy vấn (Hình 1 cho thấy các ví dụ về từng kiểu nút này). Dữ liệu di chuyển từ dưới cùng lên (như được hiển thị trong biểu đồ) và được các nút hoạt động trong biểu đồ đường dẫn truy cập xử lý. Bảng 1 mô tả đầu vào, đầu ra, và chức năng của các nút hoạt động thường xuất hiện trong một biểu đồ đường dẫn truy cập Optim Query Tuner. Bảng 1. Các hoạt động tiêu biểu được hiển thị trong Optim Query Tuner Nút hoạt Đầu vào Đầu vào Đầu ra Mô tả chức năng
động đầu tiên thứ hai Một tập các mã Quét các chỉ mục để lấy ra các mã định danh Nút chỉ định danh bản IXSCAN Không có đủ điều kiện trong một vùng khóa chỉ mục cụ mục ghi đủ điều kiện thể. (các RID) Tìm nạp các bản ghi dữ liệu và các trang dữ Một tập Một tập các bản FETCH Table node liệu tương ứng dựa trên các RID. Áp dụng các RID ghi đủ điều kiện các biến vị ngữ nếu có. Quét vùng bảng đích tuần tự để tìm nạp các Một tập các bản TBSCAN Nút bảng Không có trang dữ liệu và áp dụng các biến vị ngữ cho ghi đủ điều kiện các bản ghi nếu có. Một tập Một tập các bản Sắp xếp đầu vào theo thứ tự số trang (cho các các bản SORT Không có ghi đã sắp xếp RID) hoặc sắp xếp thứ tự khóa (với các bản ghi hoặc hoặc các RID ghi).. các RID Quét tệp công việc. Quét vùng bảng tệp công Nút tệp Một tập các bản việc đích một cách tuần tự để tìm nạp các WFSCAN Không có công việc ghi trang dữ liệu và áp dụng các biến vị ngữ cho các bản ghi nếu có. Phép nối vòng lặp lồng nhau. Với mỗi bản Một tập ghi đủ điều kiện từ đầu vào đầu tiên (bên Một tập các Một tập các bản NLJOIN các bản ngoài), quét đầu vào thứ hai (bên trong) để bản ghi ghi ghi tìm các bản ghi phù hợp và trả về các bản ghi đã nối. Một tập Một tập các Phép nối vòng lặp kết hợp. Quét cả hai đầu các bản Một tập các bản MSJOIN bản ghi đã vào để tìm các bản ghi phù hợp và trả về các ghi đã sắp ghi sắp xếp bản ghi đã nối. xếp Về đầu trang Các đường dẫn truy cập và thực hiện truy vấn Cách dễ nhất để mô tả các đường dẫn truy cập là dùng một ví dụ. Câu lệnh SQL trong Liệt kê 1 nối ba bảng để tạo ra một báo cáo bán hàng. Số tiền bán hàng được tổng hợp dựa trên trạng thái giới tính và tình trạng hôn nhân của khách hàng. Liệt kê 1. phép nối ba hướng mẫu được sử dụng để minh họa biểu đồ đường dẫn truy cập được hiển thị trong Hình 1 select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity) from cust_customer c, cust_order_header oh, cust_order_detail od where c.cust_code = oh.cust_code and oh.cust_order_number = od.cust_order_number and c.cust_prov_state_code = 'CA'
and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130) group by c.gender_code, c.marital_status_code Theo trực giác, một đường dẫn truy cập là một mô tả thủ tục về việc thực hiện truy vấn có ba thành phần:  Chuỗi nối của các bảng  Thuật toán để quét một bảng (phương thức truy cập)  Thuật toán để thực hiện một hoạt động nối (phương thức nối) Hình 1 là biểu đồ đường dẫn truy cập được tạo bởi Optim Query Tuner cho truy vấn được hiển thị trong Liệt kê 1. Hình 1. Biểu đồ đường dẫn truy cập mẫu với Liệt kê 1 (phép nối ba hướng) (Xem ảnh lớn hơn của Hình 1.) Mỗi một trong ba bảng được tham khảo trong truy vấn được thể hiện trong biểu đồ này như là các nút bảng với các tên bảng tương ứng của chúngs. Bảng đầu tiên trong chuỗi nối là bảng CUST_ORDER_DETAIL, được quét thông qua một chỉ mục được định nghĩa trên cột PRODUCT_NUMBER. Chỉ mục này được thể hiện trong biểu đồ bằng nút chỉ mục được gắn nhãn với tên chỉ mục là SQT01_CUST_ORDER_DETAIL.
Trực tiếp trên nút chỉ mục SQT01_CUST_ORDER_DETAIL là nút IXSCAN. Điều này có nghĩa là chỉ mục này được duyệt qua để lấy các mã định danh bản ghi đủ điều kiện (các RID). Sau đó nút FETCH lấy các RID đủ điều kiện để tìm nạp các bản ghi và các trang dữ liệu tương ứng từ vùng bảng trong nhóm bộ đệm DB2. Bảng thứ hai trong chuỗi nối là bảng CUST_ORDER_HEADER, được quét thông qua một chỉ mục trên cột nối có tên là CUST_ORDER_NUMBER. Phương thức truy cập với CUST_ORDER_HEADER, là quét chỉ mục, được biểu diễn theo cùng cách như bảng CUST_ORDER_DETAIL. Bảng cuối cùng trong chuỗi nối là bảng CUST_CUSTOMER, cũng được quét thông qua một chỉ mục được định nghĩa trên cột CUST_CODE. Bây giờ bạn có một cái nhìn tổng quan về các bảng và cách truy cập thông tin (trong trường hợp này là thông qua các chỉ mục), bạn đã sẵn sàng xem xét kỹ hơn các phương thức nối được sử dụng. Nút nối trong biểu đồ cho thấy rằng trình tối ưu hóa DB2 chọn một phép nối vòng lặp lồng nhau (NLJOIN) cho hoạt động nối đầu tiên giữa CUST_ORDER_DETAIL và CUST_ORDER_HEADER. Phép nối này lấy tập kết quả của bảng đầu tiên (CUST_ORDER_DETAIL) và kết quả của bảng thứ hai (CUST_ORDER_HEADER) làm các đầu vào. Sau đó kết quả của hoạt động nối đó sẽ trở thành đầu vào đầu tiên cho hoạt động nối thứ hai (cũng là một phép nối vòng lặp lồng nhau). Nó được nối tới tập kết quả của bảng cuối cùng trong chuỗi nối, CUST_CUSTOMER. Sự hiểu biết về dòng chảy dữ liệu cũng quan trọng bởi vì nó là yếu tố có ảnh hưởng nhất về khía cạnh hiệu năng truy vấn của việc thực hiện truy vấn. Phần còn lại của phần này sẽ hướng dẫn bạn thông qua biểu đồ đường dẫn truy cập thêm một lần nữa từ quan điểm dòng chảy dữ liệu để cho bạn có thể đạt được một sự hiểu biết đầy đủ về khía cạnh hiệu năng của việc thực hiện truy vấn. Số được hiển thị trong mỗi nút là đánh giá của trình tối ưu hóa về giá trị cardinality (số các yếu tố trong một tập hợp) của nguồn dữ liệu cơ bản hoặc nút hoạt động. Ví dụ, số 273 trên nút chỉ mục với SQT01_CUST_ORDER_DETAIL cho biết có 273 khóa chỉ mục riêng biệt. CUST_ORDER_HEADER_PK là một chỉ mục duy nhất; do đó, số lượng các khóa chỉ mục phù hợp chính xác với số lượng các bản ghi (539.526) trong bảng tương ứng. Điều này cũng đúng cho chỉ mục IDX_CUST_CUSTOMER và bảng CUST_CUSTOMER, tương ứng, với 31.284 khóa chỉ mục và các bản ghi. Đối với các chỉ mục không duy nhất, số lượng các khóa chỉ mục ít hơn số lượng các bản ghi. Hơn nữa, tỷ lệ giữa số lượng các bản ghi và số lượng các khóa chỉ mục cho biết, theo trung bình, số lượng các RID (các tập bản ghi có đủ điều kiện) có liên kết với mỗi khóa chỉ mục. Các số xuất hiện trong ba nút bảng trong biểu đồ cho biết kích thước của bảng theo số lượng các bản ghi. Dưới đây là những gì xảy ra trong thời gian thực hiện cho nhánh đầu tiên của phép nối vòng lặp lồng nhau:
1. Hoạt động quét chỉ mục (IXSCAN) trên chỉ mục BQT01_CUST_ORDER_DETAIL diễn ra đầu tiên, vì đó là hoạt động đầu tiên cho bảng đầu tiên trong chuỗi nối. Dựa trên đánh giá của trình tối ưu hóa, có tám RID đủ điều kiện trong vùng khóa chỉ mục đích. 2. Nút tìm nạp sử dụng tám RID đủ điều kiện này để xác định vị trí các bản ghi và trang tương ứng trong vùng bảng, có chứa 560273 bản ghi. Vì có không có biến vị ngữ bổ sung nào được hoạt động nạp áp dụng, nên giá trị cardinality đầu ra của nút tìm nạp cũng là tám. Điều này có nghĩa là sẽ có tám bản ghi đủ điều kiện do hoạt động tìm nạp tạo ra. 3. Sau đó lấy tám bản ghi đủ điều kiện này làm đầu vào đầu tiên cho nút NLJOIN ngay trên nút FETCH. Do cách các phép nối vòng lặp lồng nhau hoạt động, nên nhánh thứ hai (có bốn nút) được thực hiện cho mỗi bản ghi của bảng bên ngoài. Có nghĩa là, bảng bên trong của hoạt động nối sẽ được quét tám lần, một lần cho một trong tám bản ghi đủ điều kiện từ đầu vào bên ngoài. Nhánh thứ hai của nút NLJOIN là giống như nhánh thứ nhất của bảng đầu tiên: 1. Trình tối ưu hóa chọn một chỉ mục duy nhất (CUST_ORDER_HEADER_PK) để truy cập các bản ghi trong bảng CUST_ORDER_HEADER. 2. Như số lượng trong nút IXSCAN và nút FETCH chỉ ra, một bản ghi được tìm thấy trong CUST_ORDER_HEADER mỗi lần thực hiện quét chỉ mục. Do đó, nút NLJOIN sẽ tạo ra tổng cộng tám bản ghi. Tương tự như vậy, nhánh cuối cùng trong biểu đồ đường dẫn truy cập được đánh giá tám lần, một lần cho mỗi bản ghi được nút NLJOIN đầu tiên tạo ra. Vì chọn một chỉ mục duy nhất cho nút IXSCAN (IDX_CUST_CUSTOMER), nên chỉ có một bản ghi sẽ phù hợp trong mỗi lần quét của bảng CUST_CUSTOMER. Vì vậy, tổng cộng tám bản ghi được tạo ra trong tập kết quả cuối cùng. Theo quan điểm hiệu năng, một đường dẫn truy cập thực hiện tốt nên dùng đến số lượng dữ liệu ít nhất với tập kết quả cuối cùng. Trong ví dụ trên, số lượng các bản ghi được tìm nạp từ mỗi bảng được giữ ở mức tối thiểu. Đối với bảng CUST_ORDER_DETAIL, chỉ có tám bản ghi được tìm nạp từ vùng bảng bằng cách sử dụng quét chỉ mục. Ngoài ra, mỗi đầu dò trong đầu vào bên trong của cả hai hoạt động NLJOIN chỉ phù hợp với một bản ghi. Do đó, nếu đánh giá của trình tối ưu hóa là chính xác, đường dẫn truy cập này sẽ là một đường dẫn truy cập rất hiệu quả. (Phần Một nghiên cứu sâu hơn khi sử dụng chú thích truy vấn của bài này mô tả cách bạn có thể xác định xem đánh giá này có đúng không). Về đầu trang Các phương thức truy cập và các phương thức nối khác Trong phần trước, bạn đã tìm hiểu về một kiểu phương thức truy cập, là quét chỉ mục (IXSCAN), và một kiểu phương thức nối, là phép nối vòng lặp lồng nhau (NLJOIN). Phần này trình bày thêm một vài phương thức truy cập, các phương thức nối, và các hoạt động sắp xếp. Việc mô tả mỗi hoạt động bao gồm một biểu đồ đường dẫn Query Tuner để giải thích các ngữ nghĩa hoạt động của nó.
Quét vùng bảng (TBSCAN) với các biến vị ngữ giai đoạn-1 và giai đoạn-2 Câu lệnh SQL trong Liệt kê 2 tạo biểu đồ đường dẫn truy cập được hiển thị ở bên phải của ảnh chụp màn hình Optim Query Tuner trong Hình 2. Liệt kê 2. Truy vấn mẫu để minh họa quét bảng trong Hình 2 select count(*) from cust_order_header where cust_total_quantity > 3 and cust_sales_tax > cust_total * 0.03 Hình 2. Biểu đồ đường dẫn truy cập để quét bảng (TBSCAN) với các biến vị ngữ giai đoạn- 1 và giai đoạn-2 (Xem ảnh lớn hơn của Hình 2.) Các biến vị ngữ giai đoạn-1 và giai đoạn-2 (SARGable và số dư) Các biến vị ngữ giai đoạn-1 được áp dụng khi cột hoặc các cột, mà nó phụ thuộc, nằm trong nhóm bộ đệm. Các biến vị ngữ giai đoạn-2 được áp dụng khi cột hoặc các cột, mà nó phụ thuộc, được sao chép từ nhóm bộ đệm đến nhóm bộ nhớ riêng. Phần Tài nguyên có một liên kết đến một chủ đề trong trung tâm thông tin về DB2 cho z/OS nơi bạn có thể tìm thêm thông tin về chủ đề này.
Với DB2 cho Linux, UNIX, và Windows, các khái niệm cơ bản tương tự được biết đến là các biến vị ngữ tương ứng là SARGable và số dư. Hình 2 cho thấy một ví dụ về cách bạn có thể hiển thị các mô tả chi tiết của hai nút (bảng CUST_ORDER_HEADER và hoạt động quét bảng (TBSCAN)) từ biểu đồ đường dẫn truy cập. Bạn có thể nhận được các mô tả chi tiết từ bất kỳ nút nào trên biểu đồ chỉ bằng cách nhấn chuột phải vào nút đó và chọn Show Description (Hiển thị mô tả) từ trình đơn ngữ cảnh. Trong Hình 2, bộ mô tả cho nút bảng và bộ mô tả cho nút TBSCAN được hiển thị bên trái của biểu đồ đường dẫn truy cập. Câu lệnh SQL được hiển thị ở dưới cùng của biểu đồ này. Đường dẫn truy cập cho thấy rằng một hoạt động TBSCAN được thực hiện trên bảng CUST_ORDER_HEADER. Điều này có nghĩa là DB2 tìm nạp mọi trang dữ liệu trong vùng bảng một cách tuần tự vào nhóm bộ đệm. Sau đó nó quét mỗi bản ghi trong nhóm bộ đệm để áp dụng biến vị ngữ giai đoạn-1. Các bản ghi đủ điều kiện (chỉ các cột thích hợp) được sao chép vào một nhóm bộ nhớ riêng được một biến vị ngữ giai đoạn-2 lọc. Hàm tổng hợp, COUNT, được áp dụng cho các bản ghi còn lại sau khi biến vị ngữ giai đoạn-2 lọc. Có thể tìm thấy các chi tiết về dòng chảy dữ liệu trong phần các thuộc tính của bộ mô tả TBSCAN. Có 539.526 bản ghi được DB2 quét trong nhóm bộ đệm (giá trị (Input Cardinality trên bộ mô tả TBSCAN). Trình tối ưu hóa DB2 đánh giá rằng 473.785,44 bản ghi vẫn còn lại sau biến vị ngữ giai đoạn-1 (xem thuộc tính Stage 1 Returned Rows (Các hàng được trả về của giai đoạn-1) trong bộ mô tả) và 4.737,9688 bản ghi vẫn còn lại sau biến vị ngữ giai đoạn-2 (xem thuộc tính Stage 2 Returned Rows trong bộ mô tả). Chỉ có một bản ghi được tạo ra, do hàm COUNT. Thuộc tính Prefetch trong bộ mô tả chứa giá trị 'S', cho biết rằng việc tìm nạp trước tuần tự sẽ bắt đầu trong thời gian thực hiện để cải thiện hiệu năng vào/ra (I/O) cho việc tìm nạp các trang dữ liệu từ vùng bảng. Quét bảng (TBSCAN) với việc lược bớt phân vùng Câu lệnh SQL trong Liệt kê 3 tạo ra biểu đồ đường dẫn truy cập được hiển thị trong Hình 3. Liệt kê 3. Truy vấn mẫu để minh họa quét bảng với việc lược bớt phân vùng trong Hình 3 select count(*) from cust_order_header where cust_total_quantity > 3 and cust_sales_tax > cust_total * 0.03 and cust_order_number between 100000 and 580000
Hình 3. Biểu đồ đường dẫn truy cập để quét bảng (TBSCAN) với việc lược bớt phân vùng (Xem ảnh lớn hơn của Hình 3.) Các khóa hạn chế Các bảng, được phân vùng bởi các khóa, sử dụng các khóa giới hạn để chỉ ra các đường ranh giới của các phân vùng; một khóa giới hạn cho mỗi phân vùng. Như hiển thị trong bộ mô tả nút bảng, có 10 phân vùng trong vùng bảng, và khóa giới hạn cho phân vùng thứ chín là 625.000. Vì có một biến vị ngữ là cust_order_number between 100000 and 580000, trên một cột phân vùng, nên trình tối ưu hóa có thể giới hạn TBSCAN đến chín phân vùng đầu tiên. Sự tối ưu hóa này được mô tả trong bộ mô tả TBSCAN. Thuộc tính Page_Ranges chứa một vùng, vùng 1, bao trùm phân vùng 1 đến phân vùng 9. Trong lúc thực hiện, phân vùng 10 hoàn toàn bị bỏ qua để có hiệu năng tốt hơn. Quét chỉ mục (IXSCAN) với các biến vị ngữ phù hợp Câu lệnh SQL trong Liệt kê 4 tạo ra biểu đồ đường dẫn truy cập được hiển thị trong Hình 4. Liệt kê 4. Truy vấn mẫu để minh họa quét chỉ mục phù hợp trong Hình 4 select crdt_method_code, cust_total_quantity, count(*) from cust_order_header where cust_total_quantity - 2 > 1 and crdt_method_code > 20
and cust_order_date > '2007-01-01-01.24.58.017000' group by crdt_method_code, cust_total_quantity (Lưu ý rằng trong ví dụ trên, cột cust_order_date được định nghĩa với một kiểu là TIMESTAMP.) Hình 4. Biểu đồ đường dẫn truy cập để quét chỉ mục (IXSCAN) với các biến vị ngữ phù hợp (Xem ảnh lớn hơn của Hình 4.) Quét chỉ mục thường được thể hiện trong Query Tuner như là một nhóm có bốn nút trong biểu đồ đường dẫn truy cập. Như thể hiện trong Hình 4, CUST_ORDER_HEADER được truy cập thông qua việc quét chỉ mục phù hợp với chỉ mục BQT01_CUST_ORDER_HEADER, được định nghĩa trên cột CRDT_METHOD_CODE (bạn có thể thấy cột này bằng cách mở rộng bộ mô tả IXSCAN thêm một chút so với những gì được hiển thị trong Hình 4). Các chi tiết của bộ mô tả IXSCAN cho thấy rằng có một biến vị ngữ phù hợp với một hệ số bộ lọc 7,32% dẫn đến đánh giá là 114 trang chỉ mục và 3.9471 RID đủ điều kiện trong tổng số, tương ứng, 1.558 trang chỉ mục và 539.526 RID. Nút FETCH sử dụng các RID này để bố trí các trang dữ liệu tương ứng trong vùng bảng. Một khi đã tìm nạp vào trong các nhóm bộ đệm DB2, các bản ghi trong các trang này được quét và biến vị ngữ giai đoạn-1 được áp dụng. Mô tả chi tiết của nút FETCH cho thấy 18.515,602 bản ghi được đánh giá còn lại sau biến vị ngữ giai đoạn-1. Biến vị ngữ giai đoạn-2 được áp dụng sau khi
các cột có liên quan của các bản ghi này được sao chép vào bộ đệm riêng trong DB2. Cuối cùng, có 6.172,508 bản ghi được nút FETCH trả về. Quét chỉ mục không phù hợp (IXSCAN) Câu lệnh SQL trong Liệt kê 5 tạo ra biểu đồ đường dẫn truy cập được thể hiện trong Hình 5. Liệt kê 5. Truy vấn mẫu để minh họa quét chỉ mục không phù hợp trong Hình 5 select count(*) as count from cust_order_header Hình 5. Biểu đồ đường dẫn truy cập để quét chỉ mục không phù hợp (IXSCAN) (Xem ảnh lớn hơn của Hình 5.) Việc quét chỉ mục có thể không phù hợp, có nghĩa là không có hạn chế nào trong việc quét chỉ mục cơ bản. Đó là, DB2 quét tất cả các trang lá đơn. Thông thường, trình tối ưu hóa chọn quét chỉ mục không phù hợp khi chỉ mục cơ bản cung cấp các lợi ích như truy cập chỉ theo chỉ mục, tránh sắp xếp, hoặc một số lợi ích tương tự. Trong ví dụ được chỉ ra trong Hình 5, trình tối ưu hóa chọn khai thác quét chỉ mục không phù hợp vì hàm tổng hợp, count(*), có thể được đánh giá bằng cách chỉ quét chỉ mục. Ngoài ra, chỉ mục thường nhỏ hơn so với bảng và do đó, quét sẽ hiệu quả hơn. Việc mô tả chi tiết nút IXSCAN cho thấy rằng tất cả 1.680 trang lá được quét do thiếu một biến vị ngữ phù hợp. Không
có nút FETCH hoặc nút bảng nào được hiển thị trong biểu đồ đường dẫn truy cập vì nó là quét chỉ theo chỉ mục. Phép nối vòng lặp lồng nhau (NLJOIN) Câu lệnh SQL trong Liệt kê 6 tạo ra biểu đồ đường dẫn truy cập được hiển thị trong Hình 6. Liệt kê 6. Truy vấn mẫu để minh họa phép nối vòng lặp lồng nhau trong Hình 6 select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price) from cust_order_header oh, cust_order_detail od where oh.cust_order_number = od.cust_order_number and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130) group by oh.cust_code Hình 6. Biểu đồ đường dẫn truy cập với phép nối vòng lặp lồng nhau (NLJOIN) (Xem ảnh lớn hơn của Hình 6.) Các phép nối vòng lặp lồng nhau (NLJOIN) lấy hai nguồn dữ liệu làm đầu vào, nối các bản ghi từ bên ngoài (phía bên trái) với các các bản ghi phù hợp ở bên trong (phía bên phải), để giúp các biến vị ngữ nối đủ điều kiện, và sau đó trả về các bản ghi đã nối làm đầu ra. Để biết thêm thông tin về các phép nối vòng lặp lồng nhau, tham khảo liên kết của trung tâm thông tin thích hợp trong phần Tài nguyên để biết nền tảng DB2 của bạn.
Như thể hiện trong biểu đồ đường dẫn truy cập của truy vấn mẫu trong Hình 6, các bản ghi đủ điều kiện từ bảng bên ngoài (CUST_ORDER_DETAIL) được nối tới các bản ghi đủ điều kiện của bảng bên trong (CUST_ORDER_HEADER) bằng cách sử dụng NLJOIN và một biến vị ngữ nối EQUAL. Trong lúc thực hiện, đối với mỗi bản ghi di chuyển từ nút FETCH ở phía bên ngoài của phép nối, bảng bên trong được quét bằng cách sử dụng quét chỉ mục (IXSCAN) theo chỉ mục CUST_ORDER_HEADER_PK. Thông tin chi tiết trong bộ mô tả NLJOIN (cũng được hiển thị trong Hình 6) cho thấy rằng theo đánh giá của trình tối ưu hóa:  Có tám bản ghi đủ điều kiện sau khi áp dụng biến vị ngữ cục bộ (giá trị Cardinality của đầu vào ngoài là 8).  Một bản ghi của bảng bên trong sẽ phù hợp với mỗi bản ghi đủ điều kiện từ bảng bên ngoài (giá trị Cardinality của đầu vào bên trong là 1).  Nút NLJOIN cuối cùng sẽ tạo ra tám bản ghi đã nối làm đầu ra (giá trị Cardinality đầu ra là 8). Từ góc độ hiệu năng, điều này được coi là một hoạt động của phép nối rất hiệu quả, vì chi phí cho việc quét bảng bên trong là tối ưu qua việc truy cập duy nhất thông qua một chỉ mục duy nhất. Phép nối vòng lặp lồng nhau (NLJOIN) với hợp tử sắp xếp Lưu ý rằng câu lệnh SQL trong Liệt kê 7 giống với một câu lệnh trong Liệt kê 6 ở phần trước. Tuy nhiên, với ví dụ này lại chọn một đường dẫn truy cập khác, được hiển thị trong Hình 7. Liệt kê 7. Truy vấn mẫu để minh họa phép nối vòng lặp lồng nhau với hợp tử sắp xếp trong Hình 7 select oh.cust_code, sum(od.cust_quantity * od.cust_unit_price) from cust_order_header oh, cust_order_detail od where oh.cust_order_number = od.cust_order_number and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130) group by oh.cust_code Hình 7. Biểu đồ đường dẫn truy cập cho phép nối vòng lặp lồng nhau (NLJOIN) với hợp tử
sắp xếp (Xem ảnh lớn hơn của Hình 7.) Sự khác biệt giữa Hình 6 và Hình 7 là trong Hình 7 có thêm vào một nút SORT ở phía bên ngoài của phép nối. Kiểu đường dẫn truy cập này được gọi là một phép nối vòng lặp lồng nhau (NLJOIN) với hợp tử sắp xếp. Phần sau giải thích tại sao trình tối ưu hóa lại thêm một nút SORT. Lưu ý rằng có sự khác biệt đáng kể giữa giá trị cardinality được hiển thị trong nút fetch ở phía bên ngoài của phép nối trong Hình 7. Ví dụ trong Hình 6 chỉ có tám bản ghi, nhưng trong Hình 7, trình tối ưu đánh giá số lượng các các bản ghi đủ điều kiện là 8.708,988. Giá trị cardinality cao hơn, thì càng có nhiều đầu dò vào bảng bên trong hơn, một đầu dò cho mỗi bản ghi (bên ngoài) đủ điều kiện. Chỉ mục được khai thác cho bảng bên trong ngẫu nhiên là một chỉ mục được phân cụm. Do đó, thứ tự của các đầu dò liên tiếp vào bảng bên trong có thể ảnh hưởng đáng kể đến hiệu năng vào/ra (I/O) của bảng bên trong. Chính xác hơn, nếu các đầu dò liên tiếp của bảng bên trong theo thứ tự CUST_ORDER_NUMBER, thì chỉ mục bên trong sẽ được quét tuần tự qua các đầu dò khác nhau, lần lượt, các đầu dò đó sẽ đáp ứng quét liên tiếp bảng bên trong. Lợi ích thu được từ I/O hiệu quả hơn có tác động nhiều hơn đến chi phí cho việc sắp xếp khoảng 8.708 bản ghi. Vì vậy, trong Hình 7 trình tối ưu hóa chọn sắp xếp hợp tử cho phép nối vòng lặp lồng nhau. Nói chung, trình tối ưu hóa tạo thuận lợi cho một phép nối vòng lặp lồng nhau với hợp tử sắp xếp khi chỉ mục bên trong được phân cụm đầy đủ và hy vọng bảng bên trong được thăm dò nhiều lần. Phép nối quét kết hợp (MSJOIN) có sắp xếp với bên ngoài và bên trong
Câu lệnh SQL trong Liệt kê 8 tạo ra biểu đồ đường dẫn truy cập được hiển thị trong Hình 8. Liệt kê 8. Truy vấn mẫu để minh họa phép nối quét kết hợp trong Hình 8 select * from cust_order_header oh, cust_order_detail od where oh.cust_total_quantity = od.cust_quantity and oh.cust_total = od.cust_unit_price and oh.cust_order_date > '2009-01-16-01.00.00.000000' and od.product_number > 150000 (Lưu ý rằng trong ví dụ trên, cột cust_order_date được định nghĩa với một kiểu TIMESTAMP.) Hình 8. Biểu đồ đường dẫn truy cập cho phép nối quét kết hợp (MSJOIN) có sắp xếp với bên ngoài và bên trong (Xem ảnh lớn hơn của Hình 8.) Phép nối quét kết hợp cũng được biết đến là một phép nối kết hợp có sắp xếp. Từ viết tắt MSJOIN được sử dụng trong Optim Query Tuner để chỉ rõ thuật toán nối này. Để biết thêm thông tin về các phép nối quét kết hợp, hãy tham khảo liên kết trung tâm thông tin thích hợp trong phần Tài nguyên cho nền tảng DB2 của bạn. Một phép nối quét kết hợp luôn liên quan đến một hoặc nhiều biến vị ngữ Equal (bằng nhau). Hình 8 cho biết có hai biến vị ngữ nối Equal được khai thác với phép nối quét kết hợp của truy
vấn mẫu. Đối với phép nối quét kết hợp để thực hiện đúng, hy vọng cả hai đầu vào theo thứ tự của các cột nối. Chính xác hơn, với bảng bên ngoài của phép nối (CUST_ORDER_DETAIL), các bản ghi đủ điều kiện phải theo thứ tự của các cột CUST_QUANTITY và CUST_UNIT_PRICE. Tương tự như vậy, các bản ghi đủ điều kiện của bảng bên trong phải theo thứ tự CUST_TOTAL_QUANTITY và CUST_TOTAL. Vì bảng bên trong được quét bằng TBSCAN, không đảm bảo thứ tự dự kiến với MSJOIN, nên thêm vào một nút SORT để bắt tuân theo thứ tự này Như cho thấy trong bộ mô tả IXSCAN trong Hình 8, cột hàng đầu của chỉ mục được trình tối ưu hóa khai thác là PRODUCT_NUMBER, không cung cấp thứ tự cần thiết cho phép nối quét kết hợp. Do đó, một nút SORT cũng được thêm vào bên ngoài của phép nối để bắt tuân theo thứ tự này. Như đã nói ở trên, đầu vào có thứ tự là cần thiết cho một phép nối quét kết hợp, nhưng lại không cần thiết cho phép nối vòng lặp lồng nhau. Vì vậy, khi so sánh với một phép nối vòng lặp lồng nhau, phép nối quét kết hợp có thể phải chịu chi phí sắp xếp các đầu vào. Tuy nhiên, lợi ích của phép nối quét kết hợp là bảng bên trong không phải bị quét nhiều lần như nó bị quét với các phép nối vòng lặp lồng nhau. Vì vậy, khi quyết định chọn phương thức nối nào, trình tối ưu hóa cân đối chi phí sắp xếp và lợi ích hiệu năng cho việc quét bảng bên trong để điều chỉnh quyết định này. Một điều đáng chú ý về phép nối quét kết hợp là trình tối ưu hóa sẽ hết sức tránh SORT bằng cách theo dõi "đặc tính thứ tự" của dữ liệu. Ví dụ, có thể tránh SORT trên bảng bên trong nếu bảng bên trong được quét thông qua một chỉ mục với CUST_TOTAL và CUST_TOTAL_QUANTITY làm các cột hàng đầu. Thông thường, trình tối ưu hóa chọn phép nối quét kết hợp khi giá trị cardinality của đầu vào bên ngoài rõ ràng không nhỏ và không có phương thức truy cập hiệu quả nào cho bảng trong. Về đầu trang Một nghiên cứu sâu khi sử dụng chú thích truy vấn Phần này minh họa cách chú thích truy vấn Query Tuner và biểu đồ đường dẫn truy cập có thể giúp bạn phân tích các đường dẫn truy cập và hiệu năng SQL. Liệt kê 9 có chứa một câu lệnh SQL của phép nối ba hướng giống như đã được sử dụng trong Liệt kê 1 để giới thiệu khái niệm về các đường dẫn truy cập và thực hiện truy vấn. Liệt kê 9. Truy vấn mẫu để minh họa các khả năng chú thích truy vấn được hiển thị trong Hình 9 select c.gender_code, c.marital_status_code, sum(od.unit_cost * cust_quantity) from cust_customer c, cust_order_header oh, cust_order_detail od where c.cust_code = oh.cust_code and oh.cust_order_number = od.cust_order_number and c.cust_prov_state_code = 'CA' and od.product_number in (154110, 129170, 129150, 129110, 129140, 130130) group by c.gender_code, c.marital_status_code
Hình 9 minh họa hàm chú thích truy vấn trong Optim Query Tuner, định dạng một câu lệnh SQL sao cho mỗi tham khảo bảng trong mệnh đề FROM và mỗi biến vị ngữ trong mệnh đề WHERE lấy một dòng mới. Các biến vị ngữ được sắp xếp lại và được tạo lại nhóm dựa trên loại biến vị ngữ (cục bộ hoặc phép nối) và tham khảo bảng. Hình 9. Ví dụ định dạng truy vấn và chú thích (Xem ảnh lớn hơn của Hình 9.) Hình 9 thể hiện cách các hàm định dạng và chú thích tạo cho các câu lệnh cơ bản dễ hiểu hơn. Ba bảng tham khảo (CUST_ORDER_DETAIL, CUST_CUSTOMER, và CUST_ORDER_HEADER) được hiển thị trên ba dòng khác nhau. Mỗi một trong bốn biến vị ngữ cũng được hiển thị trên các dòng khác nhau, với hai biến vị ngữ cục bộ được hiển thị trước hai biến vị ngữ nối. Ngoài định dạng dễ đọc, bạn cũng có thể truy cập vào chú thích nhằm cung cấp thông tin thống kê quan trọng để làm cho việc phân tích hiệu năng của SQL của bạn dễ dàng hơn. Giá trị cardinality bảng (về cả số các bản ghi lẫn số các trang) và số đánh giá của các hàng đủ điều kiện là theo quyền của mỗi tham khảo bảng. Số có liên quan đến QUALIFIED_ROWS biểu diễn số đánh giá của các bản ghi sẽ giúp tất cả các biến vị ngữ cục bộ của tham khảo bảng cơ bản đủ điều kiện. Ví dụ, bảng CUST_ORDER_DETAIL được đánh giá để trả về 8,000001 trong số 560.273 bản ghi sau khi áp dụng các biến vị ngữ cục bộ. Tương tự như vậy, chỉ có 729,99976 trong số 31.284 bản ghi sẽ giúp các biến vị ngữ cục bộ cho bảng CUST_CUSTOMER đủ điều kiện. Vì không có các biến vị ngữ cục bộ nào cho CUST_ORDER_HEADER, nên tất cả 539.526 hàng sẽ tồn tại. Thông tin
này có thể dẫn đến hiểu biết về cách chọn một bảng hàng đầu trong một chuỗi nối — số các hàng đủ điều kiện càng nhỏ, thì càng có nhiều khả năng là trình tối ưu hóa sẽ chọn bảng đó làm bảng hàng đầu trong chuỗi nối. Như trong Hình 1, trình tối ưu chọn bảng CUST_ORDER_DETAIL làm bảng hàng đầu của chuỗi nối vì nó phải tạo chỉ tám bản ghi sau khi áp dụng các biến vị ngữ cục bộ. Trong đường dẫn truy cập, tham khảo bảng thứ hai (CUST_ORDER_HEADER) và tham khảo bảng thứ ba (CUST_CUSTOMER) trong chuỗi nối được truy cập thông qua chỉ mục duy nhất phù hợp hoàn toàn. Đó là, mỗi đầu dò trong tham khảo bảng thứ hai và tham khảo bảng thứ ba sẽ định vị có hiệu quả bản ghi duy nhất phù hợp với điều kiện nối. Vì vậy, đường dẫn truy cập này thực sự có thể là một đường dẫn thực hiện tốt nhất miễn là đánh giá với QUALIFIED_ROWS của tham khảo bảng đầu tiên (CUST_ORDER_DETAIL) là chính xác. Để hiểu được vấn đề về cách trình tối ưu hóa tính toán QUALIFIED_ROWS và nó chính xác như thế nào, hãy xem lại Hình 9. Lưu ý là chỉ có một biến vị ngữ cục bộ cho CUST_ORDER_DETAIL, là một biến vị ngữ danh sách IN trên cột PRODUCT_NUMBER, có sáu phần tử trong danh sách (od.product_number trong (154110, 129170, 129150, 129110, 129140, 130130)). Chú thích truy vấn cho biến vị ngữ này cho thấy hệ số lọc của biến vị ngữ (FF) là 0,000014, hoặc 0,0014%. Đó là, tỷ lệ phần trăm của các hàng được đánh giá sẽ giúp biến vị ngữ này đủ điều kiện là 0,0014%. Đánh giá với QUALIFIED_ROWS thu được bằng cách nhân giá trị cardinality bảng (560.273 bản ghi) với hệ số lọc của biến vị ngữ (0,0014%). Để hiểu được cách trình tối ưu hóa kết thúc với hệ số lọc là 0,0014%, trước tiên hãy nhìn vào sáu phần tử trong biến vị ngữ danh sách IN. Chú thích truy vấn cho biến vị ngữ này cho thấy có 273 giá trị khác nhau trên cột PRODUCT_NUMBER. Giả sử phân bố đồng đều, trình tối ưu hóa thông thường sẽ kết luận rằng 2,197% (6/273 = 0,02197) các bản ghi sẽ giúp biến vị ngữ này đủ điều kiện. Nhưng thay vào đó, nó đang sử dụng là 0,0014%. Điều này xảy ra với một độ chọn lọc của biến vị ngữ tốt hơn nhiều so với giá trị hiện có của nó! Để hiểu điều này, hãy xem MAXFREQ trong chú thích truy vấn cho biến vị ngữ này. Khi thu thập số liệu thống kê cần thiết và có sẵn, MAXFREQ cho biết tần số của giá trị xuất hiện thường xuyên nhất trên cột. Trong ví dụ này, đó là 3,32%, cao hơn nhiều so với tỷ lệ phần trăm trung bình cho mỗi giá trị (1/273 = 0,366%). Điều này cho thấy rằng một số trong 273 giá trị cũng có thể có sai lệch thấp. Số liệu thống kê có sẵn trên cột này có thể giúp tiết lộ những gì thực sự sẽ xảy ra. Bạn có thể thu được số liệu thống kê từ bộ mô tả bảng của nút bảng trong biểu đồ đường dẫn truy cập cơ bản. Để xem bộ mô tả bảng, nhấn chuột phải vào nút bảng trong biểu đồ đường dẫn truy cập. Hình 10 cho thấy bộ mô tả bảng CUST_ORDER_DETAIL.
Hình 10. Một đoạn biểu đồ đường dẫn truy cập được hiển thị trong Hình 1 (Xem ảnh lớn hơn của Hình 10.) Bằng cách truy vấn ngược xuống số liệu thống kê cột trong bộ mô tả bảng, bạn có thể phát hiện ra rằng sáu phần tử trong biến vị ngữ danh sách IN ngẫu nhiên là sáu giá trị ở dưới cùng trong số liệu thống kê tần số. Khi thêm các giá trị tần số này, bạn kết thúc với cùng hệ số bộ lọc (được đánh giá) chính xác là 0,0014%, với biến vị ngữ danh sách IN. Vì vậy, điều này giải thích cách trình tối ưu đã xác định hệ số lọc là 0,0014%. Ngoài ra, lưu ý rằng dấu thời gian để thu thập số liệu thống kê được hiển thị ở dưới cùng của bộ mô tả bảng cho thấy một ngày gần nhất liên quan đến thời gian giải thích truy vấn này. Đánh giá của trình tối ưu hóa có thể rất gần với giá trị thực tế miễn là việc phân phối dữ liệu đã không thay đổi kể từ RUNSTATS cuối cùng. Giả sử đây là trường hợp nghiên cứu này, đường dẫn truy cập cơ bản được coi là tối ưu với độ tin cậy cao. Về đầu trang Kết luận Bài này mô tả các khái niệm cơ bản về các đường dẫn truy cập và cách đọc một biểu đồ đường dẫn truy cập. Các khả năng biểu đồ đường dẫn truy cập và chú thích truy vấn của Optim Query Tuner đã được sử dụng để xem xét các truy vấn thực tế và giải thích cách và lý do trình tối ưu hóa lại chọn các đường dẫn truy cập cụ thể. Thông tin này sẽ cung cấp các khối xây dựng cần thiết cho bạn để bắt đầu với việc điều chỉnh các truy vấn. Các bài sau của loạt bài này sẽ cung cấp thêm thông tin về các phương pháp luận mà bạn có thể sử dụng để điều chỉnh truy vấn.