MySql组合索引的最左原则，和索引失效测试

1.准备一张数据量在千万级的测试表，包含TRDT_LineNum 和 CreatedTime 两个字段（忽略其他字段），类型如下

`TRDT_LineNum` INT(11) DEFAULT NULL

`CreatedTime` DATETIME DEFAULT NULL

 

 

 

2.不建索引直接运行以下SQL，需要19秒出结果

-- with no index ,cost 19s
SELECT COUNT(1) FROM btrnsdetail WHERE TRDT_LineNum = 1 AND CreatedTime BETWEEN '2019-07-10' AND '2019-07-28';

3.建立组合索引(TRDT_LineNum,CreatedTime)，再运行相同SQL，需要1.5秒

-- 92s
CREATE INDEX idx_linenum_creTime ON btrnsdetail(TRDT_LineNum,CreatedTime)
-- withe index linenum_creTime,cost 1.5
SELECT COUNT(1) FROM btrnsdetail WHERE TRDT_LineNum = 1 AND CreatedTime BETWEEN '2019-07-10' AND '2019-07-28';

 

 

4.删除上面建的索引，再建立组合索引(CreatedTime,TRDT_LineNum)，再运行相同SQL，需要4.3秒（比上面的性能慢了一倍多，应该是索引部分失效所致）

DROP INDEX idx_linenum_creTime ON btrnsdetail;
-- 88.9s
CREATE INDEX idx_linenum_creTime_new ON btrnsdetail(CreatedTime,TRDT_LineNum)
-- 4.3
SELECT COUNT(1) FROM btrnsdetail WHERE TRDT_LineNum = 1 AND CreatedTime BETWEEN '2019-07-10' AND '2019-07-28';

 

 5.