{"id":91048,"date":"2020-08-08T01:42:02","date_gmt":"2020-08-07T23:42:02","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy"},"modified":"2020-08-08T01:42:02","modified_gmt":"2020-08-07T23:42:02","slug":"ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","status":"publish","type":"post","link":"https:\/\/prohoster.info\/da\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","title":{"rendered":"Det er ikke en god id\u00e9 at bruge OFFSET og LIMIT i foresp\u00f8rgsler med paginering","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

De dage, hvor man ikke beh\u00f8vede at bekymre sig om optimering af databaseydelse, er forbi. Tiden st\u00e5r ikke stille. Hver ny iv\u00e6rks\u00e6tter inden for teknologi \u00f8nsker at skabe det n\u00e6ste Facebook og str\u00e6ber efter at indsamle alle de data, de kan f\u00e5 fat i. Disse data er afg\u00f8rende for at forbedre modellerne, som hj\u00e6lper med at generere indt\u00e6gter. Under s\u00e5danne forhold er det n\u00f8dvendigt for udviklere at skabe API'er, der g\u00f8r det muligt at arbejde hurtigt og p\u00e5lideligt med store m\u00e6ngder information.<\/p>\n

\"Det<\/a><\/noindex>
\n<\/a><\/noindex>
\nHvis du i et stykke tid har arbejdet med design af serverkomponenter til applikationer eller databaser, har du sikkert skrevet kode til at udf\u00f8re foresp\u00f8rgsler med paginering. For eksempel s\u00e5dan her:<\/p>\n

SELECT * FROM table_name LIMIT 10 OFFSET 40\n<\/code><\/pre>\n
\nEr det rigtigt?<\/p>\n
Men hvis du har udf\u00f8rt paginering p\u00e5 netop den m\u00e5de, m\u00e5 jeg desv\u00e6rre sige, at du ikke har gjort det p\u00e5 den mest effektive m\u00e5de.<\/p>\n
Vil du udfordre mig? Du kan<\/a><\/noindex> ikke<\/a><\/noindex> bruge<\/a><\/noindex> tidsstempel<\/a><\/noindex>. Slack<\/a><\/noindex>, Shopify<\/a><\/noindex> og Mixmax<\/a><\/noindex> bruger allerede de teknikker, jeg vil tale om i dag.<\/p>\n
N\u00e6vn bare \u00e9n backend-udvikler, der aldrig har brugt OFFSET<\/code> og LIMIT<\/code> til at udf\u00f8re foresp\u00f8rgsler med paginering. I et MVP (Minimum Viable Product) og i projekter, hvor der bruges sm\u00e5 datam\u00e6ngder, er denne tilgang helt anvendelig. Den, s\u00e5 at sige, \"bare virker\".<\/p>\n
Men hvis du skal skabe p\u00e5lidelige og effektive systemer fra bunden, er det vigtigt at t\u00e6nke p\u00e5 effektiviteten af foresp\u00f8rgsler til de databaser, der bruges i s\u00e5danne systemer.<\/p>\n
I dag vil vi tale om de problemer, der er forbundet med de udbredte (desv\u00e6rre) implementeringer af mekanismer til at udf\u00f8re foresp\u00f8rgsler med paginering, og hvordan man kan opn\u00e5 h\u00f8j ydeevne, n\u00e5r man udf\u00f8rer s\u00e5danne foresp\u00f8rgsler.<\/p>\n
Hvad er galt med OFFSET og LIMIT?<\/h2>\n
\nSom allerede n\u00e6vnt, OFFSET<\/code> og LIMIT<\/code> fungerer glimrende i projekter, hvor man ikke skal arbejde med store datam\u00e6ngder.<\/p>\n
Problemet opst\u00e5r, n\u00e5r databasen vokser til en s\u00e5dan st\u00f8rrelse, at den ikke l\u00e6ngere kan rumme i serverens hukommelse. Men samtidig er det n\u00f8dvendigt at bruge foresp\u00f8rgsler med paginering for at arbejde med denne database.<\/p>\n
For this problem to manifest, a situation must arise where the DBMS resorts to inefficient full table scan operations when executing each paginated query (at the same time, there may be operations for inserting and deleting data, and we do not need outdated data!).<\/p>\n
What is a \"full table scan\" (or \"sequential table scan\")? This is an operation where the DBMS sequentially reads each row of the table, that is, the data contained within it, and checks it against specified conditions. It is known that this type of table scanning is the slowest. The reason is that it involves a lot of input\/output operations affecting the server's disk subsystem. The situation is worsened by the delays associated with working with data stored on disks and the fact that transferring data from disk to memory is a resource-intensive operation.<\/p>\n
For example, you have records of 100,000,000 users, and you execute a query with the clause OFFSET 50,000,000<\/code>. This means that the DBMS will have to load all these records (which we do not even need!), place them in memory, and only after that retrieve, say, 20 results as stated in the LIMIT<\/code>.<\/p>\n
Let's say it might look like this: \"select rows from 50,000 to 50,020 out of 100,000\". So, the system will first need to load 50,000 rows to execute the query. Do you see how much unnecessary work it has to perform?<\/p>\n
If you don't believe it, take a look at the example I created using the capabilities of db-fiddle.com<\/a><\/noindex>.\u00a0<\/p>\n
\"Det
\nExample on db-fiddle.com<\/i><\/p>\n
There, on the left, in the Schema SQL<\/code>, there is code that inserts 100,000 rows into the database, and on the right, in the Query SQL<\/code>, two queries are shown. The first, slow one, looks like this:<\/p>\n
SELECT *\nFROM `docs`\nLIMIT 10 OFFSET 85,000;\n<\/code><\/pre>\n
\nAnd the second, which represents an efficient solution to the same task, looks like this:<\/p>\n
SELECT *\nFROM `docs`\nWHERE id > 85,000\nLIMIT 10;\n<\/code><\/pre>\n
\nTo execute these queries, it is enough to click the button K\u00f8r<\/code> \u00f8verst p\u00e5 siden. N\u00e5r det er gjort, sammenligner vi oplysningerne om foresp\u00f8rgsels execution tid. Det viser sig, at en ineffektiv foresp\u00f8rgsel tager mindst 30 gange l\u00e6ngere tid end at udf\u00f8re den anden (fra k\u00f8rsel til k\u00f8rsel varierer denne tid, for eksempel kan systemet rapportere, at den f\u00f8rste foresp\u00f8rgsel tog 37 ms, mens den anden tog 1 ms).<\/p>\n
Og hvis dataene bliver st\u00f8rre, vil det se endnu v\u00e6rre ud (for at overbevise dig selv \u2014 se p\u00e5 min eksempel<\/a><\/noindex> med 10 millioner r\u00e6kker).<\/p>\n
Det, vi lige har diskuteret, skal give dig en vis forst\u00e5else af, hvordan foresp\u00f8rgsler til databaser faktisk bliver behandlet.<\/p>\n
T\u00e6nk p\u00e5, at jo st\u00f8rre v\u00e6rdien er, OFFSET<\/code> jo l\u00e6ngere tid vil foresp\u00f8rgslen tage.<\/p>\n
Hvad skal man i stedet bruge for kombinationen OFFSET og LIMIT?<\/h2>\n
\nI stedet for kombinationen OFFSET<\/code> og LIMIT<\/code> skal du bruge en konstruktion bygget efter denne skabelon:<\/p>\n
SELECT * FROM table_name WHERE id > 10 LIMIT 20\n<\/code><\/pre>\n
\nDette er en udf\u00f8relse af foresp\u00f8rgslen med paginering baseret p\u00e5 en cursor (Cursor based pagination).<\/p>\n
I stedet for at gemme de aktuelle OFFSET<\/code> og LIMIT<\/code> og overf\u00f8re dem med hver foresp\u00f8rgsel, skal du gemme den sidst modtagne prim\u00e6re n\u00f8gle (normalt er dette ID<\/code>) og LIMIT<\/code>, hvilket resulterer i foresp\u00f8rgsler, der ligner den ovenfor n\u00e6vnte.<\/p>\n
Hvorfor? Sagen er, at n\u00e5r du eksplicit angiver identifieren for den sidst l\u00e6ste r\u00e6kke, fort\u00e6ller du din DBMS, hvor den skal begynde at s\u00f8ge efter de n\u00f8dvendige data. Og s\u00f8gningen vil, takket v\u00e6re brugen af n\u00f8glen, blive udf\u00f8rt effektivt, systemet skal ikke forstyrres af r\u00e6kker uden for det angivne interval.<\/p>\n
Lad os tage et kig p\u00e5 f\u00f8lgende sammenligning af ydeevnen af forskellige foresp\u00f8rgsler. Her er en ineffektiv foresp\u00f8rgsel.<\/p>\n
\"Det
\nLangsom foresp\u00f8rgsel<\/i><\/p>\n
Og her er en optimeret version af denne foresp\u00f8rgsel.<\/p>\n
\"Det
\nHurtig foresp\u00f8rgsel<\/i><\/p>\n
Begge foresp\u00f8rgsler returnerer pr\u00e6cist det samme datavolumen. Men den f\u00f8rste tager 12,80 sekunder, mens den anden tager 0,01 sekunder. Kan du m\u00e6rke forskellen?<\/p>\n
Mulige problemer<\/h2>\n
\nFor the proposed method of executing queries to work effectively, the table must have a column (or columns) containing unique, sequentially arranged indices, such as an integer identifier. In some specific cases, this can determine the success of such queries aimed at enhancing database performance.<\/p>\n
Naturally, when constructing queries, one must consider the characteristics of the table's architecture and choose mechanisms that will perform best with the existing tables. For example, if you need to work with large volumes of related data in queries, you might find this interesting denne<\/a><\/noindex> article.<\/p>\n
If we are faced with the problem of lacking a primary key, for instance, if there is a table with a many-to-many relationship, then the traditional approach involving OFFSET<\/code> og LIMIT<\/code>, will certainly suit us. However, its application may lead to potentially slow queries. In such cases, I would recommend using a primary key with auto-increment, even if it is only needed for organizing paginated query execution.<\/p>\n
If you are interested in this topic \u2014 her<\/a><\/noindex>, her<\/a><\/noindex> og her<\/a><\/noindex> \u2014 here are several useful materials.<\/p>\n
Resum\u00e9<\/h2>\n
\nThe main conclusion we can draw is that, regardless of the size of the databases in question, it is essential to analyze query execution speed. In our time, the scalability of solutions is extremely important, and if everything is designed correctly from the very beginning of building a system, it can save developers from many headaches in the future.<\/p>\n
How do you analyze and optimize database queries?<\/b><\/p>\n
\"Det<\/a><\/noindex>
\n
Kilde: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u041f\u0440\u043e\u0448\u043b\u0438 \u0442\u0435 \u0434\u043d\u0438, \u043a\u043e\u0433\u0434\u0430 \u043d\u0435 \u043d\u0430\u0434\u043e \u0431\u044b\u043b\u043e \u0431\u0435\u0441\u043f\u043e\u043a\u043e\u0438\u0442\u044c\u0441\u044f \u043e\u0431 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445. \u0412\u0440\u0435\u043c\u044f \u043d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043d\u0430 \u043c\u0435\u0441\u0442\u0435. \u041a\u0430\u0436\u0434\u044b\u0439 \u043d\u043e\u0432\u044b\u0439 \u0431\u0438\u0437\u043d\u0435\u0441\u043c\u0435\u043d \u0438\u0437 \u0441\u0444\u0435\u0440\u044b \u0432\u044b\u0441\u043e\u043a\u0438\u0445 \u0442\u0435\u0445\u043d\u043e\u043b\u043e\u0433\u0438\u0439 \u0445\u043e\u0447\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043e\u0447\u0435\u0440\u0435\u0434\u043d\u043e\u0439 Facebook, \u0441\u0442\u0440\u0435\u043c\u044f\u0441\u044c \u043f\u0440\u0438 \u044d\u0442\u043e\u043c \u0441\u043e\u0431\u0438\u0440\u0430\u0442\u044c \u0432\u0441\u0435 \u0434\u0430\u043d\u043d\u044b\u0435, \u0434\u043e \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043c\u043e\u0436\u0435\u0442 \u0434\u043e\u0442\u044f\u043d\u0443\u0442\u044c\u0441\u044f. \u042d\u0442\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0443\u0436\u043d\u044b \u0431\u0438\u0437\u043d\u0435\u0441\u0443 \u0434\u043b\u044f \u0431\u043e\u043b\u0435\u0435 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0433\u043e \u043e\u0431\u0443\u0447\u0435\u043d\u0438\u044f \u043c\u043e\u0434\u0435\u043b\u0435\u0439, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043f\u043e\u043c\u043e\u0433\u0430\u044e\u0442 \u0437\u0430\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u0442\u044c. \u0412 \u0442\u0430\u043a\u0438\u0445 \u0443\u0441\u043b\u043e\u0432\u0438\u044f\u0445 \u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442\u0430\u043c […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":91049,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-91048","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Avoid using OFFSET and LIMIT in paginated queries | ProHoster","description":"Gone are the days when performance optimization of databases was not a concern. Time does not stand still.","canonical_url":"https:\/\/prohoster.info\/da\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"da_DK","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c\u0441\u044f OFFSET \u0438 LIMIT \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u0445 \u0441 \u0440\u0430\u0437\u0431\u0438\u0435\u043d\u0438\u0435\u043c \u043d\u0430 \u0441\u0442\u0440\u0430\u043d\u0438\u0446\u044b | ProHoster","og:description":"\u041f\u0440\u043e\u0448\u043b\u0438 \u0442\u0435 \u0434\u043d\u0438, \u043a\u043e\u0433\u0434\u0430 \u043d\u0435 \u043d\u0430\u0434\u043e \u0431\u044b\u043b\u043e \u0431\u0435\u0441\u043f\u043e\u043a\u043e\u0438\u0442\u044c\u0441\u044f \u043e\u0431 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445. \u0412\u0440\u0435\u043c\u044f \u043d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043d\u0430 \u043c\u0435\u0441\u0442\u0435.","og:url":"https:\/\/prohoster.info\/da\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-08-07T23:42:02+00:00","article:modified_time":"2020-08-07T23:42:02+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"91048","title":null,"description":null,"keywords":null,"keyphrases":{"focus":[],"additional":[]},"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 12:35:24","updated":"2026-08-11 12:50:11","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts\/91048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/comments?post=91048"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts\/91048\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media\/91049"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media?parent=91048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/categories?post=91048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/tags?post=91048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}