Mysql
為什麼這兩個查詢的表現如此不同？
這是查詢 1：
SELECT A.innings_no AS Inn, bowler, OT.Out_Name AS OutType, Runs FROM (SELECT a.innings_no, a.striker, SUM(b.runs_scored) AS Runs FROM ball_by_ball a INNER JOIN batsman_scored b USING (match_id , over_id , ball_id , innings_no) WHERE b.match_id = 981018 GROUP BY a.innings_no , a.striker , striker_batting_position) AS A LEFT JOIN (SELECT w.player_out, kind_out, fielders, bowler FROM wicket_taken w INNER JOIN ball_by_ball a USING (match_id , over_id , ball_id , innings_no) WHERE a.match_id = 981018) AS B ON (striker) = (player_out) LEFT JOIN Out_Type OT ON B.kind_out = OT.Out_Id;解釋：
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 240 | 100.00 | NULL | | 1 | PRIMARY | w | NULL | ref | PRIMARY,Ball_Id,Innings_No,Over_Id,Player_Out | Player_Out | 8 | A.striker,const | 1 | 100.00 | NULL | | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index | PRIMARY | 16 | const,database.w.over_id,database.w.ball_id,database.w.innings_no | 1 | 100.00 | NULL | | 1 | PRIMARY | OT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | database.w.kind_out | 1 | 100.00 | NULL | | 2 | DERIVED | a | NULL | ref | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index | PRIMARY | 4 | const | 240 | 100.00 | Using temporary | | 2 | DERIVED | b | NULL | eq_ref | PRIMARY,Batsman_Scored_Ball_Id_index,Batsman_Scored_Innings_No_index,Batsman_Scored_Match_Id_Over_Id_Ball_Id_Innings_No_index,Batsman_Scored_Match_Id_index,Batsman_Scored_Over_Id_index | PRIMARY | 16 | const,database.a.over_id,database.a.ball_id,database.a.innings_no | 1 | 100.00 | NULL | +----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+視覺解釋：
這是查詢 2：
SELECT A.innings_no AS Inn, bowler, OT.Out_Name AS OutType, Runs FROM (SELECT a.innings_no, striker_batting_position, a.striker, SUM(b.runs_scored) AS Runs FROM ball_by_ball a INNER JOIN batsman_scored b USING (match_id , over_id , ball_id , innings_no) WHERE b.match_id = 981018 GROUP BY a.innings_no , a.striker, striker_batting_position) AS A LEFT JOIN (SELECT innings_no, striker_batting_position, w.player_out, kind_out, fielders, bowler FROM wicket_taken w INNER JOIN ball_by_ball a USING (match_id , over_id , ball_id , innings_no) WHERE a.match_id = 981018) AS B on (A.innings_no,A.striker_batting_position)=(B.innings_no,B.striker_batting_position) LEFT JOIN Out_Type OT ON B.kind_out = OT.Out_Id;解釋：
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 240 | 100.00 | NULL | | 1 | PRIMARY | w | NULL | ref | PRIMARY,Ball_Id,Innings_No,Over_Id | PRIMARY | 4 | const | 11 | 100.00 | Using where | | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index | PRIMARY | 16 | const,database.w.over_id,database.w.ball_id,A.innings_no | 1 | 100.00 | Using where | | 1 | PRIMARY | OT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | database.w.kind_out | 1 | 100.00 | NULL | | 2 | DERIVED | a | NULL | ref | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index | PRIMARY | 4 | const | 240 | 100.00 | Using temporary | | 2 | DERIVED | b | NULL | eq_ref | PRIMARY,Batsman_Scored_Ball_Id_index,Batsman_Scored_Innings_No_index,Batsman_Scored_Match_Id_Over_Id_Ball_Id_Innings_No_index,Batsman_Scored_Match_Id_index,Batsman_Scored_Over_Id_index | PRIMARY | 16 | const,database.a.over_id,database.a.ball_id,database.a.innings_no | 1 | 100.00 | NULL | +----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+視覺解釋：
這兩個查詢只有一個區別，即
joining clause第一個查詢B ON (striker) = (player_out)和第二個查詢的區別，B on (A.innings_no,A.striker_batting_position)=(B.innings_no,B.striker_batting_position)但是如果您查看Visual Explain，您會注意到 First Query Rows Count 是 240，Query Cost 是 633.62，但是 Second Query Rows Count 是 2.80K 和 Query成本是 4597.15。同樣，第一個查詢檢查了 557 行，第二個查詢檢查了 987 行。這種類型表現的原因是什麼？第一個查詢不是比第二個有效嗎？請哪位大神解釋一下，我找不到原因。
“行建構子”得到處理，但效率不高。範例
WHERE（或ON）子句：WHERE a = b -- good WHERE (x,y) = (p,q) -- bad (row constructors) WHERE x = p AND y = q -- good (same semantics as above, but better performing)（5.7.3 提高了性能。）