学习内容:无
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位',
add_time TIMESTAMP not null default CURRENT_TIMESTAMP comment '入职时间'
)charset utf8 comment '员工记录表';
添加组合索引:alter table staffs add index idx_nap(name,age,pos);
查看索引:show index from staffs;
索引类型:
- 全值匹配
explain select * from staffs where name = 'jarry' and age = 23 and pos = 'dev';
- 最左匹配,只匹配前面的几列:
explain select * from staffs where name = 'jarry' and age = 23;
explain select * from staffs where name = 'jarry';
- 匹配列全缀:可以匹配某一列值的开头部分
explain select * from staffs where name like 'j%';
注意:这两种情况没有使用到索引:
explain select * from staffs where name like '%jarry%';
explain select * from staffs where name like '%jarry';
- 匹配范围值
explain select * from staffs where name > 'jarry';
- 精确匹配某一列并范围匹配某一列
可以查询第一列的全部和第二列的一部分
explain select * from staffs where name ='jarry' and age >'25';