Евгений Быков

У меня был опыт участия в написании системы OLTP, в которой был достаточно высокий темп апдейтов, причем часто апдейтились одни и те же записи (из разных транзакций). Для того, чтобы избежать задержек, нужно было обеспечить быстрое завершение транзакций (явный commit или rollback). Если клиент обращается к БД не через хранимые процедуры, то управление транзакциями выполняется на стороне клиента, а значит, если клиент поведет себя некорректно (не станет быстро завершать транзакцию), или возникнут проблемы со связью в середине транзакции, то записи, которые апдейтят многие транзакции, окажутся заблокированы этой подвисшей транзакцией. Если же клиент взаимодействует через хранимые процедуры, то управление транзакциями осуществляется из таких процедур (явно начинается и явно завершается в одной и той же процедуре). Это минимизирует время транзакции. Для нас это было одной из основных причин применения хранимых процедур. Хотя, конечно, существенно было и то, что минимизировался трафик между клиентом и БД. На мой взгляд, эти две причины имеют достаточно большое значение именно для OLTP.
В другом проекте (на MS SQL) были огромные объемы исходных данных. Для сокращения размера БД было разработано очень специфическое представление данных в таблицах, так что эффективно можно было выполнять только некоторые предопределенные запросы с обязательными параметрами. Если в этих запросах не задать обязательные параметры и/или добавить дополнительные условия, то оптимизатор мог запросто выбрать план выполнения, при котором сервер "уходил в себя" (результата дождаться было просто невозможно). Однако заказчик требовал обеспечить ему доступ к этим данным (например предоставить некие views) из его приложений. Если бы мы предоставили ему views, то при использовании их он мог бы не задать обязательные параметры, задать дополнительные условия, перевязать эти view с какими-то другими таблицами/views/подзапросами. При выполнении таких запросов запросто возникли бы проблемы с производительностью (причем, всего сервера БД). Мы передали заказчику набор хранимых процедур с обязательными параметрами (не задать их он уже не смог бы), возвращающие резалтсеты, которые он может вычитывать точно так же, как при выполнении обычного запроса.

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

INSERT INTO player_mob_kills (player_id, mob_id, count)
    SELECT :player_id, :mob_id, 0
        WHERE NOT EXISTS(SELECT *
                             FROM player_mob_kills
                             WHERE player_id = :player_id
                               AND mob_id = :mob_id
                        )
UPDATE player_mob_kills
    SET count=count + :count
    WHERE player_id = :player_id
      AND mob_id = :mob_id;

А если так?

SELECT *
    FROM (SELECT dbo.PN1.date, dbo.PN2.mp_tn3, dbo.PN2.price, dbo.PN2.id
              FROM dbo.PN1 INNER JOIN dbo.PN2 ON dbo.PN1.id = dbo.PN2.mp_pn1
              WHERE (dbo.PN2.mp_tn3 = 31457)
          UNION
          SELECT dbo.NO1.date, dbo.NO2.mp_tn3, dbo.NO2.price, dbo.NO2.id
              FROM dbo.NO1 INNER JOIN dbo.NO2 ON dbo.NO1.id = dbo.NO2.mp_no1
              WHERE (dbo.NO2.mp_tn3 = 31457)
         ) t 
    ORDER BY 1

Порядок сортировки индекса сказывается только при создании индексов с составными ключами, т.е., с ключами из нескольких полей. Т.е., если у вас имеется индекс с ключом (field1, field2, field3) и более частыми являются запросы со следующей сортировкой:
ORDER BY field1 ASC, field2 DESC, field3 ASC, то тогда вам следует использовать индекс (field1 ASC, field2 DESC, field3 ASC) или (field1 DESC, field2 ASC, field3 DESC). Когда же у вас индекс имеет ключ из одного поля, то порядок сортировки его не имеет значения, т.к. SQL engine умеет сканировать индекс в обоих направлениях.
Для добавления записей и для выборок одиночных записей порядок сортировки индекса не играет роли - производительность будет абсолютно одинакова, а для выборки последовательности записей, т.е. при сканировании диапазона индекса этот порядок уже может сиграть, но надо понимать, что на достаточно больших диапазонах индекса. Если просто выгребать всего 50 первых записей и без WHERE, то различие будет незаметно.

