Дослідження швидкодії складеного первинного ключа в Postgres, MySQL та CockroachDB

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

Я довго працював з 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],
];
Тестування вставки для складеного унікального ключа для таблиці user_favorite_companies_v1:
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;
Тестування вставки для складеного первинного ключа для таблиці user_favorite_companies_v2 схоже на попереднє.
Результати на графіку для 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
Упередження, стосовно повільнішої вставки у таблицю зі складеним первинним ключем, для MySQL виправдалось.

Вставка мільйонів на голому SQL в Postgres

Для Postgres буде схожий тест, але для наглядності додам порівняння з MySQL (щоб побачити повну картину, потрібно зменшити зум):
PostgresMySQL
\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;
Результати на графіку для INSERT-ів:

Результати на графіку для SELECT-ів:

Результати для складеного первинного ключа трохи кращі й руйнують мою упередженість після MySQL.

Вставка мільйонів на голому SQL в CockroachDB

CockroachDB сумісний з PostgreSQL тому одразу до результатів.

Результати на графіку для INSERT-ів:

Результати на графіку для SELECT-ів:

Якщо ви з CockroachDB графіків змогли зробити висновки то напишіть.

Порівняння різних БД на одному графіку

Результати на графіку для INSERT-ів:

Результати на графіку для SELECT-ів:

Postgres в цьому порівнянні — найпрогнозованіша БД.

Епілог

Почалось з того, що мій колега з Казакстану додав в проєкт міграцію в Postgres зі складеним первинним ключем, як результат — вирішив розібратись в темі краще.

Дослідження починав з тестів на Go на паралельну вставку, але коли таблиця менше мільйона записів, то відчути відмінності складно, тому спробував для 10 мільйонів на чистому SQL, а потім вже для 20 мільйонів.

Вакансії

Мені подобається концепція, коли технічні фахівці шукають технічних фахівців. Отже, є вакансії в сфері кібербезпеки від компанії Cossack Labs:
В кінці опису вакансії є прямі контакти Анастасії.
👍ПодобаєтьсяСподобалось7
До обраногоВ обраному1
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Postgres то є кацапська СУБД і сама крінжова зі всіх RDBMS
Навіщо Характерник тестує кацапські продукти ?
То с клікхаус прибіжить то с постгрес.

Стосовно ClickHouse відповідав раніше.

Це вдвічі смішно, бо українці виступають бесплатними тестувальниками для московських продуктів. Чому у українських девелоперів не кортять руки створити щось українське, а тільки тестувати московське ?

Звісно створюю. Тут наприклад своя СУБД під капотом.
І самі технології на три голови вище кацапських.
Написав 12 рядочків коду, отримав сайт з 8тис\сек запитів на секунду
(це вже с генерацією сторінок)
dou.ua/...​rums/topic/41300/#2535483

А у вас тут безсмістовного бойлєрплейту на три ERP.
Ото нема чим людям зайнятися.
Кацапи написали постгрес мамонта, а українці це ще як канхфєтку жують.

Хм, а є якісь пруфи? Бо судячи з www.postgresql.org/community/contributors аж 2 розробника з московії, що не тяне на

кацапська СУБД

. Хоча не знайшов інфи де зареєстрована The PostgreSQL Global Development Group, але не думаю, що в рфії.

То було давно і неправда. Основні контрибьютори там РФ. Вона же взята як рекомендована база для РФ

Це ти напевно переплутав з Postgres Pro

Деякий вклад вони точно зробили (на скільки я памятаю json і повнотекстовий пошук), але це ще не дає права називати PostgreSQL москальською розробкою. А на рахунок крінжовості вимушений підтримати )

сама крінжова зі всіх RDBMS

а можна приклади?

Там всюди треш. Візьміть нормальну MS SQL або Oracle вони на дві голови краще спроєктовані. І річ навіть не в оптимизаторі запросів, то взагалі рокет сцайнц та патенти.
А навіть банальний синтаксис SQL. Таке враження що пять студентів першокурсників ліпили свою першу мову програмування в сильсько господарському пту. Чого тільки коштує постійно переписувати Stored Procedure в залежності що вона повертає. Поліморфний сінтаксис ? Нє, на расєі нє слішалі. Це як би було в паскалі придумали десять string контейнерів і кожен по різному декларується в залежності від строки що там є. І всі ці string1,string2 stringN погано комбінуються між собою. Йопвашу, навіщо я це згадав. Расчлєньонка в сінтаксісі. Буеее.

MS SQL перевіряв вже після написання статті, результати виявились кращими ніж у PostgreSQL.

А ви не пробували зробити id не автоінкрементом?

Є репозиторій де ви можете протестувати варіанти й написати свої висновки.

Думал показалось, ан нет.

Щоб позбутись упередження, я протестую на швидкодію вставки в наступні таблиці:

DDL user_favorite_companies_v1 содержит
PRIMARY KEY (id),
UNIQUE (user_id, company_id),

DDL user_favorite_companies_v2
PRIMARY KEY (user_id, company_id),

Я довго працював з MySQL

Не особо заметно по дизайну. Что PRIMARY в user_favorite_companies_v1 дается бесплатно ?

Тест вставки в такие таблицы будет не об этом, а скорей о том что будет если навести сверху композитного ключа (user_id, company_id) еще один (id)
По хорошему PRIMARY KEY (id) нужно убрать.

Второе. Если оцениваешь импакт, то оценивай так чтоб небыло других переменных.
Зачем FK здесь ?

FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (company_id) REFERENCES companies (id)

они что бесплатные в плане оверхеда ? Я бы еще понял если б у тебя составной FK был. А так это непонятная составляющая в замерах.

Убери ненужное и разница будет более заметная.

PS. По большому вся разница ключ составной vs сингл — в пейдж сплит, который будет происходить всегда но при длинных ключай это проходит чаще. Особенно если для btree нужно будет выстроить новый левел.
Отчасти это нивелируется page fill index (у каждого вендора свое название)
и дополнительной сортировке нон лидинг аттрибутов

Відмінності й так помітні, але якщо у вас є бажання щось довести то ось репозиторій.

Статья как и анализ пестрит допущениями о которых ни слова.
Эт ваш труд.
А мой комментарий о нем выше.

Clustered and Secondary Indexes:

Each InnoDB table has a special index called the clustered index that stores row data.
If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values.

Это называется таблица как куча. Не имеет к топику отношения.

Clustered and Secondary Indexes:

If you do not define a PRIMARY KEY for a table, InnoDB uses the first UNIQUE index with all key columns defined as NOT NULL as the clustered index.

Якщо приберу PRIMARY KEY з user_favorite_companies_v1 то user_favorite_companies_v1 й user_favorite_companies_v2 будуть однакові.

CREATE TABLE user_favorite_companies_v1
(
    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,
    UNIQUE (user_id, company_id) -- clustered index
) 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) -- clustered index
) ENGINE = InnoDB;

Ще з Oracle і MS SQL цікаво порівняти.

Я хотів охопити більше баз сумісних з Postgres (YugabyteDB та Neon), але там потрібно було повозитись з налаштуваннями в docker-compose, тому облишив.

Чисто цікаво перевірити чи це раптом не ритуальні дії, коли перед імпортом в базу великого об’єму данних з початку дропають усі індекси і ключі з тих таблиць і які ведеться імпорт, а потім їх відновлюють. Тобто чи це не пов’язане з міфами про старі DBMS движки та догмою і фольклором. Тобто давно вже не є актуальним. Як бачимо у випадку MySQL з innodb — ні не догма, все так і є. А там далі ще багато чього є для реальних дослідів і бенчмарків. New SWl — Claud Spanner, Tarantool etc.

Імпорти великих об’ємів даних роблять зазвичай рідко тому дешевше по часу залишити ритуальні дії (або згенерований через СУБД файл), ніж витрачати 2-3 години на дослідження.

Є ще що досліджувати для написання статей, але на то потрібно виділити пару повних робочих днів.

Спасибо за новые для меня базы.
Иногда кажется базы любят клепать также как новые JS фреймворки.

Вообще неясно зачем это все, если есть MS SQL Server

Дешевле?)
Работаю с MS SQL уже лет 19, и только недавно начал понимать, что он не так уж плох по сравнению.

Я починав свою кар’єру з Delphi та MSSQL тому досить швидко розібрався як запустити MSSQL в Docker:

Провів тести, MSSQL виявився найшвидшим по вставці (всі вставки до 11 секунд), але ще треба пошукати як зробити красиво SELECT в MSSQL:

SELECT COUNT(*)
FROM user_favorite_companies_v1
WHERE (user_id, company_id) IN (
    SELECT value, 1 + (0 + value * 20) % 10000
    FROM GENERATESERIES(1, 1000 * 1000)
);

При спробі написати правильно функцію GENERATE_SERIES й зберегти коментар на DOU то отримую http.status_code=403 від CloudFlare.

Підписатись на коментарі