In the design of the database, we often use guid or int as the main key, and according to the knowledge we have learned, we have always felt that int is more efficient as the main key, but it is impossible without careful testing
Explain the reason. It happened that during the optimization of the database today, I encountered this problem, so I did a test.
Test Environment:
Desktop PC Pentiun(R) 4 CPU 3.06GHz
Win XP professional
1.5G DDR RAM
SQL Server 2005 Personal
Testing Process:
First, create a test database, Test
The test results are as follows:
As mentioned above, the efficiency of using int as the primary key is improved compared with using guid as the main key, especially when there is a connection query and deleting records.
Moreover, in the data query with the main key in GUID today, the query timeout repeatedly occurred because of the nesting of several sub-query results. Therefore, I am in favor of using int as the main key, and I do not agree with guid as the main key.
The above views represent personal opinions, and everyone is welcome to express their opinions and explain the advantages and disadvantages of guid and int as the main key.
Follow-up tests:
After being reminded by the brothers, a non-clustered index has been added to two subtables today:
CREATE NONCLUSTERED INDEX Index_Detail_Guid on Test_Guid_Detail(Guid)
CREATE NONCLUSTERED INDEX Index_Detail_id on Test_Int_Detail(id)
Then I conducted an internal connection query and found that as @Xu Shaoxia said, the efficiency is indeed not obvious enough to indicate more than 50%, and basically only about 23% improvement, which is still acceptable.
Therefore, it is recommended
1. In systems that often need to do data migration, it is recommended to use Guid. And adding non-clustered indexes to the corresponding foreign key fields, that is, fields used for join queries, is of great benefit to improve performance. The field of the where condition can also be added as appropriate for non-clustered indexes.
2. When using the Guid type as the primary key, the data type should be uniqueidentifier, and be sure to remember to cancel the "aggregate index" of the primary key
3. For systems that do not need to be migrated, or small systems, it is still very convenient to use int as the primary key, and there is still a certain improvement in efficiency.
|
Posted on 10/9/2019 4:48:35 PM
|
|
|
|
Landlord|
Posted on 2/8/2021 11:03:12 AM
|
Posted on 3/20/2025 4:13:51 PM