В этой статье мы узнаем об оптимизации оператора ORDER BY. Перед этим вам необходимо иметь базовое представление об индексе. Для тех, кто не знает, вы можете прочитать статьи об индексах, которые я написал ранее. Теперь давайте начнем.
Два способа сортировки в MySQL
1.Возвращает упорядоченные данные напрямую через последовательное сканирование упорядоченного индекса.
Поскольку структура индекса представляет собой дерево B+, данные в индексе располагаются в определенном порядке, поэтому, если индекс можно использовать в запросе на сортировку, можно избежать дополнительных операций сортировки. Когда EXPLAIN анализирует запрос, Extra отображается как Using index.
2.Filesort sort, сортировка возвращаемых данных
Все операции, которые не возвращают напрямую отсортированные результаты по индексу, являются сортировкой файловой борты, что означает, что выполняются дополнительные операции сортировки. При объяснении анализируются запрос, дополнительно отображается как используя файловые.
Порядок путем оптимизации основных принципов
Минимизируйте дополнительную сортировку и возвращайте упорядоченные данные непосредственно по индексу.
ORDER BY практика оптимизации
Индекс таблицы клиентов, использованной для эксперимента:
Первое примечание:
MySQL может одновременно использовать только один индекс для запроса.Если вы хотите использовать индекс для нескольких полей, создайте составной индекс.
Порядок по оптимизации
1. Поле запроса должно содержать только поля индекса и первичные ключи, используемые этим запросом, а остальные неиндексные поля и поля индекса не используются в качестве поля запроса.
Чтобы запросить только поле индекса, используемое для сортировки, вы можете использовать сортировку индекса:
explain select store_id,email from customer order by store_id,email;
Однако следует отметить, что поле сортировки находится в нескольких индексах, и сортировка по индексу не может использоваться.Запрос может использовать только один индекс за раз:
explain select store_id,email,last_name from customer order by store_id,email,last_name;
запрашивать только поля индекса, используемые для сортировки ипервичный ключ, которые можно отсортировать по индексу:
VO: MySQL Default InnoDB Engine, используемый в индексе физической агрегации, первичный ключ для поиска, поэтому INNODB Engine требует таблицы, должен иметь первичный ключ, даже если не явно указан первичный ключ, InnoDB Engine будет генерировать уникальный неявный первичный ключ, И это индекс должен иметь первичный ключ.
explain select customer_id,store_id,email from customer order by store_id,email;
Запросы к индексированным полям, используемым для сортировки, и полям, отличным от первичного ключа, не будут использовать преимущества сортировки индекса:
explain select store_id,email,last_name from customer order by store_id,email;
explain select * from customer order by store_id,email;
ГДЕ + ORDER BY оптимизация
1.Поле сортировки находится в нескольких индексах и не может быть отсортировано по индексу
Поле сортировки находится в нескольких индексах (не в одном и том же индексе) и не может быть отсортировано по индексу:
explain select * from customer where last_name='swj' order by last_name,store_id;
Голос за кадром: когда поле сортировки не находится в том же индексе, невозможно выполнить сортировку в дереве B+, и необходимо выполнить дополнительную сортировку.
Поле сортировки находится в индексе, а условие WHERE и ORDER BY используют один и тот же индекс., Вы можете использовать сортировку по индексу:
explain select * from customer where last_name='swj' order by last_name;
Конечно, составной индекс также можно отсортировать по индексу:
Обратите внимание, что поля store_id, email находятся в составном индексе.
explain select * from customer where store_id = 5 order by store_id,email;
2.Порядок полей сортировки не соответствует порядку столбцов индекса, и индекс нельзя использовать для сортировки.
Голос за кадром: Это для комбинированного индекса, мы все знаем, что использование комбинированного индекса должно следоватькрайний левый принцип, предложение WHERE должно иметь первый столбец в индексе.Хотя предложение ORDER BY не имеет этого требования, оно также требует, чтобы порядок отсортированных полей соответствовал порядку столбцов объединенного индекса. Когда мы обычно используем составной индекс, мы должны выработать хорошую привычку писать в порядке следования столбцов составного индекса.
Порядок полей сортировки несовместим с порядком столбцов индекса, и порядок индекса нельзя использовать:
explain select * from customer where store_id > 5 order by email,store_id;
Вы должны убедиться, что порядок полей сортировки такой же, как порядок столбцов индекса, чтобы вы могли воспользоваться преимуществом сортировки индекса:
explain select * from customer where store_id > 5 order by store_id,email;
Предложение ORDER BY не требует, чтобы первый столбец был в индексе, по-прежнему нельзя использовать порядок индекса. Но есть условие,Это возможно только для фильтрации по равным значениям, а не для запроса диапазона:
explain select * from customer where store_id = 5 order by email;
explain select * from customer where store_id > 5 order by email;
Голос за кадром:
Причина на самом деле очень проста: при выполнении запроса диапазона первый столбец a должен быть отсортирован (по умолчанию в порядке возрастания), а второе поле b на самом деле не отсортировано. Но если поле a имеет одинаковое значение, то поле b сортируется. Поэтому, если это запрос диапазона, вы можете выполнить только одну дополнительную сортировку по b.
3.Восходящий и нисходящий порядок несовместимы и не могут быть отсортированы по индексу
Поля сортировки ORDER BY либо все сортируются в положительном порядке, либо все в обратном порядке, иначе сортировка по индексу не может быть использована.
explain select * from customer where store_id > 5 order by store_id,email;
explain select * from customer where store_id > 5 order by store_id desc,email desc;
explain select * from customer where store_id > 5 order by store_id desc,email asc;
Суммировать:
Вышеупомянутая оптимизация фактически может быть резюмирована следующим образом:Условие WHERE и ORDER BY используют один и тот же индекс, а порядок ORDER BY такой же, как порядок индекса, а поля ORDER BY расположены в порядке возрастания или убывания.. В противном случае обязательно требуется дополнительная операция сортировки, и происходит Filesort.
Оптимизация сортировки файлов
Появление Filesort можно уменьшить, создав соответствующий индекс, но в некоторых случаях Filesort не может быть полностью исчез, В настоящее время мы можем только найти способ ускорить работу Filesort.
Две алгоритмы сортировки для файловых файлов:
1. Алгоритм двух сканирований
Сначала извлеките поле сортировки и информацию об указателе строки в соответствии с условиями, а затем выполните сортировку в буфере сортировки области сортировки. Этот алгоритм сортировки должен обращаться к данным дважды, первый раз для получения информации о поле сортировки и указателе строки, второй раз для получения записи в соответствии с указателем строки, а вторая операция чтения может вызвать большое количество случайных операций ввода/вывода. О операции. Преимущество в том, что накладные расходы памяти при сортировке невелики.
2. Один алгоритм сканирования
Выньте все поля строк, которые соответствуют условиям, за один раз, а затем напрямую выведите результирующий набор после сортировки в буфере сортировки. Накладные расходы памяти при сортировке относительно велики, но эффективность сортировки выше, чем у алгоритма с двумя сканированиями.
По характеристикам двух алгоритмов сортировки,Соответствующим образом увеличьте значение системной переменной max_length_for_sort_data., что позволяет MySQL выбрать более оптимизированный алгоритм сортировки Filesort. И при написании операторов SQL,Используйте только обязательные поля вместо SELECT * все поля, что может сократить использование области сортировки и повысить производительность SQL.
Ссылаться на
«Углубленный MySQL»
Рекомендуемое чтение
MySQL - Анализ плана выполнения SQL через EXPLAIN
MySQL — оптимизация индексов на практике