sql性能优化及实战

sql性能优化及实战

写sql大家关注什么？ 实现（扩大）业务需求 取出/写入正确的数据 简便、快速完成开发 性能 …

首先要满足业务需求 其次提高sql语句的性能 兼顾sql语句涉及的内存问题

首先写出满足业务需求语句，其次要提高sql语句性能，。 sql语句的性能主要看执行语句（实现相同的功能和相同的记录）所用的时间，次要的是内存效率（若语句执行就导致内存不足影响系统上其它进程对内存资源的使用也是要考虑优化的）。 包含提高主要包含先选择后投影，先选择后连接，用空间来换时间，索引的使用（要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引），用小结果集驱动大结果集，及其它注意事项。

写sql我关注什么？ 逻辑读 sql使用的频率 Select / update 的字段是不是必需的（就是投影，尽量少用’’来查询所有字段，那样会引起内存的浪费），也就是任何地方都不要使用 select from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。 对象的大小及增长速度 过滤字段的数据分布情况 查询的数据范围 能使用尽量少的表就尽量使用少的表。

写sql我关注什么？ Sql的join方式 使用索引 使用全表扫描 是不是要建索引 在WHERE 语句中，尽量避免对索引字段进行计算操作，不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。DATE_FORMAT(CREATE_DATE,‘%Y-%m-%d’) >= ‘startDate’； 低效 STR_TO_DATE(ecd.​​​CREATE_DATE​​​, ‘%Y-%m-%d’) = STR_TO_DATE(t.​​DATE​​​, ‘%Y-%m-%d’) 高效 ecd.​​​CREATE_DATE​​​ >= STR_TO_DATE(t.​​DATE​​​, ‘%Y-%m-%d’) AND ecd.​​​CREATE_DATE​​​ < STR_TO_DATE(ADDDATE(t.​​DATE​​​, INTERVAL 1 DAY), ‘%Y-%m-%d’) 这样看似多写了语句，现得啰嗦，实际上他能使索引生效，速度比前者提高一倍。实现功能要使用对字段格式化或为了其它更高的效率，有时候也会采用对字段进行函数处理。必定需求优先，要有取舍。 在可以使用UNION ALL的语句里，使用了UNION UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。注意：UNION ALL经常会产生重复记录，但是通常由于脚本的特殊性，一般都会保证连接后的记录唯一性和消除重复记录。若不能保证自己结果的正确性和唯一性，那不可以乱套它。 如部分代码：SELECT DATE_FORMAT(aaa.DATE, ‘%Y-%m-%d’) AS 日期, MAX(aaa.a) AS 进店人数, MAX(aaa.b) AS 领取优惠券人数, MAX(aaa.c) AS 浏览拍品（含标品）人数, MAX(aaa.d) AS 出价人数, MAX(aaa.e) AS 成交单数, MAX(aaa.f) AS 拍品付款单数, MAX(aaa.g) AS 标品付款单数 就能保证结果的唯一性和正确性。

我们网站和数据库是什么样子的 网站是一个OLTP系统 ➔数据库要支持大量并发 ➔每次请求数据库的时间要求非常短 这就对sql常用语句的性能有很大的要求。有的人为了简化语句和减少多表操作就设计出一百多个字段的大表，但是这样就造成内存的大量浪费。其最好的处理是根据业务分解出高频率出现和业务相关的有意义的表，在常用字段上建立索引。多表操作是都用索引关联。

需求和业务设计实现层面55% 合理的需求和不错的设计，需求确认和评审的时候dba应该参加 sql语句层面30% 在需求范围内的调整sql语句和调整表的索引 数据库层面 调整参数 硬件层面 升级或者增加硬件

