Добавление номеров строк в SQL без ORDER BY: оптимизация

Быстрый ответ

Для генерации произвольных номеров строк с использованием функции ROW_NUMBER() можно применить непеременную константу типа 1 или использовать заполнитель, к примеру (SELECT NULL), как обходной путь для сортировки столбцов в предложении ORDER BY:

SELECT 
  /* Это выглядит странно, но его действительно можно применять */
  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNum, 
  *
FROM 
  YourTable;

Такой запрос присвоит каждой строке в таблице YourTable непоследовательные и непредсказуемые номера. Обратите внимание, что при повторном выполнении запроса могут быть получены совершенно другие номера строк – это чем-то напоминает игру в русскую рулетку!

Последствия отсутствия сортировки

Тасование колоды карт

Пропуск ORDER BY в использовании ROW_NUMBER() можно сравнить с перетасовыванием карт. Каждый раз результат будет различным, никакой последовательности не будет.

Подробнее об этом расскажет наш спикер на видео

Игра с недетерминированностью

Недетерминированная сортировка может быть полезной – хаос иногда приносит пользу! Тем не менее, нужно помнить, что это может привести к нестабильности результатов, особенно при обработке больших объемов данных или при мониторинге показателей в Redis для вашего следующего миллионного стартапа.

Магический трюк SQL

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

Достижение упорядоченности результатов

Надежный автоинкремент

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

SELECT 
  /* В данном случае результат будет упорядочен, как в очереди! */
  ROW_NUMBER() OVER (ORDER BY YourPrimaryKeyColumn) AS RowNum, 
  *
FROM 
  YourTable;

Использование временных меток или уникальных идентификаторов

Работаете ли вы с временными метками или UUID? Это отличные инструменты, которые помогут упорядочить данные, при условии, что они являются надежными и уникальными по всей таблице.

SELECT 
  /* Кто-то хотел машину времени? Возвращайтесь и используйте временные метки или 'унникальные' идентификаторы! */
  ROW_NUMBER() OVER (ORDER BY CreationTimestamp) AS RowNum, 
  *
FROM 
  YourTable;

Освоение оконных функций

Основываясь на идеях Ицхака Бен-Гана из его книги о T-SQL, оконные функции, включая ROW_NUMBER(), можно оптимизировать для улучшения производительности с помощью константной сортировки.

Визуализация

Понимайте концепцию ROW_NUMBER без ORDER BY с помощью наглядного примера:

📦📦📦📦📦 (Линия сборки с коробками)
🔢 | 1 | 2 | 3 | 4 | 5  (Присваивание номеров строк)

Без ORDER BY движок SQL базы данных подобен неловкому роботу, случайным образом присваивающему номера коробкам или строкам:

До:     📦📦📦📦📦 (Коробки без номеров)
После:  🏷️📦1  🏷️📦2  🏷️📦3  🏷️📦4  🏷️📦5

Этот неуклюжий робот может приклеивать номера по-разному в разные дни:

День 1: 🏷️📦1  🏷️📦2  🏷️📦3  🏷️📦4  🏷️📦5
День 2: 🏷️📦3  🏷️📦1  🏷️📦5  🏷️📦4  🏷️📦2

Основная идея: Пользуйтесь случайными числами – это то, что дарит нам робот без указания 'ORDER BY'.

Подводные камни и предосторожности

Кэшированные планы выполнения и их влияние

Ваш заказ в Starbucks может предсказуемо приятный, но повторное выполнение SQL-команды может привести к использованию кэшированных планов выполнения, создавая ложное представление о последовательности и влияя на производительность.

Опасное "ORDER BY 1"

Будьте внимательны: неловкий робот может понять ORDER BY 1 как сортировку по первому столбцу, а не по константе 1. Чтобы избежать нежелательных эффектов сортировки, будьте точны в своих указаниях.

Миф о "ORDER BY NULL"

И наконец, оказывается, что ORDER BY NULL не приводит к автоматической упорядоченности данных. Как и в случае с (SELECT NULL), результат остается непоследовательным.

Полезные материалы

1. ROW_NUMBER() in MySQL – Stack Overflow — Данный материал объясняет, как реализовать ROW_NUMBER() в MySQL.
2. Generic Approach to Identify Modified SQL Server Rows — Определите измененные строки в SQL Server и станьте детективом в мире своей базы данных.
3. A Performance Guide for SQL Server Query Optimization – Simple Talk — Нужно вдохновение для оптимизации запросов SQL? Эта статья будет глотком свежего воздуха.
4. SQL tables relations – Database Administrators Stack Exchange — Исследуйте тайный мир отношений между таблицами SQL и найдите идеальное равновесие.