金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 如何规划,创建_Log表及触发器

如何规划,创建_Log表及触发器

发布时间:2019-11-05 09:07编辑:数据库浏览(66)

    实例效果:

    引言

      昨天和刚入行就带我的老领导相约北京酒吧,4年师徒情,7年未见,从老公司境况到老熟人的现状,到现在的工作,未来的发展。从当下的技术到新技术的展望,聊到数据库架构,我说我现在还是在做传统的数据库架构,而老领导满心的分布式,好像不是分布式都是比较LOW了,这里面依然存在着这样一个问题,什么是“分布式”,因为每个人说的都不一样,理解的也都不一样。

       而分布式又是怎样一步一步演变的,不同情况下又该如何设计规划自己的架构,文章篇幅有限内容太多,这里只能粗浅的说一说啦。

    ------------------本文纯属个人观点,如有错误、不足望指教----------------

     

    实现表数据的增修删时,记录日志。

     

    Preface

    1.“复制”现有表,

    架构的演变

      架构演变一定是根据当时要求的场景、压力下性能的需要、安全性、连续性的要求、技术的发展.....

      我把架构的发展分为大概4个阶段:

      1.单机模式

      图片 1

       IT建设初期,高速建设阶段,大家要做的只有一件事,我需要什么构建什么,我需要ERP我买软件,需要HIS买HIS,这个时期按需构建大量的系统基本在这个时期产生,当然那个时候也没什么高可用的要求。

      2.双机热备 和 镜像

      图片 2  图片 3

      基本是20年前的技术了,在高速构建后,一堆的系统运行中,用户发现我们的核心业务如果坏掉业务受影响,停机几个小时做恢复 这是无法接受的,那么双机热备或镜像,Active-Standby的模式出现,这样一台机器工作,一台备用坏了在短时间可以接管业务,造成的损失会低很多!

      那么问题也很明显,备机资源浪费,依赖存储,数据还是单点,成本较高。产品也很多:RoseHA/RoseMirrorHA、NEC ExpressCluster、微软MSCS、Symantec VCS、Legato、RHCS 太多太多了。

      随后为了解决数据单点的问题有出现了 存储的主备,存储的双活这厂商也太多了,这里就不介绍了

      图片 4  图片 5

     

      基本上传统企业依然停留在第一和第二阶段,也就是要么单机,要么双机热备

     

      3.节点多活 

      图片 6     图片 7

      随着业务量越来越大,数据量不断飚升,系统高效性的矛盾显现出来,系统卡慢、报表、接口业务无法分离OLAP OLTP业务混合导致系统锁情况严重,资源消耗极其庞大,光靠升级硬件已经无法满足要求,横向扩展已经成为大势所趋。

      同时切换时间、备机无法启动的问题也困扰着用户。

      那么节点多活,多台机器同时对外提供访问的技术登上舞台,代表的ORACLE RAC、微软ALWAYSON 、MOEBIUS集群

      多活的两种模式也是从第二带架构的演变

      oracle rac 把双机热备的辅助节点变的可以访问,关键点数据在多节点内存中的调配

      Microsoft awo、Moebius 则是把镜像的辅助节点变的可以访问,关键点数据多节点同步

      这样横向扩展来分担压力,并且可以在业务上进行分离。

       4.分布式架构 

       图片 8

       分布式架构真的不知道从何说起,概念太大,每个人理解的都不一样,只能意会不能言传:

      比如说一份数据分开存成多份

      比如说拆分,水平拆分、垂直拆分、分库、分表、分业务

      比如说....

      其实说到底就是在第三代横向扩展也无法满足的情况下,继续“拆”,根据不同需求各种“拆”,拆到什么样呢? 大家都知道可以说最慢的环节在数据库,传统的做法复杂语句,大存储过程运行非常慢,那我们就把这些拆到表数据量足够小、语句足够简单、业务粒度小、访问压力尽量的小!

      这样细化的设计一切为业务服务,也是精细化设计产物,但这也存在一个问题,传统企业在缺少高端人才,人力的情况下根本无法做到。现在的互联网公司为业务的需要同时对IT团队的大力建设,这是传统企业根本无法达到的。

      

      当然如果有第五代那也许可以说是云,未来业务一切的技术都是云端,云端看不见摸不到,传统行业人回归业务,而IT 建设与管理也必然由专业的人做专业的事儿。

     

      个人总结的架构演变,主架构演变不包含其他辅助技术,仅供参考

      图片 9

     

       创建相应的_Log表;

    其他技术漫谈

       在这四代架构之间也有很多技术出现,主要以数据复制、存储同步为代表,如DG、OGG、LOGSHIPPING、Replication等等,这些都是不同场景下的数据复制,让一个副本变成多个,基本目的在于副本读或者本/异灾备,而这些技术也在不同的场景中扮演这重要的角色,每种技术都有自己的优缺点,不能一概而论。

      图片 10

     

      当然这里面还包含现在所谓的虚拟化、超融合、存储双活,这些技术首先不是数据库本身技术,在很多企业所谓数据库的高可用中扮演着擦边球的角色,虚拟化、超融合、存储双活都有自己适用的场景,而说到数据库的架构,这些方案只是基础架构层面。

      图片 11

        As we know,InnoDB is index organized table.InnoDB engine supports row-level lock base on indexes,if there're no indexes on a certain table the record locks will upgrade to "table-level"(not really table lock,just locks all the records in the table) locks.Furthe more,in RR transaction isolation mode,It's more complicated.'cause there're gap locks(together with record locks,we call them next key locks) to prevent phantom read between multiple tansactions.Let's do some test watch the locking conflicts.

     (注意点:

    如何选架构

    •   选架构

      首先你该选的是几代架构?

      四代架构是按照业务不断细分,以冗余 和 拆分、细化为主线大体过程

      二代冗余

      图片 12

      三代粗拆分

      图片 13

      四代细拆分

      图片 14

     

     

     

      当然这是只是大概的意思,实际中拆分的场景,条件,扩展性一系列复杂的过程。

     

       我曾经无数次遇到几十G的库 几百并发的应用就要规划分片,领导最求高大上,底下技术人员叫苦。

    •   构建

      构建中主要是对建构的细节了解和熟练,这和企业的人员配置有很大的关系,传统企业中很多在架构方案中选择第三方产品?这是为什么,构建需要专业的人,而企业最少的就是这部分人,而维护管理,责任划分也是不得不考虑的事情。

      当然架构越复杂投入的经历也就越大,这也不是一个架构师可以主导的事情。

    •   维护

      维护才是关键,业务变动后的灵活性、压力下的扩展性、出问题的排查、技术力量的支持,一系列漫长的过程开始了.....

     

     

    通过select union all 的方式,避免了IDENTITY 的“复制”,
    即如果原表有 PK 如 ID Identity,_Log表 仅“复制”ID int,“不复制” Identity属性,
    以便 Insert Update Delete时,可以Insert到Log表。)
    

    题外篇

      自己在传统行业玩的太久了,写这片文章的过程中也和PingCAP 联合创始人& CTO 黄东旭,聊了一些未来技术的发展,tidb做的风声水起,对未来数据库大家都是未知,但随着技术的不断涌现更牛的架构,更牛的理念也必将一一实现。

      比如依靠智能化的机制集群自我修复,性能自提升,架构自适应等等

    Procedure

    2.对现有表,创建Insert,Update,Delete的触发器,

    总结

       架构方案是几代不重要,重要的是适合自己的业务,保证稳定、安全、高效、持续,单机适合简单业务,没有那么高的安全性、连续性依然可以,双机热备可以保障基本的高可用,节点多活的集群适合业务压力较大简单粗暴的分离和压力分担,至于分布式如果企业有能力有资源,业务压力庞大自然会考虑,但在我接触的客户中太多认为自己业务只能通过分布式方案构建,但是其实只是简单优化+三代多活,读写分离负载均衡即可满足。

      所以根据自己业务评估最为重要,一个好的架构规划,不但解决现有问题节省成本,更会避免步子太大激进带来的不必要损失。

     

      并将相应数据 记录到对应的_Log表

    Crete a test table as below.

     

     1 zlm@192.168.56.100:3306 [zlm]>create table t1(
     2     -> c1 int unsigned not null default '0',
     3     -> c2 int unsigned not null default '0',
     4     -> c3 int unsigned not null default '0',
     5     -> c4 int unsigned not null default '0',
     6     -> primary key(c1),
     7     -> key(c2)
     8     -> ) engine=innodb;
     9 Query OK, 0 rows affected (0.02 sec)
    10 
    11 zlm@192.168.56.100:3306 [zlm]>insert into t1(c1,c2,c3,c4) values(0,0,0,0),(1,1,1,0),(3,3,3,0),(4,2,2,0),(6,2,5,0),(8,6,6,0),(10,4,4,0);
    12 Query OK, 7 rows affected (0.01 sec)
    13 Records: 7  Duplicates: 0  Warnings: 0
    14 
    15 zlm@192.168.56.100:3306 [zlm]>select * from t1;
    16 +----+----+----+----+
    17 | c1 | c2 | c3 | c4 |
    18 +----+----+----+----+
    19 |  0 |  0 |  0 |  0 |
    20 |  1 |  1 |  1 |  0 |
    21 |  3 |  3 |  3 |  0 |
    22 |  4 |  2 |  2 |  0 |
    23 |  6 |  2 |  5 |  0 |
    24 |  8 |  6 |  6 |  0 |
    25 | 10 |  4 |  4 |  0 |
    26 +----+----+----+----+
    27 7 rows in set (0.01 sec)
    28 
    29 zlm@192.168.56.100:3306 [(none)]>select @@transaction_isolation;
    30 +-------------------------+
    31 | @@transaction_isolation |
    32 +-------------------------+
    33 | REPEATABLE-READ         | //Make surej in RR transaction isolation level.
    34 +-------------------------+
    35 1 row in set (0.00 sec)
    36 
    37 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
    38 +----------------------------+-------+
    39 | Variable_name              | Value |
    40 +----------------------------+-------+
    41 | innodb_status_output_locks | ON    |
    42 +----------------------------+-------+
    43 1 row in set (0.00 sec)
    

    相应代码如下:

     

    BEGIN TRAN   
    BEGIN TRY  
    
    
    --定义TAB_CURSOR
    DECLARE TAB_CURSOR CURSOR read_only
    FOR
       SELECT name FROM SysObjects Where XType='U' 
      -- AND name = N'T01ConstItem' 
      and [name] <> N'dtproperties'
       ORDER BY Name;
    
    --打开
    OPEN TAB_CURSOR
    
    DECLARE @P_TabName NVARCHAR(200);
    DECLARE @P_TabName_Log NVARCHAR(200);
    DECLARE @P_Create_Log_Tab NVARCHAR(4000);
    DECLARE @P_Create_Trig_I NVARCHAR(4000);
    DECLARE @P_Create_Trig_U NVARCHAR(4000);
    DECLARE @P_Create_Trig_D NVARCHAR(4000);
    
    FETCH NEXT FROM TAB_CURSOR 
               INTO @P_TabName
    --循环
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
       IF (@@FETCH_STATUS <> -2)
        BEGIN   
        SET @P_TabName_Log = CONCAT(@P_TabName,N'_Log');
    
        SET @P_Create_Log_Tab = N' SELECT * ';
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' INTO ');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName_Log );
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' UNION ALL ');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' SELECT TOP (1) * ');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
        SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab, N' WHERE 1=0 ; ');
    
        EXEC( @P_Create_Log_Tab);
    
        --SET @P_Create_Log_Tab = CONCAT(N' SET IDENTITY_INSERT ',@P_TabName_Log ,' ON '); 
        --EXEC( @P_Create_Log_Tab);
    
    
        SET @P_Create_Trig_I = N' create trigger ';
        SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' trig_',@P_TabName,N'_I ');
        SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' on ',@P_TabName,N' after INSERT as ');
        SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N' begin ');    
        SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'insert into ',@P_TabName_Log );
        SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' select * , N''I'',Getdate() from Inserted ; ' );
        SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'end ');
    
        --select @P_Create_Trig_I;
    
        EXEC( @P_Create_Trig_I);
    
        SET @P_Create_Trig_U = N' create trigger ';
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_U ');
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after UPDATE as ');
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UD'',Getdate() from Deleted ; ' );
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UI'',Getdate() from Inserted ; ' );
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
        EXEC( @P_Create_Trig_U);
    
        SET @P_Create_Trig_U = N' create trigger ';
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_D ');
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after DELETE as ');
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''D'',Getdate() from Deleted ; ' );
        SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
        EXEC( @P_Create_Trig_U);
    
        END
       FETCH NEXT FROM TAB_CURSOR INTO @P_TabName
    END
    
    --关闭
    CLOSE TAB_CURSOR
    --释放
    DEALLOCATE TAB_CURSOR
    
    COMMIT TRAN;  
    
    END TRY  
    BEGIN CATCH  
        SELECT ERROR_MESSAGE() AS ErrorMessage  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ROLLBACK TRAN;  
    END CATCH  
    

    **Test 1. session1 executes "select ...  for update" and session2 executes "select ... lock in share mode".(conflict)**

     

     1 //Session1:
     2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 for update;
     3 Query OK, 0 rows affected (0.00 sec)
     4 
     5 +----+----+----+----+
     6 | c1 | c2 | c3 | c4 |
     7 +----+----+----+----+
     8 |  3 |  3 |  3 |  0 |
     9 +----+----+----+----+
    10 1 row in set (0.00 sec)
    11 
    12 //Session2:
    13 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 lock in share mode;
    14 Query OK, 0 rows affected (0.00 sec)
    15 
    16 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    17 
    18 //Session2 requested a "S" record lock on the primary key column where c1=3 while session1 has holded the "X" record lock on the same position,so session2 was blocked util lock timeout.
    

     

    本文由金沙国际官网发布于数据库,转载请注明出处:如何规划,创建_Log表及触发器

    关键词: