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