Алексей Уколов

https://dev.mysql.com/doc/refman/8.0/en/example-ma...
https://dev.mysql.com/doc/refman/8.0/en/counting-r...

Нужно просто для всех команд списания с баланса использовать мьютекс, внутри которого:
1. Получать текущий баланс.
2. Проверять, что он больше, либо равен сумме списания.
3. Вносить изменения в БД, если всё ок.
Третий шаг можно реализовать по-разному - через таблицу транзакций, через единственное поле баланса, как у вас сейчас или ещё как угодно. Эта часть совершенно не важна.
Важно только то, что функционал списания с баланса в целом становится однопоточным за счёт блокировки.
Нужно только учитывать, что блокировка должна быть распределённой. Стандартом является использование алгоритма Redlock, реализованном на базе Redis.
Ещё важно использовать один блокировщик именно для всех типов списаний. Если вы в buyProduct будете использовать один мьютекс, а в каком-нибудь buyService другой, то работать это правильно не будет.

Будет ли этот запрос быстрее работать, если для столбца `time` есть индекс?

Это покажет EXPLAIN, а ещё лучше - само выполнение запроса с индексом и без него. Скорее всего - будет.

Вообще насколько целесообразно делать индексы на все столбцы, которые есть в условии выборки и в которых много уникальных значений?

В этом и заключается смысл индексации.

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

Есть ещё варианты с конкатенацией и построением JSON, но они неудобные, а запрос получается громоздким.

Первый вариант, понятно, самый адекватный. Но если вам из attendances нужна только одна колонка, то конкатенация её значений тоже будет нормальным решением.

select id, name
from department
where id in (
  select department_id
  from employee
  group by department_id
  having count(*) <= 3
)

Лучше, конечно, разбить на два запроса.

Самая большая проблема с этой системой - зависимые данные. Если в виджете 1 запрос А, работающий с таблицей X, зависит от данных из запроса Б к таблице Y (id сущностей оттуда берёт, например), то запрос Б нужно отправить сразу, не аггрегируя его с запросом В из виджета 2, который работает с теми же данными, но отправляется позже. Большинство запросов зависят друг от друга, поэтому ощутимой пользы такая штука не принесёт.

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

Плюс, как вообще такой системе узнать, что все запросы пришли, и пора их агрегировать и исполнять?

Плюс, виджеты, которые работают с одними и теми же данными, встречаются не часто - система будет работать вхолостую.

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

Плюс, если запросы к одинаковым данным будут генерировать с разной структурой, СУБД не сможет их эффективно кешировать.

На фронтенде и парадигма другая и с запросами проще (их легко различать по URL), поэтому такую штуку и сделать легче и меньше вероятность себе ногу отстрелить. Ну и задержки там значительно серьёзнее - сходить по сети на бэкенд и сходить на бэкенде в БД - это как слетать к Альфе Центавра и съездить на другой конец города, поэтому если есть возможность не летать, её стоит использовать.

На бэкенде такую задачу (оптимизации) эффективнее решать с помощью кэша, вынесения части данных в легковесные хранилища и старых добрых лучших практик работы с БД: правильная схема, корректно составленные запросы, индексы по часто используемым колонкам.

Но вот реальный кейс, где я похожую систему реализовал и использую: есть программа на C++, постоянно делающая запросы к внешнему API. Это внешнее API устанавливает лимит на количество запросов в секунду, в который программа не всегда укладывается. Выходов два: ставить задержки перед запросами или агрегировать их в пакеты (API это поддерживает). Второе решение, очевидно, лучше с точки зрения скорости работы. Но я не реализовывал в нём анализ и объединение похожих запросов - это сложно сделать, легко накосячить, а профит будет относительно небольшой. Всю эту штуку удалось сделать только за счёт того, что все операции асинхронные и запросы выполняются через планировщик. На каком-нибудь стандартном php-проекте этого не добиться.

Что лучше триггер или процедура?

Каждой задаче свой инструмент, каждому инструменту свою задачу.

И почему не запихнуть бы всю логику в процедуры?

• Не всё можно сделать процедурами.
  
• Не всё оптимально делать процедурами.
  
• Процедуры может быть сложнее поддерживать - время DBA обычно дороже, чем время разработчика. Да и требования к уровню гораздо ниже в случае логики в коде - поменять какой-то несложный алгоритм можно доверить и джуниору, а вот менять схему БД его вряд ли пустят.
  
• Изменение процедур в некоторых СУБД может надолго залочить всю БД.
  

Для этого, внезапно, можно использовать таблицу значений и внешние ключи.

Хотя, конечно, создание таблиц при работе с базой данных кому-то может показаться радикальной идеей.

Можно ли связать role_id и id если эти названия не совпадают?

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

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

зачем нужно писать апострофы в sql запросе

Это экранирование на случай использования в именах таблиц и колонок зарезервированных слов.

какой вариант 1 или 2 более правильный и почему

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

Что будет использовать оптимальнее, join или дублирование поля из №2 в №1

Нужно проверить оба варианта на живой базе, только тогда вы получите приближенный к реальности ответ, потому что оптимизатор БД может сделать всё, что угодно, в зависимости от текущего состояния.

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

Денормализация

Mysql не поддерживает оператор EXCEPT, о чём в сообщении об ошибке и написано.
stackoverflow.com/questions/16092353/error-when-us...

Statement - это выражение. Это нейтральный термин, а request подразумевает наличие response, не для всех sql-выражений это актуально.

Union можно оставить, но добавить в каждый из подзапросов лимит и сортировку - зачем вам собирать полностью три таблицы, а потом выкидывать всё, кроме первой строки?

А вообще, такая структура БД - антипаттерн. Цена есть у всех продуктов, вне зависимости от типа, поэтому эта колонка должна быть в основной таблице. Не в последнюю очередь для того, чтобы избежать таких кривых выборок.

Лучше, конечно, разбить это на два запроса: книги и авторы всех выбранных книг через book_id IN (x, y, z).
То, что вы хотите, можно сделать одним сложным запросом, но для каждого автора будет дублироваться информация о книге - зачем гонять лишнее по сети?