Современные информационные технологии/ 2. Вычислительная техника и программирование

Бурлибаева Ш.М., Жамбылқызы Ж., Мейірбекқызы Г.М., Тәжібай А.М.

MySQL сұраныстарын ықшамдаландыру

Ықшамдаландыру - бұл шапшаңдылығын жоғарлату мақсатында жүйені өзгерту.

Деректер қорының жұмысының ықшамдаландыруын 3 түрге бөлуге болады:

· сұраныстарды ықшамдаландыру

· құрылымды ықшамдаландыру

· серверді ықшамдаландыру.

Сұраныстың ықшамдаландыруын толығырақ қарастырайық

Сұраныстың ықшамдаландыруы – ең қарапайым және жоғарғы нәтижелерге әкелетін ықшамдаландыруының бір түрі.

SELECT операторы

Көбінесе, ықшамдаландыруға келетін сұраныстар- бұл таңдамаға сұраныстары.

Таңдамаға сұраныстың орындалуын қарастыру үшін EXPLAIN операторы қолданылады. Оның көмегімен кестелер байланысатын тәртібін және бұл жағдайда қолданылатын индекстерді қарастыруға болады.

Жиі кездесетін қателер- керекті өрістердегі индекстердің жоқтығы немесе керекті емес өрістердегі индекстердің орнатылуы. Егер төмендегідей қарапайым таңдама жүргізілсе:

SELECT * FROM table WHERE field1 = 123

онда field1 өрісте индекс орнату қажет. Егер таңдамада екі өріс бойынша шарт қолданса:

SELECT * FROM table WHERE field1 = 123 AND field2 = 234

онда field1, field2 өрістерде құрамды индекс орнату қажет болады.

Егер екі немесе одан көп байланысқан кестелер қолданса:

SELECT *

FROM a, b

WHERE a.b_id = b.id

Немесе жалпы түрде:

SELECT *

FROM a

[LEFT] JOIN b ON b.id = a.b_id

[LEFT] JOIN с ON с.id = b.c_id

онда кестелер байланысқан өрістері бойынша индекстер құрылуы тиіс. Жоғарыда көрсетілген мысалда b.id және c.id өрістер бойынша индекстер құрылуы тиіс. Бірақ бұл мәлімдемеде, егер сұраныста келтірілген тәртіп бойынша таңдау жүргізілсе ғана, шынайы болып табылады. Егер, мысал ретінде, MySQL ықшамдаудырушы кестелерден жазбаларды келесі тәртіпте таңдаса: c,b,a, онда индекстерді келесі өрістер бойынша орнату қажет: b.c_id және a.b_id. LEFT JOIN көмегімен байланысқан кесте, сұраныстың сол жағындағысы, бірінші болып қарастырылады.

Кейде өте үлкен кестенің бір бөлігінен үнемі таңдау жүргізу керек болады, мысалы, көптеген сұраныстарда кестенің бөлігімен байланыс жасалынады:

[LEFT] JOIN b ON b.id = a.b_id AND b.field1 = 123 AND b.field2 = 234

Бұндай жағдайда кестенің бұл бөлігін уақытша кестеге орнату керек:

CREATE TEMPORARY TABLE tmp_b TYPE=HEAP SELECT * FROM b WHERE b.field1 = 123 AND b.field2 = 234

Және ары қарай осы уақытша кестемен жұмыс атқару қажет болады.

Егер бір деректер үшін бірнеше рет агрегатты функцияны есептесек, онда есептеулерді тездету үшін бір рет агрегатты функцияны есептеп, оның нәтижесін уақытша кестеге орналастыру керек болады.

Мысалы, келесі бір сұраныста бір кестенің атрибутінің максималды мәні және басқа кестедегі жазбалардың саны есептелінеді.

SELECT f_m. *, MAX( f_m_v_w.date ) AS last_visited, COUNT( DISTINCT f_b.id ) AS books_num,

IF ( f_m.region != 999, f_r.name, f_m.region_other ) AS region_name

FROM fair_members f_m

LEFT JOIN fair_members_visits_week f_m_v_w ON f_m_v_w.member_id = f_m.id

LEFT JOIN fair_regions AS f_r ON f_m.region = f_r.id

LEFT JOIN fair_books AS f_b ON f_b.pub_id = f_m.id

GROUP BY f_m.id

Нәтижесінде сұранысқа екі кестенің қосылуы таңдаманың орындалу жылдамдығы төмендейді. Таңдаманың жылдамдығын арттыру үшін MAX’ немесе COUNT’ функцияны бөлек сұраныста есептеу қажет.

Жол санын есептеу үшін аргумент ретінде «жұлдызшаны» көрсетіп, COUNT(*) функцияны қолдану керек.

Почему COUNT(*) функция COUNT(id) функцияға қарағанда жылдам есептелінеді, мысалы:

message кесте берілсін: id | user_id | text

PRIMARY(id), INDEX(user_id) индекспен

$user_id берілген пайдаланушының хабарламаларын есептеу керек.

Екі сұранысты салыстырайық:

SELECT COUNT(*) FROM message WHERE user_id = $user_id

және

SELECT COUNT(id) FROM message WHERE user_id = $user_id

Бірінші сұранысты іске асыру үшін шартты қанағаттандыратын жазбалар санын есептеуі жеткілікті – бұл тез орындалады, себебі біріншіден индекстер реттелген, екіншіден - алмасу буферде орналасқан.

Екінші сұранысты орындау үшін шартты қанағаттандыратын жазбаларды таңдау үшін, индекс бойынша өтеміз, ары қарай шартты қанағаттандыратын жазбаны алып шығару қажет (жазба дискіде жазылу мүмкін) id мәнін есептеу үшін, содан кейін санауышты орнатуға болады.

Нәтижесінде жазбалар саны көптеген болса бірінші сұраныстың жылдамдығы екіншіге қарағанда бірнеше рет жоғары болады.