What are the methods for sql optimization?

SQL optimization methods: 1. Try to avoid using [select *], useless fields will reduce query efficiency; 2. Avoid using in and not in, you can choose between and exists instead; 3. Avoid using or , you can choose union instead.

SQL optimization method:

(recommended learning: mysql tutorial)

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

2. Try to avoid using select *. Returning useless fields will reduce query efficiency. As follows:

SELECT * FROM t

Optimization method: use specific fields instead of *, and only return the used fields.

3. Try to avoid using in and not in, which will cause the database engine to abandon the index and perform a full table scan. 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, it can be replaced by between. As follows:

SELECT * FROM t WHERE id BETWEEN 2 AND 3

If it is a subquery, it can be replaced by exists. As follows:

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4. Try to avoid using or, which will cause the database engine to abandon the index and perform a full table scan. As follows:

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

Optimization method: Union can be used instead of or. As follows:

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

(PS: If the fields on both sides of or are the same, as in the example. It seems that the two methods are about the same efficiency, even if union scans the index, or scans the entire table)

5. Try to avoid fuzzy queries at the beginning of fields, which will cause the database engine to abandon the index and perform a full table scan. 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. Try to avoid judging null values, which will cause the database engine to abandon the index and perform a full table scan. 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 0 value. As follows:

SELECT * FROM t WHERE score = 0

7. Try to avoid performing expressions and function operations on the left side of the equal sign in the where condition, which will cause the database engine to abandon the index and perform a full table scan. As follows:

SELECT * FROM t2 WHERE score/10 = 9
SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

Optimization method: Expressions and function operations can be moved to the right side of the equal sign. As follows:

SELECT * FROM t2 WHERE score = 10*9
SELECT * FROM t2 WHERE username LIKE 'li%'

8. When the amount of data is large, avoid using the condition where 1=1. Usually, in order to facilitate the assembly of query conditions, we will use this condition by default, and the database engine will abandon the index and perform a full table scan. As follows:

SELECT * FROM t WHERE 1=1

Optimization method: Use code to judge when assembling sql. If there is no where, add where, if there is where, add and.

The above is the detailed content of What are the methods for sql optimization?. For more information, please follow other related articles on the PHP Chinese website!