Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > PHP: Базы Данных > Выборка записей по ID на большом объеме данных

Автор: Sunvas 26.7.2007, 11:12

Есть таблица в которой ~1000000 записей. В ПХП есть одномерный массив, содежащий ~1000 чисел. Как за минимальное количество запросов вытащить из таблицы все записи ИДы которых содержаться в массиве?

Автор: GZep 26.7.2007, 11:35

Код for ($i = 1, $tmp = $ids[0]; $i < sizeof($ids); $i++)     $tmp .= " or " . $ids[$i]; mysql_query("SELECT ... WHERE `id` = " . $tmp) что-то типа такого...

Автор: Diesel Draft 26.7.2007, 12:03

Код SELECT * FROM table WHERE id IN (value1, value2, value2) Пример ясен?

Автор: Mal Hack 26.7.2007, 13:51

GZep, синтаксически неверно. Вариант Diesel Draft, приемлим, рационален.

Автор: sTa1kEr 26.7.2007, 16:32

Конструкция"id IN (value1, value2, value2)" работает достаточно медленно, тем более с таким количеством элементов. По этому я бы предложил в данном случае использовать временную таблицу. Код $ids = array(1, 2, 3, 4, 5, 6, 7); $query = "    CREATE TEMPORARY TABLE `tmp` (       `tempid` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,    PRIMARY KEY (`tempid`))"; mysql_query($query); $query = "INSERT INTO tmp (`tempid`) VALUES (".implode('), (', $ids).")"; mysql_query($query); $r = mysql_query("SELECT * FROM table AS t INNER JOIN tmp AS tm ON t.id = tm.tempid"); while ($row = mysql_fetch_assoc($r)) {    // ... } Имхо, это будет намного производительнее. Таблица создается в памяти на время коннекта к базе и эффективность INNER JOIN должна оправдать два лишних запроса.

Автор: Diesel Draft 26.7.2007, 17:21

А если одновременно 2 запита пойдут  Ты ж не защитил

Автор: sTa1kEr 26.7.2007, 17:35

Цитата(Diesel Draft @  26.7.2007,  17:21 ) А если одновременно 2 запита пойдут smile Ты ж не защитил  http://dev.mysql.com/doc/refman/4.1/en/create-table.html Цитата From MySQL 3.23 on, you can use the TEMPORARY  keyword when creating a table. A TEMPORARY  table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) From MySQL 4.0.2 on, to create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. Другими словами, временная таблица создается для одного коннекта и видима только в пределах этого коннекта, по этому можно не опасатся конфликтов.

Автор: Diesel Draft 26.7.2007, 17:44

А я не увидел, думал ты обычную хочешь впихнуть.  Но все ровно подумай, операция записи в БД медленней работает чем выборка данных. Если ты считаешь что IN будет тормозить то можно через or делают, но это вовсе БД убьет вовсе

Автор: sTa1kEr 26.7.2007, 17:56

Цитата(Diesel Draft @  26.7.2007,  17:44 ) Но все ровно подумай, операция записи в БД медленней работает чем выборка данных.  Таблица находится в памяти и вставка 1000 строк с одним столбцом одним запросом будет моментальная, а вот выборка через IN будет намного медленнее, чем через JOIN.

Автор: Diesel Draft 26.7.2007, 17:57

Почему? Ну вот ты так сказал, а ты на что то опирается? П.С. Кстати 1000000 записей в Мускуле? Это немного жестоко получается. наверно уже притормаживает

Автор: Mal Hack 26.7.2007, 18:20

Столько шума из ничего... Ну создадите вы временную таблицу, а толку? Выборку-то все равно надо делать...

Автор: Diesel Draft 26.7.2007, 18:23

А где это ты будешь юзать?

Автор: Mal Hack 26.7.2007, 18:51

Временную таблицу? Когда выборка будет многоступенчатой...

Автор: sTa1kEr 26.7.2007, 22:57

Цитата(Mal Hack @  26.7.2007,  18:20 ) Столько шума из ничего... Где вы увидели шум? Я просто предложил альтернативный более сложный, но и более производительный вариант. Да и что плохого в нескольких решениях? Я же никому не навязываю свое решение. А кому-то может будет познавательно просто узнать про временные таблицы. Цитата(Mal Hack @  26.7.2007,  18:20 ) Ну создадите вы временную таблицу, а толку? Выборку-то все равно надо делать...  Смысл временной таблицы я изложил выше. Написал маленький тестик для сравнения производительности. PHP 5.2.2 MySQL 5.0.38 Тестовая таблица: Код CREATE TABLE `test` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `data` VARCHAR( 50 ) NOT NULL , `timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ) ENGINE = MYISAM ; Наполнение таблицы: Код $db = new mysqli("localhost", "root", "", "test") or die($db->error); // Вставка 10 млн строк. При выборке из первого млн. строк производительность такая же, // как если бы было просто 1 млн. строк. Если кто не верит, то можете сначала заполнить // только 1 млн, а затем дописать еще 9. for ($i = 0; $i < 10000; $i++)  {    $query = "INSERT INTO test (`data`, `timestamp`) VALUES ('".rand(0, 10000000)."', NOW())";    for ($j = 1; $j < 1000; $j++)    {       $query .= ", ('".rand(0, 10000000)."', NOW())"; // Рандомные данные    }    $db->query($query) or die($db->error); } echo "Ok\n"; Тестиование. Код function generateIds($numIds, $numRows) {    echo "--- Testing for $numIds IDs and $numRows rows ---\n";    $i = 0;    $ids = array();    while ($i < $numIds)    {       $id = rand (1, $numRows);       if (!in_array($id, $ids))       {          $ids[] = $id;          $i++;       }    }    return $ids; } function test_1($db, $ids) {    $time = microtime(true);    $query = "SELECT * FROM `test` WHERE id IN (".implode(", ", $ids).")";    $r = $db->query($query);    echo "Test 1: ".(microtime(true) - $time)." sec\n"; } function test_2($db, $ids) {    $time = microtime(true);    $query = "       CREATE TEMPORARY TABLE `tmp` (       `tempid` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,       PRIMARY KEY (`tempid`))";    $db->query($query);    $query = "INSERT INTO tmp (`tempid`) VALUES (".implode('), (', $ids).")";    $db->query($query);    $r = $db->query("SELECT * FROM table AS t INNER JOIN tmp AS tm ON t.id = tm.tempid");    echo "Test 2: ".(microtime(true) - $time)." sec\n";    $db->query("DROP TABLE `tmp`"); // Уничтожаем таблицу только для чистоты эксперимента, а так ее уничтожать не нужно. } $db = new mysqli("localhost", "root", "", "test"); $ids = generateIds(1000, 1000000); test_1($db, $ids); test_2($db, $ids); $ids = generateIds(1000, 10000000); test_1($db, $ids); test_2($db, $ids); $ids = generateIds(10000, 1000000); test_1($db, $ids); test_2($db, $ids); $ids = generateIds(10000, 10000000); test_1($db, $ids); test_2($db, $ids); Результаты теста Код --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.013659954071 sec Test 2: 0.00576281547546 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 0.64941906929 sec Test 2: 0.00584292411804 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 0.143054962158 sec Test 2: 0.0594811439514 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 1.94255399704 sec Test 2: 0.0605499744415 sec Как видите, при условиях Sunvas (1ый тест) временные таблицы выигрывают по времени в 2 раза. Однако, при увеличении количества строк в 10 раз, время при использовании IN увеличивается в 15 раз!, а INNER JOIN выполняется за то-же самое время.

Автор: Diesel Draft 27.7.2007, 00:05

sTa1kEr, ты герой    Просто мы делам в 3 раза больше операций

Автор: FractalizeR 15.1.2008, 13:56

Ну, и что, до сих пор никто не нашел ошибку sTa1kEr? Тогда позвольте мне.... Обратите внимание на вот эту строчку: Код $r = $db->query("SELECT * FROM table AS t INNER JOIN tmp AS tm ON t.id = tm.tempid"); Откуда у нас взялась таблица table? Мы ее не создавали! У нас есть только tmp (временная) и test (исходная). В результате этот запрос всегда НЕ ВЫПОЛНЯЕТСЯ, что создает ИЛЛЮЗИЮ того, что эта сумасшедшая комбинация действительно быстрее, чем IN конструкция... Если исправить этот table на test, как и должно быть, результаты теста будут совсем другими:  Код C:\temp>php test.php --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.056794881820679 sec Test 2: 0.14567685127258 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 0.06480598449707 sec Test 2: 0.15046095848083 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 0.48508286476135 sec Test 2: 0.64331102371216 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 0.65538215637207 sec Test 2: 0.86895084381104 sec Отсюда мораль: всегда проверяйте результат выполнения запроса. sTa1kEr использует не слишком хорошо написанный класс по работе с базой данных, который, почему-то не выбрасывает ни ошибки, не исключения при ошибке в синтаксисе запроса.... Мой вариант PHP файла, на котором проводился тест, с использованием стандартных функций MySQL: Код <?php $db = mysql_connect('localhost', 'root', 'root'); if (!$db) {    die('Could not connect: ' . mysql_error()); } mysql_select_db('testin'); function generateIds($numIds, $numRows) {    echo "--- Testing for $numIds IDs and $numRows rows ---\n";    $i = 0;    $ids = array();    while ($i < $numIds)    {       $id = rand (1, $numRows);       if (!in_array($id, $ids))       {          $ids[] = $id;          $i++;       }    }    return $ids; } function test_1($db, $ids) {    $time = microtime(true);    $query = "SELECT * FROM `test` WHERE id IN (".implode(",", $ids).")";    $r = mysql_query($query) or die('Test 1 query error!');    echo "Test 1: ".(microtime(true) - $time)." sec\n"; } function test_2($db, $ids) {    $time = microtime(true);    $query = "       CREATE TEMPORARY TABLE `tmp` (       `tempid` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,       PRIMARY KEY (`tempid`))";    mysql_query($query) or die('Test 2-1 query error!');    $query = "INSERT INTO tmp (`tempid`) VALUES (".implode('), (', $ids).")";    mysql_query($query)or die('Test 2-2 query error!');    mysql_query("SELECT * FROM test AS t INNER JOIN tmp AS tm ON t.id = tm.tempid")or die('Test 2-3 query error!');    mysql_query("DROP TABLE `tmp`")or die('Test 2-4 query error!'); // Уничтожаем таблицу только для чистоты эксперимента, а так ее уничтожать не нужно.    echo "Test 2: ".(microtime(true) - $time)." sec\n"; } $ids = generateIds(1000, 1000000); test_1($db, $ids); test_2($db, $ids); $ids = generateIds(1000, 10000000); test_1($db, $ids); test_2($db, $ids); $ids = generateIds(10000, 1000000); test_1($db, $ids); test_2($db, $ids); $ids = generateIds(10000, 10000000); test_1($db, $ids); test_2($db, $ids); ?>

Автор: Sunvas 15.1.2008, 14:02

Хм, видимо придется поднять старую темку.

Автор: SelenIT 15.1.2008, 19:17

Во-первых, FractalizeRу плюс за глазастость! Но ситуация несколько сложнее. Я тестировал оба варианта (с mysqli и mysql_xxx) на двух машинах - стареньком Duron-1200 с 512 "мозгами" (одной планкой) и ноутбуке с Core2Duo T5500 (1.66 ГГц) и гигагабайтом памяти (двухканальной), оба под WinXP (к сожалению). Результаты вот: Duron-1200: Цитата mysqli (вариант sTa1kErа с исправленной ошибкой): --- Testing for 1000 IDs and 1000000 rows --- Test 1: 3.36059403419 sec Test 2: 0.0949058532715 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 14.3087098598 sec Test 2: 0.0993778705597 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 4.78998088837 sec Test 2: 0.497647047043 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 89.8976659775 sec Test 2: 0.560079097748 sec То же, закомменчено удаление временной таблицы: --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.602582931519 sec Test 2: 0.0892629623413 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 11.9096381664 sec Test 2: 0.0678989887238 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 1.6547908783 sec Test 2: 0.141529083252 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 87.7105460167 sec Test 2: 0.170500993729 sec Классический mysql (вариант FractalizeRа): --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.594168901443 sec Test 2: 0.100327014923 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 11.9726989269 sec Test 2: 0.102813005447 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 1.57574701309 sec Test 2: 0.53061914444 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 75.2531871796 sec Test 2: 0.555109977722 sec Core2Duo T5500: Цитата mysqli (вариант sTa1kErа с исправленной ошибкой): --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.0285499095917 sec Test 2: 0.101317882538 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 0.0348329544067 sec Test 2: 0.114646911621 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 0.195830821991 sec Test 2: 0.326441764832 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 0.245867013931 sec Test 2: 0.439071893692 sec То же, закомменчено удаление временной таблицы: --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.0287158489227 sec Test 2: 0.0863630771637 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 0.0342700481415 sec Test 2: 0.0620341300964 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 0.193419933319 sec Test 2: 0.280586004257 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 0.246789932251 sec Test 2: 0.233731985092 sec Классический mysql (вариант FractalizeRа): --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.0272779464722 sec Test 2: 0.188948154449 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 0.0334341526031 sec Test 2: 0.1519510746 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 0.19315290451 sec Test 2: 0.380733013153 sec --- Testing for 10000 IDs and 10000000 rows --- Test 1: 0.247782945633 sec Test 2: 0.466258049011 sec Поражают как тормоза варианта с IN на старой одноядерной машине, так и быстрота варианта с джойном (без принудительного удаления времянки) на ней же. При хорошем запасе памяти и ресурсов проца, безусловно, вариант с IN (особенно при небольшом диапазоне) выглядит куда лучше, но вариант с времянкой, похоже, менее зависим от этих "мелочей". Надо бы протестить в более реальных условиях - под *NIX, под нагрузкой... Добавлено через 5 минут и 34 секунды Цитата(FractalizeR @  15.1.2008,  13:56 ) sTa1kEr использует не слишком хорошо написанный класс по работе с базой данных Это не класс, это улучшенное (по сравнению со старой php_mysql) расширение PHP5. Поддерживаются несколько запросов за вызов и новые возможности самой базы, но "волшебства" для перехвата ошибок в нем, увы, нет.

Автор: FractalizeR 16.1.2008, 00:46

Меня вообще-то поражает не это. Цитата То же, закомменчено удаление временной таблицы: --- Testing for 1000 IDs and 1000000 rows --- Test 1: 0.602582931519 sec Test 2: 0.0892629623413 sec --- Testing for 1000 IDs and 10000000 rows --- Test 1: 11.9096381664 sec Test 2: 0.0678989887238 sec --- Testing for 10000 IDs and 1000000 rows --- Test 1: 1.6547908783 sec Test 2: 0.141529083252 sec Тестирование 1.000 тысячи записей с IN заняло 11.9 секунд, а тот же самый тест с IN но для 10.000 (в 10 раз больше) записей занял 1.6 секунды (в 10 раз меньше). Вам это не кажется странным? В общем, я думаю, вы просто что-то сделали неправильно. IN со скалярными значениями всегда быстрее любых других вариантов должен быть.

Автор: SelenIT 16.1.2008, 17:40

Цитата(FractalizeR @  16.1.2008,  00:46 ) Тестирование 1.000 тысячи записей с IN заняло 11.9 секунд, а тот же самый тест с IN но для 10.000 (в 10 раз больше) записей занял 1.6 секунды (в 10 раз меньше). Вам это не кажется странным? Не в 10, а в 7, если быть совсем точным . Нет, не удивляет - объем данных, перелопаченных сервером, в 10 раз меньше, как ни крути... Цитата(FractalizeR @  16.1.2008,  00:46 ) IN со скалярными значениями всегда быстрее любых других вариантов должен быть. Не могу за это поручиться, когда речь идет о MySQL - у нее, насколько я помню, "наследственные проблемы" с оптимизацией множественных OR в условии WHERE (а IN, насколько я понимаю - частный случай этого по сути). В каких-то версиях доходило до того, что UNION отдельных запросов с единственным значением в каждом оказывался быстрее. Нужно будет протестить как следует - чисто базу, без PHP, посмотреть EXPLAIN, желательно под никсами... постараюсь сделать ближе к вечеру, если кто сделает раньше/параллельно - буду только рад.

Автор: FractalizeR 16.1.2008, 17:54

Цитата Не в 10, а в 7, если быть совсем точным smile. Нет, не удивляет - объем данных, перелопаченных сервером, в 10 раз меньше, как ни крути... Не в 10 раз меньше, а в 10 раз больше. Посмотрите внимательно на результаты теста. Тысяча строк отобрана за двенадчать секунд, а десять тысяч - за две секунды! Цитата у нее, насколько я помню, "наследственные проблемы" с оптимизацией множественных OR в условии WHERE (а IN, насколько я понимаю - частный случай этого по сути) Почитайте мануал MySQL по использованию конструкции IN и вам все станет понятно.

Автор: SelenIT 16.1.2008, 18:39

Цитата(FractalizeR @  16.1.2008,  17:54 ) Тысяча строк отобрана за двенадчать секунд, а десять тысяч - за две секунды! Но 10 тыс. отобраны из одного миллиона, а 1 тыс. - из десяти миллионов! Посмотрите внимательно на условия теста ;). Есть же разница для сервера, имхо... Цитата(FractalizeR @  16.1.2008,  17:54 ) Почитайте мануал MySQL по использованию конструкции IN и вам все станет понятно.  Вот относительно недавний http://www.jpipes.com/index.php?/archives/177-Common-Questions-and-Answers-from-Performance-Tuning-Webinars.html, первый же вопрос которого полностью совпадает с тем, что некогда крепко засело в моем мозгу (причем автор утверждает, что и ORы, и IN с константами используют бинарный поиск - судя по комментам http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html, начиная с 4-й версии). Но ведь все равно, насколько я понимаю, этот бинарный поиск будет делаться для каждой строки исходной таблицы... Насчет UNOIN'а, признаю, прогнал (судя по тем же комментам).

Автор: FractalizeR 16.1.2008, 19:01

Цитата Но 10 тыс. отобраны из одного миллиона, а 1 тыс. - из десяти миллионов! Посмотрите внимательно на условия теста ;). Есть же разница для сервера, имхо... Количество строк в таблице никто не менял. Откуда там один миллион и десять миллионов? Просто опять же автор теста допустил ошибку и тут. А я ее за ним скопировал. Нолик не дописан. Цитата Вот относительно недавний FAQ, первый же вопрос которого полностью совпадает с тем, что некогда крепко засело в моем мозгу О том, как работает IN написано в мануале по MySQL. О ветке 3.x MySQL давно уже пора забыть.

Автор: SelenIT 16.1.2008, 19:23

Цитата(FractalizeR @  16.1.2008,  19:01 ) Откуда там один миллион и десять миллионов? ... Вы невнимательно прочли условия теста и код, который создавал таблицу Цитата(sTa1kEr @  26.7.2007,  22:57 ) При выборке из первого млн. строк производительность такая же, // как если бы было просто 1 млн. строк. Если кто не верит, то можете сначала заполнить // только 1 млн, а затем дописать еще 9. Имхо, логично - индексу приходится шерстить в десять раз меньший диапазон, нагрузка на базу меньше... Цитата(FractalizeR @  16.1.2008,  19:01 ) О том, как работает IN написано в мануале по MySQL. Читал. Сортирует, потом бинарный поиск. Написано, что это very quick, если в списке одни константы одного типа. Но вряд ли при этом предполагаются десятитысячные диапазонища...  Цитата(FractalizeR @  16.1.2008,  19:01 ) О ветке 3.x MySQL давно уже пора забыть. Пора, не спорю. Не всегда получается ;)

Автор: sTa1kEr 16.1.2008, 19:58

Извините, не имею тех тестов под рукой, надо будет поискать их. Но каюсь, что пред отправкой сообщения, я менял имена таблиц (и потом еще и редактировал пост с тем же намеренем), т.ч. сейчас уже не вспомню, толи это моя опечатка тут, толи в реальном тесте. Но 1. Количество строк действительно было разное, что я и написал в посте. 2. С корректными запросами я точно проводил тест. 3. Это было неоднократно замечено и испробовано на рабочих, реальных данных. Причем не только в MySQL но и в MSSQL, результат один - IN всегда проигрывает временной таблице, хранящейся в памяти(важно, что именно в памяти, т.к. можно и на диске создать временную). А на особо сложных запросах, это настолько особо критично.

Автор: FractalizeR 16.1.2008, 20:05

Хотелось бы, чтобы вы привели образцы скриптов, где IN медленнее, чтобы я смог провести тестирование на своем компьютере. Мне с трудом верится, что конструкция IN может быть настолько неоптимальной. Конечно, в MySQL 3.23 ее использование растягивалось в цепь OR и индекс не использовался. Но 3.23 пора забыть, как страшный сон  Что касается количества рядов - то ведь оно касалось не таблицы, а функции, которая генерирует случайные id от нуля до переданного ей количества строк в таблице. Но сама таблица не менялась.

Автор: SelenIT 17.1.2008, 06:26

FractalizeR, попробуйте Ваш же скрипт на таблице с еще на порядок большим (100 млн.) числом записей. Свои результаты запощу позже - "для чистоты эксперимента".

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)