Խուսափեք OFFSET-ը և LIMIT-ը էջադրված հարցումներում օգտագործելուց

Անցել են այն ժամանակները, երբ դուք կարիք չունեիք անհանգստանալու տվյալների բազայի աշխատանքի օպտիմալացման մասին: Ժամանակը չի կանգնում: Յուրաքանչյուր նոր տեխնոլոգիական ձեռնարկատեր ցանկանում է ստեղծել հաջորդ Facebook-ը՝ միաժամանակ փորձելով հավաքել այն բոլոր տվյալները, որոնք կարող են ձեռք բերել: Ձեռնարկություններին անհրաժեշտ են այս տվյալները՝ ավելի լավ վարժեցնելու մոդելներին, որոնք օգնում են նրանց գումար վաստակել: Նման պայմաններում ծրագրավորողները պետք է ստեղծեն API-ներ, որոնք թույլ կտան արագ և հուսալի աշխատել հսկայական քանակությամբ տեղեկատվության հետ:

Եթե ​​դուք երկար ժամանակ նախագծել եք հավելվածների կամ տվյալների բազայի հետնամասեր, հավանաբար գրել եք կոդ՝ էջադրված հարցումները գործարկելու համար: Օրինակ, այսպես.

SELECT * FROM table_name LIMIT 10 OFFSET 40

Ինչպիսի՞ն է դա:

Բայց եթե այսպես եք արել ձեր էջադրումը, ապա կներեք, որ դա չեք արել ամենաարդյունավետ ձևով:

Ուզու՞մ ես առարկել ինձ։ Դուք կարող եք ոչ ծախսել ժամանակ. Անգործություն, Shopify и mixmax Նրանք արդեն օգտագործում են այն տեխնիկան, որի մասին ես այսօր ուզում եմ խոսել:

Անվանեք առնվազն մեկ հետին պլանի մշակողի, որը երբեք չի օգտագործել OFFSET и LIMIT էջադրված հարցումներ կատարելու համար: MVP-ում (Minimum Viable Product) և նախագծերում, որտեղ օգտագործվում են փոքր քանակությամբ տվյալներ, այս մոտեցումը բավականին կիրառելի է: Դա «ուղղակի աշխատում է», այսպես ասած:

Բայց եթե Ձեզ անհրաժեշտ է զրոյից հուսալի և արդյունավետ համակարգեր ստեղծել, ապա պետք է նախապես հոգ տանել նման համակարգերում օգտագործվող տվյալների բազաների հարցումների արդյունավետության մասին:

Այսօր մենք կխոսենք էջադրված հարցումների շարժիչների սովորաբար օգտագործվող (շատ վատ) ներդրման խնդիրների մասին և ինչպես հասնել բարձր կատարողականության նման հարցումներ կատարելիս:

Ի՞նչն է սխալ OFFSET-ի և LIMIT-ի հետ:

Ինչպես արդեն ասվեց, OFFSET и LIMIT Նրանք լավ են հանդես գալիս նախագծերում, որոնք մեծ քանակությամբ տվյալների հետ աշխատելու կարիք չունեն:

Խնդիրն առաջանում է, երբ տվյալների բազան այնքան մեծանում է, որ այն այլեւս չի տեղավորվում սերվերի հիշողության մեջ։ Այնուամենայնիվ, այս տվյալների բազայի հետ աշխատելիս դուք պետք է օգտագործեք էջադրված հարցումներ:

Որպեսզի այս խնդիրն ինքն իրեն դրսևորվի, պետք է լինի մի իրավիճակ, երբ DBMS-ը դիմի անարդյունավետ Full Table Scan գործողության յուրաքանչյուր էջանշված հարցումի վրա (մինչդեռ կարող են տեղի ունենալ տեղադրման և ջնջման գործողություններ, և մեզ հնացած տվյալներ պետք չեն):

