10 лучших приемов для оптимизации работы с MySQL


Оглавление (нажмите, чтобы открыть):

Отзывы и обзоры хостинга

Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.

Как понять, что дело именно в MySQL

Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в WordPress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.

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

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

С чего начать оптимизацию

Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:

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

В первом случае можно попробовать оптимизировать отдельные запросы. Здесь поможет SQL-оператор EXPLAIN и знания об индексах. Это решение применимо ко всем сайтам, в том числе размещенным на виртуальном хостинге.

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

Но прежде, чем углубляться в детали, стоит сказать о кеше запросов – быстром и простом способе снять многие проблемы. Возможность включить кеш запросов есть у владельцев VPS и выделенных серверов.

Кеш запросов

В кеше запросов (query cache) сохраняются пары запрос-ответ. Когда запрос уже есть в кеше, ответ отдается практически мгновенно. Если данные в таблицах меняются не слишком часто, происходит ощутимый прирост производительности (в противном случае кеш быстро сбрасывается).

По умолчанию кеширование выключено. Включить его можно, добавив в конфигурационный файл my.cnf строчку вида query_cache_size = 64M . Через переменную query_cache_size задается размер оперативной памяти, выделяемой под кеш, в данном случае — 64 мегабайта.

Теперь нужно перезапустить MySQL. Сделать это можно из некоторых панелей управления (в ISPmanager: Management tools — Services), либо по SSH из командной строки примерно так:
/usr/local/etc/rc.d/mysql-server stop
/usr/local/etc/rc.d/mysql-server start

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

Есть еще несколько переменных для настройки кеша:

  • query_cache_type задает режим работы кеша, когда query_cache_size установлен больше нуля. Допустимые значения query_cache_type: 0 или OFF — кеширование выключено; 1 или ON — кеширование включено для всех выражений, кроме начинающихся с SELECT SQL_NO_CACHE; 2 или DEMAND — кеширование включено только для запросов, начинающихся с SELECT SQL_CACHE.
  • query_cache_limit – максимально допустимый размер, при котором результат выполнения запроса будет сохранен в кеше.
  • query_cache_min_res_unit – минимальный размер блоков памяти, выделяемых под кеш. По умолчанию 4 Кб. Если у вас много результатов значительно меньшего объема, query_cache_min_res_unit можно понизить, чтобы память использовалась эффективнее. Подходящее значение можно рассчитать по формуле (query_cache_size — Qcache_free_memory) / Qcache_queries_in_cache.

Пример my.cnf для небольшого VPS:
query_cache_size = 64M
query_cache_limit = 2M
query_cache_type = 1
query_cache_min_res_unit = 2K

Посмотреть текущее состояние кеша можно в phpMyAdmin на вкладке Status, либо из командной строки:

  • Qcache_free_blocks – количество свободных блоков в кеше.
  • Qcache_free_memory – объем свободной ОЗУ, отведенной под кеш.
  • Qcache_hits – количество запросов, результаты которых были взяты из кеша.
  • Qcache_inserts – количество запросов, которые были добавлены в кеш.
  • Qcache_lowmem_prunes – количество запросов, которые были удалены из кеша из-за нехватки памяти.
  • Qcache_not_cached – количество запросов, которые не были записаны в кеш (с SQL_NO_CACHE или некешируемые по другим причинам).
  • Qcache_queries_in_cache – количество запросов, которые находятся в кеше.
  • Qcache_total_blocks – общее количество блоков.

Долю закешированных запросов от их общего числа можно посчитать по формуле Qcache_hits / (Com_select + Qcache_hits). Степень использования кеша — Qcache_hits / Qcache_inserts.

О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)

Оптимизация отдельных запросов

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

Результат работы оператора EXPLAIN

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

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

  • производится поиск в части WHERE
  • соединяются таблицы при JOIN
  • сортируются и группируются записи при ORDER BY и GROUP BY
  • производится поиск MIN() и MAX()

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

Разбирая вывод EXPLAIN, обратите особое внимание на столбцы

  • type (значение ALL — плохо)
  • key (NULL — плохо)
  • ref (NULL — плохо)
  • extra (Using filesort, Using temporary, Using where — плохо)

Описание всех значений и пример оптимизации запроса можно посмотреть в документации.

Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name)

Журнал медленных запросов

Если определить тяжелые запросы «на глаз» не получается, нужно собрать более обширную статистику. В этом поможет журнал медленных запросов (slow query log).

Для включения журнала в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение 1 или ON; для отключения журнала — 0 или OFF. В более старых версиях используется log-slow-queries = /var/db/mysql/slow_queries.log (путь можно задать другой).

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

Пара полезных дополнительных настроек:

  • log-queries-not-using-indexes – запись в журнал запросов, не использующих индексы.
  • slow_query_log_file – имя файла журнала. По умолчанию host_name-slow.log


Пример для записи в журнал всех запросов, выполняющихся дольше 50 миллисекунд:
slow_query_log = 1
slow_query_log_file = /var/db/mysql/slow_queries.log
long_query_time = 0.05
log-queries-not-using-indexes = 1

Пример для старых версий MySQL, все запросы дольше 1 секунды:
log-slow-queries = /var/db/mysql/slow_queries.log
long_query_time = 1

Для анализа журнала используются утилиты mysqldumpslow, mysqlsla и mysql_slow_log_filter. Они парсят журнал и выводят агрегированную информацию о медленных запросах.

mysqldumpslow – утилита из состава MySQL. Вызывается таким образом: mysqldumpslow [параметры] [файл_журнала . ] . Пример:

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1

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

Некоторые параметры mysqldumpslow:

  • -t N – отображать только первые N запросов.
  • -g pattern — анализировать только запросы, которые соответствуют шаблону (как grep).
  • -s sort_type — как сортировать вывод. Значения sort_type: t или at — сортировать по суммарному или среднему времени выполнения запросов, c — по количеству выполненных запросов данного типа.

mysqlsla – еще одна утилита для анализа логов MySQL с аналогичной функциональностью. Пример использования:

mysqlsla -lt slow /tmp/slow_queries.log

Подробности в документации.

mysql_slow_log_filter
— perl-скрипт с похожей функциональностью. Пример использования:

tail –f mysql-slow.log | mysql_slow_log_filter –T 0.5 –R 1000

Эта команда в реальном времени покажет запросы, выполняющиеся дольше 0,5 секунды или сканирующие больше 1000 строк.

Выявленные медленные запросы дальше можно оптимизировать, используя EXPLAIN и индексы.

Вторая часть статьи будет посвящена тонкой настройке MySQL. Материал находится в разработке.

Оптимизация MySQL (просто о сложном)

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

Делайте запросы MySQL удобными для кэширования

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

