2020-11-08:在Mysql中,三个字段A、B、C的联合索引,查询条件是B、A、C,会用到索引吗?
福哥答案2020-11-08:
会走索引,原因是mysql优化器会把BAC优化成ABC。
CREATE TABLE `t_testabc2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`A` varchar(255) NOT NULL,
`B` varchar(255) NOT NULL,
`C` varchar(255) NOT NULL,
`D` varchar(255) NOT NULL DEFAULT '''''',
PRIMARY KEY (`id`),
KEY `abc2` (`A`,`B`,`C`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
EXPLAIN SELECT * FROM t_testabc2 WHERE B='a2' AND A='a2' AND C='a2' --走索引
EXPLAIN SELECT * FROM t_testabc2 WHERE B >='a1' AND B<='a2' AND A >='a1' AND A<='a2' AND C >='a1' AND C<='a2' --走索引
EXPLAIN SELECT * FROM t_testabc2 WHERE B BETWEEN 'a1' AND 'a2' AND A BETWEEN 'a1' AND 'a2' AND C BETWEEN 'a1' AND 'a2' --走索引
EXPLAIN SELECT * FROM t_testabc2 WHERE B='a1' AND A LIKE 'a%' AND C='a1' --不走索引
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。