组合索引使用‘like’失效问题
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`a` varchar(254) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name`(`name`, `a`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
我不明白为什么会出现那个using where
explain select name, a from a where name='5' and a='5';
explain select name, a from a where name='5' and a in('5');
explain select name, a from a where name='5' and a like '5%';
多表连接索引不是预想结果
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`a` varchar(254) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name`(`a`, `name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `b` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`a` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `pid`(`a`, `name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
explain select count(*) from a where a.a='6' and a.name in (select b.name from b where b.a='5');
个人觉得这个sql应该先通过组合索引获取到name的值,然后a表通过组合索引得到总数,结果和我预想的不一样,在几千万数据上,我个人尝试分成两个sql效率还有所提升!!!
explain select count(*) from a where a like '6%';
这个sql也应该从索引里面查找6开头的,然后统计数量啊?为什么又有using where 啊
希望大家解答讨论!!!