X
首页 SQL优化 Informix SQL 实践与技巧
Informix SQL 实践与技巧

如何加快sql的执行速度？
    1.select 语句中使用sort,或join
    如果你有排序和连接操作，你可以先select数据到一个临时表中，然后再对临时表进行处理。因为临时表是建立在内存中，所以比建立在磁盘上表操作要快的多。
    如：
    SELECT time_records.*, case_name
    FROM time_records, OUTER cases
    WHERE time_records.client = "AA1000"
    AND time_records.case_no = cases.case_no
    ORDER BY time_records.case_no  
    这个语句返回34个经过排序的记录，花费了5分钟42秒。而：
    SELECT time_records.*, case_name
    FROM time_records, OUTER cases
    WHERE time_records.client = "AA1000"
    AND time_records.case_no = cases.case_no
    INTO temp foo;
    SELECT * from foo ORDER BY case_no
    返回34条记录，只花费了59秒。
    
    2.使用not in 或者not exists 语句
    下面的语句看上去没有任何问题，但是可能执行的非常慢：
    SELECT code FROM table1
    WHERE code NOT IN ( SELECT code FROM table2 )
    
    如果使用下面的方法：
    SELECT code, 0 flag
    FROM table1
    INTO TEMP tflag;
    然后：
    UPDATE tflag SET flag = 1
    WHERE code IN ( SELECT code
    FROM table2
    WHERE tflag.code = table2.code );
    然后：
    SELECT * FROM
    tflag
    WHERE flag = 0;
    看上去也许要花费更长的时间，但是你会发现不是这样。
    事实上这种方式效率更快。有可能第一种方法也会很快，那是在对相关的每个字段都建立了索引的情况下，但是那显然不是一个好的注意。
    
    3.避免使用过多的“or"
    如果有可能的话，尽量避免过多地使用or：
    WHERE a = "B" OR a = "C"
    要比
    WHERE a IN ("B","C")
    慢。
    有时甚至UNION会比OR要快。
    
    4.使用索引。
    在所有的join和order by 的字段上建立索引。
    在where中的大多数字段建立索引。
    WHERE datecol >= "this/date" AND datecol <= "that/date"
    要比
    WHERE datecol BETWEEN "this/date" AND "that/date" 慢
    
    如何在shell脚本中使用一个sql查询的结果？
    以下的是一个运行在sh/ksh下面的脚本。在online中，如果你想要更新一个有许多表的数据库的统计信息。这个脚本不太好。因为这个脚本只能单个处理数据库中的表，而不能同时处理大量的表。
    例子：
    # update_em
    # Run UPDATE STATISTICS on a table by table basis
    #
    DATABASE=$1
    if [ -z "$DATABASE" ]
    then
    echo "usage: update_em dbname" >&2
    exit 1
    fi
    isql $DATABASE - < dev/null | isql $DATABASE -
    output to pipe "cat" without headings
    select "update statistics for table ", tabname, ";"
    from systables where tabid >= 100 order by tabname;
    EOF
    exit 0
    
    也许你已经注意到exit的返回值对不同的isql不是都相同，因此这样作不是很可靠，代替通过$?来检查返回值的更好的主意是将标准错误重定向到一个文件中，然后在这个文件中grep “error"。例如：
    # Generate the data
    isql -qr <<!>stage.rep 2>$stage.err
    database $database;
    select ...
    !
    # Check for errors
    if grep -i "error" $stage.err >/dev/null
    then
    ...error_handler...
    fi
    
    为什么不能对一个计算产生的字段创建视图？
    问题：为什么我不能创建视图：
    CREATE VIEW tst AS
    SELECT ship_charge - totval cout
    FROM orders WHERE ship_charge > 0;
    回答：你应该这样写：
    CREATE VIEW tst (cout) AS
    SELECT ship_charge - totval
    FROM orders WHERE ship_charge > 0;
    
    如何只select 出数据库中的部分数据（例如10％）。
    问题：如果你想要得到一个select 语句正常返回的数据的一部分，例如：
    SELECT firstname, lastname, city, state
    FROM bigdatabase
    WHERE state = "TX"
    回答：
    有一个方法可以返回一个近似值，只需要在where后加上：
    AND rowid=(trunc(rowid/x)*x)
    其中的x代表你想要返回的总的记录的1/x。需要说明的是，这种方法只能返回一个近似的值，并且表中的数据在物理上分布的连续性。
    
    如何创建一个表结构和永久表完全一致的临时表。
    例如：CREATE TEMP TABLE mytemp (prodno LIKE product.prodno
    desc LIKE product.desc)
    你可以使用如下的语句：
    SELECT prodno, desc FROM product
    WHERE ROWID = -1
    INSERT INTO TEMP mytemp

