Я читал блог, написанный командой Ele.me раньше: подождите! Мы прошли эти две ямы Spring-RabbitMQ за вас. Глубоко вдохновившись, вы должны выбрать заголовок, способный привлечь внимание читателей.Конечно, помимо известного заголовка, содержание должно быть еще и сухим. Почему вы хотите взять такое название, потому что сегодня я видел теоретическую медленную оптимизацию запросов! ! ! Наконец-то в производстве

(1) Медленный sql один

поиск проблемы

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

Воспроизвести медленный sql

Выполнить sql:

select count(*) from sync_block_data
where unix_timestamp(sync_dt) >= 1539101010
AND unix_timestamp(sync_dt) <= 1539705810

Просмотр занимает много времени:Общее время — 2658 мс.
Посмотреть план выполнения:

explain select count(*) from sync_block_data
where unix_timestamp(sync_dt) >= 1539101010
AND unix_timestamp(sync_dt) <= 1539705810

Результаты выполнения плана:

Оптимизировать медленный sql one

Тип sync_dt — это тип даты и времени. Другой способ написания SQL — сравнение дат напрямую, а не временных меток. Преобразуйте метку времени в sql на сегодняшний день, 2018-10-10 00:03:30 и 2018-10-17 00:03:30 соответственно.
Выполнить sql:

select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"
AND sync_dt <= "2018-10-17 00:03:30"

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

explain select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"
AND sync_dt <= "2018-10-17 00:03:30"

Результаты выполнения плана:Посещение страницы, среднее время запроса после оптимизации составляет 900 миллисекунд.

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

Оптимизировать медленный запрос 2

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

select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"

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

explain select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"

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

Оптимизация медленного запроса 3

Создайте новое поле типа bigint sync_dt_long для хранения миллисекундного значения sync_dt и создайте индекс для поля sync_dt_long.
В тестовой среде:
Оптимизировать медленный запрос два sql

select count(*) from copy_sync_block_data
where sync_dt >="2018-10-10 13:15:02"

Занимает 34 миллисекунды
Оптимизировать медленный запрос три sql

select count(*) from copy_sync_block_data
where sync_dt_long >= 1539148502916

Занимает 22 миллисекунды
В тестовой среде скорость увеличилась примерно на 10 миллисекунд.

Оптимизация медленного запроса три сводки теста sql: в механизме хранения InnoDB эффективность сравнения bigint выше, чем datetime
После завершения трехэтапной оптимизации время запроса в производственной среде:Это снова примерно на 200 мс быстрее. При добавлении 10-секундного кэша к запрашиваемым данным скорость ответа становится самой высокой, а среднее значение составляет 20 мс.

Введение в использование объяснения

С помощью объяснения вы можете просмотреть выполнение оператора sql (например, запрошенную таблицу, используемый индекс и способ, которым mysql находит нужную строку в таблице и т. д.).
Выходные параметры использования объяснения для запроса плана запроса mysql:

имя столбца	инструкция
id	Номер выполнения идентифицирует строку, к которой принадлежит выбор. Если в операторе нет подзапросов или связанных запросов, а есть только один выбор, в каждой строке будет отображаться 1. В противном случае внутренний оператор select обычно нумеруется последовательно, в соответствии с его позицией в исходном операторе.
select_type	Показывает, является ли эта строка простой или сложной выборкой. Если запрос содержит какие-либо сложные подзапросы, самый внешний уровень помечается как ОСНОВНОЙ (ПРОИЗВОДНЫЙ, ОБЪЕДИНЕНИЕ, РЕЗУЛЬТАТ ОБЪЕДИНЕНИЯ).
table	доступ к таблице, на которую ссылаются (ссылаясь на такой запрос, как "derived3")
type	Тип операции доступа/чтения данных (ALL, index, range, ref, eq_ref, const/system, NULL)
possible_keys	Узнайте, какие индексы могут быть полезны для эффективного поиска
key	Показывает, какой индекс mysql использует для оптимизации запроса.
key_len	Отображает количество байтов, используемых mysql в индексе
ref	Показывает столбец или константу, используемую предыдущей таблицей для поиска значения в индексе записи ключевого столбца.
rows	Количество строк, которые нужно прочитать, чтобы найти нужную строку, оценка неточная. Перемножая все значения столбцов строк вместе, вы можете получить приблизительную оценку количества строк, которые будет проверять весь запрос.
Extra	Дополнительная информация, такая как использование индекса, сортировка файлов и т. д.