Я думаю, что соответствующий алгоритм на псевдокоде можно изобразить как-то так:

Создадим массив из N элементов типа Node и проинициализируем его:

class Node {
    int group;
    int key;
    int index;
}

Node[N] nodes;

for (int i = 0; i < N; i++) {
    nodes[i].group = 0;
    nodes[i].index = i;
}

Последовательность элементов с одинаковым значением поля group будем в дальнейшем называть группой. Таким образом, после инициализации мы имеем одну неотсортированную группу из N элементов.

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

for (int j = 0; j < M; j++) {
    // для каждой группы, содержащей более одного элемента
        // вычисляем значение ключа в каждом элементе этой группы
        // сортируем на месте элементы этой группы по этому ключу
        // разбиваем группу на подгруппы с одинаковым значением ключа, присваиваем подгруппам уникальные номера
    // если все группы содержат ровно по одному элементу, то досрочный выход из цикла
}

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

По-моему, очевидно, что лишних вызовов функций вычисления ключа в этом алгоритме нет.

После завершения цикла все элементы в массиве nodes отсортированы, для ссылки на элементы исходного массива используется значение поля index.

Стандартным прямолинейным решением будет следующий запрос:

SELECT t.name, (SELECT p.price FROM products p WHERE p.id = t.max_id) AS last_price
    FROM (SELECT name, max(id) AS max_id FROM products GROUP BY name) t

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

Вообще-то, с точки зрения архитектуры, правильно для отсутствующих значений использовать именно NULL - это значение для того и существует.
Насчет индексов - это зависит, во-первых, от того, какую RDBMS вы используете, а во-вторых, от того, содержит ли индекс другие колонки.
Так, например, в Oracle ключи состоящие из одних NULL в индекс не попадают, т.е., если, например, индекс строится по 2 колонкам: id и start_date, то в индекс этот ключ не попадет, только если обе эти колонки содержат NULL.
В то же время, в MS SQL Server значения NULL индексируются так же, как и остальные значения.

Для увеличения общей производительности надо заставить RDBMS использовать merge, а не hash join и не nested loop с использованием индекса.
Для этого сливаем две таблицы

SELECT id FROM x
UNION ALL
SELECT id FROM x
UNION ALL
SELECT x_id FROM y

В этом наборе каждый id входит 1 раз (если он содержится в y и не содержится в x), 2 раза (если он содержится в x и не содержится в y) и 3 раза (если содержится в обеих таблицах).
Затем группируем и выбираем те группы, которые содержат ровно две записи

SELECT id
    FROM (SELECT id FROM x
          UNION ALL
          SELECT id FROM x
          UNION ALL
          SELECT x_id FROM y
         )
    GROUP BY id
    HAVING COUNT(*) = 2

Такой запрос не требует индексов и будет очень эффективен даже на очень больших таблицах
Разумеется, решение основано на предположении об уникальности id в таблице x, и уникальности x_id в таблице y. Если же id не уникален в таблице x, то надо выполнять
SELECT DISTINCT id FROM x
вместо
SELECT id FROM x
Это же касается таблицы колонки x_id колонки y

Сливаем две таблицы

SELECT * FROM tbl1
UNION ALL
SELECT * FROM tbl2

затем группируем по всем полям и выбираем те группы, которые содержат более одной записи

SELECT *
    FROM (SELECT * FROM tbl1
          UNION ALL
          SELECT * FROM tbl2
         )
    GROUP BY field1, field2, ...
    HAVING COUNT(*) > 1

Такой запрос не требует индексов и будет очень эффективен даже на очень больших таблицах
Разумеется, решение основано на предположении об уникальности записей в каждой из таблиц