Различия между хорошо написанным SQL-кодом и не очень велики, и при работе на сайтах с высоким спросом они вызывают серьезные последствия для производительности и надежности обслуживания. В этом руководстве я расскажу, как писать быстрые запросы и какие факторы способствуют их медленной работе.
Почему MySQL?
Сегодня много говорят о больших данных и новых технологиях. NoSQL и облачные решения великолепны, но многие популярные веб-программы (такие как WordPress, phpBB, Drupal, VBulletin Forum и т. Д.) Все еще работают на MySQL. Переход на эти новые решения может быть не таким простым, как просто оптимизация конфигурации, уже имеющейся в работе. Кроме того, производительность MySQL очень хорошая, особенно версия Percona .
Не делайте распространенную ошибку, тратя все больше и больше вычислительной мощности на решение проблемы медленных запросов и высокой нагрузки на сервер, вместо того, чтобы фактически решать коренные проблемы. Добавление мощности процессора, твердотельных накопителей или оперативной памяти — это форма оптимизации, если хотите, но об этом я не буду здесь говорить. Кроме того, без оптимизированного сайта по мере роста аппаратного обеспечения проблемы будут расти в геометрической прогрессии. Так что это не солидное долгосрочное решение.
Хорошая работа с SQL всегда является жизненно важным инструментом для веб-разработчика, и, поскольку исправление часто так просто, как простое добавление индекса или незначительное изменение порядка использования таблицы, это действительно помогает узнать, как правильно использовать вашу СУБД. В этом случае мы ориентируемся на популярную базу данных с открытым исходным кодом, часто используемую вместе с PHP, и это MySQL.
Для кого это руководство?
Веб-разработчики, архитекторы баз данных / администраторы баз данных и системные администраторы, знакомые с MySQL. Если вы не знакомы с MySQL как новичком, то это руководство, скорее всего, не будет иметь особого смысла, но я постараюсь сделать его максимально информативным для новичков в MySQL.
Резервное копирование сначала
Я рекомендую попробовать шаги, предусмотренные для вашей собственной базы данных MySQL (сначала сделайте резервную копию всего, конечно!). Если у вас нет базы данных, с которой можно работать, предоставьте пример схемы базы данных, где это применимо.
Резервное копирование MySQL легко с помощью утилиты командной строки mysqldump :
bash $ mysqldump myTable > myTable-backup.sql
Вы можете узнать больше о mysqldump .
Что делает запрос медленным?
Вкратце и в порядке убывания важности следующие факторы играют существенную роль в производительности запросов и серверов:
- табличные индексы
-
Whereпредложение (и использование внутренних функций MySQL, таких какIFиDATEнапример) - сортировка с помощью
Order By - частота одновременных запросов
- тип хранилища (InnoDB, MyISAM, Memory, Blackhole)
- не использует версию Percona
- переменные конфигурации сервера (настройка my.cnf / my.ini)
- большие наборы результатов (> 1000 строк)
- непостоянные соединения
- конфигурация шардинга / кластера
- плохой дизайн стола
Мы рассмотрим все эти области в этом руководстве. Кроме того, если вы еще не используете его, пожалуйста, установите Percona , которая является заменой MySQL, которая принесет серьезное увеличение производительности. Чтобы увидеть эталон Percona против MySQL, посмотрите на это сравнение .
Что такое индексы?
MySQL использует индексы для быстрого поиска строк с конкретными значениями столбцов, например, внутри WHERE . Без индекса MySQL должен начинаться с первой строки, а затем читать всю таблицу, чтобы найти соответствующие строки. Чем больше стол, тем больше это стоит.
Если таблица имеет индекс для рассматриваемых столбцов, MySQL может быстро определить позицию, которую нужно искать в середине файла данных, не просматривая все данные. Это намного быстрее, чем последовательное чтение каждой строки.
Непостоянные соединения?
Когда ваш язык сценариев соединяется с базой данных, если вы настроили постоянные соединения, он сможет повторно использовать существующее соединение без необходимости создавать новое. Это оптимально для производственного использования и должно быть включено.
Пользователи PHP могут прочитать больше в руководстве по PHP .
Сокращение частоты одновременных запросов
Самый быстрый и эффективный способ, который я нашел, чтобы исправить это, — это использовать хранилище пар ключ-значение, такое как Memcached или Redis .
С помощью Memcache вы можете просто кэшировать содержимое вашего запроса, например, следующим образом:
« `php <? php $ cache = new Memcache; $ Cache-> Connect ( ‘локальный’, 11211); $ cacheResult = $ cache-> get (‘key-name’); if ($ cacheResult) {// .. нет необходимости запрашивать $ result = $ cacheResult; } else {// .. запустите ваш запрос $ mysqli = mysqli (‘p: localhost’, ‘username’, ‘password’, ‘table’); // добавляем p: к имени хоста для постоянства $ sql = ‘SELECT * FROM постов ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к userInfo используя (UID) WHERE posts.post_type =’ post ‘|| posts.post_type = ‘article’ ЗАКАЗАТЬ ПО столбцу LIMIT 50 ‘; $ result = $ mysqli-> query ($ sql); $ memc-> set (‘имя-ключа’, $ result-> fetch_array (), MEMCACHE_COMPRESSED, 86400); }
// Передаем $ cacheResult в шаблон $ template-> assign (‘posts’, $ cacheResult);
?> « `
Теперь пример запроса LEFT JOIN будет выполняться только один раз каждые 86 400 секунд (24 часа), что устраняет огромную нагрузку на сервер MySQL и уменьшает количество одновременных подключений.
Примечание: добавьте p: к аргументу вашего хоста в MySQLi для постоянных соединений.
Sharding / кластеризация
Когда ваши данные становятся большими или спрос на вашу услугу возрастает, может возникнуть паника. Быстрое решение, гарантирующее, что ваша служба остается в сети, может быть разрушено. Но я не рекомендую этого, потому что разделение по своей природе, кажется, делает структуры данных слишком сложными. И, как очень красноречиво объясняется в этой статье из блога Percona , не осколок.
Плохой дизайн стола
Создание схем базы данных не так уж сложно, если вы принимаете некоторые золотые правила, такие как работа с ограничениями и понимание того, что будет эффективно. Например, настоятельно рекомендуется хранить изображения в базе данных в виде типов blob объектов; хранение имени файла в столбце типа данных varchar намного лучше.
Обеспечение правильности дизайна для требуемого использования имеет первостепенное значение при создании вашего приложения. Разделяйте конкретные данные (например, категории и сообщения) и гарантируйте, что отношения «многие к одному» или «один ко многим» могут быть легко связаны с идентификаторами. Использование функции FOREIGN KEY в MySQL идеально подходит для каскадного обмена данными между таблицами.
При создании таблицы постарайтесь запомнить следующее:
- Используйте минимум, необходимый для выполнения работы; быть разреженным и к делу.
- Не ожидайте, что MySQL будет выполнять вашу бизнес-логику или быть программным — это должно быть сделано до вставки вашим языком сценариев. Например, если вам нужно рандомизировать список, выполните рандомизацию массива в PHP, а не в
ORDER BYв MySQL. - Используйте тип индекса
UNIQUEдля уникальных наборов данных и используйтеON DUPLICATE KEY UPDATEчтобы обновлять дату и время, например, дату последней проверки строки. - Используйте тип данных
INTдля целых чисел. Если вы не укажете длину, MySQL сама рассчитает, что требуется.
Основы оптимизации
Чтобы эффективно оптимизировать, мы должны рассмотреть три основных набора данных, касающихся вашего приложения:
- Анализ (медленная регистрация запросов, аудит, анализ запросов и анализ таблиц)
- Требования к производительности (сколько пользователей, каков спрос)
- Технологические ограничения (аппаратная скорость, слишком большая потребность в MySQL)
Анализ можно сделать несколькими способами. Во-первых, мы пойдем самым прямым путем, чтобы заглянуть под капот запросов MySQL. Первым инструментом в вашем наборе инструментов оптимизации является EXPLAIN . Использование этого в вашем запросе до SELECT даст вам следующий результат:
sql mysql> EXPLAIN SELECT * FROM `wp_posts` WHERE `post_type`='post'; +----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+ | 1 | SIMPLE | wp_posts | ref | type_status_date | type_status_date | 82 | const | 2 | Using where | +----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
Каждый из перечисленных столбцов содержит полезную информацию о выполняемом запросе. Столбцы, на которые нужно обратить пристальное внимание: possible_keys и Extra .
possible_keys будут отображать индексы, которые движок MySQL может использовать для запроса. Иногда вам нужно принудительно индексировать, чтобы запрос выполнялся максимально быстро.
Столбец Extra покажет, использовался ли условный WHERE или ORDER BY . Наиболее важно отметить, если появляется Using Filesort . Рассмотрим следующий пример:
sql EXPLAIN SELECT main_text FROM posts WHERE user = 'myUsername' && status = '1' && ( status_spam_user = 'no_spam' || ( status_spam_user = 'neutral' && status_spam_system = 'neutral' ) ) ORDER BY datum DESC LIMIT 6430 , 10
Этот тип запроса может попасть на диск из-за условного где, что происходит, если мы посмотрим на EXPLAIN :
sql id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE posts ref index_user,index_status index_user 32 const 7800 Using where; Using filesort
Таким образом, этот запрос имеет возможность использовать два индекса, и в настоящее время он попадает на диск из-за Using filesort в Extra .
Что 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 .
Вы можете указать несколько подсказок для оптимизатора, например:
sql SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;
Запуск EXPLAIN покажет вам, какой индекс использовался в конечном результате. Итак, чтобы исправить предыдущий пример, мы добавим USE INDEX следующим образом:
sql EXPLAIN SELECT main_text FROM posts USE INDEX (index_user) WHERE user = 'myUsername' && status = '1' && ( status_spam_user = 'no_spam' || ( status_spam_user = 'neutral' && status_spam_system = 'neutral' ) ) ORDER BY datum DESC LIMIT 6430 , 10
Теперь, когда в MySQL есть index_status для использования, запрос исправлен.
sql id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE posts ref index_user,index_status index_user 32 const 7800 Using where
Рядом с EXPLAIN находится ключевое слово DESCRIBE . С помощью DESCRIBE вы можете просматривать информацию таблицы следующим образом:
sql mysql> DESCRIBE City; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | Country | char(3) | NO | UNI | | | | District | char(20) | YES | MUL | | | | Population | int(11) | NO | | 0 | | +------------+----------+------+-----+---------+----------------+
Добавление индексов
Вы создаете индексы в MySQL с синтаксисом CREATE INDEX . Есть несколько вариантов индекса. FULLTEXT используется для целей полнотекстового поиска, а затем существует UNIQUE тип для обеспечения уникальности данных.
Чтобы добавить индекс в таблицу, используйте следующий синтаксис, например:
sql mysql> CREATE INDEX idx_start_of_username ON `users` (username(10));
Это создаст индекс для users таблицы, который будет использовать первые 10 букв столбца имени пользователя, который является типом данных varchar.
В этом случае любой поиск, требующий сортировки WHERE по имени пользователя с совпадением в первых 10 символах, будет таким же, как поиск по всей таблице.
Композитные индексы
Индексы оказывают огромное влияние на скорость, необходимую для возврата данных запроса. Просто установить первичный ключ и уникальный индекс обычно недостаточно — составные ключи — это то место, где настоящая ниша настройки находится в MySQL, и чаще всего это требует некоторой A / B-проверки с помощью EXPLAIN .
Например, если нам нужно сослаться на два столбца в нашем условном WHERE , составной ключ будет идеальным.
sql mysql> CREATE INDEX idx_composite ON users (username, active);
Здесь этот ключ создается для столбца с username из предыдущего примера и для столбца active , тип данных ENUM который указывает, активна ли учетная запись пользователя. Так что теперь при запросе данных для WHERE имя пользователя является действительным, а учетная запись active = 1 , набор данных теперь оптимизирован для лучшей обработки.
Как быстро ваш MySQL?
Включите профилирование, чтобы ближе рассмотреть ваши запросы MySQL. Это можно сделать во время выполнения с помощью set profiling=1 , а затем выполнить ваш запрос и посмотреть результат show profiles .
С PDO вот фрагмент кода, который делает именно это:
« `php $ db-> query (‘set profiling = 1’); $ db-> query (‘выберите заголовок, тело, теги из сообщений’); $ rs = $ db-> query (‘показать профили’); $ db-> query (‘set profiling = 0’); // Отключаем профилирование после выполнения запроса
$ records = $ rs-> fetchAll (PDO :: FETCH_ASSOC); // Получить результаты профилирования
$ errmsg = $ rs-> errorInfo () [2]; // Поймать любые ошибки здесь « `
Если вы не используете PDO, то же самое можно сделать с помощью mysqli :
« `php $ db = new mysqli ($ host, $ username, $ password, $ dbname);
$ db-> query (‘set profiling = 1’); $ db-> query (‘выберите заголовок, тело, теги из сообщений’); if ($ result = $ db-> query («ПОКАЗАТЬ профили», MYSQLI_USE_RESULT)) {while ($ row = $ result-> fetch_row ()) {var_dump ($ row); } $ result-> close (); }
if ($ result = $ db-> query («показать профиль для запроса 1», MYSQLI_USE_RESULT)) {while ($ row = $ result-> fetch_row ()) {var_dump ($ row); } $ result-> close (); }
$ db-> query (‘set profiling = 0’); « `
Это вернет вам данные профилирования, которые будут включать время выполнения во втором значении ассоциативного массива:
php array(3) { [0]=> string(1) "1" [1]=> string(10) "0.00024300" [2]=> string(17) "select headline, body, tags from posts" } Запрос занял 0,00024300 секунд. Это достаточно быстро, чтобы не беспокоиться. Но когда цифры возрастают, мы должны взглянуть глубже.
В качестве рабочего примера познакомьтесь с вашим приложением. Поместите проверку для константы DEBUG в драйвер базы данных уровня абстракции базы данных / frameworks, и затем вы можете начать аудит, включив регистр профиля и var_dump результат с помощью var_dump / print_r . Теперь вы сможете с легкостью просматривать и профилировать страницы вашего сайта!
Полный аудит вашего приложения
Чтобы выполнить полный аудит ваших запросов, включите ведение журнала. Некоторые разработчики, с которыми я работал, опасаются, что это двусторонняя проблема, поскольку включение ведения журнала слегка влияет на производительность, поэтому статистика, которую вы записываете, будет несколько ниже, чем на самом деле. Хотя это правда, многие тесты показывают, что разница не так уж велика.
Чтобы включить ведение журнала в MySQL версии 5.1.6, вы используете глобальные log_slow_queries и можете указать файл с slow_query_log_file global. Это можно сделать в командной строке, как показано ниже:
bash set global log_slow_queries = 1; set global slow_query_log_file = /dev/slow_query.log;
Вы можете установить это постоянно в /etc/my.cnf конфигурации /etc/my.cnf или my.ini для вашего сервера.
bash log_slow_queries = 1; slow_query_log_file = /dev/slow_query.log;
После внесения этого изменения необходимо перезапустить сервер MySQL, например service mysql restart в системах Linux.
В более новой версии MySQL 5.6.1 log_slow_queries устарела, а вместо slow_query_log используется slow_query_log . Включение TABLE качестве выходного типа обеспечивает более приятный опыт отладки и может быть сделано следующим образом в MySQL 5.6.1 и более поздних версиях:
bash log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1
long_query_time указывает количество секунд, на которые классифицируется медленный запрос. По умолчанию 10 и минимальное 0. Может принимать значения в миллисекундах, указав число с плавающей запятой; здесь я установил его на 1 секунду. Таким образом, любой запрос, занимающий более 1 секунды, будет зарегистрирован в выходном формате TABLE .
Это приведет к mysql.slow_log таблицы mysql.slow_log и mysql.general_log в MySQL .
Чтобы отключить ведение журнала, установите для log_output значение NONE .
log_queries_not_using_indexes — это полезное логическое значение, которое при включении в сочетании с медленным журналом запросов означает, что регистрируются только те запросы, которые должны получить все строки.
Эта опция не всегда означает, что индекс не используется. Например, когда запрос использует полное сканирование индекса, это будет зарегистрировано, потому что индекс не будет ограничивать количество строк.
Вход в производство?
Включение регистрации на производственном сайте с трафиком почти всегда должно быть выполнено в течение короткого периода, при этом отслеживая нагрузку, чтобы убедиться, что она не влияет на обслуживание. Если вы находитесь под большой нагрузкой и нуждаетесь в срочном исправлении, начните с устранения проблемы в командной строке с помощью SHOW PROCESSLIST или непосредственно через таблицу information_schema.PROCESSLIST , например, select * from information_schema.PROCESSLIST; ,
Регистрация всех запросов в производственной среде может многое вам сказать и является хорошей практикой для исследовательских целей, когда вы проводите аудит проекта, но если он будет работать несколько дней подряд, вы не сможете получить больше полезных данных, чем самое большее за 48 часов ( в среднем, по крайней мере, фиксируйте пиковые периоды использования, чтобы хорошо взглянуть на запросы и получить представление о частоте).
Примечание: если у вас есть сайт, который испытывает скачки пикового трафика, а затем периоды совсем немного (например, спортивный сайт во время сезона и в межсезонье), будьте логичны с тем, как вы смотрите на регистрацию. Не думайте, что сайт работает быстро. Проведите аудит и самое главное настройте некоторые графики.
Ведение журнала и pt-query-digest Percona
У Percona есть несколько отличных инструментов, а pt-query-digest — это инструмент командной строки для анализа журналов запросов, списка процессов или tcpdumps.
Вы можете использовать pt-query-digest следующими способами:
Проанализируйте файл * .log (например, выводится из журнала медленных запросов):
bash $ pt-query-digest slow.log
Отчет по самым медленным запросам с host1 в режиме реального времени (очень полезно!):
bash $ pt-query-digest --processlist h=host1
Используйте tcpdump, чтобы сообщить о самых медленных запросах из данных протокола MySQL:
« `bash $ tcpdump -s 65535 -x -nn -q -tttt -i любой -c 1000 порт 3306> mysql.tcp.txt
$ pt-query-digest –type tcpdump mysql.tcp.txt « `
Наконец, мы можем сохранить данные медленного запроса с одного хоста на другой для последующего просмотра. Здесь мы сохраняем дайджест запроса для slow.log на host2:
bash $ pt-query-digest --review h=host2 --no-report slow.log
Чтобы узнать, как в полной мере использовать инструмент pt-query-digest Percona, прочитайте страницу руководства .
Графики MySQL и производительность сервера
Этот график операций со строками InnoDB показывает операции со строками, которые выполнял InnoDB: обновляет, читает, удаляет и вставляет.
Это действительно большая тема, и я достаточно затрону ее в этом руководстве, чтобы вы начали работать с мониторингом MySQL. Однако в целом важно отметить, что мониторинг всех услуг вашего веб-сайта идеально подходит для того, чтобы действительно знать, какова ваша эффективность и использование.
Для этого я рекомендую настроить решение на основе RRDTool такое как Cacti с конфигурацией MySQL. Получите шаблон для Cacti у ребят из Percona.
После того, как вы настроили Cacti и можете начать анализировать ваше приложение, подождите некоторое время, чтобы графики могли нарастить. Через несколько дней вы начнете видеть дневной и ночной ритмы трафика и узнаете, насколько загруженным является сервер.
Если вы ищете автоматические оповещения и триггеры, обратите внимание на настройку monit , активного монитора с открытым исходным кодом для систем Unix. С помощью monit вы можете создавать правила для своего сервера и гарантировать, что вы будете предупреждены об увеличении нагрузки, чтобы вы могли ее отследить, пока она происходит.
Журнал медленных запросов
Регистрация всех медленных запросов, выполнение которых занимает более секунды, может нам кое-что сказать, но также важно знать, какие запросы выполняются сотни раз. Даже если эти запросы не выполняются быстро, накладные расходы на высокие запросы по-прежнему сказываются на сервере.
Вот почему оставаться на месте, когда вы что-то обновляете и запускаете, — самое важное время для любой новой работы с базой данных и изменений. У меня всегда есть политика в отношении моих команд — никогда не синхронизировать изменения базы данных новых функций после среды в реальном проекте. Это должно быть сделано в начале недели, самое позднее во вторник, чтобы все команды могли контролировать и оказывать поддержку соответственно.
Перед тем, как начать работу с новыми запросами, вы должны сравнить с инструментом нагрузочного тестирования, таким как ab . Когда вы запускаете тест, вы должны просматривать SHOW PROCESSLIST , а также включать ведение журнала и мониторинг с помощью системных инструментов, таких как top , free и iostat . Это важный шаг, прежде чем вводить любой новый запрос в живое производство. Но это не 100% кислотный тест, потому что живой трафик может вести себя совсем не так, как вычисленный тест.
Для сравнения с ab убедитесь, что у вас установлен пакет, например:
bash #centos users $ sudo yum install ab #debian / ubuntu users $ sudo apt-get install ab
Теперь вы можете начать с тестирования вашего приложения, например:
bash $ ab -k -c 350 -n 20000 my-domain.com/
Параметр -k означает keep-alive соединения, а -c 350 — количество одновременных подключений, т. Е. Количество людей / клиентов, которые одновременно попадут на сайт. Наконец, -n 20000 — это количество запросов, которые будут my-domain.com на my-domain.com .
Таким образом, выполнив приведенную выше команду, вы попадете на http://my-domain.com/ с 350 одновременными подключениями, пока не будет удовлетворено 20 000 запросов, и это будет сделано с помощью заголовка keep alive.
После того как процесс завершит 20 000 запросов, вы получите отзыв о статистике. Это скажет вам, насколько хорошо сайт работал под нагрузкой, которую вы испытали при использовании указанных выше параметров. Это хороший способ узнать в автоматическом смысле, изменился ли ваш запрос.
Сравнительный анализ Горячий против Холодного
Количество запросов и нагрузка на сервер оказывают огромное влияние на производительность, и это может повлиять на время запроса. В целом, вы должны включить медленный журнал запросов, чтобы поймать это в работе, и, как правило, для разработки вы должны убедиться, что все запросы выполняются в долях миллисекунды (0,0xx или быстрее) на незанятом сервере.
Внедрение Memcache окажет значительное влияние на ваши требования к нагрузке и будет использоваться для серьезной разгрузки ресурсов, которые использовались для обработки запросов. Убедитесь, что вы эффективно используете Memcached и сравните приложение с горячим кешем (предварительно загруженным со значениями) и холодным.
Чтобы избежать запуска в производство с пустым кешем, скрипт предварительного загрузчика является хорошим способом гарантировать, что кеш будет прочитан, и вы не получите огромное количество запросов, поступающих сразу при возврате из простоя из-за перебои в перегрузке.
Исправление медленных запросов
Итак, включив ведение журнала, вы теперь нашли несколько медленных запросов в вашем приложении. Давайте приступим к их исправлению! В качестве примера я продемонстрирую различные типичные проблемы, с которыми вы столкнетесь, и логику их устранения.
Если вы еще не нашли медленных запросов, то, возможно, проверьте свои настройки для long_query_time если вы используете метод ведения журнала запросов. В противном случае, проверив все ваши запросы с помощью профилирования ( set profiling=1 ), составьте список запросов, выполнение которых занимает больше долей миллисекунды (0,000x секунд), и давайте начнем с них.
Общие проблемы
Вот шесть типичных проблем, с которыми я сталкиваюсь при оптимизации запросов MySQL:
1. ORDER BY с помощью сортировки файлов.
sql mysql> explain select * from products where products.price > 4 and products.stock > 0 order by name; +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 1142 | Using where; Using filesort | +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
Избежать сортировки файлов по этому невозможно из-за имени ORDER BY name . Независимо от того, какую перестановку индексов вы используете, лучшее, что вы получите, это Using where; Using Filesort Using where; Using Filesort в вашем столбце Extra . Чтобы оптимизировать это, сохраните результат в Memcache или выполните упорядочивание на уровне логики вашего приложения.
2. Использование ORDER BY на WHERE и LEFT JOIN
ORDER BY оказывает значительное влияние на запросы. Например, нижеследующее является базовым LEFT JOIN соединением таблицы products таблицы categories посредством целочисленного идентификатора. При упорядочении убирается сортировка файлов.
« `sql mysql> объяснение выбора продуктов. * из продуктов использовать индекс (idx_price) левых категорий соединения с использованием (catID), где products.price> 4 и catID = 4 ORDER BY stock ASC limit 10; + — + —— + —- + — + —— + — + — + — + — + ———- + | id | select_type | стол | тип | возможные_ключи | ключ | key_len | ref | строки | Extra | + — + —— + —- + — + —— + — + — + — + — + ———- + | 1 | ПРОСТО | продукты | ВСЕ | idx_price | NULL | NULL | NULL | 986 | Используя где; Использование сортировки файлов | | 1 | ПРОСТО | категории | конст | ПЕРВИЧНЫЙ | ПЕРВИЧНЫЙ | 4 | конст | 1 | Используя индекс | + — + —— + —- + — + —— + — + — + — + — + ———- + 2 ряда в наборе (0,00 сек)
mysql> объяснение выбора продуктов. * из индекса использования товаров (idx_price) категории левого соединения, используя (catID), где products.price> 4 и catID = 4; + —- + ————- + ———— + ——- + ————— + —— + id | select_type | стол | тип | возможные_ключи | ключ | key_len | ref | строки | Extra | + —- + ————- + ———— + ——- + ————— + —— + 1 | ПРОСТО | продукты | ВСЕ | idx_price | NULL | NULL | NULL | 986 | Используя где | | 1 | ПРОСТО | категории | конст | ПЕРВИЧНЫЙ | ПЕРВИЧНЫЙ | 4 | конст | 1 | Используя индекс | +1 (0,00 сек) « `
Когда этого можно избежать, старайтесь не использовать ORDER BY . Если это абсолютно необходимо использовать, заказывайте только по ключу индекса.
3. Order By по временному столбцу
Просто не делай этого. Если вам нужно объединить свои результаты, сделайте это в логике своего приложения; не выполняйте фильтрацию или упорядочение временной таблицы внутри MySQL. Это будет очень ресурсоемким.
4. Не использовать индекс FULLTEXT
Использование запроса LIKE — самый медленный способ выполнить полнотекстовое сопоставление ваших данных. Реализуйте полнотекстовый поиск и воспользуйтесь преимуществами этой замечательной функции MySQL:
sql mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST ('database'); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 1 | MySQL Tutorial | DBMS stands for DataBase ... | +----+-------------------+------------------------------------------+ 2 rows in set (0.00 sec)
5. Выбирать огромное количество строк без необходимости
Забыв LIMIT в запросе, вы можете существенно изменить время поиска для больших наборов данных (более миллиона строк).
6. Пересоединение вместо создания составной таблицы или представления
Когда он достигает трех или четырех уровней LEFT JOIN , вы должны спросить себя: «Я делаю это правильно?» Если у вас есть разумные аргументы в пользу того, почему этот запрос должен быть таким, например, он появляется только на экране администратора в низком уровне. по требованию или при использовании большего статистического представления, которое можно кэшировать, затем продолжите. Но если вам требуется частый доступ к вашим данным с помощью большого количества объединений, вам следует подумать о том, как объединить столбцы в новую таблицу может быть более выгодным, или создать представление .
Выводы
Мы обсудили основы оптимизации и инструменты, которыми мы располагаем для выполнения работы. Мы должны провести аудит с помощью профилирования и использовать сначала инструмент pt-query-digest и EXPLAIN чтобы увидеть, что на самом деле происходит, а затем мы сможем улучшить дизайн.
Мы также рассмотрели несколько примеров и типичных ошибок, с которыми вы можете столкнуться при использовании MySQL. Используя подсказки индекса, мы можем гарантировать, что MySQL выберет правильные индексы для работы и не будет сбит с толку, особенно если в одной таблице несколько запросов. Чтобы продолжить чтение этой темы, ознакомьтесь с проектом Percona и блогом MySQL Performance для получения дополнительной информации.