Mysql验证联合索引的最左原则

前言

如果你接触过数据库，应该听说过在数据库表的某些列上建立索引能够加快查找速度，如果研究更深入一点的人，可能还听说过联合索引，那么索引为什么能够加快查找速度呢？联合索引究竟又是什么呢？下面说说我的简单理解。

索引

试想一下，把1~10000这1万个数字打乱顺序存储在数组中，如果要找到5000这个数字在哪，那就得从数组第0个元素开始，依次遍历找到5000这个数字所在的位置，运气好了1次就能找到，运气不好需要查询1万个数，可是如果把这1万个数作为map的key，每个数存在数组中的位置作为value，存储在map结构中很快就能找到，通常情况下要比直接遍历快的多。

其实这里的map充当的是一个索引的作用，我们知道map存储数据时使用树形结构，会根据要查找的值和当前节点比较，来确定继续查找左分支还是右分支，而数据库中的索引充当的也是这样的作用，mysql中的索引是BTree结构（多路搜索树），就是利用建立索引的列中的所有值建立了一棵树，通过有序的树形查找一般要比全局搜索快多了吧！

联合索引

简单了解了一下索引的含义，那么什么是联合索引呢？其实mysql数据库中的索引不止可以建立在一个列上，它可以将一个索引同时建立在说多个列上，也就是我们所说的联合索引，联合索引的作用特别大，有时会超过单列索引，至于什么时候建立单列索引，什么时候建立联合索引同样是个很复杂的问题，在此不做描述。有兴趣的读者可以自行搜索一下。

最左原则

当你在多个列上建立一个索引时，怎样的查找才能利用索引加快速度呢？说到这我们先建立一个带有索引的表格，具体的分析一下什么叫做索引的最左原则。

CREATE TABLE IF NOT EXISTS `test_index`(
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `a` int(4) NOT NULL DEFAULT '0',
  `b` int(4) NOT NULL DEFAULT '0',
  `c` int(4) NOT NULL DEFAULT '0',
  `data` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `union_index` (`a`,`b`,`c`)
)ENGINE=InnoDB ROW_FORMAT=DYNAMIC  DEFAULT CHARSET=binary;

分析上述建表语句，创建了一个名为test_index 的数据库表格，然后在a、b、c三列上建立了联合索引，索引名字为union_index，而最左原则指的就是当你建立了这样一个索引的时候，等于建立了(a)、 (a,b)、 (a,b,c)三个索引，通过条件 (a), (a,b), (a,b,c) 这三种条件查询的时候都可以利用索引加快速度，所以在建立索引的时候要把最常用的条件列放到联合索引的最左边，接下来我们来验证一下，工具就是mysql自带的explain命令。

测试版本

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

验证过程

1. 首先以列a作为条件查询数据，我们看到 type: ref 表示引用查找， key_len: 4 表示索引长度为4，也就说明利用了索引来进行查找

   mysql> explain select data from test_index where a = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
   possible_keys: union_index
             key: union_index
         key_len: 4
             ref: const
            rows: 70
        filtered: 100.00
           Extra: NULL
   1 row in set, 1 warning (0.01 sec)

2. 然后以列b作为条件查询数据，可以看到type: ALL表示全表查找， key_len: NULL 表示没有索引，也就说明如果只使用b作为查询条件，不能利用索引来加快查找速度

   mysql> explain select data from test_index where b = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ALL
   possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 716173
        filtered: 10.00
           Extra: Using where
   1 row in set, 1 warning (0.00 sec)

3. 接着以列c作为条件查询数据，可以看到type: ALL表示全表查找， key_len: NULL 表示没有索引，情况与用b作为条件一样，只使用c作为查询条件也不能利用索引来加快查找速度

   mysql> explain select data from test_index where c = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ALL
   possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 716173
        filtered: 10.00
           Extra: Using where
   1 row in set, 1 warning (0.00 sec)

