Размер SQL индекса: формула и онлайн-калькулятор

Большой индекс не ускоряет запросы, а замедляет вставку и потребляет память в Buffer Pool. Зная размер заранее, вы можете:

• Оценить, войдёт ли индекс целиком в innodb_buffer_pool_size (MySQL) или shared_buffers (Pg).
• Спланировать место на диске (особенно при создании индекса CONCURRENTLY — нужна временная копия).
• Выбрать состав индекса (покрывающий или нет) и порядок колонок.

С помощью Калькулятора размера SQL индекса можно не вспоминать формулы, а сразу получить оценку.

Большинство СУБД (MySQL InnoDB, PostgreSQL, Oracle) используют B-деревья. Размер индекса складывается из:

Где количество страниц зависит от числа строк и заполненности (fill factor):

Для InnoDB размер страницы по умолчанию — 16 384 байта (16 КБ). Размер ключа — сумма байт всех колонок индекса + служебные поля (1-2 байта на колонку для NULL). Размер указателя на строку (или страницу) — 6 байт.

Для PostgreSQL размер страницы — 8 192 байта (8 КБ), размер указателя — тоже 6 байт. Fill factor по умолчанию 90% (заполняется 90% страницы, чтобы оставить место для вставок).

Предположим, таблица users с 1 000 000 строк. Cоздаём индекс по полю email — VARCHAR(255) в UTF-8. Реальный размер ключа зависит от данных; примем среднюю длину 20 байт + 2 байта длины (varchar). Итого ключ = 22 байта.

1. Вычисляем строк на страницу (InnoDB): floor(16384 / (22 + 6)) = floor(16384 / 28) = 585.
2. Количество листовых страниц: ceil(1 000 000 / 585) = 1710.
3. Высота дерева: каждая внутренняя страница хранит до floor(16384 / (22+6)) = 585 указателей. Для 1710 листьев нужно ceil(1710 / 585) = 3 страницы второго уровня, итого 1 корень. Всего страниц: 1710 + 3 + 1 = 1714.
4. Общий размер: 1714 × 16384 = 28 114 176 байт ≈ 26,8 МБ (без учёта фрагментации).

Реальный размер может быть на 10-20% больше из-за внутренней фрагментации и служебных данных страниц (page header ~100 байт на страницу). Для точной оценки используйте Калькулятор размера SQL индекса.

Добавление колонок увеличивает ключ, что уменьшает количество строк на страницу. Пример: индекс на (city, status) — city VARCHAR(50) средняя 10 байт + 2 байта длины, status INT (4 байта). Ключ = 10+2+4 = 16 байт + 1 байт nullable = 17 байт. Строк на страницу: floor(16384 / (17+6)) = 712. Для 1 млн строк: ceil(1 000 000 / 712) = 1405 страниц. Размер = 1405 × 16384 ≈ 21,9 МБ.

Количество колонок напрямую влияет на размер. Практические рекомендации:

• Индекс только на INT (4 байта) — строк на страницу ~1638, на 1 млн — ~584 страницы, ~9,1 МБ.
• Индекс на VARCHAR(255) с полной длиной (в UTF-8 3 байта на символ = 765 байт) — строк на страницу ~19, на 1 млн — ~52632 страниц, ~822 МБ.

Вывод: не включайте в индекс длинные текстовые поля без крайней необходимости. Для покрытия запроса лучше использовать покрывающий индекс (include columns) в PostgreSQL или secondary index с INCLUDE в MySQL (с 8.0).

Разные СУБД и конфигурации меняют формулу:

В PostgreSQL при fill_factor=90% мы должны делить число строк на страницу на 0.9: строк на страницу = 585 * 0.9 = 526, а количество страниц увеличивается на 11%.

Чтобы сверить расчёт с реальностью, используйте системные функции:

• MySQL:

  SELECT SUM(stat_value * @@innodb_page_size) FROM mysql.innodb_index_stats WHERE database_name = 'db' AND table_name = 'users' AND index_name = 'idx_email' AND stat_name = 'size';

  
  Или проще:

  SELECT DATABASE_NAME, TABLE_NAME, INDEX_NAME, STAT_VALUE * @@innodb_page_size AS index_size_bytes FROM mysql.innodb_index_stats WHERE stat_name = 'size';

• PostgreSQL:

  SELECT pg_size_pretty(pg_relation_size('idx_email'));

  
  Либо:

  SELECT relpages * 8192 AS index_size_bytes FROM pg_class WHERE relname = 'idx_email';

Для быстрой оценки используйте Калькулятор размера SQL индекса.

Фрагментация — после массовых вставок или обновлений страницы заполняются не полностью, увеличивая размер индекса на 20-30%. В InnoDB фрагментацию можно оценить через innodb_metrics.

Включённые колонки (INCLUDE в Pg, INCLUDE в MySQL 8.0+) добавляют в листовые страницы значения, не участвующие в поиске, но увеличивающие размер ключа. Например, если к индексу на id добавить email VARCHAR(255), размер листа вырастет.

NULL-значения — в InnoDB для индексов не хранится указатель на NULL, но в некоторых версиях MySQL не-NULL ключи занимают дополнительный бит (1 байт на колонку для nullable). В PostgreSQL NULL-ы хранятся в bitmap, что добавляет накладные расходы.

Частичные индексы (WHERE условие) — размер пропорционален числу строк, удовлетворяющих условию, а не всей таблицы.

• Монотонные ключи (AUTO_INCREMENT, последовательные даты) — вставка происходит в конец, без разделения страниц. Размер меньше из-за отсутствия Page Split. В B-дереве отсутствуют пустые места.
• Покрывающий индекс — если запрос выбирает только колонки, входящие в индекс, то читаются только листовые страницы. Размер индекса не меняется, но производительность выше.
• Сжатие — InnoDB поддерживает компрессию страниц (ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE), которая уменьшает размер в 2-3 раза, но требует CPU на сжатие/распаковку.

Чтобы не считать вручную, используйте специализированные инструменты:

• Калькулятор размера SQL индекса — учитывает СУБД, размер страницы, fill factor, типы данных.
• Для оценки размера таблицы — Калькулятор размера шин (механический).
• Для изображений в документации — Изменить размер фото онлайн.
• Для теплиц — Калькулятор размеров теплицы.