Database optimization tutorial (12) Optimize SQL statements and optimize SQL statements

Database optimization tutorial (12) Optimize SQL statements and optimize SQL statements
5. Optimize SQL statements

The previous chapter describes how to design a good table. A good table naturally requires a good SQL statement to operate on it. This chapter describes how to optimize SQL statements.

1. SQL statement Optimization Principles

Optimize the Query to be optimized

Locate performance bottlenecks of optimized objects

Start with Explain

Sort indexes as much as possible

Retrieve only the Column you need

Avoid complex join and subquery as much as possible

 

2. Optimized limit

select * from test1 order by id limit 99999,10

Although the original statement uses the id index, it is equivalent to scanning the last 10 rows from the first row to the first row. It is equivalent to scanning the entire table.

If you change

select * from test1 where id>=100000 order by id limit 10

Then go to 100000 to find

3. Avoid the SELECT * command whenever possible

4. Do not allow sorting

When you query by group by, the default group is sorted, which may reduce the speed.

 

For example:

Adding order by null after group by can prevent sorting.

5. Replace subqueries with connections

In some cases, you can use a connection to replace subqueries. Because join is used, MySQL does not need to create a temporary table in memory.

[Poor efficiency]

select * from sales2 where company_id not in(select id from company2)

[Simple processing method]

select * from dept, emp where dept.deptno=emp.deptno; 

[Left Outer Join, most efficient]

select * from dept left join emp on dept.deptno=emp.deptno;  

6. Use LIMIT 1 to get a unique row

Sometimes, when you want to query a table, you know that you only need to read a row. In this case, adding a LIMIT 1 will make your query more effective. In this way, the database engine will only stop scanning after 1, instead of scanning the entire table or index.

 

7. Use EXISTS instead of in

EXISTS is far more efficient than IN. It is related to full table scan and range scan. Almost all IN operator subqueries are rewritten to subqueries using EXISTS

 

8. Don't be cheap

Do not use DISTINCT or order by when there is no need

These actions can be executed on the client, which adds additional overhead.

This chapter ends. The next chapter describes other mysql database optimization methods.