MySQL 優化器 5.6.27 和 5.5 之間的區別

我們剛剛將我們的一個客戶的數據庫的 MySQL 版本從 5.5 更新到了 5.6，除了一個給我們帶來麻煩的簡單查詢外，一切都執行良好。

有下表：

CREATE TABLE `MY_TABLE` (
 `ID` bigint(20) NOT NULL,
 `DATE` datetime DEFAULT NULL,
 `NODE` varchar(50) DEFAULT NULL,
 `CELL` varchar(50) DEFAULT NULL,
 `ZONA` tinyint(4) DEFAULT NULL,
 `non_indexed_fields...`
 PRIMARY KEY (`ID`),
 UNIQUE KEY `DATE` (`DATE`,`NODE`,`CELL`,`ZONA`),
 KEY `CELL` (`CELL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我希望以下查詢使用 CELL 索引，根據 EXPLAIN 命令，這是 5.5 中唯一可能的索引，並且現在無法在 5.6 中使用（甚至通過子句 FORCE/USE INDEX(CELL) 強制使用）

SELECT 
   this_.DATE AS y0_,
   this_.NODE AS y1_,
   this_.CELL AS y2_,
   this_.ZONA AS y3_
FROM
   MY_TABLE this_
WHERE
   (this_.CELL LIKE '2817%')
GROUP BY this_.CELL , HOUR(this_.DATE)
ORDER BY y0_ DESC
LIMIT 50

這是 5.5 版本的解釋輸出（只有 1 個可辨認的索引）：

+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | this_ | range | CELL          | CELL | 53      | NULL | 5551 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+

對於 5.6 版本（2 個有效索引）：

+----+-------------+-------+-------+---------------+------+---------+------+-----------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows      | Extra                                                     |
+----+-------------+-------+-------+---------------+------+---------+------+-----------+-----------------------------------------------------------+
|  1 | SIMPLE      | this_ | index | DATE,CELL     | DATE | 117     | NULL | 145283968 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+-----------+-----------------------------------------------------------+

如果我強制使用 CELL 索引，這就是解釋的內容：

+----+-------------+-------+------+---------------+------+---------+------+-----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows      | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+-----------+----------------------------------------------+
|  1 | SIMPLE      | this_ | ALL  | DATE,CELL     | NULL | NULL    | NULL | 145332659 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----------+----------------------------------------------+

有趣的是，如果我將排序列更改為與 y0_ 不同的另一個列，它會起作用。如果我刪除 LIMIT 子句，則相同。

+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                                               |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | this_ | range | DATE,CELL     | CELL | 53      | NULL | 5551 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+

如果我通過從中刪除 DATE 欄位（或更改順序以使其不是第一個）來更改 DATE 索引，那麼它不是一個可辨認的鍵，並且它的 CELL 索引使用得很好。

在洞天之後，我想知道這裡發生了什麼，我迷路了。為什麼 MySQL 決定掃描 145283968 結果而不是 5551？為什麼它不會讓我強制使用 CELL 索引？

有什麼想法嗎？

非常感謝！

ORDER BY y0_ DESC當你有的時候沒有意義GROUP BY this_.CELL , HOUR(this_.DATE)。 ORDER BY HOUR(this_.DATE) DESC更有意義；改成這樣。我懷疑優化器會“正確”選擇CELL索引。

另一個問題……選擇幾個非聚合值而GROUP BY只選擇其中的一個子集是不“正確的”。您要顯示哪個NODE和ZONA要顯示的內容？你會得到一個隨機的。您可以更改為MAX(NODE)，等等。您說的是NODE和ZONA；這是否意味著UNIQUE密鑰被過度指定？也許應該只是UNIQUE(DATE, CELL)。

我提出所有這些問題是因為它們可能會誤導優化器做出錯誤的決定，例如您正在經歷的決定。

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