如何更改serial类型下一次插入操作产生的值？
    我们知道serial类型的字段是系统自动增加的整数字段，那么怎样能控制下一个serial类型字段的值。
    想要下一个插入的serial类型的值比默认值大，可以用：
    ALTER TABLE tabname MODIFY( ser_col_name SERIAL([new_start_number])
    想要下一个插入的serial类型的值比默认的值要小，首先需要将serial类型重新置为1：
    INSERT INTO table (serial_column) VALUES (2147483647);
    INSERT INTO table (serial_column) VALUES (0); -- 重新从1开始!
    ....然后执行ALTER TABLE(就像上面的做法一样）。
    
    如何在发生错误的时候终止sql脚本的执行？
    如果你创建了一个sql脚本，并且在UNIX命令行中使用以下的方式来执行这个脚本：
    $ dbaccess <database> <脚本文件名>
    这时，脚本中的所有的sql语句都会被执行，即使其中的一个sql语句发生了错误。例如，如果你脚本中为如下的语句：
    BEGIN WORK;
    INSERT INTO history
    SELECT *
    FROM current
    WHERE month = 11;
    DELETE FROM current
    WHERE month = 11;
    COMMIT WORK;
    如果INSERT语句失败了，DELETE语句仍旧会继续执行。直到commit work。这样的后果可能会很严重。你可以通过设置一个环境变量来防止这种情况的发生。
    DBACCNOIGN=1
    
    如何设置decimal字段运算结果的精度？
    假定你使用dbaccess或者isql，设置环境变量DBFLTMASK=6 就可以设置为小数点后面6位，比如：
    CREATE TEMP TABLE t
    ( col_a DECIMAL(8,4) NOT NULL,
    col_b DECIMAL(8,4) NOT NULL,
    col_c DECIMAL(8,4) NOT NULL
    );
    INSERT INTO t VALUES(1.2345, 3.4567, 5.6789);
    SELECT (col_a + col_b) / col_c AS value FROM t;
    value 0.826075
    如果DBFLTMASK=7
    value 0.8260755
    
    为什么我们有时会遇到sysprocplan表被锁的提示？
    sysprocplan 表是sysmaster库中的一个表，其中记录存储过程经过优化的查询计划。每当查询树中的数据库对象有任何结构上的变化，这个查询计划就会自动更新。如果对查询树中存在的任何表有update statistics操作，也会自动更新查询计划。在查询计划更新的时候，会对sysporcplan表中的相关记录加锁。
    注意：每次你对一个表更新统计的时候，也同时会更新于这个表相关的存储过程，即UPDATE STATISTICS FOR PROCEDURE 。
    你可以作的另外一件事情就是：在存储过程中使用SET OPTIMIZATION LOW，这会让优化器在存储过程运行的时候不会试图去重新优化它。否则存储过程通常都会被重新优化一次。
    
    
    如何删除掉表中重复的记录？
    假设“keycol”字段的值唯一，而且没有对表进行分片，并且没有其它的人正在删除"sometable"中的记录，你可以执行如下的SQL：
    delete from sometable as a
    where rowid <> (select min(rowid) from sometable where keycol = a.keycol)
    如果这个表使用表分片，rowid不存在，你还可以用如下的方法：
    BEGIN WORK;
    SELECT DISTINCT * FROM Table INTO TEMP Temp1;
    DELETE FROM Table WHERE 1 = 1;
    INSERT INTO Table SELECT * FROM Temp1;
    COMMIT WORK;
    对于规模较小或中等的表，并且你有足够的存储空间来存储整个的临时表的时候，这种方法通常十分有效。
    
    如何加快SELECT COUNT(DISTINCT)的速度。
    通常“SELECT COUNT(DISTINCT)”这样的操作要花费比较长的时间，如果你这样作：
    SELECT UNIQUE xxx INTO TEMP XXX " 然后再"SELECT COUNT(*) FROM TEMP XXX"
    这样通常可以提高几倍的效率。

NULL值的使用
RDSQL中字段缺省值为空；并且对数值型的0和空值，以及字符型的空白和空值区别对待。
数值表达式中某个变为空，则整个表达式值为空；
聚合函数中，对空值忽略不计，若全部为空值，除COUNT（*）返回0外，其余返回空值。
布尔表达式中，结果可能为“未知”（见下表）。如TRUE AND NULL 结果为 “未知”，对“未知”结果在RDSQL中看作不符合查询条件。
and
T
F
?
　 or
T
F
?
　 not
　
T
T
F
?
　 T
T
T
T
　 T
F

F
F
F
?
　 F
T
F
?
　 F
T

?
?
F
?
　 ?
T
?
?
　 ?
?

结合上表，分析下列子句 ，其中n1=20；n2为空；n3=30。结果如右。
where n1*n2 < 1000 and n3 = 30; 结果：不符合查询条件
where n1*n2 < 1000 or n3 = 30; 结果：符合查询条件
ORDER BY子句中的空值，每一个空值为一组。
INSERT或UPDATE时，可使用关键字NULL/null表示空值。
字段是否可以为空，由CREATE TABLE语句中是否有NOT NULL指定或由ALTER修改。
Q：select count(*) from t1和select count(c1) from t1是否一样?


字符查找，主要使用LIKE和MATCHES。
LIKE
MATCHES
意义

%
*
匹配0或多个字符

-
?
匹配一个字符

\
\
转义字符

无
[]
选择匹配

例：matches ‘*Sp’；匹配以任何字符开始，以Sp结束的字段值
matches ‘?l*’； 匹配第一个字符任意，第二个字符为l，其余字符任意的字段值
matches ‘[A-N]*’； 匹配以A到N的字符开始，其余字符任意的字段值
matches ‘*[sS]*’； 匹配含有s或S的字段值，扩展以下可用于case insensitive查询
like ‘%\%%’； 匹配含有%的字段值

用SQL语句求表一中的关于name有多少不同的num，结果如表二。
表一：　　　　　　　　　　　　　　　　　　　　 表二：
id　　 name　　 num　　　　　　　　　　　　　　name 　　　　count1
1 　　　AA 　　　1　　　　　　　　　　　　　　　CC　　　　　　 2
2 　　　AA 　　　2　　　　　　　　　　　　　　　BB　　　　　　 2
3 　　　AA 　　　3　　　　　　　　　　　　　　　AA 　　　　　　3
4 　　　AA 　　　1
5 　　　AA 　　　2
6 　　　BB 　　　4
7 　　　BB 　　　5
8 　　　BB 　　　4
9 　　　BB 　　　5
10　　　CC 　　　6
11　　　CC 　　　6
12　　　CC 　　　7
SQL语句如下：
create table t1
(
id smallint,
name char(10),
num smallint
);
insert into t1 values(1,'AA',1);
insert into t1 values(2,'AA',2);
insert into t1 values(3,'AA',3);
insert into t1 values(4,'AA',1);
insert into t1 values(5,'AA',2);
insert into t1 values(6,'BB',4);
insert into t1 values(7,'BB',5);
insert into t1 values(8,'BB',4);
insert into t1 values(9,'BB',5);
insert into t1 values(10,'CC',6);
insert into t1 values(11,'CC',6);
insert into t1 values(12,'CC',7);

A：select name ,count(distinct num) from t1 group by name;

4）使用旋转矩阵，将表一中关于id在不同月份的费用，由纵向变为横向。
其中表一对一个id某个月份的记录数可能>1。表一：
id 　　d1 　fee费用（分）
1 2000-01-24 100
1 2000-04-24 100
2 2000-02-24 200
2 2000-06-24 200
3 2000-04-24 400
4 2000-04-24 400
5 2000-05-24 500
6 2000-06-24 600
7 2000-09-24 900
8 2000-11-24 1100
表二：
id 1月份费用 2月份费用 …… … … 12月份费用
1 　100 0 　0 　100 　0 　0 　0 　0 　0 　0 　0 　　0
2 　0 　200 0 　0 　　0 　200 0　 0 　0 　0 　0 　　0
3 　0 　0 　0 　400 　0 　0 　0 　0 　0 　0 　0 　　0
4 　0 　0 　0 　400 　0 　0 　0 　0 　0 　0 　0 　　0
5 　0 　0 　0 　0 　　500 0 　0 　0 　0 　0 　0 　　0
6 　0 　0 　0 　0 　　0 　600 0 　0 　0 　0 　0 　　0
7 　0 　0 　0 　0 　　0 　0 　0 　0 　900 0 　0 　　0
8 　0 　0 　0 　0 　　0 　0 　0 　0 　0 　0 　1100 　0

SQL语句：
create table t3
(
id smallint,
d1 datetime year to day,
fee int
);
insert into t3 values(1,"2000-01-24", 100);
insert into t3 values(1,"2000-04-24", 100);
insert into t3 values(2,"2000-02-24", 200);
insert into t3 values(2,"2000-06-24", 200);
insert into t3 values(3,"2000-04-24", 400);
insert into t3 values(4,"2000-04-24", 400);
insert into t3 values(5,"2000-05-24", 500);
insert into t3 values(6,"2000-06-24", 600);
insert into t3 values(7,"2000-09-24", 900);
insert into t3 values(8,"2000-11-24", 1100);　
create table t4 –旋转矩阵
(
m_code smallint,
y1 smallint,
y2 smallint,
y3 smallint,
y4 smallint,
y5 smallint,
y6 smallint,
y7 smallint,
y8 smallint,
y9 smallint,
y10 smallint,
y11 smallint,
y12 smallint
);
insert into t4 values(1, 1,0,0,0,0,0,0,0,0,0,0,0);
insert into t4 values(2, 0,1,0,0,0,0,0,0,0,0,0,0);
insert into t4 values(3, 0,0,1,0,0,0,0,0,0,0,0,0);
insert into t4 values(4, 0,0,0,1,0,0,0,0,0,0,0,0);
insert into t4 values(5, 0,0,0,0,1,0,0,0,0,0,0,0);
insert into t4 values(6, 0,0,0,0,0,1,0,0,0,0,0,0);
insert into t4 values(7, 0,0,0,0,0,0,1,0,0,0,0,0);
insert into t4 values(8, 0,0,0,0,0,0,0,1,0,0,0,0);
insert into t4 values(9, 0,0,0,0,0,0,0,0,1,0,0,0);
insert into t4 values(10,0,0,0,0,0,0,0,0,0,1,0,0);
insert into t4 values(11,0,0,0,0,0,0,0,0,0,0,1,0);
insert into t4 values(12,0,0,0,0,0,0,0,0,0,0,0,1);

--方法一
select id,month(d1) month,sum(fee) fei from t3 group by 1,2 into temp aa;
select id,
sum(y1*fei) y1,sum(y2*fei) y2,sum(y3*fei) y3,sum(y4*fei) y4,
sum(y5*fei) y5,sum(y6*fei) y6,sum(y7*fei) y7,sum(y8*fei) y8,
sum(y9*fei) y9,sum(y10*fei) y10,sum(y11*fei) y11,sum(y12*fei) y12
from aa, t4 where aa.month = t4.m_code
group by id order by id
--方法二
select id,
sum(y1*fee) y1,sum(y2*fee) y2,sum(y3*fee) y3,sum(y4*fee) y4,
sum(y5*fee) y5,sum(y6*fee) y6,sum(y7*fee) y7,sum(y8*fee) y8,
sum(y9*fee) y9,sum(y10*fee) y10,sum(y11*fee) y11,sum(y12*fee) y12
from t3, t4 where month(d1) = t4.m_code
group by id order by id

方法一和方法二的结果一样，但有所区别：
方法一中是先对id某个月的钱进行累加，然后进行旋转；
方法二中在表一对一个id某个月份的记录数可能>1的情况时，先对每条记录进行旋转，然后在累加求和。
 

Cheetah技术专栏| 数据库管理| 数据库性能优化| 数据库相关软件开发| 数据库相关工具| SQL优化| 数据复制及备份

© 2009 CNIUG——中国Informix数据库用户协会. Powered by Joomla! / Informix IDS 11.5