我有一个MySQL数据库(InnoDB),我正在开发用于跟踪我的组织的工作订单.有多个“站点”,每个站点的工单号从100开始.

WorkorderID    SiteID    SiteWorkorderNum
     1           1            100
     2           1            101
     3           2            100

WorkorderID是自动增量字段.
SiteID和SiteWorkorderNum都设置为唯一约束.

每当要为特定站点插入新的工作单时,应用程序将检查登录的siteid的max(SiteWorkorderNum)并将该值返回到insert语句.我想避免的问题是,同一站点的两个用户是否在同一时间输入新的工单.

我有几个可能的解决方案,但我想看看是否有更好的方法,因为每个都有它的缺点,但我肯定会比另一个更好,当然我对新想法持开放态度.

1)锁定表以保证在我们检索并插入我们的值之前没有其他用户检索SiteWorkorderNum值(但是假设我们有数千个用户试图每分钟输入工作订单,表格锁定会不会减慢速度？ )

2)不要锁定表,并检索下一个可用的SiteWorkorderNum并尝试插入数据库,如果我收到唯一约束错误,请捕获错误并再试一次,直到我成功. (这可能会让表格*释放,但是再次假装我们在同一个站点有数千个用户,多个数据库调用会不会有点低效？)

对于这些解决方案中的任何一种“如何”影响性能,我是否过于偏执？当然我没有成千上万的用户,但我想在这个设计中应用最佳实践,以防我曾经在一个流量很高的项目上工作.

解决方法:

使用MyISAM存储可以解决这种情况的有趣之处.

我在2012年4月回答了这样的问题：How can you have two auto-incremental columns in one table?您需要创建一个表,其唯一目的是为每个站点创建工作单序列

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;

以下是加载到此表中的示例：

mysql>     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql>

让我们看看每个站点的最后一个WorkorderNum

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql>

现在,假设您想要获取SiteID 3的下一个SiteWorkorderNum.您可以这样做：

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;

让我们运行它,看看会发生什么

mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql>

只要您使用MyISAM除了所有InnoDB表之外,您就可以根据自己的内容为每个站点生成workordernums.