Are you still in select *?

Time:2020-1-22

There are many reasons for the slow application, such as network, system architecture and database.

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

If you use some optimization techniques, you will get twice the result with half the effort.

Tip 1Comparison operators can use “=” instead of “< >

“=” increases the index usage.

Tip 2Knowing that there is only one query result, please use “limit 1”

“Limit 1” can avoid full table scanning. If the corresponding result is found, the scanning will not continue.

Tip 3Select the appropriate data type for the column

If you can use tinyint, you don’t need to use smallint. If you can use smallint, you don’t need to use int. you know, the smaller the disk and memory consumption, the better.

1.1 how to define boolean type data like Java type in MySQL? In fact, MySQL is not directly defined as a Boolean data type, it can only be defined as tinyint (1); when a Boolean equals 1, it means true, and when a Boolean equals 2, it means false;

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

Tip 4Turn large delete, update or insert queries into multiple small queries

Can you write a SQL statement with dozens or hundreds of lines? However, for better performance and better data control, you can turn them into multiple small queries.

Tip 5Use union all instead of union, if the result set allows repetition

Because union all does not remove the weight, the efficiency is higher than Union.

Tip 6To get multiple executions of the same result set, keep the SQL statements consistent

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

For example, you can query the product price according to the region and product ID, and use it for the first time:

 

For the second query, please keep the consistency of the above statements. For example, do not change the order of ID and region position in the where statement.

Tip 7Try to avoid using “select *”

If you don’t query all the columns in the table, try to avoid using select *, because it will scan the whole table and can’t use the index effectively, which increases the burden of the database server and the network IO overhead between it and the application client.

Tip 8Columns in the where clause are indexed as much as possible

Just “try” Oh, not all the columns. Adjust according to local conditions, because sometimes too many indexes will reduce performance.

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

Also just “try” Oh, not all the columns.

Tip 10Columns of order by are indexed as much as possible

If the column of order by is indexed, the performance will be better.

Tip 11Using limit to implement paging logic

It not only improves the performance, but also reduces the unnecessary network transmission between database and application.

Tip 12Use the explain keyword to view the execution plan

Explain can check index usage and rows scanned.

Other

There are many ways to tune SQL, and there are many different ways to query the same query results. In fact, the best way is to test in the development environment with the most realistic dataset and hardware environment, and then release to the production environment.

The above is the detailed explanation and integration of MySQL select * introduced by Xiaobian to you, hoping to help you. If you have any questions, please leave a message to me, and Xiaobian will reply you in time. Thank you very much for your support of the developepaer website!

Recommended Today

[Redis5 source code learning] analysis of the randomkey part of redis command

baiyan Command syntax Command meaning: randomly return a key from the currently selected databaseCommand format: RANDOMKEY Command actual combat: 127.0.0.1:6379> keys * 1) “kkk” 2) “key1” 127.0.0.1:6379> randomkey “key1” 127.0.0.1:6379> randomkey “kkk” Return value: random key; nil if database is empty Source code analysis Main process The processing function corresponding to the keys command is […]