SQL Tip ถ้าข้อมูลเยอะขึ้น เราควรแยกไปเป็นตารางใหม่หรือไม่

สมมติเรามีตารางเก็บ posts ใน web blog หรือ forum ที่มีโครงสร้างดังนี้

CREATE TABLE `posts` (
`id`  int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title`  varchar(255) NOT NULL ,
`content`  text NOT NULL ,
`utc_created`  datetime NULL ,
`utc_updated`  datetime NULL
);

และพบว่าตารางนี้จะเก็บข้อมูลเป็นจำนวนมาก จึงทำการออกแบบใหม่ให้ข้อมูลแยกออกเป็นตารางเก็บข้อมูลตามปี ดังนี้

CREATE TABLE `posts_2014` (...);
CREATE TABLE `posts_2015` (...);
CREATE TABLE `posts_2016` (...);

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

ดังนั้นเวลาที่จะใช้งาน เราก็จะ insert table แยกออกตามปี ดังนี้

INSERT INTO posts_2016 (title, content,utc_created) 
values
('hello world', 'hello world content', '2016-04-21 7:27:17');

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

• ต้องตรวจสอบว่าตารางที่จะ insert ข้อมูลเข้าไป มีอยู่แล้วยัง เพราะทุกปีใหม่ๆ ก็จะต้องเพิ่มตารางใหม่ๆ เข้าไปอีก เช่น ถ้าจะ insert เข้า table posts_2017 แต่ถ้าตารางนั้นยังไม่ได้สร้าง application ก็ทำงานผิดพลาด
• การ insert ข้อมูลผิดตาราง ต้องให้ application manage ว่า insert ข้อมูลเข้าไปในตารางถูกต้องหรือไม่
• id ของข้อมูลในแต่ละตารางถ้ามีการซ้ำกัน เช่นในตาราง posts_2014 และ post_2015 มี id 1000 เหมือนกัน ก็จะทำให้การย้ายข้อมูลทำได้ลำบาก ทางเลี่ยงคือไปใช้พวก guid หรือ serial id
• ถ้าจะ query ข้อมูลที่โดยไม่สนใจว่าอยู่ในปีใดๆ ก็ต้องทำการ union ข้อมูลของ table ทุกตัว เช่น

SELECT * FROM (
 SELECT * FROM posts_2014
 UNION 
 SELECT * FROM posts_2015
UNION 
 SELECT * FROM posts_2016
) AS all_posts

และถ้ามีข้อมูลของปีใหม่เช่น posts_2017 เราก็ต้องมี update query นี้อีก

• แล้วถ้าเราจะมีการ เพิ่ม field อะไรเข้าไป ก็ต้องตาม ALTER ทุกตรางอีก
• ในแต่ละ post สามารถมี comment ได้ แล้วแต่ละ comment สามารถที่จะมี foreign key constrain ได้เพียงแค่ table post เดียวเท่านั้น

CREATE TABLE `comments` (
`id`  INT NOT NULL AUTO_INCREMENT ,
`content`  varchar(1000) NULL ,
`post_id`  int NULL ,
PRIMARY KEY (`id`),
CONSTRAINT `fk__comments__post_id` FOREIGN KEY (`post_id`) REFERENCES `posts_20??` (`id`)
)
;

แนวทางแก้ไข

เราอาจจะเห็นว่า การแยกตารางแบบนี้มีข้อเสียอยู่หลายอย่าง แต่ก็เหมาะกับข้อมูลที่เป็นอดีต ที่เราไม่ได้จะนำกลับมาใช้งานอีก แต่ถ้าข้อมูลเหล่านั้นยังมีโอกาสที่ต้องนำกลับมาใช้การแยกออกไปเป็นตารางใหม่ก็จะเจอปัญหาดังที่กล่าวมา

ต่อไปจะเป็นวิธีการแก้ปัญหาที่เกิดขึ้นจากตารางที่มีขนาดใหญ่นะครับ อาจจะมีวิธีการแก้ปัญหาอื่นๆ อีก แต่วิธีการที่จะนำเสมอเป็นวิธีการที่ง่ายใช้ได้ทันทีและสามารถนำไปใช้งานได้ทั่วไปครับ เราสามารถแก้ปัญหาข้างต้นได้ดังนี้ครัผม

ใช้วิธีการที่เรียกว่า Horizontal Partitioning

เป็นการแยกข้อมูลออกจากกันในระดับของ disk การใช้งานทุกอย่างยังมองเห็นเป็น table เดียวกันครับ

ตัวอย่างคำสั่งในการใช้งาน partitioning โดยแยกข้อมูลด้วย ปีที่สร้าง post นั้นๆ แต่เราต้องจำเป็นต้องปรับแก้ให้ตารางมี primary key เพิ่มอีกตัวซึ่งก็คือ utc_created_year เพื่อทำให้เราสามารถที่แยกข้อมูลด้วยปีได้

CREATE TABLE `posts` (
`id`  int NOT NULL AUTO_INCREMENT,
`title`  varchar(255) NOT NULL ,
`content`  text NOT NULL ,
`utc_created_year`  INT NOT NULL ,
`utc_created_date`  datetime NOT NULL ,
`utc_updated_date`  datetime NULL,
PRIMARY KEY (id,utc_created_year)
) PARTITION BY HASH( utc_created_year)
PARTITIONS 4   ;

ทดสอบ insert ข้อมูล

INSERT INTO posts (title, content, utc_created_year,  utc_created_date) 
values
('hello world 1', 'hello world content',2015, '2015-04-21 7:27:17');

INSERT INTO posts (title, content, utc_created_year,  utc_created_date) 
values
('hello world 2', 'hello world content',2016, '2016-04-21 7:27:17');

เราสามารถทดสอบว่าข้อมูลที่ query ขึ้นมาถูกเรียกใช้จาก partition ใด้ ด้วยคำสั่ง

 EXPLAIN PARTITIONS SELECT * FROM posts WHERE id=1 AND utc_created_year = 2015 \G

ผลลัพธ์ที่ได้

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: posts
   partitions: p3
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

จะเห็นได้ว่าข้อมูลถูกเรียกมาจาก partitions p3

นี่ก็เป็นแนวคิดตัวอย่างง่ายๆ ในการใช้ partition เพื่อลดปัญหาที่เกิดจากการแยก table ที่ไม่ได้เป็น history data จริงๆ แต่ยังต้องกลับมาใช้งานอีก ซึ่งอาจจะต้องทำการประยุกต์ใช้ให้เหมาะสม เพราะการ scale relational database ก็ยังมีแบบอื่นๆ อีกครับ