Подробное объяснение разделения хранилища данных

Недавно мне пришлось построить хранилище данных для определенной сферы бизнеса. Проектирование и планирование были довольно хорошими. Однако, когда я столкнулся с хранилищем данных, я обнаружил все 1 ошибку. и маленькие, и наконец нашли их. Самая большая неприятность во всем процессе — это проблема проектирования раздела хранения данных ETL. Итак, в этот момент кто-то, должно быть, сказал: «Ну, это мелочь, если мы не настроим так много таблиц разделов, разве этого не будет достаточно, чтобы скорректировать весь масштаб?» Но дело в том, что если бизнес действительно необходимо просмотреть за два дня до и после, необходимо будет выполнить секционирование. За последние несколько дней я познакомился с дизайном секционирования каждой бизнес-таблицы и приобрел опыт. много нового понимания вероятности разделения хранилища данных и понимания.

1. Зачем нам нужно разбивать таблицу данных?

Итак, давайте сначала переосмыслим концепцию секционирования таблицы данных.

Данные разделяй и властвуй：Основой разделения является“разделяй и властвуй”,То есть разделение очень большой таблицы данных на несколько меньших блоков данных. Преимущество этого в том, что это может ускорить запрос.,Поскольку запрос может сканировать только соответствующие разделы, а не всю таблицу.。Разделы можно разделить по времени、географическое положение、Разделите по нескольким различным измерениям, таким как идентификатор.

Повышение эффективности запросов：хранилище Объем данных в data часто очень велик,Сканирование всей таблицы напрямую приводит к неэффективной и трудоемкой операции. по разделу,Сканировать нужно только разделы, соответствующие условиям,без необходимости сканирования всей таблицы,Это значительно уменьшает объем сканируемых данных. Например,При запросе таблицы данных, разделенной по периоду,Если вы запрашиваете данные только за определенный день,Тогда система получит доступ только к разделу соответствующего дневного периода.,Без необходимости сканирования всей таблицы.

Простое управление данными：Секционирование делает управление данными более гибким.и Эффективный。Например,Исторические данные могут быть разделены,Это позволяет архивировать или удалять только данные за определенный временной диапазон.,Избегайте крупномасштабных операций удаления всей таблицы. Это может снизить вероятность блокировки стола.,продвигатьбаза Наличие данныхи оперативность обновления данных.

параллельная обработка：Разделение может сделатьбаза данных Двигательпараллельная обработкаболее эффективен в,Потому что каждый раздел можно обрабатывать как независимую единицу. Несколько процессов запроса могут выполнять операции параллельно в разных разделах.,Это помогает повысить общую производительность запросов.

Не беда, если вы не помните вышеперечисленные четыре пункта, ведь они достаточно абстрактны, после выполнения нескольких складских построек все равно сложно прийти к какому-либо глубокому пониманию. Менеджеры супермаркетов, как мы поступаем с взаимоотношениями между товарами:

Предположим, вы управляете большим супермаркетом, и миссия супермаркета — облегчить покупателям быстрый поиск нужных им продуктов. В супермаркетах много видов и огромное количество товаров. Без какой-либо классификации и организации покупателям будет очень сложно найти то, что они хотят. Это все равно, что столкнуться с большой таблицей данных без разделов и попытаться найти в ней конкретные товары. Данные требуют очень много времени и неэффективны.

1.Сортируйте товары по категориям,Повысьте эффективность поиска： в супермаркете,Товары обычно делятся на категории.,Например, зона напитков, дневная зона поставок、закусочная、Фруктово-овощной участок、Зона замороженных продуктов и т. д.。Эти категории эквивалентныраздел данныхпроцесс。Разделив продукты на разные области,Клиенты могут в соответствии со своими потребностями,Перейдите непосредственно в соответствующую область, чтобы найти продукты.,Не нужно искать по всему супермаркету. Это как в большой таблице данных,Если мы разделим данные по времени или категории,При запросе вам нужно искать только соответствующие разделы.,вместо сканирования всей таблицы.