Дело в том, что в первом запросе была использована функция CURDATE(), особенность её работы не позволяет помещать результаты запроса в кэш. Значение даты можно предварительно записать в строку запроса, это позволит исключить использование функции CURDATE() в запросе.
По аналогии есть и другие функции, которые не кэшируются самим сервером MySQL, среди них RAND(), NOW() а так же другие функции, результат которых недетерминирован.

Просмотрите как выполняется ваш запрос с помощью синтаксиса EXPLAIN

Посмотреть, как MySQL выполняет ваш запрос можно с помощью синтаксиса EXPLAIN. Его использование может помочь определить слабые места в производительности запроса, а так же в структуре таблиц. В качестве результата запроса EXPLAIN возвратит данные, которые покажут, какие используются индексы, каким образом выбираются данные из таблиц, как сортируются, и т.д. Для этого достаточно добавить вначале SELECT-запроса ключевое слово EXPLAIN, после чего будет показана таблица, с данными.

Когда вам нужна одна запись, выставляйте LIMIT 1

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

Индексируйте поля по которым производится поиск

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

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

Индексируйте поля по которым объединяются таблицы

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

Найдите альтернативу вместо ORDER BY RAND()

Использование рандомной сортировки действительно является весьма удобным, и об этом такого же мнения многие начинающие программисты. Однако тут есть подводные камни, и очень весомые, используя подобный метод выборки в своих запросах, вы оставляете узкое место в производительности. Здесь же рекомендуется прибегнуть к дополнительному коду вместо использования ORDER BY RAND(), в качестве альтернативы, чтобы избавиться от слабого места в производительности, которое напомнит о себе при увеличении объема данных.

Используйте выборку конкретных полей, вместо SELECT *

Не ленитесь указывать конкретные нужные поля в запросе при выборке, вместо использования «*» — выборка всех полей, дело в том, что чем больше данных считывается из таблицы, тем медленнее становиться ваш запрос.

Добавляйте поле ID для всех таблиц

Каждая таблица в хорошем её исполнении должна иметь поле id типа INT, которое является первичным ключом (PRIMARY_KEY), и AUTO_INCREMENT. Кроме того, для поля нужно указать параметр UNSIGNED, который означает то, что значение всегда будет положительным.
В MySQL есть внутренние операции, которые могут использовать первичный ключ, это играет роль для сложных конфигураций баз данных, таких как кластеры, распараллеливание, и т.д.
Кроме того, если есть несколько таблиц, и необходимо выполнить объединенный запрос, то тут ID таблиц окажется весьма кстати.

ENUM как альтернатива VARCHAR

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

Используйте значение NOT NULL вместо NULL

Поля NULL занимают больше места в записи, из-за того что возникает необходимость отмечать это NULL значение. Таблицы MyISAM, поля с NULL хранятся таким образом, что каждое поле занимает 1 дополнительный бит, который округляется до ближайшего байта. Если использование NULL в поле не принципиально, то рекомендуется использовать NOT NULL.

Пользуйтесь Prepared Statements

Prepared Statements (подготовленные выражения, их так же называют связываемыми переменными) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Его использование имеет преимущества в плане безопасности, и производительности. Фильтруя значения данных, добавляемых в запрос, prepared statements таким образом защищает базу данных от SQL инъекций. Разумеется, делать подобные проверки можно и в ручную, однако в этом случае есть вероятность допустить ошибку из-за невнимательности. Последние версии MySQL компилируют prepared statements в бинарную форму, это позволяет повысить эффективность его работы. Выполняя множество однотипных запросов в приложении, MySQL будет разбирать запрос только один раз. На первых этапах prepared statements не имело возможности кэшироваться в MySQL, это являлось веской причиной для его игнорирования и отсутсвия желания использовать в своих проектах. Однако начиная с версии MySQL 5.1 ситуация кардинально поменялась.

Пользуйтесь mysql_unbuffered_query

mysql_unbuffered_query() посылает запрос MySQL query без автоматической обработки и буферизации её результата, в отличие от функции mysql_query(). Это позволяет сохранить достаточно большое количество памяти для SQL-запросов, возвращающих большое количество данных. Кроме того, вы можете начать работу с полученными данными сразу после того, как первый ряд был получен: вам не приходится ждать до конца SQL-запроса.


Вы можете хранить IP-адреса в поле с типом INT (UNSIGNED)

Для хранения IP-адресов в привычном виде многие хранят в таблице с полем типа VARCHAR(15), и лишь не многие используют целочисленный тип для этого. Плюсы в том, тип INT занимает 4 байта и имеет фиксированный размер поля. Поле типа INT должно быть UNSIGNED, т.е. целочисленным, в запросе следует использовать функцию INET_ATON(), которая будет конвертировать IP-адрес в число. Обратное преобразование выполняется с помощью функции INET_NTOA().

Используйте статичные таблицы

Статичная таблица это обычная таблица в базе, за исключеним того, что каждое поле в таблице имеет фиксированный размер. Если в таблице есть колонки, не фиксированной длины, к примеру, это могут быть: VARCHAR, TEXT, BLOB, она перестает быть статичной, и будет обрабатываться MySQL немного иначе. Статичные таблицы, или их можно ещё назвать таблицами фиксированного размера работают быстрее не статичных. Записи из таких таблицах будут просматриваться быстрее, при необходимости выбора нужной строки MySQL быстро вычислит её позицию. Если поле имеет не фиксированный размер, то в этом случае поиск производиться по индексу. Есть и другие плюсы использования статических таблиц, дело в том, что эти таблицы проще кэшируются, а так же восстанавливаются после падения базы данных.

Мастер Йода рекомендует:  Как вытащить носки одинакового цвета, не заглядывая в комод

Используйте вертикальное разделение

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

Разделяйте объемные запросы INSERT и DELETE

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

Стремитесь использовать поля небольшого размера

Как известно данные базы хранятся на жестком диске, это зачастую это может оказаться одним из слабых мест в веб-приложении. Дело в том, что записи небольшого размера являются более предпочтительными, т.к. использование их уменьшает работу с жестким диском. Если вы уверенны, что конкретная таблица будет хранить мало строк, то рациональным решением будет использование типов полей, с минимальными возможными значениями. К примеру, если основной ключ имеет тип INT, и вы будете хранить в таблице лишь небольшое кол-во данных, то лучше сделать его типа MEDIUMINT, SMALLINT или даже TINYINT.

Выбирайте тип таблиц под свои задачи

Два широко известных типа таблиц на сегодняшний день, это MyISAM и InnoDB, каждый из них имеет свои положительные и отрицательные стороны. К примеру, MyISAM хорошо считывает данные из таблиц в большом объеме, одно он более медлителен при записи. Он так же хорошо выполняет запросы вида SELECT COUNT(*).
Механизм хранения данных у InnoDB более сложный, чем у MyISAM, однако, он поддерживает блокировку строк, что является положительной стороной при масштабировании. Поэтому сказать, что одно лучше другого нельзя, да и не правильно, нужно выбирать тип исходя из своих потребностей.

