Новая тема   Ответить

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

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

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

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

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

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

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

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

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

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

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

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

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

 Alexandur
Цитата:

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

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

 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 шли по порядку при любом раскладе.

 ibiz
sergio21 писал:

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

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

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

 ibiz
sergio21 писал:


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

Код:

Код:

результат
Код:

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

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

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

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

 ibiz
sergio21 писал:

Version: 5.0.91-community

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

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

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

 ibiz
artursn писал:

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

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

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

 ibiz
artursn писал:

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

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

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

 artursn
sergio21 писал:
я понял это примерно так
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
ну тут уже зависит от бизнес-логики базы...

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

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

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

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