Оптимизация not_null_range_scan

Оптимизация NOT NULL range scan позволяет оптимизатору создавать диапазонные сканирования из условий NOT NULL, которые он смог вывести из условия WHERE.

Оптимизация появилась в MariaDB 10.5.0. Она не включена по умолчанию; для её включения нужно установить optimizer_switch флаг.

Описание

Базовый (но немного искусственный) пример:

create table items (
  price  decimal(8,2),
  weight decimal(8,2),
  ...
  index(weight)
);

-- Find items that cost more than 1000 $currency_units per kg:
set optimizer_switch='not_null_range_scan=on';
explain
select * from items where items.price > items.weight / 1000;

Условие WHERE в этом виде не может использоваться для диапазонных сканирований. Однако, можно сделать вывод, что оно будет отбрасывать строки, содержащие NULL для weight. То есть, вывести дополнительное условие, что

weight IS NOT NULL

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

+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|    1 | SIMPLE      | items | range | NULL          | weight | 5       | NULL | 1    | Using where |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+

Вот ещё один пример, более сложный, но основанный на реальном запросе. Рассмотрим запрос с объединением

-- Find orders that were returned
select * from current_orders as O, order_returns as RET
where 
  O.return_id= RET.id;

Здесь оптимизатор может вывести условие "return_id IS NOT NULL". Если большая часть заказов не возвращена (и поэтому имеет NULL для return_id), можно использовать доступ к диапазону, чтобы сканировать только те заказы, которые были возвращены.

Управление оптимизацией

Оптимизация не включена по умолчанию. Её можно включить следующим образом:

 set optimizer_switch='not_null_range_scan=on';

Трейс оптимизатора

TODO.

См. также

• MDEV-15777 - отчет об ошибке JIRA, который привел к оптимизации
• Оптимизация фильтрации NULL — это родственная оптимизация в MySQL и MariaDB. Она использует выведенные условия NOT NULL для выполнения фильтрации (но не доступа к индексу)