关于索引失效和联合索引

理解最左前缀原则，可以帮助我们避免索引失效。

索引失效

查询条件包含or

当or左右查询字段只有一个是索引，该索引失效，explain执行计划key=null；只有当or左右查询字段均为索引时，才会生效；

组合索引，不是使用第一列索引，索引失效

如果select * from key1=1 and key2= 2;建立组合索引（key1，key2）;
select * from key1 = 1;组合索引有效；
select * from key1 = 1 and key2= 2;组合索引有效；
select * from key2 = 2;组合索引失效；不符合最左前缀原则

like 以%开头

使用like模糊查询，当%在前缀时，索引失效；

如何列类型是字符串，where时一定用引号括起来，否则索引失效

当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

最左前缀原则

建立以下sql：

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;

测试的mysql版本是 5.7.

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

explain select data from test_index where a = 1
*************************** 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)

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

explain select data from test_index where b = 1
*************************** 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)

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

explain select data from test_index where c = 1
*************************** 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)

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

explain select data from test_index where a = 1 and b = 1
*************************** 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)

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

explain select data from test_index where a = 1 and c = 1
*************************** 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)

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

explain select data from test_index where b = 1 and c = 1
*************************** 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)

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

explain select data from test_index where a = 1 and b = 1 and c = 1
*************************** 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)

下面这种情况也能利用a、b上的联合索引，索引长度为8

explain select data from test_index where b = 1 and a = 1
*************************** 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)

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

explain select data from test_index where c = 1 and a = 1
*************************** 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)

联合索引总结

联合索引的最左原则就是建立索引KEY union_index (a,b,c)时，等于建立了(a)、(a,b)、(a,b,c)三个索引，从形式上看就是索引向左侧聚集，所以叫做最左原则，因此最常用的条件应该放到联合索引的组左侧。
**对于"="和"in"可以乱序。**利用联合索引加速查询时，联合查询条件符合“交换律”，也就是where a = 1 and b = 1 等价于 where b = 1 and a = 1。这归功于mysql查询优化器，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。
mysql会一直向右匹配直到遇到范围查询(<,>,between,like)就停止匹配。比如a=3 and b=4 and c>5 and d=6，如果建立(a,b,c,d)顺序的索引，d是用不到索引的。如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

对于最左匹配原则的理解

mysql索引最左匹配原则的理解?–沈杰的回答

其实我觉得只要理解一点就是，只要有最左边的索引元素，那么这个索引结构一定是按照最左索引元素排序的，后序的索引元素也是依赖于最左元素之后才有可能变得有意义。