mysql - 通过联接、全表扫描提高查询性能

Question

我正在尝试提高《战地》游戏统计报告网站上的查询性能，但在执行非常具体的查询时遇到了一些问题。我遇到的问题是 EXPLAIN 指出此查询正在进行全表扫描。这很麻烦，因为我预计这个表会变得非常大(可能有 100 万行或更多)。我使用 MySQL 5.7 作为我选择的数据库。

这是我的表格和查询:http://pastebin.com/DsiGe2UB

    --
    -- Table structure for table `player_kit`
    --

    CREATE TABLE `player_kit` (
      `id` TINYINT UNSIGNED NOT NULL,
      `pid` INT UNSIGNED NOT NULL,
      `time` INT UNSIGNED NOT NULL DEFAULT 0,
      `kills` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
      `deaths` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
      PRIMARY KEY(`pid`,`id`),
      FOREIGN KEY(`pid`) REFERENCES player(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      FOREIGN KEY(`id`) REFERENCES kit(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  ALTER TABLE `bf2stats`.`player_kit` ADD INDEX `reverse_ids` (`id`, `pid`);


    --
    -- My Full Scanning Query
    -- SELECTS players, ordering them by kills and time in kit
    --
    SELECT p.name, p.rank, p.country, k.pid, k.kills, k.deaths, k.time
    FROM player_kit AS k
      INNER JOIN player AS p ON k.pid = p.id
    WHERE k.id = 0 AND k.kills > 0
    ORDER BY kills DESC, time DESC
    LIMIT 0, 40

    --
    -- EXPLAIN results by MySQL
    --
    id select_type table partitions type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE k NULL ref PRIMARY 1 const 75 32.11 Using index condition; Using where; Using filesort
    1 SIMPLE p NULL eq_ref PRIMARY PRIMARY 4 bf2stats.k.pid 1 100.00 NULL

    --
    -- Additional Tables just in case, for reference
    --
    --
    -- Table structure for table `kit`
    --

    CREATE TABLE `kit` (
      `id` TINYINT UNSIGNED,
      `name` VARCHAR(32) NOT NULL,
      PRIMARY KEY(`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- Table structure for table `player`
    --

    CREATE TABLE `player` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(32) UNIQUE NOT NULL,
      `rank` TINYINT NOT NULL DEFAULT 0,
      `country` CHAR(2) NOT NULL DEFAULT 'xx',
      PRIMARY KEY(`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以下是 phpMyAdmin 的解释:

我希望你们中的一个人可以帮助我提高该查询的性能，因为我在其上放置的任何类型的索引似乎都没有多大帮助。

Answer 1

对于此查询:

SELECT p.name, p.rank, p.country, k.pid, k.kills, k.deaths, k.time
FROM player_kit k INNER JOIN
     player p
     ON k.pid = p.id
WHERE k.id = 0 AND k.kills > 0
ORDER BY kills DESC, time DESC
LIMIT 0, 40;

最优索引为:

• player_kit(id,kills,pid)
• player(id) -- 如果还不存在

您还可以在索引中添加其他列以获得查询的覆盖索引。