Hiệu suất bảng LIÊN KẾT MySQL

Amazon Athena là một dịch vụ truy vấn tương tác giúp dễ dàng phân tích dữ liệu trong Amazon Simple Storage Service (Amazon S3) bằng cách sử dụng SQL tiêu chuẩn. Athena không có máy chủ, vì vậy không có cơ sở hạ tầng để quản lý và bạn chỉ trả tiền cho các truy vấn mà bạn chạy. Vào năm 2019, Athena đã thêm hỗ trợ cho các truy vấn có liên kết để chạy các truy vấn SQL trên dữ liệu được lưu trữ trong các nguồn dữ liệu quan hệ, không quan hệ, đối tượng và tùy chỉnh

Vào năm 2021, Athena đã thêm hỗ trợ cho câu lệnh GIẢI THÍCH, câu lệnh này có thể giúp bạn hiểu và cải thiện hiệu quả của các truy vấn của mình. Câu lệnh EXPLAIN cung cấp bảng phân tích chi tiết về kế hoạch chạy của truy vấn. Bạn có thể phân tích kế hoạch để xác định và giảm độ phức tạp của truy vấn cũng như cải thiện thời gian chạy của nó. Bạn cũng có thể sử dụng EXPLAIN để xác thực cú pháp SQL trước khi chạy truy vấn. Làm như vậy sẽ giúp ngăn các lỗi có thể xảy ra trong khi chạy truy vấn

Athena cũng đã thêm PHÂN TÍCH GIẢI THÍCH, hiển thị chi phí tính toán của các truy vấn của bạn cùng với các kế hoạch chạy của chúng. Quản trị viên có thể hưởng lợi từ việc sử dụng EXPLAIN ANALYZE vì nó cung cấp số lượng dữ liệu được quét, giúp bạn giảm tác động tài chính do truy vấn của người dùng và áp dụng tối ưu hóa để kiểm soát chi phí tốt hơn

Trong bài đăng này, chúng tôi trình bày cách sử dụng và diễn giải các câu lệnh EXPLAIN và EXPLAIN ANALYZE để cải thiện hiệu suất truy vấn Athena khi truy vấn nhiều nguồn dữ liệu

Tổng quan về giải pháp

Để chứng minh bằng cách sử dụng các câu lệnh EXPLAIN và EXPLAIN ANALYZE, chúng tôi sử dụng các dịch vụ và tài nguyên sau

• Năm bảng cơ sở dữ liệu từ bộ dữ liệu Major League Baseball
• AWS CloudFormation để cung cấp tài nguyên AWS
• Amazon S3, Amazon DynamoDB và Amazon Aurora Phiên bản tương thích với MySQL làm bộ lưu trữ dữ liệu

Athena sử dụng AWS Glue Data Catalog để lưu trữ và truy xuất siêu dữ liệu bảng cho dữ liệu Amazon S3 trong tài khoản AWS của bạn. Siêu dữ liệu bảng cho phép công cụ truy vấn Athena biết cách tìm, đọc và xử lý dữ liệu mà bạn muốn truy vấn. Chúng tôi sử dụng trình kết nối nguồn dữ liệu Athena để kết nối với các nguồn dữ liệu bên ngoài Amazon S3

điều kiện tiên quyết

Để triển khai mẫu CloudFormation, bạn phải có những thứ sau

• Tài khoản AWS
• Người dùng AWS Identity and Access Management (IAM) có quyền truy cập vào các dịch vụ sau
  • amazon Athena
  • AWS CloudFormation
  • Amazon DynamoDB
  • Keo AWS
  • AWS Lambda
  • Dịch vụ cơ sở dữ liệu quan hệ của Amazon (Amazon RDS)
  • amazon S3
  • Đám mây riêng ảo Amazon (Amazon VPC)

Cung cấp tài nguyên với AWS CloudFormation

Để triển khai mẫu CloudFormation, hãy hoàn thành các bước sau

1. Chọn khởi chạy ngăn xếp

