1.B和B+树有何异同?
(1)B树的所有节点存放key和data域,而B+树只有叶子节点存放key和data域,非叶子节点只存放key。
(2)B树的叶子节点都是独立的,而B+树的叶子节点有一条引用链,指向相邻的yezijiedian(3)B树的见过过程相当于对范围内的值二分查找,可能没走到叶子节点就找到了值,而B+树比较稳定,每次都要查找到叶子节点,叶子节点的顺序检索也很明显。比较适合顺序检索和范围检索。
2.Mysql的MyISAM和InnoDB
- mysql的MyISAM引擎使用B+树作为索引结构,叶子节点的data域存放的是数据记录的地址,检索的时候需要找到地址,然后再去数据表中获取数据记录。
- InnoDB引擎中,数据文件本身就是索引文件,其表数据结构本身就是按照B+树组成的一个索引结构,叶子节点的data域保持了完成的数据记录。这个索引的key就是表的主键,因此也是主索引。而数据文件本身就是索引文件,因此也是聚簇索引。
- 其他的索引都成为辅助索引,辅助索引的叶子节点的data域存放的是数据记录的主键id,使用辅助索引查找数据的时候,需要根据辅助索引查找到数据记录的主键,然后再去主索引中查找到数据记录。
3.索引类型
主键索引(Primary Key)
数据表的主键列使用的就是主键索引。
二级索引(辅助索引)
二级索引又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。
PS:不懂的同学可以暂存疑,慢慢往下看,后面会有答案的,也可以自行搜索。
唯一索引(Unique Key) :唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
普通索引(Index) :普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
前缀索引(Prefix) :前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小, 因为只取前几个字符。
全文索引(Full Text) :全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引,5.6 之后 InnoDB 也支持了全文索引。
4.聚集索引和非聚集索引
聚集索引就是数据文件和索引结构存放在一起的索引。因此,聚集索引只有一个,Mysql的InnoDB就采用了聚集索引。
优点:查询速度快,叶子节点有序,叶子节点本身存放了数据记录,不需要二次查询表文件。
缺点:依赖于有序数据、更新代价比较大,如果索引列的数据被修改了,叶子使用一条引用表示有序性的,这样就需要调整数据的位置,而叶子节点还存放着data域数据记录,因此更新代价比较大。不过,对于主索引来说,一般主键是不会修改的,
非聚集索引
数据文件和索引文件分开存放的索引。MyISAM的索引和InnoDB引擎的二级索引都是非聚集索引。
优点:更新代价较小
缺点:依赖有序数据、需要二次查询。
5.覆盖索引
非聚集索引不一定回表查询
要查询的字段就是索引字段,那么直接根据该索引就可以查找到数据了,无需回表查询。
6.创建索引的注意事项⭐
- 选择合适的字段创建索引:
- 不为 NULL 的字段 :索引字段的数据应该尽量不为 NULL,因为对于数据为 NULL
的字段,数据库较难优化。如果字段频繁被查询,但又避免不了为 NULL,建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。 - 被频繁查询的字段:我们创建索引的字段应该是查询操作非常频繁的字段。
- 被作为条件查询的字段 :被作为 WHERE 条件查询的字段,应该被考虑建立索引。
- 频繁需要排序的字段 :索引已经排序,这样查询可以利用索引的排序,加快排序查询时间。
- 被经常频繁用于连接的字段
:经常用于连接的字段可能是一些外键列,对于外键列并不一定要建立外键,只是说该列涉及到表与表的关系。对于频繁被连接查询的字段,可以考虑建立索引,提高多表连接查询的效率。
- 不为 NULL 的字段 :索引字段的数据应该尽量不为 NULL,因为对于数据为 NULL
2.被频繁更新的字段应该慎重建立索引。
虽然索引能带来查询上的效率,但是维护索引的成本也是不小的。 如果一个字段不被经常查询,反而被经常修改,那么就更不应该在这种字段上建立索引了。
3.尽可能的考虑建立联合索引而不是单列索引。
因为索引是需要占用磁盘空间的,可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多,索引过多,那么当这个表的数据达到一个体量后,索引占用的空间也是很多的,且修改索引时,耗费的时间也是较多的。如果是联合索引,多个字段在一个索引上,那么将会节约很大磁盘空间,且修改数据的操作效率也会提升。
4.注意避免冗余索引 。
冗余索引指的是索引的功能相同,能够命中索引(a, b)就肯定能命中索引(a) ,那么索引(a)就是冗余索引。如(name,city )和(name )这两个索引就是冗余索引,能够命中前者的查询肯定是能够命中后者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引。
5.考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限于字符串类型,较普通索引会占用更小的空间,所以可以考虑使用前缀索引带替普通索引。