count(*) 、count(1) (速度最快。前两者速度基本无差别) count(column)（多了一步投影操作，当然慢了。优化一个操作，最好的方式是不做这个操作） …where rownum < :1 order by … 分页中的order by字段的值不唯一 避免在column上使用函数，尽量把字段上使用的函数转接到传入参数或常量上使用函数 date = :date(需要写成to_date(:date)) 不要使用标准sql的join写法(left join,right join…)，ORACLE有自己专用左右连接的语法，mysql可以忽视这条。 or与and共用且没有括号，不加容易误解。 （not）Exists 尽量改用join

避免在column上使用函数，尽量把字段上使用的函数转接到传入参数或常量上使用函数 SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

高并发低消耗的查询总是比低并发高消耗的查询更重要 选择最有效的过滤条件（和索引），在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。 select必要的column（尤其内层查询），外层没有用到的字段不投影 永远用小结果集驱动大结果集，就是处理查询出来的结果集比较小的（内层语句）后处理结果集比较大的（最外层语句）。 Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 优化一个操作，最好的方式是不做这个操作（不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0） 尽可能在索引中就完成排序 oltp下尽可能都用绑定变量 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差

（not）Exists 尽量改用join，可以看到可以通过连表直接查询出结果，为何增加一个（not）Exists 处理呢？优化一个操作，最好的方式是不做这个操作 低效

SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’); 更高效 SELECT DISTINCT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO(+) AND DEPT_CAT = ‘A’ and d.dept_no is not null; 注意：当EMP和DEPT 的记录是一对多的关系时，后者查询出来的会有重复记录所以要过去重复数据

以看到可以通过连表直接查询出结果，为何增加一个（not）Exists 处理呢？优化一个操作，最好的方式是不做这个操作 低效 他是由三个操作组成，WHERE 后先是选择后判断，再选择判断，外层是选择 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND　DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 他是由连个操作组成，WHERE 后先是连表选择后判断，外层是选择。可以看到他少一个选择判断的处理。 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)

SELECT COUNT(*)，SUM(SAL) FROM　EMP WHERE DEPT_NO = 0020 AND ENAME LIKE　‘SMITH%’; SELECT COUNT(*)，SUM(SAL) FROM　EMP WHERE DEPT_NO = 0030 AND ENAME LIKE　‘SMITH%’; 用DECODE函数高效地得到相同结果 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_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%’; 这个是Oracle的函数，mysql略过。

删除重复记录： 最高效的删除重复记录方法 (因为使用了ROWID) DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。 可以使用表链接代替or 。 select id from t where num=10 or Name = ‘admin’ 可以这样查询： select id from t where num = 10 union all select id from t where Name = ‘admi 注意：这样的处理有可能产生重复记录，sql语句要结合场景保证自己记录的正确性和唯一性。这里只是说明后者的效率高，并不是说它在那种场景都正确。

用IN来替换OR 这是一条简单易记的规则，但是实际的执行效果还须检验，在 ORACLE8i下，两者的执行路径似乎是相同的．

低效:SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20OR LOC_ID = 30 高效SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

可以看到当一个字段为多个非连续的常数时，用IN效率很高

in 和 not in 也要慎用，否则会导致全表扫描。避免在WHERE子句中使用in，not in或者having。 可以使用 exist 和not exist代替 in和not in。 Having可以用where代替，如果无法代替可以分两步处理。 使用EXISTS(或 NOTEXISTS)通常将提高查询的效率.在子查询中,NOT IN子句将执行一个内部的排序和合并. 低效 select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 很多时候用 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)

低效： 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 DEPT.LOC = ‘MELB’)

用EXISTS替换DISTINCT： 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在 SELECT子句中使用DISTINCT.

