С Новым годом! Форум программистов, компьютерный форум, киберфорум
PHP: базы данных
Войти
Регистрация
Восстановить пароль
Блоги Сообщество Поиск Заказать работу  
 
Рейтинг 4.78/18: Рейтинг темы: голосов - 18, средняя оценка - 4.78
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14

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

27.03.2013, 23:59. Показов 3339. Ответов 13
Метки нет (Все метки)

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

сейчас для хранения данных используется обычный текстовый файл f.txt, в котором ключи разделены символом “\n”, а для проверки и добавления новых ключей в базу используется следующий сценарий:
PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// считываем содержимое файла с имеющимся ключами в строку целиком
$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);
примечание: вообще говоря, при операции поиска подстроки нужно использовать
“\n”.arr[i].”\n” вместо arr[i] и “\n”.$s.”\n” вместо $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.
0
cpp_developer
Эксперт
20123 / 5690 / 1417
Регистрация: 09.04.2010
Сообщений: 22,546
Блог
27.03.2013, 23:59
Ответы с готовыми решениями:

Добавление новых записей невозможно, ключ связи таблицы не входит в набор записей
Не могу добавить данные в поля, выводит такое сообщение: Добавление новых записей невозможно,ключ связи таблицы ЧЕК не входит не входит в...

Добавление новых записей в несколько таблиц с одним ID
Здравствуйте! Помогите, пожалуйста, решить задачу. Есть три таблицы: tblDevice, tblTest1, tblTest2. Они связаны один-к-одному по...

