การจัดกลุ่มข้อมูลใน Fabric Data Warehouse

นําไปใช้กับ:✅ Warehouse ใน Microsoft Fabric

การจัดกลุ่มข้อมูลเป็นเทคนิคที่ใช้ในการจัดระเบียบและจัดเก็บข้อมูลตามความคล้ายคลึงกัน การจัดกลุ่มข้อมูลช่วยปรับปรุงประสิทธิภาพการสืบค้นและลดต้นทุนการเข้าถึงการประมวลผลและที่เก็บข้อมูลสําหรับคิวรีโดยการจัดกลุ่มเรกคอร์ดที่คล้ายกันเข้าด้วยกัน

วิธีการทำงาน

การจัดกลุ่มข้อมูลทํางานโดยการจัดเก็บแถวที่มีค่าคล้ายกันในตําแหน่งที่อยู่ติดกันบนพื้นที่จัดเก็บระหว่างการนําเข้า การจัดกลุ่มข้อมูลใช้เส้นโค้งการเติมช่องว่างเพื่อจัดระเบียบข้อมูลในลักษณะที่รักษาตําแหน่งในหลายมิติ ซึ่งหมายความว่าแถวที่มีค่าคล้ายกันในคอลัมน์การจัดกลุ่มจะถูกจัดเก็บไว้ใกล้กัน วิธีการนี้ช่วยปรับปรุงประสิทธิภาพการสืบค้นอย่างมากโดยทําการข้ามไฟล์และลดจํานวนไฟล์ที่สแกน

ซึ่งแตกต่างจากการเรียงลําดับพจนานุกรมทั่วไป การจัดกลุ่มข้อมูลใช้อัลกอริทึมที่ซับซ้อนในการนําเข้า โดยทําให้แถวที่มีค่าคอลัมน์ใกล้เคียงกันอยู่ใกล้กัน แม้ว่าตารางจะถูกจัดกลุ่มด้วยหลายคอลัมน์ก็ตาม สิ่งนี้ทําให้การจัดกลุ่มข้อมูลเหมาะอย่างยิ่งสําหรับการสืบค้นช่วง ตัวกรองคาร์ดินาลลิตี้สูง และตารางขนาดใหญ่ที่มีการแจกแจงแบบเบ้ ส่งผลให้อ่านได้เร็วขึ้น I/O ลดลง และการใช้ทรัพยากรที่มีประสิทธิภาพมากขึ้น

นี่คือภาพประกอบแนวคิดที่เรียบง่ายของการจัดกลุ่มข้อมูล:

ในไดอะแกรมนี้ ตารางที่มี Source data ป้ายกํากับจะแสดงแถวที่ผสมและเน้นด้วยสีต่างๆ เพื่อแสดงการจัดกลุ่มคลัสเตอร์บนปลายทาง ตารางที่เรียงลําดับจะแบ่งออกเป็นสามส่วนของไฟล์ โดยแต่ละส่วนจะจัดกลุ่มแถวตามสีที่คล้ายกัน ซึ่งแสดงให้เห็นว่าการจัดกลุ่มจัดระเบียบข้อมูลเป็นกลุ่มที่เก็บข้อมูลที่ปรับให้เหมาะสมตามค่าคอลัมน์ได้อย่างไร

ข้อมูลเมตาของการจัดกลุ่มข้อมูลถูกฝังอยู่ในรายการระหว่างการนําเข้า ซึ่งช่วยให้กลไกจัดการคลังสินค้าสามารถตัดสินใจได้อย่างชาญฉลาดเกี่ยวกับไฟล์ที่จะเข้าถึงระหว่างการสืบค้นของผู้ใช้ ข้อมูลเมตานี้รวมกับวิธีการจัดเก็บแถวที่มีค่าคล้ายกันเข้าด้วยกันทําให้มั่นใจได้ว่าคิวรีที่มีเพรดิเคตตัวกรองสามารถข้ามไฟล์และกลุ่มแถวทั้งหมดที่อยู่นอกขอบเขตเพรดิเคตได้ ตัวอย่างเช่น หากคิวรีกําหนดเป้าหมายข้อมูลของตารางเพียง 10% การจัดกลุ่มจะช่วยให้แน่ใจว่าเฉพาะไฟล์ที่มีข้อมูลภายในช่วงของตัวกรองเท่านั้นที่จะถูกสแกน ซึ่งจะช่วยลด I/O และการใช้การประมวลผล ตารางขนาดใหญ่จะได้รับประโยชน์มากขึ้นจากการจัดกลุ่มข้อมูล เนื่องจากประโยชน์ของการข้ามไฟล์ปรับขนาดตามปริมาณข้อมูล

