Shulou(Shulou.com)05/31 Report--

Editor to share with you the method of sql optimization, I hope you have something to gain after reading this article, let's discuss it together!

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.

The method of sql optimization:

1. Build an index in the table and give priority to the fields used by where and group by.

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

SELECT * FROM t

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

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

SELECT * FROM t WHERE id IN (2) 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

If it is a subquery, you can use exists instead. As follows:

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

4. Avoid using or as much as possible, which will cause the database engine to abandon the index for full table scan. As follows:

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

Optimization: union can be used instead of or. As follows:

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

(PS: if the fields on both sides of the or are the same, as in the example. It seems that the efficiency of the two methods is about the same, even though union scans indexes and or scans whole tables)

5. Try to avoid fuzzy queries at the beginning of the field, which will cause the database engine to abandon the index for full table scan. As follows:

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

Optimization: try to use a fuzzy query after the field. As follows:

SELECT * FROM t WHERE username LIKE 'li%'

6. Avoid judging the null value as much as possible, which will cause the database engine to abandon the index and scan the full table. As follows:

SELECT * FROM t WHERE score IS NULL

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

SELECT * FROM t WHERE score = 0

7. Try to avoid the operation of expressions and functions on the left side of the equal sign in the where condition, which will cause the database engine to abandon the index for full table scanning. As follows:

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

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

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

8. Avoid using the condition of where 1 to 1 when the amount of data is large. 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 for full table scanning. As follows:

SELECT * FROM t WHERE 1

Optimization method: use code to assemble sql to judge, there is no where plus where, there is where plus and.

After reading this article, I believe you have a certain understanding of "the method of sql optimization". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.