索引(Innodb)
目前为止最好理解的一段话:索引就像一本书的目录。而当用户通过索引查找数据时,就好比用户通过目录查询某章节的某个知识点。这样就帮助用户有效地提高了查找速度。所以,使用索引可以有效地提高数据库系统的整体性能。
非聚簇索引
- 非聚簇索引包含复合索引、唯一索引、前缀索引,其都是基于B+树的。
聚簇索引
-
聚簇索引是没有SQL可以单独生成的,其按照每张表的主键来构建一个B+树,叶子节点存放的是整张表的行数据,并且表里只能按照一颗B+树进行排序,所以一张表只能有一个聚簇索引。
-
在Innodb中,聚簇索引默认就是主键索引
-
如果表里没主键时:
- 数据库会默认生成一个唯一不为空的索引列作为主键,成为此表的聚簇索引;
- 如果没有这个索引的话,InnoDB会隐式生成一个主键来作为聚簇索引;
自增主键和uuid作为主键的区别
由于主键使用的是聚簇索引,如果主键为自增id的话,其数据也是对应相邻的存放到磁盘上的,这种方式不用频繁移动磁盘,写入性能高;如果是uuid的形式的话,频繁的插入会频繁的移动磁盘,写入性能较低。
聚簇索引图示
如图所示,上半部分是基于主键形成的B+树,下半部分是存储在磁盘中的真实数据,
当执行 select * from table where id = 11
时
由图可知在执行查询的时候,从根节点开始共经历了3次查询即可找到真实数据。倘若没有聚簇索引的话,就需要在磁盘上进行逐个扫描,直至找到数据为止。显然,索引会加快查询速度,但是在写入数据的时候,由于需要维护这颗B+树,因此在写入过程中性能也会下降。
非聚簇索引图示
首先按照name创建非聚簇索引
从图中可以观察到,创建的非聚簇索引,其在聚簇索引基础上重新生成了一颗B+树。因此,每新增一个索引,其表的体积就会增加,占用的磁盘空间更大。非聚簇索引其叶子节点并非直接是真实数据,其叶子节点依旧是索引节点,其存放的值是创建非聚簇索引的字段和聚簇索引的主键(主键索引)
倘若执行SQL select * from table where name=‘lisi’
由图可知流程,首先从非聚簇索引开始寻找聚簇索引,找到非聚簇索引上的聚簇索引后,就会到聚簇索引的B+树上进行查询,通过聚簇索引B+树找到完整的数据。
什么情况下非聚簇索引不会去聚簇索引处进行查询
select name from table where name = ‘lisi’;
此时结构图如下
此时就是,在非聚簇索引上找到了想要的值,就不会再去聚簇索引的B+树上查找值了。
当执行select col from table where col = ?,col上有索引的时候,效率比执行select * from table where col = ? 速度快好几倍!
创建多个索引
执行sql create index index_birthday on table(birthday);
此时结构图如下
可见此时我们又多了一颗非聚簇索引树,倘若新增多个索引,那么我们就有多个非聚簇索引树!此时倘若进行频繁的插入操作的话,维护这颗B+树的性能消耗是十分大的,会影响到插入的性能。
索引失效的几种情况
- 使用模糊查询,like '%XXX’的时候;只要%在前,索引就会失效,但是%在后,索引不会失效。
- 使用or条件的时候,左右的字段倘若有一个不为索引字段,此时索引也会失效。倘若or左右两个字段都为索引字段,仍有效。
- 如果索引字段是varchar类型,则一定要在查询的时候用【’ '】围起来,否则对于integer类型的话容易识别为整数型,此时不会使用索引的。