Лучше отфильтровать набор результатов с помощью оператора Where или с помощью кода приложения?
Хорошо, вот простая абстракция проблемы:
2 переменные (male_users и female_users) для хранения 2 групп пользователя т.е. штекера и розетки
- 1 путь состоит в том, чтобы использовать два запроса для выбора их:
select * from users where gender = 'male' и затем сохраните результат в male_users
select * from users where gender = 'female'и затем сохраните результат в female_users
- иначе должен выполнить только один запрос:
'select * from users'и затем цикл по набору результатов для фильтрации штекерных пользователей в программе php фрагмент кода был бы sth как это:
$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 ответа
Практическое правило для любого приложения - позволить БД делать то, что она делает хорошо: фильтровать, сортировать и объединять.
Разделите запросы на их собственные функции или методы класса:
$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), поскольку это более реалистично для целей этого столбца, я также предоставляю первичный ключ, как и следовало ожидать от table вместо произвольного значения DOUBLE.
Неиндексированные результаты
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 продемонстрировал бы аналогичную производительность при правильном использовании.
Также обратите внимание, что на этой же машине чрезмерно упрощенный цикл for, выполняющий 52 миллиона сравнений, требует дополнительных 7,3 секунды для выполнения.
<?php
$N = 52000000;
for($i = 0; $i < $N; $i++) {
if (true == true) {
}
}
Я думаю, что довольно очевидно, какой подход лучше, учитывая эти данные.
Если у вас 1 миллион пользователей, вы предпочитаете (учитывая, что половина из них - мужчины, а половина - женщины) :
- получение 1 миллиона пользователей из БД?
- или получение только 500 тыс. Пользователей из БД?
Я полагаю, вы ответите, что предпочитаете получать только половину пользователей ;-) И, в зависимости от условия, если сложнее, то может быть и меньше.
По сути, получение меньшего количества данных означает:
- меньшее количество сети, используемой «впустую» (т.е. для получения данных, которые будут немедленно удалены)
- меньше используемой памяти, особенно на сервере PHP
- потенциально меньший доступ к диску на сервере MySQL - так как данных для выборки с диска меньше
В общих случаях мы стараемся избегать получения большего количества необходимых данных; т.е. размещаем фильтрацию на стороне базы данных.
Конечно, это означает, что вам придется подумать об индексах, которые вы разместите в таблицах вашей базы данных: они должны будут соответствовать потребностям запросов, которые вы будете выполнять.
Я бы сказал, что на самом деле нет причин заставлять вашу БД выполнять дополнительную работу по оценке предложения WHERE. Учитывая, что вам на самом деле нужны все записи, вам придется выполнить работу по их извлечению. Если вы сделаете один SELECT из таблицы, он извлечет их все в табличном порядке, и вы можете разделить их самостоятельно. Если вы ВЫБИРАЕТЕ WHERE male и SELECT WHERE female, вам придется использовать индекс для каждой операции, и вы потеряете некоторую локальность данных.
Например, если ваши записи на диске чередуются между мужчинами и женщинами, и у вас есть набор данных, намного превышающий размер памяти, вам, вероятно, придется дважды прочитать всю базу данных, если вы выполните два отдельных запроса, тогда как один SELECT для обоих будет сканирование одной таблицы.
РЕДАКТИРОВАТЬ: Поскольку я забываю, я решил запустить тест. Я создал таблицу
СОЗДАТЬ ВРЕМЕННУЮ ТАБЛИЦУ гендерный_тест (some_data ДВОЙНАЯ ТОЧНОСТЬ, пол CHARACTER VARYING (20));
Я создал некоторые случайные данные,
выберите пол, количество (*) из группы gender_test по полу;
пол | count
-------- + ----------
женский | 12603133
мужской | 10465539
(2 строки)
Во-первых, давайте запустим эти тесты без индексов, и в этом случае я совершенно уверен, что прав ...
test => EXPLAIN ANALYZE SELECT * FROM пол_теста ГДЕ пол = 'мужской';
ПЛАН ЗАПРОСА
Последовательное сканирование по гендерному_тесту (стоимость = 0,00..468402,00 строк = 96519 ширина = 66) (фактическое время = 0,030..4595,367 {{ 1}} rows = 10465539 loops = 1)
Фильтр: ((пол) :: text = 'male' :: text)
Общее время выполнения : 5150,263 мсtest => ОБЪЯСНИТЬ АНАЛИЗ ВЫБРАТЬ * FROM пол_теста WHERE пол = 'женский';
ПЛАН ЗАПРОСА
Последовательное сканирование при гендерном_тесте (стоимость = 0,00 .. 468402,00 строк = 96519 ширина = 66) (фактическое время = 0,029..4751,219 строк = 12603133 цикла = 1) Фильтр: ((пол) :: text = 'female' :: text)
{{ 1}} Общее время выполнения: 5418,891 мсtest => EXPLAIN ANALYZE SELECT * FROM пол_теста;
ПЛАН ЗАПРОСА
Последовательное сканирование по гендерному_тесту (стоимость = 0,00..420142,40 рядов = 19303840 ширина = 66) (фактическое время = 0,021..3326,164 рядов = 23068672 петель = 1)
До время выполнения: 4543,393 мс (2 строки)
Забавно, похоже, что выборка данных при сканировании таблицы без фильтра действительно выполняется быстрее! Фактически, более чем в два раза быстрее! (5150 + 5418> 4543) Как я и предсказывал! :-p
Теперь давайте сделаем индекс и посмотрим, изменит ли он результаты ...
СОЗДАТЬ ИНДЕКС test_index ON пол_теста (пол);
Теперь, чтобы повторить те же запросы ...
test => ОБЪЯСНИТЬ АНАЛИЗ ВЫБРАТЬ ИЗ гендерного_теста WHERE пол = 'мужской';
ПЛАН ЗАПРОСА
Сканирование кучи растрового изображения при тестировании пола (стоимость = 2164,69..195922,27 строк = 115343 ширина = 66) (фактическое время = 2008,877..4388,348 строк = 10465539 циклов = 1)
{{1 }} Перепроверьте Cond: ((пол) :: text = 'male' :: text)
-> Сканирование растрового индекса на test_index (cost = 0.00. .2135,85 строк = 115343 ширина = 0) (фактическое время = 2006,047..2006,047 строк = 10465539 циклов = 1)
Индекс Cond: ((пол) :: text = 'male' :: text)
Общее время выполнения: 4941,64 мсtest => EXPLAIN ANALYZE SELECT * FROM пол_теста WHERE пол = 'female';
ПЛАН ЗАПРОСА
Сканирование кучи битового изображения при гендерном_тесте (стоимость = 2164,69..195922,27 строк = 115343 ширина = 66) (фактическое время = 1915,385..4269,933 строк = 12603133 петель = 1)
Перепроверьте Cond: ((пол) :: text = 'женский ':: text)
-> Сканирование растрового индекса по test_index (cos t = 0,00..2135,85 строк = 115343 ширина = 0) (фактическое время = 1912,587..1912,587 строк = 12603133 цикла = 1)
Индекс Cond: ((пол) :: text = 'female' :: text)
Общее время выполнения: 4931,555 мс (5 строк)test => EXPLAIN ANALYZE SELECT * FROM gender_test;
ПЛАН ЗАПРОСА
Последовательное сканирование при гендерном_тесте (стоимость = 0,00..457790,72 строк = 23068672 ширина = 66) (фактическое время = 0,021..3304,836 строк = 23068672 цикла = 1)
Общее время выполнения: 4523,754 мс
Забавно .... сканирование всей таблицы за один присест все равно вдвое быстрее! (4941 + 4931 против 4523)
ПРИМЕЧАНИЕ Это ненаучно по-разному.Я использую 16 ГБ ОЗУ, поэтому весь набор данных умещается в памяти. Postgres не настроен для использования почти так много, но дисковый кеш все еще помогает ... Я бы предположил (но не могу предположить, чтобы на самом деле попробовать), что эффекты только ухудшаются, когда вы нажимаете на диск. Я пробовал только индексирование Postgres по умолчанию btree. Я предполагаю, что разбиение PHP не займет много времени - неправда, но, вероятно, довольно разумное приближение.
Все тесты выполняются на Mac Pro с 8 процессорами 2,66 Xeon 16GB RAID-0 7200 об / мин
Кроме того, этот набор данных состоит из 26 миллионов строк, что, вероятно, немного больше, чем думает большинство людей ...
Очевидно ,чистая скорость - не единственное, что вас волнует. Во многих (большинстве?) Приложений вас больше заботит логическая «правильность» их раздельной выборки. Но когда дело доходит до того, что ваш босс говорит: «Нам нужно, чтобы это работало быстрее», это (по-видимому) даст вам двукратное ускорение. ОП явно спросил об эффективности. Счастливы?