Schema Design, mongodbschema Design
Schema Design
Schema: The table Schema. design the table, index, and relational model diagram of the table and table:
Schema is related to application functions and performance.
- Meet service functional requirements
- Closely related to performance
- Database scalability
- Meet peripheral requirements (statistics, migration, etc)
Schema modification in relational databases is often a high-risk operation. Schema design must be proactive and fully developer-led.
- Focus on current functions
- Completely functional-based design may have some potential risks
-
- Unreasonable table structure or index design causes performance problems
- It is not reasonably estimated that the increase in data volume causes space shortage and is difficult to maintain.
- Frequently-modified table Structures
- Major business adjustments lead to frequent data restructuring and correction
Performance-based Table Design
- Design indexes based on query requirements
- Adjust the table structure according to the core query requirements
- Adjust implementation methods based on some special business needs
Index
- Correct Indexing
- Update the primary key or unique index whenever possible.
- Use the auto-incrementing ID field as much as possible for the primary key
- Core query overwrite Index
-
- The User Login needs to return the password for verification based on the user name
- Create index idx_uname_passwd on tb_user (username, passwd );
- Create a joint index to avoid returning data to the table
Design Example
1 anti-paradigm, redundant required field 2 split large field
3. Avoid too many fields or too long lines for 4-page queries: 5. Special processing of hotspot read data 6. Special processing of hotspot write data
7. Quasi-real-time statistics
Real-time statistics improvement 1 -- trigger real-time statistics
Real-time statistics improvement 2-Cache real-time statistics
Real-time statistics improvement 3-maximum auto-increment ID acquisition total
8. scalability Design
9 partition tables and data elimination range partitions
List Partition
Hash partition 10 Meets peripheral demand statistics and background requirements 11 automatic update of Timestamp Schema Design and foresight
- Prevents and solves similar problems based on past experiences and lessons
- Record and analyze and summarize the reasons for modifying the index Schema that is difficult to use by DBAs.
Example: The business has made major changes to encrypt user information
- Database results are greatly changed, encryption fields, validation policy tables, and data correction for all tables are added.
- Are all applications that use user information management going online using ciphertext?
Summary
- Schema Design relationship Performance
- Anti-paradigm, redundant required fields
- Split large fields
- Avoid too many or too long Fields
- Paging Query
- Special processing of hotspot read data: Separate top tables from normal tables
- Special processing of hotspot write data:
-
- When a Weibo user sends a message, the message list of the person who follows the message is written. When a Weibo user sends a message, the person who follows the message reads the message list;
- Quasi-real-time statistics:
-
- Regular statistical table. The incremental sum value of the entire table is calculated based on the last update time. The statistical table is updated every minute;
- Real-time statistics:
-
- Trigger real-time statistics. When a user inserts a trigger, the statistical table is updated;
- Real-time cache statistics: The application adds new users to the memory cache. The business is read from the cache at ordinary times, the cache is invalid, a query is made from the database, and then written in the cache;
- Partition tables and data elimination
- Meet peripheral needs:
-
- For example, if a special index is added to the background statistics task,
- Add timestamp for data migration or statistics
- Automatic timestamp update
- Schema Design and foresight
