System Architecture Design-database master Selection

In the software design phase, one of the issues to be considered is the database master and no need for Master and health. This article focuses on the master and Health design selection.

1. identity as the master key.

Storage size: basically, the data type is int and needs to occupy 4 bytes. Considering the primary key index, each primary key occupies 8 bytes of storage space.
Advantages:
1. It is automatically generated by the database and does not need to be maintained manually.
2. The storage space is small. If the database is properly designed, more rows of records can be stored on each page to reduce Io read/write.
3. the system automatically handles issues that affect performance, such as concurrency and transactions, without consideration.
Disadvantages:
1. The font length is only 32 characters. In the case of massive data storage on the web, 32-bit IDs are theoretically insufficient.
2. In the case of a database cluster or during data migration or merging, multiple records may have duplicate master keys, causing serious consequences!
3. If the data operation is a master-slave operation and there is a reference relationship between them, the operation must be performed n times before the operation can be saved successfully (n> 1, regardless of the stored procedure)
4. because the data is automatically generated, there will be a waste of IDs. For example, if one million records are introduced in batch, the ID will increase by one million. When the transaction fails to roll back, this ID operation will not roll back, the current ID will be changed to ID + 1 W

2. guid
There are two types of guids: Database generation and program generation. The GUID occupies 16 bytes, and the index occupies 32 bytes of space.
Advantages:
1. There are two generation methods. Data is not duplicated and manual maintenance is not required.
2. Duplicate primary key IDs are not required during data merging and transplantation.
3. In the master-slave operating program, the program can generate the guid at one time and submit the program performance.
Disadvantages:
1. The GUID occupies 16 bytes, which seriously wastes database storage space considering indexes and other related conditions.
2. the query performance is relatively slow. You need to re-sort the indexes each time you save them, and the index performance also decreases.
3. When using RAID storage, there is another difference in performance. The read/write head and region of the guid solution are close to random allocation, while the identity solution is concentrated in one block.

3. Database maxid Algorithm
The storage byte can be 4-8 bytes, defined by yourself, and free. Each business object (table) can have a last maxid. This algorithm can be considered as an identity variant.
Advantages:
1. manually control ID generation, storage space, and index size.
2. data merging ID duplication can be considered in advance. For example, the highest bit of a database is distinguished as an object. For example, the highest bit of database 1 is 1, the highest bit of database 2 is 2, and so on. data merging does not require data duplication.
Disadvantages:
1. Whether it is Master/Slave table storage or single table Storage, you must first obtain the ID of the data, with poor performance.
2. When obtaining the maxid, if the unassociated and saved operations are placed in a transaction, if the update overwrites, for example, a generates 100, B also generates 100, resulting in potential data errors.
3. It is difficult to handle a transaction and easy to deadlock. Typically, the maximum value of maxid is checked first, and then updated to maxid + 1. In concurrency, two threads Add a shared lock to the maxid, and the update lock fails, resulting in a deadlock.

4. program id generator Algorithm

The storage byte can be 4-16 bytes, defined by yourself, and free. Multiple Algorithms

Advantages:
3. You can design a more efficient ID generation algorithm.
Disadvantages:
1. If you store the ID calculation result in the memory or file, you need to consider the program reliability.
2. In the process of program generation, you must consider concurrency issues, locks, and other related processing methods.
3. the maximum value storage problem occurs. If it is stored in the database, the above three problems will occur. If it is stored in the memory or file, how can we restart the maximum size after the algorithm generator fails. considering performance and other related problems, you can generate 1000 IDs every other time to update the database, and a generator can process the database in a unified manner.