Партиционирование таблиц в mySQL
Начиная с версии 5.1 mySQL поддерживает горизонтальное партицирование таблиц. Что это такое? Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.. На нижнем уровне для myISAM таблиц, это физически разные файлы, по 3 на каждую партицию (описание таблицы, файл индексов, файл данных). Для innoDB таблиц в конфигурации по умолчанию – разные пространства таблиц в файлах innoDB (не забываем, что innoDB позволяет настраивать индивидуальные хранилища на уровне баз данных или даже конкретных таблиц).
Как это выглядит?
CREATE TABLE orders_range (
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = MYISAM
PARTITION BY RANGE( YEAR(order_date) ) (
PARTITION p_old VALUES LESS THAN(2008),
PARTITION p_2008 VALUES LESS THAN(2009),
PARTITION p_2009 VALUES LESS THAN(MAXVALUE)
);
Что мы получаем? Первая «таблица» будет хранить данные за «архивный» период, до 2008го года, вторая — за 2008й год, и «третья» — все остальное.
Самое вкусное — запросы при этом совершенно не надо переписывать/оптимизировать:
select * from orders_range where order_date=’2009-08-01′;
И вот что при этом происходит:
Мы видим, что при выполнении этого запроса работа будет идти исключительно с «подтаблицей» p_2008.
Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления. Так вот как раз «первые» этапы, в данном случае будут происходить гораздо быстрее.
Какие еще есть преимущества?
Главным преимуществом я бы назвал тот факт, что партиция с «оперативными» данными (т.е. последними, по которым наиболее часто происходит выборка) имеют минимальный размер, и как следствие, могут постоянно находится в оперативной памяти.
Если у вас есть таблица логов, в которую непрерывно идет запись и жесткие диски не успевают, а ставить рейд вам не позволяет религия, вы можете настроить партиционирование по хеш-функции, и указать по одной партиции на каждый доступный вам жесткий диск. В таком случае, новые данные будут равномерно писаться на все жесткие диски.
Какие способы «разделения» данных предоставляет mySQL?
По диапазону значений
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30)
);
По точному списку значений
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20)
)
Зачем, спросите вы? Разбивать на партиции необходимо либо исходя из соображений оптимизации выборки (что чаще) либо исходя из соображений оптимизации записи (реже). Соответственно, идеальный вариант — это когда вы разбиваете таблицу на максимально возможное количество партиций так, что бы 90% всех выборок происходило в пределах одной партиции. И если у вас сложная логика выборки (например, объекты расположенные в северных кварталах города, ID которых идут в разнобой) то иногда есть смысл перечислять их принудительно.
PARTITION BY HASH(store_id)
PARTITIONS 4;
Вы никак не управляете партицированием, просто указываете, по какому полю строить хеш и сколько «подтаблиц» создавать. Зачем? Гораздо быстрее происходит выборка по указанному полю. В некоторых случаях позволяет достигнуть «равномерного разброса» и ускорения записи данных.
Почти то же самое что и HASH, но более логично — по ключу.
PARTITION BY KEY(s1)
PARTITIONS 10;
Т.е. выборка по указанному ключевому полю происходит максимально эффективно.
Но тут так же следует определиться со способом партицирования. Хорошо подходит для счетчика посетителей, когда его логин является единственным идентификатором, по которому необходимо выбирать все остальные данные.
Нет вертикального партицирования. Это когда разные столбцы (поля) находятся в разных «подтаблицах». Поскольку иногда это бывает полезно, вы можете достичь этого самостоятельно, пусть даже не так прозрачно: разделить таблицу на две, связав их по первичному ключу. Если вам совсем хочется красоты — можете дополнительно создать по ним VIEW, например для того что бы не переписывать старые части кода.
Зачем это делать? Например, в таблице, где у вас в основном числа и даты, есть одно поле VARCHAR (255) для комментариев, которое используется на порядок реже чем остальные поля. В случае если его вынести в другую таблицу, то мы получим фиксированный размер строки (mySQL сможет совершенно точно вычислять позицию нужной строки по индексу в файле данных). Таблица станет более устойчивой к сбоям в случае внештатных ситуаций (опять же, из-за фиксированного размера строки). Ну и существенно уменьшится сам размер таблицы.
И заканчивая статью приведу пример более «реального» партицирования таблиц — помесячно. Так как LIST/RANGE принимают только целочисленные значения, то надо немного исхитрится:
PARTITION BY RANGE( TO_DAYS(order_date) ) (
PARTITION y2009m1 VALUES LESS THAN( TO_DAYS(‘2009-02-01’) ),
PARTITION y2009m2 VALUES LESS THAN( TO_DAYS(‘2009-03-01’) ),
PARTITION y2009m3 VALUES LESS THAN( TO_DAYS(‘2009-04-01’) )
);
PS: В mysql всегда приходится немного «исхитриться», так что скучно с ней не будет никогда, а мы в свою очередь никогда не останемся без работы 🙂
Партиции и загрузка данных в оперативную память Текст научной статьи по специальности «Компьютерные и информационные науки»
Похожие темы научных работ по компьютерным и информационным наукам , автор научной работы — Юлия Владимировна Мельник
Исследование способов повышения эффективности обработки данных в реляционных БД на примере СУБД MySQL
Метрики для динамического масштабирования баз данных в облачных средах
Прикладная библиотека бизнес-функций Bfl
Алгоритм динамического масштабирования реляционных баз данных в облачных средах
Параллельные вычисления в SAP Hana
i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.
i Надоели баннеры? Вы всегда можете отключить рекламу.
Текст научной работы на тему «Партиции и загрузка данных в оперативную память»
ПАРТИЦИИ И ЗАГРУЗКА ДАННЫХ В ОПЕРАТИВНУЮ ПАМЯТЬ
Юлия Владимировна Мельник
Консультант SAP BI / BW / BO / HANA БДО Юникон Бизнес Солюшнс.
Окончила Белгородский государственный национальный исследовательский университет. Магистр компьютерных наук и математики, математик-программист со специализацией «Математическое и программное обеспечение информационных систем».
Ю.В. Мельник. Партиции и загрузка данных в оперативную память
В данной статье будет рассмотрено, что такое партиции и партиционные таблицы, какие спецификации партицирования существуют, и как произвести загрузку данных в оперативную память в SAP HANA.
В связи с тем что существует ограничение на количество строк в таблице — не более 2 млрд., возникает задача преодоления этого ограничения. Задача решается с помощью разбиения таблицы на части, или партицирования. Таким образом, очень большие таблицы могут быть разбиты на маленькие, более управляемые части — партиции. Партиции могут быть распределены между несколькими хостами. Это значит, что запрос к таблице может обрабатываться на нескольких серверах вместо одного, что также способствует дальнейшему распараллеливанию с помощью выполнения нескольких потоков для каждой таблицы.
Партицирование — функция, которая позволяет разбивать большие таблицы на логические части по выбранным критериям, благодаря чему улучшается производительность базы данных.
Партиция — часть таблицы, логически выделенная для удобства хранения и обработки в памяти и состоящая из смежных блоков. Поэтому таблица, которая была разбита на партиции, называется партицированной.
Пример. Имеется таблица с данными кодов операций и сумм, где ключевой столбец — «Дата» (см. рисунок ниже). Представим, что данная таблица содержит много данных, и количество строк подходит к пороговому ограничению 2 млрд. при постоянном увеличении данных. Чтобы избежать переполнения таблицы, разобьем ее на партиции по ключевому полю «Дата», а именно по году. Отметим, что столбец, который используется для разбиения, называется столбцом партиций. В нашем примере будут заданы партиции по годам (2011, 2012, 2013) с помощью следующего SQL-кода (используется функция даты — year ()):
CREATE COLUMN TABLE MY_TABLE (a DATE, b VARCHAR (4), c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (year (a)) (PARTITION ‘2011′
В итоге получим следующие партиции (см. рисунок 1).
Мы получили три партиции, каждая из которых меньше основной таблицы, т.е. задача с ограничением по количеству строк решена. Отметим, что количество строк в каждой новой партиции также не должно превышать 2 млрд.
Вы можете просмотреть свойства таблицы, вызвав ее контекстное меню и выбрав пункт “Open Definition” (рисунок 2). Так, в столбце “Number of Entries”, представленном на рисунке ниже, приведено количество строк, которые были отнесены к каждой из партиций, а столбец “Loaded” показывает статус загрузки в оперативную память (“FULL” — данные полностью загружены в оперативную память).
Чтобы просмотреть список всех таблиц и определить, какие из них партицированы, а какие нет, у вас должны быть следующие полномочия: системная привилегия DATA ADMIN и привилегии SELECT и UPDATE для изменения таблицы или схемы. Если приви-
Технологическая платформа для решения современных бизнес-задач
Дата Код операции Сумма
08.2011 1003 6200
Дата Код операции Сумма
01.2012 5006 1250
02.2012 4375 2550
03.2013 1003 3800
01.2013 5006 4300
03.2012 4375 5500
08.2011 1003 6200
09.2013 1360 7350
04.2012 2180 8750
Дата Код операции Сумма
01.2012 5006 1250
02.2012 4375 2550
03.2012 4375 5500
04.2012 2180 8750
Дата Код операции Сумма
03.2013 1003 3800
01.2013 5006 4300
09.2013 1360 7350
Т>Ы«Турв TsbkW?me Sfhtmj Nam*
Ш C0ST.TYPE MELNIK ГП
яз H.MIN1.SRC MELNIK Ш
?53 MyjTASLE MELNK 1> 1 3, 4
т МУТШЕ1 MELNIK XI 3,4
2ВЗ МУ_ТЛ81_Е2 MELNK 1,2,3
1Ш MV.TA8LE3 MELNK 1.2
MV_TA0LE4 MELNIK 1,13
т NAFJTOG MELNIK m
Ю.В. Мельник. Партиции и загрузка данных в оперативную память
легии есть, то нужно вызвать контекстное меню папки Catalog, схемы или папки Tables и выбрать пункт “Show Table Distribution”. Отобразится список таблиц (см. рисунок 3). Заметим, что значок в столбце “Table Type” у партицированных и непартицированных таблиц выглядит по-разному. Также непартицированные таблицы отмечены крестиком «х», а для партицированных указаны партиции, на которые они разбиты. В данном окне вы можете разбить непартицированные таблицы или объединить партиции в уже разбитых таблицах с помощью команд из контекстного меню:
• Partition Table — Разбить таблицу на партиции
• Merge Partitions — Объединить партиции
Также для объединения всех частей партицированной таблицы в единую таблицу можно использовать следующий SQL-код:
ALTER TABLE MY_TABLE3 MERGE PARTITIONS;
Результат объединения партиций представлен на рисунке 4.
Стоит отметить, что партицирование также важно при распределении таблиц по нескольким хостам SAP HANA, что способствует улучшенному распараллеливанию запросов. Поэтому партицирование обычно используется в распределенных системах, но оно также может быть полезно для систем с одним сервером.
Критерии, в соответствии с которыми таблица разбивается на партиции, называются спецификациями партицирования. В зависимости от использования спецификаций, партицирование бывает одно- и многоуровневым.
Существуют следующие спецификации партицирования:
Если в партицировании используется какая-либо одна спецификация, то оно называется одноуровневым. Иначе при использовании вложенных спецификаций мы имеем дело с многоуровневым партицированием.
Column1.^Indexes j Furihif Propirtiis| Runtime Infer rmaticn]_____
Tcul Memory Consumption (Кв): ST Nvfflbtief Entries: 6
Size on Cis-k (KB): LS
PartitionSpecification: RANGE топ111(Д)2М51>1-211
Table Name: |Ц/_ТАеш
Columns Indexes I Furtber Properties I Runtime Information
Total Men-or> Consumpt(in(KB): 22 Numbirof Entries; 6
Size or> Disk (KB): 32
Partition $pe cificatjon:
Ddeib lor Table
Host. PsrtlC1 Rang* Total Size (KB) Main Sc
1 1 20«Й)Ь200507 42
2 i 2Q№Qi-2QQfQl 14
Detail* for Table
Host;.- Part ID Range
Total Size (Кв) 2)
Технологическая платформа для решения современных бизнес-задач
i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.
Hash-партицирование используется для одинакового распределения строк по партициям при распределении нагрузки и преодоления ограничения в 2 млрд. строк. Количество партиций вычисляется с помощью хэш-функции со значением заданного столбца.
Hash-партицирование не требует углубленного знания фактических значений таблицы. Для него должны быть указаны столбцы партиций. Можно использовать несколько таких столбцов. Если таблица имеет первичный ключ, то столбцы партиций должны быть частью первичного ключа. Ниже приведен пример SQL-кода для создания таблицы с помощью Hash-партицирования и четырьмя партициями.
CREATE COLUMN TABLE MY_TABLE (a INT, b INT, c INT, PRIMARY KEY (a, b))
PARTITION BY HASH (a, b) PARTITIONS 4;
С помощью функции GET_NUM_SERVERS, которая получает значение количества серверов, можно не указывать фиксированное количество партиций — оно будет задано динамически во время выполнения в соответствии с конфигурацией. Сколько серверов в системе, столько и будет создано партиций. Рекомендуется задавать количество партиций следующим образом:
PARTITION BY HASH (a, b) PARTITIONS GET_NUM_SERVERS ();
Более подробная информация о SQL-синтаксисе для партицирования содержится в руководстве SAP HANA SQL Reference.
Как же происходит процесс загрузки созданной таблицы в оперативную память? SAP HANA сама управляет загрузкой и выгрузкой таблиц в память и из памяти. Но при необходимости можно загружать и выгружать отдельные таблицы вручную.
Например, при создании таблицы она еще не загружена в память. Это можно про-
Columni Indents further PrtjMrtitl [Runtime Letenri»ban|
Number of Entries: )
Size on Disk (KB>: Li
Memaiy CenwiWjKiart in Meirt SttHtj* (kE): •
Memory Ceimimptian in Deln Storage (kE): —
Eitimated Maximum MimoiyConjum|ilion (IS): —
Партицирование баз данных
Партицирование, или секционирование, базы данных — разделение данных, хранящихся в базе данных, на части. Партицирование позволяет осуществлять горизонтальное масштабирование, так как вертикальное масштабирование имеет потолок — нельзя бесконечно долго добавлять память на один сервер. При разделении же данных на части их можно хранить на разных серверах, а серверы добавлять по мере необходимости. Доступность и производительность повышаются, так как запросы обращаются не к огромному куску данных, а к более маленьким и легковесным частям.
Управление ИТ услугами
- горизонтальное партицирование — таблица данных разбивается на строки;
- вертикальное партицирование — таблица данных разбивается на столбцы;
- функциональное партицирование — данные группируются согласно контексту их использования в системе.
Партиция
Раздел (англ. partition ) — часть долговременной памяти жёсткого диска, выделенная для удобства работы.
На других носителях информации выделение разделов или не предусмотрено, или (за редкими исключениями) не практикуется. Однако, существуют флеш-драйвы, память которых можно разбить на два раздела, причём один раздел можно защитить паролем, при этом система «увидит» составное устройство из двух флеш-карт (на самом деле любую флеш карту можно разбить на два и боле разделов, одноко драйвер в Windows отображает только первый из них; работа с ними возможна при подмене драйвера или при работе с другими ОС).
Преимущества использования разделов
Выделение на одном жёстком диске нескольких разделов даёт следующие преимущества:
- на одном физическом жёстком диске можно хранить информацию в разных файловых системах, или в одинаковых файловых системах, но с разным размером кластера (например, выгодно хранить файлы большого размера — например, видео — отдельно от маленьких, и задавать больший размер кластера для хранилища больших файлов);
- можно отделить информацию пользователя от файлов операционной системы;
- на одном жёстком диске можно установить несколько операционных систем;
- манипуляции с одной файловой системой не сказываются на других файловых системах.
Структура диска, разбитого на разделы
Информация о размещении разделов на жёстком диске хранится в таблице разделов (англ. partition table ), которая является частью главной загрузочной записи (MBR). MBR располагается в первом физическом секторе жёсткого диска.
Раздел может быть либо логическим диском, либо дополнительным разделом.
В первом секторе каждого логического диска находится загрузочный сектор (Boot Record), отвечающий за загрузку операционной системы с этого раздела. Информация о том, какой логический диск будет использован для загрузки операционной системы, тоже записана в главной загрузочной записи.
В MBR под таблицу разделов выделено 64 байта. Каждая запись занимает 16 байт. Таким образом, всего на жестком диске может быть создано не более 4 разделов. Когда разрабатывалась структура MBR, это считалось достаточным. Однако, позднее был введён дополнительный раздел. Он не является логическим диском, а также содержит таблицу разделов, в которой можно прописать ещё до 4 разделов.
По правилам дополнительный раздел может быть только один, и не может содержать другой дополнительный раздел. Таким образом, в максимальной конфигурации на жёстком диске может быть сформировано 3 основных логических диска и 1 дополнительный раздел, содержащий 4 логических диска. Всего 7 логических дисков. И только первичный диск может быть загрузочным. Однако, различные менеджеры разделов позволяют обходить эти ограничения.
Виды логических разделов
Первичный (основной) раздел
Первичный раздел (англ. primary partition ) обязательно должен быть на физическом диске. Этот раздел всегда содержит либо одну файловую систему, либо другие логические разделы. На физическом диске может быть до четырёх первичных разделов. Некоторые старые операционные системы — например, Microsoft Windows — могли быть установлены только на первичный раздел.
Расширенный (дополнительный) раздел
Основная таблица разделов может содержать не более 4 первичных разделов, поэтому был изобретён Расширенный раздел (англ. extended partition ). Это первичный раздел, который не содержит собственной файловой системы, а содержит другие логические разделы.
Пример разбиения жёсткого диска на разделы
| Физический диск | Первичный раздел 1 (Логический раздел 1): ФС | |
| Расширенный раздел (Первичный раздел 2, Логический раздел 2) | ||
| Логический раздел 4: ФС | ||
| Логический раздел 5: ФС | ||
| Первичный раздел 3 (Логический раздел 3): ФС | ||
Программы для работы с разделами
Программы для работы с разделами обычно называют «дисковыми утилитами».
- Paragon Partition Manager
- MS-DOS, позволяет создавать разделы FAT, удалять любые разделы и выбирать активный
- текстовые утилиты для работы с разделами диска под GNU/Linux
- Disk Druid — утилита для работы с разделами диска под GNU/Linux с графическим пользовательским интерфейсом
- GNOME Partition Editor (free, GUI)
- Оснастка «Управление дисками», являющаяся частью консоли WindowsNT/2000/XP(Windows Management Console), позволяет просматривать, создавать и удалять основные и логические разделы, изменить букву логического диска или отформатировать его.
Примечания
Ссылки
См. также
- Логический диск
- Файловая система
- Кластер
- Сектор
- Главная загрузочная запись (MBR)