Оптимизация сервера MySQL с помощью утилиты MySQL Tuner

Оптимизация сервера MySQL с помощью специальной утилиты mysqltuner.

После установки MySQL на выделенный сервер, базовый файл конфигурации находится в /etc/my.cnf .
В такой конфигурации работать сервер будет, но про оптимальную работу говорить не буду.

Сперва потребуется конфигурационный файл my.cnf , более подходящий для нашего сервера.

По умолчанию примеры расположены в директории /usr/share/mysql .

Выбираем конфигурационный файл исходя из наших потребностей:

my-small.cnf — для систем с объемом памяти менее 64Mb, где MySQL используется редко или в незначительной степени;

medium.cnf — под MySQL может выделяться до 64Мb памяти (для маломощных VDS/VPS);

my-large.cnf — для систем с оперативной памятью от 512Мb;

my-huge.cnf — для систем с оперативной памятью 1-2Gb;

Выбираем одну из наиболее подходящих нам конфигураций и переписываем её вместо текущей my.cnf .

Приступим к оптимизации с помощью утилиты MySQL Tuner

Скачиваем утилиту

Скрипт попросит имя и пароль MySQL администратора, после чего выведет результаты своей работы.
Вывод результатов работы утилиты примерно такой:

Очень важно обратить внимание на строку

Количество текущих соединений — 16, количество возможных соединений — 151. Параметр 151 — это параметр использующийся по умолчанию. Обычно этого значения маловато, именно из-за него появляются сообщения вида “Too many active connections”. Увеличим этот параметр до 300.
В секции [mysqld] в конфигурационном файле /etc/my.cnf

Еще одним, наиболее важным параметром увеличения производительности MySQL является key_buffer_size . Параметр определяет размер общего для всех пользовательских процессов буфера индексных блоков MyISAM таблиц. Обычно устанавливается в пределах 30-40% от общей выделенной под MySQL оперативной памяти.

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

Рекомендации

1) Ставим MyISAM по умолчанию и отключаем ненужные нам вещи:

2) Привязываем базу для использования только на нашем сервере:

3) Выставляем необходимые максимальные ограничения:

4) Устанавливаем общий буфер, кэш для запросов и максимальный размер для временных таблиц:

5) Максимальное число соединений к базе и ожидающих их процессов:

6) Буферы на каждый процесс (большие значения типа 1Mb и выше для нас это явно перебор).

7) Служебный сортировочный буфер (память постоянно не занимает) и кэш открытых таблиц.

8) Необходимые таймауты, чтобы отсекать долгие ожидания.

9) Установка кодировки UTF-8:

10) По-желанию можно добавить логи медленных запросов или неиспользуемых индексов
(создайте доступным на запись файл для лога).

Обратите внимание на “1.7M per thread (151 max threads)”. Увеличив количество max_connections в 3 раза, сильно увеличится использование оперативной памяти. Поэтому не стоит ставить max_connections про запас. Посмотрите свои текущие метрики, сколько соединений бывает в пике, прибавьте к этому 10-20%.

Как ускорить работу MySQL и снять нагрузку с дисковой подсистемы

Любой успешный проект рано или поздно сталкивается с проблемой роста. Число посетителей веб-сайта увеличивается, веб-сервер обрабатывает бóльшее количество соединений, растёт поток запросов к базе данных. В определённый момент времени отзывчивость сайта снижается: страницы загружаются медленнее, что, согласно многочисленным исследованиям, влияет на конверсию (пример подобного исследования — http://www.webperformancetoday.com/2014/04/09/web-page-speed-affect-conversions-infographic/).

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

В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:

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


Как ускорить чтение

Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.

Движок InnoDB имеет встроенный кэш для данных и индексов — так называемый Buffer Pool. Его размер регулируется переменной innodb_buffer_pool_size. В идеале размер Buffer Pool должен быть как минимум такого объёма, чтобы в нём полностью можно было разместить все данные и индексы плюс 30%-60% от их размера. Дополнительная память используется для служебных нужд и Insert Buffer, а также для обеспечения запаса памяти на будущее. Переменная innodb_buffer_pool_size — не динамическая, поэтому после её изменения в конфигурационном файле потребуется перезапуск MySQL.

Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:

Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.

По умолчанию под pagecache выделяется почти вся незанятая процессами память, поэтому увеличить его объём можно лишь установкой дополнительных планок RAM. Однако память — недорогой по сравнению с ЦПУ и дисками ресурс, при этом эффект от увеличения кэша может привести к значительному увеличению производительности. Ниже представлен график %iowait — доли времени, в течение которого ЦПУ ожидает ввода/вывода. График снят с рабочего нагруженного сервера. Думаю, комментарии здесь излишни.

Как ускорить запись

Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.

По умолчанию InnoDB сбрасывает изменённые данные на диск с помощью системного вызова fsync(). При этом операционная система не гарантирует, что данные попадут в хранилища сию секунду, т.к. данные сперва проходят через буфер, поддерживаемый ядром. Буферизация необходима для ускорения ввода/вывода.

Однако если datadir MySQL расположен на аппаратном RAID-массиве, то есть возможность задействовать для такой буферизации NVRAM-кэш RAID-контроллера, что намного эффективнее. Следует только убедиться, что контроллер оснащён BBU (Battery Backup Unit) — отдельным источником питания для кэша. При внезапном отключении электропитания у контроллера должно быть время, чтобы сбросить содержимое кэша на диски, иначе данные в массиве останутся в неконсистентном состоянии.

При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.

Значение по умолчанию (1) предполагает, что буфер redo-логов, расположенный в памяти InnoDB, записывается на диск после каждого коммита транзакции. Это наиболее безопасный режим работы, обеспечивающий сохранность каждой транзакции даже в случае “падения” сервера. Можно выставить innodb_flush_log_at_trx_commit в значение 2, тогда логи будут записываться также после каждого коммита, но fsync() — сброс данных на диск — будет выполняться лишь раз в секунду (начиная с версии MySQL 5.6.6 этот интервал определяется переменной innodb_flush_log_at_timeout). Аварийное завершение работы СУБД не приведёт к потере транзакций, однако отключение самого сервера может привести к потере последней секунды транзакций. Значение 0 подразумевает ещё более быстрый режим записи — данные и записываются, и синхронизируются раз в секунду, безотносительно коммитов транзакций. Однако innodb_flush_log_at_trx_commit=0 может привести к потере транзакций даже при падении процесса. Администратору базы данных нужно сделать выбор исходя из текущей нагрузки и бизнес-требований.

Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:

Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).

Вывод

