Shedal

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

В принципе же, ответ таков: выделять классический DAL — Data Access Layer. Для этого существует много стандартных паттернов. Например:

• Data Mapper (+Repository)
• Active Record
• А то и Query Object

Самое простое — Data Mapper. Создаёте интерфейс для вашей сущности:

interface ICarData
{
  void Insert(Car car);
  Car GetById(int id);
  // и т.д.
}

Затем наследуете от этого интерфейса классы, его реализующие — e.g. MySqlCarData, MongDbCarData.
Отдельная задача здесь — определение, какой класс использовать. Самый простой способ — задавать имя класса через конфигурационный файл и создавать объект нужного класса простой фабрикой (switch(className) {… } ). Более красиво — использовать IoC/DI контейнер.

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

С оверхедом на хранение данных это вы зря. Во-первых, он есть практически везде, а во-вторых, storage сейчас очень дешевый.
По поводу фрагментации — разве BSON-документ может делиться на фрагменты? Насколько я помню, в дефолтной конфигурации монга этого не допускает.

Я по этому поводу когда-то большую фундаментальную статью написал, с рассмотрением разных подходов. Ознакомьтесь, если хотите: habrahabr.ru/post/121265/

Не работает ваш запрос потому, что вы из подзапроса обратно выбираете тот же самый user_id, который «подали вовнутрь». Иными словами, ваш запрос идентичен следующему:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON S.user_id = U.id
WHERE U.id = 1

Как правильно сделать, уже написали выше. ИМХО, вариант с группировкой будет лучше всего, и читабельнее тоже. Хотя лучше сравните производительность на реальных данных. На всякий случай, держите сам запрос:

SELECT u.*, MAX(s.date) max_date
FROM user u
LEFT JOIN stats s
  ON s.user_id = u.id
WHERE u.id = 1
GROUP BY u.id

SELECT *
FROM myTable t1
JOIN myTable t2
    ON t1.Id < t2.Id
    AND t1.Value > t2.Value
WHERE t2.Id = 100
ORDER BY t1.Id DESC
LIMIT 1;

Это так, к слову :)
Если нужно сделать такую выборку для всех Id, то можно сделать подзапрос, или GROUP BY.