分区限制是指在要分区的表中,是指触发器在数

2019-09-22 作者:数据库   |   浏览(145)

分区发展历程

     基于表的分区功能为简化分区表的创建和维护过程提供了灵活性和更好的性能。追溯到逻辑分区表和手动分区表的功能.

 

1 .innodb 共享锁(lock in share mode)演示

1.触发器简介

触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。

二.为什么要进行分区

   为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

   大型表除了大小以数百 GB 计算,甚至以 TB 计算的指标外,还可以是无法按照预期方式运行的数据表,运行成本或维护成本超出预定要求。例如发生性能问题、阻塞问题、备份。

Preface

会话1

会话2

SET autocommit=0;

SELECT cityname FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

SET autocommit=0;

SELECT cityname FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

-- 对 city_id=14加共享锁

SELECT * FROM  city WHERE city_id=14 LOCK IN SHARE MODE;

city_id      country_id        cityname CityCode

14     2       22     001

 

 

-- 也对 city_id=14加共享锁

SELECT * FROM  city WHERE city_id=14 LOCK IN SHARE MODE;

city_id      country_id        cityname CityCode

14     2       22     001

-- 当前会话对锁定的记录进行更新操作,等待锁。

UPDATE city  SET cityname='深圳' WHERE city_id =14;

 等待中....

 

 

-- 会话2也对锁定的记录进行更新操作,则会导致死锁退出

UPDATE city  SET cityname='深圳' WHERE city_id =14;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

获得锁后,更新成功

查询:update city set cityname='深圳' where city_id =14

共 1 行受到影响

 

2.触发器类型

1、DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

2、DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

3、登陆触发器:是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败,登录触发器不会触发。

其中 DML 触发器比较常用,根据 DML 触发器触发的方式不同又分为以下两种情况:

after 触发器(之后触发):其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发,且只能定义在表上。

instead of 触发器 (之前触发):instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

 三. 分区的概念

 

 

3.触发器语法结构

AFTER 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  

<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

<method_specifier> ::=  
    assembly_name.class_name.method_name

DDL 触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

登陆触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

 参数介绍:

  • CREATE OR ALTER:创建或者有条件的修改触发器(即要修改的触发器必须已经存在)
  • schema_name:ML触发器所属的模式的名称(即所有者,例如:dbo)。
  • trigger_name:触发器的名称
  • table | view:执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用
  • DATABASE:将 DDL 触发器的范围应用于当前数据库。如果指定,触发器会在当前数据库中发生 event_type 或 event_group 时触发。
  • ALL SERVER:将 DDL 或登录触发器的作用域应用于当前服务器。如果指定,触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发
  • WITH ENCRYPTION:加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。
  • EXECUTE AS:指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。
  • NATIVE_COMPILATION:表示触发器是本地编译的。
  • SCHEMABINDING:指定触发器引用的表不能被删除或更改。
  • FOR | AFTER:AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时,AFTER 是默认值。视图无法定义AFTER触发器。
  • INSTEAD OF:指定执行 DML 触发器而不是触发 SQL 语句,因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。对于 INSTEAD OF 触发器,在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地,在具有指定级联动作 ON UPDATE 的引用关系的表上,不允许 UPDATE 选项。
  • {[DELETE] [,] [INSERT] [,] [UPDATE]} :指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。
  • event_type:是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。
  • event_group:是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。
  • sql_statement:是触发条件和动作。触发条件指定附加条件,以确定尝试的 DML,DDL 或登录事件是否导致执行触发器操作。
  • method_specifier:对于 CLR 触发器,指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符,并且必须作为具有程序集可见性的程序集中的类存在。

    分区范围

           分区范围是指在要分区的表中,根据业务选择表中的关键字段做为分区边界条件, 
           分区后,数据所在的具体位置至关重要,这样才能在需要时只访问相应的分区。          

           注意分区是指数据的逻辑分离,不是数据在磁盘上的物理位置, 数据的位置由文件组来决定,所以一般建议一个分区对应一个文件组。

    Yesterday,I've demonstratated how to rescue a droped and a truncated table based on mysqldump backup.Let's see what's different in rescuing them based on a backup set made by Xtrabackup.Binlog is also indispensable,if we want to rerieve all the incremental data of them.

2.  innodb 排它锁(for update)演示

DML触发器例子

  1. 创建AFTER INSERT 触发器:当下单后更新tbOrderTotalPrice 的统计信息

准备一张订单表(tbOrder),包含订单ID,订单交易金额,订单创建时间,准备订单交易分段时间总额(tbOrderTotalPrice ),用来统计每个月的交易合计金额

图片 1图片 2

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tSumTotalOrderPrice]    Script Date: 2018/5/15 11:10:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tSumTotalOrderPrice] on [dbo].[tbOrder]
AFTER INSERT
AS
BEGIN
 DECLARE @DT INT
 SET @DT= CONVERT(varchar(6),GETDATE(),112)

 DECLARE @SUM DECIMAL(18, 2)
 SET @SUM=(SELECT SUM(OPrice) FROM tbOrder WHERE @DT = CONVERT(varchar(6),CreateDT,112))

 IF(EXISTS(SELECT * FROM tbOrderTotalPrice WHERE YearMoth=@DT))
 BEGIN
  UPDATE tbOrderTotalPrice SET TotalPrice=@SUM WHERE YearMoth=@DT
 END
 ELSE
 BEGIN
  INSERT INTO tbOrderTotalPrice VALUES (@DT,@SUM)
 END
