Руководство по трассировке оптимизатора

Трассировка оптимизатора использует формат JSON. Это, по сути, структурированный журнал, показывающий действия, предпринятые оптимизатором запросов.

Пример

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

MariaDB> explain select * from t1 where a<10;
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL | 10   | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

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

MariaDB> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
                            QUERY: select * from t1 where a<10
                            TRACE: 
{
  "steps": [
    {
      "join_preparation": { ... }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          { "condition_processing": { ... } },
          { "table_dependencies": [ ... ] },
          { "ref_optimizer_key_uses": [ ... ] },
          { "rows_estimation": [
              {
                "range_analysis": {
                   "analyzing_range_alternatives" : { ... },
                  "chosen_range_access_summary": { ... },
                },
                "selectivity_for_indexes" : { ... },
                "selectivity_for_columns" : { ... }
              }
            ]
          },
          { "considered_execution_plans": [ ... ] },
          { "attaching_conditions_to_tables": { ... } }
         ]
      }
    },
    {
      "join_execution": { ... }
    }
  ]
}

Структура трассировки

Для каждого SELECT есть два «Шага»:

• join_preparation
• join_optimization

Подготовка объединения демонстрирует ранние переписывания запроса. join_optmization — это место, где выполняется большая часть оптимизаций запроса. Они включают:

• condition_processing — базовые переписывания в условиях WHERE/ON.
• ref_optimizer_key_uses — Построение возможных способов выполнения доступа ref и eq_ref.
• rows_estimation — Учёт доступа range и index_merge.
• considered_execution_plans — Сама оптимизация объединения, то есть выбор порядка объединения.
• attaching_conditions_to_tables — После того, как порядок объединения установлен, части условия WHERE «присоединяются» к таблицам, чтобы отфильтровать строки как можно раньше.

Эти шаги выполняются только для одного SELECT. Если запрос содержит подзапросы, каждый SELECT будет иметь эти шаги, и будут дополнительные шаги/переписывания для обработки самого подзапроса.

Извлечение компонентов трассировки

Если вас интересует какая-то определённая часть трассировки, в MariaDB есть две полезные функции:

• JSON_EXTRACT извлекает часть документа JSON
• JSON_DETAILED представляет её в удобочитаемом виде.

Например, содержимое узла analyzing_range_alternatives можно извлечь следующим образом:

MariaDB> select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) 
   ->   from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')): [
    {
        "range_scan_alternatives": 
        [
            {
                "index": "a_b_c",
                "ranges": 
                [
                    "(1) <= (a,b) < (4,50)"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": false,
                "rows": 4,
                "cost": 6.2509,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": []
    }
]

Примеры различной информации в трассировке

Базовые переписывания

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

explain select * from t1 where not (col1 >= 3);

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

"steps": [
  {
    "join_preparation": {
      "select_id": 1,
      "steps": [
        {
          "expanded_query": "select t1.a AS a,t1.b AS b,t1.col1 AS col1 from t1 where t1.col1 < 3"
        }

Здесь видно, что NOT был удалён.

Аналогично, видно, что IN(...) с одним элементом эквивалентен равенству:

explain select * from t1 where col1  in (1);

покажет

  "join_preparation": {
    "select_id": 1,
    "steps": [
      {
        "expanded_query": "select t1.a AS a,t1.b AS b,t1.col1 AS col1 from t1 where t1.col1 = 1"

В то же время, преобразование столбца UTF-8 в UTF-8 не удаляется:

explain select * from t1 where convert(utf8_col using utf8) = 'hello';

покажет

  "join_preparation": {
    "select_id": 1,
    "steps": [
      {
        "expanded_query": "select t1.a AS a,t1.b AS b,t1.col1 AS col1,t1.utf8_col AS utf8_col from t1 where convert(t1.utf8_col using utf8) = 'hello'"
          }

поэтому избыточные CONVERT вызовы следует использовать с осторожностью.

Обработка представлений

MariaDB использует два алгоритма для обработки представлений: объединение и материализация. Если вы выполняете запрос, использующий представление, в трассировке будет либо

            "view": {
              "table": "view1",
              "select_id": 2,
              "algorithm": "merged"
            }

или

          {
            "view": {
              "table": "view2",
              "select_id": 2,
              "algorithm": "materialized"
            }
          },

в зависимости от используемого алгоритма.

Оптимизатор диапазонов — какие диапазоны будут сканированы

У MariaDB есть сложная часть, называемая оптимизатором диапазонов. Это модуль, который анализирует условия WHERE (и ON) и строит диапазоны индексов, которые необходимо сканировать для ответа на запрос. Правила построения диапазонов довольно сложны.

Пример: Рассмотрим таблицу

create table some_events ( 
  start_date date, 
  end_date date, 
  ...
  key (start_date, end_date)
);

и запрос:

explain select * from some_events where start_date >= '2019-09-10' and end_date <= '2019-09-14';
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | some_events | ALL  | start_date    | NULL | NULL    | NULL | 1000 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+

Можно предположить, что оптимизатор сможет использовать ограничения как на start_date, так и на end_date, чтобы построить узкий диапазон для сканирования. Но это не так, одно из ограничений создаёт диапазон с левой границей, а другое — с правой, поэтому их нельзя объединить.

select 
   JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) as trace 
from information_schema.optimizer_trace\G
*************************** 1. row ***************************
trace: [
    {
        "range_scan_alternatives": 
        [
            {
                "index": "start_date",
                "ranges": 
                [
                    "(2019-09-10,NULL) < (start_date,end_date)"
                ],
...

полученный диапазон использует только одну из границ.

Варианты доступа ref

Индексные вложенные циклы объединения называются «доступом ref» в MariaDB оптимизаторе.

Оптимизатор анализирует условия WHERE/ON и собирает все условия равенства, которые могут быть использованы для доступа ref с использованием индекса.

Список условий можно найти в узле ref_optimizer_key_uses. (TODO пример)

Оптимизация объединения

Узел оптимизатора объединения называется considered_execution_plans.

Оптимизатор строит порядок объединения слева направо. То есть, если запрос представляет собой объединение трёх таблиц:

select * from t1, t2, t3 where ...

то оптимизатор будет

• Выбирать первую таблицу (скажем, это t1),
• рассматривать добавление другой таблицы (скажем, t2) и строить префикс «t1, t2»
• рассматривать добавление третьей таблицы (t3) и построение префикса «t1, t2, t3», который представляет собой полное объединение. Также будут рассматриваться и другие порядки объединения.

Основная операция здесь: «дан префикс объединения таблиц A,B,C ..., попробовать добавить к нему таблицу X». В JSON это выглядит так:

      {
        "plan_prefix": ["t1", "t2"],
        "table": "t3",
        "best_access_path": {
          "considered_access_paths": [
            {
              ...
            }
          ]
        }
      }

(искать plan_prefix за которым следует table).

Если вас интересует, как был построен (или не был построен) порядок объединения #t1,t2,t3#, вам нужно искать эти шаблоны:

• "plan_prefix":[], "table":"t1"
• "plan_prefix":["t1"], "table":"t2"
• "plan_prefix":["t1", "t2"], "table":"t3"