An SQL statement may be a time bomb or a SQL time bomb.

An SQL statement may be a time bomb or a SQL time bomb.

A small SQL statement may be a perfect statement when the data volume is small. But with the increasing data volume, can you think of the catastrophic consequences? So what SQL statements are considered as good statements?

 

For SQL statements, select * is used by many people. For tables with large data volumes, such statements are nothing more than fatal. When designing a database, a good database administrator should think that the database should be prepared when the data volume is large.

 

Recently, I am doing a query statistics. Because the local database has been used for a long time, the data volume is not very large. Even if I wait for 1 second, I don't feel much about it. But when I connect to the real database, the feeling of waiting can only be described in one word. Suffering. No way. Which of the following users can use this system. The cards are stuck. Finally, place the SQL statement in the real database for execution. When a disaster comes, it is stuck. Database paralysis. Data is added in 1 second. You can imagine how big the data volume is. Only SQL statements can be optimized, but SQL statements cannot be optimized. You have to think about other methods.

 

Therefore, SQL statements are used with caution, and database optimization is very important. It is required by every "excellent" programmer.

 

How can this problem be solved? In fact, there are many solutions, depending on your understanding of the database. I don't know much about it. I can only learn from Daniel.

 

Because our data is real-time monitoring data, the data is updated quickly, but it is to prevent no data at the first startup, So we initialized and executed this SQL statement. After the restart, it can be said that it will never start again. It killed the entire system. Therefore, we simply do not execute this SQL statement, and directly give it an initial value during initialization. Drop it. Real-time data is stored in the dictionary, so we only need to query the data we need in the dictionary.

Of course, this method is helpless. Database optimization involves deep knowledge. In work and study, you must accumulate experience, dare to try, and dare to optimize. Databases have been used since June 2013, but they have remained at the basic level and have never grown. I think so, but when I look back at my blog, both stored procedures, views, and triggers are used by myself. Where have I stayed? Where am I. Yes. I won't optimize it. Big Data optimization is a big term for me, and I fear it.

 

The project is soon accepted, and the efficiency is so low. The manager invited a person dedicated to the database for 10 years to solve the efficiency problem. He optimized the project through table partitions. Restructured the database. As a matter of fact, we should have thought of this during development. It is indeed a bit inappropriate to move the database when the project is coming online soon.

 

After work, I would like to learn more about database optimization, and I will also learn how to make myself a good master. Haha.

 

For database optimization, there are many things we need to learn and pay attention. This has to be accumulated in the project practice. Everything I know begins with a loss. It's impossible to eat a fat man at once.

 

Therefore, we need to consider a lot of database usage, especially performance. We need to be more familiar with this knowledge. For example, do you know the disadvantages of "select *"? * It is nothing more than querying all columns. You know how time-consuming it is. Think carefully about every SQL statement you write and consider the consequences. We can also reduce the number of accesses to the database, so we can not retrieve data directly from the database. You can also use indexes to improve the efficiency of data retrieval. However, indexes require storage space and regular maintenance. indexes must be accurate and appropriate. Otherwise, unnecessary indexes also affect efficiency. We also need to avoid using computation on indexes.

 

Database optimization is required by each of us. I don't know much about it. I have to accumulate more experience in my work, so that in future projects, I will consider performance issues from the very beginning and think more. Performance issues should be taken seriously.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.