DB知识点记录

2023-08-20 15:07:10

DB知识点记录

分页

SqlServer：ROW_NUMBER () over (ORDER BY ID) AS RN,

MySql：limit

Oracle：ROWNUM AS RN

数据表的基本结构

一个新表被创建之时，会在磁盘中分配一段以8K为单位的连续空间，当字段的值从内存写入磁盘时，就在这空间随机保存，当8K用完会自动分配另一个8K的空间。

这里8K称为一个数据页，并分配0-7的页号，每个文件的第0页记录引导信息，叫文件头；每8个数据页（64K）的组合形成扩展区，称为扩展。全数据页的组成形成堆；

SQL规定行不能跨越数据页，每行记录最大数据量只能是8K。所以char和varchar这两种类型容量要限制在8K以内，存储超过8K的数据应使用text类型，text类型的字段不能直接录入和保存，只是存储一个指针，指向若干的8K的文本数据页所组成的扩展区。

当一个扩展区的8个数据页中既包含了空间页面又包含了数据或索引页面时，称为混合扩展，每张表都以混合扩展开始；反之称为一致扩展，专门保存数据及索引信息。

表被创建时，SQL在混合扩展中分配至少一个数据页，随着数量增长，可以在混合扩展中分配7个页，当数据超过8个页面时，则从一致扩展中分配数据页面。

空间页面专门负责数据空间的分配和管理。PFS页面：记录一个页面是否已分配，还有多少可用空间等信息。GAM页面和SGAM页面：用来记录空闲的扩展或含有空闲页面的混合扩展位置。

数据页或索引页则专门保存数据及索引信息，使用4种类型的数据页来管理表或索引，分别为：IAM页、数据页、文本/图像页和索引页

页分裂

一半的数据将保存在老页面、另一半将存放入新页面，且新页面可能被分配到任何可用的页。所以、频繁页分裂后果很严重，将使物理表产生大量数据碎片、导致直接造成I/O效率的急剧下降。

填充因子

索引的一个特性，定义所以每页上的可用空间量。FILLFACTOR（填充因子）适应以后表数据的扩展并减少了页拆分的可能性。填充因子是从0到100的百分比数值，设为100时表示将数据页填满。只有当不会对数据进行更改时才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但是需要占用更多的磁盘空间。

索引

查看索引：SHOW INDEX FROM tableName;

查看数据库索引：select * from information_schema.STATISTICS where INDEX_NAME != 'PRIMARY' and TABLE_SCHEMA = 'bank'

删除索引：ALTER TABLE tableName DROP INDEX ID;

添加索引：ALTER TABLE tableName ADD INDEX (ID);

索引通常是用来加快数据处理速度的最普遍采用的优化方式。数据库的索引相当于书的目录，可以快速找到你想要的信息。

索引的利弊：查询执行的大部分开销是I/O，使用索引提高性能的主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向，则查询只需要读少数次的磁盘就可以了。带索引的表需要在数据库中占用更多的存储空间，会增加增删数据的命令运行时长。

SQL SERVER中有多种索引类型

按存储结构区分：聚集索引、分聚集索引

按数据唯一性区分：唯一索引、非唯一索引

按键列个数区分：单列索引、多列索引

MySql所有有以下几种

普通索引、唯一索引、全文索引、单列索引、多列索引

聚集索引

一种对磁盘上实际数据重新组织以按指定的一列或多列值排序。像我们用到的汉语字典，就是一个聚集索引，比如查张，就需要翻到后面。然后根据字母顺序查找，这里用到微软的平衡二叉树算法。

聚集索引是给数据排序，一个表只能建立一个聚集索引，它的性能比其他索引更快。

非聚集索引

SqlServer默认情况下建立的索引是非聚集索引，不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。即便对数据不排序，他拥有的目录更像是目录，对查取数据的效率也是具有提升空间的，而不需要全表扫描。

索引设计原则

对于一张表来说索引的有无和建立什么样的索引，要取决于where字句和Join表达式

一般来说建立索引的原则包括以下内容

系统一般会给逐渐字段自动建立聚集索引
有大量重复值且经常有范围查询和排序、分组的列，或者经常频繁访问的列，考虑建立聚集索引
经常做插入操作的表中建立索引，应使用填充因子来减少页分裂，同时提高并发度降低死锁的发生。
在选择索引键时，应采用小数据类型的列作为键以使每个索引能容纳尽可能多的索引建和指针

