Стоимость запроса MySQL и интервал диапазона

задняя часть MySQL

предыдущий постIS NULL, IS NOT NULL, != не может использовать индексы в MySQL? Дурь несусветная!В Nagging появляются в пункте WHERE NULL, не является NULL,! = Индекс все еще возможен для использования этих условий, подчеркивала вопрос о стоимости запроса, запрос многих студентов, которые будут отражать концепцию стоимости или ничего, мы Сегодня возвращайся немного потустороннее наг.

Древовидная структура B+

мы говорим дляInnoDBЧто касается механизма хранения, то данные в таблице хранятся в так называемом дереве B+ Каждый раз, когда мы строим дополнительный индекс, это эквивалентно построению дополнительного дерева B+.

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

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

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

Пример

Возьмем пример:

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

Эта таблица содержит 2 индекса (то есть 2 B+ дерева):

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

  • заkey1Вторичный индекс, созданный столбцомidx_key1.

Вставляем в таблицу несколько записей:

INSERT INTO t VALUES
    (1, 30, 'b'),
    (2, 80, 'b'),
    (3, 23, 'b'),
    (4, NULL, 'b'),
    (5, 11, 'b'),
    (6, 53, 'b'),
    (7, 63, 'b'),
    (8, NULL, 'b'),
    (9, 99, 'b'),
    (10, 12, 'b'),
    (11, 66, 'b'),
    (12, NULL, 'b'),
    (13, 66, 'b'),
    (14, 30, 'b'),
    (15, 11, 'b'),
    (16, 90, 'b');

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

image_1dg2m0lab11lhlib1eq7g5dius2d.png-77.1kB

s1Схематическая диаграмма вторичного индекса таблицы выглядит так:

image_1dg2m44tf1o4cv3nro1oul111n2q.png-66.9kB

Как видно из рисунка, значениеNULLЗаписи вторичного индекса размещаются в крайнем левом углу дерева B+, потому что дяди, разработавшие InnoDB, предусмотрели:

We define the SQL null to be the smallest possible value of a field.

то естьNULLЗначение - самый маленький.

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

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

SELECT * FROM t WHERE key1 = 53;

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

image_1dg2me2kg1tvh1ohlera1ge712837.png-80.5kB

Опишите этот процесс словами:

  • Сначала передайте вторичный индексidx_key1соответствующийB+Дерево быстро перейти кkey1значение столбца53Запись вторичного индекса .

  • Затем передайте значение первичного ключа в записи вторичного индекса, то есть6выполнить回表операции, то есть перейти к кластеризованному индексу и снова найти егоidзначение столбца6Кластерная запись индекса.

Советы: Записи в листовых узлах дерева B+ сортируются по значению ключа в порядке возрастания, найти запись в листовом узле можно очень быстро с помощью индекса дерева B+. Однако, поскольку мы не будем придираться к внутренним узлам и каталогам страниц, процесс поиска записи в конечном узле с помощью индекса дерева B+ подробно обсуждаться не будет. был подробно описан в буклете Nuggets «Понимание MySQL в корне».

Такой запрос:

SELECT * FROM t WHERE key1 > 20 AND key1 < 50;

Схема его выполнения следующая:

image_1dg2ohcgd1psqaut11levmk1oki3k.png-88.7kB

На словах это так:

  • Сначала передайте вторичный индексidx_key1соответствующийB+Дерево быстро позиционируется, чтобы удовлетворитьkey1 > 20Первая запись , которую мы показываем на рисункеkey1значение23, а затем по значению первичного ключа во вторичном индексе3Выполните операцию формы возврата, чтобы получить полную запись пользователя и отправить ее клиенту.

  • Тогда по полученному на предыдущем шагеkey1значение столбца23записей вторичного индексаnext_recordатрибут, найти следующую запись вторичного индекса рядом с ним, то естьkey1значение столбца30, а затем выполните операцию возврата таблицы, чтобы получить полную запись пользователя и отправить ее клиенту.

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

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

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

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

Итак, вывод теперь такой:Условием определения того, может ли запрос использовать индекс, является относительно низкая доля сканируемых записей вторичного индекса во всех записях..

Анализ определенных условий запроса

Давайте посмотрим на вхождения предложения WHERE отдельноIS NULL,IS NOT NULL,!=Как оптимизатор принимает решения в этих условиях.

ЕСТЬ НУЛЕВОЙ случай

Скажем, этот запрос:

SELECT * FROM t WHERE key1 IS NULL;

Прежде чем оптимизатор фактически выполнит запрос, он сначала посетит индекс в небольшом количестве, чтобы исследоватьkey1существует[NULL, NULL]Сколько записей в этом интервале:

image_1dg2r7u6s1ms3162d1erp52immf8c.png-28.9kB

Советы: [NULL, NULL] Этот интервал означает, что в интервале есть только одно значение NULL.

В ходе исследования оптимизатор обнаружил, что отношение сканируемых записей вторичного индекса к общему количеству записей равно3/16, кажется, что использовать вторичный индекс для этого запроса надежнее, поэтому в плане выполнения отображается использование этогоidx_key1для выполнения запроса:

image_1dg2pki1r12stmt419ae1ep7125a5e.png-40.2kB

Дело НЕ НУЛЕВОЕ

Скажем, этот запрос:

SELECT * FROM t WHERE key1 IS NOT NULL;

