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

• избегать выбора *;
• присоединиться к полю, чтобы перейти к индексу;
• Используйте in и not in с осторожностью и замените in на exists;
• Избегайте функциональных операций с полями в предложениях where;
• Старайтесь не обновлять кластеризованный индекс;
• Если группировать по не нужно сортировать, вручную добавить порядок по нулю;
• join выбирает небольшую таблицу в качестве ведущей таблицы;
• Порядок по полю должен максимально соответствовать индексу...

Некоторые из этих средств могут устареть после обновления версии MySQL. Нам действительно нужно поддерживать эти методы настройки? Я не думаю, что нужно осваиватьMySQL存储架构иSQL执行原理В этом случае мы, естественно, понимаем, почему мы предлагаем такую ​​оптимизацию, и даже можем найти необоснованные методы оптимизации, упомянутые другими.

существуетВзгляд на базовую архитектуру MySQL: перемещение между буфером и дискомВ этой статье мы представили архитектуру хранилища MySQL, подробно описанную для вас в MySQL.存储,索引,缓冲,IOВ соответствующем опыте тюнинга есть определенные факты.

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

Если нет специальной инструкции, в этой статье в качестве объяснения и демонстрации используется версия MySQL 5.7.

Прочитав эту статью, вы будете знать:

• СЧИТАТЬ:В чем разница между счетчиками, обрабатываемыми механизмами хранения MyISAM и InnoDB?
• СЧИТАТЬ:Почему у count плохая производительность?
• СЧИТАТЬ:Какие существуют способы записи счетчика и как ускорить подсчет статистики?
• СОРТИРОВАТЬ ПО:Каковы режимы сортировки порядка по оператору, а также преимущества и недостатки каждого режима сортировки?
• СОРТИРОВАТЬ ПО:Какие алгоритмы сортировки будут использоваться в порядке следования операторов, и какой алгоритм сортировки будет выбран в каких сценариях
• СОРТИРОВАТЬ ПО:Как посмотреть и проанализировать порядок sql по методу оптимизации (план выполнения + лог OPTIMIZER_TRACE)
• СОРТИРОВАТЬ ПО:Как оптимизировать эффективность выполнения заказа по оператору? (идея: уменьшите размер запроса строки, попробуйте использовать индекс, лучше всего использовать покрывающий индекс, и размер памяти буфера сортировки можно соответствующим образом увеличить)
• ПРИСОЕДИНИТЬСЯ:Как выполняется объединение при взятии индекса?
• ПРИСОЕДИНИТЬСЯ:Как выполняется соединение без индексации?
• ПРИСОЕДИНИТЬСЯ:Какую оптимизацию выполняет MySQL для соединения индекса с вложенным циклом? (ММР, БКА)
• ПРИСОЕДИНИТЬСЯ:Какое влияние алгоритм BNL оказывает на кэширование? Каковы стратегии оптимизации?
• ПРИСОЕДИНИТЬСЯ:Каковы наиболее часто используемые операторы соединения?
• ПРИСОЕДИНИТЬСЯ:Каковы методы оптимизации для операторов соединения?
• СОЮЗ:Как работает заявление профсоюза?
• СОЮЗ:Как происходит дедупликация объединения?
• ГРУППА ПО:Каков план выполнения, когда группа полностью проиндексирована?
• ГРУППА ПО:При каких обстоятельствах группировка по использованию временных таблиц? При каких обстоятельствах будет использоваться временная таблица + сортировка?
• ГРУППА ПО:Любые предложения по оптимизации для группы по?
• ОТЧЕТЛИВЫЙ:Каков принцип реализации отдельного ключевого слова?
• Подзапрос:Каковы некоторые распространенные способы использования подзапросов?
• Подзапрос:Каковы общие оптимизации подзапросов?
• Подзапрос:Вы действительно хотите максимально заменить подзапросы связанными запросами?
• **Подзапросы: действительно ли **in такой медленный?
• Подзапрос:Какие оптимизации были сделаны для подзапросов после MySQL 5.6? (SEMIJOIN, Materializatioin, стратегия оптимизации Exists)
• Подзапрос:Каковы стратегии оптимизации Semijoin, каковы методы реализации стратегии Materializatioin и почему существует два метода реализации?
• Подзапрос:В дополнение к оптимизации «in-to-exists», какова роль оптимизации «existing-to-in» в MariaDB?

1. считать

Разница между механизмами хранения

• Каждая таблица механизма MyISAM хранит метаинформацию, которая содержит атрибут row_count, один в памяти и один в файле, а значение переменной count в памяти инициализируется чтением значения count в файле.¹Но если есть условие where, вам все равно придется выполнить сканирование таблицы

• Когда движок InnoDB выполняет count(), данные необходимо извлекать из движка построчно для статистики.

Ниже мы вводим count() в InnoDB.

Непротиворечивый вид в подсчете

Почему InnoDB не поддерживает переменную row_count, как это делает MyISAM?

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

Как показано на рисунке ниже, если предположить, что при выполнении подсчета представление согласованности получает максимальный идентификатор транзакции DATA_TRX_ID=1002, который может быть получен текущей транзакцией, то идентификатор транзакции в записи строки, превышающий 1002, должен быть откатан через отмену. log, и, наконец, можно получить окончательный результат.Какие записи строк должны быть подсчитаны текущей транзакцией:

row1 — это запись, недавно вставленная другими транзакциями, и текущая транзакция не должна учитываться. Итак, в конце текущая транзакция должна считать row2, row3.

Повлияет ли количество выполнений на частоту попаданий других пулов буферов страниц?

Мы знаем, что алгоритм LRU в пуле буферов улучшен, по умолчанию на старый подсписок (старую область) приходится 3/8, а загруженные по количеству страницы всегда вставляются в старый подсписок и удаляются в старом подсписке. быть переведены в новый подсписок. Таким образом, это не повлияет на частоту попаданий других пулов буферов страниц.

количество (первичный ключ)

Процесс выполнения count (первичный ключ) выглядит следующим образом:

• Исполнитель запрашивает механизм хранения для получения данных;
• Чтобы гарантировать, что количество отсканированных данных меньше,Механизм хранения находит наименьшее дерево индексов, чтобы получить все записи, серверу передается идентификатор возвращаемой записи. Перед возвратом записи будет оцениваться MVCC и его видимость, и будут возвращены только данные, видимые для текущей транзакции;
• После того, как сервер получает запись, он решает, что если идентификатор не пуст, он будет добавлен в запись результата.

