22.1 SQL Server数据库优化
SQL Server数据库优化
硬件优化
如果是数据库服务器的硬件条件跟不上，可以考虑一下几个情况。
1）使用更好的硬盘，提高IO。
2）升级网络，提高网速。
3）增加服务器的内存。
4）将数据库服务器放到一个单独的服务器上。
索引优化
如果不了解索引基础知识，参考索引简介、唯一索引、筛选索引、计算列上的索引
索引如果使用的好，可以使查询速度有质的飞跃。所以如果遇到查询速度慢问题，可以优先考虑索引优化。
1）确保每张表都要有主键
创建主键时就会给表自动创建聚集索引树，并将表数据按照主键的顺序排序。这样根据主键查找数据、根据主键排序等操作的效率会大大提升。
主键（聚集索引）只能有一个，因为数据的存储顺序只能有一种。
聚集索引使用参考聚集索引
2）合理创建聚集索引
可以在如下列上创建非聚集索引：
- Where子句常用到的列
- 用于连接其他表的外键列
- Select子句常选中的列
- Order by子句常用到的列
非聚集索引使用参考非聚集索引
3）创建适当的覆盖索引
参考覆盖索引
4)整理索引碎片
查看索引碎片信息
查看SampleDb数据库account表的索引碎片：
| use SampleDb | |
| DBCC SHOWCONTIG(account) |
DBCC SHOWCONTIG是显示指定的表的数据和索引的碎片信息。
查询结果解释如下：
| 字段 | 解释 |
|---|---|
| Page Scanned（扫描页数） | 如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。 |
| Extents Scanned（扫描扩展盘区数） | 用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。 |
| Extent Switches（扩展盘区开关数） | 该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。 |
| Avg. Pages per Extent（每个扩展盘区上的平均页数） | 该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片 |
| Scan Density [Best Count:Actual Count]（扫描密度［最佳值:实际值］） | DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。 |
| Logical Scan Fragmentation（逻辑扫描碎片） | 无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。 |
| Extent Scan Fragmentation（扩展盘区扫描碎片） | 无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。 |
| Avg. Bytes Free per Page（每页上的平均可用字节数） | 所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。 |
| Avg. Page Density (full)（平均页密度（完整）） | 每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片 |
整理索引碎片
1.删除索引并重建
这种方式有如下缺点：
索引不可用：在删除索引期间，索引不可用。
阻塞：卸载并重建索引会阻塞表上所有的其他请求，也可能被其他请求所阻塞。
对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建，因为非聚集索引中有指向聚集索引的指针)。
唯一性约束：用于定义主键或者唯一性约束的索引不能使用DROP INDEX语句删除。而且，唯一性约束和主键都可能被外键约束引用。在主键卸载之前，所有引用该主键的外键必须首先被删除。尽管可以这么做，但这是一种冒险而且费时的碎片整理方法。
因此，不建议在生产数据库，尤其是非空闲时间不建议采用这种技术。
2.重建索引
使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于上面那种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长。
通过SMSS：
上面那个是重建索引，下面是重新组织索引。
通过SQL：
单个：
| ALTER INDEX PK_account ON dbo.account | |
| REBUILD; |
表中所有：
| ALTER INDEX ALL ON dbo.account | |
| REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON); |
2.重新组织索引
这种方式不会重建索引，也不会生成新的页，仅仅是整理叶级数据，不涉及非叶级，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前两种。
重新组织单个索引
| ALTER INDEX PK_account | |
| ON dbo.account | |
| REORGANIZE; |
重新组织表中所有索引
| ALTER INDEX ALL ON dbo.account | |
| REORGANIZE; |
详细信息参考官方文档：重建和重新组织索引
将SQL代码迁移至数据库中
优点：
将客户端的大量SQL代码迁移至数据库中能减少SQL在网络传输过程中的开销。
使用存储过程、视图、自定义函数等数据库特性来使客户端代码更加简洁，这样的SQL也更利于复用。
缺点：
不利于使用ORM框架的特性，因此降低了开发效率。而且将客户端应用的业务逻辑交给数据库完成增加了客户端与数据库的耦合度，不利于今后或者说几乎很难再更换其他数据库。
因此我个人是不喜欢这种方法。
SQL优化
不同的SQL可能都能完成需求，但是好的SQL可以大大提高查询效率。
下面是一些比较好的SQL使用习惯：
1、 查询中尽量不要使用 “select *”
（3）
比如：
| -- 好 | |
| select emp_name tel from employee | |
| -- 坏 | |
| select * from employee |
使用select *的话会增加解析的时间，另外会把不需要的数据也给查询出来，数据传输也是
耗费时间的，比如text类型的字段通常用来保存一些内容比较繁杂的东西，如果使用select *则会把该字段也查询出来。
数据库无法利用“覆盖索引”。
2、避免查询时数据类型转换
比如：
| -- 好(salary是flaot类型) | |
| select emp_name from employee where salary>3000.0 | |
| -- 坏 | |
| select emp_name from employee where salary>3000 |
分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。
还有就是避免两个不同类型的列进行表连接
比如
| SELECT column_list FROM small_table, large_table | |
| WHERE smalltable.float_column = large_table.int_column |
当连接两个不同类型的列时，其中一个列必须转换成另一个列的类型，级别低的会被转换成高级别的类型，转换操作会消耗一定的系统资源;
如果你使用两个不同类型的列来连接表，其中一个列原本可以使用索引，但经过转换后，优化器就不会使用它的索引了
在这个例子中，SQL Server会将int列转换为float类型，因为int比float类型的级别低，large_table.int_column上的索引就不会被使用，但smalltable.float_column上的索引可以正常使用。
3、谨慎使用模糊查询
| -- 好 | |
| select emp_name tel from employee where emp_name like 'paral%' | |
| -- 坏 | |
| select * from employee where emp_name like '%paral%' |
当模糊匹配以%开头时，该列索引将失效，若不以%开头，该列索引有效。
4、应尽量避免在where子句中对字段进行函数操作
| -- 好 | |
| select id from t where name like 'abc%' | |
| select id from t where createdate>='2022-01-01' and createdate<='2022- | |
| 05-01' | |
| -- 坏 | |
| select id from t where substring(name,1,3)='abc' | |
| select id from t where datediff(day,createdate,'2022-05-01')=0 |
这将导致引擎放弃使用索引而进行全表扫描
5、优先使用UNION ALL，避免使用UNION
| -- 好 | |
| select name from student | |
| union all | |
| select name from teacher | |
| -- 坏 | |
| select name from student | |
| union | |
| select name from teacher |
UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。
一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。
还有一种情况，如果业务上能够确保不会出现重复记录就使用UNION ALL。
6、应尽量避免在 where 子句中对字段进行表达式操作
| -- 好 | |
| select id from t where num=100*2 | |
| -- 坏 | |
| select id from where num/2=100 |
这将导致引擎放弃使用索引而进行全表扫描
7、应尽量避免在 where 子句中对字段进行 null 值判断
| -- 好 | |
| select id from t where num=0 | |
| -- 坏 | |
| select id from t where num is null |
对字段进行 null 值判断，将导致引擎放弃使用索引而进行全表扫描。可以在建表时添加Not
Null 约束。
8、应尽量避免在 where 子句中使用 or 来连接条件
| -- 好 | |
| select id from t where num=10 | |
| union all | |
| select id from t where num=20 | |
| -- 坏 | |
| select id from t where num=10 or num=20 |
使用 or 来连接条件，将导致引擎放弃使用索引而进行全表扫描
9、很多时候用 exists 代替 in 是一个好的选择
| -- 好 | |
| select num from a where exists(select 1 from b where num=a.num) | |
| -- 坏 | |
| select num from a where num in (select num from b) | |
| -- 好 | |
| select * from orders where customer_name not exists(select customer_name | |
| from customer) | |
| -- 坏 | |
| select * from orders where customer_name not in(select customer_name | |
| from customer) | |
10、不要在子查询中使用count()求和执行存在性检查
| --坏 | |
| SELECT column_list FROMtableWHERE0< (SELECT count(*) FROM table2 WHERE ..) | |
| --好 | |
| SELECT column_list FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...) |
当你使用count()时，SQL Server不知道你要做的是存在性检查，它会计算所有匹配的值，要么会执行全表扫描，要么会扫描最小的非聚集索引;
当你使用EXISTS时，SQL Server知道你要执行存在性检查，当它发现第一个匹配的值时，就会返回TRUE，并停止查询。类似的应用还有使用IN或ANY代替count()。
11、如果在 where 子句中使用参数，也会导致全表扫描
| -- 好 | |
| select id from t with(index(索引名)) where num=@num | |
| -- 坏 | |
| select id from t where num=@num |
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行
时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，
因而无法作为索引选择的输入项。
12、避免死锁
1）不同事务访问表的顺序尽量要一致
2）事务尽可能短，访问的数据尽可能的少
3）永远不要在事务中等待用户输入。
12、避免使用动态SQL
除非迫不得已，应尽量避免使用动态SQL，因为：
1)动态SQL难以调试和故障诊断;
2)如果用户向动态SQL提供了输入，那么可能存在SQL注入风险。
13、避免使用临时表
1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;
2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上
的操作需要跨数据库通信，速度自然慢。
14、在触发器中使用下列最佳实践
1)最好不要使用触发器，触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;
2)如果能够使用约束实现的，尽量不要使用触发器;
3)不要为不同的触发事件(Insert，Update和Delete)使用相同的触发器;
4)不要在触发器中使用事务型代码。
利用查询执行计划
查看预估的执行计划
SQL Server执行一个查询之前，查询优化器会根据很多信息，如数据分布统计，索引结构，元数据等，分析出多种可能的执行计划，然后选择一个最佳执行计划。
可以通过SMSS查看执行计划，如下：
执行计划的应该从左到右的阅读，图中的每一个图标代表一个操作。每个行为都有一个相对于总体执行成本的百分比。
将鼠标放在某一个图标上可以查看该操作具体的成本开销：
通过查看执行计划进行优化
当你发现一个很慢的查询时，就应该看看预估的执行计划，找出耗时最多的操作，然后进行优化。
下面是几个成本较高的操作，以及优化方式：
1、表扫描（Table Scan）
这是最慢的查找方式，表没有聚集索引时就会发生表扫描，这时只需要创建一个聚集索引就能解决问题。
2、聚集索引扫描(Clustered Index Scan)
开销也是很大，仅仅亚于表扫描，出现这种情况一般查询中涉及的列上没有聚集索引，或者对应的聚集索引失效，这时只要创建一个该列上的非聚集索引就行了。
比如：
| --假设表只有Id列上有一个聚集索引，执行如下查询就会出现聚集索引扫描操作 | |
| select * from account where UserName='Michael Shen'; | |
| --因为UserName列上没有聚集索引，此时只需要在UserName上建一个聚集索引就行了 |
3、哈希连接(Hash Join)
当连接两个表的列没有被索引时会发生，只需在这些列上创建索引即可。
4、嵌套循环(Nested Loops)
当非聚集索引不包括select查询清单的列时会发生，只需要创建覆盖索引问题即可解决。
5、RID查找(RID Lookup)
当表上有一个非聚集索引，但却没有聚集索引时会发生，此时数据库引擎会使用行ID查找真实的行，这时一个代价高的操作，这时只要在该表上创建聚集索引即可。
高级索引优化
使用计算列，并在计算列上创建索引
使用计算列，并在计算列上创建索引，参考SQL Server计算列上的索引
给视图创建索引
如果你在视图上应用了索引，视图就成为索引视图，对于一个索引视图，数据库引擎处理SQL，并在数据文件中存储结果，和聚集表类似，当基础表中的数据发生变化时，SQL Server会自动维护索引，因此当你在索引视图上查询时，数据库引擎简单地从索引中查找值，速度当然就很快了，因此在视图上创建索引可以明显加快查询速度。
视图相关知识参考SQL Server视图
适当利用反范式化表设计
反范式化意味着数据会冗余，这是一个以空间换时间的操作。
比如一个连接查询会从两个表中选择数据，但是两张表都是大表，比如：
假设有学生表Students(Id,StudentName,ClassId)，班级表Classes(Id,ClassName).
如下查询：
| select Students.StudentName,Classes.ClassName from Students | |
| inner join Classes on Students.ClassId=Classes.Id |
如果这两个都是大表，当应用了所有优化技巧后，查询速度仍然很慢，这时可以考虑以下反范式化设计：
首先，在Students表添加一列ClassName，并且填充好数据；
然后改成如下SQL查询：
| select StudentName,ClassName from Students |
这样一来就不会执行两个表的连接操作。查询速度会快很多。
但是一定要注意的是，反范式化本来就不是好的设计，非必要情况不要使用。
合理组织数据库文件组和文件
创建SQL Server数据库时，数据库服务器会自动在文件系统上创建一系列的文件，之后创建的每一个数据库对象实际上都是存储在这些文件中的。SQL Server有下面三种文件：
1).mdf文件
这是最主要的数据文件，每个数据库只能有一个主数据文件，所有系统对象都存储在主数据文件中，如果不创建次要数据文件，所有用户对象(用户创建的数据库对象)也都存储在主数据文件中。
2).ndf文件
这些都是次要数据文件，它们是可选的，它们存储的都是用户创建的对象。
3).ldf文件
这些是事务日志文件，数量从一到几个不等，它里面存储的是事务日志。
默认情况下，创建SQL Server数据库时会自动创建主数据文件和事务日志文件，当然也可以修改这两个文件的属性，如保存路径。
文件组
为了便于管理和获得更好的性能，数据文件通常都进行了合理的分组，创建一个新的SQL Server数据库时，会自动创建主文件组，主数据文件就包含在主文件组中，主文件组也被设为默认组，因此所有新创建的用户对象都自动存储在主文件组中(具体说就是存储在主数据文件中)。
如果你想将你的用户对象(表、视图、存储过程和函数等)存储在次要数据文件中，那需要：
1)创建一个新的文件组，并将其设为默认文件组;
2)创建一个新的数据文件(.ndf)，将其归于第一步创建的新文件组中。
以后创建的对象就会全部存储在次要文件组中了。
注意：事务日志文件不属于任何文件组。
文件/文件组组织最佳实践
如果你的数据库不大，那么默认的文件/文件组应该就能满足你的需要，但如果你的数据库变得很大时(假设有1000MB)，你可以(应该)对文件/文件组进行调整以获得更好的性能，调整文件/文件组的最佳实践内容如下：
1)主文件组必须完全独立，它里面应该只存储系统对象，所有的用户对象都不应该放在主文件组中。主文件组也不应该设为默认组，将系统对象和用户对象分开可以获得更好的性能;
2)如果有多块硬盘，可以将每个文件组中的每个文件分配到每块硬盘上，这样可以实现分布式磁盘I/O，大大提高数据读写速度;
3)将访问频繁的表及其索引放到一个单独的文件组中，这样读取表数据和索引都会更快;
4)将访问频繁的包含Text和Image数据类型的列的表放到一个单独的文件组中，最好将其中的Text和Image列数据放在一个独立的硬盘中，这样检索该表的非Text和Image列时速度就不会受Text和Image列的影响;
5)将事务日志文件放在一个独立的硬盘上，千万不要和数据文件共用一块硬盘，日志操作属于写密集型操作，因此保证日志写入具有良好的I/O性能非常重要;
6)将“只读”表单独放到一个独立的文件组中，同样，将“只写”表单独放到一个文件组中，这样只读表的检索速度会更快，只写表的更新速度也会更快;
7)不要过度使用SQL Server的“自动增长”特性，因为自动增长的成本其实是很高的，设置“自动增长”值为一个合适的值，如一周，同样，也不要过度频繁地使用“自动收缩”特性，最好禁用掉自动收缩，改为手工收缩数据库大小，或使用调度操作，设置一个合理的时间间隔，如一个月。
表分区
如果电脑只有一个磁盘，那磁盘空间满了怎么办？
我们可以将电脑硬盘进行分区，按数据类别划分，每个分区存放不同的内容，保证数据的安全和磁盘空间有效的利用。
表的数据量达到5千万条，我想按日期查询某一年中某几个月的数据？怎么办？？？？
可以使用表分区，以时间进行划分，例如把2023年分成12份，那么每个分区差不多4百多万
数据，再借助于索引，查询速度将会非常快。此时你若想查2023年5月份的数据，数据库会自动
寻找5月份那一个分区数据，而不是每个区域都去搜索。
使用场景一般是某个数据库中只有某几张表数据量较大，此时我们只需要将这某几张进行分区处理。
参考SQL Server创建分区表、SQL Server将现有表分区、SQL Server表分区详细版
更多信息参考官方文档：创建分区的表和索引
水平分表
介绍
水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。
通常我们按以下原则进行水平拆分
1、顺序拆分
2、取模拆分
落地方案
进行水平拆分后的表,字段的列、类型和原表应该是相同的,但是要记得去掉自增长。查询用union all
| --用于保存所有日志ID，与操作项的Id,在操作日志的时候， | |
| --可以依据此表来获取最后一次日志插入的ID，依据此ID来决定插入日志子表 | |
| CREATE TABLE LogId | |
| ( | |
| Id INT IDENTITY(1,1) PRIMARY KEY, | |
| ItemId int | |
| ) | |
| GO | |
| --创建日志表,共创建8张表 | |
| CREATE TABLE Logs_0 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_1 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_2 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_3 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_4 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_5 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_6 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_7 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ) | |
| GO | |
| CREATE TABLE Logs_8 | |
| ( | |
| LogId INT PRIMARY KEY, | |
| LogType NVARCHAR(100),--日志类型 | |
| Operator NVARCHAR(100),--操作者 | |
| ItemId INT,--操作项主键 | |
| [Description] NVARCHAR(2000),--操作描述 | |
| OperateDate DATETIME,--操作日期 | |
| OperateIP NVARCHAR(50)--操作IP | |
| ); | |
| GO | |
| --创建视图，关联起所有的日志子表，查询时直接操作此视图 | |
| CREATE VIEW Logs | |
| AS | |
| SELECT * FROM dbo.Logs_0 UNION ALL | |
| SELECT * FROM dbo.Logs_1 UNION ALL | |
| SELECT * FROM dbo.Logs_2 UNION ALL | |
| SELECT * FROM dbo.Logs_3 UNION ALL | |
| SELECT * FROM dbo.Logs_4 UNION ALL | |
| SELECT * FROM dbo.Logs_5 UNION ALL | |
| SELECT * FROM dbo.Logs_6 UNION ALL | |
| SELECT * FROM dbo.Logs_7 UNION ALL | |
| SELECT * FROM dbo.Logs_8 | |
| GO | |
| --根据dbo.LogId最后一次插入的Id值来确定到底需要插入到哪张日志子表，并返回表名 | |
| CREATE FUNCTION [dbo].[GetTableName]( | |
| @Id INT, | |
| @table NVARCHAR(50)='Logs') | |
| RETURNS NVARCHAR(60) | |
| AS | |
| BEGIN | |
| DECLARE @tablename NVARCHAR(60) | |
| DECLARE @tableId INT, @delimiter INT | |
| SET @delimiter = 80000 --注意,一旦定下,不可再修改,因项目而异，可以更大 | |
| 小，建议不要超过50W | |
| SET @tableId = FLOOR(@Id/@delimiter) | |
| SET @tablename = 'dbo.' + @table + '_' + CAST(@tableId AS nvarchar) | |
| RETURN @tablename | |
| RETURN ''; | |
| END | |
| GO | |
| --添加日志 | |
| CREATE PROC [dbo].[LogAdd] | |
| @logid int, | |
| @logtype nvarchar(100), | |
| @operator nvarchar(100), | |
| @itemid int, | |
| @description nvarchar(2000), | |
| @operatedate datetime, | |
| @operateip nvarchar(50) | |
| as | |
| declare @tablename nvarchar(100) = 'Logs',@sql nvarchar(1000) = '' | |
| insert LogId(ItemId)values(@itemid) | |
| set @logid = @@IDENTITY | |
| set @tablename = dbo.GetTableName(@logid, 'Logs') | |
| set @sql=N'insert '+@tablename+'(LogId,LogType,[Operator],ItemId, | |
| [Description],OperateDate,OperateIP)values(@logid,@logtype,@operator,@item | |
| id,@description,@operatedate,@operateip);SELECT @@IDENTITY' | |
| exec sp_executesql @sql,N'@logid int,@logtype nvarchar(100),@operator | |
| nvarchar(100),@itemid int,@description nvarchar(2000),@operatedate | |
| datetime,@operateip nvarchar(50)' | |
| ,@logid=@logid,@logtype=@logtype,@operator=@operator,@itemid=@itemid,@desc | |
| ription=@description,@operatedate=@operatedate,@operateip=@operateip | |
| GO | |
| --修改日志 | |
| CREATE PROC [dbo].[LogUpdate] | |
| @logid int, | |
| @logtype nvarchar(100), | |
| @operator nvarchar(100), | |
| @itemid int, | |
| @description nvarchar(2000), | |
| @operatedate datetime, | |
| @operateip nvarchar(50) | |
| as | |
| declare @tablename nvarchar(100)='Logs',@sql nvarchar(1000)='' | |
| set @tablename=dbo.GetTableName(@logid,'Logs') | |
| set @sql=N'update '+@tablename+' set LogType,[Operator], | |
| [Description],OperateDate,OperateIP where LogId=@logid;SELECT @@IDENTITY' | |
| exec sp_executesql @sql,N'@logid int,@logtype nvarchar(100),@operator | |
| nvarchar(100),@itemid int,@description nvarchar(2000),@operatedate | |
| datetime,@operateip nvarchar(50)' | |
| ,@logid=@logid,@logtype=@logtype,@operator=@operator,@itemid=@itemid,@desc | |
| ription=@description,@operatedate=@operatedate,@operateip=@operateip | |
| GO | |
| --删除日志 | |
| CREATE PROC [dbo].[LogDelete] | |
| @logid nvarchar(10) | |
| AS | |
| BEGIN | |
| declare @tablename nvarchar(10)='Logs',@sql nvarchar(300)='' | |
| set @tablename=dbo.GetTableName(@logid,'Logs'); | |
| set @sql=N'delete '+@tablename + ' where LogId='+@logid; | |
| exec(@sql); | |
| END | |
| GO | |
| --得到日志查询的总记录数 | |
| CREATE PROC [dbo].[LogSearchCount] | |
| @condition NVARCHAR(1000) | |
| AS | |
| declare @sql nvarchar(2000)='' | |
| set @sql = 'select count(0) from Logs where 1=1 '+@condition | |
| exec(@sql); | |
| GO | |
| --查询日志,直接操作Logs视图 | |
| CREATE PROC [dbo].[LogSearch] | |
| @begin NVARCHAR(10), | |
| @end NVARCHAR(10), | |
| @condition NVARCHAR(1000) | |
| AS | |
| declare @sql nvarchar(2000)='' | |
| set @sql =N'SELECT * FROM (SELECT ROW_NUMBER()OVER(ORDER BY LogId) | |
| Line, | |
| CASE LogType | |
| WHEN ''Admin'' THEN ''管理员模块'' | |
| WHEN ''Product'' THEN ''素金产品'' | |
| WHEN ''OrderProduct'' THEN ''定制产品'' | |
| WHEN ''Merchant'' THEN ''分销商'' | |
| WHEN ''Diamond'' THEN ''裸钻产品'' | |
| WHEN ''Order'' THEN ''订单模块'' ELSE '''' END LogType, | |
| LogId,OperateDate,OperateIP,Operator,[Description],ItemId | |
| FROM Logs WHERE 1=1 '+@condition+' )A WHERE A.Line>'+@begin+' AND | |
| A.Line<='+@end; | |
| exec(@sql); | |
| GO | |
| CREATE PROC GetClearLog | |
| As | |
| select * from Logs where OperateDate<=GETDATE()-30 | |
垂直分表
简介
垂直(纵向)拆分表：垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张，使用时用jion关
键起来即可。
示例
1、把不常用的字段单独放在一张表
2、把text等大字段拆分出来放在附表中
3、经常组合查询的列放在一张表中;
例：
用户基本信息表：
| 字段 | 类型 | 描述 |
|---|---|---|
| UserId | int | 主键,自增，用户id |
| UserName | varchar(30) | 用户名 |
| Password | varchar(60) | 密码 |
用户详细信息表：
| 字段 | 类型 | 描述 |
|---|---|---|
| UserId | int | 主键 ,用户id ，与基本信息表对应上 |
| Hobby | varchar(30) | 爱好 |
| Detail | varchar(60) | 个人简介 |
| char(11) | 联系 QQ | |
| NickName | Varchar(30) | 昵称 |
| Signature | varchar(100) | 个性签名 |
垂直拆库
垂直(纵向)拆分库：是指按功能模块拆分，比如分为订单库、商品库、用户库...这种方式多个数据库之
间的表结构不同。
优点：
- 拆分后业务清晰，拆分规则明确。
- 系统之间整合或扩展容易。
- 数据维护简单。
缺点：
- 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
- 事务处理复杂。
示例
方案1
宽表 从字面意义上讲就是 字段比较多的数据库表 。通常是指业务主题相关的 指标、维度、属性 关联在一起的一张数据库表。 和视图很像，但视图的本质是一堆 SQL ，其实并不存储数据，而大宽表是真正意义上的物理表。
由于把不同的内容都放在同一张表存储，宽表已经不符合 三范式 的模型设计规范，随之带来的主要坏处就是数据的 大量冗余 ，与之相对应的好处就是 查询性能的提高与便捷 。
由于系统被拆分成多个数据库，无法直接进行join 关联。此时可以设计时把能够想到的，需要关联的字段事先挪列出来，构造一成大宽表。如果涉及到多系统关联查询时，只需要从大宽表中查询即可。在操作数据时，千万别忘记把大宽表中的数据也同步更新。
随着微服务的兴起，许多微服务组件也应运而生，我们可以结合 ElasticSearch组件 把大宽表设计在里面，利用 ElasticSearch 高效的索引机制以及分词能力，是完美解决垂直拆库的最终方案。
缺点：
大宽表一旦设计好了，就无法更改字段，后期想再添加新的字段，只能另求它路。
方案2
ElasticSearch + Doris
官网： Apache Doris
Apache Doris是一个现代化的 MPP 分析型数据库产品。仅需亚秒级响应时间即可获得查询结果，有效
地支持实时数据分析。Apache Doris的分布式架构非常简洁，易于运维，并且可以支持 10PB 以上的超大数据集。
Doris-On-ES将Doris的分布式查询规划能力和 ES(Elasticsearch) 的全文检索能力相结合，提供更完善的OLAP分析场景解决方案：
- ES中的多index分布式Join查询
- Doris和ES中的表联合查询，更复杂的全文检索过滤
读写分离
简介
通过数据冗余将数据库读写操作分散到不同的节点上。
- 数据库搭建主从服务器，一主一从或者一主多从
- 数据库主机负责写操作，从机负责读操作
- 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据
- 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。
适用场景
- 并发量大，单机已经不能处理该数量的并发请示
- 读远大于写的场景
- 数据实时性要求不那么严格的业务
浙公网安备 33010602011771号