Мақаланы түсіну үшін қажетті білім деңгейі: мәліметтер базасы және SQL туралы жалпы түсінік, ДҚБЖ-мен кейбір практикалық тәжірибе.
Тиімді SQL сұрауларын жазуды үйренуге болатын ең маңызды нәрсе - индекстеу. Дегенмен, екінші орында, өте жақын, көптеген SQL клиенттері дерекқордан көптеген «қажетсіз, бірақ қажетті жұмыстарды» орындауды талап ететін білім . Менен кейін қайталаңыз:
Қажет емес, бірақ қажет жұмыс
«Қажет емес, бірақ міндетті жұмыс» дегеніміз не? Капитан Обвиус бізге айтқандай, ол:
Қажет емес
Біздің клиенттік қолданбаға келесі деректер қажет болсын:
Ерекше ештеңе жоқ. Біз фильмдер дерекқорымен ( Sakila дерекқоры сияқты ) жұмыс істеп жатырмыз және пайдаланушыларға барлық фильмдердің атауы мен рейтингін көрсеткіміз келеді. Келесі сұрау бізге қажетті нәтижені бере алады:
SELECT title, rating
FROM film
Дегенмен, біздің қолданба (немесе біздің ORM) оның орнына осы сұрауды орындайды:
SELECT *
FROM film
Нәтижесінде біз не аламыз? Болжам. Біз көптеген пайдасыз ақпаратты аламыз:
Оң жақта сіз тіпті кейбір күрделі JSON жүктеліп жатқанын көре аласыз:
дискіден
кэшке
сым арқылы
клиенттің жадында
және ақырында лақтырылды [қажетсіз]
Иә, біз бұл ақпараттың көпшілігін лақтырып тастаймыз. Бұл ақпаратты алу үшін жасалған барлық әрекеттер мүлдем пайдасыз болып шықты. Бұл рас па? Рас па.
Міндетті
Ал қазір - ең нашар бөлігі. Оңтайландырушылар қазір көп нәрсені істей алатынына қарамастан, бұл әрекеттер дерекқор үшін міндетті болып табылады. Дерекқорда клиенттік қолданбаға бұл деректердің 95% қажет емес екенін білуге мүмкіндік жоқ. Және бұл ең қарапайым мысал ғана. Бірнеше кестелерді қосуды елестетіп көріңіз ... Сонымен, сіз не айтасыз, бірақ деректер базасы жылдам? Сіз ойланбаған кейбір нәрселерді түсіндіруге рұқсат етіңіз. Әрине, жеке сұраныстың орындалу уақыты ештеңеге әсер етпейді. Жарайды, ол бір жарым есе баяу жұмыс істеді, бірақ біз оны жеңеміз, солай ма? Ыңғайлы болу үшін бе? Кейде бұл шындық. Бірақ ыңғайлы болу үшін өнімділікті әрқашан құрбан етсеңіз , бұл ұсақ-түйектер қосыла бастайды. Біз енді өнімділік (жеке сұраныстарды орындау жылдамдығы) туралы емес, өткізу қабілеті (жүйенің жауап беру уақыты) туралы сөйлесетін боламыз, содан кейін шешу оңай емес күрделі мәселелер басталады. Міне, сіз масштабтылықты жоғалтасыз. Орындау жоспарларын қарастырайық, бұл жағдайда Oracle ДҚБЖ:
--------------------------------------------------|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 * сұрауын іске қосу, рейтинг дерекқорда 8 есе көп жадты пайдаланады. Күтпеген ештеңе жоқ, солай ма? Біз бұлай болатынын білдік. Бірақ біз бұл деректердің барлығын қажет етпейтін көптеген сұрауларымыз үшін әлі де келісеміз. Біз дерекқор үшін қажетсіз, бірақ міндетті жұмыс жасаймыз , ол жиналып, жиналып қалады. Біз жадты қажетінен 8 есе көп пайдаланамыз (әрине, көбейткіш өзгереді). Сонымен қатар, барлық басқа кезеңдерде (дискіні енгізу/шығару, желі арқылы деректерді беру, клиент жадты тұтыну) проблемалар бірдей, бірақ мен оларды өткізіп жіберіп, орнына қараймын ...
Индекстерді пайдалану
Көптеген деректер базалары бүгінде индекстерді қамту тұжырымдамасын бағалады . Жабық индекстің өзі индекстің ерекше түрі емес. Бірақ ол белгілі бір сұрау үшін «кездейсоқ» немесе солай болуға арналғандықтан, «арнайы индекс» болып шығуы мүмкін. Келесі сұрауды қарастырыңыз:
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
Оны жүзеге асыруда күтпеген ештеңе жоқ. Бұл қарапайым өтініш. Индекс бойынша ауқымды қараңыз, кестеге қол жеткізіңіз - және сіз аяқтадыңыз:
-------------------------------------------------------------------|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|----------------------------------------------------
Сұрауымыздың барлық қажеттіліктерін қанағаттандыратын индекстің болуының арқасында кестеге қол жеткізуді толығымен жоя алдық... қамту индексі. Маңызды ма? Және қалай! Бұл тәсіл кейбір сұрауларды шама реті бойынша жылдамdateға мүмкіндік береді (немесе кейбір өзгерістерден кейін индекс енді жабылмаған кезде оларды шама реті бойынша баяулатыңыз). Қамту индекстерін әрқашан пайдалану мүмкін емес. Сіз индекстер үшін ақы төлеуіңіз керек және оларды тым көп қоспауыңыз керек. Бірақ бұл жағдайда бәрі анық. Өнімділікті бағалайық:
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
Актер кестесінде тек 4 баған бар екенін ескеріңіз, сондықтан 1 және 2 мәлімдемелер арасындағы өнімділік айырмашылығы соншалықты үлкен емес, бірақ ол әлі де маңызды. Оңтайландырушы сұрау үшін бір немесе басқа нақты индексті таңдауы үшін Oracle оңтайландырушы кеңестерін пайдаланғанымды да ескеремін. 3-оператор біздің жарысымыздың сөзсіз жеңімпазы. Оның өнімділігі әлдеқайда жақсы және біз өте қарапайым сұрау туралы айтып отырмыз. Тағы да, біз SELECT * жазғанда, біз оңтайландыра алмайтын дерекқор үшін қажетсіз, бірақ міндетті жұмыс жасаймыз. Ол жабу индексін таңдамайды, себебі оның өзі таңдаған LAST_NAME индексінен сәл жоғарырақ, сонымен қатар, мысалы, пайдасыз LAST_UPDATE бағанын алу үшін кестеге кіруі керек. Бірақ біз 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)
Бұл көрініс ТҰТЫНУШЫ тұтынушы кестесі мен олардың мекен-жайының бөліктеріне арналған әртүрлі кестелер арасындағы барлық «...-бірге» қосылымдарын жай ғана орындайды. Рахмет, қалыпқа келтіру. Бұл көрініспен біраз жұмыс істегеннен кейін біз оған үйреніп, астындағы кестелерді ұмытып кеткенімізді елестетіп көріңіз. Ал енді біз келесі сұранысты орындаймыз:
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|----------------------------------------------------------------
Әрине. Дерекқор осы біріктірулердің барлығын және кестені толық сканерлеуді жүзеге асырады, өйткені біз оған мұны істеуді айттық - бұл барлық деректерді алыңыз. Енді, тағы да елестетіп көріңізші, бізге шынымен қажет нәрсенің бәрі осы болды:
Не, шындап, солай ма? Енді сіз менің не туралы айтып тұрғанымды түсіне бастадыңыз. Бірақ біз өткен қателіктерден бірдеңе үйрендік деп елестетіп көріңіз және осы оңтайлы сұрауды орындаңыз:
Орындау тұрғысынан жақсы жаққа түбегейлі өзгерістер. Біріктірулер жойылды, себебі оңтайландырушы енді олардың пайдасыз екенін көре алады және егер ол мұны көрсе (және сіз * таңдау арқылы бұл жұмысты міндетті етпеген болсаңыз), онда ол бұл жұмыстың барлығын орындай алмайды. Неліктен бұл жағдайда бұлай болды? 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 предикатында (ТАҢДАУ ..) таңдаулы тізім элементтері сұраудың нәтижесін өзгертпейтінін қамтамасыз ете алады, сондықтан оны орындаудың қажеті жоқ. Бұл сияқты!
Сол уақытта...
ORM-мен ең тітіркендіргіш мәселелердің бірі - SELECT * сұрауларын жазудың оңай болуы. Іс жүзінде, мысалы, HQL/JPQL-де олар әдетте әдепкі бойынша пайдаланылады. Біз ТАҢДАУ тармағын мүлдем алып тастай аламыз, өйткені біз бүкіл нысанды шығарып аламыз, солай ма? Мысалы:
FROM v_customer
Мысалы, Влад Михалчеа, сарапшы және Hibernate бағдарламасымен әзірлеуді жақтаушы , төлем жасағаннан кейін ешқандай өзгерістерді сақтағыңыз келмейтініне сенімді болсаңыз, дерлік [білікті] сұрауларды пайдалануды ұсынады. ORMs an objectілік графиктердің тұрақтылығы мәселесін шешуді айтарлықтай жеңілдетеді. Ескерту: табандылық. Нысандық графиктерді нақты өзгерту және өзгертулерді сақтау міндеттері бір-бірімен тығыз байланысты. Бірақ егер сіз мұны істемейтін болсаңыз, онда мәнін шығарып алудың қажеті не? Неліктен [нақты] сұрау жазбасқа? Түсінікті болайық: өнімділік тұрғысынан, нақты пайдалану жағдайыңызға арнайы бейімделген сұрауды жазу кез келген басқа опцияға қарағанда жақсырақ. Сізге мән бермеуі мүмкін, себебі деректер жинағы кішкентай және бұл маңызды емес. Тамаша. Бірақ, сайып келгенде, масштабтау қажет болғанда, нысан графигінің императивті өтуінің орнына сұрауларды пайдалану үшін қолданбаларды қайта жобалау өте қиын болады. Ал онсыз да сізде бір нәрсе болады.
Бірдеңе бар-жоғын білу үшін сызықтарды санау
Ресурстардың ең нашар ысырап етулерінің бірі дерекқорда бірдеңе бар-жоғын білу үшін COUNT(*) сұрауларды орындау болып табылады. Мысалы, берілген пайдаланушының тапсырыстары бар-жоғын анықтау керек. Және біз сұранысты орындаймыз:
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
)
Шынайы болмыс предикаты біріншісін тапқан бойда қосымша жолдарды іздеуді тоқтататынын анықтау үшін зымыран ғалымы қажет емес . Егер нәтиже «тапсырыссыз» болып шықса, жылдамдық салыстырмалы болады. Егер нәтиже «иә, тапсырыстар бар» болса, онда нақты мөлшерді санаудың қажеті жоқ жағдайда жауап әлдеқайда жылдамырақ алынады. Өйткені, бізге нақты сан қызық емес. Дегенмен, біз дерекқорға оны есептеуді айттық ( қажетсіз жұмыс ) және деректер базасы 1-ден жоғары барлық нәтижелерді елемейтінімізді білмейді ( қажетті жұмыс ). Әрине, дәл осындай нәтижелерге қол жеткізу үшін JPA қолдайтын коллекцияда list.size() деп атасақ, әлдеқайда нашар болар еді. Мен бұл туралы бұрын блогымда жаздым және екі нұсқаны салыстырмалы тестілеуден өткіздім ...
Қорытынды
Бұл мақалада анық айтылған. Дерекқорды қажетсіз, бірақ талап етілетін жұмыстарды орындауға мәжбүрлемеңіз . Бұл қажет емес , өйткені талаптарды ескере отырып, белгілі бір жұмысты орындау қажет емес екенін білесіз. Дегенмен, сіз дерекқорға мұны істеуді айтасыз. Бұл талап етіледі, өйткені бұл жұмыстың қажетсіз екендігіне көз жеткізу үшін дерекқордың жолы жоқ . Бұл ақпарат тек клиентке қол жетімді және serverде қол жетімді емес. Сондықтан деректер базасы оны орындауы керек. Мақалада SELECT * функциясына назар аударылды, себебі ол қарауға ыңғайлы нысан. Бірақ бұл дерекқорларға ғана қатысты емес. Бұл клиент serverге қажетсіз, бірақ талап етілетін жұмысты орындауды бұйыратын барлық бөлінген алгоритмдерге қатысты . Орташа AngularJS қолданбаңызда қанша N+1 тапсырмасы бар, онда пайдаланушы интерфейсі B қызметіне барлық қоңырауларды бір қоңырауға жинамай, B қызметіне бірнеше рет қоңырау шалып, А қызметінің нәтижесі арқылы өтеді? Бұл өте жиі кездесетін құбылыс. Шешім әрқашан бірдей. Пәрмендеріңізді орындайтын нысанға неғұрлым көбірек ақпарат берсеңіз, ол (теориялық тұрғыдан) сол пәрмендерді жылдамырақ орындайды. Оңтайлы сұрауларды жазыңыз. Әрқашан. Сіздің бүкіл жүйеңіз бұл үшін сізге алғыс айтады. Түпнұсқа мақала
GO TO FULL VERSION