当前位置：文江博客话题详情

在多用户 saas 应用程序中生成序列号

发布于 2024-07-26 18:28:14 字数 168 浏览 7 评论 0原文

人们如何在典型的 saas 应用程序中为特定用户生成自动递增整数？

例如，特定用户的所有发票的发票编号应自动递增并从 1 开始。在这种情况下不能使用 Rails id 字段，因为它在所有用户之间共享。

我可以随意计算一个用户拥有的所有发票，然后加 1，但是有人知道更好的解决方案吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

别想她 2024-08-02 18:28:14

任何关系数据库的典型解决方案可能是一个表

user_invoice_numbers (user_id int primary key clustered, last_id int)

和一个存储过程或一个 SQL 查询，

update user_invoice_numbers set last_id = last_id + 1 where user_id = @user_id
select last_id from user_invoice_numbers where user_id = @user_id

它适用于用户（如果每个用户有几个同时运行的事务），但不适用于公司（例如，当您需要 Companies_invoice_numbers 时）因为同一公司内部不同用户的交易可能会互相阻塞，这个表就会出现性能瓶颈。

您应该检查的最重要的功能要求是您的系统是否允许发票编号存在间隙。当您使用标准 auto_increment 时，您允许间隙，因为在我知道的大多数数据库中，当您回滚事务时，增量的数字将不会回滚。考虑到这一点，您可以使用以下准则之一来提高性能

1) 排除用于从长期运行的事务中获取新号码的过程。 让我们假设插入发票< /strong> 过程是一个长时间运行的事务，具有复杂的服务器端逻辑。在这种情况下，您首先获取一个新的 id ，然后在单独的事务中插入新发票。如果回滚最后一笔交易，自动编号不会减少。但 user_invoice_numbers 不会被长时间锁定，因此很多并发用户可以同时插入发票

2) 不要使用传统的事务数据库来存储每个用户最后一个 id 的数据。当您需要维护简单的键和值列表时，有很多小型但快速的数据库引擎可以为您完成这项工作。键/值数据库列表< /a>. 可能 memcached 是最受欢迎的。过去，我看到过一些项目使用 Windows 注册表甚至文件系统来实现简单的键/值存储。有一个目录，其中每个文件名都是密钥，每个文件内都有最后一个 id。这个粗略的解决方案仍然比使用 SQL 表更好，因为锁的发出和释放非常快，并且不涉及事务范围。

好吧，如果我的优化建议对于您的项目来说似乎过于复杂，那么现在就忘记这一点，直到您真正遇到性能问题。在大多数项目中，使用附加表的简单方法将工作得相当快。

Typical solution for any relation database could be a table like

user_invoice_numbers (user_id int primary key clustered, last_id int)

and a stored procedure or a SQL query like

update user_invoice_numbers set last_id = last_id + 1 where user_id = @user_id
select last_id from user_invoice_numbers where user_id = @user_id

It will work for users (if each user has a few simultaneously running transactions) but will not work for companies (for example when you need companies_invoice_numbers) because transactions from different users inside the same company may block each other and there will be a performance bottleneck in this table.

The most important functional requirement you should check is whether your system is allowed to have gaps in invoice numbering or not. When you use standard auto_increment, you allow gaps, because in most database I know, when you rollback transaction, the incremented number will not be rolled back. Having this in mind, you can improve performance using one of the following guidelines

1) Exclude the procedure that you use for getting new numbers from the long running transactions. Let's suppose that insert into invoice procedure is a long running transaction with complex server-side logic. In this case you first acquire a new id , and then, in separate transaction insert new invoice. If last transaction will be rolled back, auto-number will not decrease. But user_invoice_numbers will not be locked for long time, so a lot of simultaneous users could insert invoices at the same time

2) Do not use a traditional transactional database to store the data with last id for each user. When you need to maintain simple list of keys and values there are lot of small but fast database engines that can do that work for you. List of Key/Value databases. Probably memcached is the most popular. In the past, I saw the projects where simple key/value storages where implemented using Windows Registry or even a file system. There was a directory where each file name was the key and inside each file was the last id. And this rough solution was still better then using SQL table, because locks were issued and released very quickly and were not involved into transaction scope.

Well, if my proposal for the optimization seems to be overcomplicated for your project, forget about this now, until you will actually run into performance issues. In most projects simple method with an additional table will work pretty fast.

回复收藏 0 原文