如何插入或更新大量行(关于表的自动增量值)

lskq00tm 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(443)

我有一个mysql表，大约有300万行( listings )目前。这些列表由python脚本24/7（大约30个列表/秒）更新( Scrapy )使用 pymsql -所以查询的性能是相关的！
如果 listing 不存在（即
UNIQUE url )，将插入一条新记录（大约每100个列表）。这个 id 设置为 auto_increment 我用的是 INSERT INTO listings ... ON DUPLICATE KEY UPDATE last_seen_at = CURRENT_TIMESTAMP . 上的更新 last_seen_at 是必要的，以检查该项目是否仍然在线，因为我爬行的搜索结果页上有多个列表，而不是检查每个单独的网址每次。

+--------------+-------------------+-----+----------------+
| Field        | Type              | Key | Extra          |
+--------------+-------------------+-----+----------------+
| id           | int(11) unsigned  | PRI | auto_increment |
| url          | varchar(255)      | UNI |                |
| ...          | ...               |     |                |
| last_seen_at | timestamp         |     |                |
| ...          | ...               |     |                |
+--------------+-------------------+-----+----------------+

问题是：

起初，一切顺利。然后我注意到自动增加的间隙越来越大 id 发现是因为 INSERT INTO ... 语句：mysql尝试先执行insert。这是 id 自动递增。一旦增加，它就会保持不变。然后检测到重复并进行更新。

现在我的问题是：从长远来看，哪一个是最佳的绩效解决方案？

选项a：设置 id 列到无符号 INT 或者 BIGINT 忽略这些差距。这里的问题是我害怕在几年后达到最大值。我已经在一个约12000000自动增值约3000000上市后两天的更新。。。
选项b：切换到 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         |
+------------+-------------------+

mysql sql-update Insert insert-update scrapy

来源：https://stackoverflow.com/questions/53801493/how-to-insert-or-update-a-large-number-of-rows-regarding-the-auto-increment-val

1条答案

按热度按时间

ozxc1zmp1#

我和你的处境一模一样
我有数百万条记录被scraper输入到表中，scraper每天都在运行
我试着跟着，但失败了
将所有URL加载到python中 tuple 或者 list 在刮取的同时，只刮取那些不在列表中的内容-失败是因为在将URL加载到python中时 tuple 或者 list 脚本消耗了服务器的大量ram
在输入之前检查每条记录-失败，因为它使插入过程太慢，因为它首先必须查询包含数百万行的表，然后决定是否插入
解决方案对我有效：（对于有数百万行的表）
我搬走了 id 因为这是不敬的，我不需要
制造 url 因为主键是唯一的
添加 UNIQUE 索引——这是必须做的——它将极大地提高表的性能
执行批量插入，而不是逐个插入（请参阅下面的管道代码）
注意它正在使用 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 = []

赞(0）回复(0）举报 2021-06-15

我来回答

如何插入或更新大量行(关于表的自动增量值)

问题是：

现在我的问题是：从长远来看，哪一个是最佳的绩效解决方案？

1条答案

相关问题

热门标签

最新问答