The eight deadly SINS of Select *

Background (a phrase widely circulated on the Internet)

You can often see on the Internet there are always some people will tell you not to use the select column name instead of the specific column name, and they also give you a part of the reason, now we also dig into the select exactly why not recommended!

The first unnecessary IO

  • This can cause the DBMS to read all pages of data that you don’t use, which may be filtered out or simply not used in later SQL, but which can be costly to read, especially for large fields such as BLOb or text.

    select colunm_a,column_b(select * from XXX) tmp

The second case adds to the burden on the network

  • Reading more data means sending more data back to the client, which means consuming more network bandwidth

The third one takes up more memory

  • Because you read extra data, your program needs more memory to store it, which means you’re using precious memory resources to store unnecessary data

The fourth dependency on the order listed on the ResultSet

  • If you use select * in your application and rely on the order of columns in a ResultSet, changing the table structure (adding or removing fields) may affect the order of columns in the return result, which may result in errors or exceptions.

The fifth view binding

In the database design process, we often have such a situation

  1. An underlying table can be referenced by multiple views or stored procedures
  2. We have to be careful when we modify the underlying table, because there is no indication that if we continue to modify it, it will cause problems in the view or stored procedure
  3. Even if we knew there was a problem, there was no way to refresh the view or stored procedure to get the latest information about the table
  • If you use SELECT * in view creation, it can cause all kinds of bugs

Sixth Related Conflict (Conflict in JOIN )

  • This may work well in simple SQL, but when you associate multiple tables, SQL errors may occur due to the same name fields, such as created, Status,updated, and other common fields (common tables have these fields).

The seventh data copy

  • When you useSELECT * into INSERT .. SELECTWhen copying data (a common way of copying data), when two tables have different fields, you can put the wrong data in the wrong column.

The eighth rejected the possibility of using an overwritten index

  • Overwrite index is a very important optimization method in mysql. The basic principle is that all query fields are in the index to avoid back table, so * will cause overwrite index failure.

conclusion

Do not use the select

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

A waste of time

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

Second, it wastes resources

  • How could you do that with such precious resources

Three increases the possibility of bugs in the system

  • The program is running correctly and the data is not correct
  • Application error
  • Program results are uncertain

Why would anyone use select *

Cherish the time

  • Can fix things, why also write that many fields, not beautiful and direct ah, in fact, is lazy!

Reduce SQL parser work

  • It is true that negotiating a large number of fields in a SELECT statement increases or decreases the work of SQL parsing, such as permissions, field existence, and so on, but this was a long time ago, and now * actually increases the work of the SQL parser.