索引就是排好序的数据结构
MyISAM存储引擎
表数据存储在MYD文件中,索引存在MYI文件中
MyISAM叶子节点存储的是 key+地址(这个地址是磁盘文件的地址)
Innodb存储引擎
底层是使用B+树存储的,叶子节点存储key+数据 图中展示的是主键索引
普通索引是
B树和B+树的区别
1.B树中叶子节点没有双向指针。
2.B+树把数据都存储在叶子节点,叶子节点存储索引和数据,非叶子节点只存储索引
B数的节点都存储的key+数据
为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
1.在InnoDB中,如果没有主键,则会挑选一个唯一键来建立聚簇索引。如果唯一键也没有,数据库就会帮我们生成一个键来建立聚簇索引。所以我们最好是自己建立主键,不要让数据库多处额外的工作,降低数据库引擎的性能。
2.推荐使用整型自增主键的原因主要是 相比于使用UUID作为主键,整型的数据占用的空间小,而且数据库在进行查找数据,比较数据的时候能够更快的比较key,整型的数据更容易比较大小,而UUID是字符串,还要转换成ASCII码再比较大小,耗费数据库性能。所以还是推荐整型的自增主键。
为什么MySQL数据库使用的是B+树存储索引?而不是使用红黑树、Hash、B树?
1.红黑树:如果在内存中,红黑树的查找效率比B树更高,但是涉及到磁盘操作,B树就更好一点。因为红黑树是二叉树,数据量比较大时,树的层数会变的很高,从树的根节点向下查找的过程中,每次读一个节点,都相当于一次IO操作,因此红黑树的IO操作会比B树多很多,这就是不使用红黑树的原因。
2.Hash索引:
hash索引底层其实就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获取到实际数据。
B+树索引底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发,查找到叶子节点就可以获得所查键值。
hash索引查找单个值的情况,hash索引的效率会非常高。但是hash索引有几个非常致命的问题:1)不支持范围查询;2)不支持索引值的排序操作;3)不支持联合索引的最左匹配原则。4)hash冲突问题。
3.B树索引:
B树索引相比于B+树,在进行范围查询时,需要做局部的中序遍历,可能要跨层访问,跨层访问就代表着要进行额外的磁盘IO操作;另外,B树的非叶子节点存放了数据记录的地址,会导致存放的节点更少,树的层数变高。
4.B+树
a).B树只适合做随机检索,而B+树同时支持随机检索和顺序检索
b).B+树空间利用率更高,可以减少IO次数,磁盘读写代价更低。一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。这样的话,索引查找过程中就要产生磁盘IO消耗。B+树内部节点并没有指向关键字具体信息的指针,只是作为索引使用,其内部节点比B树小,盘块能容纳的节点中关键字数量更多,一次性读入内存中可以查找的关键字也就更多,相对的,IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素。
c).增删文件(节点)时,效率更高。因为B+树的叶子节点包含所有的关键字,并以有序的链表结构存储,这样能够很好的提高增删效率。
叶子节点维护了一个双向指针(有一小块存储空间的相邻的磁盘地址),叶子节点都是排好序的
什么是聚集索引或者聚簇索引?
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇索引将索引和数据行放到了一块,找到索引的同时也找到了数据。因为无需进行回表操作,所以效率很高。
在InnoDB中必然会有且只有一个聚簇索引。通常是主键,如果没有主键,则优先选择非空的唯一索引,如果唯一索引也没有,则会创建一个隐藏的row_id作为聚簇索引。至于为啥只有一个聚簇索引,其实很简单,因为我们的数据只有一份。
而非聚簇索引则将数据存储和索引分开,当访问数据时,在内存中直接搜索索引,找到索引后,需要通过对应的地址在磁盘中找到对应的数据行。MyISAM的索引方式就是非聚簇索引。
什么是回表查询?
InnoDB 中,对于主键索引,只需要走一遍主键索引的查询就能在叶子节点拿到数据。
而对于普通索引,叶子节点存储的是 key + 主键值,因此需要再走一次主键索引,通过主键索引找到行记录,这就是所谓的回表查询,先定位主键值,再定位行记录。
联合索引(复合索引)的底层实现?最佳左前缀法则?
联合索引底层还是使用B+树索引,并且还是只有一棵树,只是此时的排序会:首先按照第一个索引排序,在第一个索引相同的情况下,再按第二个索引排序,依次类推。在联合索引中,如果想要命中索引,需要按照简历索引时的字段顺序挨个使用。否则无法命中。
这也是为什么有“最佳左前缀原则”的原因,因为右边(后面)的索引都是在左边(前面)的索引排序的基础上进行排序的,如果没有左边的索引,单独看右边的索引,其实是无序的。
explain用过吗,有哪些字段,分别是什么意思?
explain字段有:
id:标识符
select_type:查询的类型
table:输出结果集的表
partitions:匹配的分区
type:表的连接类型
possible_keys:查询时,可能使用到的索引
key:实际使用的索引
key_len:使用的索引字段的长度
ref:列与索引的比较
rows:估计要检查的行数
filtered:按表条件过滤的行百分比
Extra:附加信息
type中有哪些常见的值?
按类型排序,从好到坏,常见的有:const > eq_ref > ref > range > index > ALL。
1.const:通过主键或唯一键查询,并且结果只有1行(也就是用等号查询)。因为仅有一行,所以优化器的其余部分可以将这一行中的列值视为常量。
2.eq_ref:通常出现于两表关联查询时,使用主键或者非空唯一键关联,并且查询条件不是主键或唯一键的等号查询。
3.ref:通过普通索引查询,并且使用的等号查询。
4.range:索引的范围查找(>=、<、in 等)。
5.index:全索引扫描。
6.All:全表扫描