有一张表:
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ic` (`c`(19)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (1, 1, 0, 'null');
INSERT INTO `t1` VALUES (2, 1, 1, 'null');
INSERT INTO `t1` VALUES (3, 2, 1, '6');
INSERT INTO `t1` VALUES (4, 2, 1, '6');
INSERT INTO `t1` VALUES (6, 3, 2, '5');
INSERT INTO `t1` VALUES (7, 4, 2, 'null');
INSERT INTO `t1` VALUES (8, 4, 2, '5');
INSERT INTO `t1` VALUES (9, 5, 2, '5');
INSERT INTO `t1` VALUES (13, 7, 3, '4');
INSERT INTO `t1` VALUES (14, 7, 4, '4');
INSERT INTO `t1` VALUES (15, 8, 4, '4');
INSERT INTO `t1` VALUES (16, 8, 4, '4');
INSERT INTO `t1` VALUES (17, 9, 4, 'null');
INSERT INTO `t1` VALUES (18, 9, 5, '4');
INSERT INTO `t1` VALUES (19, 10, 5, '4');
INSERT INTO `t1` VALUES (20, 10, 5, 'null');
INSERT INTO `t1` VALUES (28, 14, 7, '3');
INSERT INTO `t1` VALUES (29, 15, 7, '2');
INSERT INTO `t1` VALUES (30, 15, 8, '2');
INSERT INTO `t1` VALUES (31, 16, 8, '2');
INSERT INTO `t1` VALUES (32, 16, 8, 'null');
INSERT INTO `t1` VALUES (33, 17, 8, '2');
INSERT INTO `t1` VALUES (34, 17, 9, '2');
INSERT INTO `t1` VALUES (35, 18, 9, '2');
INSERT INTO `t1` VALUES (36, 18, 9, 'null');
INSERT INTO `t1` VALUES (37, 19, 9, '1');
INSERT INTO `t1` VALUES (38, 19, 10, '1');
INSERT INTO `t1` VALUES (39, 20, 10, 'null');
INSERT INTO `t1` VALUES (40, 20, 10, '1');
INSERT INTO `t1` VALUES (41, 21, 10, '1');
INSERT INTO `t1` VALUES (42, 21, 11, '1');
INSERT INTO `t1` VALUES (43, 22, 11, '1');
执行SQL:
EXPLAIN SELECT * from t1 ORDER BY c LIMIT 1
因为c列上有索引,那么order by c 应该可以利用索引的有序性,避免filesort
但是实际结果是:
全表扫描,而且Using filesort
为什么索引c没有用呢?
这是因为索引c是前缀索引。。。索引只取了前19位,这回导致优化器认为索引c是无序的。。。。
去掉截断后,就正常了:
另外:
ORDER BY c desc 也不影响使用索引排序