SQL 中判断条件的先后顺序，会引起索引失效么？，java 虚拟机的原理
select * from dbo.workflow
where flowid = 1 and flowamount = 1
模拟 b=1 and a=1 的查询
select * from dbo.workflow
where flowamount = 1 and flowid = 1
可以看到，当表新建，还没有数据时，优化器根本不会去判断用不用索引，而是直接全表扫描。反正就一个数据页。
当我们加点数据时，再看看反应：
这里不得不再提下 tally table 的用法，实在看不下去利用循环来生成测试数据的方法
DECLARE @BEGIN DATETIME = '2010-01-01'
,@END DATETIME = '2017-10-30'
DECLARE @INC INT ;
SELECT @INC = DATEDIFF(DAY,@BEGIN,@END)
; WITH
L0 AS (
SELECT * FROM (VALUES(1),(2),(3)) AS T(C) )
, L1 AS (
SELECT a.C,b.C AS BC FROM L0 AS a cross join L0 AS b )
, L2 AS (
SELECT a.C,b.C AS BC FROM L1 AS a cross join L1 AS b )
, L3 AS (
SELECT a.C,b.C AS BC FROM L2 AS a cross join L2 AS b )
, L4 AS (
SELECT a.C,b.C AS BC FROM L3 AS a cross join L3 AS b )
, L5 AS (
SELECT a.C,b.C AS BC FROM L4 AS a cross join L4 AS b )
insert into dbo.workflow (flowid,flowamount,flowcount)
SELECT TOP 50000 RNK , RNK * 10, RNK + 20
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RNK
FROM L5
) M
此时表里有 5 万条数据，再看上面两条查询的执行计划：
这儿模拟题目中的 idx(b,a) 索引结构
create index idx_amt_id on dbo.workflow(flowamount,flowid)
模拟 a=1 and b=1 的查询
select * from dbo.workflow
where flowid = 1 and flowamount = 1
模拟 b=1 and a=1 的查询
select * from dbo.workflow
where flowamount = 1 and flowid = 1
很明显，都会走索引 idx(b,a) 这种模式，与 b 在前和 a 在前无关。优化器可以优化这部分表达式的重组。
但，是不是所有条件表达式都没有先后顺序要求呢？肯定不是
只有在相等条件判断时，先后顺序不重要，一旦有表达式用于非等判断，顺序就很重要了，如下：
select * from dbo.workflow
where flowamount > 39 and flowid = 1
select * from dbo.workflow
where flowid = 1 and flowamount > 39
这里优化器提示（绿色字体部分），建立一个相等判断条件的索引在前，非等判断字段在后的索引 (flowid,flowamount)。所以本质上，索引结构中字段先后不受制于查询中相等判断条件表达式字段的顺序，而受制于非等条件判断表达式。即非等判断字段（flowamount>39）需要放在相等判断字段(flowid=1)的后面。
create index idx_id_amtr on dbo.workflow(flowid,flowamount)
select * from dbo.workflow
where flowamount > 39 and flowid = 1
select * from dbo.workflow
where flowid = 1 and flowamount > 39
再看两者的执行计划：
评论