mysql中关于索引的测试

准备环境和测试案例

drop TABLE t1
create table t1(
a int ,
b int,
c int,
d int,
e VARCHAR(255),
PRIMARY key(a)
) ENGINE = INNODB;


insert t1 values(1,1,1,1,"a");
insert t1 values(4,3,1,1,"d");
insert t1 values(2,2,2,2,"b");

insert t1 values(7,4,5,5,"g");
insert t1 values(5,2,3,5,"e");
insert t1 values(6,6,4,4,"f");
insert t1 values(3,3,2,2,"c");
insert t1 values(8,8,8,8,"h");

准备测试

show index from t1

SELECT * from t1;

测试主键索引

EXPLAIN select b from t1 where a = 4;

测试联合索引

查询索引，创建索引，删除索引
show index from t1
create index index_b_c_d ON t1(b,c,d);
drop index index_b_c_d on t1

EXPLAIN select b,c,d from t1 where b = 1 ;
EXPLAIN select b,c,d from t1 where b = 1 and c = 1 ;
EXPLAIN select b,c,d from t1 where b = 1 and c = 1 and d = 1;
以上满足最左匹配原则

EXPLAIN select a,c,d from t1 where c = 1 and d = 1;

EXPLAIN select b,c,d from t1 where d = 1 and c = 1 ;

以上测试走索引，由于返回的列，a,c 是索引，d列是查询的条件，可以不用回表，所有查询走索引更快

但是当返回的列不在索引中，就会优化，不走索引，全表查询速度更快

范围查询，也可以用到索引，大于小于都可以

EXPLAIN select b,c,d from t1 where b < 2 ;
EXPLAIN select b,c,d from t1 where b < 10 and c > 2;
EXPLAIN select a,b,c,d from t1 where b < 10 and c > 2;

以上范围查询也是可以总索引的

使用 or 的话，肯定不会使用索引的

EXPLAIN select * from t1 where b = 1 and c = 2 or d = 1;

其中关于 EXPLAIN 中的含义，还请自行百度