В последнее время возникла необходимость модифицировать существующую структуру хранения, что предполагает рассмотрение условий запросов и эффективности запросов.После прочтения нескольких статей, связанных с индексами и HBase, я вспомнил соответствующие знания в сочетании с требованиями проекта и рассказал о своем собственное понимание и обобщение.
Первые две статьи представили преимущества индексов, эволюцию структуры индекса и процесс выполнения SQL, сосредоточив внимание на анализе последовательности выполнения SQL и процесса позиционирования данных.Те, кто пропустил это, могут сначала просмотреть его:
Эта статья вступает в основную тему и знакомит с тем, как просматривать и анализировать выполнение SQL и устранять проблемы с производительностью SQL.Из этого введения вы узнаете:
- Объяснить обзор команд
- Сведения о поле Select_type
- Детали поля типа
- Детали дополнительных полей
Часть контента взята из статей нескольких блоггеров, и, наконец, будет дана ссылка на статью, чтобы поблагодарить их за прекрасный анализ.
объяснить обзор команд
На работе MySQL будет записывать операторы SQL, которые выполнялись в течение длительного времени. Поиск этих операторов SQL является первым шагом. Важно проверить план выполнения оператора SQL. Для получения плана выполнения MySQL вы можно просмотреть его с помощью метода объяснения.Вывод команды позволяет нам понять, как оптимизатор MySQL выполняет оператор SQL.
Оптимизатор MySQL работает на основе накладных расходов, которые динамически рассчитываются при выполнении каждого оператора SQL.Использование команды очень простое, просто добавьте объяснение перед оператором SELECT.
Возьмем пример
Если взять в качестве примера базовую таблицу сотрудников, структура таблицы выглядит следующим образом:
mysql> show create table employee \G;
*************************** 1. row ***************************
Table: mcc_employee
Create Table: CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userId` varchar(50) DEFAULT NULL COMMENT '员工编号',
`userName` varchar(50) DEFAULT NULL COMMENT '员工名称',
`nickName` varchar(50) DEFAULT NULL COMMENT '昵称',
`gender` varchar(10) DEFAULT NULL COMMENT '性别',
`mobilePhone` varchar(20) DEFAULT NULL COMMENT '手机号',
`miliao` varchar(100) DEFAULT NULL COMMENT '米聊号',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Простой запрос:
mysql> explain select * from employee where id =1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
Простой select_type указывает, что это обычный запрос и не содержит подзапросов и запросов на объединение.
Обзор поля
Поле id: идентификатор запроса на выборку.Каждому выбору будет автоматически присвоен уникальный идентификатор, тот, у которого больше значение id, будет выполняться первым, а тот, у которого такой же id, будет выполняться в порядке сверху вниз.
select_type: Тип запроса на выборку.Когда нет подзапроса или запроса на объединение, это просто.Когда есть подзапрос или запрос на объединение, есть несколько случаев, которые будут подробно описаны позже.
таблица: определяет, какая таблица запрашивается, и показывает, о какой таблице данные в этой строке. Иногда это не настоящее имя таблицы. То, что вы видите, является производным (n – это число, которое является полем идентификатора)
mysql> explain select * from (select * from (select * from employee where id =76) table1 ) table2 ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 3 | DERIVED | mcc_inform | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
type: тип доступа к данным и операции чтения, сильно влияющие на производительность, о чем будет подробно рассказано позже.
возможных_ключей: индекс, который может быть выбран в этом запросе.Если в поле, задействованном в запросе, есть индекс, индекс будет указан, но не обязательно будет использоваться запросом.
ключ: точный индекс, используемый в этом запросе. Если индекс не выбран, ключ равен NULL.
key_len: указывает количество байтов, используемых в индексе, которое можно использовать для вычисления длины индекса, используемого в запросе.
ref: какое поле или константу использовать с ключом.
Строки: Сколько стоит этот запрос, это оценочное значение.
filtered: указывает процент данных, отфильтрованных по этому условию запроса.
extra: дополнительная информация, которая будет подробно описана позже.
Сведения о поле Select_type
Указывает тип запроса, будь то простой запрос или сложный запрос.Если это сложный запрос, он включает ПРОСТОЙ, ПРОСТОЙ, ОБЪЕДИНЕНИЕ, РЕЗУЛЬТАТ ОБЪЕДИНЕНИЯ, ПОДЗАПРОС, ЗАВИСИМЫЙ, ЗАВИСИМОЕ ОБЪЕДИНЕНИЕ, ЗАВИСИМЫЙ ПОДЗАПРОС, ПРОИЗВОДНЫЙ и т. д. вы понимаете их, вы можете определить, какая часть выполняется.
SIMPLE
Простой выбор без использования объединения или подзапросов и т.д.:
mysql> explain select * from employee where id =1 ;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
PRIMARY
Если это сложный запрос, это означает, что это самый внешний выбор:
mysql> explain select * from (select * from employee where id =1) a ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
UNION & UNION RESULT
Второй или последующий оператор SELECT в UNION, UNION RESULT является результатом UNION:
mysql> explain select * from employee where id =1 union all select * from employee where id=2;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| 2 | UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
SUBQUERY
Первый SELECT в подзапросе:
mysql> explain select * from employee where id = (select id from employee where id =1);
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| 2 | SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
DEPENDENT UNION & DEPENDENT SUBQUERY
DEPENDENT UNION, второй или последующий оператор SELECT в UNION, но результат зависит от внешнего запроса; ЗАВИСИМЫЙ ПОДЗАПРОС, первый SELECT в подзапросе, но результат зависит от внешнего запроса:
mysql> explain select * from employee where id in (select id from employee where id =1 union all select id from employee where id=2);
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | employee | ALL | NULL | NULL | NULL | NULL | 26 | Using where |
| 2 | DEPENDENT SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 3 | DEPENDENT UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
DERIVED
SELECT производной таблицы, подзапрос предложения FROM:
mysql> explain select * from (select * from employee where id =1) a ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
Детали поля типа
Тип указывает тип операции доступа/чтения данных, который показывает, какая категория используется для соединения, а также используется индекс или нет.Это обеспечивает важную основу для оценки эффективности запроса.
Обычно используются общие типы, а производительность отсортирована от плохой к хорошей: ALL, index, range, ref, eq_ref, const, system, NULL.
NULL
Результат можно получить напрямую, без обращения к таблице или индексу:
mysql> explain select sysdate();
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
константа, система
Если первичный ключ или уникальный индекс помещены в список where, MySQL может преобразовать запрос в константу, когда в таблице есть только одна строка, тип — system.
mysql> explain select * from (select id from mcc_inform where id =1) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
eq_ref
Этот тип обычно появляется в запросах на соединение нескольких таблиц, что означает, что для каждого результата предыдущей таблицы может быть сопоставлена только одна строка результатов из последней таблицы.Операция сравнения запроса обычно =, а эффективность запроса выше:
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
ref
Подобно eq_ref, разница в том, что ref не является уникальным индексом.Этот тип обычно появляется в запросах на соединение нескольких таблиц, для индексов с неуникальным или непервичным ключом или для запросов, использующих самый левый индекс правила префикса, который может быть используется для использования = или индексированных столбцов для оператора :
index_merge
Этот тип объединения указывает на то, что используется метод оптимизации слияния индексов. Может быть несколько условий (или объединений), когда задействовано несколько полей, и между ними выполняется И или ИЛИ, тогда в это время может использоваться технология слияния индексов.
Если говорить просто о технологии слияния индексов, то на самом деле это следующее: условное сканирование нескольких индексов соответственно, а затем слияние их соответствующих результатов (пересечение/объединение).
range
Указывает, что используется запрос диапазона индекса, и некоторые записи данных в таблице получены через диапазон поля индекса. Этот тип обычно появляется в =, , >, >=, , МЕЖДУ, В() операции средний
index
Указывает полное сканирование индекса, аналогичное типу ALL, за исключением того, что тип ALL представляет собой полное сканирование таблицы, а тип индекса сканирует только все индексы, а не данные.
ALL
Указывает на полное сканирование таблицы. Этот тип запроса является одним из наименее эффективных запросов и обычно не выполняется.
Детали дополнительных полей
Много дополнительной информации в EXplain будет отображаться в поле «Дополнительно», это поле может дать нам дополнительные сведения для понимания плана выполнения и представить некоторые общие.
Using where
В случае поиска по индексу необходимо вернуться к таблице для запроса необходимых данных.
Using index
Указывает, что запрос может найти необходимые данные в дереве индексов без сканирования файла данных таблицы, что указывает на хорошую производительность.
Using filesort
Когда операция ORDER BY включена в SQL, а индекс нельзя использовать для завершения операции сортировки, оптимизатор запросов должен выбрать соответствующий алгоритм сортировки для реализации.
filesort в основном используется для операции сортировки результирующего набора данных запроса.Сначала MySQL будет использовать память sort_buffer_size для сортировки.Если результирующий набор превышает размер sort_buffer_size, он перенесет отсортированный фрагмент в файл и, наконец, способ сортировки слиянием.Завершить операцию сортировки всех данных.
filesort может применяться только к одной таблице.Если данные из нескольких таблиц должны быть отсортированы, MySQL сначала будет использовать использование временных данных для сохранения временных данных, затем использовать файловую сортировку для временной таблицы для сортировки и, наконец, вывести результат.
Using temporary
Временные таблицы используются для запросов, которые обычно появляются при сортировке, группировке и объединении нескольких таблиц.Эффективность запросов невысока, поэтому рекомендуется оптимизация.
В следующей статье будут представлены принципы оптимизации индекса и тематические исследования.
Справочная статья:
Добро пожаловать, чтобы отсканировать QR-код ниже, обратите внимание на мою личную общедоступную учетную запись WeChat и просмотрите другие статьи ~