VPF::Выборка записей по ID на большом объеме данных

Sunvas

Дата 26.7.2007, 11:12 (ссылка)

(нет голосов)

Загрузка ...

Соль и сахар

Профиль
Группа: Участник
Сообщений: 3388
Регистрация: 12.3.2006
Где: Тосно

Репутация: 2
Всего: 89

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

Это сообщение отредактировал(а) mishaSL - 12.9.2007, 15:48

--------------------

Воспитывая детей по своему образу и подобию, родители почему-то надеются, что они будут лучше их.

GZep

Дата 26.7.2007, 11:35 (ссылка)

(голосов:5)

Загрузка ...

участник Винграда

Профиль
Группа: Завсегдатай
Сообщений: 1528
Регистрация: 7.7.2006
Где: Москва

Репутация: -1
Всего: 32

Код


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 (ссылка)

(голосов:3)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5

Код


SELECT *
FROM table
WHERE id IN (value1, value2, value2)

Пример ясен?

--------------------

НЕДОМА в маси

Mal Hack

Дата 26.7.2007, 13:51 (ссылка)

(нет голосов)

Загрузка ...

Мудрый...

Профиль
Группа: Участник Клуба
Сообщений: 9926
Регистрация: 15.2.2004

Репутация: 11
Всего: 261

GZep, синтаксически неверно.

Вариант Diesel Draft, приемлим, рационален.

sTa1kEr

Дата 26.7.2007, 16:32 (ссылка)

(нет голосов)

Загрузка ...

9/10 программиста

Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146

Конструкция"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 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5

А если одновременно 2 запита пойдут smile

Ты ж не защитил

--------------------

НЕДОМА в маси

sTa1kEr

Дата 26.7.2007, 17:35 (ссылка)

(нет голосов)

Загрузка ...

9/10 программиста

Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146

Цитата(Diesel Draft @ 26.7.2007, 17:21

)

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

CREATE TABLE Syntax

Цитата

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 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5

А я не увидел, думал ты обычную хочешь впихнуть.

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

--------------------

НЕДОМА в маси

sTa1kEr

Дата 26.7.2007, 17:56 (ссылка)

(нет голосов)

Загрузка ...

9/10 программиста

Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146

Цитата(Diesel Draft @ 26.7.2007, 17:44

)

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

Таблица находится в памяти и вставка 1000 строк с одним столбцом одним запросом будет моментальная, а вот выборка через IN будет намного медленнее, чем через JOIN.

Diesel Draft

Дата 26.7.2007, 17:57 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5

Почему? Ну вот ты так сказал, а ты на что то опирается?

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

Это сообщение отредактировал(а) Diesel Draft - 26.7.2007, 17:58

--------------------

НЕДОМА в маси

Mal Hack

Дата 26.7.2007, 18:20 (ссылка)

(нет голосов)

Загрузка ...

Мудрый...

Профиль
Группа: Участник Клуба
Сообщений: 9926
Регистрация: 15.2.2004

Репутация: 11
Всего: 261

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

Diesel Draft

Дата 26.7.2007, 18:23 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5

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

--------------------

НЕДОМА в маси

Mal Hack

Дата 26.7.2007, 18:51 (ссылка)

(нет голосов)

Загрузка ...

Мудрый...

Профиль
Группа: Участник Клуба
Сообщений: 9926
Регистрация: 15.2.2004

Репутация: 11
Всего: 261

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

sTa1kEr

Дата 26.7.2007, 22:57 (ссылка)

(голосов:4)

Загрузка ...

9/10 программиста

Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146

Цитата(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 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5

sTa1kEr, ты герой smile

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

--------------------

НЕДОМА в маси

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| PHP: Базы Данных \| Следующая тема »