เมื่อใดที่ควรใช้การจัดกลุ่มข้อมูล

เมื่อตัดสินใจว่าการจัดกลุ่มข้อมูลอาจเป็นประโยชน์หรือไม่ ให้ตรวจสอบรูปแบบคิวรีและลักษณะของตารางในคลังสินค้า การจัดกลุ่มข้อมูลจะมีประสิทธิภาพสูงสุดเมื่อคิวรีกรองซ้ําๆ ในคอลัมน์เฉพาะ และเมื่อตารางต้นแบบมีขนาดใหญ่และมีข้อมูลคาร์ดินาลลิตี้ระดับกลางถึงสูง สถานการณ์ทั่วไปบางประการ ได้แก่ :

• คิวรีซ้ําด้วย WHERE ตัวกรอง: หากปริมาณงานมีคิวรีบ่อยครั้งที่กรองคอลัมน์เฉพาะ การจัดกลุ่มข้อมูลจะช่วยให้แน่ใจว่าเฉพาะไฟล์ที่เกี่ยวข้องเท่านั้นที่จะถูกสแกนในระหว่างคิวรีการอ่าน นอกจากนี้ยังใช้เมื่อมีการใช้ตัวกรองซ้ําๆ ในแดชบอร์ด รายงาน หรืองานที่จัดกําหนดการ และผลักดันลงไปยังกลไกจัดการคลังสินค้าเป็นคําสั่ง SQL
• ตารางขนาดใหญ่: การจัดกลุ่มข้อมูลจะมีประสิทธิภาพสูงสุดเมื่อนําไปใช้กับตารางขนาดใหญ่ที่การสแกนชุดข้อมูลทั้งหมดมีค่าใช้จ่ายสูง ด้วยการจัดระเบียบแถวด้วยการจัดกลุ่มข้อมูล กลไกจัดการคลังสินค้าสามารถข้ามไฟล์และกลุ่มแถวทั้งหมดที่ไม่ตรงกับตัวกรองคิวรี ซึ่งสามารถลด I/O และการใช้การประมวลผลได้
• คอลัมน์คาร์ดินาลลิตี้ระดับกลางถึงสูง: คอลัมน์ที่มีคาร์ดินาลลิตี้สูงกว่า (ตัวอย่างเช่น คอลัมน์ที่มีค่าที่แตกต่างกันจํานวนมาก เช่น รหัส หรือวันที่) จะได้รับประโยชน์มากกว่าจากการจัดกลุ่มข้อมูล เนื่องจากอนุญาตให้กลไกจัดการแยกและระบุตําแหน่งค่าที่คล้ายกันได้ สิ่งนี้ทําให้สามารถข้ามไฟล์ได้อย่างมีประสิทธิภาพ โดยเฉพาะอย่างยิ่งสําหรับการค้นหาที่เลือก คอลัมน์ที่มีคาร์ดินาลลิตี้ต่ํา (เช่น เพศ ภูมิภาค) โดยธรรมชาติจะมีค่ากระจายไปตามไฟล์จํานวนมาก ดังนั้นจึงมีโอกาสจํากัดในการข้ามไฟล์
• คิวรีแบบเลือกที่มีขอบเขตแคบ: เมื่อคิวรีโดยทั่วไปกําหนดเป้าหมายชุดย่อยของข้อมูลขนาดเล็กและรวมกับตัวกรอง WHERE การจัดกลุ่มข้อมูลจะช่วยให้แน่ใจว่าเฉพาะไฟล์ที่มีแถวที่เกี่ยวข้องเท่านั้นที่จะถูกอ่าน

