In my example I have two tables that relate to each other by the primary key of the first (master) table. The second table can have multiple rows relating back to the first (master) table. In my real world example, I have tens of thousands of rows that are being selected, updated, inserted and deleted by thousands of clients simultaneously. The issue I am experience is locking because of the concurrent updates and selects.
在我的示例中，我有两个表，它们通过第一个（主）表的主键相互关联。第二个表可以有多行与第一个（主）表相关。在我的真实世界的例子中，我有成千上万的行被数千个客户端同时选择，更新，插入和删除。由于并发更新和选择，我遇到的问题是锁定。
Will making the non unique id column relating back to the primary table a clustered index help my situation at all?
将非唯一id列与主表相关联的聚簇索引是否有助于我的情况？
Table structure:
表结构：
wks_master: wks_master_id [primary key], other_columns....
wks_std_apps: wks_std_apps_id [primary key], wks_master_id, other_columns....
Queries typically looks like:
查询通常如下所示：
select *
from wks_std_apps
where wks_master_id = @wks_master_id
update wks_std_apps
set blah...
where wks_master_id = @wks_master_id
1 个解决方案
#1
1
Ideally the second table would have a unique primary key that would server as the clustered index (this can be a combination of columns). If the value is not unique, I would use a non-clustered index which should help.
理想情况下，第二个表将具有唯一的主键，该主键将作为聚簇索引服务（这可以是列的组合）。如果值不是唯一的，我会使用非聚集索引，这应该有帮助。
#1
1
Ideally the second table would have a unique primary key that would server as the clustered index (this can be a combination of columns). If the value is not unique, I would use a non-clustered index which should help.
理想情况下，第二个表将具有唯一的主键，该主键将作为聚簇索引服务（这可以是列的组合）。如果值不是唯一的，我会使用非聚集索引，这应该有帮助。