پارتیشن بندی اطلاعات در پایگاه داده

سلام،

مطلب امروز در ابتدا به معرفی امکان پارتیشن بندی (Partitioning) در پایگاه‌های داده می پردازند و سپس نحوه استفاده از آن را در پایگاه داده MySQL آموزش می دهد.

پارتیشن بندی پایگاه داده چیست؟

منظور از پارتیشن‌بندی، تقسیم داده‌ها به قسمت‌های مستقل‌تر است. به عنوان مثال اگر در جدولی فهرست اشخاص را نگه می دارید، می توانید آن‌ها را با معیارهایی همچون جنسیت، سال تولد و... در همان جدول دسته‌بندی نمایید. این کار سبب افزایش کارایی و در نتیجه دسترس پذیری بیشتر داده‌ها می شود. در ادامه این مطلب، توضیحات بیشتری در رابطه با دلیل افزایش کارایی ارائه خواهد شد.

معیارهای دسته‌بندی اطلاعات برای پارتیشن‌بندی

در نوع پایگاه‌های داده رابطه‌ای معیارها و ضوابط گوناگونی جهت تقسیم پایگاه داده وجود دارد. برای این منظور، ابتدا یک کلید پارتیشن‌بندی (Partitioning Key) را تعریف کرده و سپس بر اساس آن و معیارهای مشخص داده‌ها را پارتیشن‌های مختلف تقسیم می‌کنند. عمومی‌ترین معیارها عبارتند از:

پارتیشن‌بندی محدوده‌ای (Range partitioning)

انتخاب پارتیشن براساس محدوده آن و کلید داده شده صورت می‌پذیرد. فرض کنید که اطلاعات کدپستی ده رقمی در پایگاه داده در ۹ دسته ۱۰۰۰۰۰۰۰۰۰ تقسیم شده اند. در صورتی که کلید داده شده در یکی از این ۹ محدوده باشد، فقط اطلاعات موجود در آن محدوده مورد بررسی قرار خواهند گرفت. به عنوان مثال کلید (کدپستی) ۶۸۹۵۶۳۲۶۵۳ مشخص می کند که فقط محدوده ۶۰۰۰۰۰۰۰۰۰ تا ۶۹۹۹۹۹۹۹۹۹ بررسی شود. یعنی تقریبا ۱/۹ کل اطلاعات جدول مورد نظر.

پارتیشن‌بندی فهرست‌وار (List partitioning)

پارتیشن‌ها بر اساس یک فهرست مشخص می‌شوند. به عنوان مثال فهرست استان‌های غربی، شرقی، مرکزی و... . مثلا استان‌های آذربایجان غربی، آذربایجان شرقی، اردبیل و زنجان می‌توانند پارتیشنی با عنوان استان‌های شمال‌غریی تشکیل دهند. در صورتیکه استان یک آدرس اردبیل باشد، فقط داده‌های استان‌های شمال‌غربی بررسی می‌شوند.

پارتیشن‌بندی درهم (Hash partitioning)

در این نوع پارتیشن‌بندی، داده‌ها بر اساس یک کلید و تابع درهم‌سازی (hash function) به پارتیشن‌های مختلف تقسیم می‌شوند.

پارتیشن‌بندی کلیدی (Key partitioning)

این نوع پارتیشن‌بندی مانند پارتیشن‌بندی درهم است با این تفاوت که نحوه قرارگیری اطلاعات به صورت خودکار توسط پایگاه داده صورت می‌گیرد.

پارتیشن‌بندی ترکیبی (Composite partitioning)

همانگونه که از نام وی مشخص است، پارتیشن‌بندی ترکیبی، مجموعه‌ای از انواع پارتیشن‌بندی‌ها است.

روش‌های ذخیره اطلاعات پارتیشن‌بندی شده

پارتیشن‌بندی، اطلاعاتی مانند کل پایگاه داده و یا جداول، ردیف‌ها و... را تقسیم کرده و در قطعات کوچک‌تر ذخیره می‌کند. برای تقسیم کردن ردیف‌های موجود در یک جدول مي‌توان به صورت افقی (Horizontal) و عمودی (Vertical) عمل کرد.

