【索引专题】索引分类相关

共以下 4 个回答

• 終达·斯维达尼亚 永久会员 2024年2月13日 上午2:16

  1、介绍一下索引的分类，以及他们的主要区别是什么？

  • InnoDB中根据叶子结点储存数据类型可以分为聚簇索引和二级索引，聚簇索引的叶子节点储存的是完整的数据，而二级索引的叶子结点储存的是主键的值，因此在使用二级索引的时候需要多一次回表操作。
  • 按字段进行分类则主要有主键索引、普通索引和前缀索引。前者是在正常情况下sql语句查询时所用到的索引，来提升查询效率。而普通索引是加在除了主键索引字段以外的字段上的索引，该索引也能对数据进行查找，但是在底层结构上该索引存放的value值是主键的索引，因此在使用普通索引的时候每次查询需要额外的一次回表，相比于主键索引效率较慢。前缀索引是指取字符串前几个字符，或者二进制的前几个字节作为索引，合适的前缀索引既可以大幅减少索引占用的空间，又能提升索引的查询效率。

  2、介绍一下什么是复合索引？什么样的情况下我们会使用复合索引？

  • 复合索引就是建立在多个列上的索引，多个列之间从左到右按次序排序，查询时也需要遵循 “最左匹配原则” 。实践中一般在查询语句经常涉及到多个列作为查询条件时，可以创建一个联合索引加快查询速度。还可以在普通索引的基础上创建一个复合索引，将其优化为覆盖索引，省去用普通索引查询时的回表操作。除此之外，当语句需要根据某个字段排序或分组时，复合索引也能加快速度。
  • 最左匹配原则是指，当联合索引需要用到多列索引时，必须要规定一个字段索引查找的优先级，通常数据不可能同时满足A字段和B字段的顺序排列，因此可以在优先满足其中一个字段的前提下满足另一个字段。所以规定优先满足最左边的字段，因此叫最左匹配原则。

  3、唯一索引了解吗？在使用的时候，有什么需要注意的不？

  • 唯一索引需要确保索引列中的所有值都是唯一的，但是可以插入多个 NULL 值。实践中，在业务已经确保值唯一的情况下，想要进一步提升性能，可以将唯一索引改为普通索引，这样就可以利用 change buffer 机制，在写多读少的场景中大幅加快查询速度。
  • change buffer 是内存中的一个缓冲区，用于暂时存储对数据的更新操作。也就是说，在需要对一个数据页进行更新时，如果这个数据页就在内存中，那么就直接更新。如果不在内存中，可以不用从磁盘中读取这个数据页，而是将对数据的操作先记录在 change buffer 中，等到真正要查询这个数据页时再将 change buffer 里的内容依次应用，得到正确的数据，这个过程也叫 merge。除了访问这个数据页会触发 merge，系统后台也会定期进行 merge，数据库正常关闭时也会进行 merge。change buffer 的优点在于，将更新操作暂存在 change buffer 中，可以减少读磁盘的过程，加快语句的执行。另外，数据读入内存也是需要占用 buffer pool 的，所以这种方法还能避免占用内存，提高内存利用率。

  4、我们有时候会听到索引下推，你知道什么是索引下推吗？那覆盖索引又是什么意思呢？

  • 索引下推是指在索引遍历过程中，对当前索引中包含的字段先做判断，直接过滤掉不满足条件的一些记录，减少回表的次数。比如现在需要根据三个字段限制 A B C 查某个数据，并且已经有了 A,B 的联合索引，可以在 A 匹配成功的同时，利用索引中包含的 B 字段数据，顺便判断一下 B 是否符合，不符合就不用回表了，减少回表次数。
  • 覆盖索引就是根据索引查数据时，如果要查的值已经在索引中了，就不用回表，直接返回结果就行。比如支付模块的支付信息表，包含有订单号和支付状态两个字段。由于经常需要通过订单号去查它的支付状态，可以单独给订单号创建一个普通索引，但考虑到每次只是由订单号查支付状态这一个字段，可以创建一个订单号和支付状态的联合索引，也就是覆盖索引，这样就省去了使用普通索引的回表操作，提高查询效率。

  0 赞同 0 条回复

  登录以回复
