MySQL: руководство профессионала

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 может быть сокращен.

3.5. Ограничения выделения разделов

Этот раздел обсуждает текущие ограничения поддержки выделения разделов MySQL:

Начиная с MySQL 5.1.12, следующие конструкции не разрешаются в выражениях выделения разделов:

Вложенные обращения к функциям (то есть, конструкции типа func1(func2(col_name))).

Сохраненные процедуры, функции, UDF или plugins.

Объявленные переменные или переменные пользователя.

Начиная с MySQL 5.1.12, следующие функции MySQL специально не позволяются в выражениях выделения разделов:

GREATEST

ISNULL

LEAST

CASE

IFNULL

NULLIF

BIT_LENGTH

CHAR_LENGTH

CHARACTER_LENGTH

FIND_IN_SET

INSTR

LENGTH

LOCATE

OCTET_LENGTH

POSITION

STRCMP

CRC32

ROUND

SIGN

DATEDIFF

PERIOD_ADD

PERIOD_DIFF

TIMESTAMPDIFF

UNIX_TIMESTAMP

WEEK

CAST

CONVERT

BIT_COUNT

INET_ATON

Использование арифметических операторов +, -, * и / разрешается в выражениях выделения разделов. Однако, результат должен быть целочисленным значением или NULL (за исключением [LINEAR] KEY).

Начиная с MySQL 5.1.12, разрядные операторы |, , ^, <<, >> и ~ не разрешаются в выражениях выделения разделов.

Начиная с MySQL 5.1.12, только следующие функции MySQL поддерживаются в выражениях выделения разделов:

ABS

ASCII

CEILING

DAY

DAYOFMONTH

DAYOFWEEK

DAYOFYEAR

EXTRACT

FLOOR

HOUR

MICROSECOND

MINUTE

MOD

MONTH

ORD

QUARTER

SECOND

TIME_TO_SEC

TO_DAYS

WEEKDAY

WEEKOFYEAR

YEAR

YEARWEEK

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