mysql - 如何改善存储过程查询

我已经存储过程，针对MySql服务器检查是否可以插入或更新记录。
对于开始时间，它是如此之快，在5-10分钟后，它是如此之慢。
我运行了3000条记录，并且sp的执行非常糟糕，在1-1.5小时后它就完成了...
我问，我该如何改善呢？

谢谢。

存储过程：

DELIMITER $$
DROP PROCEDURE IF EXISTS test.SPInsertUpdateCity$$
CREATE DEFINER=root@localhost PROCEDURE SPInsertUpdateCity(
in SP_CityName VARCHAR(100) charset utf8,
in SP_CitySynonyms mediumtext charset utf8,
in SP_CityNumberPostOffice varchar(100),
in SP_CityUpdatedDate date)
BEGIN
    if(not exists(select CityID from city where CityName = SP_CityName)) then
        insert into city(CityName, CitySynonyms, CityNumberPostOffice ,UpdatedDate)
        values(SP_CityName, CONCAT(',',SP_CitySynonyms, ','),SP_CityNumberPostOffice,SP_CityUpdatedDate);
    else if((exists(select cityId from city
    where
        CityName = SP_CityName and
        (UpdatedDate < SP_CityUpdatedDate or UpdatedDate = SP_CityUpdatedDate))) and
        not exists(SELECT CitySynonyms FROM city
        WHERE
            CitySynonyms in(select CitySynonyms from city where CitySynonyms like CONCAT('%,',SP_CitySynonyms,',%'))))
            then
            update city set
                CitySynonyms = CONCAT(CitySynonyms,SP_CitySynonyms,','),
                UpdatedDate = SP_CityUpdatedDate;
    end if;
    end if;
END$$
DELIMITER ;

最佳答案

我通常使用MSSQL，但根据我的告诉您的elseif部分，这给您带来了成功。每次运行时，您要敲击桌子3次，而可能是两次。

这是重新格式化后的原始查询，以方便比较。

    if(not exists(  select  CityID
                    from    city
                    where   CityName = SP_CityName)) then
        insert into city(   CityName, CitySynonyms, CityNumberPostOffice ,UpdatedDate)
                values  (   SP_CityName, CONCAT(',',SP_CitySynonyms, ','), SP_CityNumberPostOffice, SP_CityUpdatedDate);
    else if((exists(    select  cityId
                        from    city
                        where   CityName = SP_CityName and
                                (UpdatedDate < SP_CityUpdatedDate or UpdatedDate = SP_CityUpdatedDate))) and
            not exists( SELECT CitySynonyms
                        FROM city
                        WHERE CitySynonyms in ( select CitySynonyms
                                                from city
                                                where CitySynonyms like CONCAT('%,',SP_CitySynonyms,',%')))) then
        update city set CitySynonyms = CONCAT(CitySynonyms,SP_CitySynonyms,','),
                        UpdatedDate = SP_CityUpdatedDate;
    end if;
    end if;

这是新的

if(not exists(  select  CityID
                from    city
                where   CityName = SP_CityName)) then
    insert into city(   CityName, CitySynonyms, CityNumberPostOffice ,UpdatedDate)
            values  (   SP_CityName, CONCAT(',',SP_CitySynonyms, ','), SP_CityNumberPostOffice, SP_CityUpdatedDate);
else if((exists(    select  cityId
                    from    city
                    where   CityName = SP_CityName and
                            UpdatedDate <= SP_CityUpdatedDate)) and
        not exists( select  CitySynonyms
                    from    city
                    where   CitySynonyms like CONCAT('%,',SP_CitySynonyms,',%'))) then
    update city set CitySynonyms = CONCAT(CitySynonyms,SP_CitySynonyms,','),
                    UpdatedDate = SP_CityUpdatedDate;
end if;
end if;

