是否正在优化存储过程，以便将25万条记录更新为1.7m记录表中的随机记录？

Question

我当前正在运行以下存储过程。虽然它比我的原始程序效率高得多，但它仍然花费了大量的时间。我不确定最初的10k-30k记录发生得很快，但随着时间的推移，它变得越来越慢。我希望更新大约170万行中的大约25万行。一旦这个完成，我将做一些类似的事情，将记录插入到每个“太阳系”中。

给你举个例子，看看这需要多长时间。它现在已经运行了24小时多一点，它只在1716版本的第786次迭代中运行。更改selects限制的原因是，在我的表中，每个扇区有1000个可能的行。我个人没有看到任何速度减慢，但我不太理解MySQL的内部工作原理。

这项工作是在我的本地计算机上完成的，不，它并不慢，但总有可能需要在服务器级别进行更改，以使这些查询更有效。如果需要，我可以更改服务器设置，以便这也是一种可能性。仅供参考，我在Windows7上使用的是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，因为如果没有指定order，MySQL只会随机读取记录。它不会以任何特定的顺序读取记录，因此您可以(尽管不太可能)在不同的偏移量中获得相同的一组记录。

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

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