Печать страницы - Партіціювання даних в mysql

Створив гілку, але скоріше тільки для себе - щоб не забути які я робив дослідження. Ну якщо комусь є щось сказати, можете висловлюватись)

Чим більше даних в базі даних, тім повільніше вона працює, це очевидно. Але в більшості випадків потрібні актуальні дані - ну, наприклад, платежи тільки за останній період. По дефолту при переході в розділ "платежі" там як раз і показуються платежі за останні декілька місяців. Для таких ситуацій в базах даних використовують фішку "партіціювання" - це коли дані однієї таблиці розділяються не декілька, умовно кажуючи таблиць. Наприклад, можна розділити по даті: за 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 години, а це малова-то для експертної думки

Биллинговая система Nodeny

Главная категория => Nodeny Plus => Тема начата: Efendy от 25 Июня 2024, 15:28:22