mysql 选择速度慢

Question

我有一个具有以下结构的表：

CREATE TABLE `game_entries` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `player_id` int(11) unsigned NOT NULL,
  `game_id` int(11) unsigned NOT NULL,
  `hero_id` int(11) unsigned NOT NULL,
  `game_avg_pts` smallint(4) unsigned NOT NULL DEFAULT '0',
  `game_season` tinyint(2) unsigned NOT NULL,
  `game_length` smallint(11) unsigned NOT NULL,
  `game_mode` char(10) NOT NULL,
  `game_is_tb` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `game_map` char(10) NOT NULL,
  `game_date` datetime NOT NULL,
  `game_playersnum` tinyint(2) NOT NULL,
  `side` tinyint(2) unsigned NOT NULL,
  `won` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `pts` smallint(11) unsigned NOT NULL,
  `pts_change` smallint(4) NOT NULL,
  `kills` smallint(4) unsigned NOT NULL DEFAULT '0',
  `deaths` smallint(4) unsigned NOT NULL DEFAULT '0',
  `assists` smallint(4) unsigned NOT NULL DEFAULT '0',
  `creeps` smallint(4) unsigned NOT NULL DEFAULT '0',
  `towers` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_game` (`player_id`,`game_id`),
  KEY `index_game` (`game_id`),
  KEY `index_player` (`player_id`),
  KEY `index_hero` (`hero_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11706564 DEFAULT CHARSET=utf8;

该表有超过 11M 行。 当我尝试选择特定player_id的所有game_entries时，查询时间太长：

mysql> SELECT * FROM `game_entries` WHERE player_id = 4;
227 rows in set (1.68 sec)

mysql> SELECT * FROM `game_entries` WHERE player_id = 4 LIMIT 25;
25 rows in set (0.27 sec)

mysql> EXPLAIN SELECT * FROM `game_entries` WHERE player_id = 4;
+----+-------------+--------------+------+--------------------------+-------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys            | key         | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+--------------------------+-------------+---------+-------+------+-------+
|  1 | SIMPLE      | game_entries | ref  | player_game,index_player | player_game | 4       | const |  226 |       |
+----+-------------+--------------+------+--------------------------+-------------+---------+-------+------+-------+
1 row in set (0.00 sec)

有什么方法可以提高此查询的速度吗？

Answer 1

一种方法是在 player_id 列上添加索引。

其次，如果可能的话，您可以在选择查询中提及有限字段而不是 *

Answer 2

考虑将 {player_id, game_id} 作为主键，而不是利用 innoDb 主键上的“集群”功能。

这将使同一玩家的游戏记录彼此“靠近”，并使 player_id = ? 的搜索速度更快。