Разумеется, все эти советы не являются исчерпывающими. Ключом к быстрой работе БД является понимание ваших данных, грамотно спроектированная схема и удачно составленные запросы. Тем не менее ряд эффективных оптимизаций можно произвести на уровне сервера.

Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

Главное меню » Базы данных » База данных MySQL » Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

Это приведет к множеству неиспользованного пространства, а также может повлиять на производительность.

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

В этом руководстве объясняется, как оптимизировать и дефрагментировать таблицы в MySQL.

1. Определение таблиц для оптимизации

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

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

В приведенном выше выводе:

  • Отображает список всех таблиц, которые имеет минимум 500MB неиспользованного пространства. Как мы видим выше, в данном примере, есть 3 таблицы, которые имеют более чем 500MB неиспользованного пространства.
  • Колонка data_length_mb показывает общий размер таблицы в MB. Например, размер таблицы EMPLOYEE составляет около 21GB.
  • Столбец data_free_mb отображает общее неиспользуемое пространство в этой конкретной таблице. Например, таблица EMPLOYEE имеет 19MB неиспользованного пространства.
  • Все эти три таблицы (EMPLOYEE, DEPARTMENT и MANAGERS) сильно фрагментированы, и они должны быть оптимизированы, чтобы освободить неиспользуемое пространство.

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

Размер файла будет таким же, как вы видите в колонке “data_length_mb” в приведенном выше выводе.

В этом примере файл EMPLOYEE.MYD занимает 21GB на уровне файловой системы, но он имеет много неиспользованного пространства. Если мы оптимизируем эту таблицу, размер этого файла должен сильно уменьшится.

2. Дефрагментация с помощью команды OPTIMIZE TABLE

Есть два способа оптимизации таблицы.

Первый способ заключается в использовании команды Optimize table, как показано ниже.

Следующий пример позволит оптимизировать таблицу EMPLOYEE.

Вы также можете оптимизировать несколько таблиц в одной команде, как показано ниже.

Несколько моментов, надо иметь в виду при оптимизации таблиц:

  • Оптимизация таблицы может быть выполнена для InnoDB или MyISAM или таблиц архивов.
  • Для таблиц MyISAM, будет произведен анализ таблицы, и будет дефрагментировать соответствующий MySQL файл данных, и освободит неиспользуемое пространство.
  • Для таблиц InnoDB, после оптимизации таблицы надо будет просто выполнить alter table, чтобы восстановить пространство.
  • Если у вас есть индексы, они также изменят страницы индекса, и обновят статистику.

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

В приведенной выше оптимизации, таблица EMPLOYEE представляет собой таблицу MyISAM.

Для этого примера, перед оптимизацией, вы увидите следующий файл с расширением .MYD для таблицы.

Во время работы команды OPTIMIZE TABLE, вы можете увидеть, что он создал временный файл для таблицы с расширением .TMD. Размер этого временного файла будет продолжать расти, пока таблица оптимизируются.

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

3. Дефрагментация с помощью команды mysqlcheck

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

Следующий пример позволит оптимизировать таблицу отделов. Вы выполнить эту команду из командной строки Linux (а не в MySQL).


Примечание: Внутренняя команда mysqlcheck использует команду “OPTIMIZE TABLE”.

В приведенном выше примере:

  • mysqlcheck это команда, которая выполняется из командной строки Linux.
  • -o параметр, чтобы указать, что mysqlcheck должен выполнять операцию “OPTIMIZE TABLE”.
  • AndreyExBase является база данных
  • DEPARTMENT является таблица внутри базы данных AndreyExBase, которая должна быть оптимизирована
  • -u root указывает на то, что команда mysqlcheck должна использовать “root” в качестве пользователя mysql для подключения
  • -p указывает пароль для root MySQL. Обратите внимание, что нет пространства между опцией -p и паролем.

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

4. Дефрагментация всех таблиц или всех баз данных

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

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

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

Ниже будет оптимизировать все базы данных в вашей системе.

5. После оптимизации

После оптимизации, используя следующий запрос, проверьте total-size и unused-space-size для трех таблиц, которые мы оптимизировали в этом примере.

Как видно из приведенных выше выходных данных, то data_length_mb сильно уменьшилась для этих таблиц после оптимизации. Кроме того, data_free_mb теперь равна 0, так как нет никакой фрагментации больше.

Размер файла для этих таблиц теперь намного меньше по сравнению с исходным размером. Теперь мы оптимизировали много неиспользуемого пространства на уровне файловой системы для этих таблиц.

В этом примере, мы освободили около 37GB неиспользованного пространства после оптимизации этих трех таблиц.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Как ускорить Mysql. Чек лист работ

У Вас тормозит сайт. Вы не можете качественно отдать контент. Абоненты не могут нормально работать с закрытыми частями сайта. Служба поддержки получает звонки от разгневаных пользователей. Кто виноват и что делать? Ответ как правило в следующем:

  • некачественный код
  • не оптимизирована база
  • не оптимально настроен сервер БД
  • все вышесказанное вместе
  • не справляется железо (этот пункт следует рассматривать только после оптимизации пп. 1-3)
Мастер Йода рекомендует:  Как заработать на блоге раскрываем секреты

Оставим рефакторинг кода на откуп программистам и посмотрим, как можно ускорить mysql сервер. Все написаное ниже верно и для mariadb

Настройка mysql

  1. Установить последнюю версию mysql
  2. Запустить mysqltunner и дать ему поработать 24+ часов. Выполнить его рекомендации
  3. Настроить query_cache_size, query_cache_limit
  4. Для движка MyISAM настроить key_buffer_size
  5. Для движка ARIA настроить aria-pagecache-buffer-size (только для mariadb)
  6. Значение innodb_buffer_pool_size должно быть 70-80% от обьема RAM
  7. Для innodb-таблиц установить innodb_file_per_table=1. Для изменений потребуется сдампить и удалить (перименовать) базу. Накатить базу из дампа по-новой
  8. Посмотреть на параметр innodb_flush_log_at_trx_commit. Возможно имеет смысл отключить сброс данных на диск
  9. Выяснить, что используется чаще SELECT или UPDATE. Если SELECT то low-priority-updates=1, если UPDATE то low-priority-updates=0. Есть смысл сделать два инстанса mysql на разных сокетах/портах и разделить базы по приоритету использования SELECT/UPDATE
  10. Возможно потребуется увеличить wait_timeout если у конечного пользователя наблюдаются ошибки соединения
  11. Для временных таблиц использовать tmpfs вместо дисковой фс
  12. Если возможно, для обмена данными приложения и БД использовать сокет. Такой обмен работает быстрее, чем обмен через tcp-соединение

