Краткий обзор практики настройки SQL

предисловие

Как разработчики, мы неизбежно имеем дело с sql. Некоторые SQL могут быть в начале бизнеса, и выполнение не представляет проблемы. Однако с увеличением объема бизнеса и усложнением бизнеса возможно, что предыдущий SQL постепенно начнет утомляться. В это время вы столкнетесь с настройкой sql.

Так как его настроить? У разных людей разные позы. Вероятно, первое, о чем думает большинство людей, это добавить индекс.

Да, индексация — относительно типичный и относительно недорогой метод.

Однако как увеличить индекс? добавить куда? Повлияет ли это на другие существующие SQL после его добавления? Эти вопросы необходимо учитывать.

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

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

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

Методология

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

1. Анализ белого ящика

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

2. Интерпретация плана выполнения

С помощью результатов объяснения интерпретируйте и моделируйте реальный процесс выполнения сервера sql сервером mysql.

3. Определите узкое место

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

4. Назначьте правильное лекарство

Найдя узкие места, разбивайте их одну за другой.

Далее я поделюсь с вами некоторыми советами по настройке на двух примерах (запрос к одной таблице и запрос на соединение).

Следующий процесс настройки в основном оптимизирован на уровне SQL. Не стоящий во всей системе, такой как подтаблица, переключение носителей и так далее.

боевой

Главный герой sql — это однотабличный запрос, а время его выполнения может достигать 2,3 с.

select
  很多字段
from
  t_voucher_header
where
  period_year = 2020
  and period_month = 10
  and user_je_source_name = 'xxx'
  and `status` in (10, 30, 50)
  and employee_number != '1000'
  and can_auto_push = 1
  and is_delete = 0
  and batch_id > xxx
limit
  200

sql фон

Объем данных всей таблицы составляет более 700 w, а batch_id является полем первичного ключа.

Существующие соответствующие индексы:

Совместный индекс 1: user_je_source_name, voucher_category, record_type, company_code, статус Общий индекс 2: период_год, период_месяц, user_je_source_name, ваучер_категория, is_delete, company_code, статус Совместный индекс 3: период_год, период_месяц, имя_источника_пользователя, статус

анализ белого ящика

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

1. Используете ли вы индекс для запроса к одной таблице?

2. Правильно ли используется индекс для запроса одной таблицы?

Интерпретация плана выполнения

Выше мы можем прочитать эту информацию:

1. Длина использования индекса мала, и для ускорения поиска используется только первое поле объединенного индекса.

2. Использование условия индекса — это новая функция, появившаяся после версии 5.6, и условие индекса было удалено. Ситуация такова, что индексный столбец хотя и фигурирует в условии поиска, но использовать его нельзя (сопоставление префикса). Однако, поскольку индекс содержит критерии поиска, его можно фильтровать с помощью индекса. В этом sql условиями запроса являются период_год, период_месяц, имя_источника_пользователя, статус, номер_сотрудника, can_auto_push и is_delete. Окончательный индекс: user_je_source_name, voucher_category, record_type, company_code, status. То есть, используя столбец user_je_source_name индекса, после нахождения соответствующей записи используйте статус и batch_id индекса (batch_id — первичный ключ) для фильтрации, а затем получите идентификатор первичного ключа, который удовлетворяет условиям, а затем запросите первичный индекс на основе идентификатора первичного ключа.

3. Использование, где и Использование условия индекса появляются вместе, указывая, что после того, как данные будут запрошены в соответствии с идентификатором первичного ключа и возвращены на сервер mysql, они будут отфильтрованы в соответствии с остальными условиями. С точки зрения этого sql условия user_je_source_name и статус сопоставляются на этапе использования условия индекса, а оставшиеся период_год, период_месяц, номер_сотрудника, can_auto_push и is_delete фильтруются сервером mysql на этапе использования где.

Таким образом, процесс выполнения этого SQL-запроса имеет в нашем воображении приблизительную схему, а именно:

Определение узких мест

Выполнение однотабличного запроса sql можно условно разделить на два этапа. «Поиск вторичного индекса» и «выбор таблицы возврата».

«Вторичный поиск индекса».

Есть два значения: с одной стороны, это использование вторичного индекса для его поиска, а с другой стороны, он использует индекс для фильтрации (то есть условие индекса проталкивается вниз).

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

"возврат выбора таблицы".

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

Появление проталкивания индекса для экономии затрат на возврат к таблице.

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

