Формат за разпечатване

[maka]

Привет!

Навремето имахме курсове по бази данни. Та интересно ми е мнението на пишещите тук, като далеч по-добри програмисти от мен (който дори не съм програмист, но чат-пат ми се налага да се занимавам с подобни непривични дейности).

Проблемът е търсене на елементи от MySQL таблица, които не са в друга таблица. Аз знам само три варианта да се реши в MySQL - LEFT JOIN + IS NULL, или SELECT с вложен NOT IN (SELECT.. ), или SELECT с вложен NOT EXISTS (SELECT...). Принципно въпросът ми е кой метод е най-ефективен, но ако се налага JOIN-а и SELECT-ите да се правят по поле, което не е primary key.  Доколкото, разбира се, може да се даде принципен отговор.  (Ако се чудите защо се налага да не е по ПК поле, защото първата таблица всъщност е inner join на две други таблици, понеже авторите на въпросният крапликейшън така са направили базата.

Кое според вас е най-ефективно от гледна точка на консумация на ресурси и време на изпълнение? Като си гледам изпълнението на заявките на пръв поглед, излиза че JOIN-а е най-бавен и товарещ.

Мерси, поздрави

П.С. ако някой иска конкретен пример, мога да добавя.

[lamerko]

Здрасти!

На първо време много трябва да внимаваш с вложените заявки. Ако целта ти е да вземеш записи, за които има релация в две таблици, използвай просто JOIN (без LEFT/RIGHT/INNER и т.н. клауза). Това би решило няколко проблема...

[maka]

Здрасти! Мерси за отговора.

Знам, че по принцип не е добре да се използват вложени заявки но в крайна сметка въпросът опира до ефективност, ако вложената заявка е по-ефективна и по-производителна от JOIN, по-добре да се направи с вложена заявка.

Въпросът ми е относно производителността и ефективността на различните подходи за търсене на елемент от таблица, който НЕ Е наличен в друга таблица. Не виждам как това може да стане с INNER JOIN, а ако искам да го направя с JOIN, единственият начин е OUTER JOIN, LEFT или RIGHT зависи от конкретният пример.  Ако има и друг, по-ефективен начин да се свърши същото нещо, ще се радвам да го чуя.

Всъщност имам частен случай на този проблем - целта ми е да намеря елементи от една таблица, които присъстват в друга таблица, но НЕ СА в трета таблица.

[maka]

Ето и конкретният пример.

В 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.

[Beginner]

Здравей,
  Няма как да разбереш кое е по-ефективно докато не видиш плана на изпълнение. Всяка БД има различна система за планиране и различна система за оптимизиране на заявките.
  Може би трябва да прегледаш:
  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 е много по-сложно за който и да е оптимизатор "да се сети" какво имаш предвид. Естествено добрите оптимизатори в много голяма част от простите случаи на вложени селекти се усещат. Единствения начин да разбереш какво става в твоя случай е да видиш плана.

[maka]

Благодаря!

Всъщност точно последната статия ме накара да се поразровя за производителността на различните варианти. Естествено винаги съществува възможността да се разделят нещата на по-прости заявки и да се обработят в уеб кода на отделна машина, но все пак.

Ето 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.

[lamerko]

От трите варианта най-издържан е първия. Има обаче едно правило, което не е изпълнено - всички релации в 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%';


[maka]

Хмм прав си последният is null where може да се елиминира ако се направи по eId, мерси. Всъщност има известно подобрение дори само с тази промяна.

За допълнителният индекс и аз си мислех, има идея, но гледам да внимавам с базата защото нямам почти никаква документация за нея - ерм модели, кога къде се правят INSERT-и и пр.

По принцип точно в units таблицата не би трябвало да има супер-чести INSERT-и, че да ги импактне един допълнителен индекс, но да видим - там данните се рефрешват веднъж дневно, но би трябвало само да се добавят новооткрити записи и да се трият стари, които ако всичко е стабилно не би трябвало да са повече от 100 на ден (дори и това е много).

Но иначе сложих сега индекс на units(unitAddress) на една тестова инстанция с по-малко данни, времената за изпълнение на заявките се промениха драматично, ще го оставя за един-два дни да видим дали ще импактне нещо друго ;)

мерси ;)