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

What are the reasons for the slow execution of a SQL statement in Python? I believe many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

To be honest, this question can involve a lot of core knowledge of MySQL, which can be pulled out a lot, just like asking you "what happened after typing URL enter" when you are about to test your knowledge of computer network, and see how much you can say.

The truth of Tencent's interview before also asked this question, but the answer was very bad. I didn't think about the relevant reasons before, so I couldn't pull it out for a while. So today, I'll take you to talk about the reasons in detail. I'm sure you'll get something after reading it!

First, start pretending: classified discussion

A SQL statement executes slowly, but is it slow every time? Or is it normal in most cases and slow occasionally? So I think we also scored the following two situations to discuss.

1. Most cases are normal, but there are occasional slow situations.

2. When the amount of data is constant, this SQL statement has been executed very slowly.

In view of these two situations, let's analyze what may be the cause.

Second, for the occasional slow situation

Most of the conditions of a SQL are normal, but only occasionally it can be very slow. In view of this situation, I think there is nothing wrong with the writing of this SQL statement, but it is caused by other reasons. What is the reason?

1. I have no choice but to refresh the dirty pages in the database.

When we want to insert a piece of data into the database, or update a piece of data, we know that the database will update the data of the corresponding fields in memory, but after the update, these updated fields will not be synchronized and persisted to disk immediately, but these updated records will be written to the redo log diary until free. Synchronize the latest data to disk through the diary in redo log.

However, the capacity in redo log is limited. If the database has been busy and updated frequently, the redo log will soon be full. At this time, there is no way to synchronize the data to the disk when you are free. You can only pause other operations and devote yourself to synchronizing the data to the disk. At this time, it will cause our normal SQL statement to be executed very slowly suddenly. When the database synchronizes the data to disk, it may cause our SQL statements to execute very slowly.

What can I do if I can't get the lock

This is easier to think of, we are going to execute this statement, just this statement involves the table, others are using, and added a lock, we can not get the lock, we can only slowly wait for others to release the lock. Or, the table is not locked, but a row to be used is locked, at this time, there is nothing I can do.

If you want to determine whether you are really waiting for a lock, we can use the show processlist command to check the current status. Here I would like to remind you that some commands had better be recorded. Anyway, I was asked several commands, but I don't know how to write them. Hehe.

Let's visit and analyze the second situation. I think the analysis of the second situation is the most important.

Third, in view of the situation that has been so slow.

If this SQL statement executes so slowly every time with the same amount of data, it's time to consider your SQL writing. Let's analyze what causes our SQL statement to perform poorly.

Let's assume that we have a table with the following two fields, the primary key id, and two normal fields c and d.

Mysql > CREATE TABLE `t` (`id` int (11) NOT NULL, `c` int (11) DEFAULT NULL, `d` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB

1. It hurts my heart and doesn't use the index.

The index is not used. I think this reason can be thought of by many people, for example, if you want to query this statement.

Select * from t where 100

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.