2.Историческая обработка товаров (управление историческими данными)：Супермаркеты также регулярно проверяют старые、Уберите вещи, которые больше не продаются。Например,Старые товары будут удалены или помещены в зону скидок.,И эти предметы больше не занимают главное место на полках.。это какраздел данныхможет помочь справитьсяАрхивирование и очистка исторических данных。Для таблицы данных, разделенной по времени,Мы можем легко удалить или заархивировать старый раздел данных.,Не влияя на управление и запрос новых данных.

3.разделенныйпараллельная обработка： Представьте себе, что несколько клиентов одновременно совершают покупки в разных местах.,Например, покупатель выбирает фрукты в разделе фруктов и овощей.,Другой покупатель выбирает замороженные продукты в морозильном отделении.,Планировка супермаркета позволяет этим покупателямпараллельнозавершить свою торговую миссию,Не мешайте друг другу。Это похоже нараздел данных поддерживает параллельные запросыконцепция。база Система данных может выполнять параллельную работу с данными в разных разделах. обработка, улучшение общей скорости ответа и возможностей обработки.

4.Избегайте слишком маленьких разделов (избегайте слишком большого количества разделов)： Если зонирование супермаркета слишком сложное,Например, у каждого продукта есть независимая зона (молоко, йогурт, чистое молоко, обезжиренное молоко и т. д. находятся в разных разделах),Клиенты будут чувствовать себя растерянными,Также станет сложнее найти товары.。это какраздел данныхЕсли мы разделим данные на слишком маленькие,Системе необходимо управлять слишком большим количеством разделов.,Наоборот, это приведет к ухудшению производительности.。такразделенныйтребования к дизайнуСбалансированная детализация,Это может эффективно помочь найти,Это не приведет к слишком большому увеличению затрат на управление.

Вышеупомянутое представляет собой почти концепцию дизайна всего раздела данных, которая делает наше окончательное деловое соединение более быстрым и удобным, а также сокращает ненужное дублирование рабочего времени. Тогда мы знаем, что секционирование полезно, но не все таблицы должны быть секционированы. Вместо этого это будет очень избыточно, как четвертый пункт.

2. Какие таблицы подходят для секционирования? Какие таблицы не нуждаются в секционировании?

Не все таблицы данных подходят для операций секционирования. Применение секционирования должно определяться на основе характеристик таблицы и сценариев использования.

Стол, подходящий для разделения

Таблицы с чрезвычайно большими объемами данных：

• Типичные характеристики：Таблица содержит большой объем данных,Часто существуют миллионы или даже миллиарды строк записей.
• Если таблица очень большая и ее полное сканирование занимает очень много времени, секционирование может значительно уменьшить объем данных, которые необходимо обработать, тем самым повысив производительность запросов. Например: таблица журнала, таблица исторических заказов, запись данных датчика и т. д.

Таблица данных, запрошенная по измерению времени：

• Типичные характеристики：Данные имеют очевидные временные атрибуты.,А запросы часто фильтруются по времени.
• Например, таблица дневного журнала, таблица записей транзакций и т. д.,Данные в этих таблицах обычно хранятся по времени.,А при запросе часто нужно получить данные за конкретный период времени. Разделение по времени позволяет существенно сократить объем сканируемых данных.,Улучшить скорость запросов,В то же время это облегчает архивирование данных и управление ими.

Таблица данных со значительными логическими делениями：

• Типичные характеристики：Данные в таблице естественно разделить на несколько частей.,Например, группировка по географическому региону, типу продукта и т. д.
• Например, таблица информации о пользователе может быть разделена по регионам. Таким образом, при выполнении анализа, связанного с регионом, вы можете получить доступ только к разделам определенного региона, тем самым повышая производительность запросов.

Исторические данные должны быть заархивированы в таблице данных.：

• Типичные характеристики：В таблице больше исторических данных,К старым данным больше не обращаются часто.
• Например, исторические бизнес-данные в некоторых системах могут нуждаться в регулярном архивировании. Секционирование можно использовать для легкого архивирования и очистки определенных старых данных, не затрагивая самые последние данные, используемые в данный момент.

Таблицы, которые часто работают с определенными группами：

• Типичные характеристики：Операции над таблицами обычно сосредоточены на определенном подмножестве。
• Например, в системе электронной коммерции частота операций с незавершенными и завершенными заказами различна. Таблица заказов может быть разделена по статусу, чтобы обеспечить более быстрые операции с незавершенными заказами.

Нет Стол, подходящий для разделения

Таблица с небольшим объемом данных：

• Типичные характеристики：Количество данных в таблице Нетбольшой,Обычно имеется от нескольких тысяч до сотен тысяч строк.
• Разделение увеличит сложность управления и накладные расходы на систему. с небольшим объемом данных,Эти дополнительные накладные расходы могут фактически снизить производительность.,Существенных преимуществ нет. Для маленьких столов,Полное сканирование таблицы стоит не дорого,Преимущества разделения трудно реализовать.

Таблицы без очевидных условий разделения：

• Типичные характеристики：Данные в таблице не имеют очевидного Поле Подходит в качестве ключа раздела,Естественного способа разделения также не существует.
• Например, таблица, которая используется только для хранения элементов конфигурации или справочных данных, обычно не имеет логики секционирования и не имеет достаточно большого объема данных, чтобы ее можно было секционировать.

Режим запроса не Стол, подходящий для разделения：

• Типичные характеристики：В шаблоне запроса нет шаблона,Комбинации с участием нескольких Поле,А ключ раздела часто нельзя использовать в запросах.
• Если сложно ограничить запрос определенным разделом,Или вам нужно каждый раз сканировать несколько разделов,Тогда преимущества разделения станут ограниченными. Например,Если раздел в таблице данные разделены по «типу продукта», но большинство фактических запросов выполняются по «пользователям». ID", то эта схема разделения может не достичь ожидаемого эффекта оптимизации.

Таблицы, в которых часто обновляются ключи разделов：

• Типичные характеристики：Значение ключа раздела может часто меняться.,Данные часто перемещаются между разделами.
• При изменении значения ключа раздела,базе данных необходимо переместить соответствующие данные из одного раздела в другой,Эта операция очень дорогая,Может привести к значительному снижению производительности. поэтому,Для таблиц, которым часто требуется обновлять ключи разделов.,Разделение не рекомендуется.

Секционирование таблиц, которые могут вызвать проблемы с «горячими точками»：

• Типичные характеристики：кто-торазделенный数据量远большой于其他分区,Вызывает дисбаланс нагрузки.
• Например, если метод секционирования необоснован (например, секционирование по времени), а объем данных в определенный период времени сосредоточен в одном разделе, это приведет к частым операциям над определенным разделом, образуя горячие точки и влияя на производительность.

Давайте возьмем хранилище бизнес-домена с реальным риском. данных Приходите и посмотрите,Есть всегоrisk таблица (таблица рисков)、risk_expedite Форма (форма напоминания)、risk_handle Таблица (таблица обработки)、risk_read Таблица (таблица обзора рисков)、risk_rule Таблица (таблица правил)、risk_company_rule Таблица (Таблица правил компании)、risk_trigger_obj table (таблица объектов триггера).

Стол подходит для создания перегородок

1.risk таблица (таблица рисков)：risk В таблице фиксируется большое количество рисковых событий, и каждое рисковое событие связано с определенным временем (например, временем риска, временем выпуска заказа и т. д.). Таблицы этого типа обычно содержат очень большой объем данных, и компании обычно интересуются только записями о рисках в течение определенного периода времени. Разделение по времени может эффективно уменьшить объем данных запроса и повысить эффективность запросов. Кроме того, со временем исторические данные, возможно, больше не будут нуждаться в частом запросе, а секционирование по времени также облегчает архивирование и очистку.

2.risk_expedite Форма (форма напоминания)：Срочные операции обычно тесно связаны со временем.,Объем данных велик и продолжает увеличиваться. Разделение по времени,Вы можете легко получить записи напоминаний за определенный период времени.,И облегчить архивирование исторических данных.

3.risk_handle Таблица (таблица обработки)：Записи обработки также часто тесно связаны со временем.,Разделение по времени обработки упрощает управление записями обработки.,Повысьте эффективность запросов для обработки данных за определенный период времени.

4.risk_read Таблица (таблица обзора рисков)：Записи о проверке рисков часто накапливаются с течением времени и становятся большими.。Разделение по времени просмотра,Помогает повысить эффективность запроса записей за определенный период времени.,И облегчить управление историческими данными.

