执行数据库查询时，通常查询条件是多对个属性进行判断和约束，对于这种类型的查询，如果存在多个索引则使用多个索引，或者使用建立在多属性搜索码上的索引，这样能提高查询效率。
一、使用多个单码索引
假设数据表 instructor 有两个单码索引，分别建立在 dept_name 和 salary 上。为了找到金融系中工资为 80000 的所有老师，使用以下 SQL：
select ID
from instructor
where dept_name = "Finance" and salary = 80000;
可以使用以下几种查询策略：
1、使用 dept_name 上的索引，找出属于金融系的所有记录，再检查每条记录是否满足 salary = 80000。
2、使用 salary 上的索引，找出所有工资为 80000 的记录，再检查每条记录是否满足 dept_name = "Finance"。
3、利用 dept_name 上的索引找出指向属于金融系的记录的所有指针。同样利用 salary 上的索引找出指向工资等于 80000 的记录的所有指针。两个指针集合的交集，即为所有满足查询条件的记录的所有指针。
三种策略只有第三种利用了存在的多种索引的优势，但是在以下条件下也可能是糟糕的选择：
-
属于金融系的记录太多
-
工资为 80000 的记录太多
-
属于金融系且工资为 80000 的记录只有几个
因为为了得到一个很小的结果集，必须扫描大量指针，策略的执行效果取决于索引属性值的分布。
二、多码索引（联合索引）
1、最左前缀匹配规则
为了解决上面的问题，一个可行的方案是在复合的搜索码（dept_name, salary）上建立和使用索引，这就是联合索引。
联合索引有个规则，叫最左前缀匹配规则，即 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。
比如对于SQL：
select ID
from instructor
where dept_name = "Finance" and salary = 80000;
select ID
from instructor
where dept_name = "Finance";
都是可以匹配索引的，查询条件一个是（dept_name, salary），一个是（dept_name），复合最左前缀匹配规则。
但是对下面的查询是不匹配的：
where salary = 80000;
因为前面的查询条件没有 dept_name，联合索引最左边的属性 dept_name 没有匹配到，就不会对后面的属性 salary 使用索引。
对下面的查询也是可以匹配到索引的
where salary = 80000 and dept_name = "Finance";
因为数据库有优化器会自动调整 salary、dept_name 的顺序与索引顺序一致。
遇到范围查询，会停止对后面属性的索引匹配，比如建立索引（a, b, c, d），where 后条件为
a = 1 and b = 2 and c > 3 and d = 4
那么，a,b,c三个字段能用到索引，而d就匹配不到。因为遇到了范围查询，但是如果把索引改成（a, b, d, c）则又可以匹配了，因为数据库优化器会自动把查询条件的属性顺序调整为
a = 1 and b = 2 and d = 4 and c > 3
2、数据结构
假设，我们对(a,b)字段建立索引，那么入下图所示
对于联合索引（a, b），先按 a 进行排序，相同的 a 内部才按 b 进行排序，对于整个 B+ 树来说，a 在其中是有序的，按照前序遍历的顺序，上图中各个树节点 a 的值分别为 1, 1, 2, 2, 2, 3, 3。
而 b 是一种全局无需，局部有序的状态，即相同的 a 内部的有序。同样按照前序遍历的顺序，各个结点 b 的值分别为 1, 2, 1, 4, 4, 1, 2。因此对于 b = 2 这种查询条件是没办法使用索引的。
只有当 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 字段用不上索引。
所以，根据最左前缀匹配原则，在遇到范围查询时，就会停止匹配。
3、实战
题型一
如果sql为
SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;
如何建立索引?
如果此题回答为对(a,b,c)建立索引，那都可以回去等通知了。 此题正确答法是，(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。
例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候，优化器会 帮我们调整where后a,b,c的顺序，让我们用上索引。
题型二
如果sql为
SELECT * FROM table WHERE a > 1 and b = 2;
如何建立索引?
如果此题回答为对(a,b)建立索引，那都可以回去等通知了。 此题正确答法是，对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。 如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。
题型三
如果sql为
SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;
如何建立索引? 此题回答也是不一定，(b,a)或者(b,c)都可以，要结合具体情况具体分析。
拓展一下
SELECT * FROM `table` WHERE a = 1 and b = 2 and c > 3;
怎么建索引？嗯，大家一定都懂了！
(a, b, c) 或 (b, a, c) 根据区分度决定 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)建立索引。
拓展一下
SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
怎么建索引?
(a, b, c) 或 (b, a, c) 根据区分度决定 a 前还是 b 前。
题型五
SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;
如何建立索引？
还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!
网友评论