В чем польза объяснения: чтобы узнать, как оптимизировать выполнение операторов SQL, вам необходимо просмотреть конкретный процесс выполнения операторов SQL, чтобы повысить эффективность выполнения операторов SQL.
Вы можете использовать оператор объяснения + SQL для моделирования оптимизатора, выполняющего оператор SQL-запроса, чтобы узнать, как mysql обрабатывает оператор SQL. Посмотрите, обрабатывает ли исполнитель SQL так, как мы хотим, взглянув на план выполнения.
Информация, содержащаяся в плане выполнения объяснения, выглядит следующим образом:
- id: серийный номер запроса
- select_type: тип запроса
- таблица: имя таблицы или псевдоним
- разделы: соответствующие разделы
- тип: тип доступа
- возможных_ключей: возможные индексы
- ключ: фактический используемый индекс
- key_len: длина индекса
- ref: столбец для сравнения с индексом
- rows: предполагаемое количество строк
- filtered: процент строк, отфильтрованных по условию таблицы
- Дополнительно: дополнительная информация
Далее описывается значение конкретного представления каждого столбца и соответствующего SQL.
В тесте используется mysql версии 5.7, и используются следующие три структуры таблиц.
CREATE TABLE `demo`.`emp` (
`emp_id` bigint(20) NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '姓名',
`empno` int(20) NOT NULL COMMENT '工号',
`deptno` int(20) NOT NULL COMMENT '部门编号',
`sal` int(11) NOT NULL DEFAULT 0 COMMENT '销售量',
PRIMARY KEY (`emp_id`) USING BTREE,
INDEX `u1`(`deptno`) USING BTREE,
UNIQUE INDEX `u2`(`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
CREATE TABLE `demo`.`dept` (
`id` bigint(20) NOT NULL,
`deptno` int(20) NOT NULL COMMENT '部门编码',
`dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '部门名称',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
CREATE TABLE `demo`.`salgrade` (
`id` bigint(20) NOT NULL,
`losal` int(20) NULL DEFAULT NULL,
`hisal` int(20) NULL DEFAULT NULL,
`emp_id` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
столбец идентификатора
выберите порядковый номер запроса (номер группы), он обозначает предложение выбора таблицы выполнения запроса или последовательности операций.
Столбец id разделен на три случая:
1. Если идентификаторы совпадают, порядок выполнения сверху вниз.
mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
2. Если id другой, если это подзапрос, то порядковый номер id будет увеличиваться.Чем больше значение id, тем выше приоритет, и будет выполняться первым\
mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
3. Одновременно существуют одинаковые и разные id: один и тот же можно рассматривать как группу, которая выполняется в порядке сверху вниз, во всех группах, чем больше значение id, тем выше приоритет и тем раньше выполнение
mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal wheree.deptno = (select d.deptno from dept d where d.dname = 'SALES');
Во-вторых, столбец select_type
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询
1. sample: Простой запрос без подзапросов и объединений
mysql> explain select * from emp;
- primary: если запрос содержит какие-либо сложные подзапросы, самый внешний запрос помечается как основной.
mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
- union: Второй и последующие выборки в union, union all и подзапросы помечаются как union
mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
4. dependent union: В большом запросе, содержащем UNION или UNION ALL, если каждый небольшой запрос зависит от внешнего запроса, значение select_type остальных небольших запросов, кроме самого левого, равно DEPENDENT UNION.
mysql> explain select * from emp e where e.empno in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
5. union result: выберите, чтобы получить результат из таблицы объединения.
mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;
6. subquery: включить подзапросы в список select или where (не в предложение from).
mysql> explain select * from emp where sal > (select avg(sal) from emp) ;
7. dependent subquery: Первый выбор в подзапросе (не в предложении from) и зависит от внешнего запроса.
mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);
8. derived: ****Подзапросы, содержащиеся в списке FROM, помечаются как DERIVED, также известные как производные классы.
mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;
9. UNCACHEABLE SUBQUERY: Результат подзапроса не может быть кэширован, и первый ряд внешней линии должен быть переоценен. Для внешней основной таблицы подзапрос не может быть реализован и должен быть рассчитан каждый раз (трудоемкая операция)
mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
10. uncacheable union: Указывает, что результат запроса объединения не может быть кэширован: не найдена конкретная проверка оператора SQL.
В-третьих, столбец таблицы
К какой таблице обращается соответствующая строка, имя или псевдоним таблицы, возможно, временная таблица или набор результатов объединения.
1. Если это конкретное имя таблицы, это указывает на то, что данные получены из фактической физической таблицы, и, конечно, это также может быть псевдоним таблицы.
2. Имя таблицы имеет вид производныйN, что указывает на то, что используется производная таблица, сгенерированная запросом с идентификатором N.
3. Когда есть результат объединения, имя таблицы имеет форму объединения n1, n2 и т. д., n1, n2 представляют идентификатор, участвующий в объединении.
В-четвертых, тип столбца
Тип показывает тип доступа Тип доступа указывает, как я получаю доступ к нашим данным Проще всего подумать о полном сканировании таблицы, которое очень неэффективно, так как яростно перемещаюсь по таблице, чтобы найти необходимые данные.
Существует много типов доступа, в порядке эффективности от лучшего к худшему:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
В общем, необходимо следить за тем, чтобы запрос дошел хотя бы до уровня диапазона, желательно ref
1. all: Полное сканирование таблицы, вам нужно просмотреть всю таблицу, чтобы найти нужные строки данных от начала до конца. В нормальных условиях, если такой оператор SQL появляется и объем данных относительно велик, его необходимо оптимизировать.
mysql> explain select * from emp;
2. index: Эффективность полного сканирования индекса лучше, чем у всех.В основном есть два случая.Один - текущий запрос покрывает индекс, то есть нужные нам данные могут быть получены в индексе, или индекс используется для сортировка, чтобы избежать переупорядочения данных
mysql> explain select empno from emp;
3. range: указывает, что диапазон ограничен при использовании индексного запроса, и запрос выполняется в пределах указанного диапазона, что позволяет избежать полного индексного сканирования индекса.Применимые операторы: =, , >, >=,
mysql> explain select * from emp where empno between 100 and 200;
4. index_subquery: Используйте индексы для корреляции подзапросов, больше не сканируйте всю таблицу
mysql> explain select * from emp where deptno not in (select deptno from emp)
Но в большинстве случаев при использовании подзапроса SELECT оптимизатор запросов MySQL автоматически оптимизирует подзапрос к запросу таблицы соединения, поэтому тип будет отображаться не как index_subquery, а как ref.
5. unique_subquery: этот тип подключения похож на index_subquery и использует уникальный индекс
mysql> explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );
При использовании подзапроса SELECT в большинстве случаев оптимизатор запросов MySQL автоматически оптимизирует подзапрос к запросу таблицы соединения, поэтому тип будет отображаться не как index_subquery, а как eq_ref.
6. index_merge: в процессе запроса необходимо использовать несколько индексов в комбинации.
mysql> не моделируется
7. ref_or_null: для поля, для которого требуется как условие связи, так и нулевое значение, оптимизатор запросов выберет этот метод доступа.
mysql> не моделируется
8. ref: использовать неуникальный индекс для поиска данных
mysql> explain select * from emp where deptno=10;
9. eq_ref: при выполнении запроса к таблице эквивалентности используйте индекс первичного ключа или уникальный ненулевой индекс для поиска данных (фактически тип запроса, эквивалентный уникальному индексу, — не eq_ref, а const)
mysql> explain select * from salgrade s LEFT JOIN emp e on s.emp_id = e.emp_id;
10. const: можно сопоставить не более одной части данных, обычно с использованием первичного ключа или уникального индекса для условных запросов с равными значениями.
mysql> explain select * from emp where empno = 10;
11. system: таблица имеет только одну строку записей (равную системной таблице), что является частным случаем типа const, который обычно не отображается и не требует дискового ввода-вывода.
mysql> explain SELECT * FROM `mysql`.`proxies_priv`;
Пять, столбец возможных_ключей
Отображает один или несколько индексов, которые могут быть применены к этой таблице. Если в поле, задействованном в запросе, есть индекс, он будет указан, но не обязательно будет фактически использоваться запросом.
Шесть, ключевой столбец
Фактический используемый индекс. Если он равен нулю, индекс не используется. Если в запросе используется покрывающий индекс, индекс перекрывается с полем выбора запроса.
Семь, столбец key_len
Указывает количество байтов, используемых в индексе.Длина индекса, используемого в запросе, может быть рассчитана с помощью key_len.Чем короче длина, тем лучше без потери точности.
Чем больше индекс, тем больше место для хранения, поэтому количество и объем операций ввода-вывода будут увеличиваться, что повлияет на эффективность выполнения.
Восемь, справочная колонка
Отобразите столбец или константу, используемые предыдущей таблицей, чтобы найти значение в индексе записи ключевого столбца.
Девять, столбец строк
По статистической информации таблицы и использованию индексов примерно оцените количество строк, которые нужно прочитать, чтобы найти нужные записи.Этот параметр очень важен.Чем меньше данных будет найдено прямым SQL-запросом, тем меньше тем лучше, если цель будет достигнута.
Десять, отфильтрованный столбец
Пессимистическая оценка процента записей в таблице, удовлетворяющих условию (предложение where или условие соединения).
Одиннадцать, дополнительная колонка
Содержит дополнительную информацию.
1. using filesort: Указывает, что mysql не может использовать индекс для сортировки и может использовать только алгоритм сортировки для сортировки, который будет использовать дополнительные позиции
mysql> explain select * from emp order by sal;
2. using temporary: создать временную таблицу для сохранения промежуточных результатов, удалить временную таблицу после завершения запроса
mysql> explain select name,count(*) from emp where deptno = 10 group by name;
3. using index: Это указывает на то, что текущий запрос является покрывающим индексом и считывает данные непосредственно из индекса, не обращаясь к таблице данных. Если использование индекса имени таблицы также используется для поиска значения ключа индекса, если нет, для чтения данных используется поверхностный индекс, а не фактический поиск.
mysql> explain select deptno,count(*) from emp group by deptno limit 10;
4. using where: Используйте where для условной фильтрации
mysql> explain select * from emp where name = 1;
5. using join buffer: использовать кеш подключения
mysql> explain select * from emp e left join dept d on e.deptno = d.deptno;
6. impossible where: Результат оператора where всегда ложный
mysql> explain select * from emp where 1=0;
Если есть какие-либо проблемы в тексте, вы можете оставить мне сообщение, а также можете дать комментарии, если некоторые из них не были смоделированы.
См. "Высокопроизводительный MySQL"
Исторический обзор расширенной серии mysql:
3. В чем разница между MyISAM и InnoDB