MySQL performance optimization, optimization design and design principles interpretation

The purpose of MySQL performance optimization

How to design the database rationally?

What database design can provide the cornerstone for post-DBA optimization?

The difference between database design and program design?

Early optimization of database design

1. Clear relationship (clarify the relationship between tables, you can improve efficiency through redundancy)

2. Space saving (based on business experience, set field length)

3. Increase efficiency

database table Development Process

Prototypes and improvements (and so is the design of the table)

Type of database

1. Hierarchical database (registry) such as: The core of the Windows operating system is a registry, because of the number of configuration items, the use of hierarchical relational data storage

2. relational databases such as: MySQL

3. Time Series Database

4. Graph database such as: Shortest path, geographic information

5. Key-value databases such as: Redis

6. Object Database

7. BigTable Database

The difference between a file system and a database system

(1) The file system is used for long-term preservation of data on external memory, and the database system uses database to store data uniformly;

(2) The program and data in the file system have certain connection, the program and data in the database system are separated;

(3) The file system uses the access method of the operating system to manage the data, and the database system uses DBMS to manage and control the data uniformly;

(4) The file system realizes the data sharing in the file unit, and the database system realizes the data sharing in the record and the field.

The first step in optimization design

To save space in a table design, you must be proficient in the characteristics of various data types (what business can be used), length, and so on.

The int type only increases the primary key field =>4 bytes + = 8 bits per byte =>32 bit, when the CPU loads an instruction, 4 bytes is related to the operation of the CPU register, such as: 64 bit, because the direct system is generally 32 bits, so the data in the Operation 4 bytes is exactly, The most efficient, and now our system is basically 64 bits, in fact, there is no better use of CPU operation, so in the design table field suggested that the use of 8-byte primary key bigint, instead of using int directly to the master key.

UUID key, character type of the master key, in the CPU load is required to consume more computing process

char (10) storage space of 10 characters regardless of whether the field stores data

CHAR (10) There is also a pit, that is, after storing the ABC data, the value of the database field is "ABC 7 Space", in the precise query (where) must take the following 7 spaces

When a varchar does not occupy space, how much space does the data occupy?

Second step in optimization design

How to reasonably design a database table conforming to the three paradigms?

1NF: column is not divided. Each column is an inseparable basic data item, such as this design is unreasonable, name (Harry, Wangwu)

On the basis of 2NF:1NF, the non-primary attribute is entirely dependent on the primary key, such as student names (non-primary attributes) that are dependent on the study number (main attribute).

3NF: The property does not depend on other non-main properties, to eliminate the transfer of dependency, such as the design is unreasonable, student number key, students course Table (Study number = course), when the number of changes, the corresponding curriculum needs to be modified, which is the transfer of dependency

BCNF: 3NF compliant, only one candidate key per table

4NF: No multi-valued dependency

Because the school number can not be the key, then what is the key? First, there is the rule: do not use business rules to master the key, the primary key should be unrelated to the business.

such as the frequently used order_no (business order number), even if the only, and do not recommend the key, it is easy to generate the problem of transmission dependence, so that it does not conform to the third paradigm.

The third step of optimization design

Database optimization Strategy

1. Select a small data type

2. Design the primary key separately and consider the distributed extension

3, FOREIGN key design

(Important, we previously developed are directly using the weak foreign key to set the primary foreign key relationship, and the actual project, if you delete the primary key corresponding record, the foreign key table record is not deleted, so that the database data is very easy to confuse, inconvenience to maintain, then I use a strong foreign key way, So directly delete the primary key record, not delete the record in the Foreign key table, this is to error, so easy to find the code problem, foreign key design can have a good constraint on data integrity, when you develop the system has not been completely incomplete data problems, you can consider using weak foreign key to correlate table operations, Also eliminates the foreign key consumption, the specific setting foreign key method to examine the blog: foreign key and its constraint understanding)

4. Index Design

(For fields on business, which need fields need to be indexed?) ）

5, association relationship table design, many to one, many to many

6. Read and write frequently, separate from infrequent information

(such as in the design of the payment system, there will be both the order table and the Order record table, the order table read and write frequently, and the order record table for the management, read and write general)

7, Configuration table, log table, scheduled task table, etc.

8. Summary table Design

(Multi-Table association queries will be slow, but also easy to get stuck in the case, you can consider a summary of the business, record to the summary table)

Fourth step in optimization design

After the precipitation of the business, accumulate some design ideas or extract the common points of the multi-project, reduce the development cost

1. General-Purpose design

Example: Personnel, Department, role

2. Special design

Accessories, logs, configuration, monitoring, etc.

3. Storage Design

Type partitioning facilitates partitioning

4. Some additional fields

Create date, modify date, sort

5. Water meter

Similar to logs, but comprised of business processing results, account changes or intermediate values for business processing

The following principles should be implemented when designing a database

(a) Reduce the reliance on database functions (such as the use of MySQL features in the business, and this feature is only MySQL exists, the future of the database migration will be a great inconvenience)

(ii) Principles for defining entity relationships

The entity involved identifies all the entities involved in the relationship.

Ownership takes into account the situation in which an entity "owns" another entity.

Cardinality considers the number of instances of an entity associated with another entity instance.

(c) The column means the unique value

If you are representing coordinates (0,0), you should use two columns instead of "0,0" in 1 columns.

(iv) Sequence of columns, readability issues

(v) Defining primary and foreign keys

The data table must define the primary key and foreign key (if there is a foreign key).

(vi) SELECT key

(vii) whether NULL is allowed

Any value and null stitching are null after the concatenation.

All mathematical operations that are performed with NULL return NULL.

When NULL is introduced, logic is not easy to handle.

(eight) Normalization-paradigm

1NF

String data that contains the delimiter class character.

The tail end of the name has a number attribute.

There are no tables that define bad keys or key definitions.

2NF

Multiple attributes have the same prefix.

The repeating data group.

Aggregated data, the referenced data is in a completely different entity.

bcnf-"Each key must uniquely identify the entity, and each non-key familiarity must describe the entity. ”

4NF

Ternary relationship (Entity: Entity: entity).

A latent multi-valued attribute. (such as multiple phone numbers.) ）

Temporary data or historical values. (The subject of historical data needs to be raised, otherwise there will be a lot of redundancy.) ）

(ix) Select a data type

(10) Optimizing parallel

When designing a db, you should consider optimizations for parallelism, such as the timestamp type.

Naming rules table name rules

1. Use a prefix, but do not use a meaningless prefix

2, the underscore separates

3, all lowercase

Column name rule

1, generally do not use the prefix (when and keyword conflict can consider adding a prefix difference)

2, the underscore separates

3, all lowercase

Whether it is the table name design or the column name design, do not use Pinyin to name, after a period of time on completely do not remember, in English, even if the English is not good design when it is recommended to set up in English.

MySQL performance optimization, optimization design and design principles interpretation