澳门新萄京:创设_Log表及触发器
分类:数据库

实例效果:

目录协会表

  在 InnoDB 存款和储蓄引擎中,表都以依附主键顺序协会寄存的,这种存款和储蓄格局的表称为索引组织表。在 InnoDB 存款和储蓄引擎表中,每张表都有个主键,如若在创设表时从不显式地定义主键,则 InnoDB 存款和储蓄引擎会依照如下方式选拔或创建主键:

    首先推断表中是不是有非空的独一索引,若是有,则该列即为主键。

    假诺不适合上述标准,InnoDB 存款和储蓄引擎自动创制贰个 6 字节大小的指针(_rowid)。

  当表中有三个非空独一索引时,InnoDB 存款和储蓄引擎将选择建表时首先个概念的非空独一索引为主键。这里须求潜心的是,主键的选料根据的是定义索引的次第,并非建表时列的顺序。

 

  无声无息讲到触发器了,平日大家做程序的超少接触到触发器,触发器的操作日常是DB人士来实现。

MySQL各版本,对于add Index的管理方式是差异的,重要有二种:

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

InnoDB 逻辑存款和储蓄结构

  从 InnoDB 存款和储蓄引擎的逻辑存款和储蓄结构看,全数数据都被逻辑地存放在三个表空间中,称之为表空间。表空间又由段、区、页组成。页在局地文档中临时也叫做块。

               澳门新萄京 1

  不过一些时候某个简短的业务需求我们团结去完成,不可能每一次都去麻烦DB职员,所以说,编程人士要全才,除了编制程序以为的职业知识也要读书,比方js,css,html,t-sql等片段语法,不料定要去明白,可是要熟稔,起码语法能够看懂,那样对我们的编制程序有经济的作用,现身卓殊错误,大家也惠及调节和测量试验,以便最快寻觅乖谬。

(1)Copy Table方式
那是InnoDB最初支持的创办索引的不二等秘书籍。一面之识,创立索引是因而一时表拷贝的方式达成的。

1.“复制”现有表,

  表空间

    表空间能够用作是InnoDB 存款和储蓄引擎逻辑结构的最高层,全体的数据都存放在表空间中。

    假诺启用了参数 innodb_file_per_table ,则每张表内的数码可以独立置于二个表空间内。

    每张表的表空间内部存款和储蓄器放的只是数据、索引和插入缓冲 Bitmap 页,其余类的数据,如回滚音讯,插入缓冲索引页、系统业务音信,一回写缓冲等照旧寄放在本来的分享表空间内。

 

  闲话少说,什么事触发器,看名就会猜到其意义,正是您做三个操作,就能够触发另贰个风云,去实施一些操作。

新建一个含有新索引的有的时候表,将原表数据总体拷贝到有的时候表,然后Rename,达成创设索引的操作。

   成立相应的_Log表;

  段

    不感到奇的段有数据段、索引段、回滚段等。

 

  例如你点烟花,点是三个动作,烟花是另叁个动作,点动作日试万言就能够触发烟花这么些动作。