การจัดกลุ่มข้อมูลจะเกิดขึ้นโดยอัตโนมัติในระหว่างการนําเข้าข้อมูล โดยไม่คํานึงถึงวิธีการนําเข้าแถว ไม่จําเป็นต้องดําเนินการกับผู้ใช้หลังจากนําเข้าข้อมูลเพื่อใช้การจัดกลุ่มข้อมูล

CLUSTER BY ไวยากรณ์

การจัดกลุ่มข้อมูลถูกกําหนดระหว่างการสร้างตารางโดยใช้ CLUSTER BY ส่วนคําสั่ง ไวยากรณ์มีดังนี้:

ไวยากรณ์ CREATE TABLE (Transact-SQL) ดังนี้

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
 [ ,... n ] –- Column list
) WITH (CLUSTER BY [ ,... n ]);

ไวยากรณ์ CREATE TABLE AS SELECT (Transact-SQL) ดังนี้

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
) WITH (CLUSTER BY[ ,... n ])
AS <select_statement>;

CLUSTER BYส่วนคําสั่งนี้ต้องมีการระบุคอลัมน์อย่างน้อยหนึ่งคอลัมน์สําหรับการจัดกลุ่มข้อมูล และสูงสุดสี่คอลัมน์

ไม่รองรับการสร้างตารางที่ใช้การจัด SELECT INTO กลุ่มข้อมูล

รองรับประเภทข้อมูล

ตารางต่อไปนี้สรุปชนิดคอลัมน์ที่สามารถใช้ใน CLUSTER BY ส่วนคําสั่ง:

ประเภท	ชนิดของข้อมูล	รองรับการจัดกลุ่มข้อมูล
ตัวเลขที่แน่นอน	บิต	ไม่
ตัวเลขที่แน่นอน	Bigint, Int, Smallint, ทศนิยม2, ตัวเลข	ใช่
ตัวเลขโดยประมาณ	ลอยจริง	ใช่
วันที่และเวลา	วันที่วันที่และเวลา 2เวลา	ใช่
สตริงอักขระ1	เผา	ใช่
สตริงอักขระ1	วาร์ชาร์	ใช่
ประเภท LOB	varchar (สูงสุด),varbinary (สูงสุด)	ไม่
สตริงไบนารี	varbinary, ตัวระบุที่ไม่ซ้ํากัน	ไม่

¹ สําหรับประเภทสตริง (char/varchar) จะใช้เฉพาะอักขระ 32 ตัวแรกเมื่อสร้างสถิติคอลัมน์ ด้วยเหตุนี้ คอลัมน์ที่มีค่าที่มีคํานําหน้าแบบยาวอาจมีประโยชน์จํากัดในการจัดกลุ่มข้อมูล

² สําหรับชนิด ทศนิยม ที่มีความแม่นยํามากกว่า 18 เพรดิเคตจะไม่ถูกกดลงไปยังที่เก็บข้อมูลในระหว่างการดําเนินการคิวรี หากใช้ชนิด ทศนิยม ที่มีการจัดกลุ่มข้อมูล ให้เลือกคอลัมน์ที่มีความแม่นยําน้อยกว่า

คอลัมน์ที่มีชนิดข้อมูลที่ไม่รองรับยังคงสามารถอยู่ในตารางที่ใช้การจัดกลุ่มข้อมูล แต่ไม่สามารถใช้กับCLUSTER BY

แนวทางปฏิบัติที่ดีที่สุดเกี่ยวกับการจัดกลุ่มข้อมูล

การจัดกลุ่มข้อมูลจะมีประสิทธิภาพมากขึ้นเมื่อมีการเลือกคอลัมน์การจัดกลุ่มตามรูปแบบคิวรีจริง โดยเฉพาะอย่างยิ่งคอลัมน์ที่มีคาร์ดินาลลิตี้ปานกลางถึงสูง และเมื่อใช้เพรดิเคตช่วงระหว่างคิวรี

