• Главная
• Справочник
• Информатика
• Основы
• Основы баз данных MySQL

Оптимизация базы данных MySQL

В наше время создать приложение не проблема - интернет пестрит предложениями от фрилансеров. Продвижение тоже не проблема. С платной рекламой от Facebook мы можем охватить сотни тысяч людей, достаточно просто предложить что-то более разумное, чем у конкурентов , чтобы оно хорошо продавалось.

Самой большой проблемой для разработчика такого приложения является его обслуживание и масштабирование . Одной из основ обслуживания приложений является безопасность, а другой — минимизация затрат и оптимизация . Конечно, есть еще вопросы целостности и доступности, но об этом позже… Когда на хостинге (точнее, в базе данных MySQL) заканчивается место вы можете масштабировать хостинг по вертикали и горизонтали , то есть просто купить сервер с большим количеством ресурсов или докупить за счет создания репликации базы данных MySQL , функционального партиционирования и т.н. шардинг данных баз данных.

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

Основные шаги

1.1 Оптимизация структуры таблиц MySQL

Первое, о чем нужно позаботиться при оптимизации баз данных, — это количество таблиц, количество столбцов и размер данных, объявленных в полях. Здесь стоит действовать по принципу чем меньше - тем лучше. Это клише, но большинство людей от лени делают так, что мы используем INT(11) для хранения цен, прибавок или ключей для таблиц, которые, как мы знаем, не будут расти бесконечно. Используйте TINYINT или SMALLINT для небольших чисел.  Это то, что нужно для того, чтобы воспользоваться . Если у нас есть база данных магазина или маленькое бизнес-приложение небольшой компании, количество записей обычно не превышает десятков тысяч - на самом деле разница будет не так заметна. Но если таких полей много и записи начинают исчисляться миллионами - мы можем сэкономить очень многое, как место на диске, которое нужно базе данных (что менее важно с точки зрения оптимизации), но прежде всего буфер и ЦП сервера.

Правильный выбор типов данных для столбцов

Знаете ли вы, что если вы знаете длину строки, которую будете хранить в поле, то с точки зрения оптимизации лучше использовать CHAR, чем VARCHAR? Выигрыш по производительности удваивается! Кстати,  выделение статической памяти  всегда было, есть и будет быстрее.

Знаете ли вы, что TIMESTAMP содержит в два раза меньше памяти, чем DATETIME? Подробнее читайте в статье:  Типы данных в MySQL  .

1.2 Порядок столбцов в таблицах

Порядок столбцов в каждой таблице  оказывает огромное влияние  на производительность базы данных  .   Столбцы следует добавлять в следующем порядке:

1. Столбец первичного ключа
2. Столбец внешнего ключа
3. Часто запрашиваемые столбцы (чаще всего после слова ГДЕ)
4. Часто обновляемые столбцы (чаще всего после слова SET)
5. Более часто используемые столбцы NULL
6. Менее часто используемые столбцы NULL

2. Мы измеряем время запросов MySQL

Даже несмотря на то, что квалифицированный программист по опыту знает, какие запросы стоят базе больших усилий, а какие вряд ли станут для нее проблемой, время от времени стоит бросить скромный взгляд и проследить время выполнения запроса. Лично я при создании софта тестирую запросы в phpMyAdmin — знаю, не удобное решение. Обычно для этого используется профайлер. Я ссылаюсь на документацию, где можно проследить самые популярные варианты использования:

3. Оптимизация кода приложения

MySQL поддерживает многопоточность процессора,  а сам механизм базы данных специализирован для обработки данных, содержащихся в базах данных. Так что если, например, вы хотите усреднить или суммировать записи, обычно лучше использовать встроенные функции в MySQL, чем создавать свои «собственные циклы» в PHP. База данных — это не просто «дурацкий пакет данных». Именно для этого и предназначены такие функции, как AVG() и SUM(), которые ускоряют работу приложений и облегчают работу разработчиков.

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

4. Оптимизация запросов MySQL

Самая распространенная проблема в запросах — это злосчастный  SELECT *  . Чем больше данных мы извлекаем, тем хуже. База данных возвращает большие пакеты данных, что, как известно, отрицательно сказывается на производительности. Жестко определите, из каких столбцов вам нужны данные. Любой столбец, возвращенный в запросе без необходимости, является пустой тратой.

Функция EXPLAIN тоже полезна. Благодаря ему можно проверить, как движок базы данных подходит к конкретному запросу, использует ли он индексы и если да, то какие.

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

5. Избыточность данных!

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

Я считаю решения такого типа квинтэссенцией компьютерных наук, инженерии баз данных и архитектуры. Так же, как и в задачах математической оптимизации, иногда используются методы Монте-Карло. Конечно, этот метод упоминается только в аспирантуре, а жаль. Потому что, если вы можете получить чуть менее точный результат с гораздо меньшей работой, это то, что (в большинстве случаев) следует делать.

Простой пример из жизни: окончательное значение счета может быть сохранено в поле invoice.voice_value, хотя его можно получить путем суммирования invoice_items.item_value. При выполнении запроса, например, о годовом доходе компании на основе выставленных счетов-фактур, достаточно добавить значение из счетов-фактур, а не сумму сумм по отдельным статьям.

