Database Tuning Tutorial (12) Optimizing SQL statements

V. Optimizing SQL statements

The previous chapter tells you how to design a good table, and a good table naturally requires a good SQL statement to manipulate it. This chapter will talk about how to optimize SQL statements.

1. SQL Statement Optimization principles

Optimize the query that needs to be optimized

Positioning Optimization Object Performance bottlenecks

Start with the explain

Complete sorting in the index whenever possible

Take only the column you need

Avoiding complex joins and subqueries as much as possible

2. Optimize limit

SELECT * from Test1 ORDER by ID limit 99999,10

The original statement uses the ID index, but the equivalent of locating from the first row to 99999 rows and then to scan the 10 rows, the equivalent of scanning the entire table

If you change to

SELECT * from Test1 where id>=100000 order by ID limit 10

Then navigate directly to the 100000 lookup

3. Try to avoid the SELECT * command

4. Do not allow sorting

When a group by group query is used, the default grouping is also sorted and may slow down .

Like what:

in the Group by back Add ORDER BY null prevents sorting.

5. Use a connection to replace a subquery

In some cases, you can use a connection to replace a subquery. Because using Join,mysql, you do not need to create temporary tables in memory.

[Poor efficiency]

SELECT * from Sales2 where company_id not in (select ID from Company2)

[Simple handling method]

[Left outer connection, highest efficiency]

SELECT * FROM dept LEFT join EMP on DEPT.DEPTNO=EMP.DEPTNO;  

6. Use limit 1 to obtain a unique row

Sometimes, when you want to query a table, you know you just need to look at one line. In this case, adding a limit of 1 will make your query more efficient. This allows the database engine to discover that only 1 will stop scanning, 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 overwrites all in-action clause queries to subqueries that use exists

8. Don't be a cheap hand

Do not use distinct and order by when it is not necessary.

These actions can be performed on the client side, which adds additional overhead

At the end of this chapter, the next chapter discusses other ways to optimize MySQL databases.

Database Tuning Tutorial (12) Optimizing SQL statements