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

4.1 Почему скорость запроса замедляется?

• Быстрый запрос зависит от времени отклика
• Если вы думаете о запросе как о задаче, он состоит из серии подзадач, каждая из которых требует определенного количества времени. Если вы хотите оптимизировать запрос, вы на самом деле хотите оптимизировать его подзадачи, либо исключить некоторые из них, либо уменьшить количество выполняемых подзадач, либо ускорить выполнение подзадач (иногда вам нужно изменить некоторые запросы, чтобы уменьшить влияние этих подзадач). запросы в системе. Влияние других запущенных запросов на этот раз заключается в снижении потребления ресурсов запросом).
• Жизненный цикл запроса:
  • от клиента
  • на сервер
  • На сервере:
    • Разобрать
    • Сгенерировать план выполнения
    • Исполнение: Самый ответственный этап. В том числе большое количество вызовов для извлечения данных в механизм хранения и обработки данных после вызова, включая сортировку, группировку и т.д.
  • Вернуть результат клиенту
• Выполнение этих задач требует проведения времени в разных местах. Включая сеть, вычисления ЦП, генерирование статистики и планов выполнения, ожидание блокировки (ожидание взаимного исключения) и другие операции, особенно операции вызова для извлечения данных из базового механизма хранения, эти вызовы должны быть вызваны операциями с памятью, операциями ЦП и недостаточной производительностью. памяти Время, затрачиваемое на операции ввода-вывода. В зависимости от механизма хранения также может быть много переключений контекста и системных вызовов.

4.2 Основы медленных запросов: оптимизация доступа к данным:

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

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

4.2.1 Вы запрашиваете ненужные данные из базы данных?

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

Типичный случай:

• Запрос нежелательных записей. Распространенной ошибкой является ошибочное мнение, что MySQL вернет только те данные, которые ему нужны, хотя на самом деле MySQL возвращает весь набор результатов до выполнения вычислений. Самый простой и эффективный способ — добавить к такому запросу LIMIT.
• Возвращать все столбцы, когда связано несколько таблиц: правильный способ — взять только необходимые столбцы.
• Всегда получать все столбцы:
  • Извлечение всех столбцов не позволит оптимизации выполнить оптимизацию, например сканирование покрытия, а также приведет к дополнительному потреблению ресурсов сервером. Будь осторожен.
  • Но это может упростить разработку, обеспечить возможность повторного использования одних и тех же фрагментов кода, или приложение использует какой-то механизм кэширования и т. д. и другие факторы, из-за которых необходимо получить все столбцы. Если вы знаете, как это повлияет на производительность, это также можно учитывать.
• Повторный запрос одних и тех же данных: рекомендуется кэшировать эти данные при первом запросе и извлекать их из кэша при необходимости.

4.2.2 Сканирует ли MySQL дополнительные записи?

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

• Время отклика:
  • Время ответа = время обслуживания + время ожидания. На самом деле, нет никакого способа подразделить, и нет еще никакого способа измерить это.
    • Время обслуживания: время, которое фактически потребовалось базе данных для обработки этого запроса.
    • Время ожидания: время, когда сервер фактически не выполнял запрос, потому что он ожидал какого-либо ресурса.
  • Глядя на время ответа на запрос, оцените, является ли оно разумным. В двух словах понять, какие индексы нужны для этого запроса и каков его план выполнения, затем рассчитать, сколько потребуется последовательных и случайных IO, умножить на время, затраченное на один IO при конкретных аппаратных условиях, и, наконец, положить все эти затраты в Добавить, чтобы получить эталонное значение.
• Количество просканированных строк и количество возвращенных строк
  • При анализе запроса полезно видеть количество строк, просканированных запросом. В определенной степени это может объяснить, эффективно ли запрос находит нужные данные или нет.
  • Однако эта метрика не идеальна, поскольку не все строки имеют одинаковую стоимость доступа.
  • В идеале количество просканированных и возвращенных строк должно совпадать. Но на практике он встречается редко, например связанный запрос. Отношение количества просканированных строк к количеству возвращенных строк обычно составляет от 1:1 до 10:1.
• Количество просканированных строк и тип доступа
  • При оценке накладных расходов на запрос учитывайте стоимость поиска строки данных из таблицы.
  • Тип доступа (столбец типа в операторе EXPLAIN, столбец строки показывает количество просканированных строк):
    • ref
    • ALL (полное сканирование таблицы)
  • Как MySQL применяет условие WHERE:
    • Используйте условие WHERE в индексе для фильтрации несовпадающих записей, что выполняется в механизме хранения.
    • Используйте сканирование покрывающего индекса (используя индекс в столбце EXTRA) для возврата записей, фильтруйте нежелательные записи непосредственно из индекса и возвращайте совпадения. Это делается на сервере MySQL, но нет необходимости обращаться к таблице.
    • Возвратите данные из таблицы данных, а затем отфильтруйте записи, которые не соответствуют критериям (в столбце «Дополнительно» отображается «Использование места»). Это делается на уровне сервера MySQL, где MySQL должен читать записи из таблицы, а затем фильтровать.
  • Советы по оптимизации:
    • Используйте сканирование покрытия индекса, чтобы поместить все необходимые столбцы в индекс, чтобы подсистема хранения могла вернуть результат, не возвращаясь к таблице для получения соответствующей строки.
    • Измените структуру таблицы библиотеки. например, с помощью отдельной сводной таблицы
    • Перепишите этот сложный запрос, чтобы позволить оптимизатору MySQL выполнить запрос более оптимизированным способом.

4.3 Рефакторинг метода запроса

4.3.1 Один сложный запрос или несколько простых запросов

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

4.3.2 Разделить запрос

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

4.3.3 Декомпозиция реляционных запросов

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

SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
-- 可以分解成：
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post where tag_id=1234;
SELECT * FROM post whre post.id in (123, 456);

• Преимущество:
  • Сделайте кеш более эффективным. Многие приложения могут удобно кэшировать объект результата, соответствующий одному запросу таблицы, что может уменьшить количество условий запроса. Для кеша запросов MySQL, если таблица в ассоциации изменяется, кеш запросов нельзя использовать в течение длительного времени.После разделения, если таблица редко меняется, то запрос на основе таблицы может быть повторен.Используйте результаты кеша запросов.
  • Выполнение одного запроса снижает количество конфликтов при блокировке.
  • Выполняя ассоциацию на прикладном уровне, проще разделить базу данных и легче добиться высокой производительности и масштабируемости.
  • Эффективность самого запроса также может быть повышена. Например, использование IN() вместо запроса ассоциации позволяет MySQL запрашивать в порядке идентификаторов, что может быть более эффективным, чем случайная ассоциация.
  • Запросы на избыточные записи могут быть уменьшены. Выполнение связанного запроса на уровне приложения означает, что приложению нужно запросить запись только один раз, в то время как выполнение связанного запроса в базе данных может потребовать повторного доступа к части данных. С этой точки зрения такой рефакторинг также может снизить потребление сети и памяти.
  • Идя дальше, это эквивалентно реализации хэш-ассоциаций в приложении вместо использования ассоциаций вложенных циклов MySQL. В некоторых сценариях ассоциации хэшей намного эффективнее.
• Сценарии, в которых эффективнее вводить ассоциации в приложение путем рефакторинга запроса:
  • Когда приложение может удобно кэшировать результаты запроса
  • Когда данные могут быть распределены по разным серверам MySQL
  • Когда метод IN() можно использовать вместо связанного запроса
  • Когда в запросе используется одна и та же таблица данных

4.4 Основы выполнения запросов

При отправке запроса в MySQL рабочий процесс MySQL:

​

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

4.4.1 Протокол связи клиент/сервер MySQL

