?

? SqlServer之like、charindex、patindex

1、環(huán)境介紹
測(cè)試環(huán)境 SQL2005
測(cè)試數(shù)據(jù) 200W條

2、環(huán)境準(zhǔn)備
2.1建表
CREATE TABLE [dbo].[Depratments](
??????? [Dep_id] [int] NOT NULL,
??????? [Dep_name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
2.2創(chuàng)建數(shù)據(jù)
create procedure ins_Depratments
as
??????? declare @n int;
??????? declare @title varchar(30);
??????? set @n =1;
??????? set @title='';
begin
??????? while @n<2000000
??????? begin
?????????????? -- set @title = (select case when (cast(floor(rand() * 6) as int)) =5 then '部門經(jīng)理' else '職員'end);
??????????????? insert into Depratments (Dep_id,Dep_name) values (@n,'開發(fā)'+CAST(@n as varchar)) ;
?????????????? -- insert into employees values (@n,'劉備'+CAST(@n as varchar),'男',@title,
?????????????????????? 78000,'11110333x'+CAST(@n as varchar),@n,getdate());
?????????????? set @n=@n+1;
??????? end
end
2.3執(zhí)行??????? exec ins_Depratments
?
3、場(chǎng)景
3.1前后都有百分號(hào)的查詢
SET STATISTICS IO ON
set statistics time ON
go
select count(*) from depratments where Dep_name like '%開發(fā)1000%';
go?
select count(*) from depratments where charindex('開發(fā)1000',Dep_name)>0;
go
select count(*) from depratments where patindex('%開發(fā)1000%',Dep_name)>0;
go
無索引的情況 charindex > patindex > like
??????? CPU 時(shí)間 = 4391 毫秒，占用時(shí)間 = 5322 毫秒。
??????? CPU 時(shí)間 = 3812 毫秒，占用時(shí)間 = 4690 毫秒。
??????? CPU 時(shí)間 = 4047 毫秒，占用時(shí)間 = 5124 毫秒。
帶索引的情況 charindex > patindex > like
?????? CPU 時(shí)間 = 4297 毫秒，占用時(shí)間 = 4535 毫秒。
?????? CPU 時(shí)間 = 3844 毫秒，占用時(shí)間 = 4024 毫秒。
????? ?CPU 時(shí)間 = 4219 毫秒，占用時(shí)間 = 4351 毫秒。
結(jié)論：
當(dāng)前后都使用百分號(hào)的情況(%string%)，①charindex性能稍微好點(diǎn)，like、patindex性能相近；②索引在這種情況中失效 ?

3.2百分號(hào)在后面的查詢
SET STATISTICS IO ON
set statistics time ON
go
select count(*) from depratments where Dep_name like '開發(fā)1000%';
go
select count(*) from depratments where charindex('開發(fā)1000',Dep_name)>0;
go
select count(*) from depratments where patindex('開發(fā)1000%',Dep_name)>0;
go
?
無索引的情況 patindex > like > charindex
??????? CPU 時(shí)間 = 844 毫秒，占用時(shí)間 = 1465 毫秒。
??????? CPU 時(shí)間 = 3875 毫秒，占用時(shí)間 = 3914 毫秒。
??????? CPU 時(shí)間 = 968 毫秒，占用時(shí)間 = 969 毫秒。
?
帶索引的情況 like > patindex > charindex
??????? CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 18 毫秒
??????? CPU 時(shí)間 = 3766 毫秒，占用時(shí)間 = 4026 毫秒。
??????? CPU 時(shí)間 = 937 毫秒，占用時(shí)間 = 983 毫秒。
結(jié)論：
無索引的情況，patindex的性能最佳，是charindex性能的4倍
帶索引的情況，like的性能最佳
?
總結(jié)：
①索引只適用于百分號(hào)在后面的情況(string%)
②在前后都是百分號(hào)的情況下charindex 的性能最佳
③百分號(hào)在后面的查詢，無索引的情況，patindex的性能最佳

?

?

?

?

?

SqlServer之like、charindex、patindex