假设存在组合索引it1c1c2(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值。
根据where条件的不同,归纳如下:
1) c1=1 and c2=2:使用索引it1c1c2进行等值查找。
2) c1=1 and c2>2:使用索引it1c1c2进行范围查找,可以有两种方法。
方法1,使用通过索引键(1,2)在B树中命中一条记录,然后向后扫描找出 第一条符合条件的记录,从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于:如果c1=1 and c2=2对应的记录数很多,会产生很多无效的扫描。
3)c1>1 and c2=2:因为索引的第一个列不是等于号的,索引即使后面出现了c2=2,也不能将c2=2应用于索引查找。这里,通过索引键(1,- ∞)在B树中命中一条记录,向后扫描找出第一条符合c1>1的记录,此后的每一条记录判断是否符合c2=2,如果符合则输出,否则过滤掉。这里我们称c2=2没有参与到索引运算中去。这种情况在实际应用中经常出现。
假设索引int1c1(c1)是唯一索引,对于查询语句select c1 from t1 where c1=1,达梦数据库使用索引键(1)命中B树中一条记录,命中之后直接返回该记录(因为是唯一索引,所以最多只能有一条c1=1的记录)。
假设索引it1c2(c2)是非唯一索引,对于查询语句select c2 from t2 where c2=2,达梦数据库使用索引键(2)命中B树中一条记录,返回该记录,并继续向后扫描,如果该记录是满足c=2,返回该记录,继续扫描,直到遇到第一条不符合条件c2=2的记录。
于是,我们可以得知,对于不存在重复值的列,创建唯一索引优于创建非唯一索引。
4.非聚集索引的作用
每张表只可能一个聚集索引,聚集索引用来组织真实数据。语句“create table employee (id int cluster primary key,name varchar(20),addr varchar(20))”。表employee的数据用id来组织。如果要查找id=1000的员工记录,只要用索引键(1000)命中该聚集索引。但是,对于要查找name=’张三’的员工记录就不能使用该索引了,需要进行全表扫描,对于每一条记录判断是否满足name=’张三’,这样会导致查询效率非常低。
要使用聚集索引,必需提供id,我们只能提供name,于是需要引入一个辅助结构实现name到id的转换,这就是非聚集索引的作用。该非聚集索引的键是name,值是id。于是语句“select * from employee where name=’张三’”的执行流程是:通过键(’张三’)命中非聚集索引,得到对应的id值3(假设’张三’对应的id为3),然后用键(3)命中聚集索引,得到相应的记录。
5.是不是使用非聚集索引的查询都需要进行聚集的查询?
不是的,虽然在上一点中查询转换为聚集索引的查找,有时候可以只需要使用非聚集索引。
创建表并创建相应的索引:create table t1(c1 int,c2 int,c3 int);create index it1c2c3 on t1(c2,c3)。查询语句为:select c3 from t1 where c2=1。