快乐学习
前程无忧、中华英才非你莫属!
        找回密码
当前位置：同乐学堂 > 大数据 > 正文

Day38-（转载）SQL之优化大全

2017-06-14 分类：大数据 阅读(3904)

来源于：MySQL中文社区（http://www.mysqlpub.com/）

概要

1.    大小写对SQL语句的影响（ORACLE）

2.    尽量使用(NOT) EXISTS 替代( NOT)IN这样的操作

3.    在海量查询时尽量少用格式转换

4.    查询海量数据是，可以使用optimizer hints(ORACLE)

5.    ORDER BY和GROPU BY

6.    对列的操作

7.    避免使用OR

8.    用IN来替换OR

9.    FROM

10.    WHERE

11.    is null或is not null

12.    SELECT子句中避免使用 ‘ * ‘

13.    避免在索引列上使用计算

14.    Order by语句

15.    通配符

16.    避免相关子查询

17.    用Where子句替换HAVING子句(ORACLE)

18.    联合

19.    让MYSQL服务器做的更好

20.    本地缓存数据

21.    索引准则

22.    尽量为所有在WHERE子句中被引用的列建立索引

23.    尽可能的使用唯一索引

24.    利用多列索引

25.    考虑不为某些列建立索引

26.    惯用的一些技巧

27.    尽量避免的事情

28.    表锁定

29.    慎用游标

总结

和设计与调整数据库一样，优化执行SQL语句可以提高应用程序的性能。如果不遵循一些基本的原则，那么无论数据库结构设计得如何合理，无论数据库调整得如何好，将不会得到令用户满意得查询结果。对于SQL查询，应明确要完成得目标，并努力是查询效率最高，以最少得时间准确地检索数据。如果最终用户等待的是一个低速的查询，就好比饥饿者不耐烦地等待迟迟不到地饭菜。尽管大多数查询可有多种方式来完成，但查询的方式不同是导致同一查询执行时间为几秒种、几分钟或为几个小时的主要原因。

在此我们通过对MySQL的慢日志监控，找出主要的SQL语句运行慢的问题。然后对这些SQL进行优化。这里列出一些SQL语句优化的原则和方法，供参考。

优化的理由

　　1)SQL语句是对数据库(数据)进行操作的惟一途径；

　　2)SQL语句消耗了70%~90%的数据库资源；

　　3)SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；

　　4)SQL语句可以有不同的写法；

　　5)SQL语句易学，难精通。

    1. 大小写对SQL语句的影响（ORACLE）

SQL语句存放在内存中，可以被所有的数据库用户共享. 因此,当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同,就能很快获得已经被解析的语句。

    当你向服务器提交一个SQL语句,首先会在指定内存中查找相同的语句。这里需要注明的是,两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).

A.字符级的比较:

当前被执行的语句和共享池中的语句必须完全相同.

      例如:

          SELECT * FROM EMP;

      和下列每一个都不同

          SELECT * from EMP;

          Select *  From Emp;

B.两个语句所指的对象必须完全相同:

例如：

      用户 对象名           如何访问（声明部分）

Jack    sal_limit   private synonym

         Work_city           public synonym

         Plant_detail   public synonym

Jill       sal_limit   private synonym

         Work_city           public synonym

         Plant_detail   table owner

     考虑一下下列SQL语句能否在这两个用户之间共享.

 SQL
能否共享
原因
select max(sal_cap) from sal_limit;
不能
每个用户都有一个private synonym - sal_limit , 它们是不同的对象
select count(*) from work_city where sdesc like 'NEW%';
能
两个用户访问相同的对象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id
不能
用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.

C.两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)

a.

select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.pin;

b.

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

    1. 尽量使用(NOT) EXISTS 替代( NOT)IN这样的操作

EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

语句1

SELECT dname, deptno

FROM dept

WHERE deptno NOT IN

(SELECT deptno FROM emp);

语句2

SELECT dname, deptno

FROM dept

WHERE NOT EXISTS

(SELECT deptno

FROM emp

WHERE dept.deptno = emp.deptno);

明显的，2要比1的执行性能好很多，因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是range scan。

通常来说，采用表连接（emp.depart_cod=depart.depart_code）的方式比EXISTS更有效率，所以：连接＞exists＞IN。

    1. 在海量查询时尽量少用格式转换

如用

WHERE a.order_no = b.order_no而不用

WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)

    1. 查询海量数据是，可以使用optimizer hints(ORACLE)

例如/*+ORDERED */

如

SELECT /*+ FULL(EMP) */ E.ENAME

FROM EMP E

WHERE E.JOB = 'CLERK';

而不是

SELECT E.ENAME

FROM EMP E

WHERE E.JOB || '' = 'CLERK';

    1. ORDER BY和GROPU BY