• LilyBoy 永久会员 2023年9月21日 下午9:43

  1、介绍一下索引的分类，以及他们的主要区别是什么？
  按数据结构：哈希索引、有序数组、全文索引、B+树索引
  按存储特性：聚簇索引（主键索引）、二级索引（辅助索引）
  按字段：普通索引、唯一索引、主键索引、前缀索引
  按个数：单个索引、联合索引

  2、介绍一下什么是复合索引？什么样的情况下我们会使用复合索引？
  把多个单个的字段组合起来形成一个索引，(a,b,c)的形式就是联合索引；
  联合索引会遵循“最左匹配原则”，
  一般需要查询多个列时需要创建联合索引，可以在普通索引的基础上创建联合索引，也就是使用覆盖索引进行优化，可以省去普通索引在查询过程中出现的回表查询插座，

  3、唯一索引了解吗？在使用的时候，有什么需要注意的不？
  不允许为Null，unique关键字进行修饰，不允许出现重复值，

  4、我们有时候会听到索引下推，你知道什么是索引下推吗？那覆盖索引又是什么意思呢？
  现在我们知道，对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

  在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。

  而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

  当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化

  0 赞同 0 条回复

  登录以回复
• 小肸 永久会员 2023年8月12日 下午8:40

  可以分为聚簇索引和非聚簇索引两大类。聚簇索引中B+树以主键列为键，叶子节点存储完整的行数据。而非聚簇索引中叶子节点只存储行数据中的索引列和主键列的数据，如果是以一个索引列为键称为普通索引，如果是多个索引为键称为复合索引，注意复合索引只满足最左匹配原则。

  sql语句中如果where、order by、 group by中使用了多个索引，可以考虑建立复合索引来减少建立不必要的索引或者优化性能。比如根据user_id在订单表中查询最近的一条订单数据。就可以建立一个联合索引(user_id, create_time)。这样子就可以避免额外建立一个create_time索引。从商品表查询所有用户的最近的订单记录也可以使用联合索引。

  select * from order order by create_time group by user_id;
  

  这样只用扫描一次索引树和回表一次主键索引树。如果分别建立user_id和create_time两个索引，会扫描2次i索引树和回表一次主键索引树。

  唯一索引就是索引列没有重复的数据。第一就是如果业务上要求数据不能重复时，可以直接建立唯一索引来确保。因为即使在业务上做了确保不会向数据库中插入重复数据的操作，根据墨菲定律，数据库中一定会有脏数据产生，并且业务上判断重复数据也会产生的性能开销。第二就是如果业务上容许一定重复数据的存在，并且该数据是写多读少（不是写完数据后立刻读取），可以考虑在业务上使用判重操作，避免使用唯一索引导致change buffer失效，没有减少磁盘的随机读开销。第三就是如果使用唯一索引，必须只读列的值非空，因为唯一索引列上容许空值重复存在。

  索引下推就是在扫描索引树时，可以在索引树上进行一些过滤条件的判断，减少回表过程中在主键索引树上的扫描行数。比如一张表tmp中列有a,b,c三列，a为主键，(b,c为联合索引)。表中有100行数据。列上的值就是行的id。

  select * from tmp where b between 50 and 80 and c between 10 and 20
  

  如果没有索引下推，需要回表从主键索引中取出31行数据判断。如果有索引下推，回表从主键索引上取出的行数据便是0。

  索引下推是为了减少回表次数。而覆盖索引是为了避免回表。

  select * from tmp where b between 50 and 80
  

  如果有覆盖索引，因为联合索引树上的叶子节点存储b,c,a三列的数据，所以直接从联合索引树上返回数据，而不用再去主键索引树上取数据。

  0 赞同 0 条回复

  登录以回复
