MySQL Database optimization

1. How to unify SQL statements

SELECT * from Test;

SELECT * from Test;

For these two statements, the programmer will feel the same, but the database query optimizer thinks it is not the same.

2, add index (in the case of large amount of data, the effect is particularly significant)

The effect of adding indexes is: 1, greatly reduce the amount of data that the server needs to scan, thus improve the retrieval speed of data; 2. I can turn random I/O into sequential I/O

3, table design time to choose the optimized data type, smaller usually better. Smaller data types are usually faster because they take up less disk, memory, and CPU cache, and require less CPU cycles to process, and simple data types typically require less CPU cycles, such as Integer Fu Cao for lower-cost.

4, try to avoid null, in the WHERE clause to determine the null value of the field, will cause the engine to abandon the use of indexes for full table scanning, for example: Selec ID from test where the NUM is null, you can set the default value on num is 0, Make sure that the NUM column in the table does not have a null value, and then query: Selec ID from test where num = 0

5, the allocation really needs to be space, the use of varchar (5) and Varc (20) storage string space overhead is the same, but the longer class will consume more memory, because MySQL usually allocates a fixed size of memory blocks to hold internal values.

6, table field few but good, single table field as far as possible to control within 20 fields.

7. If the operation requires a large number of SQL statements or repeated executions, the stored procedure executes faster than the SQL statement.

A few things to note when writing SQL statements:

1. Index on the Where and order columns not involved

2. Try to avoid using in the WHERE clause! = or <> operator

3. Avoid using or in the WHERE clause to join conditions, which will cause the index to fail and perform a full table scan

4, like with caution, like keyword% index valid, like%keyword index invalidation

5, in and not should be used with caution

6. Try to avoid expression operations on the fields of the WHERE clause

MySQL Database optimization