پارتیشن بندی در Postgresql با استفاده از وراثت
لینک کوتاه سه شنبه , 25 اسفند 1394 4 دیدگاه
پارتیشن بندی اساسا در دیتاهای بزرگ سبب افزایش سرعت (قابل ملموس) دستورات SELECT میشود.
و همانطور که از اسمش پیداست براین اساس هست که رکوردهای یک جدول را به چندین پارت کوچکتر تقسیم میکند و براساس نوع تقسیم بندی فقط در یک یا چند پارت کوچکتر عملیات جستجو را انجام میدهد که این نتیجه خوبی در دیتاهای بارکورد بالا خواهد داشت ، دیتابیس هایی چون MySQL و MSSQL چنین قابلیتی رو دارا هستند که بسادگی پارتیشن بندی در آنها ایجاد کرد اما برای PostgreSQL کمی داستان متفاوت است. در حقیقت پارتیشن بندی در PostgreSQL با قابلیت وراثت جداول از یکدیگر قابل پیاده سازی میباشد به این ترتیب که یک جدول مادر ساخته میشود و جدولهای فرزند متعدد (که بعنوان پارت های جدا هستند) از جدول مادر ارث بری میکنند و وظیفه ذخیره داده ها را برعهده دارند .
در ادامه نحوه ساخت پارتیشن بندی دیتابیس در Postgresql را آموزش میدهیم .
فرض کنید ما تیبلی برای ذخیره سازی مطالب وبلاگ در نظر میگیرم
CREATE TABLE "post" (
"id" serial NOT NULL,
"title" character varying(255) NOT NULL,
"user_id" integer NOT NULL,
"story" text NOT NULL,
"status" smallint NOT NULL DEFAULT '1'
);
فرض کنیم قرار است نهایتا 3000 رکورد در این تیبل ذخیره شود ، خب ما میخواهیم این تیبل را به سه پارت 1000 رکوردی تقسیم کنیم براساس کلید اصلی !
ابتدا سه جدول میسازیم که از جدول مادر ارث بری کنند و در هر جدول حوزه مجاز id را با دستور check مشخص میکنیم .
CREATE TABLE POST1000
(
PRIMARY KEY (id) ,
CHECK(id>0 AND id<=1000)
) INHERITS (post);
CREATE TABLE POST2000
(
PRIMARY KEY (id) ,
CHECK(id>1000 AND id<=2000)
) INHERITS (post);
CREATE TABLE POST3000
(
PRIMARY KEY (id) ,
CHECK(id>2000 AND id<=3000)
) INHERITS (post);
سپس در جدولهای فرزند برای کلید id ، ایندکس تعریف میکنیم
CREATE INDEX "post1000_id" ON "post1000" ("id");
CREATE INDEX "post2000_id" ON "post2000" ("id");
CREATE INDEX "post3000_id" ON "post3000" ("id");
حالا باید یک تریگر بنویسیم که در زمان insert کردن به جدول post ، مقادیر را به جدول های فرزند منتقل کند .
CREATE OR REPLACE FUNCTION post_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.id>0 and NEW.id<=1000) THEN
INSERT INTO post1000 VALUES (NEW.*);
ELSIF (NEW.id>1000 and NEW.id<=2000) THEN
INSERT INTO post2000 VALUES (NEW.*);
ELSIF (NEW.id>2000 and NEW.id<=3000) THEN
INSERT INTO post3000 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'id is not valid for insert in partition table!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_post_trigger
BEFORE INSERT ON post
FOR EACH ROW EXECUTE PROCEDURE post_trigger();
حالا تعدادی رکورد مختلف اضافه میکنیم .
INSERT INTO "post" ("id", "title", "user_id", "story", "status")
VALUES
('1', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('3', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1000', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1001', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1002', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1999', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2000', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2001', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2002', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2003', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2999', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2998', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('3000', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('999', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ;
دقت کنید که پارامتر constraint_exclusion در فایل کانفیگ دیتابیس postgresql.conf برابر با ON باشد یا با دستور زیر اعمالش کنید
SET constraint_exclusion = on;
اکنون نتیجه کاملا گویاست !
این آموزش روی رکوردهای پایین انجام شده ، دقت کنید که بستگی به دیتاهای شما دارد ، ترجیحا از پارتهای زیادی استفاده نکنید چون هر چند پارتیشن بندی سرعت خواندن را افزایش میدهد سرعت insert,update را کند میکند (البته نه به همان نسبت )
پس در استفاده از این قابلیت زیاده روی نکنید :)
با تشکر ، رضا شیخله
نظرات :
بسیار عالی بود ادمین عزیز.