END

GO

执行INSERT 行为:INSERT INTO [dbo].[tbOrder]([OPrice],[CreateDT])VALUES(9,GETDATE())

图片 3

图片 4

 2.创建 instead of  insert 触发器,当添加用户成绩记录时候,验证学号和学科存在才能进行添加操作

准备学生表(tbStudent),学科表(tbSubject),成绩表(tbStuSubScore)

图片 5 图片 6 

图片 7

在学生表中添加测试学生,在学科标准添加测试学科

图片 8 图片 9

新增instead of insert 触发器,在执行INSERT 语句前验证学号和学科存在才能进行添加操作

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tCheckStuSubScore]    Script Date: 2018/5/17 14:20:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  yangyi
-- Create date: 2018/5/17
-- Description: 新增学科分数
-- =============================================
CREATE TRIGGER [dbo].[tCheckStuSubScore] ON [dbo].[tbStuSubScore]
   INSTEAD OF INSERT
AS 
BEGIN
 --验证学号是否存在
 IF(NOT EXISTS(SELECT * FROM tbStudent WHERE StuID=(SELECT StuID FROM inserted)))
 BEGIN
  PRINT('验证学号不否存在')
  ROLLBACK TRANSACTION
 END
 ELSE
 BEGIN
  --验证学科是否存在
  IF(NOT EXISTS(SELECT * FROM tbSubject WHERE SubID=(SELECT SubID FROM inserted)))
  BEGIN
   PRINT('验证学科不否存在')
   ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
   --执行INSERT
   INSERT INTO tbStuSubScore SELECT * FROM inserted
  END
 END
END

GO

注意:

在触发器语句中用两个特殊的表一个是deleted表和inserted。它们是通过触发器操作自动创建驻留在内存中的临时表。
Deleted表用于存储 DELETE和 UPDATE语句所影响的行的复本。在执行DELETE或 UPDATE语句时,行从触发器表中删除,并传输到 deleted表中。Deleted表和触发器表通常没有相同的行。
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本

1.插入操作(Insert) Inserted表有数据,Deleted表无数据
2.删除操作(Delete) Inserted表无数据,Deleted表有数据
3.更新操作(Update) Inserted表有数据(新数据),Deleted表有数据(旧数据)

测试:

1>.执行INSERT 添加一条学科不存不正常在的学生成绩记录,执行失败

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('001',100,0,GETDATE())  

2.>执行INSERT 添加一条学号不存不正常在的学生成绩记录,执行失败

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('004',100,1,GETDATE())

图片 10 图片 11

3.>执行一条正常的学生成绩记录,执行成功

INSERT INTO [dbo].[tbStuSubScore]([StuID],[Score],[SubID],[CreateDT])VALUES('001',100,1,GETDATE())

图片 12

    分区键                 

           在我下面的演示中,有一个库存表,我选择了UpByMemberID(会员ID) 作为分区键。 对表和索引进行分区的第一步就是定义分区的关键数据。

 

会话1

会话2

SET autocommit=0;

SELECT * FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

SET autocommit=0;

SELECT * FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

-- 对 city_id=14加for update 排它锁

SELECT cityname FROM  city WHERE city_id=14 FOR UPDATE ;

cityname

11

 

 

-- 可以查询

SELECT cityname FROM  city WHERE city_id=14

cityname

11

-- 但不能对 city_id=14加for update 排它锁

SELECT cityname FROM  city WHERE city_id=14 FOR UPDATE ;

等待中...

-- 更新后,释放锁

UPDATE city  SET cityname='深圳' WHERE city_id =14;

COMMIT;

 

 

获取锁 for update共享锁,值还是11

cityname

11

   索引分区     

           除了对表的数据集进行分区之外,还可以对索引进行分区, 使用相同的函数对表及其索引进行分区通常可以优化性能
       在下面的第六步中有创建分区索引。

               

Framework

三.创建分区实现

   在test库 添加四个文件组,  用于存储每个分区的数据,这里有四个文件组对应四个分区

       多个文件组是为了有助于优化性能和维护,应使用文件组分离数据。文件组的数目一定程度上由硬件资源决定:一般情况下,文件组数最好与分区数相同,

    并且这些文件组通常位于不同的磁盘上(演示有条有限,只在一个磁盘上做逻辑盘存放)。

1 --第一步:创建四个文件组 
2 alter database test add filegroup ByIdGroup1
3 alter database test add filegroup ByIdGroup2
4 alter database test add filegroup ByIdGroup3
5 alter database test add filegroup ByIdGroup4

--第二步: 创建四个ndf文件,对应到各文件组中,FILENAME文件存储路径
ALTER DATABASE test ADD FILE(
NAME='File1',
FILENAME='C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAtestFile1.ndf',
SIZE=5MB,
FILEGROWTH=5MB)
TO FILEGROUP ByIdGroup1

