Mysql索引

一.Mysql索引

1. 没有索引的情况下进行查找

select id from student where id = 1;  

对于上面的查询语句进行分析:

1. 在一页中进行查找

   假设数据量比较少,只有一页的数据

   • 当查询条件为主键时

     这个过程就是通过页结构中的 Page Directory中的槽[spot]进行二分查找

   • 当条件为其他列时

     由于数据页没有为非主键建立页目录[也就是进行分组],只能遍历遍历一条一条的记录进行匹配

2. 在多页中进行查找

   • 首先找到该记录的页

     由于没有针对页的页目录,所以我们只能从第一页遍历查找了

   • 从页中查找对应的记录

2.InnoDb的索引方案

• 首先InnoDb使用页作为管理存储空间的基本单位,最多保证16k的连续空间

1. 对于页来说中的记录是按照大小依次链接的单向链表,在对页建立目录时,也需要保证下一个页的主键值大于第一页的主键

   [注] :

   • 数据页的编号可能不是连续的
   • 为了保证连续,页中的记录会在页之中移动

2. 针对每一个页建立目录项,页的目录项包含两部分 存储在该键的最小主键值 & 页号[page_no]

   为了区分和普通用户记录,页的目录项的 record_type=1,

   同样这些存储页的目录项的页也会在页中为其生成 Page Directory[页目录],用来进行二分查找,加快查找效率.

3. 由于存储目录项记录的页不止一个,于是我们会为这些存储目录项的页创建更高级的目录

这样就构成了B+树

• 实际的用户记录都是存放在 B+树的最底层的节点上

• 非叶子节点都是用来存放目录项的

  (1)聚簇索引

  • 使用记录主键值的大小进行记录和页的排序
    • 页内的记录是按照主键大小顺序排成的单向链表
  • 各个存放用户记录的页也是根据主键大小拍序的双向链表
  • 各个存放目录项的页也是根据记录最小值的主键大小排序的双向链表
  • B+树的叶子节点存储的是完整的用户记录

  (2) 非聚簇索引

  MyISAM使用非聚簇索引,因为MyISAM的索引文件和数据文件是分离的,MyISAM同样使用的是B+树作为索引结构,唯一的区别是叶子节点用户记录的存放的不是数据而是数据的地址

  (3) 辅助索引(二级索引)

  聚簇索引是针对主键创建的索引,如果索引是其他列

  当索引非主键A时,会创建另外的B+树

  这可B+树与主键的B+树的区别是:

  • 使用A的大小进行记录和页排序
    • 页内按照A列的大小进项排序形成一个单向链表
    • 各个存放用户记录的页也是根据A列的大小排序的双向链表
    • 各个存放目录项的页也是根据A列大小排序的双向链表
  • B+树上不是完整的用户记录,而是A列 + 主键这两个列的值
  • 目录项记录的是A列 + 页号

  如何根据辅助索引查找?

  • 根据其提供的 A列号 查找目录项记录页
  • 根据目录项查找确定用户记录所在的页
  • 在页中二分查找用户的记录
  • 最终得到的是该列的主键

  由于最终的到的是主键,还需要在聚簇索引进行查找该主键对应的列,称之为**回表**

  为了减少存储空间的消耗[拷贝用户记录到B+树中],所以采用了这样的查找方式,用了两棵B+树,对非主键的列建立的B+树需要回表操作才能定位到完整的用户记录 .

  (4).联合索引

  对于 A 和 B两个列 创建联合索引

  • 先把各个记录和页按照 A列 进行排序
  • 在记录 A 相同的情况下,采用 B列 进行排序

3. 关于索引的补充

• 最左前缀原则 : mysql 在建立多列索引[联合索引有最左优先原则]

由上面的创建联合索引的过称可得知,若创建包含[A,B,C] 三个属性的联合索引[A_B_C],首先是对A列进行了排序 ,然后在A相同的基础上对B进行排序,C同理.

因而最左前缀原则也就不难理解,查询中会遵循最左匹配,如果匹配到 [A列],[A列,B列],[A列,B列,C列]才会用到该索引

• 注意避免冗余索引

在大多数情况下都应该尽量扩展已有的索引,而不是创建新的索引.