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

This article mainly introduces "analyzing SQL optimized limit paging delay correlation". In daily operation, I believe many people have doubts in analyzing SQL optimized limit paging delay correlation. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "analyzing SQL optimized limit paging delay correlation". Next, please follow the editor to study!

The most troublesome problem with MySQL paging query is that the offset is very large. For example, if limit 10000dint 20, it is necessary to retrieve 10020 records, return the last 20 values, and discard all the previous 10000 rows. In the case of a large number of retrieval fields, the efficiency is even worse.

SELECT id, order_seq, product_id, user_id, artisan_id, order_price, real_pay, date_format (order_time,'% Y-%m-%d% Hrig% iRu% s') order_time, user_address, STATUS, date_format (pay_time Pay_time, user_contact, coupon_price, coupon_effect_price, order_over_time, product_price, product_trade_price, source_from, create_time Out_channelFROM us_order WHERE (source_from! = 20 OR source_from IS NULL) AND out_channel = 0 ORDER BY id DESC LIMIT 1000000 people 10

This SQL, for example, takes 110s. We need to retrieve 1000010 records, and then take the last 10 records, including nearly 20 fields, which consumes a lot of IO. At the same time, because the execution time of SQL is long and the proportion of CPU time is high, in the case of high concurrency, CPU is likely to be full.

For the SQL itself, the offset of 1000000 cannot be changed, so how can we reduce the number of pages scanned by MySQL to improve query speed?

SELECT id, order_seq, product_id, user_id, artisan_id, order_price, real_pay, date_format (order_time,'% Y-%m-%d% Hrig% iRu% s') order_time, user_address, STATUS, date_format (pay_time Pay_time, user_contact, coupon_price, coupon_effect_price, order_over_time, product_price, product_trade_price, source_from, create_time Out_channel FROM us_order inner join (select id from us_order where (source_from! = 20 OR source_from IS NULL) AND out_channel = 0 ORDER BY id DESC LIMIT 1000000 AND out_channel 10) as aa using (id) WHERE (source_from! = 20 OR source_from IS NULL) AND out_channel = 0 so far The study on "analyzing limit pagination delay association optimized by SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.