Ի՞նչ է «սեղանի ամբողջական սկանավորումը» (կամ «սեղանի հաջորդական սկանավորում», հաջորդական սկանավորում): Սա գործողություն է, որի ընթացքում DBMS-ը հաջորդաբար կարդում է աղյուսակի յուրաքանչյուր տող, այսինքն՝ դրանում պարունակվող տվյալները և ստուգում դրանք տվյալ պայմանի համապատասխանության համար: Սեղանի սկանավորման այս տեսակը հայտնի է որպես ամենադանդաղը: Փաստն այն է, որ երբ այն կատարվում է, կատարվում են բազմաթիվ մուտքային/ելքային գործողություններ, որոնք ներառում են սերվերի սկավառակի ենթահամակարգը: Իրավիճակը վատթարանում է սկավառակների վրա պահվող տվյալների հետ աշխատելու հետ կապված հապաղումը, և այն փաստը, որ տվյալների սկավառակից հիշողություն փոխանցելը ռեսուրսների ինտենսիվ գործողություն է:

Օրինակ, դուք ունեք 100000000 օգտվողների գրառումներ և հարցում եք կատարում կառուցվածքով OFFSET 50000000. Սա նշանակում է, որ DBMS-ը պետք է բեռնի այս բոլոր գրառումները (և մեզ նույնիսկ դրանք պետք չեն), տեղադրի դրանք հիշողության մեջ և դրանից հետո վերցնի, ասենք, 20 արդյունք, որը հաղորդվում է: LIMIT.

Ենթադրենք, դա կարող է այսպիսի տեսք ունենալ. «ընտրեք տողերը 50000-ից մինչև 50020 100000-ից»: Այսինքն՝ համակարգը նախ պետք է բեռնի 50000 տող՝ հարցումն ավարտելու համար: Տեսնու՞մ եք, թե որքան ավելորդ աշխատանք է նա ստիպված լինելու անել։

Եթե ​​չեք հավատում ինձ, ապա նայեք իմ ստեղծած օրինակին՝ օգտագործելով գործառույթները db-fiddle.com. 

Օրինակ db-fiddle.com-ում

Այնտեղ, ձախ կողմում, դաշտում Schema SQL, կա կոդ, որը տեղադրում է 100000 տող տվյալների բազա, իսկ աջում՝ դաշտում Query SQL, ցուցադրվում է երկու հարցում։ Առաջինը, դանդաղ, այսպիսի տեսք ունի.

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Իսկ երկրորդը, որը նույն խնդրի արդյունավետ լուծումն է, այսպիսին է.

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Այս հարցումները կատարելու համար պարզապես սեղմեք կոճակը Run էջի վերևում։ Դա անելուց հետո մենք համեմատում ենք հարցումների կատարման ժամանակի մասին տեղեկատվությունը: Պարզվում է, որ անարդյունավետ հարցման կատարումը տևում է առնվազն 30 անգամ ավելի երկար, քան երկրորդի կատարումը (այս ժամանակը տարբերվում է գործարկումից մինչև գործարկում, օրինակ՝ համակարգը կարող է հայտնել, որ առաջին հարցման ավարտը տևել է 37 ms, սակայն կատարման համար երկրորդ - 1 ms):

Իսկ եթե ավելի շատ տվյալներ լինեն, ապա ամեն ինչ ավելի վատ տեսք կունենա (սրանում համոզվելու համար նայեք իմ օրինակ 10 միլիոն տողերով):

Այն, ինչ մենք հենց նոր քննարկեցինք, պետք է ձեզ որոշակի պատկերացում տա, թե ինչպես են իրականում մշակվում տվյալների բազայի հարցումները:

Խնդրում ենք նկատի ունենալ, որ որքան բարձր է արժեքը OFFSET — այնքան ավելի երկար կպահանջվի հայտի ավարտը:

Ի՞նչ պետք է օգտագործեմ OFFSET-ի և LIMIT-ի համակցության փոխարեն:

Համադրության փոխարեն OFFSET и LIMIT Արժե օգտագործել հետևյալ սխեմայով կառուցված կառուցվածքը.

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Սա հարցումների կատարում է կուրսորի վրա հիմնված էջադրումով:

