SQL编写及其优化培训.doc

1 温有飘温有飘 2005 年年 4 月月 28 日日 软脑软件（北京）公司软脑软件（北京）公司 S Q L 编编 写写 及及 其其 优优 化化 培培 训训 2 目目 录录 本培训包含的内容如下：4 1.表、索引相关知识及其在数据库内部相关的物理存储4 1.1.数据库中最小的物理存储单位：块(block)4 1.2.表数据在块中的存储以及 RowId 信息.4 1.3.索引4 1.4.Bitmap 索引介绍.6 2.数据库查询操作的内部处理过程6 2.1.数据库的数据读取是以块为单位的6 2.2.查询操作内部过程以及索引的作用6 2.3.执行计划概念7 2.4.排序处理过程以及各种引起排序的操作7 2.5.多表 join 操作的内部过程.7 3.写 SQL 语句时在性能方面的目标8 3.1.select 列表中减少不需要的数据 .8 3.2.from 列表中去除不需要的表.8 3.3.where 条件中应该考虑到索引的使用，避免一些写法.8 3.3.1.避免过滤字段中套用函数，如果必须，则考虑函数索引9 3.3.2.避免把列放入表达式中去比较9 3.4.避免不需要的排序9 3.5.避免数据类型的隐式转换9 3.6.程序代码中的注意点10 3.6.1.由多个 SQL 语句完成的一个操作尽可能写成一个 SQL 就完成，避免分 解10 3.6.2.使用数据库提供的约束来判断某些错误，如唯一性11 4.SQL 语句知识.11 4.1.SQL 语句串讲.11 4.1.1.select f1 from A .11 4.1.2.select f1 from A where f2=v_111 4.1.3.select f1 from A order by f2.11 4.1.4.select sum(f1) from A12 4.1.5.select f2,sum(f1) from A group by f212 4.1.6.select a_f2,b_f2 from A,B where A.f1=B.f1 .12 4.1.7.Select * from report where SALES_CODE in (select SALES_CODE from anken where );14 4.1.8.Select * from report where SALES_CODE exists (select 1 from anken where );14 4.1.9.in,exists 的选择.14 4.1.10.select yadfw as t1,kopeew as t2 From A union all select uufger as t1,poeprvcx 3 as t2 from B14 4.2.几种常见操作或函数15 4.2.1.比较符 like.15 4.2.2.is null，is not null15 4.2.3.to_char,to_date,to_number 15 4.2.4.nvl 16 4.2.5.其他16 5.表设计的一些建议16 参考文献16 4 本培训包含的内容如下： 表、索引相关知识及其在数据库内部相关的物理存储； 数据库查询操作的内部处理过程； 写 SQL 语句时在性能方面的目标； SQL 语句知识； 表设计的一些建议 下面阐述的内容以 Oracle 为例来讲述的，在排版方面可能有不妥当的地方，或有些内容前 后重复，但各种知识应该比较容易理解和接受； 1.表、索引相关知识及其在数据库内部相关的物理存储表、索引相关知识及其在数据库内部相关的物理存储 1.1. 数据库中最小的物理存储单位：块数据库中最小的物理存储单位：块(block) 数据库最小的物理存储单位是块（9i 默认是 8k） ，每个块只能属于一个表；一行数据 插入时会占用块中的一些存储，当块不能再容纳新数据时，则将启用新的块存储数据； 1.2. 表数据在块中的存储以及表数据在块中的存储以及 RowId 信息信息 表的字段除了设计者设计的字段外，还有个字段 Rowid；RowId 是 oracle 为每个表自 动增加的一个字段。当表中插入一行记录时，此记录在块就有一个唯一的物理位置， 这个位置信息就保存在此行记录的 RowId 字段； 1.3. 索引索引 表中记录的存储顺序是以先后插入顺序存储的，在一个数据量很大的表中，如果不引 入其他的手段，每次查找小部分记录都是从第一条扫描到最后一条，这样，系统将慢 得不能使用；使用索引可以有效解决问题；如图： 5 Report 表(100 万条记录) RowIdSession_codeSales_codeEmployee_code AAANslAAQAAA7YTAAh100061000017 AAANslAAYAAAAmWAAX10007100015 。 。 。 索引 Employee_codeRowId 5AAANslAAYAAAAmWAAX 17AAANslAAQAAA7YTAAh 索引的本质用途是通过它使读取进程在扫描源表时的数据块范围大大减小了，因此性 能大幅度提高； 一般情况下，当检索的记录与所有记录数比较300； Select * from A where f1300*5； 必须使用第二种，否则，如果 f1 有索引，第一种情况将不使用； 3.4. 避免不需要的排序避免不需要的排序 2.4 节中已说明排序需要额外的负荷，所以，当不需要排序时，应该避免； 3.5. 避免数据类型的隐式转换避免数据类型的隐式转换 举例：表 A，有一字段 code，varchar2 类型，此字段存储的值都是由数字组成的串； SQL 语句： Select * from A where code=v_number；其中，v_number 是由程序外面传入的数 值； 因为 v_number 是数值，而 code 是 varchar2 类型，所以，Oracle 会进行类型的隐式 转换，把此语句转化成如下形式执行： Select * from A where to_number(code)= v_number； 把列嵌套在函数里（索引不能使用） ，这个是我们不期望的，所以正确的做法是我们 应该进行显式的转换： Select * from A where code=to_char(v_number)； 3.6. 程序代码中的注意点程序代码中的注意点 3.6.1.由多个由多个 SQL 语句完成的一个操作尽可能写成一个语句完成的一个操作尽可能写成一个 SQL 就完成，避就完成，避 11 免分解免分解 举个例子：需要得到 2004 年中，每个季度(1-3 月，4-6 月，7-9 月，10-12 月)的日报 个数； 第一种方法： 比较容易想到的，用 4 个 SQL 语句分别去得到各个季度的统计： Select count(*) from report where day=to_date(2004-01-01,yyyy-mm-dd) and day=to_date(2004-04-01,yyyy-mm-dd) and day=to_date(2004-07-01,yyyy-mm-dd) and day=to_date(2004-10-01,yyyy-mm-dd) and day=to_date(2004-01-01,yyyy-mm-dd) and day=to_date(2004-04-01,yyyy-mm-dd) and day=to_date(2004-07-01,yyyy-mm-dd) and day=to_date(2004-10-01,yyyy-mm-dd) and day10000 的组的统计，这时用 having 过滤条件，如下： select f2,sum(f1) from A group by f2 having sum(f1)10000 where 条件是针对记录的过滤，被过滤掉的数据不参与统计。having 是针对统计完 成后，统计结果返回给用户时的过滤； 4.1.6. select a_f2,b_f2 from A,B where A.f1=B.f1 oracle 对连接 join 的操作的内部处理过程，可以参考下图： 14 上图的流程只是执行计划当中的一种，但可以借助于理解，当多于 2 张表时，情形 是一样的； 上述的连接也称内连接，其他连接还有左连接，右连接和全表连接的方式； 左连接：select a_f2,b_f2 from A,B where A.f1=B.f1（+） ，必须首先扫描 A 表以驱动 B 扫描。此时，A 标示的地方，如果针对 A 表的一条记录，在 B 表中 1 条也没有找 到符合条件的记录，则会把 A.f1 值和以 NULL 值作为 B.f1 的值返回给用户；这样， A 表中的所有记录的且在 select 列表中的信息都会返回给用户； 右连接：select a_f2,b_f2 from A,B where A.f1（+）=B.f1，首先扫描 B 表以驱动 A 扫 描，其他操作可参考左连接； 全连接：select a_f2,b_f2 from A full outer join B on A.f1=B.f1；它的记录集是 select a_f2,b_f2 from A,B where A.f1=B.f1 的结果集加上A 表中有，但在 B 表中未找到符 合条件的记录的集合（B 表中的信息是以 Null 值返回） ，再加上B 表中有，但 A 表中未找到符合条件的记录的集合（A 表中的信息是以 Null 值返回） ； 如果 f1 字段有索引，可能会使 用索引方式检索 A 15 各种连接通用写法是： 内连接：select a_f2,b_f2 from A,B where A.f1=B.f1 或 select a_f2,b_f2 from A inner join B onA.f1=B.f1(此标准在 symfoware 不支持，奇怪!) 左连接：select a_f2,b_f2 from A left outer join B on A.f1=B.f1 右连接：select a_f2,b_f2 from A right outer join B on A.f1=B.f1 全连接：select a_f2,b_f2 from A full outer join B on A.f1=B.f1；在 symfoware 中不支 持 4.1.7. Select * from report where SALES_CODE in (select SALES_CODE from anken where ); 上节讲的所有的表都在 from 后的列表中，数据源都是直接的表，不含子查询，而这 条是典型的含子查询的语句，内部执行时，可以有这种方式： 首先，执行子查询语句，把结果集临时保存，作为外层查询的一个数据源； 其次，类似 4.1.6 节中的流程图，先检索 report 表第一条记录，然后再检索上一个步 骤得到的数据源，如果包含 sales_code 值，则返回 report 当前检索到的记录，如果 没有，则 report 继续扫描下一条记录，重复上步操作，直到表的结尾； 很多含子查询的语句 oracle 内部处理的时候会先转换成等价的不含子查询的语句去 执行，如： Select A.f1,B.f1 from A,(select * from C where C.f3=V_1) B where A.f2=B.f2; 转换成： Select A.f1,B.f1 from A,C where A.f2=C.f2 and C.f3=V_1; 4.1.8. Select * from report where SALES_CODE exists (select 1 from anken where ); Oracle 先扫描 report 的一个记录，然后去执行里层子查询里的语句，只要能检索到 一条记录，则立即返回给外层一个 true 的值，report 的当前记录就可以返回给用户， 扫描一下一条记录；如果子查询执行完毕都还没有检索到一条记录，则返回 false 给 外层，则表示 report 当前记录不符合条件，则继续扫描下一条记录，直到结尾； 4.1.9. in,exists 的选择的选择 一般情况下，in 的子查询都可以写成 exists 的子查询，以下两种选择可以优化性能： 1） 当内层子查询的过滤条件有很强的过滤作用时，考虑使用 in，因为内层子查询 返回的数据源比较少； 2） 当外层查询的过滤条件有很强的过滤作用时，且内存查询可以使用索引快速定 位，则考虑使用 exists； 4.1.10. select yadfw as t1,kopeew as t2 From A union all select uufger as 16 t1,poeprvcx as t2 from B union all 前后是两个独立的查询，它的作用是合并 2 个记录集。内部处理时，oracle 先执行 union all 之前的查询返回记录给用户，执行完毕后，接着执行 union all 后面 的查询，把查询结果返回给用户； 应用程序中，有很多报表或网格显示的上下行内容来自于互不相干的表，但由于使 用绑定数据源的方式，显示数据只能由一次 SQL 语句检索得到，此时 union all 就 派上用场了； Union all 两端查询的字段个数以及对应的数据类型要一致； Union 也有把多个查询的记录集合并的功能，但它与 union all 的不同之处是，当 oracle 检索到的每条记录时不会马上返回给用户，会把它送到一个排序区中进行排 序，排序中以字段在 select 列表中更靠前的则先排序，当不同记录中各个字段信息 全部相同时，oracle 只留下一条记录，废除其他相同记录； 所以，当不需要排序且允许相同记录存在时，使用 union all 性能比 union 好； 4.2. 几种常见操作或函数几种常见操作或函数 4.2.1.比较符比较符 like 这是一个模糊查询操作的字符串比较符，如 where A like ww%，通配任意字符 的符号%,如果单个字符通配是_ 。 作比较的串中，如果%或_放于最前面，oracle 将不用索引； 4.2.2.is null，is not null 当比较一个字段是否为 null 或非 null 时，应该使用 where f1 is null 或 where f1 is not null，而不能用=或比较符； 任何值与 Null 做=,，比较时，都返回 false； 任何值与 null 进行+-*/数字运算操作时，得到的还是 null； 字符串与 null 值使用|连接还是那个字符串值； 4.2.3.to_char,to_date,to_number to_char 可以把一个数字值转换成字符串，这个简单，不多讲； to_char 把日期型转换成字符串时，最通用的格式yyyy-mm-dd hh24:mi:ss ，其中 yyyy 是 4 字符年份，mm 是 2 字符月份，dd 是 2 字符日期，hh24 是 24 小时制的时 间，如果 12 小时制则用 hh，mi 是 2 字符分钟，ss 是 2 字符秒数. 格式可以灵活使用，如取得日报的年份： select to_char(day,yyyy) from report； to_date 是把字符串转换成日期，最通用格式与 to_char 一样，如：to_date(2005-05- 01,yyyy-mm-dd)； 17 to_number 就是把只含数字的字符转换成数字； 在写 SQL 语句时，如果条件中有类型不一致的比较，应该使用函数来显式转换他们， 而不要让 oracle 自动转换导致有些索引不能使用； 4.2.4.nvl nvl(v_1,v_2)：如果值 v_1 为 null，则返回值为 v_2，否则返回 v_1; 4.2.5.其他其他 Decode，case when 函数比较有用，具体用法参考其他书籍； 5.表设计的一些建议表设计的一些建议 1） 避免多种不同类型的数据放在同一张表中 如 epm 中的 anken 表，可根据 distinguish 种类设计成多张表存储。目前情况放在一 起的缺点是，项目，任务，风险都放在一起保存，当要查一个项目时，oracle 内部 扫描表会扫描非常多的有关任务，风险等类型的记录，这些任务或风险信息越多， 查找到某个项目的时间就越长；类似的，查找任务或风险时也有这个问题； 上述情况就应该把不同的业务类型数据用不同的表保存； 目前的设计，如果 Anken 表数据量很大时，可考虑使用分区技术去优化(数据的横向 分离)，这是 oracle 数据仓库经常使用的技术，可参考相关资料； 2） 表的拆分 表字段越少，一个块(block)中能存储表的记录数就更多，查询肯定就更快了； 当表的字段