Возвращаясь к нашему sql, таблица явно имеет совместный индекс (period_year, period_month, user_je_source_name, status), который близко соответствует условиям запроса, а mysql — нет.Что это значит?

Это показывает, что условия period_year, period_month, user_je_source_name и status не имеют высокой степени дискриминации, реальная дискриминация на самом деле находится в employee_number, can_auto_push и is_delete!

С помощью метода управляющей переменной, сравнивая количество данных, когда employee_number, can_auto_push и is_delete принимают разные значения, было обнаружено, что когда is_delete принимает 0 и 1 соответственно, количество данных сильно различается.

Видно, что для поля is_delete при значении 1 объем данных составляет 50+, а при значении 0 объем данных составляет 1000+.

И мы хотим запросить данные is_delete=0.

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

SQL хочет запросить только 1000+ фрагментов данных, соответствующих is_delete=0, и может быть возможно получить эти данные, возвращаясь к таблице несколько раз. Но на этапе «поиск индекса» мы не можем отфильтровать поле is_delete, поэтому этап «поиск индекса» получит идентификаторы 50w+, а затем вернет идентификаторы 50w+ в таблицу. После возврата таблицы отфильтруйте поле is_delete через Использование где.

Весь процесс медленно возвращается к столу.

Назначьте правильное лекарство

Приведенный выше анализ выявил узкое место.

То есть индекс, установленный таблицей, не может эффективно отфильтровать данные перед их возвратом в таблицу, что приводит к тому, что на возврат таблицы уходит много времени. Поэтому мы добавляем столбец is_delete в существующий совместный индекс, состоящий из period_year, period_month, user_je_source_name и status. Таким образом, проблема текущего SQL может быть решена, и это не повлияет на другие существующие SQL.

После этой корректировки sql-запрос правильно достиг индекса period_year, period_month, user_je_source_name, status и is_delete, а также время сократилось с 2,3 секунды до миллисекунд.

Сравнительная диаграмма до и после оптимизации:

резюме

Бизнес-сценарий этого sql определяет, что окончательные запрашиваемые данные фиксируются как is_delete=0. Тогда, если нам нужно запросить данные is_delete=1, описанный выше метод добавления is_delete к существующему столбцу индекса не решит проблему, потому что он не может уменьшить возврат к таблице 50w+ данных is_delete=1.

На данный момент мы должны изменить направление нашего мышления: почему в бизнесе появляется так много физически удаленных данных (is_delete=1), это случайно или нормально? Если это нормально, есть ли проблема с нашей структурой таблицы, нужно ли нам разделить таблицу? и т.п.

И это именно то, что я хочу сказать, настройка sql = бизнес-сценарий + анализ выполнения sql, и то, и другое незаменимо. Без чьей-либо стороны говорить о настройке SQL не рекомендуется.

Акт II

Второй главный герой sql это:

SELECT
  b.很多字段
FROM
  t_voucher_line b
  INNER JOIN (
    SELECT
      batch_id batchId
    FROM
      t_voucher_header
    WHERE
      combine_batch_id = 2007044062
      AND is_delete = 0
  ) a ON b.batch_id = a.batchId
  AND b.is_delete = 0
WHERE
  b.segment3 LIKE '1002%'
  and b.id > 18496282
ORDER BY
  b.id ASC
LIMIT
  300

Время его выполнения 1с+.

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

Процесс подключения запроса

Само соединение является декартовым произведением.

Для простого запроса на соединение sql:

SELECT * FROM t1, t2 WHERE t1.c1 > 1 AND t1.c1 = t2.c1 AND t2.c2 < 'd';

условия можно разделить на

Условия запроса одной таблицы: t1.c1 > 1 и t2.c2

Условие многотабличного запроса: t1.c1 = t2.c1

Весь процесс подключения условно делится на следующие:

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

2. Для записей, совпавших на шаге 1, выполнить поиск данных в ведомой таблице t2 соответственно. Поскольку в ведущей таблице t1 находятся две записи, к ведомой таблице t2 выполняются два однотабличных запроса. В этот момент вступает в силу условие t1.c1 = t2.c1, включающее многотабличный запрос.

Когда t1.c = 2, t1.c1 = t2.c1 эквивалентно t2.c1 = 2. В это время условие запроса одной таблицы для таблицы t2: t2.c1 = 2, t2.c2

