Лучше ли фильтровать результирующий набор с помощью предложения WHERE или с помощью кода приложения?
Хорошо, вот простая абстракция проблемы:
2 переменные (мужские и женские пользователи) для хранения 2 групп пользователей, т.е. мужчин и женщин
- 1 способ - использовать два запроса для их выбора:
select * from users where gender = 'male' а затем сохраните результат в male_users
select * from users where gender = 'female' а затем сохраните результат в female_users
- другой способ - выполнить только один запрос:
'select * from users' а затем выполните цикл по результирующему набору, чтобы отфильтровать пользователей мужского пола в
фрагмент кода php программы будет выглядеть примерно так:
$result = mysql_query('select * from users');
while (($row=mysql_fetch_assoc(result)) != null) {
if ($row['gender'] == 'male'){// add to male_users}
else if ($row['gender'] == 'female'){// add to female_users}
}
Какой из них более эффективен и считается лучшим подходом?
Это всего лишь простая иллюстрация проблемы. в реальном проекте могут быть таблицы лагера для запроса и дополнительные параметры фильтрации.
Заранее спасибо!
3 answers
Эмпирическое правило для любого приложения состоит в том, чтобы позволить БД делать то, что она делает хорошо: фильтрацию, сортировку и объединение.
Разделите запросы на их собственные функции или методы класса:
$men = $foo->fetchMaleUsers();
$women = $foo->fetchFemaleUsers();
Обновление
Я взял демонстрацию Стивена PostgreSQL с полным запросом сканирования таблицы, выполняющимся в два раза лучше, чем два отдельных индексированных запроса, и имитировал его с помощью MySQL (который используется в фактическом вопросе):
Схема
CREATE TABLE `gender_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`gender` enum('male','female') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26017396 DEFAULT CHARSET=utf8
Я изменил половой тип чтобы не быть VARCHAR(20), поскольку это более реалистично для целей этого столбца, я также предоставляю первичный ключ, как и следовало ожидать, в таблице вместо произвольного ДВОЙНОГО значения.
Неиндексированные результаты
mysql> select sql_no_cache * from gender_test WHERE gender = 'male';
12995993 rows in set (31.72 sec)
mysql> select sql_no_cache * from gender_test WHERE gender = 'female';
13004007 rows in set (31.52 sec)
mysql> select sql_no_cache * from gender_test;
26000000 rows in set (32.95 sec)
Я полагаю, что это не нуждается в объяснении.
Индексированные результаты
ALTER TABLE gender_test ADD INDEX (gender);
...
mysql> select sql_no_cache * from gender_test WHERE gender = 'male';
12995993 rows in set (15.97 sec)
mysql> select sql_no_cache * from gender_test WHERE gender = 'female';
13004007 rows in set (15.65 sec)
mysql> select sql_no_cache * from gender_test;
26000000 rows in set (27.80 sec)
Приведенные здесь результаты радикально отличаются от данных Стивена. Индексированные запросы выполняют почти в два раза быстрее, чем полное сканирование таблицы. Это из правильно проиндексированной таблицы с использованием определений столбцов здравого смысла. Я вообще не знаю PostgreSQL, но в примере Стивена должна быть какая-то существенная неправильная конфигурация, чтобы не показывать аналогичные результаты.
Учитывая репутацию PostgreSQL за то, что он делает вещи лучше, чем MySQL, или, по крайней мере, не хуже, я осмелюсь сказать, что PostgreSQL продемонстрирует аналогичную производительность при правильном использовании.
Также обратите внимание, что на этой же машине чрезмерно упрощенный цикл для выполнения 52 миллионов сравнений для выполнения требуется дополнительных 7,3 секунды.
<?php
$N = 52000000;
for($i = 0; $i < $N; $i++) {
if (true == true) {
}
}
Я думаю, что довольно очевидно, какой подход лучше, учитывая эти данные.
Я бы сказал, что на самом деле нет причин заставлять вашу базу данных выполнять дополнительную работу по оценке предложения WHERE. Учитывая, что вы на самом деле хотите получить все записи, вам придется выполнить работу по их извлечению. Если вы сделаете один ВЫБОР из таблицы, он извлечет их все в табличном порядке, и вы сможете разделить их самостоятельно. Если вы ВЫБЕРЕТЕ, ГДЕ мужчина, а ГДЕ женщина, вам придется вводить индекс для каждой операции, и вы потеряете некоторую локальность данных.
Для например, если ваши записи на диске чередуются между мужчинами и женщинами, и у вас набор данных намного больше, чем память, вам, вероятно, придется прочитать всю базу данных дважды, если вы выполните два отдельных запроса, в то время как один ВЫБОР для обоих будет одним сканированием таблицы.
РЕДАКТИРОВАТЬ: Поскольку я впадаю в забвение, я решил на самом деле провести тест. Я создал таблицу
СОЗДАЙТЕ ВРЕМЕННУЮ ТАБЛИЦУ gender_test (некоторая ДВОЙНАЯ ТОЧНОСТЬ ДАННЫХ, гендерный СИМВОЛ ИЗМЕНЯЮЩИЙСЯ(20));
Я сгенерировал некоторые случайные данные,
Выберите пол, посчитайте(*) из группы gender_test по полу;
пол|количество
--------+----------
женщина |12603133
мужчина |10465539
(2 строки)
Во-первых, давайте проведем эти тесты без индексов, и в этом случае я совершенно уверен, что я прав...
Тест=>ОБЪЯСНИТЬ АНАЛИЗ ВЫБЕРИТЕ * ИЗ gender_test, ГДЕ пол = "мужской";
ПЛАН ЗАПРОСА
Сканирование Seq на gender_test (стоимость=0,00..468402,00 строк=96519 ширина=66) (фактическое время=0,030..4595,367 ряды=10465539 петель=1)
Фильтр: ((пол)::текст = 'мужчина'::текст)
Общее время выполнения: 5150,263 мсТест=>ОБЪЯСНИТЬ АНАЛИЗ ВЫБЕРИТЕ * ИЗ gender_test, ГДЕ пол = "женский";
ПЛАН ЗАПРОСА
Сканирование Seq на gender_test (стоимость = 0,00..468402,00 строк =96519 ширина = 66) (фактическое время = 0,029..4751,219 строк = 12603133 петли = 1) Фильтр: ((пол)::текст = "женский"::текст)
Общее время выполнения: 5418.891 мсТест=>ОБЪЯСНИТЬ, ПРОАНАЛИЗИРОВАТЬ, ВЫБРАТЬ *ИЗ gender_test;
ПЛАН ЗАПРОСА
Сканирование Seq на gender_test (стоимость = 0,00..420142,40 строк= 19303840 ширина =66) (фактическая время=0,021..3326,164 строки=23068672 петли=1)
Общее время выполнения: 4543.393 мс (2 строки)
Забавно, похоже, что извлечение данных при сканировании таблицы без фильтра действительно происходит быстрее! На самом деле, более чем в два раза быстрее! (5150 + 5418 > 4543) Как я и предсказывал! :-р
Теперь давайте составим индекс и посмотрим, изменит ли он результаты...
СОЗДАТЬ ИНДЕКС test_index ДЛЯ gender_test(пол);
Теперь для повторного выполнения тех же запросов...
Тест=> ОБЪЯСНИТЕ, ПРОАНАЛИЗИРУЙТЕ ВЫБОР ИЗ gender_test, ГДЕ пол = "мужской";
ПЛАН ЗАПРОСА
Сканирование кучи растровых изображений на gender_test (стоимость =2164,69..195922,27 строк=115343 ширина=66) (фактическое время = 2008,877..438,348 строк=10465539 петель=1)
Перепроверьте Конд: ((пол)::текст = 'мужчина'::текст)
-> Сканирование индекса растрового изображения на test_index (стоимость=0,00..2135,85 строк=115343 ширина=0) (фактический время=2006,047..2006,047 строк=10465539 петель=1)
Индекс Cond: ((пол)::текст = "мужской"::текст)
Общее время выполнения: 4941,64 мсТест=>ОБЪЯСНИТЬ АНАЛИЗ ВЫБЕРИТЕ * ИЗ gender_test, ГДЕ пол = "женский";
ПЛАН ЗАПРОСА
Сканирование кучи растровых изображений на gender_test (стоимость=2164,69..195922,27 строк=115343 ширина=66) (фактическое время=1915,385..4269,933 строк=12603133 циклы=1)
Перепроверьте Cond: ((пол)::текст = 'женский'::текст)
->Сканирование растрового индекса на test_index (стоимость=0,00..2135,85 строк=115343 ширина=0) (фактическое время=1912,587..1912,587 строк=12603133 циклов=1)
Индекс Cond: ((пол)::текст = "женский"::текст)
Общее время выполнения: 4931,555 мс (5 строк)Тест=>ОБЪЯСНИТЬ, ПРОАНАЛИЗИРОВАТЬ, ВЫБРАТЬ *ИЗ gender_test;
ПЛАН ЗАПРОСА
Сканирование Seq на gender_test (стоимость=0,00..457790,72 строк=23068672 ширина=66) (фактическое время=0,021..3304,836 строк=23068672 петли=1)
Общее время выполнения: 4523,754 мс
Забавно.... сканирование всей таблицы за один раз все еще в два раза быстрее! (4941 + 4931 против 4523)
ПРИМЕЧАНИЕ Есть множество способов, которыми это ненаучно. Я работаю с 16 ГБ оперативной памяти, поэтому весь набор данных помещается в память. Postgres не настроен на использование почти такого количества, но диск кэш все еще помогает... Я бы предположил (но не могу быть уверен, что действительно попытаюсь), что эффекты только ухудшаются, как только вы нажмете на диск. Я только попробовал индексирование btree Postgres по умолчанию. Я предполагаю, что разделение PHP не занимает много времени - неправда, но, вероятно, довольно разумное приближение.
Все тесты выполняются на 8-стороннем Mac Pro 2.66 Xeon 16 ГБ RAID-0 7200 об/мин
Кроме того, этот набор данных состоит из 26 миллионов строк, что, вероятно, немного больше, чем волнует большинство людей...
Очевидно, грубая скорость - это не единственное, что вас волнует. Во многих (большинстве?) приложениях вы бы больше заботились о логической "правильности" их отдельной выборки. Но, когда дело доходит до того, что ваш босс говорит: "Нам нужно, чтобы это шло быстрее", это (по-видимому) ускорит вас в 2 раза. ОП прямо спросил об эффективности. Счастлив?
Если у вас 1 миллион пользователей, вы предпочитаете (учитывая, что половина из них - мужчины, а половина - женщины) :
- извлечение 1 миллиона пользователей из базы данных?
- или только извлечение 500 тыс. пользователей из БД?
Я полагаю, вы ответите, что предпочитаете получать только половину пользователей;-) И, в зависимости от условия, если оно более сложное, оно может быть даже меньше этого.
В принципе, получение меньшего количества данных означает:
- меньше сеть, используемая "ни за что" (т.е. для извлечения данных, которые будут немедленно отброшены)
- меньше используемой памяти, особенно на PHP-сервере
- потенциально меньший доступ к диску на сервере MySQL - так как с диска требуется меньше данных
В общих случаях мы стараемся избегать получения большего количества необходимых данных; т. Е. Мы размещаем фильтрацию на стороне базы данных.
Конечно, это означает, что вам придется подумать об индексах, которые вы разместите на своем таблицы базы данных: они должны соответствовать потребностям запросов, которые вы будете выполнять.