• Протокол связи между клиентом и сервером является «полудуплексным», что означает, что в любой момент только один из клиентов или сервер может отправлять данные.
  • лимит:
    • Невозможно управлять потоком, как только один конец начинает отправлять сообщение, другой конец должен получить полное сообщение, чтобы ответить на него.
  • Клиент передает запрос на сервер в одном пакете. Если запрос слишком велик, сервер отклонит дополнительные данные и выдаст соответствующую ошибку в соответствии с конфигурацией max_allowed_packet.
  • Ответ сервера пользователю обычно представляет собой дополнительные данные, состоящие из нескольких пакетов. Когда сервер начинает отвечать на клиентский запрос, клиент должен получить весь возвращаемый результат целиком. Если берутся только первые несколько результатов или соединение разрывается напрямую после получения нескольких результатов, рекомендуется добавить в запрос ограничение LIMIT.
  • Большинство функций клиентской библиотеки (например, Java, Python), которые подключаются к MySQL, могут получить весь результирующий набор и кэшировать его в памяти, а также могут получить требуемые данные построчно. По умолчанию обычно получают весь набор результатов и кэшируют его в памяти.
    • MySQL обычно должен ждать, пока все данные будут отправлены клиенту, чтобы освободить ресурсы, занятые этим запросом, поэтому получение всех результатов и их кэширование может снизить нагрузку на сервер, позволить запросу завершиться раньше и освободить соответствующие ресурсы.
    • При использовании большинства библиотечных функций, которые подключаются к MySQL для получения данных из MySQL, результаты кажутся получением данных с сервера MySQL, хотя на самом деле они получают данные из кэша этой библиотечной функции. Однако при возврате большого набора результатов библиотечной функции потребуется много времени и памяти для хранения всех наборов результатов.Если обработку можно начать как можно быстрее, потребление памяти можно значительно сократить.В этом случае, кеш не может использоваться для записи результатов, но обрабатывается напрямую. Недостатком этого является то, что для сервера ресурс должен быть освобожден после завершения запроса, поэтому все ресурсы сервера заняты запросом.
  • Состояние запроса, для соединения MySQL или потока, есть состояние в любое время. Самый простой способ использованияSHOW FULL PROCESSLISTКоманда для просмотра:
    • SLEEP: поток ожидает, пока клиент отправит новый запрос
    • ЗАПРОС: поток выполняет запрос или отправляет результаты запроса клиенту
    • LOCKED: На уровне сервера MySQL поток ожидает блокировки таблицы. Блокировки, реализованные на уровне механизма хранения, такие как блокировки строк InnoDB, не отражаются в состоянии потока. Это типичное состояние для MyISAM, которое часто встречается в других механизмах, не имеющих блокировки строк.
    • Анализ и статистика: поток собирает статистику механизма хранения и генерирует план выполнения запроса.
    • Копирование во временную таблицу [на диске]: поток выполняет операцию и копирует свой набор результатов во временную таблицу.Это состояние обычно либо выполняет операцию GROUP BY, либо операцию сортировки файлов, либо операцию UNION. Если за этим статусом следует флаг «на диске», это указывает, что MySQL помещает на диск временную таблицу в памяти.
    • Результат сортировки: поток сортирует набор результатов.
    • Отправка данных. Поток может отправлять данные между несколькими состояниями, генерировать результирующий набор или возвращать данные клиенту.

4.4.2 Кэш запросов

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

4.4.3 Обработка оптимизации запросов

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

• Парсер и предварительная обработка:

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

• Оптимизатор запросов:

  • После синтаксического анализа и предварительной обработки синтаксическое дерево считается действительным и оптимизатор преобразует его в план выполнения.

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

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

    • Первоначально наименьшей единицей стоимости была стоимость случайного чтения страницы данных 4K. Позже формула расчета стоимости стала более сложной, и для оценки стоимости этих операций были введены некоторые «факторы», такие как стоимость выполнения ГДЕ условное сравнение.
    • в состоянии пройтиSHOW STATUS LIKE 'Last_query_cost';Стоимость текущего запроса для запроса текущего сеанса, значение N - оптимизатор MySQL считает, что ему нужно выполнить случайный поиск N страниц данных для завершения текущего запроса.
    • Last_query_cost рассчитывается на основе ряда статистических данных: количество страниц в таблице или индексе, кардинальность индекса (количество различных значений в индексе), длина индекса и строк данных, а также распределение индекс. Оптимизатор не учитывает уровень кэширования при оценке стоимости, он предполагает, что для чтения любых данных требуется дисковый ввод-вывод.

  • Причины, из-за которых MySQL выбирает неправильный план выполнения:

    • Статистика неточна. MySQL полагается на статистическую информацию, предоставляемую механизмом хранения, для оценки стоимости, но информация, предоставляемая некоторыми механизмами хранения, может сильно отличаться. Например, InnoDB не может вести точную статистику по количеству строк в таблице из-за своей архитектуры MVVC.
    • Оценки затрат в плане выполнения не эквивалентны фактическим затратам на выполнение. Таким образом, даже если статистика точна, план выполнения, заданный оптимизатором, может быть не оптимальным. Например, иногда план выполнения должен прочитать больше страниц, но его стоимость меньше, поскольку эти страницы могут быть прочитаны последовательно или эти страницы уже находятся в памяти, его стоимость доступа будет небольшой.
    • Оптимальность MySQL может быть не такой, как вы думаете. Возможно, вы хотите, чтобы время выполнения было как можно короче, но MySQL просто выбирает оптимальный план выполнения на основе своей модели затрат, и иногда это не самый быстрый способ выполнения. Итак, здесь мы видим, что выбор плана выполнения на основе стоимости выполнения не является идеальной моделью.
    • MySQL никогда не рассматривает другие одновременно выполняемые запросы, которые могут повлиять на скорость выполнения текущего запроса.
    • MySQL также не всегда является оптимизацией на основе затрат. Иногда он также основан на некоторых фиксированных правилах, например, если есть предложение MATCH() для полнотекстового поиска, полнотекстовый индекс используется при наличии полнотекстового индекса, даже если другие индексы и условия WHERE Чтобы быть быстрее, MySQL по-прежнему будет использовать соответствующий полнотекстовый индекс.
    • MySQL не учитывает стоимость операций, находящихся вне его контроля, таких как стоимость выполнения хранимых процедур или определяемых пользователем функций.
    • Иногда оптимизатор не может оценить все возможные планы выполнения, поэтому он может пропустить действительно оптимальный план выполнения.

  • Стратегия оптимизации:

    • Статическая оптимизация: непосредственно анализируйте дерево синтаксического анализа и выполняйте оптимизацию. Например, оптимизатор может преобразовать условие WHERE в другую эквивалентную форму с помощью некоторого простого алгебраического преобразования. Статическая оптимизация не зависит от специальных значений, таких как некоторые константы, введенные в условии WHERE. Статическая оптимизация работает после первого раза, даже если запрос повторяется с другими параметрами. Думайте об этом как об «оптимизации времени компиляции».
    • Динамическая оптимизация: это связано с контекстом и может также быть связано со многими другими факторами, такими как значение в условии WHER, количество строк данных, соответствующих записи в индексе и т. д. Это необходимо переоценивать каждый раз при выполнении запроса и даже повторно оптимизировать во время его выполнения, что можно считать «оптимизацией во время выполнения».

  • Типы оптимизаций, с которыми может справиться MySQL:

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

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

    • Используйте правила преобразования эквивалентности: MySQL использует некоторые преобразования эквивалентности для упрощения и нормализации выражений. Он может комбинировать и редуцировать некоторые сравнения, а также может удалять некоторые постоянные и некоторые постоянные суждения.

    • Оптимизация COUNT(), MIN() и MAX(). Если индексы и столбцы могут принимать значения NULL, это может помочь MySQL оптимизировать такие выражения. Например, чтобы найти минимальное значение столбца, вам нужно только запросить самую левую запись индекса B-Tree, которую MySQL может получить напрямую, и может воспользоваться этим, когда оптимизатор сгенерирует план выполнения (оптимизатор используйте это выражение как постоянную обработку, вы можете увидеть «Выбрать оптимизированные таблицы» в EXPLAIN). Точно так же запросы COUNT(*) без каких-либо условий WHERE обычно могут использовать некоторые оптимизации, предоставляемые механизмом хранения (MyISAM поддерживает переменную для хранения количества строк в таблице данных).

    • Оценка и преобразование в константные выражения: Когда MySQL обнаруживает, что выражение может быть преобразовано в константу, он всегда оптимизирует выражение как константу. Например: определяемая пользователем переменная, которая не изменяется в запросе, математические выражения, какой-либо конкретный запрос (выполнение MIN для индексированного столбца или даже оператор поиска первичного или уникального ключа), изменение постоянного значения из таблицы с помощью уравнение, переданное в другую таблицу (ограничение столбца постоянным значением с помощью WHERE, USING или ON).

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

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

    • Досрочное прекращение запроса: когда обнаруживается, что требования запроса выполнены, запрос может быть немедленно прекращен. Например, используется предложение LIMIT или обнаруживается условие, которое не выполняется (немедленно возвращается пустой результат). Когда подсистеме хранения необходимо получить "другие значения" или определить существование, например DISTINCT, NOT EXIST() или запросы типа LEFT JOIN, MySQL будет использовать этот тип оптимизации.

    • Распространение равного значения: если значения двух столбцов связаны равенством, то условие WHERE одного столбца может быть передано другому столбцу.

      SELECT film.film_id
      FROM sakila.film
          INNER JOIN sakila.film_actor USING(film_id)
      WHERE film.file_id > 500;
      -- 如果手动通过一些条件来告知优化器这个WHERE条件适用于两个表，在MySQL中反而让查询更难维护。
      ... WHERE film.file_id > 500 AND film_actor.film_id > 500;

    • Сравнение списка IN(): В отличие от других баз данных, IN() полностью эквивалентна нескольким условным операторам OR.MySQL сначала сортирует данные в списке IN(), а затем определяет, удовлетворяет ли значение списка двоичному поиску. Условия, первая сложность запроса — O(n), вторая — O(log n). Для большого количества значений скорость обработки MySQL будет выше.

