Партиционирование таблиц в mySQL

    Начиная с версии 5.1 mySQL поддерживает горизонтальное партицирование таблиц. Что это такое? Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.. На нижнем уровне для 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. RANGE

    По диапазону значений

    PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p3 VALUES LESS THAN (30)
    );

    2. LIST

    По точному списку значений

    PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20)
    )

    Зачем, спросите вы? Разбивать на партиции необходимо либо исходя из соображений оптимизации выборки (что чаще) либо исходя из соображений оптимизации записи (реже). Соответственно, идеальный вариант — это когда вы разбиваете таблицу на максимально возможное количество партиций так, что бы 90% всех выборок происходило в пределах одной партиции. И если у вас сложная логика выборки (например, объекты расположенные в северных кварталах города, ID которых идут в разнобой) то иногда есть смысл перечислять их принудительно.

    3. HASH

    PARTITION BY HASH(store_id)
    PARTITIONS 4;

    Вы никак не управляете партицированием, просто указываете, по какому полю строить хеш и сколько «подтаблиц» создавать. Зачем? Гораздо быстрее происходит выборка по указанному полю. В некоторых случаях позволяет достигнуть «равномерного разброса» и ускорения записи данных.

    4. KEY

    Почти то же самое что и HASH, но более логично — по ключу.

    PARTITION BY KEY(s1)
    PARTITIONS 10;

    Т.е. выборка по указанному ключевому полю происходит максимально эффективно.

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

    Чего нет?

    Нет вертикального партицирования. Это когда разные столбцы (поля) находятся в разных «подтаблицах». Поскольку иногда это бывает полезно, вы можете достичь этого самостоятельно, пусть даже не так прозрачно: разделить таблицу на две, связав их по первичному ключу. Если вам совсем хочется красоты — можете дополнительно создать по ним VIEW, например для того что бы не переписывать старые части кода.

    Зачем это делать? Например, в таблице, где у вас в основном числа и даты, есть одно поле VARCHAR (255) для комментариев, которое используется на порядок реже чем остальные поля. В случае если его вынести в другую таблицу, то мы получим фиксированный размер строки (mySQL сможет совершенно точно вычислять позицию нужной строки по индексу в файле данных). Таблица станет более устойчивой к сбоям в случае внештатных ситуаций (опять же, из-за фиксированного размера строки). Ну и существенно уменьшится сам размер таблицы.

    И заканчивая статью приведу пример более «реального» партицирования таблиц — помесячно. Так как LIST/RANGE принимают только целочисленные значения, то надо немного исхитрится:

    PARTITION BY RANGE( TO_DAYS(order_date) ) (
    PARTITION y2009m1 VALUES LESS THAN( TO_DAYS('2009-02-01') ),
    PARTITION y2009m2 VALUES LESS THAN( TO_DAYS('2009-03-01') ),
    PARTITION y2009m3 VALUES LESS THAN( TO_DAYS('2009-04-01') )
    );

    PS: В mysql всегда приходится немного «исхитриться», так что скучно с ней не будет никогда, а мы в свою очередь никогда не останемся без работы :)
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 84

      0
      спасибо за статью. мне в новом проекте как раз пригодится Ваш материал
        0
        Офигенная весчь!

        P.S. Надо больше читать маны…
          –1
          Молодцы. До этого приходилось к подобным трюкам прибегать разбивая данные на таблицы ручками :) Хотя при использовании других движков типа SQLite все еще руками приходится разбивать.
          0
          большое спасибо, теперь оптимизировать будет легче.
            +1
            я бы на вашем месте перенес бы в блог Mysql
            +4
            Пора уже отходить от mysql как просто от тупо списка таблиц с тупой выборкой данных, а относиться к ней более серьезно. Триггеры, процедуры, целостность, ограничения. Теперь вот партицирование.
            Айс!
              0
              множество из того, что вы перечислили недоступно на хостинге в силу безопасности, а если у вас на один проект — один(свой) сервер, то конечно, давно пора к этому относиться серьезно.
                0
                Партицирование есть только на MyISAM, а слова «целостность», «ограничения» а также ещё много других СУБДшных слов отношения к MyISAM не имеют.
                  +4
                  А вот не согласен. Партицирование отлично работает с innodb.
                    0
                    MyISAM и InnoBD — всё-таки разные вещи.
                    +2
                    не правда, партицирование есть и в иннодб еще как минимум

                    из мануала

                    This example shows how to create a table that is partitioned by hash into 6 partitions and which uses the InnoDB storage engine:

                    CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
                    ENGINE=INNODB
                    PARTITION BY HASH( MONTH(tr_date) )
                    PARTITIONS 6;
                      0
                      Прошу прощения, действительно ошибся 8(
                      0
                      Пока это всё только в начале пути и не стоить плясать раньше времени. С этими новшествами постоянно появляются проблемы, так что говорить о полноценной и удобной поддержке транзакций, процедур, целостности, etc ещё рано. Им ещё обкатываться и обкатываться.
                        +1
                        Этим «новшествам» в MySQL уже года так 2.5 (точно помню что пробовал партишининг еще на старой работе, с какой ушел 2.5 года назад)
                        Также как и триггеры с хранимыми процедурами.
                        Так что новшества далеко не новы, и уже успешно обкатаны.
                        Потому статья в контексте MySQL слегка «баянистая», зато хорошо расписаны pros & cons самого подхода.
                          –1
                          Обкатаны то обкатаны, только до нормального удобства им ещё далеко. Сходите в гугл например по запросу mysql transaction problem, вместо transaction можно подставить что душе угодно из списка и поймёте что радоваться пока особо нечему. Вопросы сыплются пачками, как сделать это, как сделать то, особенно от людей не понаслышке знакомых например с mssql.
                            0
                            Обилие вопросов от людей «знакомых с MSSQL» в топиках о MySQL совершенно ни о чем не говорит.
                            Разве что о растущей популярности последнего.

                            И если для MSSQL-щиков и Oracle-истов что-то работает не так как они привыкли, это отнюдь не говорит о проблемах в MySQL. Это говорит о том, что в MySQL это ввиду разных причин это попросту иначе.
                      0
                      Наконец-то это произошло в MySQL!!! Теперь можно будет на нем тянуть еще большие объемы:)
                        0
                        Что значит «наконец-то»? Релиз MySQL 5.1 вышел почти год назад.
                          +1
                          Просто даже не подозревал, что ввели такую вещь как партиционирование, думал опять по мелочам что-то добавили.
                            +3
                            Именно по этому я и написал этот пост ;)
                        0
                        да, мануалы полезнейшая вещь :)

                        спасибо за статью, а то я в одном проекте уже начал проектировать ручные механизмы такого партиционирования. Теперь можно потратить время на доработку полезного функционала.
                        • UFO just landed and posted this here
                            0
                            Большое спасибо, думаю пригодится :)
                              0
                              А я раньше извращался с MERGE…
                                0
                                Да, это конечно очень поможет при больших объемах БД. Спасибо.
                                  0
                                  По хешам… да на отдельные диски… да мои 5 миллионов строк…
                                  Пора ORM под 5.1 портировать, спасибо!
                                    –1
                                    эх, везет же, всего 5 миллионов строк…
                                    0
                                    все это хорошо.
                                    а как быть с автоматическим разбиением? или каждый новый месяц создавать новую таблицу? (
                                      +1
                                      ну мне не жалко было в своем проекте прописать еще 20 строчек на 2 года вперед. А в целом есть операции переразбития партиций или изменения логики.

                                      насколько я знаю, некоторые ребята создают партиции каждые сутки по крону
                                        0
                                        ну это же хак ), как я понял по мануалам и форуму мускуль, им уже описали идею авторазбития, может скоро это и повится )
                                          0
                                          при перепартицировании каждые сутки по крону — строки переносятся из одного раздела в другой? или просто создаётся новый раздел для новых, с этого момента, данных?
                                        +3
                                        Вау! Сча как нарежу...)
                                          0
                                          Большое спасибо, не знал :)
                                            0
                                            А есть какие-нибудь конкретные данные? Например, 5 млн записей без партиционирования и с этим страшным словом :) Интересно какой прирост производительности.
                                            0
                                            Спасибо. Долго руки не доходят узнать все особенности 5.1, до сих пор все вручную делаю.
                                              0
                                              мат-часть рулит!
                                                0
                                                От оно как оказывается. Давно хотел что то подобное с логами сделать. Да все как то башем и перлом в дамп, а тут такая красота.
                                                  0
                                                  Ещё более интерснее была бы возможность распределения таблиц по разным серверам. Но пока это видимо не планируется.
                                                    0
                                                    Поддерживаю. Партицирование конечно штука хорошая, но разнос по разным нодам было бы более заманчиво. Ну видимо предполагается, что это будет делаться средствами proxy.
                                                      0
                                                      Разнос по разным нодам, кажется, называется «шардинг». И шардинг имеет гораздо более широкую применимость в нагруженных проектах, чем партицирование. Мне кажется, что партицирование — оно только для логов и полезно…
                                                        0
                                                        Партицирование полезно для любой большой таблицы.
                                                    +3
                                                    Partitioning в контексте баз данных принято на русский переводить, как секционирование.
                                                    Слово партиционирование жутко неестественное, сильно режет слух.
                                                      0
                                                      Где это принято? Общей нормы языка по данному вопросу нет (браузер/броузер) поэтому кто-то говорит секционирования, но лично я чаще всегда слышу партицирование. Хотя в яндексе конечно больше секционирования.
                                                        0
                                                        Слова «партиция» и «партицирование» можно считать сленгом. Оно удобнее за счет того, что в названии сразу слышится действие, команда, которой можно сделать это самое действие. А в книжках, действительно, обычно используют слова «секция» и «секционирование».
                                                        0
                                                        По моему опыту если используются partitions то запросы обязательно надо прогонять через EXPLAIN PARTITIONS… Иначе легко вместо секции получить full-scan.
                                                          0
                                                          1. Можно при помощи ALTER сделать партицирование?
                                                          2. Как сделать порции по квартально, так что бы при наступлении нового квартала создавалась партиция?
                                                            +1
                                                            1. сложный вопрос

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

                                                            2. например так

                                                            PARTITION BY LIST(YEAR(order_date) * 100 + QUARTER(order_date)) (
                                                            PARTITION y2005q3 VALUES IN(200503),
                                                            PARTITION y2005q4 VALUES IN(200504),
                                                            PARTITION y2006q1 VALUES IN(200601),


                                                            или так

                                                            PARTITION BY RANGE( TO_DAYS(order_date) ) (
                                                            PARTITION y2009q1 VALUES LESS THAN( TO_DAYS('2009-03-01') ),
                                                            PARTITION y2009q2 VALUES LESS THAN( TO_DAYS('2009-06-01') ),
                                                            PARTITION y2009q3 VALUES LESS THAN( TO_DAYS('2009-09-01') )
                                                            );
                                                              0
                                                              А если построить хеш по ГОД+НОМЕР_КВАРТАЛА, и указать PARTITIONS 20? (на 5 лет должно хватить)
                                                            0
                                                            а блога sql нет случаем?
                                                            просто удобно было бы такие статьи держать в одном месте, чтоб не потерялись
                                                              +3
                                                              Мы видим, что при выполнении этого запроса работа будет идти исключительно с «подтаблицей» p_2008.
                                                              — и это странно, потому что в запросе используется 2009-й год и партиция должна быть p_2009.
                                                                +1
                                                                Опечатка в запросе, исправил
                                                                +1
                                                                если бы это добавили в интерфейс phpmyadmin — думаю, знали бы уже все
                                                                  0
                                                                  Было в анонсах 5.1 на хабре
                                                                  Ой как нравится мне когда мускульщики радуются хранимым процедурам и прочему =) Секционирование это да это молодцы =) но попрежнему плохо юзабельная СУБД =( и бизнес-логику унутрь БД не упихать =(
                                                                    +1
                                                                    ну такое :)

                                                                    Такое впечатление что заплатка на заплатке

                                                                    есть небольшие фразы мелким текстом типа «Beginning with MySQL 5.1.12, the stored functions and procedures are not permitted in partitioning „

                                                                    что сводит на нет весь бонус от их использования.

                                                                    это как в триггере который не может менять таблицу которая его породила :)
                                                                    0
                                                                    а есть ли ограничение на кол-во разбиений? например, я хочу разбить таблицу по пользователям
                                                                      0
                                                                      Не совсем понятно что вы имели ввиду под своим вопросом.

                                                                      если максимально допустимое количество партиций — то 1024

                                                                      Maximum number of partitions. The maximum number of partitions possible for a given table is 1024. This includes subpartitions.
                                                                        0
                                                                        да, это и имел в виду, спасибо
                                                                      +1
                                                                      Спасибо за действительно полезную публикацию — жму вашу мужественную руку.
                                                                      Появился вопрос — можно ли пропивать партицирование, примерно таким образом:

                                                                      PARTITION BY RANGE( TO_DAYS(%Current_date%)-TO_DAYS(order_date) ) (
                                                                      PARTITION new VALUES LESS THAN( %10 дней% ),
                                                                      PARTITION older VALUES LESS THAN( %20 дней%),
                                                                      PARTITION oldest VALUES LESS THAN( %30 дней%)
                                                                      )

                                                                      Примерно такой подход возможен в реализации?
                                                                      Если попытаться, к примеру, число дней прошедших с публикации проставлять через триггеры, а в таблице просто проставить статичные значения, будут ли данные по завершению работы триггера перемещаться из одной подтаблицы в другую?
                                                                        +2
                                                                        Такой подход я боюсь невозможен.

                                                                        Но как вариант можно каждые 10 дней выполнять

                                                                          +1
                                                                          ALTER TABLE members REORGANIZE PARTITION p0 INTO…
                                                                            +1
                                                                            Блин, залипает клавиатура :(

                                                                            Вобщем, можно каждые 10 дней реорганизовать партиции под новые фиксированные даты, тогда MySQL самостоятельно пересортирует данные указанным вами образом

                                                                            Хотя имхо это не совсем корректный подход к использованию данной фичи.
                                                                              0
                                                                              Чтож, будем делать то что можем и довольствоваться уже немалым — раньше и этой возможности небыло, а как появилась — сразу же изыски гурмана проступать начинают.
                                                                        0
                                                                        А как обстоит дело с добавлением новый секций в существующую таблицу?
                                                                        Это можно делать через alter?
                                                                          +1
                                                                          Да

                                                                          ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12));
                                                                          +1
                                                                          Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления.

                                                                          По-моему далеко не всегда это будет быстрее. Насколько я понимаю, индексы строятся тоже для отдельной партиции таблицы. Получается, что при объединении результатов выборок из нескольких партиций дальше mySQL будет искать уже по временной таблице без индексов.
                                                                          С другой стороны, это ускорит выборку, если никаких дальнейших действий с выбранными из партиций данных производить уже не надо.
                                                                            0
                                                                            если у вас идет выборка по всей базе — то она обязательно идет с агрегированием

                                                                            ускорение в данном случае достигается за счет того что каждую партицию можно по очереди загрузить в оперативную память и все сделать без временных таблиц

                                                                            выигрыш именно в этом
                                                                            0
                                                                            Я работаю с Oracle, поэтому немного не в курсе, но любопытно. Поддерживает ли mySQL субпартицирование — разбиение партиции на более мелкие подпартиции? И возможно ли создание локальных индексов (по одной из партиций)?
                                                                              0
                                                                              1. да, субпартицирование поддерживается (правда лично я никогда не понимал зачем :)
                                                                              2. индексы ВСЕГДА строятся по каждой из партиций отдельно.
                                                                                0
                                                                                Спасибо, все это очень вкусненько :-)
                                                                                Субпартиции нужны для того же, что и партиции — разбивка большой таблицы на более мелкие части для удобства хранения и поиска в ней. Например, у нас почти все таблицы разбиваются на партиции по дате (месяц/день), при этом каждая четвертая еще и субпартицируется по региону.
                                                                              0
                                                                              Вопрос в догонку: есть ли какие-то готовые средства для определения какой диапозон рядов является наиболее часто запрашиваемым с целью равномерного распределения нагрузки между серверами?
                                                                                0
                                                                                задача не нормально распределить данные, а определить наиболее частые и тяжелые запросы на выборку данных и оптимизировать их
                                                                                  0
                                                                                  Вообще говоря, зависит от задач. :)
                                                                                  А если мне необходимо делать выборку из всей таблицы или бОльшей её части?
                                                                                    0
                                                                                    но вот я приводил пример — данные о пользователе, в случае если их несколько десятков миллионов.

                                                                                    тогда просто hash от primary ID :)
                                                                                0
                                                                                спасибо за информацию, чую пора переходить на 5.1
                                                                                • UFO just landed and posted this here
                                                                                    0
                                                                                    возможность использовать несколько индексов есть и так, если построить по ним составной индекс

                                                                                    вот да, для случая с fulltext составным индексом только через partitioning
                                                                                    0
                                                                                      0
                                                                                      Кто может знает насчет partitioning при использовании FOREIGN KEY?

                                                                                      При попытке разбить таблицу мне выдает:

                                                                                      1506. Foreight key is not yet supported in conjunction with partitioning

                                                                                      … когда же появится?
                                                                                        0
                                                                                        Есть значительное ограничение, что если в таблице есть уникальные ключи, в том числе и PRIMARY, то каждый из этих ключей должен включать в себя все столбцы, по которым производится разбиение.

                                                                                        Например, у нас есть форум и есть таблица сообщений, где есть поля post_id и topic_id, где post_id — уникальный код сообщения, а topic_id — ссылка на тему форума, в котором содержится это сообщение, то разбить таблицу по полю topic_id не получится. Потому что она не входит в уникальный ключ post_id. А если сделать ключ (post_id, topic_id), то выйдет так, что может быть один и тот же код сообщения в разных темах форума. И поле post_id утратит уникальность.

                                                                                        Ещё одно важное ограничение — нельзя использовать FULLTEXT индекс.

                                                                                        P. S. Написано по горячим следам после того, как я хотел ускорить работу форума с 1,6 млн. сообщений.
                                                                                          0
                                                                                          могу ошибаться, но там могут быть проблемы из-за теперешней ситуации с мускулом. то есть на сколько я понял из информации на оф. сайте там есть что-то связанное с расширенными возможностями этой субд при покупке что-ли поддержки или какой-то лицензии. так же мне так показалось, что есть довольно сильные различия в различных сборках mysql, потому что дома на одной сборке работали партишены, на сервере, буквально на предыдущей не особо работали.
                                                                                          так же стоит хорошенько взвесить уровень разбиения и какой именно тип разбиения выбрать, ибо может сильно сказаться в обратную сторону производительности (как-то провел неделю, исследуя различные способы разбиения на части таблицу в 150млн записей).
                                                                                            0
                                                                                            Не хватает ещё слов «при партицировании insert/update проиходит быстрее» так как перестраиваются малые индексы в подтаблицах а не один большой индекс на всю таблицу.

                                                                                            Only users with full accounts can post comments. Log in, please.