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

Preface

When we use query statements, we often have to return the first few rows or rows of data in the middle. What should we do at this time? Don't worry, mysql already provides us with such a feature.

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force the SELECT statement to return a specified number of records. LIMIT accepts one or two numeric parameters. Parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of record rows returned. The offset of the initial record line is 0 (not 1): for compatibility with PostgreSQL, MySQL also supports syntax: LIMIT # OFFSET #.

So we usually use limit paging when querying data, because this avoids full table query and improves query efficiency. But after a table has more data, the paging query will slow down. Let's take a look at the detailed introduction.

MySQL paging Limit optimization

Create test table card 20 million data

Mysql > select count (*) from card;+-+ | count (*) | +-+ | 20000000 | +-+ 1 row in set (sec)

-first test the query speed of the first 1000 rows

Mysql > select * from card limit 1000510 +-+-- + | card_id | card_number | +-+-- + | 1001 | 13fc90a6-2e3bmur11e8murae62- 9c5c8e6e37cf | | 1002 | 13fc923e-2e3b-11e8-ae62-9c5c8e6e37cf | | 1003 | 13fc93d5-2e3b-11e8-ae62-9c5c8e6e37cf | | 1004 | 13fc956a-2e3b-11e8-ae62-9c5c8e6e37cf | 1005 | 13fc9702-2e3b-11e8-ae62-9c5c8e6e37cf | | 1006 | 13fc9899-2e3b-11e8-ae62-9c5c8e6e37cf | | 1007 | 13fc9a31-2e3b-11e8-ae62-9c5c8e6e37cf | | 1008 | 13fc9bc6-2e3b-11e8-ae62-9c5c8e6e37cf | 1009 | 13fc9d5e-2e3b-11e8-ae62-9c5c8e6e37cf | | 1010 | 13fc9ef5-2e3b-11e8-ae62-9c5c8e6e37cf | +-+ | -+ 10 rows in set (0.00 sec)

-Test queries after 1 million

Mysql > select * from card limit 1000000 Jol 10 +-+-- + | card_id | card_number | +-+-- + | 1000001 | 2d87021aMui 2e3bMue11e8Mueae62- 9c5c8e6e37cf | | 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000010 | 2d871039-2e3b-11e8-ae62-9c5c8e6e37cf | +-+ | -+ 10 rows in set (0.18 sec)

-Test queries after 10 million

Mysql > select * from card limit 10000000010 +-+-+ | card_id | card_number | +-+-+ | 10000001 | b11ad76c-2e49-11e8- Ae62-9c5c8e6e37cf | | 10000002 | b11aefd5-2e49-11e8-ae62-9c5c8e6e37cf | | 10000003 | b11af868-2e49-11e8-ae62-9c5c8e6e37cf | | 10000004 | b11b0031-2e49-11e8-ae62-9c5c8e6e37cf | | 10000005 | b11b07ad-2e49-11e8-ae62-9c5c8e6e37cf | | 10000006 | b11b0f0f-2e49-11e8-ae62-9c5c8e6e37cf | | 10000007 | b11b1669-2e49-11e8-ae62-9c5c8e6e37cf | | 10000008 | b11b1db2-2e49-11e8-ae62-9c5c8e6e37cf | 10000009 | b11b24fa-2e49-11e8-ae62-9c5c8e6e37cf | | 10000010 | b11b2c37-2e49-11e8-ae62-9c5c8e6e37cf | +-| -+-+ 10 rows in set (1.29 sec)

You can see that the later the query, the less efficient it will be. Because when querying data after 1 million, mysql will first query 1 million and 10 pieces of data, and then intercept the next ten pieces of data. This results in a decline in performance.

So how to avoid scanning 1 million pieces of data. We can clearly know that the primary key after 1 million is greater than 1 million. So we can rewrite the sql to use the index to reduce the number of rows scanned

Mysql > select * from card where card_id > = 1000000 limit 10 +-+-- + | card_id | card_number | +-+-- + | 1000000 | 2d870088-2e3bmur11e8murae62- 9c5c8e6e37cf | | 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf | | 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf | +-+ | -+ 10 rows in set (0.00 sec)

This can greatly improve the query efficiency.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.