-1 Страшная идея. Используйте реляционную базу данных в качестве реляционной базы данных.

чень простой запрос, который я не могу понять ....

Допустим, у меня есть таблица из двух столбцов:

userid  |  roleid
--------|--------
   1    |    1
   1    |    2
   1    |    3
   2    |    1

Я хочу получить все различные идентификаторы пользователей, которые имеютroleids 1, 2 И 3. Используя приведенный выше пример, единственный результат, который я хочу получить,userid 1. Как мне это сделать?

Ответы на вопрос(5)

я получил отрицательный голос по этому, поэтому я решил проверить это:

CREATE TABLE userrole (
  userid INT,
  roleid INT,
  PRIMARY KEY (userid, roleid)
);

CREATE INDEX ON userrole (roleid);

Запустите это:

<?php
ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records 

$start = microtime(true);

echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo "Selct DB error: " . mysql_error() . "\n";
}

$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
    $roles = rand(1, 4);
    $available = range(1, 5);
    for ($j=0; $j<$roles; $j++) {
        $extract = array_splice($available, rand(0, sizeof($available)-1), 1);
        $id = $extract[0];
        query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
        $count++;
    }
}

$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "</pre>\n";

function query($str) {
    mysql_query($str);
    if (mysql_error()) {
        echo "$str: " . mysql_error() . "\n";
    }
}
?>

Выход:

499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.

Это добавляет 500 000 случайных комбинаций ролей пользователей и примерно 25 000 соответствуют выбранным критериям.

Первый запрос:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Время запроса: 0.312 с

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Время запроса: 0.016с

Верно. Предлагаемая мной версия соединенияв двадцать раз быстрее, чем агрегатная версия.

Извините, но я делаю это для жизни и работы в реальном мире, а в реальном мире мы тестируем SQL, и результаты говорят сами за себя.

Причина этого должна быть довольно ясна. Совокупный запрос будет масштабироваться по стоимости в зависимости от размера таблицы. Каждая строка обрабатывается, агрегируется и фильтруется (или нет) черезHAVING пункт. Версия объединения (с использованием индекса) выберет подмножество пользователей на основе заданной роли, затем проверит это подмножество для второй роли и, наконец, это подмножество для третьей роли. каждыйвыбор (вреляционная алгебра условия) работает на все более небольшом подмножестве. Из этого можно сделать вывод:

Производительность версии соединения становится еще лучше с меньшим количеством совпадений.

Если было только 500 пользователей (из приведенного выше примера 500 тыс.), У которых было три заявленные роли, версия соединения будет значительно быстрее. Совокупная версия не будет (и любое улучшение производительности является результатом переноса 500 пользователей вместо 25 КБ, что, очевидно, также получает и объединенная версия).

Мне также было любопытно посмотреть, как реальная база данных (то есть Oracle) справится с этим. Поэтому я в основном повторил то же упражнение на Oracle XE (работающем на том же настольном компьютере с Windows XP, что и MySQL из предыдущего примера), и результаты практически идентичны.

Похоже, что присоединения не одобряются, но, как я уже продемонстрировал, совокупные запросы могут быть на порядок медленнее.

Обновить: После некотороговсестороннее тестированиекартина сложнее, и ответ будет зависеть от ваших данных, вашей базы данных и других факторов. Мораль этой истории - тест, тест, тест.

 Jason Punyon♦25 янв. 2009 г., 02:19
Этот DVD не пришел от меня ... но серьезно ... вы бы поместили это в свою систему?
 Jim Petkus25 янв. 2009 г., 04:08
Похоже, что-то не хватает. Что такое T2 и T3? Псевдонимы одной таблицы? Вероятно, будет работать, если он был изменен, чтобы быть таким.
 Carl Manaster07 авг. 2009 г., 21:59
Имеет ли какое-либо значение, если тесты roleId для t2 и t3 перемещены в предложение WHERE? Мне кажется, что (по крайней мере, концептуально) они принадлежат там, и я надеюсь, что это не повлияет на производительность - но я не проверял.
 Mitch Wheat26 янв. 2009 г., 02:28
Объединения почти всегда быстрее, чем эквивалентные агрегатные запросы. Но я бы не стал жертвовать ясностью, если бы не было требования к скорости (определяемой профилированием) ...
 Jens Schauder07 авг. 2009 г., 22:08
Просто чтобы быть разборчивым: имейте в виду, что два запроса не эквивалентны, когда в таблице есть двойные записи, и если в столбцах role_id присутствуют значения NULL, результаты также могут отличаться. Хотя я предполагаю, что в таблице есть pk в обеих колонках.

ионного разделения.

На английском языке: выберите тех пользователей, для которых отсутствует ни одно из требуемых значений ролей.

Я предполагаю, что у вас есть таблица Users, к которой относится таблица UserRole, и я предполагаю, что нужные значения roleid находятся в таблице:

create table RoleGroup(
  roleid int not null,
  primary key(roleid)
)
insert into RoleGroup values (1);
insert into RoleGroup values (2);
insert into RoleGroup values (3);

Я также предполагаю, что все соответствующие столбцы не имеют значения NULL, поэтому никаких сюрпризов с IN или NOT EXISTS нет. Вот SQL-запрос, который выражает английский выше:

select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where not exists (
    select R.roleid from UserRole as R
    where R.roleid = G.roleid
    and R.userid = U.userid
  )
);

Еще один способ написать именно это

select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where G.roleid not in (
    select R.roleid from UserRole as R
    where R.userid = U.userid
  )
);

Это может или не может оказаться эффективным, в зависимости от индексов, платформы, данных и т. Д. Поищите в Интернете «реляционное разделение», и вы найдете много.

 Steve Kass09 нояб. 2017 г., 00:59
Это в значительной степени прямой перевод того, что я написал вверху, и здесь, опять же с аннотациями в скобках: выберите тех пользователей (крайний SELECT), для которых нет (сначала НЕ СУЩЕСТВУЕТ) нужных значений ролей (самый внутренний SELECT) отсутствует (самый внутренний НЕ) , («Ничего не пропущено» - это то же самое, что «нет никого, кто не входит в число тех, кто там есть»)
 Istiaque Ahmed07 нояб. 2017 г., 12:04
Можете ли вы объяснить немного больше, то есть, что делает каждый подзапрос?

roleid содержатся в уникальном индексе (то есть не может быть 2 записи, где userid = x и roleid = 1

select count(*), userid from t
where roleid in (1,2,3)
group by userid
having count(*) = 3
select userid from userrole where userid = 1
intersect
select userid from userrole where userid = 2
intersect
select userid from userrole where userid = 3

реляционных БД? Будет ли оптимизатор запросов автоматически оптимизировать это?

 sayap23 июн. 2012 г., 06:28
Это было бы идеальным ответом для любой уважающей себя СУРБД. Не MySQL, хотя:bugs.mysql.com/bug.php?id=31336
Решение Вопроса
SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

мой ответ прост и понятен, и получил статус «принят», но, пожалуйста, прочитайтеответ предоставлено @cletus. У него гораздо лучшая производительность.

Размышляя вслух, еще один способ написать самообъединение, описанное @cletus:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);

Это может быть проще для вас, и MySQL поддерживает сравнение таких кортежей. MySQL также знает, как разумно использовать индексы покрытия для этого запроса. Просто пропуститеEXPLAIN и посмотрите «Использование индекса» в примечаниях для всех трех таблиц, что означает, что он читает индекс и даже не должен касаться строк данных.

Я выполнил этот запрос на 2,1 миллиона строк (июльский дамп данных переполнения стека для PostTags), используя MySQL 5.1.48 на моем Macbook, и он возвратил результат за 1,08 секунды. На приличном сервере с достаточным объемом памяти, выделенной дляinnodb_buffer_pool_size, это должно быть еще быстрее.

Ваш ответ на вопрос