1在这之前,我们先来了解一下索引覆盖的知识点+回表?
索引可以分为两种类型:聚簇索引&&普通索引。对于聚簇索引:
1什么是聚簇索引?
聚簇索引是一种数据存储方结构,聚簇索引就是按照每张表的**主键(一般是)**构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据(其实是页结构,一个页包含多行数据),也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。
如果表设置了主键,则主键就是聚簇索引
如果表没有主键,则会默认第一个 NOT NULL && UNIQUE 的列作为聚簇索引
以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引
InnoDB必须要有至少一个聚簇索引。由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录。
**对于普通索引:**普通索引也叫二级索引,除聚簇索引外的索引,即非聚簇索引。InnoDB的普通索引叶子节点存储的是主键(聚簇索引)的值。
举例说明问题:
create table user(
id int(10) auto_increment,
name varchar(30),
age tinyint(4),
primary key (id),
index idx_age (age)
)engine=innodb;
insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);
索引存储结构:
id 是主键,所以是聚簇索引,其叶子节点存储的是对应行记录的数据
聚簇索引结构如下所示:
普通索引结构如下:
age 是普通索引(二级索引),非聚簇索引,其叶子节点存储的是聚簇索引的的值
如果查询条件为主键(聚簇索引),则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如:select * from user where id = 1;
如果查询条件为普通索引(非聚簇索引),需要扫描两次B+树,第一次扫描通过普通索引定位到聚簇索引的值,然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。 如:select * from user where age = 30;
1. 先通过普通索引 age=30 定位到主键值 id=1
2. 再通过聚集索引 id=1 定位到行记录数据
这就引出了回表查询:
2回表查询
先通过普通索引的值定位聚簇索引值,再通过聚簇索引的值定位行记录数据,需要扫描两次索引B+树,它的性能较扫一遍索引树更低。
我们应该避免这种情况,所以使用索引覆盖来解决这个问题。
3索引覆盖
常见的方法是:将被查询的字段,建立到联合索引里去。
1、如实现:
select id,age from user where age = 10;
explain分析:因为age是普通索引,使用到了age索引,通过一次扫描B+树即可查询到相应的结果,这样就实现了覆盖索引
2、实现:
select id,age,name from user where age = 10;
explain分析:age是普通索引,但name列不在索引树上,所以通过age索引在查询到id和age的值后,需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询。
为了实现索引覆盖,需要建组合索引idx_age_name(age,name):
drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);
explain分析:此时字段age和name是组合索引idx_age_name,查询的字段id、age、name的值刚刚都在索引树上,只需扫描一次组合索引B+树即可,这就是实现了索引覆盖,此时的Extra字段为Using index表示使用了索引覆盖。
2执行计划
MySQL可通过explain查看执行计划,通过执行计划,我主要看这几个字段:
1 type 访问类型,SQL优化的一种重要的指标,至少是要达到range的,最好是要达到ref;
2 key 实际用到的索引,若为NULL,则没有使用到索引。若使用了覆盖索引,则该索引和查询的select字段重叠;
3 Extra 包含不适合在其他列显示但又十分重要的额外信息,using filesort:说明mysql无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的位置。using temporary:建立临时表来保存中间结果,查询完成之后把临时表删除。using index:这个表示当前的查询是覆盖索引的,直接从索引中读取数据,而不用访问数据表(回表),如果同时出现using where表明索引被用来执行索引键值的查找,如果没有,表明索引被用来读取数据,而不是真的查找。using where:使用where进行条件过滤。
字段 | 理解 |
---|---|
id | select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序,id的序号会递增,id值越大优先级越高,越先被执行 |
select_type | |
type | 访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:system>const>eq_ref>ref>range>index>all一般来说,好的sql查询至少达到range级别,最好能达到ref。 |
possible_keys | 查询涉及到的字段上存在索引,则该索引将被列出,但不一定被查询实际使用 |
key | 实际使用的索引,如果为NULL,则没有使用索引。查询中如果使用了覆盖索引,则该索引仅出现在key列表中 |
key_len | 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度,在不损失精度的前提下,长度越短越好,key_len显示的值为索引字段可能使用的最大长度,即key_len为计算所得,不是实际检索出的 |
ref | 显示索引的哪一列被使用了,如果可能的话,是一个常数。 |
rows | 根据表统计信息及索引选用情况,大致估算出找到所需记录需要读取的行数 |
Extra | 包含不适合在其他列显示但又十分重要的额外信息 |
select_type | 特点 |
---|---|
simple | 简单查询,查询中不包含子查询或union |
primary | 查询中若包含子查询,则最外层查询被标记为Primary |
subquery | 子查询 |
derived | from中的子查询被标记为derived,mysql会递归执行这些子查询,把结果放在临时表中 |
union | 若第二个select出现在union之后,则被标记为union,若union包含在from语句的子查询中,外层被标记为deriveds |
union result | 从union表获取结果的select |
type | 特点 |
---|---|
system | 表只有一条记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计。 const:表示通过索引一次就找到了,const用于比较primary key或unique索引,所以很快,如果将主键置于where列表中,MYSQL就能将该查询转换为一个常量。 |
eq_ref | 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描 |
ref | 非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。 |
range | 只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般在where语句中出现了between、<、>、in等查询。 |
index | Full index scan,index与all类型区别为index只遍历索引树,这通常比all快,因为索引文件通常比数据文件要小,也就是说虽然index和all都是全表扫描,但index是从索引中读的,而all是从硬盘中读的 |