Detailed explanation of MYSQL sql statement optimization-Mysql Tutorial-php.cn

Detailed explanation of MYSQL sql statement optimization

黄舟

Release： 2017-02-28 13:12:00

Original

1722 people have browsed it

There are many optimization techniques in SQL. Sometimes two SQL statements that can achieve the same function have different query performance, so it is necessary to master some efficient SQL codes.

1. Query Optimization

## 1. Reduce the number of rows in the record set and avoid select *

2. Use exist instead of in, for example: Select sno from stu where exist(select * from grand where sno=stu.sno)

3. Use > ;= instead of >, add equals and then compare to directly lock the minimum or maximum value. If you only write >, the database will compare row by row.

4. Use index, but do not perform calculations on the index. When encountering or, the index will fail. Use union instead of or

## 5 . Use limit 1 when querying only one piece of data. This will cause the search to continue after the data is found.

6. Avoid using rand(). When the query data wants to be out of order, You can generate a random number first:

                   select userName from user limit $rand,1;

Copy after login

7. Query join should use a small result set to drive a large result set

8. Do all the It is possible to use less subqueries. The subquery process will first create a temporary table in the inner query and delete the temporary table after the query is completed.

## 2. Analyze the database optimization strategy from the perspective of database construction

1. Optimize the database structure and divide the table with many database fields into multiple tables horizontally

## 2. Disable the index before inserting data and then enable it:

 ALTER TABLE table_name DISABLE KEYS ;
                  ALTER TABLE table_name ENABLE KEYS ;

Copy after login

3. Use batch insert statements: If you can use the LOAD DATA INFILE statement, try to use it. If you cannot use it, you can choose:

ALTER TABLE table_name ENABLE KEYS ;

Copy after login

The above is the detailed explanation of MYSQL sql statement optimization. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!