Лекция
Привет, Вы узнаете о том , что такое сортировка mysql, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое сортировка mysql , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.
В этой статье приведены примеры использования оператора ORDER BY для сортировки записей в MySQL.
ASC – (по умолчанию, необязательный). Сортирует набор в порядке возрастания или по алфавиту.
DESC – сортирует в порядке убывания.
Также возможно сделать сортировку в mysql по несколькольким столбцам, указывая их через запятую в порядке приоритета.
С помощью функции FIELD() можно получить записи из БД в нужной последовательности, например вывести первыми записи с определенными `id`.
Также можно скомбинировать сортировку из нескольких полей и показать первыми приоритетные записи, а далее по алфавиту.
Или убрать в конец ненужные записи:
В списках заказов отсортировать значения цен по возрастанию, но при этом нулевые значения были в конце поможет следующий пример.
Для таблица брендов и товаров, нужно отсортировать бренды по количеству товаров.
Проблема возникает если даты хранятся в текстовом виде (например 23.09.2020). Если отсортировать таблицу по такому полю, то результате будут перепутаны месяцы.
Текстовую дату нужно перевести в тип datetime c помощью функции STR_TO_DATE(), указав нужный формат:
Если в сортируемом поле содержатся одновременно текст и цифры, а нужно отсортировать только по цифрам, то данные нужно привести к числовому типу:
или
для этой абстрактной таблицы мы хотим узнать , как менялся остаток на балансе при этом:
MySQL может использовать индекс для удовлетворения ORDER BY предложения, filesort операцию, используемую, когда индекс не может быть использован, и информацию о плане выполнения, доступную от оптимизатора для ORDER BY.
Операторы ORDER BY с и без LIMIT могут возвращать строки в разном порядке, .
Использование индексов для выполнения ORDER BY
Использование файловой сортировки для выполнения ORDER BY
Влияние на оптимизацию ORDER BY
Доступна информация о плане выполнения ORDER BY
В некоторых случаях MySQL может использовать индекс, чтобы удовлетворить ORDER BY предложение и избежать дополнительной сортировки, связанной с выполнением filesort операции.
Индекс также можно использовать, даже если ORDER BYон не соответствует в точности индексу, при условии, что все неиспользуемые части индекса и все дополнительные ORDER BYстолбцы являются константами в WHEREпредложении. Если индекс не содержит всех столбцов, к которым обращается запрос, индекс используется только в том случае, если доступ к индексу дешевле, чем другие методы доступа.
Предполагая, что есть индекс , следующие запросы могут использовать индекс для разрешения части. Действительно ли оптимизатор делает это, зависит от того, будет ли чтение индекса более эффективным, чем сканирование таблицы, если также необходимо прочитать столбцы, не входящие в индекс. (key_part1, key_part2)ORDER BY
В этом запросе индекс on позволяет оптимизатору избегать сортировки: (key_part1, key_part2)
Однако запрос использует SELECT *, который может выбрать больше столбцов, чем key_part1и key_part2. В этом случае сканирование всего индекса и поиск строк таблицы для поиска столбцов, не входящих в индекс, может быть более затратным, чем сканирование таблицы и сортировка результатов. Если да, то оптимизатор, вероятно, не использует индекс. Если SELECT *выбираются только столбцы индекса, используется индекс без сортировки.
Если t1это InnoDB таблица, первичный ключ таблицы неявно является частью индекса, и индекс можно использовать для разрешения ORDER BYэтого запроса:
В этом запросе key_part1является постоянным, поэтому все строки, к которым осуществляется доступ через индекс, находятся в key_part2порядке, а индекс on позволяет избежать сортировки, если предложение является достаточно избирательным, чтобы сделать сканирование диапазона индекса дешевле, чем сканирование таблицы: (key_part1, key_part2)WHERE
В следующих двух запросах, используется ли индекс, аналогично тем же запросам, которые DESCранее не показывались:
Два столбца в элементе ORDER BYмогут сортировать в одном направлении (оба ASCили оба DESC) или в противоположных направлениях (один ASC, один DESC). Условием использования индекса является то, что индекс должен иметь одинаковую однородность, но не обязательно иметь одно и то же фактическое направление.
Если запрос смешивает ASCи DESC, оптимизатор может использовать индекс для столбцов, если индекс также использует соответствующие столбцы смешанного восходящего и нисходящего типов:
Оптимизатор может использовать индекс на ( key_part1, key_part2), если key_part1 идет по убыванию и key_part2 по возрастанию. Он также может использовать индекс для этих столбцов (с обратным сканированием), если он key_part1 выполняяется по возрастанию и key_part2 по убыванию. («Нисходящие индексы» ).
В следующих двух запросах key_part1 сравнивается с константой. Индекс используется, если WHERE предложение достаточно избирательно, чтобы сделать сканирование диапазона индекса дешевле, чем сканирование таблицы:
В следующем запросе имя ORDER BYне указано key_part1, но все выбранные строки имеют постоянное key_part1значение, поэтому индекс все еще можно использовать:
В некоторых случаях MySQL не может использовать индексы для разрешения ORDER BY, хотя он все еще может использовать индексы для поиска строк, соответствующих WHEREпредложению. Примеры:
В запросе используются ORDER BY разные индексы:
Запрос использует ORDER BYнепоследовательные части индекса:
Индекс, используемый для выборки строк, отличается от того, который используется в ORDER BY:
В запросе используется ORDER BYвыражение, которое включает термины, отличные от имени столбца индекса:
Запрос объединяет множество таблиц, и ORDER BY не все столбцы в первой непостоянной таблице, которая используется для извлечения строк. (Это первая таблица в EXPLAIN выходных данных, не имеющая const типа соединения.)
Запрос имеет разные ORDER BYи GROUP BY выражения.
Индекс есть только для префикса столбца, указанного в ORDER BYпредложении. В этом случае индекс не может использоваться для полного определения порядка сортировки. Например, если CHAR(20)проиндексированы только первые 10 байтов столбца, индекс не может различать значения после 10-го байта, и filesort требуется a .
Индекс не хранит строки по порядку. Например, это верно для HASH индекса в MEMORY таблице.
Доступность индекса для сортировки может зависеть от использования псевдонимов столбцов. Предположим, что столбец t1.aпроиндексирован. В этом операторе имя столбца в списке выбора - a. Он относится к t1.a, как и ссылка aв ORDER BY, поэтому t1.aможно использовать индекс :
В этом операторе также используется имя столбца в списке выбора a, но это псевдоним. Он относится к ABS(a), как и ссылка aв ORDER BY, поэтому индекс t1.aне может быть использован:
В следующем утверждении, ORDER BY относится к имени, которое не является именем столбца в списке выбора. Но в t1 named есть столбец a, поэтому можно использовать ORDER BYссылку t1.aи индекс t1.a. ( ABS(a)Конечно, итоговый порядок сортировки может полностью отличаться от порядка сортировки .)
Ранее (MySQL 5.7 и ниже) GROUP BY сортировка выполнялась неявно при определенных условиях. В MySQL 8.0 этого больше не происходит, поэтому указывать ORDER BY NULL в конце для подавления неявной сортировки (как это было сделано ранее) больше нет необходимости. Однако результаты запроса могут отличаться от предыдущих версий MySQL. Чтобы создать заданный порядок сортировки, укажите ORDER BYпредложение.
Если индекс не может использоваться для удовлетворения ORDER BYпредложения, MySQL выполняет filesort операцию, которая читает строки таблицы и сортирует их. A filesortпредставляет собой дополнительную фазу сортировки при выполнении запроса.
Чтобы получить память для filesortопераций, начиная с MySQL 8.0.12, оптимизатор выделяет буферы памяти постепенно по мере необходимости, до размера, указанного sort_buffer_size системной переменной, вместо того, чтобы выделять фиксированное количество sort_buffer_size байтов вперед, как это было сделано до MySQL 8.0. .12. Это позволяет пользователям устанавливать sort_buffer_size более высокие значения для ускорения сортировки большего размера, не беспокоясь о чрезмерном использовании памяти для мелких сортировок. (Это преимущество может отсутствовать для нескольких одновременных сортировок в Windows, которая имеет слабую многопоточность malloc.)
filesortОперация использует временные файлы на диск по мере необходимости , если набор результатов слишком велик , чтобы поместиться в памяти. Некоторые типы запросов особенно подходят для filesortопераций полностью в памяти . Например, оптимизатор может использовать filesortдля эффективной обработки в памяти без временных файлов ORDER BY операцию для запросов (и подзапросов) следующей формы:
Такие запросы распространены в веб-приложениях, которые отображают только несколько строк из большего набора результатов. Примеры:
Для медленных ORDER BYзапросов, которые filesortне используются, попробуйте понизить max_length_for_sort_data системную переменную до значения, подходящего для запуска файла filesort. (Признаком слишком высокого значения этой переменной является сочетание высокой активности диска и низкой активности процессора.) Этот метод применяется только до MySQL 8.0.20. Начиная с версии 8.0.20 max_length_for_sort_data он устарел из-за изменений оптимизатора, которые сделали его устаревшим и не имеющим эффекта.
Чтобы увеличить ORDER BYскорость, проверьте, можете ли вы заставить MySQL использовать индексы вместо дополнительной фазы сортировки. Если это невозможно, попробуйте следующие стратегии:
Увеличьте sort_buffer_size значение переменной. В идеале значение должно быть достаточно большим, чтобы весь набор результатов поместился в буфере сортировки (чтобы избежать записи на диск и проходов слияния).
Учтите, что на размер значений столбцов, хранящихся в буфере сортировки, влияет значение max_sort_lengthсистемной переменной. Например, если кортежи хранят значения длинных строковых столбцов и вы увеличиваете значение max_sort_length, размер кортежей буфера сортировки также увеличивается, и вам может потребоваться его увеличение sort_buffer_size.
Чтобы отслеживать количество проходов слияния (для слияния временных файлов), проверьте Sort_merge_passes переменную состояния.
Увеличьте read_rnd_buffer_size значение переменной, чтобы одновременно читалось больше строк.
Измените tmpdir системную переменную, чтобы она указывала на выделенную файловую систему с большим объемом свободного места. В значении переменной можно указать несколько путей, которые используются циклически; вы можете использовать эту функцию, чтобы распределить нагрузку по нескольким каталогам. Разделите пути двоеточием ( :) в Unix и точкой с запятой ( ;) в Windows. Пути должны указывать на каталоги в файловых системах, расположенных на разных физических дисках, а не на разных разделах на одном диске.
С помощью EXPLAIN вы можете проверить, может ли MySQL использовать индексы для разрешения ORDER BYпредложения:
Если Extraстолбец EXPLAINвывода не содержит Using filesort, используется индекс, а filesort не выполняется.
Если Extraстолбец EXPLAINвывода содержит Using filesort, индекс не используется и filesort выполняется.
Кроме того, если filesortвыполняется a , выходные данные трассировки оптимизатора включают filesort_summary блок. Например:
peak_memory_usedуказывает максимальный объем памяти, используемый в любой момент во время сортировки. Это значение не обязательно должно быть равным значению sort_buffer_size системной переменной. До MySQL 8.0.12 sort_buffer_size вместо этого в выходных данных отображается значение sort_buffer_size. (До MySQL 8.0.12 оптимизатор всегда выделял sort_buffer_size байты для буфера сортировки. Начиная с версии 8.0.12, оптимизатор выделяет память буфера сортировки постепенно, начиная с небольшого объема и добавляя по мере необходимости до sort_buffer_size байтов.)
sort_modeЗначение содержит информацию о содержимом кортежей в буфере сортировки:
: Это указывает на то, что кортежи буфера сортировки - это пары, которые содержат значение ключа сортировки и идентификатор строки исходной строки таблицы. Кортежи сортируются по значению ключа сортировки, а идентификатор строки используется для чтения строки из таблицы.
: Это указывает на то, что кортежи буфера сортировки содержат значение ключа сортировки и столбцы, на которые ссылается запрос. Кортежи сортируются по значению ключа сортировки, а значения столбцов считываются непосредственно из кортежа.
: Как и в предыдущем варианте, но дополнительные столбцы плотно упакованы вместе вместо использования кодирования фиксированной длины.
EXPLAIN не различает, выполняет оптимизатор или нет filesort в памяти. Использование внутренней памяти filesort можно увидеть в выводе трассировки оптимизатора. Ищите filesort_priority_queue_optimization.
Исследование, описанное в статье про сортировка mysql, подчеркивает ее значимость в современном мире. Надеюсь, что теперь ты понял что такое сортировка mysql и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Комментарии
Оставить комментарий
Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Термины: Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL