回复4楼: select ... from table with(index(IX_INDEX_1)) where ... and EEE='e' 强制索引我当时也试验过了啊,只要有EEE='e' 条件,虽然走了联合索引index_2,但聚簇索引index_1也走了啊, 是先走index_2后走的index_1,这样的开销同样比只走index_2的大,速度也慢,尤其基表数据量大的情况下更是明显。其实这都不是我关心的主要问题,我没弄明白“为什么加上EEE这个条件后(不强制走索引),就不走联合索引index_2了呢“ 这是我目前一直没有想明白和搞清楚的事情。 ~~
select AAA from tmp_t where BBB='b' and CCC='c' AND DDD='d' and EEE='e' 这个查询一共需要五个字段信息,而index_2只有四个字段的信息,光走index_2是无法完成查询的。现在有两种选择: 1、只使用index_1,因为index_1是聚集索引,其实这种方式是表扫描。 2、先通过index_2获取四个字段的信息,eee的信息通过index_2中aaa的键值定位到表中(就是index_1)寻找,进而完成查询。使用1或2,取决于表中记录和你查询出的记录的比值。如果2的成本比1高就使用1。
where BBB='b' and CCC='c' AND DDD='d'
and EEE='e'
看来优化器还是不可靠的,我比较了一下,如果聚簇索引index_1的开销比联合索引index_2大很多,
在100W数据量的表查询结果集为3W条数据的前提下,速度也慢了一些。优化器没有选出开销比较小的索引去走。如果不加上where中EEE='e'这个条件的话,按2楼的想法是顺序走两个索引,但只走了联合索引index_2,(我觉得优化器
此时的分析是index_2索引的开销少)
但是,如果加上这个EEE的条件,则只会走index_1,不清楚是为什么,还希望2楼或者其他高人可以帮忙分析一下。
(EEE这个字段是经常用来更新的,所以我不想把它作为联合索引列)
select ... from table with(index(IX_INDEX_1)) where ... and EEE='e'
强制索引我当时也试验过了啊,只要有EEE='e' 条件,虽然走了联合索引index_2,但聚簇索引index_1也走了啊,
是先走index_2后走的index_1,这样的开销同样比只走index_2的大,速度也慢,尤其基表数据量大的情况下更是明显。其实这都不是我关心的主要问题,我没弄明白“为什么加上EEE这个条件后(不强制走索引),就不走联合索引index_2了呢“
这是我目前一直没有想明白和搞清楚的事情。 ~~
带有where ... and EEE='e'这个条件的情况下,确实是会索引扫描,聚簇索引扫描
index_1->Clustered Index Scan
EstimateCPU 1.10
EstimateIO 21.78
TotalSubree 22.88where条件中去掉EEE='e'这个条件,会索引查找,只是联合索引查找
index_2->Index Seek
EstimateCPU 0.18
EstimateIO 0.06
TotalSubree 02.4---
还是index_2的开销小,查询速度也快不少。
---
还是不明白为什么带上EEE='e'这个条件就不走index_2而是进行index_1扫描了呢?
如果不加上where中EEE='e'这个条件的话,按2楼的想法是顺序走两个索引,但只走了联合索引index_2,(我觉得优化器
此时的分析是index_2索引的开销少)为什么只走index_2,而不走两个索引。这是因为index_2就包含aaa的信息,不需要再从index_1中寻找aaa的信息了。aaa是聚集索引,所有非聚集索引中都包含aaa的信息,即聚集索引的键值。(当表是堆的时候,非聚集索引通过rid定位到表记录;当表建立聚集索引,非聚集索引通过聚集索引的键定位到表中记录)
问题二、
为什么加了EEE='e'就走index_1。首先你要搞清楚一个前提:索引的键是字段值的副本。你为bbb,ccc,ddd建立了index_2,那么index_2中就包含这三个列的值,当然也包含了aaa的信息,因为aaa是聚集索引的键。(原理参考问题一解答)
select AAA from tmp_t
where BBB='b' and CCC='c' AND DDD='d'
and EEE='e' 这个查询一共需要五个字段信息,而index_2只有四个字段的信息,光走index_2是无法完成查询的。现在有两种选择:
1、只使用index_1,因为index_1是聚集索引,其实这种方式是表扫描。
2、先通过index_2获取四个字段的信息,eee的信息通过index_2中aaa的键值定位到表中(就是index_1)寻找,进而完成查询。使用1或2,取决于表中记录和你查询出的记录的比值。如果2的成本比1高就使用1。