Database design optimization (from a ten-year experience architect's Class) __ database

Yesterday listened to a public class, the class is a 10 years experience in the development of the architect, talking about the design of the database optimization and SQL statement optimization, speak very easy to understand, here first the database design optimization to share to everyone.

1. Moderate violations of the paradigm.

If you strictly follow the three paradigms, you will often need to connect many tables in the query, query efficiency is reduced, can combine business requirements, appropriate redundancy.

2. Establish a proper index.

Indexing speeds up query efficiency in some fields, but the update operation slows down and consumes storage space.

Rules established by indexing:

A. Indexed fields must be fields that are often used to make query criteria

B. If the index is a combination of multiple fields, use the field that is often used as the first query criteria. Otherwise, when you query through a single field, the database does not pass through the index query.

C. Indexed fields must be sufficiently differentiated to be indexed with a frequently identical field, with low efficiency.

3. Horizontal Division of the table

If a large amount of data is generated each year, multiple tables can be established by year.

4. Vertical partitioning of tables

If some of the fields in the table are very long, occupy a large space, when the table to retrieve a large number of i/0 operations, you can separate the field out of a table and the original table to establish 1 to 1 relationship.

5. Select the appropriate field type, such as using varchar instead of char as much as possible.

6. File pictures and other large files using hard disk storage, the database only store the file path.

7. Foreign key to express clearly, the actual work is rarely established foreign keys.

8. Centralized bulk operation is better than frequent read and write operation.

9. Choose the right engine.