使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。

    1. 对列的操作

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

    1. 避免使用OR

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。

    例如：

LOC_ID 和REGION上都建有索引.

高效:

    SELECT LOC_ID , LOC_DESC , REGION

  FROM LOCATION

        WHERE LOC_ID = 10

        UNION

        SELECT LOC_ID , LOC_DESC , REGION

        FROM LOCATION

        WHERE REGION = “MELBOURNE”

低效:

        SELECT LOC_ID , LOC_DESC , REGION

        FROM LOCATION

WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

注意:

WHERE KEY1 = 10   (返回最少记录)

OR KEY2 = 20      (返回最多记录)

（ORACLE 内部将以上转换为WHERE KEY1 = 10 AND ((NOT KEY1 = 10) AND KEY2 = 20)）

    1. 用IN来替换OR

低效:

SELECT….

FROM LOCATION

WHERE LOC_ID = 10

OR    LOC_ID = 20

OR    LOC_ID = 30

高效

SELECT…

FROM LOCATION

WHERE LOC_IN IN (10,20,30);

（这是一条简单易记的规则，但是实际的执行效果还须实际运行中检验）

    1. FROM

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表(放在where的最后)。如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。

例如: 

表 TAB1  16,384 条记录

        表 TAB2    1   条记录

        选择TAB2作为基础表 (最好的方法)

            select count(*) from tab1,tab2   执行时间0.96秒

        选择TAB2作为基础表 (不佳的方法)

            select count(*) from tab2,tab1   执行时间26.09秒

    1. WHERE

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

 例如:

 (低效,执行时间156.3秒)

SELECT …

FROM EMP E

WHERE  SAL > 50000

AND    JOB = ‘MANAGER’

AND    25 < (SELECT COUNT(*) FROM EMP  WHERE MGR=E.EMPNO);

 (高效,执行时间10.6秒)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)

AND    SAL > 50000

AND    JOB = ‘MANAGER’;

    1. is null或is not null

在where子句中使用is null或is not null的语句优化器是不允许使用索引的，尽量不用，少使用或不使用NOT。

    1. SELECT子句中避免使用 ‘ * ‘

    2. 避免在索引列上使用计算

如（WHERE SAL  > 25000/12）

    1. Order by语句

任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

    1. 通配符

如果通配符（%）在搜寻词首出现， Oracle系统不使用此列做索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。

例如：

在下面的查询中索引得到了使用

select * from employee where last_name like 'c%';

    1. 避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

    1. 用Where子句替换HAVING子句(ORACLE)

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

例如：

      低效:

        SELECT REGION，AVG(LOG_SIZE)

        FROM LOCATION

        GROUP BY REGION

        HAVING REGION REGION != ‘SYDNEY’

        AND REGION != ‘PERTH’

      高效：

               SELECT REGION，AVG(LOG_SIZE)

        FROM LOCATION

        WHERE REGION REGION != ‘SYDNEY’

        AND REGION != ‘PERTH’

        GROUP BY REGION

(HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

    1. 联合

最新MYSQL（如MYSQL4.0）的特征之一是对SQL联合的支持。联合（union）是一个将多个选择结果组合成为一个结果集的工具。

例如：

select first_name,last_name

from author

union

select fname,lname

from editor;

如果一个人既是作者又是编辑让它显示两次，如：

select first_name,last_name

from author

union all

   select fname,lname

from editor;

    1. 让MYSQL服务器做的更好

例如，要检索表中的几行，有如下程序：

○ for (int I = 0 ; I++; I< keymax){

 select * from foobar where key=I;

 process the row

}   

· select * from foobar where key< keymax;

for each row{

 process the row

}   

前一种方法浪费MYSQL要在每次循环迭代时花费解析、优化和执行同一查询的开销。

后一种好些，让MYSQL一次检索出所有的行，避免多余的开销。

    1. 本地缓存数据

在应用中缓存数据一般会使性能得到提升。如客户订购应用中，它接收州的缩写作为输入，并根据数据库中的数据扩展为完整的州名。

Accept input from user into $state_abbr

Select state_name into $state_name from state where state_abbr=$state_abbr;

Set display field =$state_name

但是我们知道各州的查询不会经常变化，所以缓存各州的表将达到提高性能的目的。如：

#在应用启动时

select state_name,state_abbr from state;

for each row{

load state name into state_name

hash table indexed by state_abbr

}

accept input from user into $state_abbr

set display field =state_name[$state_abbr]   

在此，每当用户输入一个定单时都会在数据库中保存一个查询。而查询工作在应用启动时就完成了。缓存数据适合于相对固定的信息，不希望对很快过期的数据进行缓存，因为保存本地缓存得到更新可能会比保存缓存中的数据开销更大。

    1. 索引准则

适当的索引对应用的性能至关重要，而且在MYSQL中建议用索引，它的速度时极快的。遗憾的是，索引也有相关的开销。每次向表中写入时（如INSERT、UPDATEH或DELETE），如果带有一个或多个索引，那么MYSQL也要更新各个索引。这样索引就增加了对各个表的写入操作的开销。此外，索引增加了数据库的规模。只有当某列被用于WHERE子句时，才能享受到索引的性能提升的好处。如果不使用索引，它就没有价值，而且会带来维护上的开销。

如果索引不经常用，那么它可能不值得维护，但如果能明显减少花费的时间有值得维护，所以这些决定必须要适合应用的需要。

但也有折衷的办法。

    1. 尽量为所有在WHERE子句中被引用的列建立索引

作为一般目标，可能希望为所有在WHERE子句中被引用的列建立索引。但是也有例外。如果用<、>=、=、 <=、>和BETWTEEN运算符比较或连接某些列，此时索引将被用到。而如果在WHERE子句中对某些列使用函数，那么将不会使用该列的索引。例如：

select * from employee where left(name,6)=’FOOBAR’

将不能利用name列的索引。

又例如：

select * from employee where name like ‘%FOOBAR’

也不利用name列的索引

相反，如果模式中有字符串前缀，则LIKE运算符将使用索引。如：

select * from employee where name like ‘FOOBAR%’

如前所述，要注意不要盲目为WHERE子句中被引用的列创建索引。维护索引的代价应当同性能的收益相均衡。

    1. 尽可能的使用唯一索引

如果知道索引中的数据是唯一的，如主键或其他键，就要使用唯一索引。唯一索引比普通索引更能提高性能。MYSQL能够利用值为唯一这一点来做出更为优化的假设。

    1. 利用多列索引

设计良好的多列索引可以减少所需索引的总数量。如果合适，MYSQL将使用多列索引的左边部分。设计很差的索引带来的后果是从不使用或极少使用。

熟悉应用查询情况对于确定多列索引是非常重要的。可以使用EXPLAIN SELECT工具验证结果（此工具很好用）。

    1. 考虑不为某些列建立索引

有时候，进行全表浏览要比必须读取索引和数据表更快，尤其是当索引包含的是平均分布的数据集是更是如此。对此典型的例子是性别，它有两个均匀分布的值（男和女）。通过性别 需要读取大概一半的行。在种情况下进行全表扫描浏览要更快。

因此，需要始终对应用进行测试，以查看如何工作最合适。  

    1. 惯用的一些技巧

  • Use short primary keys. Use numbers, not strings, when joining tables.

  • When using multi-part keys, the first part should be the most-used key.

  • When in doubt, use columns with more duplicates first to get better key compression.

  • If you run the client and MySQL server on the same machine, use sockets instead of TCP/IP when connecting to MySQL (this can give you up to a 7.5 % improvement). You can do this by specifying no hostname or localhost when connecting to the MySQL server.

  • Use (default on some OSes) if possible. This will turn off external locking and will give better performance.

  • Use application-level hashed values instead of using long keys:

  •   SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
  •   col_1='constant' AND col_2='constant'

  • Store BLOB's that you need to access as files in files. Store only the file name in the database.

  • It is faster to remove all rows than to remove a large part of the rows.

  • If SQL is not fast enough, take a look at the lower level interfaces to access the data.

    1. 尽量避免的事情

  • Updates to a table or INSERT on a table with deleted rows, combined with SELECTS that take a long time.

  • JOINS without using keys or keys which are not unique enough.

  • JOINS on columns that have different column types.

  • Using HEAP tables when not using a full key match with =

  • Forgetting a WHERE clause with UPDATE or DELETE in the MySQL monitor. If you tend to do this, use the option to the mysq client.

    1. 表锁定

表锁定是低水平的事务处理。简而言之，MYSQL允许你锁定一组表，使得只有一个客户可以使用。与事物不同，它不限制表的类型。但是你不能回滚对已锁定表锁进行的操作。

锁定有两个基本功能：

A、操作一组表的多条语句，使它们作为一个工作单元来执行

B、在某些情况下使多个更新执行得更快

MYSQL支持三种锁定：

  读、本地读和写

如：

lock tables account write;

select @bal:=balance from account where account_id = 1;

update account set balance=@bal*0.03 where account_id = 1;

unlock tables;

    1. 慎用游标

在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。

总结

经验显示，性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说，最大的性能问题常常是由其中这些相同方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。

其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

资料版权为MySQLpub

打赏
AD：【Python】案例最多的自动化办公视频教程
赞(0) 打赏
未经允许不得转载：同乐学堂 » Day38-（转载）SQL之优化大全
标签：

相关推荐

特别的技术，给特别的你！

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏，我们将继续给力更多优质内容，让我们一起创建更加美好的网络世界！

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!