Форум пользователей MySQL

alexey980 · 01.04.2013 21:17:56

Имеется таблица users, состоящая из строковых идентификаторов id (VARCHAR);
('aaa'), ('bbb'), ... и т.д. до 2 млн. штук, при этом id не повторяются;

Выполняем запрос на добавление к исходной таблице новых id:
('ааа'), ('xxx'), ('bbb'), ('yyy')... и т.д. до 2K штук в одном запросе;

После добавления исходная таблица должна иметь вид:
('ааа'), ('bbb'), ('xxx'), ('yyy')... повторяющиеся id не добавляются;

Также нужно сделать выборку id, которые были добавлены в результате предыдущего запроса:
('xxx'), ('yyy')...

Необходимо максимально ускорить добавление и выборку новых идентификаторов.
Если нужно добавить еще одно поле в таблицу или использовать TEMPORARY TABLE - не вопрос.
Главный критерий - скорость.

Сейчас использую такой запрос (для этого полю id присвоил тип UNIQUE, таблица типа InnoDB):

INSERT IGNORE INTO users(id) VALUES('aaa'),('xxx'),('bbb'),('yyy'),...

Добавление 2K записей в таблицу, где уже 2 млн. записей, занимает приблизительно 20 сек. Не критично.
Но можно ли ещё быстрее, учитывая, что вставка в таблицу с индексом UNIQUE - операция медленная по умолчанию.
Кроме того, приведенный запрос позволяет сделать только вставку.
А как получить выборку результата последних добавленных id: ('xxx'),('yyy),...?
Очевидно, что одним полем здесь уже не обойтись.

Другой вариант - отказаться от индекса и самому делать контроль уникальности при добавлении.
Для этого использовал временную таблицу temp, в которую залили все новые id, а затем "вручную" отфильтровывал дубликаты:

SELECT * FROM temp LEFT JOIN users ON temp.id=users.id WHERE users.id IS NULL

Но этот вариант, оказался еще более медленным (выполнение запроса превысило лимит ожидания 30 сек.).

В общем, запутался окончательно.
Нужна помощь экспертов по оптимизации запросов, и, возможно, по изменению структуры таблицы.

Shopen · 01.04.2013 21:55:04

А у вас в реальности так запрос и выглядит - вставка только идентификаторв? Или там еще какие то поля вставляются? если да - то по ним есть индексы? Если да, то попробуйте перед вставкой пачки выполнить ALTER TABLE DISABLE KEYS и ALTER TABLE ENABLE KEYS после.

Если это не поможет, то вам скорее всего надо думать в сторону ускорения I/O.

Отредактированно Shopen (01.04.2013 21:56:52)

deadka · 01.04.2013 21:58:43

Почитайте здесь.

alexey980 · 02.04.2013 15:37:13

Shopen,

в таблице только одно поле id.
остальные - если нужны, то только в роли вспомогательных, по которым потом делать выборку последних добавленных (например, timestamp или auto_increment).

id - должно быть как минимум UNIQUE, иначе не будет работать INSERT IGNORE
если перед вставкой делать ALTER TABLE DISABLE KEYS, как же тогда будут отфильтровываться дубликаты при вставке?

alexey980 · 02.04.2013 19:07:42

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

сценарий выглядит примерно так :

// сохраняем входящий пакет с новыми неотфильтрованными id во временный файл

file_put_contents('new_id_not_filtered.txt',$_POST["new_id"]);

// устанавливаем соединение с БД

$db = mysql_connect("localhost","...","...");

mysql_select_db("..." ,$db);

// запоминаем текущее время до вставки в формате 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);

тестировал на таблице с 2 млн. id, производил вставку пакета из 2К неотфильтрованных id (в которых вперемешку 1К новых уникальных значений и 1К таких, которые уже имеются в таблице).
по результатам в таблицу добавляется 1К новых уникальных id (остальные отсеиваются при вставке LOAD DATA LOCAL INFILE IGNORE).
на выполнение запроса LOAD DATA LOCAL INFILE IGNORE в этом случае уходит примерно 24 секунды.

Отредактированно alexey980 (02.04.2013 21:01:25)

Shopen · 02.04.2013 20:32:21

alexey980 написал:
в таблице только одно поле id.
остальные - если нужны, то только в роли вспомогательных, по которым потом делать выборку последних добавленных (например, timestamp или auto_increment).

Так они есть или нет "остальные" поля? Приведите результат команды SHOW CREATE TABLE users

alexey980 написал:
id - должно быть как минимум UNIQUE, иначе не будет работать INSERT IGNORE
если перед вставкой делать ALTER TABLE DISABLE KEYS, как же тогда будут отфильтровываться дубликаты при вставке?

ENABLE/DISABLE KEYS не влияет на работу уникальных индексов

Отредактированно Shopen (02.04.2013 20:35:11)

alexey980 · 02.04.2013 20:56:28

Shopen,
сейчас таблица такая:

CREATE TABLE `users` (  `id` varchar(25) NOT NULL,  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

id - основное поле.
time - вспомогательное, чтобы потом производить выборку последних отфильтрованных значений, которые были добавлены в таблицу.
раньше у time тоже был index (сделал его без индекса).

Отредактированно alexey980 (02.04.2013 21:06:18)

Форум пользователей MySQL

#1 01.04.2013 21:17:56

нужно оптимизировать запрос "вставка+проверка уникальности"

#2 01.04.2013 21:55:04

Re: нужно оптимизировать запрос "вставка+проверка уникальности"

#3 01.04.2013 21:58:43

Re: нужно оптимизировать запрос "вставка+проверка уникальности"

#4 02.04.2013 15:37:13

Re: нужно оптимизировать запрос "вставка+проверка уникальности"

#5 02.04.2013 19:07:42

Re: нужно оптимизировать запрос "вставка+проверка уникальности"

#6 02.04.2013 20:32:21

Re: нужно оптимизировать запрос "вставка+проверка уникальности"

alexey980 написал:

alexey980 написал:

#7 02.04.2013 20:56:28

Re: нужно оптимизировать запрос "вставка+проверка уникальности"

Board footer