Порядок Mysql путем оптимизации

Этот пост в блоге продолжает обсуждение оптимизации индексов MySQL. Если вы не читали предыдущую статью Оптимизация индекса MySQL (часть 1), я предлагаю вам сделать это, прежде чем переходить к этой.

Основное внимание в этом сообщении блога уделяется обсуждению принципов, лежащих в основе функциональности Order By в MySQL.

Примечание. Все тесты, упомянутые ниже, были проведены MySQL 5.7. Результаты могут отличаться при использовании MySQL 8.

Оптимизация Order By.

Предложение Order By также следует правилу самого левого префикса, что означает, что индекс может использоваться для сортировки только в том случае, если столбец, указанный в предложении Order By, является самым левым столбцом в индексе или если он следует за столбцом, указанным в предложении WHERE.

Ключом к сортировке является неотъемлемое свойство order.

Создадим новую тестовую таблицу и построим на ней составной индекс name_age_school:

CREATE TABLE `students` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  `school` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_school` (`name`,`age`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

В соответствии с созданным нами индексом столбец name естественно упорядочен. Когда значения name совпадают, столбец age упорядочен. Когда значения age совпадают, столбец school упорядочивается.

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

explain select id from students order by name;

Посмотрите на столбец Extra.

Если при выполнении инструкции EXPLAIN в столбце Extra вывода появляется Using index, это означает, что используется покрывающий индекс.

Если Using filesort появляется в столбце Extra выходных данных при выполнении инструкции EXPLAIN, это означает, что сортировка на основе файлов будет использоваться вместо сортировки на основе индекса. Если объем сортируемых данных достаточно мал, сортировка будет выполняться в памяти, в противном случае ее нужно будет делать на диске.

Полезный совет: если в столбце "Дополнительно" нет Using filesort, это означает, что используется сортировка на основе индекса.

Давайте рассмотрим пример SQL, в котором используется сортировка на основе файлов:

EXPLAIN SELECT name, age FROM students ORDER BY age DESC;

Вывод инструкции EXPLAIN будет показывать Using filesort в столбце Extra, указывая на то, что будет использоваться сортировка на основе файлов.

Использование индекса означает только использование индекса, а использование сортировки на основе файлов означает только использование сортировки на основе файлов. Это две разные вещи.

Например, вы можете использовать индекс для поиска данных, но результирующие данные сортируются с использованием файловой сортировки.

Продолжим обсуждение некоторых примеров SQL.

SQL 1.

explain select * from students where name = 'n_18' order by age, school;

Для сортировки на основе индекса поле age должно следовать за полем name, а поле school должно следовать за полем age.

SQL 2.

explain select * from students where name = 'n_18' order by school, age;

Для файловой сортировки оператор SQL сначала сортирует по полю school, а затем по полю age. Однако, поскольку поле school неупорядочено, когда поле name такое же, для выполнения сортировки потребуется сортировка на основе файлов.

SQL 3.

explain select * from students where name = 'n_18' and age = 18 order by school, age;

Для сортировки по индексу поле school должно следовать за полем age, и оно должно быть упорядоченным. Кроме того, поле age после предложения ORDER BY на самом деле будет оптимизировано до постоянного значения, поскольку данные запрашиваются по условию age=10.

SQL 4.

explain select * from students where name = 'n_18' order by age asc, school desc;

Для файловой сортировки, в то время как поле age может быть отсортировано с использованием индекса, поле school должно быть отсортировано в обратном порядке.

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

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

SQL 5.

explain select * from students where name = 'n_18' order by age desc, school desc;

Запрос может использовать сортировку по индексу, поскольку поля возраста и школы расположены в порядке убывания, что эквивалентно обходу индекса справа налево.

SQL 6.

explain select * from students where name > 'n_18' order by age, school;

Запрос будет использовать filesort, потому что поле имени использует запрос диапазона, а поле возраста не может использовать индекс.

SQL 7.

explain select * from students where name >= 'n_18' order by age, school;

Еще сортировка файлов. Если вы читали предыдущую статью, у вас могут снова возникнуть некоторые сомнения: не попадает ли поле age в индекс? Почему это сортировка файлов?

Следует еще раз подчеркнуть, что попадание в индекс — это попадание в индекс, а сортировка — это сортировка.

Действительно, когда name=n_18, age и school отсортированы и могут использовать индексную сортировку. Однако, когда name>n_18, age и school неупорядочены, необходима сортировка файлов.

Большой! Если у вас есть дополнительные вопросы или вам нужна дополнительная помощь, не стесняйтесь спрашивать!

Что такое сортировка файлов?

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

В MySQL появление Using filesort указывает на необходимость сортировки файлов.

Эффективность сортировки файлов, как правило, ниже, чем сортировка на основе индекса, из-за большого количества операций с диском I/O.

Сортировку файлов можно разделить на One-way sorting и Two-way sorting.

Односторонняя сортировка.

Однопроходная сортировка — это метод сортировки, при котором весь файл считывается в память, сортируется, а затем результат сортировки записывается обратно на диск. Этот метод подходит для ситуаций, когда объем данных невелик. Однако, если объем данных слишком велик, чтобы поместиться в память, необходима двойная сортировка.

Двусторонняя сортировка.

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

В MySQL были внесены некоторые оптимизации для сортировки.

Для односторонней сортировки MySQL делит сортировку на два этапа: сортировка в памяти и сортировка на диске. На этапе сортировки в памяти MySQL пытается максимально отсортировать данные, считывая их в память.

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

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

MySQL также оптимизирует сортировку слиянием, например, используя деревья сортировки слиянием, чтобы сократить количество сортировок слиянием и повысить эффективность сортировки.

MySQL использует сравнение между системной переменной max_length_for_sort_data (по умолчанию 1024 байта) и общим размером запрашиваемых полей, чтобы определить, какой режим сортировки использовать.

  • Если общая длина полей меньше max_length_for_sort_data, то используется режим односторонней сортировки.
  • Если общая длина полей больше max_length_for_sort_data, то используется двухсторонний режим сортировки.

Сводка.

По возможности используйте индексную сортировку.

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

Если невозможно использовать индексную сортировку, а памяти сервера достаточно, настройте параметр max_length_for_sort_data, чтобы разрешить MySQL использовать одностороннюю сортировку.

Это может уменьшить количество операций дискового ввода-вывода и повысить эффективность.

Если вам нравятся такие истории и вы хотите поддержать меня, пожалуйста, хлопните мне в ладоши.

Ваша поддержка очень важна для меня, спасибо.

Повышение уровня кодирования

Спасибо, что являетесь частью нашего сообщества! Перед тем, как ты уйдешь:

  • 👏 Хлопайте за историю и подписывайтесь на автора 👉
  • 📰 Смотрите больше контента в публикации Level Up Coding
  • 💰 Бесплатный курс собеседования по программированию ⇒ Просмотреть курс
  • 🔔 Подписывайтесь на нас: Twitter | ЛинкедИн | "Новостная рассылка"

🚀👉 Присоединяйтесь к коллективу талантов Level Up и найдите прекрасную работу