Нет необходимости создавать секционированные таблицы

1.risk_rule Таблица (таблица правил)：risk_rule В таблицах обычно хранятся правила управления рисками. Количество правил обычно невелико, и они не часто обновляются или добавляются. Поскольку объем данных невелик и затраты на полное сканирование таблицы невелики, секционирование не приведет к увеличению сложности системы.

2.risk_company_rule Таблица (Таблица правил компании)：В этой таблице хранится сопоставление между компаниями и правилами.,Данные в таблицах этого типа обычно не очень большие.,Не буду часто задавать вопросы,Поэтому секционирование не приносит существенного улучшения производительности. Напротив,Административные затраты на зонирование могут перевесить выгоды.

3.risk_trigger_obj Таблица (таблица объектов триггера)：risk_trigger_obj Данные в таблице в основном фиксируют конкретные объекты, вызванные риском. Объем данных относительно невелик и обычно используется только тогда, когда необходимо запросить информацию об объекте конкретного риска. Из-за небольшого объема данных и низкой частоты запросов стоимость управления разделами высока, а преимущества неочевидны.

Суждение

Чтобы определить, подходит ли бизнес-таблица для секционирования, можно обратиться к четырем пунктам:

Объем данных огромен?：Например, лист учета рисков、Форма напоминания, таблица обработки и т. д.,Поскольку бизнес накапливает данные, объем данных может быть очень большим.,Эти таблицы подходят для секционирования.

Имеют ли данные атрибуты времени：Если таблица Данные имеют очевидное временное измерение.（Например, когда возникает риск、Срочное время, время обработки и т. д.),Разделение по времени может существенно увеличить эффективности запросы облегчает управление историческими данными.

Режим запроса ясен?：Если запрос обычно фокусируется на определенном измерении（например, время）,Этот размер подходит для разделения ключей.

Небольшой объем данных или информации о правилах：Например, таблица правил риска、Форма правил компании и т. д.,Объем данных этих таблиц невелик.,Полное сканирование таблицы имеет низкое потребление производительности.,Никакого разделения не требуется.

3. На что следует обратить внимание при написании SQL-разделов и создании таблиц?

При написании разделов SQL для создания таблиц необходимо учитывать множество факторов, таких как тип раздела, ключ раздела, распределение данных, оптимизация запросов, обслуживание разделов и индексы. Правильное проектирование структуры разделов может значительно повысить эффективность запросов к данным и удобство управления, но секционирование также добавляет некоторую сложность. Поэтому необходимо выбрать наиболее подходящее решение, исходя из реального объема данных и сценариев бизнес-запросов.

