mysql - 如何最大限度地减少手术的处理时间？

Question

我创建了一个非常复杂的存储过程，用于检查 c_sent_messages 表中的密码是否存在于 c_passwords 表中。然后它输入一些消息并更新数据库中的记录。最后，它将记录从c_sent_messages输入到archive表中。在我添加两行将记录添加到 archive 并从 c_sent_messages 中删除消息(以免增加存储空间)

之前，一切都很好

INSERT INTO archive SELECT *, NOW() FROM c_sent_messages WHERE id=idi;          
DELETE FROM c_sent_messages WHERE  id=idi;

我的处理器也是一个好处理器(Intel 7700 HQ)，并且我没有运行任何其他软件/进程。添加这两行后，该过程花费了太多时间将记录添加到archive表中。我想最大限度地减少此过程的处理时间。有什么办法可以让它更快吗？ (向存档添加 1000 条记录花了 120 秒)

DELIMITER $$
CREATE PROCEDURE flood () 
BEGIN
DECLARE i INT DEFAULT 0;
    declare ifExist int DEFAULT 0;
    DECLARE numara VARCHAR(10);
    declare adamin_gonderdigi varchar(150);
  declare idi int default 0;


    myloop: LOOP




      SET idi               = (SELECT id  from c_sent_messages where stats = 'N' limit 1);
        SET numara            = (SELECT sender  from c_sent_messages where id = idi);
        SET adamin_gonderdigi = (SELECT msg_text from c_sent_messages WHERE id = idi);

      select count(1) into ifExist from c_passwords b WHERE adamin_gonderdigi = b.pass;

        IF ifExist > 0 THEN
            set ifExist = 0;
                select count(1) into ifExist from c_sent_messages a WHERE a.stats ="N";
                IF ifExist > 0 THEN
                    INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
                        VALUES
                            (numara, "5757", "Code is received successfully.", "S",NOW());
                    UPDATE c_passwords a SET a.stats = "S", a.x_date = NOW()
                    WHERE pass = adamin_gonderdigi;         

                        update c_sent_messages set stats = 'S' where id = idi;

                ELSE

                    INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
                    VALUES(numara,"5757","Code is used before!","S",NOW());

                    update c_sent_messages set stats = 'U' where id = idi;

                END IF; 

        ELSE 
            INSERT INTO c_received_messages  ( receiver, sender, msg_text, stats, x_date )
            VALUES(numara,"5757","Please send a valid code!","S",NOW());

            update c_sent_messages set stats = 'F' where id = idi;
        END IF;     

        INSERT INTO archive SELECT *, NOW() FROM c_sent_messages WHERE id = idi;            
        DELETE FROM c_sent_messages WHERE  id=idi;

    SET i = i + 1;
        IF
            i = 900000 THEN
                LEAVE myloop;

        END IF;     
    END LOOP myloop;

END $$
DELIMITER;

Answer 1

• 循环开始时的 3 个 SELECT 可以通过单个 SELECT 完成 - 请参阅JOIN。
• 您有INDEX(stats, id)吗？
• 看看 IODKU 是否可以替换您的 select/if/insert/update 序列。
• 尝试在单个 SQL 语句中完成整个过程，而不循环 900000 次。