事务隔离

数据库中的事务是数据库并发控制的基本单位，一条或一组语句要么全部成功，对数据库的某些数据成功修改，要么全部不成功，数据的数据还原到这些语句执行。

数据库中事务的ACID原则

原子性(Atomicity)：事务的原子性指一个事务中包含的一条语句或多条语句构成了一个完整的逻辑单元，这个逻辑单元具有不可再分的原子性。要么一起提交全部执行完成、要么一起提交全部执行失败。

一致性(Consistency)：可以理解为数据的完整性，事务的提交要确保在数据库上的操作没有破坏数据的完整性，比如不要违背一些约束的数据插入或修改行为。一旦破坏了数据完整性，会回滚这个事务来确保数据库中的数据是一致的。

隔离性(Isolation)：与数据库的事务隔离级别以及锁相关，多个用户可以对同一数据并发访问而又不破坏数据的正确性和完整性。但是并行事务的修改必须与其他并行事务的修改相互独立，隔离。但是在不同的隔离级别下，事务的读取操作可能得到的结果是不同的。

持久性(Durability)：数据持久化，事务一旦对数据的操作完成并提交后，数据修改就已经完成，即使服务重启这些数据也不会改变。如果在事务的执行过程中，系统服务崩溃或者重启，那么事务所有的操作就会被回滚，回到事务操作之前的状态。

常见事务

自动提交事务

SqlServer默认的一种事务模式，每条Sql语句都被看成一个事务进行处理。

显式事务

由Begin Transaction开启事务开始，由Commit Transaction提交事务、Rollback Transcation回滚事务结束，Save Transaction事务内部设置保存点，事务可以不全部回滚只回滚到这里

---开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try
   --语句正确
   )
   --Numb为int类型，出错
   insert into lives (Eat,Play,Numb) values ('猪肉','足球','abc')
end try
begin catch
   select Error_number() as ErrorNumber,  --错误代码
          Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
          Error_state() as ErrorState ,  --错误状态码
          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
   ) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
)
commit tran  --如果成功Lives表中，将会有3条数据。

--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from lives

set xact_abort

设置xact_abort on/off，指定是否回滚当前事务，为on时如果当前sql出错，回滚整个事务，为off时如果sql出错回滚当前sql语句，其他语句照常运行读写数据库

set xact_abort off

等待5秒：waitfor delay '0:0:5'

事务中常见的问题

SqlServer中多用户并发的情况下，使用事务可能会遇到一些情况

脏读(Dirty Reads)：一个事务正在访问并修改数据库中的数据但是没有提交，另一个事务可能读取到这些已经做出修改但未提交的数据。可能导致的结果就是所有操作都有可能回滚，比如第一个事务对数据做出的修改可能违背了数据表的某些约束，破坏了完整性，但是恰巧第二个事务却读取到了这些不正确的数据造成它自身操作也发生失败回滚。

不可重复读取(Non-Repeatable Reads)：A事务两次读取同一数据，B事务也读取这同一数据，但是A事务在第二次读取前B事务已经更新了这一数据。对于A事务来说，第一次和第二次读取到这一数据可能就不一致了。

幻读(Phantom Reads)：与不可重复读有点类似，都是两次读取，不同的是A事务第一次操作的比如说是全表的数据，此时B事务并不是只修改某一具体数据而是插入了一条新数据，然后A事务第二次读取这全表的时候就发现比上一次多了一条数据，发生幻觉了。

更新丢失(Lost Update)：两个事务同时更新，但由于某一个事务更新失败发生回滚操作，这样有可能的结果就是第二个事务已更新的数据因为第一个事务发生回滚而导致数据最终没有发生更新，因此两个事务的更新都失败了。

事务隔离级别

希望通过何种方式让并发的事务隔离开来，隔离到什么程度。比如可以容忍脏读、或者不希望并发的事务出现脏读的情况，那么这些可以通过隔离界别的设置使得并发事务之间的隔离程度变得宽松或者很严峻。

隔离级别越高，读取脏数据或者造成数据不统一不完整的机会就越少，但是在高并发的系统中性能降低就越严重。隔离级别越低，并发系统中性能上提升很大，但是数据本身可能不完整。

SqlServer 2012中可以通过下面的语法来设置事务的隔离级别（从低到高排列）

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Read Uncommitted（未提交读）

