Предисловие:
Секционирование — это шаблон проектирования таблиц.С точки зрения неспециалиста, секционирование таблиц — это разделение большой таблицы на несколько небольших таблиц в соответствии с условиями. Но для приложения секционированная таблица — это то же самое, что и таблица без секций. Другими словами, раздел прозрачен для приложения, но база данных переупорядочивает данные. Эта статья знакомит вас с введением и сценариями использования секционированных таблиц в MySQL.
1. Назначение перегородки и тип перегородки
MySQL может создать таблицу, используяPARTITION BYПункт определяет данные, хранящиеся в каждом разделе. При выполнении запроса оптимизатор фильтрует разделы, в которых нет нужных нам данных в соответствии с определением раздела, так что запросу не нужно сканировать все разделы, а нужно только найти разделы, содержащие требуемые данные.
Другой целью секционирования является хранение данных в разных таблицах в соответствии с более грубой степенью детализации. Это позволит сохранить связанные данные вместе, а также будет удобно, когда мы хотим выполнить пакетное удаление данных сразу для всего раздела.
Четыре распространенных типа разделов кратко описаны ниже:
- ДИАПАЗОН раздел: чаще всего используется, назначает несколько строк разделам на основе значений столбцов, принадлежащих заданному непрерывному интервалу. Наиболее распространенный основан на полях времени.
- СПИСОК разделов: Раздел LIST аналогичен разделу RANGE, разница в том, что LIST представляет собой набор списков перечисляемых значений, а RANGE — набор значений непрерывного интервала.
- HASH-раздел: Разделы для выбора на основе возвращаемого значения определяемого пользователем выражения, вычисляемого с использованием значений столбцов строк, которые должны быть вставлены в таблицу. Эта функция может содержать любое допустимое в MySQL выражение, которое возвращает неотрицательное целочисленное значение.
- КЛЮЧЕВОЙ раздел: Подобно разбиению по HASH, разница в том, что разбиение по KEY поддерживает только вычисление одного или нескольких столбцов, а сервер MySQL предоставляет собственную хеш-функцию. Один или несколько столбцов должны содержать целочисленные значения.
Среди четырех вышеперечисленных типов разделовRANGE分区То есть разбиение по диапазонам используется чаще всего. Характеристика раздела RANGE заключается в том, что диапазоны нескольких разделов должны быть непрерывными, но не могут перекрываться.По умолчанию используется атрибут VALUES LESS THAN, то есть каждый раздел не включает указанное значение.
2. Пример работы с разделами
В этом разделе раздел RANGE используется в качестве примера, чтобы представить операции, связанные с таблицей разделов.
# 创建分区表
mysql> CREATE TABLE `tr` (
-> `id` INT,
-> `name` VARCHAR(50),
-> `purchased` DATE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005),
-> PARTITION p4 VALUES LESS THAN (2010),
-> PARTITION p5 VALUES LESS THAN (2015)
-> );
Query OK, 0 rows affected (0.28 sec)
# 插入数据
mysql> INSERT INTO `tr` VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'alarm clock', '1997-11-05'),
-> (3, 'chair', '2009-03-10'),
-> (4, 'bookcase', '1989-01-10'),
-> (5, 'exercise bike', '2014-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'espresso maker', '2011-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '2006-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
После создания вы можете видеть, что каждому разделу будет соответствовать файл ibd. Приведенный выше оператор создания хорошо понятен.В этой таблице разделов год в дате DATE вынимается с помощью функции YEAR и преобразуется в целочисленный тип.Год меньше 1990 хранится в разделе p0, а год меньше 1995 хранится в разделе p1 и так далее. Обратите внимание, что порядок, в котором определяется каждый раздел, от низшего к высшему. Чтобы вставленные данные не сообщали об ошибке, потому что соответствующий раздел не может быть найден, мы должны вовремя создать новый раздел. Далее показаны другие операции по обслуживанию разделов.
# 查看某个分区的数据
mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
# 增加分区
mysql> alter table tr add partition(
-> PARTITION p6 VALUES LESS THAN (2020)
-> );
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 拆分分区
mysql> alter table tr reorganize partition p5 into(
-> partition s0 values less than(2012),
-> partition s1 values less than(2015)
-> );
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 合并分区
mysql> alter table tr reorganize partition s0,s1 into (
-> partition p5 values less than (2015)
-> );
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 清空某分区的数据
mysql> alter table tr truncate partition p0;
Query OK, 0 rows affected (0.11 sec)
# 删除分区
mysql> alter table tr drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 交换分区
# 先创建与分区表同样结构的交换表
mysql> CREATE TABLE `tr_archive` (
-> `id` INT,
-> `name` VARCHAR(50),
-> `purchased` DATE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.28 sec)
# 执行exchange交换分区
mysql> alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)
3. Меры предосторожности при разделении и применимые сценарии
На самом деле существует множество ограничений и вопросов, требующих внимания при использовании таблиц разделов.Пожалуйста, обратитесь к официальным документам и кратко изложите пункты следующим образом:
- Поле раздела должно быть целочисленного типа или выражением, которое разрешается в целое число.
- Рекомендуется установить для поля раздела значение NOT NULL.Если поле раздела данных строки равно нулю, в разделе RANGE данные строки будут разделены на наименьший раздел.
- Если первичный или уникальный ключ существует в разделе MySQL, столбец разделения должен быть включен в него.
- Многораздельные таблицы Innodb не поддерживают внешние ключи.
- Изменение режима sql_mode может повлиять на производительность секционированных таблиц.
- Секционированные таблицы не влияют на автоматически увеличивающиеся столбцы.
Как видно из введения выше, секционированные таблицы подходят для некоторых таблиц протоколирования. Этот тип таблицы характеризуется большим объемом данных, и существует различие между горячими и холодными данными, и данные могут быть заархивированы в соответствии с измерением времени. Этот тип таблицы больше подходит для использования секционированной таблицы, поскольку секционированная таблица может поддерживать отдельный раздел, что более удобно для архивирования данных.
4. Почему таблица разделов редко используется
В нашем проекте таблица разделов используется редко, кратко поясним следующие причины:
- Выбор полей раздела ограничен.
- Если в запросе не используется ключ раздела, могут быть просканированы все разделы, и эффективность не улучшится.
- Если данные распределены неравномерно и размеры разделов сильно различаются, повышение производительности может быть ограниченным.
- Преобразование общей таблицы в таблицу разделов затруднительно.
- Раздел необходимо поддерживать постоянно, например, раздел за июнь будет добавлен до июня.
- Увеличьте стоимость обучения, есть неизвестные риски.
Суммировать:
Эта статья более подробно знакомит с содержанием секционирования MySQL.Если вы хотите использовать секционированную таблицу, рекомендуется планировать заранее, создать секционированную таблицу и сформулировать план обслуживания во время инициализации.Удобнее использовать правильно , особенно при архивировании исторических данных. Если вам нужна таблица, использование секционированной таблицы сделает архивирование более удобным. Конечно, о таблице разделов еще много, желающие могут найти официальную документацию, в официальной документации много примеров.
Ссылаться на: