Получение информации относительно разделов

 

Этот раздел обсуждает получение информации относительно существующих разделов, что может быть выполнено несколькими способами. Они включают:

 

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

 

Использование инструкции SHOW TABLE STATUS, чтобы определить, разбита ли таблица на разделы вообще.

Запрос таблицы INFORMATION_SCHEMA.PARTITIONS.

 

Использование инструкции EXPLAIN PARTITIONS SELECT, чтобы видеть, которые разделы используются данным SELECT.

 

SHOW CREATE TABLE включает в вывод предложение PARTITION BY, используемое, чтобы создать разбитую на разделы таблицу. Например:

 

mysql> SHOW CREATE TABLE trb3\G

*************************** 1. row ***************************

Table: trb3

Create Table: CREATE TABLE `trb3` (`id` int(11) default NULL,

`name` varchar(50) default NULL,

`purchased` date default NULL)

ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(purchased)) (

PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,

PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,

PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,

PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM)

1 row in set (0.00 sec)

 

Обратите внимание: в ранних версиях MySQL 5.1 предложение PARTITIONS не показывалось для

таблиц, разбитых на разделы HASH или KEY. Эта проблема была отфиксирована в MySQL 5.1.6.

 

SHOW TABLE STATUS работает с разбитыми на разделы таблицами. Начиная с MySQL 5.1.9, вывод такой же, как для не разбитых на разделы таблиц за исключением того, что столбец Create_options содержит строку partitioned. В MySQL 5.1.8 и ранее столбец Engine всегда содержал значение PARTITION. Начиная с MySQL 5.1.9, этот столбец содержит имя типа памяти, используемого всеми разделами таблицы.

 

Вы можете также получать информацию относительно разделов из INFORMATION_SCHEMA, которая содержит таблицу PARTITIONS.

 

Начиная с MySQL 5.1.5, можно определить, которые разделы разбитой на разделы таблицы включаются в данном запросе SELECT, применяя EXPLAIN PARTITIONS. Ключевое слово PARTITIONS

добавляет столбец partitions к выводу EXPLAIN, перечисляющего столбцы, из которых записи соответствуют запросу.

 

Предположите, что Вы имеете таблицу trb1 определенную и заполняемую следующим образом:

 

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)

PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (3),

PARTITION p1 VALUES LESS THAN (7),

PARTITION p2 VALUES LESS THAN (9),

PARTITION p3 VALUES LESS THAN (11));

INSERT INTO trb1 VALUES (1, 'desk organiser', '2003‑10‑15'),

(2, 'CD player', '1993‑11‑05'),

(3, 'TV set', '1996‑03‑10'),

(4, 'bookcase', '1982‑01‑10'),

(5, 'exercise bike', '2004‑05‑09'),

(6, 'sofa', '1987‑06‑05'),

(7, 'popcorn maker', '2001‑11‑22'),

(8, 'aquarium', '1992‑08‑04'),

(9, 'study desk', '1984‑09‑16'),

(10, 'lava lamp', '1998‑12‑25');

 

Вы можете видеть, которые разделы используются в запросе типа SELECT * FROM trb1;, как показано здесь:

 

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0,p1,p2,p3

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using filesort

 

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

 

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0, p1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using where

 

EXPLAIN PARTITIONS обеспечивают информацию относительно используемых и возможных ключей, точно как со стандартной инструкцией EXPLAIN SELECT:

 

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);

Query OK, 10 rows affected (0.03 sec)

Records: 10 Duplicates: 0 Warnings: 0

 

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0, p1

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: NULL

rows: 7

Extra: Using where

 

Вы должны принять во внимание следующие ограничения на EXPLAIN PARTITIONS:

Вы не можете использовать ключевые слова PARTITIONS и EXTENDED вместе в том же самом запросе EXPLAIN … SELECT. Попытка сделать так производит синтаксическую ошибку.

 

Если EXPLAIN PARTITIONS используется, чтобы исследовать запрос для не разбитой на разделы таблицы, никакая ошибка не будет произведена, но значение столбца partitions всегда NULL.

 

Сокращение раздела

 

Этот раздел обсуждает сокращение раздела (partition pruning), оптимизацию, которая была выполнена для разбитых на разделы таблиц в MySQL 5.1.6.

 

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

 

CREATE TABLE t1 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL,

region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL)

PARTITION BY RANGE(region_code) (

PARTITION p0 VALUES LESS THAN (64),

PARTITION p1 VALUES LESS THAN (128),

PARTITION p2 VALUES LESS THAN (192)

PARTITION p3 VALUES LESS THAN MAXVALUE);

 

Рассмотрите случай, где Вы желаете получить результат запроса типа этого:

 

SELECT fname, lname, postcode, dob FROM t1

WHERE region_code > 125 AND

region_code < 130;

 

Просто видеть, что ни одна из строк, которые должны быть возвращены, не будет в разделе p0 или p3. То есть, мы должны искать данные только в разделах p1 и p2, чтобы найти строки соответствий. Делая так, можно расходовать намного меньше времени и усилий в нахождении строк соответствий, чем при просмотре всех разделов в таблице. Это и известно как сокращение (pruning). Когда оптимизатор может использовать сокращение раздела, выполнение запроса может быть на порядок быстрее, чем тот же самый запрос на не разбитой на разделы таблицы, содержащей те же самые определения столбца и данные.

 

Оптимизатор запроса может выполнять сокращение всякий раз, когда условие WHERE может быть уменьшено до любого одного из следующего:

 

partition_column = constant

 

partition_column IN (constant1, constant2, …, constantN)

 

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

 