4. 现在来测一下使用a、b作为条件的情况，我们看到 type: ref 表示引用查找， key_len: 8 表示索引长度为8，也就是说我们利用上了a、b联合索引来进行查找

   mysql> explain select data from test_index where a = 1 and b = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
   possible_keys: union_index
             key: union_index
         key_len: 8
             ref: const,const
            rows: 1
        filtered: 100.00
           Extra: NULL
   1 row in set, 1 warning (0.00 sec)

5. 紧接着来测一下使用a、c作为条件的情况，我们看到 type: ref 表示引用查找， key_len: 4 表示索引长度为4，这就奇怪了，按照最左原则来说，a、c上是不会建立索引的，为什么会有索引长度呢？其实与a、b上的索引一比较我们就能发现，a、c上的索引长度只有4，而且单独的c上是没有索引的，所以4字节长度的索引只能是a上的，也就是说这种情况我们只使用了a列上的索引来进行查找

   mysql> explain select data from test_index where a = 1 and c = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
   possible_keys: union_index
             key: union_index
         key_len: 4
             ref: const
            rows: 70
        filtered: 10.00
           Extra: Using index condition
   1 row in set, 1 warning (0.00 sec)

6. 为了进一步验证上面的想法，这一次测一下使用b、c作为条件的情况，我们看到 type: ALL 表示全表查找， key_len: NULL 表示没有索引可以使用，按照最左原则来说，b列上没有索引，c列上也没有索引，同时b、c的上也不存在联合索引，所以使用b、c作为查询条件时无法利用联合索引

   mysql> explain select data from test_index where b = 1 and c = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ALL
   possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 716173
        filtered: 1.00
           Extra: Using where
   1 row in set, 1 warning (0.00 sec)

7. 测试完两个条件的情况，接下来测试一下使用a、b、c作为条件的情况，我们看到 type: ref 表示引用查找， key_len: 12 表示索引长度为12，这完全符合联合索引的最左原则，同时使用3个条件查询可以利用联合索引

   mysql> explain select data from test_index where a = 1 and b = 1 and c = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
   possible_keys: union_index
             key: union_index
         key_len: 12
             ref: const,const,const
            rows: 1
        filtered: 100.00
           Extra: NULL
   1 row in set, 1 warning (0.00 sec)

8. 测试进行到现在，我测试了所有的情况吗？不是的！还可以颠倒顺序啊，我原来一直以为联合索引是有顺序的，结果测试后才发现，利用索引的条件符合“交换律”，也就是下面这种情况也能利用a、b上的联合索引，索引长度为8

   mysql> explain select data from test_index where b = 1 and a = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
   possible_keys: union_index
             key: union_index
         key_len: 8
             ref: const,const
            rows: 1
        filtered: 100.00
           Extra: NULL
   1 row in set, 1 warning (0.00 sec)

9. 再来试试这种情况，按照最左原则，c上没有建立索引，a上有索引，c、a没有建立联合索引，所以只能使用a上的索引进行查找，结果索引长度只有4，验证了我们的想法，联合查询条件使用索引时满足“交换律”

   mysql> explain select data from test_index where c = 1 and a = 1\G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
   possible_keys: union_index
             key: union_index
         key_len: 4
             ref: const
            rows: 70
        filtered: 10.00
           Extra: Using index condition
   1 row in set, 1 warning (0.00 sec)

10. 接下来几种交换顺序的情况(c,b)、(a,c,b)、(c,b,a)等，大家可以自己进行验证，到此为止，mysql联合索引的最左原则也就验证结束了！

总结

1. 联合索引的最左原则就是建立索引KEY union_index (a,b,c)时，等于建立了(a)、(a,b)、(a,b,c)三个索引，从形式上看就是索引向左侧聚集，所以叫做最左原则，因此最常用的条件应该放到联合索引的组左侧。

2. 利用联合索引加速查询时，联合查询条件符合“交换律”，也就是where a = 1 and b = 1 等价于 where b = 1 and a = 1，这两种写法都能利用索引KEY union_index (a,b,c)。

3. 遇到这种不确定的问题还是需要实际测试一下，简单的调整一下索引顺序可能会极大的提升效率哦！