25yicms利用ASP.NET(C#)+MSSQL技术全力打造功能最强大的营销型企业网站管理系统,企业做网站系统,做网站软件，提供div+css企业网站模板。
企业网站管理系统RSSRSS
阅读内容

SQL语言艺术笔记之为性能而设计高效访问数据库


时间：2014/10/30   来源：企业网站管理系统
 　　如果你看书喜欢看热销书那一定会错过这本经典书！它和《SQL权威指南》都是我很推崇的，但遗憾的是它在各大平台上销量都极差且无货。本书适合有SQL开发经验的中高级数据库人员，讲了很多SQL性能优化方面底层原理的东西，有助于理解SQL调优，绝对是一本适合重读和购买的书！这节企业网站管理系统推荐二章SQL语言艺术笔记之为性能而设计高效访问数据库。
　　
　　第1章：为性能而设计
　　
　　关系模型：不是指不同表之间建立了“关系”（这是常见误解），而是指表内不同字段之间存在“关系”。也就是说，如果几个值属于表的同一行，它们之间就存在关系。关系就是表（确切的说，表描述了关系），不同字段联系在一起的方式定义了关系。
　　
　　三范式（NF，normal form，标准形式）：
　　
　　1NF：列为不可拆分的最小单元。尤其在作为过滤条件where里的字段时，不可分割，至于分解到什么程度要具体视业务需求。一旦所有属性都具有原子性、且确定了键，我们的数据就符合1NF了。
　　
　　2NF：实体属性必须依赖整个组合主键。2NF必须先满足1NF，单字段主键满足2NF，复合主键时，不可以存在某个字段只依赖于主键中的部分字段。部分依赖会产生数据的冗余，浪费资源，降低查询性能。解决部分依赖则要新建表，以被部分依赖的键为新表主键。
　　
　　3NF：实体属性必须直接跟主键字段相关，而不能间接相关，即，不能存在传递依赖，3NF必须先满足2NF。传递依赖也会产生数据的冗余。
　　
　　空值：表中不可大量存在空值，否则意味着关系模型存在严重问题，动摇了查询优化的基础。尤其当where条件中出现空值NULL时需要特别注意，很容易出现与设想不一样的结果。
　　
　　第2章：高效访问数据库
　　
　　查询的识别：当一些进程消耗过多CPU资源时，我们需要确定是哪些SQL语句造成的。但要确定一小段SQL语句与整个系统的关系，有时非常困难。而在SQL中插入注释则有助于辨别查询在程序中的位置及单独应用对服务器造成的负载有多大。
　　
　　提升数据库效率：
　　
　　连接数据时会降低性能，减少数据库连接次数可提高效率。
　　
　　临时表的索引可能不是最优的，查询临时表的语句效率比永久表差。另外，查询之前必然先为临时表填入数据，这也增加了开销。
　　
　　几千个语句，借助游标不断循环很慢，换成几个语句处理同样数据还是较慢，换成一个句子则效率会得到提高。
　　
　　代码的执行越接近DBMS核心，则执行速度越快。如要使用函数，始终应首选DBMS自带函数。因为自带函数在执行时比任何第三方开发的代码更接近数据库核心，相应地其效率也会高出许多。看一个有意思的例子：
　　
　　对于手工输入的文本数据，单词之间可能包含不只一个空格，我们需要将单词间的多个空格替换为一个空格，请编写一个函数来实现。有以下方法可实现该函数：
　　
　　方法一：
　　
　　
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE OR REPLACE FUNCTION squeeze1 (p_string IN VARCHAR2)
RETURN VARCHAR2
IS
  v_string VARCHAR2(512) := ‘’:
  c_char   CHAR(1);
  n_len    NUMBER := LENGTH(p_string);
  i        BINARY_INTEGER := 1;
  j        BINARY_INTEGER;
BEGIN
  WHILE (i <= n_len)
  LOOP
    c_char := SUBSTR(p_string,i,1);
    v_string := v_string || c_char;
    IF (c_char = ' ')
    THEN
      j := i + 1;
      WHILE SUBSTR(p_string || 'X',j,1) = ' ')
      LOOP
        j := j + 1;
      END LOOP;
      i := j;
    ELSE
      i := i + 1;
    END IF;
  END LOOP;
  RETURN v_string;
END;
　　
　　上述代码通过检查每一个字段是否为空格将所有字符重新拼接一次，其中“X”是为了避免循环超出字段长度。
　　
　　方法二：
　　
　　
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION squeeze2 (p_string IN VARCHAR2)
RETURN VARCHAR2
IS
  v_string VARCHAR2(512) := p_string;
  i        BINARY_INTEGER := 1;
