Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the misunderstandings in the SQL development specification". In the daily operation, I believe that many people have doubts about the misunderstandings in the SQL development specification. The editor consulted all kinds of materials and sorted out the simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "what misunderstandings in the SQL development specification". Next, please follow the editor to study!

1. Suppose a table has two indexes at the same time, and the idx_int_c1,idx_varchar_c2 is: select*from tb where c1q100000 or c2indexes. Which index will be used in this SQL? Why?

Most people choose the first one. In fact, it is wrong to choose any index. Most enterprises will be based on CPU, or cost-based optimization. If according to C1, two conditions are met at the same time, it will choose the front; if only one can be satisfied, it will choose the best, which is the core principle of cost optimization, choose the most suitable state. From the point of view of cost optimization, whoever should be idle will go to work. Not necessarily the fastest, the most important thing is appropriate.

2. Also, how to judge which is the best when SQL is running.

For example, in the figure above: one does not add limit, and the other adds limit. If the overall time is less than 1s, which one is the best? Many people will say that it must be good to add limit. But how to prove it?

Before tuning, do a status refresh on the terminal and execute the two SQL respectively. And then compare the data. For example, the first SQL was a Key before, and then read it 17 times. The second SQL is read 4 times from 1 Key. This shows that the second action is less and the effect is better. Therefore, it is quantifiable to judge whether a SQL is good or bad.

3. The other is about the optimization of delete. Earlier, a person asked for help and said that a delete statement had been executed for more than 2 hours, but it had not been finished yet. Could you roll back the SQL? The answer is no, if you just roll back, you will die even worse.

Evaluate whether an SQL can be rolled back, or to what extent? We all have the same feeling that it would be nice to have a progress bar. But the truth is, SQL didn't. There are a lot of people who are very violent and will restart directly in such a situation. Not rebooting will jam because it blocks later tasks and SQL cannot query.

The author suggests that instead of a violent restart, first execute a "show engine innodb status\ G" statement so that you can see the execution time. If there is no running state, you can Kill it.

At this point, the study of "what are the misunderstandings in the SQL development specification" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.