Хорошо, по-видимому, на основании голосов за другой ответ это требует дальнейшего объяснения. Пример (сделано с MySQL, потому что он у меня под рукой, но принцип универсален для любого диалекта SQL):
CREATE TABLE Blah (
ID INT PRIMARY KEY,
SomeText VARCHAR(30),
ParentID INT
)
INSERT INTO Blah VALUES (1, 'One', 0);
INSERT INTO Blah VALUES (2, 'Two', 0);
INSERT INTO Blah VALUES (3, 'Three', 1);
INSERT INTO Blah VALUES (4, 'Four', 1);
INSERT INTO Blah VALUES (5, 'Five', 4);
Версия левого соединения:
SELECT a.ID, a.SomeText, COUNT(1)
FROM Blah a
JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
Неправильный. Игнорирует случай без детей.
Левое внешнее соединение:
SELECT a.ID, a.SomeText, COUNT(1)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
Неправильно, и причина несколько тонкая. COUNT(1) подсчитывает NULL строк, тогда как COUNT(b.ID) нет. Итак, вышеприведенное неверно, но это правильно:
SELECT a.ID, a.SomeText, COUNT(b.ID)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
Связанный подзапрос:
SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a
Тоже правильно.
Хорошо, так что использовать? Планы только говорят вам так много. Проблема подзапросов и left-joins — старый метод, и без его сравнительного анализа нет однозначного ответа. Итак, нам нужны некоторые данные:
<?php
ini_set('max_execution_time', 180);
$start = microtime(true);
echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
echo mysql_error();
exit();
}
mysql_select_db('scratch');
if (mysql_error()) {
echo mysql_error();
exit();
}
$count = 0;
$limit = 1000000;
$this_level = array(0);
$next_level = array();
while ($count < $limit) {
foreach ($this_level as $parent) {
$child_count = rand(0, 3);
for ($i=0; $i<$child_count; $i++) {
$count++;
query("INSERT INTO Blah (ID, SomeText, ParentID) VALUES ($count, 'Text $count', $parent)");
$next_level[] = $count;
}
}
$this_level = $next_level;
$next_level = array();
}
$stop = microtime(true);
$duration = $stop - $start;
$inserttime = $duration / $count;
echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $inserttime seconds.\n";
echo "</pre>\n";
function query($query) {
mysql_query($query);
if (mysql_error()) {
echo mysql_error();
exit();
}
}
?>
У меня закончилась память (32 МБ) во время этого прогона, поэтому в итоге получилось только 876 109 записей, но, эй, этого достаточно. Позже, когда я тестирую Oracle и SQL Server, я беру тот же самый набор данных и импортирую его в Oracle XE и SQL Server Express 2005.
Теперь другой постер поднял вопрос о том, что я использую оболочку count вокруг запросов. Он правильно указал, что в этом случае оптимизатор может не выполнять подзапросы. MySQL не кажется таким умным. Оракул есть. SQL Server, похоже, тоже.
Поэтому я приведу две цифры для каждой комбинации запроса к базе данных: первая завернута в SELECT COUNT(1) FROM ( ... ), вторая — необработанная.
Настройка:
- MySQL 5.0 с использованием PremiumSoft Navicat (
LIMIT 10000 в запросе);
- SQL Server Express 2005 с использованием Microsoft SQL Server Management Studio Express;
- Oracle XE с использованием PL/SQL Developer 7 (ограничено 10 000 строк).
Левое внешнее соединение:
SELECT a.ID, a.SomeText, COUNT(b.ID)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
- MySQL: 5.0: 51,469 с / 49,907 с
- SQL Server: 0(1) / 9 с(2)
- Oracle XE: 1,297 с / 2,656 с
(1) Практически мгновенно (подтверждение другого пути выполнения)
(2) Впечатляет, учитывая, что возвращаются все строки, а не 10 000
Просто показывает ценность реальной базы данных. Кроме того, удаление поля SomeText оказало значительное влияние на производительность MySQL. Также не было большой разницы между ограничением в 10000 и его отсутствием с MySQL (улучшение производительности в 4-5 раз). У Oracle это было только потому, что PL/SQL Developer вырвало, когда он достиг 100-мегабайтного использования памяти.
Связанный подзапрос:
SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a
- MySQL: 8,844 с / 11,10 с
- SQL Server: 0 с / 6 с
- Oracle: 0,046 с / 1,563 с
Таким образом, MySQL лучше в 4-5 раз, Oracle примерно в два раза быстрее, а SQL Server, возможно, лишь немного быстрее.
Суть остается: коррелированная версия подзапроса быстрее во всех случаях.
Другое преимущество коррелированных подзапросов заключается в том, что они синтаксически чище и их легче расширять. Под этим я подразумеваю, что если вы хотите провести подсчет в куче других таблиц, каждую из них можно легко и просто включить в качестве другого элемента выбора. Например: представьте запись клиентов по счетам, где эти счета либо неоплачены, просрочены, либо оплачены. С подзапросом это легко:
SELECT id,
(SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'UNPAID') unpaid_invoices,
(SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'OVERDUE') overdue_invoices,
(SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'PAID') paid_invoices
FROM customers c
Агрегатная версия намного уродливее.
Я не утверждаю, что подзапросы всегда лучше агрегатных объединений, но достаточно часто это так, что вам нужно проверить это. В зависимости от ваших данных, размера этих данных и вашего поставщика РСУБД разница может быть очень значительной.
person
cletus
schedule
26.01.2009
