安全性保护:防止非法用户对数据库的非法使用,避免数据泄露、篡改或破坏

   也是有条件的循环控制语句,当满足条件的时候退出循环。WHILE
循环和REPEAT 循环的区别在于:WHILE 是满足条件才执行循环,REPEAT
是满足条件退出循环;WHILE
在首次循环执行之前就判断条件,所以循环最少执行0 次,而REPEAT
是在首次执行循环之后才判断条件,所以循环最少执行1 次。
相当于开发语言的while(bool) 和do while(bool)。

Procedure

一、数据库完整性

DELIMITER $$
CREATE PROCEDURE proc_demo_cursor()
BEGIN
    -- 定义变量,获取光标中的数据
        DECLARE city_id_staff SMALLINT DEFAULT 0;

    -- 声明光标
    DECLARE cur_city CURSOR FOR SELECT city_id FROM test.city;

    -- 定义条件  没有找到数据时退出光标 NOT FOUND CLOSE cur_city
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_city;    

    SET  @result=0;

    -- 打开光标
    OPEN cur_city;
    REPEAT
    FETCH  cur_city INTO city_id_staff;
         IF city_id_staff < 10 THEN 
         SET @result=@result+1;
         ELSE 
         SET @result=@result+2;
         END IF;
    UNTIL 0 END REPEAT;

    -- 关闭光标
    CLOSE cur_city;
END $$
DELIMITER ;

-- 调用
CALL proc_demo_cursor;
SELECT @result;

 

  问题:规范化关系解决了数据维护的异常,并使数据冗余最小,但会导致数据处理性能下降

图片 1

 

 

   loop 实现简单的循环,退出循环条件可以使用leave语句。

  • pt-mext
    has only three options and it is rather easy to use.
  • pt-mext
    is a useful tool in anaylzing differential data of 
    global status of 
    MySQL.
  • pt-mext
    can be used not not only in “mysqladmin” program but also
    others.

    1) 重组不修改数据库原有的逻辑结构和物理结构

DELIMITER $$
CREATE PROCEDURE proc_demo_iterate()
BEGIN
    DECLARE increase INT DEFAULT 1;
    SET @x=0;
    ins: LOOP
      SET increase=increase+1;  
      IF increase =100 THEN
        -- 退出
        LEAVE ins;
      ELSEIF MOD(increase,2)=0 THEN
        -- 返回
        ITERATE ins;
      END IF;
      SET @x=@x+1;
     END LOOP ins;    
END $$
DELIMITER ;
-- 调用
CALL proc_demo_iterate;
SELECT @x;

 

  ③ 重新组表:当用户经常查看的某些数据时由多个表连接之后才能得到的,就可以考虑先把这些数据重新组成一个表,这样在查询时会减少连接提高效率

2. 流程控制

 

    2) 仅当主体许可证级别等于客体密级,该主体才能相应客体

SELECT city_id FROM test.city
 1 [root@zlm2 10:56:56 ~]
 2 #pt-mext -- cat mysqladmin.log
 3 Aborted_clients                               0       0
 4 Aborted_connects                              0       0
 5 Binlog_cache_disk_use                         0       0
 6 Binlog_cache_use                              0       0
 7 Binlog_stmt_cache_disk_use                    0       0
 8 Binlog_stmt_cache_use                         0       0
 9 Bytes_received                             2142    2177
10 Bytes_sent                               155447  165275
11 Com_admin_commands                            0       0
12 Com_alter_db                                  0       0
13 Com_alter_db_upgrade                          0       0
14 ...
15 
16 //pt-mext arranges the results of those three times side-by-side and print them.
17 //The first column shows the first collected result and the second column shows the second one.
18 //The number of columns is n-1(n is the collection times here).

    2) 数据库性能监控,监控数据缓冲区命中率、库缓冲、用户锁、索引使用、等待事件等

2.5 repeat 语句

Preface

  ① 自主存取控制,又称自主安全模式,通过SQL的GRANTDENYREVOKE语句来实现

  在存储过程和函数中可以使用光标对结果集进行循环的处理,光标使用包括光标的声明,open
,fetch,close。
  下面在存储过程中使用一个光标, 这个举例中光标里的逻辑不重要,
重点在于如何使用光标:

 

  ② 调整内存分配:调整相关参数控制数据库内存分配,很大程度改善数据库系统性能

2.1  IF 语句

Introduce

  ② 数据库安全性、完整性控制

DELIMITER $$
CREATE PROCEDURE proc_demo_while()
BEGIN
     DECLARE increase INT DEFAULT 1;
     SET @x=0;
     WHILE increase<=10 DO
     SET increase=increase+1;
     SET  @x=@x+increase;
     END WHILE;
