为什么mysql innodDB中组合索引中范围查询后的条件索引会失效

mysql innodDB中组合索引中范围查询后的条件索引会失效的原因：范围查询操作时MySQL需要对组合索引中的非范围查询条件逐个进行检查，由于范围查询操作不连续，会超过MySQL的最大行数限制，导致MySQL放弃使用这个组合索引。

一、mysql innodDB中组合索引中范围查询后的条件索引会失效的原因

表建立联合索引(a,b,c)，查询条件a=1,b>2,c=3，此时c条件的索引会失效。简单点来说，该组合索引是一个以a字段排序而b与c相对有序的B+树，引擎可以通过二分查找定位到a=1的数据，b在a=1确定得情况下是有序的（所以b的有序是相对的），依然可以通过二分查找取出所有b大于2的数据，但这些数据的b字段可能有很多个不同的值，所以c字段是无序的，无法用二分查找来查询c=3的数据，故c用不到索引。

当进行范围查询操作时，MySQL需要对组合索引中的非范围查询条件逐个进行检查，以过滤掉不符合要求的记录，直至扫描到范围查询条件时，由于范围查询操作具有不连续的特点，因此这个范围查询条件之后的记录数据量会变得非常大，通常会超过MySQL的最大行数限制，这就导致了MySQL放弃使用这个组合索引，而改为全表扫描（full table scan）来获取结果。这也是为什么范围查询操作容易导致MySQL查询性能降低的原因之一。

例如存在索引数据如：(a=1,b=3,c=3)，(a=1,b=4,c=0)，(a=1,b=4,c=3)，(a=1,b=4,c=0)。上述数据均满足a=1&b>2，但是c的数据却不是连续的，所以没有办法通过c索引进行过滤，所以字段c并不能够参与到索引刷选中。

二、组合索引

1、组合索引的使用

本文中组合索引的定义为（MySQL）：

ALTER TABLE table_name ADD INDEX (col1,col2,col3);

2、组合索引的本质

当创建(col1,col2,col3)组合索引时，相当于创建了(col)单列索引，(clo1,clo2)组合索引以及(col1,col2,col3)组合索引想要索引生效，只能使用col1和col1,col2和col1,col2,col3三种组合；当然，col1,col3组合也可以，但实际上只用到了col1的索引，col3并没有用到。

3、示例

组合索引相当于一个按照姓氏——名字的一个电话簿，只能先确定姓氏才可以命中索引，下列可以正确命中组合索引的语句（ = 和IN直接的字段都可以乱序，MySQL的查询优化器可以优化成索引识别的形式）

使用到索引的情况如下：

col_a = "some value"

col_a = "some value" and col_b = "some value"

col_a = "some value" and col_c = "some value"

col_a = "some value" and col_b = "some value" and col_c = "some value"

col_a = "some value" and col_c = "some value" and col_b = "some value"

col_b = "some value" and col_a = "some value" and col_c = "some value"

col_b = "some value" and col_c = "some value" and col_a = "some value"

col_c = "some value" and col_a = "some value" and col_b = "some value"

col_c = "some value" and col_b = "some value" and col_a = "some value"

未使用索引的情况：

col_b = "some value"

col_c = "some value"

col_b = "some value" and col_c = "some value"

col_c = "some value" and col_b = "some value"

总结，组合索引从最左开始组合，只要包含名列前茅列的查询都会用到该组合索引。

4、为什么要使用组合索引

• 减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用组合索引会大大的减少开销。
• 覆盖索引：对组合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
• 效率高：索引列越多，通过索引筛选出的数据越少。

5、SQL中组合索引和普通索引区别

组合索引中列的顺序非常重要，从左原则：a,ab,ba,abc。

单个索引：一个一个起作用，也就是说有三个单个索引，哪个条件查询在前哪个起作用，其他不起作用。

结论：

一般来说，列表搜索需要多个列查询，此时就可以使用联合索引，都是and的关系。什么时候需要创建索引：

• where条件会经常出现的，并且当前表的数量比较大。
• where条件中是用and而非or的时候。
• 组合索引比单个索引更适合，因为索引占用一定磁盘空间，也就说明有一定的开销，如果多个单个索引比较多，那么多资源的浪费也比较多，组合索引相当于对多个列建索引，并且只建一次，and条件下非常适合。

延伸阅读1：MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是较好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。