MySQL | 青训营

wee
80 阅读8分钟

为什么要用索引

  • 索引，一个高效地查询和检索数据的数据结构，可以看作数据的目录

    • 好处

      • 大大加快数据的检索速度(减少检索的数据量)，降低数据库的IO成本
      • 加快分组和排序的速度
      • 保证行的唯一性，增强数据的完整性

    • 缺点

      • 创建和维护索引需要耗费一定的时间，对数据进行CRUD时增加了时间开销，降低了SQL执行效率
      • 索引需要使用物理空间存储，即需要占用一定的磁盘空间

  • 分类

    • 数据结构分

      • B+树索引：多路平衡查询树，天然有序，适合范围查询
      • Hash索引：没有顺序关系，适合等值查询，范围查询需要扫描全表。
      • 全文索引：full-index

    • 物理存储分

      • 聚簇索引（主键索引）
      • 非聚簇索引（二级索引，非主键索引）

    • 逻辑特性分

      • 主键索引
      • 普通索引:单列索引
      • 唯一索引:索引列的值必须唯一，但允许有空值
      • 前缀索引
      • 联合索引：多列索引

  • 为什么使用B+树作为索引

    • InnoDB和MyISAM都支持B+树索引，它天然有序，适合范围查询。
    • MySQL数据是持久化的，索引和记录是存储在磁盘中的，因此每次使用索引进行高效检索数据时，必须访问磁盘取得索引，再根据索引再次访问磁盘检索数据后读入内存，每次检索数据都需要进行多次的IO操作
      • 由于IO操作耗费大量的时间，我们需要使得IO操作次数尽可能少，选择一种合适的数据结构很重要
      • 又由于MySQL支持范围查找，意味着数据结构需要支持高效地范围查询
    • B+树是一种基于磁盘的平衡多路查找树，高度通常很低（3-4层），意味着访问效率很高，从千万或上亿数据查找一个数据，只用到3到4次IO操作
    • B+树非叶子节点只存储索引，并不存储实际的值，相比于每个节点都存储实际值和索引的b树而言，B+树的非叶子节点可以存放更多的索引
      • B+树的所有叶子节点都存储了数据，并且叶子节点之间用双向链表连接，数据是天然有序的，通过链表能很方便地进行范围查询和排序操作
      • B+树每个节点都有大量的数据或节点，冗余数据（即叶子节点存储的实际值）或索引能够保证插入删除的效率更高，也说明B+树的树层很稳定，不会因为某个数据导致复杂的树的变化
    • 相对
      • 数组+二分查找，虽然更简单，但是b+树具备更高的插入或者删除元素的效率
      • 二叉查找树或平衡二叉树，高效维护元素的能力，但树高会随元素地增加而变大，b+树有更矮的树高，进行IO操作次数更少，所以更具备高效地查找效率
      • b树来说，b树需要中序遍历完成范围查找，IO次数当然增多，B+树的双向链表更高效进行范围查询

聚簇和非聚簇

  • 聚簇：数据存储与索引放在一起，叶子节点存储索引和索引对应的数据，聚簇索引的顺序就是数据的物理存储顺序，这样能提高查询效率，当然也增加了插入和更新的开销，并占用更多的空间
  • 非聚簇索引：数据存储与索引存储分开，索引的叶子节点指向数据对应的位置，非聚簇索引的顺序与数据物理存储顺序无关

