A SQL statement that could be a time bomb

a little bit of SQL statement, when the amount of data is very small, may be a fairly perfect sentence. But as the volume of data increases, can you think of the disastrous consequences he brings? So what kind of SQL statement is a good statement?

for SQL statement that many people come up with is SELECT * , don't think, for a table with a large amount of data, such a statement is nothing but fatal. A good database administrator, when designing a database, should think of when the volume of data is very large, it should be prepared.

recently in a query to do statistics, Because the database has been used in this machine, the amount of data is not very large, even when the query, such as 1s feel nothing, but when connected to the real database, the feeling of waiting, can only use a word to describe. Suffering. No way, such a system which users dare to use AH. The Kaduka exploded. Finally put the SQL statement in the real database execution. The disaster came, and the direct card died. The database is paralyzed. The data is added in 1 seconds. It is conceivable that the amount of data is too large. Only SQL statements can be optimized , and light-optimized SQL statements are not. We have to think of other ways.

So, SQL statements careful use, the database optimization is very important, is every "good" programmer must be.

How is this problem solved? In fact, a lot of solutions, look at your understanding of the database level. I know not deep, can only follow Daniel study.

because our data is real-time monitoring data, the data is updated very quickly, but is to prevent the first boot without data, so it was initialized, the implementation of this SQL statement. Cause we restart, it can be said that no longer start. It killed the whole system. So we simply do not execute the SQL statement, the initialization of the direct give him an initial value. Shoot it straight away. Real-time data is stored in a dictionary, so we only need to query the dictionary for the data we need.

This approach is of course helpless. The optimization of the database contains a deep knowledge. In the work, learning to accumulate experience, dare to try, dare to optimize. For the database, from 6 months beginning contact, but stay at the basic level, has never been improved. I think so, but looking back at my blog, whether it is a stored procedure or a view, the trigger has been used, it will be used. Where did I stay? I'm in a bad place. That's right. I will not optimize. Big Data optimization, for me is a tall word, I fear him.

The project is soon acceptance, the efficiency is so low, the manager invited a Ten the person who is dedicated to the database to solve the efficiency problem, he is optimized by table partitioning. refactored the database. In fact, these in the development of the time should be thought of, such as the project is fast on-line and then move the database is really a bit inappropriate.

After work, I also Baidu a bit, learn more about the optimization of the database, this to learn, I also want to learn to become Daniel, haha.

For the database optimization, there are many we need to learn, but also a lot of attention. This has to accumulate in the project practice. I know of a lost, everything from the beginning of a drop, it is not possible to eat a fat one bite.

Therefore, we have to consider a lot of the use of databases, especially performance. We need to be more in touch with this knowledge. For example: Do you know the disadvantage of select * ,* Nothing is to query all the columns. It's time-consuming, you know. think carefully about every SQL statement you write and consider the consequences. We can also reduce the number of access to the database, not directly from the library to take the data. Indexes can also be used to improve the efficiency of retrieving data, but the index needs to be stored and maintained regularly, with an appropriate amount of index to be added and accurate. Otherwise, unnecessary indexes also affect efficiency. We also want to avoid using calculations on the index.

Database optimization is what each of us needs to be. I know too little, I have to accumulate experience in the work, so that in the future project, from the beginning, the performance of the problem into consideration, a lot of thinking. Performance issues should be taken seriously.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

A SQL statement that could be a time bomb