Предлагаю рассмотреть партиции. Заодно те, кто ими не пользуются, смогут оценить их значимость. И, возможно, начнут их использовать.
Общая теория.
Итак, что же такое "партиция"? Чтобы это понять, рассмотрим ситуацию, которая возникает у всех без исключения, кто работает с быстрорастущей базой данных. А ситуация простая: по мере роста объема данных увеличивается время выборки данных, несмотря на наличие правильных индексов и другие ухищрения программиста по оптимизации запроса. А ведь, чаще всего, используется только часть данных. Например, наиболее "свежие". Допустим, за 1-2 последних месяца, хотя в таблице хранятся данные за 2-3-4 года. Но и старые данные изредка используются, поэтому их нельзя удалить.
Что можно сделать? Ну, например, разбить большую таблицу на несколько более мелких таблиц с одинаковой структурой. Один месяц - одна таблица. При создании запроса определяем, какие таблицы надо использовать и объединяем их, использую UNION.
Примерно так:
использование UNION
select * from table_part10
union
select * from table_part11
union
select * from table_part12
В принципе, да, вариант на 100% рабочий. Вот только при каждом запросе необходимо проверять условия, чтобы выбрать нужный набор таблиц. При каких-то сложных условиях, возможно, будет необходимо применять их к каждой таблице. Также очень большой гимор получаем, если надо изменить структуру таблиц - это надо проделать с каждой из них.
Еще один минус - надо постоянно следить, куда пишутся данные (должны писаться в "свою" таблицу, согласно выбранному условию), да еще не забывать своевременно создавать эти таблицы. Короче говоря, очень много дополнительных телодвижений.
Так вот. Партиции как раз берут на себя всю эту работу: разбивку на части (которые, собственно говоря, и называются партициями), подбор нужных партиций при выборке, определение партиции при вставке данных. Задача программиста очень сильно упрощается: после создания таблицы с партициями он работает с этой таблице также, как и с любой другой. Но при этом, несмотря на большое количество данных, в выборках, чаще всего, участвуют только некоторые части, а не все. Это приводит к (существенному) ускорению работы с данными.
Далее возникает вопрос совершенно закономерный вопрос: при каком объеме данных имеет смысл делать партиции? Тут же ведь, кроме собственно выборок, делаются дополнительные действия по подбору тех партиций, которые должны участвовать в выборке. Естественно, это тоже требует времени. Поэтому при совсем мало количестве данных партиции будут больше тормозить процесс, чем ускорять.
Точных цифр я дать не могу. Дам только информацию к размышлению :) Если в таблице всего 20-30-50 тысяч строк, то партиции вряд ли нужны. Если их там 2-3 млн, не говоря уж о десятках миллионов, то тогда имеет смысл дробить на партиции. При условии, еще раз подчеркну, что имеются четкие критерии разделения на части: даты (месяцы или недели года), айдишники, группы айдишников или еще что-нибудь, что будет использоваться в условиях в каждой выборке.
Общая теория, которую я вкратце рассказал, относится как к Постгре, так и к Мускулю. И заодно ко всем БД, использующим партиции в своей работе. А вот дальше рассмотрим партиции применительно к Мускулю и Постгре, именно в таком порядке.
Партиции в Мускуле.
Здесь всё достаточно просто. Всё сделано так, чтобы работа с партициями была простой для программиста, чтобы ему делать как можно меньше телодвижений. В этом смысле я был приятно удивлен, когда поработал с партициями.
Дабы не писать много слов, уже хорошо сказанных другими людьми, я отошлю на хабр: http://habrahabr.ru/post/66151/ В этой статье очень кратко, красочно и понятно описано партицирование в Мускуле. Рекомендую сначала прочитать статью, а затем уже дальше читать то, что я тут написал. Также можно более подробно прочитать тут http://www.rldp.ru/mysql/mysqlpro/parts.htm либо в официальной документации Мускуля.
Я же, предоставив хабру и другим источникам вдаваться в детали, остановлюсь на плюсах и минусах принятой в Мускуле системы партицирования.
К основному плюсу можно отнести то, о чем я уже говорил: с партициями работать очень просто. Разбивку на партиции можно задать как при создании таблицы, так и позже, при уже имеющихся данных. Можно дополнять критерии разбивки, например, добавить новые месяц и/или годы в критерии разбивки.
Удобством является то, что в качестве критерия разбивки могут служить функции от данных из таблицы
PARTITION BY RANGE( TO_DAYS(order_date) ) (
Но плюсы одновременно могут приводить и к минусам. В частности, простота включения партиций приводит к тому, что с каждой из партиций нельзя работать также, как с обычной таблицей. По крайней мере, это не будет полноценная работа.
Партиции в Постгре.
Исторически так сложилось, что с Постгре я работал больше, чем с Мускулем. И с партициями я познакомился также сначала в Постгре. В данной статье я не только дам ссылки, но и поделюсь кое-какими своими "хитростями" работы с партициями, которые позволяют им быть "на голову выше" партиций в Мускуле. Апологеты Мускуля могут попробовать опровергнуть эту мою уверенность :) Но только единственным способом - покажите, что такие же тонкости возможны в Мускуле, и я с вами тут же соглашусь. Если, конечно, сможете их найти, такие же тонкости.
Прежде, чем писать "отсебятину", дам пару ссылок: http://habrahabr.ru/post/75906/ на хабр и http://postgresql.ru.net/manual/ddl-partitioning.html на официальную документацию.
Самое первое - в Постгре работа с партициями подразумевает немного больше работы на начальном этапе, т.е. при создании партиций. Тут недостаточно просто одной-двух команд. Это, теоретически, можно отнести к минусу... Но как мы увидим дальше, во время этого процесса мы сразу же настраиваем множество тонких моментов, недоступных в Мускуле.
Итак... Какова специфика работы с партициями в Постгре?
1. Партиция создается как отдельная таблица, специальной командой, которая говорит о том, что "вот эта таблица будет наследником вон той таблицы".
2. Партиция не наследует никаких индексов, их надо создавать индивидуально для каждой части.
3. Так как партиции являются отдельными таблицами, то их можно индивидуально архивировать, удалять, изменять у них индексы... Короче говоря, делать много чего.
4. Запись в партиции обеспечивается с помощью триггера вставки BEFORE INSERT. В этом триггере мы переписываем данные в нужную часть (партицию) и отказываемся от вставки в основную (назовем ее материнскую) таблицу.
На мой взгляд, такой подход более удобен, чем в Мускуле, т.к. позволяет мне, как программисту БД, полностью контролировать процесс. Давайте по порядку рассмотрим те пункты, что я перечислил, их плюсы и минусы.
1. В Постгре есть такое понятие, как "схема" (schema). В каждой схеме мы можем создать любое количество таблиц и функций. Любые таблицы в пределах одной БД доступны для работы, необходимо только указать перед названием таблицы ее схему. Дефолтная схема, создаваемая автоматически в самом начале, называется "public", для ее таблиц не требуется указание имени схемы (хотя и не запрещено).
Так вот. Мы можем создать таблицы-партиции в какой-то отдельной схеме. Ну, допустим, она будет называться "basket", т.е. корзина. Не мусорная, а та, куда мы будем складировать наши основные данные. Кстати говоря, можно при создании партиций расположить их физически на разных жестких дисках, индивидуально для каждой части.
При этом "материнскую" таблицу расположим в схеме public, хотя бы потому, чтобы при запросах не указывать имя схемы. Да и просто для того, чтобы была четкая структура данных.
2. Чему удобно то, что индексы индивидуальны? Допустим, у нас создается по 5 индексов, парочка из которых задействуют по несколько полей. Места на диске такие индексы могут занимать достаточно много. Но вот для "старых" частей партиции мы можем удалить эти "тяжелые" индексы, все равно они редко используются. С другой стороны, если на понадобится новый индекс, мы можем его сделать только для нескольких самых "свежих" частей и создавать для будущих частей, не трогая старые части.
3. Если мы видим, что какие-то части стали совсем не нужными, то мы можем их сначала на всякий случай забэкапить, а затем удалить. Зачем бэкапить? Затем, что если вдруг они понадобятся (какие-нибудь новые отчеты за прошлые периоды), то их всегда можно восстановить и использовать.
4. Использование триггера дает нам свободу :) И также то, что если (вдруг) мы что-то не учли и какие-то данные не попадают в партиции, то они никуда не пропадут, не дадут нам ошибок. Они просто будут записаны в "материнскую" таблицу. Они также будут использоваться при выборках. Найдя их там, мы сможем создать для них правильные условия партицирования и переписать в нужную часть - у нас же материнская таблица и ее партиции являются отдельными таблицами, мы можем свободно переписывать между ними любые данные :)
А теперь от слов к делу. Приведу пример "живого" триггера, который пишет данные в партиции. Все комментарии приведены внутри кода триггерной функции. Тут только обращу внимание на то, что внутри триггера я динамически формирую команды SQL, по мере необходимости.
В схеме public имеется таблица block_180, на которую навешен триггер вставки
триггер вставки
CREATE TRIGGER insert_block_180
BEFORE INSERT
ON block_180
FOR EACH ROW
EXECUTE PROCEDURE block_180_insert_trigger();
триггерная функция для создания партиций
CREATE OR REPLACE FUNCTION block_180_insert_trigger()
RETURNS trigger AS
$BODY$
DECLARE
new_table varchar(40);
uniq_name varchar(20);
d varchar(10) array[2]; /* массив из 2-х элементов */
BEGIN
/* определяем переменных для создания имен новых таблиц и индексов */
uniq_name := get_uniq_table_name( NEW.date_ );
new_table := 'basket.block_180_' || uniq_name;
BEGIN /* блок вставки */
EXECUTE 'insert into ' || new_table || ' select $1.* ' USING NEW;
/* если вставка невозможна по причине отсутствия таблицы, то создаем ее!!! */
EXCEPTION WHEN SQLSTATE '42P01' THEN
BEGIN
/* создаем таблицу с уникальным именем, как наследника известной и индексы для нее */
EXECUTE 'create table ' || new_table || '() inherits (block_180) ';
/* создаем индексы для новой таблицы; задав несколько команд, можно создать несколько индексов */
/* в данном случае создается индекс на 4 колонки */
EXECUTE 'CREATE INDEX block_180_date_' || uniq_name || ' ON ' || new_table || ' USING btree (date_, omc, bsc, h )';
/* настройка дат для CONSTRAINT */
d := get_month_days( NEW.date_ );
/* условия, используемые при выборке: в данном случае диапазон дат, от 1 до последнего месяца */
EXECUTE 'alter table ' || new_table || ' add check (date_ between ''' || d[1] || ''' and ''' || d[2] || ''' ) ';
/* пишем повторно; та же команда вставки, но в уже существующую таблицу */
EXECUTE 'insert into ' || new_table || ' select $1.* ' USING NEW;
END; /* таблица создана, данные записаны */
END;
RETURN NULL; /* отказываемся от вставки данных в материнскую таблицу */
END;
$BODY$
В результате выполнения такой функции может быть создана таблица с именем типа basket.block_180_2014_m12, куда будут писаться данные определенного типа за декабрь 2014 года. Самое существенно тут то, что можно не париться насчет создания таких таблиц, они будут созданы автоматически и именно тогда, когда нужны. И при этом я не делаю никаких проверок вообще (о существовании таблиц), просто пишу в таблицу с нужным именем, которое формируется на основе записываемых данных. Если таблицы не существует, то будет вызван блок обработки эксепшенов, внутри которого всё будет создано: собственно таблица, ее индексы и constraint'ы.
Обращение к разным частям партиции
Если нам необходимо обратиться к данным из любой партиции (наиболее частое использование), т.е. мы предоставляем право БД самой выбрать нужные части, то пишем в запросе что-то типа
Если нам необходимо выбрать данные из наследника, то запрос также будет достаточно прост:
Но вот если мы хотим выбрать данные только из материнской таблицы (при их наличии там), то надо добавить специальное ключевое слово ONLY:
select * from block_180 where ....
Если нам необходимо выбрать данные из наследника, то запрос также будет достаточно прост:
select * from basket.block_180_2014_m12 where ...
Но вот если мы хотим выбрать данные только из материнской таблицы (при их наличии там), то надо добавить специальное ключевое слово ONLY:
select * from only block_180 where ....
На основании всего вышесказанного, я бы поставил суммарный счет за партиции 5:3 в пользу Постгре. Потому что 3:3 можно смело ставить за партиции вообще, обеим БД, а 2:0 за специфику Постгре, позволяющую очень тонко, мощно и гибко работать с партициями.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
