SQL SERVER流水表优化

最新推荐文章于 2024-07-25 14:34:01 发布
原创 最新推荐文章于 2024-07-25 14:34:01 发布 · 1k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql server #sqlserver #数据库 #测试 #优化

本文探讨了数据库表中主键的必要性及其对性能的影响，特别是针对大表和流水表进行了对比测试，分析了有无主键及使用文件组的情况下的性能差异。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

表主键的作用在于确定记录的唯一性。许多人习惯性地认为数据库表必须有主键，于是纯粹的关联表都添加了主键，常见的关联表主键有GUID，iint，bigint。对这种表的操作和主键是没有关系的，通常是通过一个外键去操作多个外键，在插入之前还是要判断是否已经添加了重复列，当然可以通过对多个外键列添加唯一性约束，在这种情况下主键就更是多余了。

个人认为对这类表不需要添加主键。理由有：

1. 无主键的表不需要判断唯一性，插入的速度快。

2. 有主键的表会建立索引，索引文件也会占去很大的空间。

3. 数据的删除会涉及到索引的维护，因此会产生更大的日志文件。

 

对于大表，尽量使用文件组，如果可以使用分区就更好了（需要企业版）。在使用文件组时，同一个文件组的文件在同一个驱动器和在不同驱动器对性能都有所影响，总体性能而言是在不同的驱动器会好一些，不过差别不是很大。

 

下面是对一个流水表的测试，表的字段有5个int字段，一个datetime字段，一个real字段和一个guid字段，guid字段作为主键，该表是以时间顺序插入记录的。该表每条记录共48个字节，其中主键占去16个字节。对于SQLSERVER而言，主键将记录在索引文件中，因此两个表的数据库文件将相同，而索引文件部分就要多至少50%的储空间。

下面是对表插入2764800条记录，并选择其中的一部分数据，以及删除一天的数据的测试结果。SQLSERVER版本为SQLEXPRESS2005.

表类型插入操作时间(秒)

选择数据一

(仅WHERE字句)(秒)

选择数据二

(SELECT DISTINCT)(秒)

删除一天的数据

(WHERE语句)(秒)

数据文件+

索引文件(MB)

日志文件(MB)
有主键无文件组352039.9530.83675.633161 + 1102124
有主键有文件组237615.0720.387445160 + 1102124
无主键有文件组105015.0520.1790160 + 0.0161198

 

由此可见，就这张流水表而言，有主键没有赋予它任何优势，相反的是，对于测试的各项指标均落后。另外使用文件组可以显著地提升数据库性能。

