Оптимизация распространения равенства

Основная идея

Рассмотрим запрос с условием WHERE:

WHERE col1=col2 AND ...

условие WHERE будет истинным только если col1=col2. Это означает, что в остальной части условия WHERE вхождения col1 можно заменить на col2 (с некоторыми ограничениями, которые обсуждаются в следующем разделе). Это позволяет оптимизатору вывести дополнительные ограничения.

Например:

WHERE col1=col2 AND col1=123

позволяет вывести новое равенство: col2=123

WHERE col1=col2 AND col1 < 10 

позволяет сделать вывод, что col2<10.

Замена тождества и сравнения

Однако существуют некоторые ограничения на то, где можно выполнить замену.

Первый и очевидный пример — тип данных строка и наборы правил сопоставления. Наиболее часто используемые наборы правил сопоставления в SQL являются «регистронезависимыми», то есть 'A'='a'. Кроме того, большинство наборов правил сопоставления имеют атрибут «PAD SPACE», что означает, что сравнение игнорирует пробелы в конце значения, 'a'='a '.

Теперь рассмотрим запрос:

INSERT INTO t1 (col1, col2) VALUES ('ab', 'ab   ');
SELECT * FROM t1 WHERE col1=col2 AND LENGTH(col1)=2

Здесь col1=col2, значения «равны». В то же время LENGTH(col1)=2, тогда как LENGTH(col2)=4, что означает, что нельзя выполнить замену для аргумента LENGTH(...).

Это не только наборы правил сопоставления. Аналогичные явления наблюдаются при сравнении равенства столбцов разных типов данных. Точные критерии, когда они происходят, довольно запутанны.

Вывод: иногда X=Y не означает, что можно заменить любую ссылку на X на Y. То, что МОЖНО сделать, это все еще заменить вхождение в сравнения <, >, >=, <=, и т. д.

Вот как мы получаем два вида замен:

• Замена тождества: X=Y, и любое вхождение X можно заменить на Y.
• Замена сравнения: X=Y, и вхождение X в сравнение (X<Z) можно заменить на Y (Y<Z).

Место в оптимизации запросов

(Черновик описания): Давайте посмотрим, как распространение равенства интегрируется с остальной частью процесса оптимизации запросов.

• Сначала строятся несколько равенств (TODO пример из трассировки оптимизатора)
  • Если несколько равенств включают константу, поля заменяются константой, если это возможно.

• С этого момента все оптимизации, такие как оптимизация диапазона, доступ к ссылкам и т. д., используют несколько равенств: когда они видят ссылку на tableX.columnY где-то, они также проверяют все столбцы, которые равны tableX.columnY.

• После выбора порядка соединения оптимизатор проходит по условию WHERE и заменяет каждую ссылку на поле на «лучшую» — ту, которую можно проверить как можно скорее.
  • Затем части условия WHERE присоединяются к таблицам, где их можно проверить.

Взаимодействие с оптимизацией ORDER BY

Рассмотрим запрос:

SELECT ... FROM ... WHERE col1=col2 ORDER BY col2

Предположим, есть INDEX(col1). Оптимизатор MariaDB может определить, что он может использовать индекс на col1 (или сортировать по значению col1) для решения ORDER BY col2.

Трассировка оптимизатора

Посмотрите на эти элементы:

• condition_processing
• attaching_conditions_to_tables

Дополнительные сведения

Распространение равенства происходит не только в верхней части условия WHERE. Оно выполняется «на всех уровнях», где уровень — это:

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