1. Làm theo lời nhắc trên bảng điều khiển AWS CloudFormation để tạo ngăn xếp
2. Lưu ý các cặp khóa-giá trị trên tab Đầu ra của ngăn xếp

Bạn sử dụng các giá trị này khi định cấu hình trình kết nối nguồn dữ liệu Athena

Mẫu CloudFormation tạo các tài nguyên sau

• Bộ chứa S3 để lưu trữ dữ liệu và hoạt động như bộ chứa tràn tạm thời cho Lambda
• Bảng AWS Glue Data Catalog dành cho dữ liệu trong bộ chứa S3
• Một bảng DynamoDB và các bảng Amazon RDS for MySQL, dùng để nối nhiều bảng từ các nguồn khác nhau
• Một VPC, mạng con và điểm cuối cần cho Amazon RDS cho MySQL và DynamoDB

Hình dưới đây cho thấy mô hình dữ liệu cấp cao cho tải dữ liệu

Tạo trình kết nối nguồn dữ liệu DynamoDB

Để tạo trình kết nối DynamoDB cho Athena, hãy hoàn tất các bước sau

1. Trên bảng điều khiển Athena, chọn Nguồn dữ liệu trong ngăn điều hướng
2. Chọn Tạo nguồn dữ liệu
3. Đối với Nguồn dữ liệu, hãy chọn Amazon DynamoDB
4. Chọn tiếp theo

1. Đối với Tên nguồn dữ liệu, hãy nhập DDB

1. Đối với hàm Lambda, chọn Tạo hàm Lambda

Thao tác này sẽ mở một tab mới trong trình duyệt của bạn

1. Đối với Tên ứng dụng, hãy nhập

   EXPLAIN (TYPE DISTRIBUTED)
   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."person" p, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   4
2. Đối với SpillBucket, hãy nhập giá trị từ ngăn xếp CloudFormation cho

   EXPLAIN (TYPE DISTRIBUTED)
   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."person" p, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   5
3. Đối với AthenaCatalogName, hãy nhập

   EXPLAIN (TYPE DISTRIBUTED)
   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."person" p, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   6
4. Để các giá trị còn lại ở mặc định của chúng
5. Chọn Tôi xác nhận rằng ứng dụng này tạo vai trò IAM tùy chỉnh và chính sách tài nguyên
6. Chọn triển khai

Bạn được đưa trở lại phần Kết nối nguồn dữ liệu trên bảng điều khiển Athena

1. Chọn biểu tượng làm mới bên cạnh chức năng Lambda
2. Chọn hàm Lambda mà bạn vừa tạo (

   EXPLAIN (TYPE DISTRIBUTED)
   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."person" p, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   6)

1. Chọn tiếp theo
2. Xem lại cài đặt và chọn Tạo nguồn dữ liệu
3. Nếu bạn chưa thiết lập vị trí kết quả truy vấn Athena, hãy chọn Xem cài đặt trên trang trình chỉnh sửa truy vấn Athena

1. Chọn Quản lý
2. Đối với Vị trí của kết quả truy vấn, duyệt đến bộ chứa S3 được chỉ định cho bộ chứa tràn Athena trong mẫu CloudFormation
3. Thêm truy vấn Athena vào đường dẫn S3
4. Chọn Lưu

1. Trong trình chỉnh sửa truy vấn Athena, đối với Nguồn dữ liệu, hãy chọn DDB
2. Đối với Cơ sở dữ liệu, chọn mặc định

Bây giờ bạn có thể khám phá lược đồ cho bảng

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

8;

1. Chọn biểu tượng tùy chọn cho bảng

   EXPLAIN (TYPE DISTRIBUTED)
   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."person" p, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   8 và chọn Xem trước Bảng

Tạo trình kết nối nguồn dữ liệu Amazon RDS cho MySQL

Bây giờ, hãy tạo trình kết nối cho Amazon RDS for MySQL

