两种索引:MyISAM(非聚集索引)和InnoDB(聚集索引)的介绍

两种索引:MyISAM(非聚集索引)和InnoDB(聚集索引)的介绍

索引:

索引是帮助Mysql高效获取数据的排好序的数据结构

• 索引的数据结构

  • 二叉树(早期Mysql用这个)

  • 红黑树(早期Mysql用这个)

  • Hash表

  • B-Tree

  • B+Tree(B-Tree的变种)(现在在用)

=========================================

B+Tree

• B+Tree(在内存查找,和磁盘做一次IO交互)

  • 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引(放在内存中查找,不和硬盘做IO交互)

  • 叶子节点包含所有索引字段,存储data(data指的硬盘数据库的地址,先内存找索引,找到之后再和硬盘做IO交互)

  • 叶子节点用指针连接,提高区间访问的性能

    • 进行范围查找方便,比如:主键a>7或者a<7

  • B+Tree的存储机制

    • 一行规定是16KB , 索引规定是8B(绿色部分) , 下一个磁盘文件地址规定是6B(白色部分) ; 16KB/(8+6)B等于1170 , 一行可以指出1170个下一个磁盘文件地址[在内存里查找] , 找到叶子节点的时候 , 叶子节点包含索引字段和data,保守估计占用内存1KB , 则三行就会有2千多万个数据

       

=========================================

两种索引

(通过索引文件和数据文件是否分离来分成聚集和非聚集索引)

• MyISAM索引实现(非聚集)[索引文件和数据文件分离]

  • 最后的是磁盘文件地址,拿到磁盘文件地址在进行IO *** 作(相当于回表)

  •  

• InnoDB索引实现(聚集)[索引文件和数据文件不分离]

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件 (索引页)

  • 聚集索引-叶节点包含了完整的数据记录 (数据页)

  •  

     

• 为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?

  • 如果没有主键,那么B+Tree则维护不了 ; 则数据库会在每一列数据库里面加一个唯一索引(也就是每一列都没有重复的数据[和主键一样]) ; 如果每列都没有唯一索引,那么数据库会建立一个隐藏列(里面装主键,每一行数据都是唯一的),来维护B+Tree

  • 整型:数据量很大的话,用UUID比较的话,会很慢,因为字符串很长 ; 自增:B+Tree的一个机制,如果不是自增的话,他会插入某条数据的中间,存满了之后,可能向上分裂,整个B+Tree都会变化,如果自增的话,变化会小一点

• 为什么非主键索引结构叶子节点存储的是主键值

  • 一致性和节省存储空间

• 索引可以用B+Tree和Hash表来存储,为什么大部分情况不用Hash表存储

  • Hash只能满足"=","IN",不支持范围查询,比如大于,小于,大于等于,小于等于....等等

  • Hash冲突问题

  • 但很多时候Hash索引要比B+树索引更高效,比如数据爆表的查询

• 缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

• 两者区别

  • 聚集索引在叶子节点存储的是表中的数据。

  • 非聚集索引在叶子节点存储的是主键和索引列。

=========================================

用索引要遵守最左原则

• KEY 'idx_name_age_position' ('name','age','position') USING BTREE
  ​
  ​
  EXPLAIN SELECt * FROM employee WHERe name='Bill' and age=31;//用了索引
  EXPLAIN SELECt * FROM employee WHERe age=30 and position='dev';//没用索引
  EXPLAIN SELECt * FROM employee WHERe position='manager';//没用索引

• 原理:因为索引是按照name先进行排序的 , 如果name相同则进行age排序 , 如果age相同则进行position排序 ;

  • 比如查Bill,有很多个Bill,后面查到HanMeimei就不用管了,然后查age,查到31,如果有很多个31,则查position

  •  

=========================================

联合索引

• 创建一个联合索引并搜索值

  • create index idx_t1_bcd on t1(b,c,d);
    select * from t1 where b=1 and c=1 and d=1;

  • 叶子节点里面的data是主键(黄色部分),然后在去主键索引里面找数据,这个过程叫做回表

  •  

    • select * from t1; #ALL(全盘)搜索,需要回表
      select b from t1; #index搜索,不需要回表,因为只找b,我们建立的bcd索引,存的数据比主键索引存的数据少,而我们只找b,所以说从bcd索引找数据更快
      select c from t1; #index搜索,不需要回表
      select b,c,d,a from t1; #index搜索,不需要回表
      select * from t1 where b>1; #ALL搜索,需要回表,因为他选择的是全部的数据,
      select b from t1 where b>1; #range搜索,不需要回表
      select b,c,d,a from t1 where b>1; #range搜索,不需要回表,因为叶子节点记录的是bcda,其他的没记录,所以不需要回表
      select * from t1 order by b,c,d; #ALL搜索,排序(内存)&不用回表,比index搜索,不用排序&多次回表(硬盘)  快;

数据库的页(page)

• 在数据库当中查找数据

  • 页:sql最少取得数据大小,16kb

  • int 是4b,varchar一个字符是4b

    • 比如你找a=7,因为一行字段就4*4b+4b=20b,远远不够16kb,必须凑齐16kb ; 所以你搜索的时候,凑齐16kb(在下列sql中是整个表的数据)才放进内存,之后再去找a=7,总体是一次IO

    • select * from t1 where a= 7;//a是主键索引(聚集索引)

    • 如果找a>7,有索引的情况下,就找a=7,然后自动返回a=7以后的数值

    • 如果找a<7,有索引的情况下,就找a=7,然后自动返回a<7以后的数值

• 存储或者查找数据,去定位页的位置,再插入或者查找,用的是二分查找