Оптимизация ORDER BY — о чем многие забывают / Хабр

На тему оптимизации MySQL запросов написано очень много, все знают как оптимизировать SELECT, INSERT, что нужно джоинить по ключу и т.д. и т.п.

Но есть один момент, тоже неоднократно описанный во всех мануалах, но почему-то про него все забывают.

Оптимизация ORDER BY в запросах с джоинами.
Оправдание: поиском воспользовался, не нашел !

Большинство считают, что если ORDER BY происходит по индексу, то и проблем никаких нет, однако это не всегда так. Недавно я разбирался с одним запросом который дико тормозил базу хотя вроде все индексы на нужных местах. ORDER BY оказался последним местом, куда я ткнулся, и проблема оказалась именно там.

Маленькая выдержка из мануалов по оптимизации:

===
Как MySQL оптимизирует ORDER BY
Ниже приведены некоторые случаи, когда MySQLне может использовать индексы, чтобы выполнить ORDER BY
…
Связываются несколько таблиц, и столбцы, по которым делается
сортировка ORDER BY, относятся не только к первой неконстантной
(const) таблице, используемой для выборки строк(это первая таблица
в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).
…
===

Для ORDER BY важно, чтобы таблица, по которой будет производиться сортировка была на первом месте. Однако по умолчанаю, в каком бы порядке вы не джойнили таблицы, встроенный в mysql оптимизатор переставит их в том порядке, как он сам посчитает нужным. То есть если вы поставили нужную таблицу первой в запросе, то это вовсе не означает, что она будет на самом деле первой.

К счастью, оптимизатору mysql можно сказать, чтобы он джоинил таблицы в том порядке, какой мы ему указали, для этого нужно в SELECT добавить команду STRAIGHT_JOIN:

SELECT STRAIGHT_JOIN… FROM table JOIN…… ORDER BY table.row

Проверка на mysql базе форума PHPBB3 содержащей около 300 000 постов:

SELECT t.*, p.*, u.username 
FROM phpbb3_topics as t, phpbb3_posts as p,  phpbb3_users as u 
WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='16' AND p.post_id<'244103' 
ORDER by post_id desc LIMIT 40

Query took 12. 2571 sec

в explain видим ужасное: Using where; Using temporary; Using filesort

Меняем порядок таблиц (кеш мускуля сбросил перезагрузкой):

SELECT STRAIGHT_JOIN t.*, p.*, u.username 
FROM phpbb3_posts as p, phpbb3_topics as t, phpbb3_users as u 
WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='13' AND p.post_id<'234103' 
ORDER by post_id desc LIMIT 40

Query took 0.0447 sec

в explain: Using where;

Вот такой принудительной перестановкой таблиц мы ускорили выполнение запроса в 300 раз!

Это совсем не значит, что нужно всегда использовать STRAIGHT_JOIN и самому следить за порядком таблиц. Но в некоторых случах это необходимо.

P.S. Этот запрос используется Яндексом для индексации форумов phpbb. До оптимизации, яндекс-бот клал сервер php.ru каждую ночь на несколько часов (сервер не очень мощный). В блоге Яндекса была дисскуссия на эту тему, но она закрыта пару лет назад и решение там не озвучено.

MySQL ORDER BY DESC | Как работает ORDER BY DESC в MySQL?

Обновлено 31 мая 2023 г.

MySQL ORDER BY DESC — это предложение ORDER BY в MySQL, которое отвечает за сортировку записей базы данных при их выборке в виде строк результата.

По сути, предложение ORDER BY использует атрибуты для указания порядка сортировки записей таблицы. Поэтому нам нужно включить модификаторы ASC или DESC с предложением ORDER BY для этой команды запроса.

Атрибуты в сочетании с предложением ORDER BY определяют расположение строк в таблице результатов в порядке возрастания или убывания. Однако по умолчанию предложение ORDER BY в MySQL автоматически устанавливает значение ASC (по возрастанию), если модификатор не указан. Итак, мы реализуем запрос ORDER BY DESC для получения строк в порядке убывания.

Синтаксис

Последующий синтаксис представляет собой простую структуру для применения предложения ORDER BY DESC к запросу базы данных MySQL:

 SELECT ColumnName1, ColumnName2, …. FROM TableName WHERE Cond_Expr ORDER BY ColumnName1 DESC, ColumnName2DESC,….ColumnNameNDESC;

Давайте объясним синтаксис ниже:

  • Чтобы применить предложение ORDER BY DESC, мы будем использовать оператор SELECT для написания запроса.
  • ColumnName обозначает имена столбцов таблицы, которые пользователь хочет получить.
  • TableName обозначает имя таблицы, включенной в запрос.
  • Предложение WHERE определяет условное выражение, которое таблица применяет для предоставления результата. Это необязательное условие используется для определения строк, отображаемых в результирующем наборе, когда оно удовлетворяется.
  • Предложение ORDER BY с DESC определяет структуру упорядочения с определенным именем столбца.

