Are you still Select *?

There are many reasons why an application is as slow as a cow. It may be due to the network, the system architecture, or the database.

So how to improve the execution speed of database SQL statements? Some people would say that performance tuning is the job of database administrators (DBAs), but performance tuning also has a lot to do with programmers.

If you use some optimization tips for the SQL statements embedded in the program, you will be able to achieve twice the result with half the effort.

Tip 1: Use “=” instead of “<>” when using comparison operator

"=" increases the chances of using the index.

Tip 2 : If you know there is only one query result, use "LIMIT 1"

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

Tip 3 : Choose the right data type for your column

If you can use TINYINT, don't use SMALLINT. If you can use SMALLINT, don't use INT. You know the reason. The less disk and memory consumption, the better.

1.1 How to define Boolean data in MySQL like in Java? In fact, MySQL does not directly define the Boolean data type. It can only be defined as tinyint(1). When boolean equals 1, it represents true, and when boolean equals 2, it represents false.

1.2 Long data type corresponds to bigint data type in MySQL database;

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

Doesn’t it seem very impressive to be able to write a SQL statement of dozens or hundreds of lines? However, to achieve better performance and better control over your data, you can break them into multiple smaller queries.

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

Because UNION ALL does not remove duplicates, it is more efficient than UNION.

Tip 6 : To get the same result set multiple times, keep the SQL statement consistent

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

For example, to query product prices based on region and product ID, the first use is:

When you run the same query for the second time, please keep the above statements consistent. For example, do not swap the order of id and region in the where statement.

Tip 7 : Avoid using "SELECT *"

If you do not query all the columns in the table, try to avoid using SELECT *, because it will perform a full table scan and cannot effectively use indexes, increasing the burden on the database server and the network IO overhead between it and the application client.

Tip 8: Use indexes in the WHERE clause as much as possible

Just "try your best", not all columns. You should adapt to local conditions and make adjustments based on actual conditions, because sometimes too many indexes can reduce performance.

Tip 9: Use indexes in JOIN clauses as much as possible

Again, it's just "try your best", not all columns.

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

Performance will also be better if the ORDER BY columns are indexed.

Tip 11: Use LIMIT to implement paging logic

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

Tip 12: Use the EXPLAIN keyword to view the execution plan

EXPLAIN can examine index usage and scanned rows.

other

There are many ways to tune SQL, and the same query results can be queried in many different ways. In fact, the best way is to test in the development environment with the most realistic data set and hardware environment, and then release it to the production environment.

The above is the detailed integration of MySql Select * introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!