Back-end программисты должны:

предисловие

В ежедневной разработке мы часто используем сортировку, дорогие друзья, а вы знаете, как работает сортировка? Какова идея оптимизации порядка? Каковы меры предосторожности при использовании order by? Эта статья будет учиться вместе с вами и покорять порядок ~

Публичный аккаунт WeChat:маленький мальчик собирает улиток
адрес github, спасибо за каждую звезду
Если вы чувствуете, что что-то приобрели, пожалуйста, помогите, поставьте лайк, перешлите, спасибо

Простой пример с использованием порядка

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

CREATE TABLE `staff` (
`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 '年龄',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )
) ENGINE = INNODB COMMENT '员工表';

Данные таблицы следующие:

Теперь у нас есть такое требование:Запросите 10 лучших сотрудников из Шэньчжэня по имени, возрасту и городу и отсортируйте их по возрасту.. Соответствующий оператор SQL можно записать следующим образом:

select name,age,city from staff where city = '深圳' order by age limit 10;

Логика этого утверждения понятна, но егонизкоуровневый поток выполненияНа что это похоже?

Как работает порядок

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

мы сначала используемExplainОзнакомьтесь с планом выполнения по ключевому слову

план выполненияkeyЭто поле указывает, что используется индекс idx_city
Дополнительно этого поляUsing index conditionУказывает условия индекса
Дополнительно этого поляUsing filesortУказывает, что используется сортировка

Мы можем обнаружить, что этот SQL использует индексы, а также использует сортировку. тогда этокак сортироватькак насчет?

Сортировать по всем полям

MySQL выделяет небольшой блок для каждого потока запросаОЗУ, заСортировать, называетсяsort_buffer. Когда ставить поле в сортировку, собственно поidx_cityИндекс находит соответствующие данные, а затем помещает данные.

Давайте рассмотрим, как индекс находит совпадающие данные.Теперь давайте сначала нарисуем дерево индекса.idx_cityДерево индексов выглядит следующим образом:

дерева индексов idx_city, конечные узлы хранятидентификатор первичного ключа. Существует также дерево кластеризованного индекса первичного ключа id, давайте нарисуем диаграмму дерева кластеризованного индекса:

Как наш запрос находит соответствующие данные?? пройти первымidx_cityИндексное дерево, найдите соответствующий идентификатор первичного ключа, а затем выполните поиск по полученному идентификатору первичного ключа.дерево индекса первичного ключа id, найдите соответствующие данные строки.

плюсorder byПосле этого общий поток выполнения выглядит следующим образом:

MySQL инициализируется для соответствующего потокаsort_buffer, введите запрашиваемые поля имени, возраста и города;
отИндексное дерево idx_city, найти первый идентификатор первичного ключа, который удовлетворяет условию city='Shenzhen', то есть id=9 на рисунке;
прибытьдерево индекса идентификатора первичного ключаПолучите эту строку данных с id=9, возьмите значения трех полей имени, возраста и города и сохраните их в sort_buffer;
отИндексное дерево idx_cityПолучить идентификатор первичного ключа следующей записи, то есть id=13 на рисунке;
Повторяйте шаги 3 и 4, покаЗначение города не равно Шэньчжэнюдо;
Предыдущие 5 шагов нашли всегород Шэньчжэньdata в sort_buffer отсортировать все данные по возрасту;
По отсортированному результату берутся первые 10 строк и возвращаются клиенту.

Схема выполнения следующая:

Прочитайте все поля, требуемые запросом, в sort_buffer, то естьСортировать по всем полям. Тут у некоторых знакомых может возникнуть вопрос, в sort_buffer помещаются все поля запроса, а sort_buffer идет из куска памяти.Если объем данных слишком большой, что делать, если sort_buffer не помещается?

Вспомогательная сортировка временных файлов на диске

На самом деле размер sort_buffer управляется параметром:sort_buffer_size. Если данные для сортировки меньше, чем sort_buffer_size, сортируйте вsort_bufferделается в памяти, если данные для сортировки больше, чем sort_buffer_size, тоСортировать по файлу на диске

Как я могу определить, используется ли файл на диске для сортировки? Вы можете использовать следующие команды

## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
select * from information_schema.optimizer_trace

Доступна сnumber_of_tmp_filesчтобы увидеть, используются ли временные файлы.

number_of_tmp_filesУказывает количество временных файлов на диске, используемых для сортировки. Если number_of_tmp_files>0, это означает, что для сортировки используются файлы диска.

Как выглядит весь процесс сортировки с использованием временных файлов на диске?

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

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

У друзей может возникнуть вопрос, т.к.sort_bufferЕсли вы не можете поставить его, вам нужно использовать временный файл на диске, что повлияет на эффективность сортировки. Тогда зачем помещать в sort_buffer нерелевантные поля сортировки (имя, город)? Поместите только поле возраста, связанное с сортировкой, ононе ароматный? может понятьсортировка по ряду.

сортировка по ряду

Сортировка rowid есть, только запрос SQLИдентификатор поля и первичного ключа, необходимые для сортировки, поместите его в sort_buffer. Так как же определить, использовать ли сортировку по полному полю или сортировку по строке?

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

show variables like 'max_length_for_sort_data';

max_length_for_sort_dataЗначение по умолчанию — 1024. Поскольку длина имени, возраста и города в примере из этой статьи = 64 + 4 + 64 = 132

## 修改排序数据最大单行长度为32
set max_length_for_sort_data = 32;
## 执行查询SQL
select name,age,city from staff where city = '深圳' order by age limit 10;

Если для сортировки используется rowid, каков весь процесс выполнения SQL?

MySQL инициализируется для соответствующего потокаsort_buffer, поместите поле возраста, которое необходимо отсортировать, и идентификатор первичного ключа;
отИндексное дерево idx_city, найти первый идентификатор первичного ключа, который удовлетворяет условию city='Shenzhen', то есть id=9 на рисунке;
прибытьдерево индекса идентификатора первичного ключаПолучить строку данных с id=9, взять значения age и id первичного ключа и сохранить их в sort_buffer;
отИндексное дерево idx_cityПолучить идентификатор первичного ключа следующей записи, то есть id=13 на рисунке;
Повторяйте шаги 3 и 4, покаЗначение города не равно Шэньчжэнюдо;
На предыдущих 5 шагах были найдены все данные, город Шэньчжэнь.sort_buffer, отсортировать все данные по возрасту;
Пройдите отсортированные результаты, возьмите первые 10 строк и следуйте значению idвернуться к оригиналу, выньте три поля города, имени и возраста и верните их клиенту.

Схема выполнения следующая:

СравниватьСортировать по всем полямпроцесс, rowid сортируется еще разформа возврата.

Что такое форма возврата? Процесс получения первичного ключа и последующего возврата к запросу индекса первичного ключа называется возвратом к таблице.

мы проходимoptimizer_trace, вы можете увидеть, используется ли сортировка rowid:

## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
select * from information_schema.optimizer_trace

Сравнение сортировки по полному полю и сортировки по строке

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

В обычных условиях для механизма хранения InnoDB предпочтительно использоватьиспользовать полное полеСортировать. Его можно найтиmax_length_for_sort_dataПараметр установлен на 1024, что является относительно большим. В нормальных условиях поле сортировки не превысит это значение, т. е.полное полеСортировать.

Некоторые идеи оптимизации заказа по

Как мы можем оптимизировать порядок по инструкции?

Поскольку данные неупорядочены, их необходимо отсортировать. Если сами данные упорядочены, то нет необходимости их упорядочивать. Упорядочиваются сами данные индекса, создаемсовместный индекс, оптимизируйте порядок по инструкции.
Мы также можем настроитьmax_length_for_sort_dataоптимизация других параметров;

Совместная оптимизация индекса

Давайте снова рассмотрим план запроса примера SQL.

explain select name,age,city from staff where city = '深圳' order by age limit 10;

Даем условия запросаcityи поле сортировкиage, добавьте совместный индексidx_city_age. Ознакомьтесь с планом выполнения

alter table staff add  index idx_city_age(city,age);
explain select name,age,city from staff where city = '深圳' order by age limit 10;

можно найти плюсidx_city_ageсовместный индекс, не нужноUsing filesortотсортировано. Зачем? потому чтоСам индекс упорядочен, мы можем видетьidx_city_ageСхематическая диаграмма суставного индекса выглядит следующим образом:

Весь процесс выполнения SQL становится фиолетовым:

Найдите удовлетворение по индексу idx_city_ageгород = 'Шэньчжэнь'идентификатор первичного ключа
прибытьиндекс идентификатора первичного ключаВыньте всю строку, получите значения трех полей имени, города и возраста и верните их непосредственно как часть набора результатов.
из индексаidx_city_ageУдалить идентификатор первичного ключа следующей записи
Повторяйте шаги 2 и 3, пока не найдетеСтатья 10запись илиНе удовлетворяет город = 'Шэньчжэнь'Цикл заканчивается, когда условие выполняется.

Блок-схема выглядит следующим образом:

Судя по схематической диаграмме, все еще есть операция «обратно к столу». Есть ли более эффективное решение для этого примера? Да, вы можете использоватьиндекс покрытия:

Индекс покрытия: в столбце данных запроса вам не нужно возвращаться к таблице для проверки, вы можете получить желаемые результаты непосредственно из столбца индекса. Другими словами, данные столбца индекса, используемые вашим SQL, охватывают столбцы результата запроса, даже если индекс покрыт.

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

Оптимизация параметров настройки

Мы также можем оптимизировать выполнение ордера, настроив параметры. Например, вы можете настроить значение sort_buffer_size. Поскольку значение sort_buffer слишком мало, если объем данных большой, он будет отсортирован по временным файлам на диске. Если конфигурация сервера MySQL высока, вы можете использовать немного больший размер.

Мы также можем настроить значение max_length_for_sort_data.Если значение слишком мало, порядок будет сортироваться по rowid и возвращаться к таблице, что снижает производительность запроса. Таким образом, max_length_for_sort_data может быть соответственно больше.

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

Некоторые примечания по использованию order by

Нет условия где, нужно ли индексировать порядок по полю?

В ежедневном процессе разработки мы можем столкнуться с порядком без условия «где».Тогда нужно ли в это время индексировать поля, стоящие за порядком? Если есть такой SQL, нужно ли индексировать create_time:

select * from A order by create_time;

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

Если запрос SQL изменен:

select * from A order by create_time limit m;

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

Когда предел подкачки слишком велик, это вызовет большое количество сортировок.

Предположим, что SQL выглядит следующим образом:

select * from A order by a limit 100000,10

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

Порядок хранения индекса не соответствует порядку по, как оптимизировать?

Предполагая, что есть совместный индекс idx_age_name, нам нужно изменить его следующим образом:Запросите имена и возраст 10 лучших сотрудников и отсортируйте их по возрасту, если возраст одинаковый, отсортируйте по имени в порядке убывания.. Соответствующий оператор SQL можно записать следующим образом:

select name,age from staff  order by age ,name desc limit 10;

Давайте посмотрим на план выполнения и обнаружим, что мы используемUsing filesort.

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

Как мы можем его оптимизировать? Если MySQL версии 8.0, поддержкаDescending Indexes, вы можете изменить индекс следующим образом:

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name` desc) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

Когда используется несколько атрибутов условия in, выполняется ли процесс сортировки при выполнении SQL?

если мы имеемСовместный индекс idx_city_name, если вы выполните этот SQL, он не будет проходить процесс сортировки, как показано ниже:

select * from staff where city in ('深圳') order by age limit 10;

Однако если используется условие in и имеется несколько условий, выполняется процесс сортировки.

 explain select * from staff where city in ('深圳','上海') order by age limit 10;

Это связано с тем, что: in имеет два условия: когда возраст удовлетворяется в Шэньчжэне, возраст сортируется, но если также добавляется возраст, удовлетворяющий Шанхаю, нельзя гарантировать, что будут отсортированы все возрасты. Отсюда необходимость использования файловой сортировки.

В конце концов

Если вы чувствуете, что что-то приобрели, пожалуйста, помогите, поставьте лайк, перешлите, спасибо
Публичный номер поиска WeChat:маленький мальчик собирает улиток, добавить в друзья, вступить в группу технического обмена

Ссылка и спасибо

MySQL бой 45 лекций