Shulou(Shulou.com)06/03 Report--

This article mainly explains "what are the reasons for not using select". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the reasons for not using select"?

The first sin: unnecessary IO

This will cause DBMS to read all the data pages, but the data you do not use, in the later SQL may be filtered out or directly useless, but reading data is costly, especially large fields, such as blob,text.

Select colunm_a,column_b (select * from XXX) tmp

The second sin: increasing the burden on the Internet

Reading more data means returning more data to the client, which means taking up more network bandwidth.

The third sin: taking up more memory

Because you read the excess data, your program needs more memory to store the data, which means you will use precious memory resources to store the unnecessary data.

The fourth sin: reliance on the order listed in ResultSet

If you use select * in your program, and rely on the column order of ResultSet (return results).

Then if you modify the table structure (add or remove fields) may affect the column order of the results returned, which may lead to your program data error or program exception.

The fifth sin: view binding

In the process of database design, we often have the following situations:

An underlying table is referenced by multiple views or stored procedures.

We have to be careful when modifying the underlying table, because there is no hint to tell us whether there will be a problem with the view or stored procedure if we continue to modify it.

Even if we know there is a problem, we have no way to refresh the view and stored procedures to get the most up-to-date information about the table.

If you use select * in creating views, it will result in all kinds of Bug.

The sixth sin: related conflict (Conflict in JOIN)

It may behave normally in a simple SQL, but when you associate multiple tables, you may cause SQL errors due to duplicate fields.

Common fields such as created,status,updated (usually available in tables).

Seventh sin: copying data

When you use SELECT * into INSERT.. When SELECT copies data (a common way of copying data), when the fields of two tables do not match, you may put the wrong data in the wrong column.

Eighth sin: rejecting the possibility of using an overlay index

Overlay index is a very important optimization method in MySQL. The basic principle is that query fields are all in the index to avoid returning to the table, so * will cause the overlay index to fail.

Summary

Do not use select

Mainly around the resources, and the stability and security of the program.

① wasted his time.

Whether reading data or transmitting data, waste means waiting, and time is priceless.

② wasted resources.

How could you do that when resources are so precious?

③ increases the possibility of Bug in the system.

For example:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The program runs correctly and the data is incorrect.

Program error report

The running result of the program is uncertain.

Why would someone use select *?

① cherishes time

Can be done, why write that multi-field, but also not beautiful and direct ah, in fact, is lazy!

② reduces the work of SQL parsers

It is considered that negotiating a large number of fields in the select statement will increase or decrease the work of SQL parsing, such as permission, whether the field exists, and so on.

This is true but this was the case a long time ago and now * instead increases the work of the SQL parser.

Thank you for your reading, the above is the content of "what are the reasons for not using select?" after the study of this article, I believe you have a deeper understanding of the reasons for not using select, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.