Отсканируйте QR-код ниже или WeChat, чтобы найти официальную учетную запись.
菜鸟飞呀飞, вы можете следить за публичной учетной записью WeChat, читать дальшеSpring源码分析,Java并发编程,Netty源码系列а такжеMySQL工作原理статья.
Я считаю, что у многих людей всегда спрашивают, есть ли у них опыт настройки SQL в процессе собеседования.Соискатели с меньшим опытом работы обычно получают какие-то ответы от Baidu в Интернете перед собеседованием, запоминают их заранее, а затем непосредственно во время Назовите ответы, которые вы запомнили заранее. Будучи новичком, я пошел на собеседование, когда у меня только что закончился год опыта работы, и я так и сделал. Помню, когда я пошел в одну компанию на собеседование, меня интервьюер спросил, что order by медленно сортируется, как это оптимизировать в данный момент? Я сначала даже не подумал об этом, поэтому ответил добавить индекс к полям в предложении order by (разумеется, этот ответ тоже был заранее получен от Baidu в Интернете), а потом интервьюер спросил, почему добавление индекса улучшит эффективность выполнения ордера, я был ошеломлен, я не знаю почему, и Baidu не сказал мне. Позже интервью, естественно, будет желтым.
Оглядываясь назад, я понимаю, что тогда это было настоящее блюдо. Но опять же, почему добавление индекса к полям в предложении order by ускоряет выполнение SQL? Обязательно ли это повышает эффективность SQL? Чтобы понять эти проблемы, мы должны начать с принципа реализации порядка.
Пример таблицы
Для удобства иллюстрации примера мы сначала создадим образец таблицы, и инструкция по созданию таблицы выглядит следующим образом.
CREATE TABLE `user` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年龄',
PRIMARY KEY ( `id` ),
INDEX ( `name` )
) ENGINE = INNODB COMMENT '用户表';
insert into `user`(id_card,name,age) values
('429006xxxxxxxx2134','张三',22),
('429006xxxxxxxx2135','李四',26),
('129006xxxxxxxx3136','王五',28),
('129106xxxxxxxx3337','赵六',17),
('129106xxxxxxxx3349','孙XX',43),
('129106xxxxxxxx3135','马大哈',39),
('129106xxxxxxxx3134','王一',55),
('139106xxxxxxxx2236','张三',7),
('139106xxxxxxxx2130','张三',31),
('439106xxxxxxxx2729','张三',29),
('439106xxxxxxxx2734','李明',78),
('429106xxxxxxxx1734','张三',96),
('129106xxxxxxxx1737','张三',89),
('129106xxxxxxxx1132','张三',3),
('129106xxxxxxxx1197','张三',11),
('129106xxxxxxxx1184','张三',14);
Мы создали пользовательскую таблицу с 4 полями: id — самоувеличивающийся первичный ключ, id_card — идентификационный номер пользователя, name — имя пользователя, age — возраст пользователя, и в дополнение к id индекса первичного ключа мы также Поле имени создает обычный индекс. Наконец, в таблицу вставляется несколько фрагментов данных, что удобно для последующих примеров.
Предположим, теперь у нас есть следующие требования: запросите идентификационные номера, имена и возраст первых трех пользователей по имени Чжан Сан в соответствии с их возрастом. Соответствующий оператор SQL должен быть написан следующим образом:
select id_card,name,age from user where name = '张三' order by age limit 3;
Логика этого оператора SQL относительно проста и понятна. Оператор будет использовать дерево индексов имен во время выполнения и будет отсортирован. Мы можем использовать ключевое слово «Объяснить» для просмотра плана выполнения SQL.
explain select id_card,name,age from user where name = '张三' order by age limit 3 \G
На приведенном выше рисунке значением ключевой строки является name, что означает, что в этом запросе будет использоваться индекс имени; значение дополнительной строки — Using filesort, что означает, что в этом запросе необходимо использовать операцию сортировки. Далее давайте посмотрим на процесс сортировки MySQL.
Сортировать по всем полям
Во-первых, MySQL выделяет часть памяти для каждого потока запросов, называемую sort_buffer, которая используется для сортировки. Насколько велика эта память? по параметруsort_buffer_sizeЭлемент управления, который можно просмотреть и изменить с помощью следующих команд:
# 查看sort_buffer的大小
show variables like 'sort_buffer_size';
# 修改sort_buffer的大小
set global sort_buffer_size = 262144;
Для примера, который мы упоминали выше, давайте посмотрим на этот процесс сортировки:
- Сначала MySQL выделяет кусок памяти размером sort_buffer_size для соответствующего потока, а затем подтверждает, какие поля помещать в память.Поскольку в этом примере запрашиваются три поля id_card, name и age, этот sort_buffer храниться в полях id_card, name, age;
- Из предыдущего плана выполнения мы уже знаем, что оператор SQL будет использовать дерево индексов имен во время выполнения, поэтому механизм хранения сначала найдет первый конечный узел с name="Zhang San" в дереве индексов имен, а затем вернет leaf Значение идентификатора первичного ключа, хранящееся в узле;
- В соответствии с идентификатором первичного ключа, возвращенным на предыдущем шаге, вернитесь к таблице, вернитесь к дереву индекса первичного ключа, чтобы найти данные, соответствующие идентификатору, и выньте значения трех полей id_card, имя , и age, верните их на серверный уровень MySQL и поместите в in sort_buffer;
- Продолжайте искать следующий узел с name="Zhang San" в дереве индекса имен, повторяйте шаги 2 и 3 и останавливайте поиск до тех пор, пока первое имя не будет равно Zhang San в дереве индекса имен.
- На предыдущих 4 шагах были найдены все данные по имени Чжан Сан, и следующим шагом будет сортировка всех данных по возрасту в sort_buffer;
- Из отсортированных данных возьмите первые три и вернитесь.
Схематическая диаграмма всего процесса выглядит следующим образом:
Этот процесс сортировки также называется сортировкой по полному полю, потому что все поля запроса помещаются в sort_buffer (id_card, name, age).
Увидев это, у некоторых людей обязательно возникнут сомнения: если объем данных, которые мы запрашиваем, слишком велик, и есть много фрагментов данных, соответствующих name="Zhang San", так что память sort_buffer не может вместить все данные, тогда мы Всю сортировку в памяти sort_buffer точно не получится сделать, так что же делать? Ответ заключается в сортировке с помощью дисковых файлов.
При сортировке дисковых файлов обычно используется алгоритм сортировки слиянием. Когда данные запрашиваются из индексного дерева идентификаторов первичного ключа, данные помещаются в sort_buffer.Когда sort_buffer почти заполнен, эта часть данных сортируется в sort_buffer, а затем отсортированные данные временно сохраняются на диске Затем в небольшом файле продолжайте запрашивать данные из дерева индексов первичного ключа, сортируйте в sort_buffer, записывайте во временный файл на диске и повторяйте цикл, пока не будут прочитаны все данные. Наконец, упорядоченные маленькие файлы на диске объединяются в упорядоченный большой файл, тем самым завершая операцию сортировки всех данных.
Из этого процесса мы можем узнать, что если размер данных для сортировки составляет определенную сумму, если размер sort_buffer меньше, то есть чем меньше размер sort_bufer_size, то нам нужно больше временных файлов при сортировке по диску , то возникновение Чем больше количество операций ввода-вывода, тем хуже производительность.
Хотя мы знаем принцип полной сортировки полей, а также можем узнать размер sort_buffer_size, запросив конфигурацию базы данных, как мы узнаем, сортируется ли наш SQL по дисковым файлам при выполнении сортировки? Мы можем просмотреть информацию об оптимизации выполнения SQL через таблицу оптимизатора_трассировки в библиотеке информации_схемы в MySQL, но переключатель оптимизатора_трассировки по умолчанию отключен, поскольку он записывает информацию об оптимизации, связанную с SQL, которая будет потреблять дополнительные ресурсы MySQL. Мы можем просматривать и изменять статус optimizer_trace с помощью следующих команд.
# 查看
show variables like 'optimizer_trace';
# 临时针对当前数据库连接修改(连接断开后，下次再连接数据库时，该值还是false)
set optimizer_trace = "enabled=on";
# 针对所有数据库连接修改
set global optimizer_trace = "enabled=on";
После включения статистики optimizer_trace мы можем просматривать информацию о выполнении SQL из этой таблицы. Взяв за пример пример из предыдущей статьи, мы выполняем следующий SQL по одному:
# 开启统计
set optimizer_trace = "enabled=on";
# 执行查询SQL
select id_card,name,age from user where name = '张三' order by age limit 3;
# 查询统计信息
select * from information_schema.optimizer_trace \G
В итоге мы можем увидеть статистику как на картинке ниже (я взял только ее часть)
Строка number_of_tmp_files на рисунке представляет собой количество временных файлов, используемых при сортировке.Если она равна 0, это означает, что сортировка не основана на сортировке файлов на диске.Если она больше 0, это означает, что сортировка файлов на диске использовал. Из-за того, что на моем компьютере установлен MySQL, размер sort_buffer_size по умолчанию равен 256 КБ, а объем данных, запрашиваемых в примере, относительно невелик, поэтому сортировка может выполняться в памяти sort_buffer без необходимости использования дисковых файлов.
Если вы хотите продемонстрировать сортировку файлов на диске, вы можете сначала установить sort_buffer_size в небольшое значение, затем выполнить операцию запроса и, наконец, посмотреть статистику в оптимизаторе_trace. Так как данных в этой статье слишком мало, а минимальный sort_buffer_size можно установить равным 32 КБ, что не может быть меньше 32 КБ, поэтому в итоге используется сортировка по памяти, поэтому демонстрации не будет. тестовая среда на работе. , проверьте это.
сортировка по ряду
После того, как мы разобрались с полной сортировкой по полям выше, у некоторых людей могут возникнуть сомнения, ведь нам нужно отсортировать только поле имени, а зачем в sort_buffer помещать поле id_card и поле age? Кроме того, сам sort_buffer имеет ограничение на размер памяти. Чем больше полей вы поместите в sort_buffer, тем меньше фрагментов данных он может хранить. Если вы хотите отсортировать несколько фрагментов данных, вам может понадобиться использовать сортировку файлов на диске. сортировка файлов не такая быстрая, как сортировка памяти.
Теперь, когда мы знаем недостатки полной сортировки полей, как мы можем ее улучшить? Разработчики MySQL уже учли этот момент, поэтому есть еще один способ сортировки, назовем его rowid sorting (почему временно? Т.к. я не нашел этого утверждения в официальной документации MySQL, такого рода утверждение было замечено в "MySQL Практические 45 лекций" на Geek Time, но конкретное название не важно, главное знать принцип).
Общая идея принципа сортировки rowid заключается в том, что все поля после выбора в операторе SQL не будут помещены в sort_buffer, а в sort_buffer будут помещены только те поля, которые необходимо отсортировать, и идентификатор первичного ключа, что соответствует примеру в этой статье. Середина: поместите поле имени и поле идентификатора первичного ключа в sort_buffer.
В фактическом процессе разработки мы не создали индекс первичного ключа для некоторых таблиц. В настоящее время MySQL будет определять, есть ли в таблице уникальный индекс. Если есть уникальный индекс, он будет использовать уникальный индекс в качестве первичный ключ; если нет уникального индекса, то MySQL будет генерировать идентификатор строки для каждой строки данных по умолчанию. Этот идентификатор строки имеет ту же функцию, что и первичный ключ. При сортировке поля, помещаемые в sort_buffer, являются отсортированными полями и идентификатором строки , поэтому это называется сортировкой rowid.
Ранее мы говорили, что полная сортировка полей также помещает в sort_buffer поля, которые не нужно сортировать.Эти поля будут занимать память.Когда эти поля достигнут определенного уровня, MySQL будет думать, что метод сортировки полного поля сортировки может потребоваться помощь. Сортировка файлов на диске повлияет на производительность, поэтому метод сортировки изменен на сортировку по идентификатору строки. Так что же это за «определенная степень»? определяется параметромmax_length_for_sort_dataУправляемый, этот параметр указывает, что когда сумма длин полей, которые необходимо поместить в sort_buffer, превышает значение этого параметра, для сортировки будет использоваться rowid. Вы можете просмотреть значение этого параметра с помощью следующей команды.
show variables like 'max_length_for_sort_data';
Значение по умолчанию параметра max_length_for_sort_data — 1024 байта.Для примера в этой статье, поскольку общая длина трех полей id_card, name и age должна быть меньше 1024 байт, она не превышает предела max_length_for_sort_data, поэтому он не будет сортироваться по rowid.
Чтобы увидеть процесс сортировки rowid, я сначала установил значение max_length_for_sort_data на меньшее значение, например 16 байт, чтобы сумма длин трех полей id_card, name и age превышала этот предел, поэтому при сортировке используется следующий алгоритм сортировки rowid.
# 限制设置为16个字节
set max_length_for_sort_data = 16;
# 查询数据
select id_card,name,age from user where name = "张三" order by age limit 3
Взяв приведенный выше запрос SQL в качестве примера, процесс сортировки rowid выглядит следующим образом:
- Сначала MySQL выделяет кусок памяти размером sort_buffer_size для соответствующего потока, а затем подтверждает, какие поля помещать в память, поскольку в примере запрашиваются три поля id_card, имя и возраст, а длины этих три поля одинаковы Сумма превышает предел max_length_for_sort_data, поэтому для сортировки используется rowid, поэтому поля, которые должны храниться в этом sort_buffer, — это возраст и идентификатор первичного ключа;
- Механизм хранения сначала находит первый конечный узел с name="Zhang San" в дереве индекса имен, а затем возвращает значение идентификатора первичного ключа, хранящееся в конечном узле;
- В соответствии с идентификатором первичного ключа, возвращенным на предыдущем шаге, вернитесь к таблице, вернитесь к дереву индекса первичного ключа, чтобы найти запись, соответствующую идентификатору, и выньте значение поля возраста, верните его на сервер. слой MySQL и поместить его в sort_buffer;
- Продолжайте искать следующий узел с name="Zhang San" в дереве индекса имен, повторяйте шаги 2 и 3 и останавливайте поиск до тех пор, пока первое имя не будет равно Zhang San в дереве индекса имен.
- Предыдущие 4 шага нашли все данные, имя которых Zhang San, и поместили поле age и идентификатор первичного ключа для сортировки в sort_buffer, следующим шагом является сортировка всех данных по возрасту в sort_buffer;
- Из отсортированных данных возьмите первые 3 данных. Поскольку данные, которые мы хотим запросить, представляют собой три поля id_card, имя и возраст, в настоящее время в sort_buffer есть только поля id и age, поэтому в настоящее время нам нужно прочитать id_card из индексного дерева первичного ключа в соответствии с к идентификатору трех полученных данных. , имя, значение возраста;
- Наконец, верните данные полей id_card, name и age.
Схематическая диаграмма этого процесса выглядит следующим образом:
Из этого процесса мы можем обнаружить, что по сравнению с сортировкой по полному полю сортировка по идентификатору строки дает больше результатов для таблицы.
Точно так же мы можем просмотреть информацию, записанную в optimizer_trace, когда идентификатор строки отсортирован. Оператор выполнения выглядит следующим образом:
select * from information_schema.optimizer_trace\G
Из рисунка видно, что идентификатор строки, отображаемый в строке sort_mode, указывает на то, что эта сортировка использует сортировку по идентификатору строки, а для полной сортировки поля идентификатор строки не отображается.
упорядочить по идеям оптимизации
Поняв принцип полной сортировки полей и сортировки по идентификатору строки, мы теперь можем подумать о том, как оптимизировать сортировку SQL.
1. Отрегулируйте размер sort_buffer_size
В первую очередь, будь то сортировка по полному полю или сортировка по rowid, на них будет влиять размер памяти sort_buffer, если данных слишком много, то это приведет к сортировке по дисковым файлам. С помощью сортировки файлов на диске генерируется дисковый ввод-вывод, а производительность падает.Очевидно, это не то, что мы ожидаем, и мы должны постараться этого избежать.Если параметр sort_buffer_size слишком мал, а конфигурация сервера MySQL высока, мы можем попытаться увеличить sort_buffer_size..
2. max_length_for_sort_data
Когда длина поля запроса превышает предел max_length_for_sort_data, MySQL будет использовать сортировку по строке, но сортировка по строке будет генерировать больше времени возврата, что может привести к чтению с диска и снижению производительности запроса, поэтому, чтобы избежать использования MySQL сортировки по строке, мы можем соответствующим образом увеличьте значение параметра max_length_for_sort_data.
Значение параметра max_length_for_sort_data в MySQL по умолчанию равно 1024, что составляет 1 КБ. Я лично считаю, что это значение уже очень велико.1024 байта уже может содержать много полей.В среднем 8 байт на поле (кроме типа varchar переменной длины) может вместить почти 256 полей. Если длина опрашиваемого поля в вашем запросе SQL превышает 1024 байта, то очень вероятно, что SQL написан неправильно, мы можем попытаться оптимизировать SQL вместо настройки системных параметров MySQL. Например, уменьшая поля запроса, разделяя запрос на несколько раз или оптимизируя SQL через промежуточную таблицу (это место также подтверждает утверждение, что стараются не использовать операторы SQL, такие как select *). Короче говоря, я лично считаю, что значение параметра max_length_for_sort_data не следует максимально корректировать.
Говоря об этом, в книге «High Performance MySQL», в начале главы 8, автор упомянул, что хотя MySQL предоставляет нам множество настраиваемых системных параметров, большинство из этих параметров можно напрямую использовать по умолчанию. для настройки в соответствии с реальной сценой. Если мы слишком сильно подкорректируем параметры, и это не было проверено на реальном производстве, очень вероятно, что это будет иметь противоположный эффект.
3. Используйте объединенный индекс
Поскольку данные не в порядке, нам нужно отсортировать данные.Если предполагается, что сами данные в порядке, то нам не нужно сортировать данные, избегая следующего размера памяти sort_buffer, сортировки файлов на диске и т. д. , проблема. Мы все знаем, что структура данных индекса в MySQL использует дерево B +, Основная особенность этой структуры данных заключается в том, что значения индекса хранятся упорядоченно, поэтому мы можем использовать эту функцию упорядочения, чтобы избежать работы по сортировке. .
Для SQL в примере в этой статье, если само поле age упорядочено в дереве индексов, то нам не нужно сортировать в sort_buffer, поэтому мы можем рассмотреть возможность создания совместного индекса имени и возраста: index(name , возраст).
Продолжайте думать, потому что нам нужно запросить информацию из трех полей id_card, имени и возраста, а объединенный индекс индекса (имя, возраст) имеет только значения полей имени и возраста, что означает, что хотя мы можно избежать этого совместного индекса Отбросить операцию сортировки, но нам все равно нужно вернуться к дереву индекса первичного ключа, чтобы получить значение поля id_card, то есть нам нужно вернуться к таблице, что может вызвать чтение с диска, поэтому у нас еще есть возможности для оптимизации.
Если вы читали эту статью моих друзей»Как работают индексы MySQL", в настоящее время вы можете подумать, что наиболее распространенным способом избежать операций возврата таблицы является использование технологии покрывающего индекса, поэтому в настоящее время мы можем создать совместный индекс трех полей имени, возраста и id_card:index(name,age,id_card), так что все данные, хранящиеся в объединенном индексном дереве, соответствуют данным, которые мы хотим запросить, поэтому нет необходимости выполнять операцию таблицы возврата. Оператор SQL выглядит следующим образом:
# 我们先删除前面为name字段创建的索引
alter table user drop index `name`;
# 创建name、age、id_card的联合索引
alter table user add index(`name`,`age`,`id_card`);
# 使用explain关键字，查看一下SQL的执行计划
EXPLAIN select id_card,name,age from user where name = "张三" order by age limit 3\G;
План выполнения SQL выглядит следующим образом:
Из рисунка видно, что строка Extra стала Using index, а Using filesort отсутствует.Использование индекса означает, что используется покрывающий индекс, а отсутствие использования файловой сортировки означает, что это выполнение SQL не требует использования sort_buffer для операций сортировки..
Следует отметить, что в примере SQL из этой статьи запрашиваются только данные с name="Zhang San", поэтому мы можем гарантировать, что значение поля age упорядочено в объединенном индексе index(name, age, id_card ). Если нашим условием запроса является имя в («Чжан Сан», «Ван Ву»), то нельзя гарантировать, что поле возраста будет в порядке, потому что в объединенном индексе первый столбец гарантированно будет в порядке, а затем следующие гарантированы в свою очередь Столбец упорядочен, поэтому его еще нужно отсортировать в это время. Если мы все еще хотим воспользоваться этой функцией, мы можем сделать запрос дважды в это время, а затем отсортировать данные в памяти приложения, например:
# 分两次查询
select id_card,name,age from user where name = '张三' order by age limit 3;
select id_card,name,age from user where name = '李四' order by age limit 3;
# 然后在应用程序中自己排序
Ответь на открытие
В начале статьи я упомянул вопрос: добавление индекса к полям в предложении order by точно ускорит эффективность выполнения SQL? Теперь давайте проведем эксперимент.Таблица-образец и данные все те же, что и раньше, разница в том, что мы удаляем все индексы, кроме первичного ключа, а затем создаем индекс для поля age. В итоговой пользовательской таблице id является индексом первичного ключа, а столбец age имеет общий индекс. Затем мы используем ключевое слово Explain для просмотра плана выполнения следующего SQL:
explain select id_card,name,age from user order by age limit 3 \G
План выполнения показан на рисунке ниже.
Из рисунка видно, что значение строки ключа равно null, что означает, что возрастной индекс не используется; значение строки типа равно ALL, что означает, что было выполнено полное сканирование таблицы; значение Дополнительная строка использует сортировку файлов, что означает, что Сортировка в sort_buffer.
Видя, что это немного спойлер, мы создали индекс для столбца возраста, почему мы его не использовали?
Это связано с тем, что мы хотим запросить три поля id_card, name и age, а дерево индекса возраста не хранит эту информацию, поэтому мы, наконец, возвращаем таблицу в дерево индекса первичного ключа для запроса этой информации. В настоящее время MySQL считает, что, хотя значение поля age в дереве возрастных индексов упорядочено, операций сортировки можно избежать, но необходимо вернуть таблицу в дерево индексов первичного ключа, чтобы получить информацию о других полях. что производительность, потребляемая этой операцией таблицы возврата, больше, чем Избегайте экономии производительности операций сортировки, поэтому просто сканируйте дерево индекса первичного ключа напрямую вместо использования дерева индекса возраста.
Продолжаем эксперимент, данных в нашей пользовательской таблице слишком мало, всего 16 штук, теперь добавляем немного данных, я написал простенькую хранимую процедуру, и вставил в базу данных 100 000 штук данных (для простоты каждый кусок данных Значения name и id_card все составлены).
delimiter ;;
create procedure fakeData()
BEGIN
DECLARE
i INT;
SET i = 1;
WHILE
( i <= 100000 ) DO
INSERT INTO user(id_card,name,age)
VALUES
( '429006xxxxxxxx2135', CONCAT('AA',i), i%100 ); # 身份证号码都是一样的（实际情况显然不是这样），姓名为AA+i，年龄为对i除以100取模
SET i = i + 1;
END WHILE;
END
delimiter ;;
# 执行存储过程
call fakeData();
Теперь в таблице около 100 000 строк данных, мы используем Объяснение для проверки вышеуказанного процесса запроса:
explain select id_card,name,age from user order by age limit 3 \G
План выполнения запроса показан на следующем рисунке:
Как видно из рисунка, тип — index, а ключ — age, значит, используется возрастной индекс, а Extra row отображается как null, а это значит, что никакой дополнительной сортировки не требуется. Один и тот же оператор SQL из-за разного количества данных в таблице видит разные планы выполнения, почему так?
Это связано с тем, что объем данных в таблице относительно велик, а дерево индекса первичного ключа id содержит 100 000 строк данных.Если выполняется полное сканирование таблицы в дереве индекса id, MySQL будет думать, что этот процесс будет занимать много времени. Обходя дерево возрастных индексов, получите идентификаторы, соответствующие трем верхним возрастам в порядке, а затем верните таблицу в дерево индексов первичных ключей, чтобы получить данные.Этот процесс выполняется быстрее, чем выполнение полного сканирования таблицы по индексу идентификаторов. дерево напрямую, поэтому я решил пойти с возрастом.Дерево индексов - это то, что мы видим в плане выполнения.
Теперь давайте посмотрим на план выполнения следующего оператора SQL:
explain select id_card,name,age from user order by age limit 1000 \G
Эта инструкция SQL похожа на предыдущую, разница только в том, что limit берет первые 1000 записей, в результате мы видим, что на скриншоте плана выполнения тип ALL и ключ null, а это значит, что эта инструкция SQL не использует возрастной индекс. , и выполняется полное сканирование таблицы. Дополнительная строка — «Используется файловая сортировка», указывающая, что ее необходимо отсортировать в памяти.
что насчет этого? Это связано с тем, что если вы используете дерево индекса возраста, вам нужно вернуться к таблице и вернуться к дереву индекса первичного ключа, чтобы получить данные. Ограничение составляет 1000, что означает, что необходимо получить 1000 фрагментов данных, что требует 1000 раз для возврата таблицы.Задача MySQL, которую этот процесс возвращает слишком много раз и потребляет слишком много.Лучше непосредственно выполнить полное сканирование таблицы в дереве индекса первичного ключа, поэтому возраст не выбран.
Прочитав эти три примера, одна и та же логика SQL, разница в количестве данных в таблице и количестве возвращаемых элементов данных, но план выполнения я вижу разный, некоторые используют индексы, некоторые не используют индексы, в результате Причина этих явлений заключается в том, как выбран оптимизатор MySQL. Следовательно, в фактическом процессе разработки, независимо от того, использует оператор SQL индекс или нет, нам нужно сначала проверить план выполнения с помощью объяснения, чтобы определить, поэтому не говорите об оптимизации SQL в будущем, а просто скажите создать индекс независимо того, является ли это 3721.
Суммировать
В этой статье в основном объясняются два метода упорядочивания путем сортировки: сортировка по полному полю и сортировка по идентификатору строки.На процесс сортировки влияют системные параметры sort_buffer_size и max_length_for_sort_data. Когда объем запрашиваемых данных превышает размер sort_buffer_size, для сортировки будет использоваться файл на диске. Если слишком много полей для запроса, и сумма длин полей запроса каждой строки записей превышает max_length_for_sort_data, MySQL посчитает, что объем данных слишком велик и может превышать sort_buffer_size, поэтому он выберет использование сортировка по ряду.
Как разработчик узнает, сортируется ли оператор SQL с помощью сортировки по полному полю или сортировки по идентификатору строки? Использовали ли вы сортировку дисковых файлов в процессе сортировки? Вы можете увидеть это, взглянув на optimizer_trace,number_of_tmp_filesПредставляет количество временных файлов на диске изsort_modeВ этой строке вы можете узнать сортировку, если отображается идентификатор строки, это сортировка по идентификатору строки, в противном случае это сортировка по полному полю. По умолчанию переключатель optimizer_trace выключен, так как статистика этой информации требует дополнительных ресурсов сервера MySQL.
Затем мы предлагаем несколько идей по оптимизации порядка за оператором на основе освоенного нами принципа сортировки порядка по.Вы можете повысить эффективность выполнения SQL, настроив системные параметры MySQL sort_buffer_size и max_length_for_sort_data или создав объединенный индекс.
Наконец, на нескольких примерах я доказал, что даже если индекс создается для полей в предложении order by, индекс не может быть выбран во время выполнения.Оптимизатор MySQL решит, использовать ли индекс в соответствии с реальной ситуацией. В реальном процессе разработки, если мы хотим оптимизировать оператор SQL, он должен быть таким же, в сочетании с реальным сценарием, с помощью таких инструментов, как объяснение, а затем решить, как оптимизировать SQL после анализа.
использованная литература
- «Высокопроизводительный MySQL»
- Geek Time Линь Сяобинь "MySQL Практические боевые 45 лекций"
Связанный
- журнал повторов — принцип, по которому данные не теряются при выходе из строя MySQL
- B-дерево и B+дерево структуры данных индекса (часть 1)
- B-дерево и B+дерево структуры данных индекса (часть 2)
- Почему MySQL не использует в качестве индексов такие структуры данных, как массивы, хеш-таблицы и двоичные деревья?
- Как работают индексы MySQL