【SQL基础_6】索引优化

一列的索引称为单列索引，多列的称为复合索引，因为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);

1. 全值匹配

如select * from staffs where name = 'July' and age = '23' and pos = 'dev' 

2. 匹配最左列

对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列。

如select * from staffs where name = ‘July’ and age = ‘23’，key字段显示用到了索引，注意，key_len字段（表示本次语句使用的索引长度）数值比上一条小了，意思是它并未使用全部索引列，事实上只用到了name和age列

3. 匹配列前缀

即一个索引中列的前一部分，主要用在模糊匹配，如select * fromstaffs where name like ‘J%’，explain信息的key字段表示使用了索引，但是mysql的B树索引不能非列前缀的模糊匹配，如select * from staffs where name like ‘%y’ 或者 like ‘%u%’，据说是由于底层存储引擎的API限制

4. 匹配范围

如select * from staffs where name > ‘Mary’，但俺在测试时发现>可以，>=却不行，至少在字符串列上不行（测试mysql版本5.5.12），然而在时间类型（timestamp）上却可以，不测试下还真不能确定说就用到了索引==

5. 精确匹配一列并范围匹配右侧相邻列

即前一列是固定值，后一列是范围值，它用了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