Функциональный индекс MySQL с IS NULL
У меня есть большая таблица в MySQL с медленным запросом, отчасти из-за такого условия, как WHERE sent_at IS NULL. sent_at — это столбец даты и времени, допускающий значение NULL, но здесь мы хотим только проверить, имеет ли он значение NULL, поэтому стандартный индекс для всех значений был бы расточительным.
Я обнаружил, что создание функционального индекса с помощью ISNULL() или IS NULL работает, но никогда не используется для запросов.
Например.
ALTER TABLE users ADD INDEX index_users_sent_at_is_null ((ISNULL(sent_at)));
Далее следует запрос:
SELECT COUNT(*) FROM users WHERE (sent_at IS NULL);
Будет иметь вывод (JSON) EXPLAIN с access_type: "ALL" и отсутствием возможных или выбранных ключей.
Выполнение эквивалента для YEAR(sent_at) работает, как и ожидалось, с использованием соответствующего функционального индекса, поэтому в IS NULL есть что-то особенное, даже несмотря на то, что виртуальный скрытый столбец, который правильно создает функциональный индекс, содержит 0 или 1.
Есть ли элегантный способ заставить это работать?
Этот индекс может иметь два возможных значения: true или false. Избирательность такого индекса, вероятно, слишком низка, чтобы MySQL мог его учесть.
@Shadow Я ожидаю, что объяснение по-прежнему покажет это как возможный ключ.
кажется невозможным индексировать значение null без сгенерированного столбца (или в законопроекте об обходном решении предлагается): dbfiddle.uk/060bclIz
Ответы 1
Я протестировал ваш пример индекса с MySQL 8.0.37 на своем локальном Macbook. Могу воспроизвести описанную вами проблему:
mysql> explain SELECT COUNT(*) FROM users WHERE (sent_at IS NULL)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
Это связано с https://bugs.mysql.com/bug.php?id=99775, где функциональный индекс не используется, когда вы используете выражение в предложении WHERE без сравнения результата с конкретным значением. Эта ошибка была проверена в 2020 году, но еще не исправлена (по состоянию на июнь 2024 года).
В качестве обходного пути вы можете сравнить выражение с определенным постоянным значением, а затем запрос сможет использовать индекс:
mysql> explain SELECT COUNT(*) FROM users WHERE (sent_at IS NULL) = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ref
possible_keys: index_users_sent_at_is_null
key: index_users_sent_at_is_null
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
Вы можете подумать, что логически эти запросы одинаковы, и вам не нужно использовать обходной путь для оптимизации. Да, я согласен. Вот почему это ошибка.
Источником этой ошибки является отсутствие типа данных BOOLEAN в MySQL. Тип данных выражения (sent_at IS NULL) не несуществующий BOOLEAN, а INTEGER (т. е. 4-байтовое число). Мы можем убедиться в этом, исследуя индексную часть файла тела таблицы: в ней хранится 80 00 00 00 для TRUE и 00 00 00 00 для FALSE. Таким образом, формально серверу необходимо применить функцию к значению выражения, которое хранится в индексе, и определить, не является ли значение FALSE/NULL. Вместо немедленного сравнения с каким-то определенным значением.
Вы проверили мощность индекса? Поможет ли вам изменить индексное выражение на
sent_at IS NULL(в идеале обе формы должны быть эквивалентны, но мы говорим о MySQL).