MySQL - Explain & Procedure analyse

EXPLAIN

Оператор Explain позволяет получить подробную информацию о том как будет выполняться тот или иной SQL запрос, он выведет описание индексов, которые будут использованы при сканирования таблицы, типы используемых объединений, данные о кол-ве сканируемых строк и т.д. При этом сам SQL запрос реально выполнен не будет.

В примере будем использовать Mysql базу данных PowerDNS с тестовыми доменами.

mysql> desc records;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| domain_id   | int(11)      | YES  | MUL | NULL    |                |
| name        | varchar(255) | YES  | MUL | NULL    |                |
| type        | varchar(6)   | YES  |     | NULL    |                |
| content     | varchar(255) | YES  |     | NULL    |                |
| ttl         | int(11)      | YES  |     | NULL    |                |
| prio        | int(11)      | YES  |     | NULL    |                |
| change_date | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> show index from records;

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| records |          0 | PRIMARY        |            1 | id          | A         |         106 |     NULL | NULL   |      | BTREE      |         |

| records |          1 | rec_name_index |            1 | name        | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

| records |          1 | nametype_index |            1 | name        | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

| records |          1 | nametype_index |            2 | type        | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

| records |          1 | domain_id      |            1 | domain_id   | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

5 rows in set (0.00 sec)

Выведем все записи закрепленные в базе за domain_id 26, через Explain, чтобы увидеть какие именно индексы будут использоваться при выполнении выборки

mysql> explain select * from records where domain_id = '26';

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra       |

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

|  1 | SIMPLE      | records | ref  | domain_id     | domain_id | 5       | const |   13 | Using where |

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

1 row in set (0.00 sec)

Type - бывает значений all (обозначает просмотр всех строк, соответственно индекс использоваться не будет), ref, range

Possible_keys, key - отображает столбец, который может использовать индекс

Rows - кол-во строк, которые требуется просмотреть (чем меньше, тем лучше)

Таким образом при добавлении в таблицу новых индексов с помощью оператора Explain можно проверять эффективность индексов.

PROCEDURE ANALYSE

Функция Procedure analyse выводит предложения по оптимальным типам столбцов таблицы

Например в этой же базе данных PowerDNS есть столбец type, который хранит тип ресурсной записи DNS, проверим его с помощью данной функции

mysql> select type from records procedure analyse()\G

*************************** 1. row ***************************

             Field_name: pdns.records.type

              Min_value: A

              Max_value: TXT

             Min_length: 1

             Max_length: 4

       Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 1.8302

                    Std: NULL

      Optimal_fieldtype: ENUM('A','AAAA','MX','NS','PTR','SOA','TXT') NOT NULL

1 row in set (0.00 sec)

Мы видим что procedure analyse проанализировав содержимое данных в столбце type предлагает нам для данного столбца использовать тип ENUM (это тип, строки которого имеют ограниченный набор строго определенных значений). Данные такого типа обрабатываются очень и очень быстро.

Смотрим текущий тип столбца type, он сейчас соответствует varchar(6).

mysql> desc records;

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| id          | int(11)      | NO   | PRI | NULL    | auto_increment |

| domain_id   | int(11)      | YES  | MUL | NULL    |                |

| name        | varchar(255) | YES  | MUL | NULL    |                |

| type        | varchar(6)   | YES  |     | NULL    |                |

| content     | varchar(255) | YES  |     | NULL    |                |

| ttl         | int(11)      | YES  |     | NULL    |                |

| prio        | int(11)      | YES  |     | NULL    |                |

| change_date | int(11)      | YES  |     | NULL    |                |

+-------------+--------------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

и если предположить что у нас все ресурсные DNS записи, которые будут добавляться в таблицу будут семи самых распространенных значений ('A','AAAA','MX','NS','PTR','SOA','TXT'), то можно смело переводить столбец type в ENUM, что даст прирост производительности.