Та же строка в InnoDB, SQLite и ClickHouse: страница 16 КБ, ячейка b-tree и колонки в файлах

Таблица из миллиона строк занимает в ClickHouse 49,1 МБ, в SQLite 76,8 МБ, в InnoDB 87,7 МБ, а SELECT sum(amount) идёт 3 мс, 36 мс и 105–260 мс. Дело в единице хранения: InnoDB и SQLite кладут строку целиком в лист b-дерева, ClickHouse режет её на столбцы по файлам. Страницу PostgreSQL разбирает статья про раскладку страницы 8 КБ; здесь та же строка лежит в трёх других местах.

Одна таблица в трёх движках

Таблица везде одна: orders(id bigint PK, customer_id int, amount int, created_at timestamp, note text), миллион строк. Значения детерминированы: customer_id = id % 100000, amount = id % 1000, created_at — 1 января 2026 плюс id секунд, note — 40 символов (md5(id) и первые 8 символов md5(id+1)).

docker run -d --name dba-rows-mysql -e MYSQL_ROOT_PASSWORD=pg mysql:8.4
docker run -d --name dba-rows-clickhouse -e CLICKHOUSE_SKIP_USER_SETUP=1 clickhouse/clickhouse-server:25.3

MySQL (docker exec -i dba-rows-mysql mysql -uroot -ppg -B):

CREATE DATABASE t; USE t;
CREATE TABLE orders(id bigint PRIMARY KEY, customer_id int, amount int,
                    created_at timestamp, note text) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth=1000000;
INSERT INTO orders
SELECT n, n%100000, n%1000, TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n SECOND,
       CONCAT(MD5(n), SUBSTR(MD5(n+1),1,8))
FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<1000000)
      SELECT n FROM s) q;
ANALYZE TABLE orders;
SELECT table_rows, data_length, index_length, data_free
FROM information_schema.TABLES WHERE table_schema='t' AND table_name='orders';

TABLE_ROWS	DATA_LENGTH	INDEX_LENGTH	DATA_FREE
994896	87687168	0	5242880

INDEX_LENGTH равен нулю не случайно: первичный ключ InnoDB не отдельная структура, а сама таблица. DATA_LENGTH = 5352 страницы по 16 384 Б (@@innodb_page_size, листьев 5292); файл orders.ibd — 96 468 992 Б. Версия: SELECT VERSION() вернул 8.4.13.

ClickHouse (docker exec -i dba-rows-clickhouse clickhouse-client -mn); ORDER BY id у MergeTree — и есть первичный ключ:

CREATE DATABASE t;
CREATE TABLE t.orders(id Int64, customer_id Int32, amount Int32, created_at DateTime, note String)
ENGINE=MergeTree ORDER BY id;
INSERT INTO t.orders SELECT number+1, (number+1)%100000, (number+1)%1000,
  toDateTime('2026-01-01 00:00:00') + toUInt32(number+1),
  concat(lower(hex(MD5(toString(number+1)))), substring(lower(hex(MD5(toString(number+2)))),1,8))
FROM numbers(1000000);
OPTIMIZE TABLE t.orders FINAL;
SELECT name, rows, marks, bytes_on_disk, data_uncompressed_bytes, part_type
FROM system.parts WHERE database='t' AND table='orders' AND active FORMAT TSVWithNames;

name	rows	marks	bytes_on_disk	data_uncompressed_bytes	part_type
all_1_1_1	1000000	124	49120302	61000000	Wide

SELECT version() вернул 25.3.14.14. Сжатие зависит от данных: amount ужалась с 4 000 000 до 264 077 Б (в 15 раз), а note из шестнадцатеричных символов — с 41 000 000 до 36 813 946 (на 10 %).

SQLite (python3 -I orders.py; INTEGER PRIMARY KEY — это rowid, отдельного индекса по id нет):

import sqlite3, hashlib, datetime
c = sqlite3.connect('orders.db')
c.execute("CREATE TABLE orders(id integer PRIMARY KEY, customer_id int, amount int, created_at timestamp, note text)")
note = lambda i: hashlib.md5(str(i).encode()).hexdigest() + hashlib.md5(str(i+1).encode()).hexdigest()[:8]
base = datetime.datetime(2026, 1, 1)
c.executemany("INSERT INTO orders VALUES(?,?,?,?,?)",
    ((i, i%100000, i%1000, (base+datetime.timedelta(seconds=i)).strftime('%Y-%m-%d %H:%M:%S'), note(i))
     for i in range(1, 1000001)))