در حالت افقی (Horizontal) ردیف‌های مختلف در جدول‌های جداگانه ذخیره می‌شوند. به عنوان مثال در جدول مشتریان، افرادی که کدپستی آنان کمتر از 50000 باشد در جدول مشتریان شرقی (CustomerEast) و باقی افرادی که دارای کدپستی بالای 50000 می‌باشند در جدول مشتریان غربی (CustomerWest) ذخیره می‌شوند. همچنین برای دستیابی به تمامی مشتریان می توان با استفاده از UNION یک VIEW ایجاد کرد که شامل تمامی ردیف‌ها باشد.

در حالت عمودی (Vertical) ستون‌های کمتری در جدول اصلی قرار می‌گیرند و بقیه ستون‌ها به صورت متناظر در جدول‌های دیگر قرار می‌گیرند. عمومی‌ترین کاربرد این روش جداکردن ستون‌هایی با اطلاعات نسبتا ثابت و ستون‌هایی با اطلاعات متغیر است. نسخه فعلی MySQL 5.6 از حالت عمودی پشتیبانی نمی‌شود.

چه زمانی و چرا به پارتیشن‌بندی پایگاه داده نیاز داریم؟

در موارد زیر استفاده از پارتیشن‌بندی به افزایش محسوس بازدهی می‌انجامد:

• داده‌های خیلی زیاد
• کمبود حافظه RAM
• داده‌هایی با دسته‌بندی‌های مشخص

هنگام اجرای کوئری (Query) در صورتیکه جدول مورد نظر پارتیشن‌بندی نشده باشد، دستور بر روی کل جدول اجرا می‌شود. مانند:

حال اگر جدول مورد نظر را پارتیشن‌بندی نمایید، هنگام اجرای Query، محدوده‌ی بررسی فقط پارتیشن(های) مرتبط را شامل می‌شود. دستورات قبلی با فرض پارتیشن‌بندی جدول، محدوده‌های مشخص شده زیر را بررسی می‌نماید:

در ادامه این مطلب به پیاده‌سازی پارتیشن‌بندی در پایگاه داده مای‌اس‌کیوال براساس نسخه 5.6 به طور خلاصه می‌پردازم.

پیاده‌سازی پارتیشن‌بندی در MySQL

بهترین راه برای فراگیری هر مهارتی، مطالعه راهنمای خود منبع است. مبحث پارتیش‌بندی اطلاعات در پایگاه داده بسیار گسترده بوده و دارای نکات ریز و درشت زیادی می‌باشد که پرداختن به همه‌ی آن‌ها از حوصله این مقاله خارج است. با این حال به صورت خلاصه نحوه ایجاد پارتیشن‌ها در زیر ارائه شده‌اند.

نحوه ساخت پارتیشن محدوده‌ای (Range partitioning)

CREATE TABLE t1 (
id int 
) ENGINE=InnoDB
# or MyISAM, ARCHIVE
PARTITION BY RANGE (id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
)

نحوه ساخت پارتیشن فهرست‌وار (List partitioning)

CREATE TABLE t1 (
id int 
) ENGINE=InnoDB
PARTITION BY LIST (id)
(
PARTITION P1 VALUES IN (1,2,4),
PARTITION P2 VALUES IN (3,5,9)
)

نحوه ساخت پارتیشن درهم (Hash partitioning)

CREATE TABLE t1 (
id int not null primary key
) ENGINE=InnoDB
PARTITION BY HASH (id)
PARTITIONS 10;

نحوه ساخت پارتیشن کلیدی (Key partitioning)

CREATE TABLE t1 (
id int not null primary key
) ENGINE=InnoDB
PARTITION BY KEY ()
PARTITIONS 10;

در تمامی موارد بالا پارتیشن توسط ستونی از نوع عدد (INTEGER) مشخص شده است. با این حال شما می‌توانید به جای این ستون با رعایت موارد زیر پارتیشن‌بندی را بسط دهید:

• اگر ستونی از به صورت Unique Key یا Primary Key تعریف شده باشد، پارتیشن‌بندی باید این ستون در بر گیرد.
• در ساخت پارتیشن می توانید به جای یک ستون، از عبارت (شامل توابع MySQL) استفاده نمایید. با این شرط که عبارت مورد نظر مقدار عددی را بازگرداند.
• در صورت ندانستن محدود عددی می توانید از عبارت MAXVALUE برای بازه بالا استفاده نمایید.
• حداکثر تعداد پارتیشن‌ها 210=1024 می باشد.

