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放到最后），让我们用上索引。