将大型数据库表折叠到每个用户一行 [英] Collapse large database table to one row per user

问题描述

我正在运行一个赌场（而不是真正的货币）网站，每次用户玩一排是在我的数据库上的表trans，使这个数据库增长太多，这是崩溃我的服务器

I am running a casino (not real money) website that everytime a user plays a row is generated on a table "trans" on my database that make this database grow soo much that is crashing my server

这是表结构

    Name        Type            Collation   Attributes          Null    Default     Extra       Action
 1  user_id     int(11)         No          None                Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 2  amount      decimal(16,8)   No          None                Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 3  aff         int(11)         No          0                   Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 4  jackpot     int(11)         No          0                   Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 5  paidout     int(11)         No          0                   Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 6  type        int(11)         No          0                   Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 7  created     timestamp       No          CURRENT_TIMESTAMP   Change  Change      Drop Drop    Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More

这个表实际上有400万行，只有30000个用户b $ b我的问题是，有没有办法折叠表let let每天，每个用户只保存一行，并保持他们的余额（金额）。

This table has abount 4 million rows actually and only 30000 users (user_id ) My question is , is there a way to to collapse the table let say daily and save only one row per user and keep their balances (amount).

我我尝试不成功：

更新转换

SELECT * FROM code> group by user_id
set amount =（select sum（amount）from trans

SELECT * FROM trans group by user_id set amount = (select sum(amount) from trans

推荐答案

一些宽泛的步骤：

1. 创建新的摘要表。


2. 在原始表格上添加触发器


3. 按照时间表清除所有旧条目

或

1. 更改应用程式逻辑以执行更新而不是插入

或

您可以编写一个GROUP BY样式查询来将所有用户信息返回而不是所有的细节 - 可能你正在做的是select *这是使用了所有的内存..坏

you can write one single GROUP BY style query to add up all the users info and return that instead of all the detail - probably what you are doing is select * which is using up all your memory.. bad

这篇关于将大型数据库表折叠到每个用户一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！