ALTER DATABASE test ADD FILE(
NAME='File2',
FILENAME='E:testFile2.ndf',
SIZE=5MB,
FILEGROWTH=5MB)
TO FILEGROUP ByIdGroup2

ALTER DATABASE test ADD FILE(
NAME='File3',
FILENAME='E:testFile3.ndf',
SIZE=5MB,
FILEGROWTH=5MB)
TO FILEGROUP ByIdGroup3


ALTER DATABASE test ADD FILE(
NAME='File4',
FILENAME='E:testFile4.ndf',
SIZE=5MB,
FILEGROWTH=5MB)
TO FILEGROUP ByIdGroup4

   执行完成后,查看如下图所示:

  图片 13

--第三步:创建分区函数(每个分区的边界值)

 每个会员统计的产品数

图片 14

 

--record: 126797 Partition1 --PRIMARY
SELECT COUNT(1) FROM dbo.Product WHERE UpByMemberID<=1740
--record: 90882 Partition2
SELECT COUNT(1) FROM dbo.Product WHERE UpByMemberID>1740 AND UpByMemberID<=3000
--record: 4999999 Partition3
SELECT COUNT(1) FROM dbo.Product WHERE UpByMemberID>3000 AND UpByMemberID<=9708
--record: 4999999 Partition4
SELECT COUNT(1) FROM dbo.Product WHERE UpByMemberID>9708 AND UpByMemberID<=9709
--record: 2018464 Partition5 ---ByIdGroup4
SELECT COUNT(1) FROM dbo.Product WHERE UpByMemberID>9709

CREATE PARTITION FUNCTION pf_UpByMemberID(int) 
AS RANGE LEFT FOR VALUES (1740,3000,9708,9709)

 

 执行完后如下图所示:

     图片 15

 

   --第四步:创建分区方案

CREATE PARTITION SCHEME ps_UpByMemberID
AS PARTITION pf_UpByMemberID TO ([PRIMARY], [ByIdGroup1],[ByIdGroup2],[ByIdGroup3],[ByIdGroup4])

  执行完后如下图所示:

    图片 16

 

--第五步:创建分区表

   右击要分区的表-->存储-->创建分区-->选择分区列(这里UpByMemberID)-->选择分区函数

    图片 17

图片 18

图片 19

图片 20

图片 21

 

 

图片 22

--第六步创建分区索引

CREATE NONCLUSTERED INDEX [ixUpByMemberID] ON [dbo].[Product] 
(
    [UpByMemberID] ASC
)
INCLUDE ( [Model]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

图片 23

-- 最后  查看各分区有多少行数据
select $PARTITION.pf_UpByMemberID([UpByMemberID]) as Patition,COUNT(*) countRow from dbo.product
group by $partition.pf_UpByMemberID([UpByMemberID])

   查出有五个分区(带主分区),以及各分区的数量

   图片 24

    最后看下是否用了分区索引

      图片 25

 

sql server 分区的优势:

  1. 当表和索引变得非常大时,分区可以将数据分为更小、更容易管理的部分。
  2. 减少索引维护时间。
  3. 常用的where条件字段做分区依据是较佳的。
  4. 并行操作获得更好的性能, 可以改善在极大型数据集(例如数百万行)中执行大规模操作的性能。
  5. 一般情况下,文件组数最好与分区数相同。文件组允许您将各个表放置到不同的物理磁盘上

 

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row

 

Precedure

 

Step 1: Destroy the table.

 

Generate a backup with Xtrabackup first.

 1 [root@zlm2 07:57:40 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 4 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 5 180727 07:57:43 innobackupex: Starting the backup operation
 6 
 7 IMPORTANT: Please check that the backup run completes successfully.
 8            At the end of a successful backup run innobackupex
 9            prints "completed OK!".
10 
11 ... //Omitted the procedure.
12 
13 180727 07:57:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
14 xtrabackup: The latest check point (for incremental): '10141351562'
15 xtrabackup: Stopping log copying thread.
16 .180727 07:57:56 >> log scanned up to (10141351571)
17 
18 180727 07:57:56 Executing UNLOCK TABLES
19 180727 07:57:56 All tables unlocked
20 180727 07:57:56 [00] Copying ib_buffer_pool to /data/backup/2018-07-27_07-57-43/ib_buffer_pool
21 180727 07:57:56 [00]        ...done
22 180727 07:57:56 Backup created in directory '/data/backup/2018-07-27_07-57-43/'
23 MySQL binlog position: filename 'mysql-bin.000035', position '954887', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220'
24 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/backup-my.cnf
25 180727 07:57:56 [00]        ...done
26 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/xtrabackup_info
27 180727 07:57:56 [00]        ...done
28 xtrabackup: Transaction log of lsn (10141351562) to (10141351571) was copied.
29 180727 07:57:56 completed OK!

 

**Continue to execute dml operation.**

本文由小鱼儿玄机30码发布于数据库,转载请注明出处: 分区限制是指在要分区的表中,是指触发器在数

关键词: 小鱼儿玄机30码