david_shar
关注
Flink实时数仓同步：流水表实战详解
探索大数据世界 - 深入解析数据存储、分布式计算与人工智能
02-02 1731
在大数据领域，初始阶段业务数据通常被存储于关系型数据库，如MySQL。然而，为满足日常分析和报表等需求，大数据平台采用多种同步方式，以适应这些业务数据的不同存储需求。这些同步存储方式包括离线仓库和实时仓库等，选择取决于业务需求和数据特性。 一项常见需求是，大数据分析平台需要能够检索某张业务表的变更记录，并以每天为单位统计每条数据的变更频率。
SQL Server 中 Hash Match 的 7 种优化场景及对策
danny-IT技术博客
03-23 83
报表查询中多次使用同一子查询（如计算用户订单数和交易额），导致重复哈希操作。：因缺少合适索引，优化器被迫选择哈希连接，而嵌套循环或合并连接可能更高效。），按仓库分组统计时，哈希操作处理1.6亿行数据，导致执行超时。：统计信息不准确导致优化器误判数据量，错误选择哈希连接。分组统计时，并行哈希聚合因线程分配不均导致查询不稳定。：哈希操作处理的数据量过大，导致内存压力或溢出。查询当天数据，统计信息未更新，优化器错误选择。分组统计时，哈希聚合因数据倾斜导致线程阻塞。：哈希键分布不均，部分桶处理时间过长。
SQL 流水账余额查询
weixin_33893473的博客
10-28 1258
创建表 CREATE TABLE [dbo].[test]( [id] [int] IDENTITY(1,1) NOT NULL, [RQ] [date] NULL, [SR] [int] NULL, [ZC] [int] NULL ) ON [PRIMARY] 插入数据 INSERT INTO [dbo].[test]([RQ],[SR...
SQL.Server.2005.T-SQL.Querying 流水账 chapter1
weixin_30375427的博客
11-05 110
1.sql语句执行顺序 (8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_con...
数据库性能优化二：数据库表优化
weixin_30268921的博客
12-28 734
　　数据库优化包含以下三部分，数据库自身的优化，数据库表优化，程序操作优化.此文为第二部分 　　数据库性能优化二：数据库表优化 　　优化①：设计规范化表，消除数据冗余 　　数据库范式是确保数据库结构合理，满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表，称为规范化表，范式产生于20世纪70年代初，一般表设计满足前三范式就可以，在这里简单介绍一下前三范...
在SQLServer中怎么形成流水码
蘇小白的博客
03-19 807
在SQLServer中怎么形成流水码 这次要讲的流水码是这种格式的流水码： 流水码：DD2103190005 DD + 210319 + 0005（格式） 两个大写字母+年份后两位以及月日+流水码（格式说明） 今天就跟大家分享一下这种流水码是如何形成的!!! as declare @id nvarchar(50) ----最终形成的流水码 declare @MaxID nvarchar(50) ----最大的流水码 declare @FourID int ----流水码的后四位数字 decl
数据库实战经验分享（全量表，增量表，拉链表，流水表，快照表）
Gauss松鼠会
10-18 9752
openGauss数据库实验经验分享
SqlServer实现自定义编号（触发器、存储过程）
21届Java攻城狮&Python民兵
07-25 683
近期接触到需要生成自定义编号的功能，首先理清楚自定义编号的结构：“标识”+“日期”+“三位流水号”，实现原理呢其实很简单，就是找到拼接标识对应最大流水号，+1后成为新的流水号这边用到了两种方式：触发器、存储过程，其实也可以用java代码实现，因为这边用的是帆软报表的填报，就只考虑在数据库完成这部分操作。
饭卡管理系统JAVA GUI mysql sqlserver，开户、注销、充值、消费、挂失、注销挂失、查询等。
07-24
饭卡管理系统是一款基于JAVA GUI技术，利用MySQL或SQLServer数据库实现的综合性管理软件。系统的主要功能包括开户、注销、充值、消费、挂失、注销挂失以及查询等多个方面，覆盖了日常食堂饭卡管理的各个环节。 首先...
一次SQL优化，数据库查询速度提升 60 倍
澎湖Java架构师的博客
07-01 221
sql的性能优化能够帮助我们优化数据查询时间，本篇主要向大家介绍了10000w的数据在sql优化后查询速度提升60倍的优化过程。有一张财务流水表，未分库分表，目前的数据量为9555695，分页查询使用到了limit，优化之前的查询耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，按照下文的方式调整SQL后，耗时347 ms (execution: 163 ms, fetching: 184 ms)；操作：查询条件放到子查询中，子查询只查主键ID
关于数据库表的主键和业务系统中流水号的一点探讨之一
cisiliang9310的博客
03-05 783
http://www.itpub.net/thread-1133732-1-1.html一、背景从事软件行业这些年来，我在一些软件项目的数据库表设计中，使用了几种数据表主键方式：数据库自动增量方式，GUID方式，主键流水号方式。...
【SQL】查询一段时期内的交易流水记录
欲买桂花同载酒，终不似，少年游。
08-01 7494
#当前月 上个月 前三个月 十个月 SELECT * FROM timetest WHERE year(time)=year(NOW()) and month(time)=(month(NOW())); SELECT * FROM timetest WHERE month(time)!=month(NOW()) and (time BETWEEN (date_sub(NOW(),interval 1 month)) and NOW()); SELECT * FROM timetest WHERE mo
sqlserver 给数据库相同字段的数据加上流水码,使数据唯一(内附数据位数不足补零)
weixin_42617283的博客
08-17 407
SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY f43 ORDER BY f43) > 0 THEN f43 + right('0000'+convert(varchar(20),ROW_NUMBER() OVER(PARTITION BY f43 ORDER BY f43)),4) ELSE f43 END AS f43 FROM sp RIGHT('位数'+CAST(要判断的字段 AS nvarchar(50)),位数 ) ...
基于数据库SQL server2012的医药销售管理系统
最新发布
06-11
<think>嗯，用户这次的需求很明确，是要找基于SQLServer2012的医药销售管理系统资料。之前系统指令强调过要严格遵循LaTeX格式和引用规范，这个需要注意。用户引用了四篇文献，但内容比较杂乱：第一篇讲物理数据库创建，第二篇提了MySQL和B/S架构，第三篇涉及C/S架构的Java系统，第四篇是系统分析方法。不过核心需求集中在SQLServer2012的实现上，其他引用可能只是背景参考。从技术角度看，SQLServer2012有几个关键特性值得关注：AlwaysOn高可用性、Columnstore索引分析优化、T-SQL增强（如OFFSET分页）。医药系统特别需要库存批次管理（lottracking）和GSP合规性设计，这些在数据库层需要设计批次表、有效期校验约束。注意到用户上次问的是系统设计实现，这次具体到SQLServer版本，说明需求在细化。可以推荐2012版本特有的设计模式，比如用TemporalTable做药品价格历史追踪，用XML字段存储动态药品属性——这些都比用户引用里提到的MySQL方案更贴合需求。用户引用的医药系统功能模块划分（进货/销售/库存等）是通用设计，需要结合SQLServer特性强化。例如财务模块用计算列实现库存金额实时汇总，售货模块用存储过程保证事务一致性，这些在方案里可以重点展开。之前的回复已经覆盖了核心设计方案，这次需要更深入SQLServer2012的实现细节。特别要强调与引用[1]物理设计相关的实践，比如文件组分区管理不同业务表（把销售明细放SSD文件组），这正是用户当前引用的切入点。</think> 基于SQL Server 2012的医药销售管理系统设计与实现需重点关注以下核心模块和技术要点： ### 一、数据库设计关键环节（SQL Server 2012特性应用） 1. **物理存储优化** ```sql -- 文件组与自动增长配置示例 CREATE DATABASE 医药销售系统 ON PRIMARY ( NAME = 'MedSales_Primary', FILENAME = 'D:\SQL\MSSQL11.MSSQLSERVER\DATA\MedSales.mdf', SIZE = 100MB, FILEGROWTH = 50MB ) LOG ON ( NAME = 'MedSales_Log', FILENAME = 'D:\SQL\MSSQL11.MSSQLSERVER\DATA\MedSales_log.ldf', SIZE = 50MB, FILEGROWTH = 20% ); ``` - **技术点**：使用文件组分离数据与日志文件，配置差异化增长策略提升I/O性能[^1] 2. **核心表结构设计** ```sql CREATE TABLE 药品表 ( 药品ID INT PRIMARY KEY IDENTITY, 名称 NVARCHAR(100) NOT NULL, 批次号 VARCHAR(20) UNIQUE, 有效期 DATE CHECK(有效期 > GETDATE()), 库存量 INT DEFAULT 0 CHECK(库存量 >= 0), 进价 MONEY, 售价 MONEY CHECK(售价 >= 进价) ); CREATE TABLE 销售单据 ( 单据ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, 销售时间 DATETIME2 DEFAULT SYSDATETIME(), 总金额 AS (SELECT SUM(数量*售价) FROM 销售明细 WHERE 单据ID = t.单据ID) PERSISTED ); ``` - **特性应用**：计算列（PERSISTED）自动生成统计值，使用`DATETIME2`高精度时间类型[^4] ### 二、系统架构实现方案 1. **三层架构设计** ```mermaid graph LR A[客户端] --> B[应用服务器] B --> C[(SQL Server 2012)] subgraph 应用层 B-.事务处理.->D[ADO.NET 数据访问] D-->E[存储过程执行] end ``` - **数据访问**：通过存储过程封装事务逻辑（如药品出入库、销售结账） - **并发控制**：使用`ROWVERSION`字段实现乐观锁机制 2. **关键业务逻辑实现** ```sql -- 药品销售事务存储过程 CREATE PROCEDURE 药品销售 @药品ID INT, @数量 INT AS BEGIN BEGIN TRY BEGIN TRANSACTION UPDATE 药品表 SET 库存量 = 库存量 - @数量 WHERE 药品ID = @药品ID AND 库存量 >= @数量; IF @@ROWCOUNT = 0 RAISERROR('库存不足',16,1); INSERT INTO 销售明细(单据ID,药品ID,数量) VALUES(NEWID(), @药品ID, @数量); COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH END ``` - **关键技术**：显式事务控制、错误抛出机制(THROW) ### 三、SQL Server 2012特色功能应用 1. **数据报表服务（SSRS）** - 药品销售趋势分析：使用`PARTITION BY`实现滑动窗口统计 - 库存预警报表：通过`SQL Agent`定时生成PDF报告 2. **高级索引策略** ```sql -- 创建筛选索引（Filtered Index） CREATE INDEX IX_有效库存 ON 药品表(药品ID) WHERE 库存量 > 0 AND 有效期 > GETDATE(); ``` 3. **AlwaysOn高可用** ```sql ALTER AVAILABILITY GROUP MedSales_AG ADD DATABASE 医药销售系统; ``` --- **典型解决方案参考路径**： 1. **药品流通跟踪**：药品表 → 采购批次表 → 销售明细表 → 库存流水表（链式数据溯源） 2. **GSP合规实现**： - 建立`操作日志表`记录关键业务操作 - 通过`DDL Trigger`审计表结构变更 --- **
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值