还有其他一些事情可以做，因此可以进一步加快查询速度。
like子句比equals慢得多。如果可能，将诸如CONCAT（'％，'，SP_CitySynonyms，'，％'）之类的CitySynonyms更改为CitySynonyms = SP_CitySynonyms。
假设您提供一个以“ D”开头的同义词，数据库可以直接索引以查找D，甚至不查看其他记录。
当然，要使其正常工作，您将需要在表上包含CitySynonyms列的索引。如果使用Like子句，数据库将运行instring（）检查
表中每一行的列，这是处理大量行时的主要性能损失。

从电话目录的角度来看它，如果我想要一个姓史密斯的人，我会在书的背面查找索引，然后发现S在600页的开头（称为“寻找”）。我不在乎前599页，甚至都不会看它们上的任何名称。没有索引，我将不得不遍历每一页，直到找到以S开头，SM开头的名称，依此类推（称为扫描）。但是LIKE子句就像搜索包含SMITH的姓氏，该姓氏可以是以下任何一项（来自Wiki的名称）BlackSmith Coppersmith GoldSmith HammerSmith Smither Smithers等等。如您所见，名字到处都是。需要检查每个名称，以查看其是否包含SMITH。它是您可以要求某人执行的最不可思议的事情之一，但是我们一直要求数据库填充类似的内容，然后问为什么它很慢。 :)

另一件事是（UpdatedDate 真的需要这张支票吗？因为如果不这样做，则可以删除整个if存在部分，因为您知道执行到该点时它就存在，因为如果有记录，则返回的第一个if校验将返回false。因此，现在，else if在桌面上是一个命中，而不是三个。

if(not exists(  Select  CityID
                from    city
                where   CityName = SP_CityName)) then

    insert into city(   CityName, CitySynonyms, CityNumberPostOffice ,UpdatedDate)
            values  (   SP_CityName, CONCAT(',',SP_CitySynonyms, ','), SP_CityNumberPostOffice, SP_CityUpdatedDate);

else if(not exists( select  CitySynonyms
                    from    city
                    where   CitySynonyms like CONCAT('%,',SP_CitySynonyms,',%'))) then

    update city set CitySynonyms = CONCAT(CitySynonyms,SP_CitySynonyms,','),
                    UpdatedDate = SP_CityUpdatedDate;
end if;
end if;

编辑-为回应以下评论中的问题而添加。

在我进一步之前，应该

update city set CitySynonyms = CONCAT(CitySynonyms,SP_CitySynonyms,','),
                UpdatedDate = SP_CityUpdatedDate;

是

update city set CitySynonyms = CONCAT(CitySynonyms,SP_CitySynonyms,','),
                UpdatedDate = SP_CityUpdatedDate
where CityName = SP_CityName;

因为目前，它为数据库的每一行都添加了一个同义词，因此我认为它应该仅更新您感兴趣的那一行。

至于喜欢的说法。我想我刚刚意识到发生了什么事。如果我错了纠正我。 CitySynonyms列是表中的大文本字段，其中多个值之间用逗号分隔，您正尝试使用like语句在该字符串中找到匹配项。

如果是这样，是否可以稍微更改表结构？这意味着将CitySynonyms列移动到第二个表中。

CREATE TABLE city ( CityID int(20) NOT NULL AUTO_INCREMENT,
                    CityName varchar(100) NOT NULL,
                    CityNumberPostOffice varchar(100) DEFAULT NULL,
                    UpdatedDate date DEFAULT NULL,
PRIMARY KEY (CityID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE CitySynonym ( CitySynonymID int(20) NOT NULL AUTO_INCREMENT,
                           CityID int(20) NOT NULL ,
                           CitySynonym varchar(100) NOT NULL,
PRIMARY KEY (CitySynonymID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

对于每个城市，您可以有多个同义词。它变得更加易于使用和维护，更不用说不再需要LIKE子句了。

Example:
City Table
CityID    CityName
1         Brisbane
2         Syndney

City Synonym Table
CitySynoymnID    CityID  Synonym
1                1       BNE
2                1       BRISSY
3                2       SYD

关于mysql - 如何改善存储过程查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/4577657/