Берилиштер базасынын иштешин оптималдаштыруу жөнүндө тынчсыздануунун кереги жок болгон күндөр артта калды. Убакыт токтобойт. Ар бир жаңы технологиялык ишкер кийинки Facebookту түзүүнү каалайт, ошол эле учурда колунан келген бардык маалыматтарды чогултууга аракет кылат. Бизнеске акча табууга жардам берген моделдерди жакшыраак окутуу үчүн бул маалыматтар керек. Мындай шарттарда программисттер чоң көлөмдөгү маалымат менен тез жана ишенимдүү иштөөгө мүмкүндүк берген API түзүшү керек.
Эгер сиз кайсы бир убакыттан бери тиркеменин же маалымат базасынын бэкенддерин иштеп жаткан болсоңуз, анда сиз пагинацияланган сурамдарды иштетүү үчүн код жазгандырсыз. Мисалы, бул сыяктуу:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Ал кандай?
Бирок, эгер сиз баракчаңызды ушинтип жасаган болсоңуз, кечирим сурайм, сиз муну эң эффективдүү жол менен жасаган жоксуз.
Мага каршы бергиңиз келеби? . , и Алар мен бүгүн айткым келген ыкмаларды колдонуп жатышат.
Эч качан колдонбогон жок дегенде бир иштеп чыгуучуну атаңыз OFFSET и LIMIT беттелген сурамдарды аткаруу үчүн. MVP (Minimum Viable Product) жана аз өлчөмдөгү маалыматтар колдонулган долбоорлордо бул ыкма абдан ылайыктуу. Ал мындайча айтканда, "жөн эле иштейт".
Бирок эгер сиз нөлдөн баштап ишенимдүү жана эффективдүү системаларды түзүшүңүз керек болсо, анда мындай системаларда колдонулган маалымат базаларын суроонун натыйжалуулугу жөнүндө алдын ала кам көрүү керек.
Бүгүн биз пагинацияланган суроо механизмдерин кеңири колдонулган (өтө жаман) ишке ашыруудагы көйгөйлөр жана мындай сурамдарды аткарууда жогорку көрсөткүчтөргө кантип жетсе болору жөнүндө сүйлөшөбүз.
OFFSET жана LIMIT менен эмне туура эмес?
Жогоруда айтылгандай, OFFSET и LIMIT Алар чоң көлөмдөгү маалыматтар менен иштөөнү талап кылбаган долбоорлордо жакшы иштешет.
Көйгөй маалымат базасы сервердин эс тутумуна батпай калгандай чоңойгондо пайда болот. Бирок, бул маалымат базасы менен иштөөдө, сиз пагинацияланган сурамдарды колдонушуңуз керек.
Бул көйгөй өзүн көрсөтүшү үчүн, СББЖ ар бир беттелген суроо боюнча натыйжасыз Толук таблицаны сканерлөө операциясына кайрылган жагдай болушу керек (кыстаруу жана жок кылуу операциялары болушу мүмкүн жана бизге эскирген маалыматтар керек эмес!).
“Толук таблицаны сканерлөө” (же “ырааттуу таблицаны сканерлөө”, ырааттуу сканерлөө) деген эмне? Бул МББС таблицанын ар бир сабын, башкача айтканда андагы маалыматтарды ырааттуу түрдө окуп, алардын берилген шартка ылайык келишин текшерген операция. Таблицаны сканерлөөнүн бул түрү эң жай экени белгилүү. Чындыгында, ал аткарылганда, сервердин дискинин подсистемасы камтылган көптөгөн киргизүү/чыгарма операциялары аткарылат. Кырдаалды дисктерде сакталган маалыматтар менен иштөөгө байланыштуу кечигүү жана дисктен эстутумга маалыматтарды өткөрүү ресурсту көп талап кылган операция болгондугу менен начарлатат.
Мисалы, сизде 100000000 XNUMX XNUMX колдонуучу бар жана сиз конструкция менен суроону иштетесиз OFFSET 50000000. Бул DBMS бул жазуулардын баарын жүктөөгө туура келет (жана алардын бизге кереги да жок!), аларды эс тутумга салып, андан кийин, айталы, 20 натыйжаны алуу керек дегенди билдирет. LIMIT.
Келгиле, мындай болушу мүмкүн дейли: "50000дөн 50020ден 100000га чейинки саптарды тандаңыз". Башкача айтканда, суроону аяктоо үчүн система адегенде 50000 XNUMX сапты жүктөө керек болот. Көрдүңүзбү, ал канчалык керексиз жумушту аткарышы керек?
Эгер мага ишенбесеңиз, функцияларды колдонуу менен мен түзгөн мисалды карап көрүңүз .
Мисал db-fiddle.com сайтында
Ошол жерде, сол жакта, талаада Schema SQL, маалымат базасына 100000 XNUMX саптарды киргизген код бар, ал эми оң жакта талаада Query SQL, эки суроо көрсөтүлөт. Биринчи, жай, төмөнкүдөй көрүнөт:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Ал эми экинчиси, ошол эле маселенин натыйжалуу чечими мындай:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Бул суроо-талаптарды аткаруу үчүн, жөн гана баскычты чыкылдатыңыз Run беттин жогору жагында. Муну аткаргандан кийин, биз суроонун аткарылуу убактысы жөнүндө маалыматты салыштырабыз. Көрсө, натыйжасыз суроону аткаруу экинчисин аткарууга караганда, жок эле дегенде, 30 эсе көп убакытты талап кылат (бул убакыт иштетилгенден башкага чейин өзгөрүп турат; мисалы, система биринчи суроону аяктоо үчүн 37 мс талап кылынышы мүмкүн, бирок экинчи - 1 мс).
Эгер көбүрөөк маалымат болсо, анда баары андан да жаман көрүнөт (буга ынануу үчүн, менин 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