• Статистика для данных и индексов:

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

• Как MySQL выполняет связанные запросы:

  • MySQL рассматривает любой запрос как управление, не ограничиваясь совпадением, требующим двух таблиц, включая каждый запрос, каждый фрагмент (например, подзапрос или даже SELECT на основе одной таблицы)
    • Пример запроса UNION: MySQL сначала помещает ряд результатов одиночного запроса во временную таблицу, а затем повторно считывает данные временной таблицы для выполнения запроса UNION. Операция чтения результатов временной таблицы также является ассоциативной.
  • Стратегия выполнения ассоциации MySQL: выполнить операцию ассоциации вложенного цикла для любой ассоциации, то есть MySQL сначала зацикливает один фрагмент данных в таблице, а затем вкладывает циклы в следующую таблицу, чтобы найти новую строку, а затем переходит до тех пор, пока не будут найдены все совпадающие строки в таблице. Затем в соответствии с совпадающими строками каждой таблицы возвращается каждый столбец, требуемый в запросе. MySQL попытается найти все совпадающие строки в последней связанной таблице.Если последняя связанная таблица не может найти больше строк, MySQL вернется к верхней связанной таблице, чтобы увидеть, можно ли найти больше совпадающих записей, и т. д. Итеративное выполнение.
    • По сути, все типы запросов работают одинаково. Например, когда MySQL встречает подзапрос в предложении FROM, он сначала выполняет подзапрос и помещает его результаты во временную таблицу (во временной таблице MySQL нет никаких индексов, как в запросе UNION), а затем использует эту временную таблицу как временная таблица. Обрабатывается как обычная таблица. Короче говоря, все типы запросов преобразуются в аналогичные планы выполнения (в MySQL 5.6 и MariaDB произошли серьезные изменения, обе версии представили более сложные планы выполнения)
    • Однако не все запросы могут быть преобразованы. Например, полное внешнее соединение невозможно выполнить с помощью вложенных циклов и поиска с возвратом, когда обнаруживается, что в связанной таблице не найдено совпадающих строк, это может быть связано с тем, что ассоциация начинается с таблицы, в которой нет совпадений. Это, вероятно, причина, по которой MySQL не поддерживает полные внешние соединения.

• План реализации:

  • MySQL создает дерево инструкций для запроса, а затем выполняет дерево инструкций через механизм хранения и возвращает результат, который отличается от других реляционных баз данных, которые генерируют байт-код запроса для выполнения запроса.
  • Окончательный план выполнения содержит всю информацию для восстановления запроса. Вы можете выполнить EXPLAIN EXTENDED для запроса, а затем выполнить SHOW WARNINGS, чтобы увидеть реконструированный запрос (та же семантика, что и исходный запрос, но оператор запроса может быть не совсем таким же)
  • Любой многотабличный запрос может быть представлен деревом, которое можно понимать как сбалансированное дерево, но план выполнения MySQL представляет собой левостороннее дерево в глубину.

• Ассоциативный оптимизатор запросов:

  • Наиболее важной частью оптимизатора MySQL является связанная оптимизация запросов, которая определяет порядок, в котором связаны несколько таблиц. Обычно, когда связано несколько таблиц, может быть несколько разных порядков связывания, и оптимизатор запросов на связывание выбирает наименее затратный порядок связывания, оценивая стоимость различных порядков.
  • Иногда оптимизатор не дает оптимальный порядок ассоциации. В этом случае вы можете использовать ключевое слово STRAIGHT_JOIN, чтобы переписать запрос и позволить оптимизатору выполнить в оптимальном порядке ассоциации, который вы считаете - в большинстве случаев оптимизатор делает выбор более точным.
  • Оптимизатор просматривает каждую таблицу как можно дольше, а затем выполняет вложенные циклы один за другим, чтобы вычислить стоимость каждого возможного дерева плана выполнения и, наконец, вернуть оптимальный план выполнения.
    • Если имеется N ассоциаций таблиц, то необходимо проверить порядок n факториальных ассоциаций. Мы называем это «пространством поиска» всех возможных планов выполнения. Если область поиска очень велика, когда таблица, которую нужно связать, превышает предел оптимизатора_поиска_глубины, оптимизатор выберет использование «жадного» метода поиска, чтобы найти «оптимальный» метод ассоциации.
  • Оптимизатор иногда выбирает неоптимальный план выполнения.
  • Иногда порядок отдельных запросов нельзя расположить произвольно, и ассоциативный оптимизатор может сильно сократить пространство поиска по этим правилам, например, левые соединения, коррелированные подзапросы. Это связано с тем, что запрос последней таблицы должен зависеть от результата запроса первой таблицы, и эта зависимость обычно может помочь оптимизатору значительно сократить количество планов выполнения, которые необходимо просмотреть.

