سلام،
مطلب امروز در ابتدا به معرفی امکان پارتیشن بندی (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;
منابع و اطلاعات بیشتر
شاد و موفق باشید.
متشکر
پاسخحذفبسیار عالی
از چه ورژنی این قابلیت به MySQL اضافه شده؟
برای پارتیشن بندی، چطور باید تعداد سطرهای هر پارتیشن رو تشخیص بدیم؟
پاسخحذفممنون
سلام،
پاسخحذفاز نسخه ۵.۱ که توسط اوراکل منتشر شده:
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
باید از 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]
درود بر شما
پاسخحذفواقعا این پست شما یکی از اون مطالب نابی و کمیابی هست که کمتر تو وبلاگستان فارسی دیده میشه.
بسیار استفاده بردم.
واقعا سپاسگذارم
مرسی سعید جان. مقاله فوق العاده خوبی بود. جدا عالی نوشتی و همینطور موضوع خیلی خوبی رو انتخاب کردی. سپاس فراوان
پاسخحذفخوب بود.
پاسخحذففقط اون قسمت آخر گفتی: «همان دستور حذف بعد از پارتیشنبندی:»
ولی دیگه اون دستور رو نگفتی و تنها دستور حذف کردن پارتیشن رو نوشتی.
سلام،
پاسخحذفدستور پارتیشنبندی جدول salaries رو به مطلبم اضافه کردم.
ممنون بابت یادآوری :)
مطالبت خیلی کامل هستن.
پاسخحذفممنون
ممنون سعید جان
پاسخحذفمطلبت عالی بود
فقط یه سوال داشتم، میشه تیبلی که وجود داره و داخلش رکورد هس رو پارتیشن بندی کرد ؟
اگه آره، چطور ؟
راستی از بین این چند پارتشین بندی، کدوم بهینه سازی تره ؟
بازم ممنون
سلام،
پاسخحذفبله امکانش هست. توو همین مطلب نحوه ساختش وجود داره. هر روش هم مزایا و معایب خودش رو داره. توو همین مطلب تقریبا توضیح هر کدوم رو دادم.
ممنون سعید جان
پاسخحذفمن یک تیبل دارم که حدود 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) و کلید هسش.
ممنون میشم کمکم کنید :)
جدولی که قصد پارتیشن بندی اون رو دارین 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
سلام میشه لطفا مطلب یا لینک منبع در زمینه ی very small databases بذارید؟
پاسخحذفدرود
پاسخحذفبسیار عالی و مفید بود
موفق باشید
باسلام خدمت آقای زبردست؛
پاسخحذفمن با پایگاه دادم مشکل دارم می خواستم ایمیلم رو که براتون فرستادم با هم گفتگویی داشته باشیم. منم سوالام رو از شما بپرسم.
سوالم رو از اینجا شروع می کنم که:
چجوری میشه innobd رو توی هاست فعال کرد؟ می خوام محتوایی نصب کنم که نیاز به ایجاد تیبل های innobd داره! چجوریه؟؟
سلام،
پاسخحذفبرای اینکه بدونین 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
سلام
پاسخحذفاز زحمتی که کشیدید و مطلبی که گزاشتید واقعا سپاسگزارم برای من خیلی مفید بود. فقط دو تا سوال دارم اگر ممکنه راهنمایی کنید:
من یک جدول دارم که الان نزدیک 3 ملیون رکورد داره و رکورداش هم به صورت روزانه در حال افزایش هست. جدول یک ستون از نوع TIME STAMP داره.
سوال اول اینکه با چه دستوری می تونم جدولم رو به صورت روزانه با ستون TIME STAMP پارتیشن بندی کنم.
سوال دوم اینکه آیا راهی وجود داره که به صورت داینامیک پارتیشن های جدولمو آپدیت کنم چون اگه قرار باشه لیست بدم خیلی طولانی میشه.
ببخشید که وقتتونو می گیرم. منتظر جوابتون هستم.
MySQL برای پارتیشن بندی بر اساس Time Stamp چند مشکل داره. پیشنهاد میکنم که تاپیک زیر رو بخونید:
پاسخحذفhttp://stackoverflow.com/questions/12583773/mysql-5-6-partitioning-on-timestamp-with-microseconds
به هر حال برای اینکار باید از پارتیشن بندی Range استفاده کنین.
سلام
پاسخحذفممنون از پاسختون - من الان جدولمو پارتیشن بندی کردم - ولی تاثیر زیادی روی سرعت سلکتم نداشت.
آیا برای سلکت باید از query خاصی استفاده کنم؟
سلام،
پاسخحذفبا توجه به نوع پارتیشن بندی و Query ممکن است که به صورت مستقیم تاثیر این پارتیشن بندی را مشاهده نکنید. با این وجود در حالتی که جدول مورد نظر توسط Query های INSERT، UPDATE، DELETE و SELECT فراخوانی میشود، فقط پارتیشن های مورد نظر Lock شده و از Latency جلوگیری مینماید.
سلام
پاسخحذفاطلاعات بسیار خوبی مدتی بود که به دنبال مطالبی که می گشتم قسمتی از ان را اینجا پیدا کردم خیلی خوشحال شدم وعالی بود فقط اگر امکان دارددارد مورد نحوه عملکرد sharding چیزی برایم بگی ممنون می شوم
سلام،
پاسخحذفسعی میکنم در این مورد هم بنویسم.