优化参数化MySQL查询

Question

我有一个具有许多参数的查询，如果在MySQLWorkbench中运行这些参数，运行大约需要一秒钟时间。

如果我接受这个查询并去掉参数，然后将值替换到查询中，那么运行大约需要22秒钟，就像我将这个查询转换为一个参数化存储过程并运行它一样(然后大约要花22秒)。

我已经在MySQL上启用了概要分析，我可以在那里看到一些东西。例如，它显示了检查的行数，并且有一个差数(20,000到400,000)，我认为这是处理时间增加20倍的原因。

配置文件中的另一个不同之处是，从MySQLWorkbench发送的参数化查询仍然具有参数(例如，限制< @lim)，而sproc则设置了值(其中限制< 300)。

我尝试过许多不同的方法，我使用JetBrains的DataGrip (以及MySQLWorkbench )，它的工作方式类似于MySQLWorkbench (通过@参数发送)，我尝试过执行来自MySQLWorkbench、DataGrip、Java (JDBC)和.Net的查询和sproc。我也尝试过用Java编写语句，但无法达到将“raw”SQL发送到MySQL的性能。

我觉得我错过了一些显而易见的东西，但我不知道它是什么。

查询相对比较复杂，它有一个CTE --几个子选择和几个连接，但是正如我所说的，它直接从MySQL直接运行。