• Оптимизация сортировки

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

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

    • Если количество данных для сортировки меньше, чем «буфер сортировки», MySQL использует память для операций «быстрой сортировки».
    • Если памяти недостаточно для сортировки, MySQL сначала разделит данные на блоки, использует «быструю сортировку» для сортировки каждого независимого блока и сохранит отсортированные результаты каждого блока на диске, затем объединит отсортированные блоки и, наконец, вернет отсортированные данные. Результаты.

  • Алгоритм сортировки:

    • Сортировка с двукратным переносом (используется в более ранних версиях): чтение указателя строки и поля для сортировки, сортировка, а затем чтение необходимой строки данных на основе результата сортировки. Для этого требуется две передачи данных, то есть данные должны быть дважды прочитаны из таблицы данных.При чтении данных во второй раз, поскольку считываются все записи, отсортированные по столбцу последовательности, это будет генерировать много случайных операций ввода-вывода, поэтому стоимость очень высока. При использовании таблиц MyISAM стоимость может быть выше, поскольку MyISAM использует системные вызовы для чтения данных (в значительной степени полагаясь на кэш данных операционной системы). Однако преимущество этого заключается в следующем: при сортировке хранить как можно меньше данных, чтобы «буфер сортировки» мог вместить как можно больше строк для сортировки.
    • Сортировка с одним переносом (используется в новой версии после 4.1): сначала считываются все столбцы, требуемые запросом, затем сортируются в соответствии с заданным столбцом и, наконец, возвращаются напрямую отсортированный результат. Для чтения всех данных без случайного ввода-вывода требуется только один последовательный ввод-вывод.Недостатком является то, что если возвращаемые столбцы очень велики, они будут занимать много места, и эти столбцы не влияют на операцию сортировки. . Поскольку одна отсортированная запись имеет большой размер, может потребоваться объединение нескольких отсортированных блоков.
    • Трудно сказать, что вышеперечисленные два алгоритма эффективнее, когда в запросе требуется, чтобы суммарная длина всех столбцов не превышала max_lenght_for_sort_data, MySQL использует «single transfer sorting», и на выбор алгоритма сортировки MySQL может влиять параметр выбор.

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

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

  • Сортировка в реляционном запросе:

    • Если все столбцы в предложении ORDER BY взяты из первой таблицы ассоциации, то MySQL упорядочивает файлы, когда ассоциация обрабатывает первую таблицу. Вы можете увидеть «Использование файловой сортировки» в поле «Дополнительно» в EXPLAIN.

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

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

4.4.4 Механизм выполнения запросов

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

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

• Фаза выполнения запроса не так уж сложна, MySQL просто выполняет шаг за шагом в соответствии с инструкциями, указанными в плане выполнения. В процессе пошагового выполнения согласно плану выполнения большое количество операций необходимо выполнить, вызвав интерфейс «handle API», реализованный механизмом хранения.

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

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

4.4.5 Возврат результата клиенту

• Последний этап выполнения запроса. Даже если запрос не должен возвращать набор результатов клиенту, MySQL все равно вернет некоторую информацию о запросе, такую ​​как количество строк, затронутых запросом.
• Если запрос можно кэшировать, то MySQL также поместит результат в кэш запроса на этом этапе.
• Возврат результирующего набора клиенту — это поэтапный процесс. Например, когда сервер завершает обработку последней связанной таблицы связанной операции и начинает генерировать первый результат, MySQL может начать постепенно возвращать результаты клиенту.
  • выгода:
    • Стороне сервера не нужно хранить слишком много результатов, и она не потребляет слишком много памяти для возврата слишком большого количества результатов.
    • Пусть клиент получит возвращенный результат как можно скорее. Можно использовать настройку SQL_BUFFER_RESULT.
  • Каждая строка в результирующем наборе будет отправлена ​​в пакете, который удовлетворяет протоколу связи клиент-сервер MySQL, а затем передана через протокол TCP.В процессе передачи TCP пакеты MySQL могут кэшироваться, а затем передаваться пакетами.

4.5 Ограничения оптимизатора запросов MySQL

Универсальный «вложенный цикл» MySQL не оптимален для каждого типа запроса, но он неприменим к небольшому числу запросов. Часто мы можем заставить MySQL завершить работу эффективно, переписав запрос. Кроме того, в версии 5.6 будут сняты многие первоначальные ограничения, что позволит максимально эффективно выполнять больше запросов.

4.5.1 Коррелированные подзапросы

Подзапросы MySQL очень плохо реализованы, и наихудший тип запросов — это подзапросы, которые содержат IN() в условном операторе WHERE.

SELECT * FROM sakila.film
WHERE film_id IN(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1 );
-- MySQL对IN()列表中的选项有专门的优化策略，但关联子查询并不是这样的,MySQL会将相关的外层表压到子查询中，它认为这样可以高效地查找到数据行。也就是说，以上查询会被MySQL更改成：
SELECT * FROM sakila.film
WHERE EXISTS(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1
    AND film_actor.film_id = film.film_id);
-- 这时子查询需要根据film_id来关联外部表的film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询。通过EXPLIAN可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY)，并且可以看到对film表进行全表扫描，然后根据返回的film_id逐个进行子查询。如果外层是一个很大的表，查询性能会很糟糕。
-- 优化重写方式1：
SELECT film.* FROM sakila.film
    INNER JOIN sakil.film_actor USING(film_id) 
WHERE actor_id =1;
-- 优化重写方式2：使用函数GROUP_CONCAT()在IN()中构造一个逗号分割的列表。
-- 优化重写方式3，使用EXISTS()等效的改写查询：
SELECT * FROM sakila.film
WHERE EXISTS(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1
    AND film_actor.film_id = film.film_id);

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

4.5.2 Ограничения UNION

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

• Если вы хотите, чтобы каждое предложение UNION принимало только часть результирующего набора в соответствии с LIMIT, или если вы хотите отсортировать результирующий набор перед объединением результирующего набора, вам необходимо использовать эти предложения в каждом предложении UNION соответственно. Кроме того, порядок выборки данных из временной таблицы не определен, если вы хотите получить правильный порядок, вам нужно добавить глобальный ORDER BY и LIMIT

  (SELECT first_name, last_name
   FROM sakila.actor
   ORDER BY last_name)
  UNION ALL
  (SELECT first_name, last_name
   FROM sakila.customer
   ORDER BY last_name)
  LIMIT 20;
  -- 在UNION子句分别使用LIMIT
  (SELECT first_name, last_name
   FROM sakila.actor
   ORDER BY last_name
   LIMIT 20)
  UNION ALL
  (SELECT first_name, last_name
   FROM sakila.customer
   ORDER BY last_name
   LIMIT 20)
  LIMIT 20;

4.5.3 Оптимизация слияния индексов

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

4.5.4 Передача по значению

• Иногда передача равного значения может привести к неожиданным дополнительным затратам. Например, имеется очень большой список IN(), и оптимизатор MySQL находит предложение WHERE, ON или USING, которое связывает значение этого списка со столбцом в другой таблице.
  • Оптимизатор скопирует список IN() в каждую связанную таблицу. Обычно оптимизатор может фильтровать записи из механизма хранения более эффективно благодаря новым условиям фильтрации для каждой таблицы. Но если список очень большой, это приведет к замедлению как оптимизации, так и выполнения.

4.5.5 Параллельное выполнение

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

4.5.6 Ассоциация хэшей

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

4.5.7 Сканирование свободного индекса

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

• Пример: предположим, что у нас есть индекс (a,b) со следующим запросомSELECT ... FROM tb1 WHERE b BETEWEEN 2 AND 3;, поскольку используется только поле b и оно не соответствует крайнему левому префиксу индекса, MySQL не может использовать этот индекс, поэтому он может найти совпадающие строки только путем полного сканирования таблицы.

  

  Если вы понимаете структуру индекса, вы обнаружите, что есть более быстрый способ выполнить вышеуказанный запрос. Физическая структура индекса (а не API подсистемы хранения) позволяет сканировать диапазон столбца b, соответствующий первому значению столбца a, а затем переходить к столбцу a и сканировать только диапазон столбца b, соответствующий первому значению. значение столбца a, т. е. свободное сканирование индекса. На данный момент нет необходимости использовать фильтрацию WHERE, поскольку все ненужные записи были пропущены.MySQL не поддерживает свободное сканирование индекса.

  

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

  -- 在Extra字段显示“Using index for group-by”,表示使用松散索引扫描
  EXPLAIN SELECT actor_id, MAX(film_id)
  FROM sakila.film_actor
  GROUP BY actor\G;

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

4.5.8 Максимальная и минимальная оптимизация

• MySQL плохо оптимизирует запросы MIN() и MAX().

  SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';
  -- 因为在first_name上没有索引，MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上当MySQL读到第一个满足条件的记录，就是需要找到的最小值，因为主键是严格按照actor_id字段的大小顺序排列的。
  -- 曲线优化办法：移除MIN()，然后使用LIMIT
  SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENNLOPE' LIMIT 1;
  -- 该SQL已经无法表达它的本意，一般我们通过SQL告诉服务器需要什么数据，再由服务器决定如何最优地获取数据。但有时候为了获得更高的性能，需要放弃一些原则。

