Database — the reason of slow SQL

Time:2021-1-28

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 -- the reason of slow SQL Database is flushing dirty pagesDatabase -- the reason of slow SQL

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”.

Database -- the reason of slow SQLThere are four scenarios for cleaning dirty pages:

  • Database -- the reason of slow SQL 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.

  • Database -- the reason of slow SQL 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.

Database -- the reason of slow SQLExecuting SQL statement encountered lock!Database -- the reason of slow SQL

Database -- the reason of slow SQLFor 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;

Database -- the reason of slow SQL No indexDatabase -- the reason of slow SQL

  • 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 tableDatabase -- the reason of slow SQL

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

Database -- the reason of slow SQL Wrong index selected in databaseDatabase -- the reason of slow SQL

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

Recommended Today

Go carbon version 1.2.0 has been released to improve and optimize the multi scenario support for orm

Carbon is a lightweight, semantic and developer friendly golang time processing library, which supports chain call, lunar calendar, Gorm, xorm and other mainstream orm If you think it’s good, please give it to a star github:github.com/golang-module/carbon gitee:gitee.com/go-package/carbon Installation and use //Using GitHub Library go get -u github.com/golang-module/carbon import ( “github.com/golang-module/carbon”) //Using gitee Library go get […]