隔离级别最低，容易产生的问题就是脏读，因为可以读取其他事务修改了但没有提交的数据。它的作用跟在事务中SELECT语句对象表上设置（NOLOCK）相同

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DBCC USEROPTIONS

Read Committed（已提交读）

SqlServer的默认设置，已提交读，可以避免脏读，可以满足大多数要求。事务中的语句不能读取已由其他事务做出修改但是还未提交的数据，但是能读取由其他事务做出修改并提交了的数据。有可能出现不可重复读和幻读的情况，因为当前事务中可能出现两次读取同一资源，但是两次读取的过程之间，另外一个事务可能对这一资源完成了读取更新并提交的行为，这样数据前后可能就不一致了。

Repeatable Read（可重复读）

不能读取已由其他事务修改了但是未提交的行，其他任何事务也不能修改在当前事务完成之前由当前事务读取的数据。但是对于其他事务插入的新行数据，当事务第二次访问表行时会检索这一新行。因此这个隔离级别的设置解决了不可重复读取的问题，但是避免不了幻读。

SNAPSHOT（快照隔离）

可以解决幻读的问题，当前事务中读取的数据在整个事务开始到事务提交结束之间，这个数据版本是一致的。其他的事务可能对这些数据做出修改，但是对于当前事务来说它是看不到这些变化。有点类似于当前事务拿到这个数据的时候是拿到这个数据的快照，因此在这个快照上做出的操作同一事务中前后几次操作都是基于同一数据版本。因此这个隔离界别可以解决幻读问题。但是要注意的是，其他事务是可以在当前事务完成之前修改由当前事务读取的数据。

默认情况下数据库不允许设置SNAPSHOT隔离界别，直接设置会出现错误。

需要使用SET命令开启这个支持

ALTER DATABASE BIWORK_SSIS
SET ALLOW_SNAPSHOT_ISOLATION ON

SERIALIZABLE（序列化）

性能最低，隔离界别最高最严格，可以几乎上面提到的所有问题。比如不能读取其他已由其它事务修改但是没有提交的数据，不允许其他事务在当前事务完成修改之前修改由当前事务读取的数据，不允许其他事务在当前事务完成修改之前插入新的行。作用于在事务内所有SELECT语句中的所有表上设置HOLDLOCK相同，并发级别比较低但又对安全性要求比较高的时候可以考虑使用。如果并发级别很高，使用这个隔离级别，性能瓶颈将非常严重。

数据库锁定

加锁是SqlServer数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

当有事务操作时，数据库引擎会要求不同类型的锁定，当锁定运行时，会阻止其他事务对已经锁定的数据行、数据页或数据表进行操作。只有在当时事务对于自己锁定的资源不在需要时，才会释放其锁定的资源，供其他事务使用。

锁的种类与范围如下

共享锁（S）

用于不更改或不更新数据的读取操作，如SELECT语句。

允许并发事务在封闭式并发控制下读取SELECT资源。

当查询某条记录时，SQL SERVER会尝试取得该条记录上存在的共享锁。如无法获取，就必须等待别人释放对该记录中某几种与共享锁互斥的锁，才能在设置共享锁之后，获取该条记录。

当需要查询某条记录时，会在该记录上防止共享锁，而其他人在查询此记录时，因为共享锁彼此不互斥，所以可以再次放置共享锁。如果有人要更新此记录，因为独占锁与共享锁互斥，所以无法放置独占锁，要等到所有读取此记录的人都读取完毕，释放了共享锁，更新数据的人才能对该记录设置独占锁，并进一步更新数据。默认的事务隔离级别下，当数据读取完毕，SqlServer会释放共享锁

更新锁（U）

用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁

更新锁是一种中继锁，当同一项资源从原来的查询操作转换为更新操作时，锁定机制会从共享锁变为更新锁，进一步变成独占锁。

在可重复读或可序列化事务中，此事务读取数据，然后修改数据。如果两个事务获得资源上的共享锁，然后试图同时更新数据，则一个事务尝试将锁转换为独占锁。共享模式到独占锁的转换必须等待一段时间，因为一个事务的独占锁与其他事务的共享锁不兼容，发生锁等待。第二个事务试图获取独占锁以进行更新，由于两个事务都要转换为独占锁，并且每个事务都等待另一个事务释放共享锁，因此发生死锁。

