Прим. переводчика: партицирование (или секционирование) — это метод разделения одной большой таблицы базы данных на более мелкие, управляемые логические части (партиции) для повышения производительности запросов, упрощения администрирования и управления большими объемами данных, например, по диапазонам дат, списков или хэшам.
Это позволяет системе обращаться только к нужным разделам, а не ко всей таблице, ускоряет отчеты, архивацию и другие операции, а также можно перемещать неактивные данные на медленные носители.
Содержание
1. Введение
2. Что такое партицирование базы данных?
3. Вертикальное партицирование бд: разделение таблиц по столбцам
4. Пример реализации вертикального партицирования на Node.js и PostgreSQL
5. Горизонтальное партицирование: разделение по строкам
6. Партицирование на основе диапазонов (Range-Based Partitioning)
7. Партицирование на основе хеширования (Hash-Based Partitioning)
8. Партицирование на основе списков (List-Based Partitioning)
9. Пример реализации горизонтального партицирования на Node.js и PostgreSQL
10. Шардинг: вывод горизонтального партицирования на новый уровень.
11. Решения для шардинга на уровне базы данных
12. Запросы между разделами: проблема производительности
13. Агрегация по разделам
14. Работа с кросс-шардовыми объединениями
15. Передовые методы и рекомендации по внедрению
16. Распространённые ошибки и как их избежать
17. Когда НЕ следует партицировать?
18. Сочетание вертикального и горизонтального партицирования данных
19. Подведение итогов
20. Искали инструмент для проектирования баз данных?
Введение
Представьте себе: таблица пользователей вашего стартапа достигла отметки в 50 миллионов строк, и запросы, которые раньше выполнялись молниеносно, теперь занимают несколько секунд. Процессы резервного копирования длятся часами, а дисковый ввод-вывод вашего сервера базы данных постоянно перегружен. Знакомо?
В этот момент большинство разработчиков понимают, что единая монолитная таблица базы данных не будет масштабироваться вечно. Решение? Разделение базы данных на разделы. Но вот в чем дело: универсального подхода не существует. Выбор между горизонтальным и вертикальным разделением (или их сочетанием) может определить, будет ли система масштабироваться плавно, а не рухнет под собственной тяжестью.
В этом руководстве мы подробно рассмотрим обе стратегии разделения данных, разберемся, когда следует использовать каждый подход, и рассмотрим практические примеры реализации в популярных базах данных. Мы также углубимся в стратегии шардинга для распределенных систем и разберем проблемы запросов к различным разделам. В итоге у вас будет четкий план эффективного разделения вашей базы данных.
Что такое партицирование базы данных?
Разделение баз данных на разделы — это практика разделения больших таблиц на более мелкие, более управляемые части при сохранении логического представления единой таблицы. Представьте это как организацию огромной библиотеки: вместо одного большого зала с миллионами книг вы создаете отдельные секции по жанру, автору или дате публикации. Каждая секция проще в управлении, но читатели по-прежнему могут получить доступ к любой необходимой им книге.
К основным преимуществам разделения пространства на разделы относятся:
Улучшенная производительность запросов: когда запросам нужно сканировать только соответствующие разделы, а не всю таблицу целиком, они выполняются намного быстрее. Зачем просматривать данные за пять лет, если вы ищете заказы за последний месяц?
Более эффективное обслуживание: такие задачи, как резервное копирование, перестроение индексов и архивирование данных, становятся более управляемыми, когда вы можете работать с отдельными разделами. Вы можете ежедневно создавать резервные копии текущих данных, а ежемесячно архивировать старые разделы.
Повышенная доступность: если в одном разделе возникают проблемы, другие разделы могут продолжать функционировать. Это особенно ценно в распределенных системах, где аппаратные сбои неизбежны.
Эффективное управление жизненным циклом данных: вы можете легко архивировать или удалять старые данные, удаляя целые разделы, что значительно быстрее, чем удаление миллионов строк по отдельности.
Однако секционирование не бесплатно. Оно усложняет проектирование схемы, может усложнить запросы, охватывающие несколько разделов, и требует тщательного планирования ключей секционирования. Давайте рассмотрим два основных подхода.
Вертикальное партицирование бд: разделение таблиц по столбцам
Вертикальное секционирование разделяет таблицу по столбцам, а не по строкам. Вы берете широкую таблицу с множеством столбцов и разбиваете ее на несколько более узких таблиц, обычно группируя вместе столбцы, к которым часто обращаются.
Когда использовать вертикальное разделение
Представьте, что вы создаёте платформу для социальных сетей. Ваша таблица пользователей может содержать десятки столбцов:
Большинству запросов требуется лишь подмножество этих столбцов. Для проверки аутентификации достаточно имени пользователя, адреса электронной почты и хеша пароля. Для страниц профиля необходима информация о профиле. Аналитические запросы ориентированы на статистику. Загрузка всех этих столбцов для каждого запроса приводит к нерациональному расходованию памяти и пропускной способности ввода-вывода.
Вот как вертикальное партицирование улучшает этот процесс:
Такое вертикальное разделение обеспечивает ряд преимуществ. Запросы аутентификации теперь затрагивают только небольшую таблицу users_auth, которая вмещает больше строк в памяти и кэше. Обновления профилей не блокируют данные аутентификации. Статистику можно обновлять независимо, не затрагивая данные профиля. Для каждой таблицы можно использовать различные стратегии индексирования, оптимизированные под ее шаблоны доступа.
Пример реализации: Node.js с PostgreSQL
Давайте посмотрим, как работать с таблицами, разделенными по вертикали, в приложении:
class UserService { constructor(pool) { this.pool = pool; } async authenticateUser(email, password) { const result = await this.pool.query( 'SELECT id, username, email, password_hash FROM users_auth WHERE email = $1', [email] ); if (result.rows.length === 0) return null; const user = result.rows[0]; // Здесь реализована логика проверки пароля await this.updateLastLogin(user.id); return { id: user.id, username: user.username, email: user.email }; } async getUserProfile(userId) { const result = await this.pool.query(` SELECT ua.id, ua.username, ua.email, up.full_name, up.bio, up.avatar_url, upr.profile_visibility, upr.show_email, us.follower_count, us.post_count FROM users_auth ua LEFT JOIN users_profile up ON ua.id = up.user_id LEFT JOIN users_privacy upr ON ua.id = upr.user_id LEFT JOIN users_stats us ON ua.id = us.user_id WHERE ua.id = $1 `, [userId]); return result.rows[0]; } async updateProfile(userId, profileData) { const { full_name, bio, location, website } = profileData; await this.pool.query(` INSERT INTO users_profile (user_id, full_name, bio, location, website) VALUES ($1, $2, $3, $4, $5) ON CONFLICT (user_id) DO UPDATE SET full_name = EXCLUDED.full_name, bio = EXCLUDED.bio, location = EXCLUDED.location, website = EXCLUDED.website `, [userId, full_name, bio, location, website]); } async incrementFollowerCount(userId) { await this.pool.query(` INSERT INTO users_stats (user_id, follower_count, last_updated) VALUES ($1, 1, CURRENT_TIMESTAMP) ON CONFLICT (user_id) DO UPDATE SET follower_count = users_stats.follower_count + 1, last_updated = CURRENT_TIMESTAMP `, [userId]); } async updateLastLogin(userId) { await this.pool.query( 'UPDATE users_auth SET last_login = CURRENT_TIMESTAMP WHERE id = $1', [userId] ); } }
Обратите внимание, как каждая операция затрагивает только соответствующий раздел.
Метод authenticateUser запрашивает только небольшую таблицу users_auth, вместо загрузки ненужных данных профиля или настроек, что значительно повышает производительность этой часто выполняемой операции.
Метод getUserProfile объединяет несколько разделов, но только для одного пользователя, что остается эффективным, поскольку каждый раздел оптимизирован для своего шаблона доступа.
Метод updateProfile обновляет данные профиля без блокировки таблицы auth, а это значит, что редактирование профиля не замедляет операции входа в систему.
Метод incrementFollowerCount демонстрирует, как часто выполняемые обновления статистики могут быть изолированы в собственном разделе, используя различные стратегии блокировки или даже различные конфигурации хранилища при необходимости.
Это разделение является ключевым преимуществом вертикального партиционирования, позволяя независимо получать доступ, обновлять и оптимизировать различные части ваших данных.
Вертикальное партицирование в различных базах данных
PostgreSQL обрабатывает вертикальное секционирование с помощью стандартных связей внешних ключей, как показано выше. Вы также можете использовать наследование таблиц, но явное разделение таблиц с внешними ключами дает вам больше контроля.
MySQL следует той же схеме, что и PostgreSQL. Одна из оптимизаций заключается в использовании разных механизмов хранения для разных секций. Например, вы можете использовать InnoDB для данных транзакционной аутентификации и MyISAM для данных профиля, требующих частого чтения (хотя сейчас обычно предпочтительнее использовать InnoDB).
MongoDB естественным образом поддерживает эту схему с помощью встроенных документов, а не ссылок:
// Основной пользовательский документ с часто используемыми данными { "_id": ObjectId("..."), "username": "vasya_pupkin", "email": "vasyapupkin@email.ru", "passwordHash": "...", "profile": { // Встроено для частого совместного использования "fullName": "Вася Пупкин", "avatarUrl": "/avatars/vasya_pupkin.jpg" }, // Ссылка на отдельные коллекции для редко встречающихся данных. "privacySettingsId": ObjectId("..."), "extendedProfileId": ObjectId("...") }
Горизонтальное партицирование: разделение по строкам
Горизонтальное партицирование делит таблицу по строкам, распределяя их по нескольким разделам на основе ключа раздела. Каждый раздел содержит подмножество от общего числа строк, но включает все столбцы.
Когда использовать горизонтальное партицирование
Горизонтальное секционирование особенно эффективно при работе с большими таблицами, где запросы, естественно, фильтруются по определенным критериям. Наиболее распространенные сценарии включают:
Данные привязанные к дате/времени (Time-series data): журналы, события, показания датчиков и аналитические данные, где запросы обычно фокусируются на последних периодах времени. Можно использовать секционирование по дням, неделям или месяцам.
Многопользовательские прило��ения: SaaS-платформы, где данные каждого клиента изолированы. Секционирование по идентификатору клиента обеспечивает разделение данных каждого клиента и упрощает перемещение больших групп клиентов на выделенные ресурсы.
Географическое распределение: приложения, обслуживающие пользователей в разных регионах, где запросы обычно специфичны для региона. Секционирование по региону или стране повышает производительность запросов и может помочь в соблюдении требований суверенитета данных.
Большие объемы транзакционных данных: заказы, транзакции или сообщения, где к старым данным редко обращаются. Недавние разделы обрабатывают большинство запросов, в то время как старые разделы можно архивировать или сжимать.
Партицирование на основе диапазонов (Range-Based Partitioning)
Разделение данных по диапазону значений в ключе раздела — это наиболее интуитивно понятный подход для временных рядов.
Вот пример системы обработки заказов в электронной коммерции:
-- Пример разбиения данных на диапазоны в PostgreSQL CREATE TABLE orders ( order_id BIGINT, user_id BIGINT NOT NULL, order_date TIMESTAMP NOT NULL, status VARCHAR(50), total_amount DECIMAL(10,2), shipping_address TEXT, items JSONB, PRIMARY KEY (order_id, order_date) ) PARTITION BY RANGE (order_date); -- Создайте разделы для каждого квартала CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); CREATE TABLE orders_2024_q2 PARTITION OF orders FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'); CREATE TABLE orders_2024_q3 PARTITION OF orders FOR VALUES FROM ('2024-07-01') TO ('2024-10-01'); CREATE TABLE orders_2024_q4 PARTITION OF orders FOR VALUES FROM ('2024-10-01') TO ('2025-01-01'); CREATE TABLE orders_2025_q1 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-04-01'); -- Создать индексы на каждом разделе CREATE INDEX idx_orders_2024_q1_user ON orders_2024_q1(user_id); CREATE INDEX idx_orders_2024_q2_user ON orders_2024_q2(user_id); -- ... повторить для остальных партиций
При запросе информации о последних заказах PostgreSQL сканирует только соответствующий раздел:
— Этот запрос сканирует только раздел за 4 квартал 2024 года. SELECT order_id, total_amount, status FROM orders WHERE order_date >= '2024-10-01' AND order_date < '2024-11-01' AND user_id = 12345;
Планировщик запросов базы данных автоматически определяет, какие разделы следует сканировать, на основе условия WHERE. Это называется «partition pruning» (сокращение за счёт партиционирования) и имеет решающее значение для производительности.
Без этого база данных сканировала бы все разделы, что занимало бы больше времени.
Партицирование на основе хеширования (Hash-Based Partitioning)
Хэш-разделение равномерно распределяет строки по разделам, используя хэш-функцию по ключу раздела. Это хорошо работает, когда у вас нет естественных диапазонов, но вы хотите равномерно распределить нагрузку.
-- Хэширование PostgreSQL для пользовательского контента CREATE TABLE user_posts ( post_id BIGINT, user_id BIGINT NOT NULL, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, likes_count INT DEFAULT 0, PRIMARY KEY (post_id, user_id) ) PARTITION BY HASH (user_id); -- Создайте 8 хэш-разделов для равномерного распределения CREATE TABLE user_posts_p0 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 0); CREATE TABLE user_posts_p1 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 1); CREATE TABLE user_posts_p2 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 2); CREATE TABLE user_posts_p3 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 3); CREATE TABLE user_posts_p4 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 4); CREATE TABLE user_posts_p5 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 5); CREATE TABLE user_posts_p6 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 6); CREATE TABLE user_posts_p7 PARTITION OF user_posts FOR VALUES WITH (MODULUS 8, REMAINDER 7);
Хэш-функция гарантирует, что сообщения каждого пользователя всегда попадают в один и тот же раздел, но пользователи равномерно распределяются по всем разделам. Это предотвращает перегрузку одного раздела, в то время как другие остаются свободными.
Важное замечание: хэш-разделение затрудняет запросы по диапазону. Если вам нужно запросить «все сообщения за последнюю неделю», база данных должна просканировать все разделы, поскольку сообщения за любой период времени распределены по всем разделам.
Хэш-разделение работает лучше всего, когда запросы фильтруются по ключу раздела (в данном случае user_id).
Партицирование на основе списков (List-Based Partitioning)
Разделение списка явно присваивает разделам определенные значения. Это идеально подходит для категориальных данных, таких как регионы, страны или статусы.
-- Пример разделения списка MySQL для развертывания в нескольких регионах CREATE TABLE customer_data ( customer_id BIGINT NOT NULL, region VARCHAR(50) NOT NULL, name VARCHAR(255), email VARCHAR(255), registration_date DATE, PRIMARY KEY (customer_id, region) ) PARTITION BY LIST COLUMNS(region) ( PARTITION p_north_america VALUES IN ('US', 'CA', 'MX'), PARTITION p_europe VALUES IN ('UK', 'DE', 'FR', 'ES', 'IT'), PARTITION p_asia VALUES IN ('JP', 'CN', 'IN', 'SG', 'KR'), PARTITION p_oceania VALUES IN ('AU', 'NZ'), PARTITION p_south_america VALUES IN ('BR', 'AR', 'CL'), PARTITION p_other VALUES IN (DEFAULT) );
Разделение списков данных особенно полезно для обеспечения соответствия нормативным требованиям и суверенитета данных.
Вы можете физически хранить данные клиентов из ЕС на серверах внутри ЕС, в то время как данные из других регионов хранятся в другом месте, при этом сохраняя единую логическую таблицу.
Пример реализации: Node.js с PostgreSQL
Вот как работать с таблицами, разделенными горизонтально, включая автоматическое управление разделами:
import pkg from 'pg'; const { Pool } = pkg; class PartitionManager { constructor(connectionConfig) { this.pool = new Pool(connectionConfig); } async createFuturePartitions(tableName, monthsAhead = 3) { const client = await this.pool.connect(); try { for (let i = 0; i < monthsAhead; i++) { const date = new Date(); date.setMonth(date.getMonth() + i); const startDate = new Date(date.getFullYear(), date.getMonth(), 1); const endDate = new Date(date.getFullYear(), date.getMonth() + 1, 1); const partitionName = `${tableName}_${startDate.getFullYear()}_${String(startDate.getMonth() + 1).padStart(2, '0')}`; const exists = await client.query( 'SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = $1)', [partitionName] ); if (!exists.rows[0].exists) { await client.query(` CREATE TABLE ${partitionName} PARTITION OF ${tableName} FOR VALUES FROM ($1) TO ($2) `, [startDate, endDate]); await client.query(`CREATE INDEX idx_${partitionName}_user ON ${partitionName}(user_id)`); console.log(`Created partition: ${partitionName}`); } } } finally { client.release(); } } async archiveOldPartitions(tableName, monthsToKeep = 12) { const client = await this.pool.connect(); const cutoffDate = new Date(); cutoffDate.setMonth(cutoffDate.getMonth() - monthsToKeep); try { const result = await client.query(` SELECT tablename FROM pg_tables WHERE tablename LIKE $1 || '_%' AND tablename ~ '_[0-9]{4}_[0-9]{2}$' `, [tableName]); for (const row of result.rows) { const match = row.tablename.match(/_(\d{4})_(\d{2})$/); if (match) { const partitionDate = new Date(parseInt(match[1]), parseInt(match[2]) - 1); if (partitionDate < cutoffDate) { await client.query(`ALTER TABLE ${tableName} DETACH PARTITION ${row.tablename}`); console.log(`Archived partition: ${row.tablename}`); } } } } finally { client.release(); } } } class OrderService { constructor(pool) { this.pool = pool; } async getRecentOrders(userId, days = 30) { const startDate = new Date(); startDate.setDate(startDate.getDate() - days); const result = await this.pool.query(` SELECT order_id, user_id, order_date, status, total_amount FROM orders WHERE user_id = $1 AND order_date >= $2 ORDER BY order_date DESC LIMIT 100 `, [userId, startDate]); return result.rows; } async createOrder(orderData) { const result = await this.pool.query(` INSERT INTO orders (user_id, order_date, status, total_amount, items) VALUES ($1, $2, $3, $4, $5) RETURNING order_id `, [ orderData.userId, orderData.orderDate || new Date(), orderData.status, orderData.totalAmount, JSON.stringify(orderData.items) ]); return result.rows[0].order_id; } } export { PartitionManager, OrderService };
Эта реализация автоматизирует управление разделами, вместо того чтобы требовать ручного вмешательства.
Метод createFuturePartitions создает разделы заранее (обычно за 3–6 месяцев), чтобы предотвратить ошибки вставки при поступлении данных за новый период времени. Запуск этого метода с помощью запланированного задания гарантирует наличие буфера будущих разделов.
Метод archiveOldPartitions отсоединяет старые разделы от родительской таблицы, а не удаляет их сразу, что безопаснее, поскольку данные все еще могут быть восстановлены при необходимости.
Вы можете экспортировать отсоединенные разделы в холодное хранилище перед их полным удалением.
Сервис OrderService демонстрирует, как запросы автоматически выигрывают от сокращения разделов, когда они включают ключ раздела (order_date) в предложение WHERE, сканируя только релевантные разделы, а не всю таблицу.
Шардинг: вывод горизонтального партицирования на новый уровень.
Шардинг расширяет горизонтальное партицирование данных на несколько серверов баз данных. В то время как партицирование данных сохраняет данные в одной базе данных, шардинг распределяет их по отдельным экземплярам базы данных, каждый из которых потенциально может находиться на разном физическом оборудовании.
Когда использовать шардинг
Шардинг — это решение, когда один сервер базы данных не справляется с нагрузкой, даже при использовании секционирования. Рассмотрите шардинг в следующих случаях:
Пропускная способность записи достигает аппаратных ограничений: один сервер базы данных может обрабатывать лишь определённое количество операций записи в секунду. Когда вертикальное масштабирование (более мощное оборудование) и оптимизация исчерпаны, шардинг распределяет операции записи между несколькими серверами.
Объем данных превышает возможности одного сервера: даже на самом большом доступном сервере ваши данные не помещаются в память, и узким местом становится дисковый ввод-вывод.
Географическое распределение имеет решающее значение: вам необходимы данные, физически расположенные близко к пользователям в разных регионах, по причинам задержки или для соблюдения требований суверенитета данных.
Требуется изоляция арендаторов: крупным корпоративным клиентам необходимы выделенные ресурсы, или вы хотите предотвратить влияние трафика одного клиента на трафик других.
Стратегии шардинга
Шардинг на уровне приложения: приложение определяет, к какому шарду обращаться. Это наиболее гибкий подход, но он размещает логику маршрутизации в коде вашего приложения.
Вот практическая реализация с использованием Node.js:
import pkg from 'pg'; const { Pool } = pkg; class ShardManager { constructor(shardConfigs) { this.shards = shardConfigs.map(config => ({ id: config.id, pool: new Pool(config), ranges: config.ranges })); } getShardForUser(userId) { const shard = this.shards.find(s => userId >= s.ranges.min && userId <= s.ranges.max ); if (!shard) throw new Error(`No shard found for user ID: ${userId}`); return shard; } getShardForUserHash(userId) { const hash = this.hashUserId(userId); return this.shards[hash % this.shards.length]; } hashUserId(userId) { let hash = 0; const str = userId.toString(); for (let i = 0; i < str.length; i++) { hash = ((hash << 5) - hash) + str.charCodeAt(i); hash = hash & hash; } return Math.abs(hash); } async queryAllShards(query, params) { const results = await Promise.all( this.shards.map(shard => shard.pool.query(query, params)) ); return { rows: results.flatMap(r => r.rows) }; } async shutdown() { await Promise.all(this.shards.map(s => s.pool.end())); } } class UserService { constructor(shardManager) { this.shardManager = shardManager; } async getUser(userId) { const shard = this.shardManager.getShardForUser(userId); const result = await shard.pool.query( 'SELECT id, username, email FROM users WHERE id = $1', [userId] ); return result.rows[0]; } async createUser(userId, userData) { const shard = this.shardManager.getShardForUser(userId); const result = await shard.pool.query( 'INSERT INTO users (id, username, email) VALUES ($1, $2, $3) RETURNING *', [userId, userData.username, userData.email] ); return result.rows[0]; } async findUserByEmail(email) { const result = await this.shardManager.queryAllShards( 'SELECT id, username, email FROM users WHERE email = $1', [email] ); return result.rows[0]; } } export { ShardManager, UserService };
Эта реализация демонстрирует сегментирование на уровне приложения, где маршрутизация контролируется вашим кодом. ShardManager отвечает за определение того, к какой базе данных обращаться, поддержание пулов соединений и выполнение межсегментных запросов при необходимости.
Метод getShardForUser реализует сегментирование на основе диапазонов, где каждый сегмент отвечает за определенный диапазон идентификаторов пользователей, что полезно для пакетных операций и аналитики по связанным пользователям.
Метод getShardForUserHash обеспечивает сегментирование на основе хешей для равномерного распределения по сегментам, предотвращая перегрузку одного сегмента и простое других.
Метод queryAllShards демонстрирует дорогостоящую операцию сканирования всех сегментов, когда невозможно определить, какой сегмент содержит данные, поэтому для запросов, не использующих ключ сегмента, часто необходимо поддерживать отдельный индекс поиска (как в Redis).
Обратите внимание, что код UserService остается чистым и простым, поскольку вся сложность маршрутизации сегментов инкапсулирована в ShardManager.
Решения для шардинга на уровне базы данных
Некоторые базы данных предоставляют встроенные возможности сегментирования, которые обеспечивают прозрачную обработку маршрутизации:
Citus (расширение для PostgreSQL): преобразует PostgreSQL в распределенную базу данных. Вы определяете столбец распределения, и Citus автоматически распределяет запросы по сегментам и маршрутизирует их.
-- Включить расширение Citus CREATE EXTENSION citus; -- Укажите узел координатора SELECT citus_set_coordinator_host('coordinator.example.com', 5432); -- Добавить рабочие узлы (шарды) SELECT citus_add_node('worker1.example.com', 5432); SELECT citus_add_node('worker2.example.com', 5432); SELECT citus_add_node('worker3.example.com', 5432); -- Создание распределенной таблицы CREATE TABLE users ( id BIGINT PRIMARY KEY, username VARCHAR(255), email VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Распределить таблицу по сегментам по идентификатору пользователя SELECT create_distributed_table('users', 'id'); — Запрос прозрачно работает SELECT * FROM users WHERE id = 12345; — Citus автоматически перенаправляет это на соответствующий сегмент.
Vitess (шардинг MySQL): Первоначально разработанный YouTube, Vitess управляет шардингом MySQL в масштабе. Он обрабатывает маршрутизацию, перешардинг и межшардинговые запросы.
MongoDB: Имеет встроенный шардинг, где вы определяете ключ шарда, и MongoDB автоматически распределяет данные.
// Включить шардинг в базе данных sh.enableSharding("myapp") // Разделение коллекции пользователей по идентификатору пользователя с использованием хешированного сегментирования sh.shardCollection("myapp.users", { "_id": "hashed" }) // MongoDB обрабатывает маршрутизацию автоматически db.users.find({ "_id": ObjectId("...") })
Запросы между разделами: проблема производительности
Ограничения партицирования проявляются при запросах к данным из нескольких разделов. Когда вашему запросу требуются данные из нескольких разделов, база данных должна сканировать каждый раздел отдельно и объединять результаты.
Проектирование запросов с учетом разделов
Самый важный принцип: проектируйте запросы таким образом, чтобы они по возможности были нацелены на конкретные разделы. Включайте ключ раздела в условие WHERE.
Хорошо: Запрос с учетом разделов
-- Сканирование только раздела октября 2024 года SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-10-01' AND '2024-10-31' AND status = 'completed';
Плохо: Сканирует все разделы
— Необходимо просканировать каждый раздел, чтобы найти выполненные заказы. SELECT COUNT(*) FROM orders WHERE status = 'completed';
Агрегация по разделам
Если вам необходимо объединить данные по разделам, рассмотрите возможность использования сводных таблиц:
-- Таблица ежедневных сводок CREATE TABLE order_daily_stats ( date DATE PRIMARY KEY, total_orders INT, total_revenue DECIMAL(12,2), avg_order_value DECIMAL(10,2), completed_orders INT, cancelled_orders INT ); -- Заполнение с помощью ежедневного задания INSERT INTO order_daily_stats (date, total_orders, total_revenue, avg_order_value, completed_orders, cancelled_orders) SELECT DATE(order_date) as date, COUNT(*) as total_orders, SUM(total_amount) as total_revenue, AVG(total_amount) as avg_order_value, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_orders, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_orders FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '1 day' AND order_date < CURRENT_DATE GROUP BY DATE(order_date) ON CONFLICT (date) DO UPDATE SET total_orders = EXCLUDED.total_orders, total_revenue = EXCLUDED.total_revenue, avg_order_value = EXCLUDED.avg_order_value, completed_orders = EXCLUDED.completed_orders, cancelled_orders = EXCLUDED.cancelled_orders;
Теперь запросы к панели мониторинга, которым необходимы ежемесячные итоги, могут обращаться к сводной таблице вместо сканирования миллионов заказов:
— Быстрый запрос к ��водной таблице SELECT SUM(total_orders) as monthly_orders, SUM(total_revenue) as monthly_revenue FROM order_daily_stats WHERE date >= '2024-10-01' AND date < '2024-11-01';
Работа с кросс-шардовыми объединениями
Объединения между шардами представляют собой особенно сложную задачу. База данных не может эффективно объединять данные, хранящиеся на разных серверах. Стратегии решения этой проблемы:
Денормализация: Храните избыточные данные, чтобы избежать объединений. Если вам часто нужны имена пользователей вместе с заказами, включите имя пользователя в таблицу заказов, даже если оно также присутствует в таблице пользователей.
Объединения на уровне приложения: Получайте данные из каждого шарда отдельно и объединяйте их в коде приложения. Это работает для небольших наборов результатов, но плохо масштабируется.
Совместное размещение: Размещайте связанные таблицы шардов, используя один и тот же ключ шарда. Если и пользователи, и заказы шардированы по user_id, запросы, объединяющие их, будут выполняться на одном шарде.
// Пример объединения на уровне приложения async function getOrdersWithUserDetails(startDate, endDate) { const ordersResult = await shardManager.queryAllShards( 'SELECT order_id, user_id, total_amount FROM orders WHERE order_date >= $1 AND order_date <= $2', [startDate, endDate] ); const userIds = [...new Set(ordersResult.rows.map(order => order.user_id))]; const userPromises = userIds.map(async (userId) => { const shard = shardManager.getShardForUser(userId); const result = await shard.pool.query( 'SELECT id, username, email FROM users WHERE id = $1', [userId] ); return result.rows[0]; }); const users = await Promise.all(userPromises); const userMap = new Map(users.map(u => [u.id, u])); return ordersResult.rows.map(order => ({ ...order, user: userMap.get(order.user_id) })); }
Этот подход работает, но обратите внимание на сложность. Вы выполняете N+1 запросов (один для заказов, затем по одному для каждого пользователя).
Для повышения производительности используйте пакетную выборку пользователей по сегментам, чтобы сократить количество запросов к базе данных с потенциально тысяч до нескольких:
async function getOrdersWithUserDetailsBatched( startDate, endDate ) { const ordersResult = await shardManager.queryAllShards( 'SELECT order_id, user_id, total_amount FROM orders WHERE order_date >= $1 AND order_date <= $2', [startDate, endDate] ); const userIdsByShard = new Map(); for (const order of ordersResult.rows) { const shard = shardManager.getShardForUser(order.user_id); if (!userIdsByShard.has(shard.id)) { userIdsByShard.set(shard.id, []); } userIdsByShard.get(shard.id).push(order.user_id); } const userPromises = Array.from(userIdsByShard.entries()).map(async ([shardId, userIds]) => { const shard = shardManager.shards.find(s => s.id === shardId); const result = await shard.pool.query( 'SELECT id, username, email FROM users WHERE id = ANY($1)', [userIds] ); return result.rows; }); const usersArrays = await Promise.all(userPromises); const users = usersArrays.flat(); const userMap = new Map(users.map(u => [u.id, u])); return ordersResult.rows.map( order => ({ ...order, user: userMap.get(order.user_id) })); }
Пакетный подход группирует идентификаторы пользователей по принадлежности к определенному сегменту, а затем выполняет запрос к каждому сегменту один раз, используя все соответствующие идентификаторы пользователей.
Это значительно сокращает количество обращений к базе данных и повышает производительность, особенно при работе с сотнями или тысячами пользователей. Хотя избежать запросов к нескольким сегментам невозможно, можно минимизировать количество запросов к каждому сегменту, объединяя запросы в пакеты. Этот подход необходим для поддержания высокой производительности в сегментированных архитектурах, где операции между сегментами неизбежны.
Передовые методы и рекомендации по внедрению
Выбор ключа раздела
Выбор ключа раздела — наиболее важное решение. Неправильный выбор может сделать разделение данных бесполезным или даже вредным.
Для разделения данных по времени: используйте столбец времени, который запрашивается чаще всего. Обычно это метка времени создания, но учитывайте шаблоны ваших запросов. Если большинство запросов фильтруются по updated_at, а не по created_at, используйте разделение по updated_at.
Для разделения данных по арендаторам: используйте tenant_id или organization_id. Это естественным образом изолирует данные арендаторов и упрощает перемещение больших арендаторов в выделенные ресурсы.
Для географического разделения: используйте коды регионов или стран, если запросы обычно специфичны для региона.
Избегайте ключей с низкой кардинальностью: не используйте логические флаги или поля статуса с небольшим количеством значений. В итоге вы получите несколько больших разделов и множество пустых.
Учитывайте шаблоны запросов: ключ раздела должен присутствовать в предложении WHERE большинства запросов. Если ваши запросы редко фильтруются по ключу раздела, разделение данных не улучшит производительность.
Мониторинг состояния разделов
Отслеживайте следующие метрики, чтобы убедиться, что секционирование работает должным образом:
Распределение размеров разделов: Разделы примерно одинаковы по размеру или есть «горячие точки»? Неравномерное распределение указывает на проблему с ключом раздела или стратегией.
Производительность запросов: Отслеживайте время выполнения запросов до и после секционирования. Уменьшение размера разделов должно значительно сократить время выполнения запросов, фильтрующих по ключу раздела.
Сканирование разделов: Отслеживайте, сколько разделов сканирует каждый запрос. Если запросы постоянно сканируют все разделы, ваш ключ раздела не соответствует шаблонам запросов.
Использование индексов: Каждый раздел должен иметь соответствующие индексы. Отслеживайте размер и использование индексов, чтобы убедиться, что вы не тратите место на неиспользуемые индексы.
Вот запрос мониторинга для PostgreSQL:
— Анализ размера раздела и количества строк SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS indexes_size, (SELECT count(*) FROM only tablename) as row_count_approx FROM pg_tables WHERE tablename LIKE 'orders_%' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC; -- Запрос для просмотра того, к каким разделам обращались в течение последнего часа SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables WHERE tablename LIKE 'orders_%' AND (seq_scan > 0 OR idx_scan > 0) ORDER BY tablename;
Распространённые ошибки и как их избежать
Ошибка 1: Избыточное разбиение на разделы
Создание слишком большого количества небольших разделов может негативно сказаться на производительности. Каждый раздел имеет накладные расходы (метаданные, файловые дескрипторы и т. д.). Как правило, разделы должны содержать не менее сотен тысяч строк. Если вы создаете ежедневные разделы для таблицы, которая получает всего 1000 строк в день, вы используете избыточное разбиение на разделы.
Решение: Используйте более крупные окна разделов. Еженедельные или ежемесячные разделы вместо ежедневных. Правильный размер зависит от объема ваших данных и шаблонов запросов.
Ошибка 2: Отсутствие ключа раздела в запросах
Если ваши запросы не фильтруют по ключу раздела, база данных должна сканировать все разделы. Это медленнее, чем для неразделенной таблицы, из-за накладных расходов на сканирование нескольких разделов.
Решение: Проведите аудит шаблонов запросов, прежде чем принимать решение о разбиении на разделы. Используйте EXPLAIN ANALYZE, чтобы убедиться, что запросы используют отсечение разделов. Если большинство запросов не получают от этого пользы, пересмотрите разбиение на разделы или выберите другой ключ раздела.
Ошибка 3: Игнорирование операций обслуживания
Операции VACUUM, ANALYZE и REINDEX должны выполняться для каждой секции отдельно. Если у вас 100 секций, операции обслуживания займут примерно в 100 раз больше времени.
Решение: Автоматизируйте обслуживание для каждой секции. Запланируйте выполнение VACUUM и ANALYZE для каждой секции в зависи��ости от частоты ее обновления. Старые секции только для чтения редко нуждаются в обслуживании.
Ошибка 4: Несогласованная схема секций
В некоторых базах данных добавление столбцов или изменение индексов в родительской таблице не применяется автоматически к существующим секциям. В результате получаются несогласованные схемы между секциями.
Решение: Используйте функции базы данных, которые распространяют изменения схемы на секции (PostgreSQL 11+ делает это автоматически). Для более старых версий или других баз данных используйте скрипты, которые применяют изменения ко всем секциям.
Ошибка 5: Неправильное распределение ключей шардирования
Шардирование на основе хеширования с использованием неэффективной хеш-функции может создавать «горячие точки», где некоторые шарды получают гораздо больший трафик, чем другие.
Решение: Используйте проверенные хеш-функции (MurmurHash, CityHash) или функции шардирования, предоставляемые базой данных. Отслеживайте распределение нагрузки на шарды и будьте готовы к перераспределению трафика при необходимости.
Когда НЕ следует партицировать?
Разделение данных на разделы (партицирование/секционирование) не всегда является оптимальным решением. Избегайте партицирования, если:
Ваша таблица небольшая: если вся таблица помещается в памяти (обычно менее 10 ГБ), секционирование усложняет работу без каких-либо преимуществ.
Запросы не фильтруют данные по одному и тому же столбцу: если разные запросы фильтруют данные по разным столбцам, ни один ключ партицирования не поможет всем запросам.
Вам необходимы сложные соединения между партициями: если большинство запросов объединяют данные из разных разделов, партицирование ухудшит производительность, сделав соединения более затратными.
Ваша инфраструктура не готова: секционирование требует более тщательного мониторинга, автоматизации обслуживания и повышения операционной эффективности. Если вы только изучаете основы администрирования баз данных, сначала освойте эти навыки.
Сочетание вертикального и горизонтального партицирования данных
В самых сложных системах одновременно используются как вертикальное, так и горизонтальное партицирование.
-- Сначала вертикальное партицирования пользовательских данных. CREATE TABLE users_auth ( id BIGINT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL ) PARTITION BY RANGE (created_at); -- Затем проведите горизонтальное партицирования по времени (новые партиции для каждого месяца). CREATE TABLE users_auth_2024_01 PARTITION OF users_auth FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE users_auth_2024_02 PARTITION OF users_auth FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); -- Отдельная таблица для профиля (вертикально разделена по отношению к авторизации) -- Также горизонтально разделена по времени создания пользователя CREATE TABLE users_profile ( user_id BIGINT, full_name VARCHAR(255), bio TEXT, avatar_url VARCHAR(512), location VARCHAR(255), created_at TIMESTAMP NOT NULL, PRIMARY KEY (user_id, created_at) ) PARTITION BY RANGE (created_at); CREATE TABLE users_profile_2024_01 PARTITION OF users_profile FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE users_profile_2024_02 PARTITION OF users_profile FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
Такой комбинированный подход обеспечивает множество преимуществ.
Запросы аутентификации затрагивают только небольшие разделы users_auth и сканируют только последние периоды времени.
Обновления профилей не блокируют данные аутентификации.
Старые данные пользователей можно архивировать, удаляя целые разделы из обеих таблиц.
Система масштабируется как вертикально (путем разделения столбцов), так и горизонтально (путем разделения строк).
✅ Подведение итогов
Разделение базы данных на разделы — мощный метод масштабирования, но он требует тщательного планирования и постоянного обслуживания.
Выберите правильную страте��ию: вертикальное разделение для широких таблиц с различными шаблонами доступа; горизонтальное разделение для больших таблиц, где запросы естественным образом фильтруются по определенному столбцу; шардирование, когда один сервер не справляется с нагрузкой.
Проектируйте систему, исходя из ключа раздела: выбор ключа раздела определяет, поможет ли разделение или навредит. Он должен соответствовать наиболее часто используемым шаблонам запросов.
Автоматизируйте обслуживание: создание, архивирование и мониторинг разделов должны быть автоматизированы. Ручное управление не масштабируется.
Мониторинг эффективности: отслеживайте размеры разделов, производительность запросов и сокращение разделов. Корректируйте свою стратегию на основе реальных моделей использования.
Начните с простого: начните с вертикального разделения, чтобы разделить задачи. Добавьте горизонтальное разделение, когда таблицы станут большими. Рассматривайте шардирование только тогда, когда разделения на одном сервере недостаточно.
Проектируйте с учётом запросов объединяющих данные разных партиций: понимайте, что некоторые запросы будут охватывать несколько партиций, и проектируйте соответственно. Используйте сводные таблицы, денормализацию или агрегацию на уровне приложения, когда это необходимо.
Разделение на разделы преобразует архитектуру вашей базы данных из монолитной структуры в более гибкую и масштабируемую систему. Инвестиции в планирование и автоматизацию окупаются по мере роста вашего приложения, обеспечивая производительность и масштабируемость, необходимые для обслуживания миллионов пользователей при сохранении управляемой операционной сложности.
Помните, цель состоит не в том, чтобы разделить всё, а в том, чтобы стратегически разделить разделы там, где это дает очевидные преимущества. Начните с самых больших и часто используемых таблиц, внедрите надлежащий мониторинг и расширяйте свою стратегию разделения по мере развития ваших потребностей.
🔍 Искали онлайн-инструмент для проектирования баз данных?
Проектировать БД в гугл-таблицах, и тем более в текстовом редакторе не удобно, не видно связей между сущностями, а простая правка может потребовать дополнительной работы.
Раньше я использовал:
dbdesigner - очень дорогая подписка;
word/google docs - либо только текстовое описание, либо очень муторная работа со вставками объектов;
В draw.io - не хватало функциональности и ориентированности на таблицы/связи. Сложно вносить правки, плохая визуализация.
Карандашом на бумаге тоже может быть по привычному удобно, однако рисунок на листе бумаги долго переносить в код, а также сложно масштабирутеся, и проблематично правится.
👍 Сейчас уже несколько лет я использую Database Design:
Удобство и простота, приятный интерфейс, легко редактировать типов данных и прочие настройки в таблицах, наглядная визуализация связей, и всех таблиц схемы.
Также есть экспорт схемы в SQL или любой другой формат, есть даже ИИ-помощник с широким спектром использования: от консультаций и массового редактирования таблиц, до проектирования базы данных, и написания кода на любом языке программирования, с пониманием контекста базы данных открытого проекта.
При выборе типа данных появляется развёрнутая подсказка об нюансах типа данных, что помогает наиболее эффективно продумывать хранение данных. Работает в web на любом устройстве, схемами можно делиться в один клик.
Делюсь так как это действительно удобно, а значит пусть как можно больше людей узнают, как это удобно!
👆 Демонстрация проектирования базы данных в Database Design.
Спасибо за внимание!
Для вас статью перевёл и выложил Андрей Рик, с разрешения автора Artem Khrienov. Оригинал статьи.
