所有分类
  • 所有分类
  • 未分类

MySQL联合索引–使用/原理/优化

简介

本文介绍MySQL的联合索引(也可以称为:组合索引、复合索引)的用法。

MySQL一次查询只能使用一个索引。如果要对多个字段使用索引,需要建立复合索引。

联合索引的原理

联合索引是对多个列进行索引。

  • 联合索引也是一棵B+树。
    • 联合索引的键值数量不是1,而是大于等于2。
    • B+树在对第一个索引排序的基础上,对第二个索引排序
  • 联合索引遵循最左前缀(最左匹配)原则。

假定上图联合索引的为(a,b),B+树在对索引a排序的基础上,对索引b排序。所以数据按照(1,1), (1,2), (2,1), (2,4), (2,4), (3,1), (3,2)顺序排放。

从整体来看

  • a是有序的:1,1,2,2,3,3
  • b是无序的:1,2,1,4,1,2

从局部来看

当a的值确定的时候,b是有序的。例如a = 1时,b值为1,2是有序的状态。当a=2时候,b的值为1,4也是有序状态。

总结

  1. WHERE a = 1 AND b = 2
    1. a, b字段能用到联合索引。
    2. 原因:当a的值确定的时候,b是有序的。
  2. WHERE b = 2
    1. 此时b字段不能能用到联合索引。
    2. 原因:b不是有序的
  3. WHERE a > 1 AND b = 2
    1. a字段能用到索引,b字段用不到索引。
    2. 原因:a的值此时是一个范围,不是固定的,在这个范围内b值不一定是有序的,因此b字段用不上索引。

综上所述:只有保证最左匹配原则,才能用上联合索引。

联合索引有效的情景

假设:创建联合索引:INDEX index_name (a, b);

以下情景都走索引

  1. SELECT * FROM table_name WHERE a=XX
    1. 会使用索引。
  2. SELETE * FROM table_name WHERE a=XX AND b=YY
    1. 会使用(a,b)联合索引的
  3. SELECT * FROM table_name WHERE b=YY AND a=XX
    1. 这条语句不符合最左匹配原则。但由于查询优化器的存在,MySQL优化器会自动调整where后的a,b的顺序与索引顺序一致。

联合索引中可以将唯一性最高的列放在索引最前面。例如:在一个公司里以age 和gender为索引,显然age要放在前面,因为性别就两种选择男或女,选择性不如age。

联合索引失效的情景

假设:创建联合索引:INDEX index_name (a, b);

以下情景都不走索引

  1. SELECT * FROM table_name WHERE b=YY
    1. 叶子节点的b值为1,2,1,4,1,2,它不是有序的,因此不能使用(a,b)联合索引。
  2. SELECT * FROM table_name WHERE a>XX AND b=YY
    1. 此处只会用到a索引,不会用到b索引。
    2. 对于联合索引,会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
      1. 比如:a = 1 AND b = 2 AND c > 3 AND d = 4, 若建立(a,b,c,d)索引,d用不到索引。
      2. 使所有索引都有效的方案:将c放到建立索引的语句的最后,例如:建立(a,b,d,c)的索引(a,b,d的顺序可以任意调整),这样执行sql的时候,优化器会帮我们调整WHERE后a,b,c,d的顺序(将c放到最后),让我们用上索引。
3

评论2

请先

  1. MySQL8有一个索引跳跃的概念
    Jackson was 2024-03-31 0
    • 对,oracle、redis等也有这个。
      自学精灵 2024-04-01 0
显示验证码
没有账号?注册  忘记密码?

社交账号快速登录