MySQL - индексирование столбца с преобладанием NULL-значений

Содержание

Вопрос или проблема
Ответ или решение
Индексация столбца с большинством NULL-значений в MySQL
Основная проблема
Как работает индексация
Пример оптимизации
Подходы к улучшению производительности
Заключение

Вопрос или проблема

У меня есть очень большая таблица с колонкой, в которой в основном находятся значения null. Я часто запрашиваю эту таблицу с условием x_column is not null.

Мне интересно, как мне её индексировать и какой лучший способ улучшить производительность в этом случае, потому что я читал в нескольких местах, что is not null даже не использует индекс. Что я могу сделать, чтобы использовать индекс здесь?

Смотрите это или это, например, но есть и другие. Я не хочу добавлять другое поле (вычисляемое поле), чтобы решить эту проблему. Есть ли другой, возможно более новый способ?

(Я использую innoDB)

Я читал в нескольких местах, что is not null даже не использует индекс.

То, что вы прочитали, неточно.

https://dev.mysql.com/doc/refman/8.4/en/is-null-optimization.html говорит:

MySQL может выполнить ту же оптимизацию на col_name IS NULL, которую он может использовать для col_name = constant_value.

Точно так же IS NOT NULL оптимизируется, как col_name <> constant_value. То есть это доступ по диапазону.

Демо:

Я протестировал MySQL 8.4.2, но это должно применяться к MySQL 8.0 и 5.x тоже.

mysql> create table mytable (id serial primary key, v varchar(10), x int, key(v));

mysql> explain select * from mytable where v is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: range
possible_keys: v
          key: v
      key_len: 43
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

Возможно, ссылки, которые вы читали и которые говорили, что is not null не использует индекс, основаны на какой-то версии MySQL 20 лет назад, но все текущие поддерживаемые версии действительно используют индекс в этом случае.

Ответ или решение

Индексация столбца с большинством NULL-значений в MySQL

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

Основная проблема

Как вы уже упомянули, на практике существует мнение, что использование условия IS NOT NULL не позволяет эффективно задействовать индексы. Однако это мнение является устаревшим и не совсем точным. В современных версиях MySQL (начиная с 5.x и вплоть до 8.0.x) условия типа IS NOT NULL могут действительно эффективно использовать индексы.

Как работает индексация

Как указано в документации MySQL, оператор IS NOT NULL может оптимизироваться аналогично условию col_name <> constant_value и использовать индексы при выполнении запросов. Это достигается благодаря тому, что MySQL осуществляет доступ к данным через диапазоны, что позволяет эффективно извлекать немногозначные строки из таблицы.

Пример оптимизации

Вы можете протестировать это в своей среде с помощью следующего SQL-кода:

CREATE TABLE mytable (id SERIAL PRIMARY KEY, v VARCHAR(10), x INT, KEY(v));

EXPLAIN SELECT * FROM mytable WHERE v IS NOT NULL;

Результатом выполнения команды EXPLAIN станет информация, которая продемонстрирует, что использование индекса эффективно. Вы увидите, что possible_keys указывает на ваш индекс v, а key — на используемый индекс.

Подходы к улучшению производительности

Индексация: Убедитесь, что столбец x_column, по которому вы делаете выборки, индексирован. Это обеспечит использование индексов для ускорения операций выбора.
Статистика и анализ: Периодически выполняйте команду ANALYZE TABLE mytable;, чтобы обновить статистику и обеспечить оптимальный план выполнения запросов.
Использование других конструкций: Если у вас есть возможность, используйте более сложные условия выбора (например, фильтры с использованием дополнительных критериев) для уменьшения общего объема данных, обрабатываемых в запросах.
Альтернативные решения: Хотя вы указали, что не хотите добавлять вычисляемое поле для решения проблемы, в некоторых случаях, если это возможно, можно создать отдельный столбец, который будет хранить дополнительные данные и, следовательно, улучшит время выборки при использовании условных выборок с IS NOT NULL.
Мониторинг: Регулярно отслеживайте производительность запросов. Если вы заметите наличие медленных запросов, возможно, будет полезно пересмотреть структуру вашей базы данных или индексацию.

Заключение

Индексация столбца с большинством NULL-значений — это достаточно сложная задача, но при правильной настройке и понимании особенностей MySQL можно добиться серьезных улучшений в производительности. Следуя рекомендациям, описанным выше, вы сможете более эффективно использовать индексы в своих запросах с условиями IS NOT NULL.

MySQL – индексирование столбца с преобладанием NULL-значений