Как работает ORDER BY DESC в MySQL?

  • Реализация оператора SELECT в запросе MySQL будет извлекать записи данных из определенной таблицы; затем вы заметите, что предоставленная таблица строк результатов не имеет упорядоченной формы.
    Следовательно, чтобы представить набор результатов в виде отсортированного списка, нам нужно добавить предложение MySQL ORDER BY с запросом SELECT.
  • С помощью предложения ORDER BY вы можете упорядочить строки с любым конкретным именем столбца. Чтобы в дальнейшем результат отображался в отсортированном виде, то есть в порядке возрастания или убывания, мы будем использовать ключевые слова ASC или DESC, которые определяют атрибуты для этого предложения ORDER BY.
  • Предложение ORDER BY использует ключевое слово DESC для получения записей в порядке убывания. Мы можем указать один или несколько столбцов таблицы для сортировки после предложения ORDER BY, разделив список столбцов запятыми. Важно отметить, что разные столбцы можно сортировать по-разному, используя ключевые слова ASC или DESC после ORDER BY, следуя указанному синтаксису. Как показано ниже:
 ВЫБЕРИТЕ ИмяСтолбца1, ИмяСтолбца2, …. FROM TableName ORDER BY ColumnName1 ASC, ColumnName2 DESC,….ColumnNameN [ASC | ПОИСК];

Таким образом, строки результатов будут отсортированы определенным образом.

Примеры MySQL ORDER BY DESC

Давайте продемонстрируем несколько примеров с предложением ORDER BY DESC и увидим его использование и работу с ним в MySQL:

Пример №1 — MySQL ORDER BY DESC с использованием одного столбца таблицы для иллюстрации; мы возьмем образец таблицы в нашей базе данных с именем «Клиент», имеющей поля CusotmerID, CutomerName, Credit_Limit и City. Просмотрите содержимое таблицы следующим образом: 

 ВЫБЕРИТЕ * ОТ Заказчика;

Вывод:

Давайте запросим записи данных из таблицы Customer, чтобы отобразить результат, используя предложение ORDER BY просто без атрибута:

 SELECT CustomerID, CustomerName, City FROM Customer ORDER BY CustomerName;

Выходные данные:

Из приведенного выше вывода видно, что строки результатов по умолчанию сортируются в порядке возрастания, даже если ключевые слова ASC и DESC не указаны явно.

Теперь давайте напишем запрос с использованием предложения MySQL ORDER BY DESC для сортировки набора результатов запроса по определенному столбцу таблицы в порядке убывания:

 SELECT CustomerID, CustomerName, City FROM Customer ORDER BY CustomerName DESC;

Вывод:

Чтобы увидеть разницу, вы можете использовать модификатор DESC с предложением ORDER BY, который сортирует содержимое в порядке убывания. Например, столбец CustomerName в запросе указан с ORDER BY DESC.

Пример #2 — MySQL ORDER BY DESC с использованием нескольких столбцов таблицы

Мы будем использовать предложение MySQL ORDER BY DESC с несколькими столбцами таблицы в запросе и увидим набор результатов при выполнении. Оператор MySQL, запрашивающий ту же таблицу Customer, что и предыдущий, выглядит следующим образом:

 SELECT CustomerID, CustomerName, City FROM Customer ORDER BY CustomerName DESC, City DESC;

Вывод:

Здесь мы использовали ключевые слова DESC с обоими именами столбцов после предложения ORDER BY, чтобы получить результирующий набор в порядке убывания. Таким образом, если один столбец указан с модификатором DESC, а другой столбец — нет, результат будет автоматически отсортирован в порядке возрастания.

Пример №3. MySQL ORDER BY DESC с использованием предложения WHERE

Предположим, нам нужно сгенерировать результирующий набор с клиентами, имеющими кредитный лимит больше или равный 3000, из того же запроса, что и использование предложения ORDER BY DESC для выборки строк результатов. . Мы запросим записи с предложением WHERE следующим образом:

 SELECT CustomerName, City, Credit_LimitFROM Customer WHERE Credit_Limit>=3000 ORDER BY City DESC;

Вывод:

Пример №4 — MySQL ORDER BY DESC с использованием опции LIMIT

Мы можем применить предложение LIMIT, которое позволяет ограничить количество строк, извлекаемых в результирующем наборе. Мы будем использовать вместе с предложением ORDER BY DESC опцию LIMIT в конце, чтобы увидеть результаты. Возьмем предыдущий пример и немного изменим его, как показано ниже:

 SELECT * FROM Customer WHERE Credit_Limit>=1500 ORDER BY Credit_Limit DESC;

Вывод:

Здесь мы просто получили результаты, имеющие 6 строк с порядком по кредитному лимиту столбца и предложением WHERE. Теперь, используя LIMIT, запрос становится следующим:

 SELECT * FROM Customer WHERE Credit_Limit>=1500 ORDER BY Credit_LimitDESC LIMIT 3;

Вывод:

Теперь посмотрите на вывод; строки результатов сокращаются до 3, как уже упоминалось, а кредитный лимит находится в порядке убывания, как указано в запросе выше.

Заключение