Когда t1.c = 3, t1.c1 = t2.c1 эквивалентно t2.c1 = 3. В это время условие запроса одной таблицы для таблицы t2: t2.c1 = 3, t2.c2

3. Объедините результаты, запрошенные на шаге 2, чтобы получить окончательный результат.

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

Вышеупомянутый метод представляет собой относительно прямой метод, просматривающий результаты шага 1 и запрашивающий управляемую таблицу один за другим (хотя индекс управляемой таблицы можно использовать для ускорения запроса управляемой таблицы), который называется вложенным. петлевое соединение (Nested-Loop Join).

Псевдокод, подобный следующему

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录       
            if row satisfies join conditions, send to client
        }
    }
}

Если m записей читаются из ведущей таблицы, количество посещений ведомой таблицы равно m раз. При каждом доступе к управляемой таблице страницы данных считываются с жесткого диска несколько раз. Если управляемая таблица разделена на n страниц данных, то для доступа к управляемой таблице требуется всего m*n чтений страниц данных, и каждая считанная страница данных является операцией ввода-вывода, что представляет собой большой объем данных. занимает очень много времени.

На основе соединения с вложенным циклом Mysql оптимизирует его по горизонтали и предлагает концепцию буфера соединения.

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

Конечно, вертикальное расширение, есть и другие методы соединения, такие как объединение слиянием (Merge Join), хеш-соединение (Hash Join). У каждого из них есть свое применение.

Соединение с вложенным циклом подходит для небольших внешних циклов и упорядоченной последовательности условий цикла памяти.Этот метод имеет низкие требования к ЦП и памяти, но высокие требования к вводу-выводу. Соединения слиянием больше подходят для сценариев, в которых оба конца соединения упорядочены (что-то вроде сортировки слиянием).Что касается требований к ЦП и памяти, то требования к вводу-выводу относительно низкие. Хэш-соединения больше подходят для больших объемов данных и отсутствия индексов.Требования к ЦП и памяти относительно высоки, а требования к IO могут быть высокими или низкими.

На основе вышеприведенного анализа продолжить оптимизацию sql по установленной методологии.

sql фон

Таблица t_voucher_header была представлена ​​в первой оптимизации SQL.

Объем данных таблицы t_voucher_line составляет 1700+, а идентификатор является первичным ключом.

Существующие связанные индексы: индекс, установленный столбцом batch_id. t_voucher_header и t_voucher_line связаны через поле batch_id, и одна запись t_voucher_header соответствует нескольким записям t_voucher_line.

анализ белого ящика

1. В sql появляются подзапросы, что приводит к потреблению временных таблиц. Отношение замены между подзапросами и запросами на соединение состоит в том, что запросы на соединение должны быть заменены подзапросами, но подзапросы не могут быть заменены запросами на соединение. Можно ли для этого sql заменить подзапрос запросом на соединение?

2. Для запроса t_voucher_header он включает в себя comb_batch_id и is_delete, а при подключении используется поле batch_id (первичный ключ). Есть ли индекс, который включает в себя comb_batch_id и is_delete, таким образом, возвращаясь к таблице?

3. Выбранные поля все из этой таблицы b, и таблица возврата к b неизбежна. Для запроса t_voucher_line он включает is_delete, segment3, id (первичный ключ), а при подключении используется поле batch_id. Есть ли в поле соединения batch_id условия для индексации? Эффективно ли использует запрос индекс? Можно ли как можно полнее охватить поля запроса?

4. Что такое выбор ведущего стола и ведомого стола?

5. Может ли операция сортировки использовать преимущества порядка индекса, чтобы избежать трудоемкой сортировки больших объемов данных?

Интерпретация плана выполнения

С приведенным выше анализом давайте посмотрим на план выполнения MySQL.

1. Таблица драйверов t_voucher_header, t_voucher_line — управляемая таблица.

2. Для запроса t_voucher_header Использование индекса указывает, что и условие запроса, и поле выбора могут соответствовать охвату индекса, и нет необходимости возвращать таблицу.

3. В запросе t_voucher_line Использование условия индекса+Использование где указывает, что по индексу выполняется только частичная фильтрация, и может быть несколько возвратов в таблицу, что может быть причиной медлительности.

4. Запрос t_voucher_header сканирует 10+ строк и возвращает их все. Кроме того, в последующих действиях упорядочены по операциям, появляются сообщения Использование временных файлов и Использование файловой сортировки, указывающие на то, что временные таблицы используются для хранения и сортировки подзапросов, которые часто занимают много времени и, скорее всего, являются причиной эта медлительность sql.

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

