простая база с одним полем, но много записей. как быстро организовать проверку и добавление?

[alexey980]

есть простейшая БД (с одним полем), состоящая из уникальных строковых ключей, типа:

SDPT234ddfs4545FFqsdfs
TXB2ZQ5d
Z0KM4Ob2Ovyd5L1iK946vQ
5729HcIuHcvabd
...
время от времени на сервер приходит пакет новых ключей в виде строки, в которой ключи разделены символом “\n” (или каким-нибудь другим разделителем – это не важно).
во вновь прибывшем пакете может быть от 100 до 3000 ключей.
для каждого нового ключа из входящего пакета нужно проверить, есть ли такой ключ в базе, и если нет, то добавить его в базу.

сейчас для хранения данных используется обычный текстовый файл f.txt, в котором ключи разделены символом “\n”, а для проверки и добавления новых ключей в базу используется следующий сценарий:

// считываем содержимое файла с имеющимся ключами в строку целиком
$s = file_get_contents('f.txt', true);  
//преобразуем вновь прибывший пакет ключей в массив 
$arr =  explode("\n", $_POST["newkeys"]);    
//подсчитываем количество новых ключей в пакете
$n = count($arr);   
 
// цикл «проверка-добавление» для каждого нового ключа из массива 
for ($i=0; $i<$n; $i++){    
    if ( strpos($s, $arr[$i]) === false){ 
        $s = arr[i]."\n".$s;
    }
}
//перезаписываем обновленную строку с ключами в файл
file_put_contents('f.txt', $s);

сейчас, в базе порядка 30 тысяч ключей, и проблем в работе сценария пока никаких нет.
но что делать, если база со временем разрастется?

для стресс-теста был сгенерирован файл, аналогичный файлу f.txt, но с количеством ключей - 2 млн. (объем файла получился около 40 Мб).
первая же попытка прогнать цикл «проверка-добавление» в массиве из 50-ти новых ключей показала, что сценарий начинает заметно тормозить.
если же массив вновь добавляемых ключей увеличить до 1000, то сценарий встает колом.

в связи с чем возникает вопрос:
как лучше реализовать такую задачу на php?
можно ли что-то придумать здесь быстрее, чем strpos()?

P.S.
в качестве сервера используется домашний ПК /Intel(R) Core(TM)2 Quad CPU Q8200 @2.33GHz 2.34Ghz, ОЗУ 4GB/ под управлением Windows 7 64бит + Denwer 3.

 

[С.]

Используй базу данных, такую как MySQL хотя бы.

 

[alexey980]

загнал свой тестовый f.txt с 2-мя миллионами строк в mysql-таблицу, используя запрос:
LOAD DATA LOCAL INFILE 'f.txt' INTO TABLE mytable

пробую первый запрос для подсчета количества записей в таблице:
SELECT COUNT(*) FROM mytable

запрос обрабатывается более 1-й минуты. это уже выглядит очень пессимистично.
я, конечно, еще повожусь немного с mysql, но почему-то кажется мне, что толку мало будет от него при таком большом количестве записей в таблице (2 млн.) и при запросах, когда нужно добавлять еще по 3 тысячи записей за раз, при этом проверив, имеются ли они уже в таблице...

попробую использовать следующий запрос:
INSERT IGNORE INTO mytable VALUES ('aaa'),('bb'),('ccc'),...

но что делать, если при этом нужно еще и вернуть результат (например, какие именно записи и сколько их добавилось в результате запроса)?

 

[С.]

Можно подумать, что эти же самые задачи на текстовом файле у тебя исполняются молниеносно. Применение базы данных не приводит к автоматическому ускорению, а лишь создает возможность этого. Надо еще и голову приложить, чтобы оптимизировать запросы, возможно изменить архитектуру.

 

[aetsyss]

insert on duplicate key

 

[alexey980]

aetsyss
в моем случае не важно, что использовать NSERT IGNORE INTO или INSERT INTO... ON DUPLICATE KEY

вопрос сейчас вот в чем:
как отобразить строки, которые были добавлены в результате последнего запроса?

