Тема 2 настройки производительности MySQL (объясните план выполнения, используйте подробное объяснение)

MySQL
Тема 2 настройки производительности MySQL (объясните план выполнения, используйте подробное объяснение)

Что такое план выполнения объяснения

  • EXPLAIN анализирует вашипроверить предложениеилиУзкое место в производительности структуры.

  • Используйте ключевое слово EXPLAIN для моделирования оптимизатора, выполняющего операторы SQL, чтобы знать, как MySQL обрабатывает ваши операторы SQL.

  • В повседневной работе мы иногда сталкиваемся с медленным выполнением SQL.В это время мы можем использовать корреляционное слово EXPLAIN для выполнения SQL, и мы можем проверить, использует ли оператор SQL индекс, является ли это полным сканированием таблицы и т. д., все это можно просмотреть с помощью команды EXPLIN, мы можем выполнить дальнейшую оптимизацию, просмотрев информацию.

  • Добавьте ключевое слово EXPLAIN перед оператором SELECT, и MySQL установит флаг для запроса.вернет информацию о плане выполнения вместо выполнения этого SQL.

подготовка данных

  • студенческий стол

id = первичный ключ, name = имя учащегося, update_time = время обновления

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO `student` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
  • Расписание занятий

id = первичный ключ, name = название курса (обычный индекс)

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO `course` (`id`, `name`) VALUES (1,'Java'), (2,'Python'), (3,'JS');
  • Форма отношений студента и курса

id = первичный ключ, student_id = первичный ключ таблицы учащихся, course_id = первичный ключ таблицы курса, comment = примечания

где student_id и course_idсоставной индекс

DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student_course_id` (`student_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO `student_course` (`id`, `student_id`, `course_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

EXPLAIN выходной набор результатов

Пока что таблица, которую нужно подготовить, завершена, давайте просто воспользуемся EXPLAIN, чтобы посмотреть, что там.

explain select * from actor;

Выполнение приведенного выше оператора даст такой результирующий набор, и позже мы подробно расскажем о значении каждого поля.

При использовании EXPLAIN для каждой таблицы в запросе выводится одна строка записей (в одном SQL несколько записей с несколькими SELECT).

EXPLAIN имеет два варианта:

  • объяснить расширенный:Дополнительная оптимизация запросов обеспечивает некоторую информацию на основе объяснения. После этогоshow warnings;Команда может получить оптимизированное утверждение запроса, вы можете получить оптимизированное утверждение запроса, так что то, что оптимизировано. (Это может оптимизировать какой-то простой SQL, немного сложный, или вы можете сделать это самостоятельно (= · Ω · =) O)
explain extended select * from student WHERE id = 1; 
SHOW WARNINGS;

  • объяснить разделы:По сравнению с объяснением здесь есть еще одно поле partitions.Если запрос основан на секционированной таблице, в нем будут показаны секции, к которым запрос будет обращаться.

Столбцы в EXPLAIN

Далее мы покажем информацию о каждом столбце в EXPLAIN.

идентификатор столбца

Номер столбца id является порядковым номером SELECT, и существует несколько идентификаторов для нескольких SELECT, и порядок идентификаторов увеличивается в порядке появления SELECT.Чем больше столбец идентификатора, тем выше приоритет выполнения. Если идентификатор тот же, он будет выполняться сверху вниз. Если идентификатор равен NULL, он будет выполнен последним..

  • простой подзапрос
EXPLAIN SELECT (SELECT 1 FROM student LIMIT 1) FROM course;

  • Подзапросы в предложении FROM
EXPLAIN SELECT id FROM (SELECT id FROM student) AS stu;

Примечание: я использую5.7Версия после выполнения представляет собой простой запрос, а временная таблица не используется в возвращаемом результате.

я использую5.6Когда выполняется проверка версии, оператор выполняется, и в наборе результатов появляется временная таблица.

В версиях до 5.7 создавалась временная таблица, и эта временная таблица использовалась во внешних запросах SELECT, как показано на следующем рисунке:

select_type с идентификатором 2DERIVER, таблица с идентификатором 1 указывает, что при запросе используется временная таблица.

Проверил официальную документацию, официальная документация гласит:Ссылки на производные таблицы и представления оптимизируются с помощью слияния или материализации.(Простите меня за то, что я английский отморозок, я перевел это прямо из Google (￣.￣), и вы можете прочитать исходный текст официального сайта напрямую, если вы хорошо владеете английским языком).

Оптимизатор может обрабатывать производные ссылки на таблицы, используя две стратегии (которые также применимы к ссылкам на представления):

  • Объединить производную таблицу во внешний блок запроса

  • Реализуйте производные таблицы как внутренние временные таблицы.

例1：
SELECT * FROM (SELECT * FROM t1) AS derived_t1;

通过合并派生表 derived_t1，该查询的执行类似于：

SELECT * FROM t1;

例2：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
  
通过合并派生表 derived_t2，该查询的执行类似于：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

Вкратце: оптимизатор обрабатывает производные таблицы и ссылки на представления одинаково: он максимально избегает ненужной материализации, которая может передавать условия из внешнего запроса в производную таблицу и генерировать более эффективный план выполнения.

  • запрос объединения
EXPLAIN SELECT 1 UNION ALL SELECT 1;

Примечание. Как и в случае подзапросов в предложении FROM, версия 5.7 и более поздние оптимизированы.

столбец select_type

select_type указывает, является ли соответствующая строка простым или сложным запросом, и если это сложный запрос, то каким из трех вышеперечисленных сложных запросов является.

  • simple

Простой запрос

EXPLAIN SELECT * FROM student WHERE id = 2;

  • primary

Самый внешний выбор в сложном запросе

  • subquery

Подзапросы, включенные в select (не в предложение from)

  • derived

MySQL будет хранить результаты во временную таблицу, также известный как полученную таблицу (английский смысл производных)

Используйте этот пример, чтобы узнать о основном, типе подзапроса

EXPLAIN SELECT (SELECT 1 FROM student WHERE id = 1) FROM (SELECT * FROM course WHERE id = 1) c;

  • union

второй и последующий выбор в объединении

  • union result

выберите, чтобы получить результаты из временной таблицы объединения

столбец таблицы

Этот столбец указывает, к какой таблице обращается строка EXPLAIN.

тип столбца (важно)

Этот столбец представляет тип ассоциации или тип доступа, который является тем, как MySQL решает, как посмотреть строки в таблице, чтобы найти приближенный ассортимент записей строки данных.

От лучшего к худшему, это: system > const > eq_ref > ref > range > index > ALL, Вообще говоря, необходимо, чтобы запрос достигrangeуровень, желательноref

  • NULL

MySQL может разложить оператор запроса на этапе оптимизации, и ему больше не нужно обращаться к таблице или индексу на этапе выполнения.

Например: выбор минимального значения в столбце индекса можно выполнить, просматривая только индекс, не обращаясь к таблице во время выполнения.

EXPLAIN SELECT MIN(id) FROM student;

  • константа, система

MySQL может оптимизировать часть запроса и превратить его в константу (см. результаты показа предупреждений).

  • используется дляprimary keyилиunique keyПри сравнении всех столбцов с константами, чтобы в таблице была максимум одна совпадающая строка, которая читается 1 раз, что быстрее.

  • system является частным случаем const. Когда только один кортеж в таблице соответствует, это система

EXPLAIN extended SELECT * FROM (SELECT * FROM course WHERE id = 1) tmp;


show warnings;

  • eq_ref

primary keyилиunique keyВсе части индекса используются конкатенацией, возвращая не более одной совпадающей записи. Это, вероятно, лучший тип соединения из const, и простые запросы на выборку не имеют этого типа.

EXPLAIN SELECT * FROM student_course LEFT JOIN course ON student_course.course_id = course.id;

  • ref

По сравнению с eq_ref он не использует уникальный индекс, а использует общий индекс или частичный префикс уникального индекса.Индекс необходимо сравнить с определенным значением, и может быть найдено несколько подходящих строк.

  • Простой запрос SELECT, name — обычный индекс (неуникальный индекс)
EXPLAIN SELECT * FROM course WHERE name = "Java";

  • Запрос таблицы ассоциаций, idx_student_course_id — это совместный индекс для student_id и course_id, где используется часть student_id левого префикса student_course.
EXPLAIN SELECT student_id FROM student LEFT JOIN student_course ON student.id = student_course.student_id;

  • range

Сканирование диапазона обычно происходит в таких операциях, как IN(), BETWEEN, >, = и т. д. Используйте индекс для получения заданного диапазона строк.

EXPLAIN SELECT * FROM student WHERE id > 1;

  • index

Сканировать полный индекс таблицы, что обычно быстрее, чем ALL. (индекс читается из индекса, и все читается с жесткого диска)

EXPLAIN SELECT * FROM course;

  • ALL

То есть полное сканирование таблицы, а это значит, что MySQL нужно найти нужную строку от начала до конца. Обычно это требует увеличения индекса для оптимизации.

EXPLAIN SELECT * FROM student;

столбец возможных_ключей

В этом столбце показано, какие индексы может использовать запрос для поиска.

  • При использовании EXPLAIN может быть столбец возможных_ключей, но ключ показывает NULL.Эта ситуация возникает из-за того, что в таблице не так много данных, MySQL считает, что индекс бесполезен для этого запроса, и выбирает запрос полной таблицы.

  • Если столбец имеет значение NULL, связанный индекс отсутствует. В этом случае вы можете повысить производительность запроса, проверив предложение WHERE, чтобы увидеть, можно ли создать соответствующий индекс, а затем использовать EXPLAIN, чтобы увидеть результат.

ключевой столбец

Этот столбец показывает, какой индекс MySQL фактически использует для оптимизации доступа к таблице.

  • Если индекс не используется, столбец имеет значение NULL.

  • Если вы хотите заставить MySQL использовать или игнорировать индекс в столбце возможных_ключей, используйте принудительный индекс, игнорируйте индекс в запросе.

столбец key_len

Этот столбец показывает количество байтов, используемых MySQL в индексе.Это значение можно использовать для расчета того, какие столбцы в индексе используются.

Правила расчета key_len следующие:

  • нить

    Типы длина
    char(n) длина n байт
    varchar(n) 2 байта хранят длину строки, если это utf-8, то длина 3n+2

  • Числовой тип

    Типы длина
    tinyint 1 байт
    tinyint 2 байта
    int 4 байта
    bigint 8-байтовый

  • Тип времени 

    Типы длина
    date 3 байта
    timestamp 4 байта
    timestamp 8 байт

  • Если поле может быть NULL, ему требуется 1 байт, чтобы записать, является ли оно NULL.

Максимальная длина индекса составляет 768 байт.Когда строка слишком длинная, MySQL выполнит обработку, аналогичную индексу левого префикса, и извлечет символ, извлекая символ первой половины.

столбец ссылок

Этот столбец показывает столбец или константу, используемую в значении поиска в таблице в индексе записи ключевого столбца, общие из них: const (константа), имя поля (например: student.id)

столбец строк

В этом столбце указано количество строк, которое, по оценкам mysql, нужно прочитать и обнаружить.Обратите внимание, что это не количество строк в результирующем наборе.

Дополнительный столбец (важно)

В этом столбце отображается дополнительная информация. Общие важные значения следующие:

  • Using index

Столбец запроса покрывается индексом, а условием фильтра WHERE является ведущий столбец индекса, что обеспечивает высокую производительность. Как правило, используется покрывающий индекс (индекс содержит все запрашиваемые поля). Для InnoDB, если это вспомогательный индекс, производительность значительно улучшится.

EXPLAIN SELECT student_id FROM student_course WHERE student_id = 1;

  • Using where

Столбец запроса не охватывается индексом, а условие фильтра WHERE не является ведущим столбцом индекса.

EXPLAIN SELECT * FROM student WHERE NAME = 'Python';

  • Using where Using index

Столбец запроса покрывается индексом, а условием фильтра WHERE является один из столбцов индекса, но не ведущий столбец индекса, что означает, что квалифицированные данные не могут быть запрошены напрямую через поиск по индексу.

EXPLAIN SELECT course_id FROM student_course WHERE course_id = 1;

  • NULL

Столбец запроса не покрывается индексом, а условием фильтра WHERE является ведущий столбец индекса, что означает, что индекс используется, но некоторые поля не покрываются индексом, что должно быть достигнуто путем «возврата таблицы ", не только с использованием индекса, но и вовсе без индекса

EXPLAIN SELECT * FROM student_course WHERE student_id = 1;

  • Using index condition

Как и при использовании where, столбцы запроса не полностью покрываются индексом, а условие WHERE представляет собой диапазон ведущих столбцов.

EXPLAIN SELECT * FROM student_course WHERE student_id > 1;

  • Using temporary

MySQL необходимо создать временную таблицу для обработки запроса. Обычно это происходит, когдаоптимизироватьДа, в первую очередь я подумал об использованиипоказательоптимизировать.

  • student.name не имеет индекса. В это время создается временная таблица для DISTINCT
EXPLAIN SELECT DISTINCT name FROM student;

  • Course.name устанавливает индекс idx_name.В настоящее время при запросе используется дополнительный параметр Использование индекса, а временная таблица не используется.
EXPLAIN SELECT DISTINCT name FROM course;

  • Using filesort

Вместо того, чтобы читать строки из таблицы в порядке индекса, MySQL сортирует результаты, используя внешний индекс. На этом этапе MySQL просматривает все подходящие записи в соответствии с типом соединения, сохраняет ключ сортировки и указатель строки, а затем сортирует ключ и извлекает информацию строки по порядку. В этом случае также необходимо рассмотреть возможность использования индексов для оптимизации.

  • student.name не создает индекс, он просматривает всю таблицу учеников, сохраняет имя ключа сортировки и соответствующий идентификатор, затем сортирует имя и извлекает записи строк.
EXPLAIN SELECT * FROM student ORDER BY name;

  • Course.name устанавливает индекс IDX_NAME, а дополнительный использует индекс при запросе.
EXPLAIN SELECT * FROM course ORDER BY name;

  • отфильтрованный столбец

отфильтровано значение в полбалла,rows * filtered / 100Вы можете оценить количество строк, которые будут объединены с предыдущей таблицей в EXPLAIN (предыдущая таблица относится к таблице, значение идентификатора которой в EXPLAIN меньше, чем текущее значение идентификатора таблицы).

Куй железо пока горячо

Подготовьте таблицы и данные

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('小明',22,'JAVA',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('李雷', 23,'Python',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('韩梅梅',23,'JS',NOW());

Как указано выше,name,age,positionдлясоставной индекс

полное соответствие стоимости

EXPLAIN SELECT * FROM employees WHERE NAME= '小明';


EXPLAIN SELECT * FROM employees WHERE name= '小明' AND age = 22;


EXPLAIN SELECT * FROM employees WHERE NAME= '小明' AND age = 22 AND POSITION ='JAVA';

Как и выше, все три SQL используют индексы.

правило наилучшего левого префикса

EXPLAIN SELECT * FROM employees WHERE NAME = '李磊';

Как показано на рисунке, видно, чтоиндекс используется.

EXPLAIN SELECT * FROM employees WHERE POSITION = 'Python';

Как показано на рисунке, видно, чтонет индекса.

EXPLAIN SELECT * FROM employees WHERE age = 23 AND POSITION ='Python';

Как показано на рисунке, видно, чтонет индекса.

Суммировать: Если индексируется несколько столбцов, соблюдается правило крайнего левого префикса. Ссылается на запрос, начинающийся с самого левого переднего столбца индекса и не пропускающий столбцы в индексе.

Ничего не делать с индексированными столбцами

Например, вычисления, функции, преобразование типов (автоматическое или ручное) и т. д. приведут к сбою индекса и переходу к полному сканированию таблицы.

EXPLAIN SELECT * FROM employees WHERE left(NAME,1) = '韩';

Как показано на рисунке, видно, чтонет индекса.

Невозможно использовать столбец справа от условия диапазона в индексе

EXPLAIN SELECT * FROM employees WHERE name= '小明' AND age = 22 AND position ='JAVA';
通过上边实践，知道该语句用到了索引

EXPLAIN SELECT * FROM employees WHERE name= '小明' AND age > 22 AND position ='JAVA';

Мы можем рассчитать следующую длину key_len, узнать ее можно только по индексуnameа такжеage, positionИндексы не используются, поэтомуИспользование индекса столбца в правой части условия диапазона в составном индексе не удастся.

Используйте покрывающий индекс

Запросы, которые обращаются только к индексу (столбец индекса содержит столбец запроса), уменьшая операторы SELECT *

EXPLAIN SELECT * FROM employees WHERE name= '小明' AND age = 22 AND POSITION ='Java';


EXPLAIN SELECT NAME, age, POSITION FROM employees WHERE NAME= '小明' AND age = 22 AND POSITION ='Java';

можно увидеть,От NULL к использованию индекса

Недопустимый индекс при использовании не равно (!= или )

EXPLAIN SELECT * FROM employees WHERE NAME != '小明'

IS NULL, IS NOT NULL также не может использовать индекс

EXPLAIN SELECT * FROM employees WHERE name IS NOT NULL

LIKE индексы, начинающиеся с подстановочных знаков, не будут работать.

EXPLAIN SELECT * FROM employees WHERE name LIKE '%李'

  • Помещение подстановочных знаков в конце индекса не приведет к ошибке
EXPLAIN SELECT * FROM employees WHERE name LIKE '李%'

  • Или используйте покрывающий индекс, поле запроса должно быть покрывающим индексным полем.
EXPLAIN SELECT NAME, age, position FROM employees WHERE name LIKE '%李'

Примечание. Когда поле, на которое указывает покрывающий индекс, является полем типа varchar(380) и выше, покрывающий индекс не работает!

Строковый индекс без одинарных кавычек не работает

EXPLAIN SELECT * FROM employees WHERE NAME = 1000;

Используйте или меньше, используйте его для подключения индекса во многих случаях.

EXPLAIN SELECT * FROM employees WHERE name = '李磊' OR NAME = '韩梅梅';

Суммировать

Предположим, что a, b, c — составные индексы.

ГДЕ заявление Использование индекса
ГДЕ a = 'Сяо Мин' использовать для
ГДЕ a = «Сяо Мин» И b = «Ли Лэй» используйте а и б
ГДЕ a = «Сяо Мин» И b = «Ли Лэй» И c = «Хань Мэймэй» используйте а, б, в
ГДЕ b = «Ли Лэй» ИЛИ ГДЕ b = «Ли Лэй» И c = «Хань Мэймэй» ИЛИ ГДЕ c = «Хань Мэймэй» не используется
ГДЕ a = «Сяо Мин» И c = «Хань Мэймэй» a используется, c не используется, потому что b прерывается
ГДЕ a = «Сяо Мин» И b > «Ли Лэй» И c = «Хань Мэймэй» a, b используются, c нельзя использовать после диапазона
ГДЕ a = «Сяо Мин» И b = «Ли Лэй%» И c = «Хань Мэймэй» используйте а, б, в
ГДЕ a = «Сяо Мин» И b = «% Ли Лэй» И c = «Хань Мэймэй» использовать только
ГДЕ a = «Сяо Мин» И b = «% Ли Лэй%» И c = «Хань Мэймэй» использовать только
ГДЕ a = «Сяо Мин» И b = «Ли% лей%» И c = «Хань Мэймэй» используйте а, б, в
Категории