create database factory ;
use factory
go
create table dbo.workflow ( flowid int, flowamount int, flowcount int )
go
先回答第一个问题,判断条件的顺序会影响索引使用吗
这儿模拟题目中的 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
可以看到,当表新建,还没有数据时,优化器根本不会去判断用不用索引,而是直接全表扫描。反正就一个数据页。
当我们加点数据时,再看看反应:
这里不得不再提下 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© )
, 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
小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注Java)
59700)]
[外链图片转存中…(img-bxsJO9aV-1710844659700)]
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注Java)
[外链图片转存中…(img-x5Bc3WD8-1710844659700)]