mysql - 如何更新具有大量更新的表,同时保持表对大量用户的可用性?
问题描述
如何更新具有大量更新的表,同时保持表对大量用户的可用性?
解决方案
解决这个问题的方法不止一种。要使用适合您的代码给出准确的答案,我需要知道您使用的是什么数据库以及表格的布局。但一般来说,这种方法对我有用:
我有一个类似的表,它非常大,我的所有用户都依赖。在该表锁定超过一秒之前,我可以向该表插入大约 5,000 行,我认为这是不可接受的。我通过反复试验找出了这个限制。我尝试插入 1,000 行、2000 行、10,000 行等。我玩弄了更新,直到我弄清楚在数据库开始分页数据并锁定表超过一秒之前的限制是多少。
这个限制对你来说会有所不同,这取决于你的表有多大,有多少索引,有多少列等。反复试验几乎是确定表的特定限制在哪里的唯一方法。
一旦您知道在不锁定表的情况下可以插入的最大行数(您将始终锁定它,关键是不要将其锁定“太久”),那么您可以简单地以块的形式执行更新。
因此,假设您的更新限制为 1,000 行。(这个数字太低总比太高好)
- 将要执行的更新收集到带有 RowID 的临时表中,称之为#Updates
- 创建另一个临时表来保存 RowID,称之为“#Done”
- 启动一个循环,直到 #Updates 中有 0 行不在 #Done 中
- 从#Updates 中选择未出现在#Done 中的前1,000 行,按RowID 排序并将它们插入到您的表中
- 添加 #Updates 中未出现在 #Done 中的前 1,000 行,按 RowID 排序并将它们插入到 #Done
- 计算还剩多少行
- 循环并再次执行此操作,直到 #Updates 中没有任何内容未出现在 #Done 中
下面是一些在 SQL Server 中执行此操作的代码:
--Variable needed for the loop
DECLARE @RowsLeft integer
--Create a temp table to hold all your updates (define the columns you actually need)
CREATE TABLE #Updates (MyUpdateValue varchar(100),
RowID integer IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED) --Put a RowID on it and make it the Clustering Key
--Just a dummy query, whatever the source of your update is should go here
INSERT INTO #Updates WITH(TABLOCKX) (MyUpdateValue)
SELECT MyUpdateValue
FROM Mydatabase.dbo.SourceOfUpdates
WHERE Conditions = 'True'
--Create a temp table to hold RowIDs of updates you've already inserted into your big table
CREATE TABLE #Done (RowID integer NOT NULL PRIMARY KEY CLUSTERED)
--Count how many rows of updates you have
SELECT @RowsLeft = COUNT(*)
FROM #Updates
--Start a while loop that ends when all your updates are done
WHILE @RowsLeft > 0
BEGIN
--Do the first 1000 updates which aren't in #Done
--(Replace 1000 with the number that your database / table can handle without locking up)
INSERT INTO MyBigImportantTable (MyUpdateValue)
SELECT TOP 1000 MyUpdateValue
FROM #Updates a
LEFT JOIN #Done b ON a.RowID = b.RowID
WHERE b.RowID IS NULL
ORDER BY a.RowID
--Insert them into #Done, again replace 1000 with the right number for your particular situation
INSERT INTO #Done
SELECT TOP 1000 a.RowID
FROM #Updates a
LEFT JOIN #Done b ON a.RowID = b.RowID
WHERE b.RowID IS NULL
ORDER BY a.RowID
--Count how many rows of updates remain
SELECT @RowsLeft = COUNT(*)
FROM #Updates a
LEFT JOIN #Done b ON a.RowID = b.RowID
WHERE b.RowID IS NULL
ORDER BY a.RowID
END
推荐阅读
- python - 尝试在 Django 中创建新记录时出现外键错误
- php - Laravel:处理数据后使用外部变量?
- flutter - 什么是偏移类?
- firebase - Stackdriver 怎么弄乱了我的错误分组
- batch-rename - 替换一长串文件名中的一个字符
- typescript - TypeScript - 如何从方法的参数推断类泛型类型?
- python - Zappa 部署错误部署 lambda 失败。对“/”的 GET 请求产生了 502 响应代码
- python - pymongo 无序 vs 有序批量写入速度
- mysql - MYSQL:在单个查询中计算按两个不同条件分组的列的总和
- javascript - 如何使用 ctrl+p 从网页加载 pdf