count(1)

Процесс выполнения count(1) в основном такой же, как и у count (первичный ключ).Разница в том, что для каждой запрашиваемой записи значение в записи не берется, а напрямую возвращается "1" для статистического накопления. . Все ряды считаются.

количество (поле)

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

count(*)

count(*) не принимает значение записи, аналогично count(1).

Сравнение эффективности выполнения: count(field)

2. заказать по

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

Индекс выглядит следующим образом:

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

2.1 Как отслеживать и выполнять оптимизацию

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

SET optimizer_trace='enabled=on';

Затем выполните sql.После выполнения вы можете просмотреть детали выполнения с помощью следующей информации о стеке:

SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

Ниже приведены

select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 100,2;

Результат выполнения , из которых 8457 записей удовлетворяют a=3,Сосредоточьтесь на следующих свойствах для заказа по:

"filesort_priority_queue_optimization": {  // 是否启用优先级队列
  "limit": 102,           // 排序后需要取的行数，这里为 limit 100,2，也就是100+2=102
  "rows_estimate": 24576, // 估计参与排序的行数
  "row_size": 123,        // 行大小
  "memory_available": 32768,    // 可用内存大小，即设置的sort buffer大小
  "chosen": true          // 是否启用优先级队列
},
...
"filesort_summary": {
  "rows": 103,                // 排序过程中会持有的行数
  "examined_rows": 8457,      // 参与排序的行数，InnoDB层返回的行数
  "number_of_tmp_files": 0,   // 外部排序时，使用的临时文件数量
  "sort_buffer_size": 13496,  // 内存排序使用的内存大小
  "sort_mode": "sort_key, additional_fields"  // 排序模式
}

2.1.1, режим сортировки

sort_mode имеет следующие формы:

• sort_key, rowid: указывает, что кортеж буфера сортировки содержит значение ключа сортировки и идентификатор строки исходной строки таблицы. После сортировки идентификатор строки необходимо использовать для возврата в таблицу. Этот алгоритм также называетсяoriginal filesort algorithm(алгоритм сортировки возвращаемой таблицы);
• sort_key, additional_fields: указывает, что кортеж буфера сортировки содержит значение ключа сортировки и столбцы, необходимые для запроса.После сортировки данные извлекаются напрямую из кортежа буфера без возврата в таблицу.Этот алгоритм также называетсяmodified filesort algorithm(не возвращаться к таблице для сортировки);
• sort_key, packed_additional_fields: Аналогичен предыдущей форме, но дополнительные столбцы (например, типы varchar) упакованы плотно вместе вместо использования кодирования фиксированной длины.

Как выбрать режим сортировки

какой режим сортировки выбрать, сmax_length_for_sort_dataЭто свойство связано, размер этого свойства по умолчанию составляет 1024 байта:

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

2.1.2, алгоритм сортировки

В зависимости от объема данных, участвующих в сортировке, могут быть выбраны различные алгоритмы сортировки:

• Если результат сортировки мал и меньше памяти, он будет использовать优先级队列выполнить сортировку кучи;

  • Например, следующее извлекает только первые 10 записей, которые будут отсортированы по приоритетной очереди:

  • select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 10;
    

• Если предел сортировки n, m, n слишком велик, то есть данные необходимо отсортировать позже, то будет использоваться буфер сортировки.快速排序:

  • Таким образом, в таблице есть еще три фрагмента данных с a=1, но из-за необходимости ограничить количество записей самым последним, MySQL сравнит накладные расходы на сортировку приоритетной очереди и быструю сортировку и выберет более подходящий вариант. Алгоритм сортировки Здесь от приоритетной очереди окончательно отказываются, а для быстрой сортировки вместо нее используется буфер сортировки:

  • select a, b, c, d from t20 force index(idx_abc)  where a=1 order by d limit 300,2;
    

• Если буфер сортировки данных, участвующих в сортировке, не может быть загружен, то мы будем быстро сортировать буфер сортировки в памяти пакетами, а результат помещать во временный файл сортировки, и, наконец, делать все отсортированные временные файлы.归并排序, чтобы получить окончательный результат;

  • Таким образом, запись a = 3 превышает буфер сортировки. Данные, которые мы хотим найти, состоят из строк 1000 после сортировки, а буфер сортировки не может содержать строки данных 1000. Наконец, MySQL выбирает использование буфера сортировки для выполнения пакетной обработки. быстрая сортировка и объединение окончательных результатов.

  • select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 1000,10;
    

2.2, порядок берет индекс, чтобы избежать сортировки

Выполните следующий sql:

select a, b, c, d from t20 force index(idx_d) where d like 't%' order by d limit 2;

Давайте посмотрим на план выполнения:

Найдено дополнительное перечислено как:Using index condition, то есть здесь пропал только индекс.

Поток выполнения показан на следующем рисунке:

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

Как просмотреть и изменить размер буфера сортировки?

Давайте посмотрим на текущий размер буфера сортировки:

Можно обнаружить, что размер буфера сортировки по умолчанию составляет 512 КБ.

Мы можем установить размер этого свойства:

SET GLOBAL sort_buffer_size = 32*1024;

или

SET sort_buffer_size = 32*1024;

Ниже мы равномерно устанавливаем буфер сортировки на 32k

SET sort_buffer_size = 32*1024; 

2.3 Случай алгоритма сортировки

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

Если результат сортировки мал и меньше буфера сортировки, для сортировки кучи будет использоваться приоритетная очередь;

Например, следующий код извлекает только первые 10 записей:

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

Общее количество записей с a=3:8520. Посмотреть план выполнения:

Обнаружено, что условие where использует индекс, а порядок по лимиту использует сортировку. Давайте внимательнее посмотрим на журнал выполнения optimizer_trace:

"filesort_priority_queue_optimization": {
  "limit": 10,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "chosen": true  // 使用优先级队列进行排序
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 11,
  "examined_rows": 8520,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 1448,
  "sort_mode": "sort_key, additional_fields"
}

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

Таким образом, этот поток выполнения показан на следующем рисунке:

1. Сканировать до 8520 записей, где условие a=3;
2. Вернуться к столу, чтобы найти записи;
3. Поместите необходимые поля в записи 8520 в буфер сортировки;
4. Сортировка стека в буфере сортировки;
5. Возьмите 10 лучших элементов ограничения 10 из отсортированных результатов, запишите их в сетевой буфер и подготовьтесь к отправке их клиенту.

