SQL语句执行效率及分析(note)
in和exists的区别与SQL执行效率分析
in和exists的区别与SQL执行效率分析本文对in和exists的区别与SQL执行效率进行了全面整理分析……最近很多论坛又开始讨论in和exists的区别与SQL执行效率的问题，本文特整理一些in和exists的区别与SQL执行效率分析SQL中in可以分为三类：1、形如select * from t1 where f1 in ('a','b')，应该和以下两种比较效率select * from t1 where f1='a' or f1='b'或者select * from t1 where f1 ='a' union all select * from t1 f1='b'你可能指的不是这一类，这里不做讨论。
2、形如select * from t1 where f1 in (select f1 from t2 where t2.fx='x')，其中子查询的where里的条件不受外层查询的影响，这类查询一般情况下，自动优化会转成exist语句，也就是效率和exist一样。
3、形如select * from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx)，其中子查询的where里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般认为效率不如exists。
除了第一类in语句都是可以转化成exists 语句的SQL，一般编程习惯应该是用exists而不用in，而很少去考虑in和exists的执行效率.in和exists的SQL执行效率分析A，B两个表，(1)当只显示一个表的数据如A，关系条件只一个如ID时，使用IN更快：select * from A where id in (select id from B)(2)当只显示一个表的数据如A，关系条件不只一个如ID，col1时，使用IN就不方便了，可以使用EXISTS：select * from Awhere exists (select 1 from B where id = A.id and col1 = A.col1)(3)当只显示两个表的数据时，使用IN，EXISTS都不合适，要使用连接：select * from A left join B on id = A.id所以使用何种方式，要根据要求来定。
SQL查询语句使用rand（）的执行效率与优化
SQL查询语句使⽤rand（）的执⾏效率与优化若要在i ≤ R ≤ j这个范围得到⼀个随机整数R，需要⽤到表达式 FLOOR(i + RAND() * (j – i + 1))。
例如，若要在7 到 12 的范围（包括7和12）内得到⼀个随机整数, 可使⽤以下语句：SELECT FLOOR(7 + (RAND() * 6));从 Mysql 表中随机读取数据不难，⽅法还挺多的，但是如果要考虑效率，得到⼀个快速的⾼效率的⽅法，那就不是⼀件简单的事情了（⾄少对我来说不简单）。
随机获得Mysql数据表的⼀条或多条记录有很多⽅法，下⾯我就以users（userId，userName，password……）表（有⼀百多万条记录）为例，对⽐讲解下⼏个⽅法效率问题：1. select * from users order by rand() LIMIT 1执⾏该sql语句，⽼半天没有反应，最后被迫⼿动停⽌执⾏，怎个伤⼈了得啊！后来我查了⼀下MYSQL⼿册，⾥⾯针对RAND()的提⽰⼤概意思就是，在 ORDER BY从句⾥⾯不能使⽤RAND()函数，因为这样会导致数据列被多次扫描，导致效率相当相当的低！效率不⾏，切忌使⽤！2. SELECT * FROM users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users)) * RAND() +(SELECT MIN(userId) FROM users) LIMIT 1执⾏该sql语句，⽤时0.039s，效率太给⼒了！接着我就把”LIMIT 1“改为了”LIMIT 10000“，⽤时0.063s。
经过多次验证，结果肯定是随机的！结论：随机取⼀条或多条记录，⽅法都不错！3. 通过sql获得最⼤值和最⼩值，然后通过php的rand⽣成⼀个随机数randnum，再通过SELECT * FROM users WHERE userId >=randnum LIMIT 1，获得⼀条记录效率应该还可以，多条应该就不⾏了。
高效SQL语句5篇
高效SQL语句5篇第一篇：高效SQL语句1.SELECT子句中避免使用“*”当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL 列引用…*‟是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中, 会将“*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.2.使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如:Sql代码1.SELECT COUNT(*)，SUM(SAL)FROM EMP WHERE DEPT_NO = 0020 ANDENAME LIKE …SMITH%‟;2.SELECT COUNT(*)，SUM(SAL)FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE …SMITH%‟;SELECT COUNT(*)，SUM(SAL)FROM EMP WHERE DEPT_NO = 0020 ANDENAME LIKE …SMITH%‟;SELECT COUNT(*)，SUM(SAL)FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE …SMITH%‟;你可以用DECODE函数高效地得到相同结果:Sql代码1.SELECT COUNT(DECODE(DEPT_NO,0020,‟X ‟,NULL))D0020_COUNT,2.COUNT(DECODE(DEPT_NO,0030,‟X ‟,NULL))D0030_COUNT,3.SUM(DECODE(DEPT_NO,0020,SAL,NUL L))D0020_SAL,4.SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030 _SAL5.FROM EMP WHERE ENAME LIKE …SMITH%‟;SELECT COUNT(DECODE(DEPT_NO,0020,‟X ‟,NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,‟X ‟,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SA L FROM EMP WHERE ENAME LIKE …SMITH%‟;类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.3.删除重复记录最高效的删除重复记录方法(因为使用了ROWID)Sql代码1.DELETE FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);DELETE FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);4.用TRUNCATE替代DELETE当删除表中的记录时，在通常情况下，回滚段(rollback segments)用来存放可以被恢复的信息，如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)，而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.5.计算记录条数和一般的观点相反, count(*)比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的.例如 COUNT(EMPNO)6.用Where子句替换HAVING子句避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、总计等操作，如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销, 例如: Sql代码1.--低效2.SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!= …SYDNEY‟AND REGION!= …PERTH‟3.--高效4.SELECT REGION，AVG(LOG_SIZE)FROMLOCATION WHERE REGION REGION!= …SYDNEY‟ ND REGION!= …PERTH‟ GROUP BYREGION--低效SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!= …SYDNEY‟AND REGION!= …PERTH‟--高效SELECT REGION，AVG(LOG_SIZE)FROMLOCATION WHERE REGION REGION!= …SYDNEY‟ ND REGION!= …PERTH‟ GROUP BY REGION7.用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.Sql代码1.--低效2.SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = …MELB‟)3.--高效:4.SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS(SELECT …X‟FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LO C = …MELB‟)--低效SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = …MELB‟) --高效:SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS(SELECT …X‟FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = …MELB‟)8.用NOT EXISTS替代NOT IN在子查询中，NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下，NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例如:SELECT …FROM EMPWHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=‟A‟);Sql代码1.--为了提高效率改写为:(方法一: 高效)SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)= …A‟2.--(方法二: 最高效)SELECT ….FROM EMP E WHERE NOT EXISTS(SELECT …X‟FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = …A‟);3.--为了提高效率改写为:(方法一: 高效)SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO =B.DEPT(+)AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)= …A‟4.--(方法二: 最高效)SELECT ….FROM EMP E WHERE NOT EXISTS(SELECT …X‟FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = …A‟);9.用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换例如: Sql代码1.--低效:2.SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO3.--高效:4.SELECT DEPT_NO,DEPT_NAMEFROM DEPT D WHERE EXISTS(SELECT …X‟FROM EMP E WHERE E.DEPT_NO =D.DEPT_NO);5.--EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.--低效:SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO--高效:SELECT DEPT_NO,DEPT_NAMEFROM DEPT D WHERE EXISTS(SELECT …X‟FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);--EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.10.用索引提高效率索引是表的一个概念部分，用来提高检索数据的效率，实际上ORACLE使用了一个复杂的自平衡B-tree结构，通常通过索引查询数据比全表扫描要快，当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引，同样在联结多个表时使用索引也可以提高效率，另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证，除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列.通常, 在大型表中使用索引特别有效.当然,你也会发现, 在扫描小表时,使用索引同样能提高效率，虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O，因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢注：定期的重构索引是有必要的.11.避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描.举例：Sql代码1.--低效：2.SELECT …FROM DEPT WHERE SAL * 12 > 25000;3.--高效:4.SELECT … FROM DEPT WHERE SAL> 25000/12;--低效：SELECT …FROM DEPT WHERE SAL * 12 > 25000;--高效:SELECT … FROM DEPT WHERE SAL> 25000/12;12.用>=替代>Sql代码1.--如果DEPTNO上有一个索引2.--高效：SELECT *FROM EMPWHERE DEPTNO >=43.--低效：SELECT *FROM EMPWHERE DEPTNO >3--如果DEPTNO上有一个索引 4.--高效：SELECT *FROM EMPWHERE DEPTNO >=45.--低效：SELECT *FROM EMPWHERE DEPTNO >3两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.第二篇：高效的SQL语句如何写高效率的SQL语句、Where子句中的连接顺序：ORACLE采用自下而上的顺序解析WHERE子句。
SQL执行效率分析
很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。
比如：select * from table1 where name='zhangsan' and tID > 10000和执行:select * from table1 where tID > 10000 and name='zhangsan'一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。
事实上，这样的担心是不必要的。
SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。
虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。
在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。
如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。
SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。
形式如下：列名操作符<常数或变量>或<常数或变量> 操作符列名列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。
如：Name=‟张三‟价格>50005000<价格Name=‟张三‟ and 价格>5000如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。
SQL语句执行效率及分析
SQL语句执行效率及分析2.SQL提高查询效率2018-05-12 21:201.对查询进行优化，应尽量幸免全表扫描，第一应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量幸免在 where 子句中对字段进行 null 值判定，否则将导致引擎舍弃使用索引而进行全表扫描，如：select id from t where num is null能够在num上设置默认值0，确保表中num列没有null值，然后如此查询：select id from t where num=03.应尽量幸免在 where 子句中使用!=或<>操作符，否则将引擎舍弃使用索引而进行全表扫描。
4.应尽量幸免在 where 子句中使用 or 来连接条件，否则将导致引擎舍弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20能够如此查询：select id from t where num=10union allselect id from t where num=205.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)关于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 36.下面的查询也将导致全表扫描：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=@num8.应尽量幸免在 where 子句中对字段进行表达式操作，这将导致引擎舍弃使用索引而进行全表扫描。
项目中优化sql语句执行效率的方法
项目中优化sql语句执行效率的方法在项目开发中，优化SQL语句执行效率是非常重要的一项工作。
优化SQL语句可以提高数据库的性能，减少系统的响应时间，提高用户体验。
下面是一些优化SQL语句执行效率的方法：1. 使用索引索引是数据库中提高查询效率的重要手段。
在查询语句中使用索引可以大大提高查询速度。
在创建表时，可以根据表的特点和查询需求创建相应的索引。
在查询语句中，使用WHERE子句可以利用索引进行快速查询。
2. 避免使用SELECT *SELECT *会查询表中的所有列，包括不需要的列。
这样会增加查询的时间和资源消耗。
在查询语句中，只查询需要的列，可以减少查询时间和资源消耗。
3. 使用JOIN优化查询JOIN是连接两个或多个表的操作。
在使用JOIN时，应该尽量避免使用子查询，因为子查询会增加查询的时间和资源消耗。
可以使用JOIN 语句将多个表连接起来，减少查询时间和资源消耗。
4. 避免使用LIKELIKE是模糊查询，可以查询包含指定字符串的记录。
但是，LIKE查询会增加查询的时间和资源消耗。
在查询语句中，应该尽量避免使用LIKE，可以使用全文检索等方法代替。
5. 使用EXPLAIN分析查询语句EXPLAIN是MySQL中的一个命令，可以分析查询语句的执行计划。
通过分析执行计划，可以找到查询语句中的瓶颈，优化查询语句，提高查询效率。
6. 避免使用ORDER BYORDER BY是对查询结果进行排序的操作。
在查询语句中，应该尽量避免使用ORDER BY，因为排序会增加查询的时间和资源消耗。
如果需要排序，可以在查询语句中使用LIMIT子句限制查询结果的数量，减少排序的时间和资源消耗。
7. 使用缓存缓存是提高查询效率的重要手段。
在查询语句中，可以使用缓存来缓存查询结果，减少查询时间和资源消耗。
可以使用Memcached等缓存工具来实现缓存。
总之，优化SQL语句执行效率是提高数据库性能的重要手段。
在项目开发中，应该根据实际情况选择合适的优化方法，提高查询效率，提高用户体验。
SQL性能优化详解
SQL性能优化详解SQL是一种用于管理关系数据库的语言，对于大型数据库系统来说，SQL性能优化是至关重要的，因为它可以显著提高数据库查询和操作的效率。
本文将详细讨论SQL性能优化的几个关键方面。
1.优化查询语句查询语句是SQL性能优化的一个核心方面。
以下是几种优化查询语句的方法：-使用索引：索引是一种数据结构，可以大大加快查询操作。
在频繁的查询字段上创建索引，可以显著提高查询性能。
-减少查询行数：只查询需要的行数，可以减少查询的时间。
使用LIMIT关键字可以限制结果集的大小。
-避免使用SELECT*：只选择需要的字段，避免选择多余字段的开销。
-使用合适的JOIN：使用INNERJOIN、OUTERJOIN等合适的JOIN类型可以提高查询的效率。
2.优化数据库结构数据库结构的设计也会对SQL查询的性能产生影响。
以下是几种优化数据库结构的方法：-分解大表：将大表拆分为多个小表，可以减少查询的范围，提高查询的性能。
-正规化与反规范化：正规化是将数据库设计为多个表，以保持数据的一致性和完整性。
但是，对于查询频繁的场景，可以考虑使用反规范化来提高查询性能。
-使用合适的数据类型：选择合适的数据类型可以减少存储空间和提高查询效率。
例如，使用整型代替字符串型可以减少存储空间，并且比较整型比较字符串效率更高。
3.优化索引索引是提高查询性能的重要手段。
以下是几种优化索引的方法：-创建索引：在查询频繁的字段上创建索引，可以加速查询操作。
但是，创建过多的索引也会降低插入和更新的性能。
-使用复合索引：如果在多个字段上经常进行查询操作，可以创建复合索引，以减少索引的数量和提高查询性能。
-避免过长的索引名：过长的索引名可能导致索引占用较大的存储空间，降低性能。
4.缓存机制缓存机制可以减少查询频繁的数据的数据库访问次数，从而提高查询性能。
以下是几种缓存机制：-查询缓存：在数据库中缓存查询结果，下次查询相同的结果时，可以直接从缓存中获取，减少数据库的访问次数。
SQL语句执行原理及性能优化
图 １冬 季 工 况 下 迎 面 风 速 的 变 化 与 空 调 热 回 收 效 率 的 关 系 由上 图关 系 曲线可 以看 出 ， 当转 轮 的厚 度 为 ０ ． ０ ５米 曲线 。 时， 在转速小于 ｌ ５时 ， 全 热 回 收 和 显 热 回 收 的 回 收 效 率 随 着转 速 的不 断增 加 而 形 成 较快 的增 长 ， 而在 转 速 大 于 １ ５ 时， 全 热 回收 和 显 热 回 收 的 回 收 效 率 随 着 转 速 的 增 加 趋 势 空 调 比较 缓 慢 ， 而且全 热 回收 和显 热 回收 的 热 回收 效率 呈现 出 热 ｌ 川 了基 本 相 同 的 变 化 趋 势 。
程 序 因 处 理 的 数 据 量 过 大而 造 成 机 器死 机 的情 况 时 有 发 生 。 因 此 ， 如何 有 效地提 高 Ｓ ＱＬ语 句 的 执 行 效 率 ， 优化 Ｓ ＱＬ语 句 的性 能， 越 来越 成 为 开 发 人 员 关 心 的 重要 课 题 。
关键 词 ： Ｓ ＱＬ； 执 行原理 ； 性 能优 化 中图分类号 ： ＴＢ 文献 标 识 码 ： Ａ 文章编 号 ： １ ６ ７ ２ — ３ １ ９ ８ （ ２ ０ １ ３ ） ０ ５ — ０ １ ８ ６ ． ０ ２
收 效
璋￡
（ ％）
３ 结 语
随 着 目前 全 世 界 范 围 内 的 能 源 形 势 的 不 断 紧 张 ， 进 行 空 调 系 统 热 回收 的 开 发 和 研 究 以 及 空 调 热 回 收 技 术 的 应 用 研 究 是 非 常 的有 必 要 的 ， 其本 质是 废气 的利 用 ， 这 是 进 行 建 筑物 节能的重要 手段 和有 效措 施 ， 对 于 我 国 能 源 供 应 压 力 的减 小 以 及 能 源 的 充 分 利 用 和 节 约 具 有 重 要 意 义 。 本 文 首 先 对 于 空 调 热 回 收 系 统 以 及 空 调 系 统 的 热 回 收 节 能 做 了 详 细 的 阐述 ， 在此基础上 ， 着 重 讨 论 了 空 调 热 回 收 系 统 节 能 中 热 回 收 效 率 的影 响 因 素 ， 主 要 包 括 空 调 回 风 量 和 风 管 漏 风 对 热 回收 效 率 的 影 响 、 建 筑 物 维 护 结 构 的 密 封 性 对 热 回 收 效 率 的影 响 以 及 空 调 热 回 收装 置 本 身 对 空 调 热 回 收 的影 响 三个方 面， 通过对这些 因素的分 析研 究 ， 有 助 于 在 提 高 空 调 系统热 回收效率 的同 时， 实 现 空 调 热 回 收 系 统 的 科 学 合 理 配置 ， 对于实际工程 中空调热 回收装 置 的选用 、 空 调 热 回 收 效 果 的完 善 具 有 重 要 意 义 。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SQL语句执行效率及分析(note)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 intdeclare @k intdeclare @nick varchar(10)set @i = 1while @i<1000000beginset @k = @i % 10set @nick = convert(varchar,@i)insert into t_userinfo(nick,classid,writetime) values(@nick,@k,getdate()) set @i = @i + 1end-- 耗时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 erid,b.nick,b.classid,b.writetime from(select top 20 erid from(select top 900020 userid from t_userinfo order by userid asc ) a order by erid desc) a inner join t_userinfo b on erid = eridorder by erid 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_userinfowhere classid = 1order by userid asc)-- 耗时2 秒-- step 6 where 查询select erid,b.nick,b.classid,b.writetime from(select top 20 erid from(select top 90000 userid from t_userinfowhere classid = 1order by userid asc) a order by erid desc) a inner join t_userinfo b on erid = eridorder by erid asc-- 查询分析器显示不到1 秒.查询效率分析：子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。
在这种情况下可以考虑用联接查询来取代。
如果要用子查询，那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。
因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。
无论在哪种情况下,NOT IN都是最低效的。
因为它对子查询中的表执行了一个全表遍历。
建立合理的索引,避免扫描多余数据，避免表扫描！几百万条数据，照样几十毫秒完成查询.2.SQL提高查询效率2008-05-12 21:201.对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。
2.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=03.应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=205.in 和not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用between 就不要用in 了：select id from t where num between 1 and 36.下面的查询也将导致全表扫描：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=@num8.应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
如：select id from t where num/2=100应改为:select id from t where num=100*29.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
如：select id from t where substring(name,1,3)='abc'--name以abc开头的idselect 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 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
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)14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的select 的效率，但同时也降低了insert 及update 的效率，因为insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16.应尽可能的避免更新clustered 索引数据列，因为clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
若应用系统需要频繁更新clustered 索引数据列，那么需要考虑是否应将该索引建为clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用varchar/nvarchar 代替char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。
如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。
但是，对于一次性事件，最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替create table，避免造成大量log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。