Как получить пул данных за период по столбцам YEAR, MONTH, DAY?

Question

[nurzhannogerbek]

Здравствуйте, товарищи! Мне нужен ваш совет.

В базе данных MySQL есть таблица с подобной структурой (см. ниже). В этой таблице хранится порядка 20 млн. данных, а весит таблица порядка 3.5 ГБ. Задача как можно быстрее вывести данные за определенный период.

| DATE_TIME_KEY       | YEAR | MONTH | DAY | VALUE | 
|--------------------------------------------------|
| 2018-11-09 00:00:00 | 2018 | 11    | 9   | 1056  |
| 2018-11-09 01:00:00 | 2018 | 11    | 9   | 8756  |
| 2018-11-10 00:00:00 | 2018 | 11    | 10  | 6582  |
| 2018-11-10 01:00:00 | 2018 | 11    | 10  | 2382  |
|**************************************************|
| 2018-12-09 00:00:00 | 2018 | 12    | 9   | 6892  |
| 2018-12-09 00:00:00 | 2018 | 12    | 9   | 8592  |
| 2018-12-10 00:00:00 | 2018 | 12    | 10  | 9587  |
| 2018-12-10 00:00:00 | 2018 | 12    | 10  | 6587  |
|**************************************************|
| 2019-03-05 00:00:00 | 2019 | 3     | 5   |  5693 |
| 2019-03-05 00:00:00 | 2019 | 3     | 5   |  7593 |
| 2019-03-06 00:00:00 | 2019 | 3     | 6   |  5698 |
| 2019-03-06 00:00:00 | 2019 | 3     | 6   |  8598 |

На столбцы YEAR, MONTH, DAY было добавлено партиционирование, а на столбец DATE_TIME_KEY добавил индексирование.

Выполнил два следующих запроса, которые возвращает пул данных за один день. Оба запроса возвращают данные примерно за одинаковое время. Плюс минус 1.5 секунды.

Первый запрос:

SELECT * FROM big_table
WHERE YEAR=2018 AND MONTH=9 AND DAY=1

Второй запрос:

SELECT * FROM big_table
FORCE INDEX (date_time_index)
WHERE DATE_TIME_KEY BETWEEN '2018-09-01 00:00:00' AND '2018-09-01 23:59:59'

У меня возникает два вопроса.

Первый вопрос:
Что в теории быстрее, партиционирование или индексирование?

Второй вопрос:
Так как пул данных за один день в моем случаи оказался не показательным, я решил взять пул данных за месяц и замерить скорость возврата данных. Не знаю как правильно получить пул данных за определенный период по столбцам YEAR, MONTH, DAY. Помогите пожалуйста составить запрос.

Answer 1

[V Sh.]

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

Comments

[nurzhannogerbek]

Оператор EXPLAIN по первому запросу вернул следующее:

| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
|----------------------------------------------------------------------------------------------------------------------------|
| 1  | SIMPLE      | big_table | 2018_9_1   | ALL  | NULL          | NULL | NULL    | NULL | 120477 | 0.01     | Using where |

При этом на второй запрос оператор EXPLAIN возвращает такую информацию:

| id | select_type | table     | partitions                   | type   | possible_keys            | key             | key_len | ref  | rows   | filtered | Extra                  |
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1  | SIMPLE      | big_table | здесь список всех партиций   | range  | date_time_index          | date_time_index | 9       | NULL | 40     | 10.01    | Using index conditions |

Какой можно сделать вывод?

На счет кэширование у меня есть вопрос. Это можно как-то избежать?

[V Sh.]

NogerbekNurzhan, Кеширование - полезная штука для быстродействия. Насколько я знаю БД сохраняет план выполнения запроса (чтобы не строить его заново) и просто подставляет нужные параметры. Именно поэтому советуют использовать параметры в запросах, которые возвращают данные, а не использовать динамический SQL (там где возможно). Не совсем понятно, зачем его отключать. Если для тестов производительности, то, по-моему, есть специальные команды, которые на время отключают кеширование или сбрасывают буфер. Что касается конкретно этого примера, то тут не помогу: я работаю с Oracle в основном и через UI, там все красиво и отображается конкретная разбивка по операциям и их стоимости, а также показываются узкие места запросов. В основном это отсутствие индексов (FULLSCAN TABLE) и сортировка.

[nurzhannogerbek]

Понятно, а какой UI вы используете для Oracle?

Answer 2

[Stalker_RED]

Вероятно второй запрос быстрее, потому что выбирает по полю с индексом.
Возможно если создать индексы по year и month - будет быстрее. Возможно композитный по этим двум полям будет еще быстрее.

Comments

[nurzhannogerbek]

Здравствуйте! В самом посте я упоминал, что пул данных за день оба запроса обрабатывают за плюс минус одинаковое время. Поэтому я для примера решил получить пул данных за месяц. На самом деле у меня задача получить пул данных за определенный период. Может быть например такое: c 2018-09-15 по 2018-10-15. По сути тоже месячный период считай. Даже если я добавлю индексирование на поля YEAR, MONTH, DAY, как правильно составить запрос по этим полям?