1. The execution speed of SQL statement is slow

The slow execution speed of SQL statements can be divided into two situations for discussion,

1. Most things are normal and occasionally slow
2. The amount of data remains the same and has been very slow

1.1 most cases are normal and occasionally very slow

Database is flushing dirty pages

When we want to insert a piece of data into the database or update a piece of data, we know that the database will be in the databaseMemoryThe data of the corresponding field is updated in,These updated fields will not be synchronized and persisted to disk immediatelyIt’s a record of these updatesWrite it into the redo logWhen idle, synchronize the latest data to thediskGo in.

When the contents of memory data page and disk data page are inconsistent, we call this memory page “dirty page”.

After memory data is written to disk, the contents of memory and data pages on disk are consistent, which is called “clean page”.

There are four scenarios for cleaning dirty pages:

• Redolog is full:The capacity of the redo log is limited. If the database is always busy and updated frequently, the redo log will be full soon. At this time, you can’t synchronize the data to the disk when you are idle. You can only pause other operations and synchronize the data to the disk wholeheartedly,It will cause our normal SQL statement to execute very slowlySo,When the database synchronizes the data to the disk, it may cause our SQL statement execution to be very slow.

• Out of memory:If you query more data at a time, you need to apply for memory when the data page you are looking up is not in memory. When the memory is insufficient, you need to eliminate some memory data pages. If it is a clean page, you can release it directly,If it happens to be a dirty page, you need to brush the dirty page.

• When MySQL thinks the system is “idle”:There’s no pressure on the system.

• When MySQL is closed normally:At this time, MySQL will flush all the dirty pages in the memory to the disk, so that the next time MySQL starts, it can read data directly from the disk, and the startup speed will be very fast.

Executing SQL statement encountered lock!

For example, in the case of concurrent transaction, the executed SQL statement involves table lock and row lock. In this case, you can only wait, resulting in slow SQL statement

1.2 the amount of data remains unchanged and has been very slow

If the execution is very slow every time, you should consider the SQL statement itself

Suppose there are the following table building statements:

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

No index

• Field has no index
  
  For example, if you don’t add an index to the target field, you can only scan the whole table. For example, in the following sentence, you don’t add an index to field C

  select * from t where 100 <c and c < 100000;

• Fields have indexes, but not indexes
  
  In the following SQL statement, although you added an index to C, you did not index it because of the operation:

  select * from t where c - 1 < 100000;

  Similarly, this function operation does not use the index!

  select * from t where pow(c,10) < 100000;

  There is also the following union index. According to the leftmost matching principle, there is no middle name, so you can only scan the whole table

  ALERT TABLE 't' ADD INDEX 'union_index'('a','b','c');
  select * from t where b < 100 and c = 10;

Wrong index selected in database

This work adoptsCC agreementReprint must indicate the author and the link of this article