BEGIN
  i := INSTR(v_string,'  ');
  WHILE (i > 0)
  LOOP
    v_string := SUBSTR(v_string,1,i) || LTRIM(SUBSTR(v_sting,i + 1));
    i = INSTR(v_string,'  ');
  END LOOP;
  RETURN v_string;
END;
　　
　　上述代码主要思想为，利用length()和replace()函数处理结果。
        方法三：
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE OR REPLACE FUNCTION squeeze3 (p_string IN VARCHAR2)
RETURN VARCHAR2
IS
  v_string VARCHAR2(512) := p_string;
  len1     NUMBER;
  len2     NUMBER;
BEGIN
  len1 := LENGTH(v_string);
  v_string := REPLACE(v_string,'  ',' ');
  len2 := LENGTH(v_string);
  WHILE (len2 < len1)
  LOOP
    len1 := len2;
    v_string := REPLACE(v_string,'  ',' ');
    len2 := LENGTH(v_string);
  END LOOP;
  RETURN v_string;
END;
　　
　　现在，建立一个10000行的表squeezable来测试三个函数的性能差异：
　　
　　
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 建表
CREATE TABLE squeezable (rand_text VARCHAR2(50));
-- 过程
DECLARE
  i        BINARY_INTEGER;
  j        BINARY_INTEGER;
  k        BINARY_INTEGER;
  v_string VARCHAR2(50);
BEGIN
  FOR i IN 1 .. 10000
  LOOP
    j := dbms_random.VALUE(1,100);
    v_string := dbms_random.string('u',50);
    WHILE (j < LENGTH(v_string))
    LOOP
      k := dbms_random.VALUE(1,3);
      v_string := SUBSTR(SUBSTR(v_string,1,j) || RPAD(' ',k) || SUBSTR(v_string,j + 1),1,50);
      j := dbms_random.VALUE(1,100);
    END LOOP;
    INSERT INTO squeezable VALUES (v_string);
  END LOOP;
  COMMIT;
END;
-- 测试
SELECT squeeze1(random_text) FROM squeezable;
SELECT squeeze2(random_text) FROM squeezable;
SELECT squeeze3(random_text) FROM squeezable;
　　测试结果显示，squeeze1耗时0.86秒，squeeze2耗时0.48秒，squeeze3耗时0.39秒。这证明了，离核心越近，代码运行越快。
　　
　　尽可能把条件逻辑放到SQL语句中（如借助case表达式），而不是SQL的宿主语言中。
　　
　　尽可能的合并SQL语句以减少表扫描，可思考如下例题：

【举例1】

1
2
3
4
5
6
7
8

UPDATE test
SET c1 = 0
WHERE c1 IN (1,3)
  AND c3 = 0;
UPDATE test
SET c2 = 0
WHERE c2 IN (1,3)
  AND c3 = 0;
　　以上两条update语句将两次扫描表test，但如果可以合并成一条update则将仅扫描一次，如下：
　　
　　
1
2
3
4
5
6
7
8
9
10
11
12
13
14

UPDATE test
SET c1 = (CASE c1
            WHEN 1 THEN 0
            WHEN 3 THEN 0
            ELSE c1
          END),
    c2 = (CASE c2
            WHEN 1 THEN 0
            WHEN 3 THEN 0
            ELSE c2
          END)
WHERE (c1 IN (1,3)
      OR c2 IN (1,3))
  AND c3 = 0;
　　

【举例2】

1
2
3
4
5
6
7
8

SELECT c1 INTO var1
  FROM test
 WHERE c3 = 'c3_text'
   AND c4 = 1;
SELECT c1 INTO var2
  FROM test
 WHERE c3 = 'c3_text'
   AND c4 = 3;

以上两条语句给变量var1和var2赋值，可以使用以下语句合并成一条：

1
2
3
4
5

SELECT c1 BULK COLLECT INTO array1
  FROM test
 WHERE c3 = 'c3_text'
   AND c4 IN (1,3)
ORDER BY c4;
　　以上语句借助bulk collect子句一次性将两个值放到数据array1中，另外使用order by区分数据中值顺序。
　　
　　自定义函数嵌到SQL语句后，它可能被调用相当多次。如果在select语句的选出项列表中使用自定义函数，则每返回一行数据就会调用一次该函
点击次数：       打印此页  关闭
相似内容
网站设计