Как проверить / обновить столбцы в базе данных, содержащей около миллиона строк?
У меня есть база данных MYSQL, которая содержит около одного миллиона (1000000) строк, я хочу проверить все строки и обновить некоторые в соответствии с условием, поэтому, например, я запускаю такой оператор SQL:
select messageid from messages where messageid ! = ""
Затем я получаю все идентификаторы и сохраняю их в переменной:
$existMessages;
Затем я генерирую строку из 4 символов 0-9a-z:
function generateRandomString($length = 4) {
return substr(str_shuffle(str_repeat($y='0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil($length/strlen($y)) )),1,$length);
}
Затем я обновляю существующие идентификаторы сгенерированными строками, после проверки уникальности сгенерированных идентификаторов.
Этот процесс замедляется и занимает весь ЦП по мере увеличения строк.
Есть ли лучший способ сделать это? Нравится использовать операторы SQL непосредственно в MYSQL? Или что делать?
Возможно, это одноразовая вещь, чтобы все они имели это, и где бы они ни были вставлены, также должны быть добавлены уникальные идентификаторы? Тогда тебе не пришлось бы этого делать?
@James, Да, в общем, должно быть так же, но это особый случай, и мне нужно время от времени обновлять этот столбец уникальным случайным значением
"время от времени", но если бы это было в коде INSERT, вам не нужно было бы делать это когда-либо снова? Вставка уже сделала бы это
Только миллион, у меня была таблица с 120 миллионами, затем мы переместили ее в MongoDB, в основном потому, что она потребляла более 80 ГБ жесткого диска и 30 ГБ или оперативную память, не то чтобы MongoDB справлялась с этим лучше. Миллион - это мелочь, у меня сейчас 2 стола с чуть более чем миллионом :-)
Я не понимаю цель этого запроса select messageid from messages where messageid ! = "", вы выбираете идентификатор по идентификатору, когда вы знаете, что это за идентификатор? Возможно, это всего лишь пример, но он плохой, так как есть способы оптимизировать выборку, но нам понадобится настоящий выбор. Например, если у вас есть уникальное поле и вы хотите вставить данные, быстрее обнаружить повторяющиеся ключевые ошибки, чем выполнять выбор и вставку, потому что вам не нужно выполнять 1 из запросов.
@James, это потому, что это не идентификатор автоинкремента столбца, есть id и messageid, id - это автоинкремент, где messageid - уникальная строка, например, когда вы хотите создать ссылку активации, чтобы вы создавали случайный ключ
@ArtisticPhoenix, некоторые строки уже содержат случайную строку, теперь есть новые созданные строки, поэтому я выбираю все идентификаторы, чтобы убедиться, что идентификаторы не дублируются
Может, я вас неправильно понимаю, или вы меня. Я буду ясен, насколько это возможно. Вы вставляете строку, в которой находится поле messageid. Позже вы придете и в той же строке в messageid вы обновите данные. Если это для любой / всех строк, где messageid пуст, то он должен быть установлен при первой вставке. И / или всякий раз, когда какой-либо скрипт очищает это поле, он должен вызывать скрипт, из которого вы генерируете случайный код, и затем обновлять его этим. т.е. всякий раз, когда ЛЮБОЙ скрипт выполняет действия с этой таблицей и этим полем, если оно будет пустым, тогда установите его вместо того, чтобы устанавливать его как пустое и позже исправлять это :)
Какой драйвер базы данных вы используете, mysqli или PDO, пробовали ли вы использовать небуферизованные запросы для выбора? Обычно это будет компромисс между временем и памятью. for your comment я говорю, что я не понимаю, почему у вас пустой идентификатор вместо простого удаления и вставки, обновления медленнее, чем любой из этих 2.
Похоже, у вас проблемы с дизайном, поскольку идентификаторы обновляются нечасто. Это может быть одноразовая операция по исправлению дефекта, но не требуется в качестве задачи обслуживания. Возможно, TRIGGER, который действует на INSERT, мог бы лучше исправить неправильные идентификаторы.
Кажется, я не проясняю, столбец messageid не является идентификатором строки, я не хочу вставлять туда числа, я не заказываю таблицу с этим столбцом, для этой цели есть столбец с именем id
@ArtisticPhoenix, я использую PDO
Хорошо, но вы все еще не дали мне достаточно информации, каков запрос для этого Then I update the existing IDs with the generated strings. Является ли идентификатор уникальным и т. Д ... ЕСЛИ у вас есть миллион строк, велика вероятность, что ваш случайный результат будет генерировать одну и ту же строку более одного раза, особенно с $length = 4 или (62 * 62 * 62 * 62 = 14,776,336)
Итак, вы видите, что у вас есть в основном шанс 1 из 14, когда у вас есть 1 миллион строк. Это ухудшается каждый раз, когда вы добавляете строку.
Ответы 4
Сначала вы должны наложить ограничение unique or primary на столбец, который вы хотите сделать уникальным.
После этого вы можете выполнить команду обновления
UPDATE TABLE_NAME SET COLMUN_NAME=generateRandomString() WHERE messageid ! = "";
Вы можете использовать SQL напрямую и создать простой хеш на основе некоторых данных в строке, например:
UPDATE table_name SET messageid = MD5(messageid) WHERE messageid ! = "";
Возможно, вы захотите сделать это партиями, поэтому добавьте LIMIT в оператор, т.е. ОГРАНИЧИВАЙТЕ 0,1000 на выполнение групп за раз.
«Этот процесс становится медленнее и занимает весь ЦП по мере увеличения количества строк». запросы с LIMIT также будут медленнее при использовании большего числа смещений.
Вы можете использовать предложение об обновлении ниже. Это выберет 4 случайные буквы и цифры как для нижнего, так и для верхнего регистра, а также от 0 до 9.
update table_name cross join (select
@chars:='1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIKLMNOPQRSTUVWXYZ') tab
set messageid = concat(substring(@chars, floor(rand()*61) + 1, 1),
substring(@chars, floor(rand()*61) + 1, 1),
substring(@chars, floor(rand()*61) + 1, 1),
substring(@chars, floor(rand()*61) + 1, 1)
)
where messageid ! = "";
Я устраню необходимость в «проверке» и сделаю это быстрее.
Таких 4-символьных строк не намного больше миллиона. Таким образом, будет досадное количество дубликатов, если вы создадите их «случайным образом».
Вместо этого я рекомендую
- Сгенерируйте их все (или миллион из них)
- Перемешайте их
Примените их к своему столу.
CONV(x, 10, 36)сгенерирует значение по основанию 36 (0-9A-Z) из x. Но следующее может быть лучше ...- Создайте таблицу из 36 строк по 0..9, a..z.
CROSS JOINон сам себе 4 раза, чтобы сгенерировать все 36 ^ 4 комбо.ORDER BY RAND()перемешает их без дублирования.UPDATEс несколькими таблицами позволяет копировать строку из 4 символов из одной таблицы в другую.
вы должны использовать Процедура хранения MySQL