若避免这种死锁问题，请使用更新锁。一次只有一个事务可以获得资源的更新锁，如果事务修改资源，则更新锁转换为独占锁。

当查询一条记录后，将次内容更新，一定会先查找记录，在查找的过程中会对相关的记录放置共享锁，等找到相应的记录之后，会对记录放置更新锁，以避免发生死锁。因为共享锁与更新锁并不互斥，如果两个人同时对同一条记录放置共享锁，先进行更新的人，可以在别人放置共享锁时，继续放置更新锁，因为更新锁互斥，当另一个人想再放置更新锁时，无法设置，进入停止等待状态。

独占锁/排他锁（X）

用于数据修改操作，例如INSERT、UPDATE、DELETE。确保不会同时对同一资源进行多重更新

可以防止并发事务对资源进行访问。使用独占锁时时，任何其他事务都无法修改数据。仅在使用NOLOCK提示或未提交读隔离级别时才会进行读取操作。

对数据进行添加、修改、删除操作时，语句在执行所需的操作之前首先执行读取操作以获取数据。因此需先对所在的资源放置独占锁，以确保以上操作未完成时不受到干扰，独占锁在开启事务之后，一直保留到事务结束。

UPDATE语句可能根据与一个表的连接修改另一个表中的行，再次情况下，除了请求更新行上的独占锁之外，UPDATE还将请求在连接表中读取的行上的共享锁

意向锁

用于建立锁的层次结构。意向锁包含三种类型：意向共享（IS）、意向排他（IX）、意向排他共享（SIX）

在记录上放置共享锁之前，需要对存放该记录的更大范围上设置意向锁，以避免其他连接对该页放置独占锁。

数据库引擎使用意向锁来保护共享锁（S）或独占锁（X）放置在锁层次结构的底层资源上。意向锁在较低级别锁前可以获取它们，因此会通知将意向锁放置在较低级别上。

意向锁有两种用途：

防止其他事务以会使较低级别的锁无效的方式修改较高级别的资源。
提高数据库引擎在较高的粒度级别检测锁冲突的效率

在该表的数据页或数据行上请求共享锁之前，在表级请求共享意向锁，以防止另一个事务随后在包含那一页的表上尝试放置独占锁。意向锁可以提高性能，因为数据库引擎仅在表级别检查意向锁来确定事务是否可以安全地获取该表上的锁。而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表

意向锁包括意向共享（IS）、意向排他（IX）、意向排他共享（SIX）等等

意向共享（IS）

保护针对层次结构中某些低层资源请求或获取的共享锁

意向独占（IX）

保护针对层次结构中某些（而并非所有）低层资源请求或获取的独占锁。 IX 是 IS 的超集，它也保护针对低层级别资源请求的共享锁。

意向独占共享（SIX）

保护针对层次结构中某些（而并非所有）低层资源请求或获取的共享锁以及针对某些（而并非所有）低层资源请求或获取的意向独占锁。 *资源允许使用并发 IS 锁。例如，获取表上的 SIX 锁也将获取正在修改的页上的意向独占锁以及修改的行上的独占锁。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其他事务对资源进行更新，但是其他事务可以通过获取表级的 IS 锁来读取层次结构中的低层资源。

意向更新（IU）

保护针对层次结构中所有低层资源请求或获取的更新锁。仅在页资源上使用 IU 锁。如果进行了更新操作，IU 锁将转换为 IX 锁。

共享意向更新（SIU）

S 锁和 IU 锁的组合，作为分别获取这些锁并且同时持有两种锁的结果。例如，事务执行带有 PAGLOCK 提示的查询，然后执行更新操作。带有 PAGLOCK 提示的查询将获取 S 锁，更新操作将获取 IU 锁。

更新意向排他（UIX）

U 锁和 IX 锁的组合，作为分别获取这些锁并且同时持有两种锁的结果。

架构锁

在执行依赖于表架构的操作时使用。架构锁包含两种类型：架构修改（Sch-M）和架构稳定性（Sch-S）

数据库引擎在表数据定义语言（DDL）操作的过程中使用架构修改锁。保持该锁期间，Sch-M锁将阻止对表进行并发访问。

数据库引擎在编译和执行查询时使用架构稳定性锁，不会阻止某些事务锁，其中包括排他锁。因此在编译查询的过程中，其他事务将继续运行。但是无法针对表执行获取Sch-M锁的并发DDL操作和并发DML操作