2.3.2 Внутренняя быстрая сортировка

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

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

Есть следующий SQL:

select a, b, c, d from t20 force index(idx_abc)  where a=1 order by d limit 300,2;

Мы устанавливаем буфер сортировки на 32 КБ:

SET sort_buffer_size = 32*1024; 

Среди них есть 3 записи с a=1. Посмотреть план выполнения:

Можно обнаружить, что условие where использует индекс, а порядок по лимиту использует сортировку. Давайте внимательнее посмотрим на журнал выполнения optimizer_trace:

"filesort_priority_queue_optimization": {
  "limit": 302,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "strip_additional_fields": {
    "row_size": 57,
    "sort_merge_cost": 33783,
    "priority_queue_cost": 61158,
    "chosen": false  // 对比发现快速排序开销成本比优先级队列更低，这里不适用优先级队列
  }
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 3,
  "examined_rows": 3,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 32720,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

Можно обнаружить, что от приоритетной очереди здесь окончательно отказались, а для быстрой сортировки используется буфер сортировки.

Таким образом, этот поток выполнения показан на следующем рисунке:

1. Сканировать до 3 записей, где условие a=1;
2. Вернуться к столу, чтобы найти записи;
3. Поместите необходимые поля в 3 записи вsort bufferсередина;
4. в буфере сортировки快速排序;
5. Возьмите лимит 300, 2 записи 300 и 301 из отсортированных результатов, запишите их в сетевой буфер и подготовьте к отправке клиенту.

2.3.3 Внешняя сортировка слиянием

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

Есть следующий sql:

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;

Среди них 8520 записей с a=3. План выполнения следующий:

Можно обнаружить, что здесь используется index, а для сортировки используется order by limit. Далее просмотрите лог выполнения optimizer_trace:

"filesort_priority_queue_optimization": {
  "limit": 1010,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "strip_additional_fields": {
    "row_size": 57,
    "chosen": false,
    "cause": "not_enough_space"  // sort buffer空间不够，无法使用优先级队列进行排序了
  }
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 8520,
  "examined_rows": 8520,
  "number_of_tmp_files": 24,  // 用到了24个外部文件进行排序
  "sort_buffer_size": 32720,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

Мы видим, что из-за ограничения в 1000, чтобы вернуть записи после 1000 строк после сортировки, очевидно буфер сортировки уже не может поддерживать очередь с таким большим приоритетом, поэтому мы переходим к сортировке памяти буфера сортировки, и здесь нам нужно чтобы разделить буфер сортировки в буфере сортировки.Выполнить быструю сортировку в пакетах, получить несколько отсортированных внешних временных файлов и, наконец, выполнить сортировку слиянием. (Расположение внешнего временного файла задается параметром tmpdir)

Его процесс показан на следующем рисунке:

2.4 Случай режима сортировки

2.4.1, sort_key, режим дополнительных_полей

sort_key, additional_fields, кортеж буфера сортировки содержит значение ключа сортировки и столбцы, необходимые для запроса (сначала верните таблицу, чтобы получить требуемые данные и сохраните их в буфере сортировки), и напрямую извлеките данные из кортежа буфера после сортировки, не возвращая снова к столу.

Все примеры в разделах 2.3.1 и 2.3.2 выше относятся к этому режиму сортировки, поэтому мы не будем продолжать приводить примеры.

2.4.2, режим

sort_key, packed_additional_fields: Аналогичен предыдущей форме, но дополнительные столбцы (например, типы varchar) упакованы плотно вместе вместо использования кодирования фиксированной длины.

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

2.4.3, режим

Как мы упоминали ранее, какой режим сортировки выбрать, иmax_length_for_sort_data²Это свойство связано сmax_length_for_sort_dataЗадает максимальный размер отсортированной строки.Значение этого свойства по умолчанию — 1024 байта:

То есть, если размер, занимаемый столбцом запроса и столбцом сортировки, меньше этого значения, он исчезнет в это время.sort_key, additional_fieldsилиsort_key, packed_additional_fieldsалгоритм, иначе он будет использоватьsort_key, rowidмодель.

Теперь мы намеренно устанавливаем это значение меньше, имитируяsort_key, rowidмодель:

SET max_length_for_sort_data = 100;

Выполните sql в это время:

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

В это время проверьте журнал optimizer_trace выполнения sql:

"filesort_priority_queue_optimization": {
  "limit": 10,
  "rows_estimate": 27033,
  "row_size": 49,
  "memory_available": 32768,
  "chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 11,
  "examined_rows": 8520,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 632,
  "sort_mode": "<sort_key, rowid>"
}

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

1. где условие a=3 сканирует 8520 записей;
2. Вернуться к столу, чтобы найти записи;
3. Найдите эти 8520 записейidиdполе, помещаем его в буфер сортировки для сортировки в куче;
4. После сортировки возьмите первые 10 предметов;
5. Возьмите id этих 10 элементов и верните требуемые значения полей a, b, c, d в запрос таблицы;
6. Результаты возвращаются клиенту по очереди.

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

2.5, порядок по сводке оптимизации

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

• Порядок по полю должен максимально использовать тип поля фиксированной длины, поскольку поле сортировки не поддерживает сжатие;
• Если порядок по полю должен использовать переменную длину, длину следует максимально контролировать, и причина та же, что и выше;
• Старайтесь не использовать select * в запросе, чтобы избежать слишком большого количества запросов, что приводит к недостаточной памяти буфера сортировки во время сортировки, что приводит к внешней сортировке или превышению размера строки.max_length_for_sort_dataвести, чтобы идтиsort_key, rowidРежим сортировки, приводящий к большему количеству операций чтения с диска, влияющий на производительность;
• Попробуйте добавить совместный индекс к полю сортировки и связанным с ним условиям, лучше всего использовать покрывающий индекс.

3. присоединиться

Чтобы продемонстрировать соединение, нам нужно использовать эти две таблицы:

CREATE TABLE `t30` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE `t31` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `f` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;

insert into t30(a,b,c) values(1, 1, 1),(12,2,2),(3,3,3),(11, 12, 31),(15,1,32),(33,33,43),(5,13,14),(4,13,14),(16,13,14),(10,13,14);

insert into t31(a,f,c) values(1, 1, 1),(21,2,2),(3,3,3),(12, 1, 1),(31,20,2),(4,10,3),(2,23,24),(22,23,24),(5,23,24),(20,23,24);

В официальной документации MySQL8.8.2 EXPLAIN Output Format⁷Упоминание: MySQL используетNested-Loop LoinАлгоритм обрабатывает все связанные запросы. Использование этого алгоритма означает такой режим исполнения:

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

Ниже мы говорим оNested-Loop JoinРазличные реализации алгоритма.

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

3.1 Соединение индекса с вложенным циклом

3.1.1 Соединение индекса с вложенным циклом

Выполняем следующий sql:

select * from t30 straight_join t31 on t30.a=t31.a;

Посмотреть план выполнения:

Его можно найти:

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

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

1. Сначала пройдите кластеризованный индекс t30;
2. Для каждой записи t30 найдите значение a и перейдите к индексу idx_a t31, чтобы узнать, существует ли запись;
3. Если он существует, получите идентификатор индексной записи, соответствующей t30, и вернитесь к таблице, чтобы найти полную запись;
4. Возьмите все поля t30 и t31 соответственно и верните их в качестве результатов.

Поскольку в этом процессе используется индекс idx_a, этот алгоритм также называется:Index Nested-Loop (индексированное вложенное циклическое соединение). Его псевдокодовая структура выглядит следующим образом:

// A 为t30聚集索引
// B 为t31聚集索引
// BIndex 为t31 idx_a索引
void indexNestedLoopJoin(){
  List result;
  for(a in A) {
    for(bi in BIndex) {
      if (a satisfy condition bi) {
        output <a, b>;
      }
    }
  }
}

Предполагая, что количество записей в t30 равно m, количество записей в t31 равно n, а сложность каждого поиска в дереве индексов равна log2(n), поэтому общая сложность приведенного выше сценария составляет:m + m*2*log2(n).

то естьЧем меньше таблица накопителей, тем ниже сложность и выше эффективность поиска.

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

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

Это должно быть из MySQLMRR（Multi-Range Read）⁵Запустился механизм оптимизации.

3.1.2.1, Оптимизация многодиапазонного чтения

Мы выполняем следующий код, чтобы принудительно включить функцию MMR:

set optimizer_switch="mrr_cost_based=off"

Затем выполните следующий SQL, где a — это индекс:

select * from t30 force index(idx_a) where a<=12 limit 10;

Можно получить следующий план выполнения:

Можно обнаружить, что столбец «Дополнительно» указывает на то, что используется оптимизация MRR.

Здесь для демонстрации сцены обхода индекса добавлено ключевое слово force index.

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

Без оптимизации MRR поток выглядит следующим образом:

1. Найдите запись с a
2. Возьмите первые 10 записей, возьмите идентификатор и вернитесь к таблице, чтобы найти полную запись, здесь запрос задней таблицыСлучайное чтение, менее эффективное;
3. Полученный результат возвращается клиенту через сетевой буфер.

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

1. Найдите запись с a
2. Возьми 10 и вставь идентификаторread rnd buffer;
3. read rnd bufferСортировать по идентификатору в;
4. После сортировки вернитесь к таблице, чтобы запросить полные записи.Чем больше идентификаторов, тем больше вероятность создания последовательных идентификаторов после сортировки и их последовательного чтения с диска;
5. Результат запроса записывается в сетевой буфер и возвращается клиенту;

3.1.2.2, Пакетный доступ к ключу

Подобно идее оптимизации Multi-Range Read, MySQL также изменяет случайное чтение на последовательное чтение, так чтоIndex Nested-Loop JoinДля повышения эффективности запросов этот алгоритм называетсяBatched Key Access(BKA)⁶алгоритм.

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

В алгоритме БКА驱动表используяjoin bufferпартия в被驱动表из辅助索引Совпадающие данные сопоставляются в середине, и получается пакет результатов, и интерфейс MRR механизма базы данных передается одновременно, так что MRR можно использовать для оптимизации чтения с диска.

Чтобы включить этот алгоритм, мы выполняем следующую команду (BKA зависит от MRR):

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

Мы снова выполняем следующий связанный запрос sql:

select * from t30 straight_join t31 on t30.a=t31.a;

Мы можем получить следующий план выполнения:

Можно обнаружить, что он используется здесь:Using join buffer(Batched Key Access).

Последовательность выполнения следующая:

1. Помещать данные таблицы дисков в буфер соединения пакетами;
2. Пакетное сопоставление результатов со вспомогательным индексом управляемой таблицы в буфере соединения для получения набора результатов;
3. Отправьте набор результатов предыдущего шага в MRR-интерфейс движка пакетами;
4. Обработка интерфейса MRR такая же, как и в предыдущем разделе, в основном выполняется оптимизация последовательного чтения с диска;
5. Комбинируя вывод конечного результата, видно, что результат здесь отличается от порядка, в котором не включена оптимизация BKA.Здесь в качестве порядка вывода используется сортировка по id ведомой таблицы t31, т.к. последний шаг — выполнить оптимизацию MRR при чтении ведомой таблицы t31 при сортировке.

Если условие соединения не попадет в индекс, что будет?Далее пытаемся выполнить соответствующий sql.

3.2 Соединение без индекса (блочное соединение с вложенным циклом)

3.2.1 Блочное соединение с вложенным циклом (BNL)

Выполняем следующий sql:

select * from t30 straight_join t31 on t30.c=t31.c;

Посмотреть план выполнения:

Его можно найти:

• t30 используется в качестве ведущего стола, а t31 используется в качестве ведомого стола;
• Через ассоциацию полей c индекс не используется при поиске данных в таблице t31;
• Буфер соединения используется в процессе соединения, и здесь используется блочное соединение с вложенным циклом;

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

1. Данные в таблице накопителя t30 хранятся в буфере соединений пачками (блоками), если их можно хранить все сразу, то они будут храниться здесь единовременно;
2. t31 сканируется в таблице дисков, а затем поочередно извлекается и сравнивается с записями в буфере соединения (быстрое сравнение в памяти), чтобы определить, выполняется ли условие равенства c;
3. Результат объединенных записей, отвечающих условиям, выводится в сетевой буфер и, наконец, передается клиенту.

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

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

3.2.2, проблема БНЛ

существуетВзгляд на базовую архитектуру MySQL: перемещение между буфером и дискомВ этой статье мы представили алгоритм LRU буферного пула MySQL следующим образом:

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

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

Рассмотрим следующие два сценария, влияющие на пул буферов:

• Если присоединение сканирует в это время большую холодную таблицу, в течение периода присоединения страницы данных будут продолжать записываться в старый подсписок (старая область), а страницы данных в конце очереди будут исключены, что повлияет на продвижение других страниц бизнес-данных в новый подсписок, потому что другие бизнес-данные, скорее всего, будут исключены из старого подсписка в течение секунды;
• Если в это время алгоритм BNL делит управляющую таблицу на несколько пакетов, и каждый пакет сканирует управляемую таблицу и сопоставляет ее более 1 секунды, то в это время страница данных управляемой таблицы будет переведена в новый подсписок. в это время страницы данных других предприятий также будут заранее исключены из нового подсписка.

3.2.3, решение проблемы BNL

3.2.3.1 Увеличьте размер join_buffer_size

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

3.2.3.2 Преобразование BNL в BKA

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

3.2.3.3, добавив временную таблицу

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

3.2.3.4 Использование хэш-соединения

Что такое хэш-соединение?Проще говоря, это такая модель:

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

В общем, реализация соединения MySQL — это реализация различных алгоритмов вложенных циклов, описанных выше, но начиная с MySQL 8.0.18.⁸Для начала мы можем использовать хеш-соединение для реализации непрерывного запроса к таблице. Если вам интересно, вы можете прочитать эту статью дальше, чтобы понять: [Хэш-соединение в MySQL 8 | Блог MySQL Server] (нет sqlserver team.com/hashi-join-i… only supports inner hash,more often than it does.)

3.3, различные соединения

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

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

LEFT JOIN EXCLUDING INNER JOIN

RIGHT JOIN EXCLUDING INNER JOIN

OUTER JOIN EXCLUDING INNER JOIN

Для получения более подробной информации см.:

• MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS¹⁰
• How the SQL join actually works?¹¹

3.3. Резюме использования соединения

• Целью оптимизации соединения является максимально возможное сокращение числа циклов вложенного цикла в соединении, поэтому, пожалуйста, пусть маленькая таблица будет управляющей таблицей;
• Связанные поля должны быть максимально проиндексированы, чтобы можно было использовать индексное соединение с вложенным циклом;
• Если есть порядок, пожалуйста, используйте поле таблицы драйверов в качестве порядка, в противном случае будет использоваться временное использование;
• Если алгоритм BNL неизбежно используется, чтобы уменьшить влияние на использование пула буферов, вызванное многократным сканированием управляемой таблицы, вы можете попытаться увеличить join_buffer_size;
• Чтобы еще больше повысить эффективность выполнения алгоритма BNL, мы можем добавить индекс к условию ассоциации и преобразовать его в алгоритм BKA; если стоимость добавления индекса высока, это может быть достигнуто путем добавления индекса к временная таблица;
• Если вы используете MySQL 8.0.18, вы можете попробовать использовать хэш-соединение, если это более ранняя версия, вы также можете реализовать хэш-соединение в программе самостоятельно.

4. Союз

Используя объединение, можно объединить два результата запроса.

объединение всех не удалит повторяющиеся строки, объединение удалит повторяющиеся прочитанные строки.

4.1 объединить все

Выполните следующий sql:

(select id from t30 order by id desc limit 10) union all (select c from t31 order by id desc limit 10)

План выполнения sql следующий:

Последовательность выполнения следующая:

1. Запросите результат из таблицы t30, запишите его непосредственно в сетевой буфер и отправьте обратно клиенту;
2. Запросите результат из таблицы 331, запишите его непосредственно в сетевой буфер и отправьте обратно клиенту.

4.2, союз

Выполните следующий sql:

(select id from t30 order by id desc limit 10) union (select c from t31 order by id desc limit 10)

План выполнения sql следующий:

Последовательность выполнения следующая:

1. Запросите запись из t30 и запишите ее во временную таблицу;
2. Запросите записи из t30, запишите их во временную таблицу и выполните дедупликацию через уникальный индекс во временной таблице;
3. Возвращать данные временной таблицы клиенту через сетевой буфер.

5. группировать по

5.1. Заполните указатель

Добавляем индекс к t30:

alter table t30 add index idx_c(c);

Выполните следующую группу bysql:

select c, count(*) from t30 group by c;

План выполнения следующий:

Выяснено, что здесь используется только индекс, причина в том, чтоidx_cСам индекс сортируется по c, затем напрямую сканируется индекс idx_c по порядку, и вы можете напрямую подсчитать количество записей для каждого значения c, не занимаясь другой статистикой.

5.2 Временная таблица

Теперь мы ставимidx_cЧтобы удалить индекс, выполните следующий sql:

select c, count(*) from t30 group by c order by null;

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

План выполнения следующий:

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

1. сканирование кластерного индекса t30;
2. Создать временную таблицу с полем c в качестве основного ключа и последовательно накапливать записи, просмотренные в t30, по полю c временной таблицы;
3. Вернуть последнюю накопленную временную таблицу клиенту.

5.3 Временная таблица + сортировка

Если мы сделаем предыдущий шагorder by nullУдалено, по умолчанию результаты группировки будут сортироваться по полю c. Давайте посмотрим на его план выполнения:

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

Давайте подробнее рассмотрим, как это работаетOPTIMIZER_TRACEбревно:

"steps": [
  {
    "creating_tmp_table": {
      "tmp_table_info": {
        "table": "intermediate_tmp_table",  // 创建中间临时表
        "row_length": 13,
        "key_length": 4,
        "unique_constraint": false,
        "location": "memory (heap)",
        "row_limit_estimate": 1290555
      }
    }
  },
  {
    "filesort_information": [
      {
        "direction": "asc",
        "table": "intermediate_tmp_table",
        "field": "c"
      }
    ],
    "filesort_priority_queue_optimization": {
      "usable": false,
      "cause": "not applicable (no LIMIT)" // 由于没有 limit，不采用优先级队列排序
    },
    "filesort_execution": [
    ],
    "filesort_summary": {
      "rows": 7,
      "examined_rows": 7,
      "number_of_tmp_files": 0,
      "sort_buffer_size": 344,
      "sort_mode": "<sort_key, rowid>"  // rowid排序模式
    }
  }
]

Также из журнала можно узнать, что здесь используется промежуточная временная таблица.Поскольку ограничения на ограничение количества записей нет, то здесь не используется сортировка приоритетной очереди.Режим сортировки здесьsort_key, rowid. Процесс его выполнения следующий:

1. сканирование кластерного индекса t30;
2. Создать временную таблицу с полем c в качестве основного ключа и последовательно накапливать записи, просмотренные в t30, по полю c временной таблицы;
3. Поместите полученную временную таблицу в буфер сортировки для сортировки, здесь сортировка по rowid;
4. Найдите нужные поля через отсортированный rowid обратно во временную таблицу и верните ее клиенту.

Временные таблицы хранятся на диске или в памяти?

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

Размер временной таблицы памяти в текущем сеансе можно задать следующим sql: SET tmp_table_size = 102400;

5.5. Прямая сортировка

Ознакомьтесь с официальной документациейSELECT Statement¹², вы можете обнаружить, что после SELECT можно использовать множество модификаторов, чтобы повлиять на эффект выполнения SQL:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

Здесь мы сосредоточимся на этих двух:

• SQL_BIG_RESULT: его можно использовать в SQL, включая группировку и различение, чтобы напомнить оптимизатору о том, что объем данных запроса велик.В это время MySQL будет напрямую выбирать временную таблицу диска вместо временной таблицы памяти, чтобы избежать временного хранения данных на диске. table, когда в процессе выполнения обнаруживается недостаток памяти. В настоящее время более склонны использовать сортировку вместо двухмерной статистики временных таблиц. Мы продемонстрируем такой случай позже;
• SQL_SMALL_RESULT: может использоваться в SQL, включая группировку и различение, напоминая оптимизатору о том, что объем данных невелик, и напоминая оптимизатору о непосредственном выборе временной таблицы памяти, которая будет подсчитываться временной таблицей вместо сортировки.

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

Далее мы будем использовать пример, чтобы проиллюстрировать использованиеSQL_BIG_RESULTПоток выполнения SQL модификатора.

Есть следующий SQL:

select SQL_BIG_RESULT c, count(*) from t30 group by c;

План выполнения следующий:

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

Результат его выполнения следующий:

1. Сканируем таблицу t30 и помещаем поле c в буфер сортировки построчно;
2. Отсортируйте поле c в буфере сортировки, чтобы получить отсортированный массив c;
3. Пройдите этот отсортированный массив c, подсчитайте результаты и выведите.

5.4, ​​группировка по предложениям по оптимизации

• Попробуйте позволить группировать по индексу, что может максимизировать эффективность;
• Если группу по результату сортировать не нужно, то можно добавитьgroup by null, чтобы избежать сортировки;
• Если объем данных в группе велик, вы можете использоватьSQL_BIG_RESULTМодификатор, напоминающий оптимизатору, что для получения результата группы следует использовать алгоритм сортировки.

6. Отдельный¹³

В большинстве случаевDISTINCTможно рассматривать какGROUP BYЧастный случай , следующие два SQL-запроса эквивалентны:

select distinct a, b, c from t30;

select a, b, c from t30 group by a, b, c order by null;

Планы выполнения этих двух SQL-запросов следующие:

Из-за этой эквивалентности оптимизации запросов, применяемые к Group by, также применяются к DISTINCT.

**Различие: **различное заключается в том, что запись в каждой группе удаляется после группировки по, и она не сортируется после отдельной группировки.

6.1 Отличительный в экстра

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

explain select distinct t30.a  from t30, t31 where t30.c=t30.c;

План выполнения следующий: можно обнаружить, что в колонке Extra есть отчет, который указывает на то, что эта оптимизация используется.¹³:

7. Подзапросы

Во-первых, давайте проясним несколько понятий:

**Подзапросы:** могут быть вложены в другой запрос (выберите вставить обновление удалить), или подзапрос может быть вложен в другой подзапрос.

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

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

create table class (
  id bigint not null auto_increment,
  class_num varchar(10) comment '课程编号',
  class_name varchar(100) comment '课程名称',
  pass_score integer comment '课程及格分数',
  primary key (id)
) comment '课程';

create table student_class (
  id bigint not null auto_increment,
  student_name varchar(100) comment '学生姓名',
  class_num varchar(10) comment '课程编号',
  score integer comment '课程得分',
  primary key (id)
) comment '学生选修课程信息';

insert into class(class_num, class_name, pass_score) values ('C001','语文', 60),('C002','数学', 70),('C003', '英文', 60),('C004', '体育', 80),('C005', '音乐', 60),('C006', '美术', 70);

insert into student_class(student_name, class_num, score) values('James', 'C001', 80),('Talor', 'C005', 75),('Kate', 'C002', 65),('David', 'C006', 82),('Ann', 'C004', 88),('Jan', 'C003', 70),('James', 'C002', 97), ('Kate', 'C005', 90), ('Jan', 'C005', 86), ('Talor', 'C006', 92);

Подзапросы можно использовать по-разному. Давайте сначала перечислим использование подзапросов.

7.1 Как использовать подзапросы

7.1.1 Подзапросы, где

7.1.1.1 Операторы сравнения

Операторы сравнения, такие как =, >,

Найдите информацию о курсе с наибольшим номером, выбранную студентом:

SELECT class.* FROM class WHERE class.class_num = ( SELECT MAX(class_num) FROM student_class );

7.1.1.2, в и не в

Если подзапрос возвращает более одного значения, в предложении WHERE можно использовать другие операторы, такие как операторы IN или NOT IN. как

Узнайте, какие курсы прошли студенты:

SELECT class.* FROM class WHERE class.class_num IN ( SELECT DISTINCT class_num FROM student_class );

7.1.2, из подзапроса

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

SELECT max(count), min(count) FROM (SELECT class_num, count(1) as count FROM student_class group by class_num) as t1;

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

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

инезависимый подзапросразные,коррелированные подзапросы— это подзапрос, который использует данные из внешнего запроса. Другими словами, коррелированный подзапрос зависит от внешнего запроса. Коррелированный подзапрос оценивается один раз для каждой строки внешнего запроса.

Ниже приведен коррелированный подзапрос в операторе сравнения.

Найдите записи о курсах учащихся с оценками выше среднего по каждому курсу:

SELECT t1.* FROM student_class t1 WHERE t1.score > ( SELECT AVG(score) FROM student_class t2 WHERE t1.class_num = t2.class_num);

В коррелированном подзапросе подзапрос должен выполняться один раз для каждой внешней записи., так как class_num каждой внешней записи может отличаться.

7.1.3.1, существует и не существует

Когда подзапрос используется с операторами EXISTS или NOT EXISTS, подзапрос возвращает логическое значение TRUE или FALSE.

Найти все курсы с общим баллом учащихся больше 100:

select * from class t1 
where exists(
  select sum(score) as total_score from student_class t2 
  where t2.class_num=t1.class_num group by t2.class_num having total_score > 100
)

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

Выше мы продемонстрировали различные варианты использования подзапросов, а теперь поговорим об оптимизации подзапросов.¹⁶.

Подзапросы в основном оптимизируются следующими тремя методами:

• Semijoin, преобразование semi-join, автоматически преобразует подзапрос sql в semijion;
• Материализация, материализация подзапросов;
• Стратегия EXISTS, в которой существует;

Среди них Semijoin может использоваться только в подзапросах IN, = ANY или EXISTS и не может использоваться в подзапросах NOT IN, ALL или NOT EXISTS.

Ниже мы делаем подробное введение.

Вы действительно хотите максимально заменить подзапросы связанными запросами?

В "Высокопроизводительном MySQL"²¹В книге упоминается, что наиболее важным предложением по оптимизации подзапросов является использование вместо этого ассоциативных запросов, но если вы используете MySQL 5.6 или более позднюю версию или MariaDB, вы можете напрямую игнорировать это предложение. Поскольку в этих версиях выполняется много оптимизаций подзапросов, мы сосредоточимся на этих оптимизациях позже.

Эффективность действительно такая низкая?

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

7.2.1 Полусоединение

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

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

Полусоединения обычно используются для оптимизации операторов IN или EXISTS.

7.2.1.1 Сценарии оптимизации

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

в коррелированных подзапросах

Этот сценарий, реализованный с использованием in, может выглядеть так:

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM student_class where condition);

Здесь оптимизатор может распознать, что предложение IN требует, чтобы подзапрос возвращал только уникальный class_num из таблицы student_class. В этом случае запрос автоматически оптимизируется для использования полусоединения.

Если реализовано с помощью exists, это может выглядеть так:

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM student_class WHERE class.class_num = student_class.class_num);

