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

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

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

1. Где медлительность?

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

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

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

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

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

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

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

• Уменьшите количество подзапросов
• Сокращение лишних повторяющихся операций

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

• Запрашиваются ли ненужные данные
• были ли просканированы дополнительные записи

2. Запрашиваются ли нежелательные данные

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

Вот некоторые типичные случаи:

1. Запросить нежелательные записи

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

Разработчики обычно используют сначалаSELECTОператор запрашивает большое количество результатов, а затем первые N строк данных получаются запросом приложения или интерфейсным уровнем отображения.Например, на новостном веб-сайте запрашивается 100 записей, но только первые 10 записей отображается на странице.

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

2. Вернуть все столбцы, если несколько таблиц связаны

Если вы хотите запросить все в фильмахAcademy DinosaurАктеры, появляющиеся в , не запрашивают следующим образом:

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

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

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. Всегда запрашивайте все столбцы

каждый раз, когда ты видишьselect *Когда пришло время посмотреть на это странным взглядом, неужели вам нужно вернуть все столбцы данных?

В большинстве случаев в этом нет необходимости.select *Это приведет к полному сканированию таблицы, что не позволит оптимизатору выполнить оптимизацию, такую ​​как сканирование индекса. Слишком большое количество столбцов также приведет к дополнительным операциям ввода-вывода, памяти и потреблению ЦП сервером. Даже если вам действительно нужно запросить все столбцы, вы должны перечислить все столбцы один за другим, а не*.

4. Повторно запрашивайте одни и те же данные

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

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

3. Проверяются ли дополнительные записи

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

• Время отклика
• количество отсканированных строк
• количество возвращенных строк

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

медленный запрос: используется для регистрации времени ответа в MySQL, превышающего пороговое значение (long_query_time, оператор по умолчанию 10s), и медленный запрос будет записан в журнал медленных запросов. Переменнаяslow_query_longЧтобы включить медленный запрос, по умолчанию он отключен, вы можете записать медленный журнал в таблицу slow_log или файл для проверки и анализа.

1. Время отклика

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

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

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

2. Количество просканированных строк и количество возвращенных строк

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

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

** В идеале количество просканированных и возвращенных строк должно совпадать. ** Но на самом деле таких красивых вещей не так много.Например, при выполнении связанного запроса отношение количества просмотренных строк к количеству возвращенных строк обычно очень мало.1:1а также10：1Между ними, но иногда это значение может быть очень большим.

3. Количество сканируемых строк и тип доступа

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

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

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

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

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