大容量更新锁（BU）

在向表进行大容量数据复制且制定了TABLOCK提示时使用

数据库引擎在将数据大容量复制到表中时，指定TABLOCK提示或使用sp_tableoption选项，则是使用大容量更新锁。大容量更新锁允许多个线程将数据并发地大容量加载到同一表，以降低数据表的锁定竞争，同时防止其他不进行大容量加载数据的进程访问该表。

键范围锁

当使用可序列化事务隔离级别时保护查询读取的行的范围。确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

在使用可序列化事务隔离界别时，保护用户对查询时所读取的数据行范围，以确保其他事务无法插入受"键范围锁"保护的数据行。键范围锁放置在索引上，指定开始于结束的索引键值。这些操作会先在索引上获取锁定，此种锁定可以*任何尝试插入、修改、删除索引键值在"键范围锁"中的数据行。

SQL优化

查看执行时间和CPU占用时间

set statistics time on
SELECT * FROM MSTB_ACCL_ABRS_SHOP_INFO;
set statistics time off

消息里面就可以查看执行时间了

查看查询对I/O的操作情况

set statistics io on
SELECT * FROM MSTB_ACCL_ABRS_SHOP_INFO;
set statistics io off

扫描计数：索引或表扫描次数

逻辑读取：数据缓存中读取的页数

物理读取：从磁盘中读取的页数

预读：查询过程中，从磁盘放入缓存的页数

lob逻辑读取：从数据缓存中读取，image，text，ntext或大型数据的页数

lob物理读取：从磁盘中读取，image，text，ntext或大型数据的页数

lob预读：查询过程中，从磁盘放入缓存的image，text，ntext或大型数据的页数

如果物理读取和预读次数比较多，可以使用索引优化

查询->查询选择->高级，勾选SET STATISTICS TIME可以不用在SQL中指定

查看执行计划

执行右侧有显示执行计划，会显示此步骤执行的详细内容。表下面都显示一个开销百分比

select查询优化

保证不查询多余的列与行。

慎用distinct关键字，只在查询一个字段或者很少字段的情况下使用

慎用union关键字，主要功能是把各个查询语句的结果集合并到一个结果集中返回给你。会依次执行select语句-->合并结果集-->对结果集进行排序。效率很低，应避免使用

判断表中是否存在：select top(1) id from product

insert查询优化

尽量使用insert into select批量插入，明显提升效率

SqlServer表分区

一般情况下，建立数据库时，表数据都存放在一个文件里。但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分成多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减少，还得到硬件系统的加强，自然对我们操作数据是大大有利的。

所以大数据量的数据表，对分区的需要还是必要的，因为可以提高select效率，还可以对历史数据进行区分存档。表分区会对数据库产生不必要的开销，除了性能会增加实现对象的管理费用和复杂性。

分区是要把一个表数据拆分为若干子集合，把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或者多个文件组，多个文件组可以提高数据库的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，创建时建议分区跟文件组个数相同。

创建文件组

alter database 数据库名 add filegroup 文件组名

创建数据文件到文件组里面

alter database 数据库名 add file 数据标识 to filegroup 文件组名

<数据标识> （name:文件名，fliename:物理路径文件名，size:文件初始大小kb/mb/gb/tb，filegrowth:文件自动增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited）

alter database AmwayFrameworkWorkflow add file (name=N'ById1',filename=N'D:\数据库\ById1.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup1;
alter database AmwayFrameworkWorkflow add file (name=N'ById2',filename=N'D:\数据库\ById2.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup2;
alter database AmwayFrameworkWorkflow add file (name=N'ById3',filename=N'D:\数据库\ById3.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup3;
alter database AmwayFrameworkWorkflow add file (name=N'ById4',filename=N'D:\数据库\ById4.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup4;
alter database AmwayFrameworkWorkflow add file (name=N'ById5',filename=N'D:\数据库\ById5.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup5;

可以右键数据库->属性->文件组中查找

使用向导创建分区表

右键要分区的表->存储->创建分区->显示向导视图

会让你选择可用分区列，如果是int类型，那么你可以指定1-100W是一个分区，100W-200W是另一个分区。

加入你选择的是datatime，那么可以指定日期作为分区。

http://www.cnblogs.com/knowledgesea/p/3696912.html

SqlServer游标

http://www.cnblogs.com/knowledgesea/p/3699851.html

￥

码农公寓