END $$
DELIMITER ;

-- 调用
CALL proc_demo_while;
SELECT @x;
 1 [root@zlm2 10:49:28 ~]
 2 #cat mysqladmin.log
 3 +-----------------------------------------------+--------------------------------------------------+
 4 | Variable_name                                 | Value                                            |
 5 +-----------------------------------------------+--------------------------------------------------+
 6 | Aborted_clients                               | 0                                                |
 7 | Aborted_connects                              | 0                                                |
 8 | Binlog_cache_disk_use                         | 0                                                |
 9 | Binlog_cache_use                              | 0                                                |
10 | Binlog_stmt_cache_disk_use                    | 0                                                |
11 | Binlog_stmt_cache_use                         | 0                                                |
12 | Bytes_received                                | 2142                                             |
13 | Bytes_sent                                    | 155447                                           |
14 | Com_admin_commands                            | 0                                                |
15 | Com_assign_to_keycache                        | 0                                                |
16 | Com_alter_db                                  | 0                                                |
17 | Com_alter_db_upgrade                          | 0                                                |
18 ...
19 
20 +-----------------------------------------------+--------------------------------------------------+
21 | Variable_name                                 | Value                                            |
22 +-----------------------------------------------+--------------------------------------------------+
23 | Aborted_clients                               | 0                                                |
24 | Aborted_connects                              | 0                                                |
25 | Binlog_cache_disk_use                         | 0                                                |
26 | Binlog_cache_use                              | 0                                                |
27 | Binlog_stmt_cache_disk_use                    | 0                                                |
28 | Binlog_stmt_cache_use                         | 0                                                |
29 | Bytes_received                                | 2177                                             |
30 | Bytes_sent                                    | 165275                                           |
31 | Com_admin_commands                            | 0                                                |
32 | Com_assign_to_keycache                        | 0                                                |
33 | Com_alter_db                                  | 0                                                |
34 | Com_alter_db_upgrade                          | 0                                                |
35 ...
36 
37 +-----------------------------------------------+--------------------------------------------------+
38 | Variable_name                                 | Value                                            |
39 +-----------------------------------------------+--------------------------------------------------+
40 | Aborted_clients                               | 0                                                |
41 | Aborted_connects                              | 0                                                |
42 | Binlog_cache_disk_use                         | 0                                                |
43 | Binlog_cache_use                              | 0                                                |
44 | Binlog_stmt_cache_disk_use                    | 0                                                |
45 | Binlog_stmt_cache_use                         | 0                                                |
46 | Bytes_received                                | 2212                                             |
47 | Bytes_sent                                    | 175103                                           |
48 | Com_admin_commands                            | 0                                                |
49 | Com_assign_to_keycache                        | 0                                                |
50 | Com_alter_db                                  | 0                                                |
51 | Com_alter_db_upgrade                          | 0                                                |

  反规范化方法:

-- 语法
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

--  举例(UNTIL 0 END REPEAT)退出 当search_condition为0时,使用END REPEAT退出
    REPEAT
    FETCH  cur_city INTO city_id_staff;
         IF city_id_staff < 10 THEN 
         SET @result=@result+1;
         ELSE 
         SET @result=@result+2;
         END IF;
    UNTIL 0 END REPEAT;
 1 [root@zlm2 11:07:16 ~]
 2 #pt-mext -r -- mysqladmin ext -i1 -c3
 3 Aborted_clients                               0       0
 4 Aborted_connects                              0       0
 5 Binlog_cache_disk_use                         0       0
 6 Binlog_cache_use                              0       0
 7 Binlog_stmt_cache_disk_use                    0       0
 8 Binlog_stmt_cache_use                         0       0
 9 Bytes_received                             2453      35
10 Bytes_sent                               185024    9828
11 Com_admin_commands                            0       0
12 Com_alter_db                                  0       0
13 Com_alter_db_upgrade                          0       0
14 ...
15 
16 [root@zlm2 11:13:28 ~]
17 #pt-mext -- mysqladmin ext -i1 -c3
18 Aborted_clients                               0       0
19 Aborted_connects                              0       0
20 Binlog_cache_disk_use                         0       0
21 Binlog_cache_use                              0       0
22 Binlog_stmt_cache_disk_use                    0       0
23 Binlog_stmt_cache_use                         0       0
24 Bytes_received                             2764    2799
25 Bytes_sent                               214601  224429
26 Com_admin_commands                            0       0
27 Com_alter_db                                  0       0
28 Com_alter_db_upgrade                          0       0
29 ...

