SUM таблицы истории в базе данных, чтобы показать общий кредит пользователя (репутация)

Ответ 1

Если мы сделаем это именно тогда, когда пользователь получил некоторые кредиты, которые могут быть получены пользователем, они получили два разных кредита в одно и то же время (очень возможно), и поскольку мы не можем поместить таблицу Auto Increment in Totals (поскольку каждый пользователь только есть 1 запись), мы можем пропустить 1 кредит и не добавлять его в итоговую таблицу, или если есть решение этой проблемы, я не знаю об этом, я только теперь должен использовать ИИ в этих ситуациях

Мы не пропустим это. Проверьте следующие инструкции SQL:

INSERT INTO history SET username = 'X', credits = 2;
UPDATE users SET credits_sum = (SELECT SUM(credits) FROM `history` WHERE username = 'X') WHERE username = 'X';

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

Конечно, вместо username = 'X' следует использовать первичный ключ из таблицы users.

Ответ 2

Чтобы сделать это масштабируемым по мере увеличения количества записей в вашей базе данных, вы можете рассмотреть следующее:

Создайте две таблицы: один, "исторические итоги", содержит итоговые значения для каждого пользователя до 00:00:00 утра; второй может быть (относительно) небольшой таблицей "сегодняшних кредитов".

Когда вам нужен текущий статус, вы добавляете поиск из "исторической таблицы" в "новые кредиты" (маленькая таблица, таким образом, быстро). В полночь вы добавляете все дневные кредиты на итоговые суммы, затем (после задержки) удаляете соответствующие элементы из таблицы "Сегодня". Вам нужна задержка, поэтому нет ситуации, когда элементы удалялись из "текущей" таблицы, когда вы ее запрашиваете. Чтобы вы всегда получали правильный ответ, вы должны пометить "исторические" данные полем "рассчитанное до даты/времени"; и после того, как вы обновили итоговые суммы, вы удалите "всю информацию до этого времени" из "текущей" базы данных. Если вы сначала проверите базу данных итогов для общей и временной метки, затем вычислите "сумму с" из текущей базы данных, не должно быть никакой ошибки. Это причина задержки между обновлением итогов и удалением элементов из текущей базы данных.

Ответ 3

• Да, так и будет. Я бы порекомендовал хранить (под) итоги в другой таблице и позволять хранимой процедуре обновлять их автоматически.
• В больших масштабах вы должны начать денормализацию, поэтому держите сумму, поэтому вам не нужно постоянно ее пересчитывать.
• Pagination - хорошая идея для производительности и удобства использования, поскольку тысячи строк не помогают читаемости. Однако я предложил бы фильтровать по диапазону (т.е. id BETWEEN x AND y вместо LIMIT 100 OFFSET 500
• Да, так и будет. Если есть что-то, что не меняется слишком часто. Загрузите его. Например... в Redis или Memcached.

Ответ 4

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

Предполагая, что таблица для отслеживания итоговых кредитов выглядит примерно так:

CREATE TABLE reputation (
  username varchar(20) primary key,
  total int
)

то триггер будет выглядеть так:

CREATE TRIGGER historyInsert AFTER INSERT ON history
FOR EACH ROW BEGIN
  INSERT INTO reputation (username,total)
  VALUES (NEW.username,NEW.credits)
  ON DUPLICATE KEY UPDATE total = total + NEW.credits;
END

Когда что-то вставляется в вашу таблицу истории, он запускает этот триггер. Для каждой вставленной строки триггер либо вставляет новое значение в таблицу репутации, либо обновляет общее значение, если пользователь уже существует.

Обратите внимание, что INSERT ... ON DUPLICATE KEY UPDATE является атомарной операцией в MySQL, поэтому вам не нужно беспокоиться о двух обновлениях, происходящих одновременно.

Демо-версия SQL Fiddle

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

Затем код запуска становится еще проще:

CREATE TRIGGER historyInsert AFTER INSERT ON history
FOR EACH ROW BEGIN
  UPDATE users SET total = total + NEW.credits
  WHERE username = NEW.username
END

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

И это намного эффективнее, чем вычислять SUM по всей истории каждый раз при вставке нового значения.

Ответ 5

• Как и другие здесь, я защищал бы разделение на "живую" и "историческую" таблицу для пользовательских кредитов. У вас может быть ночная (или еженедельная или любая) работа, переносящая записи из живого в историческое. Если вы можете сохранить "живую" таблицу достаточно компактной, чтобы она (и поддерживающая индексы) в значительной степени была в памяти, производительность не должна быть проблемой. Возможно, вы захотите добавить третью таблицу "итоговых кредитов" в конце любой работы, которую вы используете для ведения исторической таблицы: таким образом, просмотр итогов кредита (за исключением сегодняшнего) - это одно индексированное чтение.

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

• Предел поможет некоторым, но выделяет недостаток дизайна: не храните записи, которые вы не будете ссылаться: они продолжают использовать дисковое пространство, пространство указателей и память. Вы должны быть достаточно рациональными (и хладнокровными) о том, что вам действительно нужно. Посмотрите на свою бизнес-модель: почему вы хотите, чтобы пользователи могли просматривать историю своих кредитов? И вы будете отчуждать их, если отрезаете то, что они могут просматривать при каких-то произвольных ограничениях? Вы должны сами выяснить политику, потому что знаете свой бизнес и своих пользователей. Но сделайте технологию службой политики, а не наоборот.

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

--- Второй набор вопросов

• Перемещение кредитов в историю на дневных границах. Даже в таблице "live" используйте текущую дату как часть выбранных критериев. Таким образом, вы никогда не отбросьте (или дважды считаете) кредиты непреднамеренно.

• Не уверен, что понимаю. Кредиты будут помещаться в "живой" стол в тот момент, когда они заработаны, а затем скопированы в историческую таблицу на границе дня. "Живая" таблица всегда будет актуальной для этого дня, и историческая таблица всегда будет актуальной для вещей старше одного дня.

Надеюсь, ваш проект будет хорошо...

Ответ 6

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

В псевдокоде:

 function postCreditTransaction($username, integer $credit){
      $db->insert("credit_history", array("USERNAME"=>$username, "CREDIT"=>$credit));
      $db->update("update user_table set credit = credit + $credit where username = ".$db->quote($username));
 }

Это даст вам подробную информацию, предоставленную кредитной историей, но с низким уровнем доступа к общему количеству.

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

Ответ 7

Хорошо, начинаем с короткого резюме:

• Да, вам нужно сохранить заранее рассчитанную репутацию для производительности цели.
• Если есть таблица с информацией пользователя - добавьте поле "репутация_sum" (нет смысла отделять эти данные), если нет - создайте специальную таблицу.
• Когда изменения репутации вы узнаете разницу, добавьте эту разницу в "репутацию".

Здесь я имею в виду - не используйте "SELECT SUM всей истории..." для вычисления нового значения "репутация_сум". Когда вы добавляете/обновляете/удаляете запись из таблицы "история", вычисляете total_reputation_change_value и обновляете "репутацию_сум" без пересчета суммы по всем записям таблицы "история". total_reputation_change_value для операции INSERT будет - значение поля "кредиты"; то же для DELETE, но с унарным минусом; разница между старыми и новыми значениями для UPDATE. Это даст гораздо больше запросов/с, если репутация часто меняется. Это также будет нарушать целостность данных немного больше. Если вы этого боитесь - сделайте специальное задание cron, которое обновит данные "Reputation_sum", периодически суммируя записи из истории. Но в большинстве случаев (с правильным определенным рабочим потоком) нет необходимости делать это.

Также я советую вам не использовать USERNAME в качестве внешнего ключа (если у вас есть таблица "users", и это внешний ключ). Лучше сделать целое число USERID. Он будет быстрее искать в таблице истории.

Теперь позвольте мне ответить на ваши вопросы.

Я хотел знать, что это не так (SELECT SUM из всей истории, чтобы показать, что пользовательский кредит INSTEAD имеет другую таблицу для общих пользователей пользователей) будет создавать проблемы, если таблица истории будет настолько огромной? (скажем, 100 000 000 записей через несколько лет)

Да, если рассчитывать репутацию каждый раз из таблицы, которая "позволяет сказать +100 000 000 записей через несколько лет", это будет действительно неэффективно из-за количества вычислений. Возможно, не будет никаких задержек, если у вас достаточно серверов, но я уверен, что они будут)