พิจารณาแนวทางปฏิบัติที่ดีที่สุดต่อไปนี้เมื่อใช้การจัดกลุ่มข้อมูล:

• การจัดกลุ่มข้อมูลมีประสิทธิภาพมากขึ้นบนตารางขนาดใหญ่
• เมื่อใดก็ตามที่เป็นไปได้ ให้นําเข้าและอัปเดตเป็นชุดเพื่อประมวลผลแถวจํานวนมากขึ้นในคราวเดียว แทนที่จะใช้งานขนาดเล็ก เพื่อประสิทธิภาพสูงสุด การดําเนินงาน DML ควรมีอย่างน้อย 1 ล้านแถวเพื่อรับประโยชน์จากการจัดกลุ่มข้อมูล หลังจากแทรก อัปเดต และลบต่อเนื่อง การบีบอัดข้อมูลสามารถรวมแถวจากไฟล์ขนาดเล็กให้เป็นแถวที่มีขนาดเหมาะสมที่สุด
• เลือกคอลัมน์ที่มีคาร์ดินาลลิตี้ปานกลางถึงสูงสําหรับการจัดกลุ่มข้อมูล เนื่องจากให้ผลลัพธ์ที่ดีกว่าเนื่องจากการกระจายค่าที่แตกต่างกัน คอลัมน์ที่มีคาร์ดินาลลิตี้ต่ําอาจมีโอกาสจํากัดในการตัดแต่งไฟล์
• เลือกคอลัมน์ตามการใช้ WHERE เพรดิเคตบ่อยครั้งในแดชบอร์ด รายงาน งานตามกําหนดการ หรือคิวรีของผู้ใช้ เงื่อนไขการรวมความเท่าเทียมกันไม่ได้รับประโยชน์จากการจัดกลุ่มข้อมูล สําหรับภาพรวมของวิธีการใช้ Query Insights เพื่อช่วยเลือกคอลัมน์สําหรับการจัดกลุ่มข้อมูลตามปริมาณงานปัจจุบันของคุณ โปรดดูบทช่วยสอน: การใช้การจัดกลุ่มข้อมูลในคลังข้อมูล Fabric
• อย่าใช้การจัดกลุ่มข้อมูลตามคอลัมน์เกินความจําเป็นอย่างยิ่ง การจัดกลุ่มแบบหลายคอลัมน์จะเพิ่มความซับซ้อนให้กับที่เก็บข้อมูล เพิ่มค่าใช้จ่าย และอาจไม่ให้ประโยชน์ เว้นแต่คอลัมน์ทั้งหมดจะถูกใช้ร่วมกันในแบบสอบถามที่มีเพรดิเคต
• ลําดับคอลัมน์ที่ใช้ใน CLUSTER BY ไม่สําคัญและไม่เปลี่ยนแปลงวิธีการจัดเก็บแถว
• เมื่อสร้างตารางที่มีการจัดกลุ่มข้อมูลโดยใช้ CREATE TABLE AS SELECT (CTAS) หรือการนําเข้าข้อมูลด้วย INSERT INTO ... SELECTให้ส่วนที่เลือกของคําสั่งเหล่านี้ง่ายที่สุดเท่าที่จะเป็นไปได้เพื่อคุณภาพการจัดกลุ่มข้อมูลที่เหมาะสมที่สุด

การจัดกลุ่มข้อมูลสามารถลดต้นทุนระหว่างการสืบค้นได้อย่างมาก อย่างไรก็ตาม การนําเข้าข้อมูลจะก่อให้เกิดหน่วยเวลาและความจุ (CU) บนตารางที่ใช้การจัดกลุ่มข้อมูลมากขึ้นเมื่อเปรียบเทียบกับตารางที่เทียบเท่ากับข้อมูลเดียวกันโดยไม่มีการจัดกลุ่มข้อมูล สิ่งนี้เกิดขึ้นเนื่องจากกลไกจัดการคลังสินค้าจําเป็นต้องสั่งซื้อข้อมูลในระหว่างการนําเข้า เนื่องจากข้อมูลที่นําเข้าจะถูกอ่านหลายครั้ง การจัดกลุ่มข้อมูลจึงสามารถลดการใช้การประมวลผลโดยรวมของปริมาณงานที่กําหนดได้