Случай оптимизации

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

SELECT t1.class_num, t1.class_name
    FROM class t1
    WHERE t1.class_num IN
        (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

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

explain extended SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN         (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);
show warnings\G;

Получите следующий план выполнения и результат перезаписи SQL:

Из этого результата перезаписи SQL видно, что окончательный подзапрос становится оператором полуобъединения:

/* select#1 */ select `test`.`t1`.`class_num` AS `class_num`,`test`.`t1`.`class_name` AS `class_name` 
from `test`.`class` `t1` 
semi join (`test`.`student_class` `t2`) where ((`test`.`t2`.`class_num` = `test`.`t1`.`class_num`) and (`test`.`t2`.`score` < `test`.`t1`.`pass_score`))

А в плане выполнения смотрим столбец Extra:

Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)

Using join bufferЭтот элемент используется в запросе ассоциации соединения. Он был введен, когда мы говорим об операторе соединения. Теперь давайте сосредоточимся на нем.FirstMatch(t1)эта оптимизация.

**FirstMatch(t1)Это одна из стратегий оптимизации Semijoin. ** Ниже мы подробно расскажем о стратегиях оптимизации Semijoin.

7.2.1.2, Стратегия оптимизации полусоединения

MySQL поддерживает 5 стратегий оптимизации Semijoin, которые последовательно представлены ниже.

7.2.1.2.1, Первое совпадение

Найдите записи во внутренней таблице, которые соответствуют внешней таблице,Как только первый будет найден, прекратите поиск совпадений..

Кейс — статистика по курсам с неудовлетворительными оценками учащихся:

SELECT t1.class_num, t1.class_name
    FROM class t1
    WHERE t1.class_num IN
        (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

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

Процесс выполнения, картинка относительно крупная, пожалуйста увеличьте и посмотрите:

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

Вы также можете перейти на официальный сайт MariaDB, чтобы проверить официальнуюFirstMatch Strategy¹⁸объяснять.

7.2.1.2.2, Дублирование исключения

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

Конкретные демонстрационные примеры см. на официальном сайте MariaDB:DuplicateWeedout Strategy¹⁹, ниже приведен пример официального веб-сайта:

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

7.2.1.2.3, Свободное сканирование

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

Конкретные демонстрационные примеры см. на официальном сайте MariaDB:LooseScan Strategy²⁰, ниже приведен пример официального веб-сайта:

7.2.1.4, Материализация²²

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

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

Выполняем следующий SQL:

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

Последовательность выполнения следующая:

1. Выполнить подзапрос: узнать подходящие записи из таблицы student_class через условие where и поместить все записи в материализованную временную таблицу;
2. Найдите соответствующие записи в таблице классов с помощью условия where и выполните операцию соединения с материализованной временной таблицей.

Уникальный индекс в материализованной таблице

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

Операция объединения может выполняться в двух направлениях:

• Свяжите таблицу классов с материализованной таблицей, т. е.扫描物化表, чтобы соответствовать записи таблицы классов, которую мы называемMaterialize-scan;
• Свяжите материализованную таблицу с таблицей классов, то есть просмотрите таблицу классов, перейдите к物化表中查找совпадающие записи, которые мы называемMaterialize-lookup, в настоящее время мы используем уникальный индекс материализованной таблицы для поиска, и эффективность будет очень высокой.

Ниже мы опишем эти две реализации.

Materialize-lookup

Или возьмите приведенный выше sql в качестве примера:

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

План выполнения следующий:

Его можно найти:

• Тип select_type таблицы t2 МАТЕРИАЛИЗОВАН, что означает, что результат запроса с id=2 будет сохранен в материализованной временной таблице. И использовать все поля запроса как уникальный индекс временной таблицы, чтобы предотвратить вставку повторяющихся записей;
• Запрос с id=1 получаетsubquery2Имя таблицы, эта таблица является материализованной таблицей, которую мы получаем из запроса с id=2.
• Запрос с id=1 сначала просматривает таблицу t1, по очереди получает каждую запись в таблице t1 и переходит кsubquery2воплощать в жизньeq_ref, используется здесьauto_key, чтобы получить совпадающие записи.

Другими словами, оптимизатор выбирает полное сканирование таблицы t1 (класс), а затем дематериализует таблицу для выполнения всех эквивалентных поисков и, наконец, получает результат.

Модель исполнения показана на следующем рисунке:

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

еслиМатериализованная таблица, найденная подзапросом, маленькая, но внешняя таблица большая, а связанное поле является полем индекса внешней таблицы, тогда оптимизатор выберет сканирование материализованной таблицы, чтобы связать внешнюю таблицу., это,Materialize-scan, следующий пример демонстрирует этот сценарий.

Materialize-scan

Теперь попробуем добавить в таблицу классов уникальный индекс class_num:

alter table class add unique uk_class_num(class_num);

И вставьте больше данных в класс. Затем выполните тот же sql и получите следующий план выполнения:

Можно обнаружить, что запрос с id=1 в это время выбирает подзапрос2, то есть сканируется материализованная таблица, и результат сканирования построчно попадает в таблицу (класс) t1.eq_refСопоставление, индекс таблицы t1 используется в процессе сопоставления.

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

Теперь я спрашиваю всех: когда стратегия **Материализации решит связать внутреннюю таблицу с внешней таблицей? ** Я считаю, что каждый должен иметь ответ в своем сердце.

Модель исполнения следующая:

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

Теперь я оставляю вас с другим вопросом:В приведенном выше примере, каковы затраты на эти две материализации (статистика с точки зрения чтения и записи строк)

Отвечать:

Материализация-поиск: 40 раз чтение таблицы student_class, 40 раз запись материализованной временной таблицы, 42 раза чтение внешней таблицы, 40 раз поиск извлечение материализованной временной таблицы;

Materialize-scan: прочитать таблицу student_class 15 раз, записать материализованную временную таблицу 15 раз, просмотреть материализованную временную таблицу 15 раз и выполнить запрос индекса таблицы class 15 раз.

7.2.2 Материализация

использование оптимизатораMaterialization(материализация) для более эффективной обработки подзапросов.Материализация оптимизирована для некоррелированных подзапросов.

Материализация ускоряет выполнение запросов, сохраняя результаты подзапросов в виде временных таблиц (обычно в памяти). Когда MySQL впервые получает результат подзапроса, он материализует результат во временную таблицу. Затем, если результат подзапроса понадобится снова, он считывается непосредственно из временной таблицы.

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

Материализованные временные таблицы подзапросов хранятся вОЗУ, если таблица слишком велика, она возвращается на диск для хранения.

Зачем использовать материализованную оптимизацию

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

Переключатель оптимизации можно запросить с помощью следующей команды (Switchable Optimizations²³)условие:

SELECT @@optimizer_switch\G;

То есть следующее в независимом операторе подзапроса:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

Он будет переписан как существующий связанный оператор подзапроса:

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

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

7.2.3, СУЩЕСТВУЕТ стратегия

Рассмотрим следующий подзапрос:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

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

7.2.3.1, условие нажатия вниз

Если мы сможем отправить external_expr в подзапрос для условного суждения, как показано ниже:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

Это уменьшает количество строк в подзапросе. По сравнению с прямым использованием IN это может повысить эффективность выполнения SQL.

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

8.2.2.3 Optimizing Subqueries with the EXISTS Strategy²⁵

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

Подробности можно прочитать:EXISTS-to-IN Optimization²⁴

7.2.4. Резюме

Подводя итог, как оптимизируются подзапросы:

• Во-первых, используйте Semijoin для оптимизации и устранения подзапросов.Обычно для объединения таблиц используется стратегия FirstMatch;
• Если Semijoin нельзя использовать для оптимизации, а текущий подзапрос является неассоциативным подзапросом, то подзапрос будет материализован, чтобы избежать множественных запросов, при этом оптимизация этого шага будет осуществляться по принципу выбора небольшой таблицы в качестве вождение таблицы и попытаться максимально использовать ассоциацию полей индекса.Разделено на два метода выполнения: материализация-поиск, материализация-сканирование. Обычно хеш-индекс используется в качестве материализованной временной таблицы для повышения эффективности поиска;
• Если подзапрос не может быть материализован, то можно рассматривать только стратегию оптимизации Exists.condition push downПоместите условие в существующий подзапрос, чтобы уменьшить результирующий набор подзапроса, чтобы достичь цели оптимизации.

8. предельное смещение, строк

Использование лимита:

limit [offset], [rows]

Где offset представляет собой смещение, а rows — количество возвращаемых строк.

offset  limit  表中的剩余数据
 _||_   __||__   __||__
|    | |      | |
RRRRRR RRRRRRRR RRR...
       |______|
          ||
         结果集

8.1 Принцип выполнения

MySQL сканирует таблицу, считывает данные смещения + строки, отбрасывает предыдущие записи смещения и возвращает оставшиеся записи строк.

Например следующий sql:

select * from t30 order by id limit 10000, 10;

Всего будет отсканировано 10010.

8.2 Методы оптимизации

Если смещение запроса велико, не используйте смещение напрямую, а выполняйте поиск и поиск по идентификатору в кластеризованном индексе.

1. Используйте автоинкрементные индексы, например:

select * from t30 where id > 10000 limit 10;

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

---

Содержание этой статьи почти представлено здесь.Друзья, которые могут прочитать это здесь, очень терпеливы и ставят вам лайки.

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

Вы можете следить за моим блогом:itzhai.comДля других статей я буду продолжать обновлять технологии, связанные с серверной частью, включая JVM, основу Java, проектирование архитектуры, сетевое программирование, структуры данных, базы данных, алгоритмы, параллельное программирование, распределенные системы и другой связанный контент.

Если вы чувствуете, что прочитали эту статью, вы можете关注мой аккаунт или点赞Что ж, программировать нелегко, ваша поддержка — самая большая мотивация для моего письма, еще раз спасибо!

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

больше статей

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

Автор этой статьи: артмышление

Ссылка на блог:Woohoo.ITZhai.com/database/Хорошо…

Внутренняя операция SQL: взгляд на природу настройки с точки зрения принципа выполнения

Заявление об авторских правах: Авторские права принадлежат автору и не могут быть воспроизведены без разрешения Нарушение должно быть расследовано! Чтобы связаться с автором, пожалуйста, добавьте публичный аккаунт.

---

References