предисловие
Ранее в статье【Эти вещи от ввода-вывода до индексации] Автор обсуждает роль индексов в запросах к базе данных, главным образом, чтобы уменьшить количество запросов для повышения эффективности выполнения, основная причина заключается в снижении стоимости ввода-вывода. В этой статье будет приведен пример связанной оптимизации для базы данных Mysql, подключения запроса и индекса и расширения возможностей практического применения!
О Mysql
Как только оптимизация запросов задействована, она неотделима от применения индексов.В этой статье в качестве объекта исследования выбирается InnoDB, движок, обычно используемый в mysql, и делается краткое описание структуры индекса B+, используемого движком InnoDB.
B+ дерево InnoDB
Предположим, мы создаем таблицу Student с первичным ключом id:
CREATE TABLE `Student` (
`id` int(16) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Вставьте 12 частей данных:
insert into Student(id,name) valuse(1,'XiaoHong')
insert into Student(id,name) valuse(2,'XiaoMing')
insert into Student(id,name) valuse(3,'XiaoFang')
....
insert into Student(id,name) valuse(12,'XiaoYou')
На этом этапе механизм Innodb сам создаст структуру индекса дерева B+ на основе идентификатора первичного ключа.У нас есть следующая абстракция:
Как понять форму конструкции на схеме?
Данные таблицы сначала сохраняются в дисковом пространстве в соответствии с порядком идентификатора первичного ключа.Листовые узлы на рисунке хранят реальные данные каждой строки в таблице.Можно признать, что сами данные таблицы принадлежат части индекса первичного ключа, как показано на рисунке ниже, каждая строка данных хранится в порядке, соответствующем идентификатору первичного ключа. :
Мы установили идентификатор как тип Int, чтобы он занимал 4 байта, поле имени — фиксированный 10-байтовый тип Char, и каждая строка таблицы Student будет занимать 14 байт дискового пространства. В идеале мы можем упростить его до такого понимания: предположим, что первая строка (1, XiaoHong) на рисунке находится по адресу диска 0x01, тогда вторая строка (2, XiaoMing) находится по адресу диска 0x0f (0x01+14=0x0f). , и так далее.
Нелистовые узлы хранят значения индексов и соответствующие указатели.Мы видим, что эти 12 строк данных разделены на пять узлов (один нелистовой узел и четыре листовых узла) в соответствии с идентификатором первичного ключа.В реальной среде Mysql использует принцип чтения с диска по блоку, чтобы установить каждый блок диска (который также можно понимать как страницу, как правило, 4 КБ, а размер страницы установлен на 16 КБ в innodb) как размер узла дерева, так что каждый раз содержимое сгенерированный дисковым вводом-выводом, может получить все данные соответствующего узла.
Для нелистовых узлов указатель слева от каждого значения индекса указывает на адрес узла соответствующих данных меньше значения индекса, а указатель справа от значения индекса указывает на адрес узла соответствующих данных больше больше или равно значению индекса:
Как показано на рисунке выше, данные узла, на которые указывает левый указатель со значением индекса 4, должны быть меньше 4, а соответствующий правый указатель должен указывать на диапазон узлов больше или равный 4. Более того, в случае определенного количества значений индекса, чтобы управлять высотой дерева как можно меньше, дерево B+ потребует, чтобы каждый нестраничный узел хранил как можно больше данных. количество значений индекса нелистового узла должно быть не менее (n -1)/2, где n — максимальное количество значений, которые может содержать размер блока страницы. Согласно предполагаемой структурной форме на приведенном выше рисунке мы знаем, что каждый блок страницы может содержать только три значения индекса или не более трех строк данных (на самом деле он будет намного больше).Исходя из этого предположения, если вы продолжите вставлять данные строки, то первым является конечный узел. Для новых данных не будет места, и конечный узел разделяется, чтобы добавить новый конечный узел для завершения сохранения:
Как вы понимаете, мы пытаемся продолжить вставку 2 частей данных:
insert into Student(id,name) valuse(13,'XiaoRui')
insert into Student(id,name) valuse(14,'XiaoKe')
Это закончится следующим образом:
Поскольку каждый нестраничный узел может содержать до 3 значений индекса и соответствующих 4 указателей (разветвление), сложность всего запроса составляетO(log4N), где N — количество строк в таблице. Для таблицы Student с данными о 1000 учащихся сложность запроса по идентификатору составляетlog41000= 5, здесь сложность запроса может быть интуитивно понята как высота дерева в дереве B+, а адрес блока страницы, где расположены целевые данные, наконец, определяется путем постепенного определения неконечных узлов слой за слоем.
Таким образом, данные таблицы механизма innodb организованы по структуре индекса первичного ключа, а конечные узлы хранят данные строки, которая представляет собой файловую организацию дерева B +.Если данные строки расположены по первичному ключу, это будет иметь большую эффективность. , поэтому при создании таблицы, независимо от того, четко ли определен индекс первичного ключа, механизм автоматически создаст индекс первичного ключа для таблицы, а затем создаст файловую организацию B+. В практических приложениях при запросе некоторых данных через первичный ключ первым шагом является определение адреса конкретного конечного узла в дереве B+.Поскольку конечный узел просто устанавливается на целое число, кратное непрерывному адресу дискового блока, быстрый ввод-вывод загружает все содержимое узла в память, затем фильтрует содержимое узла из памяти, чтобы найти целевые данные!
Но движок innodb также позволяет нам строить индексы по другим полям таблицы отдельно, которые часто называют вспомогательными индексами, Например, мы создаем таблицу Student следующим образом:
CREATE TABLE `Student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Вставьте образцы данных:
insert into Student(id,name) valuse(1,'A')
insert into Student(id,name) valuse(2,'A')
insert into Student(id,name) valuse(3,'B')
......
......
insert into Student(id,name) valuse(12,'F')
Как понять, в каком виде существует структура индекса поля имени? Прямо над:
Можно видеть, что вспомогательный индекс также будет строить структуру индекса дерева B+, но конечные узлы хранят значение идентификатора первичного ключа, а нечисловые индексы сортируются в соответствии с предустановленными правилами сортировки набора символов в структуре индекса, например поскольку name=A в соответствующем сопоставлении меньше, чем B.
В соответствии со структурой рисунка выше предположим, что мы делаем следующее:
select * from Student where name='A';
Затем вторичный индекс будет использоваться для обнаружения конечного узла 1, а затем загружается в память.При извлечении из памяти обнаруживается, что есть два идентификатора первичного ключа: 1 и 2, соответствующие условиям, а затем извлекаются из Индекс первичного ключа через идентификатор первичного ключа, и все данные строки загружаются.
Во вспомогательном индексе innodb также поддерживает форму составного индекса, который объединяет несколько полей в индекс по порядку.Например, мы создаем следующую таблицу Student:
CREATE TABLE `StudentTmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Комбинация имени и возраста составляет индекс, и соответствующая структура индекса дерева B+ имеет следующую форму:
В этом составном индексе содержимое листовых узлов сначала сортируется в соответствии с полем имени, а затем в соответствии с полем возраста, когда значение поля имени одинаково, так что отношение сортировки между двумя полями будет полностью использовано. при запросе имени и возраста в качестве комбинированных условий. Реализуйте многоуровневое позиционирование индекса.
Хорошо, мы не будем запутываться в деталях дерева B+, нам нужно только построить в уме общую форму структуры индекса и представить, что запрос индекса — это процесс постепенного нахождения целевых данных в дереве. -подобную структуру И очень полезно понимать, что сложность запроса связана с количеством указателей в нелистовых узлах дерева B+, что очень помогает нам формировать чувствительность стоимости запроса.
Используйте метод объяснения, чтобы понять стоимость запроса
Узнав об улучшении эффективности запросов, вызванном построением индекса, как мы узнаем, как каждый запрос Sql использует индекс в практических приложениях? Метод объяснения может помочь нам в принятии решений перед выполнением, и он очень полезен, особенно для сложных операторов запроса с несколькими уровнями вложенности или соединениями через соответствующие параметры.
Анализ плана можно выполнить, поставив перед запросом sql ключевое слово объяснения:
explain select id from Student where id=1;
После выполнения получаются следующие результаты:
Мы видим, что форма результата имеет 10 параметров, таких как id, table, select_type..., и каждый параметр имеет соответствующее значение результата, Далее мы разберемся с этим шаг за шагом.
id: используется для определения порядка выполнения каждого подзапроса, чем больше значение, тем выше приоритет выполнения
Приведенный выше запрос — это просто простой запрос, поэтому идентификатор имеет только один 1. Теперь мы добавим подзапрос:
explain select name from Student where id=(select max(id) from Student);
имеют:
Вы можете видеть, что есть две строки результатов, указывающие на то, что этот sql имеет два плана запроса, поле таблицы используется для указания имени таблицы, соответствующего плану запроса, а роль значения id состоит в том, чтобы подсказать нам, какой план запроса выполняется первым.
таблица: укажите имя таблицы, связанное с соответствующим планом запроса
В приведенном выше примере описания поля id мы обнаружили, что план запроса с id=2 (select max(id) from Student
) Соответствующее имя таблицы пусто, что кажется нестандартным.Разве этот план запроса не включает операции с таблицами? В поле Extra мы нашли такое описание:Select tables optimized away
Этот оператор сообщает нам, что движок оптимизировал план запроса.На основе оптимизации на уровне индекса, такой как операции min/max или операции count(*), нет необходимости ждать этапа выполнения для извлечения таблицы, и значение может храниться в некоторых местах заранее читать напрямую. Я предполагаю, что одна ситуация заключается в том, что, поскольку само поле id принадлежит индексу первичного ключа таблицы Student, сам движок сохраняет значения min(id) и max(id) в режиме реального времени для запроса или напрямую считывает первый и последний в дереве индекса первичного ключа.Получаются данные листового узла, поэтому аналогичные планы запросов имеют большую эффективность выполнения при фактическом выполнении.
select_type: определяет тип плана запроса
select_type в основном имеет следующие типы:
- SIMPLE: простой SELECT, без UNION или подзапросов и т. д.
- PRIMARY: если запрос содержит какие-либо сложные части, самый внешний выбор помечен как PRIMARY.
- UNION: второй или последующий оператор SELECT в UNION.
- ПОДЗАПРОС: первый SELECT в подзапросе
DERIVED (SELECT для производной таблицы, подзапрос в предложении FROM)
заexplain select id from Student where id=1;
select_type имеет значение SIMPLE, что указывает на то, что sql является самой простой формой запроса.
заexplain select name from Student union select name from Course;
имеют:
Мы видим, что есть два плана запроса. Запрос для самой внешней таблицы Student является PRIMARY, что означает, что оператор является сложным оператором и содержит другие планы запроса. Включенный план запроса — это план запроса курса. select_type запроса курса plan — UNION, что подтверждает приведенное выше описание типа UNION. В сочетании со значением поля id мы знаем, что движок сначала выполняет план таблицы курса, а затем выполняет план таблицы студента.
заexplain select id,(select count(*) from Course) as count from Student;
имеют:
заexplain select name from (select name from Student where id=1) tb;
имеют:
Особенностью этого оператора является то, что план подзапроса для таблицы Student заключен в слой, поэтому соответствующий select_type имеет значение DERIVED.
К этому моменту мы понимаем, что sql будет разбиваться более чем на один план запроса во время выполнения, и между планами есть определенный приоритет выполнения, а select_type хорошо определяет наличие разных планов, что позволяет нам поставить структуру сложного SQL разбирается, и один за другим анализируются разные планы запросов, чтобы завершить общую оптимизацию.
Далее мы начинаем фокусироваться на нескольких других полях формы эксплианного анализа:
- type
- возможных_ключей: индексы, которые могут использоваться планом запроса.
- ключ: индекс, фактически используемый планом запроса
- Строки: сложность запроса, которую также можно просто понимать как количество строк, которые должны быть обработаны планом запроса.
Эти поля тесно связаны с индексом и действительно послужат ориентиром для нашего анализа стоимости запроса.По этим полям мы можем хорошо судить об использовании индекса.
тип: метод извлечения, используемый при запросе данных в таблице.
Тип указывает, использует ли план запроса структуру индекса и конкретные характеристики поиска. Конкретные категории:
- ALL: индексы не используются, MySQL будет просматривать всю таблицу, чтобы найти совпадающие строки.
- Индекс: используйте только структуру индекса, в innodb можно понимать только глобальный поиск по дереву B+, не работая напрямую с таблицей.
- диапазон: получить только строки в заданном диапазоне, используя индекс для выбора строк
- ref: Получено по индексу, но индекс не является уникальным, и может быть получено несколько записей с одним и тем же значением.
- eq_ref: аналогично ref, разница в том, что используемый индекс является уникальным индексом. Для каждого значения ключа индекса соответствует только одна запись в таблице. Короче говоря, первичный ключ или уникальный ключ используется в качестве условия ассоциации в множественном числе. -столовое соединение
- const, system: эти типы доступа используются, когда MySQL оптимизирует часть запроса и преобразует его в константу. Если первичный ключ помещен в список where, MySQL может преобразовать запрос в константу. System является частным случаем типа const. Когда таблица запроса имеет только одну строку, используйте system
- NULL: MySQL разлагает оператор в процессе оптимизации, и ему даже не требуется доступ к таблице или индексу во время выполнения.Например, выбор минимального значения из столбца индекса может быть выполнен с помощью отдельного поиска по индексу
заexplain select name from Student where name='学生1';
имеют:
Тип ALL, то есть имя не является индексом в таблице Student. Чтобы запросить данные с именем «student 1», база данных должна будет выполнить глобальный поиск по данным таблицы. В строках указано величина поиска.Мы можем понимать это как сложность запроса, поскольку базе данных необходимо обрабатывать данные таблицы построчно, выше rows=699323, мы можем судить, что таблица Student составляет около 700 000 строк.
заexplain select name from Student;
имеют:
Тип — индексный, потому что мы заранее построили вспомогательный индекс для имени, поэтому всю информацию об имени в таблице запроса нужно только просмотреть в B+-дереве, соответствующем имени:
Как показано на рисунке выше, начните сканирование непосредственно с крайнего левого конечного узла дерева вспомогательных индексов и запросите всю информацию об именах. Сами запрошенные данные сортируются по порядку. Если у вас есть требование сортировки для sql:
select name from Student order by name asc;
Тогда скорость запроса будет значительно выше по сравнению с получением из табличной структуры данных!
заexplain select * from Student where id>1 and id<5;
имеют:
Тип — диапазон, что означает, что запрос сначала определяется структурой индекса, как показано на следующем рисунке:
за explain select name from Student where name='A';
имеют:
Тип ref указывает на то, что индекс имени не является уникальным индексом, т. е. в таблице может быть несколько записей с одним и тем же именем.При извлечении данных через структуру индекса имени все записи, соответствующие условиям, будут получено.
заexplain select Student.name,Score.score from Score join Student on Score.s_id=Student.id
имеют:
Мы заметили, что в этом запросе на соединение, когда идентификатор первичного ключа Student используется в качестве условия соединения, тип плана запроса соответствующей таблицы Student — eq_ref, что указывает на то, что используется функция уникального индекса.
за explain select id from Student where id=1;
имеют:
Тип - const, что обычно происходит, когда условие запроса, соответствующее SQL, является уникальным индексом, указывающим, что механизм выполнил специальную обработку оператора.Перед выполнением плана результат запрашивается и преобразуется в константу, так что фактический процесс выполнения напрямую Заключение констант в кавычки избавляет вас от повторения процесса запроса. Приведем другой пример:
explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id where Student.id=1;
имеют:
Для этого запроса соединения, без учета оптимизации const перед выполнением, его можно выразить в виде следующей логики выполнения в псевдокоде:
outerIterator=select A.s_id,A.score from Score as A;
//对Score表进行全局的行扫描
while (outerRow=outerIterator.next){
innerIterator=select A.id,A.name from Student as A where A.id=1;
innerRow=innerIterator.next;
if (innerRow.id=outerRow.s_id){
//将符合条件的结果记录输出
print(outerRow.score,innerRow.name);
}
}
Как показано выше, сначала нужно выполнить глобальный запрос к таблице Score, В течение этого периода каждую строку необходимо сравнить с данными соответствующего идентификатора таблицы Student, но каждое сравнение — это потребление запроса таблицы Student. , поэтому его можно оптимизировать в следующей логике:
//将Student表的查询计划优先执行,并将结果赋值到常量
constIterator=select A.id,A.name from Student as A where A.id=1;
constRow=constIterator.next;constId=constRow.id;constName=constRow.name;
//查询计划执行过程
outerIterator=select A.s_id,A.score from Score as A;
while (outerRow=outerIterator.next){
//计划执行过程中只需和对应常量比较,大大提高执行效率
if (innerRow.id=constId){
print(outerRow.score,constName);
}
}
Путем извлечения результатов плана таблицы Student в константы можно избежать потребления запросов, вызванного поиском в цикле, и улучшение производительности, вызванное этим, является очень значительным.
До сих пор мы сделали базовое введение в метод объяснения. Разделив план SQL-запроса и оценив степень использования индекса, мы можем предоставить большинство идей по оптимизации. Далее мы проведем тест с реальными данными. Сосредоточьтесь на Обратите внимание на изменения в поле строк, чтобы определить эффект от нашей оптимизации и надеемся на более глубокое осмысление всего процесса.
Практическая оптимизация
Раньше я видел, как одноклассник обсуждал на форуме процесс оптимизации базы данных.Здесь мы продемонстрируем со ссылкой на табличную ситуацию, с которой они столкнулись в то время.Мы предполагаем, что база данных огромной системы управления образованием будет оптимизирована на основе Версия MySQL 5.5, которая включает 3 таблицы. :
Таблица учеников (id: идентификатор ученика; имя: имя ученика)
CREATE TABLE `Student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Таблица Student инициализируется 100 000 строк данных:
INSERT INTO `Student` (`id`, `name`)
VALUES
(1, '学生0'),
(2, '学生1'),
(3, '学生2'),
.....
.....
.....
(700000,'学生699999')
Расписание курса (идентификатор: идентификатор курса; имя: название курса)
CREATE TABLE `Course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Учебная программа инициализируется 100 строками данных:
INSERT INTO `Course` (`id`, `name`)
VALUES
(1, '课程0'),
(2, '课程1'),
(3, '课程2'),
.....
.....
.....
(100,'课程99')
Таблица результатов (id: идентификатор записи; s_id: идентификатор студента; c_id: идентификатор курса; оценка: оценка)
CREATE TABLE `Score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(11) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
В таблице результатов записаны баллы соответствующих курсов разных студентов.Мы инициализировали данные 20 курсов 100 000 студентов в таблице с общим количеством строк 2:
INSERT INTO `Score` (`id`, `s_id`, `c_id`, `score`)
VALUES
(1, 1, 1, 63),
(2, 2, 1, 67),
(3, 3, 1, 40),
.....
.....
(20000000,100000,20,95)
В практических приложениях есть такое требование, необходимо запросить имена всех студентов, набравших 100 баллов по определенному курсу, и написать следующее утверждение:
select Student.name from Student where id in (select s_id from Score where c_id = 1 and score = 100 );
План выполнения содержит два плана запроса.Мы заметили, что соответствующие строки равны 100 000 и 1 миллион соответственно, а соответствующая сложность запроса составляет O(100 000) и O(2 000 000), что в точности совпадает с количеством строк, соответствующих таблица. , что свидетельствует о том, что выполнено полное сканирование таблицы. Мы видим, что поле типа ALL, что подтверждает наше предположение. Затем попробуйте проиндексировать подзапрос:
alter table Score add index index_cid(c_id)
Автор попытался запустить его еще раз, но, подождав несколько минут, сдался и продолжил просмотр результатов анализа объяснения:
Видя, что сложность запроса таблицы Score снижена до O (200000), всего в 10 раз больше оптимизации производительности, мы знаем, что это неуникальный индекс через type = ref, что указывает на то, что c_id содержит большое количество одинаковых значений в таблице. Не значительно, пробуем еще раз установить вторичный индекс для C_ID и Score:
alter table Score add index_cid_score (c_id,score)
На этот раз мы получили в общей сложности 1566 результатов, что заняло в общей сложности 103 секунды:
В соответствии с результатами анализа объяснения:
С помощью комбинированного индекса мы снижаем сложность запроса таблицы Score до O(1565), что значительно улучшается по сравнению с одним индексом, но общее время выполнения по-прежнему неудовлетворительно. Обращаем внимание на таблицу Student и обнаруживаем, что соответствующий план запроса не использует индекс.Согласно результатам объяснения, значение id плана запроса таблицы Score равно 2, а значение id таблицы Student равно 1. Согласно правилу приоритета, он должен быть выполнен первым.
select s_id from Score where c_id = 1 and score = 100
Всего 1566 результатов за 45 мс:
Затем выполните Студенческий план:
select Student.name from Student where id in (55,68,104,243......99688)
В общей сложности 1566 результатов заняли 1,06 с:
Мы ожидаем, что общее время выполнения двух планов запросов должно быть 1,06 с + 0,045 с = 1,105 с, что сильно отличается от фактических 103 с. Как это объяснить? Внимательно следите за тем, чтобы select_type плана таблицы Score былDEPENDENT SUBQUERY, описано вышеSUBQUERY, что означает первый SELECT в подзапросе, гдеDEPENDENTИдентификация отличается от обычных подзапросов тем, что в плане существует отношение зависимости, то есть процесс выполнения плана таблицы Score зависит от результата выполнения внешнего плана (таблицы Student), весь логический процесс представлен псевдокод следующим образом:
//外部先对Student表进行全局的行扫描
outerIterator=select Student.id,Student.name from Student
while (outerRow=outerIterator.next){
//内部Score的执行过程依赖于外部Student表的执行结果
innerIterator=select Score.s_id from Score where c_id = 1 and score = 100
innerRow=innerIterator.next;
if (innerRow.s_id=outerRow.id){
//将符合条件的结果记录输出
print(outerRow.name);
}
}
Сначала таблица Student выполняет полное сканирование таблицы, а затем количество внутренних запросов Score зависит от количества строк результатов в таблице Student.Результирующая сложность запроса составляет O (Всего) = O (Студент) * O (Оценка). , пример здесь Стоимость расчета, которую должен нести Mysql, составляет: O (157337275) = O (100535) * O (1565), поэтому это занимает много времени запроса! Так есть ли способ заставить работать индекс id таблицы Student?По крайней мере, теоретически, согласно нашим предыдущим предположениям, мы можем контролировать весь запрос примерно до 1 с?
С точки зрения логики выполнения мы можем представить следующую ситуацию:
//外部先对Score表按条件查询
outerIterator=select Score.s_id from Score where c_id = 1 and score = 100
while (outerRow=outerIterator.next){
//内部Student的执行过程依赖于外部Score表的执行结果
innerIterator=select Student.id,Student.name from Student where id=outerRow.s_id
innerRow=innerIterator.next;
if (innerRow!=null){
//将符合条件的结果记录输出
print(innerRow.name);
}
}
Сначала используйте комбинированный индекс таблицы Score для получения данных с c_id=1 и score=100, а затем используйте индекс id таблицы Student для получения информации об имени в сопоставлении циклов.С точки зрения сложности запроса:
explain select Score.s_id from Score where c_id = 1 and score = 100
имеют:explain select name from Student where id=?
имеют:Теоретически угадайте: O(1565)=O(1565)*O(1), мы пытаемся выразить sql запросом соединения:
select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100;
Стоимость всего 0,048 с! Взгляните на анализ объяснения:
Конечно, это оправдало наши ожидания.План таблицы Student использует уникальный индекс, таблица Score использует составной индекс, а конечная сложность запроса также контролируется на уровне O (1565), что отличается от подзапроса в начале. пример. Почему запрос соединения использует его в полной мере? Что насчет индекса? Как понять внутреннюю логику выполнения?
Давайте сначала поговорим о запросе на соединение. В mysql реализация соединения — это, по сути, процесс декартова произведения. Все строки двух таблиц в декартовом произведении будут соединены одна за другой. Например, оператор:
select * from Student,Course;
Соответствующий логический процесс:
//外部先对Student表进行全局的行扫描
outerIterator=select Student.id,Student.name from Student;
while (outerRow=outerIterator.next){
//循环中外部结果每一行都将和Course表每一行进行一次连接
innerIterator=select Course.id,Course.name from Course;
while (innerRow=innerIterator.next){
//获取对应连接结果
print(outerRow.id,outerRow.name,innerRow.id,innerRow.name)
}
}
В mysql мы обычно говорим так:
select Student.id,Student.name,Course.id,Course.name from Student join Course;
Видно, что максимальная сложность запроса на соединение может достигать O(количество строк в таблице «Студент»)*O(количество строк в таблице «Курс») Если для выполнения запроса на соединение добавляется n таблиц, то модель сложности имеет O = O (количество строк в таблице 2) * O (количество строк в таблице 2)... * O (количество строк в таблице n), это будет почти экспоненциальный взрывной рост! В практических приложениях масштаб подключения к данным часто управляется ключевыми словами on и where.В частности, строки результатов сначала фильтруются в соответствии с фактическими условиями фильтрации данных, а затем внутренний запрос объединяется с индексом для завершения оптимизации.
Хорошо, вернемся к вопросу выше:
select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100;
Автор уже видел эти данные. Как правило, база данных выполняет процесс декартова произведения при выполнении соединения соединения. Поле on используется в качестве условия оценки для соединения строк. Наконец, условие where используется для фильтрации строк результатов. Конкретный логический процесс выглядит следующим образом:
//外部先对Student表进行全局的行扫描
outerIterator=select Student.id,Student.name from Student;
while (outerRow=outerIterator.next){
//内部Score的执行过程依赖于外部Student表的执行结果
innerIterator=select Score.s_id,Score.c_id,Score.score from Score;
while(innerRow=innerIterator.next){
//on字段条件在此处决定是否进行连接
if (outerRow.id=innerRow.s_id){
//将符合连接条件的结果保存
tmpArr[]=(outerRow.name,innerRow.c_id,innerRow.score);
}
}
}
//接下来开始where条件的结果过滤
for i:=0;i<n;i++{
if (tmpArr[i].c_id=1&&tmpArr[i].score=100){
resultArr[]=tmpArr[i];
}
}
//完成最后的结果输出
print(resultArr)
В соответствии с приведенным выше процессом сложность запроса, которую мы прогнозируем, должна быть O = O (номер строки таблицы учащихся) * O (номер строки таблицы оценок); но mysql не так прост, благодаря анализу объяснения запроса соединения выше мы видим что выполнение обеих процедур использует структуру индекса двух таблиц:
В таблице Score используется составной индекс index_cid_score. Мы можем предположить, что движок сначала пытается сделать первое суждение об условии «где», а затем объединяет набор результатов и таблицу «Студент». В процессе подключения мы обнаружили, что таблица «Студент» использует индекс первичного ключа.Таким образом, также предполагается, что условие совпадения ключевого слова on применяется к плану запроса таблицы Student, и логический процесс описывается следующим образом:
//外部先对Score表进行where条件筛选,查询中利用到组合索引
outerIterator=select Score.s_id,Score.c_id,Score.score from Score where c_id=1 and score=100;
while (outerRow=outerIterator.next){
//内部Student的执行过程利用到主键索引,on字段的判断条件此时体现在Student查询计划的where条件中
innerIterator=select Student.name from Student where id=outerRow.s_id
//如果存在对应行则保留
if(innerRow=innerIterator.next){
resultArr[]=(innerRow.name,outerRow.c_id,outerRow.score);
}
}
//完成最后的结果输出
print(resultArr)
Очевидно, что сложность логического процесса апелляции зависит от количества строк после условного извлечения в таблице Score, что также согласуется с результатами нашего фактического анализа объяснения.
Однако одна из проблем, о которой я думаю, заключается в том, что для Mysql это не означает, что соединение соединения может соответствовать требованиям оптимизации.С одной стороны, разные движки и разные версии Mysql могут использовать разные методы оптимизации.Не существует фиксированного стандарта ., это изменение следует анализировать вместе с Объяснением в фактической бизнес-обработке.
Автор также попытался выполнить этот пример в версии Mysql 5.6 и обнаружил, что для sql:
select Student.name from Student where Student.id in (select s_id from Score where c_id = 1 and score = 100 )
При той же конструкции индекса результаты анализа объяснения следующие:
Значение id плана запроса Score равно 2, что имеет более высокий приоритет выполнения, но в select_type есть слова, которых раньше не было в Mysql5.5:MATERIALIZED, давайте сначала посмотрим на результат выполнения:
Это то же самое, что и наш результат о соединении соединения в версии Mysql5.5! назадMATERIALIZEDДумая,MATERIALIZEDОфициальное описание:
The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index is unique, which eliminates duplicates and makes the table smaller.
Общий смысл заключается в том, что для того, чтобы сделать подзапрос более эффективным, контроллер оптимизации создаст временную таблицу для результата подзапроса, который обычно размещается в памяти, и сгенерирует соответствующий хэш-индекс во временной таблице для повышения эффективности. запроса памяти Логика примера Процесс можно описать так:
//先对子查询的Score表进行物化操作,即查询结果放置内存中
materalizedRows=select Score.s_id from Score where c_id = 1 and score = 100
for i=0;i<n;i++{
//内存中取出对应数据
materalizedRow=materalizedRows[i]
//内部Student的执行过程利用到主键索引,on字段的判断条件此时体现在Student查询计划的where条件中
innerIterator=select Student.name from Student where id=materalizedRow.s_id
//如果存在对应行则保留
if(innerRow=innerIterator.next){
resultArr[]=(innerRow.name,outerRow.c_id,outerRow.score);
}
}
//完成最后的结果输出
print(resultArr)
Этот логический процесс аналогичен подключению соединения выше. Вот способ просмотра оптимизированного выражения sql. Введите два оператора одновременно в консоли:
explain select s.name from Student s where s.id in (select s_id from Score sc where sc.c_id = 1 and sc.score = 100 );
show warnings;
Получите оптимизированную форму sql:
select `test`.`Student`.`name` AS `name` from `test`.`Student` semi
join (`test`.`Score`)
where (
(`test`.`Student`.`id` = `<subquery2>`.`s_id`)
and (`test`.`Score`.`score` = 100)
and (`test`.`Score`.`c_id` = 1)
)
Разумеется, в Mysql 5.6 подзапрос будет преобразован в форму соединения соединения, и так называемыйMATERIALIZEDОптимизация, автор предполагает, что это просто форма оптимизации, принятая соединением соединения, что означает, что разные версии MySQL будут корректировать структуру операторов SQL.Автор предполагает, что этот метод можно использовать сначала для понимания сложных запросов, а затем комбинировать. метод анализа!
На этом процесс оптимизации всего примера подошел к концу.Независимо от того, насколько сложны требования запроса к реальной среде, мы можем сначала попытаться разделить план запроса, соблюсти приоритет выполнения каждого плана, а затем понять логику выполнения внутри движка, и, наконец, рассчитать общую стоимость запроса корректируется и оптимизируется шаг за шагом.В большинстве случаев автор пытался много раз!
Суммировать
В начале полного текста мы сначала понимаем структуру индекса механизма innodb, цель состоит в том, чтобы сформировать чувствительность стоимости запроса с теоретической поддержкой оценки сложности запроса, а метод объяснения специфичен для процесса практического применения. , вот что может придумать автор Самый простой способ оптимизации. Последний пример демонстрирует гибкость процесса оптимизации, которая выражается в поддержке различных версий Mysql, что требует накопленного опыта в практических приложениях, чтобы лучше справляться. Автору необходимо напомнить, что структура индекса также влияет на стоимость обслуживания базы данных.Помимо повышения эффективности запросов, она также увеличивает нагрузку на базу данных с точки зрения удаления и вставки данных.Это необходимо взвесить в соответствии с реальная ситуация!