Знатоки MySQL, подскажите, плз.

предположим, есть таблица в MySQL, в которой имеются поля id, key1, key2.
поле id - автоинкрементное.
поля key1 и key2 в комбинации должны быть Unique.

т.о., id увеличивается на 1, независимо от того, была ли осуществлена запись в базу, или из-за наличия дубликата запись не состоялась. это создает в нумерации "дыры", что крайне неудобно для дальнейшей работы с базой.

порывшись в инете, я нашел несколько вариантов решений данной проблемы.
1. можно перед каждой новой записью давать команду ALTER TABLE 'My_Table' AUTO_INCREMENT=1 (но это лишняя нагрузка на сервер)
2. можно сделать отдельное поле, куда вписывать порядковый номер одновременно с остальными данными. (но это нужно каждый раз опрашивать базу, какой последний номер использован, а при многопоточности это вообще плохо реализуемо.)
3. можно время от времени удалять поле id и добавлять его заново. при этом база сама пронумерует все записи строго по порядку. (но при этом придется каждый раз вручную останавливать все модули, работающие с базой, а потом заново их запускать.)

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

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

А не проще проверять уникальность key1/key2 до записи в базу?

а как у вас проверяется уникальность полей key1, key2 на каком этапе?
может их проверять раньше или создавать транзикацию перед записью, записывать и если поля не прошли проверку отменять. поле не увеличиться и сохраниться многопоточность приложения.

а почему нельзя сделать уникальное поле hash и писать туда md5(key1.key2)?
при добавлении записи в бд в такой схеме нет необходимости доп. селектов.

Я может чего-то не понял, но чем индексы не подходят?

Код:

UNIQUE INDEX 'back_in_the_u_key' ('key1', 'key2')

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

artursn уникальность никак не проверяется. просто делается попытка записать очередную пару кеев в базу. если попытка удалась, значит пара уникальна. если не удалась, значит она уже существует. но при этом id (автоинкремент) все-равно увеличивается на 1.

LeadFarmer, gimcnuk сорри, но будучи почти полным нубом в вопросах MySQL, я не понял, о чем речь. разжуйте, если можно, и по возможности примитивным языком.

а пока всем спасибо и плюшки.

Цитата:

В MySQL ключ UNIQUE может иметь только различающиеся значения. При попытке добавить новую строку с ключом, совпадающим с существующей строкой, возникает ошибка выполнения команды.

Добавляешь к своей базе

Код:

ALTER TABLE your_table ADD UNIQUE INDEX 'un_in' ('key1', 'key2')

либо при создании

Код:

CREATE TABLE your_table (..., UNIQUE INDEX 'un_in' ('key1', 'key2'))

gimcnuk, дык, это я сделал изначально.
проблема не в том, как избежать дубликатов, а в том, как с минимальной нагрузкой для сервера сделать так, чтобы автонумерация в поле id (автоинкремент) не имела "дыр".
пример:
база пуста.
заносим в базу "А1", "B1". id автоматически получает значение 1.
заносим в базу "А2", "B1". id автоматически получает значение 2.
заносим в базу "А1", "B1". запись не производится, т.к. это дубликат. id автоматически получает значение 3.
заносим в базу "А2", "B2". id автоматически получает значение 4.

если вывести все содержимое таблицы, имеем:
id key1 key2
1 A1 B1
2 A2 B1
4 A2 B2

а хотелось бы, чтобы значения id шли по порядку при любом раскладе.

sergio21 писал:

gimcnuk, дык, это я сделал изначально. проблема не в том, как избежать дубликатов, а в том, как с минимальной нагрузкой для сервера сделать так, чтобы автонумерация в поле id (автоинкремент) не имела "дыр". пример: база пуста. заносим в базу "А1", "B1". id автоматически получает значение 1. заносим в базу "А2", "B1". id автоматически получает значение 2. заносим в базу "А1", "B1". запись не производится, т.к. это дубликат. id автоматически получает значение 3. заносим в базу "А2", "B2". id автоматически получает значение 4. если вывести все содержимое таблицы, имеем: id key1 key2 1 A1 B1 2 A2 B1 4 A2 B2 а хотелось бы, чтобы значения id шли по порядку при любом раскладе.

странно, у меня примерно такая же конструкция и ид идут подряд, хотя 10% дубликатов проскакивают, а заносишь как INSERT IGNORE?

ibiz: нет, делаю простой INSERT.
IGNORE просто "подавляет" сообщения об ошибках, превращает их в предупреждения.
все остальное происходит так-же, как описано выше. специально попробовал добавить в команду IGNORE. результат прежний.

Оффтопик: ibiz, ты проверь свою базу. сравни количество записей и самый большой ID, который там есть. думаю, будет разница.

sergio21 писал:

пример: база пуста. заносим в базу "А1", "B1". id автоматически получает значение 1. заносим в базу "А2", "B1". id автоматически получает значение 2. заносим в базу "А1", "B1". запись не производится, т.к. это дубликат. id автоматически получает значение 3. заносим в базу "А2", "B2". id автоматически получает значение 4. если вывести все содержимое таблицы, имеем: id key1 key2 1 A1 B1 2 A2 B1 4 A2 B2 а хотелось бы, чтобы значения id шли по порядку при любом раскладе.

