Эта статья участвовала в "Проект «Звезда раскопок»”, чтобы выиграть творческий подарочный пакет и бросить вызов творческим поощрительным деньгам.
Каков план выполнения?
Используйте ключевое слово EXPLAIN для моделирования оптимизатора, выполняющего запросы SQL, чтобы знать, что MySQL Как работать с оператором SQL, анализировать узкое место в производительности оператора запроса или структуры таблицы.
Введение на официальном сайте:Dev.MySQL.com/doc/Furious/…
Предпосылка: версия mysql во всех случаях в этой статье — 5.7.23.
Что план выполнения помогает нам достичь?
- порядок чтения таблицы
- Тип операции операции чтения данных
- Какие индексы можно использовать
- Какие индексы используются на самом деле
- ссылки между таблицами
- Сколько строк в таблице запрашивается оптимизатором
Как использовать план выполнения?
- expain + оператор SQL
- План выполнения содержит информацию
План выполнения содержит информационное пояснение
id
Порядковый номер запроса выбора, который содержит набор чисел, указывающих порядок, в котором предложение выбора или таблица операций выполняются в запросе.
use oemp;
#测试表1
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#测试表2
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#测试表3
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#id 相同
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id
and t3.other_column = '';
#id 不同
explain select t2.* from t2 where id = (select id from t1 where id =
(select t3.id from t3 where t3.other_column = ''));
#id 相同和不同同时存在
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2
where s1.id = t2.id;
Есть три случая: один и тот же идентификатор, другой идентификатор, один и тот же идентификатор и разные идентификаторы существуют одновременно.
тот же идентификатор
Идентификатор тот же, а результаты выполнения сверху вниз
- результат операции
идентификатор отличается
Если идентификатор отличается, если это самозапрос, серийный номер идентификатора будет увеличен.Чем больше значение id, тем выше приоритет и выполняется первым
- результат операции
Один и тот же идентификатор и разные идентификаторы существуют одновременно
Если идентификатор одинаковый, его можно рассматривать как группу и выполнять сверху вниз; Во всех группах, чем больше значение id, тем выше приоритет и выполняется первым; Производная = ПРОИЗВОДНАЯ
- Результаты
производное_слияние было введено в Mysql 5.7 и попытается объединить производную таблицу (производная таблица, самозапрос после) ссылки на представление и общие табличные выражения с внешним запросом.
MySQL 5.7 не является совместимой реализацией, и ее можно обойти, настроив оптимизатор_переключателя.
set optimizer_switch='derived_merge=off';
Грубо говоря, если установлено значение on, то строка производного_слияния не появится.
Результат выглядит следующим образом:
select_type
Включить область действия: simple.primary, подзапрос, производный, объединение, результат объединения. Тип запроса в основном используется для различения сложных запросов, таких как обычные запросы, запросы на объединение, подзапросы и т. д.
- простой, простой оператор выбора, запрос не содержит самозапроса или объединения
- первичный, если запрос содержит какие-либо сложные части, самый внешний запрос помечается как первичный
- подзапрос, включить подзапрос в список select или where
- производные, в том числе самозапросы в списке from помечаются как производные (derived) MySQL будет рекурсивно выполнять эти самозапросы, помещая результаты во временную таблицу.
- union, если второй выбор появляется после union, помечается как union.Если union включен в запрос предложения from, внешний выбор будет помечен как производный
- результат объединения, выберите, чтобы получить результаты из таблицы объединения
table
- Эта строка данных относится к такой таблице
type
Тип: all, index, range, ref, eq_ref, const, system, null тип показывает тип защиты, что является более важным показателем.От хорошего к плохому: system > count > eq_ref > range > index > all
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >> unique_subquery > index_subquery > range > index > ALL
system
Таблица имеет только одну строку записей (равную системной таблице), представляющую собой специальный столбец типа const, который обычно не отображается, и на это тоже можно не обращать внимания
count
explain select * from (select * from t1 where id =1) d1;
Указывает, что индекс найден один раз, а const используется для сравнения первичного ключа или уникального индекса. Поскольку сопоставляется только одна строка данных, MySQL может быстро преобразовать запрос в константу, поместив первичный ключ в список where.
eq_ref
explain select * from t1, t2 where t1.id = t2.id;
Уникальное сканирование индекса, для каждого ключа индекса ему соответствует только одна запись в таблице. Обычно используется при сканировании первичных ключей или уникальных индексов. Случай запроса:
ref
# tb_emp ddl
CREATE TABLE `tb_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
) ;
#员工表添加年龄列
alter table tb_emp add column `age` int(11) default null after `name`;
#添加复合索引
create index idx_emp_name_age on tb_emp(`name`, `age`);
explain select * from tb_emp where `name` = 'z3';
Сканирование неуникального индекса, которое возвращает все строки, соответствующие одному значению, по существу является доступом к индексу, который возвращает все строки, которые соответствуют одному значению, однако может быть найдено более одной строки, соответствующей условию, поэтому следует быть гибридом поиска и сканирования
range
explain select * from t1 where id between 1 and 3;
explain select * from t1 where id in (1, 2, 3);
Получить только строки в заданном диапазоне, используя индекс для выбора строк. Ключевой столбец показывает, какой индекс использовался
Как правило, у вас есть такие запросы, как между, , in и т. д. в операторе where.
Этот индекс сканирования диапазона лучше, чем полное сканирование таблицы, потому что его нужно начинать только в одной точке индекса и заканчивать в другой точке без полного сканирования таблицы.
Результат дела:
index
explain select id from t1;
Полное сканирование индекса, индекс отличается от ALL, тип индекса только обходит дерево индексов, что обычно быстрее, чем ALL, потому что файл индекса обычно меньше, чем файл данных.(То есть, хотя all и index читают всю таблицу целиком, index читается из индекса, а all читается с жесткого диска)результат поиска:
all
explain select * from t1;
Полное сканирование таблицы просматривает всю таблицу, чтобы найти совпадающие строки.
Примечание. Вообще говоря, гарантируется, что запрос достигает по крайней мере уровня ранжирования, предпочтительно ref.
possible_keys
Отображает один или несколько индексов, которые можно применить к этой таблице. Если в поле, задействованном в запросе, есть индекс, он будет указан.Но не обязательно фактически используется запросом.
key
Фактический используемый индекс, если NULL, индекс не используется. Если в запросе используется покрывающий индекс, индекс отображается только в списке KEY.
explain select col1, col2 from t1;
create index idx_col1_col2 on t1(col1, col2);
explain select col1, col2 from t1;
Случай 1 (до индексации)
Случай 2 (после индексации)
key_len
desc t1;
explain select * from t1 where col1 = 'ab';
explain select * from t1 where col1 = 'ab' and col2 = 'bc';
Указывает количество байтов, используемых в индексе. Этот столбец можно использовать для расчета длины индекса, используемого в запросе. Чем короче длина, тем лучше без потери точности.
key_len показывает только максимально возможную длину поля индекса, ** не фактическую используемую длину **. То есть key_len e вычисляется в соответствии с определением таблицы, а не извлекается из таблицы.результат поиска:
Резюме: чем больше условий, тем больше стоимость и больше длина key_len.Рекомендуется при определенных условиях, чем короче key_len, тем выше эффективность.
Rows
Основываясь на статистике таблицы и выборе индекса, приблизительно оцените количество строк, которые нужно прочитать, чтобы найти нужную запись.
filtered
Extra
Содержит дополнительную информацию, не подходящую для отображения в других столбцах, но важную идентификатор, тип_выбора, таблица, тип, возможные_ключи, ключ, длина_ключа, ссылка, строки, дополнительные
1. Using filesort
сортировка файлов
2. Using temporary
explain select col2 from t1 where col1 in ('ab', 'ac', 'as') group by col2 \G;
explain select col2 from t1 where col1 in ('ab', 'ac', 'as')
group by col1, col2, col3 \G;
Временные таблицы используются для хранения промежуточных результатов, а MySQL использует временные таблицы при сортировке результатов запроса.
Обычно используется в порядке сортировки и группировки по запросу.
пример:
3. Using index
explain select col2 from t1 where col1=100;
explain select col1, col2 from t1;
Указывает, что соответствующая операция выбора использует покрывающий индекс (Covering Index), который позволяет избежать доступа к строкам данных таблицы, и эффективность хорошая~
Если использование where появляется одновременно, это означает, что индекс используется для выполнения поиска значения ключа индекса;
Если использование where также отсутствует, это указывает на то, что индекс используется для чтения данных, а не для выполнения поиска.
пример:Индекс покрытия
- Индекс покрытия (Covering Index), называемый индексом покрытия
- Первый способ понять: то есть столбец данных select можно получить только из индекса, не читая строку данных, MySQL может использовать ваш индекс для возврата полей списка выбора, без повторного чтения файла данных по индексу, другими словамиСтолбец запроса должен быть покрыт созданным индексом
- Понимание метода 2: индекс — это способ эффективного поиска строк, но обычная база данных также может использовать индекс для поиска данных столбца, поэтому ей не нужно читать всю строку, в конце концов, листовые узлы индекса хранят данные, которые они индексируют; Вы можете получить нужные данные, взяв индекс, поэтому вам не нужно читать строку. Индекс, который содержит (или покрывает) данные, удовлетворяющие результатам запроса, называется покрывающим индексом.
- Примечание: 1. Если вы хотите использовать покрывающий индекс, вы должны обратить внимание на список выбора, обобщающий только необходимые столбцы, а не на выбор *, 2. Потому что, если все поля индексируются вместе, файл индекса будет слишком большим, и производительность запроса будет снижена.
4. Using Where
Указывает, что там, где используется фильтрация
5. using join buffer
Используется кеш ссылок
6. impossible where
explain select * from t1 where 1=2;
Значение предложения where всегда ложно и не может использоваться для получения каких-либо кортежей.
7. select tbale optimized away
При отсутствии предложения GROUPBY оптимизация операций MIN/MAX на основе индексов или оптимизация операций COUT(*) для механизма хранения MyISAM не требует ожидания фазы выполнения для расчета, а оптимизация завершается на этапе запроса генерация плана выполнения.
8. distinct
Оптимизируйте отдельную операцию, чтобы прекратить поиск одного и того же значения, как только будет найден первый соответствующий кортеж.
Например
Пример описания:
explain select d1.name, (select id from t3) d2 from
(select id, name from t1 where other_column = '') d1
union
(select name, id from t2);
результат поиска:Анализ случая:
- Первая строка (порядок выполнения 4): столбец id равен 1, что указывает на первый выбор объединения, первичная таблица select_type указывает, что запрос является внешним запросом, таблица
- Столбец помечен значком , что указывает на то, что результат запроса исходит из производной таблицы, где 3 в производном3 означает, что запрос получен из третьего запроса выбора, т. е. выберите [выберите d1.name ...] с идентификатором 3.
- Вторая строка (порядок выполнения 2): id равен 3, что является частью третьего выбора во всем запросе, потому что запрос включен в from, поэтому он является производным. [выберите идентификатор, имя, откуда other_column = '']
- Третья строка (порядок выполнения 3): подзапрос в списке выбора имеет select_type подзапроса, который является вторым выбором во всем запросе [идентификатор выбора из t3].
- Четвертая строка (порядок выполнения — 1): select_type — это union, указывающий, что четвертый выбор — это второй выбор в unin, а первое выполнение — [имя выбора, идентификатор из t2]
- Пятая строка (порядок выполнения 5): представляет собой этап чтения строк во временной таблице объединения. [Объединение двух результатов]
использованная литература
«Добро пожаловать для обсуждения в области комментариев, официальный представитель NuggetsПроект «Звезда раскопок»После мероприятия в комментариях будет разыграно 100 штук Наггетсов.Подробнее о лотерее читайте в статье о мероприятии».