Как ВСТАВИТЬ или ОБНОВИТЬ большое количество строк (относительно значения auto_increment таблицы)
На данный момент у меня есть таблица MySQL с примерно 3 миллионами строк (listings). Эти списки обновляются 24/7 (около 30 листингов в секунду) с помощью скрипта Python (Scrapy) с использованием pymsql, поэтому производительность запросов важна!
Если listing не существует (например, UNIQUEurl), будет вставлена новая запись (примерно в каждом сотом листинге). id настроен на auto_increment, и я использую INSERT INTO listings ... ON DUPLICATE KEY UPDATE last_seen_at = CURRENT_TIMESTAMP. Обновление на last_seen_at необходимо, чтобы проверить, находится ли элемент все еще в сети, поскольку я просматриваю страницу результатов поиска с несколькими списками на ней и не проверяю каждый раз каждый отдельный URL.
+--------------+-------------------+-----+----------------+
| Field | Type | Key | Extra |
+--------------+-------------------+-----+----------------+
| id | int(11) unsigned | PRI | auto_increment |
| url | varchar(255) | UNI | |
| ... | ... | | |
| last_seen_at | timestamp | | |
| ... | ... | | |
+--------------+-------------------+-----+----------------+
Проблема:
Сначала все шло нормально. Затем я заметил все большие и большие пробелы в столбце auto_incremented id и выяснил, что это связано с оператором INSERT INTO ...: MySQL пытается сначала выполнить вставку. Это когда id автоматически увеличивается. После увеличения он остается. Затем обнаруживается дубликат и происходит обновление.
Теперь мой вопрос: какое решение является лучшим с точки зрения производительности в долгосрочной перспективе?
Вариант А: Установите столбец id на беззнаковый INT или BIGINT и просто игнорируйте пробелы. Проблема в том, что я боюсь достичь максимума через пару лет обновления. У меня уже есть значение auto_increment около 12000000 для около 3000000 списков после двух дней обновления ...
Вариант Б: Переключитесь на оператор INSERT IGNORE ..., проверьте затронутые строки и UPDATE ..., если необходимо.
Вариант C:SELECT ... существующие списки, проверьте наличие в пределах python и INSERT ... или UPDATE ... в зависимости от.
Есть другие мудрые варианты?
Дополнительная информация: Мне нужен id для информации, относящейся к listing, хранящейся в других таблицах (например, listings_images, listings_prices и т. д.). IMHO использование URL-адреса (который является уникальным) не будет лучшим вариантом для внешних ключей.
+------------+-------------------+
| Field | Type |
+------------+-------------------+
| listing_id | int(11) unsigned |
| price | int(9) |
| created_at | timestamp |
+------------+-------------------+
@ P.Salmon да, это то, что я предложил в своем ответе, этот столбец идентификатора не нужен, просто сделайте URL-адрес ПЕРВИЧНЫМ КЛЮЧОМ и добавьте УНИКАЛЬНЫЙ индекс для этого
@ P.Salmon Я добавил информацию о том, что есть больше таблиц, в которых хранится информация о листинге, и поэтому мне нужен id - использование длинного строкового URL-адреса не будет лучшим вариантом в этом случае. Поправьте меня если я ошибаюсь.
Ответы 1
Я был в такой же ситуации, как твоя
У меня есть миллионы записей, которые парсером вводятся в таблицу, парсер работал каждый день
Я пробовал подписаться, но не смог
- Загрузите все URL-адреса в Python
tupleилиlist, и во время очистки очищайте только те, которых нет в списке - НЕ удалось, потому что во время загрузки URL-адресов в сценарий Pythontupleилиlistпотреблялось очень много оперативной памяти сервера - Проверяйте каждую запись перед вводом - FAILED, потому что это сделало процесс INSERTion слишком медленным, потому что он сначала должен запросить таблицу с миллионами строк, а затем решить, следует ли INSERT или нет
РЕШЕНИЕ РАБОТАЛО ДЛЯ МЕНЯ: (для таблицы с миллионами строк)
- Я удалил столбец
id, потому что он непочтительный и мне он не нужен. - Сделайте
urlPRIMARY KEY, так как он будет уникальным - Добавьте
UNIQUEINDEX - ЭТО НЕОБХОДИМО - Это значительно повысит производительность вашего стола - Выполняйте массовую вставку вместо вставки по одной (см. Код конвейера ниже)
Обратите внимание, что он использует INSERT IGNORE INTO, поэтому будут вводиться только новые записи, и если они существуют, они будут полностью проигнорированы.
Если вы используете REPLACE INTO вместо INSERT IGNORE INTO в MySQL, новые записи будут введены, но если запись существует, она будет обновлена.
class BatchInsertPipeline(object):
def __init__(self):
self.items = []
self.query = None
def process_item(self, item, spider):
table = item['_table_name']
del item['_table_name']
if self.query is None:
placeholders = ', '.join(['%s'] * len(item))
columns = '`' + '`, `'.join(item.keys()).rstrip(' `') + '`'
self.query = 'INSERT IGNORE INTO '+table+' ( %s ) VALUES ( %s )' \
% (columns, placeholders)
self.items.append(tuple(item.values()))
if len(self.items) >= 500:
self.insert_current_items(spider)
return item
def insert_current_items(self,spider):
spider.cursor.executemany(self.query, self.items)
self.items = []
def close_spider(self, spider):
self.insert_current_items(spider)
self.items = []
Спасибо, что поделились этим! Это была моя первая идея, но мне нужен идентификатор для реляционных таблиц, в которых хранится дополнительная информация для listing (например, listings_prices, listings_images и т. д.). Я добавлю эту информацию в свой исходный пост.
Хорошо, оставьте столбец автоматического увеличения как есть, а затем добавьте уникальный индекс в столбец URL-адреса, затем посмотрите, работает ли INSERT IGNORE
Пробовал это раньше, но тогда обновление отсутствует. Обновление необходимо, чтобы проверить, активен ли «листинг». Я не сканирую каждый отдельный URL, а только страницу результатов поиска с несколькими списками на ней.
Четвертым вариантом может быть удаление столбца идентификатора auto_increment.