Optimization on 1. Sql.
( 1 ) increase filter conditions as much as possible in child queries
create table person1( id int, name varchar(50), age int ); create table person2( id int, name varchar(50), age int ); 不推荐: SELECT * FROM person1 WHERE id in( SELECT id FROM person2 ) AND age>?; 推荐: SELECT * FROM person1 WHERE id in( SELECT id FROM person2 WHERE age>? );
( 2 ) use explain to view details of sql.
Like explain select * from person1 g
G indicates the vertical version.
Some detailed parameters are displayed, and then further added.
( 3 ) when the amount of data is large, you need to avoid scanning too much records, and there are several points to note:
A. Creating appropriate indexes such as fields appearing on order by, where, group by, on clauses, and so on.
B. The smaller the index field.
C. Columns with large discretization are placed before the joint index. A large number of discrete points refers to the diversity. The joint index is similar ( id1, id2 ).
D. Avoid repeating indexes and redundant indexes.
2. Optimization of database table structure.
( 1 ) int handles a simple MySQL over a varchar type.
( 2 ) minimum data types to store data as much as possible.
( 3 ) with fewer text types, use a single table store separately.
( 4 ) use not null as possible, set default value.
( 5 ) the database paradigm can reduce the redundancy of the field, but the can be considered in the appropriate case.
( 6 ) horizontal split of the database: to resolve the data of a database, split a table horizontally into multiple tables, encountered problems: how to query data in multiple tables.
( 7 ) vertical split of the database: split the column of the original table and the number of columns of the prerequisites. Principles: frequently used columns are placed in a table that isn't used in a table, and the text field is placed in a single table.