Секционирование таблицы

Помогите разобраться с принципами работы секционирования.

Дано: таблица с данными вида date, user_id, param1, param2 etc. Таблица секционирована по date. (.. by range month(date)) и состоит соотвественно из 12 секций. Секционирование безусловно увеличило производительность запросов, однако на explain partitions видно, что проверяются все секции таблицы, т.е. partition pruning не задействован. В документации написано, что функция month не оптимизированна для partition pruning.

Вопросы:
1. За счет чего увеличивается скорость выполнения запроса, если по прежнему проверяются все секции таблицы?
2. Можно ли динамически создавать секции или как лучше организвать секционирование, чтобы учитывался partition pruning?
3. Есть ли смысл в субсекционирвании по user_id?

Спасибо.

запрос, его explain и ddl таблиц. статистику по распределению значений для полей во where

ScareCrow,

да мне как бы просто хочется теоретически понять ну или может кто-то даст ссылки где можно об этом прочитать.

на твои вопросы без этих данных не ответишь.
по month прунинга нет. если у тебя именно что 12 секций можно сделать месяц интежером и партиционировать по нему.
ну вот еще почитай да.
http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/mysql_5.1_partitioning_with_dates.html

лиюо в запросе подставлять месяц явно как число.

ScareCrow,

то что MONTH() не поддерживает прунинг, это я знаю. Это и написано по ссылке, которую я дал. Если сделать 12 фиксированных партиций, то я могу покрыть текущий год, а в конце его придется опять пересоздавать таблицу на следующий. Я правильно понимаю? Динамически ведь невозможно добавить партицию? Переписывать запросы, передавая месяц, - плохое решение. Слишком много придется переписывать.
И все же, если прунинг не поддерживается через MONTH(), то за счет чего достигается производительность? И еще, нужно ли обращать внимание на написание запроса. К примеру после WHERE сначала писать параметр, по которому секционируется таблица, или оптимизатор сам знает, как лучше сделать выборку?

saturnxxi

Помогите разобраться с принципами работы секционирования. Дано: таблица с данными вида date, user_id, param1, param2 etc. Таблица секционирована по date. (.. by range month(date)) и состоит соотвественно из 12 секций. Секционирование безусловно увеличило производительность запросов, однако на explain partitions видно, что проверяются все секции таблицы, т.е. partition pruning не задействован. В документации написано, что функция month не оптимизированна для partition pruning.

Так partition pruning не обязан работать всегда, это зависит от запроса, если есть там SAGR-и по условию партицирования, то partition pruning потенциально может работать, если нет -- он бессмысленен.

Запросы показывай конкретные, тогда можно будет что -то сказать.

Вопросы:
1. За счет чего увеличивается скорость выполнения запроса, если по прежнему проверяются все секции таблицы?

Ни за счёт чего. Если тебе надо в запросе обрабатывать данные из всех партиций, то запрос от партицирования никак не убыстряется, а порой даже наоборот.

2. Можно ли динамически создавать секции или как лучше организвать секционирование, чтобы учитывался partition pruning?

Я не понял вопрос. Секционирование -- вообще достаточно статичная штука. Ты хочень пересоздавать разделы перед каждым запросом? Глупость, если так.

3. Есть ли смысл в субсекционирвании по user_id?

Зависит от твоих данных и запросов.

то что MONTH() не поддерживает прунинг, это я знаю. Это и написано по ссылке, которую я дал. Если сделать 12 фиксированных партиций, то я могу покрыть текущий год, а в конце его придется опять пересоздавать таблицу на следующий. Я правильно понимаю?


Нет, можно просто секционировать по месяцу, и писать в каждую партицию январь, февраль и так далее, от разных лет.
Можно и добавлять и удалять партиции, только я не назвал бы это "динамическим".


Динамически ведь невозможно добавить партицию? Переписывать запросы, передавая месяц, - плохое решение. Слишком много придется переписывать.


Это -- единственно правильное решение.

И все же, если прунинг не поддерживается через MONTH(), то за счет чего достигается производительность? И еще, нужно ли обращать внимание на написание запроса. К примеру после WHERE сначала писать параметр, по которому секционируется таблица, или оптимизатор сам знает, как лучше сделать выборку?


Я не понял, неужели ты считаешь, что оптимизатор сам выдерет месяц из даты и обрежет ненужные партиции ?
Нет, надо в явном виде указывать условие секционирования в WHERE запроса.
Ну или делай тогда секции по дате, по диапазонам дат.

Ни за счёт чего. Если тебе надо в запросе обрабатывать данные из всех партиций, то запрос от партицирования никак не убыстряется, а порой даже наоборот.

Поэтому я и хочу, чтобы был прунинг. Чтобы запросы, которые затронут несколько секций, были производительными. И что значит "ни за счет чего"? Прунинг ведь это просто дополнительная оптимизация.

Я не понял вопрос. Секционирование -- вообще достаточно статичная штука. Ты хочень пересоздавать разделы перед каждым запросом? Глупость, если так.

Чтобы добиться прунинга, я могу создать 12 партиций используя TO_DAYS('yyyy-mm-dd') на текущий год, но тогда по истечении периода, по которому были созданы секции, придется создавать новую таблицу с новыми секциями для нового периода. Или можно просто добавить партицию, или изменить существующие? Не хочу я пересоздавать разделы перед каждым запросом.

автор
3. Есть ли смысл в субсекционирвании по user_id? Зависит от твоих данных и запросов.

Все запросы у меня имеют приблизительно такой вид:

SELECT ... FROM ... WHERE date >= 'x' AND date <= 'y' AND user_id = z GROUP BY... 

Т.е. колонка, по которой устроены секции, почти всегда присутствует как и user_id.

Я не понял, неужели ты считаешь, что оптимизатор сам выдерет месяц из даты и обрежет ненужные партиции ?

Пример запроса указан выше. Меня интересует последовательность WHERE, нужно ли всегда начинать с даты, или оптимизатор сам разберется при построении запроса?

Пример запроса указан выше.

Лучше было бы полный запрос и полный DDL таблицы.

Меня интересует последовательность WHERE, нужно ли всегда начинать с даты, или оптимизатор сам разберется при построении запроса?


У тебя запрос:

SELECT ... FROM ... WHERE date >= 'x' AND date <= 'y' AND user_id = z GROUP BY... 

и партиция по:

month(date)

Чтобы пошло отсечение партиций нужно что-то типа:

SELECT ... 
FROM ... 
WHERE 
      month(date) in (1,2 3)
and date >= 'x' AND date <= 'y' 
AND user_id = z 
GROUP BY...