在我们创建表的时候,会生成一个B+tree 数据结构,用以承载我们不断新增的数据。
聚簇索引示意图:
目录页记录下一级所有页的主键最大值,而数据页会在最底层形成一个双向链表。这样就保证在对主键精确查找与范围查找时,会快速的查找到结果集。
执行过程:where id>=1 and id <=9
1、解析:MySQL解析器会将sql的查询条件解析成一个区间 id in [1,9]。
2、根据区间最小值 id=1条件,查询页码9的数据9,18。其中1<9,所以继续查找页码4的数据 3、6、9。1<3,所以定位到数据页 1。最大值9同理会定位到数据页3。
3、由于数据页时双向链表结构,所以很容易获取出[1,9]区间的数据
二级索引:
二级索引中目录页与聚簇索引基本保持一直,由页码与索引列组成。数据页由页码、索引列加主键组成。
执行过程:where name=a;
1、解析:MYSQL解析器会将sql的查询条件解析为 name=[a];
2、根据name=a;条件,查询页码4 ,a<c 所以查询页码1的数据
3、在页码1查询到 索引为a的数据,得到主键。
4、回表:将获取到的主键值 在聚簇索引查询。注:如果查询的字段仅为索引列于主键,是不用回表的
联合索引:
将多个字段联合一起创建索引,结构与二级索引类似,唯一变化就是在 目录页与数据页中的索引字段由一个变成多个。
**选择什么样的字段创建索引:**
我觉得这是一个开放性的话题,不过话题永远会落到 “热数据”、“重复率”等等,却总是感觉不清晰。
最适合创建索引的字段就是主键,但是主键出生就有索引,不用我们去创建。但是可以依照主键的特性找到创建索引的条件。
1、访问率高,如果主键的访问率不高那只能说它不是主键
2、重复率低:主键是重复率低的极致表现,已经存在唯一性
3、容易排序:创建索引时,是需要索引字段进行排序。当然进入数据库的数据都可排序,但是像主键自增的数字还是最直接、快捷的。
4、占用存储空间小:如果对text文本进行索引,它的存储量也许就能达到或者超过1M,但是正常数据交互量才16K(页:一般默认情况下),这样会造成多个页来存储一个字段,而且还在目录页、数据页冗余式储存。不提浪费存储空间,IO消费就能让这个索引失去价值。
索引带来的坏影响:
1、消耗大量储存空间,多创建一个索引就等于多一份数据冗余。空间消耗难免的
2、写入的性能变差:在进行新增、修改、删除操作时,都会伴随着数据页、目录页的变化。聚簇改变之后还要同步其他的索引树,性能肯定也有影响
3、查询性能不一定有提升,有”回表“的可能性,相当于遍历了两颗索引树。还有其他情况:例如sql语句原因无法使索引产生作用。。。