如何改进存储过程查询

Question

我有一个存储过程，它检查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 ; 

Answer 1

我通常使用MSSQL，但从我可以告诉你的是，它的其他部分，给你的命中。你每次都会命中桌面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慢得多。如果可能，将CitySynonyms like CONCAT('%，'，SP_CitySynonyms，'，%')更改为CitySynonyms = SP_CitySynonyms。假设您提供了一个以"D“开头的同义词，数据库可以直接对D进行索引查找，甚至不会查看其他记录。当然，要实现这一点，您需要在包含CitySynonyms列的表上创建一个索引。如果使用Like子句，数据库将针对表中每一行的列运行instring()检查，这在处理相当多的行时是一个主要的性能影响。

以电话号码簿为例，如果我想要一个姓Smith的人，我会查找书后面的索引，发现S的开头在第600页(称为seeking)。我不关心前599页，甚至不会看上面的任何名字。如果没有索引，我将不得不遍历每一页，直到得到以S开头的名称，然后是SM，依此类推(称为扫描)。但LIKE子句就像搜索一个包含史密斯的姓氏，这个姓氏可以是以下任意一个(来自维基的名字)：BlackSmith、铜史密斯、GoldSmith、HammerSmith、Smither Smither等等。如你所见，名字到处都是。将需要检查每个名称，以查看其是否包含SMITH。这是你可以要求别人做的最不酷的事情之一，但我们总是要求数据库这样做，然后问为什么它很慢。:)

另一件事是(UpdatedDate < SP_CityUpdatedDate or UpdatedDate = SP_CityUpdatedDate)，我已将其更改为UpdatedDate <= SP_CityUpdatedDate。真的需要那张支票吗？因为如果您不这样做，您可以删除整个if exists部分，因为如果执行到达该点，您就知道它存在，因为第一个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;

be

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

因为就目前而言，它会为数据库中的每一行添加一个同义词，而我认为它应该只更新您感兴趣的那一行。

至于like语句。我想我刚刚意识到发生了什么。如果我错了，请纠正我。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

Answer 2

至少您可以使用UPSERT，而不是先检查行是否存在，然后再插入该行。

Answer 3

我想指出的是，任何时候你在字段中有一个逗号分隔的列表，你都需要重构数据库来拥有一个相关的表。在分隔字段上搜索或尝试更新分隔字段永远不会获得良好的性能。数据库设计的首要规则之一是在一个字段中永远不能包含超过一条信息。通过使用子表来保存信息，您将在所有内容上获得更快的性能。