{"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\/it\/blog\/administrirovanie\/ne-stoit-polzovatsya-offset-i-limit-v-zaprosah-s-razbieniem-na-straniczy","title":{"rendered":"Non utilizzare OFFSET e LIMIT nelle query con paginazione","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

I giorni in cui non era necessario preoccuparsi dell'ottimizzazione delle prestazioni dei database sono finiti. Il tempo non si ferma. Ogni nuovo imprenditore nel settore tecnologico desidera creare un nuovo Facebook, cercando di raccogliere tutti i dati a cui riesce ad accedere. Questi dati sono necessari alle imprese per un'inferenza pi\u00f9 accurata dei modelli, che aiutano a generare profitto. In queste condizioni, i programmatori devono creare API che consentano di lavorare rapidamente e in modo affidabile con enormi volumi di informazioni.<\/p>\n

\"Non<\/a><\/noindex>
\n<\/a><\/noindex>
\nSe da un po' di tempo ti occupi della progettazione della parte server delle applicazioni o dei database, probabilmente hai scritto codice per l'esecuzione di query con paginazione. Ad esempio, qualcosa del genere:<\/p>\n

SELECT * FROM table_name LIMIT 10 OFFSET 40\n<\/code><\/pre>\n
\n\u00c8 cos\u00ec?<\/p>\n
Ma se hai eseguito la paginazione in questo modo, posso dire con rammarico che non l'hai fatto nel modo pi\u00f9 efficiente.<\/p>\n
Vuoi contestarmi? Puoi<\/a><\/noindex> non<\/a><\/noindex> spendere<\/a><\/noindex> il tempo<\/a><\/noindex>. Slack<\/a><\/noindex>, Shopify<\/a><\/noindex> e Mixmax<\/a><\/noindex> sono gi\u00e0 in uso tecniche che voglio discutere oggi.<\/p>\n
Nomina almeno un backend developer che non ha mai usato OFFSET<\/code> e LIMIT<\/code> per eseguire query con paginazione. In MVP (Minimum Viable Product, prodotto minimo funzionante) e in progetti dove vengono utilizzati piccoli volumi di dati, questo approccio \u00e8 del tutto applicabile. In un certo senso, 'funziona e basta'.<\/p>\n
Ma se \u00e8 necessario costruire da zero sistemi affidabili ed efficienti, \u00e8 importante occuparsi in anticipo dell'efficienza delle query eseguite sui database utilizzati in tali sistemi.<\/p>\n
Oggi parleremo dei problemi associati alle implementazioni ampiamente diffuse (purtroppo) dei meccanismi per eseguire query con paginazione, e come ottenere alte prestazioni nell'esecuzione di tali query.<\/p>\n
Cosa c'\u00e8 di sbagliato con OFFSET e LIMIT?<\/h2>\n
\nCome gi\u00e0 detto, OFFSET<\/code> e LIMIT<\/code> funzionano perfettamente in progetti in cui non \u00e8 necessario lavorare con grandi volumi di dati.<\/p>\n
Il problema sorge quando il database cresce a tal punto che smette di entrare nella memoria del server. Ma, nel frattempo, \u00e8 necessario eseguire query con paginazione su questo database.<\/p>\n
Affinch\u00e9 questo problema si manifesti, deve verificarsi una situazione in cui il DBMS ricorre a un'operazione inefficiente di scansione completa della tabella (Full Table Scan) per ogni query con paginazione (nel frattempo, possono avvenire operazioni di inserimento e cancellazione di dati, e i dati obsoleti non ci servono!).<\/p>\n
Cosa si intende per 'scansione completa della tabella' (o 'scansione sequenziale della tabella', Sequential Scan)? \u00c8 un'operazione in cui il DBMS legge sequenzialmente ogni riga della tabella, ossia i dati in essa contenuti, e verifica la loro corrispondenza con le condizioni date. \u00c8 noto che questo tipo di scansione \u00e8 il pi\u00f9 lento. Il motivo \u00e8 che viene eseguita molte operazioni di input\/output che coinvolgono il sistema di archiviazione del server. La situazione \u00e8 aggravata dai ritardi associati al lavoro con i dati memorizzati su disco, e il fatto che il trasferimento dei dati dal disco alla memoria \u00e8 un'operazione dispendiosa in termini di risorse.<\/p>\n
Ad esempio, hai registrazioni di 100000000 utenti e stai eseguendo una query con la struttura OFFSET 50000000<\/code>. Questo significa che il DBMS dovr\u00e0 caricare tutte queste registrazioni (e noi non ne abbiamo nemmeno bisogno!), metterle in memoria e solo allora prendere, per esempio, 20 risultati, come indicato in LIMIT<\/code>.<\/p>\n
Diciamo che potrebbe apparire cos\u00ec: 'seleziona righe da 50000 a 50020 da 100000'. Cio\u00e8, per eseguire questa query, il sistema dovr\u00e0 prima caricare 50000 righe. Vedi quante operazioni inutili dovr\u00e0 eseguire?<\/p>\n
Se non ci credi, dai un'occhiata all'esempio che ho creato usando le funzionalit\u00e0 di db-fiddle.com<\/a><\/noindex>.\u00a0<\/p>\n
\"Non
\nEsempio su db-fiddle.com<\/i><\/p>\n
L\u00e0 a sinistra, nel campo Schema SQL<\/code>, c'\u00e8 un codice che inserisce 100000 righe nel database, e a destra, nel campo Query SQL<\/code>, ci sono due query. La prima, lenta, \u00e8 cos\u00ec:<\/p>\n
SELECT *\nFROM `docs`\nLIMIT 10 OFFSET 85000;\n<\/code><\/pre>\n
\nE la seconda, che \u00e8 una soluzione efficiente dello stesso problema, \u00e8 cos\u00ec:<\/p>\n
SELECT *\nFROM `docs`\nWHERE id > 85000\nLIMIT 10;\n<\/code><\/pre>\n
\nPer eseguire queste query, basta premere il pulsante Esegui<\/code> nella parte superiore della pagina. Facendo ci\u00f2, confronteremo i dati sui tempi di esecuzione delle query. Si scopre che l'esecuzione della query inefficiente richiede, almeno, 30 volte pi\u00f9 tempo rispetto all'esecuzione della seconda (da un'esecuzione all'altra, questo tempo varia; ad esempio, il sistema potrebbe comunicare che la prima query richiede 37 ms e la seconda 1 ms).<\/p>\n
E se i dati aumenteranno, tutto apparir\u00e0 ancora peggio (per verificarlo, dai un'occhiata al mio esempio<\/a><\/noindex> con 10 milioni di righe).<\/p>\n
Ci\u00f2 che abbiamo appena discusso dovrebbe darti una certa comprensione di come vengono realmente elaborati le richieste ai database.<\/p>\n
Tieni presente che maggiore \u00e8 il valore OFFSET<\/code> \u2014 pi\u00f9 a lungo ci vorr\u00e0 per eseguire la richiesta.<\/p>\n
Cosa dovresti usare invece della combinazione OFFSET e LIMIT?<\/h2>\n
\nInvece della combinazione OFFSET<\/code> e LIMIT<\/code> dovresti usare una struttura basata su questo schema:<\/p>\n
SELECT * FROM table_name WHERE id > 10 LIMIT 20\n<\/code><\/pre>\n
\nQuesto \u00e8 l'esecuzione di una richiesta con paginazione basata su cursore.<\/p>\n
Invece di memorizzare localmente gli attuali OFFSET<\/code> e LIMIT<\/code> e passarli con ogni richiesta, dovresti memorizzare l'ultima chiave primaria ricevuta (di solito \u00e8 ID<\/code>) e LIMIT<\/code>, e cos\u00ec si otterranno richieste simili a quella sopra citata.<\/p>\n
Perch\u00e9? Il fatto \u00e8 che, specificando esplicitamente l'identificatore dell'ultima riga letta, informi il tuo DBMS su dove iniziare a cercare i dati richiesti. Inoltre, grazie all'uso della chiave, la ricerca sar\u00e0 efficiente, senza che il sistema debba distrarsi su righe al di fuori dell'intervallo specificato.<\/p>\n
Diamo un'occhiata al seguente confronto delle prestazioni di diverse richieste. Ecco una richiesta inefficiente.<\/p>\n
\"Non
\nRichiesta lenta<\/i><\/p>\n
Ecco una versione ottimizzata di questa richiesta.<\/p>\n
\"Non
\nRichiesta veloce<\/i><\/p>\n
Entrambe le richieste restituiscono esattamente lo stesso volume di dati. Ma il primo impiega 12,80 secondi per completarsi, mentre il secondo solo 0,01 secondo. Senti la differenza?<\/p>\n
Problemi potenziali<\/h2>\n
\nPer garantire il funzionamento efficace del metodo proposto per l'esecuzione delle richieste, \u00e8 necessario che nella tabella ci sia una colonna (o colonne) contenente indici univoci e disposti in modo continuo, come un identificatore intero. In alcuni casi specifici, questo pu\u00f2 determinare il successo dell'uso di queste richieste per migliorare la velocit\u00e0 con cui si interagisce con il database.<\/p>\n
Naturalmente, quando si costruiscono le richieste, \u00e8 importante considerare le caratteristiche dell'architettura delle tabelle e scegliere i meccanismi che funzionano meglio sulle tabelle esistenti. Ad esempio, se \u00e8 necessario lavorare con grandi volumi di dati correlati, potrebbe risultarti interessante questo<\/a><\/noindex> articolo.<\/p>\n
Se ci troviamo di fronte al problema dell'assenza di una chiave primaria, ad esempio se c'\u00e8 una tabella con una relazione \"molti-a-molti\", allora l'approccio tradizionale che prevede l'uso di OFFSET<\/code> e LIMIT<\/code>, sar\u00e0 sicuramente adatto. Ma il suo utilizzo pu\u00f2 portare all'esecuzione di richieste potenzialmente lente. In tali casi, consiglierei di utilizzare una chiave primaria con auto-incremento, anche se necessaria solo per organizzare l'esecuzione delle richieste con paginazione.<\/p>\n
Se sei interessato a questo argomento \u2014 ecco<\/a><\/noindex>, ecco<\/a><\/noindex> e ecco<\/a><\/noindex> \u2014 alcuni materiali utili.<\/p>\n
Risultati<\/h2>\n
\nLa principale conclusione che possiamo trarre \u00e8 che, indipendentemente dalle dimensioni dei database di cui si tratta, \u00e8 sempre necessario analizzare la velocit\u00e0 di esecuzione delle richieste. Oggi la scalabilit\u00e0 delle soluzioni \u00e8 estremamente importante, e se dall'inizio del lavoro su un sistema si progetta tutto correttamente, ci\u00f2 pu\u00f2 evitare al programmatore molti problemi in futuro.<\/p>\n
Come analizzi e ottimizzi le richieste ai database?<\/b><\/p>\n
\"Non<\/a><\/noindex>
\n
Fonte: 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\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\udd47Non dovresti usare OFFSET e LIMIT nelle richieste con paginazione | ProHoster","description":"Sono finiti i tempi in cui non era necessario preoccuparsi dell'ottimizzazione delle prestazioni dei database. Il tempo non rimane fermo.","canonical_url":"https:\/\/prohoster.info\/it\/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":"it_IT","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\/it\/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\/it\/wp-json\/wp\/v2\/posts\/91048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=91048"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/91048\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media\/91049"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=91048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=91048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=91048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}