4.5.9 Запрос и обновление в одной таблице

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

  -- 符合标准的SQL，但是无法运行
  mysql> UPDATE tbl AS outer_tbl
  -> SET cnt = (
  -> SELECT count(*) FROM tbl AS inner_tbl
  -> WHERE inner_tbl.type = outer_tbl.type
  -> );
  -- 生成表来绕过该限制：
  mysql> UPDATE tbl
  -> INNER JOIN(
  -> SELECT type, count(*) AS cnt
  -> FROM tbl
  -> GROUP BY type
  -> ) AS der USING(type)
  -> SET tbl.cnt = der.cnt;

4.6 Подсказки оптимизатора запросов

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

• Некоторые типы подсказок:

  • HIGH_PRIORITY и LOW_PRIORITY:

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

    • HIGH_PRIORITY: при использовании с оператором SELECT MySQL перепланирует этот оператор перед всеми операторами, ожидающими блокировки таблицы для изменения данных. Он фактически помещает его в начало очереди стола вместо ожидания в обычном порядке. Для операторов INSERT эффект заключается в том, чтобы просто отменить действие глобального параметра LOW_PRIORITY на этот оператор.
    • Используется для операторов SELECT, INSERT, UPDATE и DELETE, чтобы держать оператор в ожидании, пока в очереди есть другие операторы, которым требуется доступ к той же таблице, даже те, которые были отправлены на сервер позже, чем этот оператор.

  • ОТЛОЖЕННЫЙ:

    • Действительно только для INSERT и REPLACE.
    • MySQL немедленно вернет оператор, используя эту подсказку, клиенту, поместит вставленные данные строки в буфер, а затем запишет данные пакетами, когда таблица простаивает.
    • Системы ведения журналов очень полезны для использования таких подсказок или других приложений, которым необходимо записывать большие объемы данных, но клиенту не нужно ждать, пока один оператор завершит ввод-вывод.
    • Ограничение: не все механизмы хранения поддерживают это; и этот совет приведет к тому, что функция 1255628 не будет работать должным образом.

  • STRAIGHT_JOIN:

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

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

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

  • SQL_SMALL_RESULT и SQL_BIG_RESULT:

    • Действителен только для операторов SELECT, сообщает оптимизатору, как использовать временные таблицы и сортировки для запросов GROUP BY или DISTINCT.
    • SQL_SMALL_RESULT сообщает оптимизатору, что набор результатов невелик и что его можно поместить во временную индексированную таблицу в памяти, чтобы избежать операций сортировки.
    • SQL_BIG_RESULT сообщает оптимизатору, что набор результатов может быть очень большим, и рекомендуется использовать временную таблицу на диске для операций сортировки.

  • SQL_BUFFER_RESULT:

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

  • SQL_CACHE и SQL_NO_CACHE

    • Сообщает MySQL, следует ли кэшировать этот набор результатов в кэше запросов.

  • SQL_CALC_FOUND_ROWS:

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

  • ДЛЯ ОБНОВЛЕНИЯ И БЛОКИРОВКИ В РЕЖИМЕ ОБМЕНА

    • Не совсем намек оптимизатора либо.
    • В основном он управляет механизмом блокировки оператора SELECT, но он действителен только для механизмов хранения, реализующих блокировку на уровне строк.
    • Строки данных, соответствующие условиям запроса, будут заблокированы. Эти два приглашения не нужны для операторов INSERT...SELECT, поскольку версии 5.0 и более новые будут блокироваться по умолчанию.
    • InnoDB — единственный движок со встроенной поддержкой обеих подсказок. Но эти две подсказки могут помешать правильной работе некоторых оптимизаций, таких как сканирование покрытия индекса. InnoDB не может заблокировать строку исключительно без доступа к первичному ключу, потому что информация о версии строки хранится в первичном ключе.
    • Этими двумя подсказками часто злоупотребляют, и они могут легко вызвать конфликты блокировок на сервере, и их следует по возможности избегать.

  • ИСПОЛЬЗУЯ ИНДЕКС, ИНДЕКС IGONRE и ИНДЕКС СИЛА:

    • Сообщает оптимизатору, какие индексы использовать или не использовать для запроса записей (например, какие индексы использовать при определении порядка ассоциаций).
    • 5.1 и более новые версии могут использовать FOR ORDER BY и FOR GROUP BY, чтобы указать, допустимо ли это для сортировки и группировки.
    • USING INDEX и FORCE INDEX в основном одинаковы. Но FORCE INDEX сообщит оптимизатору, что стоимость полного сканирования таблицы будет намного выше, чем стоимость сканирования индекса, даже если индекс на самом деле бесполезен. Эту подсказку можно использовать, когда обнаруживается, что оптимизатор выбрал неправильный индекс или по какой-то причине (например, хочет получить упорядоченные результаты, когда ORDER BY неприменим) для использования другого индекса.

• 5.0 и новее новые параметры для управления поведением оптимизатора:

  • optimizer_search_depth: контролирует предел оптимизатора при исчерпывающем выполнении плана. Если запрос находится в состоянии "Статистика" в течение длительного времени, рассмотрите возможность его понижения.
  • optimizer_prune_level: включено по умолчанию, позволяет оптимизатору решать, следует ли пропускать определенные планы выполнения в зависимости от количества строк, которые необходимо отсканировать.
  • optimizer_swith: содержит некоторые флаги, которые включают/отключают функции оптимизатора.

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

4.7.1 Оптимизация запросов count()

• Роль count():

  • Подсчитайте количество значений столбца, требующих, чтобы значение столбца было ненулевым (не учитывайте NULL). Если в скобках функции COUNT() указан столбец или выражение столбца, счетчик представляет собой количество результатов со значением для этого выражения (не NULL).
  • Считать ряды. Когда MySQL подтверждает, что значение выражения в круглых скобках не может быть пустым, он фактически подсчитывает количество строк.
    • При использовании СЧЁТ()Время,Он не распространяется на все столбцы, как мы догадались, на самом деле он игнорирует все столбцы и считает все строки напрямую.
    • Распространенная ошибка: указание столбца в круглых скобках и желание подсчитать количество строк в результирующем наборе.

• Мифы о MyISAM:

  • Только count(*) без какого-либо условия WHERE, функция COUNT() MyISAM будет очень быстрой, потому что на самом деле нет необходимости подсчитывать количество строк в таблице. MySQL может использовать характеристики механизма хранения, чтобы получить это значение напрямую.
  • Если MySQL знает, что столбец col не может быть значением NULL, он внутренне оптимизирует count(col) до count(*)
  • Когда в статистике есть предложение WHERE, COUNT() MyISAM ничем не отличается от других механизмов хранения, и больше нет мифической скорости.

• простая оптимизация

  • Воспользуйтесь очень быстрой функцией MyISAM в полной таблице count(*), чтобы ускорить запросы для некоторых конкретных условий.

    -- 使用标准数据据worold
    SELECT count(*) FROM world.city WHERE ID > 5;
    -- 将条件反转，可很大程度减少扫描行数到5行以内
    SELECT (SELECT count(*) FROM world.city) - COUNT(*) 
    FROM world.city WHERE ID <= 5;

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

    -- 使用SUM
    SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;
    -- 使用COUNT，只需要将满足条件的设置为真，不满足设置为NULL
    SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULLASred FROM items;