например, есть таблица mytable:
ааа
bbb

формируем запрос:
INSERT IGNORE INTO mytable VALUES ('aaa'),('xxx'),('yyy')

в результате таблица будет иметь вид:
ааа
bbb
xxx
yyy

с помощью mysql_affected_rows() можно узнать, что в результате запроса было добавлено 2 строки.
а как узнать, какие именно?
(то есть, чтобы в результате запроса было выведено 'xxx' и 'yyy')

 

[С.]

Никак. Если надо знать, какие записи уже есть, то сначала СЕЛЕКТ, а потом ИНСЕРТ (но уже только новые).

 

[Semen]

...
с помощью mysql_affected_rows() можно узнать, что в результате запроса было добавлено 2 строки.
а как узнать, какие именно?
(то есть, чтобы в результате запроса было выведено 'xxx' и 'yyy')

в этом случае одним полем не обойтись, нужно ещё одно "insert_time"
и вторым запросом выбрать то что было добавлено

 

[alexey980]

Semen,
наверное, так и придется делать.

 

[Тугай]

Если применять бд и индексы, то узкое место не сколько новых записей, а сколько уже есть в бд, потому - что если есть индексы, то чем больше записей в бд, тем медленней записи добавляются.
Это все в теории, на самом деле для одного поля и одного индекса по нему, это будет очень очень быстро.

После недавних событий и тестов, могу констатировать факт, что PHP вообще тормоз, и выбор одной записи бд из таблицы в 500 записей, по ключу , быстрее последовательного поиска в массиве ключей в памяти.
Когда у вас 30к записей, вы получите минимум 10 раз прироста производительности, а то и больше.

То что написано про count(*) - это глупость, добавьте индекс и он будет отрабатывать мгновенно все равно сколько там записей в бд если запрос без условий.

 

[fixxxer]

Индекс на что, на *?

Хотя - есть трюк с select count(*) use (unique_index_name), ога. Но не проще ли select count(id)?

 

[Тугай]

fixxxer
На то что у тс'а - на единственное поле в единственной таблице.

Вроде же это очевидно что последовательный поиск в строках общим размером в 30Кб, будет медленней чем поиск в BTREE, ну пусть 100Кб.

 

[alexey980]

то что я писал выше про SELECT COUNT(*) - это моя глупость. таблица изначально кривая была.
сейчас сделал таблицу InnoDB с одним полем varchar(25). типа поля - UNIQUE. таблица без индексов.
в таблицу залил 2 млн. записей из файла через LOAD DATA INFILE...
загрузка данных в таблицу из файла заняла 297 секунд. но это разовая операция. дальше нормально.
SELECT COUNT(*) FROM `mytable` работает очень быстро теперь.
вставка новых записей в таблицу одним запросом тоже работает очень быстро.
INSERT IGNORE INTO `mytable` VALUES... ('aaa'),('xxx'),('yyy')...
вставляет до 5000 тысяч записей за раз моментально. при этом дубликаты игнорируются.
осталось только оптимизировать выборку записей, которые добавляются в таблицу в ходе запроса INSERT IGNORE INTO `mytable` VALUES ('aaa'),('xxx'),('yyy')...
хотелось, бы обойтись только одним полем в таблице, но как уже указал Semen, скорее всего придется просто добавить еще одно поле (insert_time)
и вторым запросом выбрать то что было недавно добавлено.

P.S.
про strpos()-поиск совпадений в строке, считанной из файла, можно забыть как страшный сон.

 

[fixxxer]

типа поля - UNIQUE. таблица без индексов

) unique это и есть индекс

загрузка данных в таблицу из файла заняла 297 секунд

можно ускорить, создавая unique index после load data infile

судя по тому, что select count(*) работате быстро, у тебя unique not null - так делай уж его primary key, это то же самое

 

[alexey980]

правильно ли я понял, что если вместо UNIQUE указать PRIMARY KEY, то загрузка и запросы еще быстрее будут исполняться?

 

[Тугай]

