MySQL 5.6 子查詢無法使用索引性能退化問題分析

問題現場

表結構

CREATE TABLE `oplogs` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `kind` VARCHAR(45) NOT NULL DEFAULT '',
    `op` VARCHAR(100) NOT NULL,
    `user` VARCHAR(25) NOT NULL DEFAULT '',
    `ip` VARCHAR(16) NOT NULL DEFAULT '',
    `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34896 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

數據量：34,895 行

問題 SQL

SELECT a.id, a.kind, a.op, a.user, a.ip, a.updatetime
FROM oplogs AS a
INNER JOIN (
    SELECT id FROM oplogs WHERE 1 ORDER BY id DESC LIMIT 0, 20
) AS b USING (id);

版本性能對比

EXPLAIN 深度分析

5.5.38（優秀）：

id=2, select_type=DEPENDENT SUBQUERY, type=index, key=PRIMARY, rows=20

子查詢走主鍵索引倒序掃描，僅讀 20 行。

5.6.19（退化）：

id=2, select_type=MATERIALIZED, type=ALL, key=NULL, rows=34895

子查詢被物化為臨時表，全表掃描 3.5 萬行，未使用索引。

根因探究

MySQL 5.6 優化器重構

5.6 引入子查詢物化與半連接優化，但對 ORDER BY ... LIMIT 子查詢的處理存在回歸：

• 優化器嘗試將子查詢物化為臨時表
• 物化過程忽略 ORDER BY ... LIMIT 的索引優化機會
• 導致全表掃描構建臨時表，再參與 JOIN

解決方案與工作繞過

方案一：強制優化器策略（5.6.7+）

SELECT /*+ MAX_EXECUTION_TIME(1000) */ ...
-- 或關閉物化
SET optimizer_switch = 'materialization=off';

方案二：改寫為 JOIN + 衍生表

SELECT a.*
FROM oplogs a
JOIN (
    SELECT id FROM oplogs ORDER BY id DESC LIMIT 20
) b ON a.id = b.id;

將子查詢提升為衍生表，5.6 對衍生表優化較好。

方案三：使用直連子查詢語法

SELECT * FROM oplogs
WHERE id IN (
    SELECT id FROM oplogs ORDER BY id DESC LIMIT 20
);

方案四：升級版本

MySQL 5.7 / 8.0 修復此回歸，子查詢優化器大幅重寫，ORDER BY LIMIT 子查詢可正常走索引。