• Используйте приближение:

  • Иногда в некоторых бизнес-сценариях не требуется абсолютно точное значение COUNT, и вместо него можно использовать приблизительное значение.
  • Количество строк, оцененное оптимизатором из EXPLAIN, является хорошим приближением.Выполнение EXPLAIN не требует фактического выполнения запроса, поэтому стоимость очень низкая.
  • Например, если вы хотите точно подсчитать количество людей онлайн на сайте, условие WHERE обычно очень сложное: с одной стороны, вам нужно отфильтровать текущих неактивных пользователей, а с другой стороны, вам нужно отфильтровать пользователей по умолчанию с некоторыми конкретными идентификаторами в системе и устранить влияние этих ограничений на общее количество.Небольшой, но вполне может улучшить производительность этого запроса. Дальнейшая оптимизация может попытаться удалить ограничения, такие как DISTINCT, чтобы избежать сортировки файлов. Запрос, переписанный таким образом, намного быстрее, чем точный запрос, и возвращает почти те же результаты.

• Более сложные оптимизации:

  • Как правило, COUNT() необходимо сканировать большое количество строк (имеется в виду доступ к большому объему данных) для получения точных результатов, поэтому его трудно оптимизировать.
  • Оптимизация:
    • ранее упомянутый метод
    • Единственное, что можно сделать на уровне MySQL — это сканирование покрытия индекса.
    • Рассмотрите возможность изменения архитектуры приложения, добавления сводных таблиц или внешней системы кэширования, такой как Memcached. Вскоре вы можете оказаться в знакомом затруднительном положении: «быстро, точно и легко реализовать», три всегда могут удовлетворить только двоих, а один должен быть отброшен.

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

  • Убедитесь, что в столбце предложения ON или USING есть индекс.
    • При создании индекса необходимо учитывать порядок ассоциаций. Когда таблица A и таблица B связаны со столбцом c, порядок ассоциации оптимизатора совместного использования будет B и A, поэтому нет необходимости создавать индекс для соответствующего столбца таблицы B. Никакая мудрость индексации не сопряжена с дополнительным бременем.
    • В общем, если у вас нет других причин, вам нужно только создать индекс для столбца ответа второй таблицы в порядке ассоциации.
  • Убедитесь, что любые выражения в GROUP BY и ORDER BY включают только столбцы в одной таблице, чтобы MySQL потенциально мог использовать индексы для оптимизации процесса.
  • При обновлении MySQL обратите внимание на синтаксис ассоциации, приоритет операторов и другие места, которые могут измениться. Поскольку то, что раньше было обычной ассоциацией, может стать декартовым произведением, разные типы ассоциаций могут давать разные результаты.

4.7.3 Оптимизация подзапросов

• Вместо этого используйте связанные запросы, где это возможно. Это не абсолютно, 5.6 и более новые версии или MariaDB, вы можете использовать подзапросы напрямую.

4.7.4 Оптимизация GROUP BY и DISTINCT

• Во многих случаях MySQL оптимизирует оба типа запросов одинаково, фактически оптимизатор MySQL внутренне преобразует два типа запросов во время обработки. Все они могут быть оптимизированы с помощью индексов, что является наиболее эффективным способом оптимизации.
• Когда нельзя использовать индексы, GROUP BY использует две стратегии: использовать временные таблицы или сортировку файлов для группировки. Вы можете заставить оптимизатор вести себя так, как вам нужно, указывая SQL_BIG_RESULT и SQL_SMALL_RESULT.
• Если вам нужно сгруппировать по GROUP BY для связанного запроса и сгруппировать по столбцу в таблице поиска, то эффективность группировки по столбцу идентификаторов таблицы поиска обычно выше, чем у других столбцов.
  • Негруппирующий столбец, который появляется после SELECT, должен напрямую зависеть от группирующего столбца, а значение в каждой группе уникально, иначе бизнесу все равно, какое это значение.
  • Как правило, не рекомендуется использовать несгруппированные столбцы непосредственно в SELECT сгруппированного запроса, поскольку такие результаты обычно неопределенны и могут отличаться при изменении индекса или при выборе оптимизатором другой стратегии оптимизации.
• Если столбец сортировки не указан явно в предложении ORDER BY, когда в запросе используется предложение GROUP BY, результирующий набор автоматически сортируется по полю группировки. Если вас не волнует порядок набора результатов, и этот порядок по умолчанию приводит к необходимости сортировки файлов, вы можете использовать ORDER BY NULL, чтобы запретить MySQL сортировать файлы.
• Оптимизируйте GROUP BY WITH ROLLUP:
  • Вариант сгруппированного запроса требует, чтобы MySQL выполнял суперагрегацию возвращаемых сгруппированных результатов. Эту логику можно реализовать с помощью предложения WITH ROLLUP, но это может оказаться недостаточно оптимальным.
    • Во многих случаях лучше, если вы можете выполнять суперагрегации в своем приложении, хотя это требует возврата клиенту большего количества результатов.
    • Вы также можете использовать подзапросы, вложенные в предложение FROM, или хранить промежуточные данные через временную таблицу, а затем выполнять UNION с временной таблицей, чтобы получить окончательный результат.
    • Лучший способ — максимально перенести функцию WITH ROLLUP в приложение.

4.7.5 Оптимизация пейджинга LIMIT

• Когда в системе требуется операция подкачки, обычно используется метод LIMIT плюс смещение, и в то же время добавляется соответствующее предложение ORDER BY. При наличии соответствующего индекса эффективность обычно будет очень хорошей, в противном случае MySQL необходимо выполнять множество операций по сортировке файлов.
• Метод оптимизации при очень большом смещении:
  • Ограничьте количество страниц на странице
  • Оптимизация производительности для больших смещений:
    • По возможности используйте сканирование покрывающего индекса вместо запроса всех столбцов. Например, используя отложенную ассоциацию
    • Иногда можно преобразовать запрос LIMIT в запрос известных местоположений и позволить MySQL получить соответствующие результаты посредством сканирования диапазона.
    • Используйте «Закладки», чтобы записать место, откуда в последний раз были извлечены данные.
    • Используйте предварительно вычисленную сводную таблицу или свяжите ее с избыточной таблицей, которая содержит только столбец первичного ключа и столбец данных, который необходимо отсортировать.

4.7.6 Оптимизация SQL_CALC_FOUND_ROWS

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

• Схема проектирования 1: измените конкретное количество страниц на кнопку «следующая страница», предполагая, что на каждой странице отображается 20 записей, затем используйте LIMIT, чтобы вернуть 21 запись и отобразить только 20 записей, если существует 21-я запись, затем отобразить «следующая страница». « », иначе это означает, что данных больше нет, поэтому нет необходимости отображать
• Схема проектирования 2: сначала получить и кэшировать больше данных, а затем получать их из этого кэша для каждой подкачки.
• Другие дизайнерские решения: иногда вы также можете рассмотреть возможность использования значения столбца rows в результате EXPLAIN в качестве приблизительного значения общего количества наборов результатов (на самом деле общее количество результатов поиска Google также является приблизительным значением). Когда требуются точные результаты, для удовлетворения требований используется только COUNT(*).В настоящее время, если можно использовать покрывающий индекс, он обычно намного быстрее, чем SQL_CALC_FOUND_ROWS.

4.7.7 Оптимизация запросов UNION

• MySQL всегда выполняет запросы UNION, создавая и заполняя временные таблицы. Поэтому многие стратегии оптимизации не могут быть использованы надлежащим образом. Часто бывает необходимо вручную протолкнуть предложения WHERE, LIMIT, ORDER BY в каждый подзапрос UNION, чтобы оптимизатор мог в полной мере использовать эти условия для оптимизации.
• Если вам действительно не нужно, чтобы сервер удалял повторяющиеся строки, вы должны использовать UNION ALL.
  • Если нет ключевого слова ALL, MySQL добавит к временной таблице параметр DISTINCT, что приведет к уникальной проверке всей временной таблицы, что очень дорого.
  • Даже с ключевым словом ALL MySQL по-прежнему будет использовать временные таблицы для хранения результатов.

4.7.8 Статический анализ запросов

Percona Toolkit contains pt-query-advisor, a tool that parses a log of queries, analyzes
the query patterns, and gives annoyingly detailed advice about potentially bad practices
in them.

