mysql - 如何更新一个有大量更新的表，同时保持表对大量用户的可用性？

Question

关闭。这个问题需要更多focused .它目前不接受答案。












想改善这个问题吗？更新问题，使其仅关注一个问题 editing this post .

去年关闭。




Improve this question




如何更新一个有大量更新的表，同时保持表对大量用户的可用性？

Answer 1

解决这个问题的方法不止一种。要使用适合您的代码给出准确答案，我需要知道您使用的是什么数据库以及表格的布局。但一般来说，这种方法对我有用:
我有一个类似的表，它非常大，我的所有用户都依赖它。在该表锁定超过一秒之前，我可以向该表插入大约 5,000 行，我认为这是 Not Acceptable 。我通过反复试验找出了这个限制。我尝试插入 1,000 行、2000 行、10,000 行等。我尝试了更新，直到我弄清楚数据库开始分页数据并锁定表超过一秒钟之前的限制。
这个限制对你来说会有所不同，这取决于你的表有多大、你有多少索引、有多少列等。试错几乎是找出你的表的特定限制在哪里的唯一方法。
一旦您知道可以在不锁定表的情况下插入的最大行数(您将始终锁定它，重点是不要将其锁定“太长时间”)，那么您就可以简单地分块执行更新。
因此，假设您的更新限制为 1,000 行。 (这个数字太低总是比太高更好)

将您想要进行的更新收集到一个带有 RowID 的临时表中，将其命名为 #Updates

创建另一个临时表来保存 RowID，将其命名为“#Done”

开始一个循环，直到 #Updates 中有 0 行不在 #Done

中

从 #Updates 中选择前 1,000 行，这些行没有出现在 #Done 中，按 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