Как выполнить оператор, содержащий предложение IN в MySQL

MySQL Эксплуатация и техническое обслуживание

Ярлыки: Статьи официального аккаунта

Для разработчиков даMySQLОператоры, содержащие предложение IN, должны быть знакомы и уже не могут быть знакомыми Они используются почти каждый день и время от времени. Однако многие мелкие партнеры не знают, как выполняется оператор, содержащий предложение IN. В некоторых сценариях оптимизации запросов они не могут его найти. В этой статье я буду ныть об операторе, содержащем предложение IN в MySQL. взяв в качестве примера механизм хранения InnoDB MySQL 5.7).

Готов к работе

Для плавного развития сюжета сначала создадим таблицу:

CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

можно увидеть таблицуtсодержит два индекса:

  • отidКластеризованный индекс с первичным ключом столбца
  • заkey1Вторичный индекс, созданный столбцом

Теперь в этой таблице 10 000 элементов данных:

mysql> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

Найдите записи из дерева B+

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

SELECT * FROM t WHERE 
    key1 >= 'b' AND key1 <= 'c';

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

image_1duk7ejao14qa17s3116e1lmjqt19.png-88.4kB

Советы: Простите меня за крайне упрощенную версию сложной древовидной структуры B+, соответствующей индексу.Чтобы выделить ключевые моменты, мы игнорируем структуру страницы и напрямую отображаем записи всех листовых узлов вместе. Ключевой момент, который мы хотим подчеркнуть, заключается в следующем: записи в конечном узле B+ сортируются в соответствии со значением столбца индекса.Для кластеризованного индекса записи в соответствующем конечном узле B+ сортируются по столбцу id.Для idx_key1 Для вторичного индекса записи в соответствующем листовом узле B+ сортируются в соответствии со столбцом key1.

мы хотим узнатьkey1Значение столбца находится в['b', 'c']Для записей в этом интервале нужно:

  • пройти первымidx_key1индекс, соответствующийB+Быстро найти деревоkey1значение столбца'b', и самая левая запись вторичного индекса, запись вторичного индекса содержит соответствующее значение первичного ключа, а затем находит полную запись в кластеризованном индексе в соответствии со значением первичного ключа (этот процесс называется таблицей возврата), возвращает ее на уровень сервера , а уровень сервера отправляет его клиенту.

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

  • Продолжайте поиск по односвязному списку записей и повторяйте описанный выше процесс до тех пор, пока значение столбца key1 найденной записи вторичного индекса не будет удовлетворено.key1 <= 'c'Это состояние , как показано на рисунке, то есть когда мы находимся вidx_key1найдено во вторичном индексеkey1='ca'После этой записи выяснилось, что она не соответствуетkey1 <= 'c'состоянии, так что прекращайте поиски.

Вышеупомянутый процесс проходит черезB+Процесс поиска записи со значением ключа в определенном диапазоне дерева.

Процесс выполнения с предложением IN

Если мы хотим выполнить следующий оператор:

SELECT * FROM t WHERE 
    key1 IN ('b', 'c');

Если оптимизатор решит выполнить приведенный выше оператор с использованием вторичного индекса, как он будет выполняться?

Оптимизатор обрабатывает условия в предложении IN как 2 интервала диапазона (хотя оба интервала содержат только одно значение):

  • ['b', 'b']
  • ['c', 'c']

Затем во время выполнения инструкции вам нужно передатьB+Дерево для определения местоположения двух записей:

  • Сначала найдите ключ-значение в диапазоне['b', 'b']запись:

    • пройти первымidx_key1индекс, соответствующийB+Быстро найти деревоkey1значение столбца'b', а самая левая вторичная индексная запись отправляется обратно в таблицу на серверный уровень, а затем на клиент.

    • Затем по односвязному списку, составленному из записей,key1=bЗапись вторичного индекса найдена и отправлена ​​на уровень сервера после возвращения в таблицу, а затем отправлена ​​клиенту.

    • Повторяйте описанный выше процесс до тех пор, пока значение столбца key1 найденной записи вторичного индекса не будет удовлетворено.key1 = 'b'до этого состояния.

  • Пара "ключ-значение" ретаргетинга находится в диапазоне['c', 'c']запись:

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

Поэтому, если вы пишете больше параметров в операторе IN, это означает, что вам нужно передатьB+Чем больше раз дерево находит записи.

В случае дублирования значений параметров в предложении IN

Например, следующее утверждение:

SELECT * FROM t WHERE 
    key1 IN ('b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b');

Хотя предложение IN содержит много параметров, MySQL будет генерировать для него только интервал диапазона при разборе грамматики, то есть:['b', 'b'].

Проблема порядка параметров с предложением IN

Например, следующее утверждение:

SELECT * FROM t WHERE key1 IN ('c', 'b');

Есть ли разница между IN ('c', 'b') и IN ('b', 'c')? То есть, когда механизм хранения обрабатывает предложение IN ('c', 'b'), он сначала находитkey1 = 'c'записывай, а потом ищиkey1 = 'b'записывать? Если это так, возможно ли, что следующие два утверждения могут зайти в тупик:

事务T1中的语句一：
SELECT * FROM t WHERE 
    key1 IN ('b', 'c') FOR UPDATE;

事务T2中的语句二：
SELECT * FROM t WHERE 
    key1 IN ('c', 'b') FOR UPDATE;

Будьте уверены, когда интервал диапазона генерируется, интервал диапазона естественным образом сортируется, то есть, даже если условиеIN ('c', 'b'), оптимизатор также сначала позволит подсистеме хранения найти значение ключа в['b', 'b']Записи в этом интервале диапазона, а затем найти значение ключа в['c', 'c']записи в этом интервале диапазона.

Влияние системной переменной eq_range_index_dive_limit на предложение IN

Важно помнить, что оптимизатор MySQL решает использовать индекс для выполнения запроса просто потому, что:Стоимость использования этого индекса достаточно низкая. То есть, даже если у нас есть следующее утверждение:

SELECT * FROM t WHERE 
    key1 IN ('b', 'c');

Оптимизатору MySQL необходимо проанализировать, используются ли вторичные индексы.idx_key1При выполнении запроса значение ключа равно['b', 'b']и['c', 'c']Сколько записей содержится в этих двух диапазонах, а затем рассчитайте стоимость определенным образом. По сравнению со стоимостью полного сканирования таблицы выберите способ с меньшими затратами для выполнения запроса.

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

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

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

Когда использовать статистический метод индексного погружения и когда использовать статистический метод индексной статистики? Это зависит от значения системной переменной eq_range_index_dive_limit, давайте посмотрим на значение этой системной переменной на моей машине:

mysql> SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.20 sec)

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

Однако на это нужно обратить особое внимание: в MySQL 5.7.3 и более ранних версиях значение eq_range_index_dive_limit по умолчанию равно 10. Поэтому, если вы используете 5.7.3 и более ранние версии, для расчета стоимости запросов легко использовать статистику индекса вместо погружений в индекс. Когда в вашем запросе используется запрос IN, но индекс фактически не используется, вам следует подумать, не слишком ли мало значение eq_range_index_dive_limit.

Не по теме

Эта статья была впервые опубликована в паблике «Мы все маленькие лягушки».

Написание статей очень утомительно, и иногда вы чувствуете, что чтение идет очень гладко, что на самом деле является результатом бесчисленных правок за ним. Если вы думаете, что это хорошо, пожалуйста, помогите переслать его.Большое спасибо~ Вот мой публичный аккаунт "Мы все маленькие лягушки".

Категории