mysql - 优化存储过程以将 250k 记录更新为 1.7m 记录表中的随机记录？

Question

我目前正在运行以下存储过程。虽然它比我原来的程序更有效率，但仍然花费了过多的时间。我实际上不确定减速是什么，因为第一个 10k-30k 记录发生得很快，但随着它越来越深入，它变得越来越慢。我预计会更新大约 250k 行，大约 170 万行。完成后，我将做类似的事情，将记录插入每个“太阳系”。

举例说明这所花费的时间。它现在已经运行了 24 小时多一点，并且仅在它必须执行的 1716 的第 786 次迭代中运行。更改选择限制的原因是我的表中每个扇区可能有 1000 行。我个人没有看到任何减速，但是我不太了解 MySQL 的内部工作原理。

这项工作是在我的本地计算机上完成的，不，它并不慢，但总是有可能需要在服务器级别完成一些更改，以使这些查询更有效率。如果需要，我可以更改服务器设置，这样也有可能。仅供引用，我在 Windows 7 上使用 MySQL 的库存配置。

DECLARE CurrentOffset int; -- Current offset limit to only deal with one 
DECLARE CurrentOffsetMultiplier int;
DECLARE RandRow int; -- Random Row to make a Solar System with
DECLARE CheckSystemExists int; -- Used to insure RandRow is not already a Solar System Row
DECLARE TotalSystemLoops int; -- Total number of loops so each Galaxy gets it's systems.
DECLARE RandomSolarSystemCount int; -- This is the number of Solar Systems that will be in each Galaxy;
DECLARE UpdateSolarCount int;
DECLARE NumberOfOffsets int;

SET CurrentOffsetMultiplier = 0;
SET NumberOfOffsets = 1716;
SET CurrentOffset = 0;


OffsetLoop: LOOP
        SET UpdateSolarCount = 0;
        /*Sets the amount of Solary Systems going in a Galaxy*/
            CheckRandomSolarSystemCount: LOOP
                SET RandomSolarSystemCount = FLOOR(125 + RAND() * (175 - 125) + 1);  
                IF RandomSolarSystemCount >= 125 THEN
                    IF RandomSolarSystemCount <= 175 THEN
                        LEAVE CheckRandomSolarSystemCount;
                    END IF;
                END IF;
            END LOOP;
        UpdateGalaxyWithSolarSystems: LOOP
                SET UpdateSolarCount = UpdateSolarCount + 1;
                IF UpdateSolarCount > RandomSolarSystemCount THEN
                    LEAVE UpdateGalaxyWithSolarSystems;
                END IF;
                        /*Sets RandRow and CheckSystemExists*/
                        CheckExistsLoop: Loop
                            SET RandRow = FLOOR(0 + RAND() * (1000)+ 1);
                            SET CheckSystemExists = (SELECT COUNT(*)
                                FROM
                                    (SELECT * FROM
                                        (SELECT * FROM galaxies2 LIMIT CurrentOffset, 1000) AS LimitedTable
                                    LIMIT RandRow ,1) AS RandTable
                                WHERE SolarSystemName IS NULL);
                            IF CheckSystemExists THEN
                                LEAVE CheckExistsLoop;
                            END IF;
                        END LOOP;

                        /*Updates the tables SolarSystemName column with a default system name*/
                        UPDATE galaxies2 
                            SET SolarSystemName = CONCAT("Solar System ", RandRow)
                            WHERE galaxies2.idGalaxy = 
                                (SELECT LimitedTable.idGalaxy AS GalaxyID FROM
                                    (SELECT galaxies2.idGalaxy FROM galaxies2 LIMIT CurrentOffset, 1000) AS LimitedTable
                                LIMIT RandRow ,1)
                        ;
        END LOOP;
        SET CurrentOffsetMultiplier = CurrentOffsetMultiplier + 1;
        SET CurrentOffset = CurrentOffsetMultiplier * 1000;
        IF CurrentOffsetMultiplier = 1717 THEN
            LEAVE OffsetLoop;
        END IF;
END LOOP;

Answer 1

它变得越来越慢，因为您正在 galaxies2 表中“行走”。

SELECT * FROM galaxies2 LIMIT CurrentOffset, 1000

随着 CurrentOffset 值的增加，MySQL 必须“遍历”越来越多的记录才能到达起点。您实际上可以通过在主键上指定 ORDER BY 来提高速度。无论如何，您都希望有一个 ORDER BY，因为如果没有指定顺序，MySQL 只会随机读取记录。它不会以任何特定顺序读取记录，因此您可以(尽管不太可能)以不同的偏移量获取同一组记录。

最好在自动增量字段上指定一个范围。假设你有一个。那么第一个和最后一个查询应该执行大致相同的。它不是那么理想，因为删除的记录可能存在间隙。

SELECT * FROM galaxies2 WHERE auto_incr_field BETWEEN CurrentOffset AND CurrentOffset+1000