ORDER BY 的 SQL 查詢優化問題

ORDER BY 的 SQL 查詢優化問題

目前狀態 我有一個國際象棋遊戲及其相應動作的數據庫（儲存為稱為 fens 的字元串）。我有兩個主表“Game”和“GamePosition”。GamePosition 在 fen 列上有一個索引，Game 在 white_elo 上有一個索引。我目前有 170471 場比賽和 14813401 個職位。我正在執行 mysql 5.7.28。

對象 我正在嘗試根據玩家的 elo 評分來獲取評分最高的遊戲。我在這裡稍微簡化了我的查詢，但是要點和性能問題是一樣的。

SELECT Game.id
FROM Game
JOIN GamePosition ON Game.id = game_id
WHERE fen = 'rnbqkbnr/pppppppp/8/8/3P4/8/PPP1PPPP/RNBQKBNR'
ORDER BY white_elo DESC
LIMIT 10

如果我得到很多結果（通常第一步給出 67k+ 個結果），這個查詢往往會有點慢（1.2 秒）。由於我計劃至少將數據庫擴展 10 倍，因此我想優化我的查詢。我執行了一個 EXPLAIN，它表明它需要先進行文件排序，然後才能在所有結果中找到評分最高的遊戲。這似乎是問題所在。從查詢中刪除 ORDER BY 使其超快（0.0008 秒）。

如果可以優化查詢，或者我是否可以以不同的方式儲存數據，有什麼想法嗎？

更新：

      Table: GamePosition
Create Table: CREATE TABLE `GamePosition` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `game_id` int(11) NOT NULL,
 `fen` varchar(100) NOT NULL,
 `move_color` char(1) NOT NULL,
 `castling_rights` varchar(4) NOT NULL,
 `en_passant_square` varchar(2) NOT NULL,
 `half_move` smallint(6) NOT NULL,
 `full_move` smallint(6) NOT NULL,
 `timestamp` time DEFAULT NULL COMMENT 'time in video',
 `move` varchar(10) DEFAULT NULL,
 `from_square` varchar(4) DEFAULT NULL,
 `to_square` varchar(4) DEFAULT NULL,
 `parent_position` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `position_ibfk_1` (`game_id`),
 KEY `fen` (`fen`),
 KEY `parent_position` (`parent_position`),
 CONSTRAINT `game_key` FOREIGN KEY (`game_id`) REFERENCES `Game` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

      Table: Game
Create Table: CREATE TABLE `Game` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `pgn` text NOT NULL,
 `white_id` int(11) NOT NULL,
 `black_id` int(11) NOT NULL,
 `white_elo` int(11) DEFAULT NULL,
 `black_elo` int(11) DEFAULT NULL,
 `result` tinyint(4) DEFAULT NULL COMMENT '0=white, 1=draw 2=black',
 `date` date DEFAULT NULL,
 `event_id` int(11) NOT NULL,
 `eco` varchar(5) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `event_key` (`event_id`),
 KEY `black_key` (`black_id`),
 KEY `white_key` (`white_id`),
 CONSTRAINT `black_key` FOREIGN KEY (`black_id`) REFERENCES `Player` (`id`),
 CONSTRAINT `event_key` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`),
 CONSTRAINT `white_key` FOREIGN KEY (`white_id`) REFERENCES `Player` (`id`)
)

      Table: Player
Create Table: CREATE TABLE `Player` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name_unique` (`id`)
)

實際查詢

SELECT GamePosition.id AS position_id, Game.id AS id,
      white.name AS white, black.name AS black, Game.id,
      white_elo, black_elo, result, date
   FROM Game
   JOIN GamePosition ON Game.id = game_id
   JOIN Player white ON white.id = white_id
   JOIN Player black ON black.id = black_id
   WHERE fen = '$fen'
   ORDER BY white_elo+black_elo DESC
   LIMIT $limit
   OFFSET $offset";

親切的問候，比約恩

避免在此文件上進行文件排序的唯一方法是維護 Game 和 GamePosition 之間的 JOIN 的物化視圖，以便您可以跨 (fen, white_elo) 創建索引。

我建議你在第一步使用不同的策略。之後，您的架構和查詢可能會執行良好。

猜測哪些列在哪些表中，我認為您需要

INDEX(fen, game_id)

“我在這裡稍微簡化了我的查詢”——警告：為“簡化”查詢提供的任何建議可能仍適用於實際查詢，也可能不適用。

這可能適用於第一步：

SELECT 
   FROM Game AS g
   WHERE EXISTS ( SELECT 1 FROM GamePosition
              WHERE game_id = g.id
                AND fen = 'rnbqkbnr/pppppppp/8/8/3P4/8/PPP1PPPP/RNBQKBNR' )
   ORDER BY white_elo DESC
   LIMIT 10

風險在於，對於一些奇怪的第一步，它需要掃描一百萬行才能找到 10 行。解決方法是每晚獲取前 10 行並保存它們。這有效地為您提供了 10 款遊戲的列表，這些遊戲對於您正在做的事情來說是“足夠”的。

更多的

在第一步之後，將查詢由內向外翻轉可能會有所幫助。原則是LIMIT盡快解決。

（我假設result在Game??? 加入時請限定所有列；這樣更容易閱讀。）

（Game.id 似乎被提取了兩次？）

SELECT x.position_id,
      x.game_id,
      w.name AS white, b.name AS black,
      g.white_elo, g.black_elo, g.result, g.date
   FROM ( SELECT gp.id AS position_id,
                 g1.id AS game_id
             FROM GamePosition AS gp
             JOIN Game AS g1  ON g1.id = gp.game_id'
             WHERE gp.fen = '$fen'
             ORDER BY g1.white_elo + g1.black_elo DESC
             LIMIT $limit
             OFFSET $offset
        ) AS x
   JOIN Game AS g2 ON x.game_id = g2.id
   JOIN Player white ON white.id = g2.white_id
   JOIN Player black ON black.id = g2.black_id

指數：

GamePosition:  INDEX(fen, game_id, id)

引用自：https://dba.stackexchange.com/questions/267085