1.关于SQL查询效率,100w数据,查询只要1秒,与您分享: 机器情况 p4: 2.4 内存: 1 G os: windows 2003 数据库: ms sql server 2000 目的: 查询性能测试,比较两种查询的性能
SQL查询效率 step by step
-- setp 1. -- 建表 create table t_userinfo ( userid int identity(1,1) primary key nonclustered, nick varchar(50) not null default '', classid int not null default 0, writetime datetime not null default getdate() ) go
-- 建索引 create clustered index ix_userinfo_classid on t_userinfo(classid) go
-- step 2.
declare @i int declare @k int declare @nick varchar(10) set @i = 1 while @i<1000000 begin set @k = @i % 10 set @nick = convert(varchar,@i) insert into t_userinfo(nick,classid,writetime) values(@nick,@k,getdate()) set @i = @i + 1 end -- 耗时 08:27 ,需要耐心等待
-- step 3. select top 20 userid,nick,classid,writetime from t_userinfo where userid not in ( select top 900000 userid from t_userinfo order by userid asc )
-- 耗时 8 秒 ,够长的
-- step 4. select a.userid,b.nick,b.classid,b.writetime from ( select top 20 a.userid from ( select top 900020 userid from t_userinfo order by userid asc ) a order by a.userid desc ) a inner join t_userinfo b on a.userid = b.userid order by a.userid asc
-- 耗时 1 秒,太快了吧,不可以思议
-- step 5 where 查询 select top 20 userid,nick,classid,writetime from t_userinfo where classid = 1 and userid not in ( select top 90000 userid from t_userinfo where classid = 1 order by userid asc ) -- 耗时 2 秒
-- step 6 where 查询 select a.userid,b.nick,b.classid,b.writetime from ( select top 20 a.userid from ( select top 90000 userid from t_userinfo where classid = 1 order by userid asc ) a order by a.userid desc ) a inner join t_userinfo b on a.userid = b.userid order by a.userid asc
建立合理的索引,避免扫描多余数据,避免表扫描! 几百万条数据,照样几十毫秒完成查询. 2. SQL提高查询效率 2008-05-12 21:20 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20
5.in 和 not in 也要慎用,否则会导致全表扫描,如: select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描: select id from t where name like '%abc%' 若要提高效率,可以考虑全文检索。
7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: select id from t where num=@num 可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where num/2=100 应改为: select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)='abc'--name以abc开头的id select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 应改为: select id from t where name like 'abc%' select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
12.不要写一些没有意义的查询,如需要生成一个空表结构: select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(...)
13.很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用下面的语句替换: select num from a where exists(select 1 from b where num=a.num)
提高SQL查询效率(要点与技巧): · 技巧一: 问题类型:ACCESS数据库字段中含有日文片假名或其它不明字符时查询会提示内存溢出。 解决方法:修改查询语句 sql="select * from tablename where column like '%"&word&"%'" 改为 sql="select * from tablename" rs.filter = " column like '%"&word&"%'" =========================================================== 技巧二: 问题类型:如何用简易的办法实现类似百度的多关键词查询(多关键词用空格或其它符号间隔)。 解决方法: '//用空格分割查询字符串 ck=split(word," ") '//得到分割后的数量 sck=UBound(ck) sql="select * tablename where" 在一个字段中查询 For i = 0 To sck SQL = SQL & tempJoinWord & "(" & _ "column like '"&ck(i)&"%')" tempJoinWord = " and " Next 在二个字段中同时查询 For i = 0 To sck SQL = SQL & tempJoinWord & "(" & _ "column like '"&ck(i)&"%' or " & _ "column1 like '"&ck(i)&"%')" tempJoinWord = " and " Next =========================================================== 技巧三:大大提高查询效率的几种技巧
1. 尽量不要使用 or,使用or会引起全表扫描,将大大降低查询效率。 2. 经过实践验证,charindex()并不比前面加%的like更能提高查询效率,并且charindex()会使索引失去作用(指sqlserver数据库) 3. column like '%"&word&"%' 会使索引不起作用 column like '"&word&"%' 会使索引起作用(去掉前面的%符号) (指sqlserver数据库) 4. '%"&word&"%' 与'"&word&"%' 在查询时的区别: 比如你的字段内容为 一个容易受伤的女人 '%"&word&"%' :会通配所有字符串,不论查“受伤”还是查“一个”,都会显示结果。 '"&word&"%' :只通配前面的字符串,例如查“受伤”是没有结果的,只有查“一个”,才会显示结果。 5. 字段提取要按照“需多少、提多少”的原则,避免“select *”,尽量使用“select 字段1,字段2,字段3........”。实践证明:每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。 6. order by按聚集索引列排序效率最高。一个sqlserver数据表只能建立一个聚集索引,一般默认为ID,也可以改为其它的字段。 7. 为你的表建立适当的索引,建立索引可以使你的查询速度提高几十几百倍。(指sqlserver数据库) · 以下是建立索引与不建立索引的一个查询效率分析: Sqlserver索引与查询效率分析。 表 News 字段 Id:自动编号 Title:文章标题 Author:作者 Content:内容 Star:优先级 Addtime:时间 记录:100万条 测试机器:P4 2.8/1G内存/IDE硬盘 ======================================================= 方案1: 主键Id,默认为聚集索引,不建立其它非聚集索引 select * from News where Title like '%"&word&"%' or Author like '%"&word&"%' order by Id desc 从字段Title和Author中模糊检索,按Id排序 查询时间:50秒 ======================================================= 方案2: 主键Id,默认为聚集索引 在Title、Author、Star上建立非聚集索引 select * from News where Title like '"&word&"%' or Author like '"&word&"%' order by Id desc 从字段Title和Author中模糊检索,按Id排序 查询时间:2 - 2.5秒 ======================================================= 方案3: 主键Id,默认为聚集索引 在Title、Author、Star上建立非聚集索引 select * from News where Title like '"&word&"%' or Author like '"&word&"%' order by Star desc 从字段Title和Author中模糊检索,按Star排序 查询时间:2 秒 ======================================================= 方案4: 主键Id,默认为聚集索引 在Title、Author、Star上建立非聚集索引 select * from News where Title like '"&word&"%' or Author like '"&word&"%' 从字段Title和Author中模糊检索,不排序 查询时间:1.8 - 2 秒 ======================================================= 方案5: 主键Id,默认为聚集索引 在Title、Author、Star上建立非聚集索引 select * from News where Title like '"&word&"%' 或 select * from News where Author like '"&word&"%' 从字段Title 或 Author中检索,不排序 查询时间:1秒 · 如何提高SQL语言的查询效率? 问:请问我如何才能提高SQL语言的查询效率呢? 答:这得从头说起: 由于SQL是面向结果而不是面向过程的查询语言,所以一般支持SQL语言的大型关系型数据库都使用一个基于查询成本的优化器,为即时查询提供一个最佳的执行策略。对于优化器,输入是一条查询语句,输出是一个执行策略。 一条SQL查询语句可以有多种执行策略,优化器将估计出全部执行方法中所需时间最少的所谓成本最低的那一种方法。所有优化都是基于用记所使用的查询语句中的where子句,优化器对where子句中的优化主要用搜索参数(Serach Argument)。 搜索参数的核心思想就是数据库使用表中字段的索引来查询数据,而不必直接查询记录中的数据。 带有 =、<、<=、>、>= 等操作符的条件语句可以直接使用索引,如下列是搜索参数: emp_id = "10001" 或 salary > 3000 或 a =1 and c = 7 而下列则不是搜索参数: salary = emp_salary 或 dep_id != 10 或 salary * 12 >= 3000 或 a=1 or c=7 应当尽可能提供一些冗余的搜索参数,使优化器有更多的选择余地。请看以下3种方法: 第一种方法: select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code="01") and (employee.dep_code="01"); 它的搜索分析结果如下: Estimate 2 I/O operations Scan department using primary key for rows where dep_code equals "01" Estimate getting here 1 times Scan employee sequentially Estimate getting here 5 times 第二种方法: select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code="01"); 它的搜索分析结果如下: Estimate 2 I/O operations Scan department using primary key for rows where dep_code equals "01" Estimate getting here 1 times Scan employee sequentially Estimate getting here 5 times 第一种方法与第二种运行效率相同,但第一种方法最好,因为它为优化器提供了更多的选择机会。 第三种方法: select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (employee.dep_code="01"); 这种方法最不好,因为它无法使用索引,也就是无法优化…… 使用SQL语句时应注意以下几点: 1、避免使用不兼容的数据类型。例如,Float和Integer,Char和Varchar,Binary和Long Binary不兼容的。数据类型的不兼容可能使优化器无法执行一些本可以进行的优化操作。例如: select emp_name form employee where salary > 3000; 在此语句中若salary是Float类型的,则优化器很难对其进行优化,因为3000是个整数,我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。 2、尽量不要使用表达式,因它在编绎时是无法得到的,所以SQL只能使用其平均密度来估计将要命中的记录数。 3、避免对搜索参数使用其他的数学操作符。如: select emp_name from employee where salary * 12 > 3000; 应改为: select emp_name from employee where salary > 250; 4、避免使用 != 或 <> 等这样的操作符,因为它会使系统无法使用索引,而只能直接搜索表中的数据。 · ORACAL中的应用 一个1600万数据表--短信上行表TBL_SMS_MO 结构: CREATE TABLE TBL_SMS_MO ( SMS_ID NUMBER, MO_ID VARCHAR2(50), MOBILE VARCHAR2(11), SPNUMBER VARCHAR2(20), MESSAGE VARCHAR2(150), TRADE_CODE VARCHAR2(20), LINK_ID VARCHAR2(50), GATEWAY_ID NUMBER, GATEWAY_PORT NUMBER, MO_TIME DATE DEFAULT SYSDATE ); CREATE INDEX IDX_MO_DATE ON TBL_SMS_MO (MO_TIME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ); CREATE INDEX IDX_MO_MOBILE ON TBL_SMS_MO (MOBILE) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ); 问题:从表中查询某时间段内某手机发送的短消息,如下SQL语句: SELECT MOBILE,MESSAGE,TRADE_CODE,MO_TIME FROM TBL_SMS_MO WHERE MOBILE='130XXXXXXXX' AND MO_TIME BETWEEN TO_DATE('2006-04-01','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2006-04-07','YYYY-MM-DD HH24:MI:SS') ORDER BY MO_TIME DESC 返回结果大约需要10分钟,应用于网页查询,简直难以忍受。 分析: 在PL/SQL Developer,点击“Explain Plan”按钮(或F5键),对SQL进行分析,发现缺省使用的索引是IDX_MO_DATE。问题可能出在这里,因为相对于总数量1600万数据来说,都mobile的数据是很少的,如果使用IDX_MO_MOBILE比较容易锁定数据。 如下优化: SELECT /*+ index(TBL_SMS_MO IDX_MO_MOBILE) */ MOBILE,MESSAGE,TRADE_CODE,MO_TIME FROM TBL_SMS_MO WHERE MOBILE='130XXXXXXXX' AND MO_TIME BETWEEN TO_DATE('2006-04-01','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2006-04-07','YYYY-MM-DD HH24:MI:SS') ORDER BY MO_TIME DESC 测试: 按F8运行这个SQL,哇~... ... 2.360s,这就是差别。