一列的索引称为单列索引,多列的称为复合索引,因为BTREE索引是顺序排列的,所以比较适合范围查询,但是在复合索引中,还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。
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);
- 全值匹配
如select * from staffs where name = 'July' and age = '23' and pos = 'dev'
- 匹配最左列
对于复合索引来说,不总是匹配所有字段列,但是可以匹配索引中靠左的列。
如select * from staffs where name = ‘July’ and age = ‘23’,key字段显示用到了索引,注意,key_len字段(表示本次语句使用的索引长度)数值比上一条小了,意思是它并未使用全部索引列,事实上只用到了name和age列
- 匹配列前缀
即一个索引中列的前一部分,主要用在模糊匹配,如select * fromstaffs where name like ‘J%’,explain信息的key字段表示使用了索引,但是mysql的B树索引不能非列前缀的模糊匹配,如select * from staffs where name like ‘%y’ 或者 like ‘%u%’,据说是由于底层存储引擎的API限制
- 匹配范围
如select * from staffs where name > ‘Mary’,但俺在测试时发现>可以,>=却不行,至少在字符串列上不行(测试mysql版本5.5.12),然而在时间类型(timestamp)上却可以,不测试下还真不能确定说就用到了索引==
- 精确匹配一列并范围匹配右侧相邻列
即前一列是固定值,后一列是范围值,它用了name与age两个列的索引(key_len推测)
如select * from staffs where name = ‘July’ and age > 25
.6 只访问索引的查询
比如staffs表的情况,索引建立在(name,age,pos)上面,前面一直是读取的全部列,如果我们用到了哪些列的索引,查询时也只查这些列的数据,就是只访问索引的查询,如
select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’
select name,age from staffs where name = July and age > 25