c.commit()
for p in ("page_size", "page_count"): print(p, c.execute(f"PRAGMA {p}").fetchone()[0])

page_size 4096
page_count 18749

sqlite3.sqlite_version вернул 3.50.2.

Сводка опыта

Время снято в каждом движке своим способом, поэтому сравнивай порядки величин, а не проценты. SQLite: среднее по 1000 запросов и по трём суммам, через Python. InnoDB: хранимая процедура на 100 000 запросах по разным ключам; число страниц — разность счётчика Innodb_buffer_pool_read_requests (15 652 219 − 15 652 216); сумма — EXPLAIN ANALYZE, хост был занят другими задачами, отсюда разброс. Первый прогон процедуры после вытеснения orders из буферного пула дал 27,4 мкс. ClickHouse: clickhouse-benchmark в один поток (589 и 301 запрос в секунду; прошлый запуск дал 540 и 316) включает разговор по localhost; read_rows и read_bytes — из system.query_log.

Главное в read_rows: чтобы вернуть одну строку, ClickHouse прочитал 8192 строки (565 248 Б значений; SELECT id — 65 536 Б), а sum(amount) не тронул остальные четыре столбца. Точечное чтение там в 270–400 раз дороже, чем в SQLite и InnoDB, а сумма в 11–80 раз дешевле.

InnoDB: запись в 80 байт на странице 16 КБ

Таблица — b-дерево с ключом id, строки лежат в листьях целиком. Разбор orders.ibd (копия через docker cp, смещения из page0types.h) прошёл корень (страница 4, уровень 2), страницу 42 (уровень 1) и лист 4147: три страницы, как насчитал счётчик. На листе 189 записей по 80 Б с ключами 777641–777829, heap_top 15 240 из 16 384, соседи 4146 и 4148. Запись собирается так: 5 байт заголовка, байт карты NULL и байт длины note, затем ключ 8, DB_TRX_ID 6, DB_ROLL_PTR 7, три int по 4 и note 40: 7 + 8 + 6 + 7 + 12 + 40 = 80. Пять байт закреплены в rem/rec.h:

/* Number of extra bytes in an old-style record,
in addition to the data and the offsets */
constexpr uint32_t REC_N_OLD_EXTRA_BYTES = 6;
/* Number of extra bytes in a new-style record,
in addition to the data and the offsets */
constexpr int32_t REC_N_NEW_EXTRA_BYTES = 5;

Из 80 байт 20 служебные (против 23 байт заголовка строки в PostgreSQL). Смещения заголовка страницы (PAGE_N_RECS, PAGE_LEVEL) лежат в page0types.h, а не в page0page.h и rem0rec.h, как в плане: там функции доступа.

SQLite: ячейка в листе страницы 4 КБ

Одно b-дерево с ключом rowid; формат ячейки описан в btreeInt.h:

** The content of a cell looks like this:
**
**    SIZE    DESCRIPTION
**      4     Page number of the left child. Omitted if leaf flag is set.
**     var    Number of bytes of data. Omitted if the zerodata flag is set.
**     var    Number of bytes of key. Or the key itself if intkey flag is set.
**      *     Payload
**      4     First page of the overflow chain.  Omitted if no overflow

Разбор файла нашёл строку 777777 на листе 14580: ячейка 74 Б (длина записи 1, rowid 3, запись 70), 53 ячейки на странице, 60 свободных байт совпали с unused из dbstat. Поле id в записи — NULL, значение уже лежит в rowid. Дерево из 48 внутренних страниц и 18 700 листьев имеет глубину 3, как в InnoDB. Строка с note из 10 000 символов заняла две страницы переполнения по 4092 Б, в листе осталось 1 816 Б; это сходится с правилом «Database File Format»: M + ((P − M) mod (U − 4)) = 489 + 1327. В PostgreSQL для такого значения есть отдельная таблица, см. TOAST.

ClickHouse: столбцы по файлам и гранула в 8192 строки