4.7.9 Использование пользовательских переменных

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

• Инструкции:

  SET @one       := 1;
  SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
  SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK;
  SELECT ... WHERE col <= @last_week;
  -- 具有“左值”特性，在给一个变量赋值的同时使用这个变量
  SELECT actor_id, @rownum := @rownum + 1 As rownum ...

• Недоступные сценарии:

  • Запрос с использованием пользовательских переменных, не может использовать кеш запроса
  • Пользовательские переменные нельзя использовать там, где используются константы или идентификаторы, такие как имена таблиц, имена столбцов и предложения LIMIT.
  • Жизненный цикл пользовательских переменных действителен в соединении, поэтому их нельзя использовать для связи в соединении.
  • При использовании пула подключений или постоянных подключений пользовательские переменные могут позволить взаимодействовать, казалось бы, несвязанному коду (если это так, обычно это ошибка кода или пула подключений).
  • Версии до 5.0 чувствительны к регистру, поэтому обратите внимание на совместимость кода между разными версиями MySQL.
  • Вы не можете явно объявить тип пользовательской переменной. Время определения точного типа неопределенной переменной также может различаться в разных версиях MySQL. Если вам нужно целое число/число с плавающей запятой/строка, лучше инициализировать с помощью 0/0.0/''. Пользовательская переменная MySQL — это динамический тип, который изменяется при назначении.
  • Оптимизатор MySQL может оптимизировать эти переменные в определенных сценариях, что может привести к тому, что код будет вести себя не так, как ожидалось.
  • Порядок присвоений и сроки присвоений не всегда фиксированы и зависят от решения оптимизатора. Реальная ситуация может сбить с толку.
  • Символ присваивания := имеет очень низкий приоритет, поэтому имейте в виду, что выражения присваивания должны использовать явные круглые скобки.
  • Использование неопределенных переменных не приводит к синтаксическим ошибкам, и очень легко сделать ошибку, если вы этого не заметите.

• Сценарии применения:

  • Заявление об оптимизации ранжирования:

    -- 查询获取演过最多电影的前10位演员，然后根据出演电影次数做一个排名，如果出演次数一样，则排名相同。
    mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
    -> SELECT actor_id,
    -> @curr_cnt := cnt AS cnt,
    -> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
    -> @prev_cnt := @curr_cnt AS dummy
    -> FROM (
    -> SELECT actor_id, COUNT(*) AS cnt
    -> FROM sakila.film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10
    -> ) as der;

  • Избегайте повторного запроса только что обновленных данных:

    -- 在更新行的同时又希望获取得到该行的信息。虽然看起来仍然需要两个查询和两次网络来回，但第二个查询无须访问任何数据表，速度会快很多
    UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
    SELECT @now;

  • Подсчитайте количество обновлений и вставок

    -- 使用了INSERT ON DUPLICATE KEY UPDATE的时候，想统计插入了多少行的数据，并且有多少数据是因为冲突而改写成更新操作。
    -- 实现该办法的本质如下，当每次由于冲突导致更新时对变量@x自增一次，然后通过对这个表达式乘以0来让其不影响要更新的内容
    INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
    ON DUPLICATE KEY UPDATE
    c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );

  • Определить порядок значений

    • Одна из самых частых проблем, не замечание использования переменных при присваивании и чтении может быть на разных этапах выполнения запроса.

      -- WHERE和SELECT是在查询执行的不同阶段被执行的，而WHERE是在ORDER BY文件排序操作之前执行。
      mysql> SET @rownum := 0;
      mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
      -> FROM sakila.actor
      -> WHERE @rownum <= 1;
      +----------+------+
      | actor_id | cnt  |
      +----------+------+
      | 1        | 1    |
      | 2        | 2    |
      +----------+------+

    • Постарайтесь, чтобы назначение и извлечение переменных происходило на одной фазе выполнения запроса.

      mysql> SET @rownum := 0;
      mysql> SELECT actor_id, @rownum AS rownum
      -> FROM sakila.actor
      -> WHERE (@rownum := @rownum + 1) <= 1;

    • Поместите пробег задания в НАИМЕНЬШИЙ(), чтобы задания можно было выполнять без изменения порядка сортировки вообще. Этот метод полезен, когда вы не хотите влиять на результат выполнения предложения, но хотите завершить копирование переменной. Такими функциями являются НАИБОЛЬШИЙ(), ДЛИНА(), ISNULL(), NULLIF(), IF() и COALESCE().

      -- LEAST()总是返回0
      mysql> SET @rownum := 0;
      mysql> SELECT actor_id, first_name, @rownum AS rownum
      -> FROM sakila.actor
      -> WHERE @rownum <= 1
      -> ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);

• Напишите ленивый UNION:

  Предположим, вам нужно написать запрос UNION, в котором первый подзапрос выполняется первым как условие ветвления, и если найдена соответствующая строка, вторая ветвь пропускается. В некоторых бизнес-сценариях такой спрос действительно есть, например, сейчас вы ищете «горячие» данные в часто используемой таблице, а если не можете их найти, то можете перейти в другую менее посещаемую таблицу, чтобы найти «холодные данные». (Различение «горячих» и «холодных» данных — хороший способ улучшить частоту попаданий в кэш).

  -- 在两个地方查找一个用户，一个主用户表，一个长时间不活跃的用户表，不活跃的用户表的目的是为了实现更高效的归档。
  -- 旧的UNION查询，即使在users表中已经找到了记录，上面的查询还是会去归档表中再查找一次。
  SELECT id FROM users WHERE id = 123
  UNION ALL
  SELECT id FROM users_archived WHERE id = 123;
  -- 用一个偷懒的UINON查询来抑制这样的数据返回，当第一个表中没有数据时，我们才在第二个表中查询。一旦在第一个表中找到记录，就定义一个变量@found，通过在结果列中做一次赋值来实现，然后将赋值放在函数GREATEST中来避免返回额外的数据。为了明确结果来自哪一个表，新增了一个包含表名的列。最后需要在查询的末尾将变量重置为NULL，保证遍历时不干扰后面的结果。
  SELECT GREATEST(@found := −1, id) AS id, 'users' AS which_tbl
  FROM users WHERE id = 1
  UNION ALL
  SELECT id, 'users_archived'
  FROM users_archived WHERE id = 1 AND @found IS NULL
  UNION ALL
  SELECT 1, 'reset' FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;

• Другое использование пользовательских переменных:

  • Переменным можно присваивать значения не только в операторах SELECT, но и в операторах SQL любого другого типа. Например, оператор UPDATE можно улучшить так же, как оператор ранжирования был улучшен с помощью подзапросов.
  • Иногда необходимо использовать некоторые хитрости, чтобы получить желаемый результат. Потому что оптимизатор может рассматривать переменную как константу времени компиляции вместо того, чтобы присваивать ей значение. Помещение функции в такую ​​функцию, как LEAST(), обычно позволяет избежать таких проблем. Другой способ — проверить, присвоено ли переменной значение до выполнения запроса.

• Другое использование:

  • Вычислять итоги и средние значения во время выполнения запроса
  • Имитация функций FIRST() и LAST() в операторе GROUP.
  • Выполните некоторые расчеты данных на большом количестве данных.
  • Вычислить хэш MD5 большой таблицы
  • Напишите функцию обработки выборки, которая изменяет значение на 0, когда значение в выборке превышает определенное граничное значение.
  • Эмулировать курсоры чтения/записи
  • Добавьте значения переменных в предложение WEHRE оператора SHOW.

4.8 Тематическое исследование

4.8.1 Создание таблицы очереди с помощью MySQL

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

Причины, по которым оригинальный метод обработки не подходит:

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

