SQL的查询条件顺序对性能和语法的影响

SQL的查询条件顺序对性能和语法的影响

首先我们创建一个表TABLE1，包含有A、B、C三个字段，如下：
(不指定任何的主外键和索引的情况)
CREATE TABLE TABLE1(
A INT,
B VARCHAR(5),
C VARCHAR(6)
)
往表中插入数据
insert into TABLE1
values(1,'1',5)
insert into TABLE1
values(2,'1',15)
insert into TABLE1
values(3,'7','文字')
insert into TABLE1
values(4,'1','60')
insert into TABLE1
values(5,'1','25')

执行
SELECT * FROM TABLE1
得到如下结果：
1 1 5
2 1 15
3 7 文字
4 1 60
5 1 25

由于我们的C列的内容由数字型字符和文字型字符组成
当执行
SELECT * FROM TABLE1 WHERE A>=1 AND B='1' AND C<60
语句时，根据SQLServer执行的顺序
会是
1、SELECT * FROM TABLE1 WHERE A>=1
1 1 5
2 1 15
3 7 文字
4 1 60
5 1 25
2、将“1”的结果再加上 AND B='1' 去过滤
1 1 5
2 1 15
4 1 60
5 1 25
3、最后再加上 AND C<60 去过滤
1 1 5
2 1 15
5 1 25

这样的结果很符合我们的要求，如果我们将查询条件的位置做一下调换如：
SELECT * FROM TABLE1 WHERE C<60 and A>=1 AND B='1'
这时将会出错，错误提示如下：
("服务器: 消息 245，级别 16，状态 1，行 1 将 varchar 值 '文字' 转换为数据类型为 int 的列时发生语法错误。")

这是因为我们的C列中的内容既包含了数字又包含了文字，在将C<60做为第一个查询条件时将会先用 C<60做为过滤的条件，此时
因为C列的数据中是包括了文字的，所以就会出现转换类型出错。
这样的情况也不难理解，跟前边分步执行的方式去思考也就明白。

我们再换一种方式
将A列设置为此表的主键或者为A列创建一个索引。

此时我们再来执行
SELECT * FROM TABLE1 WHERE A>=1 AND B='1' AND C<60

此时查询分析器便会提示出错，原因仍然是由于C列的类型转换问题。

可以通过
USE sise_mis
GO
SET SHOWPLAN_ALL ON
GO
SELECT * FROM TABLE1 WHERE A>=1 AND B='1' AND C<60
GO
SET SHOWPLAN_ALL OFF
GO

来查看查询语句的分析执行过程,通过查看执行的过程你可以发现，C<60被提到了最前边执行，因为创建了索引，并且索引被使用,从而
导致了查询条件顺序发生了变化。

可以通过两种方式来解决此类问题:

1、为 C 列设置上索引，来改变查询条件的顺序

2、为表设置强制索引来改变查询的条件的顺序
如：select * from table1 with(index(indexname)) WHERE A>=1 AND B='1' AND C<60
（强制索引我未能在Hibernate中找到相应的可配置或者HQL的语法，有发现的麻烦给予指导）

最后，类似本例C列比较数值型的大小或者说范围的可以使用like来完成相应的条件查询，则不存在类型转换问题
如 C<60 或以用 (c like '[1-5]_' or c like '[0-9]')这个查询条件来代替。


索引：
检查列中唯一数据值的数量，并与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型是什么。