MySQL раздел обрезка

Недавно мы узнали дорогой урок об обрезке разделов MySQL. Там лучше поделиться этим здесь, чтобы другие не повторили нашу ошибку.

Фон

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

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

Разделение по хешу

Было бы хорошо, если бы мы делали то, что изначально намеревались сделать. Однако мы изменили тип раздела для удобства, и это сделало новую таблицу более медленной.

В исходной таблице раздел основан на столбце отметки времени, который представляет время в виде количества часов с начала эпохи. Например, первая секунда 2017 года в GMT
1483228800 секунд с эпохи. Чтобы получить количество часов, мы разделим число на 3600, чтобы получить 1483228800 div 3600) = 412008.

Из-за раздела по типу диапазона нам нужен сценарий обслуживания, который создает ежемесячный раздел на следующий год. Этот способ разделения не очень идеален, потому что размер раздела велик и даже не равномерен. Следовательно, мы конвертировали ежемесячные разделы в недельные, но слишком ленивы, чтобы определить каждый диапазон, и переключились с раздела по диапазону на раздел по хешу.

Это короткая версия того, как будет выглядеть определение хеша, если мы сделаем разбиение по диапазону

И вот как будет выглядеть определение раздела, если мы сделаем раздел по хешу

Разделение по типу хэша не только сокращает синтаксис. MySQL попытается разделить записи равномерно, применяя функцию по модулю для выбора раздела. Однако, чтобы сделать длительность одного раздела в одну неделю, мы делим число hour_epoch на 168, чтобы эффективно получить week_epoch.

С новой схемой таблицы мы были довольны меньшими разделами, более коротким описанием и большим количеством индексов.

Проблема производительности

Из-за огромного объема данных мы не смогли полностью перенести данные в новую схему для проверки производительности. Мы только провели предварительный тест производительности с данными за 2 недели и не обнаружили никаких проблем с производительностью. Однако в финальном тестировании мы были удивлены, увидев смешанный результат. Большинство запросов выполняются быстрее, чем ожидалось, но некоторые — медленнее.

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

« Для таблиц, которые разделены с помощью HASH или [LINEAR] KEY, сокращение раздела также возможно в случаях, когда в предложении WHERE используется отношение simple = к столбцу, используемому в выражении разделения »

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

Решение

Узнав об этой проблеме, мы попытались найти способ исправить проблему с производительностью. Есть 2 предложенных решения

• Обманите оптимизатор запросов, чтобы разделить большой диапазон на несколько небольших диапазонов, каждый из которых соответствует одному разделу. Таким образом, оптимизатор запросов будет работать с каждым отдельным небольшим диапазоном.
• Снова перестройте схему, указав правильный тип раздела.

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

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

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

Так выглядит наш обмен разделами

Несмотря на то, что это не так быстро, как вы можете догадаться, поскольку MySQL будет выполнять проверку строки за строкой, чтобы обеспечить возможность записи каждой записи временной таблицы в разделе окончательной таблицы. Если мы используем MySQL 5.7, эту проверку можно отключить, добавив
БЕЗ ВАЛИДАЦИИ »до конца второй команды.

Поскольку мы используем Aurora, которая поддерживает только MySQl 5.6, нам потребовалось 2 дня, чтобы полностью обновить тип раздела. Однако это был бы один месяц, если бы мы не использовали обмен разделами.

К счастью, на этот раз нам удалось оправиться от ошибки. Мы надеемся, что вы извлечете уроки из нашей ошибки и не забудьте внимательно прочитать документ, прежде чем использовать какой-либо причудливый метод.