关系型数据库篇（MSSQL）

小咪咪 2022-05-14 10:59 148阅读 0赞

1.前言  
常用的数据库端使用总结下：数据库表设计规范，索引，SQL优化，系统表查询  
1.设计数据库表遵循三范式  
a)第一范式：确保每列保持原子性  
b)第二范式：确保表中的每列都和主键相关  
c)第三范式：确保每列都和主键列直接相关,而不是间接相关

2.索引定义  
唯一索引：唯一索引不允许两行具有相同的索引值，一个表可以设置多个唯一索引  
聚集索引:表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个  
非聚集索引:非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个  
主键与唯一索引的区别:  
①.主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。 唯一性索引列允许空值，而主键列不允许为空值。 主键列在创建时，已经默认为空值 + 唯一索引了。  
②.主键可以被其他表引用为外键，而唯一索引不能。 一个表最多只能创建一个主键，但可以创建多个唯一索引。 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

3.索引添加规则  
a)针对优先级高的、使用频繁的查询来增加索引，同时测试查询看索引是否被使用，并且不要同时增加多个索引。  
b)除非有非常好的原因，否则在每一个表都增加一个聚集索引  
c)选择很少改变的、高度唯一的、数据类型占用字节少的列做为聚集索引键。非聚集索引应该建立在包含高度唯一数据的列上，基于查询中的应用挑选列，特别是在join和where子句中的列，为非键列考虑使用include。基于使用的查询来预估需要哪些索引，然后通过数据库引擎优化顾问工具来评估这些索引  
d)随着数据和应用程序活动的改变，索引的性能和效能也会改变，所以要监视查询性能。特别是索引碎片化会降低查询性能，返回相同的结果需要更多的IO操作，通过重新生成、重新组织索引来保持索引碎片最小化。  
e)为只读文件组或数据库上的索引使用100%填充因子，这样使得完成查询需要访问的数据页更少，相应的IO操作也更少，效率自然得到提升。

4.索引碎片  
a)产生碎片：  
在SQL Server中，存储数据的最小单位是页，每一页所能容纳的数据为8060字节.而页的组织方式是通过B树结构，  
在聚集索引B树中，只有叶子节点实际存储数据，而其他根节点和中间节点仅仅用于存放查找叶子节点的数据.  
每一个叶子节点为一页，每页是不可分割的. 而SQL Server向每个页内存储数据的最小单位是表的行(Row).当叶子节点中新插入的行或更新的行使得叶子节点无法容纳当前更新或者插入的行时，分页就产生了.在分页的过程中，就会产生碎片.  
b)外部碎片:  
首先，理解外部碎片的这个“外”是相对页面来说的。外部碎片指的是由于分页而产生的碎片.  
比如，我想在现有的聚集索引中插入一行，这行正好导致现有的页空间无法满足容纳新的行。从而导致了分页。  
性能的影响：主要是在于需要进行更多的跨区扫描，从而造成更多的IO操作.  
c)内部碎片：  
和外部碎片一样，内部碎片的”内”也是相对页来说的，表内存空间不能容纳，造成分页  
性能的影响：内部碎片会造成数据行分布在更多的页中，从而加重了扫描的页树，也会降低查询性能.  
d)碎片的解决办法

基本上所有解决办法都是基于对索引的重建和整理，只是方式不同:  
①.删除索引并重建  
这种方式并不好.在删除索引期间，索引不可用.会导致阻塞发生。而对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建).虽然这种方法并不好，但是对于索引的整理最为有效  
②.使用DROP\_EXISTING语句重建索引  
为了避免重建两次索引，使用DROP\_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞  
③.如前面文章所示，使用ALTER INDEX REBUILD语句重建索引  
使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长.  
④.使用ALTER INDEX REORGANIZE  
这种方式不会重建索引，也不会生成新的页，仅仅是整理，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种.

理解填充因子：  
重建索引固然可以解决碎片的问题.但是重建索引的代价不仅仅是麻烦，还会造成阻塞。影响使用.而对于数据比较少的情况下，重建索引代价并不大。而当索引本身超过百兆的时候。重建索引的时间将会很慢。  
填充因子的作用正是如此。对于默认值来说，填充因子为0（0和100表示的是一个概念）,则表示页面可以100%使用。所以会遇到前面update或insert时，空间不足导致分页.通过设置填充因子，可以设置页面的使用程度  
设置填充因子的值 ：  
如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页，但同时因为需要更多的页，所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.  
具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:  
①.当读写比例大于100：1时，不要设置填充因子，100%填充  
②.当写的次数大于读的次数时，设置50%-70%填充  
③.当读写比例位于两者之间时80%-90%填充  
具体设置的数据还要根据具体情况进行测试才能找到最优.

