Хорошо, вот простая абстракция проблемы:
2 переменных (male_users и female_users) для хранения 2 групп пользователей, т.е. мужчин и женщин
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} }
какой из них более эффективен и рассматривается как лучший подход?
это просто простая иллюстрация проблемы. реальный проект может иметь таблицы lager для запроса и дополнительные параметры фильтра.
заранее спасибо!
Эмпирическое правило для любого приложения – позволить БД делать то, что он делает хорошо: фильтрация, сортировка и объединение.
Разделите запросы на свои собственные функции или методы класса:
$men = $foo->fetchMaleUsers(); $women = $foo->fetchFemaleUsers();
Я воспринял демонстрацию Steve 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), поскольку он более реалистичен для цели этого столбца. Я также предоставляю первичный ключ, как вы ожидали бы от таблицы вместо произвольного значения 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 продемонстрировал бы подобную производительность при правильном использовании.
Также обратите внимание, что на этой же машине слишком упрощенный цикл, выполняющий 52 миллиона сравнений, занимает дополнительно 7,3 секунды для выполнения.
<?php $N = 52000000; for($i = 0; $i < $N; $i++) { if (true == true) { } }
Я думаю, что довольно очевидно, что лучше подходит с учетом этих данных.
Я бы сказал, что нет никакой причины заставить вашу БД выполнять дополнительную работу по оценке предложения WHERE. Учитывая, что вы действительно хотите все записи, вам придется выполнять работу по их извлечению. Если вы сделаете одиночный SELECT из таблицы, он будет извлекать их все в таблице-порядке, и вы можете разбить их самостоятельно. Если вы выбрали WHERE male и SELECT WHERE female, вам нужно нажать индекс для каждой операции, и вы потеряете некоторую локальность данных.
Например, если ваши записи на диске чередуются с мужчиной-женщиной, и у вас есть набор данных, намного больший, чем память, вам, вероятно, придется дважды считывать всю базу данных, если вы делаете два отдельных запроса, тогда как один SELECT для обоих будет односкатное сканирование.
РЕДАКТИРОВАТЬ: Поскольку я опускаюсь в небытие, я решил провести тест. Я создал таблицу
СОЗДАТЬ ВРЕМЕННЫЙ ТАБЛИЦ gender_test (some_data DOUBLE PRECISION, gender CHARACTER VARYING (20));
Я создал некоторые случайные данные,
выберите пол, счетчик (*) из группы gender_test по полу;
пол | подсчитывать
——– + ———-
женщины | 12603133
мужчина | 10465539
(2 строки)
Во-первых, давайте проведем эти тесты без индексов, и в этом случае я уверен, что я прав …
test => EXPLAIN ANALYZE SELECT * FROM gender_test WHERE gender = 'male';
ПЛАН QUERY
Seq Scan on gender_test (стоимость = 0.00..468402.00 строк = 96519 ширина = 66) (фактическое время = 0.030..4595.367 строк = 10465539 циклов = 1)
Фильтр: ((пол) :: text = 'male' :: text)
Общая продолжительность работы: 5150,263 мсtest => EXPLAIN ANALYZE SELECT * FROM gender_test WHERE gender = 'female';
ПЛАН QUERY
Seq Scan on gender_test (cost = 0.00..468402.00 rows = 96519 width = 66) (фактическое время = 0,029..4751.219 rows = 12603133 loops = 1) Фильтр: ((пол) :: text = 'female' :: text)
Общая продолжительность выполнения: 5418,891 мсtest => EXPLAIN ANALYZE SELECT * FROM gender_test;
ПЛАН QUERY
Seq Scan on gender_test (cost = 0.00..420142.40 rows = 19303840 width = 66) (фактическое время = 0,021..3326.164 rows = 23068672 loops = 1)
Общее время выполнения: 4543.393 мс (2 строки)
Забавно, похоже, что получение данных в сканировании таблицы без фильтра действительно быстрее! На самом деле, более чем в два раза быстрее! (5150 + 5418> 4543). Как я и предсказывал! :-п
Теперь давайте сделаем индекс и посмотрим, изменит ли он результаты …
CREATE INDEX test_index ON gender_test (пол);
Теперь, чтобы повторить те же запросы …
test => EXPLAIN ANALYZE SELECT FROM gender_test WHERE gender = 'male';
ПЛАН QUERY
Сканирование растровой карты на gender_test (cost = 2164.69..195922.27 rows = 115343 width = 66) (фактическое время = 2008.877..4388.348 rows = 10465539 loops = 1)
Recheck Cond: ((пол) :: text = 'male' :: text)
-> Индекс битовой карты Сканирование на test_index (стоимость = 0,00..2135.85 строк = 115343 ширина = 0) (фактическое время = 2006.047..2006.047 rows = 10465539 loops = 1)
Index Cond: ((пол) :: text = 'male' :: text)
Общая продолжительность работы: 4941,64 мсtest => EXPLAIN ANALYZE SELECT * FROM gender_test WHERE gender = 'female';
ПЛАН QUERY
Сканирование растровой кучи на gender_test (cost = 2164.69..195922.27 rows = 115343 width = 66) (фактическое время = 1915.385..4269.933 rows = 12603133 loops = 1)
Recheck Cond: ((пол) :: text = 'female' :: text)
-> Индекс растрового изображения Сканирование на test_index (стоимость = 0,00..2135.85 строк = 115343 ширина = 0) (фактическое время = 1912.587..1912.587 rows = 12603133 loops = 1)
Index Cond: ((пол) :: text = 'female' :: text)
Общая продолжительность выполнения: 4931,555 мс (5 строк)test => EXPLAIN ANALYZE SELECT * FROM gender_test;
ПЛАН QUERY
Seq Scan on gender_test (cost = 0.00..457790.72 rows = 23068672 width = 66) (фактическое время = 0,021..3304,836 строк = 23068672 циклов = 1)
Общая продолжительность выполнения: 4523.754 мс
Забавно … сканирование всей таблицы за один раз еще в два раза быстрее! (4941 + 4931 против 4523)
ПРИМЕЧАНИЕ . Всевозможные способы ненаучности. Я работаю с 16 ГБ оперативной памяти, поэтому весь набор данных вписывается в память. Postgres не настроен на использование почти так много, но кеш диска все еще помогает … Я бы предположил (но не может быть уверен, что на самом деле попытаюсь), что эффекты только ухудшатся, как только вы нажмете диск. Я попробовал только индексирование btree Postgres по умолчанию. Я предполагаю, что разбиение на PHP не требует времени – не верно, но, вероятно, довольно разумное приближение.
Все тесты выполняются на Mac Pro 8-way 2.66 Xeon 16GB RAID-0 7200 об / мин
Кроме того, этот набор данных составляет 26 миллионов строк, что, вероятно, немного больше, чем большинство людей заботятся о …
Очевидно, что необработанная скорость – это не единственное, что вам нужно. Во многих (большинстве?) Приложениях вам будет больше нужна логическая «правильность» для их получения отдельно. Но, когда дело доходит до вашего босса, говорящего «нам нужно, чтобы это ускорилось», это, по-видимому, даст вам 2x ускорение. OP явно задал вопрос об эффективности. Счастливый?
Если у вас 1 миллион пользователей, вы предпочитаете (учитывая, что половина из них – мужчина, а половина женщины) :
Я полагаю, вы ответите, что предпочитаете получать только половину пользователей 😉 И, в зависимости от условия, если он более сложный, он может быть даже меньше этого.
В принципе, выборка данных меньше:
В общем случае мы стараемся избегать получения большего количества необходимых данных; т.е. мы размещаем фильтрацию на стороне базы данных.
Конечно, это означает, что вам нужно будет подумать об индексах, которые вы поместите в таблицы базы данных: они должны будут соответствовать потребностям запросов, которые вы будете делать.