精华内容
下载资源
问答
  • 解决数据库高并发访问瓶颈问题 一、缓存式的Web应用程序架构:  在Web层和db层之间加一层cache层,主要目的:减少数据库读取负担,提高数据读取速度。cache存取的媒介是内存,可以考虑采用分布式的cache层,这样...

    解决数据库高并发访问瓶颈问题

    一、缓存式的Web应用程序架构:

      在Web层和db层之间加一层cache层,主要目的:减少数据库读取负担,提高数据读取速度。cache存取的媒介是内存,可以考虑采用分布式的cache层,这样更容易破除内存容量的限制,同时增加了灵活性。

     

    二、业务拆分:

      电商平台,包含了用户、商品、评价、订单等几大模块,最简单的做法就是在一个数据库中分别创建users、shops、comment、order四张表。

      但是,随着业务规模的增大,访问量的增大,我们不得不对业务进行拆分。每一个模块都使用单独的数据库来进行存储,不同的业务访问不同的数据库,将原本对一个数据库的依赖拆分为对4个数据库的依赖,这样的话就变成了4个数据库同时承担压力,系统的吞吐量自然就提高了。

      

     

    三、MySQL主从复制,读写分离:

      当数据库的写压力增加,cache层(如Memcached)只能缓解数据库的读取压力。读写集中在一个数据库上让数据库不堪重负。使用主从复制技术(master-slave模式)来达到读写分离,以提高读写性能和读库的可扩展性。读写分离就是只在主服务器上写,只在从服务器上读,基本原理是让主数据库处理事务性查询,而从数据库处理select查询,数据库复制被用于把事务性查询(增删改)导致的改变更新同步到集群中的从数据库。

    MySQL读写分离提升系统性能:

      1、主从只负责各自的读和写,极大程度缓解X锁和S锁争用。

      2、slave可以配置MyISAM引擎,提升查询性能以及节约系统开销。

      3、master直接写是并发的,slave通过主库发送来的binlog恢复数据是异步的。

      4、slave可以单独设置一些参数来提升其读的性能。

      5、增加冗余,提高可用性。

    实现主从分离可以使用MySQL中间件如:Atlas

     

      MySQL主从复制的原理:数据复制的实际就是Slave从Master获取Binary log文件,然后再本地镜像的执行日志中记录的操作。由于主从复制的过程是异步的,因此Slave和Master之间的数据有可能存在延迟的现象,此时只能保证数据最终的一致性。

     

    四、分表分库:

      在cache层的高速缓存,MySQL的主从复制,读写分离的基础上,这时MySQL主库的写压力开始出现瓶颈,而数据量的持续猛增,由于MyISAM使用表锁,在高并发下会出现严重的锁问题,大量的高并发MySQL应用开始使用InnoDB引擎代替MyISAM。采用Master-Slave复制模式的MySQL架构,只能对数据库的读进行扩展,而对数据的写操作还是集中在Master上。这时需要对数据库的吞吐能力进一步地扩展,以满足高并发访问与海量数据存储的需求。

      对于访问极为频繁且数据量巨大的单表来说,首先要做的是减少单表的记录条数,以便减少数据查询所需的时间,提高数据库的吞吐,这就是所谓的分表。在分表之前,首先需要选择适当的分表策略,使得数据能够较为均衡地分布到多张表中,并且不影响正常的查询。

      分表能够解决单表数据量过大带来的查询效率下降的问题,但是却无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问,当数据库master服务器无法承载写操作压力时,不管如何扩展Slave服务器都是没有意义的,对数据库进行拆分,从而提高数据库写入能力,即分库

      数据库经过业务拆分及分库分表,虽然查询性能和并发处理能力提高了。但是原本跨表的事务上升为分布式事务;由于记录被切分到不同的库和不同的表中,难以进行多表关联查询,并且不能不指定路由字段对数据进行查询。且分库分表后需要进一步对系统进行扩容(路由策略变更)将变得非常不方便,需要重新进行数据迁移。

     

    分表策略:

      使用用户ID是最常用的分库的路由策略。

      当数据比较大的时候,对数据进行分表操作,首先要确定需要将数据平均分配到多少张表中,也就是:表容量。

      这里假设有100张表进行存储,则我们在进行存储数据的时候,首先对用户ID进行取模操作,根据 user_id%100 获取对应的表进行存储查询操作。

      在实际的开发中,我们的用户ID更多的可能是通过UUID生成的,这样的话,我们可以首先将UUID进行hash获取到整数值,然后在进行取模操作。

     

    分库策略:

     

      数据库分表能够解决单表数据量很大的时候数据查询的效率问题,但是无法给数据库的并发操作带来效率上的提高,因为分表的实质还是在一个数据库上进行的操作,很容易受数据库IO性能的限制。

      因此,如何将数据库IO性能的问题平均分配出来,很显然将数据进行分库操作可以很好地解决单台数据库的性能问题。

      分库策略与分表策略的实现很相似,最简单的都是可以通过取模的方式进行路由。

     

    分库与分表实现策略:

      上述的配置中,数据库分表可以解决单表海量数据的查询性能问题,分库可以解决单台数据库的并发访问压力问题。

      有时候,我们需要同时考虑这两个问题,因此,我们既需要对单表进行分表操作,还需要进行分库操作,以便同时扩展系统的并发处理能力和提升单表的查询性能,就是我们使用到的分库分表。

      分库分表的策略相对于前边两种复杂一些,一种常见的路由策略如下:

      1、中间变量 = user_id%(库数量*每个库的表数量);   

      2、库序号 = 取整(中间变量/每个库的表数量);   

      3、表序号 = 中间变量%每个库的表数量;

     

    一般我们都采用:使用Atomikos多库数据源控制事务,业务层面分库(不同库内不会出现同一张表),库内hash分表;这样就是将数据库压力在业务层面分散开,大表的查询效率通过hash分库的方式快速锁定。

    展开全文
  • 有一些技术同学可能对于“读写分离”了解不多,认为数据库的负载问题都可以使用“读写分离”来解决。这其实是一个非常大的误区,我们要用“读写分离”,首先应该明白“读写分离”是用来解决什么样的问题的,而不是...

    有一些技术同学可能对于“读写分离”了解不多,认为数据库的负载问题都可以使用“读写分离”来解决。

    这其实是一个非常大的误区,我们要用“读写分离”,首先应该明白“读写分离”是用来解决什么样的问题的,而不是仅仅会用这个技术。

    什么是读写分离?

    其实就是将数据库分为了主从库,一个主库用于写数据,多个从库完成读数据的操作,主从库之间通过某种机制进行数据的同步,是一种常见的数据库架构。

    一个组从同步集群,通常被称为是一个“分组”。

    数据库分组架构解决什么问题?

    大多数互联网业务,往往读多写少,这时候,数据库的读会首先称为数据库的瓶颈,这时,如果我们希望能够线性的提升数据库的读性能,消除读写锁冲突从而提升数据库的写性能,那么就可以使用“分组架构”(读写分离架构)。

    用一句话概括,读写分离是用来解决数据库的读性能瓶颈的。

    但是,不是任何读性能瓶颈都需要使用读写分离,我们还可以有其他解决方案。

    在互联网的应用场景中,常常数据量大、并发量高、高可用要求高、一致性要求高,如果使用“读写分离”,就需要注意这些问题:

    数据库连接池要进行区分,哪些是读连接池,哪个是写连接池,研发的难度会增加;为了保证高可用,读连接池要能够实现故障自动转移;主从的一致性问题需要考虑。在这么多的问题需要考虑的情况下,如果我们仅仅是为了解决“数据库读的瓶颈问题”,为什么不选择使用缓存呢?

    为什么用缓存

    缓存,也是互联网中常常使用到的一种架构方式,同“读写分离”不同,读写分离是通过多个读库,分摊了数据库读的压力,而存储则是通过缓存的使用,减少了数据库读的压力。他们没有谁替代谁的说法,但是,如果在缓存的读写分离进行二选一时,还是应该首先考虑缓存。

    为什么呢?

    缓存的使用成本要比从库少非常多;缓存的开发比较容易,大部分的读操作都可以先去缓存,找不到的再渗透到数据库。当然,如果我们已经运用了缓存,但是读依旧还是瓶颈时,就可以选择“读写分离”架构了。简单来说,我们可以将读写分离看做是缓存都解决不了时的一种解决方案。

    当然,缓存也不是没有缺点的

    对于缓存,我们必须要考虑的就是高可用,不然,如果缓存一旦挂了,所有的流量都同时聚集到了数据库上,那么数据库是肯定会挂掉的。

    对于常见的数据库瓶颈是什么呢?

    其实是数据容量的瓶颈。例如订单表,数据量只增不减,历史数据又必须要留存,非常容易成为性能的瓶颈,而要解决这样的数据库瓶颈问题,“读写分离”和缓存往往都不合适,最适合的是什么呢?

    数据库水平切分

    什么是数据库水平切分?

    数据库水平切分,也是一种常见的数据库架构,是一种通过算法,将数据库进行分割的架构。一个水平切分集群中的每个数据库,通常称为一个“分片”。每一个分片中的数据没有重合,所有分片中的数据并集组成全部数据。

    水平切分架构解决什么问题呢?

    大部分的互联网业务,数据量都非常大,单库容量最容易成为瓶颈,当单库的容量成为了瓶颈,我们希望提高数据库的写性能,降低单库容量的话,就可以采用水平切分了。

    而有少部分程序员,会没有分析数据库的性能瓶颈是什么,就贸贸然的使用“读写分离”,殊不知“水平切分”才是正道。

    展开全文
  • 数据库学习】数据库总结

    万次阅读 多人点赞 2018-07-26 13:26:41
    1,数据库 1)概念 数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。 常见数据库管理系统有:Access、mysql、sql server 2)特点 ①数据库数据特点 永久存储、有组织...

    1,概念

    1)数据库

    数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。
    数据库中存储的是数据及数据之间的关系。

    正常情况读写文件系统比数据库快一到两个数据级;
    数据库的查询,大量并发的时候可能最浪费时间的是connect和close。
    数据库的优势是体现的大量数据的查询、统计以及并发读写,不是在速度上。

    2)数据库数据特点

    永久存储、有组织、可共享。
    (数据的最小存取单位是数据项)

    3)数据库系统的特点

    ①数据结构化

    ②数据的共享性,冗余度,易扩充

    ③数据独立性高

    数据独立性包括:物理独立性和逻辑独立性
    a)物理独立性(外模式\模式映像):
    用户程序不需要了解,应用程序要处理的只是数据的逻辑结构,这样当数据的物理存储改变了,应用程序不用改变。
    b)逻辑独立性(模式\内模式映像):
    逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的,即,当数据的逻辑结构改变时,用户程序也可以不变。
    逻辑数据独立性(logical data independence)是指概念模式改变,外模式和应用程序不变。在逻辑数据独立性里,数据的逻辑结构发生改变或存储关系的选择发生改变时用户不会受到影响。改变概念模式,例如增加和删除实体、增加和删除属性、增加和删除联系,不需要改变现有的外模式或重写应用程序。在DBMS中只需要修改视图的定义和映像来支持逻辑数据独立性。对用户来说,不再关心所做的修改是非常重要的。换句话说,模式经过逻辑重构之后,根据外模式构建的应用程序还是和从前一样工作。

    4)概念模型(E-R模型)

    ①概念

    概念模型的一种表示方法:实体联系方法,用E-R方法(E-R模型)来描述。
    概念模型是用于信息世界的建模,是一种信息模型,与具体的DBMS无关。且能满足用户对数据的处理要求,易于修改。
    概念模型与具体数据模型无关且容易向数据库模型转化。

    实体:举行表示
    属性:椭圆表示,并用直线与实体连接
    联系:菱形表示,用直线与实体连接,同时在边上标上联系的类型(1:1,1:n,m:n)。
    

    一个联系转化为一个关系模式,与该联系相连的各实体的码以及联系的属性转化为关系的属性,该关系的码则有三种情况:
    若联系为1:1,则每个实体的码均是该关系的后选码。
    若联系为1:n,则关系的码为n端实体的码。
    若联系为m:n,则关系的码为诸实体码的组合。

    数据库模式定义语言DDL(Data Definition Language):是用于描述数据库中要存储的现实世界实体的语言。一个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作方法定义等。

    数据库逻辑设计: 将概念设计所得到的概念模型转换为某一具体的数据模型(层次、网状、关系、面向对象).

    5)关系完整性

    在关系模型中,关系完整性主要是指以下三方面:

    实体完整性

    所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值;
    比如学生表的主码通常是取学号为主码

    参照完整性

    是指参照关系中每个元素的外码要么为空(NULL),要么等于被参照关系中某个元素的主码;
    参照关系也称为外键表,被参照关系也称为主键表。

    用户定义的完整性

    指对关系中每个属性的取值作一个限制(或称为约束)的具体定义。比如 性别属性只能取”男“或”女“,再就是年龄的取值范围,可以取值0-130 ,但不能取负数,因为年龄不可能是负数。

    6)关系数据库规范化

    目地:使结构更合理,消除存储异常,使数据冗余尽量小,便于插入、删除和更新。
    原则:遵从概念单一化“一事一地”原则,即一个关系模式描述一个实体或实体间的一种联系。
    规范的实质:概念的单一化。
    规范化的方法:将关系模式投影分解成两个或两个以上的关系模式。

    2,依赖和范式

    1)依赖

    ①部分函数依赖

    设X,Y是关系R的两个属性集合,存在X→Y,若X’是X的真子集,存在X’→Y,则称Y部分函数依赖于X。

        举个例子:通过AB能得出C,通过A也能得出C,通过B也能得出C,那么说C部分依赖于AB。
    

    ②完全函数依赖

    设X,Y是关系R的两个属性集合,X’是X的真子集,存在X→Y,但对每一个X’都有X’!→Y,则称Y完全函数依赖于X。

        举个例子:通过AB能得出C,但是AB单独得不出C,那么说C完全依赖于AB.
    

    ③传递函数依赖

    设X,Y,Z是关系R中互不相同的属性集合,存在X→Y(Y !→X),Y→Z,则称Z传递函数依赖于X。

        举个例子:通过A得到B,通过B得到C,但是C得不到B,B得不到A,那么成C传递依赖于A
    

    ④多值依赖

    设R(U)是属性集U上的一个关系模式。X,Y,Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成立,当且仅当对R(U)的任一关系r,给定的一对(x,z)值有一组Y的值,这组值仅仅决定于x值而与z值无关。

    举例:
    有这样一个关系 <仓库管理员,仓库号,库存产品号> ,假设一个产品只能放到一个仓库中,但是一个仓库可以有若干管理员,那么对应于一个 <仓库管理员,库存产品号>有一个仓库号,而实际上,这个仓库号只与库存产品号有关,与管理员无关,就说这是多值依赖。

    2)范式

    各个范式联系:
    5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF

    ①1NF(满足最低要求的范式:字段不可再分,原子性)

    如果一个关系模式R的所有属性都是不可分的基本数据项,则R∈1NF。
    自我理解1NF就是无重复的列。
    如:(X1,X2)→X3,X2→X3 其中x3对x2部分依赖
    如:(X1,X2)→X3,X2→X4 其中有非主属性X4部分依赖于候选键{X1,X2},所以这个关系模式不为第二范式;又因为范式之间的关系满足1NF⊇2NF⊇3NF ⊇ BCNF,所以是第一范式。

    ②2NF(消除部分子函数依赖:一个表只能说明一个事物)

    若R∈1NF,且每一个非主属性完全函数依赖于码,则R∈2NF。
    即要求数据库表中的每个实例或行必须可以被唯一地区分。

    ③3NF(消除传递依赖,即消除非主属性对键的传递依赖:每列都与主键有直接关系,不存在传递依赖。任何非主属性不依赖于其它非主属性。)

    若R∈3NF,则每一个非主属性既不部分依赖于码,也不传递依赖于码。
    自我理解是:表中所有的数据元素不但要能唯一地被主键所标识,而且他们之间还必须相互独立,不存在其他的函数关系。

    ④BCNF(修正第三范式、扩充第三范式 消除主属性对键的传递依赖)

    所有非主属性对每一个码都是完全函数依赖;
    所有主属性对每一个不包含它的码,也是完全函数依赖;
    没有任何属性完全函数依赖于非码的任何一组属性。

    ⑤4NF

    关系模式R<U,F>∈1NF,如果对于R的每个非平凡多值依赖X->->Y(Y∉X),X都含有码,则称R<U,F>∈4NF

    3,数据库平台

    数据库管理系统(DBMS):是系统软件,是数据库系统的核心。
    常见数据库管理系统有:Access、mysql、sql server

    4,数据库语句

    SQL 语言是非过程化的语言,易学习。
    SQL语言具有两种使用方式:一种是在终端交互方式下使用,称为交互式SQL; 另一种是嵌入在高级语言的程序中使用,称为嵌入式SQL,而这些高级语言可以是C、PASCAL、COBOL等,称为宿主语言。

    1)基本对象

    关系数据库系统支持 三级模式结构,其概念模式、外模式和内模式中的基本对象有表、视图和索引。
    三级模式结构有效地组织、管理数据,提高了数据库的逻辑独立性和物理独立性。使数据库达到了数据独立性。

    ①模式(schema,逻辑模式)

    A.概念

    是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共数据视图。是数据库系统模式结构的中间层,即不涉及数据的物理存储细节和硬件环境,也与具体的应用程序、开发工具及高级设计语言无关。
    模式是数据库数据在逻辑级上的视图,一个数据库只有一个模式。

    也用于区分一个 大项目中的各个小项目,这样若有相同名字的表的话, 不同模式不会发生冲突。相当于编程时的命名空间。
    如:
    一个公司的系统,分2个子系统,分别为财务系统和人力资源系统.
    这2个子系统, 共用一个数据库。
    那么 财务系统的表, 可以放在财务的模式(schema).
    人力资源系统的表,放在人力资源系统的模式里面。
    这2个子系统,能够互相访问对方的表。
    但是又不因为 表重名 的问题,影响对方。

    B.访问

    访问具体的一个表,可以由 4个部分组成
    分别为 服务器名, 数据库名,模式名,表名。

    对于访问本地的数据库:
    不指定模式名的话, 数据库默认使用dbo模式。
    (DBO是每个数据库的默认用户,具有所有者权限,即DbOwner )
    pg不指定模式的话默认使用public模式。

    C.操作

    --创建
    CREATE SCHEMA schema_name;
    

    ②外模式(子模式,用户模式)

    是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图,是与某一应用有关的数据的逻辑表示。
    外模式通常是模式的子集,一个数据库可以有多个外模式,但一个应用程序只能有一个外模式。
    外模式是保证数据库安全性的一个有力措施:用户只能访问外模式的数据,其余数据不可见。

    ③内模式(存储模式)

    一个数据库只有一个内模式。
    内模式是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式。

    数据库管理系统在三级模式之间提供了两层映像:
    外模式/模式映像(保证数据的逻辑独立性)
    模式/内模式映像(保证了物理独立性)

    ④表

    表分为临时表和永久表。

    临时表

    临时表存储在tempdb中(如下),当不再使用时会自动删除。

    IF OBJECT_ID('tempdb..#ownerAnnouce') IS NOT NULL
    

    根据进程独立,只有进程的拥有者有表的访问权限,其它用户不能访问该表;
    不同的用户进程,创建的临时表虽然“名字”相同,但是这些表之间相互并不存在任何关系;在SQLSERVER中,通过特别的命名机制保证临时表的进程独立性。

    临时表有两种类型:本地和全局。

    A.本地临时表

    名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 SQL Server 实例断开连接时被删除。

    B.全局临时表

    名称以两个数字符号 (##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。

    临时表优点

    真正的临时表利用了数据库临时表空间,由数据库系统自动进行维护,因此节省了表空间。并且由于临时表空间一般利用虚拟内存,大大减少了硬盘的I/O次数,因此也提高了系统效率。

    临时表的创建

    A. create table #临时表名
    B.select * into #临时表名 from 表名(永久表或临时表)

    ⑤视图

    A.概念

    视图是一张虚拟表,视图的字段是自定义的,视图只支持查询,查询数据来源于实体表。

    一般视图是只读的,在pg中通过添加规则可以进行视图的更新。从pg9.1开始,用户可以通过INSTEAD OF的触发器来实现视图更新。

    B.优缺点

    • 优点
      视图可以将多个复杂关联表提取信息,方便查询,但不能优化查询速度(调用视图查询时才进行动态检索数据)。
      即,如果你认为一个sql查询非常慢,为了优化它的速度把它建立成视图,这是不可取的,视图是每次调用的时候生成,并不是数据源变化就刷新数据,并不能提高检索效率。
    • 缺点
      视图就是临时表,即调即用,如果数据源没有任何变化,在反复调用中,临时表会缓存到内存中(SHOW STATUS LIKE ‘Qcache%’;),视图中不能创建索引,但视图可以基于索引生成 。

    C.场景

    1. 重用SQL语句;
    2. 简化复杂SQL操作(生成视图),重用查询且不需要知道基本查询细节。
    3. 保护数据。用户有表的部分权限。
    4. 更改数据格式和表示。视图可返回与底层表不同的表示和格式。

    D.操作

    --创建视图
    CREATE OR REPLACE VIEW view_name(studentName, studentAge)  --(studentName, studentAge) 可以去掉,加上是重命名列名
    AS 
    SELECT user_info.name, user_info.age from user_info;
    
    --删除视图
    DROP VIEW view_name;
    

    ⑥实体视图

    相对于普通的视图来说,实体化视图的不同之处在于实体化视图管理存储数据,占据数据库的物理空间。

    实体化视图的结果会保存在一个普通的数据表中,在对实体化视图进行查询的时候不再会对创建实体化视图的基表进行查询,而是直接查询实体化视图对应的结果表,然后通过定期的刷新机制来更新实体化视图表中的数据。

    demo

    -- 创建物化视图
    CREATE MATERIALIZED VIEW MAX_ID_MVIEW 
    AS
      SELECT PART_ID, MAX(ID)  MAX_ID
      FROM PART_DETAIL GROUP BY PART_ID;
      
    -- 如果刷新时不带CONCURRENTLY则无需创建唯一索引
    CREATE UNIQUE INDEX IDX_MAX_ID ON MAX_ID_MVIEW(PART_ID);
    
    -- 利用watch命令每120s刷新一次物化视图
    REFRESH MATERIALIZED VIEW CONCURRENTLY MAX_ID_MVIEW; \watch 120
    

    作用

    1. 减轻网络负担:通过实体化视图将数据从一个数据库分发到多个不同的数据库上,通过对多个数据库访问来减轻对单个数据库的网络负担。
    2. 搭建分发环境:通过从一个中央数据库将数据分发到多个节点数据库,达到分发数据的目的。
    3. 复制数据子集:实体化视图可以进行行级/列级的筛选,这样可以复制需要的那一部分数据。
    4. 实体化视图是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度。
    5. 物化视图的快速刷新采用了增量的机制,在刷新时,只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。

    ⑦索引

    为了改变数据库的性能和可访问性所增加的一组辅助性数据。
    详细介绍见下文。

    2)数据结构创建及修改

    1>数据库操作

    --查看数据库
    show databases;
    --建库
    create database children;
    --删库
    drop database children;
    --调用数据库
    use children;
    

    2>表操作

    --pg建表
    CREATE TABLE if not exists public.stu_info(   --创建public模式下的表
        FOREIGN KEY (ID) REFERENCES people_info (ID),  --单个外键,一般情况下不建议增加这种强约束
        id int8 PRIMARY KEY,    --系统会自动为主键创建一个隐含的索引  primary key(Sno,Cno)组合主键
        address VARCHAR (255) UNIQUE NOT NULL,
        birthday TIMESTAMP NOT NULL,  
        age int default 15,  --默认值,影响后续插入值。但对旧数据没有影响。
       CONSTRAINT student2_pkey PRIMARY KEY (id),
       CONSTRAINT ck_age CHECK(age<18), --检查约束,约束某些字段需要满足的要求。NULL被认为满足条件。
       CONSTRAINT uk_tbl_unique_a_b unique(id ,address) --唯一约束。唯一键中可以写入任意多个NULL!即可以存在多组 1,null  
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE myschema.tb_test
      OWNER TO postgres;
    
    --重命名表
    alter table tableName RENAME TO newName;--pg
    

    i>指定默认值

    一般用于数据预置或create_time、update_time的自动录入。各个DBMS获得系统日期如下:

    DBMS函数/变量
    AccessNOW()
    DB2CURRENT_DATE
    MySQLCURRENT_DATE()
    OracleSYSDATE
    PostgreSQLCURRENT_DATE
    SQL ServerGETDATE()
    SQLitedate(‘now’)
    --修改默认值
    alter table tableName alter column age set DEFAULT 15;--pg
    --删除默认值
    alter table tableName alter column age drop DEFAULT 15;--pg
    

    ii>表约束

    表约束有:主键、外键、检查约束、唯一约束、非NULL约束。

    --添加主键(有些DBMS不允许在建表之后修改主键)
    ALTER TABLE tableName ADD PRIMARY KEY(fieldName) ; --fieldName在库中不能有重复数据
    --增加约束
    alter table tableName add check (age<16);--pg 增加检查约束,约束名为:tableName_age_check
    alter table tableName add constraint uk_tbl_unique_a_b unique (a,b);--pg 增加唯一约束
    alter table tableName alter column fieldName set NOT NULL;--pg 增加非空约束
    --删除约束
    alter table tableName drop constraint constraintName;--pg 根据约束名删除检查约束、唯一约束
    alter table tableName alter column fieldName drop NOT NULL;--pg 删除非空约束(非空约束没有约束名)
    

    iii>修改表字段

    --增加列\添加一个字段
    alter table tableName add column columnName varchar(30) default 'a' not null; --column 可加可不加
    --删除列(会连同字段上的约束一并删除)
    alter table tableName drop column columnName; --column 可加可不加
    --修改列名:
    alter table tableName rename column fieldName TO fieldNameNew;--pg、oracle中
    exec sp_rename '[表名].[列名]‘,’[表名].[新列名]'--在sqlserver
    ALTER TABLE 表名 CHANGE 列名 新列名 列类型--mysql
    
    --修改字段类型或长度:
    alter table tableName modify column 字段名 类型;
    alter table tableName alter column fieldName TYPE text;--pg修改字段数据类型。仅在当前数据都可以隐式转换为新类型时才可以执行成功
    --将NAME最大列宽增加到10个字符
    ALTER TABLE CARD ALTER COLUMN NAME varchar(10) 
    
    

    3)数据查询

    数据库处理一个查询的步骤:
    客户端连接->查询缓存->解析器->预处理器->查询优化器->查询执行引擎->数据

    1. 客户端发送一条查询给服务器;
    2. 服务器先会检查查询缓存query cache,如果命中了缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;
    3. 服务器端进行SQL解析parsing、预处理transition,再由优化器optimization生成对应的执行计划;
    4. 根据优化器生成的执行计划,调用存储引擎的API来执行分布distribution查询;
    5. 将结果返回给客户端。
    

    1>简单查询

    select * from student;
    select 1+2; #当表达式与表列无关时,在pg和mysql中不适用“from tableName”
    

    拼接查询:
    Access和 SQL Server使用 + 号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base 使用 ||。

    select label || '_' || id from user_info;  --结果:abc_1
    

    2>条件查询

    功能表达举例备注
    等于=
    不等于<>!=
    空值is null,is not nullselect * from student where class is not null;
    确定集合,ininnot inselect * from student where age not in(21,23);在sql标准中仅支持100个以内的占位符作为查询参数。根据数据库不同,对in的参数和长度有不同的限制,否则会直接报错。
    确定范围between and , not between and
    模糊查询like ,not likeselect * from student where name like '%丽%';’ %代表任意长度(可为0)的字符串;_(下划线):代表任意单个字符。(汉字代表2个字符,所以一个汉字用两个下划线);\为转义字符

    select出的别名是否可以作为where查询条件?不能,因为执行计划中where在selectz之前。如:select label a from asset_field where a = '分类'

    3>排序查询

    非排序查询的数据顺序:pg默认返回数据的顺序是插入表的数据顺序。

    # 单个排序:
    select name,age from student order by age desc; # 默认为asc:升序排列。desc:降序排序。
    
    #多重排序:
    order by 字段5,字段6 asc  //先按字段5排序,再按字段6排序
    

    4>case when then查询

    --简单case函数
    case sex
      when '1' then '男'
      when '2' then '女’
      else '其他' end
    --case搜索函数
    case when sex = '1' then ''
         when sex = '2' then ''
         else '其他' end  
    

    应用:

    select (case sex
    		  when '1' then '男'
    		  when '2' then '女’
    		  else '其他' end)sex from student where class = 11;
    

    5>where、group by、having

    大部分的where都可以背having代替,不同的是where过滤行,而having过滤分组,用在group by之后。(where在分组前过滤,having在分组后过滤)

    select class,avg(age) as age from student 
    group by class 
    having avg(age)>23 /*要求平均年龄大于23*/
    

    where肯定在group by 之前
    where后的条件表达式里不允许使用聚合函数,而having可以。

    6> 函数

    聚合函数

    avg平均数,同min(age)、max(age)、sum(age)

    select avg(age)  as age from student group by class order by age desc; 
    

    count

    select count(class)from student;
    /*数量 因为使用了92标准,所以null不计入count*/
    count(*) 跟count(1) 的结果一样,返回记录的总行数,都包括对NULL 的统计,
    count(column) 是不包括NULL 的统计。
    

    distinct

    select distinct(class)from student;/*去重复,出现所有不同的内容*/
    select count(distinct(class)) from student;
    

    其它

    LEFT(“123456789”,LEN(“数据库”))/*分两步运算,第一步是运算LEN函数,结果是3。第二步针对123456789这个字符从左边开始连续取三个数*/
    
    select top 100 * from student where no=11;/*显示前100行*/
    select isnull(name,'无') as name,age,class from student;/*isnull之后就无列名了 用as给列重命名*/
    select name,age,class,'the name is' + name as introduce from student;/*用加号形成一个自定义列*/
    

    7>SQL-92 规则

    是数据库的一个标准。以下代码 写在存储过程前面,表示遵从SQL-92 规则。
    SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<) 比较时取值为 FALSE。

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    

    SET ANSI_NULLS ON
    即使 column_name 中包含空值,使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
    即使 column_name 中包含非空值,使用 WHERE column_name < NULL 的 SELECT 语句仍会返回零行。

    SET QUOTED_IDENTIFIER ON
    为ON:标识符可以由双引号分隔,而文字必须由单引号分隔。
    为OFF:标识符不可加引号。

    8>多层查询 EXISTS

    如果内层查询语句查询到符合条件的记录,就返回一个真值(true),否则,将返回
    一个假值(false)。

    SELECT * FROM employee
    WHERE EXISTS
    (SELECT d_name FROM department WHERE d_id=1003);
    

    同理还有:NOT EXISTS。

    9>关联查询、联结(JOIN)表

    关系数据库设计中表的设计是把信息分解成多个表,一类数据一个表,各表通过某些共同的值相互关联。
    一般情况下我们不建议建立外键这种强关联的关联信息。

    可伸缩(scale)
    能够适应不断增加的工作量而不失败。关系数据库的可伸缩性远远优于非关系数据库。

    注意:

    1. 联结的表越多效率越低。
    2. SQL本身不限制联结表的数目,但DBMS有最大数目限制。
    3. 一般情况下,联结查询比子查询快,实际应用中应该尝试两种方法看哪种快。
    JSON类型说明备注
    JOIN如果表中有至少一个匹配,则返回行INNER已省略。外联结比内联结返回的行数多(还包括没有关联的行)
    LEFT JOIN即使右表中没有匹配,也从左表返回所有的行OUTER已省略
    RIGHT JOIN即使左表中没有匹配,也从右表返回所有的行OUTER已省略
    FULL JOIN只要其中一个表中存在匹配,就返回行OUTER已省略

    luo_persons表:

    id_plast_namefirst_nameaddresscity

    luo_orders表:

    id_oorder_noid_p

    要求输出:谁订购了产品,并且他们订购了什么产品?

    ①联表查询(等值联结,equijoin)

    SELECT
    	a.last_name, a.first_name, b.order_no
    FROM
    	luo_persons a,
    	luo_orders b 
    WHERE
    	a.id_p = b.id_p	
    

    ②join查询(内联结,inner join, 推荐)

    /*(推荐)等值联结明确指定联结类型可转换为inner join

    SELECT
    	last_name,
    	first_name,
    	order_no 
    FROM
    	luo_persons
    	INNER JOIN luo_orders ON luo_persons.id_p = luo_orders.id_p
    

    ③union查询(复合查询、并查询)

    UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

    注意:

    1. UNION 内部的 SELECT 语句必须拥有相同数量的列、表达式或聚集函数。列也必须拥有相似的数据类型(可以不完全相同,但是可以互相转换)。同时,每条 SELECT 语句中的列的顺序必须相同。
    2. 默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
    3. UNION能组合的最大语句数目限制需要查询具体的DBMS文档。
    	select id_p from luo_persons 
    	union 
    	SELECT id_p from luo_orders
    

    某些DBMS中还支持其它类型的UNION:

    1. EXCEPT(或MINUS):检索在第一个表中存在而在第二个表中不存在的行;
    2. INTERSECT:检索两个表中都存在的行。

    4)数据更新

    ①数据插入

    i> insert

    insert into tableName(no,name) values'1','kate');
    --按表中列的顺序,但如果表结构发生了变化那么对应 sql也要改。不推荐
    insert into product values('001','001','N','N');
    

    有自增长主键(id)的插入:
    i>可以把id的值设置为null或者0,这样mysql会自己做处理
    ii>手动指定需要插入的列,不插入这一个字段的数据!

    ii> insert select

    将select结果插入表中,一般用于可重复执行的sql。
    注:
    1.insert select语句中,如果select返回多行,那么会insert多行数据。

    INSERT INTO "public"."vendors"("vend_name", "vend_id") select 'vend_name1', 1 
    WHERE NOT EXISTS (select 1  FROM "public"."vendors" WHERE vend_id = 1);
    

    iii> select into

    1. SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。
    2. SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
    3. select into 可以从多个表中检索数据,但只能插入到一个表中。

    函数里面,把一个查询出来的值存入临时变量:

    SELECT LastName,FirstName
    INTO _lName,_fName  FROM Persons
    

    也可以存入临时表中:

    SELECT *
    INTO Persons_backup
    FROM Persons
    

    ②数据修改

    update tableName set name = 'Tom' where name='kate';
    update tableName set age = age + 1;
    

    5)数据删除

    删除表中几行:

    DELETE FROM Person WHERE LastName = 'Wilson' 
    

    删除表中所有行,保留表、不释放空间。所删除的每行记录都会进日志,可以回滚。

    DELETE FROM table_name
    

    删除表:删除内容和定义,释放空间

    drop table user;    
    DROP TABLE IF EXISTS "public"."role_relation"; 可重复执行sql
    

    删除表中所有数据,保留表、同时释放空间(速度比delete快,但是无法撤回,日志里面只记录页释放):

    truncate table book;
    

    truncate是DDL语句(Data Definition,数据定义语句),相当于用重新定义一个新表的方法把原表的内容直接丢弃了,所以执行起来很快。delete语句是DML语句(Data Manipulation,数据操作语句),把数据一条一条的删除,所以删除多行数据执行较慢。

    6)其他注意

    ①加中括号

    列名、表名、存储过程名、函数名等都可以按需要加中括号。防止某些关键字在应用中引起歧义。

    select [select] from 表名;
    

    7)数据库授权

    ①授权GRANT

        GRANT <权限>
        ON <对象类型>  <对象名>
        TO <用户>
        [WITH GRANT OPTION]  // 如果指定了WITH GRANT OPTION子句,则获得某种权限的用户还可以把这种权限再授予其他用户,允许用户传递权限,但是不允许循环授权。
    

    举例:

    例1:把查询Student表的权限授给用户U1
    GRANT SELECT
    ON TABLE Student
    TO U1;
    
    例2:把全部操作权限授予用户U2和U3
    GRANT ALL PRIVILEGES
    ON TABLE Student,Course
    TO U2,U3;
    
    例3:把查询权限授予所有用户
    GRANT SELECT
    ON TABLE SC
    TO PUBLIC;
    

    ③权限的收回 REVOKE

    REVOKE <权限>
    ON <对象类型>  <对象名>
    FROM <用户>
    

    举例:

    例6:收回所有用户对表sc的查询权限
    REVOKE SELECT
    ON TABLE SC
    FROM PUBLIC;
    

    ③对用户模式的授权

    由DBA(数据库管理员,Database Administrator,简称DBA)在创建用户时实现。

    CREATE USER <username>
    [WITH] [DBA|RESOURCE|CONNECT]
    

    只有系统的超级用户才有权创建一个新的数据库用户
    新创建的用户有三种权限:DB,|RESOURCE,CONNECT

    ④数据库角色创建及授权

    CREATE ROLE <角色名>
    

    给角色授权:

    GRANT <权限>
    ON <对象类型>  对象名
    TO <角色>
    

    将一个角色授予其他的角色或用户

    GRANT <角色1>
    TO <角色3>
    [WITH ADMIN OPTION]//如果指定了WITH ADMIN OPTION 子句,则获得某种权限的角色或用户还可以把这种权限再授予其他角色
    

    角色权限的收回

    REVOKE <权限>
    ON <对象类型>  <对象名>
    FROM <角色>
    

    ⑤DENY 拒绝账户访问

    在安全系统中创建一项,以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。

    DENY { ALL | statement [ ,...n ] }
    TO security_account [ ,...n ]
    

    和授权区别:
    不授权是没有权限,但是如果这个用户属于某个角色,这个角色有了权限,那么这个用户可以从角色继承这个权限。如果选择了deny,即使这个用户属于某个具有权限的角色,他也没有权限。

    8)数据类型

    ①uniqueidentifier

    可存储16字节的二进制值,其作用与全局唯一标记符(GUID)一样。GUID是唯一的二进制数:世界上的任何两台计算机都不会生成重复的GUID值。GUID主要用于在用于多个节点,多台计算机的网络中,分配必须具有唯一性的标识符。

    9)函数

    ①OBJECT_ID

    A. 返回指定对象的对象 ID

    USE master;
    GO
    SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder') AS 'Object ID';
    GO
    

    B. 验证对象是否存在

    USE AdventureWorks;
    GO
    IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion;
    GO
    

    N是显式的将非unicode字符转成unicode字符,它来自 SQL-92 标准中的 National(Unicode)数据类型,用于扩展和标准化,在这里可以不用,写作object_id(PerPersonData)。

    10)SQL中的借书经典案例

    ①问题描述

    本题用到下面三个关系表:
    CARD 借书卡。 CNO 卡号,NAME 姓名,CLASS 班级
    BOOKS 图书。 BNO 书号,BNAME 书名, AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW 借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期

    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。

    要求1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。

    CREATE TABLE BORROW(
        CNO int FOREIGN KEY REFERENCES CARD(CNO),
        BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
        RDATE datetime,
        PRIMARY KEY(CNO,BNO)) 
    

    要求2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。

    SELECT CNO,借图书册数=COUNT(*)
    FROM BORROW
    GROUP BY CNO
    HAVING COUNT(*)>5
    

    要求3. 查询借阅了"水浒"一书的读者,输出姓名及班级

    CARD 借书卡。 CNO 卡号,NAME 姓名,CLASS 班级
    BOOKS 图书。 BNO 书号,BNAME 书名, AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW 借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期

    SELECT * FROM CARD c
    WHERE EXISTS(
        SELECT * FROM BORROW a,BOOKS b 
        WHERE a.BNO=b.BNO
            AND b.BNAME=N'水浒'
            AND a.CNO=c.CNO) 
    

    要求4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。

    SELECT * FROM BORROW 
    WHERE RDATE<GETDATE() 
    

    要求5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。

    SELECT BNO,BNAME,AUTHOR FROM BOOKS
    WHERE BNAME LIKE N'%网络%' 
    

    N’string’ 表示string是个Unicode字符串

    要求6. 查询现有图书中价格最高的图书,输出书名及作者。

    SELECT BNO,BNAME,AUTHOR FROM BOOKS
    WHERE PRICE=(
        SELECT MAX(PRICE) FROM BOOKS) 
    

    要求7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。

    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
        AND NOT EXISTS(
            SELECT * FROM BORROW aa,BOOKS bb
            WHERE aa.BNO=bb.BNO
                AND bb.BNAME=N'计算方法习题集'
                AND aa.CNO=a.CNO)
    ORDER BY a.CNO DESC 
    

    要求8. 将"C01"班同学所借图书的还期都延长一周。

    UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
    FROM CARD a,BORROW b
    WHERE a.CNO=b.CNO
        AND a.CLASS=N'C01' 
    
    DATEADD(datepart,number,date)  
    date 参数是合法的日期表达式。number 是您希望添加的间隔数;对于未来的时间,此数是正数,对于过去的时间,此数是负数。
    

    要求9. 从BOOKS表中删除当前无人借阅的图书记录。

    DELETE FROM BOOKS a
    WHERE NOT EXISTS(
        SELECT * FROM BORROW
        WHERE BNO=a.BNO) 
    

    要求11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。

    CREATE TRIGGER TR_SAVE ON BORROW
    FOR INSERT,UPDATE
    AS
    IF @@ROWCOUNT>0
    INSERT BORROW_SAVE SELECT i.*
    FROM INSERTED i,BOOKS b
    WHERE i.BNO=b.BNO
        AND b.BNAME=N'数据库技术及应用' 
    

    要求13.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。

    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO
        AND b.BNAME IN(N'计算方法',N'组合数学')
    GROUP BY a.CNO
    HAVING COUNT(*)=2
    ORDER BY a.CNO DESC
    

    5,索引

    6,关系运算

    1)集合运算符

    并(∪)、差(-)、交(∩)、笛卡尔积(×)

    笛卡尔积(直积):表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
    例如,A={a,b}, B={0,1,2},则
    A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
    

    2)专门的关系运算符

    ①选择(限制、σ)

    在关系R中选择满足给定条件的诸元组。

    ②投影(π)

    关系R上的投影是从R中选择出若干属性列组成新的关系。
    这里写图片描述
    投影之后可既改变行,又改变元组的数量。

    ③连接(θ连接、⋈)

    从两个关系的笛卡尔积中选取属性间满足一定条件的元组。(连接由乘积(笛卡尔积)、选择、投影组成)
    分为等值连接(=)、自然连接(要求比较的分量是相同的属性组,并在结果中把重复的属性列去掉)。
    这里写图片描述

    ④除运算(➗)

    RS÷S的意义就是:“在R和S的联系RS中,找出与S中所有的元组有关系的R元组”。

    3)算术比较符

    4)逻辑运算符

    非与或

    7,数据库完整性

    1)实体完整性

    主键唯一且不为空。

    2)参照完整性

    不允许修改外码
    级连操作:当删除或修改被参照表时,同时删除或修改参照表中的不一致元祖。

    3)用户定义的完整性

    4)触发器(Trigger)

    是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义,任何用户对标的增删改操作均由服务器自动激活相应触发器,在DBMS核心层进行集中的完整性控制。

    8,存储过程(Stored Procedure)

    1)概念

    存储过程是一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

    2)优点

    ①执行效率高

    存储过程因为SQL 语句已经预编译过了,因此运行的速度比较快。

    ②降低了客户机和服务器之间的通信

    存储过程在服务器端运行,减少客户端的压力。
    减少网络流量,客户端调用存储过程只需要传存储过程名和相关参数即可,与传输SQL 语句相比自然数据量少了很多。

    ③方便实施企业规则(提高了可维护性、安全性)

    可以把企业规则的运算程序写成存储过程放入数据库服务器中,由RDBMS管理,既有利于集中控制,又能够方便地进行维护。
    当用户规则发生变化时,只要修改存储过程,无须修改其他应用程序。

    允许模块化程序设计,就是说只需要创建一次过程,以后在程序中就可以调用该过程任意次,类似方法的复用。
    增强了使用的安全性,充分利用系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。程序员直接调用存储过程,根本不知道表结构是什么,有什么字段,没有直接暴露表名以及字段名给程序员。

    ④安全性高

    可设定只有某些用户才具有对指定存储过程的使用权。

    3)缺点

    调试麻烦(至少没有像开发程序那样容易),可移植性不灵活(因为存储过程是依赖于具体的数据库)。

    4)场景

    当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程;
    当在一个事务的完成需要很复杂的商业逻辑时(比如,对多个数据的操作,对多个状态的判断更改等)要考虑;还有就是比较复杂的统计和汇总也要考虑,但是过多的使用存储过程会降低系统的移植性。

    sql尽量放在存储过程中。
    面对大量数据,用orcle比sql server稳定。

    5)代码

    ①创建

    use test1
    set ansi_nulls on
    go
    set quoted_identifier on
    go
    create procedure procedure_student
    	-- add the parameters for the stored procedure here
    	@gradeid int,
    	@gradename varchar(10) --传入的参数
    as
    begin
    	--计算内容
    end
    go
    

    ②执行

    exec dbo.procedure_student 1,'g'
    

    9,数据库恢复技术

    1)事务

    10,并发控制

    为了保证事务的隔离性和一致性,DBMS需要对并发操作进行正确调度。

    1)并发操作带来的数据不一致性

    ①更新丢失

    ②读“脏”数据

    事务T1修改数据,T2读取数据,T1由于某种原因被撤销,则数据修改回原值,但T2读取的数据是之前修改的数据,即脏数据、不正确的数据。

    ③不可重复读

    事务T1读数据后,T2修改了数据,T1无法再现上一次读取的结果。

    ④幻读

    事务T1读数据后,T2新增或者删除了数据,T1无法再现上一次读取的结果。

    2)并发控制技术

    悲观锁:封锁
    乐观锁:版本号、时间戳

    3)封锁分类(悲观锁)

    ①共享锁(S锁、读锁)

    (读取)操作创建的锁。其他用户可以并发读取数据,但任何事物都不能获取数据上的排它锁,直到已释放所有共享锁。
    若事务T对数据对象A加上S锁,则事务T只能读A;其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

    ②排它锁(X锁、写锁,eXclusive lock)

    若事物T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T释放A上的锁。它防止任何其它事务获取资源上的锁,直到在事务的末尾将资源上的原始锁释放为止。

    ③更新锁(U锁)

    用来预定要对此页施加X锁,它允许其他事务读,但不允许再施加U锁或X锁;当被读取的页将要被更新时,则升级为X锁;U锁一直到事务结束时才能被释放。

    4)封锁问题

    ①活锁

    i>饥饿

    考虑一台打印机分配的例子,当有多个进程需要打印文件时,系统按照短文件优先的策略排序,该策略具有平均等待时间短的优点,似乎非常合理,但当短文件打印任务源源不断时,长文件的打印任务将被无限期地推迟,导致饥饿以至饿死。

    ii>活锁概念

    与饥饿相关的另外一个概念称为活锁,在忙式等待条件下发生的饥饿,称为活锁。

    a)忙式等待:不进入等待状态的等待。
    b)阻塞式等待:进程得不到共享资源时将进入阻塞状态,让出CPU 给其他进程使用。
    c)忙等待和阻塞式等待的相同之处:
    在于进程都不具备继续向前推进的条件,不同之处在于处于忙等待的进程不主动放弃CPU,尽管CPU 可能被剥夺,因而是低效的;而处于阻塞状态的进程主动放弃CPU ,因而是高效的。

    iii>举例

    事务T1请求封锁R,T2请求封锁R,T3请求封锁R……
    T1释放R之后,系统批准了T3的请求,然后是T4……请求,T2可能永远等待下去。(在整个过程中,事务T2 在不断的重复尝试获取锁R)。

    iv>与死锁区别

    活锁的时候,进程是不会阻塞的,这会导致耗尽CPU 资源,这是与死锁最明显的区别。
    处于活锁的实体是在不断的改变状态,所谓的“活”, 而处于死锁的实体表现为等待;活锁有一定几率解开,而死锁是无法解开的。

    v>避免方式

    采用先来先服务策略。

    ②死锁

    i>概念

    是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去,此时称系统处于死锁状态或系统产生了死锁。

    ii>举例

    T1请求封锁R1,T2请求封锁R2,然后T1又请求封锁R2,T1一直等待T2释放R2,此时,T2请求封锁R1,T2将一直等待T1释放R1。

    iii>死锁原因

    在数据库中,产生死锁的原因主要是:
    两个或多个事务都已封锁了一些数据对象,然后又都请求其他事务已封锁的数据对象,从而出现死等待。

    产生死锁的四个必要条件:
    (1) 互斥条件:一个资源每次只能被一个进程使用。
    (2) 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
    (3) 不可剥夺条件: 进程已获得的资源,在末使用完之前,不能强行剥夺。
    (4) 环路等待条件: 若干进程之间形成一种头尾相接的循环等待资源关系。
    只要系统发生了死锁,这些条件必然成立,而只要上述条件之一不满足,就不会发生死
    锁。

    iv>死锁预防

    预防死锁的发生只需破坏死锁产生的四个必要条件之一即可。

    1. 破坏互斥条件
      如果允许系统资源都能共享使用,则系统不会进入死锁状态。但有些资源根本不能同时访问,如打印机等临界资源只能互斥使用。所以,破坏互斥条件而预防死锁的方法不太可行,而且在有的场合应该保护这种互斥性。
    2. 破坏不剥夺条件
      当一个已保持了某些不可剥夺资源的进程,请求新的资源而得不到满足时,它必须释放已经保持的所有资源,待以后需要时再重新申请。这意味着,一个进程已占有的资源会被暂时释放,或者说是被剥夺了,或从而破坏了不可剥夺条件。
      该策略实现起来比较复杂,释放已获得的资源可能造成前一阶段工作的失效,反复地申请和释放资源会增加系统开销,降低系统吞吐量。这种方法常用于状态易于保存和恢复的资源,如CPU 的寄存器及内存资源,一般不能用于打印机之类的资源。
    3. 破坏请求和保持条件
      釆用预先静态分配方法,即进程在运行前一次申请完它所需要的全部资源,在它的资源未满足前,不把它投入运行。一旦投入运行后,这些资源就一直归它所有,也不再提出其他资源请求,这样就可以保证系统不会发生死锁。
      这种方式实现简单,但缺点也显而易见,系统资源被严重浪费,其中有些资源可能仅在运行初期或运行快结束时才使用,甚至根本不使用。而且还会导致“饥饿”现象,当由于个别资源长期被其他进程占用时,将致使等待该资源的进程迟迟不能开始运行。
    4. 破坏环路等待条件
      为了破坏循环等待条件,可釆用顺序资源分配法。首先给系统中的资源编号,规定每个进程,必须按编号递增的顺序请求资源,同类资源一次申请完。也就是说,只要进程提出申请分配资源Ri,则该进程在以后的资源申请中,只能申请编号大于Ri 的资源。
      这种方法存在的问题是,编号必须相对稳定,这就限制了新类型设备的增加;尽管在为资源编号时已考虑到大多数作业实际使用这些资源的顺序,但也经常会发生作业使甩资源的顺序与系统规定顺序不同的情况,造成资源的浪费;此外,这种按规定次序申请资源的方法,也必然会给用户的编程带来麻烦。

    都不好用,一般采用死锁的诊断和解除。

    v>死锁的诊断和解除

    a)超时法
    如果一个事务等待时间超时,则认为发生死锁。(可能误判)
    b)事务等待图法
    事务等待图是一个有向图,反映了事务的等待情况。如果图中出现回路,就表示出现了死锁。

    处理方案是:选择一个处理代价最小的事务,将其撤销并释放所有锁。
    a) 从死锁进程处剥夺资源
    b) 终止部分或全部进程

    5) 两段锁协议(Two-Phase Locking――2PL)

    两段锁协议规定所有的事务应遵守的规则:
    ① 在对任何数据进行读、写操作之前,首先要申请并获得对该数据的封锁。
    ② 在释放一个封锁之后,事务不再申请和获得其它任何封锁。
    即事务的执行分为两个阶段:
    第一阶段是获得封锁的阶段,称为扩展阶段。
    第二阶段是释放封锁的阶段,称为收缩阶段。

    定理:若所有事务均遵守两段锁协议,则这些事务的所有交叉调度都是可串行化的。
    对于遵守两段协议的事务,其交叉并发操作的执行结果一定是正确的。值得注意的是,上述定理是充分条件,不是必要条件。一个可串行化的并发调度的所有事务并不一定都符合两段锁协议,存在不全是2PL的事务的可串行化的并发调度。
    同时我们必须指出,遵循两段锁协议的事务有可能发生死锁。

    此时事务T1 、T2同时处于扩展阶段,两个事务都坚持请求加锁对方已经占有的数据,导致死锁。
    为此,又有了一次封锁法。一次封锁法要求事务必须一次性将所有要使用的数据全部加锁,否则就不能继续执行。因此,一次封锁法遵守两段锁协议,但两段锁并不要求事务必须一次性将所有要使用的数据全部加锁,这一点与一次性封锁不同,这就是遵守两段锁协议仍可能发生死锁的原因所在。

    11,常见图

    DFD 数据流图(Data Flow Diagram):
    这里写图片描述
    ER图 实体-联系图(Entity-Relationship Diagram)
    这里写图片描述

    12,数据库连接:JDBC与JdbcTemplate

    13,数据库安全

    1)SQL注入

    ①概念

    在SQL 语句在拼接的情况下,用户输入为一部分sql语句。

    ②解决方法

    i> 对特殊字符进行过滤、转义或者使用预编译的sql 语句绑定变量

    SQL执行时,2种方式:
    ①字符串处理(拼接),然后执行SQL
    用户输入的时候,可以通过输入sql语句来进行SQL注入。
    ②传参,执行SQL -->交给SQL引擎**(推荐)**
    用prepareStatement,参数用set 方法进行填装。

    String sql= "insert into userlogin values(?,?)";
    PreparedStatement ps=conn.prepareStatement(sql);
    for(int i=1;i<100;i++){
    ps.setInt(1, i);
    ps.setInt(2, 8888);
    ps.executeUpdate();
    ps.close();
    conn.close();
    

    ii> 当sql 语句运行出错时,不要把数据库返回的错误信息全部显示给用户,以防止泄漏服务器和数据库相关信息

    iii>检查变量的数据类型和格式

    只要是有固定格式的变量,在SQL 语句执行前,应该严格按照固定格式去检查,确保变量是我们预想的格式,这样很大程度上可以避免SQL 注入攻击。
    例如:对于where id={$id}这种形式,数据库里所有的id 都是数字,那么就应该在SQL 被执行前,检查确保变量id 是int 类型。

    iv>所有的SQL 语句都封装在存储过程中

    所有的SQL 语句都封装在存储过程中,这样不但可以避免SQL 注入,还能提高一些性能。

    14,分布式数据库

    1)概念

    分布式数据库是一个物理上分散的而逻辑上集中的数据集。
    它有三大特点: 数据分布性 逻辑关联性 站点自治性

    2)五个基本原则

    ①资源的重复性
    指分布式系统中硬件,软件以及数据的冗余配置。
    ②物理上的分布性
    从硬件,软件以及数据上看都是相互独立地分布。
    ③高层操作系统(或者分布式操作系统)
    高层操作系统负责对分布性的资源进行统一的控制,它使一个简单的硬件堆积转变为一个统一协调的工作系统。
    ④系统的透明性
    透明性是分布式系统的灵魂,实现不同层次的透明性是分布式系统必须解决的关键问题之一。
    ⑤协作的自治性
    每一节点都是一个完整的处理系统,同时又是合作的。 简而言之:分布式系统是一个多节点的,处理或数据分布的,在统一下提高综合处理能力的协作体。

    3)待解决问题

    不完整系统状态信息
    时间延迟
    通信的代价
    负载均衡

    4)分类(从控制方式角度)

    ①紧耦合式DDBMS

    全局控制信息放在一个称为中心站点的站点上。所有的全局访问都必须通过中心站点来确定远程数据片的位置。
    优点:容易实现数据的一致性和完整性。
    缺点:易产生访问瓶颈,系统效率不高,可靠性较差。

    ②联邦式DDBMS

    每个站点都包含全局控制信息的一个副本,都可以接受全局访问。任何对远程数据的请求,都可以通过广播方式传播到其他节点。
    优点:具有较好的可靠性和可用性,并行性好,更容易适应旧有的系统集成和异构分布式数据库系统的建立。
    缺点:保持数据的一致性很困难,实现难度大。

    ③组合式DDBMS

    是上述方案的折衷,它把站点分为两类,一类具有全局控制信息,称为主节点,可以接受全局任务,另一类没有全局信息,只能为主节点提供数据服务。
    优点:灵活性较好,易于实现层次控制结构。
    缺点:设计复杂。

    5)分布透明性

    即在分布式数据库系统中用户不必关心数据的分布情况。分为三个层次:

    ①分片透明性

    它是分布式数据库系统的最高透明性层次,它向用户完全屏蔽了DDB的分片信息。这样的透明性保持了高水平的数据独立性。

    ②位置透明性

    用户的应用程序不需要关心数据分片的具体存储站点,当数据库的数据片的存储站点发生改变时,只需改变对应的GRS/NRS映射就可以保持全局表示模式不发生改变

    ③数据模型透明性

    它向用户屏蔽的只是本站点的具体数据库存储及其管理情况。 在异构的情况下,这种透明性避免了用户对不同数据模型的转换的实现。
    本地透明性是3种透明方式中最低的。

    6)数据分割方法

    ①水平分割

    把全局关系的元组分割成一些子集,这些子集被称为数据分片或段(Fragment)。
    水平分割可以通过关系运算“选择”来定义。

    水平分片是对全局关系执行“选择”操作,把具有相同性质的元组进行分组,构成若干个不相交的子集.水平分片的方法可归为初级分片和导出分片两类。

    ②垂直分割

    把全局关系按照属性组(纵向)分隔成一些数据分片或段。
    垂直分割可以通过关系运算“投影”来定义。

    ③混合分割

    可把水平分割和垂直分割这两种方法结合起来使用,产生混合式数据分片。

    ④数据分片应遵循的原则

    若R={R1,R2,…,Rn}满足:
    1)完整性(completeness)条件:
    如果分片 a∈R,则必有a∈Ri,i=l,2,…,n
    2)可重构(reconstructed)条件:
    R=∪ Ri,(水平分片)或R=∞Ri,(垂直分片)
    3)不相交(disjoint)条件:
    Ri∩ Rj=φ,i≠j,I,j:=1,2,…,,n(水平 分片)
    Ri∩Rj=主键属性,I,j=1,2,…,n(垂直分片)

    7)分布式数据库和集中式区别

    分布式(distributed)是指在多台不同的服务器中部署不同的服务模块,通过远程调用协同工作,对外提供服务。
    集群(cluster)是指在多台不同的服务器中部署相同应用或服务模块,构成一个集群,通过负载均衡设备对外提供服务。

    15,数据库优化

    1)优化SQL 语句

    ①explain

    通过explain(查询优化神器)用来查看SQL 语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。
    通常我们可以对比较复杂的尤其是涉及到多表的SELECT 语句,把关键字EXPLAIN 加到前面,查看执行计划。例如:explain select * from news;

    explain语法:

    explain select … from … [where ...] 
    

    ② 用具体的字段列表代替“*

    任何地方都不要使用select * from t ,不要返回用不到的任何字段。

    ③ 不在索引列做运算或者使用函数

    ④ 查询尽可能使用limit 减少返回的行数,减少数据传输时间和带宽浪费。

    2)优化表的数据类型

    ① 使用procedure analyse()函数对表进行分析

    该函数可以对表中列的数据类型提出优化建议。能小就用小。表数据类型第一个原则是:使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu 缓存的使用。
    使用方法:select * from 表名procedure analyse();

    ② 对表进行拆分

    通过拆分表可以提高表的访问效率。有2 种拆分方法:
    1.垂直拆分
    把主键和一些列放在一个表中,然后把主键和另外的列放在另一个表中。如果一个表中某些列常用,而另外一些不常用,则可以采用垂直拆分。
    2.水平拆分
    根据一列或者多列数据的值把数据行放到二个独立的表中。

    ③ 使用中间表来提高查询速度

    创建中间表,表结构和源表结构完全相同,转移要统计的数据到中间表,然后在中间表上进行统计,得出想要的结果。

    3)硬件优化

    ①CPU 的优化

    选择多核和主频高的CPU。

    ②内存的优化

    使用更大的内存。将尽量多的内存分配给MYSQL 做缓存。

    ③磁盘I/O 的优化

    i>使用磁盘阵列

    RAID 0 没有数据冗余,没有数据校验的磁盘陈列。实现RAID 0至少需要两块以上的硬盘,它将两块以上的硬盘合并成一块,数据连续地分割在每块盘上。
    RAID1 是将一个两块硬盘所构成RAID 磁盘阵列,其容量仅等于一块硬盘的容量,因为另一块只是当作数据“镜像”。
    使用RAID-0+1 磁盘阵列。RAID 0+1 是RAID 0 和RAID 1 的组合形式。它在提供与RAID 1 一样的数据安全保障的同时,也提供了与RAID 0 近似的存储性能。

    ii>调整磁盘调度算法

    选择合适的磁盘调度算法,可以减少磁盘的寻道时间。

    4)MySQL 自身的优化

    对MySQL 自身的优化主要是对其配置文件my.cnf 中的各项参数进行优化调整。如指定MySQL 查询缓冲区的大小,指定MySQL 允许的最大连接进程数等。

    5)应用优化

    ①使用数据库连接池

    ②使用查询缓存

    它的作用是存储select 查询的文本及其相应结果。如果随后收到一个相同的查询,服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表,当表中数据更改后,查询缓存中的相关条目就会被清空。

    6)大访问量的优化

    ①使用优化查询的方法

    (见上面)

    ②主从复制,读写分离

    i>主从复制(master,slave):

    通过配置两台(或多台)数据库的主从关系,可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能,实现数据库的读写分离,从而改善数据库的负载压力。一个系统的读操作远远多于写操作,因此写操作发向master,读操作发向slaves 进行操作(简单的轮循算法来决定使用哪个slave)。
    利用数据库的读写分离,Web 服务器在写数据的时候,访问主数据库(Master),主数据库通过主从复制机制将数据更新同步到从数据库(Slave),这样当Web 服务器读数据的时候,就可以通过从数据库获得数据。这一方案使得在大量读操作的Web 应用可以轻松地读取数据,而主数据库也只会承受少量的写入操作,还可以实现数据热备份,可谓是一举两得的方案。
    这里写图片描述

    负载均衡(Load Balance,简称LB)

    7)数据库分表、分区、分库

    分表见上面描述。
    分区就是把一张表的数据分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O 读写性能,实现比较简单。包括水平分区和垂直分区。
    分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog 等库。

    17,应用

    1)服务器与服务器之间传输文件夹下的文件,一个文件夹下有10 个文件,另一个文件夹下有100 个文件,两个文件夹大小相等,问,哪个传输更快?

    10 个文件更快。
    1)建立连接数更少,建立连接的开销比传输文件的开销大。
    2)文件写入磁盘,要计算文件的起始位置,文件数目少的话,这个开销就小了

    展开全文
  • 一、sql 数据库CPU瓶颈   对于SQL Server的一个工作进程的状态有很多,主要状态有运行中(RUNNING)、可运行(RUNNABLE)和挂起(SUSPENED)3种。 通过查看系统监视计数器Processor:% Processor Time,可以确定CPU...

    一、sql 数据库CPU瓶颈    

          对于SQL Server的一个工作进程的状态有很多,主要状态有运行中(RUNNING)、可运行(RUNNABLE)和挂起(SUSPENED)3种。

    通过查看系统监视计数器Processor:% Processor Time,可以确定CPU瓶颈。如果这个计数器的值很高。比如持续15-20分钟超80%,就意味着CPU出现了瓶颈。


    当您怀疑计算机硬件是影响SQL Server运行性能的主要原因时,可以通过SQL Server Performance Monitor监视相应硬件的负载,以证实您的猜测并找出系统瓶颈。下文将介绍一些常用的分析对象及其参数。

      Memory: Page Faults / sec

      如果该值偶尔走高,表明当时有线程竞争内存。如果持续很高,则内存可能是瓶颈。

      Process: Working Set

      SQL Server的该参数应该非常接近分配给SQL Server的内存值。在SQL Server设定中,如果将"set working set size"置为0, 则Windows NT会决定SQL Server的工作集的大小。如果将"set working set size"置为1,则强制工作集大小为SQLServer的分配内存大小。一般情况下,最好不要改变"set working set size"的缺省值。

      Process:%Processor Time

      如果该参数值持续超过95%,表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

      Processor:%Privileged Time

      如果该参数值和"Physical Disk"参数值一直很高,表明I/O有问题。可考虑更换更快的硬盘系统。另外设置Tempdb in RAM,减低"max async IO","max lazy writer IO"等措施都会降低该值。

      Processor:%User Time

      表示耗费CPU的数据库操作,如排序,执行aggregate functions等。如果该值很高,可考虑增加索引,尽量使用简单的表联接,水平分割大表格等方法来降低该值。

      Physical Disk:Avg.Disk Queue Length

      该值应不超过磁盘数的1.5~2倍。要提高性能,可增加磁盘。

      注意:一个Raid Disk实际有多个磁盘。

      SQLServer:Cache Hit Ratio

      该值越高越好。如果持续低于80%,应考虑增加内存。 注意该参数值是从SQL Server启动后,就一直累加记数,所以运行经过一段时间后,该值将不能反映系统当前值。



    检测CPU压力的另一个方法是计算可运行状态下的工作进程数量,通过执行如下的DMV查询可以得到这个信息:

    SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id

    FROM  sys.dm_os_workers  AS t1, sys.dm_os_schedulers  AS t2

    WHERE t1.state = 'RUNNABLE' AND t1.scheduler_address=t2.scheduler_address

    AND t2.scheduler_id < 255

    GROUP BY t2.scheduler_id

    也可以执行如下的查询得到工作进程在可运行状态下花费的时间:

    SELECT SUM(signal_wait_time_ms) FROM sys.dm_os_wait_stats

    下面查询是找出每次执行占用CPU最多的前100位查询:

    SELECT TOP 100  total_worker_time/execution_count  AS avg_cpu_cost, plan_handle, execution_count,

    (SELECT SUBSTRING(text, statement_start_offset/2+1,

    (CASE WHEN statement_end_offset = -1  THEN LEN(CONVERT(nvarchar(max), text)) * 2

    ELSE statement_end_offset END - statement_end_offset)/2)

    FROM  sys.dm_exec_sql_text(sql_handle))  AS query_text

    FROM sys.dm_exec_query_stats

    ORDER BY avg_cpu_cost  DESC

    稍做修改,找出运行最频繁的查询:

    SELECT TOP 100  total_worker_time/execution_countASavg_cpu_cost, plan_handle, execution_count,

    (SELECT  SUBSTRING(text,statement_start_offset/2+1,

    (CASE  WHEN  statement_end_offset = -1  THEN LEN(CONVERT(nvarchar(max), text)) * 2

    ELSE  statement_end_offset  END - statement_end_offset)/2)

    FROM  sys.dm_exec_sql_text(sql_handle))  AS  query_text

    FROM  sys.dm_exec_query_stats

    ORDER BY execution_count  DESC

    可以使用下列系统监视性能计数器查看编译和重编译的速度:

    1.     SQLServer: SQL Statistics: BatchRequests/Sec(每秒批处理请求数)

    2.     SQLServer: SQL Statistics: SQLCompilations/Sec(每秒SQL编译次数)

    3.     SQLServer: SQL Statistics: SQLRecompilations/Sec(每秒SQL重编译次数)

    还可以通过下面语句得到SQLServer在优化查询计划上花费的时间:

    SELECT  *  FROM  sys.dm_exec_query_optimizer_info

    WHERE counter='optimizations' OR  counter = 'elapsed time'

    下面查询找到被编译得最多的前10位查询计划:

    SELECT  TOP 10  plan_generation_num, execution_count,

    (SELECT SUBSTRING(text, statement_start_offset/2+1,

    (CASE  WHEN  statement_end_offset = -1  THEN LEN(CONVERT(nvarchar(max), text)) * 2

    ELSE  statement_end_offsetEND-statement_end_offset)/2)

    FROM  sys.dm_exec_sql_text(sql_handle))ASquery_text

    FROM  sys.dm_exec_query_stats

    WHERE  plan_generation_num> 1

    ORDER  BY  plan_generation_num  DESC

     

    二、sql 数据库内存瓶颈

    内存有压力时,一个查询计划可能得移出内存。如果这个计划被再次提交执行,就必须再优化一次,而由于查询优化是CPU密集型运算,这就会给CPU带来压力。同样,内存有压力时,数据库页面可能需要被移出缓冲区池。如果这些页面很快就再次被选中,就会导致更多的物理IO。

    通常所说的内存指的是服务器上的可用物理内存(既RAM)。还有另外一种内存叫做虚拟地址空间(VAS)或虚拟内存。在Windows系统上,所有位应用程序都有一个GB的进程地址空间,用来获取最大GB的物理内存。在GB的可用内存之外,进程还可以在用户模式下得到GB的VAS,另外GB保留只能通过内核模式获取。要想更改这个配置,可以在boot.ini文件中使用/3GB switch。

    常见的操作系统机制是页面调试,它使用一个交换文件来存储最近未使用的部分进程内存。当这一内存被再次引用时,它就直接从交换文件中读取(或调入)物理内存。

     

    可以通过性能计数器,监测下面参数:

    1. 内存:可用字节(Available Bytes)

    2. SQL Server:缓冲管理器:缓存命中率(Buffer Cache Hit Ratio)指的是那些不用通过磁盘读取而直接在缓冲区池中找到的页的比例。对于大多数产品工作负荷而言,这个值应该是多。(应该是越大越好)

    3. SQL Server:缓冲管理器:页平均寿命(Page Life Expectancy)指的是一个没有被引用的页在缓冲区池中保留的秒数。如果数值较低,则说明缓冲区池遇到了内存不足的情况。

    4. SQL Server:缓冲管理器:检查点页/秒(Checkpoint Pages/Sec)指的是被检查点刷新的页数,或者要求所有脏页被刷新的其它操作的数目。它能显示工作负荷中增加的缓冲区池活动量。

    5. SQL Server:缓冲管理器:延迟写入/秒(Lazywrites/Sec)指的是缓冲管理器的延迟写入器写入的缓冲数目,它的作用类似于前面提到的检查点页/秒。



    怀疑内存不足时:

    方法1:

    【监控指标】:Memory Available MBytes ,Memory的Pages/sec, page read/sec, Page Faults/sec

    【参考值】:

    如果 Page Reads/Sec 比率持续保持为 5,表示可能内存不足。

    Page/sec 推荐00-20(如果服务器没有足够的内存处理其工作负荷,此数值将一直很高。如果大于80,表示有问题)。

    方法2:根据Physical Disk 值分析性能瓶颈

    【监控指标】:Memory Available MBytes ,Pages read/sec,%Disk Time 和 Avg.Disk Queue Length

    【参考值】:%Disk Time建议阈值90%

    当内存不足时,有点进程会转移到硬盘上去运行,造成性能急剧下降,而且一个缺少内存的系统常常表现出很高的CPU利用率,因为它需要不断的扫描内存,将内存中的页面移到硬盘上。

    怀疑内存泄漏时

    【监控指标】:Memory Available MBytes ,Process\Private Bytes和Process\Working Set,PhysicalDisk/%Disk Time

    【说明】:

    Windows资源监控中,如果Process\Private Bytes计数器和Process\Working Set计数器的值在长时间内持续升高,同时Memory\Available bytes计数器的值持续降低,则很可能存在内存泄漏。内存泄漏应该通过一个长时间的,用来研究分析当所有内存都耗尽时,应用程序反应情况的测试来检验。

    CPU分析

    【监控指标】:

    System %Processor Time CPU,Processor %Processor Time CPU

    Processor%user time 和Processor%Privileged Time

    system\Processor Queue Length

    Context Switches/sec 和%Privileged Time

    【参考值】:

    System\%Total processor time不持续超过90%,如果服务器专用于SQL Server,可接受的最大上限是80-85% ,合理使用的范围在60%至70%。

    Processor %Processor Time小于75%

    system\Processor Queue Length值,小于CPU数量的总数+1

    CPU瓶颈问题

    1、System\%Total processor time如果该值持续超过90%,且伴随处理器阻塞,则说明整个系统面临着处理器方面的瓶颈.

    注:在某些多CPU系统中,该数据虽然本身并不大,但CPU之间的负载状况极不均衡,此时也应该视作系统产生了处理器方面的瓶颈.

    2、排除内存因素,如果Processor %Processor Time计数器的值比较大,而同时网卡和硬盘的值比较低,那么可以确定CPU 瓶颈。(内存不足时,有点进程会转移到硬盘上去运行,造成性能急剧下降,而且一个缺少内存的系统常常表现出很高的CPU利用率,因为它需要不断的扫描内存,将内存中的页面移到硬盘上。)

    造成高CPU使用率的原因:

    频繁执行程序,复杂运算操作,消耗CPU严重

    数据库查询语句复杂,大量的 where 子句,order by, group by 排序等,CPU容易出现瓶颈

    内存不足,IO磁盘问题使得CPU的开销增加

    磁盘I/O分析

    【监控指标】:PhysicalDisk/%Disk time,PhysicalDisk/%Idle Time,Physical Disk\ Avg.Disk Queue Length, Disk sec/Transfer

    【参考值】:%Disk Time建议阈值90%

    Windows资源监控中,如果% Disk Time和Avg.Disk Queue Length的值很高,而Page Reads/sec页面读取操作速率很低,则可能存在磁盘瓶径。

    Processor%Privileged Time该参数值一直很高,且如果在 Physical Disk 计数器中,只有%Disk time 比较大,其他值都比较适中,硬盘可能会是瓶颈。若几个值都比较大, 那么硬盘不是瓶颈。若数值持续超过80%,则可能是内存泄露。如果 Physical Disk 计数器的值很高时该计数器的值(Processor%Privileged Time)也一直很高, 则考虑使用速度更快或效率更高的磁盘子系统。

    Disk sec/Transfer 一般来说,该数值小于15ms为最好,介于15-30ms之间为良好,30-60ms之间为可以接受,超过60ms则需要考虑更换硬盘或是硬盘的RAID方式了.

    Average Transaciton Response Time(事务平均响应时间)随着测试时间的变化,系统处理事务的速度开始逐渐变慢,这说明应用系统随着投产时间的变化,整体性能将会有下降的趋势

    Transactions per Second(每秒通过事务数/TPS)当压力加大时,点击率/TPS曲线如果变化缓慢或者有平坦的趋势,很有可能是服务器开始出现瓶颈

    Hits per Second(每秒点击次数)通过对查看“每秒点击次数”,可以判断系统是否稳定。系统点击率下降通常表明服务器的响应速度在变慢,需进一步分析,发现系统瓶颈所在。

    Throughput(吞吐率)可以依据服务器的吞吐量来评估虚拟用户产生的负载量,以及看出服务器在流量方面的处理能力以及是否存在瓶颈。

    Connections(连接数)当连接数到达稳定状态而事务响应时间迅速增大时,添加连接可以使性能得到极大提高(事务响应时间将降低)

    Time to First Buffer Breakdown(Over Time)(第一次缓冲时间细分(随时间变化))可以使用该图确定场景或会话步骤运行期间服务器或网络出现问题的时间。

    碰到过的性能问题:

    • 1. 在高并发的情况下,产生的处理失败(比如:数据库连接池过低,服务器连接数超过上限,数据库锁控制考虑不足等)
    • 2. 内存泄露(比如:在长时间运行下,内存没有正常释放,发生宕机等)
    • 3. CPU使用偏离(比如:高并发导致CPU使用率过高)
    • 4. 日志打印过多,服务器无硬盘空间

    如何定位这些性能问题:

    1. 查看系统日志,日志是定位问题的不二法宝,如果日志记录的全面,很容易通过日志发现问题。

    比如,系统宕机时,系统日志打印了某方法执行时抛出out of memory的错误,我们就可以顺藤摸瓜,很快定位到导致内存溢出的问题在哪里。

    2. 利用性能监控工具,比如:JAVA开发B/S结构的项目,可以通过JDK自带的Jconsole,或者JProfiler,来监控服务器性能,Jconsole可以远程监控服务器的CPU,内存,线程等状态,并绘制变化曲线图。

    利用Spotlight可以监控数据库使用情况。

    我们需要关注的性能点有:CPU负载,内存使用率,网络I/O等

    3. 工具和日志只是手段,除此之外,还需要设计合理的性能测试场景

    具体场景有:性能测试,负载测试,压力测试,稳定性测试,浪涌测试等

    好的测试场景,能更加快速的发现瓶颈,定位瓶颈

    4. 了解系统参数配置,可以进行后期的性能调优

    除此以外,还想说个题外话,就是关于性能测试工具的使用问题

    在刚开始用Loadrunner和JMeter的时候,做高并发测试时,都出现过没有把服务器压垮,这两个程序自己先倒下的情况。

    如果遇到这个问题,可以通过远程调用多个客户端的服务,分散性能测试工具客户端的压力来解决。

    说这个的目的是想说,做性能测试的时候,我们一定要确保瓶颈不要发生在我们自己的测试脚本和测试工具上。
    http://blog.csdn.net/leamonjxl/article/details/7090514
    展开全文
  • 数据库高性能写入

    千次阅读 2013-06-27 22:02:13
    在开发过程中,我们不时会遇到系统性能瓶颈问题,而引起这一问题原因可以很多,有可能是代码不够高效、有可能是硬件或网络问题,也有可能是数据库设计的问题。 本篇博文将针对一些常用的数据库性能调休方法进行...
  • 数据库种类及关系型数据库原理

    千次阅读 2017-09-14 08:02:25
    一、数据库种类及关系型数据库原理1.1数据库种类:按照早期的数据库理论,比较流行的数据库模型有三种: 层次式数据库 网络式数据库 关系型数据库 在当今互联网中,最常用的数据库模型: 关系型数据库 非关系型...
  • 【IT168 技术】在过往与很多人的交流过程中发现,在谈到基于硬件来进行数据库性能瓶颈分析的时候,常被大家误解为简单的使用更为强劲的主机或者存储来替换现有的设备。  个人觉得这其中可能存在一个非常大的误区。...
  • 用 Memcache 可以缓解 php和数据库压力下面代码是解决高负载下数据库写入瓶颈问题,遇到最实用的:写入ip pv uv的时候,用户达到每分钟几万访问量,要记录这些数据,实时写入数据库必定奔溃. 用以下技术就能解决,还有如...
  •  最近听说了很多关于NoSQL的新闻,比如之前Sourceforge改用MongoDB,...再加上之前做数据库比较时有人推荐我mongodb,所以也搜索了一下NoSQL,觉得NoSQL可能真的是未来的趋势。  NoSQL vs SQL  传统SQL数据库
  • 一直没时间写博客了,一直在专...1、数据库端的压力瓶颈,以前再华为hwa项目组搞hadoop大数据时我就清晰的知道,项目都会因为时间的积累导致堆积的数据越来越多,而数据库没有进行处理会导致数据一直物理存储着,会导致
  • 提升HBase数据库写入性能

    千次阅读 2014-05-08 10:29:22
    其次,实际的应用场景一般是用户从关系型数据库中导出了文本类型的数据,然后希望能把导出的数据写到HBase里。在这种情况下,需要小心谨慎地设计和实现FileInputFormat的file split逻辑。   3. BulkLoad ...
  • 我们知道在高并发下数据库的一种优化方案:读写分离,它就是依靠主从复制的技术使得数据库实现了数据复制为多份,增强了抵抗大量并发读请求的能力,提升了数据库的查询性能的同时,也提升了数据的安全性,当某一个...
  • 在过去的几年中,在写入容量方面,我遇到了瓶颈,并在不同的 ES 群集上犯了许多错误。 尤其是其中一项要求是写入具有严格 SLA 的实时索引以进行读取操作时。 如果你在生产环境中使用 Elasticsearch,很可能你也已经...
  • 深入浅出-数据库的原理

    千次阅读 2016-06-29 21:54:56
    如果有人问你数据库的原理,叫他看这篇文章 ...一提到关系型数据库,我禁不住想:有些东西被忽视了。关系型数据库无处不在,而且种类繁多,从小巧实用的...你可以自己谷歌/百度一下『关系型数据库原理』,看看结果
  • 消息队列

    千次阅读 多人点赞 2019-09-19 21:42:59
    如上图,在不使用消息队列服务器的时候,用户的请求数据直接写入数据库,在高并发的情况下数据库压力剧增,使得响应速度变慢。但是在使用消息队列之后,用户的请求数据发送给消息队列之后立即 返回,再由消息队列的...
  • HBase

    千次阅读 2019-09-02 17:07:32
    HBase是Apache提供的开源的非关系型数据库。 HBase的底层存储是基于Hadoop,是一个分布式,可扩展,大数据库数据库 HBase能够实时读写大量的数据。单张表就可以做到10亿*百万列数据量的级别。 Hbase是一个NOSQL(not ...
  • Redis面试题集

    千次阅读 多人点赞 2019-09-16 10:19:31
    (2)数据库会将操作信息写入binlog日志当中 (3)订阅程序提取出所需要的数据以及key (4)另起一段非业务代码,获得该信息 (5)尝试删除缓存操作,发现删除失败 (6)将这些信息发送至消息队列 (7)重新从...
  • 是时候选择NewSQL数据库

    万次阅读 2016-12-01 13:53:47
    开源 RDBMS 与互联网的崛起很长时间以来,关系型数据库一直是大公司的专利,市场被 Oracle / DB2 等企业数据库牢牢把持。但是随着互联网的崛起、开源社区的发展,上世纪九十年代 MySQL 1.0 的发布,标志着关系型...
  • sql server 磁盘瓶颈

    千次阅读 2011-08-22 16:43:46
    1.确认磁盘瓶颈 PhysicalDIsk--Avg.Disk Queue Length 这个计数器报告了每个磁盘的队列长度,如果队列长度持续大于2,则可能会影响到性能。 PhysicalDisk--Avg.Disk sec/Read和Avg.Disk sec/Write 表
  • 在淘宝平台的改造过程中,通过各个服务中心拥有各自独立数据库的方式,即采用数据垂直分区的方式对业务数据进行分区。确实在很大程度上缓解了当时数据库连接资源少和数据库因为表多、数据多造成的性能等问题。随着...
  • 大家都听过这样的一句话:在物联网大数据的场景之下,TDengine最大的优势之一,就是写入速度——这是由于TDengine独特设计的成果。但是,一些用户在初次使用TDengine的时候会觉得写入性能并没有达到自己的预期。这些...
  • NoSQL 发展,数据库发展MySQL瓶颈

    千次阅读 2017-04-10 21:23:14
    NoSQL概念随着web2.0的快速发展,非关系型、分布式数据存储得到了快速的发展,它们不...(“NoSQL”一词最早于1998年被用于一个轻量级的关系数据库的名字。)NoSQL被我们用得最多的当数key-value存储,当然还有其他的文
  • mysql千万级数据库插入速度和读取速度的调整记录。
  • 现在的应用服务已经不再是单一化的组成了,我们随时可以将自己的应用部署到云上提供访问和服务,数据库也一样。云数据库(字符数据)概念跟云存储(字符、图片、文件等)是相似的,就是通过多个计算机节点提供资源池...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 62,227
精华内容 24,890
关键字:

数据库写入瓶颈