本条点子开创索引,制程中,原表是可读的。不过会消耗生机勃勃倍的蕴藏空间。

 (注意点:

  区

    区是由三番三次页组成的上空,在别的意况下每一个区的大大小小都为 1 MB。为了有限支撑区中页的一连性,InnoDB 存储引擎一次从磁盘申请 4~5 个区。在私下认可情形下,InnoDB 存款和储蓄引擎页的轻重为 16KB,即四个区中风流洒脱共有 陆12个三番五次的页。

    InnoDB 1.2.x 版本新增添了参数  innodb_page_size ,通过该参数能够将暗中同意页的分寸设为 4K,8K,可是页中的数据库不是减掉。

    无论页的朗朗上口怎么生成,区的大大小小总是 1M。

 

  还只怕有正是触发器必需依附贰个重头戏,比方依赖于某一张表,犹如编制程序中事件那些定义。

(2)Inplace方式
那是原生MySQL 5.5,以致innodb_plugin中提供的创造索引的不二秘诀。所谓Inplace,也正是索引创制在原表上一向开展,不会拷贝一时表。相对于Copy Table方式,那是一个进步。

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

  页

    页是 InnoDB 磁盘管理的微乎其微单位。

    在 InnoDB 存款和储蓄引擎中,私下认可每个页的大小是 16KB。从 InnoDB 1.2.x 版本最早,能够由此参数 innodb_page_size 将页的大大小小设置为 4K、8K、16K。若设置达成,则具备表中页的尺寸都为设置的值,不可能对其重新开展改变。除非通过 mysqldump 导入和导出操作来发生新的库。

    在 InnoDB 存储引擎中,不可胜计的页类型有:

      数据页(B-tree Node)

      undo 页(undo Log Page)

      系统页(System Page)

      事务数据页(Transaction system Page)

      插入缓冲位图页(Insert Buffer Bitmap)

      插入缓冲空闲列表页(Insert Buffer Free List)

      未压缩的二进制大对象页(Uncompressed BLOB Page)

      压缩的二进制大对象页(compressed BLOB Page)

 

  上面大家通过三个简便的实例,和贵裔一步一步的来驾驭和选取触发器。

Inplace格局创造索引,创设进程中,原表雷同可读的,但是不得写。

2.对现存表,创制Insert,Update,Delete的触发器,

  行

    InnoDB 存款和储蓄引擎是面向列的,也正是说数据是按行进行贮存的。

    各种页寄存的行记录是有硬性定义的,最多允许存放 16KB / 2 - 200 行的笔录,即 7992 行记录。

 

  实例必要:

(3)Online方式
那是MySQL 5.6.7中提供的创立索引的措施。无论是Copy Table方式,照旧Inplace方式,创制索引的经过中,原表只好同意读取,不可写。对选择有比较大的界定,因而MySQL最新版本中,InnoDB支持了所谓的Online格局创造索引。

  并将相应数额 记录到相应的_Log表

InnoDB 行记录格式

  在 InnoDB 1.0.x 版本早先,InnoDB 存款和储蓄引擎提供了 Compact 和 Redundant 三种格式来寄放行记录数据,那也是近年来应用最多的风姿浪漫种格式。Redundant 格式是为了同盟早先版本而保留的。在 MySQL 5.1 版本中,私下认可设置为 Compact 行格式。

   show table status like 'table_name'; 查看当前表使用的行格式。

  Compact 行记录格式:

    Compact 行记录是在 MySQL 5.0 中引进的,其安排指标是快捷地蕴藏数据。轻松的话,三个页中寄放的行数据更多,其品质就越高。

  Redundant 行记录格式:

    Redundant 是 MySQL 5.0 版本以前 InnoDB 的行记录存款和储蓄格局。

  行溢出多少:

    InnoDB 引擎能够将一条记下中的有个别数据存款和储蓄在真的的数码页面之外。

  Compressed 和 Dynamic 行记录格式:

    InnoDB 1.0.x 版本初始引进了新的文件格式,早前辅助的 Compact 和 Redundant 格式称为 Antelope 文件格式,新的文件格式称为 Barracuda 格式。Barracuda 文件格式下全数二种新的行记录格式:Compressed 和 Dynamic。新的两种记录格式对于寄存的 BLOB 中的数据采纳了一心的行溢出艺术。

    Compressed 行记录格式的另二个功用正是,存款和储蓄在个中的行数据会以 zlib 的算法举行减削,因而对此 BLOB、TEXT、VARCHAOdyssey那类大尺寸类型的数码可见实行丰硕管用的囤积。

  CHA昂Cora 的行存款和储蓄结构:

    在分化的字符集下,CHAEscort 类型列内部存款和储蓄的或者不是定长的数额。

    InnoDB 存款和储蓄引擎中,CHA奥迪Q7类型被视为变长字符类型,对于不能够沾满长度的字符照旧填充 0x20。

 

    1,建筑商品表(Store卡塔 尔(阿拉伯语:قطر‎,订单表(orders卡塔尔,日志表(Logs卡塔 尔(阿拉伯语:قطر‎

InnoDB的Online Add Index,首先是Inplace方式成立索引,无需使用不时表。在遍历聚簇索引,搜罗记录并插入到新索引的长河中,原表记录可更改。而改革的记录封存在Row Log中。当聚簇索引遍历完结,并全部安顿到新索引之后,回看Row Log中的记录改进,使得新索引与聚簇索引记录达到平等状态。

 

InnoDB 数据页结构

  InnoDB 数据页由以下 7 个部分组成:

    File Header(文件头)

    Page Header(页头)

    Infimun 和 Supermum Records

    User Records(顾客记录,即行记录卡塔 尔(阿拉伯语:قطر‎

    Free Space(空闲记录卡塔 尔(阿拉伯语:قطر‎

    Page Directory(页目录)

    File Trailer(文件结尾信息卡塔 尔(阿拉伯语:قطر‎

 

    2,创造订单表插入触发器,完毕插入一条订单信息,商品表中物品数量相应回退,订单中的总金额相应增加。

与Copy Table形式对待,Online Add Index选拔的是Inplace情势,没有须求Copy Table,裁减了半空中开采;与此同不常间,Online Add Index独有在重放Row Log最终贰个Block时锁表,减少了锁表的年月。

相应代码如下:

Named File Format 机制

  从 InnoDB 1.0.x 版本开始,InnoDB 存款和储蓄引擎通过 Named File Format 机制来缓慢解决差异版本下页结构包容性的主题材料。

   innodb_file_format 用来钦赐文件格式。

 

 

  

    3,创设订单表更新触发器,完毕校订一条订单音信,商品表中物品数量相应改换,订单中的总金额相应改变。(和2雷同卡塔尔国

与Inplace情势比较,Online Add Index摄取了Inplace格局的优势,却裁减了锁表的大运。

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  

    4,成立日志表触发器,达成立异商品表价格浮动情状。

1.Inplace add Index

 

1,创设商品表(Store卡塔尔国,订单表(orders卡塔 尔(英语:State of Qatar),日志表(Logs卡塔尔国

  第一步没怎么讲授,大家创造表,并加一些演示数据。

  示例代码:

 1 create table Store
 2 (
 3     ID uniqueidentifier primary key,
 4     ProductID int not null,
 5     ProductPrice money not null default 1,
 6     ProductCH nvarchar(80) not null,
 7     ProductDate datetime not null,
 8     NowNumber int not null
 9 );
10 create table orders
11 (
12     OrderID int primary key,
13     ProductID int not null,
14     BuyNumber int Not null default 1,
15     BuyPricr money not null,
16     NowOrderPrice money default 0 
17 )
18 create table Logs
19 (
20     ID uniqueidentifier primary key,
21     operatedatetime datetime,
22     ProductID int,
23     oldprice money,
24     newprice money
25 );
26 
27 insert into dbo.Store
28 values(NEWID(),1001,5000,'联想','2011-9-1',50)
29 insert into dbo.Store
30 values(NEWID(),1002,6000,'apple','2011-9-1',50)
31 insert into dbo.orders(OrderID,ProductID,BuyNumber,BuyPricr)
32 values(10013,1001,5,6000)

测试表

2,成立订单表触发器

  必要2,3相似操作本身在一块讲授了。

  先看示例代码:

 1 create trigger tri_order_NowOrderPrice
 2 on orders after insert
 3 as
 4 begin
 5     declare @NowOrderPrice money;
 6     declare @BuyNumber int;
 7     declare @ProductID int;
 8     select @ProductID= ProductID,@BuyNumber= BuyNumber from inserted;
 9     select @NowOrderPrice=inserted.BuyNumber * inserted.BuyPricr from inserted;
10     update orders set NowOrderPrice=@NowOrderPrice where ProductID=@ProductID;
11     update Store set NowNumber=NowNumber-@BuyNumber where ProductID=@ProductID;
12 end;
13 
14 create trigger tri_store_NowOrderPrice2
15 on orders after update
16 as
17 begin
18     declare @NowOrderPrice money;
19     declare @ProductID int;
20     declare @BuyNumber1 int;
21     declare @BuyNumber2 int;
22     select @ProductID= ProductID from inserted;
23     select @BuyNumber1=BuyNumber from inserted;
24     select @NowOrderPrice=inserted.BuyNumber * inserted.BuyPricr from inserted;
25     select @BuyNumber2=deleted.BuyNumber from deleted where ProductID=@ProductID;
26     update orders set NowOrderPrice=@NowOrderPrice where ProductID=@ProductID;
27     update Store set NowNumber=NowNumber-(@BuyNumber1-@BuyNumber2) where ProductID=@ProductID;
28 end;

  触发器的要害字是trigger,语法是,on 表名 after 操作名称(日常为insert,update,delete卡塔尔国,begin end中写一些触发器的拍卖操作。

  inserted得到insert之后的数目。语法正是这样轻便。

  create table t1 (a int primary key, b int)engine=innodb;

  insert into t1 values (1,1),(2,2),(3,3),(4,4);

3,制造日志表触发器

  日志表触发器要做的操作正是记录商品价位浮动,这对应就相应在商品表中创制触发器。

  示例代码:

 1 create trigger tri_NowOrderPrice4
 2 on dbo.Store after insert,update,delete
 3 as
 4 begin
 5     if exists(select *from inserted) and exists(select *from deleted)
 6         begin
 7             print('update');
 8             declare @price1 money;
 9             declare @price2 money;
10             declare @datetime datetime;
11             select @price1=ProductPrice from deleted;
12             select @price2=ProductPrice from inserted;
13             if @price1!=@price2
14                 begin
15                     declare @ProductID int;
16                     select @ProductID=ProductID from inserted
17                     insert into Logs(ID,operatedatetime,ProductID,oldprice,newprice)
18                     values(newid(),convert(datetime,getdate()),@ProductID,@price1,@price2)
19                     
20                     select *from dbo.Logs
21                 end
22         end
23     else if exists(select *from inserted)
24         begin
25             print('insert');
26         end
27     else if exists(select *from deleted)
28         begin
29             print('delete');
30         end
31     else
32         begin
33             print('others');
34         end
35 end

  触发器就是那几个剧情,有关触发器的某些参差不齐操作希望我们不常间研商下,讲的不得了请大家多多指正,希望我们学好t-sql语言。

  现在继续整合治理编制程序相关内容,希望大家多多关怀。。。。

  

Inplace Add Index管理流程
SQL

  alter table t1 add index idx_t1_b(b);

 

拍卖流程

  sql_table.cc::mysql_alter_table();

    // 判断当前操作是否可以进行Inplace实现,不可进行Inplace Alter的包括:

    // 1. Auto Increment字段修改;

    // 2. 列重命名;

    // 3. 行存储格式修改;等

    mysql_compare_tables() -> ha_innobase::check_if_incompatible_data();

    // Inplace创建索引第一阶段(主要阶段)

    handler0alter.cc::add_index();

      …

      // 创建索引数据字典

      row0merge.c::row_merge_create_index();

        index = dict_mem_index_create();

        // 每个索引数据字典上,有一个trx_id,记录创建此索引的事务

        // 此trx_id有何功能,接着往下看

        index->trx_id = trx_id;

         // 读取聚簇索引,构造新索引的项,排序并插入新索引

         row0merge.c::row_merge_build_indexes();

            // 读取聚簇索引,注意:只读取其中的非删除项

            // 跳过所有删除项,为什么可以这么做?往下看

            row_merge_read_clustered_index();

            // 文件排序

            row_merge_sort();

            // 顺序读取排序文件中的索引项,逐个插入新建索引中

            row_merge_insert_index_tuples();

    // 等待打开当前表的所有只读事务提交

    sql_base.cc::wait_while_table_is_used();

    // 创建索引结束,做最后的清理工作

    handler0alter.cc::final_add_index();

    // Inplace add Index完毕

Inplace Add Index达成解析
澳门新萄京:创设_Log表及触发器。在目录创造完毕未来,MySQL Server立刻能够利用新建的目录,做询问。可是,遵照上述流程,对自己个人来讲,有两个疑问点:

目录数据字典上,为啥须求保障四个trx_id?
trx_id有啥意义?  

遍历聚簇索引读取全部记录时,为啥可跳过删除项?
只读取非删除项,那么新建索引上未有版本新闻,不可能管理原有业务的快速照相读;  

MySQL Server层,为何要求拭目以俟展开表的只读事务提交?
等待近期表上的只读事务,能够保险那几个事情不会动用到新建索引  

依照深入分析,等待展开表的只读事务截至较好通晓。因为新索引上未有版本音信,若那一个事情使用新的目录,将会读不到科学的本子记录。

 

那便是说InnoDB是何等管理任何这几个在开创索引在此之前曾经起始,但却一贯未提交的老事务呢?那些职业,由于开始的一段时代为未有读取当前表,由此不会被守候甘休。这么些业务在LX570劲客隔绝品级下,会读取不到准确的版本记录,因为运用的目录上并未版本音信。

 

当然,InnoDB同样思量到了此难点,并应用了一种比较简单介绍的拍卖方案。在目录上保险三个trx_id,标记成立此索引的职业ID。若有叁个比那一个专业更老的作业,筹算利用新建的目录举行快速照相读,那么直接报错。

 

设想如下的面世管理流程(事务隔开等级为奥德赛Tiggo):

session 1:                               session 2:

// 此时创建Global ReadView

select * from t2;

                                       delete from t1 where b = 1;

                                       // idx_t1_b索引上,没有b = 1的项

                                       alter table t1 add index idx_t1_b(b);

// 由于ReadView在delete之前获取

// 因此b = 1这一项应该被读取到

select * from t1 where b = 1;

当session 1实践最终一条select时,MySQL Optimizer会采取idx_t1_b索引举行询问,但是索引上并从未b = 1的项,使用此索引会导致查询出错。那么,InnoDB是何等管理那个状态的吧?

 

拍卖流程:

…

ha_innobase::index_init();

  change_active_index();

    // 判断session 1事务的ReadView是否可以看到session 2创建索引的事务

    // 此处,session 2事务当然不可见,那么prebuilt->index_usable = false

    prebuilt->index_usable = row_merge_is_index_usable(readview, index->trx_id);

…

ha_innobase::index_read();

  // 判断index_usable属性,此时为false,返回上层表定义修改,查询失败

  if (!prebuilt->index_usable)

    return HA_ERR_TABLE_DEF_CHANGED;

 

澳门新萄京:创设_Log表及触发器。MySQL Server收到InnoDB重返的怪诞之后,会将错误报给客商,客商会收到以下错误:

 

mysql> select * from t1 where b = 1;


ERROR 1412 (HY000): Table definition has changed, please retry transaction

2.Online add Index

测试表

  create table t1 (a int primary key, b int)engine=innodb;

  insert into t1 values (1,1),(2,2),(3,3),(4,4);

 

Online Add Index管理流程
SQL

  alter table t1 add index idx_t1_b(b);

 

拍卖流程

  sql_table.cc::mysql_alter_table();

    // 1. 判断当前DDL操作是否可以Inplace进行

    check_if_supported_inplace_alter();

      …

    // 2. 开始进行Online创建的前期准备工作

    prepare_inplace_alter_table();

      …

      // 修改表的数据字典信息

      prepare_inplace_alter_table_dict();

        …

        // 等待InnoDB所有的后台线程,停止操作此表

        dict_stats_wait_bg_to_stop_using_tables();

        …

        // Online Add Index区别与Inplace Add Index的关键

        // 在Online操作时,原表同时可以读写,因此需要

        // 将此过程中的修改操作记录到row log之中

        row0log.cc::row_log_allocate();

          row_log_t* log = (row_log_t*)&buf[2 * srv_sort_buf_size];

          // 标识当前索引状态为Online创建,那么此索引上的

          // DML操作会被写入Row Log,而不在索引上进行更新

          dict_index_set_online_status(index, ONLINE_INDEX_CREATION);

      …

    // 3. 开始进行真正的Online Add Index的操作(最重要的流程)

    inplace_alter_table();

      // 此函数的操作,前部分与Inplace Add Index基本一致

      // 读取聚簇索引、排序、并插入到新建索引中

      // 最大的不同在于,当插入完成之后,Online Add Index

      // 还需要将row log中的记录变化,更新到新建索引中

      row0merge.cc::row_merge_build_index();

        …

        // 在聚簇索引读取、排序、插入新建索引的操作结束之后

        // 进入Online与Inplace真正的不同之处,也是Online操作

        // 的精髓部分——将这个过程中产生的Row Log重用

        row0log.cc::row_log_apply();

          // 暂时将新建索引整个索引树完全锁住

          // 注意:只是暂时性锁住,并不是在整个重用Row Log的

          // 过程中一直加锁(防止加锁时间过长的优化,如何优化?)

          rw_lock_x_lock(dict_index_get_lock(new_index));

            …

          // InnoDB Online操作最重要的处理流程

          // 将Online Copy Table中,记录的Row Log重放到新建索引上

          // 重放Row Log的算法如下:

          // 1. Row Log中记录的是Online创建索引期间,原表上的DML操作

          //  这些操作包括:ROW_OP_INSERT;ROW_OP_DELETE_MARK; …



          // 2. Row Log以Block的方式存储,若DML较多,那么Row Logs可能

          //   会占用多个Blocks。row_log_t结构中包含两个指针:head与tail

          //   head指针用于读取Row Log,tail指针用于追加写新的Row Log;



          // 3.在重用Row Log时,算法遵循一个原则:尽量减少索引树加锁

          //  的时间(索引树加X锁,也意味着表上禁止了新的DML操作)



          //   索引树需要加锁的场景:

          //  (一) 在重用Row Log跨越新的Block时,需要短暂加锁;



          //   (二) 若应用的Row Log Block是最后一个Block,那么一直加锁

          //     应用最后一个Block,由于禁止了新的DML操作,因此此

          //     Block应用完毕,新索引记录与聚簇索引达到一致状态,

          //     重用阶段结束;



          //  (三) 在应用中间Row Log Block上的row log时,无需加锁,新的

          //     DML操作仍旧可以进行,产生的row log记录到最后一个

          //     Row Log Block之上;



          // 4. 如果是创建Unique索引,那么在应用Row Log时,可能会出现

          //   违反唯一性约束的情况,这些情况会被记录到

          //   row_merge_dup_t结构之中

          row_log_apply_ops(trx, index, &dup);

            row_log_apply_op();

              row_log_apply_op_low();

                …

          // 将New Index的Online row log设置为NULL,

          // 标识New Index的数据已经与聚簇索引完全一致

          // 在此之后,新的DML操作,无需记录Row Log

          dict_index_set_online_status();

            index->online_status = ONLINE_INDEX_COMPLETE;

          index->online_log = NULL;

          rw_lock_x_unlock(dict_index_get_block(new_index));

          row_log_free();

      …

    // 4. Online Add Index的最后步骤,做一些后续收尾工作

    commit_inplace_alter_table();

      …

Online Add Index达成剖判
在看完后面解析的InnoDB 5.6.7-RC版本中落实的主导管理流程之后,个人仍旧遗留了多少个问题,主要的主题素材有:

 

Online Add Index是不是扶助Unique索引?

适于的答案是:帮衬(不过存在Bug,前面剖析)。InnoDB扶持Online成立Unique索引。

既是扶持,就能够直面Check Duplicate Key的难题。Row Log中即便存在与索引中千篇后生可畏律的键值怎么管理?怎么检验是否存在同样键值?

InnoDB解决此主题材料的方案也相比简单介绍易懂。其保险了叁个row_merge_dup_t的数据结构,存款和储蓄了在Row log回放进程中碰着的背离唯风度翩翩性冲突的Row Log。应用完Row Log之后,外界剖断是或不是留存Unique冲突(有多少Unique冲突,均会记录),Online成立Unique索引战败。

Row Log是什么的结构,怎样组织的?

在Online Add Index进度中,并发DML产生的校订,被记录在Row Log中。首先,Row Log不是InnoDB的Redo Log,而是每一个正在被Online创建的目录的独自占领结构。

 

Online创设索引,坚守的是先创造索引数据字典,后填充数据的方法。因而,当索引数据字典创建成功以往,新的DML操作就足以读取此索引,尝试进行改善。但是,由于索引结构上的status状态为ONLINE_INDEX_CREATION,由此这个改善无法直接动用到新索引上,而是放入Row Log之中,等待被重播到目录之上。

 

Row Log中,以Block的办法管理DML操作内容的寄放。五个Block的轻重为由参数innodb_sort_buffer_size调节,暗中认可大小为1M (1048576)。开始化阶段,Row Log申请多少个这么的Block。

 

在Row Log重播的历程中,到底供给多长期的锁表时间?

面前的流程深入分析中,也论及了锁表的主题材料(内部为锁新建索引树的操作实现)。

在重放Row log时,有七个状态下,须要锁表:

气象风华正茂:在应用完多个Block,跳转到下三个Block时,需求短短锁表,判定下贰个Block是还是不是为Row Log的尾声三个Block。若不是终极贰个,跳转达成后,释放锁;使用Block内的row log不加锁,客商DML操作照旧能够开展。

意况二:在利用尾数Block时,会一向有所锁。那时差异意新的DML操作。保障最后叁个Block回看实现之后,新索引与聚簇索引记录达到同等状态。

综上解析八个锁表景况,景况二会穷追猛打锁表,可是由于也只是最后一个Block,因而锁表时间也不够长,只会短暂的震慑客商操作,在低峰期,这一个影响是能够选用的。

3. Online Add Index是否也存在与Inplace格局相似的节制?

出于Online Add Index相同的时间也是Inplace格局的,由此Online方式也设有着Inplace方式所存在的主题素材:新索引上缺乏版本音信,因而无法为老事务提供快速照相读。

不唯有如此,相对于Inplace格局,Online格局的封锁更甚一筹,不止抱有小于创建此Index的事体不可动用新索引,同一时候,全体在新索引创制进程中开始的职业,也没办法使用新索引。

其大器晚成加强的限量,在rowmerge.cc::row_merge_read_clustered_index()函数中调治,在聚簇索引遍历完结之后,将新索引的trx_id,赋值为Online Row Log中最大的事体ID。待索引创制完成今后,全数小于那一件事情ID的政工,均不可动用新索引。

在遍历聚簇索引读取数据时,读取的是记录的风行版本,那么此记录是否在Row Log也会设有?InnoDB如什么地点理这种场地?

先是,答案是断定的。遍历聚簇索引读取记录最新版本时,那么些记录有异常的大希望是新业务订正/插入的。这一个记录在遍历阶段,已经被应用到新索引上,于此相同的时间,那几个记录的操作,也被记录到Row Log之中,现身了一条记下在新索引上存在,在Row Log中也存在的景观。

理之当然,InnoDB已经考虑到了那么些主题素材。在重播Row Log的进度中,对于Row Log中的每条记下,首先会咬定其在新索引中是还是不是曾经存在(row0log.c::row_log_apply_op_low()),若存在,则当前Row Log可以跳过(恐怕是将操作类型转变)。

诸如:Row Log中记录的是一个INSERT操作,若此INSERT记录在新索引中大器晚成度存在,那么Row Log中的记录,能够直接废弃(若存在项与INSERT项完全意气风发致);或然是将INSERT调换为UPDATE操作(Row Log记录与新索引中的记录,部分索引列有分裂);

Online Add Index是还是不是留存Bug?

答案同样是肯定的,存在Bug。

 

其间有五个Bug,再度现身方案如下:

create table t1 (a int primary key, b int, c char(250))engine=innodb;

insert into t1(b,c) values (1,'aaaaaaa');

// 保证数据量够多

insert into t1(b,c) select b,c from t1;

insert into t1(b,c) select b,c from t1;

insert into t1(b,c) select b,c from t1;

…

// max(a) = 196591

select max(a) from t1;

// b中同样没有相同项

update t1 set b = a;

session 1                                   session 2

alter table t1 add unique index idx_t1_b(b);

                                           insert into t1(b,c) values (196592,'b');

                                           // 此update,会产生b=196589的重复项

                                           update t1 set b=196589 where a=196582;

                                           delete from t1 where a = 262127;

 

在以上的测量检验中,首先为表盘算充足的数量,指标是session 1做Online Add Index的读取聚簇索引阶段,session 2新的记录也能够被读到。

 

在session 1的Online Add Index实现今后(成功),实践以下多少个指令,结果如下:

mysql> show create table t1;



 ——- ————————————————–

| Table | Create Table

 ——- ————————————————–

| t1 | CREATE TABLE `t1` (

`a` int(11) NOT NULL AUTO_INCREMENT,

`b` int(11) DEFAULT NULL,

`c` char(250) DEFAULT NULL,

PRIMARY KEY (`a`),

UNIQUE KEY `idx_t1_b` (`b`)

) ENGINE=InnoDB AUTO_INCREMENT=262129 DEFAULT CHARSET=gbk |

 ——- ————————————————–

mysql> select * from t1 where a in (196582,196589);

 ——– ——– ——— 

| a | b | c |

 ——– ——– ——— 

| 196582 | 196589
| aaaaaaa |

| 196589 | 196589
| aaaaaaa |

 ——– ——– ——— 

2 rows in set (0.04 sec)

 

能够看出,b上业本来就有了贰个Unique索引,然则表中却存在五个相近的取值为196389的值。

 

此Bug,是拍卖Row Log的重放进度,未详细思忖全数景况招致的。因而,在MySQL 5.6版本牢固此前,慎用!

 

Online Add Index可借鉴之处
在MySQL 5.6.7中学习到四个文本操作函数:一是posix_fadvise()函数,指定POSIX_FADV_DONTNEED参数,可成功读写不Cache:Improving Linux performance by preserving Buffer Cache State  unbuffered I/O in Linux;二是fallocate()函数,钦命FALLOC_FL_PUNCH_HOLE参数,可造成读时清空:Linux Programmer's 马努al FALLOCATE(2) 有相通需要的爱侣,可试用。

 

posix_fadvise函数 POSIX_FADV_DONTNEED参数,首要效用便是放任文件在Cache中的clean blocks。因而,若客户不希望多少个文书占用过多的文件系统Cache,能够依期的调用fdatasync(),然后紧接着posix_fadvise(POSIX_FADV_DONTNEED),清空文件在Cache中的clean blocks,不错的功用!

您恐怕感兴趣的小说:

  • Mysql数据库之索引优化
  • Mysql质量优化案例切磋-覆盖索引和SQL_NO_CACHE
  • mysql质量优化之索引优化
  • MySQL索引之主键索引
  • MySQL索引之集中索引导介绍绍
  • MySQL中的独一索引的简要学习课程
  • MySQL中的联合索引学习课程
  • MySQL查看、创造和删除索引的法子
  • 粗略介绍下MYSQL的索引类型
  • mysql数据库索引损坏及修复资历分享
  • mysql索引务必精通的多少个基本点难题

本文由澳门新萄京发布于数据库,转载请注明出处:澳门新萄京:创设_Log表及触发器

上一篇:澳门新萄京采用的恢复方法 下一篇:没有了
猜你喜欢
热门排行
精彩图文
  • 澳门新萄京采用的恢复方法
    澳门新萄京采用的恢复方法
    set rowcount 20000 delete from UFSystem..ua_log set rowcount 0 原文地址: truncate 表之后,采用的恢复方法 作者: ban仙 一、sybase创建用户数据库的脚本。 设置  trunca
  • Mysql数据库备份工具,逻辑备份
    Mysql数据库备份工具,逻辑备份
          几天前收到某个业务项目,MySQL数据库逻辑备份mysqldump备份失败的邮件,本是在休假,但本着工作认真负责,7*24小时不间断运维的高尚职业情操,开
  • 澳门新萄京:创建数据库邮件,数据库邮件
    澳门新萄京:创建数据库邮件,数据库邮件
      本文转自: 原文: SQL Server数据库邮件使用 SMTP服务器转发邮件,允许用户配置数据库邮件,并通过存储过程 msdb.dbo.sp_send_dbmail 向特定的用户发送邮件,
  • 澳门新萄京:Consul初体验
    澳门新萄京:Consul初体验
      Preface       Today I'm gonna implement a consul inmy environment to discover service of MySQL database and check whetherthe master-slave is working normally.   Introduce       Consul is a to
  • 澳门新萄京:的锁定和阻塞,MySQL长事务导致的
    澳门新萄京:的锁定和阻塞,MySQL长事务导致的
     从前蒙受过,但只限于听同事说加上NOLOCK好一些,后天留神钻探测量检验了下,终于驾驭了,那么加与不加到底差异在哪吧? with(nolock)的功能: 本帖提供