Оптимизация большой таблицы mysql, правильно ли?

Question

[Troodi Larson]

Есть большая таблица history, ежедневно добавляется 1+ млн строк. Делать выборку из одной таблицы очень ресурсозатратно и запросы выборки данных для определенного юзера за определенный интервал времени идут долго. Я думаю сделать разбиение таблиц по дням history_день_месяц_год, и промежуточную таблицу с днями когда юзера есть история за дни, чтобы не перебирать все таблицы, а только за те дни, когда записывалась история. Насколько имеет смысл партицирование в данной ситуации? Ведь иначе таблица будет увеличиваться до той поры, пока сервер уже совсем не справится с нагрузкой. Какие есть варианты решения?

Comments

[Илья]

может индексы?

[Troodi Larson]

Илья, для базы в миллиарды строк я думаю это не очень спасет ситуацию.

[Олег]

Troodi Larson, Не спец по BigData но... тут бы хотя бы пример.
Я всегда считал что дело не в том, СКОЛЬКО строк (какой объем) в базе. А то, каким образом написан запрос в нее.
Именно запрос отвечает за скорость работы всей СУБД.
А одна у вас будет таблица на миллион или по финалу 10 по миллиону...
А так, опять же как знаю я, JOINы быстрее. А соответственно ваш вариант логической связки для отбора разных таблиц - должен спасать. НО... может кто что подскажет и умнее.

[Troodi Larson]

Олег, Старые данные в целом не очень-то актуальны. Выборка только по user_id, и дате.

[d'Ivan]

Troodi Larson, тут по сценарию использования подходят СУБД временных серий и колоночные.
Возможно, ClickHouse в помощь.
Но сначала попробуйте с индексами.

[ComodoHacker]

Когда встает вопрос об оптимизации производительности, предметный разговор возможен только, если представлены:

• Структура таблиц и индексов (DDL)
  
• Текущий объем данных и скорость роста (это есть)
  
• Запросы, которые работают неудовлетворительно, с реальными планами
  
• Статистика их выполнения (желательно)
  

Иначе все советы это гадание на кофейной гуще.

[Вова Дружаев]

InnoDB или MyISAM?

[Андрей]

Первым делом оптимизация самой таблицы, допустим int или tinyint, вторым делом - сам запрос select который уходит к базе типа, уверен он ещё идёт joinом с другими таблицами, ну и порцирование, соответственно по дате добавления по месяцам основываясь на datetime

Answer 1

[Vitsliputsli]

Базы данных для того и создавались чтобы работать с большим объемом данных. 1 млн строк в день вполне нормальная работа. Весь вопрос как вы работаете с базой.

может индексы?

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

Почему вы так решили? Создавайте индексы, их вполне достаточно для большинства задач. если работает медленно, показывайте запрос и план.

Только если вы проводите серьезную аналитику по большому количеству данных, тогда стоит задумываться о колоночных БД.

Comments

[Troodi Larson]

Спасибо, но просто за несколько лет там будет уже более миллиарда строк, а это уже даже mysql будет сложновато, но это несколько мне известно. Выборка истории для user_id, может происходить за весь период. Вот тут весь вопрос - запрос элементарный, но какие объемы выдержит не ясно, хотя если до миллиарда выдержит, то нормально - но потом надо будет масштабировать

[Сергей Горностаев]

Troodi Larson, не думаю, что MySQL существенно отличается в этом вопросе, но у меня PostgreSQL на стандартных настройках достаточно быстро делал выборки из таблицы с 23 миллиардами записей.

[Troodi Larson]

Сергей Горностаев, Ого-го, ну это отлично. Спасибо! А через что вы просматривание данные в postgre, что-то pgadmin не очень удобен.

[Сергей Горностаев]

Troodi Larson, через консоль

[Troodi Larson]

Сергей Горностаев, понял, как вариант, да так данные просматривать проще. Спасибо большое!

Answer 2

[Dimonchik]

чем clickhouse не угодил? копируй в него и не жужжи

Comments

[Troodi Larson]

Да, тоже смотрю в эту сторону, ещё смотрю мнения людей.

Answer 3

[paldraken]

Используйте партиционирование (partitioning).
Это как раз и есть способ разбиения разбиения большой таблицы на логические части по определенным критериям. В вашем случае это будет поле дата. Например:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Пример отсюда
Впрочем миллион строк это действительно немного и возможно стоить начать с индексов