Дослідження швидкодії складеного первинного ключа в Postgres, MySQL та CockroachDB
Я довго працював з MySQL і за той час набув таке упередження стосовно повільності складеного первинного ключа (composite primary key), що уникав його використання навіть в Postgres.
Щоб позбутись упередження, я протестую на швидкодію вставки в наступні таблиці:
Складений унікальний ключ | Складений первинний ключ |
---|---|
CREATE TABLE user_favorite_companies_v1 ( id SERIAL, user_id INT NOT NULL, company_id INT NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL, updated_at TIMESTAMP WITH TIME ZONE NOT NULL, favorite_state BOOLEAN NOT NULL, PRIMARY KEY (id), UNIQUE (user_id, company_id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (company_id) REFERENCES companies (id) ); | CREATE TABLE user_favorite_companies_v2 ( user_id INT NOT NULL, company_id INT NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL, updated_at TIMESTAMP WITH TIME ZONE NOT NULL, favorite_state BOOLEAN NOT NULL, PRIMARY KEY (user_id, company_id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (company_id) REFERENCES companies (id) ); |
В кожну таблицю буду робити 20 вставок по мільйону рядків, результати виведу на графіку.
Вставка мільйонів на голому SQL в MySQL
Щоб протестувати перебудову індексу, вставлятиму перехрещуванням.
Для початку створимо таблиці й заповнимо їх даними, на основі яких будуватимемо вставки.
CREATE TABLE users ( id INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE companies ( id INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB;
SET SESSION cte_max_recursion_depth = 1000000; INSERT INTO users (id, created_at) SELECT number, '2022-11-05 12:00:00' FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series; INSERT INTO companies (id, created_at) SELECT number, '2022-11-05 12:00:00' FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 10 * 1000 ) SELECT * FROM numbers ) AS generate_series;
CREATE TABLE user_favorite_companies_v1 ( id INT AUTO_INCREMENT, user_id INT NOT NULL, company_id INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, favorite_state BOOLEAN NOT NULL, PRIMARY KEY (id), UNIQUE (user_id, company_id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (company_id) REFERENCES companies (id) ) ENGINE = InnoDB; | CREATE TABLE user_favorite_companies_v2 ( user_id INT NOT NULL, company_id INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, favorite_state BOOLEAN NOT NULL, PRIMARY KEY (user_id, company_id), FOREIGN KEY (user_id) REFERENCES users (id), FOREIGN KEY (company_id) REFERENCES companies (id) ) ENGINE = InnoDB; |
[ [1, 11, 21, 31, /* ..., */ 91], [2, 12, 22, 32, /* ..., */ 92], [3, 13, 23, 33, /* ..., */ 93], [4, 14, 24, 34, /* ..., */ 94], /* ..., */ [9, 19, 29, 39, /* ..., */ 99], ];
SET SESSION profiling_history_size = 100; SET SESSION cte_max_recursion_depth = 1000000; SET SESSION profiling = 1; TRUNCATE user_favorite_companies_v1;
INSERT INTO user_favorite_companies_v1 (user_id, company_id, created_at, updated_at, favorite_state) SELECT number, 1 + (0 + number * 20) % 10000, '2022-11-05 12:00:00', '2022-11-05 12:00:00', TRUE FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series ON DUPLICATE KEY UPDATE updated_at = '2022-11-05 12:00:00', favorite_state = TRUE; SELECT COUNT(*) FROM user_favorite_companies_v1 WHERE (user_id, company_id) IN ( SELECT number, 1 + (0 + number * 20) % 10000 FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series );
-- (1 + number * 20) ...
-- (2 + number * 20) ...
-- (3 + number * 20) ...
-- ...
-- (18 + number * 20) ...
INSERT INTO user_favorite_companies_v1 (user_id, company_id, created_at, updated_at, favorite_state) SELECT number, 1 + (19 + number * 20) % 10000, '2022-11-05 12:00:00', '2022-11-05 12:00:00', TRUE FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series ON DUPLICATE KEY UPDATE updated_at = '2022-11-05 12:00:00', favorite_state = TRUE; SELECT COUNT(*) FROM user_favorite_companies_v1 WHERE (user_id, company_id) IN ( SELECT number, 1 + (19 + number * 20) % 10000 FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series );
TRUNCATE user_favorite_companies_v1; SHOW PROFILES;
Результати на графіку для INSERT-ів:
Результати на графіку для SELECT-ів:
Якщо хочете самостійно протестувати, то ось репозиторій й команди:
up: docker-compose up -d mysql-v1-test: cat ./console/mysql.v1.sql | docker exec -i -e MYSQL_PWD=mypass cpkr_mysql mysql --database=yaaws --user=myuser --verbose | tee ./console/mysql.v1.output mysql-v2-test: cat ./console/mysql.v2.sql | docker exec -i -e MYSQL_PWD=mypass cpkr_mysql mysql --database=yaaws --user=myuser --verbose | tee ./console/mysql.v2.output down: docker-compose down
Вставка мільйонів на голому SQL в Postgres
Для Postgres буде схожий тест, але для наглядності додам порівняння з MySQL (щоб побачити повну картину, потрібно зменшити зум):Postgres | MySQL |
---|---|
\timing | SET SESSION profiling_history_size = 100; SET SESSION cte_max_recursion_depth = 1000000; SET SESSION profiling = 1; |
TRUNCATE user_favorite_companies_v1; | TRUNCATE user_favorite_companies_v1; |
INSERT INTO user_favorite_companies_v1 ( user_id, company_id, created_at, updated_at, favorite_state ) SELECT generate_series, 1 + (0 + generate_series * 20) % 10000, '2022-11-05 12:00:00', '2022-11-05 12:00:00', TRUE FROM generate_series(1, 1000 * 1000) ON CONFLICT (user_id, company_id) DO UPDATE SET updated_at = '2022-11-05 12:00:00', favorite_state = TRUE; | INSERT INTO user_favorite_companies_v1 ( user_id, company_id, created_at, updated_at, favorite_state ) SELECT number, 1 + (0 + number * 20) % 10000, '2022-11-05 12:00:00', '2022-11-05 12:00:00', TRUE FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series ON DUPLICATE KEY UPDATE updated_at = '2022-11-05 12:00:00', favorite_state = TRUE; |
SELECT COUNT(*) FROM user_favorite_companies_v1 WHERE (user_id, company_id) IN ( SELECT generate_series, 1 + (0 + generate_series * 20) % 10000 FROM generate_series(1, 1000 * 1000) ); | SELECT COUNT(*) FROM user_favorite_companies_v1 WHERE (user_id, company_id) IN ( SELECT number, 1 + (0 + number * 20) % 10000 FROM ( WITH RECURSIVE numbers AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 1000 * 1000 ) SELECT * FROM numbers ) AS generate_series ); |
SHOW PROFILES; |
Результати на графіку для SELECT-ів:
Результати для складеного первинного ключа трохи кращі й руйнують мою упередженість після MySQL.
Вставка мільйонів на голому SQL в CockroachDB
CockroachDB сумісний з PostgreSQL тому одразу до результатів.Результати на графіку для INSERT-ів:
Результати на графіку для SELECT-ів:
Якщо ви з CockroachDB графіків змогли зробити висновки то напишіть.
Порівняння різних БД на одному графіку
Результати на графіку для INSERT-ів:Результати на графіку для SELECT-ів:
Postgres в цьому порівнянні — найпрогнозованіша БД.
Епілог
Почалось з того, що мій колега з Казакстану додав в проєкт міграцію в Postgres зі складеним первинним ключем, як результат — вирішив розібратись в темі краще.Дослідження починав з тестів на Go на паралельну вставку, але коли таблиця менше мільйона записів, то відчути відмінності складно, тому спробував для 10 мільйонів на чистому SQL, а потім вже для 20 мільйонів.
Вакансії
Мені подобається концепція, коли технічні фахівці шукають технічних фахівців. Отже, є вакансії в сфері кібербезпеки від компанії Cossack Labs:- Application Security Engineer ($2500–5000)
- Mobile Application Security Engineer ($2500–5000)
Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті
30 коментарів
Додати коментар Підписатись на коментаріВідписатись від коментарів