MySQL索引最左匹配原则

原创转载请注明出处：http://agilestyle.iteye.com/blog/2433805

 

创建一个测试表

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(32) NOT NULL,
  `b` varchar(32) NOT NULL,
  `c` varchar(64) NOT NULL,
  `d` varchar(128) NOT NULL,
  `e` varchar(256) NOT NULL,
  `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),  
  `update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

插入数据

INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');

 

查看索引

show index from test;

 

查看执行计划

explain select * from test where id = "1";

 

explain select * from test where a = "1";

 

explain select * from test where b = "1";

 

explain select * from test where a = "1" and b = "1";

 

创建索引（a和b的联合索引）

create index idx_a_b on test(a, b);

 

查看索引

show index from test;

 

查看执行计划

explain select * from test where a = "1";

 

explain select * from test where b = "1";

 

explain select * from test where a = "1" and b = "1";

 

创建索引（b的索引）

create index idx_b on test(b);

查看索引

show index from test;

 

查看执行计划

explain select * from test where a = "1";

 

explain select * from test where b = "1";

 

explain select * from test where a = "1" and b = "1";

 

删除索引

drop index idx_a_b on test;
drop index idx_b on test;

 

删除索引后，创建a, b, c 3个字段的联合索引

create index idx_a_b_c on test(a, b, c);

查看如下语句的执行计划

-- ref
explain select * from test where a = "1";
-- all 扫全表
explain select * from test where b = "1";
-- all 扫全表
explain select * from test where c = "1";
-- ref
explain select * from test where a = "1" and b = "1" and c = "1";
-- ref
explain select * from test where a = "1" and b = "1";
-- ref
explain select * from test where a = "1" and c = "1"
-- all 扫全表
explain select * from test where b = "1" and c = "1"