存储已删除用户数据的最佳方式？
我正在开发一个跟踪和处理工单/票据的应用程序。每张票证都通过外键链接到创建/拥有该票证的用户，该外键级联 MySQL 中的任何更改。显然，如果用户出于某种原因删除了他们的帐户，我们仍然希望保留他们的门票和基本信息的记录。
我想到的第一个实现此目的的方法是在用户表中有一列，表示它们是活动的还是非活动的，即是否已删除。这样，当他们关闭/删除他们的帐户时，他们只需翻转这个值，然后就无法访问该应用程序。
我的另一个想法是在删除帐户时将用户记录移动到已删除的用户表中。这种方式将使用户表的性能保持在最佳状态，当它变大时这可能是一个大问题，但会增加额外的查询来移动记录。
显然，其中一部分可能是偏好，但我对性能方面感兴趣。本质上，问题是选择查询与插入查询相比如何，以及在什么情况下通过添加插入查询（将记录移动到已删除的用户表）来提高整体性能？
I'm working on an application that tracks and handles work orders/tickets. Each ticket is linked to the user who creates/owns the ticket via a foreign key that cascades any changes in MySQL. Obviously if a user were ever to delete their account for some reason, we would still want to keep a record of their tickets and their basic information.
The first way to accomplish this that came to mind is to have a column in the users table that denotes whether they're active or inactive, ie deleted or not. That way when they close/delete their account, their just flipping this value and are then unable to access the app.
The other idea I had was to move the user record to a deleted users table when the account is deleted. This way would keep the performance of the users table at it's best which could be a huge deal when it grows large, but adds additional queries to move the record.
Obviously part of this can be preference, but I'm interested in the performance aspects. Essentially the question is how does a select query compare to an insert query and at what point would overall performance increase by adding the insert queries (moving records to the deleted users table) to the mix?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好的。
。您现在有两个联接：用户到票证和前用户到票证。这是不必要的复杂性。
如果“大”指的是数百万用户，那么你是对的。
但是，如果“大”指的是数千个用户，那么您将无法衡量太大的差异。
和。如果将来确实有明显的放缓，您可以使用“物化视图”之类的东西来自动创建“活动”用户的子集视图/表。
事实并非如此。停用（但不是删除）用户有很多优点，但没有真正的缺点。
有很多级别的活动 - 安全锁定（但未禁用） - 暂时禁用 - 委托给其他用户。很多很多的状态变化。删除的原因很少。没有理由“移到另一张桌子”。
只有您可以衡量您的表、索引、服务器和事务组合的这一点。没有通用的答案。
Good.
Bad. You now have two joins: user to ticket and former user to ticket. This is a needless complexity.
If, by "large", you mean millions of users, then you're right.
If, however, by "large", you means thousands of users, you won't be able to measure much difference.
And. If you really do have a measurable slowdown in the future, you can use things like "materialized views" to automatically create a subset view/table of "active" users.
Not really. Deactivating (but not deleting) users has numerous advantages and no real disadvantages.
There are lots of levels of activity -- security lockout (but not disabled) -- temporarily disabled -- delegated to another users. Lots and lots of status changes. Few reasons for a delete. A no reasons for a "move to another table".
Only you can measure this for your tables, your indexes, your server and your mix of transactions. There's no generic answer.
在我看来，将用户标记为已删除或未删除是更好的方法。第二种方法，使用新表，将导致引用用户表的每个表发生更改。您应该有“已删除用户表”的新外键。这将更改从此表中选择行的所有查询。
正如您所写，该应用程序是关于票证的，从逻辑上讲，大多数查询都是关于选择和编辑票证的。因此，影响将在此表上，我认为您不会对用户进行大量查询。
对“用户”表进行优化并对“票证”表进行更复杂的查询不会有回报。
In my opinion, marking the user as deleted or not is better approach. The second way, with new table, will cause changes in every table where you reference users table. You should have new foreign key to the "deleted user table". This will change all queries for selection rows from this tables.
As you wrote, the app is about tickets, logically most of the queries will be about selecting and editing tickets. So the impact will be on this table, I do not think that you make big queries about the users.
Optimization on "user" table and making more complex queries for "ticket" table won't payoff.
我在开发的应用程序中遇到了类似的问题。该问题涉及系统其他元素的日志和用户信息。
我通过在日志表中添加一列来解决这个问题，其中包含一个存储为字符串的 JSON 对象，其中包含日志记录所需的信息。如果您想从表中物理删除用户，并且还需要有关已删除用户的信息，那么这种方法很好。
I had a similar problem in an application I was developing. The problem involved logs and user information from other elements of the system in it.
I solved the problem in such a way that I added a column to the log table, which contains a JSON object stored as a string with the necessary information for logging. Such a way is good if you want to physically delete users from the table, and further need information about deleted users.