Ներկայիսները տեղում պահելու փոխարեն OFFSET и LIMIT և փոխանցեք դրանք յուրաքանչյուր հարցումով, դուք պետք է պահեք վերջին ստացված հիմնական բանալին (սովորաբար սա է ID) Եվ LIMIT, արդյունքում կստացվեն վերը նշվածի նման հարցումներ։

Ինչո՞ւ։ Բանն այն է, որ բացահայտորեն նշելով ընթերցված վերջին տողի նույնացուցիչը, դուք ասում եք ձեր DBMS-ին, թե որտեղ պետք է սկսի փնտրել անհրաժեշտ տվյալները: Ավելին, որոնումը, բանալու օգտագործման շնորհիվ, կիրականացվի արդյունավետ՝ համակարգը ստիպված չի լինի շեղվել նշված տիրույթից դուրս գտնվող գծերով։

Եկեք նայենք տարբեր հարցումների կատարողականի հետևյալ համեմատությանը: Ահա անարդյունավետ հարցում:

Դանդաղ խնդրանք

Եվ ահա այս հարցման օպտիմիզացված տարբերակը:

Արագ հարցում

Երկու հարցումներն էլ վերադարձնում են նույն քանակությամբ տվյալներ: Բայց առաջինը ավարտելու համար պահանջվում է 12,80 վայրկյան, իսկ երկրորդի համար՝ 0,01 վայրկյան: Զգո՞ւմ եք տարբերությունը։

Հնարավոր խնդիրները

Որպեսզի առաջարկված հարցման մեթոդն արդյունավետ աշխատի, աղյուսակը պետք է ունենա եզակի, հաջորդական ինդեքսներ պարունակող սյունակ (կամ սյունակներ), ինչպիսին է ամբողջ թվի նույնացուցիչը: Որոշ կոնկրետ դեպքերում դա կարող է որոշել տվյալների բազայի հետ աշխատելու արագությունը բարձրացնելու համար նման հարցումների օգտագործման հաջողությունը:

Բնականաբար, հարցումներ կառուցելիս պետք է հաշվի առնել աղյուսակների հատուկ ճարտարապետությունը և ընտրել այն մեխանիզմները, որոնք լավագույնս կաշխատեն առկա աղյուսակների վրա: Օրինակ, եթե Ձեզ անհրաժեշտ է աշխատել մեծ ծավալի առնչվող տվյալների հետ հարցումներում, կարող եք դա հետաքրքիր թվալ սա է հոդված.

Եթե ​​մենք բախվում ենք առաջնային բանալին բաց թողնելու խնդրի հետ, օրինակ, եթե մենք ունենք շատ-շատ հարաբերակցությամբ աղյուսակ, ապա օգտագործման ավանդական մոտեցումը. OFFSET и LIMIT, երաշխավորված է մեզ համապատասխանելու համար։ Բայց դրա օգտագործումը կարող է հանգեցնել պոտենցիալ դանդաղ հարցումների: Նման դեպքերում ես խորհուրդ կտայի օգտագործել ավտոմատ աճող առաջնային բանալին, նույնիսկ եթե այն անհրաժեշտ է միայն էջադրված հարցումները մշակելու համար:

Եթե ​​ձեզ հետաքրքրում է այս թեման - այստեղ, այստեղ и այստեղ - մի քանի օգտակար նյութեր.

Արդյունքները

Հիմնական եզրակացությունը, որ մենք կարող ենք անել, այն է, որ անկախ տվյալների բազայի չափի մասին է խոսքը, միշտ անհրաժեշտ է վերլուծել հարցումների կատարման արագությունը։ Մեր օրերում լուծումների մասշտաբայնությունը չափազանց կարևոր է, և եթե ամեն ինչ ճիշտ նախագծված է որոշակի համակարգի վրա աշխատելու հենց սկզբից, ապա դա ապագայում կարող է ծրագրավորողին փրկել բազմաթիվ խնդիրներից։

Ինչպե՞ս եք վերլուծում և օպտիմիզացնում տվյալների բազայի հարցումները:

Source: www.habr.com