六、查询优化

2.2 CASE 语句

Summary

  ② 强制存取控制

  图片 2

 

  ① 逻辑存储结构

2.4  ITERATE语句

Example

    ② 普通用户:由DBA用户或由相应特权的用户创建,并授予系统特权

2.6 while 语句

 

    ① DBA用户:由DBMS自动创建,sys与system用户,拥有全部系统特权

 

Collect the global status of MySQL
with 1s interval and 3 times in all by mysqladmin.

    1) 水平分割:根据行的使用特点进行分割,分割之后所有表的结构都相同,而存储的数据不同,并使用(Union)操作

  ITERATE该语句必须用在循环中,作用相当于for 中的continue,
跳过当前循环剩下的语句,直接进入下一轮循环。

**Execute pt-mext to print them again
with the “mysqladmin.log” file.**

  ④ 数据库的重组与重构

--  举例二种用法
CASE
WHEN i_staff_id = 2 THEN
    SET @x1 = @x1 + d_amount;
ELSE
    SET @x2 = @x2 + d_amount;
END CASE;
-- 或者:
CASE i_staff_id
WHEN 2 THEN
    SET @x1 = @x1 + d_amount;
ELSE
    SET @x2 = @x2 + d_amount;
END CASE;

 

  ③ 检测并改善数据库性能

DELIMITER $$
CREATE PROCEDURE proc_demo_loop()
BEGIN
    DECLARE increase INT DEFAULT 1;
    SET @x=0;
    ins: LOOP
      SET increase=increase+1;  
      IF increase =100 THEN
        SET @x=increase;
        -- 退出
        LEAVE ins;
      END IF;

     END LOOP ins;    
END $$
DELIMITER ;

-- 调用
CALL proc_demo_loop;
SELECT @x;

**The
“mysqladmin.log” file contains all the output of the three times
individually.**

    1) 主体为许可证级别

  有条件的循环控制语句,当满足条件的时候退出循环,在上面的光标也是一种循环,使用repeat来退出光标,使用close来关闭光标。

**Execute pt-mext without flat text
file.**

  ④ 调整资源竞争

  图片 3

 

  反规范化:将规范化关系转换为非规范化的关系的过程

图片 4

1 --relative //Each column substracts the value of the column before it.

 

  包括 if ,case, loop, leave, iterate, repeat, while 语句进行流程控制

Usage

  数据库的规范化过程:高效利用存储空间,减少数据的冗余,减少数据的不一致性

--  语法如下
IF search_condition THEN statement_list
[ELSE IF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
-- 举例
IF city_id_staff < 10 THEN 
    SET @result=@result+1;
ELSE 
    SET @result=@result+2;
END IF;

    We are always obliged to analyze many
outputs generated by various tools directly even in flat text files in
server performance diagnosis.Further more,we need to compare the
differences depend on these results such as the output of “mysqladmin”
tool.

七、空间管理

2.3  loop 语句

1 pt-mext [OPTIONS] -- COMMAND

    2) 客体为密级,分为绝密秘密可信公开等若干级别

1. 光标的使用(游标)

**Common Parameters**

  ② 空间溢出带来灾难停机事故

  图片 5

    pt-mext is tht very tool which can
help you to accomplish the work mentioned above 
easily.It can execute the command
which you specify then print the result side-by-side in one line.Not
only can it print results from a running program but also can it print
results from a flat text file.

  监控分析:值管理员借助工具监测DBMS的运行情况,掌握系统当前或以往的负荷、配置、应用等信息,并分析监测数据的性能参数和环境信息、评估DBMS的整体运行状态

**Specify the “–relative” option to get
the differential data.**

    Oracle的安全机制分为:数据库级表级行集列级

 1 [root@zlm2 11:05:50 ~]
 2 #pt-mext --relative -- cat mysqladmin.log
 3 Aborted_clients                               0       0
 4 Aborted_connects                              0       0
 5 Binlog_cache_disk_use                         0       0
 6 Binlog_cache_use                              0       0
 7 Binlog_stmt_cache_disk_use                    0       0
 8 Binlog_stmt_cache_use                         0       0
 9 Bytes_received                             2142      35 //2277-2142=35
10 Bytes_sent                               155447    9828 //165275-155447=9828
11 Com_admin_commands                            0       0
12 Com_alter_db                                  0       0
13 Com_alter_db_upgrade                          0       0
14 
15 //Differential data is more distinct for us to know about the variation of performance.
16 //Even though we use "--relative" option,there're still two columns here.

发表评论

电子邮件地址不会被公开。 必填项已用*标注