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

Editor to share with you what is the use of the LIMIT sentence in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

problem

In order for the story to develop smoothly, we need to have a table:

CREATE TABLE t (id INT UNSIGNED NOT NULL AUTO_INCREMENT, key1 VARCHAR, common_field VARCHAR, PRIMARY KEY (id), KEY idx_key1 (key1)) Engine=InnoDB CHARSET=utf8

Table t contains three columns, the id column is the primary key, and the key1 column is the secondary index column. The table contains 10,000 records.

When we execute the following statement, we use the secondary index idx_key1:

Mysql > EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -- + | 1 | SIMPLE | t | NULL | index | NULL | idx_key1 | 303 | NULL | 1 | 100.00 | NULL | +-+ -+ 1 row in set 1 warning (0.00 sec)

This is easy to understand because in the secondary index idx_key1, the key1 columns are ordered. While the query is to take the first record sorted according to the key1 column, then MySQL only needs to get the first secondary index record from idx_key1, and then directly go back to the table to get the complete record.

However, if we replace LIMIT 1 in the above statement with LIMIT 5000, 1, we need to do a full table scan and filesort. The execution plan is as follows:

Mysql > EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1 +- -+ | 1 | SIMPLE | t | NULL | ALL | NULL | 9966 | 100.00 | Using filesort | + -+-+ 1 row in set 1 warning (0.00 sec)

Some students do not understand: LIMIT 5000, 1 can also use secondary index idx_key1 ah, we can first scan to 5001 secondary index records, 5001 secondary index records back to the table operation, this price is definitely stronger than full table scan + filesort.

I'm sorry to tell you that due to the defects in the implementation of MySQL, the above ideal situation will not occur, it will only be clumsy to perform full table scan + filesort, let's talk about what's going on.

Server layer and storage engine layer

As we all know, MySQL is actually divided into server layer and storage engine layer:

The server layer handles common things such as connection management, SQL syntax parsing, parsing execution plans, and so on.

The storage engine layer is responsible for specific data storage, such as whether the data is stored in a file or in memory, and what the specific storage format is. We basically use the InnoDB storage engine now, and other storage engines use very little, so we don't cover other storage engines.

The execution of a SQL statement in MySQL can only get the final result through multiple interactions between the server layer and the storage engine layer. For example, the following query:

SELECT * FROM t WHERE key1 >'a 'AND key1

< 'b' AND common_field != 'a'; server层会分析到上述语句可以使用下边两种方案执行： 方案一：使用全表扫描 方案二：使用二级索引idx_key1，此时需要扫描key1列值在('a', 'b')之间的全部二级索引记录，并且每条二级索引记录都需要进行回表操作。 server层会分析上述两个方案哪个成本更低，然后选取成本更低的那个方案作为执行计划。然后就调用存储引擎提供的接口来真正的执行查询了。 这里假设采用方案二，也就是使用二级索引idx_key1执行上述查询。那么server层和存储引擎层的对话可以如下所示：

Server layer: "hey, please check the first record in the ('aversion,' b') interval of the idx_key1 secondary index, and then return the complete record to me after returning to the table."

InnoDB: "copy it, check it out". Then InnoDB quickly locates the first second-level index record in the scan interval ('averse,' b') through the B+ tree corresponding to the idx_key1 secondary index, and then returns the complete clustered index record to the server layer.

After receiving the complete clustered index record, the server layer continues to determine whether the commonplace index record is true, if not, discard the record, otherwise send the record to the client. Then he said to the storage engine, "Please give me the next record."

Tips:

Sending the record to the client here is actually sent to the local network buffer, which is controlled by net_buffer_length, and the default is 16KB size. The network packet is not really sent to the client until the buffer is full.

InnoDB: "copy that. I'll check it out." According to the next_record attribute of the record, InnoDB finds the next secondary index record in the ('ajar,' b') interval of idx_key1, and then returns the complete clustered index record to the server layer by returning the table operation.

Tips:

Both clustered index records and secondary index records contain an attribute called next_record, each record is connected into a linked list according to next_record, and the records in the linked list are sorted by key value (for clustered indexes, the key value refers to the value of the primary key, for the secondary index record, the key value refers to the value of the secondary index column).

After receiving the complete clustered index record, the server layer continues to determine whether the commonplace index record is true, if not, discard the record, otherwise send the record to the client. Then he said to the storage engine, "Please give me the next record."

... And then repeat the process over and over again.

Until:

That is, until InnoDB discovers that the next secondary index record obtained from the next_record of the secondary index record is not in the ('averse,' b') range, it says to the server layer, "well, there is no next record in the ('axiom,' b') interval."

The server layer ends the query when it receives a message that InnoDB says there is no next record.

Now you know the basic interaction between the server layer and the storage engine layer.

Then what the heck is LIMIT?

You may be a little surprised to say that MySQL only deals with the contents of the LIMIT clause when the server layer is ready to send records to the client. Take the following sentence as an example:

SELECT * FROM t ORDER BY key1 LIMIT 5000, 1

If you use idx_key1 to execute the above query, MySQL will handle it as follows:

The server layer asks for the first record from InnoDB, and InnoDB gets the first secondary index record from idx_key1, then returns the complete clustered index record to the server layer through table back operation. The server layer is ready to send it to the client. At this time, it is found that there is a requirement of LIMIT 5000, 1, which means that section 5001 of the qualified records can really be sent to the client. So let's do some statistics here. We assume that the server layer maintains a variable called limit_count to count how many records have been skipped, and limit_count should be set to 1.

The server layer asks InnoDB for the next record, and InnoDB finds the next secondary index record according to the next_record attribute of the secondary index record, and returns the complete clustered index record to the server layer. When the server layer sends it to the client, it finds that the limit_count is 1, so it abandons the operation of sending it to the client, increases the limit_count by 1, and the limit_count becomes 2.

... Repeat the above operation

It is not until limit_count is equal to 5000 that the server layer actually sends the complete clustered index record returned by InnoDB to the client.

From the above process, we can see that since MySQL determines whether the LIMIT clause meets the requirements before the record is actually sent to the client, if the query is executed with a secondary index, it means that 5001 table returns will be performed. When analyzing the execution plan, the server layer will feel that the cost of performing so many returns to the table is too high, and it is not as fast as direct full table scan + filesort, so it chooses the latter to execute the query.

What shall I do?

Because of the limitations of MySQL's implementation of the LIMIT clause, is it impossible to speed up the query by using a secondary index when dealing with statements such as LIMIT 5000, 1? In fact, it is not, just rewrite the above sentence as:

SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d WHERE t.id = d.id

In this way, SELECT id FROM t ORDER BY key1 LIMIT 5000, 1 exists separately as a subquery, and since the query list of the subquery has only one id column, MySQL can execute the subquery by scanning only the secondary index idx_key1, and then look it up in table t based on the primary key values obtained in the subquery.

This saves the table operation of the first 5000 records, thus greatly improving the query efficiency!

The above is all the content of the article "what is the use of LIMIT sentences in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.