ใบงานที่ 2: การออกแบบ sort key (ORDER BY)

เวลาที่ใช้โดยประมาณ: 20–25 นาที เอกสารอ้างอิง: เอนจิน MergeTree — ส่วนการออกแบบ ORDER BY

แนวคิด

clause ORDER BY ของ ClickHouse ไม่ใช่ของประดับ มันกำหนด primary index — อินเด็กซ์ แบบเบาบางระดับบล็อกที่ทำให้ ClickHouse ข้ามบล็อกข้อมูลที่ไม่เกี่ยวข้องได้เมื่อประเมิน clause WHERE มันยังกำหนดลำดับการเรียงข้อมูลทางกายภาพภายใน part ซึ่งส่งผลต่อการบีบอัด

ORDER BY ที่ผิด = คิวรีช้า + เปลืองพื้นที่จัดเก็บ ORDER BY ที่นำด้วย UUID หมายถึงไม่มี การข้ามบล็อกเลยสำหรับคิวรีเชิงวิเคราะห์ใด ๆ (UUID เป็นค่าสุ่ม ไม่มีคำนำหน้าที่เรียงลำดับได้) ORDER BY ที่นำด้วยวันที่หมายถึงคิวรีที่กรองด้วยวันที่จะข้ามตารางไปได้เกือบทั้งหมด

กฎสามข้อสำหรับการออกแบบ ORDER BY

กฎข้อ 1: อนุมานจากตัวกรองของคิวรี ไม่ใช่จากสคีมาต้นทาง ดูคอลัมน์ใน WHERE, GROUP BY และ JOIN ของคิวรีที่คุณใช้บ่อยที่สุด คอลัมน์ที่ถูกกรองมากที่สุดน่าจะควร ปรากฏใน ORDER BY (ตราบใดที่มันไม่มี cardinality สูงเกินไป) primary key ของตารางต้นทาง (ถ้ามี) มักไม่เกี่ยวข้องเลย

กฎข้อ 2: cardinality ต่ำมาก่อน cardinality สูงมาท้าย primary index ของ ClickHouse มี หนึ่งรายการต่อทุก ~8192 แถว (หนึ่ง granule) คอลัมน์ที่มี cardinality ต่ำ (เช่น toStartOfMonth(date) = ราว 48 ค่าที่ไม่ซ้ำในช่วง 4 ปี) จับกลุ่มแถวจำนวนมากไว้ด้วยกัน — อินเด็กซ์จึงข้าม granule ทั้งก้อนได้ คอลัมน์ที่มี cardinality สูง (เช่น trip_id = 50M ค่าที่ไม่ซ้ำ) มีค่าไม่ซ้ำในทุกแถว การวางมันไว้ต้นแถวหมายถึงอินเด็กซ์ข้ามอะไรไม่ได้เลย ลำดับนี้เป็นค่าเริ่มต้น ไม่ใช่การลบล้างกฎข้อ 1: คอลัมน์ที่คิวรีกรองแบบช่วงและตัดแถวออกไป ได้เกือบหมด ยังชิงตำแหน่งนำหน้าคอลัมน์ที่มี cardinality ต่ำกว่าแต่ถูกกรองด้วยความเท่ากัน เท่านั้นได้

กฎข้อ 3: สำหรับ ReplacingMergeTree ให้ปิดท้ายด้วยตัวระบุแถวที่ไม่ซ้ำ คีย์การขจัด ข้อมูลซ้ำคือ tuple ORDER BY ทั้งชุด ถ้า trip_id ขาดหายไปจาก ORDER BY การเดินทางสองรายการที่ต่างกันแต่มี pickup_at เดียวกันและไม่มีคอลัมน์อื่นอีก จะถูกถือว่า เป็นข้อมูลซ้ำ วาง trip_id ไว้ท้ายสุดเพื่อรับประกันความไม่ซ้ำโดยไม่กระทบประสิทธิภาพอินเด็กซ์

แบบฝึกหัด: การวิเคราะห์เวิร์กโหลดคิวรี

ก่อนออกแบบ sort key ให้หาให้ได้ว่าคิวรีกรองด้วยคอลัมน์ใดจริง ๆ แล็บ NYC Taxi มีคิวรี ตัวแทน 7 รายการ สำหรับแต่ละรายการ ให้เลือกคอลัมน์ตัวกรองที่ตัดแถวออกไปได้มากที่สุด

แบบฝึกหัด: การประมาณ cardinality

สำหรับแต่ละคอลัมน์ ORDER BY ที่เป็นตัวเลือก ให้ประมาณ cardinality ของมันบนชุดข้อมูล 50M แถวช่วง 4 ปี ตารางด้านล่างส่วนใหญ่เป็นข้อมูลอ้างอิง — สองช่องที่เปิดไว้คือค่าที่ไม่ซ้ำ โดยประมาณและระดับชั้น cardinality ของ pickup_at เอง สี่ปีคือประมาณ 126 ล้าน วินาที (และเพียงราว 2.1 ล้านนาที) producer ประทับ timestamp ให้ทุกการเดินทางจาก นาฬิกาเครื่อง และ PICKUP_AT ถูกจัดเก็บเป็น DateTime64(3, 'UTC') — คำนวณดูว่า การเดินทาง 50 ล้านรายการครองช่องเหล่านั้นได้กี่ช่อง ก่อนที่คุณจะเลือกช่วงค่า

แบบฝึกหัด: การออกแบบ sort key

ใช้การวิเคราะห์เวิร์กโหลดคิวรีและการประมาณ cardinality ของคุณ เสนอ ORDER BY ให้ trips_raw, fact_trips และ agg_hourly_zone_trips ข้อเตือนความจำ:

• cardinality ต่ำมาก่อน → ข้ามบล็อกได้มากที่สุด
• คอลัมน์ที่ปรากฏใน WHERE/GROUP BY ของหลายคิวรี → ใส่เข้าไป
• สำหรับตาราง ReplacingMergeTree → ปิดท้ายด้วยตัวระบุแถวที่ไม่ซ้ำ
• อย่าใส่คอลัมน์ที่ไม่เคยถูกใช้กรอง

แล้วจึงทำคำถามเชิงเหตุผลและคำถามทบทวนเมื่อกรอกครบทุกตาราง

Loading worksheet...

ถ่ายลงใน migration-plan.md

เมื่อกรอกใบงานนี้เสร็จแล้ว ให้คัดลอกการตัดสินใจเรื่อง ORDER BY ของคุณไปยังส่วนที่ 4 ของ migration-plan.md และติ๊กช่อง:

- [ ] Sort key design: completed