SQL Существует множество типов разделов, например Раздел диапазона (Диапазон Partitioning）、Разделение списка、Хэш-разделение и Составной раздел (Композитный Partitioning）。Выбор правильного типа раздела очень важен.：

• раздел диапазона：Подходит для сегментации непрерывных данных, таких как время.,Например, разделение по частям Года и частям Луны.
• раздел списка：Подходит для сегментации данных с дискретными значениями.,Например, разделить по регионам и категориям.
• Хэш-раздел：Подходит для равномерно распределенных данных,Предотвращение искажения данных,Особенно, когда нет явного естественного ключа раздела.
• Составной раздел：Можно комбинировать более двух методов разделения.,Например, сначала нажмите раздел времени,Затем распределите его по хешу внутри каждого раздела. Этот метод подходит для сценариев, требующих более гибкой стратегии разделения.

Именование каждой таблицы разделов также индивидуально.,Дайте разделам осмысленные имена,Простота управления и обслуживания.

Извлечение Поле зависит от того, является ли оно полной суммой, приращением или существует ограничение на раздел для извлечения:

В реальных приложениях вы можете использовать инкрементальное, полное хранилище или хранилище с застежкой-молнией.

• управление транзакциями：При работе с секционированной таблицей,Может включать в себя несколькоразделенный Исправлять。Письмо SQL Когда требуется особое внимание Согласованность транзакций гарантирует, что данные во всех разделах могут корректно обрабатываться в транзакциях.
• Искажение данных, вызванное неправильным выбором ключей раздела：Если выбран ключ раздела Неткогда,Может привести к тому, что некоторые разделы будут содержать большие объемы данных.,Других разделов относительно немного. Такое искажение данных может привести к тому, что определенные разделы будут испытывать высокую нагрузку при запросе.,Другие разделы доступны редко,Это влияет на общую производительность. поэтому,Ключ раздела необходимо выбирать так, чтобы данные распределялись как можно более равномерно.
• Избегайте слишком частой смены разделов：Часто меняйте разделы（Например, частое слияние разделов、раскол и т. д.）Повлияет на стабильность столаипроизводительность,Частые изменения в разделах следует свести к минимуму.

1. Разделение диапазона

Разделение диапазона по времени — один из наиболее распространенных методов, особенно подходящий для таблиц данных с атрибутами времени, таких как таблицы журналов, таблицы записей транзакций и т. д.

Таблица торговых рисков разбита по годам:

CREATE TABLE risk (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    risk_code VARCHAR(50) NOT NULL UNIQUE,
    risk_company_id BIGINT NOT NULL,
    risk_time DATETIME DEFAULT NULL,
    risk_level TINYINT DEFAULT NULL,
    risk_desc LONGTEXT DEFAULT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE (YEAR(risk_time)) (
    PARTITION p_2022 VALUES LESS THAN (2023),    -- Данные о рисках на 2022 год
    PARTITION p_2023 VALUES LESS THAN (2024),    -- Данные о рисках на 2023 год
    PARTITION p_2024 VALUES LESS THAN (2025),    -- Данные о рисках на 2024 год
    PARTITION p_future VALUES LESS THAN MAXVALUE -- будущие данные
);

p_future Разделы используются для хранения данных за пределами текущего года, чтобы избежать сбоя при вставке данных.

2. Разделение списка

Разделение по определенным дискретным значениям, таким как регион, тип продукта, уровень риска и т. д. Подходит для сценариев, где данные имеют дискретные характеристики.

Правила риска разделены по уровням риска:

CREATE TABLE risk_rule (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    rule_code VARCHAR(150) NOT NULL UNIQUE,
    rule_name VARCHAR(150) NOT NULL,
    risk_level TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY LIST (risk_level) (
    PARTITION p_high VALUES IN (1),   -- высокий риск
    PARTITION p_medium VALUES IN (2), -- средний риск
    PARTITION p_low VALUES IN (3),    -- низкий риск
    PARTITION p_reminder VALUES IN (4) -- Уровень оповещения
);

Преимущество этого заключается в том, что запросы для определенных уровней риска могут обрабатываться быстрее.

3. Хэш-разделение

Хэш-раздел используется для равномерного распределения данных по нескольким разделам во избежание неравномерности данных. Он особенно подходит для ситуаций, когда объем данных велик и нет естественных разделов.

Хэш-разделение таблицы рисков по идентификатору компании:

CREATE TABLE risk (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    risk_code VARCHAR(50) NOT NULL UNIQUE,
    risk_company_id BIGINT NOT NULL,
    risk_time DATETIME DEFAULT NULL,
    risk_level TINYINT DEFAULT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY HASH (risk_company_id) PARTITIONS 4;

Это может обеспечить равномерное распределение данных в различных разделах и уменьшить количество узких мест в запросах и записи.

4. Составное разбиение

Составное секционирование сочетает в себе два или более метода секционирования и обычно используется для данных с несколькими измерениями.

по времениикомпания ID Создайте составной раздел:

CREATE TABLE risk (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    risk_code VARCHAR(50) NOT NULL UNIQUE,
    risk_company_id BIGINT NOT NULL,
    risk_time DATETIME DEFAULT NULL,
    risk_level TINYINT DEFAULT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE (YEAR(risk_time))
SUBPARTITION BY HASH (risk_company_id)
SUBPARTITIONS 4 (
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

основной раздел：в соответствии с risk_time Выполните разделение диапазона по году, чтобы разделить данные по годам.

подраздел：в каждомосновной В разделе нажмите risk_company_id Выполните хеш-секционирование, чтобы равномерно распределить данные по 4 в подразделе.

Это может эффективно объединить два измерения времени и компании для дальнейшей оптимизации производительности запросов.

На этом все, увидимся в следующий раз, будем рады вашему вниманию!