argon bulletin board
Факултети => Факултет по математика и информатика => Темата е започната от: maka в 06.04.2012, 23:18:36
-
Привет!
Навремето имахме курсове по бази данни. Та интересно ми е мнението на пишещите тук, като далеч по-добри програмисти от мен (който дори не съм програмист, но чат-пат ми се налага да се занимавам с подобни непривични дейности).
Проблемът е търсене на елементи от MySQL таблица, които не са в друга таблица. Аз знам само три варианта да се реши в MySQL - LEFT JOIN + IS NULL, или SELECT с вложен NOT IN (SELECT.. ), или SELECT с вложен NOT EXISTS (SELECT...). Принципно въпросът ми е кой метод е най-ефективен, но ако се налага JOIN-а и SELECT-ите да се правят по поле, което не е primary key. Доколкото, разбира се, може да се даде принципен отговор. (Ако се чудите защо се налага да не е по ПК поле, защото първата таблица всъщност е inner join на две други таблици, понеже авторите на въпросният крапликейшън така са направили базата.
Кое според вас е най-ефективно от гледна точка на консумация на ресурси и време на изпълнение? Като си гледам изпълнението на заявките на пръв поглед, излиза че JOIN-а е най-бавен и товарещ.
Мерси, поздрави :)
П.С. ако някой иска конкретен пример, мога да добавя.
-
Здрасти!
На първо време много трябва да внимаваш с вложените заявки. Ако целта ти е да вземеш записи, за които има релация в две таблици, използвай просто JOIN (без LEFT/RIGHT/INNER и т.н. клауза). Това би решило няколко проблема...
-
Здрасти! Мерси за отговора.
Знам, че по принцип не е добре да се използват вложени заявки :) но в крайна сметка въпросът опира до ефективност, ако вложената заявка е по-ефективна и по-производителна от JOIN, по-добре да се направи с вложена заявка.
Въпросът ми е относно производителността и ефективността на различните подходи за търсене на елемент от таблица, който НЕ Е наличен в друга таблица. Не виждам как това може да стане с INNER JOIN, а ако искам да го направя с JOIN, единственият начин е OUTER JOIN, LEFT или RIGHT зависи от конкретният пример. Ако има и друг, по-ефективен начин да се свърши същото нещо, ще се радвам да го чуя.
Всъщност имам частен случай на този проблем - целта ми е да намеря елементи от една таблица, които присъстват в друга таблица, но НЕ СА в трета таблица.
-
Ето и конкретният пример.
В MySQL 5.0.54a-enterprise имаме следните три таблици (опростени), които съхраняват различна информация за различни устройства (работни станции, сървъри, инфраструктурни устройства, и т.н.)
entities
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| eId | int(11) | NO | PRI | NULL | |
| eName | varchar(255) | NO | MUL | NULL | |
| eType | int(11) | NO | MUL | NULL | |
+------------------+--------------+------+-----+---------+-------+
endpoints
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| eId | int(11) | NO | PRI | NULL | |
| address | varchar(39) | NO | MUL | NULL | |
+--------------+---------------------+------+-----+---------+-------+
units
+-------------------------+-------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+-------------------------------------+------+-----+---------+-------+
| eId | int(11) | NO | PRI | NULL | |
| unitName | varchar(255) | YES | MUL | NULL | |
| unitAddress | varchar(39) | YES | | NULL | |
| unitLocation | varchar(255) | YES | | NULL | |
+-------------------------+-------------------------------------+------+-----+---------+-------+
Искам по подаден LIKE pattern за IP адрес, примерно '110.120.1%' да получа информацията от entities и endPoints таблиците за всички entity-та, които са също налични в endPoints таблицата, но НЕ присъстват в units таблицата.
Вариантите ми са три, според трите подхода за търсене на елемент от таблица, които не са в друга таблица:
LEFT JOIN + IS NULL:
select i.eId, e.eName, e.eType, i.address from endpoints i inner join entities e on e.eId=i.eId left join units u on u.unitAddress=i.address where i.address like '110.120.1%' and u.unitAddress is null;
SELECT + NOT IN:
select i.eId, e.eName, e.eType, i.address from endpoints i inner join entities e on e.eId=i.eId where i.address like '110.120.1%' not in (select unitAddress from units);
SELECT + NOT EXISTS:
select i.eId, e.eName, e.eType, i.address from endpoints i inner join entities e on e.eId=i.eId where i.address like '110.120.1%' and not exists (select unitAddress from units u where u.unitAddress=i.address);
Кой от тези би трябвало да е по-ефективен по принцип (И защо)?
Малко информация за таблиците:
В units имаме приблизително 6000 записа, в entities - прибл. 400000, a в endPoints - прибл. 300000.
В entities имаме абстрактна информация за устройствата и техните компоненти, в endpoints имаме информация за техните IP адреси, а в units имаме само данни за основните компоненти на устройствата - шаситата, т.е. един единствен адрес.
- във всички таблици полето eId е primary key;
- всички eId стойности от endpoints се съдържат в entities;
- всички eId стойности от units се съдържат в entities;
- НЯМА стойности от units, за които units.еId=endPoints.eId;
- но има стойности от units, за които units.unitAddress=endPoint.address,
т.е.: шасито на един сървър с 5 IPta примерно, ще има запис в units с основния си адрес, и с eId, което е налично и в entities; За всеки IP адрес на сървъра, вкл. основния му адрес, има отделни записи в endPoints, с eId ключове, които са налични в entities, но не са налични в units. Иде реч, че entities и endpoints могат да се джойннат на primary key полето еId, но endpoints и units не могат да се джойннат по primary key поле, а само по адрес, което не е primary key и е индекс само в endPoints.
-
Здравей,
Няма как да разбереш кое е по-ефективно докато не видиш плана на изпълнение. Всяка БД има различна система за планиране и различна система за оптимизиране на заявките.
Може би трябва да прегледаш:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect
http://www.selikoff.net/2008/12/10/memo-avoid-nested-queries-in-mysql-at-all-costs/
Принципно вложените структури не се оптимизират добре. Когато имаш select в select е много по-сложно за който и да е оптимизатор "да се сети" какво имаш предвид. Естествено добрите оптимизатори в много голяма част от простите случаи на вложени селекти се усещат. Единствения начин да разбереш какво става в твоя случай е да видиш плана.
-
Благодаря!
Всъщност точно последната статия ме накара да се поразровя за производителността на различните варианти. :) Естествено винаги съществува възможността да се разделят нещата на по-прости заявки и да се обработят в уеб кода на отделна машина, но все пак.
Ето execution plan-овете на трите заявки:
LEFT JOIN/IS NULL:
explain extended select e.eId, e.eName, e.eType, i.address from entities e inner join endpoints i on i.eId=e.eId left join units u on u.unitAddress=i.address where i.address like '110.120.130%' and u.unitAddress is null;
+----+-------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
| 1 | SIMPLE | i | range | PRIMARY,еndpointsAddress_idx | endpointsAddress_idx | 119 | NULL | 1756 | Using where; Using index |
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 4477 | Using where |
| 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | ncim.i.eId | 1 | |
+----+-------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
SELECT/NOT IN
explain extended select e.eId, e.eName, e.eType, i.address from entities e inner join endpoints i on i.eId=e.eId where i.address like '110.120.130%' and i.address not in (select unitAddress from units);
+----+--------------------+---------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
| 1 | PRIMARY | i | range | PRIMARY,endpointsAddress_idx | endpointsAddress_idx | 119 | NULL | 1756 | Using where; Using index |
| 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | ncim.i.eId | 1 | |
| 2 | DEPENDENT SUBQUERY | units | ALL | NULL | NULL | NULL | NULL | 4477 | Using where |
+----+--------------------+---------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
SELECT/NOT EXISTS
explain extended select e.eId, e.eName, e.eType, i.address from entities e inner join endpoints i on i.eId=e.eId where i.address like '110.120.1%' and not exists (select unitAddress from units u where u.unitAddress=i.address);
+----+--------------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
| 1 | PRIMARY | i | range | PRIMARY,endpointsAddress_idx | endpointsAddress_idx | 119 | NULL | 1756 | Using where; Using index |
| 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | ncim.i.eId | 1 | |
| 2 | DEPENDENT SUBQUERY | u | ALL | NULL | NULL | NULL | NULL | 4477 | Using where |
+----+--------------------+-------+--------+-------------------------------+-----------------------+---------+-----------------+------+--------------------------+
3 rows in set, 2 warnings (0.00 sec)
Има ли някаква голяма разлика в тях? Аз не мога да видя голяма разлика, само в реда на операциите, а LEFT JOIN-а последователно се изпълнява най-бавно. Има ли логика в това? Според мен недостатъкът идва оттам, че релацията между endpoints и units е по полето adress/unitAddress, което не е ключ и е индекс само в endpoints.
-
От трите варианта най-издържан е първия. Има обаче едно правило, което не е изпълнено - всички релации в JOIN и WHERE клаузите да са по ключове и да не се кастват (губи се ефективността). Аз лично бих сложил индекс на units.unitAddress и бих конструирал заявката по следния начин:
SELECT
e.eId,
e.eName,
e.eType,
i.address
FROM entities e
INNER JOIN endpoints i ON (i.eId = e.eId)
LEFT JOIN units u ON (u.unitAddress = i.address AND u.eId is null)
WHERE i.address LIKE '110.120.130%';
-
Хмм прав си последният is null where може да се елиминира ако се направи по eId, мерси. Всъщност има известно подобрение дори само с тази промяна.
За допълнителният индекс и аз си мислех, има идея, но гледам да внимавам с базата защото нямам почти никаква документация за нея - ерм модели, кога къде се правят INSERT-и и пр. :)
По принцип точно в units таблицата не би трябвало да има супер-чести INSERT-и, че да ги импактне един допълнителен индекс, но да видим - там данните се рефрешват веднъж дневно, но би трябвало само да се добавят новооткрити записи и да се трият стари, които ако всичко е стабилно не би трябвало да са повече от 100 на ден (дори и това е много).
Но иначе сложих сега индекс на units(unitAddress) на една тестова инстанция с по-малко данни, времената за изпълнение на заявките се промениха драматично, ще го оставя за един-два дни да видим дали ще импактне нещо друго ;)
мерси ;)