Source: blog.csdn.net/qq_32421489…

Applications are slow for a variety of reasons, whether it’s the network, the system architecture, or the database.

So how to improve the database SQL statement execution speed? Some might say that performance tuning is a matter for database administrators (DBAs), but performance tuning is also a matter for programmers.

Procedures embedded lines of SQL statements, if the use of some optimization tips, will be able to achieve twice the result with half the effort.

Tip 1: The comparison operator uses “=” instead of “<>”

“=” increases the chance of index usage.

Tip 2 If you know there is only one query result, use “LIMIT 1”

“LIMIT 1” can avoid full table scan. If the result is found, the scan will not continue.

Tip 3: Choose the right data type for the column

If you can use TINYINT, you can’t use SMALLINT. If you can use TINYINT, you can’t use INT.

Tip 4 turn large DELETE, UPDATE, or INSERT queries into multiple smaller queries

Does being able to write a SQL statement with dozens or hundreds of lines seem like a tall order? However, for better performance and better data control, you can break them down into multiple small queries.

Tip 5 uses UNION ALL instead of UNION if the result set allows repetition

Because UNION ALL does not eliminate weight, the efficiency is higher than that of UNION.

Tip 6 Keep your SQL statements consistent for multiple executions of the same result set

The goal is to take full advantage of the query buffer.

For example, to query the product price by region and product ID, the first time to use:

For the second query, keep the same as the previous statement. For example, do not swap the ID and region positions in the WHERE statement.

* Tip 7 avoid using “SELECT” whenever possible

If you do not query all the columns in the table, avoid SELECT * because it does a full table scan and does not make efficient use of the index, increasing the burden on the database server and the network IO overhead between it and the application client.

Tip 8: Try to index the columns in the WHERE clause

Just “try” oh, not all columns. Adjust for local conditions, because sometimes too many indexes can degrade performance.

Tip 9 The columns in the JOIN clause should be indexed as much as possible

Again, just “try” oh, not all columns.

Tip 10 ORDER BY columns should be indexed as much as possible

The ORDER BY column also performs better if it is indexed.

Tip 11 Implement paging logic using LIMIT

Not only improves performance, but also reduces unnecessary network transfers between databases and applications.

Tip 12 Use the EXPLAIN keyword to view the execution plan

EXPLAIN can check index usage as well as rows scanned.

other

There are many ways to tune SQL, and the same query result can be queried in many different ways.

The best way to do this is to test the most realistic data sets and hardware in the development environment and then release them to production.