Макаланы түшүнүү үчүн талап кылынган бorм деңгээли: маалымат базалары жана SQL жөнүндө жалпы түшүнүк, DBMS менен кээ бир практикалык тажрыйба.
Натыйжалуу SQL сурамдарын жазууну үйрөнө ала турган эң маанилүү нерсе - бул индекстөө. Бирок, экинчи орунда, өтө жакын артта, көптөгөн SQL кардарлары маалымат базасынан көптөгөн "керексиз, бирок зарыл иштерди" талап кылаарын билүү . Менден кийин кайтала:
Керексиз, бирок талап кылынган иш
«Керексиз, бирок милдеттүү иш» деген эмне? Капитан Обвиус бизге айткандай, ал:
Керексиз
Биздин кардар колдонмосуна төмөнкү маалыматтар керек болсун:
адаттан тыш эч нерсе жок. Биз тасмалар базасы менен иштеп жатабыз (мисалы, Sakila маалымат базасы ) жана колдонуучуларга бардык тасмалардын аталышын жана рейтингин көрсөтүүнү каалайбыз. Төмөнкү суроо бизге керектүү жыйынтыкты бере алат:
SELECT title, rating
FROM film
Бирок, биздин колдонмо (же биздин ORM) анын ордуна бул суроону аткарат:
SELECT *
FROM film
Натыйжада биз эмне алабыз? Guess. Биз көптөгөн пайдасыз маалыматтарды алабыз:
Оң жакта сиз кээ бир татаал JSON жүктөлүп жатканын көрө аласыз:
дисктен
кэшке
зым менен
кардардын эсинде
жана акыры ыргытылган [керексиз]
Ооба, биз бул маалыматтын көбүн ыргытабыз. Бул маалыматты алуу үчүн жасалган бардык иш-аракеттер таптакыр пайдасыз болуп чыкты. Бул чынбы? Чынбы.
Милдеттүү
Ал эми азыр - эң жаман бөлүгү. Оптимизаторлор азыр көп нерсени жасай алышат да, бул аракеттер маалымат базасы үчүн милдеттүү болуп саналат. Маалыматтар базасы кардар тиркемесинде бул маалыматтардын 95% керек эмес экенин биле алbyte. Жана бул эң жөнөкөй эле мисал. Бир нече tableларды туташтырууну элестетиңиз... Анда эмне дейсиз, бирок маалымат базалары тез? Сиз ойлобогон кээ бир нерселер тууралуу айтып берейин. Албетте, жеке суроо-талаптын аткарылышы эч нерсеге таасир этпейт. Макул, ал бир жарым эсе жайыраак иштеди, бирок биз аны жеңебиз, туурабы? Ыңгайлуулук үчүн? Кээде бул чындык. Бирок, сиз ар дайым ыңгайлуулук үчүн аткарууну курмандыкка чалсаңыз , бул кичинекей нерселер кошула баштайт. Биз мындан ары аткаруу (жеке суроо-талаптарды аткаруу ылдамдыгы) жөнүндө эмес, өткөрүү жөндөмдүүлүгү (системанын жооп берүү убактысы) жөнүндө сөз кылабыз, андан кийин чечүү оңой эмес олуттуу көйгөйлөр башталат. Мына ошондо сиз масштабдуулукту жоготосуз. Келгиле, аткаруу пландарын карап көрөлү, бул учурда, Oracle DBMS:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|166K||1| TABLE ACCESS FULL| FILM |1000|166K|--------------------------------------------------
ага салыштырмалуу:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|20000||1| TABLE ACCESS FULL| FILM |1000|20000|--------------------------------------------------
SELECT аталышынын ордуна SELECT * суроосун иштетүү, рейтинг маалымат базасында 8 эсе көп эстутумду колдонот. Күтүлбөгөн эч нерсе жок, туурабы? Биз мындай болорун билгенбиз. Бирок биз бул маалыматтардын бардыгын талап кылбаган көптөгөн өтүнүчтөрүбүзгө дагы эле макулбуз. Биз маалымат базасы үчүн керексиз, бирок милдеттүү иштерди түзөбүз , ал улам-улам үйүлүп, үйүлүп турат. Керектүүдөн 8 эсе көп эстутумду колдонобуз (албетте, мультипликатор өзгөрөт). Ошол эле учурда, башка бардык этаптарда (дискти киргизүү/чыгаруу, тармак аркылуу берorштерди өткөрүү, кардар эстутумду керектөө) көйгөйлөр так ошондой, бирок мен аларды өткөрүп жиберип, анын ордуна карап чыгам...
Индекстерди колдонуу
Бүгүнкү күндө көпчүлүк маалымат базалары индекстерди жабуу түшүнүгүн баалашкан . Жабуучу индекс өзү индекстин өзгөчө түрү эмес. Бирок ал белгилүү бир суроо үчүн "өзгөчө индекс" болуп чыгышы мүмкүн, же "кокустан" же ушундай болушу үчүн арналган. Төмөнкү суроону карап көрүңүз:
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
Аны ишке ашыруу жагынан күтүүсүз эч нерсе жок. Бул жөнөкөй өтүнүч. Диапазонду индекс боюнча көрүңүз, tableга кириңиз - жана бүттүңүз:
-------------------------------------------------------------------|Id|Operation|Name|Rows|-------------------------------------------------------------------|0| SELECT STATEMENT ||8||1| TABLE ACCESS BY INDEX ROWID| ACTOR |8||*2| INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME |8|-------------------------------------------------------------------
Жакшы план, туурабы? Ооба, эгер бизге бул чындап керек болсо, анда жок:
Албетте, биз эстутумду коротуп жатабыз ж.б. Бул суроону альтернатива катары карап көрөлү:
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
Анын планы мындай:
----------------------------------------------------|Id|Operation|Name|Rows|----------------------------------------------------|0| SELECT STATEMENT ||8||*1| INDEX RANGE SCAN| IDX_ACTOR_NAMES |8|----------------------------------------------------
Биздин сурообуздун бардык муктаждыктарын канааттандырган индекстин болушунун аркасында tableга кирүү мүмкүнчүлүгүн толугу менен жок кыла алдык... жабуу индекси. Бул маанилүүбү? Анан кантип! Бул ыкма кээ бир сурамдарды чоңдук тартиби боюнча тездетүүгө (же индекс кээ бир өзгөрүүлөрдөн кийин камтылбай калганда аларды чоңдук тартиби менен жайлатууга) мүмкүндүк берет. Камтуу индекстерин дайыма колдонуу мүмкүн эмес. Сиз индекстер үчүн төлөшүңүз керек жана аларды өтө көп кошпоңуз. Бирок бул учурда баары ачык-айкын көрүнүп турат. Келгиле, аткарууну баалайлы:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER :=100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 1 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Улучшенный request, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 2 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Оптимальный request: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 3 : '||(SYSTIMESTAMP - v_ts));
END;/
Натыйжада, биз алабыз:
Оператор 1: +000000000 00:00:02.479000000
Оператор 2: +000000000 00:00:02.261000000
Оператор 3: +000000000 00:00:01.857000000
Актёр tableсында 4 гана тилке бар экенин эске алыңыз, андыктан 1 жана 2 билдирүүлөрүнүн ортосундагы аткаруу айырмасы анчалык деле чоң эмес, бирок дагы эле маанилүү. Оптимизатор суроо үчүн тигил же бул конкреттүү индексти тандоосу үчүн Oracle оптимизаторунун кеңештерин колдонгонумду да белгилейм. Оператор 3 биздин жарыштын талашсыз жеңүүчүсү. Анын иштеши алда канча жакшы, биз өтө жөнөкөй суроо жөнүндө сөз болуп жатат. Дагы бир жолу, биз SELECT * жазганда, биз маалымат базасы үчүн керексиз, бирок ал оптималдаштыра албаган милдеттүү иштерди жасайбыз. Ал жабуу индексин тандаbyte, анткени ал өзү тандаган LAST_NAME индексинен бир аз жогорураак жана башка нерселер менен бирге, мисалы, пайдасыз LAST_UPDATE тилкесин алуу үчүн дагы эле tableга кирүү керек. Бирок биз SELECT * канчалык тереңирээк талдасак, нерселер ошончолук жаман болуп чыгат. жөнүндө сүйлөшөлү...
CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
Бул көрүнүш жөн гана "...-бирге" бардык кардарлардын tableсы менен алардын даректеринин бөлүктөрү үчүн ар кандай tableлардын ортосундагы биригүүлөрдү жасайт. Рахмат, нормалдаштыруу. Элестеткиле, бул көрүнүш менен бир аз иштегенден кийин, биз ага көнүп, анын астындагы үстөлдөрдү унутуп калдык. Эми биз төмөнкү суроону аткарабыз:
SELECT *
FROM v_customer
Натыйжада, биз абдан таасирдүү планды алабыз:
----------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|----------------------------------------------------------------|0| SELECT STATEMENT ||599|47920|14||*1| HASH JOIN ||599|47920|14||2| TABLE ACCESS FULL | COUNTRY |109|1526|2||*3| HASH JOIN ||599|39534|11||4| TABLE ACCESS FULL | CITY |600|10800|3||*5| HASH JOIN ||599|28752|8||6| TABLE ACCESS FULL| CUSTOMER |599|11381|4||7| TABLE ACCESS FULL| ADDRESS |603|17487|3|----------------------------------------------------------------
Жакшы Албетте. Берorштер базасы бул кошулмаларды жана толук tableны сканерлөөдө, анткени биз ага ушундай кылууну айттык - бул маалыматтардын баарын алып келиңиз. Эми, дагы бир жолу, биз чындап эле бул керек экенин элестетиңиз:
Эмне, чындап эле, туурабы? Эми эмне жөнүндө айтып жатканымды түшүнө баштадыңыз. Бирок биз мурунку каталардан бир нерсе үйрөндүк деп элестетип, бул оптималдуу суроону аткарыңыз:
Аткаруу жагынан жакшы жакка кескин өзгөрүүлөр. Кошулуулар жок кылынды, анткени оптимализатор азыр алардын пайдасыз экенин көрө алат жана эгер ал муну көрө алса (жана сиз * тандоо менен бул ишти милдеттүү кылбасаңыз), анда ал бул иштин баарын аткара алbyte. Эмне үчүн бул учурда ушундай? ADDRESS.ADDRESS_ID негизги ачкычына CUSTOMER.ADDRESS_ID тышкы ачкычы акыркысынын так бир маанисине кепилдик берет, бул JOIN операциясы катарлардын санын көбөйтпөй же азайтпай турган "...-бирге" кошулуу болот дегенди билдирет. . Жана биз эч кандай саптарды такыр тандабайбыз же талап кылбайбыз, демек, аларды жүктөөнүн эч кандай мааниси жок. JOINди алып салуу суроонун натыйжасына такыр таасир этпейт. Маалымат базалары муну ар дайым жасайт. Сиз дээрлик бардык маалыматтар базасында төмөнкү суроону иштете аласыз:
--Oracle
SELECT CASE WHEN EXISTS (
SELECT 1/0FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (SELECT 1/0)
Бул учурда, сиз төмөнкү суроону аткаргандагыдай арифметикалык өзгөчөлүктү күтүшүңүз мүмкүн:
SELECT 1/0 FROM dual
Болду:
ORA-01476: бөлүүчү нөлгө барабар
Бирок бул болбойт. Оптимизатор (же атүгүл талдоочу) EXISTS предикатында эч кандай тандалган тизме элементтери (SELECT ..) сурамдын натыйжасын өзгөртпөсүн камсыздай алат, андыктан аны аткаруунун кереги жок. Бул сыяктуу!
Ошол эле учурда...
ORM менен болгон эң тажатма көйгөйлөрдүн бири - бул SELECT * сурамдарын жазуу оңой. Чынында, мисалы, HQL / JPQLде алар көбүнчө демейки боюнча колдонулат. Биз SELECT пунктун таптакыр өткөрүп жиберсек болот, анткени биз бүтүндөй an objectти алып чыгабыз, туурабы? Мисалы:
FROM v_customer
Мисалы, Vlad Mihalcea, эксперт жана Hibernate менен иштеп чыгуунун жактоочусу , сиз текшерүүдөн кийин эч кандай өзгөртүүлөрдү сактап калгыңыз келбегениңизге ишенсеңиз, дээрлик ар дайым [квалификациялуу] сурамдарды колдонууну сунуштайт. ORMs an objectтик графиктердин туруктуулугу маселесин чечүүгө чоң жардам берет. Эскертүү: туруктуулук. Объектилердин графиктерин чындыгында өзгөртүү жана өзгөртүүлөрдү сактоо милдеттери бири-бири менен тыгыз байланышта. Бирок, эгерде сиз муну кылбай турган болсоңуз, анда эмне үчүн маңызын чыгарып убара? Эмне үчүн [такталган] өтүнүч жазууга болбосун? Ачык айталы: аткаруунун көз карашынан алганда, сиздин конкреттүү колдонуу жагдайыңызга ылайыкташтырылган суроо жазуу, албетте, башка варианттарга караганда жакшыраак. Сиздин маалымат топтомуңуз кичинекей болгондуктан, сизге маани бербеши мүмкүн жана бул маанилүү эмес. Абдан жакшы. Бирок, акыры масштабдуулук керек болгондо, an object графигин императивдик айланып өтүүнүн ордуна сурамдарды колдонуу үчүн колдонмолоруңузду кайра иштеп чыгуу абдан кыйынга турат. А сизде ансыз бир нерсе болот.
бир нерсе бар же жок экенин билүү үчүн саптарды санап
Ресурстарды эң жаман коротуунун бири бул маалымат базасында бир нерсе бар же жок экенин билүү үчүн COUNT(*) суроону иштетүү. Мисалы, биз берилген колдонуучунун дегеле буйрутмалары бар же жок экенин бorшибиз керек. Жана биз өтүнүчтү аткарабыз:
SELECTcount(*)
FROM orders
WHERE user_id =:user_id
Башталгыч. Эгерде COUNT = 0 болсо, анда эч кандай буйрук жок. Болбосо, ооба. ORDERS.USER_ID тилкесинде индексибиз бар болгондуктан, аткаруу анчалык деле жаман болбойт. Бирок сиздин оюңузча, жогорудагы сурамдын аткарылышы төмөнкү вариантка кандай салыштырылат:
--Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
)
Чыныгы бар болуу предикаты биринчисин табаар замат кошумча саптарды издөөнү токтоторун аныктоо үчүн ракета orмпозунун кереги жок . Демек, натыйжа "буйрук жок" болуп чыкса, анда ылдамдык салыштырууга болот. Бирок, натыйжа "ооба, буйруктар бар" болсо, анда так санды эсептөөнүн кереги жок болгон учурда, жооп тезирээк кабыл алынат . Анткени, так саны бизди кызыктырbyte. Бирок, биз маалымат базасына аны эсептөөнү айттык ( керексиз жумуш ) жана маалымат базасы 1ден жогору бардык натыйжаларды этибарга албай жатканыбызды билбейт ( талап кылынган иш ). Албетте, ошол эле натыйжаларга жетүү үчүн JPA тарабынан колдоого алынган коллекцияда list.size() деп атасак, андан да жаман болмок. Мен бул тууралуу мурун эле блогумда жазганмын жана эки вариантты тең салыштырып сынап көргөм...
Корутунду
Бул макалада ачык айтылган. Базаны керексиз, бирок талап кылынган иштерди жасоого мажбурлабаңыз . Бул кереги жок , анткени талаптарды эске алуу менен, кандайдыр бир конкреттүү ишти аткаруунун кереги жок экенин билесиз. Бирок, сиз маалымат базасына муну айтыңыз. Бул талап кылынат, анткени маалымат базасы бул иштин кереги жок экенине кепилдик бере алbyte . Бул маалымат кардар үчүн гана жеткorктүү жана serverге жеткorктүү эмес. Ошентип, маалымат базасы аны аткарышы керек. Макала SELECT * боюнча багытталган, анткени ал карап чыгууга ыңгайлуу an object. Бирок бул маалымат базаларына гана тиешелүү эмес. Бул кардар serverге керексиз, бирок талап кылынган ишти аткарууну айткан бардык бөлүштүрүлгөн алгоритмдерге тиешелүү . Орточо AngularJS тиркемеңизде канча N+1 тапшырмасы бар, анда UI B кызматына бир нече жолу чалуу А кызматынын натыйжасы аркылуу өтүп, В кызматына бардык чалууларды бир чалууга чогулткандын ордуна? Бул абдан кеңири таралган көрүнүш. Чечим ар дайым бирдей. Буйруктарыңызды аткарган an objectке канчалык көп маалымат берсеңиз, ал (теориялык жактан) ошол буйруктарды ошончолук тезирээк аткарат. Оптималдуу суроолорду жазыңыз. Ар дайым. Бул үчүн бүт тутумуңуз сизге ыраазычылык билдирет. Оригиналдуу макала
GO TO FULL VERSION