SQL SERVER实践应用--TED透明数据加密及性能测试(转)-白红宇

SQL SERVER实践应用--TED透明数据加密及性能测试(转)

阅读量：5160 次

发布时间：2019-06-13

本文共 5009 字，大约阅读时间需要 16 分钟。

---------------------------------------------------------------------

--创建主密钥

use master

Create MASTER KEY ENCRYPTION

BY PASSWORD = 'zhouwei123!'

--创建证书,用于透明数据加密

CREATE CERTIFICATE TDE_Server_Certificate

WITH SUBJECT = 'Server-level cert for TDE'

--第一步：现在开始透明加密

USE cte

CREATE DATABASE ENCRYPTION KEY--创建数据库加密密钥

WITH ALGORITHM = TRIPLE_DES_3KEY--加密方式

ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使用服务器级证书加密

--第二步打开加密开关

ALTER DATABASE cte

SET ENCRYPTION ON

--查看数据库是否加密

SELECT name ,is_encrypted

FROM sys.databases

--第三步在cte及nocet中创建相同表

use cte

create table GoodsInfoCte

(

Autoid int identity(1,1) primary key,

GoodsId int,

GoodsName varchar(50),

CusID int,

CusName varchar(50)

);

use nocte

create table GoodsInfoNocte

(

Autoid int identity(1,1) primary key,

GoodsId int,

GoodsName varchar(50),

CusID int,

CusName varchar(50)

);

--第四步：清空缓存

DBCC FLUSHPROCINDB(5)

DBCC FLUSHPROCINDB(9)

---------------------------------------------------------------------

插入100000记录对比

--insert nocte..GoodsInfoNocte 100000

declare @b int

set @b=1

while(@b<100000)

begin

insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)

select 123,'pc-dell',123456,'张三×××××××××××'

set @b=@b+1

end

insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)

select GoodsId,GoodsName,CusID,CusName from logshink..GoodsInfo

--insert nocte..GoodsInfoNocte 100000

declare @b int

set @b=1

while(@b<100000)

begin

insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)

select 123,'pc-dell',123456,'张三×××××××××××'

set @b=@b+1

end

insert into nocte..GoodsInfoNocte(GoodsId,GoodsName,CusID,CusName)

select GoodsId,GoodsName,CusID,CusName from logshink..GoodsInfo

执行时间对比（1-6 是循环语句执行时间，7-10从logshink..GoodsInfo插入）：

1	2	3	4	5	6	7	8	9	10
37	38	36	37	36	37	CPU 时间 = 1903 毫秒，占用时间 = 3042 毫秒	CPU 时间 = 2028 毫秒，占用时间 = 2921 毫秒	CPU 时间 = 1981 毫秒，占用时间 = 2999 毫秒	CPU 时间 = 1965 毫秒，占用时间 = 3244 毫秒
29	28	29	30	29	30	CPU 时间 = 515 毫秒，占用时间 = 1223 毫秒	CPU 时间 = 562 毫秒，占用时间 = 1175 毫秒	CPU 时间 = 483 毫秒，占用时间 = 1105 毫秒	CPU 时间 = 515 毫秒，占用时间 = 1275 毫秒

I0对比：

表'GoodsInfoCte'。扫描计数0，逻辑读取322516 次，物理读取0 次，预读658 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'GoodsInfo'。扫描计数1，逻辑读取748 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'GoodsInfoNocte'。扫描计数0，逻辑读取322516 次，物理读取0 次，预读658 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'GoodsInfo'。扫描计数1，逻辑读取748 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

占用空间对比：

---------------------------------------------------------------------

查询前300000对比

select top 300000 * from nocte..GoodsInfoNocte

select top 300000 * from cte..GoodsInfoCte

实际执行计划对比（左：GoodsInfoNocte，右：GoodsInfoCte）

执行时间对比（上：GoodsInfoNocte，下：GoodsInfoCte）：

1	2	3	4	5	6
CPU 时间= 109 毫秒，占用时间= 3338 毫秒。	CPU 时间= 62 毫秒，占用时间= 3285 毫秒。	CPU 时间= 109 毫秒，占用时间= 3263 毫秒。	CPU 时间= 125 毫秒，占用时间= 3271 毫秒。	CPU 时间= 234 毫秒，占用时间= 3353 毫秒。	CPU 时间= 265 毫秒，占用时间= 3353 毫秒。
CPU 时间= 234 毫秒，占用时间= 3283 毫秒。	CPU 时间= 250 毫秒，占用时间= 3310 毫秒。	CPU 时间= 187 毫秒，占用时间= 3291 毫秒。。	CPU 时间= 78 毫秒，占用时间= 3291 毫秒。	CPU 时间= 125 毫秒，占用时间= 3282 毫秒。	CPU 时间= 156 毫秒，占用时间= 3323 毫秒。