数据库表字段索引的建立对于数据检索的查询效率提升至关重要。
本文主要讲解联合索引建立时索引命中场景。
1、创建表
create table 't_user_role'(
id bigint(20) auto_increament,
user_id bigint(20) not null,
role_id bigint(20) not null,
createTime datetime,
primary key(id),
key 'idx_user_role_id'('user_id','role_id') use btree
)ENGINE=innoDB COMMENT='用户角色表';
2、查看表索引信息
show index from t_user_role;
3、SQL执行计划
场景一:
explain select * from t_user_role where user_id=44 order by createTime desc limit 10;
场景二:
explain select * from t_user_role where role_id=44 order by createTime desc limit 10;
场景三:
explain select * from t_user_role where role_id=33 and user_id=44 order by createTime desc limit 10;
场景四:
explain select * from t_user_role where user_id=33 and role_id=44 order by createTime desc limit 10;
场景五:
explain select * from t_user_role order by user_id desc limit 10;
场景二和场景五并不会命中索引