Определение узких мест

На данный момент процесс выполнения mysql можно в основном смоделировать следующим образом:

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

2. Стоимость возврата таблицы при обращении к ведомой таблице не минимизирована.

3. Сортировка большого количества данных занимает много времени.

Назначьте правильное лекарство

1. Эквивалент sql переписан, подзапрос становится запросом на соединение

SELECT
    b.很多字段
FROM
    t_voucher_line b
    INNER JOIN t_voucher_header a
    ON b.batch_id = a.batch_id
WHERE
    a.combine_batch_id = 2007044062
    AND a.is_delete = 0
    AND b.is_delete = 0
    AND b.segment3 LIKE '1002%'
    AND b.id > 18496282
ORDER BY
    b.id ASC
    LIMIT 300

2. Для запроса ведомой таблицы t_voucher_line задействовано относительно немного полей, помимо id первичного ключа есть поля batch_id, segment3 и is_delete, а существующий индекс idx_batch_id покрывает только batch_id. Для segment3 и is_delete нужно судить по таблице возврата. На первом этапе мы сначала расширили индекс, чтобы избежать избыточного использования таблицы возврата. Расширьте batch_id до batch_id, segment3, is_delete. Посмотрите еще раз на план выполнения sql

Ничего страшного, для запроса ведомой таблицы t_voucher_line исчезла Using where, то есть потребление возвращаемой таблицы сократилось до минимума, а также время выполнения сократилось до 0,6с.

3. Решите сортировку

Для набора результатов из запроса на соединение он естественно упорядочен по полям индекса управляющей таблицы.

Этот SQL-запрос к таблице драйверов индексируется с помощью comb_batch_id. Для поля comb_batch_id тип доступа — const, а batch_id — поле первичного ключа таблицы драйверов. Поэтому окончательный результирующий набор должен быть упорядочен в соответствии с batch_id. Связь между двумя таблицами осуществляется через batch_id. Можно ли использовать порядок batch_id, чтобы избежать сортировки?

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

Получается, что сортировка неизбежна?

Оглядываясь назад на sql, можно определить (b.id > 18496282) + (ORDER BY b.id ASC) + (ограничение 300), что целью сортировки является пейджинг по (id >?). Поскольку естественное упорядочение таблицы драйвера batch_id использовать нельзя, можно использовать метод пейджинга. То есть через метод (получить полные id)+(выбрать по id). Окончательный sql становится:

SELECT
    b.id
FROM
    t_voucher_line b
    INNER JOIN t_voucher_header a ON b.batch_id = a.batch_id
WHERE
    a.combine_batch_id = 2007044062
    AND a.is_delete = 0
    AND b.is_delete = 0
    AND b.segment3 LIKE '1002%'

В то же время соответствующий режим использования sql также изменяется синхронно. В конце концов, sql тоже сокращается с 1с до миллисекунд, лекарство для лечения.

Конечно, здесь нужно обратить внимание на один момент — max_allowed_packet, который ограничивает размер возвращаемого пакета данных между сервером и клиентом. Возьмем в качестве примера 32 МБ, первичный ключ имеет тип bigint, тогда за раз может быть возвращено около 32 МБ.10241024/8≈419w ид.

резюме

Бизнес-сценарий этого sql соответствует запуску пакетных задач.Мы можем использовать метод (получить полные идентификаторы) + (выбрать по идентификатору) без серьезных проблем.Что, если соответствующий фронтальный запрос на подкачку? Если вам нужно только запросить данные конкретной страницы, как следует адаптировать метод (получить полные идентификаторы) + (выбрать по идентификатору)?

Как и в предыдущем предложении, настройка sql = бизнес-сценарий + анализ выполнения sql, оба необходимы. Без чьей-либо стороны говорить о настройке SQL не рекомендуется.

Суммировать

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

1. Оптимизация SQL, по возможности потребление таблицы возврата нужно минимизировать.

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

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

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

5. При написании SQL вы можете следить за каким-то зрелым опытом и избежать некоторых проблем заранее. Однако система всегда движется вперед. Нельзя сказать, что один SQL можно использовать для решения различных сценариев. TUNING - это непрерывный процесс.

6. Детали важны, и причиной одного времени выполнения SQL может быть несколько деталей. 10 0,1 с — это 1 с.