Створив гілку, але скоріше тільки для себе - щоб не забути які я робив дослідження. Ну якщо комусь є щось сказати, можете висловлюватись)
Чим більше даних в базі даних, тім повільніше вона працює, це очевидно. Але в більшості випадків потрібні актуальні дані - ну, наприклад, платежи тільки за останній період. По дефолту при переході в розділ "платежі" там як раз і показуються платежі за останні декілька місяців. Для таких ситуацій в базах даних використовують фішку "партіціювання" - це коли дані однієї таблиці розділяються не декілька, умовно кажуючи таблиць. Наприклад, можна розділити по даті: за 2023рік в такий таблиці, за 2024й в іншій таблиці і так далі. Насправді для нас це виглядає як одна таблиця, а всю работу під капотом виконує субд.
Що нам дає це: дікілька таблиць меньше ніж одна. Тому і запис і вибірка будуть швидше. Це і є основна ціль. Виникає питання: а як вибирати інформацію з великого діапазону, коли частина буде в одній, а частина в нішій "таблиці" (цях)? Величезний плюс в тому, що це робить сама СУБД! Вона знає коли звернутися до однієї таблиці, а коли до декількох.
Все. Далі піде технічна інфа, скоріше за все тільки для мене)
Я провів дослідження:
* як зробити партіціювання
* буде воно робити з реплікацією мастер-слейв
Підняв 2 докера з mysql-8 і налаштував реплікацію. Щось додаю на мастері - і це з'являється на слейві. Ура мені і докеркомпоузу.
Намагаюсь зробити партіціювання по полю time:
ALTER TABLE pays PARTITION BY RANGE (time) (
PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
PARTITION p2024 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
Ідея така: дані до 24 року зберігаю в партиції p2023, до 25го - в p2024, інші в pmax. Але це не працює:
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered
На цьому можна було закінчити. Короче, у мускула (чи скрізь так) обмеженне партіціювання - неможна розділяти на часті, якщо поле, по якому йде розділення не входить в прімарі кі. Я не знаю чому, може логіка в цьому є. Але мені потрібне вирішення проблеми.
Ладно, я використав більш простий варіант - розділяти по id. Нам потрібно буде просто вибрати який максимальний id в якому році:
ALTER TABLE pays PARTITION BY RANGE (id) (
PARTITION p2023 VALUES LESS THAN (3),
PARTITION p2024 VALUES LESS THAN (8),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
3 і 8 маленьки числа бо це все тестово.
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'pays';
показує що робить:
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2023 | 2 |
| p2024 | 3 |
| pmax | 0 |
+----------------+------------+
Виконав це на слейві - все робить так само.
Як отримати доступ до ціх розділів напряму? В постгресі я міг, тут не можу. В принципі, це не потрібно, але є випадок коли нам би це знадобилося - коли ми хочемо бекапити тільки частину поточного року. В інших бекапах будуть данні за інші роки, тому нам не потрібно втрачати час і дисковий простір на бекап старих даних. mysqldump дампить всю таблицу. Ну ок, не фартануло по цьому питанню. Але ж з гуглежем в 5 хвилин я можу і помилятися, просто для мене це питання непринципово.
Ітак. Як подивиться, що ми маємо якийсь профіт:
explain select * from pays where id<3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pays | p2023 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
тут бачимо, що профіт є - вибірка йде чисто з розділу p2023
Перевіряємо, що субд може шукати одразу по декількох розділів:
explain select * from pays;
+----+-------------+-------+------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | pays | p2023,p2024,pmax | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+-------+------------------+------+---------------+------+---------+------+------+----------+-------+
Але в бою ми профіта не отримаємо, бо нам був би корисний такий запит:
explain select * from pays where time<unix_timestamp('2020-01-01');Але він, очиковано, ніякого профіту не дає:
+----+-------------+-------+------------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | pays | p2023,p2024,pmax | range | time | time | 4 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------------+-------+---------------+------+---------+------+------+----------+-----------------------+
Тобто на даном етапі я не бачу варіанту отримати плюси від партіціювання. Можливо ми щось виграємо на швидкості вставки даних в таблицю pays. Але це потрібно проводити дослідження на великіх даних. При цьому я не думаю, що зараз є у когось проблеми саме при створюванні даних в таблиці pays.
Але. Все може бути не так як я думаю, бо я витратив на все 4 години, а це малова-то для експертної думки
