IS NULL, IS NOT NULL, != не может использовать индексы в MySQL? Дурь несусветная!

MySQL

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

Я не знаю, когда это началось, но в Интернете ходит поговорка:

Когда предложение WHERE в MySQL содержит условия IS NULL, IS NOT NULL и !=, индексный запрос использовать нельзя, и можно использовать только полное сканирование таблицы.

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

CREATE TABLE s1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

В этой таблице 10000 записей:

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

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

image_1dfqmch3p1f881eqmvb29gk1tom6e.png-40.7kB

image_1dfqmbf5616fb1g0b1trv13elsst61.png-40.7kB

image_1dfqmarklhku131o18rs15281min5k.png-40.2kB

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

Как значения NULL хранятся в записях

В MySQL каждая запись имеет свой фиксированный формат, мы используемInnoDBмеханизм храненияCompactВозьмем формат строки в качестве примера, давайте посмотримNULLКак хранится значение. существуетCompactВ линейном формате запись состоит из следующих частей:

image_1dfqmp377ebqgqf15e1tuv1qri6r.png-72.8kB

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

CREATE TABLE record_format_demo (
     c1 VARCHAR(10),
     c2 VARCHAR(10) NOT NULL,
     c3 CHAR(10),
     c4 VARCHAR(10)
 ) CHARSET=ascii ROW_FORMAT=COMPACT;

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

  1. Во-первых, разрешить хранение в таблице статистикиNULLЧто такое столбцы .

    Как мы уже говорили, столбец первичного ключа,NOT NULLИзмененные столбцы не могут быть сохраненыNULLзначение, поэтому эти столбцы не будут включены в статистику. скажем, столrecord_format_demo3 колонкиc1,c3,c4разрешено хранитьNULLзначение иc2столбецNOT NULLукрашение, хранение не допускаетсяNULLценность.

  2. Если в таблице не разрешено хранениеNULLколонка, затемNULL值列表больше не существует, иначе он будет хранить все разрешенныеNULLСтолбец соответствует двоичному биту, а двоичные биты расположены в обратном порядке в соответствии с порядком столбцов.Значения двоичных битов следующие:

    • Значение двоичного бита равно1, значение, представляющее столбец, равноNULL.
    • Значение двоичного бита равно0, значение, представляющее столбец, неNULL.

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

    image_1dfqn3dt810cpog1l4710q637q78.png-19.3kB

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

  3. дизайнInnoDBдядя правилаNULL值列表Он должен быть представлен битами целого числа байтов.Если количество используемых двоичных битов не является целым числом байтов, к старшему биту байта добавляется 0.

    поверхностьrecord_format_demoДопустимо только 3 значения, т.к.NULLСтолбец , соответствующий 3 двоичным битам, меньше одного байта, поэтому 0 добавляется к старшему разряду байта, и эффект следующий:

    image_1dfqn48071s0i104314m31isi1ks97l.png-37.7kB

    И так далее, если в таблице разрешено 9NULL, то эта записьNULL值列表Для представления этой части требуется 2 байта.

Предположим, мы сейчасrecord_format_demoВставьте запись в таблицу:

INSERT INTO record_format_demo(c1, c2, c3, c4)
    VALUES('eeee', 'fff', NULL, NULL);

эта записьc1,c3,c4в этих 3 столбцахc3иc4ЗначенияNULL, поэтому ситуация с двоичными битами, соответствующими этим трем столбцам, такова:

image_1dfqng28g7df1l68r4737p3a882.png-38.6kB

Итак, эта записьNULL值列表В шестнадцатеричном представлении это:0x06.

Как запись со значением ключа NULL хранится в дереве B+?

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

image_1dfqnp86e76v16h31l7qk21v458f.png-296kB

И кластеризованный индекс, и вторичный индекс соответствуют тому же, что и на рисунке выше.B+Деревья (то есть индексов столько, сколько соответствующих деревьев)B+дерево), но:

  • Для кластеризованного индекса записи на странице сортируются по значению первичного ключа, для вторичного индекса записи на странице сортируются по значению заданного столбца индекса.

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

  • Для кластеризованного индекса страница, соответствующая дочернему узлу дерева B+, хранит полную пользовательскую запись (то есть запись содержит все значения столбцов, которые мы определили, а также содержит некоторые скрытые столбцы, добавленные самой InnoDB); индекс уровня, который хранится на странице, соответствующей дочернему узлу дерева B+, является только索引列的值 + 主键值.

Как указано, значение первичного ключа записи не может быть сохранено.NULLзначение, поэтому результат предложения WHERE в следующем операторе должен бытьFALSE:

SELECT * FROM tbl_name WHERE primary_key IS NULL;

Такой оптимизатор операторов может сам определить, что предложение WHERE должно быть NULL, поэтому он вообще его не выполнит, не верьте нам (дополнительная информация указывает на то, что предложение WHERE вообще неверно):

image_1dfqofhth2941mtorq72f1nqf8s.png-35.5kB

Для вторичных индексов значение индексированного столбца может бытьNULL. что для значения столбца индексаNULLДля записей вторичного индекса они помещаются вB+Где дерево? Ответ: в крайнем левом углу дерева B+. Допустим, у нас есть следующий запрос:

SELECT * FROM s1 WHERE key1 IS NULL;

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

image_1dfqqjqnahm6176uta91j7j1q8ram.png-52.9kB

Из рисунка видно, что дляs1вторичный индекс в таблицеidx_key1По стоимостиNULLЗаписи вторичного индекса помещаются вB+Крайний левый угол дерева, это связано с конструкциейInnoDBУ дяди есть такое правило:

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

То есть они ставят SQL вNULLЗначение считается наименьшим значением в столбце.

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

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

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

С того времениIS NULL,IS NOT NULL,!=В этих условиях могут использоваться индексы, поэтому когда следует использовать индексы и когда следует использовать полное сканирование таблицы?

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

  • Стоимость чтения записей вторичного индекса

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

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

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

SELECT * FROM s1 WHERE key1 IS NULL;

Оптимизатор проанализирует, что этому запросу нужно найти толькоkey1значениеNULL, а затем получить доступ к вторичному индексуidx_key1, посмотрите на значениеNULLСколько записей имеется (если количество подходящих записей вторичного индекса невелико, статистические результаты точны, если их слишком много, будет использоваться определенный метод для вычисления нечеткого значения, конечно, алгоритм также более хлопотный , не будем расширять Саид, сказано в буклете), называется этот метод, при котором оптимизатор сначала обращается к индексу для подсчета количества записей индекса, которые необходимо просмотреть до фактического выполнения запросаindex dive. Конечно, для некоторых запросов, скажем, в предложении WHERE есть условие IN, а условие IN содержит много параметров, например:

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

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

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

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

Не верь слухам, не пропускай слухи

Как видите, в MySQL решение не использовать индекс для выполнения запроса простое: достаточно ли малы затраты? а не используется ли он в предложении WHEREIS NULL,IS NOT NULL,!=эти условия. Все, пожалуйста, опровергайте слухи в будущем, это не так сложно, это просто стоимость.

Не по теме

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

Категории