当我尝试获取 503 行并插入每个团队他的统计数据时，我得到 75000 服务不可用。当我限制为 10000 行时，它运行良好，但看起来 75000 太多而无法加载？超时、上传和内存限制为 99999，这是 Cloudways 服务器上的最大值。
第一
我从历史匹配表中得到大约 75000 行，如下所示：
function select_tipgin_teams($season){
$select1 =
$this->db->select("HomeGoals as FTHG,AwayGoals as FTAG,HalfTimeHomeGoals as HTHG,
HalfTimeAwayGoals as HTAG,HomeTeam,AwayTeam,HomeTeamID,AwayTeamID,League,LeagueID")
->where('Season',$season)
->order_by('Date','desc')
->get('historicmatches');
return $select1;
}
而不是我想从比赛中获取每个团队并为每个团队插入数据。脚本太长，不能粘贴在这里，它不是太重要。
我使用此查询插入数据，userstats 表有 200 多列......
function insert_userstats($insert){
$query =
"INSERT INTO `zzazfhvsnt`.`userstats`
(`teamid`,`teamname`,`league`,`leagueid`,`leaguecode`,`season`,`played`,`played_home`,`played_away`,`win_home`,`draw_home`,`loss_home`,
`win_away`,`draw_away`,`loss_away`,`odds_home_win`,`odds_home_draw`,`odds_home_loss`,
`odds_away_win`,`odds_away_draw`,`odds_away_loss`,`odds_avg_win`,`goals_scored`,`goals_scored_home`,
`goals_scored_away`,`first_half_goals`,`second_half_goals`,`goals_conceded`,`goals_conceded_home`,
`goals_conceded_away`,`first_half_conceded`,`second_half_conceded`,`both_score_yes`,`both_score_no`,
`both_score_home_yes`,`both_score_home_no`,`both_score_away_yes`,`both_score_away_no`,
`both_score_1st_yes`,`both_score_1st_no`,`both_score_2nd_yes`,`both_score_2nd_no`,
over05,over15,over25,over35,over45,under05,under15,under25,under35,under45,over05_home,over15_home,
over25_home,over35_home,over45_home,under05_home,under15_home,under25_home,under35_home,under45_home,
over05_away,over15_away,over25_away,over35_away,over45_away,under05_away,under15_away,under25_away,
under35_away,under45_away,over05_1st,over15_1st,over25_1st,over35_1st,over45_1st,
under05_1st,under15_1st,under25_1st,under35_1st,under45_1st,over05_2nd,over15_2nd,over25_2nd,
over35_2nd,over45_2nd,under05_2nd,under15_2nd,under25_2nd,under35_2nd,under45_2nd,
`halffull_hh`,`halffull_hd`,`halffull_ha`,`halffull_dh`,`halffull_dd`,`halffull_da`,`halffull_ah`,
`halffull_ad`,`halffull_aa`, `halffull_home_hh`,`halffull_home_hd`,`halffull_home_ha`,
`halffull_home_dh`,`halffull_home_dd`,`halffull_home_da`,`halffull_home_ah`,`halffull_home_ad`,
`halffull_home_aa`,`halffull_away_hh`,`halffull_away_hd`,`halffull_away_ha`,`halffull_away_dh`,
`halffull_away_dd`,`halffull_away_da`,`halffull_away_ah`,`halffull_away_ad`,`halffull_away_aa`,
halfmost_first,halfmost_second,halfmost_equal,halfmost_home_first,halfmost_home_second,
halfmost_home_equal,halfmost_away_first,halfmost_away_second,halfmost_away_equal,
`win_both_halves_home_yes`,`win_both_halves_home_no`,`win_both_halves_away_yes`,
`win_both_halves_away_no`,`score_both_halves_home_yes`,`score_both_halves_home_no`,
`score_both_halves_away_yes`,`score_both_halves_away_no`,`halftime_home_win_1h`,
`halftime_home_win_2h`,`halftime_home_win_home_1h`,`halftime_home_win_home_2h`,
`halftime_home_win_away_1h`,`halftime_home_win_away_2h`,`halftime_draw_1h`,
`halftime_draw_2h`,`halftime_draw_home_1h`,`halftime_draw_home_2h`,`halftime_draw_away_1h`,
`halftime_draw_away_2h`,`halftime_away_win_1h`,`halftime_away_win_2h`,`halftime_away_win_home_1h`,
`halftime_away_win_home_2h`,`halftime_away_win_away_1h`,`halftime_away_win_away_2h`,
`wins_to_nil_yes`,`wins_to_nil_home_yes`,`wins_to_nil_away_yes`,`bts_over25`,`bts_over25_home`,
`bts_over25_away`,corner_for,corner_against,corner_home_for,corner_home_against,corner_away_for,
corner_away_against,corner_min_for,corner_max_for,corner_min_against,corner_max_against,
corner_min_home_for,corner_max_home_for,corner_min_home_against,corner_max_home_against,
corner_min_away_for,corner_max_away_for,corner_min_away_against,corner_max_away_against,
shot_for,shot_against,shot_home_for,shot_home_against,shot_away_for,shot_away_against,shot_min_for,
shot_max_for,shot_min_against,shot_max_against,shot_min_home_for,shot_max_home_for,
shot_min_home_against,shot_max_home_against,shot_min_away_for,shot_max_away_for,
shot_min_away_against,shot_max_away_against,target_for,target_against,target_home_for,
target_home_against,target_away_for,target_away_against,target_min_for,target_max_for,
target_min_against,target_max_against,target_min_home_for,target_max_home_for,
target_min_home_against,target_max_home_against,target_min_away_for,target_max_away_for,
target_min_away_against,target_max_away_against,
fouls_for,fouls_against,fouls_home_for,
fouls_home_against,fouls_away_for,fouls_away_against,fouls_min_for,fouls_max_for,fouls_min_against,
fouls_max_against,fouls_min_home_for,fouls_max_home_for,fouls_min_home_against,fouls_max_home_against,
fouls_min_away_for,fouls_max_away_for,fouls_min_away_against,fouls_max_away_against,
yellow_team,yellow_opponent,yellow_home_team,yellow_home_opponent,yellow_away_team,
yellow_away_opponent,yellow_min_team,yellow_max_team,yellow_min_opponent,yellow_max_opponent,
yellow_min_home_team,yellow_max_home_team,yellow_min_home_opponent,yellow_max_home_opponent,
yellow_min_away_team,yellow_max_away_team,yellow_min_away_opponent,yellow_max_away_opponent,
red_team,red_opponent,red_home_team,red_home_opponent,red_away_team,red_away_opponent,exact_score,
exact_score_home,exact_score_away,exact_score_1st,exact_score_2nd,`title_overall`,`title_home`,
`title_away`,`top3_overall`,`top3_home`,`top3_away`, `mid_overall`,`mid_home`,`mid_away`,
`bott3_overall`,`bott3_home`,`bott3_away`,`rel_overall`,`rel_home`,`rel_away`,`source`
)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE
`teamid` = ?,`teamname` = ?,`league` = ?,`leagueid` = ?,`leaguecode` = ?,`season` = ?,
`played` = `played` + ?,`played_home` = `played_home` + ?,`played_away` = `played_away` + ?,
`win_home` = `win_home` + ?,`draw_home` = `draw_home` + ?,`loss_home` = `loss_home` + ?,
`win_away` = `win_away` + ?,`draw_away` = `draw_away` + ?,`loss_away` = `loss_away` + ?,
`odds_home_win` = `odds_home_win` + ?,`odds_home_draw` = `odds_home_draw` + ?,
`odds_home_loss` = `odds_home_loss` + ?,`odds_away_win` = `odds_away_win` + ?,
`odds_away_draw` = `odds_away_draw` + ?,`odds_away_loss` = `odds_away_loss` + ?,
`odds_avg_win` = `odds_avg_win` + ?,`goals_scored` = `goals_scored` + ?,
`goals_scored_home` = `goals_scored_home` + ?,`goals_scored_away` = `goals_scored_away` + ?,
`first_half_goals` = `first_half_goals` + ?,`second_half_goals` = `second_half_goals` + ?,
`goals_conceded` = `goals_conceded` + ?,`goals_conceded_home` = `goals_conceded_home` + ?,
`goals_conceded_away` = `goals_conceded_away` + ?,`first_half_conceded` = `first_half_conceded` + ?,
`second_half_conceded` = `second_half_conceded` + ?,`both_score_yes` = `both_score_yes` + ?,
`both_score_no` = `both_score_no` + ?,`both_score_home_yes` = `both_score_home_yes` + ?,
`both_score_home_no` = `both_score_home_no` + ?,`both_score_away_yes` = `both_score_away_yes` + ?,
`both_score_away_no` = `both_score_away_no` + ?,`both_score_1st_yes` = `both_score_1st_yes` + ?,
`both_score_1st_no` = `both_score_1st_no` + ?,`both_score_2nd_yes` = `both_score_2nd_yes` + ?,
`both_score_2nd_no` = `both_score_2nd_no` + ?,over05 = over05 + ?,over15 = over15 + ?,
over25 = over25 + ?,over35 = over35 + ?,over45 = over45 + ?,under05 = under05 + ?,
under15 = under15 + ?,under25 = under25 + ?,under35 = under35 + ?,under45 = under45 + ?,
over05_home = over05_home + ?,over15_home = over15_home + ?,over25_home = over25_home + ?,
over35_home = over35_home + ?,over45_home = over45_home + ?,under05_home = under05_home + ?,
under15_home = under15_home + ?,under25_home = under25_home + ?,under35_home = under35_home + ?,
under45_home = under45_home + ?,over05_away = over05_away + ?,over15_away = over15_away + ?,
over25_away = over25_away + ?,over35_away = over35_away + ?,over45_away = over45_away + ?,
under05_away = under05_away + ?,under15_away = under15_away + ?,under25_away = under25_away + ?,
under35_away = under35_away + ?,under45_away = under45_away + ?,over05_1st = over05_1st + ?,
over15_1st = over15_1st + ?,over25_1st = over25_1st + ?,over35_1st = over35_1st + ?,
over45_1st = over45_1st + ?,under05_1st = under05_1st + ?,under15_1st = under15_1st + ?,
under25_1st = under25_1st + ?,under35_1st = under35_1st + ?,under45_1st = under45_1st + ?,
over05_2nd = over05_2nd + ?,over15_2nd = over15_2nd + ?,over25_2nd = over25_2nd + ?,
over35_2nd = over35_2nd + ?,over45_2nd = over45_2nd + ?,under05_2nd = under05_2nd + ?,
under15_2nd = under15_2nd + ?,under25_2nd = under25_2nd + ?,under35_2nd = under35_2nd + ?,
under45_2nd = under45_2nd + ?,
`halffull_hh` = `halffull_hh` + ?,`halffull_hd` = `halffull_hd` + ?,`halffull_ha` = `halffull_ha` + ?
,`halffull_dh` = `halffull_dh` + ?,`halffull_dd` = `halffull_dd` + ?,`halffull_da` = `halffull_da` + ?
,`halffull_ah` = `halffull_ah` + ?,`halffull_ad` = `halffull_ad` + ?,`halffull_aa` = `halffull_aa` + ?,
`halffull_home_hh` = `halffull_home_hh` + ?,`halffull_home_hd` = `halffull_home_hd` + ?,
`halffull_home_ha` = `halffull_home_ha` + ?,
`halffull_home_dh` = `halffull_home_dh` + ?,`halffull_home_dd` = `halffull_home_dd` + ?,
`halffull_home_da` = `halffull_home_da` + ?,`halffull_home_ah` = `halffull_home_ah` + ?,
`halffull_home_ad` = `halffull_home_ad` + ?,
`halffull_home_aa` = `halffull_home_aa` + ?,`halffull_away_hh` = `halffull_away_hh` + ?,
`halffull_away_hd` = `halffull_away_hd` + ?,`halffull_away_ha` = `halffull_away_ha` + ?,
`halffull_away_dh` = `halffull_away_dh` + ?,
`halffull_away_dd` = `halffull_away_dd` + ?,`halffull_away_da` = `halffull_away_da` + ?,
`halffull_away_ah` = `halffull_away_ah` + ?,`halffull_away_ad` = `halffull_away_ad` + ?,
`halffull_away_aa` = `halffull_away_aa` + ?,halfmost_first = halfmost_first + ?,
halfmost_second = halfmost_second + ?,halfmost_equal = halfmost_equal + ?,
halfmost_home_first = halfmost_home_first + ?,halfmost_home_second = halfmost_home_second + ?,
halfmost_home_equal = halfmost_home_equal + ?,halfmost_away_first = halfmost_away_first + ?,
halfmost_away_second = halfmost_away_second + ?,halfmost_away_equal = halfmost_away_equal + ?,
`win_both_halves_home_yes` = win_both_halves_home_yes + ?,
`win_both_halves_home_no` = win_both_halves_home_no + ?,
`win_both_halves_away_yes` = win_both_halves_away_yes + ?,
`win_both_halves_away_no` = win_both_halves_away_no + ?,
`score_both_halves_home_yes` = score_both_halves_home_yes + ?,
`score_both_halves_home_no` = score_both_halves_home_no + ?,
`score_both_halves_away_yes` = score_both_halves_away_yes + ?,
`score_both_halves_away_no` = score_both_halves_away_no + ?,
`halftime_home_win_1h` = halftime_home_win_1h + ?,`halftime_home_win_2h` = halftime_home_win_2h + ?,
`halftime_home_win_home_1h` = halftime_home_win_home_1h + ?,
`halftime_home_win_home_2h` = halftime_home_win_home_2h + ?,
`halftime_home_win_away_1h` = halftime_home_win_away_1h + ?,
`halftime_home_win_away_2h` = halftime_home_win_away_2h + ?,`halftime_draw_1h` = halftime_draw_1h + ?,
`halftime_draw_2h` = halftime_draw_2h + ?,`halftime_draw_home_1h` = halftime_draw_home_1h + ?,
`halftime_draw_home_2h` = halftime_draw_home_2h + ?,`halftime_draw_away_1h` = halftime_draw_away_1h + ?,
`halftime_draw_away_2h` = halftime_draw_away_2h + ?,`halftime_away_win_1h` = halftime_away_win_1h + ?,
`halftime_away_win_2h` = halftime_away_win_2h + ?,
`halftime_away_win_home_1h` = halftime_away_win_home_1h + ?,
`halftime_away_win_home_2h` = halftime_away_win_home_2h + ?,
`halftime_away_win_away_1h` = halftime_away_win_away_1h + ?,
`halftime_away_win_away_2h` = halftime_away_win_away_2h + ?,
`wins_to_nil_yes` = wins_to_nil_yes + ?,`wins_to_nil_home_yes` = wins_to_nil_home_yes + ?,
`wins_to_nil_away_yes` = wins_to_nil_away_yes + ?,`bts_over25` = bts_over25 + ?,
`bts_over25_home` = bts_over25_home + ?,`bts_over25_away` = bts_over25_away + ?,
corner_for = corner_for + ?,corner_against = corner_against + ?,corner_home_for = corner_home_for + ?,
corner_home_against = corner_home_against + ?,corner_away_for = corner_away_for + ?,
corner_away_against = corner_away_against + ?,corner_min_for = ?,corner_max_for = ?,corner_min_against = ?,
corner_max_against = ?,corner_min_home_for = ?,corner_max_home_for = ?,corner_min_home_against = ?,
corner_max_home_against = ?,corner_min_away_for = ?,corner_max_away_for = ?,
corner_min_away_against = ?,corner_max_away_against = ?,
shot_for=shot_for+?,shot_against=shot_against+?,shot_home_for=shot_home_for+?,
shot_home_against=shot_home_against+?,shot_away_for=shot_away_for+?,
shot_away_against=shot_away_against+?,
shot_min_for=?,shot_max_for=?,shot_min_against=?,shot_max_against=?,shot_min_home_for=?,
shot_max_home_for=?,shot_min_home_against=?,shot_max_home_against=?,shot_min_away_for=?,
shot_max_away_for=?,shot_min_away_against=?,shot_max_away_against=?,target_for=target_for+?,
target_against=target_against+?,target_home_for=target_home_for+?,
target_home_against=target_home_against+?,target_away_for=target_away_for+?,
target_away_against=target_away_against+?,target_min_for=?,target_max_for=?,
target_min_against=?,target_max_against=?,target_min_home_for=?,target_max_home_for=?,
target_min_home_against=?,target_max_home_against=?,target_min_away_for=?,target_max_away_for=?,
target_min_away_against=?,target_max_away_against=?,
fouls_for = fouls_for + ?,fouls_against = fouls_against + ?,fouls_home_for = fouls_home_for + ?,
fouls_home_against = fouls_home_against + ?,fouls_away_for = fouls_away_for + ?,
fouls_away_against = fouls_away_against + ?,fouls_min_for = ?,fouls_max_for = ?,fouls_min_against=?,
fouls_max_against=?,fouls_min_home_for=?,fouls_max_home_for=?,fouls_min_home_against=?,
fouls_max_home_against=?,fouls_min_away_for=?,fouls_max_away_for=?,fouls_min_away_against=?,
fouls_max_away_against=?,
yellow_team = yellow_team + ?,yellow_opponent = yellow_opponent + ?,
yellow_home_team = yellow_home_team + ?,yellow_home_opponent = yellow_home_opponent + ?,
yellow_away_team = yellow_away_team + ?,yellow_away_opponent = yellow_away_opponent + ?,
yellow_min_team = ?,yellow_max_team = ?,yellow_min_opponent = ?,yellow_max_opponent = ?,
yellow_min_home_team = ?,yellow_max_home_team = ?,yellow_min_home_opponent = ?,
yellow_max_home_opponent = ?,yellow_min_away_team = ?,yellow_max_away_team = ?,
yellow_min_away_opponent = ?,yellow_max_away_opponent = ?,
red_team = red_team + ?,red_opponent = red_opponent + ?,red_home_team = red_home_team + ?,
red_home_opponent = red_home_opponent + ?,red_away_team = red_away_team + ?,
red_away_opponent = red_away_opponent + ?,exact_score = ?,exact_score_home = ?,
exact_score_away = ?,exact_score_1st = ?,exact_score_2nd = ?,`title_overall`=?,`title_home`=?,
`title_away`=?,`top3_overall`=?,`top3_home`=?,`top3_away`=?,`mid_overall`=?,`mid_home`=?,`mid_away`=?,
`bott3_overall`=?,`bott3_home`=?,`bott3_away`=?,`rel_overall`=?,`rel_home`=?,`rel_away`=?,`source`=?
;";
$this->db->query($query,$insert);
}
我能做什么？
使用循环，每次迭代仅执行 5K 到 10K 查询。几年前，当我不得不从一个表选择/插入数百万个条目到另一个表时，我遇到了类似的问题。使用循环解决了它。