Настройка БД

  1. Запустить mytop/mtop и отследить медленные запросы
  2. Включить slow.log. Оптимизировать медленные запросы. Использовать EXPLAIN
  3. Проверить индексы в таблицах. Там, где используется поиск однозначно должен быть индекс
  4. Установить правильные тип и размер полей. Это уменьшит размер таблицы
  5. Использовать партиционирование если в таблице есть старые данные, которые нужны, но к ним редко обращаются
  6. Регулярный optimize table таблиц с движком ARIA/MyISAM
  7. Пробовать persistant connection в базу. Возможно полегчает, возможно нет. Это индивидуально


Железо и система

  1. Много памяти, чем больше — тем лучше. База любит память, дать ей разумный максимум, который только возможен
  2. Хороший, многоядерный сервер
  3. Дисковая система в ra >Помнить, что mysql с параметрами по-умолчанию не оптимизирован под высокие нагрузки. Оптимизация БД это комплекс работ. Каждое соединение, каждый байт — считаются

Александр Черных
системный администратор

Оптимизация MySQL

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


PROCEDURE ANALYSE()

Стандартная функция, которая анализирует поля таблицы в запросе и выводит советы по оптимальной длине/типу полей. Например SELECT * FROM table PROCEDURE ANALYSE() .

Известно, что использование типа полей занимающего большую память делает более медленной работу с таблицами. Enum быстрее чем Varchar, varchar быстрее чем text, medium int быстрее чем int и т.д. Часто можно оптимизировать типы в зависимости от данных в таблице.


Определение не эффективных индексов

Индексы в таблицах — большое благо, но не стоит забывать что кроме того, что индексы ускоряют выборки из таблицы (SELECT) они замедляют обновление таблиц (UPDATE) и добавление новых полей (INSERT), так как при каждом обновлении данных все индексы перестраиваются. Также индексы занимают место на диске. В таблицах где данные часто обновляются/добавляются использование индексов должно быть сбалансированным.

С помощью этого хитрого запроса можно увидеть 10 самых малоэффективных индексов во всей базе данных :

SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `inde name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != ‘mysql’ GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != ‘mysql’ AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01))

Взято отсюда: http://pastebin.com/f6b1c381c. Уберите LIMIT 10, чтобы увидеть статистику по всем индексам.

EXPLAIN – Определение используемых индексов и строк, которые нужно обработать MySQL базе данных для выполнения запроса.

Очень просто, добавляем к нашему запросу слово EXPLAIN. Например: EXPLAIN SELECT * FROM table WHERE var=value ORDER BY field. Очень полезно для просмотра эффективности индексов.

Полный отказ от ORDER BY rand();

Конструкция ORDER BY rand(); отдаёт поля из таблицы в случайном порядке. Основная засада в том, что при выполнении этой конструкции не используются индексы и осуществляется полное сканирование таблицы. Уже при нескольких тысячах записей могут начаться проблемы. Разумно переписать код, чтобы все записи брались из базы данных, добавлялись в массив и сортировались в массиве (shuffle в PHP). Если записей очень много, имеет смысл сделать кеширование — например делать случайную сортировку раз в час.

Отключить InnoDB если он не нужен

Это движки таблиц. MyISAM – быстрее и проще, InnoDB – умеет много полезного, например транзакции, отсутствие блокировок. Если все эти фишки не нужны, то кто-то советует оставаться на MyISAM, кто-то наоборот переходить на InnoDB. Я пока ещё этот вопрос для себя не решил и использую MyISAM.

В любом случае если InnoDB не используется стоит отключить его поддержку, т.к. сервер кушает лишнюю память и работает медленее. Для отключения добавляем skip-innodb в конфиг (/etc/mysql/my.cnf).

Медленные запросы

Некоторые запросы выполняются очень медленно. Скорей всего о большинстве них вы и не подозреваете, т.к. вы тестировали когда в таблице было несколько сотен записей, а сейчас там несколько десятков тысяч и т.д. Найти такие запросы, на которые стоит обратить самое пристальное внимание, можно если включить логгирование Slow Queries. В конфиг файле (/etc/mysql/my.cnf) прописываем:

log_slow_queries = /var/log/mysql/mysql-bin.log
long_query_time = 2

2 – это количество секунд, больше которых выполняемый запрос будет считаться медленным и добавляться в лог. Если добавить ещё строчку log-queries-not-using-indexes в логи будут добавляться все запросы, для которых не использованы индексы.

Затем перегружаем MySQL сервер (/etc/init.d/mysql restart) и в /var/log/mysql будут добавлятся файлы логов с медленными запросами за последние 7 дней (по умолчанию).

Кеширование в MySQL

Стоит конечно писать скрпиты так, чтобы запросов в базу данных было как можно меньше, но часть ваших проблем на себя может взять MySQL. Правильная настройка кеширования приведёт к тому, что повторяющиеся запросы будут браться из кеша и выполняться очень быстро. У меня например 75% запросов выполняется из кеша. Конечно, этот процент зависит от типа запросов, от размера самого кеша, от частоты запросов к базе данных и т.д. Обычно стандартная конфигурация не всегда подходит, т.к. для разной нагрузки нужны разные настройки. Чтобы узнать что менять, смотрим следующий пункт.

Автоматический тюнинг

С помощью простой утилитки mysqltuner можно обнаружить самые основные узкие места в конфигурации MySQL.

Выполняем на сервере:

wget http://mysqltuner.com/mysqltuner.pl
chmod u+x mysqltuner.pl
./mysqltuner.pl

Далее нужно ввести root логин и пароль к MySQL серверу, а скрипт проанализирует данные и выведет советы в автоматическом режиме. Я меняю параметры в конфиге, на которые нужно обратить внимание, а затем через несколько дней смотрю опять.

Также полезные советы можно посмотреть в phpMyAdmin если нажать ссылку “Текущее состояние MySQL” на главной странице.

Минимизирование изменения таблиц

Добавление или изменения данных в таблицы приводит к тому, что перестраиваются все индексы и сбрасывается кэш таблицы. Хорошо, если в таблице немного записей. У меня в нескольких проектах есть таблицы в которых несколько миллионов записей, благодаря индексам выборки из таблиц происходят быстро, но при изменении таблицы и при одновременном обращении к ней, происходит резкое замедление выполнения выборок (запросы SELECT).

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

Оптимизация таблиц

Иногда следует выполнять OPTIMIZE TABLE, т.к. таблицы со временем фрагментируются из-за изменения, добавления данных и скорость доступа к ним, со стороны дисковой системы сервера уменьшается.

Полезные ссылки по Оптимизации MySQL:

/mysql:оптимизация

Правильная настройка Mysql под нагрузки и не только. Обновлено.

#Серверная #Базы данных #mysql #оптимизация

Проверка работы Mysql под нагрузкой Sysbench


#Масштабирование #Тестирование нагрузки #mysql #оптимизация

Повышение скорости работы запросов с MySQL Handlersocket

#Серверная #Базы данных #mysql #оптимизация

Check-unused-keys для определения неиспользуемых индексов в базе данных

#Серверная #Базы данных #mysql #оптимизация #индексы

Оптимизация постраничного вывода данных

#Серверная #Базы данных #mysql #оптимизация

Синтаксис и оптимизация Mysql LIMIT

#Серверная #Базы данных #mysql #оптимизация

Эффективная замена ORDER BY RAND()

#Серверная #Базы данных #mysql #оптимизация

Как строятся по-настоящему большие системы на основе MySQL

#Масштабирование #Работа с данными #mysql

Как с помощью этого параметра повысить производительность Mysql

Ускорение репликации в Mysql 5.6+

#Серверная #Базы данных #mysql #репликация

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

#Серверная #Базы данных #mysql #профилирование #оптимизация

Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL

#Серверная #Базы данных #mysql #логирование

Быстрая альтернатива Mysqldump для больших таблиц без блокировок и выключений.

#Серверная #Базы данных #mysql #оптимизация

Что такое индексы в Mysql и как их использовать для оптимизации запросов

#Серверная #Базы данных #mysql #индексы #профилирование

Быстрый подсчет уникальных значений за разные периоды времени

#Масштабирование #Работа с данными #mysql #redis

Чеклист по максимальному ускорению WordPress

Оптимизация записи на диск в Mysql с помощью innodb_flush_method

Архитектурные принципы высоконагруженных приложений

#Масштабирование #Архитектурные решения #nginx #php #load balancing

5 методик использования lazy loading для оптимизации

4 шага и 9 инструментов для анализа нагрузки на сервер

Что значит высокая нагрузка (highload) и что при этом делать?

#Масштабирование #Основные принципы #highload

. , то оптимизация будет . отсутствует оптимизация порядка . от MySQL (и . от MySQL, файл . диалекта MySQL, и .

. Внешние MySQL и . . FROM mysql(&#39 . бесполезными.Оптимизация операций .

10 лучших приемов для оптимизации работы с MySQL

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

Сегодня идет много разговоров о Dig Data и других новых технологиях. NoSQL и облачные решения это супер, но много популярного софта (такого как WordPress, phpBB, Drupal) до сих пор работает на MySQL. Миграция на новейшие решения может вылиться не только в изменении конфигурации на серверах. К тому же, эффективность MySQL до сих пор на уровне, особенно версия Percona.

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

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

Для кого эта статья?

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

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

Делать бэкапы MySQL просто, используя утилиту mysqldump:

Вы можете узнать больше о mysqldump .


Что делает запрос медленным?

Вот общий список факторов, влияющих на скорость выполнения запросов и нагрузки сервера:

  • индексы таблиц;
  • условие WHERE(и использования внутренних функций MySQL, например, таких как IF или DATE);
  • сортировка по ORDER BY;
  • частое повторение одинаковых запросов;
  • тип механизма хранения данных (InnoDB, MyISAM, Memory, Blackhole);
  • не использование версии Percona;
  • конфигурации сервера ( my.cnf / my.ini );
  • большие выдачи данных (более 1000 строк);
  • нестойкое соединение;
  • распределенная или кластерная конфигурация;
  • слабое проектирование таблиц.

Далее мы обратимся ко всем этим проблемам. Также, установите Percona , если вы еще не используете эту встроенную замену стандартному MySQL — это придаст сильное увеличение мощности базы данных.

Что такое индексы?

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

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

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

Уменьшаем частое повторение одинаковых запросов

Наиболее быстрый и эффективный способ, который я нашел для этого — это создание хранилища запросов и результатов их выполнения с помощью Memcached или Redis. С Memcache вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

Теперь тяжелый запрос, использующий LEFT JOIN, будет выполняться только раз за каждые 86 400 секунд (то есть раз в сутки), что значительно уменьшит нагрузку MySQL сервера, оставив ресурсы для других соединений.

Примечание: Допишите p: в начале аргумента хоста MySQLi для создания постоянного соединения.

Распределенная или кластерная конфигурация

Когда данных становится все больше, и скорость вашего сервиса идет под уклон, паника может овладеть вами. Быстрым решением может стать распределения ресурсов (sharding). Однако я не рекомендую делать это, если вы не обладаете хорошим опытом, поскольку распределение по своей сути делает структуры данных сложнейшими.

Слабое проектирование таблиц

Создание схем баз данных не является сложной работой, если следовать таким золотым правилам, как работа с ограничениями и знание того, что будет эффективным. Например, хранение изображений в ячейках типа BLOB очень смущает — лучше храните путь к файлу в ячейке VARCHAR, это является гораздо лучшим решением.

Обеспечение правильного проектирования для нужного использования является первостепенным в создании вашего приложения. Храните различные данные в различных таблицах (например, категории и статьи) и убедитесь, что отношения к другу (many to one) и один ко многим (one to many) могут быть легко связаны с идентификаторами (ID). Использование FOREIGN KEY в MySQL идеально подходит для хранения каскадных данных в таблицах.

При создании таблицы помните следующее:

  • Создавайте эффективные таблицы для решения ваших задач, а не заполняйте таблицы лишними данными и связями.
  • Не ожидайте от MySQL выполнения вашей бизнес логики или програмности — данные должны быть готовы к вставке строки вашей скриптовым языком. Например, если вам нужно отсортировать список в случайном порядке, сделайте это в массиве PHP, не используя ORDER BY из арсенала MySQL.
  • Используйте индексные типы UNIQUE для уникальных наборов данных и применяйте ON DUPLICATE KEY UPDATE, чтобы хранить дату обновленной, например, для того, чтобы знать, когда строка была в последний раз изменена.
  • Используйте тип данных INT для сохранения целых чисел. Если вы не укажете размер типа данных, MySQL сделает это за вас.

Основы оптимизации

Для эффективной оптимизации мы должны применять три подхода к вашему приложению:

  1. Анализ (логирование медленных запросов, изучение системы, анализ запросов и проектирование базы данных)
  2. Требования к исполнению (сколько пользователей)
  3. Ограничения технологий (скорость железа, неправильное использование MySQL)

Анализ может быть сделан несколькими путями. Сначала мы рассмотрим наиболее очевидные способы, чтобы заглянуть под капот вашей MySQL, в котором выполняются запросы. Самый первый инструмент оптимизации в вашем арсенале это EXPLAIN. Если добавить этот оператор перед вашим запросом по SELECT, результат запроса будет таким:

Мастер Йода рекомендует:  Как запретить пользователю сохранять картинки

Колонки, вы видите, сохраняют важную информацию о запросе. Колонки, на которые вы должны обратить наибольшее внимание это possible_keys и Extra.

Колонка possible_keys покажет индексы, в которые MySQL имел доступ, чтобы выполнить запрос. Иногда нужно назначить индексы, чтобы запрос выполнялся быстрее. Колонка Extra покажет, были ли использованы дополнительные WHEREили ORDER BY. Наиболее важно обратить внимание, есть ли Using Filesort в выводе.

Что делает Using Filesort, указано в справке MySQL:

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

Лишний проход замедлит ваше приложение, этого нужно избегать, чего бы это ни стоило. Другой критический результат Extra, который мы должны избегать — это Using temporary. Он говорит о том, что MySQL пришлось создать временную таблицу для выполнения запроса. Очевидно, это ужасное использования MySQL. В таком случае результат запроса должен быть сохранен в Redis или Memcache и не выполняться пользователями лишний раз.

Чтобы избежать проблемы с Using Filesort мы должны увериться, что MySQL использует INDEX. Сейчас указано несколько ключей в possible_keys, из которых можно выбирать, но MySQL может выбрать только один индекс для финального запроса. Также индексы могут быть составлены из нескольких колонок, также вы можете ввести подсказки (хинты) для оптимизатора MySQL, указывая на индексы, что вы создали.

Оптимизатор MySQL будет использовать статистику, основанную на запросах таблиц, чтобы выбрать лучший индекс для выполнения запроса. Он действует достаточно просто, основываясь на встроенной статистической логике, поэтому имея несколько вариантов, не всегда делает правильный выбор без помощи хинтинга. Чтобы убедиться, что был использован правильный (или неправильный) ключ, воспользуйтесь ключевым словам FORCE INDEX, USE INDEX и IGNORE INDEX в вашем запросе. Вы можете прочитать больше о хинтинге индексов в справке MySQL .

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

В дополнение к EXPLAIN существует ключевое слово DESCRIBE. Вместе с DESCRIBE можно просматривать информацию из таблицы следующим образом:

Для добавления индексов в MySQL надо использовать синтаксис CREATE INDEX. Есть несколько видов индексов. FULLTEXT Применяется для полнотекстового поиска, а UNIQUE — для хранения уникальных данных.

Чтобы добавить индекс в вашу таблицу, используйте следующий синтаксис:

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

Индексы имеют большое влияние на скорость выполнения запросов. Только назначения главного уникального ключа недостаточно — композитные ключи являются реальной областью применения в настройке MySQL, что иногда требует некоторых A/B проверок с использованием EXPLAIN.

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

Как только мы создали ключ на основе колонки username, в котором хранится имя пользователя и колонки active типа ENUM, определяющий, активен ли его аккаунт. Теперь все оптимизировано для запроса, который будет использовать WHERE для поиска валидного имени пользователя с активным аккаунтом (active = 1).

Насколько быстра ваша MySQL?

Включим профилирование, чтобы подробнее рассмотреть MySQL запросы. Это можно сделать, выполнив команду set profiling=1, после чего для просмотра результата надо выполнить show profiles.

Если вы используете PDO, выполните следующий код:

То же самое можно сделать с помощью mysqli:

Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

Этот запрос выполнялся 0.00024300 секунд. Это довольно быстро, поэтому не будем беспокоиться. Но когда числа становятся большими, мы должны смотреть глубже. Перейдите к вашему приложению, чтобы потренироваться на рабочем примере. Проверьте константу DEBUG в конфигурации вашей базы данных, а затем начните изучать систему, включив вывод результатов профилирования с помощью функций var_dump или print_r. Так вы сможете переходить со страницы на страницу в вашем приложении, получив удобное профилирование системы.

Полный аудит работы базы вашего сайта

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


Чтобы включить логирование в MySQL 5.1.6 используйте глобальную переменную log_slow_queries, также вы можете отметить файл для логирования с помощью переменной slow_query_log_file. Это можно сделать, выполнив следующий запрос:

Также это можно указать в файлах конфигурации /etc/my.cnf или my.ini вашего сервера.

После внесения изменений не забудьте перезагрузить MySQL сервер необходимой командой, например service mysql restart, если вы используете Linux.

В версиях MySQL после 5.6.1 переменная log_slow_queries обозначена как устаревшая и вместо нее используется slow_query_log. Также для более удобного дебаггинга можно включить вывод в таблице, задав переменной log_output значение TABLE, однако эта функция доступна только с MySQL 5.6.1.

Переменная long_query_time определяет количество секунд, после которых выполнение запроса считается медленным. Значение это 10, а минимум это 0. Также можно указать миллисекунды, используя дробь; сейчас я указал одну секунду. И теперь каждый запрос, который будет выполняться дольше 1 секунды, записывается в логи в таблице.

Логирование будет вестись в таблицах mysql.slow_log и mysql.general_log вашей MySQL базы данных. Чтобы выключить логирование, измените log_output на NONE.

Логирование на рабочем сервере

На рабочем сервере, который обслуживает клиентов, лучше применять логирование только на короткий период и для мониторинга нагрузки, чтобы не создавать лишней нагрузки. Если ваш сервис перегружен и необходимо безотлагательное вмешательство, попробуйте выделить проблему, выполнив SHOW PROCESSLIST, или обратитесь к таблице information_schema.PROCESSLIST, выполнив SELECT * FROM information_schema.PROCESSLIST;.

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

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

Логирование множества запросов

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

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

Горячий и холодный кэш

Количество запросов и нагрузка сервера имеет сильное влияние на исполнение, также может повлиять на время выполнения запросов. При разработке вы должны взять за правило, что выполнение каждого запроса должно быть не более доли миллисекунды (0.0xx или быстрее) на свободном сервере.

Применение Memcache имеет сильный эффект на нагрузку серверов, освободит ресурсы, которые выполняют запросы. Убедитесь, что вы используете Memcached эффективно и протестовали ваше приложение с горячим кэшем (подгруженными данным) и с холодным кэшем.

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

Исправление медленных запросов

Теперь, когда логирование настроено, вы могли найти несколько медленных запросов на вашем сайте. Давайте исправим их! Для примера я покажу несколько распространенных проблем, вы можете встретить и логику их исправления.

Если вы пока не нашли медленного запроса, проверьте настройки long_query_time, если вы пользуетесь этим методом логирования. Иначе, проверив все ваши запросы профилирования (set profiling=1), составьте список запросов, отнимают больше времени, чем доля миллисекунд (0.000x секунд) и начнем из них.

Вот шесть самых распространенных проблем, которые я находил, оптимизируя MySQL запросы:

ORDER BY и filesort

Предотвращение filesort иногда невозможно из-за выражения ORDER BY. Для оптимизации сохраните результат в Memcache, или выполните сортировку в логике вашего приложения.

Использование ORDER BY вместе с WHERE и LEFT JOIN

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

Применение ORDER BY по временным колонками

Просто не делайте этого. Если вам нужно объединить результаты, сделайте это в логике вашего приложения; не используйте фильтрацию или сортировку во временной таблице запроса MySQL. Это требует много ресурсов.

Игнорирование индекса FULLTEXT

Использование LIKE это самый лучший способ сделать полнотекстовый поиск медленным.

Беспричинный выбор большого количества строк

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

Чрезмерное использование JOIN вместо создания композитных таблиц или представления

Когда в одном запросе вы пользуетесь больше чем тремя-четырьмя операторами LEFT JOIN, спросите себя: все ли здесь верно? Продолжайте, если у вас есть на то веская причина, например — запрос используется не часто для вывода в панели администратора, или результат вывода может быть сохранен в кэше. Если же вам нужно выполнять запрос с большим количеством операций объединения таблиц, тогда лучше задуматься о создании композитных таблиц из необходимых столбиков или использовать представления.

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

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

10 лучших приемов для оптимизации работы с MySQL

Для запуска магазина плагинов мы использовали WooCommerce и плагин WooCommerce Software Subscriptions. Суть запроса – получение всех подписок покупателя по его номеру. В WooCommerce довольно сложная модель данных. Хотя заказ и хранится в виде кастомного типа поста, но id покупателя не хранится в post_author, а является частью post мета данных. Также есть несколько подключений к пользовательским таблицам, созданным плагином подписки на ПО. Давайте более подробно разберем запрос.

MySQL твой друг

В MySQL есть полезное выражение DESCRIBE, с помощью которого можно выводить информацию о структуре таблицы (ее колонки, типы данных, значения по умолчанию). Если выполнить DESCRIBE wp_postmeta;, то вы увидите:

Круто, но вы, возможно, уже знаете об этом. Но знали ли вы, что префикс DESCRIBE можно использовать на SELECT, INSERT, UPDATE, REPLACE и DELETE? Более широко известен синоним EXPLAIN, который даст нам подробную информацию о том, как будет выполняться выражение.

Результат для медленного запроса:

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

Самая главная колонка type, в ней описывается способ объединения таблиц. Если стоит ALL, значит MySQL читает всю таблицу с диска, увеличивает скорость чтения/записи и перекладывает загрузку на CPU. Процесс называется полное сканирование таблицы (более подробно позже).

Колонка rows также указывает на то, что MySQL должен делать. Она показывает, сколько строк необходимо просмотреть для нахождения результата.

Explain дает больше информации, чем мы можем оптимизировать. Например, таблица pm2 (wp_postmeta) говорит нам, что мы используем Using filesort, так как хотим, чтобы результаты хранились с помощью выражения ORDER BY. Если бы мы еще группировали запрос, это добавило бы еще больше нагрузки на выполнение.

Визуальное расследование

MySQL Workbench – еще один хороший бесплатный инструмент для расследований. Для баз данных MySQL 5.6 и выше результат EXPLAIN можно вывести в виде JSON, а MySQL Workbench превращает этот JSON в визуальный план выполнения выражения:

Он автоматически рисует предупреждения по проблемам, окрашивая части запроса, которые будут долго выполняться. Мы сразу видим, что присоединение к таблице wp_woocommerce_software_licences (алиас |) добавляет проблем.

Решение

Часть запроса выполняет полное сканирование таблицы, чего следует избегать, так как она использует колонку без индекса order_id для объединения между таблицами wp_woocommerce_software_licences и wp_posts. Распространенная проблема в медленных запросах. Ее очень легко решить.

Индексы

Order_id – довольно важная часть определения данных в базе данных, и если мы будем строить запрос таким образом, то нам понадобится добавить индекс в эту колонку, или MySQL будет буквально сканировать все строки таблицы, пока не найдет необходимые. Добавим индекс и посмотрим, что он даст:

Нам удалось сэкономить 5 секунд запроса простым добавлением индекса, круто!

Знайте свой запрос

Проанализируйте запрос – join за join, подзапрос за подзапросом. Запрос делает то, что не должен? Его можно оптимизировать?

В нашем случае мы подключаем таблицу лицензий к таблице постов с помощью order_id, постоянно ограничивая выражение типами постов shop_order. Это необходимо для обеспечения целостности данных, чтобы убедиться, что мы используем только правильные записи заказов. На самом деле эта часть запроса устарела. Мы знаем, что безопаснее, чтобы в строке лицензии ПО в таблице был order_id, относящийся к заказу WooCommerce в таблице постов, поскольку это применяется в коде плагина PHP. Давайте удалим join и посмотрим, улучшит ли это запрос:

Несильно, но запрос теперь выполняется меньше 3 секунд.

Кэшируйте все!

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

Query Monitor выяснил, что наш запрос запускается 4 раза за одну загрузку страницы. Хотя и хорошо кэшировать MySQL запросы, но дублировать считывания из базы данных в одном запросе точно не стоит. Статическое кэширование в коде PHP – простой и крайне эффективный способ решения проблемы. Вы получаете результат запроса из базы данных при первом запросе и храните результат в статическом свойстве класса. В последующих вызовах будет возвращаться результат из статического свойства:

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

Мыслите нестандартно

Можно предпринять и другие способы для ускорения выполнения запросов, в которых нужно чуть больше, чем просто поправить запрос или добавить индекс. Одна из самых медленных частей нашего запроса – процесс объединения таблиц для перехода от id покупателя к id товара, и это необходимо делать для каждого покупателя. А что если сделать все объединения за раз, чтобы получать данные о покупателе тогда, когда это необходимо?

Вы можете денормализовать данные, создав таблицы, в которой хранятся данные лицензии, а также id пользователя и товара для всех лицензий, а также запрос к конкретному покупателю. Понадобится пересобрать таблицу с помощью MySQL triggers на INSERT/UPDATE/DELETE для таблицы лицензий (или других в зависимости от изменения данных), но это значительно повысит производительность запроса данных.

Если несколько join замедляют запрос, можно ускорить его, разбив на 2 и более выражения, после чего выполнять их отдельно в PHP, собирать и фильтровать результаты в коде. Laravel делает что-то похожее в жадной загрузке в Eloquent.

WordPress может замедлять запросы к таблице wp_posts, если объем данных большой, и присутствует множество кастомных типов постов. Если запросы типов постов замедляют сайт, попробуйте уйти от модели хранения кастомных типов постов в сторону кастомной таблицы.

Результаты

С помощью этих подходов по оптимизации запросов нам удалось ускорить наш запрос с 8 до 2 секунд, а также снизить количество вызовов с 4 до 1. Время запросов записывалось на версии для разработки, в продакшн оно было бы меньше.

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

Автор: Iain Poulson

Источник: https://www.tutorialspoint.com/

Редакция: Команда webformyself.

Добавить комментарий