出於某種原因，查詢狀態掛在“發送數據”（使用 show processlist;）上，我不知道為什麼。這是創建表的腳本：

CREATE TABLE IF NOT EXISTS `esp_game` (
 `gameID` int(10) unsigned NOT NULL,
 `gameType` tinyint(3) unsigned NOT NULL,
 `mapID` tinyint(3) unsigned NOT NULL,
 `createDate` int(11) NOT NULL,
 PRIMARY KEY (`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
 `gameID` int(11) unsigned NOT NULL,
 `summonerID` int(11) unsigned NOT NULL,
 `championID` int(11) NOT NULL,
 `teamID` int(11) NOT NULL,
 `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`gameID`,`summonerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `gameTypeMap` (
 `gameTypeID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `gameTypeName` varchar(50) NOT NULL DEFAULT '',
 `gameTypePortugueseDesc` varchar(50) NOT NULL DEFAULT '',
 PRIMARY KEY (`gameTypeID`),
 UNIQUE KEY `gameTypeName` (`gameTypeName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

這是卡住的查詢：

SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;

額外的資訊：

esp_game 有大約 10M 個條目（~1 Gig），esp_gameplayer 有大約 100M（~7 Gigs）條目和 gameTypeMap 10。

刪前說明：

EXPLAIN SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows    | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
|  1 | SIMPLE      | g     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 | 7706280 | Using filesort |
|  1 | SIMPLE      | gt    | eq_ref | PRIMARY       | PRIMARY | 1       | teemo.g.gameType     |       1 |                |
|  1 | SIMPLE      | gp    | eq_ref | PRIMARY       | PRIMARY | 8       | teemo.g.gameID,const |       1 | Using index    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
3 rows in set (0.00 sec)

伺服器配置：

[mysqld]
(...)
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 1536M
innodb_lock_wait_timeout = 25

我設法讓它再次工作的唯一方法是從這些表中截斷所有內容 - 幸運的是我能夠做到這一點，但很快我將無法再截斷所有內容了！任何想法如何解決這個問題？

首先，再看一下 EXPLAIN 計劃。

第一行表示查詢優化器將執行以下操作：

• 在不使用任何鍵的情況下對至少 7,706,280 行進行全表掃描（因此是Sending data查詢狀態的根本原因）
• 通過 from 的每一行都esp_game將查找esp_gameplayer和gameTypeMap

我希望您嘗試以下方法：

重構您的查詢

您的原始查詢是：

SELECT
   g.gameID, g.gameType, g.mapID,
   g.createDate, gt.gameTypePortugueseDesc
FROM
   esp_game g
   INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129
   INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

如果 summererID 驅動要檢索哪些遊戲 ID，則查詢需要是查詢esp_gameplayer中的線索表

SELECT
   g.gameID, g.gameType, g.mapID,
   g.createDate, gt.gameTypePortugueseDesc
FROM
   (SELECT gameID FROM esp_gameplayer WHERE summonerID=401129) gp
   INNER JOIN esp_game g ON g.gameID = gp.gameID
   INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

更改 esp_gameplayer 主鍵

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
 `gameID` int(11) unsigned NOT NULL,
 `summonerID` int(11) unsigned NOT NULL,
 `championID` int(11) NOT NULL,
 `teamID` int(11) NOT NULL,
 `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

要反轉 PRIMARY KEY，請執行以下操作：

CREATE TABLE IF NOT EXISTS `esp_gameplayer_new` (
 `gameID` int(11) unsigned NOT NULL,
 `summonerID` int(11) unsigned NOT NULL,
 `championID` int(11) NOT NULL,
 `teamID` int(11) NOT NULL,
 `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO esp_gameplayer_new SELECT * FROM esp_gameplayer;
ALTER TABLE esp_gameplayer RENAME esp_gameplayer_old;
ALTER TABLE esp_gameplayer_new RENAME esp_gameplayer;

由於您要求提供特定的召喚者 ID，因此反轉主鍵可以更快地為召喚者 401129 收集所有相應的遊戲 ID

進行更改，重新執行 EXPLAIN 計劃。它應該會改善很多。

試一試 ！！！

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