常见数据库（Oracle/MySQL)开发中的问题集(续)-CSDN博客

常见数据库（Oracle/MySQL)开发中的问题集
    声明：许多内容来自互联网，由于查阅的内容过多，无法能一一给出链接地址，如果有问题，请联系，3q

    系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质 SQL语句之间的速度差别可达到上百倍，可见对于一
    个系统不是简单的能实现其功能就可以了，而是要写出高质量的SQL语句，提高系统的可用性。

    作为一名程序员，在开发的过程中，不仅仅是关注代码的编码，在与数据库打交道的代码逻辑我们应该需要谨慎的编写，以求避免系统性能的代码或SQL出现。下面是性能影响的排序（以影响程度排序）
    D1    业务逻辑（影响最大）
    D2    数据设计（表结构设计，数据建模）
    D3    应用程序设计（SQL语句的写法）
    -->以上三条都会与作为程序员的我们息息相关的！一个好的程序员，不仅是能编写漂亮的代码，还要有一定的数据设计能力，和优秀的SQL编写能力，即，能知晓如何编写出性能好的SQL语句，能够察觉到SQL语句的写法是否会影响到性能.....（个人拙见，大牛见笑）
    D4 数据库的逻辑结构（索引，区间，段，表空间）
D5 数据库操作（参数和配置）
D6 访问路径
D7 内存分配情况
D8 I/O 和物理设计（DBA 与业务沟通）（峰值多大，使用周期）
D9 资源的征用
D10 操作系统的调节

从上面来看，D1-D3 影响最大但比较难修改（事先去做），而且责任大部分在程序员肩上。
一下是可能引起全表扫描的操作
在索引列上使用NOT或者'<>'
对索引列使用函数或者计算
NOT IN操作
通配符位于查询字符串的第一个字符
IS NULL或者IS NOT NULL
多列索引，但它的第一个列并没有被WHERE子句引用
注：对字段没进行索引的，执行的时候都是全表扫描。
既然是进行全表扫描，应该如何避免一些性能问题呢？看下面的建议吧！

关注SQL语句中 where 的编写
多数情况下，Oracle使用索引来更快的遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，造成优化器删去索引而使用全表扫描。所以，应清楚优化器根据何种原则来删除索引。

IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样之情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句，Oracle会停止使用索引而执行全表扫描。判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。可以考虑在设计表时，对索引列设置为NOT NULL。
如何避免呢？如：
1）a is not null 改为 a>0 或a>' '等。
2）不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。
......

IN 与 NOT IN 操作符
     建议：尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替
     IN操作符：Oracle会试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询。若成功就直接采用多个表的连接查询。in就是至少多了一个转换的过程。
     Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。
     而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。
     推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

     WHERE后面的条件顺序影响
     在条件中都没有建立索引的字段，所以执行的时候都是全表扫描。进行全表扫描，数据量巨大的时候，就会对查询产生直接的影响。
     Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。

Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式

关注数据库的N+1查询

关注数据库的分页查询
Oracle
    SELECT *
　　FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2
　　where t2.r >= M

　　例如从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录，语句如下：

　　SELECT *
　　FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2
　　Where t2.R >= 10

MySQL
    SELECT * FROM 表名称 LIMIT M,N
    从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录
    select * from sys_option limit 10,20

SQLServer
    从数据库表中的第M条记录开始取N条记录，利用关键字Top：值得注意的是如果SELECT语句中既有top，又有order by，那么，会从排序好的结果中选择：
    SELECT *
    FROM (SELECT TOP N * FROM
                (SELECT TOP (M+N-1) * FROM TABLE_NAME ORDER BY 主键 DESC) TBL_1) TBL_2
    ORDER BY 主键 ASC

    Example here:(从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录)
    SELECT *
    FROM (SELECT TOP 20 *
    FROM (SELECT TOP 29 * FROM Sys_option ORDER BY sys_id DESC) T1 ) T2
    ORDER BY sys_id ASC


Oracle工作中遇到的知识总结：

    RONUM：
    rownum是Oracle中的伪列，并不真正存在于表中，它只在查询的时候产生。也就是说表中的数据并没有一个真正的物理的列去表示一条记录是第几行，而只是在查询中才会对这些行进行标识。
    rownum的正确生成时机：rownum是在where条件过滤之后，在任何排序（order by）或聚集（aggregation）之前赋给行的。
    同时，只有当rownum被分配给行后才会递增。rownum的初始值为1。rownum在查询中产生后就不再变化

rownum被分配的过程大致如下：
rownum = 1
for x in (select * from one_tbl)
loop
    if (x satisifies the predicate)then
        output the row
        rownum = rownum + 1
    end if
end loop

一个查询的执行顺序大概是这样的：
    Select …, ROWNUM
    From t
    Where <where clause>
    Group by <columns>
    Having <having clause>
    Order by <columns>

    1.The FROM and WHERE clause go first.
    2.ROWNUM is assigned and incremented to each output row from the FROM or WHERE.
    3.SELECT is applied.
    4.GROUP BY is applied.
    5.HAVING is applied.
    6.It is sorted.

SQL语句：select * from emp where ROWNUM <= 5 order by sal desc 的错误原因：
该语句的目的是想返回top 5薪水最高的员工信息，但根据rownum的产生原理，rownum在order by之前就已经产生，
所以该语句并不能起到top 5的作用，正确的语法如下：
select * from (select * from emp order by sal desc) where ROWNUM <= 5;