۱۳۹۱/۰۴/۰۴

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

سلام،
مطلب امروز در ابتدا به معرفی امکان پارتیشن بندی (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;


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


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

۲۳ نظر:

  1. متشکر
    بسیار عالی
    از چه ورژنی این قابلیت به MySQL اضافه شده؟

    پاسخحذف
  2. برای پارتیشن بندی، چطور باید تعداد سطرهای هر پارتیشن رو تشخیص بدیم؟


    ممنون

    پاسخحذف
  3. سلام،

    از نسخه ۵.۱ که توسط اوراکل منتشر شده:

    https://dev.mysql.com/doc/refman/5.1/en/partitioning.html

    MySQL 5.1 Community binaries provided by Oracle include partitioning support.

    ولی در طی ۵.۱.۵ و ۵.۱.۶ و ۵.۱.۷ تغییرات مختلفی داشته. پیشنهاد می کنم لینک زیر رو بخونین:
    https://dev.mysql.com/doc/refman/5.1/en/partitioning.html

    پاسخحذف
  4. باید از INFORMATION_SCHEMA و جدول partitions اطلاعات مورد نظرتون رو بگیرید. مانند:
    [sql]
    select
    partition_name part,
    partition_expression expr,
    from_seconds(partition_description) descr,
    table_rows
    FROM
    INFORMATION_SCHEMA.partitions
    WHERE
    TABLE_SCHEMA = 'test'
    AND TABLE_NAME='t2';
    [/sql]

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

    پاسخحذف
  6. مرسی سعید جان. مقاله فوق العاده خوبی بود. جدا عالی نوشتی و همینطور موضوع خیلی خوبی رو انتخاب کردی. سپاس فراوان

    پاسخحذف
  7. خوب بود.
    فقط اون قسمت آخر گفتی: «همان دستور حذف بعد از پارتیشن‌بندی:»
    ولی دیگه اون دستور رو نگفتی و تنها دستور حذف کردن پارتیشن رو نوشتی.

    پاسخحذف
  8. سلام،
    دستور پارتیشن‌بندی جدول salaries رو به مطلبم اضافه کردم.
    ممنون بابت یادآوری :)

    پاسخحذف
  9. مطالبت خیلی کامل هستن.
    ممنون

    پاسخحذف
  10. ممنون سعید جان
    مطلبت عالی بود
    فقط یه سوال داشتم، میشه تیبلی که وجود داره و داخلش رکورد هس رو پارتیشن بندی کرد ؟
    اگه آره، چطور ؟

    راستی از بین این چند پارتشین بندی، کدوم بهینه سازی تره ؟
    بازم ممنون

    پاسخحذف
  11. سلام،

    بله امکانش هست. توو همین مطلب نحوه ساختش وجود داره. هر روش هم مزایا و معایب خودش رو داره. توو همین مطلب تقریبا توضیح هر کدوم رو دادم.

    پاسخحذف
  12. ممنون سعید جان
    من یک تیبل دارم که حدود 3 میلیون رکورد داره و از طریق کد زیر میخواستم پارتیشن بندی کنم ولی با ارور "The used table type doesn't support FULLTEXT indexes" مواجه شدم (از ومپ سرور استفاده میکنم و نسخه mysql اَم هم 5.5.24)

    این کدی که اجرا کردم:

    ALTER TABLE posts
    PARTITION BY RANGE (id)
    (
    PARTITION p1 VALUES LESS THAN (10000),
    PARTITION p2 VALUES LESS THAN (20000),
    PARTITION p3 VALUES LESS THAN (30000),
    PARTITION p4 VALUES LESS THAN (40000),
    PARTITION p5 VALUES LESS THAN (50000),
    PARTITION p6 VALUES LESS THAN (60000),
    PARTITION p7 VALUES LESS THAN (70000),
    PARTITION p8 VALUES LESS THAN (80000),
    PARTITION p9 VALUES LESS THAN (90000),
    PARTITION p10 VALUES LESS THAN (100000),
    PARTITION p11 VALUES LESS THAN (110000),
    PARTITION p12 VALUES LESS THAN (120000),
    PARTITION p13 VALUES LESS THAN (130000),
    PARTITION p14 VALUES LESS THAN (140000),
    PARTITION p15 VALUES LESS THAN (150000),
    PARTITION p16 VALUES LESS THAN (160000),
    PARTITION p17 VALUES LESS THAN (170000),
    PARTITION p18 VALUES LESS THAN (180000),
    PARTITION p19 VALUES LESS THAN (190000),
    PARTITION p20 VALUES LESS THAN (200000),
    PARTITION p21 VALUES LESS THAN (210000),
    PARTITION p22 VALUES LESS THAN (220000),
    PARTITION p23 VALUES LESS THAN (230000),
    PARTITION p24 VALUES LESS THAN (240000),
    PARTITION p25 VALUES LESS THAN (250000),
    PARTITION p26 VALUES LESS THAN (260000),
    PARTITION p27 VALUES LESS THAN (270000),
    PARTITION p28 VALUES LESS THAN (280000),
    PARTITION p29 VALUES LESS THAN (290000),
    PARTITION p30 VALUES LESS THAN (300000),
    PARTITION p31 VALUES LESS THAN (210000),
    PARTITION p32 VALUES LESS THAN (320000),
    PARTITION p33 VALUES LESS THAN (330000),
    PARTITION p34 VALUES LESS THAN (340000),
    PARTITION p35 VALUES LESS THAN (350000),
    PARTITION p36 VALUES LESS THAN (360000),
    PARTITION p37 VALUES LESS THAN (MAXVALUE)
    )

    ID از جنس int(10) و کلید هسش.
    ممنون میشم کمکم کنید :)

    پاسخحذف
  13. جدولی که قصد پارتیشن بندی اون رو دارین index از نوع fulltext داره. این از محدودیت های پارتیشن‌بندی جدوله:

    FULLTEXT indexes. Partitioned tables do not support FULLTEXT indexes or searches. This includes partitioned tables employing the MyISAM storage engine.

    http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html
    راهنمای زیر میتونه به رفع این مشکل کمک کنه:
    http://linuxhostingsupport.net/blog/converting-table-to-innodb-the-used-table-type-doesnt-support-fulltext-indexes

    پاسخحذف
  14. سلام میشه لطفا مطلب یا لینک منبع در زمینه ی very small databases بذارید؟

    پاسخحذف
  15. درود
    بسیار عالی و مفید بود
    موفق باشید

    پاسخحذف
  16. باسلام خدمت آقای زبردست؛
    من با پایگاه دادم مشکل دارم می خواستم ایمیلم رو که براتون فرستادم با هم گفتگویی داشته باشیم. منم سوالام رو از شما بپرسم.
    سوالم رو از اینجا شروع می کنم که:
    چجوری میشه innobd رو توی هاست فعال کرد؟ می خوام محتوایی نصب کنم که نیاز به ایجاد تیبل های innobd داره! چجوریه؟؟

    پاسخحذف
  17. سلام،

    برای اینکه بدونین innodb براتون فعاله یا نه دستور زیر رو در خط فرمان MySQL وارد کنین:
    [code]
    SHOW VARIABLES LIKE 'have_innodb';
    [/code]

    در صورتی که خروجیتون مانند زیر باشه، یعنی innodb برای شما فعاله:
    [code]
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | have_innodb | YES |
    +---------------+-------+
    [/code]

    در صورتی که innodb برای شما فعال نبود از راهنمای زیر استفاده کنین:
    https://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#option_mysqld_innodb

    پاسخحذف
  18. سلام
    از زحمتی که کشیدید و مطلبی که گزاشتید واقعا سپاسگزارم برای من خیلی مفید بود. فقط دو تا سوال دارم اگر ممکنه راهنمایی کنید:
    من یک جدول دارم که الان نزدیک 3 ملیون رکورد داره و رکورداش هم به صورت روزانه در حال افزایش هست. جدول یک ستون از نوع TIME STAMP داره.
    سوال اول اینکه با چه دستوری می تونم جدولم رو به صورت روزانه با ستون TIME STAMP پارتیشن بندی کنم.
    سوال دوم اینکه آیا راهی وجود داره که به صورت داینامیک پارتیشن های جدولمو آپدیت کنم چون اگه قرار باشه لیست بدم خیلی طولانی میشه.

    ببخشید که وقتتونو می گیرم. منتظر جوابتون هستم.

    پاسخحذف
  19. MySQL برای پارتیشن بندی بر اساس Time Stamp چند مشکل داره. پیشنهاد میکنم که تاپیک زیر رو بخونید:
    http://stackoverflow.com/questions/12583773/mysql-5-6-partitioning-on-timestamp-with-microseconds

    به هر حال برای اینکار باید از پارتیشن بندی Range استفاده کنین.

    پاسخحذف
  20. سلام
    ممنون از پاسختون - من الان جدولمو پارتیشن بندی کردم - ولی تاثیر زیادی روی سرعت سلکتم نداشت.
    آیا برای سلکت باید از query خاصی استفاده کنم؟

    پاسخحذف
  21. سلام،

    با توجه به نوع پارتیشن بندی و Query ممکن است که به صورت مستقیم تاثیر این پارتیشن بندی را مشاهده نکنید. با این وجود در حالتی که جدول مورد نظر توسط Query های INSERT، UPDATE، DELETE و SELECT فراخوانی می‌شود، فقط پارتیشن های مورد نظر Lock شده و از Latency جلوگیری می‌نماید.

    پاسخحذف
  22. سلام
    اطلاعات بسیار خوبی مدتی بود که به دنبال مطالبی که می گشتم قسمتی از ان را اینجا پیدا کردم خیلی خوشحال شدم وعالی بود فقط اگر امکان دارددارد مورد نحوه عملکرد sharding چیزی برایم بگی ممنون می شوم

    پاسخحذف