今天遇到这个问题,之前没有仔细想过这个问题,记录一下。
先创建一张表
create table mytable (
col1 int primary key,
col2 int not null,
col3 int not null,
col4 int not null,
col5 int not null,
col6 int not null
);
添加组合索引
ALTER TABLE mytable ADD INDEX index_name(col1,col2,col3);
插入数据
INSERT INTO cloud.mytable (col1,col2,col3,col4,col5,col6) VALUES
(1,1,1,1,1,1),
(2,1,1,1,1,1),
(3,1,1,1,1,1),
(4,1,1,1,1,1),
(5,1,1,1,1,1),
(6,1,1,1,1,1),
(7,1,1,1,1,1),
(8,1,1,1,1,1),
(9,1,1,1,1,1),
(10,1,1,1,1,1);
INSERT INTO cloud.mytable (col1,col2,col3,col4,col5,col6) VALUES
(11,1,1,1,1,1);
explain select * from mytable where col1=1;// 命中索引
explain select * from mytable where col2=1;// 未命中索引
explain select * from mytable where col3=1;// 未命中索引
explain select * from mytable where col1=1 and col2 = 1;// 命中索引
explain select * from mytable where col2=1 and col1 = 1;// 命中索引
explain select * from mytable where col1=1 and col3 = 1;// 命中索引
explain select * from mytable where col3=1 and col1 = 1;// 命中索引
explain select * from mytable where col2=1 and col3 = 1;// 未命中索引
explain select * from mytable where col3=1 and col2 = 1;// 未命中索引
explain select * from mytable where col1=1 and col2 = 1;// 命中索引
explain select * from mytable where col1=1 and col2 = 1 and col3 =1;// 命中索引
explain select * from mytable where col1=2 and col3 = 1 and col1 =1;// 未命中索引
explain select * from mytable where col1=3 and col2 = 1 and col1 =1;// 未命中索引
explain select * from mytable where col1=1;// 命中索引
explain select * from mytable where col1=1 and col4=1;// 命中索引
explain select * from mytable where col2=1;// 未命中索引
explain select * from mytable where col3=1;// 未命中索引
explain select * from mytable where col1=1 or col2=1;// 未命中索引
explain select * from mytable where col2=1 or col1=1;// 未命中索引
explain select * from mytable where col1=1 or col2=1 or col3=1;// 未命中索引
但是请注意在对主键和int类型的索引使用比较类型的则可以出发索引;
使用联合索引的,但是在索引列使用比较、计算的不可触发索引的使用
explain select * from mytable where col1 > 0;// 命中索引
explain select * from mytable where col1 + 1 > 1;// 未命中索引
explain select * from mytable where col1 > 1 + 1;// 命中索引
explain select * from mytable where col1 like "%1"; //不可命中索引
explain select * from mytable where col1 regexp "^%1"; //不可命中索引
explain select * from mytable where col1 like "1%"; //可以命中索引
参考链接:https://www.jianshu.com/p/af6075c5e9fb
Copyright: 采用 知识共享署名4.0 国际许可协议进行许可 Links: https://lixj.fun/archives/mysql组合索引不被命中使用的情况