процесс оптимизации:

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

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

  • Можно использовать аннотированную функцию SLEEP() для обработки времени ожидания. Это удерживает поток заблокированным до тех пор, пока тайм-аут или другой поток не завершит текущий SLEEP с помощью KILL QUERY. Таким образом, после добавления пакета данных в список пар, вы можете передатьSHOW PROCESSLIST, найдите поток, который в данный момент находится в спящем режиме, согласно комментарию, и УБЕЙТЕ его. Уведомления могут быть реализованы с помощью функций GET_LOCK и RELEASE_LOCK() или могут быть реализованы вне базы данных, например, с помощью службы сообщений.

    SELECT /* waiting on unsent_emails */ SLEEP(10000), col1 FROM table;

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

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

  • Вы можете использовать UPDATE напрямую, чтобы обновить запись, а затем проверить, есть ли другие записи для обработки. (Все SELECT FOR UPDATE можно переписать аналогичным образом)

    -- 该表的owner用来存储当前正在处理这个记录的连接ID，即由函数CONNECTION_ID()返回额ID，如果当前记录没有被任何消费者处理，则该值为0
    CREATE TABLE unsent_emails (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        -- columns for the message, from, to, subject, etc.
        status ENUM('unsent', 'claimed', 'sent'),
        owner INT UNSIGNED NOT NULL DEFAULT 0,
        ts TIMESTAMP,
        KEY (owner, status, ts)
    );
    -- 常见的处理办法。这里的SELECT查询使用到索引的两个列，理论上查找的效率应该更快。问题是，两个查询之间的“间隙时间”，这里的锁会让所有其他同一的查询全部被阻塞。所有这样的查询将使用相同的索引，扫描索引相同结果的部分，所以很可能被阻塞。
    BEGIN;
    SELECT id FROM unsent_emails
        WHERE owner = 0 AND status = 'unsent'
        LIMIT 10 FOR UPDATE;
    -- result: 123, 456, 789
    UPDATE unsent_emails
        SET status = 'claimed', owner = CONNECTION_ID()
        WHERE id IN(123, 456, 789);
    COMMIT;
    -- 改进后更高效的写法,无须使用SELECT查询去找到哪些记录还没有被处理。客户端的协议会告诉你更新了几条记录，所以可以直到这次需要处理多少条记录。
    SET AUTOCOMMIT = 1;
    COMMIT;
    UPDATE unsent_emails
        SET status = 'claimed', owner = CONNECTION_ID()
        WHERE owner = 0 AND status = 'unsent'
        LIMIT 10;
    SET AUTOCOMMIT = 0;
    SELECT id FROM unsent_emails
        WHERE owner = CONNECTION_ID() AND status = 'claimed';
    -- result: 123, 456, 789

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

  • Просто периодически запускайте оператор UPDATE, чтобы обновить его до исходного состояния, затем выполните SHOW PROCESSLIST, чтобы получить идентификатор потока, который в настоящее время работает, и используйте некоторые условия WHERE, чтобы избежать получения тех процессов, которые только что начали обработку.

    -- 假设获取的线程ID有(10、20、30),下面的更新语句会将处理时间超过10分钟的记录状态更新成初始状态。
    -- 将范围条件放在WHERE条件的末尾，这个查询恰好能勾使用索引的全部列，其它的查询也都能使用上这个索引，这样就避免了再新增一个额外的索引来满足其它的查询
    UPDATE unsent_emails
        SET owner = 0, status = 'unsent'
      WHERE owner NOT IN(0, 10, 20, 30) AND status = 'cla
        AND ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;

Некоторые основные принципы в этом случае:

• Делайте как можно меньше и ничего не делайте, если можете. Не используйте опрос, если в этом нет необходимости, так как это увеличит нагрузку и приведет к большому количеству малопродуктивной работы.
• Делайте то, что нужно сделать как можно быстрее. Попробуйте использовать UPDATE вместо SELECT FOR UPDATE, а затем UPDATE, потому что чем быстрее отправляется транзакция, тем меньше время удержания блокировки, что может значительно снизить конкуренцию и повысить эффективность последовательного выполнения. Разделяйте обработанные и необработанные данные, чтобы набор данных был достаточно небольшим.
• Еще одним источником вдохновения для этого случая является то, что некоторые запросы нельзя оптимизировать; рассмотрите возможность использования другого запроса или другой стратегии для достижения той же цели. Обычно это требуется для SELECT FOR UPDATE

Иногда лучше всего перенести очередь задач из базы данных, Redis и memcached — хороший контейнер очереди.

6.8.2 Расчет расстояния между двумя точками

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

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

CREATE TABLE locations (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  lat FLOAT NOT NULL,
  lon FLOAT NOT NULL
);
INSERT INTO locations(name, lat, lon)
  VALUES('Charlottesville, Virginia', 38.03, −78.48),
  ('Chicago, Illinois', 41.85, −87.65),
  ('Washington, DC', 38.89, −77.04);

Предполагая, что Земля круглая, используйте формулу наибольшего круга (гаверсинуса), где лежат две точки, чтобы вычислить расстояние между двумя точками. При существующих координатах latA и lonA, latB и lonB формула для расчета расстояния между точкой A и точкой B выглядит следующим образом:

ACOS(
COS(latA) * COS(latB) * COS(lonA - lonB)
+ SIN(latA) * SIN(latB)
)

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

SELECT * FROM locations WHERE 3979 * ACOS(
  COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;

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

Оптимизированное место:

• Посмотрите, действительно ли нужен такой точный расчет. На самом деле в алгоритме уже много неточностей:

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

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

• Чтобы понять, действительно ли вам нужно вычислять длину окружности, рассмотрите возможность использования вместо нее квадрата. Для квадрата со стороной 200 миль расстояние от вершины до центра составляет около 141 мили, что не слишком далеко от фактически рассчитанных 100 миль. Рассчитайте межосевое расстояние для 0,0253 радиана (100 миль) по квадратной формуле:

  SELECT * FROM locations
    WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
    AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253);

Теперь посмотрим, как индекс можно использовать для оптимизации этого запроса:

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

Добавьте два новых столбца для хранения аппроксимации координат FLOOR(), затем используйте IN() в запросе, чтобы поместить целочисленные значения всех точек в список:

mysql> ALTER TABLE locations
-> ADD lat_floor INT NOT NULL DEFAULT 0,
-> ADD lon_floor INT NOT NULL DEFAULT 0,
-> ADD KEY(lat_floor, lon_floor);

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

-- 查询某个范围的所有点，数值需要在应用程序中计算而不是MySQL
mysql> SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb,
-> CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub,
-> FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb,
-> CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub;
+--------+--------+--------+--------+
| lat_lb | lat_ub | lon_lb | lon_ub |
+--------+--------+--------+--------+
| 36     | 40     | −80    | −77    |
+--------+--------+--------+--------+
-- 生成IN()列表中的整数：
SELECT * FROM locations
  WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
  AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
  AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);

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

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

SELECT * FROM locations
  WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
  AND 3979 * ACOS(
  COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;

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

Стратегия оптимизации, используемая в этом случае:

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

4.8.3 Использование пользовательских функций

• C и C++ — хороший выбор, если вам нужна более высокая скорость, когда операторы SQL больше не могут эффективно выполнять некоторые задачи.
• Случай: вам нужно вычислить значение XOR двух случайных 64-битных чисел, чтобы увидеть, совпадают ли эти два значения. Около 35 миллионов записей необходимо выполнить за секунды.
  • После несложного расчета становится известно, что при нынешних аппаратных условиях выполнить в MySQL невозможно.
  • Напишите программу расчета, которая работает на том же сервере, что и фоновая программа, а затем напишите определяемую пользователем функцию для взаимодействия с предыдущей программой через простой сетевой протокол связи. Запуск программы в распределенном режиме может выполнить 4 миллиона сопоставлений за 130 миллисекунд. Таким образом, интенсивные вычисления могут быть размещены на некоторых серверах общего назначения, при этом будучи полностью прозрачными для внешнего мира, создается впечатление, что MySQL проделал всю работу.

4.9 Резюме

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

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

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