สร้างตารางที่มีประสิทธิภาพ

  • 8 นาที

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

ออกแบบและสร้างตาราง

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

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

เลือกชนิดข้อมูลที่เหมาะสม

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

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

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

สํารวจชนิดข้อมูลทั่วไป

ชนิดข้อมูลที่เหมาะสมส่งผลต่อการจัดเก็บ ประสิทธิภาพ และการทํางาน:

ประเภท หมวดหมู่ ประเภทข้อมูล ขนาดการจัดเก็บ แนวทางการใช้งาน ตัวอย่าง
ตัวเลข INT, , BIGINT, DECIMALFLOAT 4 ไบต์ 8 ไบต์ แตกต่างกันไป เลือกตามความต้องการช่วงและความแม่นยํา Quantity INT, , Revenue DECIMAL(10,2)Population BIGINT
เชือก VARCHAR, , CHARNVARCHAR 1 ไบต์/ถ่าน คงที่ 2 ไบต์/อักขระ ใช้สําหรับ VARCHAR ข้อมูล CHAR ที่มีความยาวตัวแปร สําหรับความยาว NVARCHAR คงที่ สําหรับ Unicode Email VARCHAR(100), , CountryCode CHAR(2)ProductName NVARCHAR(100)
วันที่/เวลา DATE, , DATETIME2DATETIMEOFFSET 3 ไบต์ 6-8 ไบต์ 10 ไบต์ DATETIME2 ให้ความแม่นยําที่ดีกว่า DATETIME BirthDate DATE, , OrderTimestamp DATETIME2EventTime DATETIMEOFFSET
ไบ นารี VARBINARY, IMAGE แตกต่างกันไป สําหรับจัดเก็บข้อมูลไบนารี เช่น รูปภาพหรือเอกสาร ProfilePhoto VARBINARY(MAX), DocumentContent VARBINARY(MAX)
พิเศษ UNIQUEIDENTIFIER, , XMLJSON 16 ไบต์ แตกต่างกันไป ไบนารีดั้งเดิม UNIQUEIDENTIFIER สําหรับ GUID XML สําหรับเอกสาร JSON XML (SQL 2025+) สําหรับเอกสาร JSON ในรูปแบบไบนารีดั้งเดิม RowGUID UNIQUEIDENTIFIER, , Config XMLSettings JSON

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

ข้อกําหนดขนาดตารางโดยประมาณ

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

สำคัญ

ตารางที่ออกแบบมาไม่ดีซึ่งจัดเก็บ 200 ไบต์ต่อแถวแทนที่จะเป็น 100 ไบต์จะเพิ่มความต้องการพื้นที่จัดเก็บข้อมูล เวลาสํารองข้อมูล และข้อกําหนด I/O ของคุณเป็นสองเท่า

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

ตัวอย่างเช่น บริษัทค้าปลีกที่จัดเก็บธุรกรรม 100 ล้านรายการต่อวันโดยมีพื้นที่เพิ่มอีก 50 ไบต์ต่อแถว สิ้นเปลือง 5 GB ต่อวัน นั่นคือพื้นที่จัดเก็บข้อมูลที่ไม่จําเป็น 1.8TB ต่อปี บวกกับเวลาและค่าใช้จ่ายในการสํารองข้อมูลที่เพิ่มขึ้นตามสัดส่วน

ตัวอย่างต่อไปนี้แสดงวิธีการประมาณขนาดของ Employee ตาราง:

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

เคล็ดลับ

คุณสามารถใช้ Copilot เพื่อช่วยคุณสร้างการประมาณขนาดตาราง

ออกแบบคอลัมน์ที่มีประสิทธิภาพ

ตัวอย่างต่อไปนี้แสดงให้เห็นถึงตารางที่ออกแบบ Product มาอย่างดีซึ่งใช้หลักการที่กล่าวถึงในหน่วยนี้:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);

