索引列只要參與了計算, 查詢就會不走索引, 為什麼 MySQL 不對這種情況進行優化?

假設有一張名為 order 的表, 有 3 列, 其中 ctime 列有索引, 表結構如下:

查看執行計劃, 只有下面的 SQL (一) 會走索引, SQL (二) 會進行全表掃描.

SQL (一)

SELECT *
FROM `order`
WHERE ctime + INTERVAL 30 SECOND &< "2017-07-11 12:00:00" AND ctime + INTERVAL 300 SECOND &> "2017-07-11 12:00:00";


SQL (二)

SELECT *
FROM `order`
WHERE ctime &< "2017-07-11 12:00:00" - INTERVAL 30 SECOND AND ctime &> "2017-07-11 12:00:00" - INTERVAL 300 SECOND;


以上兩個 SQL 實際是等價的, 優化器只需要做一次簡單的變換就可以將 SQL (二) 優化為 SQL (一); 可實際上, MySQL 並不會做這個優化, 為什麼?

建表 SQL 如下:

## Create table
DROP TABLE IF EXISTS `order`;

CREATE TABLE `order` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR(20) NOT NULL
COMMENT "用戶 id",
`ctime` DATETIME NOT NULL
COMMENT "創建時間",
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_ctime` (`ctime`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARSET = utf8mb4
COMMENT = "訂單";

## Create procedure
DROP PROCEDURE IF EXISTS batch_insert;

DELIMITER //

CREATE PROCEDURE batch_insert()
BEGIN
DECLARE v INT DEFAULT 1;
WHILE v &<= 1000 DO INSERT INTO `order` (user_id, ctime) VALUES (concat(v, "0000"), "2017-07-11 12:00:00" - INTERVAL (v * 60) SECOND); SET v = v + 1; END WHILE; END // CALL batch_insert() // DELIMITER ;

因為SQL沒實現的那麼聰明，僅此而已。今天你要我把a+b&

說來，我對題主的描述很感興趣，為啥不是圖二走索引？

題主的圖應該是放反了！

第一個是：全表掃描，實際是在MySQLServer中過濾，因為MySQL暫不支持函數索引；

第二個是：範圍掃描，同時使用了索引下推（ICP）。

版本： 5.7.18-log

PS：

吐槽下，題主的表名竟然是 `order` 而不是 order！

sql引擎可真心不是編譯器，能給你做的優化是有限度的。

LZ是不是寫反了。。。應該是第2個才走索引，第1個不走才對吧

為了給面試官提供面試題啊