มุมมองของระบบ

สามารถสืบค้นข้อมูลเมตาของการจัดกลุ่มข้อมูลได้โดยใช้sys.index_columns แสดงคอลัมน์ทั้งหมดที่ใช้ในการจัดกลุ่มข้อมูล รวมถึงลําดับคอลัมน์ที่ใช้ในส่วน CLUSTER BY คําสั่ง

คิวรีต่อไปนี้แสดงรายการคอลัมน์ทั้งหมดที่ใช้ในการจัดกลุ่มข้อมูลบนคลังสินค้าปัจจุบัน และตาราง:

SELECT
    t.name AS table_name,
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE ic.data_clustering_ordinal > 0
ORDER BY
    t.name,
    ic.data_clustering_ordinal;

Note

ลําดับคอลัมน์จะแสดงเพื่อใช้อ้างอิงเป็นลําดับที่ใช้เมื่อ CLUSTER BY กําหนดตารางเท่านั้น ตามที่กล่าวไว้ใน แนวทางปฏิบัติที่ดีที่สุด ลําดับคอลัมน์จะไม่ส่งผลต่อประสิทธิภาพการทํางาน

ข้อจํากัดและข้อสังเกต

• ประสิทธิภาพการนําเข้าข้อมูลอาจลดลงเมื่อตารางมีคอลัมน์ varchar ขนาดใหญ่ที่มีขนาดข้อมูลที่แปรผันสูง
  • ตัวอย่างเช่นพิจารณาตารางที่มีคอลัมน์ varchar(200): หากบางแถวมีอักขระเพียงไม่กี่ตัวในขณะที่แถวอื่นเข้าใกล้ความยาวสูงสุดความแปรปรวนอย่างมีนัยสําคัญในขนาดข้อมูลอาจส่งผลเสียต่อความเร็วในการนําเข้า
  • ปัญหานี้เป็นที่ทราบกันดีและจะได้รับการแก้ไขในรุ่นที่กําลังจะมาถึง
• IDENTITY คอลัมน์ไม่สามารถใช้กับ CLUSTER BY. ตารางที่มีIDENTITYคอลัมน์ยังคงสามารถใช้สําหรับการจัดกลุ่มข้อมูลได้ เนื่องจากใช้คอลัมน์ที่แตกต่างกันด้วยCLUSTER BY
• ต้องกําหนดการจัดกลุ่มข้อมูลเมื่อสร้างตาราง ไม่รองรับการแปลงตารางปกติเป็นตารางที่มีCLUSTER BY ในทํานองเดียวกัน ไม่อนุญาตให้ปรับเปลี่ยนคอลัมน์การจัดกลุ่มหลังจากสร้างตารางแล้ว หากจําเป็นต้องใช้คอลัมน์การจัดกลุ่มที่แตกต่างกัน ให้ใช้ CREATE TABLE AS SELECT (CTAS) เพื่อสร้างตารางใหม่ที่มีคอลัมน์การจัดกลุ่มที่ต้องการ
• ในบางกรณี การจัดกลุ่มข้อมูลอาจถูกนําไปใช้แบบอะซิงโครนัส ในกรณีดังกล่าว ข้อมูลจะถูกจัดระเบียบใหม่ด้วยงานเบื้องหลัง และตารางอาจไม่ได้รับการปรับให้เหมาะสมอย่างสมบูรณ์เมื่อการนําเข้าเสร็จสิ้น สิ่งนี้สามารถเกิดขึ้นได้ภายใต้เงื่อนไขต่อไปนี้:
  • เมื่อใช้ INSERT INTO ... SELECT or CREATE TABLE AS SELECT (CTAS) และการเปรียบเทียบตารางต้นทางและเป้าหมายจะแตกต่างกัน
  • เมื่อนําเข้าจากข้อมูลภายนอกที่เป็นรูปแบบ CSV ที่บีบอัด
  • เมื่อใบแจ้งยอดการนําเข้ามีแถวน้อยกว่า 1 ล้านแถว
