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)
。我提出所有這些問題是因為它們可能會誤導優化器做出錯誤的決定,例如您正在經歷的決定。