MySQL simple optimization "easy to learn"

1, select the most applicable field properties;

table field as small as possible, do not add unnecessary space to the database, such as: the value of ' 01 ', ' 02 ', to char (2) can be;

2, use the connection (join) to replace the subquery (sub-queries);

Using join IS because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.

3, the establishment index;

Generally used in join,where judgment, the order is ordered on the field.

4. Avoid using SELECT * from table name;

What fields you want to write.

5, try to avoid in where in! = or <>, or discard the use of the index for full table scanning;

6. try to avoid the null value of the field in the where, use or as the connection condition, otherwise it will cause the engine to abandon using the index for full table scan;

7, the following query will also result in a full table scan: (Can not be placed before and after the percent sign);

Select ID from the where name like '% sheet% '

8, in and not in also to use with caution, for the value, can use between do not use in;

9, avoid the expression of the field in the Where to operate, it will also cause the full table scan;

Error: Select ID from user where age/2=10

Correct: Select ID from user where age=10*2

10, as far as possible to set the number of fields, because the engine in the processing of queries and connections, character type will be compared, the digital type will only be compared once;

Well, MySQL's simple optimization is about here, like a little bit of attention ~

MySQL simple optimization "easy to learn"