Заявление ANALYZE

Описание

Заявление ANALYZE statement похоже на EXPLAIN statement. ANALYZE statement вызовет оптимизатор, выполнит оператор и затем создаст EXPLAIN вывод вместо набора результатов. EXPLAIN вывод будет снабжён статистикой из выполнения оператора.

Это позволяет проверить, насколько близки оценки оптимизатора о плане запроса к реальности. ANALYZE создаёт обзор, а команда ANALYZE FORMAT=JSON предоставляет более подробный вид плана запроса и выполнения запроса.

Синтаксис:

ANALYZE explainable_statement;

где оператор — любой оператор, для которого можно запустить EXPLAIN.

Вывод команды

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

ANALYZE SELECT * FROM tbl1 
WHERE key1 
  BETWEEN 10 AND 200 AND 
  col1 LIKE 'foo%'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: key1
          key: key1
      key_len: 5
          ref: NULL
         rows: 181
       r_rows: 181
     filtered: 100.00
   r_filtered: 10.50
        Extra: Using index condition; Using where

По сравнению с EXPLAIN, ANALYZE создаёт два дополнительных столбца:

• r_rows — это аналог столбца rows, основанный на наблюдениях. Он показывает, сколько строк фактически было прочитано из таблицы.
• r_filtered — это аналог столбца filtered, основанный на наблюдениях. Он показывает, какая доля строк осталась после применения условия WHERE.

Интерпретация вывода

Соединения

Давайте рассмотрим более сложный пример.

ANALYZE SELECT *
FROM orders, customer 
WHERE
  customer.c_custkey=orders.o_custkey AND
  customer.c_acctbal < 0 AND
  orders.o_totalprice > 200*1000

+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       9.13 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |     10 |   100.00 |      30.03 | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

Здесь можно увидеть, что

• Для таблицы customer, customer.rows=149095, customer.r_rows=150000. Оценка количества строк, которые мы прочитаем, была довольно точной.
• customer.filtered=18.08, customer.r_filtered=9.13. Оптимизатор несколько переоценил количество записей, которые будут соответствовать селективности условия, прикреплённого к таблице `customer` (вообще, когда у вас есть полный сканирование, а r_filtered меньше 15%, пора подумать о добавлении соответствующего индекса).
• Для таблицы orders, orders.rows=7, orders.r_rows=10. Это означает, что в среднем для данного c_custkey существует 7 заказов, но в нашем случае их было 10, что близко к ожидаемому (если это число постоянно сильно отличается от ожидаемого, возможно, пора запустить ANALYZE TABLE или даже вручную изменить статистику таблицы, чтобы получить лучшие планы запросов).
• orders.filtered=100, orders.r_filtered=30.03. Оптимизатор не имел возможности оценить, какая доля записей останется после проверки условия, прикрепленного к таблице orders (это orders.o_totalprice > 200*1000). Поэтому он использовал 100%. На самом деле это 30%. 30% обычно не достаточно селективно для того, чтобы оправдывать добавление новых индексов. Для соединений с многими таблицами может стоить собрать и использовать статистику по столбцам для интересующих столбцов, это может помочь оптимизатору выбрать лучший план запроса.

Значение NULL в r_rows и r_filtered

Давайте немного изменим предыдущий пример

ANALYZE SELECT * 
FROM orders, customer 
WHERE
  customer.c_custkey=orders.o_custkey AND
  customer.c_acctbal < -0 AND 
  customer.c_comment LIKE '%foo%' AND
  orders.o_totalprice > 200*1000;

+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       0.00 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |   NULL |   100.00 |       NULL | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

Здесь можно видеть, что orders.r_rows=NULL и orders.r_filtered=NULL. Это означает, что таблица orders не сканировалась ни разу. Действительно, мы также видим customer.r_filtered=0.00. Это показывает, что часть WHERE, прикреплённая к таблице `customer`, никогда не выполнялась (или выполнялась менее чем в 0,01% случаев).

ANALYZE FORMAT=JSON

ANALYZE FORMAT=JSON генерирует вывод в формате JSON. Он генерирует гораздо больше информации, чем табличный ANALYZE.

Примечания

• ANALYZE UPDATE или ANALYZE DELETE фактически произведут обновления/удаления (ANALYZE SELECT выполнит операцию выбора и затем отбросит результат).
• В PostgreSQL есть похожая команда, EXPLAIN ANALYZE.
• Функция EXPLAIN в журнале медленных запросов позволяет MariaDB печатать ANALYZE медленных запросов в журнале медленных запросов (см. MDEV-6388).

См. также

• ANALYZE FORMAT=JSON
• ANALYZE TABLE
• Задача JIRA для оператора ANALYZE, MDEV-406