เพิ่มประสิทธิภาพด้วยดัชนี

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

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

อย่างไรก็ตาม ดัชนีจะใช้พื้นที่ INSERTจัดเก็บและทําให้ , UPDATEและ DELETE การดําเนินการช้าลง เนื่องจากฐานข้อมูลต้องรักษาโครงสร้างดัชนีควบคู่ไปกับข้อมูล การแลกเปลี่ยนนี้ทําให้การเลือกดัชนีเป็นการตัดสินใจออกแบบที่สําคัญซึ่งส่งผลโดยตรงต่อทั้งประสิทธิภาพการสืบค้นและปริมาณงานการเขียน

ดัชนีประเภทต่างๆ มีจุดประสงค์ที่แตกต่างกัน

ใช้ดัชนี rowstore

การออกแบบดัชนีที่มีประสิทธิภาพเป็นกุญแจสําคัญในการบรรลุฐานข้อมูลและประสิทธิภาพของแอปพลิเคชันที่ดี การขาดดัชนี การจัดทําดัชนีมากเกินไป หรือดัชนีที่ออกแบบมาไม่ดีเป็นสาเหตุหลักของปัญหาประสิทธิภาพของฐานข้อมูล

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

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

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

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

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

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

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

ทําความเข้าใจดัชนี columnstore

ดัชนี rowstore แบบดั้งเดิมจัดเก็บข้อมูลทีละแถว ซึ่งเหมาะสําหรับระบบธุรกรรมที่ดึงข้อมูลแต่ละบันทึก แต่การสืบค้นเชิงวิเคราะห์ที่สแกนหลายล้านแถวเพื่อคํานวณผลรวม (SUM, AVG, COUNT) เสียเวลาอ่านคอลัมน์ที่ไม่ต้องการ ดัชนี Columnstore มีจุดมุ่งหมายเพื่อแก้ปัญหานี้โดยการจัดเก็บข้อมูลทีละคอลัมน์ โดยอ่านเฉพาะคอลัมน์ที่จําเป็นสําหรับการค้นหาของคุณ

ทําความเข้าใจสถาปัตยกรรม columnstore

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

เมื่อคุณแทรกข้อมูล ชุดงานขนาดเล็กจะไปที่ deltastore ก่อน ซึ่งเป็นโครงสร้าง rowstore ชั่วคราวโดยใช้ดัชนีต้นไม้ B+ เมื่อกลุ่มแถวเดลต้าสะสมแถวเพียงพอ (อย่างน้อย 102,400) กระบวนการเบื้องหลังที่เรียกว่า tuple-mover จะบีบอัดลงในที่เก็บคอลัมน์ แถวที่มาถึงผ่านการโหลดจํานวนมาก 102,400 แถวขึ้นไปจะข้าม deltastore และบีบอัดลงในคอลัมน์สโตร์โดยตรง

ตารางต่อไปนี้อธิบายคําแนะนําสําหรับดัชนี columnstore:

ใช้ดัชนี Clustered Columnstore (CCI)

ดัชนี Clustered Columnstore (CCI) เป็นดัชนี columnstore ชนิดหนึ่งที่กลายเป็นโครงสร้างที่เก็บข้อมูลหลักสําหรับทั้งตาราง แทนที่ดัชนี rowstore แบบคลัสเตอร์ที่มีอยู่ ซึ่งแตกต่างจากดัชนี columnstore (NCCI) ที่ไม่ใช่คลัสเตอร์ ซึ่งสร้างสําเนาคอลัมน์รองควบคู่ไปกับตาราง rowstore CCI จะจัดเก็บข้อมูลตารางทั้งหมดในรูปแบบคอลัมน์เท่านั้น

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

คุณสามารถสร้างดัชนี columnstore แบบคลัสเตอร์ได้โดยใช้ CREATE CLUSTERED COLUMNSTORE INDEX คําสั่ง ตัวอย่างมีดังนี้:

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

ใช้ดัชนี Nonclustered Columnstore (NCCI)

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

คุณสามารถสร้างดัชนี columnstore ที่ไม่ใช่คลัสเตอร์ โดยใช้ CREATE NONCLUSTERED COLUMNSTORE INDEX คําสั่ง ตัวอย่างมีดังนี้:

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

ตรวจสอบดัชนีที่เก็บคอลัมน์

คุณสามารถตรวจสอบสถานภาพและประสิทธิภาพของดัชนี columnstore ของคุณโดยการสืบค้น sys.dm_db_column_store_row_group_physical_stats มุมมองการจัดการแบบไดนามิก

แบบสอบถามต่อไปนี้แสดงสถิติกลุ่มแถว รวมถึงสถานะ จํานวนแถว แถวที่ถูกลบ และขนาดที่เก็บข้อมูล กลุ่มแถวที่เปิดอยู่ยังคงยอมรับการแทรกใน deltastore กลุ่มแถวที่ปิดกําลังรอให้ tuple-mover บีบอัด และกลุ่มแถวที่บีบอัดจะเก็บข้อมูลในรูปแบบคอลัมน์ จํานวนแถวที่ถูกลบสูงหรือกลุ่มแถวขนาดเล็กจํานวนมากบ่งชี้ถึงการกระจายตัวที่คุณสามารถแก้ไขได้ด้วยALTER INDEX REORGANIZE

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

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