我的主要问题是，为什么查询在一种格式上比另一种格式快20倍。向MySQL发送查询的方式是否与此有关(通过发送的“@”值，我可以在存储过程中复制这个值吗？

更新1月1日

谢谢您的评论，我最初没有发布这个查询，因为我更感兴趣的是关于变量/参数的使用的一般概念，以及如何利用它(或者不是)。

以下是原始查询：

with tmp_bat as (select bd.MatchId,
                        bd.matchtype,
                        bd.playerid,
                        bd.teamid,
                        bd.opponentsid,
                        bd.inningsnumber,
                        bd.dismissal,
                        bd.dismissaltype,
                        bd.bowlerid,
                        bd.fielderid,
                        bd.score,
                        bd.position,
                        bd.notout,
                        bd.balls,
                        bd.minutes,
                        bd.fours,
                        bd.sixes,
                        bd.hundred,
                        bd.fifty,
                        bd.duck,
                        bd.captain,
                        bd.wicketkeeper,
                        m.hometeamid,
                        m.awayteamid,
                        m.matchdesignator,
                        m.matchtitle,
                        m.location,
                        m.tossteamid,
                        m.resultstring,
                        m.whowonid,
                        m.howmuch,
                        m.victorytype,
                        m.duration,
                        m.ballsperover,
                        m.daynight,
                        m.LocationId
                 from (select *
                       from battingdetails
                       where matchid in
                             (select id
                              from matches
                              where id in (select matchid from battingdetails)
                                and matchtype = @match_type
                             )) as bd
                          join matches m on m.id = bd.matchid
                          join extramatchdetails emd1
                               on emd1.MatchId = m.Id
                                   and emd1.TeamId = bd.TeamId
                          join extramatchdetails emd2
                               on emd2.MatchId = m.Id
                                   and emd2.TeamId = bd.TeamId
)
select players.fullname                                    name,
       teams.teams                                         team,
       ''                                                  opponents,
       players.sortnamepart,
       innings.matches,
       innings.innings,
       innings.notouts,
       innings.runs,
       HS.score                                            highestscore,
       HS.NotOut,
       CAST(TRUNCATE(innings.runs / (CAST((Innings.Innings - innings.notOuts) AS DECIMAL)),
                     2) AS DECIMAL(7, 2))                  'Avg',
       innings.hundreds,
       innings.fifties,
       innings.ducks,
       innings.fours,
       innings.sixes,
       innings.balls,
       CONCAT(grounds.CountryName, ' - ', grounds.KnownAs) Ground,
       ''                                                  Year,
       ''                                                  CountryName
from (select count(case when inningsnumber = 1 then 1 end)                           matches,
             count(case when dismissaltype != 11 and dismissaltype != 14 then 1 end) innings,
             LocationId,
             playerid,
             MatchType,
             SUM(score)                                                              runs,
             SUM(notout)                                                             notouts,
             SUM(hundred)                                                            Hundreds,
             SUM(fifty)                                                              Fifties,
             SUM(duck)                                                               Ducks,
             SUM(fours)                                                              Fours,
             SUM(sixes)                                                              Sixes,
             SUM(balls)                                                              Balls
      from tmp_bat
      group by MatchType, playerid, LocationId) as innings
         JOIN players ON players.id = innings.playerid
         join grounds on Grounds.GroundId = LocationId and grounds.MatchType = innings.MatchType
         join
     (select pt.playerid, t.matchtype, GROUP_CONCAT(t.name SEPARATOR ', ') as teams
      from playersteams pt
               join teams t on pt.teamid = t.id
      group by pt.playerid, t.matchtype)
         as teams on teams.playerid = innings.playerid and teams.matchtype = innings.MatchType
         JOIN
     (SELECT playerid,
             LocationId,
             MAX(Score)  Score,
             MAX(NotOut) NotOut
      FROM (SELECT battingdetails.playerid,
                   battingdetails.score,
                   battingdetails.notout,
                   battingdetails.LocationId
            FROM tmp_bat as battingdetails
                     JOIN (SELECT battingdetails.playerid,
                                  battingdetails.LocationId,
                                  MAX(battingdetails.Score) AS score
                           FROM tmp_bat as battingdetails
                           GROUP BY battingdetails.playerid,
                                    battingdetails.LocationId,
                                    battingdetails.playerid) AS maxscore
                          ON battingdetails.score = maxscore.score
                              AND battingdetails.playerid = maxscore.playerid
                              AND battingdetails.LocationId = maxscore.LocationId                              ) AS internal
      GROUP BY internal.playerid, internal.LocationId) AS HS
     ON HS.playerid = innings.playerid and hs.LocationId = innings.LocationId

where innings.runs >= @runs_limit

order by runs desc, KnownAs, SortNamePart

limit 0, 300;

无论你在哪里看到'@match_type‘，我都会用它来代替一个值('t')。这个查询需要1.1秒才能运行。使用硬编码值的查询，而不是到3.5秒的变量(参见下面的另一个注释)。对此查询的解释提供如下内容：

1,PRIMARY,<derived7>,,ALL,,,,,219291,100,Using temporary; Using filesort
1,PRIMARY,players,,eq_ref,PRIMARY,PRIMARY,4,teams.playerid,1,100,
1,PRIMARY,<derived2>,,ref,<auto_key3>,<auto_key3>,26,"teams.playerid,teams.matchtype",11,100,Using where
1,PRIMARY,grounds,,ref,GroundId,GroundId,4,innings.LocationId,1,10,Using where
1,PRIMARY,<derived8>,,ref,<auto_key0>,<auto_key0>,8,"teams.playerid,innings.LocationId",169,100,
8,DERIVED,<derived3>,,ALL,,,,,349893,100,Using temporary
8,DERIVED,<derived14>,,ref,<auto_key0>,<auto_key0>,13,"battingdetails.PlayerId,battingdetails.LocationId,battingdetails.Score",10,100,Using index
14,DERIVED,<derived3>,,ALL,,,,,349893,100,Using temporary
7,DERIVED,t,,ALL,PRIMARY,,,,3323,100,Using temporary; Using filesort
7,DERIVED,pt,,ref,TeamId,TeamId,4,t.Id,65,100,
2,DERIVED,<derived3>,,ALL,,,,,349893,100,Using temporary
3,DERIVED,matches,,ALL,PRIMARY,,,,114162,10,Using where
3,DERIVED,m,,eq_ref,PRIMARY,PRIMARY,4,matches.Id,1,100,
3,DERIVED,emd1,,ref,"PRIMARY,TeamId",PRIMARY,4,matches.Id,1,100,Using index
3,DERIVED,emd2,,eq_ref,"PRIMARY,TeamId",PRIMARY,8,"matches.Id,emd1.TeamId",1,100,Using index
3,DERIVED,battingdetails,,ref,"TeamId,MatchId,match_team",match_team,8,"emd1.TeamId,matches.Id",15,100,
3,DERIVED,battingdetails,,ref,MatchId,MatchId,4,matches.Id,31,100,Using index; FirstMatch(battingdetails)

对硬编码值的查询的解释如下所示：

1,PRIMARY,<derived8>,,ALL,,,,,20097,100,Using temporary; Using filesort
1,PRIMARY,players,,eq_ref,PRIMARY,PRIMARY,4,HS.PlayerId,1,100,
1,PRIMARY,grounds,,ref,GroundId,GroundId,4,HS.LocationId,1,100,Using where
1,PRIMARY,<derived2>,,ref,<auto_key0>,<auto_key0>,30,"HS.LocationId,HS.PlayerId,grounds.MatchType",17,100,Using where
1,PRIMARY,<derived7>,,ref,<auto_key0>,<auto_key0>,46,"HS.PlayerId,innings.MatchType",10,100,Using where
8,DERIVED,matches,,ALL,PRIMARY,,,,114162,10,Using where; Using temporary
8,DERIVED,m,,eq_ref,"PRIMARY,LocationId",PRIMARY,4,matches.Id,1,100,
8,DERIVED,emd1,,ref,"PRIMARY,TeamId",PRIMARY,4,matches.Id,1,100,Using index
8,DERIVED,emd2,,eq_ref,"PRIMARY,TeamId",PRIMARY,8,"matches.Id,emd1.TeamId",1,100,Using index
8,DERIVED,<derived14>,,ref,<auto_key2>,<auto_key2>,4,m.LocationId,17,100,
8,DERIVED,battingdetails,,ref,"PlayerId,TeamId,Score,MatchId,match_team",MatchId,8,"matches.Id,maxscore.PlayerId",1,3.56,Using where
8,DERIVED,battingdetails,,ref,MatchId,MatchId,4,matches.Id,31,100,Using index; FirstMatch(battingdetails)
14,DERIVED,matches,,ALL,PRIMARY,,,,114162,10,Using where; Using temporary
14,DERIVED,m,,eq_ref,PRIMARY,PRIMARY,4,matches.Id,1,100,
14,DERIVED,emd1,,ref,"PRIMARY,TeamId",PRIMARY,4,matches.Id,1,100,Using index
14,DERIVED,emd2,,eq_ref,"PRIMARY,TeamId",PRIMARY,8,"matches.Id,emd1.TeamId",1,100,Using index
14,DERIVED,battingdetails,,ref,"TeamId,MatchId,match_team",match_team,8,"emd1.TeamId,matches.Id",15,100,
14,DERIVED,battingdetails,,ref,MatchId,MatchId,4,matches.Id,31,100,Using index; FirstMatch(battingdetails)
7,DERIVED,t,,ALL,PRIMARY,,,,3323,100,Using temporary; Using filesort
7,DERIVED,pt,,ref,TeamId,TeamId,4,t.Id,65,100,
2,DERIVED,matches,,ALL,PRIMARY,,,,114162,10,Using where; Using temporary
2,DERIVED,m,,eq_ref,PRIMARY,PRIMARY,4,matches.Id,1,100,
2,DERIVED,emd1,,ref,"PRIMARY,TeamId",PRIMARY,4,matches.Id,1,100,Using index
2,DERIVED,emd2,,eq_ref,"PRIMARY,TeamId",PRIMARY,8,"matches.Id,emd1.TeamId",1,100,Using index
2,DERIVED,battingdetails,,ref,"TeamId,MatchId,match_team",match_team,8,"emd1.TeamId,matches.Id",15,100,
2,DERIVED,battingdetails,,ref,MatchId,MatchId,4,matches.Id,31,100,Using index; FirstMatch(battingdetails)

关于如何改进我的SQL的指针总是受欢迎的(我绝对是而不是数据库人员)，但是我仍然想了解我是否可以将SQL与代码中的变量一起使用，以及为什么这会大大提高性能。

更新2 1月1日

AAArrrggghhh。我的机器一夜之间重新启动，现在查询通常运行得更快。仍然是1秒vs3秒，但是20次的减速似乎已经消失了

Answer 1

在WITH结构中，您是否过度考虑了选择In (选择in (选择in )).夸大我在解决方案中可以简化为with Innings的内容。

而且，您曾两次加入extraMatchDetails，但在比赛和团队中加入的条件相同，但从未在"WITH CTE“中使用这些表中的任何一个，从而使该组件毫无用处，不是吗？然而，MATCH表有homeTeamID和AwayTeamID，这就是我认为您的实际意图是

此外，您的WITH正在提取许多不需要或在后续返回中不需要或使用的列，如船长、WicketKeeper。

所以我重组了..。预先查询打击的细节一次，并总结，然后你应该能够加入这一点。

希望这可能是一个更适合您的需要，功能和性能。

with innings as 
(
select
        bd.matchId,
        bd.matchtype,
        bd.playerid,
        m.locationId,
        count(case when bd.inningsnumber = 1 then 1 end) matches,
        count(case when bd.dismissaltype in ( 11, 14 ) then 0 else 1 end) innings,
        SUM(bd.score) runs,
        SUM(bd.notout) notouts,
        SUM(bd.hundred) Hundreds,
        SUM(bd.fifty) Fifties,
        SUM(bd.duck) Ducks,
        SUM(bd.fours) Fours,
        SUM(bd.sixes) Sixes,
        SUM(bd.balls) Balls
    from
        battingDetails bd
            join Match m
                on bd.MatchID = m.MatchID           
    where
        matchtype = @match_type
    group by 
        bd.matchId,
        bd.matchType, 
        bd.playerid,
        m.locationId
)


select
        p.fullname playerFullName,
        p.sortnamepart,
        CONCAT(g.CountryName, ' - ', g.KnownAs) Ground,
        t.team,
        i.matches,
        i.innings,
        i.runs,
        i.notouts,
        i.hundreds,
        i.fifties,
        i.ducks,
        i.fours,
        i.sixes,
        i.balls,
        CAST( TRUNCATE( i.runs / (CAST((i.Innings - i.notOuts) AS DECIMAL)), 2) AS DECIMAL(7, 2)) 'Avg',
        hs.maxScore,
        hs.maxNotOut,
        '' opponents,
        '' Year,
        '' CountryName
    from
        innings i
            JOIN players p
                ON i.playerid = p.id 
            join grounds g
                on i.locationId = g.GroundId 
                and i.matchType = g.matchType
            join
            (select 
                    pt.playerid, 
                    t.matchtype, 
                    GROUP_CONCAT(t.name SEPARATOR ', ') team
                from 
                    playersteams pt
                        join teams t 
                            on pt.teamid = t.id
                group by 
                    pt.playerid, 
                    t.matchtype) as t 
                on i.playerid  = t.playerid 
                and i.MatchType = t.matchtype 
            join
                ( select
                        i2.playerid,
                        i2.locationid,
                        max( i2.score ) maxScore,
                        max( i2.notOut ) maxNotOut
                    from
                        innings i2
                    group by
                        i2.playerid,
                        i2.LocationId ) HS
                on i.playerid = HS.playerid
                AND i.locationid = HS.locationid
            FROM 

    where 
        i.runs >= @runs_limit
    order by 
        i.runs desc, 
        g.KnownAs, 
        p.SortNamePart
    limit 
        0, 300;

现在，我知道您已经说过，在服务器重新启动之后，性能会更好，但实际上，您所做的事情似乎确实存在过度膨胀的查询。

Answer 2

不确定这是正确的答案，但我想我应该张贴这个，以防其他人有同样的问题。

问题似乎是在存储过程中使用CTE。我有一个查询，它创建一个CTE，然后使用该CTE 8次。如果我使用内插变量运行此查询，则大约需要0.8秒，如果我将其转换为存储过程并使用存储过程参数，则运行大约需要一分钟( 45到63秒)！

我找到了几种解决这个问题的方法，一种是使用多个临时表(在本例中为8)，因为MySQL不能在查询中重用临时表。这样可以缩短查询时间，但并不像可维护或可伸缩的解决方案。另一个解决方法是将变量保留在适当的位置，并从存储过程参数中分配它们，这也没有真正的性能问题。我的sproc看起来是这样的：

create procedure bowling_individual_career_records_by_year_for_team_vs_opponent(IN team_id INT,
                                                                                IN opponents_id INT)
begin

    set @team_id = team_id;
    set @opponents_id = opponents_id;

    # use these variables in the SQL below

    ...


end

不确定这是最好的解决方案，但它对我有效，并保持SQL的结构与以前相同。