SQL-Ex blog

Диапазонное секционирование

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

Применение диапазонного секционирования

1. Создадим простую таблицу:

CREATE TABLE arctype.range_crypto(

  timestamp INT,

  open DOUBLE,

  close INT,

  high DOUBLE,

  low DOUBLE,

  volume DOUBLE,

  );

2. (не обязательно) Заполните таблицу данными. В данном случае загрузите этот набор данных. Затем импортируйте данные CSV в таблицу.
3. Создайте секции диапазонов с помощью выражения ALTER TABLE:

ALTER TABLE arctype.range_crypto

  PARTITION BY RANGE (close) (

      partition p0 VALUES LESS THAN (10000),

      partition p1 VALUES LESS THAN (20000),

      partition p2 VALUES LESS THAN (30000),

      partition p3 VALUES LESS THAN (40000),

      partition p4 VALUES LESS THAN (50000),

      partition p5 VALUES LESS THAN (60000),

      partition p6 VALUES LESS THAN  MAXVALUE

  )

4. Теперь вы можете запросить данные из любой созданной секции:

SELECT * 

  FROM arctype.range_crypto PARTITION (p3) 

  WHERE close BETWEEN 35000 and 38000;

И напротив, если выполнить такой запрос:

SELECT * 

  FROM arctype.range_crypto PARTITION (p0) 

  WHERE close BETWEEN 35000 and 38000;

то результатом будет пустой набор. Задание секции указывает MySQL, куда смотреть, что позволяет быстрей выполнять запросы (если у вас миллионы строк).

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

ALTER TABLE arctype.range_crypto 

  PARTITION BY RANGE COLUMNS (timestamp, close) (

    PARTITION from_2018_10k VALUES LESS THAN (1533127192, 10000),

    PARTITION from_2019_20k VALUES LESS THAN (1564663192, 20000),

    PARTITION from_2020_30k VALUES LESS THAN (1596285592,30000),

    PARTITION from_2021_40k VALUES LESS THAN (1627821592,40000 ),

    PARTITION from_latest_highest VALUES LESS THAN (MAXVALUE, MAXVALUE)

  );

Если вы хотите запросить все строки с временными метками между 2018 и 2019, когда цены закрывались между 13500 и 11600, можно выполнить подобный запрос:

SELECT * 

  FROM arctype.range_crypto PARTITION(from_2019_20k) 

  WHERE close BETWEEN 11600 AND 13500

Списочное секционирование

При списочном секционировании строки группируются исходя из того, что их значение в столбце, используемого для секционирования, подобно значению в заданном списке (набору дискретных значений). Другими словами, когда вы создаете список (списки) секционирования, MySQL проверяет: "Содержит ли столбец в этой строке значение, подобное значениям в этом списке?" Если значение совпадает, MySQL добавляет строку в секцию для этого значения в вашем списке.

Применение списочного секционирования

1. Создайте пробную таблицу с помощью следующего скрипта:

CREATE TABLE arctype.football(

  home_team TEXT,

  away_team TEXT,

  home_goals INT,

  away_goals INT, 

  result TEXT,

  season TEXT

  );

2. Не обязательно - наполните таблицу тестовыми данными. В данном случае загрузите этот набор данных.
3. Теперь вы можете создать списочное секционирование с помощью выражения ALTER TABLE.

ALTER TABLE arctype.football

  PARTITION BY LIST (home_goals) (

  PARTITION odd VALUES IN (1,3,5,7,9),

  PARTITION even VALUES IN (0,2,4,6,8)

  );

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

SELECT * FROM arctype.football PARTITION(odd) WHERE (home_goals=3);

Хэш-секционирование

При определении предыдущих разбиений, вы имели ситуацию, при которой значение столбца, диапазон или значения столбцов попадали в конкретную секцию. При хэш-секционировании вы должны задать желаемое число секций для столбца (или выражения). MySQL использует MOD(выражение, число секций) для определения того, куда попадет та или иная строка. Чтобы понять, как это работает, рассмотрим пример:



Для первой строки, секция, к которой она принадлежит определяется на основе столбца home_goals с помощью MOD(1,2)=1. Секция для третьей строки будет определяться с помощью MOD(2,2)=0. Если у вас будет строка с home_goals = 9, то MOD(9,2)=1.

Применение хэш-секционирования

Вы можете применить хэш-секционирование в MySQL с помощью того же самого оператора ALTER TABLE.

ALTER TABLE arctype.football

PARTITION BY HASH (home_goals)

PARTITIONS 2;

Заключение

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