Благодаря этой процедуре, заключающейся (может показаться) в создании ненужного избыточного поля,  запросы с таким типом отчетов могут выполняться быстрее во столько раз, сколько среднее количество позиций в накладной!  Если обычно в счетах-фактурах есть одна позиция, то фокус усугубит ситуацию, но если в счетах-фактурах 10 позиций, мы получим годовой отчет примерно в 10 раз быстрее. Именно поэтому невозможно создать универсальное руководство по оптимизации баз данных, ведь все зависит от конкретного случая.

6. Оптимизация сервера MySQL

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

Второе: чем выше версия MySQL, тем лучше.

Как известно, изменение размера buffer_pool_size, key_buffer_size, query_cache_limit, query_cache_size может благоприятно сказаться на Cache-Hit-Ratio, т.е. на эффективности внутренней системы Cache. Не следуйте руководствам, найденным в Интернете. Просто внесите небольшие изменения и проверьте запросом:

SELECT FORMAT(((QCACHE_HITS / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100),2) AS query_cache_hit_rate_percentage
-- , QCACHE_FREE_BLOCKS, QCACHE_FREE_MEMORY, QCACHE_HITS, QCACHE_INSERTS, QCACHE_LOWMEM_PRUNES, QCACHE_NOT_CACHED, QCACHE_QUERIES_IN_CACHE, QCACHE_TOTAL_BLOCKS
FROM (
SELECT
MAX(IF(VARIABLE_NAME = 'QCACHE_FREE_BLOCKS', VARIABLE_VALUE, NULL)) QCACHE_FREE_BLOCKS,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_FREE_MEMORY', VARIABLE_VALUE, NULL)) QCACHE_FREE_MEMORY,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_HITS', VARIABLE_VALUE, NULL)) QCACHE_HITS,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_INSERTS', VARIABLE_VALUE, NULL)) QCACHE_INSERTS,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_LOWMEM_PRUNES', VARIABLE_VALUE, NULL)) QCACHE_LOWMEM_PRUNES,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_NOT_CACHED', VARIABLE_VALUE, NULL)) QCACHE_NOT_CACHED,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_QUERIES_IN_CACHE', VARIABLE_VALUE, NULL)) QCACHE_QUERIES_IN_CACHE,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_TOTAL_BLOCKS', VARIABLE_VALUE, NULL)) QCACHE_TOTAL_BLOCKS
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE "QCACHE%"
) AS stats;

… Увеличился ли коэффициент попаданий в кэш, потому что в этом все дело.

7. Индексация

Индексы — это частный случай избыточных данных, это пары ключ-местоположение, предназначенные для возврата результатов без поиска по всей таблице (что-то вроде оглавления в книге). Лучшее правило индексации:  индексируем как можно меньше полей  , а если приходится индексировать, то  те поля, которые чаще всего идут после слова WHERE  .

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

Если вы используете поисковую систему по ключевым словам в приложении (например, поисковую систему в CMS или интернет-магазине), создайте полнотекстовый индекс и запросите не с LIKE '% keyword%', а MATCH AGAINST. Такие хитрости стоит знать, если мы начинаем создавать собственные CMS и системы магазинов.

8. Создавайте анти-паттерны

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

9. Выбор механизма хранения данных

Движок InnoDB поддерживает транзакции, обеспечивает лучший параллелизм, но медленнее как при чтении, так и при записи. MyISAM, с другой стороны, не поддерживает одновременное чтение и запись. Механизм Памяти несравненно быстрее, если мы используем его как кеш.

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

10. Кэш на прикладном уровне

В дополнение к стандартному кэшированию ответов MySQL мы можем использовать наши собственные структуры кэша. Если у нас есть SSD-диск, решения на основе файловой системы будут работать, особенно в случае возврата ответов, которые подсчитывают или сообщают данные.

Чуть лучшим решением будет использование open-source технологий Redis или memcached, которые хранят объекты в оперативной памяти — что  позволяет обрабатывать данные на несколько порядков быстрее  . Такое решение радикально разгружает базу данных, если ею пользуются одновременно многие люди. Благодаря этому мы можем отложить покупку сильного хостинга и ситуацию, при которой мы переплачиваем за облачное масштабирование.

Такие сайты, как Википедия, YouTube и Facebook, используют memchaced. Узнайте больше о  том, как установить и использовать memcached в вашем собственном PHP-приложении  .

11. Исключение некоторых результатов из кэширования

Тяжелые запросы, выполняемые время от времени (которые дополнительно кэшируются на прикладном уровне) и возвращающие большое количество ответов, следует исключить из кэша запросов с помощью команды SQL_NO_CACHE.

Идея состоит в том, чтобы не тратить место в кэше, которое можно использовать для многих других более мелких и более частых запросов, которые могут использовать результат, хранящийся в кэше. Примеры запросов, которые можно исключить из шифрования: отчетность, запросы, выполняемые в рамках CRON, изменение рейтинга в реальном времени и т. д.

Это действительно работает?

Пока все советовали мигрировать на гораздо более дорогой хостинг, я провел оптимизацию небольшой базы сайта, на что у меня ушло, наверное, полчаса. Эффект нельзя было не заметить. После перезагрузки сервера утилизация оперативной памяти снизилась с 90% до 70%:

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