一般可以考虑用EXIST 替换,EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.例子： (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO =E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT „X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

用>=替代>

高效:SELECT * FROM EMP WHERE DEPTNO >=4 低效:SELECT * FROM EMP WHERE DEPTNO >3两者的区别在于, 前者 DBMS 将直接跳到第一个 DEPT等于 4 的记录而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个DEPT大于3的记录。

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = ‘admin’ 可以这样查询：

select id from t where num = 10 union all select id from t where Name = ‘admin’

用>=替代> 高效: SELECT * FROM EMP WHERE DEPTNO >=4

低效: SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于, 前者 DBMS 将直接跳到第一个 DEPT等于 4 的记录而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个DEPT大于3的记录。

1.避免SQL中出现隐式类型转换 当某一张表中的索引字段在作为where条件的时候，如果进行了隐式类型转换，则此索引字段将会不被识别，因 为隐式类型转换也属于计算，所以此时DBMS会使用全表扫面 eu.EVENT_ID = ‘1’ AND eu.TYPE = 4 如EVENT_ID为数字类型，TYPE为字符类型，这样使用就进行了隐式类型转换 字符串隐式转换为日期格式：DATE <= ‘2018-04-13 23:59:59，先转换为日期更高效：DATE < STR_TO_DATE(‘2018-04-14’,’%Y.%m.%d’ ) 2.防止检索范围过宽 使用like运算符的时候，“a%”将会使用索引，而“a%c”和“%a”则会使用全表扫描，因此“a%c”和“%a”不能被有效的评估匹配的数量。 3.为了提高sql的性能，尽量不要使用正则表达式。因为正则表达式会使索引无效，导致全表查询。

低效 SELECT t2.* FROM

(SELECT t1.*, ROWNUM rnum FROM (SELECT a.*, b.receive_fee FROM beyond_trade_base a, beyond_trade_process b WHERE a.trade_no = b.trade_no(+) AND a.seller_account = :1 AND a.gmt_create >= TO_DATE (:2, ‘yyyy-mm-dd’) AND a.gmt_create < TO_DATE (:3, ‘yyyy-mm-dd’) ORDER BY a.gmt_create DESC) t1 WHERE ROWNUM <= :4) t2 WHERE rnum >= :5 先左连接后选择

高效 SELECT /+ ordered use_nl(a,b) / a.*, b.receive_fee

FROM (SELECT t2.* FROM (SELECT t1.*, ROWNUM rnum FROM (SELECT t.* FROM beyond_trade_base t WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') ORDER BY gmt_create DESC) t1 WHERE ROWNUM <= :4) t2 WHERE rnum >= :5) a, beyond_trade_process b

WHERE a.trade_no = b.trade_no(+) 先选择后左连接＝LEFT JOIN

​

注意：由于 mysql的各种语句执行客户端对连续执行的语句进行了优化，所以多次执行后所用的时间不太准确，第一次执行的时间比较准确，后面重复执行的时间由于优化一般时间都比第一次短。计算sql语句时要以第一次执行的时间为准。

以看到第一种方案操作最多，比第二，第三方案多了一个子查询。这个子查询有点多余，可以通过第二，第三方案进行优化。由于步骤少，后两者应该执行时间短。

通过实际执行，实际结果是第一方案和第二方案执行时间几乎一致，第三种方案是前两种方案执行时间的二倍。应该是mysql对语句进行了优化，才导致前两种方案没

有想像的大，反而是前两种方案的执行时间基本一致，第一种方案相对于第二，第三种方案代码冗余太大。第三种方案和第二种方案影响效率的是用：

STR_TO_DATE(ul.​​CREATE_DATE​​​, ‘%Y-%m-%d’) = STR_TO_DATE(t.​​DATE​​, ‘%Y-%m-%d’) 代替了

ul.​​CREATE_DATE​​​ >= STR_TO_DATE(t.​​DATE​​, ‘%Y-%m-%d’)

AND ul.​​CREATE_DATE​​​ < STR_TO_DATE(ADDDATE(t.​​DATE​​, INTERVAL 1 DAY), ‘%Y-%m-%d’)。因为“=“左边也对数据库的字段进行了函数处理，导致索引

失效，进而造成执行时间增加了一倍，可以看到虽然方案二看着代码有点啰嗦，但是他的执行效率比方案三要高的多。

连接效率高于EXISTS，EXISTS高于IN，特别是大表操作时，后两者会导致语句执行时间过久进而请求超时。从现网执行前三种方案的语句直接导致，请求超时无法

查询，而方案四语句执行正常。从查询分析上看，方案四只进行两步查询操作，简单投影，只涉及两个表，索引正常。方案一和方案二，在测试环境执行时间18.6秒，

现网环境由于执行时间太长，导致无法查询出结果。方案三在在测试环境执行时间38.4秒，现网环境由于执行时间太长，导致无法查询出结果。第四种方案测试环境

执行时间0.38秒，现网环境执行时间0.38秒。可见执行时间比第一种，第二种方案提高了近50倍。虽然本该是索引字段的地方使用了函数：

GROUP BY DATE(ul.​​CREATE_DATE​​​) 。”=”左边对数据库字段使用了函数：DATE(cd.​​CREATE_DATE​​​) = DATE(ul.​​CREATE_DATE​​)。虽然这些影响了查询的性

能。但是他相对减少一个日期表，对查询语句简单化，查询结果简单化而是值得的。可以看到影响性能的语句并非不能用，而要看它的使用能否对整体语句性能有提

升，慎用并不是不能用。要以需求为中心，其次要准确，再次要考虑性能。像第前三种方案由于性能问题，导致结果查不出来，需求都不能满足，何谈其它

注意事项：SELECT STR_TO_DATE(‘2018-04-04’, ‘%Y-%m-%d %h:%i:%s’);得到的是2018-01-04 00:00:00 ，SELECT STR_TO_DATE(‘2018-01-04 00:00:00’, ‘%Y-%m-%d %h:%i:%s’)得到的是NULL，而SELECT DATE(‘2018-01-04 00:00:00’) 得到的是2018-01-04，只要是日 期， DATE得到的就是日期，不存在空的问题。 由于领优惠卷记录表d表和进店记录表ul表不是一对一的关系，是一对多或多对多的关系。用户可以一天今天多次，但是可能只领了一张优惠券，他们的连接表会有重复记录，所以要去掉重复记录的用户ID.这个就是连接代替EXISTS需要注意的事项。 当要把查询出的结果倒入到表格时把日期转化为字符串，不然表格中显示不出来。如：SELECT DATE_FORMAT(aaa.DATE, ‘%Y-%m-%d’) AS 日期。不能这样写：SELECT aaa.DATE AS 日期

优化后性能提升50倍的完整代码：

SELECT DATE_FORMAT(aaa.DATE, '%Y-%m-%d') AS 日期, MAX(aaa.a) AS 进店人数, MAX(aaa.b) AS 领取优惠券人数, MAX(aaa.c) AS 浏览拍品（含标品）人数, MAX(aaa.d) AS 出价人数, MAX(aaa.e) AS 成交单数, MAX(aaa.f) AS 拍品付款单数, MAX(aaa.g) AS 标品付款单数 FROM(SELECT DATE(ul.`CREATE_DATE`) AS DATE , COUNT(DISTINCT ul.`USER_ID`) AS a, 0 AS b, 0 AS c, 0 AS d, 0 AS e, 0 AS f, 0 AS gFROM ulWHERE ul.`STATE`= 'U' AND DATE(ul.`CREATE_DATE`) >= DATE('startDate') AND DATE(ul.`CREATE_DATE`) < DATE(ADDDATE('endDate', INTERVAL 1 DAY))GROUP BY DATE(ul.`CREATE_DATE`) UNION ALLSELECT DATE(ul.`CREATE_DATE`) , 0 AS a, COUNT(DISTINCT cd.`USER_ID`) AS b, 0 AS c, 0 AS d, 0 AS e, 0 AS f, 0 AS gFROM cd, ulWHERE cd.`USER_ID` = ul.`USER_ID` AND DATE(cd.`CREATE_DATE`) = DATE(ul.`CREATE_DATE`) AND cd.`STATE`= 'U' AND ul.`STATE`= 'U' AND cd.`CREATE_DATE` >= DATE('startDate') AND cd.`CREATE_DATE`< DATE(ADDDATE('endDate', INTERVAL 1 DAY))GROUP BY DATE(ul.`CREATE_DATE`) UNION ALLSELECT DATE(ul.`CREATE_DATE`), 0 AS a, 0 AS b, COUNT(DISTINCT et.`USER_ID`) AS c, 0 AS d, 0 AS e, 0 AS f, 0 AS g FROM (SELECT track_event.`CREATE_DATE`,track.`USER_ID` FROM track_event, track WHERE track_event.TRACK_ID = track.TRACK_ID AND (category = 'auction' OR category = 'gift') AND action = 'view' AND track_event.STATE = 'U' AND track_event.`CREATE_DATE` >= DATE('startDate') AND track_event.`CREATE_DATE`< DATE(ADDDATE('endDate', INTERVAL 1 DAY))) et, ul WHERE et.`USER_ID` = ul.`USER_ID` AND ul.`STATE`= 'U' AND DATE(et.`CREATE_DATE`) = DATE(ul.`CREATE_DATE`) AND et.`CREATE_DATE` >= DATE('startDate') AND et.`CREATE_DATE`< DATE(ADDDATE('endDate', INTERVAL 1 DAY))GROUP BY DATE(ul.`CREATE_DATE`) UNION ALLSELECT DATE(ul.`CREATE_DATE`), 0 AS a, 0 AS b, 0 AS c, COUNT(DISTINCT ad.`USER_ID`) AS d, COUNT(DISTINCT ad.`AUCTION_ID`) AS e, 0 AS f, 0 AS gFROM ad, ul WHERE ad.`USER_ID` = ul.`USER_ID` AND DATE(ad.`CREATE_DATE`) = DATE(ul.`CREATE_DATE`) AND ad.`STATE`= 'U' AND ul.`STATE`= 'U' AND ad.`CREATE_DATE` >= DATE('startDate') AND ad.`CREATE_DATE`< DATE(ADDDATE('endDate', INTERVAL 1 DAY))GROUP BY DATE(ul.`CREATE_DATE`) UNION ALLSELECT DATE(ul.`CREATE_DATE`), 0 AS a, 0 AS b, 0 AS c, 0 AS d, 0 AS e, COUNT(1) AS f, 0 AS g FROM td, ulWHERE td.`USER_ID` = ul.`USER_ID` AND DATE(td.`CREATE_DATE`) = DATE(ul.`CREATE_DATE`) AND td.`STATE`= 'U' AND ul.`STATE`= 'U' AND td.`TRADE_TYPE` = 'A' AND td.`PAY_DATE` IS NOT NULL AND td.`CREATE_DATE` >= DATE('startDate') AND td.`CREATE_DATE`< DATE(ADDDATE('endDate', INTERVAL 1 DAY))GROUP BY DATE(ul.`CREATE_DATE`) UNION ALLSELECT DATE(ul.`CREATE_DATE`), 0 AS a, 0 AS b, 0 AS c, 0 AS d, 0 AS e, 0 AS f, COUNT(1) AS gFROM td, ulWHERE td.`USER_ID` = ul.`USER_ID` AND DATE(td.`CREATE_DATE`) = DATE(ul.`CREATE_DATE`) AND td.`STATE`= 'U' AND ul.`STATE`= 'U' AND td.`TRADE_TYPE` = 'G' AND td.`PAY_DATE` IS NOT NULL AND td.`CREATE_DATE` >= DATE('startDate') AND td.`CREATE_DATE`< DATE(ADDDATE('endDate', INTERVAL 1 DAY))GROUP BY DATE(ul.`CREATE_DATE`) ) AS aaa GROUP BY DATE ORDER BY DATE ASC

pptx文件下载地址：（sql性能优化及实例） 这个下载地址是《sql性能优化分享》的后期修改与补充。下载新的，别载老的了，别重复下载！！！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。