Сокращение может также применяться к коротким диапазонам, которые оптимизатор может преобразовывать в эквивалентные списки значений. Например, в предыдущем примере, предложение WHERE может быть преобразовано в WHERE region_code IN (125, 126, 127, 128, 129, 130). Затем оптимизатор может определять, что первые три значения в списке найдены в разделе p1, следующие три значения в разделе p2 и что другие разделы не содержат никаких релевантных значений.

 

Этот тип оптимизации может применяться всякий раз, когда выражение выделения разделов состоит из равенства или диапазона, который может быть уменьшен до набора равенств, или же когда выражение выделения разделов представляет связь уменьшения или увеличение. Сокращение может также быть применено для таблиц, разбитых на разделы на основании столбцов DATE или DATETIME, когда выражение выделения разделов использует функцию YEAR() или TO_DAYS().

Обратите внимание: в будущих версиях MySQL планируется добавлять поддержку сокращения для дополнительных функций, которые действуют на значения DATE или DATETIME, возвращают целое число и увеличиваются или уменьшаются. Например, предположите, что таблица t2, определенная как показано здесь, разбита на разделы на столбце DATE:

 

CREATE TABLE t2 (fname VARCHAR(50) NOT NULL,

lname VARCHAR(50) NOT NULL,

region_code TINYINT UNSIGNED NOT NULL,

dob DATE NOT NULL)

PARTITION BY RANGE(YEAR(dob)) (

PARTITION d0 VALUES LESS THAN (1970),

PARTITION d1 VALUES LESS THAN (1975),

PARTITION d2 VALUES LESS THAN (1980),

PARTITION d3 VALUES LESS THAN (1985),

PARTITION d4 VALUES LESS THAN (1990),

PARTITION d5 VALUES LESS THAN (2000),

PARTITION d6 VALUES LESS THAN (2005),

PARTITION d7 VALUES LESS THAN MAXVALUE);

 

Следующие запросы к t2 могут использовать сокращение:

SELECT * FROM t2 WHERE dob = '1982‑06‑23';

SELECT * FROM t2 WHERE dob BETWEEN '1991‑02‑15' AND '1997‑04‑25';

SELECT * FROM t2 WHERE YEAR(dob) IN (1979, 1980, 1983, 1985, 1986, 1988);

SELECT * FROM t2 WHERE dob >= '1984‑06‑21' AND dob <= '1999‑06‑21'

 

В случае последнего запроса оптимизатор может также действовать следующим образом:

Найти раздел, содержащий нижний конец диапазона..

 

YEAR('1984‑06‑21') выдает значение 1984, которое найдено в разделе d3.

 

Найти раздел, содержащий верхний конец диапазона..

YEAR('1999‑06‑21') оценивается к 1999, которое найдено в разделе d5.

 

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

В этом случае, это означает, что просмотрены только разделы d3, d4 и d5. Остающиеся разделы могут безопасно игнорироваться (и игнорируются).

 

Пока мы смотрели только на примеры, использующие выделение разделов RANGE, но сокращение может применяться также и с другими типами выделения разделов.

 

Рассмотрите таблицу, которая разбита на разделы LIST, где выражение выделения разделов увеличивается или уменьшается, типа таблицы t3, показанной здесь. В этом примере мы принимаем для краткости, что столбец region_code ограничен значениями от 1 до 10.

 

CREATE TABLE t3 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL)

PARTITION BY LIST(region_code) (

PARTITION r0 VALUES IN (1, 3),

PARTITION r1 VALUES IN (2, 5, 8),

PARTITION r2 VALUES IN (4, 9),

PARTITION r3 VALUES IN (6, 7, 10));

 

Для запроса типа

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3

оптимизатор определяет, в которых разделах значения 1, 2 и 3 найдены (r0 и r1) и пропускает остающиеся (r2 и r3).

 

Для таблиц, которые разбиты на разделы HASH или KEY, сокращение раздела также возможно в случаях, в которых предложение WHERE использует простое отношение = против столбца, используемого в выражении выделения разделов. Рассмотрите таблицу, созданную подобно этому:

 

CREATE TABLE t4 (fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL,

region_code TINYINT UNSIGNED NOT NULL,

dob DATE NOT NULL)

PARTITION BY KEY(region_code) PARTITIONS 8;

 

Любой запрос типа этого может быть сокращен:

SELECT * FROM t4 WHERE region_code = 7;

 

Сокращение также может быть использовано для коротких диапазонов, потому что оптимизатор может направлять такие условия в отношениях IN. Например, при использовании той же самой таблицы t4 как определено ранее, запросы типа этих могут быть сокращены:

 

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

 

В обеих случаях, предложения WHERE преобразованы оптимизатором в WHERE region_code IN (3, 4, 5).

Важно: эта оптимизация используется только, если размер диапазона меньший, чем число разделов.

Рассмотрите этот запрос:

SELECT * FROM t4 WHERE region_code BETWEEN 4 AND 8;

 

Диапазон в предложении WHERE покрывает 5 значений (4, 5, 6, 7, 8), но t4 имеет только 4 раздела. Это означает, что предыдущий запрос никак не может быть сокращен.

 

Сокращение может использоваться только на целочисленных столбцах таблиц, разбитых на разделы HASH или KEY. Например, этот запрос на таблице t4 не может использовать сокращение, потому что dob столбец типа DATE:

 

SELECT * FROM t4 WHERE dob >=‑ '2001‑04‑14' AND dob <= '2005‑10‑15';

 

Однако, если таблица сохраняет значения года в столбце типа INT, то запрос WHERE year_col >= 2001 AND year_col <= 2005 может быть сокращен.