Эта статья покажет вам, как использовать команду объяснения для анализа sql.
На самом деле в Интернете есть много статей, которые подробно знакомят с использованием объяснения.Эта статья объединяет примеры и принципы, чтобы попытаться дать вам лучшее понимание.Поверьте, после внимательного прочтения у вас должен быть особый урожай.
объяснение переводится в значение объяснения, которое называется планом выполнения в mysql, то есть через эту команду видно, что mysql решает, как выполнять sql после анализа оптимизатором.
Говоря об оптимизаторе, позвольте мне сказать еще одну вещь, mysql имеет встроенный мощный оптимизатор, основная задача оптимизатора состоит в том, чтобы оптимизировать написанный вами SQL и выполнить его с максимально возможной меньшей стоимостью, например, сканированием меньшего количества количество строк, избегать сортировки и т. д. Каков опыт выполнения инструкции sql? Я представил оптимизатор, упомянутый в предыдущей статье.
Вы можете спросить, а когда вы обычно используете объяснение? В большинстве случаев некоторые sql с медленной эффективностью запросов находятся из журнала медленных запросов mysql для использования анализа объяснения, а некоторые оптимизируют mysql. При добавлении индекса, например, используйте объясните, чтобы проанализировать, можно ли попасть в добавленный индекс, и в развитии бизнеса вам может понадобиться использовать объяснение, чтобы выбрать более эффективный sql, когда требования будут выполнены.
Итак, как использовать объяснение, это очень просто, просто добавьте объяснение непосредственно перед sql, как показано ниже.
mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)
Как видите, функция объяснения вернет около 10 полей. Поля, возвращаемые разными версиями, немного различаются. Каждое поле представляет определенное значение. Я не собираюсь подробно представлять каждое поле в этой статье. Их много. Боюсь, вам нелегко запомнить, лучше сначала разобраться в нескольких важных полях.
Среди них более важны поля type, key, rows и Extra, мы будем использовать конкретные примеры, чтобы помочь вам лучше понять смысл этих полей.
Прежде всего необходимо кратко представить буквальное значение этих полей.
Тип указывает, как mysql получает доступ к данным.Обычными являются полное сканирование таблицы (все), обход индекса (индекс), запрос диапазона (диапазон), постоянный или эквивалентный запрос (ref, eq_ref), эквивалентный запрос первичного ключа (const), когда Когда в таблице только одна запись (системная). Ниже приведен порядок эффективности от лучшего к худшему.
system > const > eq_ref > ref > range > index > all
Ключ представляет собой имя индекса, которое фактически будет использовать процесс запроса.
Rows указывает количество строк, которые может потребоваться просмотреть в процессе запроса.Эти данные не обязательно точны.Это данные, выбранные mysql.
Extra представляет некоторую дополнительную информацию, обычно показывающую, используется ли индекс, требуется ли сортировка, используется ли временная таблица и т. д.
Что ж, приступим к разбору случая.
Или создайте тестовую таблицу, используя механизм хранения, созданный в предыдущей статье.Мы вставляем сюда 10w кусков тестовых данных.Структура таблицы следующая:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Затем посмотрите на следующий оператор запроса.Обратите внимание, что эта таблица в настоящее время имеет только один индекс первичного ключа, а обычный индекс еще не создан.
mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)
Значение типа ALL, что означает, что вся таблица была просканирована.Обратите внимание, что в поле строки 100332 строки.На самом деле, всего у нас есть только 10w данных, поэтому это поле является только оценкой mysql, и это не обязательно точно. Такое полное сканирование таблицы очень неэффективно и нуждается в оптимизации.
Далее добавляем обычные индексы к полям а и б соответственно, а потом уже после добавления индекса смотрим в sql.
mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | id | A | 100332 | NULL | NULL | | BTREE | | |
| t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | YES | BTREE | | |
| t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
Вышеупомянутый sql кажется немного запутанным?Тип на самом деле показывает, что индекс не был добавлен к полю a только что, и возможных_ключей также показывает, что a_index доступен, но ключ показывает нуль, указывая, что mysql на самом деле не использует индекс, Почему это?
Это связано с тем, что если вы выберете *, вам нужно вернуться к индексу первичного ключа, чтобы найти поле b. Этот процесс вызывается обратно в таблицу. Этот оператор отфильтрует 9w фрагментов данных, которые соответствуют условиям, то есть скажем, эти 9w фрагментов данных нужно вернуть в таблицу.Сканирование таблицы имеет только 10w фрагментов данных, поэтому с точки зрения оптимизатора mysql это не так хорошо, как прямое полное сканирование таблицы, по крайней мере, процесс возврат таблицы исключается.
Конечно, это не означает, что пока есть операция с таблицей возврата, индекс не попадет. Ключ к использованию индекса в том, что mysql думает, какой запрос дешевле. Давайте немного изменим условие where в приведенном выше sql .
mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
На этот раз значением типа является диапазон, а ключом является a_index, что означает попадание в индекс a, что является хорошим выбором, поскольку только 1000 фрагментов данных удовлетворяют этому условию SQL, а mysql считает, что 1000 фрагментов данных вернулся к столу.Стоимость сканирования низкая, так что mysql на самом деле очень умный парень.
Мы также видим, что значение в поле «Дополнительно» — «Использование условия индекса», что означает, что индекс используется, но необходимо вернуть таблицу, а затем посмотрите на следующий оператор.
mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Значение в этом дополнении — Использование где; Использование индекса, что означает, что запрос использует индекс, и запрашиваемое поле может быть получено в индексе без возврата в таблицу.Очевидно, что эта эффективность выше, чем приведенная выше, поэтому не пишите легко select * , запрашивайте только поля, необходимые для бизнеса, чтобы максимально избежать возврата к таблице.
Давайте посмотрим на еще один, который необходимо отсортировать.
mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
Это Extra возвращает использование файловой сортировки, что означает, что требуется сортировка. Это необходимо оптимизировать. То есть после того, как данные будут найдены, MySQL должен отсортировать их в памяти. Вы должны знать, что сам индекс упорядочен. , поэтому в общем, старайтесь максимально использовать порядок индекса, например, написав следующее.
mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | range | a_index,ab_index | a_index | 5 | NULL | 10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Давайте создадим еще один составной индекс, чтобы увидеть.
mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t | range | a_index,ab_index | ab_index | 5 | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
Этот sql только что упоминался выше.Когда составной индекс не создается, это полное сканирование таблицы.Теперь он фактически использует покрывающий индекс, что также исключает процесс возврата к таблице, то есть на (ab_index) index Вы можете найти поле для запроса.
Эта статья вводит, как использовать объяснение, чтобы проанализировать план выполнения SQL через несколько примеров, а также упоминает некоторые общие оптимизации индекса. На самом деле есть больше возможностей. Вы также можете написать SQL самостоятельно, а затем использовать объяснение аналитики Что можно оптимизировать.
Я писал эту статью от случая к случаю три или четыре дня.Я изначально готовил больше примеров, но каждый раз, когда я писал часть ее, мое мышление было нарушено.Ну, если у вас есть какие-либо вопросы, пожалуйста, оставьте сообщение ниже, чтобы exchange, статья для вас полезная, ставьте лайк, чтобы выразить поддержку и ободрение.