• การนําเข้าข้อมูลบนตารางการจัดกลุ่มข้อมูลจะก่อให้เกิดค่าใช้จ่ายเมื่อเปรียบเทียบกับตารางที่มี Schema เดียวกันที่ไม่ได้ใช้การจัดกลุ่มข้อมูล สิ่งนี้เกิดขึ้นเนื่องจากจําเป็นต้องมีการคํานวณเพิ่มเติมเพื่อเพิ่มประสิทธิภาพการจัดเก็บข้อมูล เมื่อคอลัมน์การจัดกลุ่มมีการเปรียบเทียบที่ไม่คํานึงถึงตัวพิมพ์เล็กและใหญ่ ก็คาดว่าจะมีค่าใช้จ่ายมากขึ้น
• การจัดกลุ่มข้อมูลสามารถให้ประโยชน์ต่อเวลาตอบสนองของคิวรี

ตัวอย่าง

ตอบ สร้างตารางคลัสเตอร์สําหรับข้อมูลการขาย

ตัวอย่างนี้สร้างตารางอย่างง่าย Sales และใช้ CustomerID คอลัมน์ และ SaleDate สําหรับการจัดกลุ่มข้อมูล

CREATE TABLE Sales (
    SaleID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
) WITH (CLUSTER BY (CustomerID, SaleDate))

B. สร้างตารางแบบคลัสเตอร์โดยใช้ CREATE TABLE AS SELECT

ตัวอย่างนี้ใช้CREATE TABLE AS SELECTเพื่อสร้างสําเนาของSalesตารางที่มีอยู่พร้อมกับCLUSTER BYSaleDateคอลัมน์

CREATE TABLE Sales_CTAS 
WITH (CLUSTER BY (SaleDate)) 
AS SELECT * FROM Sales

C. ดูคอลัมน์ที่ใช้สําหรับการจัดกลุ่มข้อมูลบนตารางที่กําหนด

ตัวอย่างนี้แสดงรายการคอลัมน์ที่ใช้สําหรับการจัดกลุ่มข้อมูลใน Sales ตาราง

SELECT
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE 
    ic.data_clustering_ordinal > 0
   AND t.name = 'Sales'
ORDER BY
    t.name,
    ic.data_clustering_ordinal;

ผลลัพธ์:

D. ตรวจสอบประสิทธิภาพของตัวเลือกคอลัมน์สําหรับการจัดกลุ่มข้อมูล

ข้อมูลเชิงลึกของคิวรีสามารถช่วยประเมินผลกระทบของการจัดกลุ่มข้อมูลต่อปริมาณงานของคุณโดยการเปรียบเทียบเวลา CPU และข้อมูลที่สแกนระหว่างคิวรีที่กําหนดกับการเรียกใช้ที่เทียบเท่ากับสําเนาคลัสเตอร์ของตารางต้นฉบับ ตัวอย่างต่อไปนี้แสดงวิธีการดึงข้อมูล CPU ที่จัดสรรและปริมาณข้อมูลที่สแกนในดิสก์ หน่วยความจํา และที่เก็บข้อมูลระยะไกลสําหรับคิวรีเฉพาะ

SELECT 
    allocated_cpu_time_ms, 
    data_scanned_disk_mb, 
    data_scanned_memory_mb, 
    data_scanned_remote_storage_mb
FROM 
    queryinsights.exec_requests_history 
WHERE 
     distributed_statement_id = '<Query_Statement_ID>'

รหัสคําสั่งแบบกระจายของแบบสอบถามที่คุณต้องการประเมินอยู่ที่ไหน<Query_Statement_ID>

ขั้นตอนถัดไป

บทช่วยสอน: การใช้การจัดกลุ่มข้อมูลใน Fabric Data Warehouse

เนื้อหาที่เกี่ยวข้อง

• แนวทางประสิทธิภาพการทํางานในคลังข้อมูล Fabric
• ตรวจสอบคลังข้อมูลผ้า