1. Trên bảng điều khiển Athena, chọn Nguồn dữ liệu trong ngăn điều hướng
2. Chọn Tạo nguồn dữ liệu
3. Đối với Nguồn dữ liệu, chọn MySQL
4. Chọn tiếp theo

1. Đối với Tên nguồn dữ liệu, hãy nhập MySQL

1. Đối với hàm Lambda, chọn Tạo hàm Lambda

1. Đối với Tên ứng dụng, hãy nhập

   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t, 
   "AwsDataCatalog"."athenablog"."person" p 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   0
2. Đối với SecretNamePrefix, hãy nhập

   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t, 
   "AwsDataCatalog"."athenablog"."person" p 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   1
3. Đối với SpillBucket, hãy nhập giá trị từ ngăn xếp CloudFormation cho

   EXPLAIN (TYPE DISTRIBUTED)
   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."person" p, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   5
4. Đối với DefaultConnectionString, hãy nhập giá trị từ ngăn xếp CloudFormation cho

   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t, 
   "AwsDataCatalog"."athenablog"."person" p 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   3
5. Đối với LambdaFunctionName, hãy nhập

   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t, 
   "AwsDataCatalog"."athenablog"."person" p 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   4
6. Đối với SecurityGroupIds, hãy nhập giá trị từ ngăn xếp CloudFormation cho

   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t, 
   "AwsDataCatalog"."athenablog"."person" p 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   5
7. Đối với SubnetIds, hãy nhập giá trị từ ngăn xếp CloudFormation cho

   SELECT t.id AS ticket_id, 
   e.eventid, 
   p.first_name 
   FROM 
   "DDB"."default"."sportseventinfo" e, 
   "AwsDataCatalog"."athenablog"."ticketinfo" t, 
   "AwsDataCatalog"."athenablog"."person" p 
   WHERE 
   t.sporting_event_id = cast(e.eventid as double) 
   AND t.ticketholder_id = p.id

   6
8. Chọn Tôi xác nhận rằng ứng dụng này tạo vai trò IAM tùy chỉnh và chính sách tài nguyên
9. Chọn triển khai

1. Trên bảng điều khiển Lambda, hãy mở chức năng bạn đã tạo (_______6_______4)
2. Trên tab Cấu hình, bên dưới Biến môi trường, chọn Chỉnh sửa

1. Chọn Thêm biến môi trường
2. Nhập một cặp khóa-giá trị mới
   • Đối với Khóa, hãy nhập

     SELECT t.id AS ticket_id, 
     e.eventid, 
     p.first_name 
     FROM 
     "DDB"."default"."sportseventinfo" e, 
     "AwsDataCatalog"."athenablog"."ticketinfo" t, 
     "AwsDataCatalog"."athenablog"."person" p 
     WHERE 
     t.sporting_event_id = cast(e.eventid as double) 
     AND t.ticketholder_id = p.id

     8
   • Đối với Giá trị, hãy nhập giá trị từ ngăn xếp CloudFormation cho

     SELECT t.id AS ticket_id, 
     e.eventid, 
     p.first_name 
     FROM 
     "DDB"."default"."sportseventinfo" e, 
     "AwsDataCatalog"."athenablog"."ticketinfo" t, 
     "AwsDataCatalog"."athenablog"."person" p 
     WHERE 
     t.sporting_event_id = cast(e.eventid as double) 
     AND t.ticketholder_id = p.id

     3
3. Chọn Lưu

