我正在研究跟踪和处理工单/门票的应用程序。 每个故障单都链接到通过外键创建/拥有故障单的用户，该外键可以对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?
最满意答案
在users表中有一个列，表示它们是处于活动状态还是非活动状态，即是否已删除。
好。
我的另一个想法是将用户记录移到已删除的用户表中
坏。 您现在有两个连接：用户到票证和前用户到票证。 这是一种不必要的复杂性。
当它变大时可能是一个巨大的交易，
如果“大”，你的意思是数百万用户，那么你是对的。 但是，如果“大”，则意味着成千上万的用户，您将无法衡量太多差异。
和。 如果您未来确实会有明显的减速，您可以使用诸如“物化视图”之类的内容来自动创建“活动”用户的子集视图/表格。
很明显，这可能是偏好的一部分，
不是真的。 取消激活（但不删除）用户具有许多优点并且没有真正的缺点。
有很多级别的活动 - 安全锁定（但未禁用） - 暂时禁用 - 委托给其他用户。 很多很多的状态变化。 删除的理由很少。 没有理由“转移到另一张桌子”。
如何通过将插入查询（将记录移动到已删除的用户表）添加到混合中，将选择查询与插入查询进行比较，以及在什么时候整体性能会提高？
只有你可以测量你的表格，索引，服务器和事务组合。 没有通用的答案。
have a column in the users table that denotes whether they're active or inactive, ie deleted or not.
Good.
The other idea I had was to move the user record to a deleted users table
Bad. You now have two joins: user to ticket and former user to ticket. This is a needless complexity.
could be a huge deal when it grows large,
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.
Obviously part of this can be preference,
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".
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?
Only you can measure this for your tables, your indexes, your server and your mix of transactions. There's no generic answer.
更多推荐
发布评论