ตารางนี้แสดงแนวทางปฏิบัติที่ดีที่สุดหลายประการ:

  • ชนิดข้อมูลที่เหมาะสม: INT สําหรับคีย์หลัก (เล็กกว่า BIGINT หรือ UNIQUEIDENTIFIER) DECIMAL(10,2) สําหรับการคํานวณทางการเงินที่แม่นยําแทน FLOATเพื่อ DATETIME2 ความแม่นยําที่ดีกว่าแบบดั้งเดิม DATETIME
  • คอลัมน์ขนาดที่เหมาะสม: NVARCHAR(100) สําหรับชื่อผลิตภัณฑ์และ NVARCHAR(50) สําหรับหมวดหมู่ตามความยาวของข้อมูลที่คาดไว้
  • ข้อจํากัด: NOT NULL รับประกันคุณภาพของข้อมูลโดยป้องกันการขาดค่าวิกฤต
  • ค่าเริ่มต้น: ค่าอัตโนมัติสําหรับ StockQuantity (0) และ LastRestocked (เวลา UTC ปัจจุบัน) ช่วยลดความซับซ้อนของรหัสแอปพลิเคชัน
  • คีย์หลักที่มีประสิทธิภาพ: IDENTITY สร้างคีย์ตามลําดับที่จัดกลุ่มอย่างมีประสิทธิภาพและใช้พื้นที่เก็บข้อมูลน้อยที่สุด (4 ไบต์เทียบกับ 16 ไบต์สําหรับ GUID)

Note

ตัวอย่างนี้ใช้ NVARCHAR (2 ไบต์ต่ออักขระ) สําหรับการสนับสนุน Unicode หากข้อมูลของคุณเป็นแบบ ASCII เท่านั้น VARCHAR (1 ไบต์ต่ออักขระ) จะลดพื้นที่จัดเก็บสตริงลงครึ่งหนึ่ง A ProductName VARCHAR(100) ใช้ ~30 ไบต์เทียบกับ ~60 ไบต์สําหรับ NVARCHAR(100) ชื่อ 30 อักขระ ใน 10 ล้านแถว ซึ่งจะช่วยประหยัดได้ประมาณ 300 MB ใช้สําหรับ NVARCHAR ข้อมูลระหว่างประเทศใช้ VARCHAR เมื่อประสิทธิภาพการจัดเก็บมีความสําคัญและข้อมูลจะยังคงเป็น ASCII เท่านั้น

แนวทางปฏิบัติที่ดีที่สุดสําหรับการออกแบบ

ใช้หลักการสําคัญเหล่านี้เมื่อออกแบบและใช้งานตารางเพื่อให้มั่นใจถึงประสิทธิภาพและการบํารุงรักษา:

  • ใช้ประเภทข้อมูลที่เหมาะสม - ประเภทข้อมูลที่เล็กกว่าจะลดพื้นที่เก็บข้อมูลและปรับปรุงประสิทธิภาพ
  • พิจารณาขนาดตารางตั้งแต่เนิ่นๆ - ประมาณขนาดแถวและขนาดตารางทั้งหมดเพื่อวางแผนการจัดเก็บและการจัดทําดัชนี
  • ใช้ข้อจํากัดที่มีความหมาย - รับรองคุณภาพของข้อมูลในระดับฐานข้อมูล
  • วางแผนเพื่อการเติบโต - ออกแบบตารางเพื่อจัดการกับปริมาณข้อมูลในอนาคต
  • ดัชนีเชิงกลยุทธ์ - คอลัมน์ดัชนีที่ใช้ในส่วน WHEREคําสั่ง , JOINและ ORDER BY ส่วนคําสั่ง
  • เลือก columnstore สําหรับการวิเคราะห์ - ใช้ดัชนี columnstore สําหรับตารางขนาดใหญ่ที่มีการสืบค้นเชิงวิเคราะห์
  • ปรับให้เป็นมาตรฐานตามความเหมาะสม - ปรับสมดุลการทําให้เป็นมาตรฐานกับความต้องการด้านประสิทธิภาพของคิวรี
  • ตรวจสอบการบีบอัดแถวและหน้า - เปิดใช้งานการบีบอัดสําหรับตารางขนาดใหญ่เพื่อประหยัดพื้นที่เก็บข้อมูล

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