Индексы для больших данных

Когда таблица растёт до сотен миллионов или миллиардов строк, обычные стратегии оптимизации перестают работать. Индекс, который был быстрым для 1 млн строк, может стать bottleneck для 100 млн.

#Проблема масштаба

Основная проблема: индекс не помещается в buffer pool.

-- Размер индекса таблицы
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'size'
ORDER BY size_mb DESC;

Если innodb_buffer_pool_size = 32 ГБ, а суммарный размер индексов = 80 ГБ — данные не помещаются в память. Это означает частые чтения с диска, что в 100-1000 раз медленнее RAM.

#Партиционирование

Партиционирование — разделение одной логической таблицы на физические части:

-- RANGE партиционирование по дате
CREATE TABLE orders (
    id BIGINT,
    customer_id INT,
    created_at DATE,
    total DECIMAL(10,2),
    PRIMARY KEY (id, created_at)  -- partition_key должен быть частью PK
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Partition pruning — оптимизатор читает только нужные партиции:

-- Читает только партицию p2024
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- EXPLAIN показывает: partitions: p2024
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;

Проблемы партиционирования:

Новые партиции нужно создавать заранее (через EVENT или скрипт)
Запросы БЕЗ условия по partition_key читают ВСЕ партиции
UNIQUE ограничения должны включать partition_key
Максимум 8192 партиции (практический лимит значительно ниже)

#Партиционирование vs шардинг

Характеристика	Партиционирование	Шардинг
Серверы	Один	Несколько
Транзакции	Атомарные	Распределённые
JOIN	Работают	На уровне приложения
Масштабирование	Вертикальное	Горизонтальное
Сложность	Низкая	Высокая

#Стратегия горячих/холодных данных

Разделение данных по 'температуре' доступа:

-- Горячие данные (последний месяц) — основная таблица
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id INT,
    created_at DATETIME,
    total DECIMAL(10,2),
    INDEX idx_customer (customer_id),
    INDEX idx_created (created_at)
);

-- Холодные данные (старше месяца) — архивная таблица
CREATE TABLE orders_archive (
    id BIGINT PRIMARY KEY,
    customer_id INT,
    created_at DATETIME,
    total DECIMAL(10,2)
    -- Меньше индексов — только для редких запросов
);

-- Представление для прозрачного доступа
CREATE VIEW orders_all AS
SELECT * FROM orders
UNION ALL
SELECT * FROM orders_archive;

Преимущества:

Основная таблица меньше — индексы помещаются в buffer pool
INSERT быстрее — меньше индексов обновлять
Холодные данные на более медленном (и дешёвом) storage

Архивирование по расписанию:

-- Перенос старых данных
INSERT INTO orders_archive
SELECT * FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

DELETE FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

#Префиксный индекс

Для длинных строковых столбцов — индексировать только первые N символов:

-- Полнотекстовый индекс на email(10) — только первые 10 символов
CREATE INDEX idx_email_prefix ON users(email(10));

Подбор длины префикса:

-- Проверяем селективность для разных длин
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS prefix_20,
    COUNT(DISTINCT email) / COUNT(*) AS full_email
FROM users;

Выберите длину, где селективность приближается к полному значению (например, >0.9). Для UTF-8: префикс в символах, не байтах.

Ограничения: префиксный индекс НЕ может быть covering для полного значения столбца, НЕ поддерживает ORDER BY по полному столбцу.

#Практические рекомендации

Партиционирование по дате — самый частый сценарий. Автоматизируйте создание новых партиций через EVENT:

DELIMITER //
CREATE EVENT create_partition_monthly
ON SCHEDULE EVERY 1 MONTH
DO BEGIN
    -- Динамическое создание новой партиции
    ALTER TABLE orders ADD PARTITION (PARTITION p2026 VALUES LESS THAN (2027));
END //
DELIMITER ;

Мониторьте buffer pool hit ratio:

-- Hit ratio должен быть >99%
SELECT 
    ROUND((1 - pool.physical_reads / (pool.physical_reads + pool.logical_reads)) * 100, 2) 
    AS buffer_pool_hit_ratio
FROM (
    SELECT 
        SUM(CASE WHEN variable_name = 'Innodb_buffer_pool_reads' THEN variable_value END) AS physical_reads,
        SUM(CASE WHEN variable_name = 'Innodb_buffer_pool_read_requests' THEN variable_value END) AS logical_reads
    FROM performance_schema.global_status
) pool;

Низкий hit ratio (<99%) — данные не помещаются в память. Рассмотрите партиционирование, архивирование или увеличение buffer pool.

Характеристика

Партиционирование

Шардинг

Серверы

Один

Несколько

Транзакции

Атомарные

Распределённые

JOIN

Работают

На уровне приложения

Масштабирование

Вертикальное

Горизонтальное

Сложность

Низкая

Высокая