предисловие

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

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

Общие шаги по оптимизации SQL

1. Найдите те операторы SQL с низкой эффективностью выполнения с помощью журналов медленных проверок и т. д.

2. Объясните, как проанализировать план выполнения SQL.

Нужно сосредоточиться на типе, строках, отфильтрованных, дополнительных.

Печатайте сверху вниз, эффективность становится все выше и выше

ВСЕ полное сканирование таблицы
индекс индекс полное сканирование
индекс диапазона сканирование диапазона, общие выражения =,между,в и другие операции
ref использует сканирование неуникального индекса или сканирование уникального префикса индекса для возврата одной записи, часто в реляционных запросах.
eq_ref похож на ref, разница в том, что он использует уникальный индекс и связанный с ним запрос с использованием первичного ключа.
const/system Для одной записи система будет рассматривать другие столбцы в соответствующей строке как константы, такие как первичный ключ или запрос уникального индекса.
null MySQL не обращается к какой-либо таблице или индексу и возвращает результат напрямую

Хотя эффективность становится все выше и выше сверху вниз, согласно стоимостной модели предполагается наличие двух показателейidx1(a, b, c),idx2(a, c), SQLselect * from t where a = 1 and b in (1, 2) order by c;Если используется idx1, то типом является диапазон, если используется idx2, то типом является ref; когда число сканируемых строк примерно в 5 раз больше, чем idx1, будет использоваться idx1, иначе будет использоваться idx2

Extra

Использование файловой сортировки: MySQL требует дополнительного прохода, чтобы выяснить, как извлекать строки в отсортированном порядке. Сортировка выполняется путем просмотра всех строк в соответствии с типом соединения и сохранения ключа сортировки и указателя строки для всех строк, соответствующих предложению WHERE. Затем ключи сортируются, и строки извлекаются в отсортированном порядке.
Использование временных: временная таблица используется для сохранения промежуточных результатов, производительность особенно низкая, и ее необходимо оптимизировать.
Использование индекса: указывает, что в соответствующей операции выбора используется покрывающий индекс (Coveing Index), чтобы избежать доступа к строкам данных таблицы, и эффективность хорошая! Если использование where появляется одновременно, это означает, что квалифицированные данные не могут быть запрошены напрямую через поиск по индексу.
Использование условия индекса: новый ICP после MySQL 5.6, использующий условие индекса, заключается в использовании ICP (проталкивание индекса) для фильтрации данных на уровне механизма хранения, а не на уровне службы, и использовании существующих данных индекса для уменьшения возвращаемой таблицы. Данные.

3. показать анализ профиля

Узнайте о состоянии потока выполнения SQL и затраченном времени. По умолчанию выключено, откройте инструкцию "set profiling = 1;"

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

4. след

Трассировка анализирует, как оптимизатор выбирает план выполнения.С помощью файла трассировки мы можем лучше понять, почему купон выбирает план выполнения A, а не план выполнения B.

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

5. Определите проблему и примите соответствующие меры

Оптимизировать индексы
Оптимизация операторов SQL: изменение SQL, сегментация запросов IN, сегментация запросов по времени, фильтрация на основе последних данных
Используйте другие способы реализации: ЭС, хранилище данных и т.п.
фрагментация данных

Анализ сценария

Случай 1. Крайнее левое совпадение

показатель

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

Оператор SQL

select * from _t where orderno=''

Сопоставление запроса выполняется слева направо. Чтобы использовать order_no для перехода к индексу, условие запроса должно содержать shop_id или index (shop_id, order_no) для обмена заказами до и после

Случай 2. Неявное преобразование

показатель

KEY `idx_mobile` (`mobile`)

Оператор SQL

select * from _user where mobile=12345678901

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

Кейс 3. Большая пагинация

показатель

KEY `idx_a_b_c` (`a`, `b`, `c`)

Оператор SQL

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

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

select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;

Случай 4. в + порядок по

показатель

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

Оператор SQL

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

Запрос in ищется по n*m в нижней части MySQL, аналогично объединению, но более эффективно, чем объединение. Когда запрос in вычисляет стоимость (стоимость = количество кортежей * среднее значение IO), количество кортежей получается путем запроса значений, содержащихся в in, по одному.

Поэтому этот процесс расчета будет относительно медленным, поэтому MySQL устанавливает критическое значение (eq_range_index_dive_limit), после 5.6 стоимость столбца не будет участвовать в расчете после превышения критического значения. В результате выбор плана выполнения будет неточным. Значение по умолчанию — 200, то есть состояние превышает 200 данных, что вызовет проблемы при расчете стоимости, что может привести к тому, что индекс, выбранный Mysql, будет неточным.

Метод обработки может поменять местами порядок до и после (order_status, created_at) и настроить SQL для задержки ассоциации.

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

показатель

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

Оператор SQL

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

Запросы диапазона также имеют «В, между»

Случай 6, не равно, не содержит быстрых поисков, которые не могут использовать индексы. (Можно использовать ICP)

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

В индексах избегайте NOT, !=, , !, NOT EXISTS, NOT IN, NOT LIKE и т. д.

Случай 7. Оптимизатор решает не использовать индекс

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

select * from _order where  order_status = 1

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

Случай 8. Сложный запрос

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

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

Случай 9, смешанное использование вознесения и убывания

select * from _t where a=1 order by b desc, c asc
desc 和asc混用时会导致索引失效

Кейс 10. Большие данные

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

На этом статья окончена.Если вы еще не поняли, то можете прочитать книгу "High Performance MySQL", электронный файл можно нажатьпорталполучать.