Начиная с версии 5.1 MySQL поддерживает горизонтальное секционирование таблиц.
Что это? Секционирование — это разделение больших таблиц на логические части по выбранным критериям.
.
На нижнем уровне для таблиц 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';
И вот что происходит: mysql> explain partitions select * from orders_range3 where order_date='2008-08-01';
+----+-------------+---------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | orders_range3 | p_2008 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+---------------+------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
Мы видим, что при выполнении этого запроса работа будет работать исключительно с «подтаблицей» p_2008.
Причём ускорение достигается даже при выполнении запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первоначальная «обработка» таблиц меньшего размера, затем данные объединяются и выполняются окончательные вычисления.
Так что как раз «первые» этапы в этом случае пройдут гораздо быстрее.
Какие еще преимущества есть? Главным преимуществом я бы назвал то, что раздел с «оперативными» данными (т.е.
самыми последними, которые чаще всего выбираются) имеет минимальный размер, и как следствие, может постоянно находиться в оперативной памяти.
Если у вас есть таблица журналов, в которую постоянно ведется запись, а жесткие диски не успевают за ней, а ваша религия не позволяет вам настроить рейд, вы можете настроить разделение с помощью хеш-функции и указать один раздел для каждый доступный вам жесткий диск.
В этом случае новые данные будут записываться на все жесткие диски равномерно.
Какие методы MySQL предоставляет для «разделения» данных? 1. ДИАПАЗОН По диапазону значений РАЗДЕЛЕНИЕ ПО ДИАПАЗОНУ (store_id) ( РАЗДЕЛ p0 ЗНАЧЕНИЯ МЕНЬШЕ (10), РАЗДЕЛ p1 ЗНАЧЕНИЯ МЕНЬШЕ (20), РАЗДЕЛ p3 ЗНАЧЕНИЯ МЕНЬШЕ (30) ); 2. СПИСОК По точному списку значений РАЗДЕЛЕНИЕ ПО СПИСКУ(store_id) ( РАЗДЕЛ pNorth ЗНАЧЕНИЯ В (3,5,6,9,17), РАЗДЕЛ peast ЗНАЧЕНИЯ В (1,2,10,11,19,20) ) Почему ты спрашиваешь? Разбивать на разделы необходимо либо из соображений оптимизации выборки (что встречается чаще), либо из соображений оптимизации записи (реже).
Соответственно, идеальный вариант — это когда вы разбиваете таблицу на максимально возможное количество секций, чтобы 90% всех выборок приходились на одну партицию.
А если у вас сложная логика выборки (например, объекты, расположенные в северных кварталах города, идентификаторы которых противоречивы), то иногда имеет смысл перечислять их принудительно.
3.ХЕШ РАЗДЕЛЕНИЕ ПО ХЕШУ (store_id) РАЗДЕЛЫ 4; Вы никак не контролируете разбиение, вы просто указываете, по какому полю строить хэш и сколько «подтаблиц» создавать.
За что? Выборка по указанному полю происходит намного быстрее.
В ряде случаев это позволяет добиться «равномерного рассеяния» и ускорить запись данных.
4. КЛЮЧ Почти то же самое, что HASH, но более логично — по ключу.
РАЗДЕЛЕНИЕ ПО КЛЮЧУ(s1) РАЗДЕЛЫ 10; Те.
выборка на основе заданного ключевого поля происходит максимально эффективно.
Но здесь также необходимо определиться со способом разбиения.
Хорошо подходит для счетчика посетителей, когда его логин является единственным идентификатором, по которому необходимо выбирать все остальные данные.
Чего не хватает? Отсутствие вертикального разделения.
Это когда разные столбцы (поля) находятся в разных «подтаблицах».
Поскольку иногда это бывает полезно, то можно добиться этого самостоятельно, пусть и не так прозрачно: разбить таблицу на две, связав их первичным ключом.
Если очень хочется красоты, можно дополнительно создать на их основе VIEW, например, чтобы не переписывать старые части кода.
Зачем это делать? Например, в таблице, где в основном присутствуют числа и даты, есть одно поле VARCHAR (255) для комментариев, которое используется на порядок реже остальных полей.
Если мы переместим ее в другую таблицу, то получим фиксированный размер строки (mySQL сможет точно рассчитать положение нужной строки по индексу в файле данных).
Таблица станет более устойчивой к сбоям в случае нештатных ситуаций (опять же за счет фиксированного размера строк).
Ну а размер самой таблицы значительно уменьшится.
И в завершение статьи приведу пример более «реального» секционирования таблиц — помесячного.
Поскольку LIST/RANGE принимает только целочисленные значения, вам придется немного посложничать: РАЗДЕЛЕНИЕ ПО ДИАПАЗОНУ( TO_DAYS(order_date) ) ( РАЗДЕЛ y2009m1 ЗНАЧЕНИЯ МЕНЬШЕ (TO_DAYS('2009-02-01')), РАЗДЕЛ y2009m2 ЗНАЧЕНИЯ МЕНЬШЕ (TO_DAYS('2009-03-01')), РАЗДЕЛ y2009m3 ЗНАЧЕНИЯ МЕНЬШЕ (TO_DAYS('2009-04-01')) ); PS: В mysql всегда приходится немного повозиться, так что вам никогда не будет скучно, а мы в свою очередь никогда не останемся без работы :) Теги: #MySQL #разметка #разработка веб-сайтов
-
Дорожное Строительство
19 Oct, 24 -
Стирание Не Может Быть Восстановлено
19 Oct, 24 -
Путешествовать Онлайн Уже Возможно!
19 Oct, 24
