This article mainly summarizes the experience from the work experience summed up SQL statement optimization problem, we use Demo to illustrate how to improve the efficiency of SQL execution:

1, the limit page optimization problem

SELECT * FROM message_1 LIMIT 10000,10Copy the code

This statement is very fast. Let’s see how long it takes to change it to the following statement.

SELECT * FROM message_1 LIMIT 1000000,10Copy the code

The running result is shown as follows:

The above statement took 17.7 seconds! So how can we optimize this SQL?

Spring, MyBatis, Netty source code analysis, high concurrency, high performance, distributed, microservice architecture principle, JVM performance optimization, concurrent programming these become the architect essential knowledge system. You are also welcome to join the exchange learning

Solutions:

We add “order by ID” to alter SQL:

SELECT * FROM message_1 order by id LIMIT 1000000,10Copy the code

The execution diagram is as follows:

It took just 1.1 seconds, which is a 17-fold improvement in efficiency. This is because the SQL statement uses the primary key ID as the index, so it is fast.

2. Do not use SELECT * from table under any circumstances

Because this will be a full table scan, resulting in low efficiency

SELECT * FROM message_1Copy the code

Should be changed to

SELECT content FROM message_1Copy the code

3. Optimize batch insertion

INSERT INTO message_1(id,content) values(1,Content of the '1')INSERT INTO message_1(id,content) 
values(2,Content of the '2')INSERT INTO message_1(id,content) 
values(3,Content of the '3')INSERT INTO message_1(id,content) values(4,Content of '4')INSERT INTO
 message_1(id,content) values(5,'content 5')Copy the code

Should be changed to

INSERT INTO message_1(id,content) values(1,Content of the '1'), (2,Content of the '2'),
(3,Content of the '3'), (4,Content of '4'), (5,'content 5')Copy the code

4. Optimization of like statement

SELECT content message_1 A WHERE content like '% I want to learn %'Copy the code

Select * from ‘%’ where ‘%’ = ‘%’; select * from ‘%’ where ‘%’ = ‘%’;

SELECT content FROM message_1 WHERE content like 'I want to learn %'Copy the code

5. Avoid using IS NULL or IS NOT NULL statements in WHERE statements

SELECT content FROM message_1 WHERE content IS NULLCopy the code

The above statement also invalidates the index for a full table scan. We avoid using IS NULL or IS NOT NULL for conditional selection in the WHERE statement, which invalidates the index and results in a slow query

6. Do not use functions, arithmetic operations, or other expression operations in WHERE conditions

SELECT content FROM message_1 WHERE datediff(day,createTime,'2020-05-25') = 0Copy the code

Instead of

SELECT content FROM message_1 WHERE createTime>='2020-05-01' and createTime<'2020-05-25'Copy the code

This tells us not to have functions, arithmetic operations, or other expression operations in the WHERE condition, which would also invalidate the index.

7. Sort index problems

If only one index is used in the mysql query and the INDEX is already used in the WHERE condition, the fields in order BY will not use the index. So try not to sort multiple fields at the same time, and if such a scenario exists, it is best to set joint indexes for these fields.

8, union all replace union

The difference between union and Union all lies in that the former needs to merge more than two result sets and then perform unique filtering operation, which will definitely involve data sorting and increase CPU computing and resource consumption and delay. Therefore, union all is used when you are sure there are no duplicates or you do not care about duplicates.

9, Left Joinright join and inner Join

SELECT A.id,A.name,B.id,B.name FROM message_1 LEFT JOIN message_2 b ON b.id =B.id; SELECT A.id,A.name,B.id,B.name FROM message_1 RIGHT JOIN message_2 b ON B A.id= B.id; SELECT A.id,A.name,B.id,B.name FROM message_1 INNER JOIN message_2 b ON A.id =B.id;Copy the code

The inner join is faster because it is an equivalent join and returns fewer rows. Therefore, it is best to use inner Join during project development

10. Limit the number of indexes to 5

Because more indexes is not always better. Although indexes improve query efficiency, they also reduce the efficiency of modification and addition. Inserts and updates have the ability to rebuild indexes, so it is best to limit the number of indexes in a table to five. If so, you need to optimize for that table