برای درک بیشتر موارد بالا با نحوه ساخت پارتیشن در جدول های t1 و t2 در زیر توجه فرمایید:

CREATE TABLE t1 (
d date
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(d))
(
PARTITION P1 VALUES 
LESS THAN (1999),
PARTITION P2 VALUES 
LESS THAN (2005),
PARTITION P3 VALUES 
LESS THAN (2010),
PARTITION P4 VALUES 
LESS THAN (MAXVALUE)
)

CREATE TABLE t2 (
d date
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES 
LESS THAN (TO_DAYS('1999-01-01')),
PARTITION P2 VALUES 
LESS THAN (TO_DAYS('2005-01-01')),
PARTITION P3 VALUES 
LESS THAN (TO_DAYS('2010-01-01')),
PARTITION P4 VALUES 
LESS THAN (MAXVALUE)
)

پارتیشن‌بندی چقدر در سرعت اجرای دستورات تاثیر دارد؟

برای بررسی تاثیر پارتیشن‌بندی نیاز به یک پایگاه داده با مقدار قابل توجهی اطلاعات داریم. برای این منظور از پروژه MySQL Employees Test Database که شامل حدودا ۴ میلیون رکورد در ۶ جدول است استفاده کرده‌ایم. در زیر ساختار این پایگاه داده را مشاهده می‌کنید:

به جستار (Query) زیر و زمان اجرای آن توجه فرمایید:

SELECT COUNT(*) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';
+----------+
| COUNT(*) |
+----------+
|   260957 |
+----------+
1 row in set (1.07 sec)

و همان دستور بعد از پارتیشن‌بندی:

SELECT COUNT(*) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';
+----------+
| COUNT(*) |
+----------+
|   260957 |
+----------+
1 row in set (0.32 sec)

برای مشاهده عملکرد پارتیشن‌بندی برای افزایش سرعت حذف اطلاعات به جستار (Query) زیر و زمان اجرای آن توجه فرمایید:

DELETE FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';
Query OK, 247489 rows affected (10.57 sec)

و همان دستور حذف بعد از پارتیشن‌بندی:

ALTER TABLE salaries DROP PARTITION p15;
Query OK, 0 rows affected (1.35 sec)

نحوه پارتیشن‌بندی جدول salaries در پایگاه داده employees

در زیر دستور پارتیشن‌بندی جدول حقوق‌ها (salaries) به صورت محدوده‌ای (RANGE) بر اساس تاریخ سال from_date مشاهده می‌فرمایید:

ALTER TABLE salaries 
PARTITION BY RANGE (YEAR(from_date))
(
PARTITION p1 VALUES LESS THAN (1985),
PARTITION p2 VALUES LESS THAN (1986),
PARTITION p3 VALUES LESS THAN (1987),
PARTITION p4 VALUES LESS THAN (1988),
PARTITION p5 VALUES LESS THAN (1989),
PARTITION p6 VALUES LESS THAN (1990),
PARTITION p7 VALUES LESS THAN (1991),
PARTITION p8 VALUES LESS THAN (1992),
PARTITION p9 VALUES LESS THAN (1993),
PARTITION p10 VALUES LESS THAN (1994),
PARTITION p11 VALUES LESS THAN (1995),
PARTITION p12 VALUES LESS THAN (1996),
PARTITION p13 VALUES LESS THAN (1997),
PARTITION p14 VALUES LESS THAN (1998),
PARTITION p15 VALUES LESS THAN (1999),
PARTITION p16 VALUES LESS THAN (2000),
PARTITION p17 VALUES LESS THAN (2001),
PARTITION p18 VALUES LESS THAN (2002),
PARTITION p19 VALUES LESS THAN (2003),
PARTITION p20 VALUES LESS THAN (MAXVALUE)
)

اگر با خطای Cannot delete or update a parent row: a foreign key constraint fails مواجه شدید، با دستور زیر کلید خارجی salaries_ibfk_1 را حذف نمایید:

ALTER TABLE salaries DROP FOREIGN KEY salaries_ibfk_1;

منابع و اطلاعات بیشتر

• Partition (database)
• MySQL 5.6 Reference Manual :: Partitioning
• Sample database with test suite
• MySQL Sandbox
• Partitions Performance with MySQL 5.1 and 5.5
• MySQL partitions tutorial

شاد و موفق باشید.