MySQL-联合索引

 

　　联合索引

 

　　所谓最左原则指的就是如果SQL语句中用到了联合索引中额最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，需要注意的是，当遇到范围查询（>、<、between、like）就会停止匹配。

　　假设，我们对（a，b）字段建立一个索引，也就是说 where条件后为 a = 1 and b = 2 是可以匹配索引的。但是要注意的是 执行 b = 2 and a = 1也是能匹配到索引的，因为 MySQL 有优化器会自动调整 a，b 的顺序与索引顺序一致。

　　相反的，如果执行 b = 2 就匹配不到索引了。

　　而对（a，b，c，d）建立索引，where后条件为

　　　　a = 1 and b = 2 and c > 3 and d = 4

　　那么a，b，c 三个字段可以用到索引，而 d 就匹配不到了，因为遇到了范围查询，停止索引匹配。

　　假设，我们对（a，b）字段建立索引，那么如下图

 

　　如图所示他们是按照 a来进行排序，在 a相等的情况下，才按 b来排序。

　　因此，我们可以看到 a 是有序的 1，1，2，2，3，3。而 b 是一种全局无序，局部相对有序状态。

　　从局部来看，当 a 的值确定的时候，b 是有序的。例如 a = 1时，b 值为 1，2 是有序的状态。当a = 2时，b 的值为 1，4 也是有序状态。

　　因此，当执行 a = 1 and b = 2 是a，b字段能用到索引的。而 执行 a > 1 and b = 2时，a 字段可以用到索引，b 字段不能用到索引。因为 a 的值此时是一个范围，不是固定的，在这个范围内 b 值不是有序的，因此 b 字段上用不到索引。

　　综上所述，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

　　查询条件顺序与联合索引不一致时，sql优化器会调整为一致的。

 

 

　　SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

　　如何新建索引？

　　如果此时回答对（a，b，c）建立索引，那都可以回去等通知了。

　　正确的答法应该是（a，b，c）或者（b，c，a）或者（c，a，b）都可以，重点是要将区分度高的字段放在前面，区分度低的字段放在后面。类似于性别，状态这种字段区分度就很低，一般放在后面。

　　假设区分度由大到小为 b，a，c。那么我们就对（b，a，c）建立索引。在执行 sql 的时候，优化器会帮我们调整 where 后 a，b，c 的顺序，让我们使用上索引。

 

　　SELECT * FROM table WHERE a > 1 and b = 2

　　如何建立索引？

　　如果回答对（a，b）建立索引，那基本上就凉凉了。

　　正解为对（b，a）建立索引。如果建立（a，b）索引，那么只有 a字段能用得上索引，毕竟最左匹配原则遇到范围查询就会停止匹配。如果对（b，a）建立索引那么两个字段都能用的上，优化器会帮我们调整 where 后 a，b的顺序，让我们用上索引。

 

　　SELECT * FROM table WHERE a = 1 ORDER BY b;

　　如何建立索引？

　　使用（a，b）建索引，当 a = 1 的时候，b相对有序，可以避免再次排序。

 

　　SELECT * FROM table WHERE a > 1 ORDER BY b;

　　如何建立索引？

　　对（a）建立索引，因为 a的值是一个范围，这个范围对 b 值是无序的，没必要对（a，b）建立索引。