Summary of practical methods for optimizing SQL query Statements _mysql

Query statement optimization is a way to optimize SQL efficiency, you can optimize the SQL statements to use the existing indexes to avoid full table scanning, thereby improving query efficiency. Recently in the project to optimize some of the SQL, summed up a number of methods.

1. Create an index in the table, giving priority to the fields that are used by the where and group by.

2, try to avoid using SELECT *, return useless fields will reduce query efficiency. As follows:

SELECT * FROM T

Optimization: Use specific fields instead of *, returning only the fields that are used.

3, try to avoid using in and not in, causing the database engine to discard the index for full table scanning. are as follows:

SELECT * FROM T WHERE ID in (2,3)

SELECT * FROM T1 WHERE username into (select username from T2)

Optimization method: If it is a continuous value, you can replace it with between. As follows:

SELECT * FROM T WHERE ID BETWEEN 2 and 3

If it is a subquery, you can replace it with exists. As follows:

SELECT * from t1 where EXISTS (select * from t2 where t1.username = t2.username)

4. Avoid using or, as much as possible, causes the database engine to discard indexes for full table scans. are as follows:

SELECT * FROM t WHERE id = 1 OR id = 3

Optimization method: You can use union instead of or. As follows:

SELECT * FROM t WHERE ID = 1
UNION
SELECT * FROM t WHERE ID = 3

(PS: If the fields on either side are the same, as in the example.) Looks like two ways of efficiency, even if the Union scan is an index, or scan the entire table.

5, try to avoid in the field at the beginning of the fuzzy query, will cause the database engine to discard the index for full table scan. are as follows:

SELECT * FROM T WHERE username like '%li% '

Optimization method: Try to use fuzzy query after the field. As follows:

SELECT * FROM T WHERE username like ' li% '

6, as far as possible to avoid the determination of NULL value, will cause the database engine to discard the index for full table scan. are as follows:

SELECT * from T WHERE score is NULL

Optimization method: You can add a default value of 0 to the field and judge the value of 0. As follows:

SELECT * from t WHERE score = 0

7, as far as possible to avoid the equals sign in the where condition of the expression, function operations, will cause the database engine discard the index for full table scan. are as follows:

SELECT * from t2 WHERE SCORE/10 = 9

SELECT * from T2 WHERE SUBSTR (username,1,2) = ' Li '

Optimization: You can move expressions, function operations to the right of the equals sign. As follows:

SELECT * from t2 WHERE score = 10*9

SELECT * FROM T2 WHERE username like ' li% '

8, when the data volume is large, avoids using where 1=1 condition. Typically, for the convenience of assembling query conditions, we use this condition by default, and the database engine discards the index for full table scans. are as follows:

SELECT * from T WHERE 1=1

Optimization method: Use code to assemble the SQL to judge, no where to add where, where plus and.

In fact, summed up, we should also find that in the query, to try to make the database engine to use the index. And how to get the database to use the index according to our meaning involves the concept of scanning parameters (SARG). During the query analysis phase of the database engine, the query optimizer is used to analyze each stage of the query, such as a query-like SQL statement, to determine the amount of data that needs to be scanned. If a phase can be used as a scanning parameter, it can limit the amount of data that is searched, thereby improving search efficiency to some extent.

Sarg definition: An operation that restricts the search because it usually refers to a specific match, a match within a range of values, or a two or more conditional and join.

So we're going to make the query conditions we write as much as possible to make the engine recognized as a scan parameter. Specific approaches, as mentioned above.

The above is a small series for you to bring the SQL query statement optimization of practical methods to summarize all the content, I hope that we support cloud Habitat Community ~