SELECT
a.*
FROM
[Function] a
where
not exists (
select FunID from RelFunPakageFun b
where FunPakageID = '786D25CB-8E2E-4F2D-A893-808F1AC24AE5' and a.FunID = b.FunID
)
SELECT
*
FROM
[Function] a
where
not exists (
select * from RelFunPakageFun b
where FunPakageID = '786D25CB-8E2E-4F2D-A893-808F1AC24AE5' and a.FunID = b.FunID
)
结论:not exists 内是否使用*号对于性能没有影响
select * from Tenant
select
[TenantID]
,[FullName]
,[ShortName]
,[AdminID]
,[Status]
,[CreateTime]
,[Creater]
,[EditTime]
,[Editor]
from Tenant
结论:*与写明各列性能相差不大,在列不是多得离谱的情况下
SELECT
a.*
FROM
[Function] a
where
not exists (
select FunID from RelFunPakageFun b
where FunPakageID = '786D25CB-8E2E-4F2D-A893-808F1AC24AE5' and a.FunID = b.FunID
)
SELECT
*
FROM
[Function]
where
FunID not in (
select FunID from RelFunPakageFun
where FunPakageID = '786D25CB-8E2E-4F2D-A893-808F1AC24AE5'
)
结论:exists相比in有更高的索引利用率,但实际效率相差并不大,除非海量数据否则用户不会有等待时间上的差别
select COUNT(*) from tbTest
select COUNT(tid) from tbTest
select COUNT(tint) from tbTest
select COUNT(tname) from tbTest
select COUNT(tinfo) from tbTest
tid唯一索引,tint无索引,tname不唯一索引,tinfo无索引
tin int,tin int,tname varchar(50),tinfo varchar(50)
结论:count时*与唯一索引速度相同,速度与类型无关,不唯一索引慢于唯一索引,无索引最慢。count应使用*,避免写的不是唯一索引