специально создал отдельную таблицу и попробовал

Код:

CREATE TABLE `ztest` (   `Id` int(11) NOT NULL auto_increment,   `key1` varchar(255) NOT NULL default '0',   `key2` varchar(255) NOT NULL default '0',   PRIMARY KEY  (`Id`),   UNIQUE KEY `key1_key2` (`key1`,`key2`) ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Код:

sql_query("INSERT IGNORE INTO ztest(key1,key2) VALUES ('A1','B1');"); sql_query("INSERT IGNORE INTO ztest(key1,key2) VALUES ('A2','B1');"); sql_query("INSERT IGNORE INTO ztest(key1,key2) VALUES ('A1','B1');"); sql_query("INSERT IGNORE INTO ztest(key1,key2) VALUES ('A2','B2');");

результат

Код:

1   A1   B1 2   A2   B1 3   A2   B2

sergio21: так в чем сакральный смысл "непопорядковости" первичного ключа? я вот в свое время прочитал весь ман 4.0 по мускулю, и там ни в одном месте нет проблемы этой. Кстати MYISAM вроде не должен так делать. При вставке вставляется "следующий после последнего", а вот в InnoDB эта тема есть, - там все по-другому и где-то на внутреннем уровне этот индекс храниться - и при ошибочной вставке ключ (и удаленные в последствии ключи) считается последним, т.е. есть пропуски. Но это не в коей мере ничему не мешает (вот у меня база в ней 7к строк, а порядковый номер далеко за 15к. и постоянно что-то удаляется, и более того - это поле юзается как внешний ключ для других таблиц, - проблем никаких. ссылочная целостность есть, уникальный "ползунок" для записей есть). Попробуй что ли не юзать автоинкрементное поле, а просто создай id INT NOT NULL и туда при вставке пиши сам числа. и сам отслеживай. Или через LAST_INSERT_ID() попробуй. т.е. получай последний id, прибавляй 1 и вставляй (хотя вряд ли в автоинкремент поле можно самому число вставить, но попробуй).

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

FXIX: сакральный смысл в том, что с базой будет работать многопоточная программа. и гораздо проще писать ее, зная, что номера идут по порядку, чем учитывать возможные "дыры" в нумерации. в этом случае придется встраивать дополнительные запросы к базе, которые будут излишне нагружать сервер.
поэтому я изначально пытаюсь найти решение, которое будет наиболее быстрым, простым, и будет создавать минимальную дополнительную нагрузку.

всем спасибо за внимание, всем +6.
буду юзать вариант с командой ALTER TABLE 'My_Table' AUTO_INCREMENT=1 перед каждой новой записью. это устраняет проблему. но не знаю пока, как это будет выглядеть под нагрузкой. попозже попробую непосредственно на сервере.

sergio21 писал:

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

Version: 5.0.91-community

sergio21 писал:

предположим, есть таблица в MySQL, в которой имеются поля id, key1, key2. поле id - автоинкрементное. поля key1 и key2 в комбинации должны быть Unique.

Вопрос из зрительного зала: нахуя тут поле id вообще?

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

а нельзя просто считать все в массив и использовать ид элемента массива?

artursn писал:

а нельзя просто считать все в массив и использовать ид элемента массива?

если у тебя память резиновая, то можно вообще весь скрипт в памяти всегда хранить

за InnoDB я не скажу, потому что проводил различные тесты на различных машинах и результат скорости всегда был хуже чем при использовании isam

ну в цыкле он же тоже не всю базу считывает.....или я что-то не понял....

artursn писал:

ну в цыкле он же тоже не всю базу считывает.....или я что-то не понял....

ну вот есть 10к записей, надо выбрать 20 записей с 5600 по 5620, как это сделать?

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

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

sergio21 писал:

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

я понял это примерно так
1 . SELECT * FROM table WHERE key1='' and key2=''
2. Получаем это в двумерный массив массив
3. потом чичтаем foreach ($array as $kay=>$value)
4. и потом делаем с каждой строчкой что нужно
например
foreach ($array as $kay=>$value)
{
echo '<a href=view.php?id='.$value['key1'].'">'.$value['title'].'</a>';
}
Как вариант..

ну тут уже зависит от бизнес-логики базы...

artursn писал:

я понял это примерно так 1 . SELECT * FROM table WHERE key1='' and key2='' ...

неправильно понял. если бы я знал содержимое key1 и key2 в записи номер n, зачем мне нужно было бы их еще раз считывать из базы?
будет так:
SELECT key1, key2 FROM table WHERE id = n

n загоняется в цикл.

sergio21: ну а что мешает просто пропускать несуществующие записи? к примеру

Цитата:

mysql> select * from table where id=3; Empty set (0.00 sec)

ну проходишься ты по порядку по ID-ам в цикле. сразу второй строкой после запроса делаешь проверку на NULL (или array(). в зависимости от либы на которой пишешь) и цикл вверх бросаешь (if(is_null($result)) {continue;}).
Про ALTER TABLE почитай в мане.

Цитата:

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL incorporates the alteration into the copy, then deletes the original table and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

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