• 秊 永久会员 2023年8月2日 下午8:32

  1、介绍一下索引的分类，以及他们的主要区别是什么？

  在 InnoDB 中，按叶子节点存储的是否为完整的表数据，可以分为聚簇索引和二级索引。聚簇索引的每个叶子节点都存储了一行完整的数据，而二级索引的叶子节点只存储了某一列的数据和主键值，一般在利用二级索引进行查询时需要根据主键值进行回表操作，当然可以利用覆盖索引优化。

  按字段进行分类，可分为主键索引，普通索引和前缀索引。主键索引是建立在主键上的索引，通常在创建表时一起创建。唯一索引不允许有重复值，但可以有多个空值。前缀索引是指对字符串类型字段的前几个字符，或者二进制类型的前几个字节建立的索引，合适的前缀索引既可以大幅减少索引占用的空间，又能提升索引的查询效率。

  按索引字段个数分类，可分为单列索引和联合索引，区别在于建立索引的列数不同。

  2、介绍一下什么是复合索引？什么样的情况下我们会使用复合索引？

  复合索引就是建立在多个列上的索引，多个列之间从左到右按次序排序，查询时也需要遵循 “最左匹配原则” 。

  实践中一般在查询语句经常涉及到多个列作为查询条件时，可以创建一个联合索引加快查询速度。还可以在普通索引的基础上创建一个复合索引，将其优化为覆盖索引，省去用普通索引查询时的回表操作。除此之外，当语句需要根据某个字段排序或分组时，复合索引也能加快速度。

  3、唯一索引了解吗？在使用的时候，有什么需要注意的不？

  唯一索引需要确保索引列中的所有值都是唯一的，但不保证值为 NULL，也就是可以插入多个 NULL 值。实践中，在业务已经确保值唯一的情况下，想要进一步提升性能，可以将唯一索引改为普通索引，这样就可以利用 change buffer 机制，在写多读少的场景中大幅加快查询速度。

  3.5、讲一下 change buffer 的作用

  change buffer 是内存中的一个缓冲区，用于暂时存储对数据的更新操作。也就是说，在需要对一个数据页进行更新时，如果这个数据页就在内存中，那么就直接更新。如果不在内存中，可以不用从磁盘中读取这个数据页，而是将对数据的操作先记录在 change buffer 中，等到真正要查询这个数据页时再将 change buffer 里的内容依次应用，得到正确的数据，这个过程也叫 merge。

  除了访问这个数据页会触发 merge，系统后台也会定期进行 merge，数据库正常关闭时也会进行 merge。

  change buffer 的优点在于，将更新操作暂存在 change buffer 中，可以减少读磁盘的过程，加快语句的执行。另外，数据读入内存也是需要占用 buffer pool 的，所以这种方法还能避免占用内存，提高内存利用率。

  4、我们有时候会听到索引下推，你知道什么是索引下推吗？那覆盖索引又是什么意思呢？

  索引下推是 5.6 版本后推出的，是指在索引遍历过程中，对当前索引中包含的字段先做判断，直接过滤掉不满足条件的一些记录，减少回表的次数。比如现在需要根据三个字段限制 A B C 查某个数据，并且已经有了 A,B 的联合索引，5.6 以前是直接根据最左匹配原则，只利用 A 这个索引查主键，然后对所有 A 匹配的行进行回表。现在有了索引下推，可以在 A 匹配成功的同时，利用索引中包含的 B 字段数据，顺便判断一下 B 是否符合，不符合就不用回表了，减少回表次数。

  覆盖索引就是根据索引查数据时，如果要查的值已经在索引中了，就不用回表，直接返回结果就行。比如支付模块的支付信息表，包含有订单号和支付状态两个字段。由于经常需要通过订单号去查它的支付状态，可以单独给订单号创建一个普通索引，但考虑到每次只是由订单号查支付状态这一个字段，可以创建一个订单号和支付状态的联合索引，也就是覆盖索引，这样就省去了使用普通索引的回表操作，提高查询效率。

  0 赞同 0 条回复

  登录以回复