03.mysql调优--索引基本实现

组合索引：

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

---

覆盖索引

不必读取整个行，索引叶子节点​​​​​​​​​​​​​​存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。