mysql索引简单介绍

守护可爱多
157 阅读6分钟

为什么需要索引

  • 我们都知道，磁盘是用页来存储数据的，一个页默认16KB，所以在抛开缩索引的情况下访问某一条数据的话需要经历两个步骤

    • 定位页
    • 在页中定位记录。两者的时间消耗都是O(N)级别代价太高。

索引结构

  • 以InnoDB为例，InnoBD的索引通过B+树来实现，B+树不同于普通二叉树，单个节点可以保存多个元素，其数据全部保存在叶子节点，所有的叶子节点通过链表的方式进行连接，查询稳定。也就是说保存相同的数据，B+树更矮壮，查询快。

  • 在索引中B+树的非叶子节点我们把它叫做目录项，起到对查询的一个导航作用，叶子节点存放数据。下面以一幅简单的图来说明索引的结构(当然不止两层)。

  • image-20211203224404548

  • 这个索引树的数据全部存放在叶子节点，我们假设每个页的容量为5条记录，页与页之间以双向链表的形式连接，页内的记录以单链表的形式进行连接。

  • 需要注意的是页编号不一定是连续的。其中记录类型主要有(其中2和3在间隙锁中也进行使用)：

    • 0：普通用户记录
    • 1：目录项记录
    • 2：Infimum(最小)记录
    • 3：Supremum(最大)记录

  • 比如说我们执行语句select * from user where id = 5;查询id为6的记录，首先在页号为30的目录项中进行二分查找，得到id为6的记录可能在页号为23的数据页中，再在页23中通过二分法定位到id为6的数据。

索引分类

  • 聚簇索引

    • 每一个表有且只有一个聚簇索引，由系统自动为我们创建，也就是我们最常见的索引:数据完整地存放在所有的叶子节点中，页与页之间通过双向链表的形式连接，页内记录通过单链表的形式连接。在聚簇索引中系统默认在主键id上建立索引，如果没有主键id的话系统会选择具有唯一性的列作为索引，万不得已会选择row_id作为索引。
    • 聚簇索引主要还是当用户把主键id作为搜索条件时进行查询加速。因为聚簇索引只为主键id建立了索引。

  • 二级索引

    • 在主键以外的数据列上建立索引，非叶节点存储的是目录项，叶子节点存储的是索引+主键id，具体情况见下图:
    • image-20211203213620607
    • 从图中我们可以看到，我们对c1列建立主键索引，对c2列建立普通索引(辅助、二级索引)，其中目录项按照c2进行排序，叶子节点中的数据项也是按照c2进行排序，当我们执行语句select * from tb where c2=8,语句的查询方式和聚簇索引查询方式一致。通过目录项定位到数据页，再通过数据页查找数据。
    • 但是需要注意的是，和聚簇索引不同，聚簇索引叶子节点存放的是完整记录，也就是说记录的所有字段都存在叶子节点，但是二级索引的叶子节点只存放了c1、c2字段，所以我们拿到包含这两个字段的记录后，需要根据获取到的id到聚簇索引中查询完整的数据再返回给server，在二级索引中拿到主键id并到聚簇索引中去查询这个过程称为回表。
    • 索引覆盖:在查询语句中只涉及到索引列，那么就意味着在二级索引中拿到的数据就可以直接返回，无需回表查询完整的记录，同样的索引覆盖在其他类型索引中也是一样的。

  • 联合索引

    • 之前我们提到的都是在不同的列上单独建立索引，联合索引就是同时在多个列上建立索引，比如说我们在c1、c2、c3上建立联合索引，数据的排布方式就是:首先按c1从小到大排序，c1相同的数据按c2从小到大排序，c1，c2相同的数据按照c3从小到大排序。

    • 联合索引和二级索引大同小异，二级索引叶子节点存储的数据为索引列+主键列，那么联合索引的叶子节点中也是同样存储着索引列(联合索引中索引的列)+主键列。下面以一幅简单的示意图来展示联合索引：

    • image-20211203220854168

    • 在联合索引中会存在以下几个常见的情况:(比如说我们有一个表，在(c2,c3,c4)上建立联合索引)

      • 最左前缀原则:

        • 建立上面这个联合索引相当于在表上建立了(c2)(c2,c3)(c1,c2,c3)这几个索引，其余的查询都不会启用索引

      • 索引下推:

        • 假设我们在tb_1中建立三个二级索引，c2，c3，c4，在叶子节点中数据按照c2，c当我们执行查询语句select * from tb_1 where c2=5 and c4='tom'的时候，不符合最左前缀原则，无法使用索引进行查询，但是在辅助索引中的叶子节点中数据记录是包括c4字段的，执行引擎就直接判断当前记录的c4是否符合要求，符合要求才进行回表操作，否则进行下一条记录查询。正常情况下是执行引擎拿到一条符合要求的数据就进行回表操作再到server层进行where条件判断，现在由server下面的engine执行，所以叫做索引下推。

索引失效

  • 什么情况下无法利用索引呢?

    • 查询语句中使用LIKE关键字：在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。
    • 查询语句中使用多列索引：多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
    • 查询语句中使用OR关键字：查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

  • 索引失效做个总结

    • 有or，但是前后至少有一个字段没有建立索引会导致索引失效。
    • 复合索引未遵循最左前缀
    • like以%开头
    • 需要类型转换
    • where中索引列有运算
    • where中索引列使用了函数
    • 如果mysql觉得全表扫描更快（数据少）
avatar
文章
阅读
粉丝