Оптимизация фильтрации по rowid

Целевой сценарий использования фильтрации по rowid следующий:

• таблица использует доступ по ссылке к индексу IDX1
• но также имеет довольно ограниченное предикатное условие по другому индексу IDX2.

В этом случае полезно:

• Выполнить поиск только по индексу IDX2 и собрать rowid записей индекса в структуру данных, которая позволяет фильтрацию (назовем её $FILTER).
• При выполнении доступа по ссылке к IDX1 проверить $FILTER перед чтением всей записи.

Пример

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

SELECT ...
FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE
  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
  o_totalprice between 200000 and 230000;

Предположим, условие по l_shipdate очень ограничительно, что означает, что таблица lineitem должна быть первой в порядке объединения. Затем оптимизатор может использовать o_orderkey=l_orderkey равенство для выполнения поиска по индексу, чтобы получить порядок, из которого поступает строка line item. С другой стороны, o_totalprice between ... также может быть довольно избирательным.

С фильтрацией план запроса будет:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
         type: range
possible_keys: PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity
          key: i_l_shipdate
      key_len: 4
          ref: NULL
         rows: 98
        Extra: Using index condition
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: eq_ref|filter
possible_keys: PRIMARY,i_o_totalprice
          key: PRIMARY|i_o_totalprice
      key_len: 4|9
          ref: dbt3_s001.lineitem.l_orderkey
         rows: 1 (5%)
        Extra: Using where; Using rowid filter

Обратите внимание, что у таблицы orders есть «Использование фильтра rowid». В колонке type находится "|filter", а в колонке key показан индекс, который используется для построения фильтра. Колонке rows показывает ожидаемую избирательность фильтра, она составляет 5%.

Вывод ANALYZE FORMAT=JSON для таблицы orders покажет

    "table": {
      "table_name": "orders",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "i_o_totalprice"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["o_orderkey"],
      "ref": ["dbt3_s001.lineitem.l_orderkey"],
      "rowid_filter": {
        "range": {
          "key": "i_o_totalprice",
          "used_key_parts": ["o_totalprice"]
        },
        "rows": 69,
        "selectivity_pct": 4.6,
        "r_rows": 71,
        "r_selectivity_pct": 10.417,
        "r_buffer_size": 53,
        "r_filling_time_ms": 0.0716
      }

Обратите внимание на элемент rowid_filter. Внутри него есть элемент range. selectivity_pct — это ожидаемая избирательность, сопровождаемая r_selectivity_pct, показывающей фактическую наблюдаемую избирательность.

Подробности

• Оптимизатор принимает решение о том, когда использовать фильтр, на основе затрат.
• Структура данных фильтра в настоящее время представляет собой упорядоченный массив rowid. (Фильтр Блума был бы здесь лучше и, вероятно, будет представлен в будущих версиях).
• Оптимизация должна поддерживаться движком хранилища. В настоящее время она поддерживается InnoDB и MyISAM. Она не поддерживается в размеченных таблицах.

Управление

Фильтрацию по rowid можно включать/выключать с помощью флага rowid_filter в переменной optimizer_switch. По умолчанию оптимизация включена.