Part all_1_1_1 — каталог: у каждого столбца свой .bin (размеры совпадают с data_compressed_bytes) и файл засечек .cmrk2, плюс primary.cidx в 184 Б. Вместо .mrk2 из справочников здесь .cmrk2: засечки сжаты (compress_marks = 1), суффикс собирает MarkType::getFileExtension в MergeTreeIndexGranularityInfo.cpp. Засечки читает loadIndexGranularityImpl:

        while (!marks_reader->eof())
        {
            MarkInCompressedFile mark;
            size_t granularity;

            readBinaryLittleEndian(mark.offset_in_compressed_file, *marks_reader);
            readBinaryLittleEndian(mark.offset_in_decompressed_block, *marks_reader);
            ++marks_count;

Засечка — смещение сжатого блока в .bin и смещение гранулы внутри распакованного блока. Строка 777777 лежит в грануле (777777 − 1) / 8192 = 94 (ключи 770 049–778 240); её смещения в id.bin — 3 084 021, в note.bin — 28 349 116. Чтобы вернуть одну строку, сервер идёт в пять файлов и распаковывает гранулу каждого столбца.

UUID-ключ в InnoDB дробит страницы

Пока id растёт, строка дописывается в хвост и листья заполняются подряд. Случайный порядок ключа заставляет вставлять в середину заполненной страницы и делить её. Опыт: orders_rand — те же строки и bigint, вставка ORDER BY MD5(id); orders_uuid — ключ binary(16) = UNHEX(MD5(id)), вставка в порядке id.

Порядок вставки даёт +62 из +75,5 %, ещё 13 пунктов — 8 лишних байт ключа в каждой записи.

DBA / 01koddo

Одна строка в трёх местах: запись, ячейка, гранула

Что из этого следует

Первичный ключ InnoDB — это раскладка таблицы. Порядок вставки должен совпадать с порядком ключа. Случайный порядок в опыте дал +62 % к размеру на том же bigint, а 16-байтный ключ добавил ещё 13 пунктов. Если UUID нужен снаружи, храни его отдельным столбцом с уникальным индексом, а кластерным ключом оставь растущее число или идентификатор, упорядоченный по времени, и проверь тем же опытом, что листья не разрежаются.

В SQLite строка и её ключ — одна ячейка. Пока строка короткая, это 53 ячейки на 4 КБ и дерево глубины 3. Значение длиннее примерно 4 КБ уходит в цепочку страниц переполнения: у нашего note в 10 000 символов их две. Длинные тексты и файлы вынеси в отдельную таблицу или в файловую систему, а не держи рядом с часто читаемыми полями.

ClickHouse не для выборки одной строки. 1,7 мс на строку против 4,2 мкс в SQLite — не ошибка настройки, а цена формата: гранула каждого столбца. Эта же гранула делает дешёвой сумму по одному столбцу: 3,3 мс на миллион строк, потому что читается один amount.bin в 264 077 байт. Запросы «дай карточку по id» отправляй в строковое хранилище, а аналитику по столбцам — в колоночное.

Размер на диске без своих данных не предсказать. У нас 87,7, 76,8 и 49,1 байта на строку, но сжатие amount дало 15 раз, а note из шестнадцатеричного текста — 10 %. Меряй information_schema.TABLES, PRAGMA page_count и system.parts на выборке своей таблицы, а не на таблице из справки.

Чек-лист

1. Узнай единицу хранения движка: страница 16 КБ в InnoDB (@@innodb_page_size), 4 КБ в SQLite (PRAGMA page_size), гранула 8192 строки в ClickHouse (index_granularity).
2. Для InnoDB выбирай растущий или упорядоченный кластерный ключ и сравни n_leaf_pages после загрузки данных в боевом порядке.
3. В SQLite смотри dbstat, если он есть в сборке: страницы overflow значат, что часть значений ушла за пределы листа.
4. В ClickHouse смотри read_rows и read_bytes в system.query_log: 8192 строки на точечный запрос — свойство формата, а не поломка.
5. Сравнивай размеры и время только на одинаковых данных и одним способом измерения; цифры статьи относятся к 1 млн строк и шестнадцатеричной строке note.

Контейнеры удаляются командой docker rm -f dba-rows-mysql dba-rows-clickhouse, файлы опыта — rm -f orders.db ov.db orders.ibd.