专栏继续更新中:MySQL详解

咱们这儿讨论InnoDB存储引擎,数据和索引存储在同一个文件student.ibd

InnoDB主键索引树和二级索引树

场景1:主键索引树

uid是主键,其他字段没有增加任何索引

InnoDB主键索引树和二级索引树

select * from student;

假如是上面这样查询,这表明整表查找,从左到右遍历叶子节点链表,从小到大拜访

select * from student where uid<5;

假如是上面这样查询,这表明规模查询,就直接在有序链表中遍历查找就能够了,直到遍历到第一个不小于5的key完毕遍历

select * from student where uid=5;

假如是上面这样查询,这表明等值查询,在索引树上进行二分查找即可

InnoDB主键索引树和二级索引树

由于name没有索引,于是做整表查找

select * from student where name='linfeng';

InnoDB主键索引树和二级索引树

场景2:二级索引树

uid是主键,以name创立了普通索引(二级索引)

以name为索引构建的索引树,称为辅佐索引树,也叫做二级索引树。key是辅佐索引字段name的值,然后还有外加uid主键的值

InnoDB主键索引树和二级索引树

在辅佐索引树上,key是辅佐索引的值,也便是name;data数据值是地点记录行的主键值(PRIMARY KEY),也便是uid(并不是表的一行数据),一行表数据只在主键索引树上存在

剖析句子1:

select name from student where name='linfeng';

由于过滤字段是name且只select了name一个字段,name有索引,索引树上直接就有,所以从name的二级索引树上去等值匹配linfeng

剖析句子2:

select uid,name from student where name='linfeng';

InnoDB主键索引树和二级索引树

这种状况select的是name和uid,而这些在二级索引树上也是直接就有,所以查找二级索引树就完事了。

剖析句子3:

select * from student where name='linfeng';

这种状况下就涉及到回表了,这是一个很重要的概念。由于name字段有索引,所以咱们会到name字段构建的二级索引树上去查找。但二级索引树没有linfeng这个人一切的信息,所以完好的查询过程应该是这样的:

  1. 用linfeng到二级索引树上进行匹配,拿到二级索引树上存储的uid
  2. 然后拿着这个uid去主索引树上去匹配,最后拿到linfeng的一切信息(回表

InnoDB主键索引树和二级索引树

而这个回表意味着更多的磁盘I/O,会影响功率,假如业务只需求uid、name,就别写select *了,这样能够避免回表(在二级索引树上查到主键,再去主键索引树上查找)

剖析句子4:

咱们删去name的索引后履行以下句子

select * from student where age=20 order by name;

InnoDB主键索引树和二级索引树

没有用到索引,还运用外部排序了。此外咱们还看到using filesort,这时需求优化了。

咱们的过滤条件是age,先给age增加索引,看看行不行

InnoDB主键索引树和二级索引树

能够看到,age射中索引了,查询age地点的索引树。由于咱们写的是select *,仍然存在回表。还有using filesort,由于运用age=20查询到的成果是多个,但是name此时是没有次序的,所以还需求再进行外部排序。

那能不能通过给name加载索引来解决问题呢?

不能,由于一次SQL履行只能用到1个索引,查找了这个字段的索引树就不会再去查找另一个字段的索引树了,由于加载索引是要耗费磁盘I/O的,查找多个索引树就太慢了!

剖析:已然索引树上只能存自己树立的索引字段以及主键,那咱们把需求查询的字段都设置成索引不就好了?

解决方法:咱们能够在二级索引树上的key:age+name,形成联合索引,先按age排序,age相同了,再按name排序

InnoDB主键索引树和二级索引树

再次select *

InnoDB主键索引树和二级索引树

这时候就运用到联合索引了,并且没有using filesort,这次是这样查询的:

先用age=20在辅佐索引树上查找,假如数据足够会找到多个成果,这个成果便是现已排好序的,不需求再using filesort

咱们现在直接用第二个字段name作为过滤条件

InnoDB主键索引树和二级索引树

咱们看到这儿没有用到索引,由于咱们用(age,name)创立索引,是先按age排序,再按name排序。假如咱们只用name作为过滤条件,这就没有办法运用索引匹配了,由于是优先用age排序。

所以咱们经常说:多列索引一定要运用到第1个字段,这样才干用到索引!

InnoDB主键索引树和二级索引树

在树立(age,name)联合索引的状况下,以下操作不回表(到二级索引树上查找,再去主索引树上查找):

  • select age
  • select age, name
  • select uid,age,name

以下操作要回表

  • select *
  • select age,name,sex