This article takes about 1.6 minutes to read.
Author: AIOps
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.
, END,
The growth path of programmers
Though the road is long, the journey is sure to come
This article was originally posted on the wechat public account of the same name “The Growth of programmers”, reply to “1024” you know, give a thumbs up.
Wechat ID: cxyDCzzl
Highlights from the past
7 big platform tools for programmers to connect private work
Teach you a way to use IDE programming to improve efficiency SAO operation!
A way to make money on the side during college
A conversation that tells you what an architect does?
As a programmer, you read several books a year on technology
Five Rare Linux commands
Why do programmers only turn off the monitor and never the computer after work?
The classic ebook package for programmers
How to introduce yourself gracefully in an interview?
How to design a database architecture that supports millions of concurrent transactions?