Оптимизация для таблиц в миллиарды строк, партиционирование, горячие и холодные данные
Когда таблица растёт до сотен миллионов или миллиардов строк, обычные стратегии оптимизации перестают работать. Индекс, который был быстрым для 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;Проблемы партиционирования:
| Характеристика | Партиционирование | Шардинг |
|---|---|---|
| Серверы | Один | Несколько |
| Транзакции | Атомарные | Распределённые |
| 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;Преимущества:
Архивирование по расписанию:
-- Перенос старых данных
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.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.