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

• ::当我们的索引出现单边增长的话,就会形成一个链表结构,此时我们去查询6号索引的话需要查询6次,很慢,所以不合适
  树.png
• ::
  如果出现单边增长的情况 红黑树会自动自旋平衡(平衡二叉树)
  为什么不适合做数据库的数据结构,因为数据如果太多了比如一百万条,它是无法掌控自己的高度的,如果数据在叶子节点,我们查询重根节点开始查,可想而知.
  红黑树.png
• ::
  hash表的性能也非常高为什么 不用?
  无法进行范围查询.
• ::这就是mysql底层用的数据结构.官方定义根节点大小为16k,共可以存储2千多万的数据.
  B+树.png

myisam数据库存储引擎实现

in.png

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

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

inndob数据库存储引擎实现

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

ibd文件存储的是: 索引数据+主键对应的表字段 in.png
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表更安全。