btree索引生效原则

btree索引的常见误区

• 错误1

现在有一个商品表，有cat_id类别字段，price价格字段。

假设我们给cat_id和price分别加上各自的所有，那么当我们使用sql：

select * from goods where cat_id = 3 and price > 100;//查询第3个栏目,100元以上的商品

这句sql只能用上cat_id或price索引,因为它们两是独立的索引,同时只能用上1个。

• 错误2：在多列上建立一个索引，查询哪个列，索引都将发挥作用

这是错误的，在多列索引上，如果需要索引发挥作用，需要满足左前缀要求。

以 index(a,b,c) 为例,(注意和顺序有关)

where a=2 可以用到索引where a=1 and b=2 可以用到索引where a=1 and b=2 and c=3 可以用到索引where b=1 / c=1 不能用到索引where a=1 and c=1 a可以发挥索引，c不能使用到索引where a=1 and b>10 and c=1 a可以发挥索引，b也可以发挥索引，c不能发挥索引where a=1 and b like 'xxx%' and c=1 a可以发挥索引，b可以发挥索引，c不能发挥索引

联合索引的注意点

假设有一个联合索引：index(a,b,c)，如果使用如下查询：

select * from t1 where a=3 and b>10, and c=7;

如果联合索引的某个部分使用模糊查询Like，范围查询>、<等，那么这个部分可以使用到该索引，但是其后面的部分就不能使用到该索引了。
这个sql语句中：a,b部分可以使用到索引，而c就不能使用到索引了。

经典面试题

题目：假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分的是哪几个？

A：where c1=x and c2=x and c4>x and c3=x
B ：where c1=x and c2=x and c4=x order by c3
C ：where c1=x and c4= x group by c3,c2
D ：where c1=x and c5=x order by c2,c3
E ：where c1=x and c2=x and c5=? order by c2,c3

分别对5个选项进行测试，创建测试表：

• A选项

where c1=x and c2=x and c4>x and c3=x 和 where c1=x and c2=x and c3=x and c4>x 是一样的，索引可以用到c1,c2,c3,c4部分。

possible_keys：可能使用到的索引
key：真正使用到的索引
key_len：所有使用到的长度，这里的4表示用到索引字段的字节数，又因为表中的字段都是tinyint型，都只占用一个字节，所有可以推断c1,c2,c3,c4都使用到了索引，才为4

• B选项

key_len只有两个字节，是不是仅仅认为c1,c2使用到了索引，答案是否定的。通常来说，没有使用到索引的字段是要用到外部文件进行排序的，恰恰是因为c3使用到了索引，所有才不需要利用外部文件进行排序。

我们来看一下常规排序，这里的Extra中多了一个Using filesort，表示where c1=x and c2=x and c4=x order by c5; 这条sql中c5没有使用到索引，索引才会使用外部文件进行排序，我们在回到Ｂ选项，可以看到没有使用到Using filesort，索引就表明c3也使用到了索引。

• C选项

这里只有c1使用到了索引，因为group by c3,c2和group by c2,c3不是一个概念，所以C选项中c2,c3没有使用到索引，这里的Extra中存在Using temporary和Using filesort表示分组的时候需要用到临时表，分组的时候也要进行排序。这又佐证了c2,c3并没有使用到索引，如果c2,c3使用到了索引，就不会有temporary和Using filesort了。

• D选项

c1,c2,c3可以使用到索引，c2和c3是先使用到了索引，因为索引是有序的，所有order by的时候就没有出现Using filesort了。

• E选项

key_len：表示c1,c2使用到了索引

由于c2=1,那么order by中的c2就可以删除了，因为就一种值了，那么sql语句就变为：
group by c3

由于c2使用到了索引，所以c3也可以使用到索引，那么就会进行排序，那么order by的使用就不用Using filesort进行排序了。