Для индекса с одной частью интервалы значений индекса удобно представлять соответствующими условиями в WHERE запроса, обозначаемыми как условия диапазона, а не “интервалами”.

Определение условия диапазона для индекса с одной частью следующее:

“Значение константы” в предыдущих описаниях означает одно из следующего:

Ниже приведены примеры запросов с условиями диапазона в WHERE запроса:

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

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

MySQL пытается извлечь условия диапазона из WHERE запроса для каждого из возможных индексов. В процессе извлечения условия, которые не могут быть использованы для построения условия диапазона, отбрасываются, условия, которые дают перекрывающиеся диапазоны, объединяются, а условия, которые дают пустые диапазоны, удаляются.

Рассмотрим следующее утверждение, где key1 — это индексированный столбец, а nonkey — нет:

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

Процесс извлечения для ключа key1 следующий:

В общем случае (и как показано в предыдущем примере), условие, используемое для сканирования по диапазону, менее строгое, чем WHERE запрос. MySQL выполняет дополнительную проверку, чтобы отфильтровать строки, удовлетворяющие условию диапазона, но не всему WHERE запросу.

Алгоритм извлечения условия диапазона может обрабатывать вложенные конструкции AND/OR произвольной глубины, и его результат не зависит от порядка, в котором условия появляются в WHERE запросе.

MySQL не поддерживает слияние нескольких диапазонов для метода доступа range для пространственных индексов. Чтобы обойти это ограничение, можно использовать UNION с идентичными SELECT операторами, за исключением того, что каждое пространственное условие вы помещаете в различные SELECT.

Условие диапазона для индекса из нескольких частей является расширением условия диапазона для однокомпонентного индекса. Условие диапазона для индекса из нескольких частей ограничивает строки индекса, которые находятся в пределах одного или нескольких интервалов кортежей ключей. Интервалы кортежей ключей определяются над набором кортежей ключей с использованием упорядочивания из индекса.

Например, рассмотрим индекс из нескольких частей, определенный как key1(key_part1, key_part2, key_part3), и следующий набор кортежей ключей, упорядоченных по ключу:

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

Условие key_part1 = 1 определяет этот интервал:

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

Интервал охватывает 4-й, 5-й и 6-й кортежи в предыдущем наборе данных и может использоваться методом доступа к диапазону.

В противоположность этому, условие key_part3 = 'abc' не определяет единственный интервал и не может использоваться методом доступа к диапазону.

Следующие описания более подробно показывают, как работают условия диапазона для индексов из нескольких частей.

Для описания того, как выполняются оптимизации для объединения или устранения интервалов для условий диапазона для однокомпонентного индекса, см. Метод доступа к диапазону для однокомпонентных индексов. Аналогичные шаги выполняются для условий диапазона для индексов из нескольких частей.

Рассмотрим эти выражения, где col_name - индексированный столбец:

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

Каждое выражение истинно, если col_name равно одному из нескольких значений. Эти сравнения являются сравнениями диапазона равенства (где «диапазон» — это одно значение). Оптимизатор оценивает стоимость чтения соответствующих строк для сравнений диапазона равенства следующим образом:

При использовании погружений в индекс оптимизатор производит погружение в каждом конце диапазона и использует количество строк в диапазоне как оценку. Например, выражение col_name IN (10, 20, 30) содержит три диапазона равенства, и оптимизатор производит два погружения на каждый диапазон для получения оценки строк. Каждая пара погружений даёт оценку количества строк, имеющих данное значение.

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

Системная переменная eq_range_index_dive_limit позволяет настроить количество значений, при которых оптимизатор переключается с одной стратегии оценки строк на другую. Чтобы разрешить использование погружений в индекс для сравнений до N диапазонов равенства, установите eq_range_index_dive_limit на N + 1. Чтобы отключить использование статистики и всегда использовать погружения в индекс независимо от N, установите eq_range_index_dive_limit на 0.

Для обновления статистики индексов таблицы для лучших оценок используйте ANALYZE TABLE.

До MySQL 8.4 нет способа пропустить использование погружений в индекс для оценки полезности индекса, кроме использования системной переменной eq_range_index_dive_limit. В MySQL 8.4 пропуск погружений в индекс возможен для запросов, удовлетворяющих всем этим условиям:

Для EXPLAIN FOR CONNECTION вывод меняется следующим образом, если погружения в индекс пропускаются:

Без FOR CONNECTION вывод EXPLAIN не меняется, когда погружения в индекс пропускаются.

После выполнения запроса, для которого погружения в индекс пропускаются, соответствующая строка в таблице Информационной схемы OPTIMIZER_TRACE содержит значение index_dives_for_range_access равное skipped_due_to_force_index.

Рассмотрим следующий сценарий:

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

Для выполнения этого запроса MySQL может выбрать сканирование индекса для извлечения всех строк (индекс включает все столбцы, которые нужно выбрать), а затем применить условие f2 > 40 из клаузы WHERE для получения конечного набора результатов.

Сканирование диапазона более эффективно, чем полное сканирование индекса, но в этом случае не может быть использовано, так как нет условия для f1, первого столбца индекса. Оптимизатор может выполнить несколько сканирований диапазонов, по одному для каждого значения f1, используя метод под названием Skip Scan, аналогичный Loose Index Scan (см. Раздел 10.2.1.17, «Оптимизация GROUP BY»):

Для набора данных, показанного ранее, алгоритм работает следующим образом:

Использование этой стратегии уменьшает количество обращённых строк, потому что MySQL пропускает строки, которые не соответствуют каждому построенному диапазону. Этот метод доступа Skip Scan применим при следующих условиях:

Использование Skip Scan показано в выводе EXPLAIN следующим образом:

Использование Skip Scan показано в выводе трассировки оптимизатора элементом "skip scan" следующего формата:

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

Вы также можете увидеть элемент "best_skip_scan_summary". Если Skip Scan выбран в качестве лучшего варианта доступа диапазона, записывается "chosen_range_access_summary". Если Skip Scan выбран в качестве наилучшего метода доступа в целом, присутствует элемент "best_access_path".

Использование Skip Scan зависит от значения флага skip_scan системной переменной optimizer_switch. По умолчанию этот флаг равен on. Чтобы его отключить, установите skip_scan на off.

Помимо использования системной переменной optimizer_switch для управления использованием Skip Scan оптимизатором на уровне сеанса, MySQL поддерживает подсказки оптимизатора, чтобы повлиять на оптимизатор на уровне каждого оператора. См. Раздел 10.9.3, «Подсказки оптимизатора».

Оптимизатор может применять метод доступа по диапазону к запросам такого вида:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

Ранее для использования сканирования по диапазону необходимо было писать запрос следующим образом:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

Для использования сканирования по диапазону запросы должны удовлетворять этим условиям:

Дополнительную информацию об оптимизаторе и конструкторах строк см. в разделе 10.2.1.22 «Оптимизация выражений конструкторов строк».

Для управления памятью, доступной оптимизатору диапазона, используйте системную переменную range_optimizer_max_mem_size:

Для отдельных запросов, которые превышают доступную память для оптимизации диапазона и для которых оптимизатор переходит к менее оптимальным планам, увеличение значения range_optimizer_max_mem_size может улучшить производительность.

Для оценки необходимого объёма памяти для обработки выражения диапазона используйте следующие рекомендации: