Mysql之索引优化(索引的分类、回表、覆盖索引、键表SQL、口诀、练习)

索引的分类

分类角度	索引名称
数据结构	B+树，Hash索引，B-Tree等
存储层面	聚簇索引，非聚簇索引
逻辑层面	主键索引，普通索引，复合索引(MySQL 组合索引和联合索引和复合索引都是一个东西)，唯一索引，空间索引等

回表

假设我们执行一条查询语句
select * from person where ID = 6,因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。

如果执行的sql语句是非主键索引

select * from person where age = 18

上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。

从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。

覆盖索引

我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做"覆盖索引"

键表SQL

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default "",
    age int not null default 0,
    pos varchar(20) not null default "",
    add_time timestamp not null default CURRENT_TIMESTAMP 
)charset utf8;

create table user(
    id int not null auto_increment primary key,
    name varchar(20) default null,
    age int default null,
    email varchar(20) default null
) engine=innodb default charset=utf8;

插入数据

insert into staffs(`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('July',23,'dev',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('2000',23,'dev',now());

insert into user(name,age,email) values('1aa1',21,'b@163.com');
insert into user(name,age,email) values('2aa2',22,'a@163.com');
insert into user(name,age,email) values('3aa3',23,'c@163.com');
insert into user(name,age,email) values('4aa4',25,'d@163.com');

建立复合索引

create index idx_staffs_nameAgePos on staffs(name,age,pos);

口诀

全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用
varchar引号不可丢,SQL高级也不难

练习

假设index(a,b,c)

where语句	索引是否被使用到
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a,b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或 where b = 3 and c = 4 或 where c = 4	N
where a = 3 and c = 5	使用到a,c没有被使用,b中间断了
where a = 3 and b > 4 and c = 5	使用到了a,b
where a = 3 and b like ‘kk%’ and c = 4	使用到了a,b,c
where a = 3 and b like ‘%kk’ and c = 4	使用到了a
where a = 3 and b like ‘%kk%’ and c = 4	使用到了a