Goodbye! select

The application program is as slow as a cow, and there are many reasons. It may be the reason of the network, the system structure, and the database.

So how to improve the execution speed of database SQL statements? Some people will say that performance tuning is a database administrator (DBA) business, but performance tuning also has a lot to do with programmers.



Lines of SQL statements embedded in the program, if you use some optimization techniques, you can achieve a multiplier effect with half the effort.

Tip 1 The comparison operator can use "=" instead of "<>"

"=" increases the probability of using the index.

Tip 2 Knowing that there is only one query result, please use "LIMIT 1"

"LIMIT 1" can avoid the full table scan, and will not continue to scan if the corresponding result is found.

Tip 3 Choose the appropriate data type for the column

If you can use TINYINT, you don’t need SMALLINT. If you can use SMALLINT, you don’t need INT. As you know, the smaller the disk and memory consumption, the better.

Tip 4 Turn a large DELETE, UPDATE or INSERT query into multiple small queries

Does it seem compelling to be able to write a SQL statement of dozens or hundreds of lines? However, in order to achieve better performance and better data control, you can turn them into multiple small queries.




Tip 5 Use UNION ALL instead of UNION, if the result set allows duplicates

Because UNION ALL does not remove duplicates, the efficiency is higher than UNION.


Tip 6 To obtain multiple executions of the same result set, please keep the SQL statement consistent

The purpose of this is to make full use of the query buffer.

For example, query the product price based on the region and product id, the first time I used:



Then the second time the same query, please keep the consistency of the above statement, for example, do not reverse the order of id and region in the where statement.

Tip 7 Try to avoid using "SELECT *"

If you don't query all the columns in the table, try to avoid using SELECT *, because it scans the entire table and cannot effectively use indexes, which increases the burden on the database server and the network IO overhead between it and the application client.




Tip 8 The columns in the WHERE clause should be indexed as much as possible

Just "try to", not all columns. Adjust measures according to local conditions, because sometimes too many indexes will reduce performance.

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

The same is just "try to", not all columns.

Tip 10 ORDER BY columns are indexed as much as possible

If the ORDER BY column is indexed, the performance will be better.

Tip 11 Use LIMIT to implement paging logic

It not only improves performance, but also reduces unnecessary network transmission between databases and applications.

Technique 12 Use the EXPLAIN keyword to view the execution plan

EXPLAIN can check index usage and scanned rows.

other

There are many SQL tuning methods, and there can be many different query methods for the same query result. In fact, the best way is to test with the most realistic data set and hardware environment in the development environment, and then release it to the production environment.