关于SQL查询效率,100w数据,查询只要1秒 | 您所在的位置:网站首页 › sql查询执行时间 › 关于SQL查询效率,100w数据,查询只要1秒 |
1.关于SQL查询效率,100w数据,查询只要1秒,与您分享:机器情况p4: 2.4内存: 1 Gos: 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 intdeclare @nick varchar(10)set @i = 1while @i='2005-11-30' and createdate 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_TIMEFROM TBL_SMS_MOWHERE 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_TIMEFROM TBL_SMS_MOWHERE 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,这就是差别。 |
CopyRight 2018-2019 实验室设备网 版权所有 |