Это то, что делают большинство профессиональных программистов? (если нет, что есть).

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

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

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

Как насчет раздела истории, если пользователи хотят просмотреть историю кредитов, мы должны ОГРАНИЧИТЬ его, как LIMIT 100 записей, когда * SELECT * ing или нет (для производительности)

Конечно, вы должны. В большинстве случаев пользователи не могут видеть все 100 (200, 300) позиций одновременно. Также они будут искать ВСЕ записи (как я понимаю, они будут иметь много записей в этом разделе) не каждый раз. Даже если пользователь увидит все записи, это все равно займет несколько секунд или минут. Использование ограничений для одного запроса будет распределять нагрузку с течением времени и уменьшать пики нагрузки. Это увеличит среднюю производительность для пользователей.

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

Должен ли он запускаться на каждом обновлении страницы или на каждой странице? (если 1000 пользователей подключены к сети и этот запрос SELECT применяется при каждом обновлении, это не замедляет работу сервера)

Любая активность пользователей замедлит ваш сервер, это невозможно исправить:) Но здесь мы говорим об эффективности использования разных методов, для получения необходимой функциональности. Что касается меня, я не знаю, что "если 1000 пользователей подключены к сети и этот запрос SELECT применяется для каждого обновления". Это форум, где вы можете увидеть много записей пользователей с репутацией? Или, может быть, это страница профиля только с одной репутацией? Или, может быть, вы хотите увидеть репутацию 1000 пользователей онлайн, без офлайн?

Если мы сделаем это именно тогда, когда пользователь получил некоторые кредиты, не возможно, что пользователь получил два разных кредита в одно и то же время (это возможно), и поскольку мы не можем поместить таблицу Auto Increment in Totals (потому что у каждого пользователя только 1 запись), мы можем пропустить 1 кредит, или если есть решение этой проблемы, я не знаю об этом

Вы не должны заботиться о целостности транзакций, потому что это проблема СУБД. Вы должны вносить изменения в поле "репутация" каждый раз, когда изменилась репутация. Я имею в виду - просто сделайте запрос SQL.

Если мы установим Cron-Job, чтобы делать это часто, то кредиты пользователя не обновляются до тех пор, пока задание cron не обновит таблицу итогов

Не используйте cron. Или используйте только для актуализации данных, если хотите.