Structure Design of database optimization

Design Benefits

• Good database logic design and physical designer database for high Performance Foundation
• Paradigm-and inverse-paradigm design (reduce redundancy, reduce anomalies, and make data organization more harmonious)
• optimization purposes
  
  • Reduce data redundancy (as much as possible)
  • Try to avoid exceptions such as updates, insertions, and deletions in data maintenance
    
    • Insert: If an entity in the table exists with another entity
    • Update: If you change the individual properties of an entity in a table, you need to update multiple tables
    • Delete: Deleting a moment in a table causes other entities to disappear

Design process

• Demand analysis
  • Comprehensive understanding of product design Requirements
  • Storage requirements (e.g. one-to-many, many-to-one)
  • Data processing requirements
  • Security and integrity of data
• Logical Analysis
  • Logical structure of the design data
  • Logical relationships before data entities to resolve data redundancy and maintenance exceptions
• Physical design
  • Design table structure based on the data features used
• Maintenance optimization
  • Optimize for indexes, storage structures, and more
• Normalization of
  • Design data structures without redundancy and maintenance exceptions
• Inverse normalization
  • In terms of normalization, the paradigm of database design is introduced in the previous, so-called anti-normalization is for the purpose of performance and reading efficiency and appropriate to the requirements of the database design paradigm, and allow the existence of a small amount of data redundancy, in other words, inverse normalization is the use of space to exchange time

This article simply explains the advantages and disadvantages of physical design, normalization and inverse normalization; others will be explained in the article "Database Optimization" series one by one;

Normalization and inverse normalization

• Advantages and disadvantages of normalized design
  • Advantages
    
    • Minimizing data redundancy
    • Normalized update operations are faster than inverse normalization
    • Normalized tables are usually smaller than inverse paradigms
  • Disadvantages
    
    • Multiple tables need to be associated with a query (MySQL limit cannot exceed 10 tables)
    • More difficult to index optimization

• Advantages and disadvantages of anti-normalization design
  • Advantages
    
    • Reduce the association of tables
    • Better index optimization
  • Disadvantages
    
    • Data redundancy and database maintenance exceptions exist
    • Requires more cost for data modification

Three paradigms of design normalization requirements:
First Paradigm
* All fields in a database table have only a single attribute
* Columns of a single attribute are composed of basic data types
* Design and out of the table are simple two-dimensional table
Second Paradigm
* Requires only one business primary key in a table, that is, a table that conforms to the second normal form cannot have a dependency on a partial primary key for a non-primary key column
Third Paradigm
* Data cannot have a transitive relationship, that is, each attribute is directly related to the primary key rather than the indirect relationship

Physical design

• The content of physical design
  
  • Define naming conventions for databases, tables, and fields
  • Choosing the right Storage engine
  • Select the appropriate data type for the fields in the table
  • Building the database structure

• Define naming conventions for databases, tables, and fields
  
  • Readability principle
  • Ideographic Principles
  • Long name principle

• Choosing the right Storage engine

　　

• Select the appropriate data type for the fields in the table (data page)
  • When a column can select more than one data type, it should take precedence over the number type, followed by the date or binary type, and finally the character type. For data types of the same level, it is preferable to select a data type that takes up less space

• How to select a primary key for InnoDB
  
  • The primary key should be as small as possible
  • The primary key should be sequential growth
  • InnoDB primary key and business primary key can be different;

RELATED LINKS

Examples and stories of database optimization, what impact performance of database optimization

Non-moving Peaks
Blog Park: http://www.cnblogs.com/mylly/
All rights reserved, welcome to keep the original link to reprint:)

Structure Design of database optimization