mysql 索引匹配原则_MySQL索引最左匹配原则

创建一个测试表

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"