1. Quay lại phần Kết nối nguồn dữ liệu trên bảng điều khiển Athena
2. Chọn biểu tượng làm mới bên cạnh chức năng Lambda
3. Chọn hàm Lambda bạn đã tạo (______16_______0

1. Chọn tiếp theo
2. Xem lại cài đặt và chọn Tạo nguồn dữ liệu
3. Trong trình chỉnh sửa truy vấn Athena, đối với Nguồn dữ liệu, chọn MYSQL
4. Đối với Cơ sở dữ liệu, chọn sportsdata

1. Chọn biểu tượng tùy chọn bên cạnh các bảng và chọn Xem trước Bảng để kiểm tra dữ liệu và lược đồ

Trong các phần sau, chúng tôi trình bày các cách khác nhau để tối ưu hóa các truy vấn của chúng tôi

Thứ tự tham gia tối ưu sử dụng kế hoạch EXPLAIN

Tham gia là một thao tác SQL cơ bản để truy vấn dữ liệu trên nhiều bảng bằng cách sử dụng quan hệ trên các cột phù hợp. Các thao tác nối ảnh hưởng đến lượng dữ liệu được đọc từ một bảng, lượng dữ liệu được chuyển đến các giai đoạn trung gian thông qua mạng và lượng bộ nhớ cần thiết để xây dựng bảng băm nhằm tạo điều kiện cho một phép nối.

Nếu bạn có nhiều thao tác nối và các bảng nối này không theo đúng thứ tự, thì bạn có thể gặp sự cố về hiệu suất. Để chứng minh điều này, chúng tôi sử dụng các bảng sau từ các nguồn khác nhau và nối chúng theo một thứ tự nhất định. Sau đó, chúng tôi quan sát thời gian chạy truy vấn và cải thiện hiệu suất bằng cách sử dụng tính năng GIẢI THÍCH từ Athena, tính năng này cung cấp một số gợi ý để tối ưu hóa truy vấn

Mẫu CloudFormation mà bạn đã chạy trước đó đã tải dữ liệu vào các dịch vụ sau

Bộ lưu trữ AWSTên bảngSố hàngAmazon DynamoDBsportseventinfo657Amazon S3person7,025,585Amazon S3ticketinfo2,488

Hãy xây dựng một truy vấn để tìm tất cả những người đã tham gia sự kiện theo loại vé. Thời gian chạy truy vấn với phép nối sau mất khoảng 7 phút để hoàn thành

SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

Bây giờ, hãy sử dụng EXPLAIN trên truy vấn để xem kế hoạch chạy của nó. Chúng tôi sử dụng cùng một truy vấn như trước, nhưng thêm giải thích (LOẠI PHÂN PHỐI)

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

Ảnh chụp màn hình sau đây cho thấy đầu ra của chúng tôi

Lưu ý liên kết chéo trong Đoạn 1. Các phép nối được chuyển đổi thành tích Descartes cho mỗi bảng, trong đó mọi bản ghi trong một bảng được so sánh với mọi bản ghi trong một bảng khác. Do đó, truy vấn này mất một khoảng thời gian đáng kể để hoàn thành

Để tối ưu hóa truy vấn của chúng tôi, chúng tôi có thể viết lại nó bằng cách sắp xếp lại thứ tự các bảng tham gia là

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

8 đầu tiên,

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

2 thứ hai và người cuối cùng. Lý do cho điều này là vì mệnh đề WHERE, đang được chuyển đổi thành mệnh đề THAM GIA BẬT trong giai đoạn lập kế hoạch truy vấn, không có mối quan hệ nối giữa bảng

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

3 và bảng

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

8. Do đó, trình tạo kế hoạch truy vấn đã chuyển đổi loại liên kết thành liên kết chéo (một sản phẩm của Cartesian), ít hiệu quả hơn. Việc sắp xếp lại các bảng sẽ sắp xếp mệnh đề WHERE thành loại INNER JOIN, đáp ứng mệnh đề THAM GIA BẬT và thời gian chạy giảm từ 7 phút xuống 10 giây

Mã cho truy vấn được tối ưu hóa của chúng tôi như sau

SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

Sau đây là đầu ra GIẢI THÍCH của truy vấn của chúng tôi sau khi sắp xếp lại mệnh đề nối

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

Ảnh chụp màn hình sau đây cho thấy đầu ra của chúng tôi

Phép nối chéo đã thay đổi thành INNER THAM GIA với phép nối trên các cột (

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

5,

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

6,

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

7), điều này dẫn đến truy vấn chạy nhanh hơn. Các liên kết giữa các bảng

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

2 và

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

3 được chuyển đổi thành loại phân phối PHẦN THAM GIA, trong đó cả hai bảng bên trái và bên phải đều được phân vùng băm trên tất cả các nút worker do kích thước của bảng

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

3. Phép nối giữa bảng

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

8 và

EXPLAIN (TYPE DISTRIBUTED) 
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."ticketinfo" t, 
"AwsDataCatalog"."athenablog"."person" p 
WHERE t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

2 được chuyển đổi thành loại phân phối REPLICATED, trong đó một bảng được phân vùng băm trên tất cả các nút công nhân và bảng còn lại được sao chép tới tất cả các nút công nhân để thực hiện thao tác nối

Để biết thêm thông tin về cách phân tích các kết quả này, hãy tham khảo Tìm hiểu kết quả câu lệnh Athena GIẢI THÍCH

Cách tốt nhất là chúng tôi khuyên bạn nên có câu lệnh THAM GIA cùng với mệnh đề BẬT, như được hiển thị trong đoạn mã sau

SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"AwsDataCatalog"."athenablog"."person" p 
JOIN "AwsDataCatalog"."athenablog"."ticketinfo" t ON t.ticketholder_id = p.id 
JOIN "ddb"."default"."sportseventinfo" e ON t.sporting_event_id = cast(e.eventid as double)

Ngoài ra, cách tốt nhất là khi bạn nối hai bảng, hãy chỉ định bảng lớn hơn ở phía bên trái của phép nối và bảng nhỏ hơn ở phía bên phải của phép nối. Athena phân phối bảng bên phải cho các nút công nhân, sau đó truyền bảng bên trái để thực hiện nối. Nếu bảng bên phải nhỏ hơn, thì sẽ sử dụng ít bộ nhớ hơn và truy vấn sẽ chạy nhanh hơn

Trong các phần sau, chúng tôi trình bày các ví dụ về cách tối ưu hóa các lần đẩy xuống cho các vị từ bộ lọc và hoạt động của bộ lọc phép chiếu cho nguồn dữ liệu Athena bằng cách sử dụng EXPLAIN ANALYZE

Tối ưu hóa đẩy xuống cho trình kết nối Athena cho Amazon RDS cho MySQL

Đẩy xuống là một tối ưu hóa để cải thiện hiệu suất của truy vấn SQL bằng cách di chuyển quá trình xử lý của nó càng gần dữ liệu càng tốt. Đẩy xuống có thể giảm đáng kể thời gian xử lý câu lệnh SQL bằng cách lọc dữ liệu trước khi truyền qua mạng và lọc dữ liệu trước khi tải vào bộ nhớ. Trình kết nối Athena cho Amazon RDS for MySQL hỗ trợ đẩy xuống cho biến vị ngữ bộ lọc và đẩy xuống phép chiếu

Bảng sau đây tóm tắt các dịch vụ và bảng mà chúng tôi sử dụng để minh họa quá trình đẩy xuống bằng Aurora MySQL

Tên bảngSố hàngKích thước tính bằng KBplayer_partitioned5,157318. 86sport_team_partitioned625. 32

Chúng tôi sử dụng truy vấn sau đây làm ví dụ về bộ lọc biến vị ngữ và phép chiếu

SELECT full_name,
name 
FROM "sportsdata"."player_partitioned" a 
JOIN "sportsdata"."sport_team_partitioned" b ON a.sport_team_id=b.id 
WHERE a.id='1.0'

Truy vấn này chọn người chơi và đội của họ dựa trên ID của họ. Nó phục vụ như một ví dụ về cả hoạt động lọc trong mệnh đề WHERE và phép chiếu vì nó chỉ chọn hai cột

Chúng tôi sử dụng EXPLAIN ANALYZE để biết chi phí cho việc chạy truy vấn này

EXPLAIN ANALYZE 
SELECT full_name,
name 
FROM "MYSQL"."sportsdata"."player_partitioned" a 
JOIN "MYSQL"."sportsdata"."sport_team_partitioned" b ON a.sport_team_id=b.id 
WHERE a.id='1.0'

Ảnh chụp màn hình sau đây hiển thị đầu ra trong Đoạn 2 cho bảng

SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"AwsDataCatalog"."athenablog"."person" p 
JOIN "AwsDataCatalog"."athenablog"."ticketinfo" t ON t.ticketholder_id = p.id 
JOIN "ddb"."default"."sportseventinfo" e ON t.sporting_event_id = cast(e.eventid as double)

3, trong đó chúng tôi quan sát thấy trình kết nối có bộ lọc kéo xuống thành công ở phía nguồn, do đó, nó chỉ cố quét một bản ghi trong số 5.157 bản ghi trong bảng. Đầu ra cũng cho thấy rằng quá trình quét truy vấn chỉ có hai cột (_______33_______4 là cột chiếu và

SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"AwsDataCatalog"."athenablog"."person" p 
JOIN "AwsDataCatalog"."athenablog"."ticketinfo" t ON t.ticketholder_id = p.id 
JOIN "ddb"."default"."sportseventinfo" e ON t.sporting_event_id = cast(e.eventid as double)

5 và cột tham gia), đồng thời sử dụng CHỌN và THAM GIA, điều này cho biết quá trình đẩy xuống chiếu thành công. Điều này giúp giảm bớt việc quét dữ liệu khi sử dụng bộ kết nối nguồn dữ liệu Athena

Bây giờ, hãy xem xét các điều kiện trong đó tính năng đẩy xuống vị ngữ bộ lọc không hoạt động với trình kết nối Athena

Câu lệnh LIKE trong các vị từ bộ lọc

Chúng tôi bắt đầu với truy vấn ví dụ sau để chứng minh bằng cách sử dụng câu lệnh THÍCH trong các vị từ bộ lọc

SELECT * 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name LIKE '%Aar%'

Sau đó, chúng tôi thêm PHÂN TÍCH GIẢI THÍCH

EXPLAIN ANALYZE 
SELECT * 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name LIKE '%Aar%'

Đầu ra EXPLAIN ANALYZE cho thấy rằng truy vấn thực hiện quét bảng (quét bảng

SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"AwsDataCatalog"."athenablog"."person" p 
JOIN "AwsDataCatalog"."athenablog"."ticketinfo" t ON t.ticketholder_id = p.id 
JOIN "ddb"."default"."sportseventinfo" e ON t.sporting_event_id = cast(e.eventid as double)

3, chứa 5.157 bản ghi) cho tất cả các bản ghi mặc dù mệnh đề WHERE chỉ có 30 bản ghi phù hợp với điều kiện %Aar%. Do đó, quá trình quét dữ liệu hiển thị kích thước bảng hoàn chỉnh ngay cả với mệnh đề WHERE

Chúng tôi có thể tối ưu hóa cùng một truy vấn bằng cách chỉ chọn các cột bắt buộc

EXPLAIN ANALYZE 
SELECT sport_team_id,
full_name 
FROM "MYSQL"."sportsdata"."player_partitioned" 
WHERE first_name LIKE '%Aar%'

Từ đầu ra EXPLAIN ANALYZE, chúng ta có thể quan sát thấy rằng trình kết nối hỗ trợ đẩy xuống bộ lọc chiếu, bởi vì chúng ta chỉ chọn hai cột. Điều này làm giảm kích thước quét dữ liệu xuống một nửa kích thước bảng

Câu lệnh OR trong các vị từ bộ lọc

Chúng tôi bắt đầu với truy vấn sau đây để chứng minh bằng cách sử dụng câu lệnh OR trong các vị từ bộ lọc

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

0

Chúng tôi sử dụng PHÂN TÍCH GIẢI THÍCH với truy vấn trước như sau

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

1

Tương tự như câu lệnh LIKE, đầu ra sau đây cho thấy rằng truy vấn đã quét bảng thay vì đẩy xuống chỉ các bản ghi khớp với mệnh đề WHERE. Truy vấn này chỉ xuất ra 16 bản ghi, nhưng quá trình quét dữ liệu cho thấy quá trình quét hoàn tất

Tối ưu hóa đẩy xuống cho trình kết nối Athena cho DynamoDB

Đối với ví dụ sử dụng trình kết nối DynamoDB, chúng tôi sử dụng dữ liệu sau

BảngSố hàngKích thước tính bằng KBsportseventinfo65785. 75

Hãy kiểm tra hoạt động của bộ lọc dự án và vị từ bộ lọc cho bảng DynamoDB của chúng ta bằng cách sử dụng truy vấn sau. Truy vấn này cố gắng lấy tất cả các sự kiện và thể thao cho một vị trí nhất định. Chúng tôi sử dụng EXPLAIN ANALYZE cho truy vấn như sau

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

2

Đầu ra của PHÂN TÍCH GIẢI THÍCH cho thấy rằng vị từ bộ lọc chỉ truy xuất 21 bản ghi và bộ lọc dự án chỉ chọn hai cột để đẩy xuống nguồn. Do đó, quét dữ liệu cho truy vấn này nhỏ hơn kích thước bảng

Bây giờ hãy xem vị trí đẩy xuống của bộ lọc không hoạt động ở đâu. Trong mệnh đề WHERE, nếu bạn áp dụng hàm TRIM() cho cột Vị trí rồi lọc, tối ưu hóa đẩy xuống vị từ không áp dụng, nhưng chúng ta vẫn thấy tối ưu hóa bộ lọc hình chiếu, áp dụng. Xem đoạn mã sau

EXPLAIN (TYPE DISTRIBUTED)
SELECT t.id AS ticket_id, 
e.eventid, 
p.first_name 
FROM 
"DDB"."default"."sportseventinfo" e, 
"AwsDataCatalog"."athenablog"."person" p, 
"AwsDataCatalog"."athenablog"."ticketinfo" t 
WHERE 
t.sporting_event_id = cast(e.eventid as double) 
AND t.ticketholder_id = p.id

3

Đầu ra của EXPLAIN ANALYZE cho truy vấn này cho thấy rằng truy vấn quét tất cả các hàng nhưng vẫn bị giới hạn chỉ ở hai cột, điều này cho thấy rằng vị từ bộ lọc không hoạt động khi áp dụng hàm TRIM

Từ các ví dụ trước, chúng ta đã thấy rằng trình kết nối nguồn dữ liệu Athena cho Amazon RDS dành cho MySQL và DynamoDB hỗ trợ các biến vị ngữ bộ lọc và biến vị ngữ phép chiếu để tối ưu hóa đẩy xuống, nhưng chúng ta cũng thấy rằng các thao tác như LIKE, OR và TRIM khi được sử dụng trong . Do đó, nếu bạn gặp phải các khoản phí không rõ nguyên nhân trong truy vấn Athena được liên kết của mình, chúng tôi khuyên bạn nên sử dụng EXPLAIN ANALYZE với truy vấn và xác định xem trình kết nối Athena của bạn có hỗ trợ thao tác kéo xuống hay không

Xin lưu ý rằng việc chạy EXPLAIN ANALYZE phát sinh chi phí vì nó quét dữ liệu

Phần kết luận

Trong bài đăng này, chúng tôi đã giới thiệu cách sử dụng EXPLAIN và EXPLAIN ANALYZE để phân tích truy vấn Athena SQL cho nguồn dữ liệu trên AWS S3 và truy vấn SQL liên kết Athena cho nguồn dữ liệu như DynamoDB và Amazon RDS for MySQL. Bạn có thể sử dụng điều này làm ví dụ để tối ưu hóa các truy vấn, điều này cũng sẽ giúp tiết kiệm chi phí

Việc sử dụng các bảng LIÊN KẾT trong MySQL là gì?

Công cụ LIÊN KẾT MySQL là gì?

Các tính năng của công cụ lưu trữ FEDERATED là gì?

Làm cách nào để tạo bảng LIÊN KẾT trong MySQL?