MySQL查询速度很慢

概述我的表格包含以下列：gamelogs_id (auto_increment primary key) player_id (int) player_name (varchar) game_id (int) season_id (int) points (int) 该表具有以下索引+-----------------+------------+--------

我的表格包含以下列：

gamelogs_ID (auto_increment primary key)player_ID (int)player_name (varchar)game_ID (int)season_ID (int)points (int)

该表具有以下索引

+-----------------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| table           | Non_unique | Key_name           | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| player_gamelogs |          0 | PRIMARY            |            1 | player_gamelogs_ID | A         |      371330 |     NulL | NulL   |      | BTREE      |         |               || player_gamelogs |          1 | player_name        |            1 | player_name        | A         |        3375 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | points          |            1 | points          | A         |         506 |     NulL | NulL   | YES  | BTREE      |         ## heading ##|               || player_gamelogs |          1 | game_ID            |            1 | game_ID            | A         |       37133 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | season             |            1 | season             | A         |          30 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | team_abbreviation  |            1 | team_abbreviation  | A         |          70 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | player_ID          |            1 | game_ID            | A         |       41258 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | player_ID          |            2 | player_ID          | A         |      371330 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | player_ID          |            3 | dk_points          | A         |      371330 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | game_player_season |            1 | game_ID            | A         |       41258 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | game_player_season |            2 | player_ID          | A         |      371330 |     NulL | NulL   | YES  | BTREE      |         |               || player_gamelogs |          1 | game_player_season |            3 | season_ID          | A         |      371330 |     NulL | NulL   |      | BTREE      |         |               |+-----------------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我试图在比赛开始之前计算一个赛季和球员的积分平均值.因此,对于本赛季的第3场比赛,avg_points将是游戏1和2的平均值.游戏数量按顺序排列,使得较早的游戏比较晚的游戏少.我也可以选择使用日期字段,但我认为数字比较会更快？

我的查询如下：

SELECT game_ID,player_ID,player_name,(SELECT avg(points)           FROM player_gamelogs t2         WHERE t2.game_ID < t1.game_ID            AND t1.player_ID = t2.player_ID            AND t1.season_ID = t2.season_ID) AS avg_points  FROM player_gamelogs t1 ORDER BY player_name,game_ID;

EXPLAIN生成以下输出：

| ID | select_type        | table | type | possible_keys                        | key  | key_len | ref  | rows   | Extra                                           |+----+--------------------+-------+------+--------------------------------------+------+---------+------+--------+-------------------------------------------------+|  1 | PRIMARY            | t1    | ALL  | NulL                                 | NulL | NulL    | NulL | 371330 | Using filesort                                  ||  2 | DEPENDENT SUBquery | t2    | ALL  | game_ID,game_player_season | NulL | NulL    | NulL | 371330 | Range checked for each record (index map: 0xC8) |

我不确定这是因为涉及的任务的性质还是因为我的查询效率低下.谢谢你的任何建议！最佳答案请考虑以下查询：

SELECT t1.season_ID,t1.game_ID,t1.player_ID,t1.player_name,AVG(COALESCE(t2.points,0)) AS average_player_pointsFROM player_gamelogs t1        left JOIN player_gamelogs t2 ON                 t1.game_ID > t2.game_ID             AND t1.player_ID = t2.player_ID            AND t1.season_ID = t2.season_ID GROUP BY    t1.season_ID,t1.player_nameORDER BY t1.player_name,t1.game_ID;

笔记：

>要以最佳方式执行,您需要一个额外的索引(season_ID,game_ID,player_name)
>更好的是,将播放器表从ID中检索名称.对我来说,我们必须从日志表中获取播放器名称,而且如果它在索引中是必需的,这似乎是多余的.
>按已按分组列排序已分组.如果可以,请避免事后订购,因为它会产生无用的开销.正如评论中所述,这不是一种官方行为,并且假设其随时间的一致性的结果应该考虑与突然失去分类的风险. 总结

以上是内存溢出为你收集整理的MySQL查询速度很慢全部内容，希望文章能够帮你解决MySQL查询速度很慢所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。