alexey980
Вот fixxxer правильно подсказывает тип UNIQUE просто автоматом создает индекс. А без индексов начальная загрузка данных быстрее.
Не знаю как вы там общаетесь с mysql, но если phpmyadmin, то на табе "Структура" можно индексами по управлять.
Дисковое пространство - самый дешевый ресурс. Поэтому есть стандартная практика не жалеть его дисковое пространство и для каждой таблицы mysql создавaть поле типа int auto_increment - и объявлять его как primary key.
Это тот же самый UNIQUE но который автоматом принимает последовательные значения и требует 4 байта на запись.
Все ваши ключи - это неупорядоченное множество (так принянто в реляционных бд, что записи в таблице хранятся в случайном порядке), добавив поле id int auto_increment, primary ket(id) - вы его сделаете упорядоченным.
И дальше уззнать что добавилось очень просто:
select max(id) as max_id ...
запоминаем max_id в нашей программе скажем в $max_id
insert ...
select key where id > $max_id

То что упоминается про count(*) - это немного мимо

 

[Тугай]

правильно ли я понял, что если вместо UNIQUE указать PRIMARY KEY, то загрузка и запросы еще быстрее будут исполняться?

Нет, создайте просто еще одно поле int auto_increment , сделйте его PRIMARY KEY и используйте. UNIQUE на ключи убирать не надо.

 

[alexey980]

Тугай
fixxxer

спасибо. теперь разобрался.

 

[alexey980]

итак, подведём промежуточный итог:

есть InnoDB-таблица users.
в таблице два поля: num (INT, PRIMARY KEY, AUTO_INCREMENT) и id (VARCHAR, UNIQUE).

типовая операция с таблицей включает следующие запросы:
шаг 1. запоминаем номер последней записи как num_MAX;
шаг 2. добавляем в таблицу новые id (если такие уже есть в таблице, то дубликаты не добавляются);
шаг 3. выводим только те id, которые были добавлены в ходе предыдущего запроса (то есть, у которых num > num_MAX) .

// устанавливаем соединение и выбираем базу данных
$db = mysql_connect("localhost","alex","password");
mysql_select_db("test" ,$db);

// шаг 1: запоминаем номер последней записи таблицы
$sql = "SELECT MAX(num) FROM users";
$result = mysql_query($sql);  
$row = mysql_fetch_array($result);
$num_MAX = $row[0]; 
echo "сейчас в таблице $num_MAX записей";

// шаг 2: добавляем новые id (будем считать что список новых записей для добавления ааа, bbb, ccc и т.д. уже сформирован заранее)
$sql = "INSERT IGNORE INTO users(id) VALUES('aaa'),('bbb'),('ccc'),...('...')";
$result = mysql_query($sql);
$n = mysql_affected_rows();
echo "добавлено $n новых записей";

// шаг 3: делаем выборку новых id, которые были добавлены в результате последнего запроса
if ($n > 0){
     $sql = "SELECT id FROM users WHERE num > $num_MAX";
     $result = mysql_query($sql);
     // обрабатываем результат выборки while($row = mysql_fetch_array($result)){...echo $row[0];...}
}
		
// закрываем соединение с базой
mysql_close($db);

для стресс-теста была подготовлена таблица, в которой уже 2 миллиона id.
затем сгенерирован запрос на добавление в таблицу еще 2 тысяч новых id.
при первом выполнении сценария самым узким местом, что не удивительно, оказался
INSERT IGNORE INTO...
на выполнение этого запроса потребовалось примерно 20 секунд.
причем, при повторном прогоне сценария (через CTRL+R) времени на insert новых случайным образом сгенерированных id в таком же количестве - 2 тысячи потребовалось уже значительно меньше.

вопрос: можно ли как-то еще оптимизировать таблицу или запросы, чтобы уменьшить время на добавление новых записей?
20 секунд, конечно, не столь уж критично, но и не так уж мало.

 

[С.]

Я уже писал, что оптимизация не лежит только на базе, в этом тоже есть свои пределы. Оптимизировать мокзно и за счет архитектуры. Например добавлять без проверки, а дупликаты удалять позже. Но поскольку о твоей здаче знаешь только ты, то и посоветовать со стороны тебе ничего нельзя.