Предложение ORDER BY, сопровождаемое атрибутом-модификатором DESC, определяет порядок сортировки строк таблицы при выборке с помощью запроса. Если какой-либо атрибут не указан в запросе для предложения ORDER BY, то по умолчанию строки результатов будут упорядочены по возрастанию.

В MySQL вы можете применять предложение ORDER BY DESC в операторах SELECT LIMIT, SELECT и DELETE LIMIT.

Рекомендованные статьи

Мы надеемся, что эта информация EDUCBA о «ORDER BY DESC MySQL» была вам полезна. Вы можете просмотреть рекомендуемые статьи EDUCBA для получения дополнительной информации.

  1. MySQL ЗАМЕНИТЬ
  2. Работа MySQL UNIQUE INDEX
  3. MySQL SYSDATE()
  4. PostgreSQL DATE_PART()

Как использовать ORDER BY и CAST в MySQL

Во многих случаях, когда вы работаете с запросами SQL, очень важно иметь отформатированные данные, когда они возвращаются. Один из способов форматирования — вы можете видеть вещи в определенном порядке. Например, прямо сейчас, если я просто скажу SELECT guides_title FROM guides , и если я запущу этот запрос, он вернет все результаты.

Результаты будут просто отсортированы по идентификатору, который используется по умолчанию для запуска SQL. Итак, если я запущу этот код, вы увидите, что в нем есть «мой блог», «что-то еще», «мой замечательный пост», «мой блог» и «мой блог», и это может быть нормально. Однако у нас также есть возможность добавить предложение ORDER BY, которое позволит нам сортировать данные по мере их поступления и набор результатов.

Итак, чтобы сделать это, мы добавим еще одну строку и скажем «ORDER BY», а затем вы выберете параметр, по которому хотите упорядочить. Итак, здесь я скажу guides_title, а затем вы скажете, хотите ли вы, чтобы он был нисходящим или восходящим. Сначала будем делать спуск.

Теперь, если я запущу этот код, вы увидите, что теперь он отсортирован и содержит «что-то еще», «Мой замечательный пост» и «мой блог». Это все точно отсортировано. Если бы я скопировал это и отсортировал по возрастанию, это был бы тот же набор результатов. Это просто будет перевернуто. Поэтому, если я запущу его снова сейчас, вы увидите, что он отсортирован по алфавиту.

Теперь я хочу показать вам кое-что, что может показаться немного странным. Если я вставлю это и скажу, что мне нужен guides_title, но еще до этого я хочу получить guides_revenue. Тогда я на самом деле хочу отсортировать по доходу, и я попытаюсь сделать это по убыванию. Теперь, если я запущу этот запрос, он будет работать, поскольку он не выдает ошибку.

Однако я хочу, чтобы вы заметили здесь кое-что немного странное. Итак, по умолчанию MySQL работает следующим образом: он обрабатывает это и говорит 750, 750, 500, 1300, а затем 1000. Ну, в этом нет никакого смысла. Если мы сортируем по убыванию, мы ожидаем, что сможем увидеть самые большие числа вплоть до самых маленьких. Теперь, если я переключу это на возрастание и обработаю это, вы увидите, что оно ведет себя более странно.

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

Проблема в том, что когда мы пытаемся упорядочить что-то вроде буквенно-цифрового, например, guides_title, это работает отлично. Однако guides_revenue Если вы помните, если вы посмотрите на схему, то способ, которым мы ее структурировали, был немного другим. Итак, если мы пойдем к нашим столам за руководствами, и если я посмотрю на инспектора схемы в столбцах.

Вы можете видеть, что у нас есть это целое число с 11 VARchar прямо здесь. Таким образом, это не совсем с точки зрения доходов. На самом деле это не имеет никакого смысла, потому что он пытается просто взять все элементы и обрабатывает их как буквенно-цифровые значения, хотя на самом деле это числа.

Это классная вещь, и это одна из причин, по которой мы настроили этот тип данных. Я хочу, чтобы вы видели, что это будет происходить много раз, когда вы работаете с такими вещами, как устаревшие приложения для работы с данными. Где элемент в этом атрибуте данных столбца не соответствует вашим ожиданиям. Здесь же для guides_revenue задано значение varchar.

Вероятно, не то, что вы ожидаете от чего-то вроде дохода. Но будут времена, когда вам придется с этим смириться. Способ, которым вы можете это исправить, — это выполнение так называемого CASTing. Итак, что делает приведение, так это то, что оно дает вам возможность изменять тип данных на лету для поступающих значений.0003

Здесь я скажу приведение как беззнаковое, а затем мы сделаем по возрастанию. Что это собирается сделать, так это ввести эти значения и вместо того, чтобы обрабатывать их так же, как буквенно-цифровые символы. Он будет приводить его к беззнаковому типу данных, что означает, что он будет рассматривать его как число, и именно это представляет беззнаковый тип.

Итак, теперь давайте запустим это, и я обработаю его, теперь вы можете видеть, что теперь это работает.

Итак, когда у меня есть возрастание, наименьшее число здесь находится вверху, а затем внизу есть самое большое число.

No related posts.