Сосредоточьтесь на типе, разница в типе на самом деле приводит к ухудшению производительности в шесть раз! ! !

type показывает тип доступа, что является более важным показателем.Результирующие значения от хорошего к плохому таковы: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL , Вообще говоря, необходимо убедиться, что запрос достигает как минимум уровня диапазона, предпочтительно ref.

тип	инструкция
All	В худшем случае полное сканирование таблицы
index	То же, что и полное сканирование таблицы. Просто сканируйте таблицу в порядке индекса, а не в строке. Основное преимущество состоит в том, что сортировки не требуется, но накладные расходы остаются очень высокими. Если вы видите Использование индекса в столбце Дополнительно, это означает, что используется покрывающий индекс, и сканируются только проиндексированные данные, что намного дешевле, чем полное сканирование таблицы в порядке индексов.
range	Сканирование диапазона, ограниченное сканирование индекса. Ключевой столбец показывает, какой индекс использовался. При сравнении столбцов ключевых слов с константами с помощью операторов =, , >, >=, , BETWEEN или IN можно использовать диапазон
ref	Индексированный доступ, который возвращает все строки, соответствующие одному значению. Такой доступ к индексу происходит только тогда, когда используется неуникальный индекс или уникальный неуникальный префикс индекса. Этот тип отличается от eq_ref тем, что он используется в операциях ассоциации, которые используют только крайний левый префикс индекса, либо индекс не является UNIQUE и PRIMARY KEY. ref можно использовать для индексированных столбцов с помощью операторов = или .
eq_ref	Возвращается не более одной совпадающей записи. Возникает при использовании поиска по уникальному индексу или первичному ключу (эффективно)
const	Когда определено, что совпадет не более одной строки, оптимизатор MySQL прочитает ее перед запросом и только один раз, поэтому это очень быстро. Когда первичный ключ помещается в предложение where, mysql преобразует запрос в константу (эффективную)
system	Это частный случай константного соединения, когда только одна строка таблицы удовлетворяет условию.
Null	Это означает, что mysql может разложить оператор запроса на этапе оптимизации и даже не должен обращаться к таблице или индексу на этапе выполнения (эффективно).

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

Функциональная операция используется в предложении where
Функция unix_timestamp используется в операторе SQL с медленным запросом для подсчета разницы в секундах от «1970-01-01 00:00:00» до текущего времени. Вызывает полное сканирование индекса для подсчета объема данных за последние семь дней.

решение

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

постскриптум

Эта проблема не была обнаружена в тестовой среде в то время, и скорость запросов тестовой среды все еще была приемлемой. Не быть найденным сводится к количеству данных. Объем производственных данных находится на уровне миллионов, а объем данных тестовой среды — на уровне 10 000. Объем данных в 50 раз хуже.Увеличение объема данных также усугубляет проблему медленных запросов.

(2) Медленный SQL II

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

Воспроизвести медленный sql

выполнить sql

select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539336488355 and copyright_apply_time <= 1539941288355 group by point

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

explain select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539336488355 and copyright_apply_time <= 1539941288355 group by point

Результаты выполнения плана:Индекс срабатывает, но в дополнительном поле отображаются «Использование временных файлов» и «Использование файловой сортировки».

Оптимизировать медленный sql one

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

select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539336488355 and copyright_apply_time <= 1539941288355 group by point order by null

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

Оптимизировать медленный sql два

Бизнес-логика медленного запроса SQL заключается в подсчете количества каждого периода времени в пределах диапазона условий в соответствии с классификацией периода времени.
Например, если заданный диапазон условий представляет собой отметку времени 2018-10-20~2018-10-27, этот sql будет считать приращение данных 2018-10-20~2018-10-27 каждый день. Теперь оптимизировано проверять каждый день, проверять данные семь раз по отдельности и убирать операцию группировки

select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539855067355 and copyright_apply_time <= 1539941467355 

Просмотр занимает много времени:Затратное время составляет 38 миллисекунд, даже если он проверяется 7 раз, время составляет менее 1123 миллисекунд.
Посмотреть план выполнения:По сравнению с дополнительным медленным запросом, Использование временных файлов и Использование сортировки файлов меньше в дополнительном поле. Идеально

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