MySQL5.7 虚拟列实现表达式索引

1、建表

CREATE TABLE t1 (id INT ,rank INT, log_time DATETIME, nickname VARCHAR(64)) ENGINE INNODB;
ALTER TABLE t1 ADD PRIMARY KEY (id), ADD KEY idx_rank (rank),ADD KEY idx_log_time (log_time);

2、增加数据

DELIMITER $$
DROP PROCEDURE IF EXISTS ddd $$


USE `test`$$


DROP PROCEDURE IF EXISTS `ddd`$$


CREATE DEFINER=`root`@`localhost` PROCEDURE `ddd`()
BEGIN
    DECLARE i INTEGER;
SET i=1;
WHILE i<=5000 DO
BEGIN 
SET i=i+1;
INSERT INTO t1 (rank,log_time,nickname) VALUE (i,NOW(),i);
END;
END WHILE;
    END$$


DELIMITER ;

3、查询语句

EXPLAIN SELECT * FROM t1 WHERE DATE(log_time) = '2016-07-10'

结果：TYPE是ALL，扫描的函数是5000，也就是说这条语句进行了一个全表扫描

4、老版本解决方案

explain SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00:00:00'\G  

结果：走索引idx_log_time

5、5.7解决方案

ALTER TABLE t1 ADD COLUMN log_date  DATE AS (DATE(log_Time)) stored, ADD KEY idx_log_date (log_date);

增加函数索引idx_log_date

6、结果

EXPLAIN SELECT * FROM t1 WHERE DATE(log_time) = '2016-07-10'

EXPLAIN SELECT * FROM t1 WHERE log_date = '2016-07-10'

这两条语句均走了索引idx_log_date