Summary of the practical methods for optimizing SQL query statements, and the practical use of SQL query statements

Summary of the practical methods for optimizing SQL query statements, and the practical use of SQL query statements

Optimization of query statements is a way to optimize SQL efficiency. You can optimize SQL statements to use existing indexes as much as possible to avoid full table scans, thus improving query efficiency. I have recently optimized some SQL statements in the project and summarized some methods.

1. Create an index in the table, and prioritize the fields used by where and group.

2. Avoid using select * whenever possible. Returning useless fields will reduce the query efficiency. As follows:

SELECT * FROM t

Optimization Method: Use a specific field instead of *. Only the fields used are returned.

3. Avoid using in and not in as much as possible, which will cause the database engine to discard the index for full table scanning. As follows:

SELECT * FROM t WHERE id IN (2, 3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

Optimization Method: if it is a continuous value, you can use between instead. As follows:

SELECT * FROM t WHERE id BETWEEN 2 AND 3

For subqueries, use exists instead. As follows:

SELECT * FROM t1 where exists (SELECT * FROM t2 WHERE t1.username = t2.username)

4. Avoiding or as much as possible will cause the database engine to discard the index for full table scanning. 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 or are the same, as in the example. The efficiency of the two methods is similar. Even if union scans indexes, or scans the entire table)

5. Avoid fuzzy queries at the beginning of a field, which will cause the database engine to discard the index for full table scanning. As follows:

SELECT * FROM t WHERE username LIKE '% li %'

Optimization Method: Use Fuzzy search after fields as much as possible. As follows:

SELECT * FROM t WHERE username LIKE 'Li %'

 
6. Try to avoid null value judgment, which will cause the database engine to discard the index for full table scan. As follows:

SELECT * FROM t WHERE score IS NULL

Optimization Method: You can add the default value 0 to the field to determine the value 0. As follows:

SELECT * FROM t WHERE score = 0

7. Avoid performing expression and function operations on the left side of the where condition with a medium number. This will cause the database engine to discard the index for full table scanning. As follows:

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 where substr (username, 1, 2) = 'lil'

Optimization Method: You can move expressions and function operations to the right of the equal sign. As follows:

SELECT * FROM t2 WHERE score = 10*9

SELECT * FROM t2 WHERE username LIKE 'Li %'

8. When the data volume is large, avoid using the where 1 = 1 condition. This condition is usually used by default to facilitate the assembly of query conditions. The database engine will discard the index for full table scanning. As follows:

SELECT * FROM t WHERE 1 = 1

Optimization Method: when using code to assemble SQL statements, you can determine whether to add where, where and without where.

 
In fact, to sum up, we should also find that we should try to make the database engine use indexes during queries. How to make the database use indexes as we mean involves the concept of scan parameters (SARG. In the query and analysis stage, the database engine uses the query optimizer to analyze each stage of the query (for example, different query stages exist for an SQL statement with a query, to determine the amount of data to be scanned. If a phase can be used as a scan parameter, you can limit the amount of data to be searched to improve search efficiency to a certain extent.

The definition of SARG: it is used to restrict a search operation, because it usually refers to a specific match, a match within the range of a value or the AND connection of two or more conditions.

Therefore, we need to make the query conditions we write as much as possible for the engine to identify as scan parameters. The specific method is as mentioned above.

The above is a summary of all the practical methods for optimizing SQL query statements provided by Alibaba Cloud ~