InnoDB 存储引擎中每张表只能有一个聚簇索引，通常是主键索引，非聚簇索引都是二级索引，可以有多个

  • 主键索引：

    • 建立在主键字段
    • 叶子节点存放的是实际数据（所有完整的记录）
      • 表中有主键，默认聚簇
      • 没有主键，选择第一个不包含NULL值的唯一列作为聚簇
      • 若都不存在，存储引擎自动生成一个隐式子层id作为聚簇索引的索引键

  • 辅助索引

    • 叶子节点存放的数据为主键（说的是主键值），说明需要回表

    • 唯一、前缀、普通都是二级

      • 唯一索引（UNIQUE）：与普通不一样的是属性列不能出现重复的数据，但是允许数据为NULL，一张表可以允许创建多个唯一索引。
      • 普通（INDEX）：建立在普通字段上的索引，一张表可以允许多个普通索引，并允许数据重复和数据为NULL。
      • 前缀：前缀索引只能建立在字符串类型（char、varchar、binary、varbinary）等类型上。
        • 创建方式可以是CREATE INDEX 索引名 ON 表名(列名(指定前缀长度)); 
        • 目的是为了减少索引占用的存储空间，提高查询效率。

  • 回表

    二级索引查找记录的过程为：

    • 先获得二级索引中的B+树的索引值，检索 二级索引的B+树 找到对应的叶子节点后获取到对应的主键值，再通过主键值检索 主键索引的B+树 找到对应的叶子节点即可获得对应的数据（记录）。
    • 第一次检索获取主键值，再通过主键值再次检索获得记录的这个过程叫做 回表。

  • image.png

    • ID为主键（主键不能为null，外键只能为null或者主键值）
      • select *from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树
      • select *from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID 的值为500（表明了节点值就是ID），再到ID索引树搜索一次。这个过程称为回表。
    • 所以说二级索引一定会回表查询吗
      • select ID from T where k=5，我查的就是主键，阁下该如何应对？
      • 这时候不需要回表，直接返回数据即可，这种在二级索引的B+树的叶子节点能直接查询到数据的过程就叫做 覆盖索引

  • 索引下推（index condition pushdown,简称ICP）

    • 概括:对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的行数
    • 5.6之后引进的优化功能
      • 可以让存储引擎在非聚簇索引检索数据时，对索引包含的字段先做判断是否符合条件，过滤掉不符合的记录，再返回给数据库Server层，减少回表次数，减少不必要的数据传输，提高查询效率和性能
      • 理解上来说就是，本该交由Server进行判断是否符合条件的工作下推到了存储引擎层，使得存储引擎返回给Server层数据减少，减少不必要数据传输和回表操作（过滤）
      • 注意：存储引擎层只是判断索引中包含的字段是否符合条件，对于那些没有索引又需要判断的字段会返回到Server层进行判断
    • 用索引A筛数据时，直接同时用索引B判断，比如查询姓张，年龄小于10的。
      • 原来是查出所有姓张的，然后一一回表，判断年龄。
      • 有了优化，现在是直接根据索引判断跳过，会少很多回表次数和不必要的数据传输。
    • 把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数 和 MySQL Server访问存储引擎的次数，与覆盖索引目的一样，都是为了减少回表的次数

  • 联合索引

    • 多个字段组合的索引咯CREATE INDEX 索引名 ON 表名(列名1, 列名2, ...);
    • image.png
      • 先按照前面列名1排序，相同情况下再按照列名2排序
      • 列名1全局有序，列名2，3，4……全局无序，局部相对有序
    • 联合索引失效
      • 如果你where条件只有列名2，没有列名1，是不会利用索引而是遍历查找的
      • 利用索引的前提是：索引数据结构中索引键是有序的（列名1，列名2在where子句的in 和 = 中顺序不重要，有查询优化器）
    • 核心：最左前缀原则
      • 建立联合索引时，检索数据时从联合索引的最左边开始匹配
        • 创建（a,b,c）联合索引相当于建立三个索引(a),(a,b),(a,b,c)，即如果命中了前面三个其中任意一个，都是可以命中索引，用索引检索数据的
      • 查询时，where字段顺序与是否走索引无关，但数据量大小以及存在select*可能不会走索引

  • 字符串索引

    1. 直接创建完整索引，这样可能比较占用空间，一般不选择
    2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引（回表）
    3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
    4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟（3）一样，都不支持范围扫描。

总结

  • MySQL，理一遍顺一遍，印象还会更加深刻
avatar
文章
阅读
粉丝