На новой форме организовать добавление новых записей
Помогите пожалуйста. Имеется база данных Access. Нужно на новой форме организовать добавление новых записей (Например Фамилия, Имя,...

13
 Аватар для koza4ok
632 / 440 / 67
Регистрация: 19.09.2012
Сообщений: 1,632
28.03.2013, 11:27
попробуйте поставить колонке таблице индекс UNIQUE и добавлять в БД через INSERT IGNORE.
У вас в колонке не будет дубликатов
1
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14
28.03.2013, 16:23  [ТС]
koza4ok,
допустим, я последовал вашему совету и сформировал mysql-таблицу mytable с одним полем типа UNIQUE.
пример таблицы mytable для наглядности:

aaa
bbb
ccc

выполняем, например, такой запрос:
SQL
1
INSERT IGNORE INTO mytable VALUES ('aaa'),('xxx'),('ccc'),('yyy')
если я правильно понял, то в результате mytable будет иметь вид:

aaa
bbb
ccc
xxx
yyy

можно ли при этом еще и вывести множество строк, которые были добавлены в результате последнего запроса?
то есть только 'xxx' и 'yyy'

хочется и рыбку съесть и...
то есть чтобы в таблицу были добавлены только уникальные значения, и при этом еще знать какие именно значения были добавлены.
0
 Аватар для Vovan-VE
13210 / 6599 / 1041
Регистрация: 10.01.2008
Сообщений: 15,069
28.03.2013, 17:04
Цитата Сообщение от alexey980 Посмотреть сообщение
можно ли при этом еще и вывести множество строк, которые были добавлены в результате последнего запроса?
Имхо, нет, нельзя. Только поштучные INSERT'ы с проверкой affected rows.
0
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14
28.03.2013, 20:02  [ТС]
боюсь, с поштучными INSERTами картина будет весьма печальная.
хорошо, если поштучно нужно вставить 10-20 строк, а если 2-3 тысячи?

сегодня загнал свою стресс-базу с 2-мя миллионами строк в mysql-таблицу.
первый же запрос для подсчета общего количества строк SELECT COUNT(*) FROM mytable подвесил сценарий более чем на минуту.
это уже выглядит не очень оптимистично.
как же с такой mysql-таблицей дальше-то работать
0
 Аватар для Vovan-VE
13210 / 6599 / 1041
Регистрация: 10.01.2008
Сообщений: 15,069
29.03.2013, 15:39
Цитата Сообщение от alexey980 Посмотреть сообщение
первый же запрос для подсчета общего количества строк SELECT COUNT(*) FROM mytable подвесил сценарий более чем на минуту.
это уже выглядит не очень оптимистично.
как же с такой mysql-таблицей дальше-то работать
Какой Engine у таблицы? В MyISAM в инфе о таблице известно точное количество строк. InnoDB ориентируется по индексам. Описанная ситуация похожа на InnoDB без уникальных индексов в таблице.

Добавлено через 43 секунды
См также результат из:
MySQL
1
EXPLAIN SELECT COUNT(*) FROM `mytable`
0
87 / 87 / 8
Регистрация: 02.09.2012
Сообщений: 510
29.03.2013, 17:08
если есть доступ к фс сервера mysql можно воспользоваться LOAD DATA из файла данных который вы закачаете на сервер. скорость страшно сказать на несколько порядков выше чем построчный insert.. скорее всего придется поговорить с хостером..

Добавлено через 8 минут
а проверку для формирования загрузочного файла делать обычным селектом, благо он выполняется быстро
0
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14
29.03.2013, 17:32  [ТС]
еще раз.
таблица уже в базе.
я её так и загружал:
MySQL
1
LOAD DATA INFILE 'file.txt' IGNORE INTO TABLE `mytable`
кстати загрузка 2-х млн. записей заняла 297 секунд.
сейчас вопрос в том, как максимально ускорить добавление новых записей (есть необходимость добавлять до 2000 тысяч записей за раз), при этом чтобы дубликаты не добавлялись в таблицу, и чтобы выводился список последних добавленных записей (ну и оптимально общее количество записей в таблице)
P.S.
тип таблицы: InnoDB, в таблице только одно поле VARCHAR(25) с типом UNIQUE
0
87 / 87 / 8
Регистрация: 02.09.2012
Сообщений: 510
29.03.2013, 17:41
формируйте файл на основе селектов - не пихайте в него то что уже есть.. или какие у вас там проверки... ускорить - бросить бесплатный софт с вашими объемами 2000 за раз.. это что - типовая операция для обычного пользователя системы? а если не типоавая - то подождет несколько минут, не повесится..
0
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14
30.03.2013, 17:15  [ТС]
сегодня попробовал такое вот решение.

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

типовая операция с таблицей включает следующие запросы:
шаг 1. запоминаем номер последней записи как num_MAX;
шаг 2. добавляем в таблицу новые id (если такие уже есть в таблице, то дубликаты не добавляются);
шаг 3. выводим только те id, которые были добавлены в ходе предыдущего запроса (то есть, у которых num > num_MAX) .
PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// устанавливаем соединение и выбираем базу данных
$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 users(id) VALUES('aaa'),('bbb'),('ccc'),...('...')
на выполнение этого запроса потребовалось примерно 20 секунд.
причем, при повторном прогоне сценария (через CTRL+R) времени на insert новых случайным образом сгенерированных id в таком же количестве - 2 тысячи потребовалось уже значительно меньше.

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

Добавлено через 25 секунд
если, скажем, для данного конкретного примера оставить все-таки таблицу с одним полем id.
уже без всяких индексов.
перед добавлением новых id: aaa, bbb, ccc... делать предварительную выборку из таблицы вроде
SELECT * FROM users WHERE id in ('aaa', 'bbb', 'ccc')
потом сравнивая эту выборку с входящим списком новых id, удалить из него дубликаты.
(здесь уже можно и простым сравнением строк или регулярными выражениями обойтись)
и затем уже этот усеченный список вставлять в таблицу с помощью INSERT, но уже без всяких игноров.
не будет ли так быстрее?
0
87 / 87 / 8
Регистрация: 02.09.2012
Сообщений: 510
30.03.2013, 18:07
Цитата Сообщение от alexey980 Посмотреть сообщение
вопрос: можно ли как-то еще оптимизировать таблицу или запросы, чтобы уменьшить время на добавление новых записей?
20 секунд, конечно, не столь уж критично, но и не так уж мало.
...
(здесь уже можно и простым сравнением строк или регулярными выражениями обойтись)
insert медленная операция в бд..что тут еще оптимизировать.. А регулярки в 20000 кратном использовании вас повесят.. неизвестна конечная ваша задача, но загрузка такого количества записей... чел(оператор) по загрузке подождет, а пользователю нужно быстрее выборку получить.. вроде ничего критичного у вас нет....

Добавлено через 2 минуты
Цитата Сообщение от alexey980 Посмотреть сообщение
можно ли что-то придумать здесь быстрее, чем strpos()?
нет

Добавлено через 1 минуту
сделать ключи не строковыми а целыми числами..
0
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14
31.03.2013, 02:33  [ТС]
вопрос сейчас только в одном. сразу вставлять в таблицу 2К записей через INSERT IGNORE... или сначала через SELECT... WHERE... IN... сделать выборку дубликатов, усечь из 2К записей дубликаты и вставлять уже усеченный список через обычный INSERT.

Добавлено через 6 минут
еще вопрос. индексы ведь тормозят таблицу? потом что в первом варианте нужно использовать индексную таблицу, а во втором можно без индексов.

Добавлено через 7 часов 32 минуты
беру свои слова обратно. без индекса таблица работает медленнее по всем фронтам.
0
 Аватар для Vovan-VE
13210 / 6599 / 1041
Регистрация: 10.01.2008
Сообщений: 15,069
31.03.2013, 06:27
Цитата Сообщение от alexey980 Посмотреть сообщение
без индекса таблица работает медленнее по всем фронтам.
Индексы ускоряют выборку за счет замедления редактирования (вставка/изменение/удаление).
0
0 / 0 / 0
Регистрация: 26.03.2013
Сообщений: 14
03.04.2013, 16:10  [ТС]
если кто следил за темой, то решение свелось к следующему.

используется таблица users (тип InooDB) 2 поля:
id - varchar(22), primary key;
time - timestamp, по умолчанию CURRENT_TIMESTAMP (дополнительное вспомогательное поле).

главная "хитрость" в том, чтобы вместо запроса INSERT IGNORE INTO ... VALUES('aaa'),('bbb'),('ccc'),...('...') использовать запрос LOAD DATA LOCAL INFILE '.....txt' IGNORE INTO TABLE, предварительно сохранив данные для добавления во временный txt-файл. сохранение данных во временный txt происходит ничтожно быстро, зато потом загрузка данных в таблицу из файла будет во много раз быстрее чем групповой INSERT (про поштучный вообще молчу).

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// сохраняем входящий пакет с новыми неотфильтрованными id во временный файл
file_put_contents('new_id_not_filtered.txt',$_POST["new_id"]);
 
// устанавливаем соединение с БД
$db = mysql_connect("localhost","...","...");
mysql_select_db("..." ,$db);
 
//определяем количество записей в таблице
$sql = "SELECT COUNT(*) FROM users";
$result = mysql_query($sql);  
$row = mysql_fetch_array($result);    
echo "количество записей в таблице: ".$row[0];
 
// запоминаем текущее время до вставки в формате mysql timestamp
$before_insert_time = "'".date("Y-m-d H:i:s")."'";
 
// вставка новых id из временного файла, дубликаты отсеиваются
$sql = "LOAD DATA LOCAL INFILE 'new_id_not_filtered.txt' IGNORE INTO TABLE users(id)";
$result = mysql_query($sql);
 
// возвращаем id которые были добавлены в результате последней вставки
$sql = "SELECT id FROM users WHERE time >= $before_insert_time";
$result = mysql_query($sql);
$n_new = 0;
while ($row = mysql_fetch_array($result)){
    $n_new++;
    //.... передаем дальше в другой сценарий последние добавленные id: $row[0] .... ;
}
echo "добавлено новых id: $n_new";        
 
//закрываем соединение с БД
mysql_close($db);
0
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
raxper
Эксперт
30234 / 6612 / 1498
Регистрация: 28.12.2010
Сообщений: 21,154
Блог
03.04.2013, 16:10
Помогаю со студенческими работами здесь

Добавление записей невозможно: ключ связи таблицы не входит в набор записей
Люди помогите пожалуйста. Есть база данных с 4 таблицами. Есть форма, на ней располагается подчиненная форма с полями и полем со...

Добавление записей невозможно: ключ связи таблицы не входит в набор записей
Здравствуйте! Возникла проблема при заполнении формы основанной на запросе. Как можно исправить? Читал уже другие темы с...

Формы: добавление новых записей и удаление записей
у меня следующая форма (приложение) добавила кнопки добавление новых записей, удаление записей, вроде работает, нужно ли для них писать...

База данных на основе массива записей: добавление, удаление, поиск записей
Комрады, учусь асму, а некоторые моменты не осиливаю :( задача в реализовании некой базы данных с записями &quot;фамилия&quot;,...

Добавление новых записей и новых полей
Есть одна проблемка: Делаю программку тут одну, в основе лежит БД(Access 2007). У меня есть главная форма, где пользователь может...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
14
Ответ Создать тему
Новые блоги и статьи
Учёным и волонтёрам проекта «Einstein@home» удалось обнаружить четыре гамма-лучевых пульсара в джете Млечного Пути
Programma_Boinc 01.01.2026
Учёным и волонтёрам проекта «Einstein@home» удалось обнаружить четыре гамма-лучевых пульсара в джете Млечного Пути Сочетание глобально распределённой вычислительной мощности и инновационных. . .
Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост.
Programma_Boinc 28.12.2025
Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / **********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / **********/ gallery/ bKBkQFf Пост отсюда. . .
Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США.
Programma_Boinc 26.12.2025
Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .
Thinkpad X220 Tablet — это лучший бюджетный ноутбук для учёбы, точка.
Programma_Boinc 23.12.2025
Рецензия / Мнение/ Перевод Нашел на реддите интересную статью под названием The Thinkpad X220 Tablet is the best budget school laptop period . Ниже её машинный перевод. Thinkpad X220 Tablet —. . .
PhpStorm 2025.3: WSL Terminal всегда стартует в ~
and_y87 14.12.2025
PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .
Как объединить две одинаковые БД Access с разными данными
VikBal 11.12.2025
Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.
Новый ноутбук
volvo 07.12.2025
Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro
Музыка, написанная Искусственным Интеллектом
volvo 04.12.2025
Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .
От async/await к виртуальным потокам в Python
IndentationError 23.11.2025
Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов
Argus19 22.11.2025
Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2026, CyberForum.ru