Mysql索引和底层实现

索引是什么?

帮助mysql高效获取数据的排好序的数据结构.(可以当做一本书的页码)
一般情况下都是高效.但是由于索引是以空间换时间的策略,索引本身在提高查询效率的同时会影响插入,更新,删除的效率,所以频繁写不建议使用索引.
选择在where,group by,order by,on从句中出现的列作为索引项,离散度不大的列没需要创建索引.
主键已经是索引了,使用primay key的主键不再设置unique唯一索引

索引的类型

主键索引 primay key
唯一索引 unique
普通索引 index
组合索引 index
全文索引 fulltext

可以应用的索引操作符

大于等于
Between
in
Like不以%_开头

不能应用的索引操作符

NOT IN
Like以%_开头

如何选择索引字段

字段出现在查询条件中,并且查询条件可以使用索引
数字的索引和检索要比字符串的索引效率要高
语句执行率高,一条几千次
通过字段查询可筛选的记录集很小

索引的数据结构:

为什么mysql数据结构不用其他树用B+Tree树?

例如我们的索引如下:

树.png

: $\color{red}{二叉树}$ :当我们的索引出现单边增长的话,就会形成一个链表结构,此时我们去查询6号索引的话需要查询6次,很慢,所以不合适

树.png
: $\color{red}{红黑树}$ :
如果出现单边增长的情况红黑树会自动自旋平衡(平衡二叉树)
为什么不适合做数据库的数据结构,因为数据如果太多了比如一百万条,它是无法掌控自己的高度的,如果数据在叶子节点,我们查询重根节点开始查,可想而知.

红黑树.png
: $\color{red}{hash表}$ :
hash表的性能也非常高为什么不用?
无法进行范围查询.
: $\color{red}{B+Tree树}$ :这就是mysql底层用的数据结构.官方定义根节点大小为16k,共可以存储2千多万的数据.

B+树.png

myisam数据库存储引擎实现

in.png

非聚合 :数据和索引是分离的通过指针找到对应的地址找到对应的表数据字段

MylSAM存储引擎索引存储原理.png

inndob数据库存储引擎实现

frm文件存储的是: 表结构定义的数据

ibd文件存储的是: 索引数据+主键对应的表字段

in.png

常见面试问题:

什么是聚集索引?
LnnoDB的主键索引就是一个聚集索引(索引和主键对应的数据放在同一个B+树)
为什么LnnoDB表必须有主键?
如果没有主键索引会默认挑选一个适合字段作为索引,mysql的设计师来设置时,就是维护表数据时必须要有这个B+树来维护他,如果没有B+树数据根本没有东西来组织了.主键索引用来维护所有数据,为了数据查找快
为什么最好用整形自增作为主键
比较大小快,如果用uuid字符串比较太慢了.
为什么要选自增?

如果使用字符串uuid的话万一主键是整个索引的中间的话会造成再次分裂

图片.png

如果使用自增的话就可以一直往后面排因为左边总是比右边小,这样性能消耗就比较小.

MyISAM和Innodb区别

InnoDB和MyISAM是许多人在使用MySQL时常用的两个存储引擎，这两个存储引擎各有优劣，视具体应用而定。
基本的差别为：MyISAM类型不支持事务处理，而InnoDB类型支持。MyISAM类型强调的是性能，其执行速度比InnoDB类型更快，而InnoDB提供事务支持已经外部键等高级数据库功能。

具体实现的差别：

MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。 MyISAM不支持外键，而InnoDB支持外键
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考- 虑使用MyISAM。 InnoDB表比MyISAM表更安全。