千云物流 -sqlServer在盘古上的使用

原创已于 2024-03-25 09:36:57 修改 · 1k 阅读

18 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sqlserver #数据库 #服务器

于 2024-03-22 17:11:22 首次发布

本文介绍了如何通过优化事务逻辑、调整隔离级别和索引，以及在SQLServer中处理死锁问题。还详细讲解了如何创建和管理数据库分区，包括文件组、分区函数、分区方案和存储统计的计算。

数据库锁测试

注意: 通过优化事务逻辑、调整索引、减少锁的粒度或使用更合适的隔离级别来解决。

-- 读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-创建表
CREATE TABLE TableA (    ID INT PRIMARY KEY,    Data VARCHAR(50)  );  
CREATE TABLE TableB (    ID INT PRIMARY KEY,    Data VARCHAR(50)  );

- 添加测试数据
INSERT INTO TableA (ID, Data) VALUES (1, 'DataA');  
INSERT INTO TableB (ID, Data) VALUES (1, 'DataB');

- 开启一个窗口
BEGIN TRANSACTION;  
-- 事务1锁定TableA的记录  
UPDATE TableA SET Data = 'NewDataA' WHERE ID = 1;  
-- 事务1等待TableB的记录被释放  
WAITFOR DELAY '00:00:10';  -- 等待10秒，以确保事务2已经开始  
UPDATE TableB SET Data = 'NewDataB' WHERE ID = 1;  
COMMIT TRANSACTION;  -- 这行代码实际上不会执行，因为事务会被死锁中断

- 开启第二个窗口
BEGIN TRANSACTION;  
-- 事务2锁定TableB的记录  
UPDATE TableB SET Data = 'NewDataB_2' WHERE ID = 1;  
-- 事务2等待TableA的记录被释放  
WAITFOR DELAY '00:00:05';  -- 等待5秒，以确保事务1已经开始并锁定了TableA  
UPDATE TableA SET Data = 'NewDataA_2' WHERE ID = 1;  
COMMIT TRANSACTION;  -- 这行代码实际上不会执行，因为事务会被死锁中断

数据库表分片测试

为表创建分区并且动态添加分区.

-- 创建分区函数
CREATE PARTITION FUNCTION RangePFN(int) AS RANGE LEFT FOR VALUES (10, 20, 30);
-- 查询分区文件
SELECT * FROM sys.filegroups WHERE name = 'logf5';

-- 添加分区文件
ALTER DATABASE etms_log ADD FILEGROUP logf5;
ALTER DATABASE etms_log ADD FILEGROUP logf10;
ALTER DATABASE etms_log ADD FILEGROUP logf15;
ALTER DATABASE etms_log ADD FILEGROUP logf20;


-- 将文件添加到新创建的文件组
ALTER DATABASE etms_log ADD FILE (
    NAME = 'logf5',  --文件名称
    FILENAME = 'C:\logs\logf5.mdf',  -- 自动增长当前大小的10%。
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
) TO FILEGROUP logf5;

ALTER DATABASE etms_log ADD FILE (
    NAME = 'logf10',
    FILENAME = 'C:\logs\logf10.mdf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
) TO FILEGROUP logf10;

ALTER DATABASE etms_log ADD FILE (
    NAME = 'logf15',
    FILENAME = 'C:\logs\logf115.mdf', 
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
) TO FILEGROUP logf15;

ALTER DATABASE etms_log ADD FILE (
    NAME = 'logf20',
    FILENAME = 'C:\logs\logf20.mdf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
) TO FILEGROUP logf20;

-- 创建分区方案,这里需要创建4个文件是由于文件最后一个需要有文件
CREATE PARTITION SCHEME RangePS AS PARTITION RangePFN TO (logf5, logf10, logf15,logf20);

-- 创建分区表
CREATE TABLE range_table
(
    -- 定义列
    id INT NOT NULL,
    data VARCHAR(100)
    -- 其他列定义
) ON RangePS(id)

-- 插入数据
-- 现在，插入数据的语句将根据 id 列的值将数据分配到不同的分区
INSERT INTO range_table (id, data) VALUES (1, 'Data1');
INSERT INTO range_table (id, data) VALUES (10, 'Data2');
INSERT INTO range_table (id, data) VALUES (11, 'Data3');
INSERT INTO range_table (id, data) VALUES (20, 'Data4');
INSERT INTO range_table (id, data) VALUES (25, 'Data5');

这里创建了表的分片,初始创建了4个分片
在这里插入图片描述


-- 1. 创建新的分区函数，添加新的分区范围
ALTER DATABASE etms_log ADD FILEGROUP logf40;
ALTER DATABASE etms_log 
ADD FILE (NAME = 'logf40', FILENAME =  'C:\logs\logf40.mdf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)  
TO FILEGROUP logf40;  
ALTER PARTITION SCHEME RangePS NEXT USED logf40;  
ALTER PARTITION FUNCTION RangePFN() SPLIT RANGE (40);

在这里插入图片描述

为没有创建分区的表创建分区
这里需要注意,如果你的分区上有别的非聚集索引的方案,就会出现"您将无法切出或切入此表中的数据，因为该表具有非存储对齐索引" 的问题

-- 删除原来的主键
ALTER TABLE sm_log DROP CONSTRAINT PK__sm_log__3213E83F11F9DFAB;
-- 指定分区键创建联合主键,也是聚集索引,并关联到分区方案上
ALTER TABLE sm_log ADD CONSTRAINT PK__sm_log__3213E83F11F9DFAB PRIMARY KEY (id,create_time) ON MonthlyPS(create_time);

删除分区及文件
一定要按照顺序执行以下命令.

-- 删除表上相关分区的索引
DROP INDEX IF EXISTS YourIndexedColumnName ON etms_log; 
-- 删除表
drop table etms_log
-- 删除分区方案和分区函数
DROP PARTITION SCHEME MonthlyPS;
DROP FUNCTION IF EXISTS pf_Monthly;

-- 删除分区文件
ALTER DATABASE etms_log REMOVE FILE logf5;
-- 移除分区filegroup
ALTER DATABASE etms_log REMOVE FILEGROUP logf5;

数据库存储统计

数据库计算统计

	-- 查询分区表的占用大小
	SELECT   
	    o.name AS TableName,  
	    p.partition_number,  
	    SUM(a.total_pages) * 8 AS SizeInKB,  
	    SUM(a.total_pages) * 8 / 1024 AS SizeInMB  
	FROM   
	    sys.partitions p  
	JOIN   
	    sys.allocation_units a ON p.partition_id = a.container_id  
	JOIN   
	    sys.objects o ON p.object_id = o.object_id  
	WHERE   
	    o.type = 'U' -- User tables only  
	    AND o.name = 'range_table' -- Replace with your table name  
	GROUP BY   
	    o.name, p.partition_number  
	ORDER BY   
	    o.name, p.partition_number;
	
	-- 数据库占用情况
	EXEC sp_spaceused;
	```