Прежде чем оптимизатор фактически выполнит запрос, он сначала посетит индекс в небольшом количестве, чтобы исследоватьkey1существует(NULL, +∞)Сколько записей в этом интервале:

image_1dg2rbnspvavkk710k0adkhpua9.png-28.9kB

Советы: Здесь мы рассматриваем NULL как минимальное значение, которое вы можете считать меньшим, чем -∞. Также обратите внимание, что интервал (NULL, +∞) является открытым интервалом, что означает, что он не включает значения NULL.

В ходе исследования оптимизатор обнаружил, что отношение сканируемых записей вторичного индекса к общему количеству записей равно13/16, и, очевидно, это соотношение уже очень велико, поэтому оптимизатор решает использовать полное сканирование таблицы для выполнения запроса:

image_1dg2pu5kc79oh5c1m2vj131inu6l.png-36.4kB

Как это использоватьIS NOT NULLИспользует ли условный запрос вторичным индексом? Это не просто, пусть таблица встретитсяIS NOT NULLЗаписи условия достаточно, мы можем его выполнить:

UPDATE t SET key1 = NULL WHERE key1 < 80;

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

SELECT * FROM t WHERE key1 IS NOT NULL;

Прежде чем оптимизатор фактически выполнит запрос, он сначала посетит индекс в небольшом количестве, чтобы исследоватьkey1существует(NULL, +∞)Сколько записей в этом интервале:

image_1dg2remb8gts2j6rjnvuic8b6.png-29.3kB

В ходе исследования оптимизатор обнаружил, что отношение сканируемых записей вторичного индекса к общему количеству записей равно3/16, кажется, что использовать вторичный индекс для этого запроса надежнее, поэтому в плане выполнения отображается использование этогоidx_key1для выполнения запроса:

image_1dg2q4glvgea1kt01q71h5q1b2p72.png-40kB

!= случай

Скажем, этот запрос:

SELECT * FROM t WHERE key1 != 80;

Прежде чем оптимизатор фактически выполнит запрос, он сначала посетит индекс в небольшом количестве, чтобы исследоватьkey1существует(NULL, 80)и(80, +∞)Сколько записей в этих двух интервалах:

image_1dg2rst6h1egu1cue1idnbk67s1bj.png-31.5kB

В ходе исследования оптимизатор обнаружил, что отношение сканируемых записей вторичного индекса к общему количеству записей равно2/16, кажется, что использовать вторичный индекс для этого запроса надежнее, поэтому в плане выполнения отображается использование этогоidx_key1для выполнения запроса:

image_1dg2pki1r12stmt419ae1ep7125a5e.png-40.2kB

Подожди! Зачем делать планrowsКак насчет значения столбца 3? ? ? Что за хрень, явно только 2 записи соответствуют условиям. Ха-ха, давайте перечислим количество подходящих записей, найденных в каждом интервале:

  • (NULL, 80)В интервале 0 записей, удовлетворяющих условиямkey1 != 80.

  • (80, +∞)Два интервала записи удовлетворяют условиюkey1 != 80.

Но у дяди, который проектировал оптимизатор, есть здесь оговорка:Когда количество записей, удовлетворяющих заданным условиям в определенном диапазоне, равно 0, оно резко разбивается на 1.. То есть фактический оптимизатор думает, что в(NULL, 80)В этом диапазоне есть 1 запись, соответствующая критериямkey1 != 80. Так что выполняйте планrowsСтолбец просто показывает3.

Советы: Далее объяснение дяди, который сам проектировал оптимизатор, когда количество записей в определенном диапазоне, удовлетворяющем заданным условиям, равно 0. Оптимизатор MySQL, кажется, считает, что оценка 0 строк всегда точна и может вернуть результат «Пустой набор» на основе этого Точность не гарантируется, и даже если бы это было так, для чтения с блокировкой мы все равно должны выполнить поиск для установите блокировку следующего ключа.Добавьте 1 к значению, чтобы убедиться, что MySQL не делает предположение!

Суммировать

До сих пор мы анализировалиIS NULL,IS NOT NULL,!=При каких обстоятельствах выполняется запрос этих трех условий с использованием вторичного индекса, основной вывод таков:Стоимость определяет план выполнения, который не имеет ничего общего с использованием используемых условий запроса. Оптимизатор сначала делит несколько интервалов диапазонов для вторичных индексов, которые могут быть использованы, а затем исследует, сколько записей содержится в этих интервалах.Сумма записей вторичных индексов в этих интервалах диапазонов составляет определенную долю от общего числа записей. . оптимизатор откажется от использования вторичного индекса для выполнения запроса и вместо этого будет использовать полное сканирование таблицы.

Советы: На самом деле, слишком большое количество делений диапазона также повлияет на принятие оптимизатором решения.Например, если в условии IN слишком много параметров, это уменьшит вероятность решения оптимизатора использовать вторичный индекс для выполнения запроса. Кроме того, у оптимизатора есть два способа исследовать количество записей индекса в определенном диапазоне, один из них — это так называемое погружение индекса (этот метод точен, когда данных мало, и есть некоторое отклонение, когда данные малы). большой), следует опираться на индексную статистику, то есть статистические данные для исследования (статистика в этом случае очень неточная, а отклонение иногда сверхогромное), в любом случае, какой бы метод ни использовался, оптимизатор будет число индексных записей в вычисляется. Подробные алгоритмы приведены в брошюре для этих двух методов расследования, и, конечно, они занимают много места.

Не по теме

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

Категории