组合索引:
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';
以下这句也会用到索引,因为mysql对索引进行了优化,即使顺序反了,也会用到
explain select * from staffs where name = 'jarry' and pos = 'dev' and age = 23 ;
- 最左匹配
只匹配前面的几列:
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';
- 只访问索引的查询
查询的时候只访问索引,不需要访问数据行,本质上就是索引覆盖
explain select name,age,pos from staffs where name = 'jarry' and age = 23 and pos = 'dev';+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_nap | idx_nap | 140 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
Extra = Using index,表明用到了索引覆盖
组合索引的案例
建立组合索引(a,b,c),不同sql语句使用索引情况
语句 | 索引是否发挥作用 |
---|---|
where a=3 | 使用到了a |
where a=3 and b=4 | 使用到了a,b |
where a=3 and b=4 and c=5 | 使用到了a,b,c |
where b=3 or where c=4 | 两个语句都没有使用到索引 |
where a=3 and b>4 and c=5 | 使用到了a,b,c没有用到的原因是,索引必须是精确值,b使用到了范围查找,范围查找,后面失效 |
where a=3 and b like ‘%4%' and c=5 | 只使用到了a |
覆盖索引
不必读取整个行,索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。