查询索引碎片：  
①.dbcc showcontig  
②.SELECT a.index\_id, name, avg\_fragmentation\_in\_percent  
FROM sys.dm\_db\_index\_physical\_stats (DB\_ID(N’AdventureWorks2012’), OBJECT\_ID(N’HumanResources.Employee’), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object\_id = b.object\_id AND a.index\_id = b.index\_id  
重新组织索引碎片：  
①. ALTER INDEX IX\_Employee\_OrganizationalLevel\_OrganizationalNode ON HumanResources.Employee REORGANIZE

5.查询表语句  
①.查看表名和对应的数据行数

select a.name as '表名',b.rows as '表数据行数' from sysobjects a inner join sysindexes b on a.id = b.id where a.type = 'u' and b.indid in (0,1) --and a.name not like 't%' order by b.rows desc

②.查看表名和表占用空间信息

--判断临时表是否存在，存在则删除重建
    if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u') drop table #tabName go create table #tabName( tabname varchar(100), rowsNum varchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused_size varchar(100) ) declare @name varchar(100) declare cur cursor for select name from sysobjects where xtype='u' order by name open cur fetch next from cur into @name while @@fetch_status=0 begin insert into #tabName exec sp_spaceused @name --print @name fetch next from cur into @name end close cur deallocate cur select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小' from #tabName --where tabName not like 't%' order by cast(rowsNum as int) desc

③.系统存储过程说明

--sp_spaceused 该存储过程在系统数据库master下。
    exec sp_spaceused '表名' --该表占用空间信息
    exec sp_spaceused           --当前数据库占用空间信息

6.常用SQL脚本  
①.分页查询 第n页数据（n>=1）

select * from (select *,ROW_NUMBER() OVER(order by title) rowid from SectionTemplate) b where b.rowid between (pageindex-1)*pagesize and pageindex*5

②.分组和聚合函数一起用

SELECT PCompany,COUNT(*) counts FROM ProductInfo group by PCompany order by counts desc

![这里写图片描述][70]

③.对结果做运算

select empno,ename,sal+999 from emp;
      select * from emp where not JOB = 'CLERK';
      select * from emp where sal in (800,1000,1200,1400,1600,1800);
      select * from emp where comm is not null;
      select * from emp where comm is null;
      select * from emp where sal between 1000 and 2900;

④.模糊查询

select * from emp where ename like '%T_';

⑤.空值排在最后

select UserInfoID,User_No,User_Names from UserInfo order by case when User_NO is null then 1 else 0 end asc,User_NO asc

⑥.字符串的操作

select SUBSTRING('你好中国人',0,2); 
    select REPLACE('你好中国人','中国','外国') ;
    select LEN('abcdefghijklmnopqrstuvwxyz') ;
    select '你好' + '中国人';

⑦.行列转换

select * from tbScoreNew;
    
    
    --(1)静态SQL
    SELECT * FROM ( SELECT [姓名],'语文' AS 课程,[语文] AS 分数 ,[日期] FROM tbScoreNew UNION ALL SELECT [姓名],'数学' AS 课程,[数学] AS 分数 ,[日期] FROM tbScoreNew UNION ALL SELECT [姓名],'物理' AS 课程,[物理] AS 分数 ,[日期] FROM tbScoreNew ) T ORDER BY [姓名] SELECT * FROM ( SELECT [姓名],[日期],[语文],[数学],[物理] FROM dbo.tbScoreNew ) T UNPIVOT ([分数] FOR [课程] IN ([语文],[数学],[物理])) T2 ORDER BY [姓名]

![这里写图片描述][70 1]  
⑧.子查询

select * from emp where sal = (select max(sal) from emp);

⑨.连接查询  
连接查询可以待条件：and，where  
a)内连接/相等连接（Inner Join）  
在连接条件这使用等号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列  
b)外连接  
①.左连接(Left Jion/Left Outer Jion)  
以左表数据为准，没有数据null填空  
②.右连接(Rigt Jion/Rigt Outer Jion)  
以右表数据为准，没有数据null填空  
③.全连接(Full Outer)  
全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。  
c)自身连接  
①.对自身表连接

select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
    from t_institution i
    join t_institution s
    on i.superior_inst = s.inst_no

d)交叉(无限制) 连接(笛卡尔积)  
交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。  
大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。  
⑩.having  
having 用法与WHERE用法类似，但有三点不同  
1、HAVING只用于GROUP BY（分组统计语句），  
2、WHERE 是用于在初始表中筛选查询，HAVING用于在WHERE和GROUP BY 结果中查询。  
3、HAVING可以使用聚合函数，面WHERE 不能。

下面的语句统计用户表中姓名为“李”（WHERE子句定义），出现多于一次(having 用聚合函数COUNT(1)定义）的人的用户
    SELECT USERCODE,username=max(username),次数=count(1) from usertable where username like '李%' group by usercode having count(1)>1

[70]: /images/20220514/f27c2f661fad40c884abf55b8e8a1656.png
[70 1]: /images/20220514/f9b467c8096a440b9c43a589da2d5e1d.png