กลยุทธ์การเพิ่มประสิทธิภาพคิวรี

BigQuery เบื้องต้น

Matt Forrest

Field CTO

หลักการสามข้อ

มีหลักการเพิ่มประสิทธิภาพหลัก 3 ข้อ:

ลดปริมาณข้อมูลที่ต้องนำมาประมวลผล
เพิ่มประสิทธิภาพการดำเนินการในคิวรี
ลดขนาดผลลัพธ์ที่ได้จากคิวรี

¹ https://cloud.google.com/bigquery/docs/best-practices-performance-compute#use-bi-engine

การลดปริมาณข้อมูล

หลีกเลี่ยงการใช้ SELECT * และเลือกเฉพาะคอลัมน์ที่ต้องการ
กรองข้อมูลใน CTE ตั้งแต่เนิ่นๆ และทำบ่อยๆ
ใช้คำสั่ง WHERE กรองข้อมูลตั้งแต่เนิ่นๆ และทำบ่อยๆ

การเพิ่มประสิทธิภาพ JOIN

ใช้ CTE เพื่อลดข้อมูลที่ต้องการก่อน
Join โดยใช้ชนิดข้อมูล INT64

WITH filter_my_data AS (SELECT 
-- Filter data with 
-- WHERE in the CTE first
)
SELECT 
-- This query will run 
-- faster with less data
JOIN a USING (user_id)

การเพิ่มประสิทธิภาพ WHERE

ใน BigQuery ให้ใช้
- BOOL
- INT
- FLOAT
- DATE
ชนิดข้อมูลที่ใช้กับ WHERE แบบ STRING หรือ BYTE ไม่เหมาะสม

ไม่เหมาะสม

SELECT user_id, date_ordered
FROM dataset.table
WHERE product = 'shoes'

เหมาะสม

SELECT user_id, date_ordered
FROM dataset.table
WHERE product_id = 1234

การเพิ่มประสิทธิภาพ ORDER BY

ORDER BY ควรอยู่ที่ส่วนท้ายสุด (ด้านนอกสุด) ของคิวรีเสมอ
ข้อยกเว้นมีเพียงการใช้ ORDER BY ภายใน window clause เท่านั้น

ORDER BY แบบที่ยังไม่ได้ปรับแต่ง

ไม่เหมาะสม

WITH order_total AS (SELECT
user_id,           
sum(product_price) as order_sum
FROM orders
GROUP BY user_id   
-- Order by is not at the end of the query
ORDER BY last_purchase_date
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN order_total USING (user_id);

ORDER BY แบบที่ปรับแต่งแล้ว

เหมาะสม

WITH order_total AS (SELECT
user_id,    
last_purchase_date
sum(product_price) as order_sum
GROUP BY user_id         
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN a USING (user_id)
-- Order by should always be at the end
ORDER BY orders_total.last_purchase_date;

การใช้ EXISTS แทน COUNT

หากต้องการตรวจสอบเพียงว่ามีระเบียนอยู่ในตารางหรือไม่ ให้ใช้ EXISTS
หลีกเลี่ยงการใช้ COUNT สำหรับกรณีนี้

SELECT EXISTS (

  -- Write the main query as a subquery within the exists call
  SELECT
    user_id
  FROM
    dataset.table
  WHERE
    product_category = 'home_goods'
    AND status = 'Closed Account'
);

วิธีเพิ่มประสิทธิภาพอื่นๆ

ใช้ฟังก์ชัน aggregate แบบประมาณค่า เช่น APPROX_TOP_SUM หรือ APPROX_COUNT_DISTINCT
ตาราง BigQuery ส่วนใหญ่แบ่งพาร์ทิชันตามวันที่ ควรระบุวันที่ใน WHERE clause ด้วย

มาฝึกกันเถอะ!

BigQuery เบื้องต้น